MySql：多表设计-关联查询
目录
多表设计
代码
运行
数据库设计范式
设计三范式
1、第一范式：
2、第二范式：
3、第三范式：
多表设计_关联查询
外键
外键约束
代码
运行
注意：
应用
代码
运行
代码
运行
关联查询
含义：
笛卡尔乘积现象：
代码
运行
代码
运行
功能分类：
内关联(inner join)
代码
运行
外关联
左外关联(left join)
代码
运行
右外关联(right join)
代码
运行
应用
代码
运行
代码
运行
DQL-基础查询
子查询
含义：
代码
运行
分类：
按子查询出现的位置：
按功能、结果集的行列数不同：
三种查询
量子查询（一行一列）
代码
运行
列子查询（一列多行）
代码
运行
表子查询
代码
运行
多表设计
专业信息表
专业编号（主键）
专业名称
学生与专业是有关系的，如何建立学生和专业表的关系
代码
CREATE TABLE major(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20)
) -- 修改表 为表添加一个列 一对多 多对一
ALTER TABLE student ADD majorid INT运行
数据库设计范式
(数据库设计规则)如何将多个信息合理的进行组织
设计三范式
1、第一范式：
满足列的原子性（不可再拆分）
例：联系方式——>邮箱，电话，qq 可再拆分
2、第二范式：
数据库表中必须要有主键，能唯一的表示某条记录的列（有些表中可以没有主键），要求其他字段都依赖于主键。
3、第三范式：
一张表中存储一类信息，在一张表中关联其他表中的数据时，只需要关联主键列即可
一个系统中会用到多张表，每张表之间都存在关联关系
多表设计_关联查询
数据表之间的关联/引用关系是依靠具体的主键（primary key）和外键 （foreign key）建立起来的。
外键
引用另外一个数据表的某条记录。
外键列类型与主键列类型保持一致
外键约束
约束名规则：
-- 外键 在一个表中外键是用来与另一个表的主键关联的
-- 主键 不能为空 唯一 检查
-- 外键有两种情况
-- 1、不加外键约束
-- 可以任意的对表数据进行操作，即使两个 表中的数据对应不上也没有问题
-- 2、添加外键约束
-- 添加外键约束后两张表操作时，`student``student`不能导致外键列和主键列对应关系不成立
代码
ALTER TABLE student ADD CONSTRAINT fk_student_major_majorid FOREIGN KEY(majorid) REFERENCES major(id)-- 删除外键约束
ALTER TABLE student DROP FOREIGN KEY fk_student_major_majorid运行
注意：
1、当主表中没有对应的记录时，不能将记录添加到从表
2、不能更改主表中的值而导致从表中的记录孤立
3、从表存在与主表对应的记录，不能从主表中删除该行
4、删除主表前，先删从表
应用
多对一 球员与球队 学生与专业
一对多 球队与球员 专业与学生
多对多 学生选课，一个学生可以选择多个课程
-- 学生选课 一个学生至少选择两个课程
-- 课程信息表 多对多关系设计
代码
CREATE TABLE course(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20)
) 运行
-- 添加一个学生选课表 学生和课程关系表 放一个学生的学号外键，放一个课程外键
代码
CREATE TABLE student_course(stunumber INT,courseid INT
) ALTER TABLE student_course ADD CONSTRAINT fk_student_course_stunumber FOREIGN KEY(stunumber) REFERENCES student(number)
ALTER TABLE student_course ADD CONSTRAINT fk_student_major_courseid FOREIGN KEY(courseid) REFERENCES course(id)运行
关联查询
关联查询 多表关联在一起查询
学号 姓名 性别 电话 专业名称 （信息来自与两张表中）
含义：
又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
笛卡尔乘积现象：
表1（m）和表2（n）行列不相同，相乘会出现结果=m*n
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件
代码
-- 关联时没有任何的限制，会产生笛卡尔乘积现象，这并不是为我们想要的效果
SELECT * FROM student,major 运行
代码
-- 先产生一个笛卡尔乘积 然后在条件筛选
SELECTs.number,s.name,s.gender,s.phone,m.name
FROM student s, major m
WHERE s.majorid = m.id运行
功能分类：
内关联(inner join)
把满足条件关联在一起
把满足了条件的两张表中的交集数据查询出来
Select 结果 from 表1，表2 where 表1.column1 = 表2.column2
代码
select number,name,gender,phone,name from student inner join major on majorid = idSELECT s.number,s.name,s.gender,s.phone,m.name
FROM student sINNER JOIN major m ON s.majorid = m.id运行
外关联
左外关联(left join)
特点 即使不满足连接条件，也会把左边表中的所有数据查询出来
select 结果 from 表1 left join 表2 on 表1.column1 = 表2.column2
代码
SELECTs.number,s.name,s.gender,s.phone,m.name
FROMstudent s LEFT JOIN major m ON s.majorid = m.id 运行
右外关联(right join)
特点 即使不满足条件，也要把右边表中的所有数据查询出来
select 结果 from 表1 right join 表2 on 表1.column1 = 表2.column2
代码
SELECTs.number,s.name,s.gender,s.phone,m.name
FROMstudent s RIGHT JOIN major m ON s.majorid = m.id 运行
应用
统计每一个专业下有多少学生
代码
SELECTCOUNT(number),m.name mname
FROMstudent s RIGHT JOIN major m ON s.majorid = m.id GROUP BY m.name运行
查询学生，以及学生选择了哪些课程（课程名称）
多对多关联查询时，分组合并 group_concat(c.name) cname 在多对多情况下，可以将同一组中多个名字连接起来
代码
SELECTs.number,s.name,m.name mname,GROUP_CONCAT(c.name) cname
FROMstudent s LEFT JOIN student_course sc ON s.number = sc.stunumber LEFT JOIN course c ON sc.courseid = c.idLEFT JOIN major m ON s.majorid = m.id
GROUP BY s.number,s.name,m.name运行
DQL-基础查询
子查询
含义：
出现在其他语句中的select语句，称为子查询或内查询；外部的查询语句，称为主查询或 外查询
代码
-- 查询身高最高的学生 最高身高不知道,不知道可以通过sql进行查询
SELECT * FROM student WHERE height = (SELECT MAX(height) FROM student)运行
分类：
按子查询出现的位置：
from后面：支持表子查询
where：支持标量子查询，列子查询
按功能、结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
表子查询（结果集一般为多行多列
三种查询
例：男生和女生谁的人数大于2 首先统计出男生女生各自的人数
量子查询（一行一列）
代码
SELECT * FROM student WHERE height = (SELECT MAX(height) FROM student) 运行
列子查询（一列多行）
代码
-- 在where后面 使用标列子查询（一列多列）
SELECT * FROM student WHERE height IN (SELECT height FROM student WHERE height IN (1.81,1.98))运行
表子查询
把子查询的结果当作另一个表的数据来源
代码
SELECT * FROM (SELECT COUNT(*) c,gender FROM student GROUP BY gender) tWHERE t.c>2运行
