文章目录。

• 前言。
• 1. 基本查询回顾。
• 2. 多表查询。
• 3. 自连接。
• 4. 子查询。
• • 4.1 单行查询。
  • 4.2 多行子查询。
  • 4.3 查询多列子。
  • 4.4 在from子句中使用子查询。
  • 4.5 合并查询。
  • • 4.5.1 union。
    • 4.5.2 union all。

前言。

  我们之前解释的mysql表的查询是查询一张表，这在实际发展中远远不够。

1. 基本查询回顾。

  查询工资高于500或MANAGER的员工，同时，他们的首字母是大写J。

select。*。from。EMP。 where。(。sal。>500。or。job。='MANAGER')。and。ename。 like。'J%';

  。员工工资按部门号升级排序。

select。*。from。EMP。 order。by。deptno。,sal。 desc。;

  。降序排序采用年薪。

select。ename。,sal。*。12。+ifnull。(。comm。,0)。as。'年薪'from。EMP。 order。by。年薪。 desc。;

  。显示工资最高的员工的名称和工作岗位。

select。ename。,job。 from。EMP。 where。sal。 =(。select。max。(。sal。)。from。EMP。)。;

  。员工信息显示工资高于平均工资。

select。ename。,sal。 from。EMP。 where。sal。>(。select。avg。(。sal。)。from。EMP。)。;

  。显示各部门的平均工资和最高工资。

select。deptno。,format。(。avg。(。sal。)。,2。)。,max。(。sal。)。from。EMP。 group。by。deptno。;

  。显示部门号及其平均工资低于2000。

select。deptno。,avg。(。sal。)。as。avg_sal。 from。EMP。 group。by。deptno。 having。avg_sal。<2000。;

  。显示各岗位员工总数，平均工资。

select。job。,count。(。*。)。,format。(。avg。(。sal。)。,2。)。from。EMP。 group。by。job。;

2. 多表查询。

  在实际开发中，数据往往来自不同的表，因此需要多表查询。本节我们使用了一个简单的公司管理系统，三张表EMP，DEPT，SALGRADE演示如何进行多表查询。
  。显示员工名称、员工工资和部门名称。因为以上数据来自EMP和DEPT表，所以要联合查询，事实上，我们只需要emp表中的deptno。 = dept表中deptno字段的记录。

select。EMP。.。ename。,EMP。.。sal。,DEPT。.。dname。 from。EMP。,DEPT。 where。EMP。.。deptno。 =DEPT。.。deptno。;

  。显示部门号为10的部门名，员工名称和工资。

select。ename。,sal。,dname。 from。EMP。,DEPT。 where。EMP。.。deptno。=DEPT。.。deptno。 and。DEPT。.。deptno。 =10。;

  。显示每个员工的姓名，工资，工资水平。

select。ename。,sal。,grade。 from。EMP。,SALGRADE。 where。EMP。.。sal。 between。losal。 and。hisal。;

3. 自连接。

  自连接是指在同一张表中连接查询。
  。显示员工FORD上级领导的编号和姓名（mgr是员工领导的编号–empno）
  使用的子查询：

select。empno。,ename。 from。emp。 where。emp。.。empno。=(。select。mgr。 from。emp。 where。ename。='FORD')。;

  使用多表查询（#xfff09自查￰

-- 使用表中的别名。--from emp leader, emp worker，给自己的表起别名，因为要先做笛卡尔积，所以可以先识别别别名。select。leader。.。empno。,leader。.。ename。 from。emp leader。,emp worker。 where。leader。.。empno。 =worker。.。mgr。 and。worker。.。ename。='FORD';

4. 子查询。

  子查询是指嵌入在其他sql语句中的select语句，又称嵌套查询。

4.1 单行查询。

  返回一行记录的子查询。
  。显示SMITH同一部门的员工。

select。*。from。EMP。 WHERE。deptno。 =(。select。deptno。 from。EMP。 where。ename。='smith')。;

