Routine collection of commonly used SQL query statements and SQL statements

Routine collection of commonly used SQL query statements and SQL statements

Common SQL query statements are as follows:

1. Simple query statements

1. view the table structure

SQL> DESC emp;

2. query all columns

SQL> SELECT * FROM emp;

3. query specified Columns

SQL> SELECT empmo, ename, mgr FROM emp;

SQL> SELECT DISTINCT mgr FROM emp; only items with different results are displayed

4. query the specified row

SQL> SELECT * FROM emp WHERE job = 'cler ';

5. Use arithmetic expressions

SQL> SELECT ename, sal * 13 + nvl (comm, 0) FROM emp;

Nvl (comm, 1) means that, if there is a value in comm, nvl (comm, 1) = comm; comm has no value, 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> '01-July 82 ';

6. Use the like operator (% ,_)

% Indicates one or more characters, _ indicates one character, and [charlist] indicates any single character in the character column, [^ charlist] or [! Charlist] any single character not in the character column.

SQL> SELECT * FROM emp WHERE ename like's _ T % ';

7. Use the In clause In the where clause

SQL> SELECT * FROM emp WHERE job IN ('cler', 'analyst ');

8. query statements with blank or non-empty fields

SQL> SELECT * FROM emp WHERE mgr IS/IS NOT NULL;

9. Use logical operation symbols

SQL> SELECT * FROM emp WHERE (sal> 500 or job = 'manage') and ename like 'J % ';

10. Sort the query results by field values.

SQL> SELECT * FROM emp ORDER BY deptno, sal DESC; (sort BY department in ascending ORDER and sort BY salary in descending ORDER)

2. 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 (used for grouping statistics on query results) and having clause (used to restrict group Display 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 having AVG (sal) <2000;

Summary of data groups:

A. Grouping functions can only appear in the selection list, having, and order by clauses (not in where)

B. If select statements contain group by, having, and order by statements, the order of these statements is group by, having, and order.

C. If there are columns, expressions, and grouping functions in the selection column, these columns and expressions must appear in the group by clause; otherwise, an error occurs.

Using group by is not a prerequisite for 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-join (connection query for the same table)

SQL> SELECT er. ename, ee. ename mgr_name from emp er, emp ee where er. mgr = ee. empno;

5. subquery (the select statement embedded in other SQL statements, also called nested query)

5.1 single row subquery

SQL> SELECT ename FROM emp WHERE deptno = (SELECT deptno FROM emp where ename = 'Smith '); query the name of the person in the same department as SMITH in the table. Because only one row is returned, "=" is used to connect to 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 ); the name, job, salary, and department Number of the employee with the same department number as 10 in the query table. Because there are multiple rows of returned results, "IN" is used to connect the subquery statement.

The difference between in and exists: the subquery after exists () is called a subquery and does not return the value of the list. Only a true or false result is returned. The running mode is to run the primary query first, and then query the corresponding results in the subquery. If it is true, it is output, otherwise it is not output. Then, the subquery is performed based on each row in the primary query. The subquery after in () returns the result set. in other words, the execution order is different from that of exists. The subquery first generates a result set, and then the primary query goes to the result set to find a list of fields that meet the requirements. The output that meets the requirements, and vice versa.

5.3 use ALL

SQL> SELECT ename, sal, deptno FROM emp WHERE sal> ALL (SELECT sal FROM emp WHERE deptno = 30); or SQL> SELECT ename, sal, deptno FROM emp WHERE sal> (select max (sal) FROM emp WHERE deptno = 30 ); query the names, salaries, and department numbers of all employees with higher salaries than the Department number 30. The above two statements have the same functions, but the function will be much higher in terms of execution efficiency.

5.4 use ANY

SQL> SELECT ename, sal, deptno FROM emp WHERE sal> ANY (SELECT sal FROM emp WHERE deptno = 30); or SQL> SELECT ename, sal, deptno FROM emp WHERE sal> (select min (sal) FROM emp WHERE deptno = 30 ); query the name, salary, and department number of an employee whose salary is higher than the employee whose department number is 30 (as long as the employee's salary is higher than the employee's salary. The preceding two statements have the same functions, but the execution efficiency is much higher.

5.5 Multi-column subqueries

SQL> SELECT * FROM emp WHERE (job, deptno) = (SELECT job, deptno FROM emp WHERE ename = 'Smith ');

5.6 use subquery 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 paging Query

Each row of data in the database has a corresponding row 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> = 6;

Specify the query column and query result sorting. You only need to modify the bottom layer of the subquery.

5.8 create 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, intersect intersection, union all union + 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 merge queries is much higher than that of logical queries such as and or.

5.10 insert data using a subquery

SQL> CREATE TABLE myEmp (empID number (4), name varchar2 (20), sal number (6), job varchar2 (10), dept number (2 )); create an empty table first;

SQL> INSERT INTO myEmp (empID, name, sal, job, dept) SELECT empno, ename, sal, job, deptno FROM emp WHERE deptno = 10; insert the data with the department Number 10 in the emp table to the new myEmp table to Perform Batch Data Query.

5.11 used to query and update table data

SQL> UPDATE emp SET (job, sal, comm) = (SELECT job, sal, comm FROM emp where ename = 'Smith ') WHERE ename = 'Scott ';

The preceding section describes the commonly used SQL query statements for routine collection.