MySQL-----多表查询（一）

目录
一.多表关系：
1.1 一对多(多对一)：
1.2 多对多:
1.3 一对一:
二.多表查询概述：
三.毗连查询：
3.1内毗连：
3.2外毗连：
3.3自毗连查询：
3.4团结查询：

---

一.多表关系：

项目开发中，在举行数据库表结构筹划时，会根据业务需求及业务模块之间的关系，分析并筹划表结构，由于业务之间相互关联，以是各个表结构之间也存在着各种接洽，基本上分为三种：

• 一对多(多对一)
  
• 多对多
  
• 一对一
  

1.1 一对多(多对一)：

• 案例: 部分 与 员工的关系
  
• 关系: 一个部分对应多个员工，一个员工对应一个部分
  
• 实现: 在多的一方创建外键，指向一的一方的主键
  

大抵关系图（展示部分）：

对应SQL脚本：

1. create table dept(
2. id int auto_increment comment 'ID' primary key,
3. name varchar(50) not null comment '部门名称'
4. )comment '部门表';
5. INSERT INTO dept (id, name) VALUES (1, '研发部'), (2, '市场部'),(3, '财务部'), (4,
6. '销售部'), (5, '总经办');
8. create table emp(
9. id int auto_increment comment 'ID' primary key,
10. name varchar(50) not null comment '姓名',
11. age int comment '年龄',
12. job varchar(20) comment '职位',
13. salary int comment '薪资',
14. entrydate date comment '入职时间',
15. managerid int comment '直属领导ID',
16. dept_id int comment '部门ID'
17. )comment '员工表';
19. INSERT INTO emp (id, name, age, job,salary, entrydate, managerid, dept_id)
20. VALUES
21. (1, '金庸', 66, '总裁',20000, '2000-01-01', null,5),(2, '张无忌', 20,
22. '项目经理',12500, '2005-12-05', 1,1),
23. (3, '杨逍', 33, '开发', 8400,'2000-11-03', 2,1),(4, '韦一笑', 48, '开
24. 发',11000, '2002-02-05', 2,1),
25. (5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1),(6, '小昭', 19, '程
26. 序员鼓励师',6600, '2004-10-12', 2,1);
27. -- 添加外键约束
28. alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references
29. dept(id);

复制代码

1.2 多对多:

• 案例: 学生 与 课程的关系
  
• 关系: 一个学生可以选修多门课程，一门课程也可以供多个学生选择
  
• 实现: 创建第三张中间表，中间表至少包含两个外键，分别关联两方主键
  

大抵关系图：

对应SQL脚本：

1. -- 建立学生表
2. reate table student(
3. id int auto_increment primary key comment '主键ID',
4. name varchar(10) comment '姓名',
5. no varchar(10) comment '学号'
6. ) comment '学生表';
8. insert into student values (null, '黛绮丝', '2000100101'),(null, '谢逊',
9. '2000100102'),(null, '殷天正', '2000100103'),(null, '韦一笑', '2000100104');
10. -- 建立课程表
11. create table course(
12. id int auto_increment primary key comment '主键ID',
13. name varchar(10) comment '课程名称'
14. ) comment '课程表';
16. insert into course values (null, 'Java'), (null, 'PHP'), (null , 'MySQL') ,
17. (null, 'Hadoop');
19. -- 建立二者关系表
20. create table student_course(
21. id int auto_increment comment '主键' primary key,
22. studentid int not null comment '学生ID',
23. courseid int not null comment '课程ID',
24. constraint fk_courseid foreign key (courseid) references course (id), -- 加入外键约束
25. constraint fk_studentid foreign key (studentid) references student (id)
26. )comment '学生课程中间表';
27. insert into student_course values (null,1,1),(null,1,2),(null,1,3),(null,2,2),
28. (null,2,3),(null,3,4)

复制代码

1.3 一对一:

• 案例: 用户 与 用户详情的关系
  
• 关系: 一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另 一张表中，以提升操作效率
  
• 实现: 在恣意一方参加外键，关联别的一方的主键，而且设置外键为唯一(UNIQUE)
  

大抵关系图：

对应SQL脚本：

1. -- 创建用户表
2. create table tb_user(
3. id int auto_increment primary key comment '主键ID',
4. name varchar(10) comment '姓名',
5. age int comment '年龄',
6. gender char(1) comment '1: 男 , 2: 女',
7. phone char(11) comment '手机号'
8. ) comment '用户基本信息表';
9. -- 创建用户受教育信息表
10. create table tb_user_edu(
11. id int auto_increment primary key comment '主键ID',
12. degree varchar(20) comment '学历',
13. major varchar(50) comment '专业',
14. primaryschool varchar(50) comment '小学',
15. middleschool varchar(50) comment '中学',
16. university varchar(50) comment '大学',
17. userid int unique comment '用户ID',
18. constraint fk_userid foreign key (userid) references tb_user(id)
19. ) comment '用户教育信息表';
20. -- 向表中插入数据
21. insert into tb_user(id, name, age, gender, phone) values
22. (null,'黄渤',45,'1','18800001111'),
23. (null,'冰冰',35,'2','18800002222'),
24. (null,'码云',55,'1','18800008888'),
25. (null,'李彦宏',50,'1','18800009999');
27. insert into tb_user_edu(id, degree, major, primaryschool, middleschool,
28. university, userid) values
29. (null,'本科','舞蹈','静安区第一小学','静安区第一中学','北京舞蹈学院',1),
30. (null,'硕士','表演','朝阳区第一小学','朝阳区第一中学','北京电影学院',2),
31. (null,'本科','英语','杭州市第一小学','杭州市第一中学','杭州师范大学',3),
32. (null,'本科','应用数学','阳泉第一小学','阳泉区第一中学','清华大学',4);

复制代码

二.多表查询概述：

我们之前在讲解SQL语句的时候，讲解了DQL语句，也就是数据查询语句，但是之前讲解的查询都是单表查询，而本章节我们要学习的则是多表查询操作,多表查询就是指从多张表中查询数据。其中，多表查询又分为毗连查询与子查询
在查询之前，我们先导入数据（创建表以及表之间的关系）：

1. -- 创建部门表
2. create table dept(
3. id int auto_increment comment 'ID' primary key,
4. name varchar(50) not null comment '部门名称'
5. )comment '部门表';
7. INSERT INTO dept (id, name) VALUES (1, '研发部'), (2, '市场部'),(3, '财务部'), (4,
8. '销售部'), (5, '总经办'), (6, '人事部');
9. -- 创建员工表
10. create table emp(
11. id int auto_increment comment 'ID' primary key,
12. name varchar(50) not null comment '姓名',
13. age int comment '年龄',
14. job varchar(20) comment '职位',
15. salary int comment '薪资',
16. entrydate date comment '入职时间',
17. managerid int comment '直属领导ID',
18. dept_id int comment '部门ID'
19. )comment '员工表';
20. -- 设置外键约束
21. alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references
22. dept(id);
23. -- 插入数据
24. INSERT INTO emp (id, name, age, job,salary, entrydate, managerid, dept_id)
25. VALUES
26. (1, '金庸', 66, '总裁',20000, '2000-01-01', null,5),
27. (2, '张无忌', 20, '项目经理',12500, '2005-12-05', 1,1),
28. (3, '杨逍', 33, '开发', 8400,'2000-11-03', 2,1),
29. (4, '韦一笑', 48, '开发',11000, '2002-02-05', 2,1),
30. (5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1),
31. (6, '小昭', 19, '程序员鼓励师',6600, '2004-10-12', 2,1),
32. (7, '灭绝', 60, '财务总监',8500, '2002-09-12', 1,3),
33. (8, '周芷若', 19, '会计',48000, '2006-06-02', 7,3),
34. (9, '丁敏君', 23, '出纳',5250, '2009-05-13', 7,3),
35. (10, '赵敏', 20, '市场部总监',12500, '2004-10-12', 1,2),
36. (11, '鹿杖客', 56, '职员',3750, '2006-10-03', 10,2),
37. (12, '鹤笔翁', 19, '职员',3750, '2007-05-09', 10,2),
38. (13, '方东白', 19, '职员',5500, '2009-02-12', 10,2),
39. (14, '张三丰', 88, '销售总监',14000, '2004-10-12', 1,4),
40. (15, '俞莲舟', 38, '销售',4600, '2004-10-12', 14,4),
41. (16, '宋远桥', 40, '销售',4600, '2004-10-12', 14,4),
42. (17, '陈友谅', 42, null,2000, '2011-10-12', 1,null);

复制代码

原来查询单表数据，执行的SQL情势为：select * from emp;
在多表查询中，我们就只需要利用逗号分隔多张表即可，如： select * from emp , dept
查询结果（结果很长，展示部分）：

为什么会出现这种结果呢？这里要引入笛卡尔积的概念了：