SQL simple query, limited query and sorting, SQL query limited sorting

SQL simple query, limited query and sorting, SQL query limited sorting

Structured Query Language (structured Query Language) is a database Query and programming Language used to access data and Query, update, and manage relational database systems.

The SQL language consists of four parts:

※Data Definition Language (DDL)Such as CREATE, DROP, and ALTER statements.

※Data Operation Language (DML)Such as INSERT, UPDATE, and DELETE statements.

※Data Query Language (DQL)For example, SELECT statement.

※Data Control Language (DCL)For example, GRANT, REVOKE, COMMIT, ROLLBACK, and other statements.

Simple Query

The syntax format of a simple SQL query statement is:

Select * | field list alias

From table name;

-- Query all columns select * from emp; -- Query specified columns select empno, ename, job from emp; -- specify the column alias select empno number, ename name, job from emp; select empno as number, ename as name, job as job from emp; -- deduplication result select job from emp; select distinct job from emp; -- join column result select 'number is' | empno | 'employee '|' name is: '| ename |'. The job is: '| job from emp; -- simple four arithmetic operations select sal * 12 annual salary from emp;

Limited Query

The syntax format of the restricted query is

Select {distinct} * | alias of the specific column name

From table name

{Where Condition Statement };

-- Query the select * from emp where sal> 1500 for all employees with a salary greater than 1500; -- query the employee information that can receive the bonus each month. select * from emp where comm is not null; -- Query employee information without bonuses select * from emp where comm is null; -- query the result that the basic salary is greater than 1500, select * from emp where sal> 1500 and comm is not null; -- the basic salary cannot exceed 1500, select * from emp where sal <= 1500 and comm is null; -- not can reverse the condition to false, convert false to true select * from emp where not (sal> 1500 or comm is not null); -- Query all information of employees whose basic salary is greater than or equal to 1500, but whose basic salary is less than or equal to 3000 select * from emp where sal> = 1500 and sal <= 3000; -- between keyword. SQL provides a special filter statement for a specified range.... AND... Format: BETWEEN minimum AND maximum select * from emp where sal between 1500 and 3000; -- query the employee information generated on January 1, 1981. select * from emp where hiredate between date '2017-01-01 'and date '2017-12-31 '; select * from emp where hiredate between '1-January-81 'and '31-December-81 '; select * from emp where hiredate between '1-December 1-December 31-December 31-December 31-December 1981 '; -- query the employee information whose name is SMITH. select * from emp where ename = 'Smith '; -- query the employee number 72.16,7. 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,749 9, 7521); -- query whether the employee number is not 72.16,7499, select * from emp where empno not in (7521 9, 7369,749); -- query the employee name SMITH, ALLEN, KING employee information select * from emp where ename in ('Smith ', 'allen', 'King '); -- query the employee information with the second letter "L" of the employee. select * from emp where ena Me like '_ L %'; -- query the employee information starting with the employee name "S" select * from emp where ename like's % '; -- query the information of an employee whose name contains the letter "S". select * from emp where ename like '% S % '; -- select * from emp where hiredate like '% 81' to query the employee information whose employment year is 81 '; -- query the employee information that contains the number 5 in the salary value select * from emp where sal like '% 100 '; -- query the information of employees whose department number is not 10 select * from emp where deptno <> 10; select * from emp where deptno! = 10;

Sort

The syntax format of sorting is

Select {distinct} * | column alias

From table name

{Where condition}

{Order By sorting Field 1, sorting Field 2 ASC | DESC}

-- Sort employee information in ascending order of wages select * from emp order by sal asc; -- Sort employee information in descending order of wages select * from emp order by sal desc; -- retrieve information about employees whose department number is 30 and sort by wage in descending order. If the wages are the same, select * from emp where deptno = 30 order by sal desc in ascending order of employment date, hiredate asc; select * from emp where deptno = 30 order by sal desc, hiredate; select empno, ename, job, mgr, hiredate, sal, comm, deptno from emp where deptno = 30 order by 6 desc, 5 asc; select empno, ename, job, mgr, hiredate, sal, comm, deptno from emp where deptno = 30 order by 6 desc, 5; select * from emp where deptno = 30 order by 6 desc, 5;