3.多表关系

4-1 多表关系介绍

-- 为什么有多表关系(把数据存在一张表的弊端)
	1. 组织结构不清晰
	2. 浪费硬盘空间
	3. 扩展性差
-- 什么是多表关系
	把不同的数据存放在不同的表中时，需要将这些表通过某种方式建立关联，关联建好之后，这些表之间就存在关系了，在MySQL中表与表之间的关系是通过外键来建立的，表与表之间的关系共有三种，分别是一对一、一对多和多对多。
-- 如何使用多表关系
	请大家看后续的笔记哦😊

4-2 如何确定表关系

-- 如何确定表和表之间的关系
	换位思考
-- 案例
 	图书 作者 出版社 作者信息 ？ 它们之间的关系是什么
	图书和出版社之间的关系：
		站在图书的角度，一本书只能由授权一个出版社出版，
		一个出版社可以出版多本书籍，
		所以图书和出版社是一对多的关系
	作者和作者信息之间的关系：
		站在作者的角度，一个作者只能有一个作者信息，
		一个作者信息只能对应一个作者，
		作者和作者信息是一对一的关系
	图书和作者之间的关系：
		站在图书的角度，一本书的作者可以有多个也可以有一个，
		站在作者的角度，一个作者可以写多本书
		所以图书和作者之间的关系就是多对多的关系
    图书和作者信息：没有直接关系，需要通过作者对这两个表进行关联

4-3 创建多表关系 - 一对一关系

-- foreign key
	在MySQL中表与表之间的关系是通过外键(foreign key)来建立的，外键也是属于约束条件的一种，外键就是将表和表之间的数据进行一个硬绑定。
	
-- 如何建立表关系
	在建立表关系时，表和表之间的关联通常以主键id作为关联字段。
	
-- 什么时候需要用到一对一的关系？
	如果一张表的字段特别多，每次查询数据时又不是所有的字段都能用的到，我们就可以将表一分为二，比如说用户信息表，用户的信息包括用户名 密码 用户的年龄 用户的性别 地址 电话等等，可能经常用的只有用户的用户名和密码，这种情况我们就可以将一张用户信息表拆分成用户基本信息表和用户详细信息表，同样判断这两张表的关系还是通过换位思考：
	首先看用户基本信息表：一个用户能否有多个详细信息？答案是不可以；
    再看用户详情表：一个用户详情能否属于多个用户？答案是不可以;
    单向的一对多都不成立，那么两者之间的表关系就是一对一或者没有关系。
-- 使用SQL语句建立一对一的外键关系时需要遵循的规则
	外键建在任意一张表中都可以都可以，但是推荐将外键建在查询频率较高的表中，在创建表的时候需要先创建被关联表。
	在写入数据的时候需要先向被关联表中写入数据
	当不同的表建立关系时，需要进行级联更新和删除也可以称为同步更新同步删除，如果不建立级联更新和删除的话，无法对被关联表中被关联的数据进行删除或者修改id的操作，因为两张表是相互关联的。
	
    
-- 创建用户详情表
create table authordetail(
	id int primary key auto_increment,
	phone int,
	addr varchar(64)
);

-- 用户基本信息表
create table author(
	id int primary key auto_increment,
    name varchar(32),
    age int,
    authordetail_id int,
    foreign key(authordetail_id) references authordetail(id)  -- authordetail_id外键字段，authordetail(id)被关联字段
    on update cascade  -- 级联更新
    on delete cascade  -- 级联删除
);

4-4 创建多表关系 - 一对多

-- 在MySQL的关系在没有多对一的概念，一对多和多对一都是一对多以部门和员工关系为例：
-- 分析员工和部门表的表关系
	员工可以属于多个部门吗？答案：不能
	一个部门可以有多个员工吗？ 答案：可以
	员工对部门表就是一对多的关系
-- 在创建一对多表关系时需要遵循以下几点
    第一，外键字段建立在多的一方，即员工表
    第二，在创建表的时候，一定要先创建被关联一方，即部门表
    第三，在录入数据的时候也必须先录入被关联表的数据，即部门表的数据
    第四，当不同的表建立关系时，需要进行级联更新和删除也可以称为同步更新同步删除，如果不建立级联更新和删除的话，无法对被关联表中被关联的数据进行删除或者修改id的操作，因为两张表是相互关联的。
    
