一、表设计之关联关系

• 外键：主键是用于表示数据的唯一性字段，外键是用于建立关联关系的字段，值通常指向另一张表的主键

• 一对一

  1. 什么是一对一的关系：有A,B两张表，A表中一条数据对应B表中的一条数据，称之为一对一的关系

  2. 应用场景：用户表和用户详情表，商品表和商品信息扩展表

  3. 如何建立关系：在从表中添加外键执行主表的主键

  4. 练习：创建User(id，username,password)和user_info表(uid，nick,age,phone,address)然后插入以下数据

     1 李白  libai123  太白  28  13838283388  四川江邮
     2 杜甫  dufu123   子美  24  13232323322  四川成都
     3 白居易 bai123   乐天  27  13131313311  河南郑州

     -- 创建表
     create table user(id int primary key auto_increment, #主表的主键username varchar(50),password varchar(50)
     );
     ​
     create table user_info(uid int,  #从表的外键，指向主表的主键nick varchar(50),age int,phone varchar(50),address varchar(50)
     );
     ​
     -- 添加数据
     insert into user values
     (null,'李白','libai123'),
     (null,'杜甫','dufu123'),
     (null,'白居易','bai123');
     ​
     insert into user_info values
     (1,'太白',28,'13838283388','四川江邮'),
     (2,'子美',24,'13232323322','四川成都'),
     (3,'乐天',27,'13131313311','河南郑州');
     ​
     ​

  5. 查询每一个用户的用户名，昵称和电话

     selectu.username,ui.nick,ui.phone
     fromuser u
     left joinuser_info ui
     onu.id = ui.uid;

  6. 查询太白的用户名和密码

     selectu.username,u.password
     fromuser u
     left joinuser_info ui
     onu.id = ui.uid
     where ui.nick = '太白'

  7. 查询白居易的昵称和家庭住址

     selectui.nick,ui.address
     fromuser u
     left joinuser_info ui
     onu.id = ui.uid
     where u.username = '白居易'

• 一对多

  1. 什么是一对多的关系，有A，B两张表，A表中的一条数据对应B表中的多条数据，同时B表中的一条数据对应A表中的一条数据

  2. 应用场景：员工表和部门表，商品表和商品分类表

  3. 如何建立关系：在多的表中添加外键指向另一张表的主键

  4. 练习

     • 创建 t_emp(id,name,sal,dept_id) 和t_dept(id，name, loc)

       create table t_emp(id int primary key auto_increment,name varchar(50),sal double,dept_id int # 外键  指向t_dept表的主键
       );
       ​
       create table t_dept(id int primary key auto_increment,name varchar(50),loc varchar(50)
       )   

     • 保存以下数据

       1  诸葛亮 3000  法师部   A国
       2  周瑜   2000  法师部   A国
       3  关羽   1500  战士部   B国
       4  张飞   1000  战士部   B国
       5  孙尚香  5000 射手部   C国

       insert into t_dept values
       (null,'法师部','A国'), #1
       (null,'战士部','B国'), #2
       (null,'射手部','C国'); #3
       ​
       insert into t_emp values
       (null,'诸葛亮',3000,1),
       (null,'周瑜',2000,1),
       (null,'关羽',1500,2),
       (null,'张飞',1000,2),
       (null,'孙尚香',5000,3)
       ​

       1. 查询每个员工的名字和所在部门的名称

          SELECTe.name,d.name
          FROMt_emp e
          LEFT JOINt_dept d
          ONe.dept_id = d.id

       2. 查询A国的员工姓名

          SELECTe.name
          FROMt_emp e
          LEFT JOINt_dept d
          ONe.dept_id = d.id
          WHEREd.loc='A国'
          ​

       3. 查询张飞的部门名称

          SELECTd.name
          FROMt_emp e
          LEFT JOINt_dept d
          ON  e.dept_id = d.id
          WHEREe.name='张飞'

       4. 查询工资在2000以下员工的姓名，所在地

          SELECTe.name,d.loc
          FROMt_emp e
          LEFT JOINt_dept d
          ONe.dept_id = d.id
          WHERE   e.sal <2000

• 多对多

  1. 什么是多对多：A B两张表，A表中的一条数据对应B表中的多条数据，同时B表中的一条数据对应A表中的多条数据

  2. 应用场景：学生表和老师表

  3. 如何建立关系：创建一个关系表，两个字段，分别执行另外两张表的主键

  4. 练习：

     1. 创建student(id,name)表和teacher(id,name) 和关系表

     2. 保存以下数据

        张老师： 张三，李四，刘红，赵小绿，钱小能
        李老师：张三，赵小绿，李小红，江波，西门长海
        孙老师：江波，张三，钱枫，刘红，赵德柱
        王老师：西门长海，赵德柱，江波，钱小能，李菲菲
        ​

     3. 表设计

        创建表并添加数据

        create table student(id int primary key auto_increment,name varchar(30)
        );
        ​
        insert into student values
        (null,'张三'),(null,'李四'),(null,'刘红'),    
        (null,'赵小绿'),(null,'钱小能'),(null,'李晓红'), 
        (null,'江波'),(null,'西门长海'),(null,'钱枫'),
        (null,'赵德柱'),(null,'李菲菲');
        ​
        ​
        create table teacher(id int primary key auto_increment,name varchar(50)
        );
        ​
        insert into teacher values
        (null,'张老师'),(null,'李老师'),
        (null,'孙老师'),(null,'王老师');​
        create table s_t_table(t_id int,s_id int
        );
        ​
        insert into s_t_table values
        (1,1),(1,2),(1,3),(1,4),(1,5),
        (2,1),(2,4),(2,6),(2,7),(2,8),
        (3,7),(3,1),(3,9),(3,3),(3,10),
        (4,8),(4,10),(4,7),(4,5),(4,11);
        ​

     4. 查询每个学生对应的老师

        SELECTs.name,t.name
        FROMstudent s
        LEFT JOINs_t_table stt
        ONs.id = stt.s_id
        LEFT JOINteacher t
        ONt.id = stt.t_id;

     5. 查询孙老师的学生有哪些

        SELECTs.name
        FROMstudent s
        LEFT JOINs_t_table stt
        ONs.id = stt.s_id
        LEFT JOINteacher t
        ONt.id = stt.t_id
        WHEREt.name = '孙老师'
        ​

     6. 查询江波的老师是谁

        SELECTt.name
        FROMstudent s
        LEFT JOIN s_t_table sst
        ONs.id = sst.s_id
        LEFT JOIN teacher t
        ONt.id = sst.t_id
        WHEREs.name = '江波'

二、自关联

• 当前表的外键指向当前表的主键，这种关联方式叫做自关联

• 应用场景：需要保存上下级关系的时候（ Z-Tree）

• 查询时使用内连接即可 join on

• 查询员工姓名和对应的主管名称

  SELECT e.ename,m.ename
  FROMemp e
  JOINemp m
  ONe.mgr = m.empno

三、连接方式和关联关系

• 连接方式：包括 等值连接 、内连接，外连接（左外连接，右外连接） 是指查询多张表（等值连接和内连接是把一张表看做两张表的）数据的时候使用的查询方式

• 关联关系：包括：一对一 ， 一对多， 多对多， 是指设计表的时候，两张表之间存在的逻辑关系