MySql：多表设计-关联查询

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

运行

代码

-- 先产生一个笛卡尔乘积 然后在条件筛选    
SELECT
  s.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 = id

SELECT s.number,s.name,s.gender,s.phone,m.name 
FROM student s
  INNER JOIN major m ON s.majorid = m.id

运行

外关联

左外关联(left join)

特点即使不满足连接条件，也会把左边表中的所有数据查询出来

select 结果 from 表1 left join 表2 on 表1.column1 = 表2.column2

代码

SELECT
  s.number,
  s.name,
  s.gender,
  s.phone,
  m.name
FROM
  student s LEFT JOIN major m ON s.majorid = m.id

运行

右外关联(right join)

特点即使不满足条件，也要把右边表中的所有数据查询出来

select 结果 from 表1 right join 表2 on 表1.column1 = 表2.column2

代码

SELECT
  s.number,
  s.name,
  s.gender,
  s.phone,
  m.name 
FROM
  student s RIGHT JOIN major m ON s.majorid = m.id

运行

应用

统计每一个专业下有多少学生

代码

SELECT
  COUNT(number),
  m.name mname
FROM
  student s RIGHT JOIN major m ON s.majorid = m.id    
  GROUP BY m.name

运行

查询学生，以及学生选择了哪些课程（课程名称）
多对多关联查询时，分组合并 group_concat(c.name) cname 在多对多情况下，可以将同一组中多个名字连接起来

代码

SELECT
  s.number,
  s.name,
  m.name mname,
  GROUP_CONCAT(c.name) cname
FROM
  student s LEFT JOIN student_course sc ON s.number = sc.stunumber    
            LEFT JOIN course c ON sc.courseid = c.id
            LEFT 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)

运行

三种查询

例：男生和女生谁的人数大于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) t
         WHERE t.c>2

运行

苏十八

关注

16
点赞
踩
17

收藏

觉得还不错? 一键收藏
0
评论
MySql：多表设计-关联查询

数据库表中必须要有主键，能唯一的表示某条记录的列（有些表中可以没有主键），要求其他字段都依赖于主键。Select 结果 from 表1，表2 where 表1.column1 = 表2.column2。-- 添加一个学生选课表学生和课程关系表放一个学生的学号外键，放一个课程外键。一张表中存储一类信息，在一张表中关联其他表中的数据时，只需要关联主键列即可。又称多表查询，当查询的字段来自于多个表时，就会用到连接查询。-- 外键在一个表中外键是用来与另一个表的主键关联的。
复制链接

扫一扫