Daily collection of common SQL query statements Daquan _mssql

The common SQL query statement looks like this:

First, simple query statement

1. View table structure

SQL>DESC EMP;

2. Query all Columns

Sql>select * from EMP;

3. Query specified column

Sql>select empmo, ename, Mgr from EMP;

Sql>select DISTINCT Mgr from EMP; Show only items with different results

4. Query specified line

Sql>select * from emp WHERE job= ' clerk ';

5. Using an arithmetic expression

Sql>select ename, SAL*13+NVL (comm,0) from EMP;

NVL (comm,1) means that if there is a value in Comm, then NVL (comm,1) =comm; No value in Comm, then NVL (comm,1) = 0.

Sql>select ename, SAL*13+NVL (comm,0) year_sal from EMP; (Year_sal is an alias and can be sorted by alias)

Sql>select * from emp WHERE hiredate> ' January-January-82 ';

6. Using the LIKE operator (%,_)

% represents one or more characters, _ represents a character, [charlist] represents any single character of the word columns, [^charlist] or any single character [!charlist] not in the word columns.

Sql>select * from the EMP WHERE ename like ' s__t% ';

7. In the Where condition, use in

Sql>select * from EMP WHERE job in (' Clerk ', ' ANALYST ');

8. Query field content is empty/non-empty statement

Sql>select * from emp WHERE Mgr Is/is is not NULL;

9. Using Logical operation Symbols

Sql>select * from the EMP WHERE (sal>500 or job= ' MANAGE ') and ename like ' j% ';

10. Sort the query results by the values of the fields

Sql>select * from emp deptno, Sal DESC; (Ascending by department and in descending pay)

Second, complex query

1. Data Grouping (Max,min,avg,sum,count)

Sql>select MAX (SAL), MIN (age), AVG (SAL), SUM (SAL) from EMP;

Sql>select * from the EMP where sal= (SELECT MAX (SAL) from EMP);

Sql>selec COUNT (*) from EMP;

2. Group BY (for grouping statistics on query results) and HAVING clause (used to restrict the display of grouped results)

Sql>select Deptno,max (SAL), AVG (SAL) from EMP GROUP by Deptno;

Sql>select deptno, Job, avg (SAL), MIN (SAL) from EMP Group by Deptno,job has AVG (SAL) <2000;

For a summary of data grouping:

A. A grouping function can only appear in a select list, having, ORDER BY clause (cannot appear in a where)

B. If the SELECT statement contains a group by, a having, an order by, then they are ordered by the group by.

C. If there are columns, expressions, and grouping functions in the selection column, the columns and expressions must appear in the GROUP BY clause, or there will be an error.

The use of group by is not a prerequisite for having a having.

3. Multi-Table Query

Sql>select e.name,e.sal,d.dname from EMP E, dept D WHERE E.deptno=d.deptno ORDER by D.deptno;

Sql>select E.ename,e.sal,s.grade from EMP e,salgrade s wher e.sal BETWEEN s.losal and s.hisal;

4. Self-connection (refers to the same table connection query)

Sql>select Er.ename, ee.ename mgr_name from emp er, emp ee where er.mgr=ee.empno;

5. Subqueries (SELECT statements embedded in other SQL statements, also called nested queries)

5.1 Single-line subquery

Sql>select ename from EMP where deptno= (SELECT deptno from EMP where Ename= ' Smith '), and the name of the person in the query table with the SMITH department. Because there is only one row to return the result, the subquery is concatenated with "="

5.2 Multi-line subqueries

Sql>select Ename,job,sal,deptno from EMP where job in (SELECT DISTINCT job from EMP where deptno=10); the same worker in the query table that works with department Number 10 The name, work, salary and department number of the worker. Because there are multiple rows of returned results, the subquery is concatenated with "in".

In and exists: the subquery following the exists () is called the correlated subquery, which does not return the value of the list. Simply returns the result of a ture or false, which runs by first running the main query once and then querying its corresponding result in the subquery. If it is ture, the output will not be output. Then query in the subquery according to each line in the main query. The subquery that follows in () returns the result set, in other words, the execution order is different from the exists (). The subquery first produces the result set, and then the main query goes to the result set to find the list of fields that meet the requirements. The output that conforms to the requirements is not output.

5.3 Use all

Sql>select Ename,sal,deptno from EMP where Sal> all (SELECT sal from EMP where deptno=30); Sql>select ename,sal,d Eptno from the EMP where sal> (SELECT MAX (SAL) from EMP where deptno=30) and query the name, salary, and department number of the employee whose salary is higher than the department number 30th. The above two statements are functionally the same, but the functions are much higher in execution efficiency.

5.4 Use any

Sql>select Ename,sal,deptno from EMP where sal> either (SELECT sal from EMP where deptno=30); Sql>select ename,sal,d Eptno from the EMP where sal> (SELECT MIN (SAL) from EMP where deptno=30); The name of the employee whose salary is higher than the number of any employee with the department number 30th (as long as the salary is higher than that of the employee), Salary and department number. The above two statements are functionally the same, but the functions are much higher in execution efficiency.

5.5 Multi-column subqueries

Sql>select * from the EMP where (job, deptno) = (SELECT job, deptno from emp where ename= ' SMITH ');

5.6 Using subqueries in the FROM clause

Sql>select emp.deptno,emp.ename,emp.sal,t_avgsal.avgsal from EMP, (SELECT emp.deptno,avg (emp.sal) avgsal from EMP GROUP by Emp.deptno) T_avgsal where Emp.deptno=t_avgsal.deptno and emp.sal>t_avgsal.avgsal the order by Emp.deptno;

5.7 Pagination Query

Each row of data in a database has a corresponding line number, called RowNum.

Sql>select a2.* from (select a1.*, rownum rn from (SELECT * to emp Order by Sal) A1 where rownum<=10) A2 where rn& gt;=6;

Specify query columns, query result ordering, and so on, only need to modify the innermost subquery.

5.8 Creating a new table with query results

Sql>create TABLE MyTable (id,name,sal,job,deptno) as SELECT empno,ename,sal,job,deptno from EMP;

5.9 Merge Query (union and set, intersect intersection, UNION All and set + intersection, minus difference set)

Sql>select ename, Sal, job from emp WHERE sal>2500 UNION (intersect/union all/minus) SELECT ename, Sal, job from EMP WHERE job= ' MANAGER ';

The execution efficiency of the merge query is much higher than that of and,or logical query.

5.10 Inserting data with subqueries

Sql>create TABLE myemp (EmpID number (4), name VARCHAR2, sal number (6), Job varchar2 (a), dept number (2)); Build an empty table first;

Sql>insert into Myemp (EmpID, name, Sal, Job, dept) SELECT Empno, ename, Sal, Job, deptno from EMP WHERE deptno=10; Then the data of the department Number 10 in the EMP table is inserted into the new table Myemp to realize the batch query of the data.

5.11 Using the query to update the data in the table

Sql>update emp SET (Job, sal, comm) = (SELECT job, Sal, comm from EMP where ename= ' SMITH ') where ename= ' SCOTT ';

The above is described in this article for you to introduce the daily collection of commonly used SQL query statements Daquan, I hope you like.