【JaveWeb教程】（22） MySQL数据库开发之多表查询：内毗连、外毗连、子查 ...

数据库开发-MySQL

1. 多表查询

1.1 概述

1.1.1 数据预备

SQL脚本：

1. #建议：创建新的数据库
2. create database db04;
3. use db04;
5. -- 部门表
6. create table tb_dept
7. (
8.     id          int unsigned primary key auto_increment comment '主键ID',
9.     name        varchar(10) not null unique comment '部门名称',
10.     create_time datetime    not null comment '创建时间',
11.     update_time datetime    not null comment '修改时间'
12. ) comment '部门表';
13. -- 部门表测试
14. insert into tb_dept (id, name, create_time, update_time)
15. values (1, '学工部', now(), now()),
16.        (2, '教研部', now(), now()),
17.        (3, '咨询部', now(), now()),
18.        (4, '就业部', now(), now()),
19.        (5, '人事部', now(), now());
21. -- 员工表
22. create table tb_emp
23. (
24.     id          int unsigned primary key auto_increment comment 'ID',
25.     username    varchar(20)      not null unique comment '用户名',
26.     password    varchar(32) default '123456' comment '密码',
27.     name        varchar(10)      not null comment '姓名',
28.     gender      tinyint unsigned not null comment '性别, 说明: 1 男, 2 女',
29.     image       varchar(300) comment '图像',
30.     job         tinyint unsigned comment '职位, 说明: 1 班主任,2 讲师, 3 学工主管, 4 教研主管, 5 咨询师',
31.     entrydate   date comment '入职时间',
32.     dept_id     int unsigned comment '部门ID',
33.     create_time datetime         not null comment '创建时间',
34.     update_time datetime         not null comment '修改时间'
35. ) comment '员工表';
36. -- 员工表测试数据
37. INSERT INTO tb_emp(id, username, password, name, gender, image, job, entrydate,dept_id, create_time, update_time)
38. VALUES
39. (1,'jinyong','123456','金庸',1,'1.jpg',4,'2000-01-01',2,now(),now()),
40. (2,'zhangwuji','123456','张无忌',1,'2.jpg',2,'2015-01-01',2,now(),now()),
41. (3,'yangxiao','123456','杨逍',1,'3.jpg',2,'2008-05-01',2,now(),now()),
42. (4,'weiyixiao','123456','韦一笑',1,'4.jpg',2,'2007-01-01',2,now(),now()),
43. (5,'changyuchun','123456','常遇春',1,'5.jpg',2,'2012-12-05',2,now(),now()),
44. (6,'xiaozhao','123456','小昭',2,'6.jpg',3,'2013-09-05',1,now(),now()),
45. (7,'jixiaofu','123456','纪晓芙',2,'7.jpg',1,'2005-08-01',1,now(),now()),
46. (8,'zhouzhiruo','123456','周芷若',2,'8.jpg',1,'2014-11-09',1,now(),now()),
47. (9,'dingminjun','123456','丁敏君',2,'9.jpg',1,'2011-03-11',1,now(),now()),
48. (10,'zhaomin','123456','赵敏',2,'10.jpg',1,'2013-09-05',1,now(),now()),
49. (11,'luzhangke','123456','鹿杖客',1,'11.jpg',5,'2007-02-01',3,now(),now()),
50. (12,'hebiweng','123456','鹤笔翁',1,'12.jpg',5,'2008-08-18',3,now(),now()),
51. (13,'fangdongbai','123456','方东白',1,'13.jpg',5,'2012-11-01',3,now(),now()),
52. (14,'zhangsanfeng','123456','张三丰',1,'14.jpg',2,'2002-08-01',2,now(),now()),
53. (15,'yulianzhou','123456','俞莲舟',1,'15.jpg',2,'2011-05-01',2,now(),now()),
54. (16,'songyuanqiao','123456','宋远桥',1,'16.jpg',2,'2007-01-01',2,now(),now()),
55. (17,'chenyouliang','123456','陈友谅',1,'17.jpg',NULL,'2015-03-21',NULL,now(),now());