-- 创建表
-- 创建被关联表，部门表
mysql> create table bm(
    id int primary key auto_increment, 
    bm_name varchar(10), 
    bm_desc char(64)
);
Query OK, 0 rows affected (0.01 sec)

-- 创建外键所在的表，员工表
mysql> create table yg(
    id int primary key auto_increment, 
    yg_name varchar(6), 
    bm_id int, 
    foreign key(bm_id) references bm(id)  -- 表示bm_id是外键字段，关联到bm表中的id字段
    on update cascade  # 级联更新
    on delete cascade  # 级联删除
);
Query OK, 0 rows affected (0.10 sec)

4-5 创建多表关系 - 多对多

-- 分析作者和图书之间的关系
    站在图书的角度，一本书的作者可以有多个也可以有一个，
    站在作者的角度，一个作者可以写多本书
    所以图书和作者之间的关系就是多对多的关系
-- 多对多关系创建需要注意的点
	多对多的关系就是双向的一对多，多对多关系中的表每个表都会有外键，就会导致如果只创建两张表，是不可能创建成功的，因此在创建多对多关系的表时，需要创建第三张表，这第三张表用来专门存储多对多关系的两张表的关联。
	在第三章表中存储表关系
-- 创建书籍表
mysql> create table book(
    id int primary key auto_increment, 
    name varchar(10), 
    price int
);
Query OK, 0 rows affected (0.01 sec)

-- 创建作者表
mysql> create table author(
    id int primary key auto_increment, 
    name varchar(6), 
    age int
);
Query OK, 0 rows affected (0.01 sec)


-- 创建第三章表，存储book和author表的关联关系
mysql> create table book2author(
    id int primary key auto_increment, 
    author_id int, 
    book_id int, 
    foreign key(author_id) references author(id) 
    on update cascade 
    on delete cascade, 	
    foreign key(book_id) references book(id) 
    on update cascade 
    on delete cascade);
Query OK, 0 rows affected (0.02 sec)

4-6 创建多表关系总结

-- 为什么有多表关系(把数据存在一张表的弊端)
	1. 组织结构不清晰
	2. 浪费硬盘空间
	3. 扩展性差
-- 如何判断和建立表与表之间的关系
	换位思考确定表与表之间的关系，表与表之间的关系有一对一 一对多 多对多
	在MySQL中表与表之间的关系是通过外键(foreign key)来建立的，外键也是属于约束条件的一种，外键就是将表和表之间的数据进行一个硬绑定。
	在建立表关系时，表和表之间的关联通常以主键id作为关联字段。
	当不同的表建立关系时，需要进行级联更新和删除也可以称为同步更新同步删除，如果不建立级联更新和删除的话，无法对被关联表中被关联的数据进行删除或者修改id的操作，因为两张表是相互关联的。
-- 表与表之间的关系有哪些
	一对一：外键建在查询频率高的一方，需要先创建被关联的表，添加数据时也是先添加被关联表的数据
	一对多：外键建在多的一方，需要先创建被关联的表，添加数据时也是先添加被关联表的数据
	多对多：需要创建第三张表去存储多对多关系的表的外键关联

4-7 多表查询和笛卡尔乘积

-- 什么是多表查询
	需要的数据来自于多张表，单表无法满足，实际上是将多张表有关联的数据合并成一张新表，在新表中做where group by/ having / limit / order by等子句等操作
	
-- 多表查询的类型：连表查询和子查询
	连表查询
        笛卡尔乘积join
        内连接inner join
        外连接left join/right join
	子查询
	
-- 笛卡尔乘积join(join可以省略)
	select 表名.字段 from 表1 join 表2;
	将两张表中所有的数据进行一一对应然后生成一张大表，通常不建议使用使用笛卡尔乘积
	SELECT * from author join authordetail;  -- 两个表进行‘x’的操作得到一张大表
	
	SELECT * FROM author, authordetail WHERE author.authordetail_id = authordetail.id;  -- 找到两个表之间的对应关系

-- 查询作者小明的电话号码
	SELECT author.name, authordetail.phone FROM author, authordetail 
    WHERE author.authordetail_id = authordetail.id AND author.NAME = '小明';

-- 查询电话号码是119的作者名字
	SELECT author.NAME, authordetail.phone FROM author JOIN authordetail 
	WHERE author.authordetail_id = authordetail.id AND authordetail.phone = '119';

