MYSQL-关联查询

为什么需要多表关联
数据冗余–拆分
数据库设计范式
为了建立冗余较小，结构合理的数据库，设计数据库时必须遵循一定的规则。
在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。

1、第一范式（确保每列保持原子性）
2、第二范式就是要有主键，要求其他字段都依赖于主键。
3、第三范式就是要消除传递依赖，方便理解，可以看做是“消除冗余”。

 -- 创建年级表
 --数据表之间的关联/引用关系是依靠具体的主键（primary key）和外键（foreign key）建立起来
--的。
 CREATE TABLE grade(
          id INT PRIMARY KEY AUTO_INCREMENT,
          NAME VARCHAR(10) 
 )
 
 ALTER TABLE student DROP gradeid

关联表(主键);
 ALTER TABLE student ADD gradeid INT AFTER mobile
 -- 弱关联 -- 人为定义的关系  没有实际约束   删除关联表数据没有任何限制
 
  --ALTER TABLE 表名 ADD [CONSTRAINT 约束名] FOREIGN KEY(外键列) REFERENCES
 -- 强关联 -- 添加外键约束     外键关联另一个表的主键                与grade表中的id关联
 ALTER TABLE student ADD CONSTRAINT fk_gradeid FOREIGN KEY(gradeid) REFERENCES grade(id)
 
 ALTER TABLE student DROP FOREIGN KEY fk_gradeid
 
 RENAME TABLE t_student TO student
 

1、当主表中没有对应的记录时，不能将记录添加到从表
2、不能更改表中的值从而导致从表中的记录孤立
3、从表存在与主表对应的记录，不能从主表中删除该行
4、删除主表前，先删从表

关联查询按功能划分：
内连接：
等值连接
非等值连接
自连接
外连接：
左外连接
右外连接

/*
   关联查询
      学号,姓名,性别,年级名称
  
 */
 -- 笛卡尔乘积
 SELECT * FROM student,grade 
 
 -- 添加条件  多表时,为表定义别名, 通过别名去调用表中的列,这样就不会重复
SELECT
  s.num,
  s.name,
  s.sex,
  g.name
FROM
  student s,
  grade g
WHERE s.gradeid = g.id  -- 先合并表,后筛选

-- 内关联  在关联时添加条件,把满足条件的数据关联起来
SELECT
  s.num,
  s.name,
  s.sex,
  g.name
FROM
  student s
  INNER JOIN grade g
    ON s.gradeid = g.id  -- 等值连接
  -- 把满足条件的两张表中的交集数据查询出来
  -- 语法：select 结果 from 表1，表2 where 表1.column1 = 表2.column2
    
-- 非等值连接
ALTER TABLE student ADD score INT AFTER mobile
 
CREATE TABLE score_level(
    lev_name VARCHAR(5),
    lower_score INT,
    upper_score INT
) 
 
-- 内连接  非等值连接 
SELECT
  s.num,
  s.name,
  sl.lev_name
FROM
  student s
  INNER JOIN score_level sl
    ON s.score BETWEEN sl.lower_score
    AND sl.upper_score          
                      
 -- 省  市  县(区) 
 -- 自关联  地区表 
 CREATE TABLE t_area(
          id INT PRIMARY KEY,
          NAME VARCHAR(10),
          pid INT 
 )
 
SELECT
  ta.name,
  tp.name pname
FROM
  t_area ta
  INNER JOIN t_area tp
    ON ta.pid = tp.id
WHERE ta.id = 61010

-- 左外关联 
-- 无论关联条件是否成立,都会将左边表的数据全部查询出来
 SELECT
  s.num,
  s.name,
  s.sex,
  g.name
FROM
  student s
  LEFT JOIN grade g
    ON s.gradeid = g.id 
 
 
 -- 右外关联
 -- 无论关联条件是否成立,都会将右边表的数据全部查询出来
SELECT
 COUNT(s.gradeid),g.id,g.name
FROM
  student s
  RIGHT JOIN grade g
    ON s.gradeid = g.id
 GROUP BY  g.id,g.name