复制代码

1.1.2 先容

多表查询：查询时从多张表中获取所需数据
   单表查询的SQL语句：select 字段列表 from 表名;
  那么要实行多表查询，只需要利用逗号分隔多张表即可，如： select 字段列表 from 表1, 表2;
  查询用户表和部门表中的数据：

1. select * from  tb_emp , tb_dept;

复制代码

此时,我们看到查询效果中包含了大量的效果集，总共85条记录，而这其实就是员工表所有的记录(17行)与部门表所有记录(5行)的所有组合情况，这种现象称之为笛卡尔积。
笛卡尔积：笛卡尔乘积是指在数学中，两个集合(A集合和B集合)的所有组合情况。

   在多表查询时，需要消除无效的笛卡尔积，只保留表关联部门的数据
  

在SQL语句中，如何去除无效的笛卡尔积呢？只需要给多表查询加上毗连查询的条件即可。

1. select * from tb_emp , tb_dept where tb_emp.dept_id = tb_dept.id ;

复制代码

   由于id为17的员工，没有dept_id字段值，所以在多表查询时，根据毗连查询的条件并没有查询到。
  1.1.3 分类

多表查询可以分为：

• 毗连查询
  
  
  
  • 内毗连：相称于查询A、B交集部门数据
    
  
  
  
  
• 外毗连
  
  
  
  • 左外毗连：查询左表所有数据(包括两张表交集部门数据)
    
  • 右外毗连：查询右表所有数据(包括两张表交集部门数据)
    
  
  
• 子查询
  

1.2 内毗连

内毗连查询：查询两表或多表中交集部门数据。
内毗连从语法上可以分为：

• 隐式内毗连
  
• 显式内毗连
  

隐式内毗连语法：

1. select  字段列表   from   表1 , 表2   where  条件 ... ;

复制代码

显式内毗连语法：

1. select  字段列表   from   表1  [ inner ]  join 表2  on  连接条件 ... ;

复制代码

案例：查询员工的姓名及所属的部门名称

• 隐式内毗连实现
  

1. select tb_emp.name , tb_dept.name -- 分别查询两张表中的数据
2. from tb_emp , tb_dept -- 关联两张表
3. where tb_emp.dept_id = tb_dept.id; -- 消除笛卡尔积

复制代码

• 显式内毗连实现
  

1. select tb_emp.name , tb_dept.name
2. from tb_emp inner join tb_dept
3. on tb_emp.dept_id = tb_dept.id;

复制代码

多表查询时给表起别名：

• tableA as 别名1 , tableB as 别名2 ;
  
• tableA 别名1 , tableB 别名2 ;
  

利用了别名的多表查询：

1. select emp.name , dept.name
2. from tb_emp emp inner join tb_dept dept
3. on emp.dept_id = dept.id;

复制代码

  留意事项:
  一旦为表起了别名，就不能再利用表名来指定对应的字段了，此时只能够利用别名来指定字段。
  1.3 外毗连

外毗连分为两种：左外毗连 和 右外毗连。
左外毗连语法结构：

1. select  字段列表   from   表1  left  [ outer ]  join 表2  on  连接条件 ... ;

复制代码

  左外毗连相称于查询表1(左表)的所有数据，当然也包含表1和表2交集部门的数据。
  右外毗连语法结构：

1. select  字段列表   from   表1  right  [ outer ]  join 表2  on  连接条件 ... ;

复制代码

  右外毗连相称于查询表2(右表)的所有数据，当然也包含表1和表2交集部门的数据。
  案例：查询员工表中所有员工的姓名, 和对应的部门名称

1. -- 左外连接：以left join关键字左边的表为主表，查询主表中所有数据，以及和主表匹配的右边表中的数据
2. select emp.name , dept.name
3. from tb_emp AS emp left join tb_dept AS dept
4.      on emp.dept_id = dept.id;

复制代码

案例：查询部门表中所有部门的名称, 和对应的员工名称

1. -- 右外连接
2. select dept.name , emp.name
3. from tb_emp AS emp right join  tb_dept AS dept
4.      on emp.dept_id = dept.id;

