【SQL】SQL多表查询

🎄概念

• 一般我们说的多表查询都涉及外键和父子表之间的关系。
• 比如一对多:一般前面指的是父表后面指的是子表。

⭐分类

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

⭐一对多

📢案例：部门与员工的关系

📢关系：一个部门对应多个员工，一个员工对应一个部门

📢实现：在多的一方建立外键，指向一的一方的主键(例如上一章节的SQL约束示例)

⭐多对多

📢案例：学生与课程的关系

📢关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择

📢实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键，使用中间表来维护二者之间的关联关系

1. 创建学生表

• id为学生表的主键。并插入三条数据。

create table student(id int auto_increment primary key comment '主键 学生ID',name varchar(10) comment '姓名',no varchar(10) comment '学号')
comment '学生表';insert into student values (null, '黛绮丝', '2000100101'),(null, '谢逊',
'2000100102'),(null, '殷天正', '2000100103'),(null, '韦一笑', '2000100104');

   2.创建课程表

create table course(id int auto_increment primary key comment '课程表主键ID',name varchar(10) comment '课程名称'
)comment '课程表';insert into course values (null, 'Java'), (null, 'C++'), (null , 'MySQL') ,(null, 'Hadoop');

 3.创建关联表

• 这里创建是会失败的，是因为我们关联的是学生表中的no学号字段和课程表中的name名称字段，但这两个字段并不是主键以及唯一约束。
• 外键所关联的字段必须要具有唯一性。

create table student_course(id int  auto_increment primary key comment '主键',studentno varchar(10) not null comment '学生表学号',courseno  varchar(10) not null comment '课程表课程名',constraint fk_studentNo foreign key (studentno) references student (no),constraint fk_courseno foreign key (courseno) references course (name)
)comment '学生课程中间表';

 4. 增加唯一约束，保证外键创建成功。

alter table student add constraint unique_No unique (no);
alter table course add constraint unique_name unique (name);

insert into student_course values (null,'2000100101','C++'),(null,'2000100102','Hadoop'),(null,'2000100103','C++'),(null,'2000100104','Java')

5.关联图

• 从显示图中可以看出，中间表的studentno对应student的no字段。courserno对应course表的name字段。

⭐一对一

📢案例：用户 与 用户详情的关系

📢关系: 一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率。

📢 实现: 在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的 (UNIQUE)

create table user_base(id int auto_increment primary key comment '用户id主键',name varchar(10) comment '用户姓名',age int comment '年龄',gender char(1) comment '性别1 男 2 女',phone char(11) comment '手机号'
)comment '用户基本信息表'create table user_base_all(id int auto_increment primary key comment '主键ID',degree varchar(20) comment '学历',major varchar(50) comment '专业',primaryschool varchar(50) comment '小学',middleschool varchar(50) comment '中学',university varchar(50) comment '大学',userid int unique comment '用户表id',constraint fk_userud foreign key (userid) references user_base (id)
)comment '用户详情表'insert into user_base(id, name, age, gender, phone) values
(null,'黄渤',45,'1','18800001111'),
(null,'冰冰',35,'2','18800002222'),
(null,'码云',55,'1','18800008888'),
(null,'李彦宏',50,'1','18800009999');insert into user_base_all(id, degree, major, primaryschool, middleschool,university, userid) values
(null,'本科','舞蹈','静安区第一小学','静安区第一中学','北京舞蹈学院',1),
(null,'硕士','表演','朝阳区第一小学','朝阳区第一中学','北京电影学院',2),
(null,'本科','英语','杭州市第一小学','杭州市第一中学','杭州师范大学',3),
(null,'本科','应用数学','阳泉第一小学','阳泉区第一中学','清华大学',4);

📢关联图

🎄多表查询引入

⭐数据准备

📢创建部门表并插入数据

create table department(id int auto_increment primary key comment '主键 自增ID',name varchar(20) not null comment '部门名称'
)comment '部门表';insert into department values (null,'研发部'),(null,'市场部'),(null,'财务部')
,(null,'销售部')
,(null,'总经办')
,(null,'人事部')

📢创建员工表并插入数据

