What are the common SQL query statements?

Common SQL query statements include: 1. View table structure [SQL>DESC emp;]; 2. Query all columns [SQL>SELECT * FROM emp]; 3. Query specified columns [SQL>SELECT empmo ,]; 4. Query the specified row [SQL>SELECT * FROM].

Common sql query statements are:

1. Simple query statements

1. View the table structure

SQL>DESC emp;

2. Query all columns

SQL>SELECT * FROM emp;

3. Query the specified column

SQL>SELECT empmo, ename, mgr FROM emp;

SQL>SELECT DISTINCT mgr FROM emp; Only display items with different results

4. Query the specified row

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

5 . Using the 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; if there is 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>'01-1月-82';

6. Use the like operator (%, _)

% represents one or more characters, _ represents one character, [charlist] represents any single character in the character column, [^charlist] or [!charlist] is not a character Any single character in the column.

SQL>SELECT * FROM emp WHERE ename like 'S__T%';

7. Use In

SQL>SELECT * FROM emp WHERE job IN ('CLERK','ANALYST');

# in the where condition. 8. Query the statement that the field content is empty/non-empty

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 the value of the field

SQL>SELECT * FROM emp ORDER BY deptno, sal DESC;

(in ascending order by department , and in descending order by salary)

11. Use case ... when ... then ... end to process the query results

SQL>SELECT CASE a WHEN "original_a" THEN "New name Aa" WHEN "original_b" THEN "New name Bb" END AS XXX;

Select the a field in the table and name it XXX. When the content of a is original_a, The content is displayed as "New Name Aa".

12. Format date data

SQL>SELECT DATE_FORMAT(start_time,"%Y-%m-%d") as "时间";

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 to group statistics on query results) and having clause (used to limit 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 grouping:

a. Grouping functions can only appear in the selection list, having, order by clause (not in where)

b. If the select statement contains group by, having, order by at the same time, then their order is group by, having, order by.

c. If there are columns, expressions and grouping functions in the selected column, these columns and expressions must appear in the group by clause, otherwise an error will occur. That is: the column name in the SELECT clause must be a grouping column or column function

Using group by is not a prerequisite for using 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;
Select a.*, b.x, c.y from a
　　left outer join (Select * from tablex where condition1)b
　　on a.id=b.id
　　left outer join (Select * from tabley where condition2)c
　　on a.id=c.id
where condition3;

Use the selected data as a new table to perform a left join query; from: https://q.cnblogs.com/q /67530/

4. Self-join (referring 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. Subquery (embedded in other sql statements) select statement, 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 names of people in the same department as smith in the query table. Because the return result is only one row, use "=" to connect the subquery statement

5.2 Multi-row subquery

SQL>SELECT ename,job,sal,deptno from emp WHERE job IN (SELECT DISTINCT job FROM emp WHERE deptno=10);

Query table for employees with the same job as department number 10 Name, job, salary, department number. Because there are multiple rows in the returned result, "IN" is used to connect the subquery statements.

The difference between in and exists: The subquery after exists() is called a correlated subquery, and it does not return the value of the list. It just returns a true or false result. The operation method is to run the main query once, and then query the corresponding results in the subquery. If it is true, it will be output, otherwise it will not be output. Then query in the subquery based on each row in the main query. The subquery after in() returns the result set. In other words, the execution order is different from exists(). The subquery first generates a result set, and then the main query goes to the result set to find a list of fields that meet the requirements. Output that meets the requirements, otherwise no 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,deptno FROM emp WHERE sal> (SELECT MAX(sal) FROM emp WHERE deptno=30);

to query the name, salary and department number of employees whose salary is higher than that of all employees with department number 30. The above two statements are functionally the same, but in terms of execution efficiency, the function will be much higher.

5.4 Use ANY

SQL>SELECT ename,sal,deptno FROM emp WHERE sal> ANY (SELECT sal FROM emp WHERE deptno=30);或SQL>SELECT ename,sal,deptno FROM emp WHERE sal> (SELECT MIN(sal) FROM emp WHERE deptno=30);

to query the name and salary of employees whose salary is higher than that of any employee with department number 30 (as long as the salary is higher than that of a certain employee) and department number. The above two statements are functionally the same, but in terms of execution efficiency, the function will be much higher.

5.5 Multi-column subquery

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 Paging query

Each row of data in the database Each 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;

To specify query columns, sort query results, etc., you only need to modify the innermost 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 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';

合并查询的执行效率远高于and,or等逻辑查询。

 5.10 使用子查询插入数据

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

先建一张空表；

SQL>INSERT INTO myEmp(empID, name, sal, job, dept) SELECT empno, ename, sal, job, deptno FROM emp WHERE deptno=10;

再将emp表中部门号为10的数据插入到新表myEmp中，实现数据的批量查询。

5.11 使用了查询更新表中的数据

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

相关免费学习推荐：SQL视频教程

The above is the detailed content of What are the common SQL query statements?. For more information, please follow other related articles on the PHP Chinese website!