Analysis of Oracle basic query filtering and sorting examples

Basic query:
Copy codeThe Code is as follows:
-- Query Information of all employees
Select * from emp;
-- Set the row width
Set linesize 120;
-- Set the column width to the width of four digits
Col empno for 9999;
-- Set the column width. a indicates the string length of eight digits.
Col ename for a8
-- Set pageSize to display 30 records per page
Set pagesize 30;
-- SQL supports arithmetic expressions. Note: if an expression contains null values, the entire expression is empty.
Select empno, ename, sal, sal * 12, comm, sal * 12 + comm from emp;
-- Use the filter empty function. If comm is empty, it will be replaced by 0.
Select empno, ename, sal, sal * 12, comm, sal * 12 + nvl (comm, 0) from emp;
-- Three methods of Alias are used. There is no difference between double quotation marks in the alias: Double quotation marks can contain spaces and special characters, but not double quotation marks.
Select empno as "employee ID", ename "name", sal monthly salary, comm, sal * 12 + nvl (comm, 0) annual income from emp;
-- About disctinct, take the same record only once
Select distinct deptno from emp;
-- When disctinct and multiple values are the same, deptno and job are considered the same records.
Select distinct deptno, job from emp;
-- Connector usage
Select ename | 'salary: '| sal from emp;

Note:
The SQL language is case insensitive.
SQL statements can be written in one or more rows.
Keywords cannot be abbreviated or branch
Each clause is generally written by a branch.
Use indentation to improve statement readability.

Filtering and sorting
Copy codeThe Code is as follows:
-- Query System Parameters
Select * from v $ nls_parameters;
-- Modify the Date Format
Alter session set NLS_DATE_FORMAT = 'yyyy-mm-dd ';
-- The conversion date of the hermit mode will affect the performance.
Select * from emp where hiredate = '2017-11-17 ';
-- Display mode conversion date
Select * from emp where hiredate = to_date ('2017-11-17 ', 'yyyy-mm-dd ');
-- Comparison operator: queries employees whose salaries are greater than 1000 and smaller than 2000. between and includes boundaries
Select * from emp where sal> = 1000 and sal <= 2000;
Select * from emp where sal between 1000 and 2000;
-- In: queries employees of Department 10 and department 20 in the collection.
Select * from emp where deptno = 10 or deptno = 20;
Select * from emp where deptno in (10, 20 );
-- Fuzzy query: queries employees whose names start with "S"
Select * from emp where ename like's % ';
-- To query employees whose names contain four characters, four underscores are required.
Select * from emp where ename like '____';
-- Query the employees whose names contain underscores (_). _ escape is required when underlines are special characters. escape is declared by escape.
Select * from emp where ename like '% \ _ %' escape '\';
-- Query employees whose bonuses are not empty
Select * from emp where comm is not null;
-- Query employee information, sorted by monthly salary
Select * from emp order by sal;
-- A command: APPEND Command. Change the above to sort in descending order.
A desc
-- Query employee information, sorted by annual salary
Select empno, ename, sal, sal * 12 from emp order by sal * 12;
-- Order by can be followed by an alias
Select empno, ename, sal, sal * 12 annual salary from emp order by annual salary;
-- Order by can be followed by the sequence number
Select empno, ename, sal, sal * 12 from emp order by 4;
-- Order by applies to multiple columns, first sorting by the first column, and then sorting by the second column
Select * from emp order by deptno, sal;
-- Order by applies to multiple columns. in descending order, desc is required for each column.
Select * from emp order by deptno desc, sal desc
-- Route null values to the end
Select * from emp order by comm desc nulls last;
-- Shield/enable feedback
Set feedback off/set feedback on

Sorting rules:
It can be sorted by the column name in the select statement.
Sort by alias column name
It can be sorted by the sequential values of the column names in the select statement.
If you want to sort by multiple columns, the rule is to first sort by the first column, if the same, then sort by the second column; and so on