Learn SQL Diary (i)

Write a summary, always learn to forget. 11.01 Re-learning SQL-------Oracle

#1. Column format

You can control the display format of columns in a query result set by using the column command.

Syntax format: column[column_name alias option]

The column_name parameter specifies the name of the column that will be controlled

The alias parameter is used to specify the name of the column

The option parameter is used to specify the display format for a column

Partial values and meanings of option options

Clear clears the display properties set for the column so that it uses the default display properties

Color defines the display colors for a column

format Specifies the display format for the column

Heading defining the title of a column

Justify adjust the alignment of column headings default number column right-aligned I other columns left align to set the value to

...

#2. Set pagination display

Set pagesize 100--100 rows of data per page display

Set pagesize 0--Indicates no paging

#3. Null value in Oracle

1) Any data can be null

2) null value and any data do arithmetic operation result is null value

3) null value and string do JOIN operation result equivalent null value does not exist

@1. function NVL syntax for handling null values: NVL (D1,D2)--If D1 is empty then D2 instead

The arguments to the @2.nvl function can be numeric, character, or date, but the data type of the two parameter must be the same

#4. Sql> select empno, ename | | Job detail from Emp_xxx;

--"| |" notation means two data strings, similar to the + number between two strings in Java

--The previous example is to concatenate ename and job two strings--if the job is null, it is equivalent to not stringing any characters.

Sql> select empno, ename | | ' Work as ' | | Job detail from Emp_xxx;

--There are constant characters between the two fields of the thread, one for each row

#5. The case sensitivity of SQL statements is case insensitive to data

Sql> SELECT * from emp_xxx where job = ' Analyst ';

--If the data is analyst, no results are found

#6. Lower () character data converted to lowercase

Upper () character data converted to uppercase

Ignore case to find employees with position "analyst"
Sql> SELECT * from emp_xxx where upper (job) = ' ANALYST ';

#7. Between and keyword isolated data is closed interval

#8. Fuzzy matching like%

Sql> Select COUNT (*) from User_tables

Where table_name like ' s\_% ' escape ' \ ' ;
--if there are special characters (such as _ or%) in the data to be queried,

-When making fuzzy queries,

--need to add \ symbol to escape and specify escape phrase

Learn SQL Diary (i)