Analysis of Oracle Basic Query Filter Sorting Example

Basic query:

-- Query information about all employees 
select * from emp;
-- Set line width 
set linesize 120;
-- Set the column width to 4 Width of digits 
col empno for 9999;
-- Set column width ,a Represents a total of strings 8 Bit length 
col ename for a8
-- Settings pageSize Display per page 30 A record 
set pagesize 30;
--sql Support arithmetic expressions in , Note: If 1 If an expression contains a null value, the whole expression is null 
select empno,ename,sal,sal*12,comm,sal*12+comm from emp;
-- Use the filter function if comm If it is empty, it will be used with 0 Substitute 
select empno,ename,sal,sal*12,comm,sal*12+nvl(comm,0) from emp;
-- Using an alias 3 A kind of way , There is a difference between aliases without double quotation marks: those with double quotation marks can contain spaces and special characters, while those without double quotation marks cannot 
select empno as " Employee number ",ename " Name ",sal  Monthly salary ,comm,sal*12+nvl(comm,0) Annual income  from emp;
-- About disctinct, The same record is only taken 1 Times 
select distinct deptno from emp;
-- When disctinct When following multiple values , When deptno And job Mean 1 Sample, it is considered to be the same record 
select distinct deptno,job from emp;
-- Use of Connectors 
select ename ||' The salary is '||sal from emp;

Note:
The SQL language is case insensitive.
SQL can be written on 1 or more lines
Keywords cannot be abbreviated or branched
Each clause 1 should be written separately.
Use indentation to improve the readability of statements.

Filtering and sorting

-- Query system parameters 
select * from v$nls_parameters;
-- Modify the date format 
alter session set NLS_DATE_FORMAT='yyyy-mm-dd';
-- The hermit mode converts the date, which will affect the performance 
select * from emp where hiredate='1981-11-17';
-- Display mode conversion date 
select * from emp where hiredate=to_date('1981-11-17','yyyy-mm-dd');
-- Comparison operator, query salary is greater than 1000 , less than 2000 Employees, between and Boundary 
select * from emp where sal>=1000 and sal<=2000;
select * from emp where sal between 1000 and 2000;
--in:  In the collection, query 10 No. Department and 20 Employees of Department No. 
select * from emp where deptno=10 or deptno=20;
select * from emp where deptno in (10,20);
-- Fuzzy query : Query name to S Head employee 
select * from emp where ename like 'S%';
-- The query name is 4 Word of employees, requirements 4 Underline 
select * from emp where ename like '____';
-- Inquire about employees whose names are underlined, _ Underscores are special characters that need to be escaped. escape  Declare escape 
select * from emp where ename like '%\_%' escape '\';
-- Query employees whose bonus is not empty 
select * from emp where comm is not null;
-- Query employee information and sort by monthly salary 
select * from emp order by sal;
--a Command: Append the command to change the top to descending order 
a  desc
-- Query employee information and sort by annual salary 
select empno,ename,sal,sal*12 from emp order by sal*12;
--order by It can be followed by an alias 
select empno,ename,sal,sal*12  Annual salary  from emp order by  Annual salary ;
--order by It can be followed by a serial number 
select empno,ename,sal,sal*12 from emp order by 4;
--order by For multiple columns, first follow the 1 Column sorting; Then according to the 2 Column row 
select * from emp order by deptno,sal;
--order by Acting on multiple columns, descending order requires each column to have desc
select * from emp order by deptno desc,sal desc
-- Put null values last 
select * from emp order by comm desc nulls last;
-- Shielding / Turn on feedback information 
set feedback off/set feedback on

Rules for sorting:
You can sort by column name in select statement
You can sort by other names
You can sort by the order value of column names in the select statement
If you want to sort according to multiple columns, the rule is to sort according to the first column first, and if the same, sort according to the second column; And so on