复制代码

   留意事项：
  左外毗连和右外毗连是可以相互替换的，只需要调解毗连查询时SQL语句中表的先后顺序就可以了。而我们在一样平常开发利用时，更偏向于左外毗连。
  1.4 子查询

1.4.1 先容

SQL语句中嵌套select语句，称为嵌套查询，又称子查询。

1. SELECT  *  FROM   t1   WHERE  column1 =  ( SELECT  column1  FROM  t2 ... );

复制代码

  子查询外部的语句可以是insert / update / delete / select 的任何一个，最常见的是 select。
  根据子查询效果的差异分为：

• 标量子查询（子查询效果为单个值[一行一列]）
  
• 列子查询（子查询效果为一列，但可以是多行）
  
• 行子查询（子查询效果为一行，但可以是多列）
  
• 表子查询（子查询效果为多行多列[相称于子查询效果是一张表]）
  

子查询可以书写的位置：

• where之后
  
• from之后
  
• select之后
  

1.4.2 标量子查询

子查询返回的效果是单个值(数字、字符串、日期等)，最简单的情势，这种子查询称为标量子查询。
常用的操作符： = <> > >= < <=
案例1：查询"教研部"的所有员工信息
   可以将需求分解为两步：
  

• 查询 “教研部” 部门ID
  
• 根据 “教研部” 部门ID，查询员工信息
  

1. -- 1.查询"教研部"部门ID
2. select id from tb_dept where name = '教研部';    #查询结果：2
3. -- 2.根据"教研部"部门ID, 查询员工信息
4. select * from tb_emp where dept_id = 2;
6. -- 合并出上两条SQL语句
7. select * from tb_emp where dept_id = (select id from tb_dept where name = '教研部');

复制代码

案例2：查询在 “方东白” 入职之后的员工信息
   可以将需求分解为两步：
  

• 查询 方东白 的入职日期
  
• 查询 指定入职日期之后入职的员工信息
  

1. -- 1.查询"方东白"的入职日期
2. select entrydate from tb_emp where name = '方东白';     #查询结果：2012-11-01
3. -- 2.查询指定入职日期之后入职的员工信息
4. select * from tb_emp where entrydate > '2012-11-01';
6. -- 合并以上两条SQL语句
7. select * from tb_emp where entrydate > (select entrydate from tb_emp where name = '方东白');

复制代码

1.4.3 列子查询

子查询返回的效果是一列(可以是多行)，这种子查询称为列子查询。
常用的操作符：
操作符形貌IN在指定的集合范围之内，多选一NOT IN不在指定的集合范围之内 案例：查询"教研部"和"咨询部"的所有员工信息
   分解为以下两步：
  

• 查询 “销售部” 和 “市场部” 的部门ID
  
• 根据部门ID, 查询员工信息
  

1. -- 1.查询"销售部"和"市场部"的部门ID
2. select id from tb_dept where name = '教研部' or name = '咨询部';    #查询结果：3,2
3. -- 2.根据部门ID, 查询员工信息
4. select * from tb_emp where dept_id in (3,2);
6. -- 合并以上两条SQL语句
7. select * from tb_emp where dept_id in (select id from tb_dept where name = '教研部' or name = '咨询部');

复制代码

1.4.4 行子查询

子查询返回的效果是一行(可以是多列)，这种子查询称为行子查询。
常用的操作符：= 、<> 、IN 、NOT IN
案例：查询与"韦一笑"的入职日期及职位都相同的员工信息
   可以拆解为两步进行：
  

• 查询 “韦一笑” 的入职日期 及 职位
  
• 查询与"韦一笑"的入职日期及职位相同的员工信息
  

1. -- 查询"韦一笑"的入职日期 及 职位
2. select entrydate , job from tb_emp where name = '韦一笑';  #查询结果： 2007-01-01 , 2
3. -- 查询与"韦一笑"的入职日期及职位相同的员工信息
4. select * from tb_emp where (entrydate,job) = ('2007-01-01',2);
6. -- 合并以上两条SQL语句
7. select * from tb_emp where (entrydate,job) = (select entrydate , job from tb_emp where name = '韦一笑');