4-8 多表查询 - 内连接

-- inner join: 应用最广泛，取两张表有关联部分的数据
select 表名.字段 from 表1 inner join 表2 on 表1.表1外键字段=表2.id；

-- 连表查询两张表有关的数据
SELECT * FROM author INNER JOIN authordetail ON author.authordetail_id = authordetail.id;

-- 连表之后进行其他操作
-- 电话号码是119的作者名称和地址
SELECT author.name, authordetail.addr 
FROM author INNER JOIN authordetail ON author.authordetail_id = authordetail.id 
WHERE authordetail.phone='119';
-- 查看每个城市作者的数量以及作者的姓名
SELECT
	authordetail.addr AS '城市',
	GROUP_CONCAT( author.NAME ) AS '作者姓名' 
FROM
	author INNER JOIN authordetail ON author.authordetail_id = authordetail.id 
GROUP BY authordetail.addr;

-- 将查询作者的姓名、年龄、地址并且按照作者的年龄从大到小进行排序
SELECT
	author.NAME,
	authordetail.age,
	authordetail.addr 
FROM
	author INNER JOIN authordetail ON author.authordetail_id = authordetail.id 
ORDER BY
	authordetail.age DESC;

4-9 多表查询 - 外连接

-- 外连接有两种分别是left join和right join
-- left join：left join左边的表是主表，数据全部显示，left join右边的表是辅表,如果辅表的数据比主表中的数据多多的数据不会显示，如果少的话就使用null补全。right join和left join相反，通常使用left join的场景较多。
SELECT * FROM bm LEFT JOIN yg ON bm.id = yg.bm_id;

-- 表1 join 表2 on 关联条件 的原理：驱动表
拿到其中一张表作为驱动表，使用关联条件(on之后的条件)去和另外一张表进行比对，如果符合就将这两张表的数据进行拼接。当两张表的行数差异较大时，将小表（查询得到的结果更少的结果集）作为驱动表，可以优化查询降低数据比对的次数，对于内连接来说，无法控制驱动表是谁，完全由优化器决定，如果需要人为干预驱动表，可以通过外连接实现。
left join中驱动表就是左表，right join中驱动表就是右表

4-10 多表查询 - 子查询

-- 子查询指一个查询语句嵌套在另一个查询语句内部的查询，在 SELECT 子句中先进行子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表

-- 每个部门员工的人数
SELECT (SELECT bm_name from bm WHERE id=bm_id) as '部门名称', COUNT(id) from yg GROUP BY bm_id;

-- 思路
1. 首先先按照bm_id进行分组
select bm_id, count(id) from yg group by bm_id;

2. 通过子查询将select之后的bm_id替换成部门名称
SELECT (SELECT bm_name from bm WHERE id=bm_id) as '部门名称', COUNT(id) from yg GROUP BY bm_id;

-- 查询小雨所在部门的部门信息和该部门的所有员工姓名
SELECT id, bm_name, bm_desc, (select GROUP_CONCAT(yg_name) from yg group by bm_id HAVING bm_id=id) as '部门员工' from bm where id=(SELECT bm_id from yg where yg_name='小雨');
-- 思路
1. 查询小雨所在的部门id
SELECT bm_id from yg where yg_name='小雨';

2. 根据上一条得到的id去bm表中查询部门信息
SELECT id, bm_name, bm_desc from bm where id=(SELECT bm_id from yg where yg_name='小雨'); 

3. 根据部门id进行分组，得到不同部门的员工的名字(列转行)，再进行筛选，筛选出部门是第一条的到部门id的数据
select GROUP_CONCAT(yg_name) from yg group by bm_id HAVING bm_id=第一条查询所得到的id;

4. 将上述查询组合一下
SELECT id, bm_name, bm_desc, (select GROUP_CONCAT(yg_name) from yg group by bm_id HAVING bm_id=id) as '部门员工' from bm where id=(SELECT bm_id from yg where yg_name='小雨');

4-11 多表查询总结

-- 什么是多表查询
	需要的数据来自于多张表，单表无法满足，实际上是将多张表有关联的数据合并成一张新表，在新表中做where group by/ having / limit / order by等子句等操作
	
-- 多表查询的类型：连表查询和子查询
	多表查询
        笛卡尔乘积join
        内连接inner join
        外连接left join/right join
	子查询
	
