MySQL表的增删改查(进阶)
文章目录
前言
下面是 MySQL 中进阶版的增删查改
一、数据库约束
1.约束类型
- not null — 表示某列不能存储 null 值(非空)
- unique — 保证某列的每行都必须有唯一的值(后续插入/修改数据,都会先触发一次查询操作)
- default — 规定没有给列赋值时的默认值
- primary key — 每一行记录的身份标识(主键)
- foreign key — 外键,描述两个表之间的关联关系
示例 :
create table student(
-> id int not null,
-> sn int unique,
-> name varchar(20) default 'unkonwn',
-> qq_mail varchar(20)
-> );
主键和外键示例:
创建学生表 student,一个学生对应一个班级,一个班级对应多个学生。
因为学生表和班级表有这样的关系(下面会进行介绍),所以我们要将学生表和班级表进行关联起来,使用班级表中的 id 为主键,学生表中的 classes_id 为外键。
create table classes(
-> id int primary key auto_increment,
-> name varchar(20)
-> );
create table student(
-> id int primary key auto_increment,
-> sn int unique,
-> name varchar(20) default 'unknown',
-> qq_mail varchar(20),
-> classes_id int,
-> foreign key (classes_id) references classes(id)
-> );
这样就可以将学生表和班级表建立起联系。
二、表的设计
在数据库中存在三大范式,即一对一、一对多和多对多。
多对多需要借助一个关联表,通过一个中间表来关联其他的表,这样就可以建立各个表之间的联系了。(相当于是你通过朋友来结交认识了他的朋友,多个朋友多条路嘛)。
示例:
create table score(
id int primary key auto_increment,
score decimal(3,1),
student_id int,
course_id int,
foreign key (student_id) references student(id),
foreign key (course_id) references course(id)
);
一个学生可以选择多门课程,一门课程也可以包含多个学生,这就是形成了多对多的关系。
示例中就是通过学生课程中间表:考试成绩表 score ,通过两个外键将学生表和课程表建立起了连接。
- 表的设计:
1)找到实体
2)明确实体之间的关系
a)一对一
b)一对多
c)多对多
三、查询(难点)
1.聚合查询
1.1 聚合函数
sql 中提供了一些“聚合函数”,通过聚合函数来完成行和行之间的运算。
函数 | 说明 |
---|---|
count ( ) | 返回查询到的数据的数量 |
sum ( ) | 返回查询到的数据的总和,不是数字没有意义 |
avg( ) | 返回查询到的数据的平均值,不是数字没有意义 |
max( ) | 返回查询到的数据的最大值,不是数字没有意义 |
min( ) | 返回查询到的数据的最小值,不是数字没有意义 |
示例:
select count(name) from student;
select sum(chinese + math + english) from exam_result;
select avg(chinese + math + english) from exam_result;
select amx(english) from exam_result;
semect min(math) from exam_result;
- sum 聚合函数是先把对应的列相加,得到一个临时表;再对这个临时表的结果进行行和行的相加操作。
1.2 group by 子句
使用 group by 进行分组,针对每个分组,再分别进行聚合查询。针对指定的列进行分组,把这一列中相同值的行分到一组中,得到若干个组,针对这些组,再分别使用聚合查询。
示例:
create table emp(
-> id int primary key auto_increment,
-> name varchar(20),
-> role varchar(20),
-> salary int
-> );
insert into emp(name,role,salary) values
-> ('张三','程序员',8000),
-> ('李四','程序员',10000),
-> ('王五','产品经理',9000),
-> ('赵六','老板',100000),
-> ('田七','测试',11000),
-> ('周八','程序员',13000);
当我们想要知道大家的平均薪资时,上面此时查询到的是所有人的平均薪资,显然这并不合理,老板的收入大大拉高了大家的平均薪资,所以我们在查询前要先进行分组。
在求大家的平均薪资这样的需求下,先分组再求平均值更加合理多了。
1.3 having
- group by 子句进行分组以后,需要对分组结果再进行条件过滤时,不能使用 where 语句,而需要用 having 来描述条件。
- where 语句是在分组前先进行条件过滤。
示例:
1、查询每个岗位的平均薪资,但是排除张三。(分组前的条件)
2、查询每个岗位的平均薪资,但是排除平均薪资超过3w的 结果。(分组后的条件)
select role,avg(salary) from emp where name != '张三' group by role;
select role,avg(salary) from emp group by role having avg(salary) < 30000;
- 也可以同时存在 where 语句和 having 子句
示例:
2.联合查询
2.1 笛卡尔积
实际开发中往往数据来自不同的表,所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积:笛卡尔积的元素数量等于两个集合的元素数量的乘积。如果集合A有m个元素,而集合B有n个元素,那么A×B就有m×n个元素。
- 笛卡尔积是通过排列组合的方式,得到的一个更大的表。
- 笛卡尔积的列数是这两个表的列数相加。
- 笛卡尔积的行数是这两个表的行数相乘。
2.2 内连接
在进行联合查询前。我们先创建出四张表。学生,课程,班级;而分数表就是学生和课程之间的关联表。
案例:
1、查询“小美”同学的成绩:
1)先把这两个表,进行笛卡尔积(student 和 score)
select * from student,score;
2)加上连接条件,筛选有效数据(学生表的 id 和 student_id )
select * from student,score where student.id = score.student_id;
3)结合需求,进一步添加条件,针对结果进行筛选。
select * from student,score where student.id = score.student_id and student.name = '小美';
4)针对查询到的列进行精简,只保留需求中关心的列。
select studen.name,score.score from student,score where student.id = score.student_id and student.name = '小美';
2、查询所有同学的总成绩,及同学的个人信息:
3、查询所有同学的成绩,及同学的个人信息:
2.3 外连接
如果两张表里面的记录都是存在对应关系,内连接和外连接的结果是一致的。如果存在不对应的记录,内连接和外连接就会出现差别。
案例:查询所有同学的成绩,及同学的个人信息,如果该同学没有成绩,也需要显示
-- 左外连接
select * from student stu left join score sco on stu.id=sco.student_id;
-- 右外连接
select * from score sco right join student stu on stu.id=sco.student_id;
左外连接,就是以左侧表为基准,保证左侧表中的每个数据都会出现在最后的结果中里,如果在右侧表中不存在,对应的列就填成null。
四、练习题(自测)
练习一:
有员工表、部门表和薪资表,根据查询条件写出对应的sql
现在有员工表、部门表和薪资表。部门表depart的字段有depart_id, name;员工表 staff 的字段有 staff_id, name, age, depart_id;薪资表salary 的字段有 salary_id,staff_id,salary,month。
(问题a):求每个部门’2016-09’月份的部门薪水总额
select depart.name,sum(salary.salary) from depart,salary,staff where
depart.depart_id = satff.depart_id and salary.satff_id = staff.staff_id
and year(salary.moth) = 2016 and month(salary.month) = 9
group by depart.depart_id;
说明:
mysql中年和月的函数分别是year(字段),month(字段)
(问题b):求每个部门的部门人数,要求输出部门名称和人数
select depart.name,sum(staff.staff_id) from depart.depart_id = staff.depart_id
group by staff.depart_id;
(问题c):求公司每个部门的月支出薪资数,要求输出月份和本月薪资总数
select depart.name,salary.month,sum(salary.salary) from
depary.depart_id = stff.depart_id and salary.staff_id = staff.staff_id
group bydepart.depart_id,salary.month;
总结
这里的进阶部分主要总结了:
1、数据库约束(主键约束和外键约束)
2、表的关系(一对一、一对多、多对多)
3、聚合查询和分组查询
4、联合查询