06-连接查询和子查询

合集 - mysql(11)

1.02-数据库和表的基本操作2023-07-302.03-简单查询2023-07-303.04-条件查询2023-07-304.05-分组汇总查询2023-07-30

5.06-连接查询和子查询2023-07-30

6.07-数据定义2023-07-307.08-存储引擎和事务2023-07-308.09-索引和视图2023-07-309.10-DBA命令和数据库设计的三范式2023-07-3010.11-MySQL数据库的存储过程2023-07-3011.12-MySQL数据库的触发器2023-07-30

收起

06-连接查询和子查询

课程目标

掌握查询中连接查询、子查询。

6.1连接查询－SQL92语法

连接查询：也可以叫跨表查询，需要关联多个表进行查询

1.显示每个员工信息，并显示所属的部门名称，如下图6-1所示：

select ename, dname from emp, dept;

SQL> select ename, dname from emp, dept;

ENAME DNAME

---------- --------------

SMITH ACCOUNTING

ALLEN ACCOUNTING

WARD ACCOUNTING

JONES ACCOUNTING

MARTIN ACCOUNTING

BLAKE ACCOUNTING

CLARK ACCOUNTING

SCOTT ACCOUNTING

KING ACCOUNTING

TURNER ACCOUNTING

ADAMS ACCOUNTING

JAMES ACCOUNTING

FORD ACCOUNTING

MILLER ACCOUNTING

SMITH RESEARCH

ALLEN RESEARCH

WARD RESEARCH

JONES RESEARCH

MARTIN RESEARCH

BLAKE RESEARCH

CLARK RESEARCH

SCOTT RESEARCH

KING RESEARCH

TURNER RESEARCH

ADAMS RESEARCH

JAMES RESEARCH

FORD RESEARCH

MILLER RESEARCH

SMITH SALES

ALLEN SALES

WARD SALES

JONES SALES

MARTIN SALES

BLAKE SALES

CLARK SALES

SCOTT SALES

KING SALES

TURNER SALES

ADAMS SALES

JAMES SALES

FORD SALES

MILLER SALES

SMITH OPERATIONS

ALLEN OPERATIONS

WARD OPERATIONS

JONES OPERATIONS

MARTIN OPERATIONS

BLAKE OPERATIONS

CLARK OPERATIONS

SCOTT OPERATIONS

KING OPERATIONS

TURNER OPERATIONS

ADAMS OPERATIONS

JAMES OPERATIONS

FORD OPERATIONS

MILLER OPERATIONS

已选择56行。

图6-1

以上输出，不正确，输出了56条数据，其实就是两个表记录的乘积，这种情况我们称为：“笛卡儿乘积”，出现错误的原因是：没有指定连接条件

指定连接条件，如下图6-2所示：

select emp.ename, dept.dname from emp, dept where emp.deptno=dept.deptno;

也可以使用别名

select e.ename, d.dname from emp e, dept d where e.deptno=d.deptno;

图6-2

以上结果输出正确，因为加入了正确的连接条件

以上查询也称为 “内连接”，只查询相等的数据（连接条件相等的数据）

2.取得员工和所属的领导的姓名，如下图6-3所示：

select e.ename, m.ename from emp e, emp m where e.mgr=m.empno;

SQL> select * from emp;（普通员工）

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- -------------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-12月-80 800 20

7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30

7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30

7566 JONES MANAGER 7839 02-4月 -81 2975 20

7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30

7698 BLAKE MANAGER 7839 01-5月 -81 2850 30

7782 CLARK MANAGER 7839 09-6月 -81 2450 10

7788 SCOTT ANALYST 7566 19-4月 -87 3000 20

7839 KING PRESIDENT 17-11月-81 5000 10

7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30

7876 ADAMS CLERK 7788 23-5月 -87 1100 20

7900 JAMES CLERK 7698 03-12月-81 950 30

7902 FORD ANALYST 7566 03-12月-81 3000 20

7934 MILLER CLERK 7782 23-1月 -82 1300 10

已选择14行。

SQL> select * from emp;（管理者）

EMPNO ENAME JOB HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- -------------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-12月-80 800 20

7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30

7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30

7566 JONES MANAGER 7839 02-4月 -81 2975 20

7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30

7698 BLAKE MANAGER 7839 01-5月 -81 2850 30

7782 CLARK MANAGER 7839 09-6月 -81 2450 10

7788 SCOTT ANALYST 7566 19-4月 -87 3000 20

7839 KING PRESIDENT 17-11月-81 5000 10

7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30

7876 ADAMS CLERK 7788 23-5月 -87 1100 20

7900 JAMES CLERK 7698 03-12月-81 950 30

7902 FORD ANALYST 7566 03-12月-81 3000 20

7934 MILLER CLERK 7782 23-1月 -82 1300 10

已选择14行。

SQL> select e.ename, m.ename from emp e, emp m where e.mgr=m.empno;

ENAME ENAME

---------- ----------

SMITH FORD

ALLEN BLAKE

WARD BLAKE

JONES KING

MARTIN BLAKE

BLAKE KING

CLARK KING

SCOTT JONES

TURNER BLAKE

ADAMS SCOTT

JAMES BLAKE

FORD JONES

MILLER CLARK

已选择13行。

图6-2

以上称为“自连接”，只有一张表连接，具体的查询方法，把一张表看作两张表即可，如以上示例：第一个表emp e代表了员工表，emp m代表了领导表，相当于员工表和部门表一样

6.2 连接查询－SQL99语法

1.（内连接）显示薪水大于2000的员工信息，并显示所属的部门名称

采用SQL92语法：

select e.ename, e.sal, d.dname from emp e, dept d where e.deptno=d.deptno and e.sal > 2000;

