数据库之外键、表与表关系、查询关键字
目录
MySQL外键及表与表之间关系
表与表关系种类
- 一对多(没有多对一)
- 多对多
- 一对一
- 没有关系
判断表关系的方式:换位思考
一对多
1.一对多
以员工和部门表为例
先站在员工表的基础之上
问:一个员工信息能否对应多个部门信息
答:不可以
再站在部门表的基础之上
问:一个部门信息能否对应多个员工信息
答:可以
结论:一个可以一个不可以 那么表关系就是"一对多"
员工表是多 部门表是一
'''
针对一对多的表关系 外键字段建在多的一方
但是要先创建一对应的表 再创建多对应的表
'''
添加外键
create table emp(
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 dep(
id int primary key auto_increment,
dep_name varchar(32),
dep_desc varchar(254)
);
多对多
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 # 级联删除
);
一对一
3.一对一表关系
作者表与作者详情表
先站在作者表的基础之上
问:一个作者信息能否对应多个作者详情信息
答:不可以
再站在作者详情表的基础之上
问:一个作者详情信息能否对应多个作者信息
答:不可以
结论:两个都不可以
那么表关系可能是"一对一"或者"没有关系"
# 外键字段建在任何一方都可以 但是推荐建在查询频率较高的表中
添加外键
和一对多唯一不同的是 一对一外键字段要加unique
# 作者表
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 # 级联删除
);
# 作者详情表
create table author_detail(
id int primary key auto_increment,
phone varchar(32),
address 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 # 级联删除
);
create table dep1(
id int primary key auto_increment,
dep_name varchar(32),
dep_desc varchar(254)
);
由于外键有实质性的诸多约束 当表特别多的时候外键的增多反而会增加耦合程度
所以在实际开发项目中 有时候并不会使用外键创建表关系
而是通过SQL语句层面 建立逻辑意义上的表关系
eg:操作员工表的sql执行完毕之后 立刻跟着执行操作部门的sql
查询关键字
# 数据准备
create table emp(
id int primary key auto_increment,
name varchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #一个部门一个屋子
depart_id int
);
#插入记录
#三个部门：教学，销售，运营
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
('jason','male',18,'20170301','张江第一帅形象代言',7300.33,401,1), #以下是教学部
('tom','male',78,'20150302','teacher',1000000.31,401,1),
('kevin','male',81,'20130305','teacher',8300,401,1),
('tony','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jack','female',18,'20110211','teacher',9000,401,1),
('jenny','male',18,'19000301','teacher',30000,401,1),
('sank','male',48,'20101111','teacher',10000,401,1),
('哈哈','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('呵呵','female',38,'20101101','sale',2000.35,402,2),
('西西','female',18,'20110312','sale',1000.37,402,2),
('乐乐','female',18,'20160513','sale',3000.29,402,2),
('拉拉','female',28,'20170127','sale',4000.33,402,2),
('僧龙','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3);
查询关键字之select与from
from控制的是查询哪张表
select控制的是查询表里面的哪些字段
select * from emp;
select id,name from emp;
查询关键字之where筛选
where筛选功能
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 = 20000 or salary = 18000 or salary = 17000;
select * from emp where salary in (20000,18000,17000); # 简写
3.查询id小于3或者大于6的数据
select * from emp where id not between 3 and 6;
4.查询薪资不在20000，18000，17000范围的数据
select * from emp where salary not in (20000,18000,17000);
5.查询岗位描述为空的员工名与岗位名 针对null不能用等号，只能用is
select name,post from emp where post_comment = NULL; # 查询为空！
select name,post from emp where post_comment is NULL;
select name,post from emp where post_comment is not NULL;
模糊查询
模糊查询:没有明确的筛选条件
关键字:like
关键符号:
%:匹配任意个数任意字符
_:匹配单个个数任意字符
show variables like '%mode%se';
1.查询员工姓名中包含o字母的员工姓名和薪资
# 在你刚开始接触mysql查询的时候，建议你按照查询的优先级顺序拼写出你的sql语句
"""
先是查哪张表 from emp
再是根据什么条件去查 where name like ‘%o%’
再是对查询出来的数据筛选展示部分 select name,salary
"""
select name,salary from emp where name like '%o%';
2.查询员工姓名是由四个字符组成的员工姓名与其薪资
select name,salary from emp where name like '____';
select name,salary from emp where char_length(name) = 4;
查询关键字之group by分组
group by
定义:按照某个指定的条件将单个单个的个体分成一个个整体
eg: 按照男女分组
按照肤色分组
按照年龄分组
select 字段1,.... from emp group by 字段1; # 前后字段1必须保持一致,前面不写字段1会报错
# 分组之后默认只能够直接获取到分组的依据 其他数据都不能直接获取(除了用group_concat)
针对5.6需要自己设置sql_mode 5.7及之后的忽视
set global sql_mode = 'only_full_group_by,STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH';
退出客户端重新进入就行了
聚合函数
聚合函数主要就是配合分组一起使用
| 关键字 | 作用 |
|---|---|
| max() | 求最大值 |
| min() | 求最小值 |
| sum() | 求和 |
| count() | 求数量 |
| avg() | 求平均值 |
group_concat
解决了分组后只能获得按条件分组的那组信息，可以获得其他字段的信息
# group_concat 分组之后使用
如果真的需要获取分组以外的数据字段 可以使用group_concat()
# 每个部门的员工姓名
select post,group_concat(name) from emp group by post;
select post,group_concat(name,'|',sex) from emp group by post; # 可以加任意符号进行分割显示
# concat 不分组使用
select concat(name,sex) from emp;
select concat(name,'|',sex) from emp;
题目
# 每个部门的最高工资
select post,max(salary) from emp group by post;
补充:在显示的时候还可以给字段取别名
select post as '部门',max(salary) as '最高工资' from emp group by post;
as也可以省略 但是不推荐省 因为寓意不明确
# 每个部门的最低工资
select post,min(salary) from emp group by post;
# 每个部门的平均工资
select post,avg(salary) from emp group by post;
# 每个部门的工资总和
select post,sum(salary) from emp group by post;
# 每个部门的人数
select post,count(id) from emp group by post;
统计的时候只要是非空字段 效果都是一致的
这里显示age，salary，id最后演示特殊情况post_comment
关键字之having过滤
having与where的区别
| 名字 | 作用 |
|---|---|
| having | 在分组之后对数据进行筛选 |
| where | 在分组之前对数据进行筛选 |
1.统计各部门年龄在30岁以上的员工平均薪资,并且保留平均薪资大于10000的部门.
select post,avg(salary) from emp where age>30 group by post having avg(salary)>10000;
# 这里的having就是对分组之后的最后平均薪资大于10000的部门进行筛选
关键字之distinct去重
# 对有重复的展示数据进行去重操作 一定要是重复的数据
select distinct id,age from emp; # 不行,因为id不重复
select distinct post from emp; # 对部门进行去重
select distinct age from emp; # 对年龄进行去重
关键字之order by排序
select * from emp order by salary; #默认升序排
select * from emp order by salary desc; #降序排
#先按照age降序排，在年轻相同的情况下再按照薪资升序排
select * from emp order by age desc,salary;
# 统计各部门年龄在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;
关键字之regexp正则
eg:
select * from emp where name regexp '^j.*(n|y)$';
关键字之exists
EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录，
而是返回一个真假值，True或False。
当返回True时，外层查询语句将进行查询
当返回值为False时，外层查询语句不进行查询。
select * from emp
where exists
(select id from emp where id > 3);
select * from emp
where exists
(select id from emp where id > 250);
练习判断表关系
判断表关系
# 班级表
cid caption
# 学生表
sid sname gender class_id
# 老师表
tid tname
# 课程表
cid cname teacher_id
# 成绩表
sid student_id course_id number
练习单表查询
1. 查询岗位名以及岗位包含的所有员工名字
2. 查询岗位名以及各岗位内包含的员工个数
3. 查询公司内男员工和女员工的个数
4. 查询岗位名以及各岗位的平均薪资
5. 查询岗位名以及各岗位的最高薪资
6. 查询岗位名以及各岗位的最低薪资
7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
