SQL-查询的分类及使用

简单查询

• 检索所有的列

SELECT * FROM EMP;

• 检索指定的列

SELECT ENAME FROM EMP;

• 带有表达式的SELECT子句

SELECT SAL * (1 + 0.1), SAL FROM EMP;

• 为列指定别名

SELECT EMPNO AS "员工编号", ENAME AS "员工姓名", JOB AS "职务" FROM EMP;

• 显示不重复记录

SELECT DISTINCT JOB FROM EMP;

筛选查询

• 比较筛选

–查询emp表中工资大于1500的数据记录

SELECT * FROM EMP WHERE SAL > 1500;

–查询emp表中工资为800、1600、1250的雇员数据

//MySQL数据库ANY括号内需要写查询语句
SELECT * FROM EMP WHERE SAL = ANY(800, 1600, 1250);

–查询emp表中工资不是800、1600、1250的雇员数据

//MySQL数据库ALL括号内需要写查询语句
SELECT * FROM EMP WHERE SAL <> ALL(800, 1600, 1250);

• 使用特殊关键字筛选

–查询名字以S开头的员工信息-LIKE关键字

SELECT * FROM EMP WHERE ENAME LIKE 'S%';

–查询职务为PRESIDENT、MANAGER和ANALYST中任意一种的员工信息-IN关键字

SELECT * FROM EMP WHERE JOB IN ('PRESIDENT', 'MANAGER', 'ANALYST');

–查询工资为2000-3000之间的员工信息-BETWEEN…AND关键字

SELECT * FROM EMP WHERE SAL BETWEEN 2000 AND 3000;

–查询奖金为空值的员工信息-IS NULL关键字

SELECT * FROM EMP WHERE COMM IS NULL;

• 逻辑筛选

–查询工资为2000-3000之间的员工信息-AND运算符

SELECT *
  FROM EMP
 WHERE SAL > 2000
   AND SAL < 3000;

–查询工资小于2000或者大于3000的员工信息-OR运算符

SELECT *
  FROM EMP
 WHERE SAL < 2000
    OR SAL > 3000;

分组查询

–查询有哪些部门编号

SELECT DEPTNO FROM EMP GROUP BY DEPTNO;

–计算每个部门的平均工资

SELECT DEPTNO AS "部门编号", AVG(SAL) AS "平均工资"
  FROM EMP
 GROUP BY DEPTNO;

–查询平均工资大于2000的部门编号

SELECT DEPTNO AS "部门编号", AVG(SAL) AS "平均工资"
  FROM EMP
 GROUP BY DEPTNO
HAVING AVG(SAL) > 2000;

排序查询

–按照部门编号、员工编号对emp表数据进行排序

SELECT * FROM EMP ORDER BY DEPTNO, EMPNO;

–按照部门编号升序、员工编号降序对emp表数据进行排序

SELECT * FROM EMP ORDER BY DEPTNO, EMPNO DESC;

多表关联查询

• 表别名

–查询员工编号 员工名称 所在部门名称

SELECT E.EMPNO AS "员工编号", E.ENAME AS "员工姓名", D.DNAME "部门名称"
  FROM EMP E, DEPT D
 WHERE E.DEPTNO = D.DEPTNO;

• 内连接

–查询员工编号 员工名称 所在部门名称

SELECT E.EMPNO AS "员工编号", E.ENAME AS "员工姓名", D.DNAME "部门名称"
  FROM EMP E
 INNER JOIN DEPT D
    ON E.DEPTNO = D.DEPTNO;

• 外连接

–左外连接：查询员工编号 员工名称 所在部门名称
–左外连接会显示左边表的不满足条件数据

SELECT E.EMPNO AS "雇员编号", E.ENAME AS "雇员名称", D.DNAME "部门名称"
  FROM EMP E
  LEFT JOIN DEPT D
    ON E.DEPTNO = D.DEPTNO;

–右外连接：查询员工编号 员工名称 所在部门名称
–右外连接会显示右边表的不满足条件数据

SELECT E.EMPNO AS "雇员编号", E.ENAME AS "雇员名称", D.DNAME "部门名称"
  FROM EMP E
  RIGHT JOIN DEPT D
    ON E.DEPTNO = D.DEPTNO;

–完全外连接：查询员工编号 员工名称 所在部门名称
–执行完全外连接实际上是执行一次左外连接一次右外连接再取出重复行

SELECT E.EMPNO AS "雇员编号", E.ENAME AS "雇员名称", D.DNAME "部门名称"
  FROM EMP E
  FULL JOIN DEPT D
    ON E.DEPTNO = D.DEPTNO;

• 自然连接

–查询emp表中sal大于2000的记录，实现emp表与dept表的自然链接
–自然连接不能指定表的别名

SELECT * FROM EMP NATURAL JOIN DEPT WHERE SAL > 2000;

• 自连接

–查询管理者所管理的下属员工信息

SELECT E2.ENAME AS "上层管理者", E1.ENAME AS "下属员工"
  FROM EMP E1
  LEFT JOIN EMP E2
    ON E1.MGR = E2.EMPNO
 ORDER BY E1.MGR;

• 交叉连接

–交叉连接emp表和dept表，交叉连接会产生笛卡儿积

SELECT * FROM DEPT CROSS JOIN EMP;

子查询

–在emp表中查询部门名称(dname)为’RESEARCH’的员工信息

SELECT *
  FROM EMP
 WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DNAME = 'RESEARCH')

-------可改为如下方式-------

SELECT *
  FROM EMP
  JOIN DEPT
    ON EMP.DEPTNO = DEPT.DEPTNO
 WHERE DEPT.DNAME = 'RESEARCH'

• 单行子查询(可以使用单行比较运算符)

–在emp表中,查询出既不是最高工资,也不是最低工资的员工信息

SELECT *
  FROM EMP
 WHERE SAL < (SELECT MAX(SAL) FROM EMP)
   AND SAL > (SELECT MIN(SAL) FROM EMP)

• 多行子查询

–使用IN运算符
–在emp表中,查询不是销售部门(SALES)的员工信息

SELECT *
  FROM EMP
 WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE DNAME <> 'SALES')

–使用ANY运算符
–在emp表中,查询工资大于部门编号为10的任意一个员工工资即可的其他
–部门的员工信息

SELECT *
  FROM EMP
 WHERE SAL > ANY (SELECT SAL FROM EMP WHERE DEPTNO = 10)
   AND DEPTNO <> 10
 ORDER BY EMPNO

-------可改为如下方式-------

SELECT *
  FROM EMP
 WHERE SAL > (SELECT MIN(SAL) FROM EMP WHERE DEPTNO = 10)
   AND DEPTNO <> 10
 ORDER BY EMPNO

–使用ALL运算符
–在emp表中,查询工资大于部门编号为30的所有员工工资的员工信息

SELECT *
  FROM EMP
 WHERE SAL > ALL (SELECT SAL FROM EMP WHERE DEPTNO = 30)
 ORDER BY EMPNO

-------可改为如下方式-------

SELECT *
  FROM EMP
 WHERE SAL > (SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 30)
 ORDER BY EMPNO

• 关联子查询

–在emp表中,使用’关联子查询’检索工资大于同职位的平均工资的员工信息

SELECT *
  FROM EMP E
 WHERE E.SAL > (SELECT AVG(SAL) FROM EMP WHERE JOB = E.JOB)
 ORDER BY JOB