MySQL 表的增删改查（进阶篇①）· 数据库约束 · 表的设计 · 新增查询结果 · 聚合查询 · 聚合函数 · group by · having · 笛卡尔积

一、数据库约束

1.1 约束类型

NOT NULL - 指示某列不能存储 NULL 值（非空）。
UNIQUE - 保证某列的每行必须有唯一的值（唯一）。
DEFAULT - 规定没有给列赋值时的默认值（默认）。
PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列有唯一标识，有助于更快地找到表中的一个特定记录（主键）。
FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性（外键）。

1.2 非空约束 not null

创建表时，可以指定某列不为空；

-- 设置 id 列不为空
create table students(id int not null, sn int, name varchar(20));

查看表结构.

一旦插入不符合约束的数据，会插入失败，避免问题进一步扩大。

1.3 唯一约束 unique

插入数据的时候，会先查找该列是否重复。

-- 创建一个 id 列为唯一约束的 stu 表.
create table stu(id int unique, name varchar(20));

插入违反约束的数据，会出现异常。

1.4 默认约束 default

将没有初始化 name 列，默认为 匿名。

当我们忽略插入该列数据，就会出现设置好的默认值啦~

1.5 主键约束 primary key

表示该列非空且唯一 ！

create table stu3(id int primary key, name varchar(20));

不能插入 null 数据；不能插入重复数据；不能设置多个主键。

1.6 自增主键 primary key auto_increment

很多场景下，主键都是一个单纯的数字。

我们可以借助 MySQL 提供的自增主键，每次输入数据的时候，不需要用户手动指定 id 的值了。

create table stu4 (id int primary key auto_increment, name varchar(20));

实际操作，可以看到设置自增主键的 id 列自动增加。

1.7 外键约束 foreign key

外键用于关联其它表的主键或唯一键，语法：

foreign key (字段名) references 主表(列)

案例：

创建班级表 classes，id 为主键

-- 创建班级表，有使用MySQL关键字作为字段时，需要使用``来标识
DROP TABLE IF EXISTS classes;
CREATE TABLE classes (
	id INT PRIMARY KEY auto_increment,
	name VARCHAR(20),
	`desc` VARCHAR(100)
);

创建学生表student，一个学生对应一个班级，一个班级对应多个学生。
使用id为主键，classes_id为外键，关联班级表id。

-- 重新设置学生表结构
DROP TABLE IF EXISTS student;
CREATE TABLE student (
	id INT PRIMARY KEY auto_increment,
	sn INT UNIQUE,
	name VARCHAR(20) DEFAULT 'unkown',
	qq_mail VARCHAR(20),
	classes_id int,
	FOREIGN KEY (classes_id) REFERENCES classes(id)
);

注意事项：

1. 如果给 student 插入的数据中，在对应的 classId 在 class 表中不存在，此时会插入失败。
2. 有了外键约束，导致两个表不能随便删除。

---

二、表的设计

学习设计表的通用手段，抓住实体和实体之间的关系。

而实体之间的关系，一般有四种状态：没关系、一对一、一对多、多对多。

一对一
一个人只有一个身份证，一个身份证对应一个人。

一对多
一个班级拥有多个学生，一个学生只能有一个班级。

多对多
一门课程可以被多个学生选择，学生也可以选择多个课程。

---

三、结合操作，新增查询结果

插入查询结果.

案例：创建一个学生表 stu1，设计有 id，sn，name 字段。将已有的学生数据从 student 表中，复制到新学生表 stu1 中。

student 表的数据.

插入查询.

insert into stu1(id,sn,name) select id,sn,name from student;

最后结果 stu1 表.

---

四、聚合查询

4.1 聚合函数

常见的统计总数、计算平均值等操作，可以使用聚合函数来实现。

函数	说明
COUNT	返回查询到的数据总量
SUM	返回查询数据的总和，不是数字无意义
AVG	返回查询数据的平均值，不是数字无意义
MAX	返回查询数据的最大值，不是数字无意义
MIN	返回查询数据的最小值，不是数字无意义

COUNT

-- 统计 student 表有几个同学
select count(*) from student;

-- 统计表中有 qq_mail 的同学，qq_mail 为 NULL 的数据不会计入结果
select count(qq_mail) from student;

SUM

-- 统计数学成绩总分
SELECT SUM(math) FROM exam_result;

