多表创建 外键以及多表查询

1. 多表创建的介绍

表关系的创建一共有三种：
一对一
一对多
多对多
"""如何判断表关系：换位思考法"""

# 一对多
以图书表和出版社表为例
先站在图书表的角度
    问：
        1. 一本图书能否有多个出版社出版?
            答：不能
 站在出版社的角度问：
        1. 一个出版社能否出版多本图书?
            答：能
          得出结论：一个能，一个不能，那么，表关系就是'一对多'
        
   """一对多的表关系外键字段建在多的一方"""
主要建表的方法就是
1. primary key auto_increment  # 设置主键 并且自增
2.  foreign key  # 建立表的关联字段
3. references on update cascade on delete cascade # 级联更新与级联删除

查询的几个方法 一条SQL语句的执行结果当成另外一条SQL语句的执行条件 
1. 子查询
select * from dep where id= (select dep_id from emp where name='jason');

2. 连表查询 或多表查询

# 我们连表的时候有专业的连表语法
inner join # 内连接，数据只取两张表中共有的数据
left join # 左连接，数据以左表为准，展示左表所有的数据，右表没有的数据使用NULL填充
right join # 又连接，数据以右表为准，展示右表所有的数据，左表没有的数据使用NULL填充
union # 连接多条SQL语句执行的结果

2. 一对多 表的创建

# 在SQL成面建立一对多的关系
# 多表的创建，先创建表的基本字段， 在添加外键字段
"""
一对多表的创建 
一定要先创建被关联表 在创建关联表 如果被关联表没创建来 没有被关联的表 报错

"""
# 在SQL成面建立一对多的关系
# 多表的创建，先创建表的基本字段， 在添加外键字段

先创建出版表
create table publish(
    id int primary key auto_increment,
    title varchar(128)
);

再创建图书表
create table book(
    id int primary key auto_increment,
    title varchar(128),
    price decimal(8, 2),
    publish_id int,
    foreign key(publish_id) references publish(id) # 意思是：book表中的publish_id和publish表中的id是外键关系
);

# 插入数据也是 先插入被关联表的数据 再插入关联表的数据

往出版社表中录入
insert into publish (title) values ('北京出版社');
insert into publish (title) values ('东京出版社');

# 往表中录入数据
insert into book (title, price, publish_id) values('水浒传', 1000, 1);
insert into book (title, price, publish_id) values('西游记', 1000, 2);

3. 外键约束

1. 在创建表的时候，应该先创建被关联表(没有外键字段的表)
2. 在录入数据的时候，应该先录入被关联表(没有外键字段的表)
3. 在录入数据的时候，应该录入被关联表中已经存在的值.
4. 如果对被关联表中的数据进行修改和删除的时候，需要把关联表中的数据也跟着修改或者删除(不现实)
需要使用以下方式创建表关系
先创建图书表
create table book(
    id int primary key auto_increment,
    title varchar(128),
    price decimal(8, 2),
    publish_id int,
    foreign key(publish_id) references publish(id) # 意思是：book表中的publish_id和publish表中的id是外键关系
    on update cascade  # 级联更新
    on delete cascade   # 
);


在创建出版表
create table publish(
    id int primary key auto_increment,
    title varchar(128)
);

"""
但是，由于创建了外键关系和级联更新级联删除，那么，两张表之间就有了强制的约束关系，这样就增加了表与表之间的强耦合度

所以，以后实际项目中，我们大多数不建立这种强耦合关系，我们使用的是建立逻辑意义上的关系

4. 多对多表的关系

问题：外键字段建在哪里?
答案：多对多的外键字段需要建立第三张表来存储
"""在SQL层面建立多对多的关系"""
先建立两种不是关联的表 最后在建立关联表
先创建图书表
create table book(
    id int primary key auto_increment,
    title varchar(128),
    price decimal(8, 2)
);

create table author(
    id int primary key auto_increment,
    name varchar(32)
);

# 建立第三张表来保存两张表的关系
create table book2author(
    id int primary key auto_increment,
    book_id int,
    author_id int,
    foreign key(book_id) references book(id) 
    on update cascade
    on delete cascade,
    foreign key(author_id) references author(id) 
    on update cascade
    on delete cascade
);
insert into book2author(book_id, author_id) values(1, 1),(1, 2),(2, 1);

5. 一对一表的创建

问题：外键字段建在哪里?
答案：一对一的外键字段可以建在任何一张表中，但是，推荐建在查询频率较高的一张表中
"""在SQL层面建立多对多的关系"""

先建立被关联表
create table author_detail(
    id int primary key auto_increment,
    addr varchar(32),
    height decimal(5,2)
);

在创建关联表
create table author(
    id int primary key auto_increment,
    name varchar(32),
    author_detail_id int unique,
    foreign key (author_detail_id) references author_detail(id)
);

6. 多表查询以及链表查询 子查询

多表查询的思路：
1. 子查询
    # 一条SQL语句的执行结果当成另外一条SQL语句的执行条件
    # 大白话：分步操作
问题：查看姓名为jason的部门名称:
    1. 先查询部门id
    select dep_id from emp where name='jason';
    
    2. 拿着部门id作为条件，在去部门表中查询部门名称
        select * from dep where id=200;
        
    3. 把上述两条SQL语句合并为一条SQL语句
        select * from dep where id= (select dep_id from emp where name='jason');

2. 连表查询
     1. inner join 
    select * from emp inner join dep on emp.dep_id=dep.id;
    
    2. left join
    select * from emp left join dep on emp.dep_id=dep.id;
    
    3. right join
    select * from emp right join dep on emp.dep_id=dep.id;
    
    4. union
    select * from emp left join dep on emp.dep_id=dep.id
    union
    select * from emp right join dep on emp.dep_id=dep.id;
    
    5. 还可以给表名起别名
    select * from emp as e inner join dep as d on e.dep_id=d.id;