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 the column width to four digits
Col empno for 9999;
--Set column width, a indicates a string of eight-bit length
Col ename for A8
---Set pagesize display 30 records per page
Set PageSize 30;
Arithmetic expressions are supported in--sql, note: If an expression contains a null value, the entire expression is empty
Select Empno,ename,sal,sal*12,comm,sal*12+comm from EMP;
--Using an empty filter function, if Comm is empty, replace with 0
Select EMPNO,ENAME,SAL,SAL*12,COMM,SAL*12+NVL (comm,0) from EMP;
--in three ways of using aliases, there is the difference between the aliases: double quotes can contain spaces and special characters, without double quotes, you can't
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 once
SELECT DISTINCT deptno from EMP;
--when disctinct with multiple values, when the deptno and job are the same, it is considered to be the same record
SELECT distinct deptno,job from EMP;
--Use of connectors
Select Ename | | ' 's salary is ' | | Sal from EMP;
Attention:
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 mode conversion date, will 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, query for employees with a salary greater than 1000, less than 2000, between and include 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 for four-word employees, four underscores required
SELECT * from emp where ename like ' ___ ';
--Query The meaning of the employee in the name, _ Underline for special characters need to escape, escape declaration escape
SELECT * from emp where ename like '%\_% ' escape ' \ ';
--Query for employees with no empty bonuses
SELECT * FROM EMP where Comm was 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 yearly salary
Select empno,ename,sal,sal*12 from emp to sal*12;
--order by following the alias
Select Empno,ename,sal,sal*12 Yearly salary from EMP;
--order by followed by the 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 order requires each column to have DESC
SELECT * from emp to DEPTNO desc,sal desc
--line The null value to the end
SELECT * FROM emp to 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;