4.2 多行子查询。

  返回多行记录的子查询。
  。#xff1b;与10号部门的工作岗位相同的员工名称，工作，工资，部门号，但不包括10个自己的。

select。ename。,job。,sal。,deptno。 from。emp。 where。job。 in。(。select。distinct。job。 from。emp。 	where。deptno。=10。)。and。deptno。<>10。;

  。all关键字；显示工资高于部门30的员工的姓名、工资和部门号。

select。ename。,sal。,deptno。 from。EMP。 where。sal。 >all。(。select。sal。 from。EMP。 where。deptno。=30。)。;

  。any关键字；显示姓名、工资和部门号（，工资高于部门30的任何员工;包括自己部门的员工）

select。ename。,sal。,deptno。 from。EMP。 where。sal。 >any。(。select。sal。 from。EMP。 where。deptno。=30。)。;

4.3 查询多列子。

  单行子查询是指子查询只返回单列，单行数据󿄛多行子查询是指返回单列多行数据󿀌都是单列的，多列子查询是指查询返回多列数据的子查询语句。
  。所有与SMITH部门和职位完全相同的员工，不包括SMITH本人。

mysql。>select。ename。 from。EMP。 where。(。deptno。,job。)。=(。select。deptno。,job。 from。EMP。	where。ename。='SMITH')。and。ename。 <>'SMITH';

4.4 在from子句中使用子查询。

  from子句中出现了子查询句。这里需要数据查询技巧，使用一个子查询作为临时表。
  。显示每个员工的姓名、部门、工资和平均工资高于自己部门的平均工资。

//获得各部门的平均工资，将其视为临时表。select。ename。,deptno。,sal。,format。(。asal。,2。)。from。EMP。,(。select。avg。(。sal。)。asal。,deptno dt。 from。EMP。 group。by。deptno。)。tmp。		where。EMP。.。sal。 >tmp。.。asal。 and。EMP。.。deptno。=tmp。.。dt。;

  。找出每个部门工资最高的人的姓名，工资，部门，最高工资。

select。EMP。.。ename。,EMP。.。sal。,EMP。.。deptno。,ms。 from。EMP。,(。select。max。(。sal。)。ms。,deptno。 from。EMP。 group。by。deptno。)。tmp。		where。EMP。.。deptno。=tmp。.。deptno。 and。EMP。.。sal。=tmp。.。ms。;

  。显示各部门信息（部门名称，编号，地址）以及人员数量。
  方法1：使用多表。

select。DEPT。.。dname。,DEPT。.。deptno。,DEPT。.。loc。,count。(。*。)。'部门人数'from。EMP。,DEPT。	where。EMP。.。deptno。=DEPT。.。deptno。 group。by。DEPT。.。deptno。,DEPT。.。dname。,DEPT。.。loc。;

  方法2：使用子查询。

-- 1. 人员统计EMP表。select。count。(。*。)。,deptno。 from。EMP。 group。by。deptno。;-- 2. 将上表视为临时表。select。DEPT。.。deptno。,dname。,mycnt。,loc。 from。DEPT。,(。select。count。(。*。)。mycnt。,deptno。 from。EMP。 group。by。deptno。)。tmp。		where。DEPT。.。deptno。=tmp。.。deptno。;

4.5 合并查询。

  ￰在实际应用中c;为合并多个select的执行结果，集合操作符可以使用 union，union all。

4.5.1 union。

  该操作符用于获取两个结果集的并集。使用此操作符时，结果集中的重复行将自动去除。
  。找出工资大于2500或职位是MANAGER的人。

select。ename。,sal。,job。 from。EMP。 where。sal。>2500。union。select。ename。,sal。,job。 from。EMP。 where。job。='MANAGER';--删除重复记录。

4.5.2 union all。

  该操作符用于获取两个结果集的并集。使用此操作符时，结果集中的重复行不会被删除。
  。找出工资超过2.5万或职位是MANAGER的人。

select。ename。,sal。,job。 from。EMP。 where。sal。>2500。union。all。select。ename。,sal。,job。 from。EMP。 where。job。='MANAGER';