create table employee(id int auto_increment primary key comment '主键 自增ID',name varchar(50) not null comment '姓名',age int comment '年龄',job varchar(20) comment '职位',salary int comment '薪资',entrydate date comment '入职时间',managerid int comment '直属领导ID',dept_id int comment '部门ID',constraint fk_employee_deptid foreign key (dept_id) references department (id)
)comment '员工表';insert into employee values (1, '金庸', 66, '总裁',20000, '2000-01-01', null,5),
(2, '张无忌', 20, '项目经理',12500, '2005-12-05', 1,1),(3, '杨逍', 33, '开发', 8400,'2000-11-03', 2,1),
(4, '韦一笑', 48, '开发',11000, '2002-02-05', 2,1),
(5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1),
(6, '小昭', 19, '程序员鼓励师',6600, '2004-10-12', 2,1),(7, '灭绝', 60, '财务总监',8500, '2002-09-12', 1,3),
(8, '周芷若', 19, '会计',48000, '2006-06-02', 7,3),
(9, '丁敏君', 23, '出纳',5250, '2009-05-13', 7,3),
(10, '赵敏', 20, '市场部总监',12500, '2004-10-12', 1,2),(11, '鹿杖客', 56, '职员',3750, '2006-10-03', 10,2),
(12, '鹤笔翁', 19, '职员',3750, '2007-05-09', 10,2),
(13, '方东白', 19, '职员',5500, '2009-02-12', 10,2),
(14, '张三丰', 88, '销售总监',14000, '2004-10-12', 1,4),(15, '俞莲舟', 38, '销售',4600, '2004-10-12', 14,4),
(16, '宋远桥', 40, '销售',4600, '2004-10-12', 14,4),
(17, '陈友谅', 42, null,2000, '2011-10-12', 1,null);

⭐笛卡尔积

📢如果我们针对上面的员工和部门表进行多表查询时，没有给顶足够多的条件，就会产生笛卡尔积

📢比如部门表有6条记录，而员工表有12条记录，那么我们利用这条SQL查出来的将是17*6=102条记录。这显然是不对的了。

📢显而易见我们在多表查询中需要消除掉无效的笛卡尔积

select * from employee,department;

⭐消除笛卡尔积

select * from employee,department where employee.dept_id = department.id;

🎄多表查询

⭐分类

📢连接查询

• 内连接：相当于查询A、B交集部分数据
• 外连接：

1. 左外连接：查询左表所有数据，以及两张表交集部分数据
2. 右外连接：查询右表所有数据，以及两张表交集部分数据

• 自连接：当前表与自身的连接查询，自连接必须使用表别名

📢子查询

🎄内连接

• 📢内连接查询的是两张表交集的部分
• 📢内连接分为隐式内连接和显式内连接
• 📢二者的查询结果是一致的。

⭐隐式内连接

📢语法

SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ;

📢案例:

A. 查询每一个员工的姓名 , 及关联的部门的名称 ( 隐式内连接实现 )

select employee.name, department.name from employee,department
where employee.dept_id = department.id;

⭐显式内连接

📢语法

• inner关键字可以省略

SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;

📢案例:

A. 查询每一个员工的姓名 , 及关联的部门的名称 ( 显式内连接实现 )

select employee.name, department.name from employee inner join department
on employee.dept_id = department.id;

🎄外连接

📢外连接分为两种,分别是左外连接和右外连接。

📢对于外连接，想查的表在右边就是右连接,想查的表在左边就是左连接。

📢对于左右连接是可以调换顺序的，以开发者的习惯为主，如果习惯把要查的表放在左表那就只需要记住左外连接一种即可。

⭐左外连接

• 查询左表的所有数据以及和右表交集的数据。
• outer可以省略，直接使用left join

📢语法

select 字段列表 FROM 左表 left [outer] join 右表 on 条件...

📢示例

A：查询 emp 表的所有数据 , 和对应的部门信息

由于需求中提到，要查询 emp 的所有数据，所以是不能内连接查询的，需要考虑使用外连接查询。

select employee.*, department.name from employee left outer join departmenton employee.dept_id = department.id

⭐右外连接

• 查询右表的所有数据以及和左表交集的数据。

📢语法

select 字段列表 from 左表 right [outer] join 右表 on 条件...

📢示例

A： 查询dept表的所有数据, 和对应的员工信息(右外连接)

select department.*,employee.* from employee right outer join departmenton employee.dept_id = department.id

⭐自连接

• 顾名思义，就是自己连接自己，也就是把一张表连接查询多次。
• 对于自连接查询，可以是内连接查询，也可以是外连接查询

📢语法

• 必须起别名
• 否则不清楚所指定的条件、返回的字段，到底
  是哪一张表的字段。

select 字段列表 from 表A 别名a JOIN 表A 别名b on 条件...

📢案例

A: 查询员工 及其 所属领导的名字

select a.name '员工',b.name '领导' from employee as a, employee as b where a.managerid = b.id;

B:查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来

• 对于案例A 查询并没有查到没有领导的员工,比如总裁的信息。

select a.name '员工',b.name '领导' from employee a left join  employee b on a.managerid = b.id;