采用SQL99语法：

select e.ename, e.sal, d.dname from emp e join dept d on e.deptno=d.deptno where e.sal>2000;

或

select e.ename, e.sal, d.dname from emp e inner join dept d on e.deptno=d.deptno where e.sal>2000;

在实际中一般不加inner关键字

SQL92语法和SQL99语法的区别：SQL99语法可以做到表的连接和查询条件分离，特别是多个表进行连接的时候，会比SQL92更清晰。

2.（外连接）显示员工信息，并显示所属的部门名称，如果某一个部门没有员工，那么该部门也必须显示出来，如下图6-4所示：

右连接：

select e.ename, e.sal, d.dname from emp e right join dept d on e.deptno=d.deptno;

左连接：

select e.ename, e.sal, d.dname from dept d left join emp e on e.deptno=d.deptno;

以上两个查询效果相同

图6-4

连接分类：

内连接

表1 inner join 表2 on 关联条件

做连接查询的时候一定要写上关联条件

inner 可以省略

外连接

左外连接

表1 left outer join 表2 on 关联条件

做连接查询的时候一定要写上关联条件

outer 可以省略

右外连接

表1 right outer join 表2 on 关联条件

做连接查询的时候一定要写上关联条件

outer 可以省略

左外连接（左连接）和右外连接（右连接）的区别：

左连接以左面的表为准和右边的表比较，和左表相等的不相等都会显示出来，右表符合条件的显示,不符合条件的不显示

右连接恰恰相反，以上左连接和右连接也可以加入outer关键字，但一般不建议这种写法，如：

select e.ename, e.sal, d.dname from emp e right outer join dept d on e.deptno=d.deptno;

select e.ename, e.sal, d.dname from dept d left outer join emp e on e.deptno=d.deptno;

左连接能完成的功能右连接一定可以完成，如下图6-5所示：

图6-5

6.3 子查询

子查询就是嵌套的select语句，可以理解为子查询是一张表

1.在where语句中使用子查询，也就是在where语句中加入select语句

查询员工信息，查询哪些人是管理者，要求显示出其员工编号和员工姓名

实现思路：

(1)首先取得管理者的编号，去除重复的

select distinct mgr from emp where mgr is not null;

distinct 去除重复行

(2)查询员工编号包含管理者编号的，如下图6-6所示：

select empno, ename from emp where empno in (select distinct mgr from emp where mgr is not null);

图6-6

(3)查询哪些人的薪水高于员工的平均薪水，需要显示员工编号，员工姓名，薪水

实现思路

1)取得平均薪水

select avg(sal) from emp;

2)取得大于平均薪水的员工，如下图6-7所示：

select empno, ename, sal from emp where sal > (select avg(sal) from emp);

图6-7

2.在from语句中使用子查询，可以将该子查询看做一张表

(1)查询员工信息，查询哪些人是管理者，要求显示出其员工编号和员工姓名

首先取得管理者的编号，去除重复的

select distinct mgr from emp where mgr is not null;

将以上查询作为一张表，放到from语句的后面，如下图6-8所示：

使用92语法：

select e.empno, e.ename from emp e, (select distinct mgr from emp where mgr is not null) m where e.empno=m.mgr;

使用99语法：

select e.empno, e.ename from emp e join (select distinct mgr from emp where mgr is not null) m on e.empno=m.mgr;

图6-8

(2)查询各个部门的平均薪水所属等级，需要显示部门编号，平均薪水，等级编号

实现思路

1)首先取得各个部门的平均薪水，如下图6-9所示：

select deptno, avg(sal) avg_sal from emp group by deptno;

图6-9

2)将部门的平均薪水作为一张表与薪水等级表建立连接，取得等级，如下图6-10所示：

select deptno,avg(sal) avg_sal from emp group by deptno;

select * from salgrade;

select a.deptno,a.avg_sal,g.grade from (select deptno,avg(sal) avg_sal from emp group by deptno ) a join salgrade g on a.avg_sal between g.losal and hisal;

平均薪水表

薪水等级表

图6-10

3.在select语句中使用子查询

查询员工信息，并显示出员工所属的部门名称

第一种做法，将员工表和部门表连接，如下图6-11所示：

select e.ename, d.dname from emp e, dept d where e.deptno=d.deptno;

第二种做法，在select语句中再次嵌套select语句完成部门名称的查询select e.ename, (select d.dname from dept d where e.deptno=d.deptno) as dname from emp e;

图6-11

6.4 union

union可以合并集合（相加）

1.查询job包含MANAGER和包含SALESMAN的员工，如下图6-12所示：

select * from emp where job in('MANAGER', 'SALESMAN');

图6-12

2.采用union来合并，如下图6-13所示：

select * from emp where job='MANAGER'

union

select * from emp where job='SALESMAN';

图6-13

合并结果集的时候，需要查询字段对应个数相同。在Oracle中更严格，不但要求个数相同，而且还要求类型对应相同。

6.5 limit的使用

MySQL提供了limit ，主要用于提取前几条或者中间某几行数据

select * from table limit m,n

其中m是指记录开始的index，从0开始，表示第一条记录

n是指从第m+1条开始，取n条。

select * from tablename limit 2,4

即取出第3条至第6条，4条记录

1.取得前5条数据，如下图6-14和6-15所示：

select * from emp limit 5;

图6-14

图6-15

2.从第二条开始取两条数据，如下图6-16所示：

select * from emp limit 1,2;

图6-16

3.取得薪水最高的前5名，如下图6-17所示：

select * from emp e order by e.sal desc limit 5;

图6-17

6.6 本章小结

本章主要阐述了SQL语句中的边接查询和子查询。