Orcal database review notes

Orcal database review notes
① In SQL, which is not equal to <> eg: select * from emp where deptno <> 10; ① fuzzy match keyword is like _, which represents a character, % indicates 0 or more. eg: select ename from emp where ename like '_ A % ';
② Escape characters in SQL are followed by the escape keyword by The 'character used '(the character in the experiment ''can be any character, number, or letter, but it must be 1 character)
Eg: SELECT * FROM student WHERE sname LIKE '% \ %' ESCAPE '\';
SELECT * FROM student WHERE sname LIKE '% $ % 'escape' $ '; ③ When order by is used, asc can be sorted in ascending order by default, and desc is used in descending order. when a combination is used, it is first sorted by the first field in use. After the first field is sorted in order, the internal order is then followed by the field sorting rules.
Eg: select ename, sal, deptno from emp order by deptno asc, ename desc;
In the preceding example, data in the same deptno is sorted in descending order by ename after being sorted in the ascending order of deptno.
Therefore, you must consider which primary field is sorted !!! ④ A simple and comprehensive SQL statement select ename, sal * 12 annual salary from emp
Where ename not like '_ A %' and sal> 800
Order by sal desc; ⑤ character function: substr (field name, starting position, truncated length) the subscript of the string is from 1. The first field of the experiment is written to 0 and 1. The result is from the first character. For example: select substr (ename,) from emp;
Select substr (ename, 1, 2) from emp;
The execution result of the preceding two statements is the same. 6 numeric functions: I round (m) Rounding to an integer; round (m, n) n is a positive number, representing the digits of decimal places, n is a negative number, then it is a positive number rounded to select round (23.456) from dual; // result 23
Select round (23.456, 2) from dual; // result 23.46
Select round (23.456,-1) from dual; // result 20
Select round (123.456,-2) from dual; // result 100
Ⅱ trunc (m, n) truncation if n is a positive number, it is the number of digits retained after the decimal point. If n is a negative number, it is 0;
Select trunc (23.456, 1) from dual; // result 23.4
Select trunc (23.456,-1) from dual; // result-1★7. Conversion Function: to_char (data to be converted, format) in date format conversion, hh24 is in 24-hour format, hh is in 12-hour format, and mieg: select to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss') from dual; output result: 15:13:17
Select to_char (sysdate, 'yyyy-mm-dd hh: mi: ss') from dual; output result: 03:13:17★Aggregate functions: sum (), avg (), count (), max (), min () count (1), and count (*) query the total number of records in a table. we recommend that you use count (1) to describe how efficient it is,
Count (field name) indicates the number of non-empty records in this field.
Count (distinct field name) removes the number of records in this field after repeated
Eg: select count (1) from emp; // result 14 indicates a total of 14 records in the table
Select count (*) from emp; // result 14 indicates a total of 14 records in the table
Select count (comm) from emp; // Result 4 indicates that four records are not empty in the comm column.
Select count (deptno) from emp; // result 14 indicates that the number of non-empty deptno records in the table is 14.
Select count (distinct deptno) from emp; // result 3 indicates that no repeated detpno records in the table are 3