-- 不及格 < 60 的总分，没有结果，返回 NULL
SELECT SUM(math) FROM exam_result WHERE math < 60;

AVG

-- 统计平均总分
select AVG(chinese + math + english) 平均总分 from exam_result;

MAX

-- 返回英语最高分
select MAX(english) from exam_result;

MIN

-- 返回>70分的数学最低分
select MIN(math) from exam_result where math > 70;

4.2 GROUP BY 子句

select 语句中使用 group by 子句可以对指定列进行分组查询。

案例：准备测试表以及数据，职员表emp。

create table emp(
	id int primary key auto_increment,
	name varchar(20) not null,
	role varchar(20) not null,
	salary numeric(11,2)
);

insert into emp(name, role, salary) values
	('马云','服务员', 1000.20),
	('马化腾','游戏陪玩', 2000.99),
	('孙悟空','游戏角色', 999.11),
	('猪无能','游戏角色', 333.5),
	('沙和尚','游戏角色', 700.33),
	('隔壁老王','董事长', 12000.66);

-- 查询每个角色的最高工资、最低工资和平均工资
select role,max(salary),min(salary),avg(salary) from emp group by role;

4.3 HAVING

group by 子句进行分组之后，如果需要对分组结果再进行条件过滤，不能使用 where 语句，而是要使用 having.

-- 显示平均工资低于 1500 的角色和它的平均工资
select role,max(salary),min(salary),avg(salary) from emp group by role having avg(salary)<1500;

4.4 联合查询 - 笛卡尔积

实际开发中往往数据来自不同的表，所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积。

初始化测试数据：一共有四张表，学生表student，课程表course，班级表classes，成绩表score。

drop table if exists classes;
drop table if exists student;
drop table if exists course;
drop table if exists score;

create table classes (id int primary key auto_increment, name varchar(20), `desc` varchar(100));

create table student (id int primary key auto_increment, sn varchar(20),  name varchar(20), qq_mail varchar(20) ,
        classes_id int);

create table course(id int primary key auto_increment, name varchar(20));

create table score(score decimal(3, 1), student_id int, course_id int);

insert into classes(name, `desc`) values 
('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'),
('中文系2019级3班','学习了中国传统文学'),
('自动化2019级5班','学习了机械自动化');

insert into student(sn, name, qq_mail, classes_id) values
('09982','黑旋风李逵','xuanfeng@qq.com',1),
('00835','菩提老祖',null,1),
('00391','白素贞',null,1),
('00031','许仙','xuxian@qq.com',1),
('00054','不想毕业',null,1),
('51234','好好说话','say@qq.com',2),
('83223','tellme',null,2),
('09527','老外学中文','foreigner@qq.com',2);

insert into course(name) values
('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');

insert into score(score, student_id, course_id) values
-- 黑旋风李逵
(70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
-- 菩提老祖
(60, 2, 1),(59.5, 2, 5),
-- 白素贞
(33, 3, 1),(68, 3, 3),(99, 3, 5),
-- 许仙
(67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
-- 不想毕业
(81, 5, 1),(37, 5, 5),
-- 好好说话
(56, 6, 2),(43, 6, 4),(79, 6, 6),
-- tellme
(80, 7, 2),(92, 7, 6);

简单的将两个表进行笛卡尔积

select * from student,score;

部分运行结果：

一共 160 行数据，我们观察可以发现。
笛卡尔积将 student 表和 score 表进行合并了，新表的列数是两个表的列数之和；新表的行数是两张表的行数之积。

有效数据和无效数据都杂糅在一起。我们需要将无效数据剔除。

两个表都有 student 中的 id，我们将两个表的 student 的 id 对应起来。

select * from student, score where student.id=score.student_id;

筛选出来 20 条有效数据，这时候的表的含义是，每个同学的每门课的成绩。
假设我们只需要 “许仙” 的每门课成绩，要如何操作？

我们只需要在刚才的基础上，再加上一个条件，按照名字筛选下这批数据即可。

 select * from student,score where student.id=score.student_id and name='许仙';

4.5 小结

多表查询/联合查询，本质就是 “先做加法，再做减法”。

先把所有可能性通过笛卡尔积罗列出来，然后再设定条件筛选，设定条件的时候，一定至少要指定一个连接条件（两个表之间得有一个带关联关系的 id ）

一般实际开发中，谨慎使用多表查询，因为多表查询是一个效率较低的操作。