MySQL外键约束&SQL查询关键字

1. 外键约束

外键其实就是用来标识表与表之间的数据关系的
简单理解为外键字段是可以让你去到其他表中查找数据

1.1 表与表之间建立关系

表关系总共有四种
一对多
多对多
一对一
没关系

"判断表关系的方式：换位思考"
1. 一对多
    以员工和部门为例
        先站在员工的基础上
        "问"一个员工信息能否对应多个部门信息
        "答"不可以
        站在部门的基础上
        "问"一个部门信息能否对应多个员工信息
        "答"可以
        此时"结论："一个可以一个不可以，那么表关系就是"一对多"
        员工表是多，部门表是一
        "针对一对多的表关系，外键字段建立在多的一方"
        # 表关系没有多对一 一说，都是一对多。

"""
使用SQL语句建立真正意义上的表关系，可以先创建不含外键字段的基本表，之后再添加外键字段。
create table emp(
    id int primary key auto_increment,
    name varchar(32),
    age int,
    dep_id int,
    foreign key(dep_id) references dep(id)
);
create table dep(
    id int primary key auto_increment,
    dep_name varchar(32),
    dep_desc varchar(256)
);

"""


2. 多对多关系
以书籍 与 作者为例
    先站在书籍表的基础之上
    "问"一本书籍信息能否对应多个作者信息
    "答"可以
    "问"一个作者信息能否对应多个书籍信息
    "结论："两个都可以，那么表关系就是"多对多"。
    # 多对多的表关系，需要单独开设第三张表来存储。（并且第三张表可以不绑定关系）
	
	
"""
create table book(
    id int primary key auto_increment,
    title varchar(32),
    price float(6,2)
);
create table author(
    id int primary key auto_increment,
    name varchar(32),
    age int
);
"""
# 关系表
create table book_auth(
    in int primary key auto_increment,
    book_id int,
    auth_id int,
    foreign key(book_id) references author(id)
    on update cascade
    on delete cascade,
    foreign key(auth_id) references book(id)
    on update cascade
    on delete cascade
);

3. 一对一关系
    以作者表 与 作者详情表为例
    # 外键字段建在任何一方都可以，但是推荐建在查询频率较高的表中。

"""
create table author(
    id int primary key auto_increment,
    name varchar(32),
    age int,
    auth_id int unique,
    foreign key(auth_id) references author_detail(id)
    on update cascade
    on delete cascade
);

create table author_detail(
    id int primary key auto_increment,
    phone varchar(32),
    addr varchar(64)
);

"""

1.2 外键约束

1. 在创建表的时候，需要先创建的是被关联表（没有外键字段的表）

2. 在插入数据时，也需要先保证被关联表中有数据。

3. 在插入数据时，外键字段只能填写被关联表中已经存在的数据。

4. 在修改和删除被关联表中的数据的时候，无法直接操作。
    如果想要数据之间自动修改和删除，需要添加额外的配置
    create table emp1(
        id int primary key auto_increment,
        name varchar(32),
        age int,
        dep_id int,
        foreign key(dep_id) references dep(id)
        on update cascade  # 级联更新
        on delete cascade
    );

    create table dep1(
        id int primary key auto_increment,
        dep_name varchar(32),
        dep_desc varchar(256)
    );

"""
由于外键有实质性的诸多约束，当表特别多(超过100)的时候，外键的增多反而会增加耦合程度。
    所以在实际开发项目中，有时候并不会使用外键创建表关系。
    而是通过SQL语句层面，建立逻辑意义上的表关系。
    如：操作员工表的sql执行完毕之后，立刻跟着执行部门表的sql。
	
"""

1.3 操作表的SQL语句补充

# 修改表名
alter table 表名 rename 新表名;

# 增加字段
alter table 表名 add 字段名 数据类型 [完整性约束条件];

alter table 表名 add 字段名 数据类型 first;

alter table 表名 add 字段名 数据类型 after 字段名;

# 删除字段
alter table 表名 drop 字段名;

# 修改字段
alter table 表名 modify 
alter table 表名 change

2. SQL查询关键字（重要）

"查询关键字之 select 与 from"
from 控制查询哪张表
select 控制的是查询表里面的哪些字段

2.1 查询关键字之 where 筛选

where 筛选功能
select id from emp where id between 3 and 6;
select id from emp where id>=3 and id<=6;

# 模糊查询关键字：like
    %:匹配任意个数的任意字符
    _:匹配单个个数的任意字符

"针对NULL 不能用=等于号，只能用is"

1.2 查询关键字之 group by 分组

分组
"""
按照某个指定的条件将单个的个体分成一个个整体
    eg：按性别分组，按部门分组，按...分组
"""
# 分组之后默认只能够直接获取到分组的依据，其他数据都不能直接获取
    针对5.6需要自己设置sql_mode
    set global aql_mode='only_full_group_by';

# 聚合函数
聚合函数主要就是配合分组一起使用的
max min sum count avg

select post,max(salary) from emp group by post;

"group_concat()":分组之后使用
# 可以支持我们获取多个非分组字段，还支持中间拼接符号。
select post,group_concat(name,"|",sex,"|",age) from emp group by post;

select post,group_concat(name) from emp group by post;


"concat"：分组之前或不分组使用
select concat(name,sex) from emp;
# 同样也支持拼接
select concat(name,"|",age) from emp;

2.3 查询关键字之 having过滤

"""
where 与 having 都是筛选功能，但是有区别
    where在分组之前对数据进行筛选
    having在分组之后对数据进行筛选
	
一定要有一个简单的认识，一条SQL语句的结果可以看成一张全新的表
"""

2.4 查询关键字之 distinct去重

"去重的前提是数据一模一样！！"

select distinct age from emp;
# 原生SQL中用的可能不多，后面django框架中会用的多一些。

2.5 查询关键字之 order by排序

select * from emp order by age;
# 默认升序

select * from emp order by age desc,salary asc;

2.6 查询关键字之 limit 分页

select * from emp limit 3;

select * from emp limit 5,5;

2.7 查询关键字之 regexp正则

select * from emp where name regexp "^j.*(n|y)$";