数据库---mysql外键和查询
自增特性
案例：
create table t1(
id int primary key auto_increment,
name varchar(32)
);
insert into t1(name) values('jason'),('kevin'),('tony');
delete from t1 where id=2;
insert into t1(name) values('oscar');
delete from t1 where id=4;
insert into t1(name) values('oscar');
select * from t1;
# 结果
+----+-------+
| id | name |
+----+-------+
| 1 | jason |
| 3 | tony |
| 5 | oscar |
+----+-------+
结论：自增不会随着数据的删除而回退；delete删除数据但无法重置主键，truncate删除数据重置主键。
外键
外键是某个表中的一列，它包含在另一个表的主键中。外键字段就是用来记录表与表之间数据的关系。
外键关系
一对多
理解：
就是1个东西可以包含多个东西。例子：一个部门中有很多个员工，部门是'一，'员工是'多'。一对多关系，外键字段建在"多"的一方(员工表)。
外键SQL语句：
create table dep(
id int primary key auto_increment comment '编号',
dep_name varchar(32) comment '部门名称',
dep_desc varchar(32) comment '部门描述'
);
create table emp(
id int primary key auto_increment comment '编号',
name varchar(32) comment '姓名',
age int comment '年龄',
dep_id int comment '部门编号',
foreign key(dep_id) references dep(id)
);
foreign key的约束效果:
- 创建表的时候，应该先创建被关联表(没有外键字段的表)。
- 插入数据的时候，应该先插入被关联表(没有外键字段的表)，因为外键字段填入的值只能是被关联表中已经存在的值。
- 修改、删除被关联表数据都会出现障碍。
修改和删除被关联数据出错的解决方案：级联更新级联删除。
create table dep1(
id int primary key auto_increment comment '编号',
dep_name varchar(32) comment '部门名称',
dep_desc varchar(32) comment '部门描述'
);
create table emp1(
id int primary key auto_increment comment '编号',
name varchar(32) comment '姓名',
age int comment '年龄',
dep_id int comment '部门编号',
foreign key(dep_id) references dep1(id)
on update cascade # 级联更新
on delete cascade # 级联删除
);
多对多关系
多对多关系举例：
1、一个班级有若干个老师，一个老师也可以带若干个班级。
2、一门课程同时有若干个学生选修，一个学生可以同时选修多门课程。
案例：
create table book(
id int primary key auto_increment,
title varchar(32),
price float(10,2)
);
create table author(
id int primary key auto_increment,
name varchar(32),
gender enum('male','female','others')
);
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 # 级联删除
);
针对多对多表关系:
两张基表内的数据没有在第三张表内绑定关系的情况下随意新增修改删除。
一对一关系
一对一关系举例：
1、每个中国本土居民和他们唯一的身份证编号。
2、每个班级中唯一的班长，每个班长也只服务于唯一的班级。
针对'一对一关系'外键字段建在任意一方都可以,但是推荐建在查询频率较高的较好的一方
create table User(
id int primary key auto_increment,
name varchar(32),
gender enum('male','female','others'),
user_detail_id int unique, # 好好体会为什么加unique
foreign key(user_detail_id) references UserDetail(id)
on update cascade # 级联更新
on delete cascade, # 级联删除
);
create table UserDetail(
id int primary key auto_increment,
phone bigint,
age int
);
一对一注意：就是在一对多的多里面添加上unique限制只能有一个。
表查询关键字
数据准备
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
select用于指定查询的字段,from用于指定查询的表
select id,name from emp;
查询关键字之where筛选
# 1.查询id大于等于3 小于等于6的数据
select * from emp where id>=3 and id<=6;
select * from emp where id between 3 and 6;
# 2.查询id小于3或者大于6的数据
select * from emp where id not between 3 and 6;
# 3.查询薪资是20000或者18000或者17000的数据
select * from emp where salary=20000 or salary=18000 or salary=17000;
select * from emp where salary in (20000,18000,17000);
# 4.查询薪资不在20000,18000,17000的数据
select * from emp where salary not in (20000,18000,17000);
# 5.查询岗位描述为空的数据
select * from emp where post_comment=null; # 不可以
'''针对null只能用is不能用等号'''
select * from emp where post_comment is null; # 可以
# 6.查询员工姓名中包含字母o的员工姓名和薪资
注意：查询条件如果不是很明确的情况下，我们统一称之为'模糊查询'
关键字
like:开启模糊查询的关键字
关键符号
%:匹配任意个数的任意字符
_:匹配单个个数的任意字符
select name,salary from emp where name like '%o%';
# 7.查询员工姓名是由四个字符组成的数据
select * from emp where name like '____';
select * from emp where char_length(name)=4;
查询关键字之group by分组
什么是分组?
按照指定的条件将单个单个的个体组织成一个个整体。 eg:按照性别分组 按照部门分组 按照年龄分组 按照国家分组...
为什么需要分组?分组的好处在于可以快速统计出某些数据。
如何分组
select * from emp group by post;
5.7以下版本的mysql会报错！！
mysql5.7及以上版本默认自带sql_mode=only_full_group_by，该模式要求分组之后默认只可以直接获取分组的依据不能直接获取其他字段，原因是分组的目的就是按照分组的条件来管理诸多数据，最小单位应该是分组的依据而不是单个单个的数据， 如果是MySQL5.6及以下版本需要自己手动添加。
聚合函数
专门用于分组之后的数据统计。
| 聚合函数 | 功能 |
|---|---|
| max | 统计最大值 |
| min | 统计最小值 |
| sum | 统计求和 |
| count | 统计计数 |
| avg | 统计平均值 |
分组题目：
# 1.统计每个部门的最高薪资
select post,max(salary) from emp group by post;
# 2.统计每个部门的平均薪资
select post,avg(salary) from emp group by post;
# 3.统计每个部门的员工人数
select post,count(id) from emp group by post;
# 4.统计每个部门的月工资开销
select post,sum(salary) from emp group by post;
# 5.统计每个部门最小的年龄数
select post,min(age) from emp group by post;
"""间接获取分组以外其他字段的数据"""
# 1.统计每个部门下所有员工的姓名
select post,group_concat(name) from emp group by post;
# 2.统计每个部门下所有员工的姓名和年龄
select post,group_concat(name,age) from emp group by post;
select post,group_concat(name,'|',age) from emp group by post;
"""字段起别名"""
select post,group_concat(name) as '姓名' from emp group by post;
select id as '序号',name as '姓名' from emp;
# as关键字也可以不写 但是语义不明确 建议加上
select id '序号',name '姓名' from emp;
作业
判断表关系
| 班级表 | |
|---|---|
| cid | caption |
| 1 | 一班 |
| 2 | 二班 |
| 3 | 三班 |
| 学生表 | |||
|---|---|---|---|
| sid | sname | gender | class_id |
| 1 | tom | 一年级 | 1 |
| 2 | kevin | 一年级 | 2 |
| 3 | jason | 一年级 | 3 |
| 老师表 | |
|---|---|
| tid | name |
| 1 | Alice |
| 2 | Bob |
| 课程表 | ||
|---|---|---|
| cid | cname | teacher_id |
| 1 | python | 1 |
| 2 | c++ | 2 |
| 成绩表 | |||
|---|---|---|---|
| sid | student_id | course_id | number |
| 1 | 1 | 1 | 80 |
| 2 | 2 | 1 | 60 |
| 3 | 3 | 2 | 90 |
关系判断：
班级表和学生表>>>一对多
老师表和课程表>>>一对一
课程表和成绩表>>>一对多
学生表和课程表>>>一对多
分组练习题
1. 查询岗位名以及岗位包含的所有员工名字
select post,group_concat(name) from emp group by post;
2. 查询岗位名以及各岗位内包含的员工个数
select post,count(id) from emp group by post;
3. 查询公司内男员工和女员工的个数
select sex,count(id) from emp group by sex;
4. 查询岗位名以及各岗位的平均薪资
select post,avg(salary) from emp group by post;
5. 查询岗位名以及各岗位的最高薪资
select post,max(salary) from emp group by post;
6. 查询岗位名以及各岗位的最低薪资
select post,min(salary ) from emp group by post;
7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
select sex as'性别',avg(salary) as '平均薪资' from emp group by sex;
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· Manus的开源复刻OpenManus初探
· AI 智能体引爆开源社区「GitHub 热点速览」
· 三行代码完成国际化适配，妙~啊~
· .NET Core 中如何实现缓存的预热？