① in SQL is not equal to using <>eg:select * from EMP where Deptno <>10;
The ① fuzzy match keyword is like _ represents a character, and% represents 0 or more. Eg:select ename from emp where ename like ' _a% ';
Escape characters in ②sql follow the ' used characters ' after the Escape keyword (the characters in the experiment can be any character, numbers and letters, but must be able to be 1 characters)
Eg:select * FROM student WHERE sname like '%\%% ' ESCAPE ' \ ';
SELECT * FROM student WHERE sname like '%$%% ' ESCAPE ' $ ';
③ is sorted by default in ascending order by using order by ASC can not write, descending is desc. When you use a combination, you sort the first field by using the sort, the first field is ordered, the inner order is followed by the field collation
Eg:select Ename,sal,deptno from emp order by DEPTNO Asc,ename desc;
In the above example, the data in the same deptno is sorted in descending order of ename by first Deptno.
So, be sure to consider which!!! The main field of the sort is.
④ a simple consolidated SQL statement Select ENAME,SAL*12 Annual salary from EMP
where ename not like ' _a% ' and sal > 800
ORDER BY Sal Desc;
⑤ character functions: substr (field name, beginning character position, truncated character length) the subscript of the string is 1, beginning with the experimental Start field 0 and 1 results are from the first character Eg:select substr (ename,0,2) from EMP;
Select substr (ename,1,2) from EMP;
The results of the above two sentences are the same.
⑥ number function: Ⅰround (m) rounded to integer; Round (M,n) n is positive, represents the number of decimal places, n is negative, then is a positive rounding select round (23.456) from dual;//result 23
Select Round (23.456,2) from dual;//results 23.46
Select Round (23.456,-1) from dual;//results 20
Select Round (123.456,-2) from dual;//results 100
Ⅱtrunc (M,n) truncation n is a positive number when the digit is retained after the decimal point, is negative, the bit is 0;
Select Trunc (23.456,1) from dual;//results 23.4
Select Trunc (23.456,-1) from dual;//results-1
★⑦ conversion Function: To_char (data to be converted, format) in the date format conversion, HH24 is a 24-hour system, HH is 12 hour minutes with Mi eg:select to_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') from Dual; output results: 2014-09-13 15:13:17
Select To_char (sysdate, ' Yyyy-mm-dd hh:mi:ss ') from dual; Output: 2014-09-13 03:13:17
The ★⑧ aggregate function: SUM (), AVG (), COUNT (), Max (), Min () count (1), and COUNT (*) are all the total number of records in the query table. COUNT (1) is recommended to be efficient and not to be tested,
Count (field name) represents the number of records in this field that are not empty.
COUNT (distinct field name) minus the number of records in this field after repetition
Eg:select count (1) from emp;//Results 14 Table of total 14 records
Select COUNT (*) from emp;//results 14 Description Table Total 14 records
Select COUNT (comm) from emp;//result 4 Description Comm column non-empty is 4 records
Select COUNT (deptno) from emp;//results 14 Description Table Deptno non-empty records are 14
Select COUNT (Distinct deptno) from emp;//results 3 Note the Detpno record that is not duplicated in the table is 3
Orcal Database Review notes
