MySQL表的增删改查(进阶)

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、联合查询