数据库 mysql多表设计 关联查询 内连接 等值连接 自连接 左外连接 表与表之间的关联关系

为什么需要多表

• 需要存储不同的信息

为什么多表需要关联

学生 年级 课程

数据与数据之间有关系
减少数据冗余

表设计原则 ： 数据库设计范式 为了设计处冗余小，结构合理

数据库设计3范式

• 第一范式：表中列的原子性 列不可再拆分
  联系方式 有歧义 手机/邮箱/qq 错误
  手机 qq 邮箱 不可再拆分 正确

• 第二范式：要有主键，其他列依赖于主键存在，主键是唯一的，可以表示一条记录的

• 第三范式：消除传递依赖，不同的表只存储一类信息，相互通过编号关联即可

     CREATE TABLE grade(
     id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20)
   )

• 在学生表中添加年级的id

  ALTER TABLE student ADD COLUMN gradeid INT

在多表关联时，有两种关联方式：

• 1.弱关联 表与表之间没有任何约束关系
• 2.强关联 外键约束

添加外键约束 修改student表 添加约束 外键列 gradeid 引用关联 grade表中的id列

ALTER TABLE student 
ADD CONSTRAINT fk_student_grade_gradeid 
FOREIGN KEY(gradeid) 
REFERENCES grade(id)

关联查询 又称多表查询

• 学号 姓名 性别 所在年级（年级的名称）
• 查询的数据来源于多张表 为表定义别名
• 问题：笛卡尔乘积现象 结果不对，避免发生,添加条件

SELECT
  s.no,
  s.name,
  s.gender,
  g.name
FROM
  student s,-- 给表名后直接加字母就是给表名定义临时的名字
  grade g
WHERE
  s.gradeid = g.id

内连接

• 把满足了条件的两张表中的交集数据查询出来

• 等值连接

语法：Select 
        结果 
      from 
        表1，表2 
      where 
        表1.column1 = 表2.column2
        
SELECT
    s.no,
    s.name,
    s.gender,
    g.name gname
FROM
    student s
  INNER JOIN grade g
    ON a.gradeid = g.id

• 非等值连接

SELECT 
  s.no,
  s.name,
  h.level_name
 FROM -- 结果在设置表的中间时显示设置的东西
   student s
 INNER JOIN
   height_level h
 ON s.height BETWEEN h.level_lower AND h.lever_upper

• 内连接 自连接

SELECT
 tal.name pname,
 ta.name
FROM  --  在同一个表中两个列相连
 t_area ta
 INNER JOIN t_area tal  
   ON ta.pid = tal.id

• 左外连接
• 查询出所有的学生
• 即使不满足连接条件，也会被查询出来

SELECT
  s.no,
  s.name,
  s.gender,
  g.name gname
FROM
  student s
  LEFT JOIN grade g
    ON s.gradeid = g.id

• 右外连续查询

SELECT
  g.name,
  COUNT (s.gradeid)
FROM
  student s
  RIGHT JOIN grade g
    ON s.gradeid = g.id
GROUP BY g.name

数据库中表与表之间关联关系

• 一对一关联
• 一对多关联
• 多对一关联
• 多对多关联

SELECT
 s.no,
 s.name,
 GROUP_CONCAT (c.name) cname
FROM
 student s
 LEFT JOIN student_course sc
   ON s.no = sc.studentid
 LEFT JOIN course c
   ON sc.courseid = c.id
GROUP BY s.no,
 s.name