-- 笛卡尔乘积join(join可以省略)
	select 表名.字段 from 表1 join 表2;
	将两张表中所有的数据进行一一对应然后生成一张大表，通常不建议使用使用笛卡尔乘积
	
-- inner join: 应用最广泛，取两张表有关联部分的数据
	select 表名.字段 from 表1 inner join 表2 on 表1.表1外键字段=表2.id；

-- 外连接有两种分别是left join和right join
    -- left join：left join左边的表是主表，数据全部显示，left join右边的表是辅表,如果辅表的数据比主表中的数据多多的数据不会显示，如果少的话就使用null补全。right join和left join相反，通常使用left join的场景较多。
    -- 表1 join 表2 on 关联条件 的原理：驱动表
    拿到其中一张表作为驱动表，使用关联条件(on之后的条件)去和另外一张表进行比对，如果符合就将这两张表的数据进行拼接。当两张表的行数差异较大时，将小表（查询得到的结果更少的结果集）作为驱动表，可以优化查询降低数据比对的次数，对于内连接来说，无法控制驱动表是谁，完全由优化器决定，如果需要人为干预驱动表，可以通过外连接实现。
    left join中驱动表就是左表，right join中驱动表就是右表
    
-- 子查询指一个查询语句嵌套在另一个查询语句内部的查询，在 SELECT 子句中先进行子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表

4-12 多表关系 - 增删改操作

• 一对一关系

-- 一对一表关系：(作者/作者信息)
	-- 增加数据：先增加没有外键的表的数据，再增加另一张表的数据
	INSERT INTO authordetail ( phone, addr ) 
	VALUES 
	( '110', '上海' ), 
	( '119', '北京' ),
	( '114', '重庆' ),
	( '120', '天津' );  -- 向被关联表中写入数据
	INSERT INTO author ( NAME, age, authordetail_id ) VALUES
	( '小明', 27, 1 ),
	( '小红', 28, 2 ),
	( '小鱼', 29, 3 ),
	('小雪', 30, 4 );  -- 向另一张表中写入数据，关联字段的数据必须存在于被关联表中，否则会报错

	-- 修改数据：
	UPDATE authordetail INNER JOIN author ON authordetail.id = author.authordetail_id 
	SET authordetail.addr = '天津' WHERE author.NAME = '小明';-- 小明的地址修改为天津，使用的连	表的方式
	
	-- 删除数据：
	DELETE FROM author WHERE authordetail_id = ( SELECT id FROM authordetail WHERE phone = '109' );-- 删除电话号码是109的作者，可以删除并且不会影响作者详情表
	delete from authordetail where phone='117'; -- 删除电话号码是117的作者信息，作者表中对应的数据也会被删除
	-- 删除表
	必须先删除被关联表

• 一对多关系

-- 增加数据：先增加没有外键的表的数据，再增加另一张表的数据
-- 修改数据：
	UPDATE bm set bm_desc='测试工作' WHERE id = (SELECT bm_id FROM yg WHERE yg_name='小吴'); -- -- 把小吴所在的部门的部门描述修改成 测试工作，子查询的方式
	
-- 删除数据
	delete from bm where bm_name='测试';-- 部门数据被删除，对应部门的yg数据肯定被删除
	
    delete from yg WHERE bm_id = (SELECT id from bm WHERE bm_name='测试')-- 删除测试部门的所有同学，删除员工不会影响部门
    
-- 删除表
	必须先删除被关联表	

• 多对多关系

-- 增加数据：先增加两张没有外键约束的表，最后再增加第三张表的数据
-- 修改数据
   UPDATE book2author SET author_id =( SELECT id FROM author WHERE NAME = '小明' ) WHERE book_id =( SELECT id FROM book WHERE `name` = '西游' ); -- 将西游的作者修改成小明

-- 删除数据
	-- 不管删除哪一方的表格最终影响的都是第三章表的数据，删除第三章表格的数据不会影响另两张表的数据
	-- 删除表，需要先删除第三张表

4-13 多表关系实战 - 设计表关系

设计一个简单的培训机构的管理系统，需求如下：
1. 学校有以下培训班，比如python go DBA linux运维 软件测试
2. 老师可以教不同班级
3. 学生只能在班级中的一个里边上课
根据上述的需求设计一个简单的数据库，以及表，关于表的字段可以随意发挥

