The following is a detailed analysis of the basic query filtering ordering examples in Oracle, which can be consulted by the
Basic query:
Copy Code code as follows:
--Query all employees for information
select * from EMP;
--Set line width
set Linesize 120;
--Sets the width of a column width of four digits
col empno for 9999;
--Sets the column width, a indicates a string of eight-bit length
Col ename for A8
--Set pagesize 30 records per page
set pagesize 30;
Arithmetic expressions are supported in
--sql, 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 filter function if Comm is empty, replace
with 0
Select EMPNO,ENAME,SAL,SAL*12,COMM,SAL*12+NVL (comm,0) from EMP;
--three ways to use aliases, there is the difference between the unique quotes in the alias: double quotes can contain spaces and special characters, without double quotes, you 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 taken only once
SELECT DISTINCT deptno from EMP;
-When disctinct with multiple values, it is considered to be the same record when deptno and job are the same
SELECT distinct deptno,job from EMP;
--Use of connectors
Select Ename | | ' 's salary is ' | | Sal from EMP;
Note:
SQL language is not sensitive to case.
SQL can be written on one line or multiple lines
Keywords cannot be abbreviated nor can branches
The clauses are usually written in branches.
Use indentation to improve the readability of the statement.
Filtering and sorting
Copy Code code as follows:
--Query system parameters
select * from V$nls_parameters;
--Modify date format
alter session set nls_date_format= ' YYYY-MM-DD ';
--Hermit-style conversion dates affect 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 that queries employees with a salary greater than 1000, less than 2000, between and contains boundaries
SELECT * from emp where sal>=1000 and sal<=2000;
SELECT * from emp where Sal between 1000 and 2000;
--in: In the collection, check the staff of Department No. 10th and Unit 20th
SELECT * from emp where deptno=10 or deptno=20;
SELECT * from emp where deptno in (10,20);
-Fuzzy query: Query the employee whose name begins with S
SELECT * from emp where ename like ' s% ';
-Query the employee with a four-word name, requiring four underscores
SELECT * from emp where ename like ' ___ ';
--Query the name in the meaning of the underlined employees, _ underline for special characters need to escape, escape declaration escape
SELECT * from emp where ename like '%_% ' escape ';
--query for employees who are not empty bonuses
SELECT * from EMP where comm are NOT null;
--Check employee information, sorted by monthly salary
SELECT * from emp order by Sal;
--a command: Append command, change top to descending sort
a desc
--Check employee information, sorted by annual salary
Select empno,ename,sal,sal*12 from the emp order by SAL*12;
--order by followed by alias
Select empno,ename,sal,sal*12 Yearly salary from EMP;
--order by After can follow serial number
Select empno,ename,sal,sal*12 from emp order by 4;
--order by action on multiple columns, sorted by first column, and then by second row
SELECT * from emp order by Deptno,sal;
--order by action on multiple columns, descending requires each column to have desc
SELECT * from emp deptno desc,sal desc
--The null value is queued to the last
SELECT * from EMP is comm desc nulls last;
--shielding/opening feedback information
Set Feedback off/set feedback on
rules for sorting:
You can sort by column names in the SELECT statement
You can sort by alias column name
You can sort by the order values of the column names in the SELECT statement
If you want to sort by more than one column, the rule is sorted by the first column and, if the same, by the second column;
