# 外键
其实就是用来标识表与表之间的数据关系
# 表与表之间关系
一对多
多对多
一对一
1.一对多关系(一个部门信息对应多个员工信息)
create table dep(id int primary key auto_increment,dep_name varchar(32),dep_desc varchar(254));
create table emp(id int primary key auto_increment,name varchar(32),age int,dep_id int,foreign key(dep_id) references dep(id));
'''针对一对多的表关系 外键字段建在多的一方'''
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 book2author(id int primary key auto_increment,author_id int,book_id int,foreign key(author_id) references author(id) on update cascade on delete cascade,foreign key(book_id) references book(id) on update cascade on delete cascade);
on update cascade # 级联更新
on delete cascade # 级联删除
'''多对多表关系 需要单独开设第三张表存储(并且第三张表可以不绑定)'''
3.一对一关系(一个作者信息只能对应一个作者详情信息，一个作者详情信息只能对应一个作者信息)
create table author_detail(id int primary key auto_increment,phone varchar(32),address varchar(32));
create table author(id int primary key auto_increment,name varchar(32),age int,author_id int unique,foreign key(author_id) references author_detail(id) on update cascade on delete cascade); # 因为是一对一 所以外键字段使用unique约束
'''外键字段健在任何一方都可以 但是推荐建在查询频率较高的表中'''
外键约束
1.在创建表的时候 需要先创建被关联表(没有外键字段的表)
2.在插入新数据的时候 应该先确保被关联表中有数据
3.在插入新数据的时候 外键字段只能填写被关联表中已经存在的数据
4.在修改和删除被关联表中的数据的时候 无法直接操作
如果想要数据之间自动修改和删除 需要添加额外的配置
create table dep1(id int primary key auto_increment,dep_name varchar(32),dep_desc varchar(32));
create table emp1(id int primary key auto_increment,name varchar(32),age int,dep_id int,foreign key(dep_id) references dep1(id) on update cascade on delete cascade);
操作表的SQL语句补充
语法：
1.修改表名
alter table 表名 rename 新表名;
2.增加字段
alter table 表名 add 字段名 数据类型 [完整性约束条件];
alter table 表名 add 字段名 数据类型 [完整性约束条件] first;
alter table 表名 add 字段名 数据类型 [完整性约束条件] after 字段名;
3.删除字段
alter table 表名 drop 字段名;
4.修改字段
alter table 表名 modify 字段名 数据类型 [完整性约束条件];
# modify只能改字段数据类型完整约束 不能改字段名 但是change可以
alter table 表名 change 旧字段名 新字段名 旧数据类型 [完整性约束条件];
查询关键字之where筛选
'''
模糊查询:没有明确的筛选条件
关键字:like
关键符号:
%:匹配任意个数任意字符
_:匹配单个个数任意字符
show variables like '%mode%';
'''
1.查询id大于等于3小于等于6的数据
select id,name from emp where id >= 3 and id <= 6;
select * from emp where id between 3 and 6;
2.查询薪资是20000或者18000或者17000的数据
select * from emp where salary in(20000,18000,17000);
3.查询员工姓名是由四个字符组成的员工姓名与其薪资
select name,salary from emp where name like '____';
select name,salary from emp where char_length(name)=4;
4.查询岗位描述为空的员工名与岗位名
select name,post from emp where post_comment is NULL;
查询关键字之group by分组
# 分组:按照某个指定的条件将单个单个的个体分成一个个整体
# 分组之后默认只能够直接获取到分组的依据 其它数据不能直接获取
针对5.6需要自己设置sql_mode
set global sql_mode='only_full_group_by,strict_trans_tables,pad_char_to_full_length';
# 聚合函数
聚合函数主要就是配合分组一起使用
max min sum count avg
# 数据分组应用场景:每个部门的平均薪资，男女比列等
select post,avg(salary) from emp group by post;
# 每个部门的人数
select post,count(id) from emp group by post;
'''
补充说明
如果真的需要获取分组之外的数据字段，可以使用group_concat()
1.每个部门的员姓名
select post,group_concat(name) from emp group by post;
select post,group_concat(name,'|',sex) from emp group by post;
2.concat不分组使用
select concat(name,sex) from emp;
select concat(name,'|',sex) from emp;
'''
关键字之having过滤
# where与having都是筛选功能 但是有区别
where在分组之前对数据进行筛选
having在分组之后对数据进行筛选
select post,avg(salary) from emp where age>30 group by post having avg(salary)>10000
关键字之order by排序
select * from emp order by salary asc; # 默认升序
select * from emp order by salary desc; # 降序
# 先按照age降序排，再按照薪资升序排
select * from emp order by age desc,salary asc;
# 统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
select post,avg(salary) from emp where age>10 group by post having avg(salary)>1000 order by avg(salary) desc;
关键字limit分页
# 限制展示条数
select * from emp limit 3;
# 查询工资最高的人的详细信息
select * from emp order by salary desc limit 1;
# 分页显示
select * from emp limit 0,5; # 第一个参数表示起始位置，第二个参数表示的是条数，不是索引位置
select * from emp limit 5,5;
select * from emp where name regexp '^j.*(n|y)$';
