SQL simple query, limited query and sorting

SQL, Structured query Language Structured Query Language , is a database query and programming language for accessing data and querying, updating, and managing relational database systems.

SQL language contains 4 sections:

※ data definition language (DDL) create , drop , alter

※ Data manipulation language (DML) insert , update , delete statement.

※ data query Language (DQL) select statement.

※ Data Control Language (DCL) , such as: GRANT , REVOKE , COMMIT , ROLLBACK and other statements.

Simple Query

The syntax format for a simple SQL query statement is:

Select *| Field List Aliases

from Table name ;

--Query all columns SELECT * FROM emp;--query specified column select Empno, ename, job from emp;--specify column alias select empno number, ename name, job from Emp;sel ECT empno as number, ename as name, job as work from emp;--to duplicate result select job from Emp;select distinct job from emp;--connection column result select ' Number is ' | | Empno | | ' Employee's ' | | ' Name is: ' | | ename | | ', Work is: ' | | Job from emp;--simple arithmetic select sal*12 Annual salary from EMP;

Qualifying Queries

The syntax format for a qualified query is

select{distinct}*| specific column name aliases

From table name

{Where condition statement};

--Check out all employees with a salary greater than 1500 select * from emp where sal > 1500;--query monthly employee information that can get a bonus select * from EMP where comm are NOT null;  --Query employee information without bonuses SELECT * from EMP where comm is null;--query for employee information with base pay greater than 1500 and can receive bonuses SELECT * from emp where Sal > Comm is not null;--query for information about employees who do not have a base salary of 1500 and cannot receive bonuses SELECT * from emp where Sal <= and comm are null;--not can reverse the condition, Turn the condition into a false, turn the false into a true select * from the EMP where not (Sal >, or comm are not null);--Query all information for employees with a base salary greater than or equal to 1500, but less than or equal to 3000 select * From EMP where Sal >= and sal <= 3000;--between keyword, SQL provides a specific range of filter statements between ... And ... The format is: between minimum and maximum select * from emp where Sal between and 3000;--query born from January 1, 1981 to December 31 Employee Information SELECT * FROM emp where hiredate between date ' 1981-01-01 ' and date ' 1981-12-31 '; select * from emp where hiredate between ' January-January -81 ' and ' 31-  December -81 '; select * from emp where hiredate between ' January-January -1981 ' and ' 3 January-December-1981 ';--query out name is Smith's employee information select * from EMP where ename = ' SMITH ';--a letter from the employee who queried the employee number is 7369,7499,7521Interest SELECT * from emp where empno = 7369 or empno = 7499 or empno = 7521;--in keyword in (val1, Val2, Val3 ... valn) SELECT * FROM EMP where empno in (7369, 7499, 7521);--Query the employee number that is not 7369,7499,7521 of the employee select * from EMP where empno not in (7369, 7499, 75 21);--Query for employee information named Smith,allen,king SELECT * from emp where ename in (' SMITH ', ' ALLEN ', ' KING ');--Query employee information for employee's second letter "L" Selec T * from EMP where ename like ' _l% ';----query employee whose name begins with the letter "s" SELECT * from the EMP where ename like ' s% ';----query employee information for employee name containing the letter "s" s Elect * from EMP where ename like '%s% ';--Inquiry into employee information for 81 years of entry year SELECT * from emp where hiredate like '%81 ';---Query employee information with number 5 in payroll value S Elect * from EMP where Sal is like '%5% ';--query all department numbers not 10 employee information select * from emp where Deptno <> 10;select * from EMP wher E Deptno! = 10;

Sort

The syntax format for sorting is

Select {DISTINCT} *| specific column aliases

From table name

{Where Condition}

{ORDER by Sort Field 1, sort field 2 asc| DESC}

--Sort employee information by salary Ascending select * from emp ORDER by Sal asc;--the employee information in descending order select * from emp order BY Sal Desc;  --Retrieves the employee information for department number 30, and according to the salary descending, the same salary is the same as the date of entry in ascending order select * from emp where deptno = the ORDER by sal Desc, hiredate Asc;select * from EMP WHERE deptno = ORDER BY sal Desc, Hiredate;select empno,ename,job,mgr,hiredate,sal,comm,deptno from EMP where dept No = 6 desc, 5 asc;select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where deptno = ORDER BY 6 D ESC, 5;select * from emp where deptno = 6 desc,5;

SQL simple query, limited query and sorting