分析：
班级和学生一对多,外键建在多的一方即学生
学生和学生信息一对一
班级和老师多对多


-- 创建库
create database manage_sys charset=utf8mb4;
-- 创建表
-- 创建班级表
CREATE TABLE bj (
	id INT PRIMARY KEY auto_increment,
	bj_name VARCHAR ( 10 ),
	bj_desc VARCHAR ( 36 )
) ENGINE = INNODB charset = utf8mb4;  

-- 创建学生表
CREATE TABLE xs (
	id INT PRIMARY KEY auto_increment,
	xs_name VARCHAR ( 10 ),
	score FLOAT ( 5, 2 ) UNSIGNED,
	bj_id INT,
    info_id INT,
    FOREIGN KEY ( bj_id ) REFERENCES bj ( id ) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY ( info_id  ) REFERENCES stu_info ( id ) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE = INNODB charset = utf8mb4;  

-- 创建老师表
CREATE TABLE teacher ( id INT PRIMARY KEY auto_increment, teacher_name VARCHAR ( 10 ), age INT UNSIGNED, sex enum ( '男', '女' ) ) ENGINE = INNODB charset = utf8mb4;  

-- 创建班级和老师的多对多关系表
CREATE TABLE bj2teacher (
	id INT PRIMARY KEY auto_increment,
	bj_id INT,
	teacher_id INT,
	FOREIGN KEY ( bj_id ) REFERENCES bj ( id ) ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY ( teacher_id ) REFERENCES teacher ( id ) ON UPDATE CASCADE ON DELETE CASCADE 
) ENGINE = INNODB charset = utf8mb4; 

-- 创建学生信息表
CREATE TABLE stu_info ( 
id INT PRIMARY KEY auto_increment, 
age INT, 
sex enum ( '男', '女' ), 
employment bool DEFAULT ( TRUE )
) ENGINE = INNODB charset = utf8mb4;

4-14 多表查询实战 - 连表查询

-- 查询每个班级的人数
SELECT bj.bj_name, count( xs.id ) 
FROM xs INNER JOIN bj ON xs.bj_id = bj.id 
GROUP BY bj.bj_name;
	
-- 查询DBA班级里学生的分数并按照从大到小的顺序排序
SELECT xs.xs_name, xs.score 
FROM xs INNER JOIN bj ON xs.bj_id = bj.id 
WHERE bj.bj_name = 'python' 
ORDER BY xs.score DESC;

-- 查询python班级的就业信息，哪些同学就业哪些同学没就业
select stu_info.employment, GROUP_CONCAT(xs.xs_name) 
from (xs inner join bj on xs.bj_id=bj.id) inner join stu_info on xs.info_id=stu_info.id
where bj.bj_name='python'
group by stu_info.employment;

4-15 多表查询实战 - 子查询

-- 查询每个班级的人数
select (select bj_name from bj where id=bj_id) as 班级, count(id) as '学生人数' from xs group by bj_id;
-- 思路
1. 首先在学生表中根据班级id进行分类，得到班级id
2. 得到班级id之后就可以去bj表里查询班级名称


-- 查询DBA班级里学生的分数并按照从大到小的顺序排序
select xs_name, score from xs where bj_id=(select id from bj where bj_name='DBA') order by score desc;
-- 思路
1. 首先确定从哪张表中查询数据
2. 筛选条件班级id可以从班级表中查询得到

-- 查询小雨所在班级的班级信息和班级所有学生的姓名
select 
	bj.bj_name,
	bj.bj_desc, 
	(SELECT GROUP_CONCAT(xs_name) from xs group by bj_id having bj_id=id) as '学生姓名' 
from bj
where id=(select bj_id from xs where xs_name='小雨');
-- 思路
1. 找到小雨所在的班级id
select bj_id from xs where xs_name='小雨';
2. 根据找到的班级id找到小雨所在的班级信息
select * from bj where id=班级id;
3. 在学生表中根据班级id进行分组筛选出班级id是小雨所在的班级id的学生姓名
SELECT GROUP_CONCAT(xs_name) from xs group by bj_id having bj_id=小雨所在的班级id；
4. 组合上述sql得到最终结果

create view V_score as select student.学号,student.姓名,student.民族,student.性别,课程号,成绩 from student,score where student.学号=score.学号 and student.性别='男';



select 学号,姓名,课程号,成绩 from v_score where 民族='侗';