复制代码

1.4.5 表子查询

子查询返回的效果是多行多列，常作为临时表，这种子查询称为表子查询。
案例：查询入职日期是 “2006-01-01” 之后的员工信息 , 及其部门信息
   分解为两步实行：
  

• 查询入职日期是 “2006-01-01” 之后的员工信息
  
• 基于查询到的员工信息，在查询对应的部门信息
  

1. select * from emp where entrydate > '2006-01-01';
3. select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id ;

复制代码

1.5 案例

基于之前设计的多表案例的表结构，我们来完成本日的多表查询案例需求。
预备情况
将资料中预备好的多表查询的数据预备的SQL脚本导入数据库中。

• 分类表：category
  
• 菜品表：dish
  
• 套餐表：setmeal
  
• 套餐菜品关系表：setmeal_dish
  

需求实现

• 查询代价低于 10元 的菜品的名称 、代价 及其 菜品的分类名称
  

1. /*查询技巧：
2.      明确1：查询需要用到哪些字段
3.         菜品名称、菜品价格 、 菜品分类名
4.      明确2：查询的字段分别归属于哪张表
5.         菜品表：[菜品名称、菜品价格]
6.         分类表：[分类名]
7.      明确3：如查多表，建立表与表之间的关联
8.         菜品表.caategory_id = 分类表.id
9.      其他：（其他条件、其他要求）
10.         价格 < 10
11. */
12. select d.name , d.price , c.name
13. from dish AS d , category AS c
14. where d.category_id = c.id
15.       and d.price < 10;

复制代码

• 查询所有代价在 10元(含)到50元(含)之间 且 状态为"起售"的菜品名称、代价及其分类名称 (纵然菜品没有分类 , 也要将菜品查询出来)
  

1. select d.name , d.price, c.name
2. from dish AS d left join category AS c on d.category_id = c.id
3. where d.price between 10 and 50
4.       and d.status = 1;

复制代码

• 查询每个分类下最贵的菜品, 展示出分类的名称、最贵的菜品的代价
  

1. select c.name , max(d.price)
2. from dish AS d , category AS c
3. where d.category_id = c.id
4. group by c.name;

复制代码

• 查询各个分类下 菜品状态为 “起售” , 并且 该分类下菜品总数量大于等于3 的 分类名称
  

1. /*查询技巧：
2.      明确1：查询需要用到哪些字段
3.         分类名称、菜品总数量
4.      明确2：查询用到的字段分别归属于哪张表
5.         分类表：[分类名]
6.         菜品表：[菜品状态]
7.      明确3：如查多表，建立表与表之间的关联
8.         菜品表.caategory_id = 分类表.id
9.      其他：（其他条件、其他要求）
10.         条件：菜品状态 = 1 (1表示起售)
11.         分组：分类名
12.         分组后条件： 总数量 >= 3
13. */
14. select c.name , count(*)
15. from dish AS d , category AS c
16. where d.category_id = c.id
17.       and d.status = 1 -- 起售状态
18. group by c.name  -- 按照分类名分组
19. having count(*)>=3; -- 各组后筛选菜品总数据>=3

复制代码

• 查询出 “商务套餐A” 中包含了哪些菜品 （展示出套餐名称、代价, 包含的菜品名称、代价、份数）
  

1. select s.name, s.price, d.name, d.price, sd.copies
2. from setmeal AS s , setmeal_dish AS sd , dish AS d
3. where s.id = sd.setmeal_id and sd.dish_id = d.id
4.       and s.name='商务套餐A';

复制代码

• 查询出低于菜品均匀代价的菜品信息 (展示出菜品名称、菜品代价)
  

1. -- 1.计算菜品平均价格
2. select avg(price) from dish;    -- 查询结果：37.736842
3. -- 2.查询出低于菜品平均价格的菜品信息
4. select * from dish where price < 37.736842;
6. -- 合并以上两条SQL语句
7. select * from dish where price < (select avg(price) from dish);

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。