Common SQL query statements

First, simple query statements

1. View table structure

SQL>DESC EMP;

2. Querying all Columns

Sql>select * from EMP;

3. Querying a specified column

Sql>select empmo, ename, Mgr from EMP;

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

4. Querying a specified row

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 are aliases and can be sorted by alias)

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

6. Using the LIKE operator (%,_)

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

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

7. In the Where condition, use the In

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

8. Query for empty/non-empty statements of field contents

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

9. Using Logical operation Symbols

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

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

Sql>select * from emp ORDER by Deptno, Sal DESC; (by department ascending, and by salary descending)

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 EMP where sal= (SELECT MAX (SAL) from EMP));

Sql>selec COUNT (*) from EMP;

2. Group BY (Grouping statistics for query results) and HAVING clause (used to restrict grouping display results)

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

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

For a summary of data groupings:

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

B. If the SELECT statement contains both group BY, have, order by, then the order by IS group BY, have, order by.

C. In the Select column, if there are columns, expressions, and grouping functions, the columns and expressions must appear in the GROUP BY clause, or an error will occur.

Using GROUP BY is not a precondition 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-connect (refers to the connection query of the same table)

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 the EMP where deptno= (SELECT deptno from EMP where Ename= ' Smith '); the name of the person in the query table that is in the same department as Smith. Because only one row is returned, use "=" to concatenate the subquery statement

5.2 Multi-row subqueries

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

In and exists: exists () the subquery that follows is called the correlated subquery, which is not the value of the returned list. Just returns the result of a ture or false, which runs the main query once, and then queries the subquery for its corresponding results. If the ture is output, the reverse is not output. Then, according to each row in the main query to go to the subquery query. In () is the subquery that 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 match the requirements. The output that meets the requirements, or vice versa.

5.3 Using all

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

5.4 Using any

Sql>select Ename,sal,deptno from EMP where sal> any (SELECT sal from EMP where deptno=30), or Sql>select ename,sal,d Eptno from the EMP where sal> (SELECT MIN (SAL) from EMP where deptno=30), query the employee whose salary is higher than the department number 30th (as long as it is higher than the salary of an employee), Salary and department number. The above two statements are functionally identical, but the function is much higher on the execution efficiency.

5.5 Multi-column subqueries

Sql>select * from 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 ORDER by Emp.deptno;

5.7 Paged 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 * from emp ORDER by Sal) A1 where rownum<=10) A2 where rn& gt;=6;

Specify query columns, query results sorting, and so on, only need to modify the innermost sub-query.

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 set, intersect intersection, UNION All unions + 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 ';

Merge queries are executed much more efficiently than logical queries such as And,or.

5.10 Inserting data using subqueries

Sql>create TABLE myemp (EmpID number (4), name VARCHAR2, sal number (6), Job varchar2, 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 insert the data of unit Number 10 in the EMP table into the new table myemp, and 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 ';

Common SQL query statements