一文帮你玩转MySQL表—增，删，查，改（进阶）
目录
1. 数据库约束
1.1 约束类型
· NOT NULL 指示某列不能存储NULL值
· UNIQUE 保证某列必须有唯一的值
· DEFAULT 规定没有给列赋值时的默认值
· PRIMARY KEY ，NOT NULL和UNIQUE的结合，确保某列有唯一标识，有助于更容易更快捷地找到表中的一个特定记录
· FOREIGN KEY 保证一个表中的数据匹配另一个表中的值的参照完整性
· CHECK 保证列中的值符合指定的条件
1.2 NOT NULL约束
创建表时，可以指定某列不为空：
create table student(
id int NOT NULL, --id不为空
name varchar(10),
sex varchar(1),
age int
);1.3 UNIQUE 唯一约束
指定id列为唯一的，不重复：
drop table if exists student;
create table student(
id int UNIQUE, --id为唯一的
name varchar(10),
sex varchar(1),
age int
);1.4 DEFAULT 默认值约束
指定插入数据时，如果name列为空，则将默认值设为unkown：
drop table if exists student;
create table student(
id int UNIQUE,
name varchar(10) DEFAULT 'unkown',
sex varchar(1),
age int
);1.5 PRIMARY KEY 主键约束
指定id列为主键：
drop table if exists student;
create table student(
id int PRIMARY KEY,
name varchar(10) DEFAULT 'unkown',
sex varchar(1),
age int
);对于整数类型的主键，常搭配自增长auto_increment来使用，插入数据对应字段不给值，使用最大值+1
id int PRIMARY KEY auto_increment,1.6 FOREIGN KEY 外键约束
外键用于关联其他表的主键或唯一值
语法：
foreign key (字段名) references 主表(列);示例：
创建班级表：
-- 创建班级表
drop table if exists classes;
create table classes(
id int primary key auto_increment,
name varchar(20)
);创建学生表，一个学生对应一个班级，一个班级对应多个学生，id为主键，classes_id为外键，关联班级表id：
-- 创建学生表来关联班级表
drop table if exists student;
create table student(
id int PRIMARY KEY,
name varchar(10) DEFAULT 'unkown',
sex varchar(1),
classes_id int,
foreign key (classes_id) references classes(id)
);
1.7 CHECK约束（了解）
MySQL使用时不报错，但忽略该约束：
create table test_user (
id int,
name varchar(10),
sex varchar(1),
check (sex = '男' or sex = '女')
);2. 表的设计
三大范式：
一对一：
一对多：
多对多：
创建课程表：
drop table if exists course;
create table course (
id int primary key auto_increment,
name varchar(20)
);创建学生课程中间表，考试成绩表：
drop table if exists score;
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)
);3. 新增
插入查询的结果
语法：
INSERT INTO table_name [(column [, column ...])] SELECT ...示例：
新建一张user表：
create table user (
id int primary key auto_increment,
name varchar(10),
sex varchar(1),
age int,
email varchar(20)
);将学生表的数据复制到user表中：
insert into user (name,sex,age) select name,sex,age from student;4. 查询
4.1 聚合查询
4.1.1 聚合函数
常见的统计总数，计算平均值等操作，可以使用聚合查询来实现，常见的聚合函数：
| 函数 | 说明 |
| COUNT([DISTINCT] expr) | 返回查询到的数据的数量 |
| SUM([DISTINCT] expr) | 返回查询到的数据的总和 |
| AVG([DISTINCT] expr) | 返回查询到的数据的平均值 |
| MAX([DISTINCT] expr) | 返回查询到的数据的最大值 |
| MIN([DISTINCT] expr) | 返回查询到的数据的最小值 |
示例：
· COUNT
-- 统计班级有多少个同学
select count(*) from student;
select count(0) from student;· SUM
-- 统计学生的数学总成绩
select sum(math) from student_score;
-- 统计不及格学生的数学总成绩
select sum(math) from student_score where math<60;· AVG
-- 统计平均总分
select avg(chinese+math+english) from student_score;· MAX
-- 找出英语的最高成绩
select max(english) from student_score;· MIN
-- 找出语文的最低成绩
select min(chinese) from student_score; 4.1.2 GROUP BY
select中使用group by子句可以对指定列进行分组查询，需要满足：使用group by 进行分组查询时，select指定的字段必须是“分组依据字段”，其他字段要想出现在select中必须包含在聚合函数中。
语法：
select column1, sum(column2), .. from table group by column1,column3;案例表：
create table emp (
id int primary key auto_increment,
name varchar(10),
role varchar(10) comment '角色',
salary decimal(10,2) comment '薪资'
);
insert into emp (name,role,salary) values
('小王','员工',3000.50),
('小贺','老板',200000.00),
('小张','秘书',15000),
('小方','保洁员',3000),
('小乔','员工',4500.20),
('小李','员工',5000.28);
查询每个角色的最高工资，最低工资和平均工资：
select role,max(salary),min(salary),avg(salary) from emp group by role;结果：
4.1.3 HAVING
GROUP BY子句进行分组后，如果要对分组后的结果进行条件过滤不能使用WHERE，要使用HAVING语句。
示例：显示平均工资低于4000的角色，和他的平均工资：
select role,avg(salary) from emp group by role having avg(salary)<4000;
结果：
4.2 联合查询
先将后续用到的表和数据给出：班级表，学生表，课程表，分数表
drop table if exists classes;
create table classes (
id int primary key auto_increment,
name varchar(20)
);
insert into classes (name) values
('计算机1班'),
('自动化2班'),
('机械3班');
drop table if exists student;
create table student (
id int primary key auto_increment,
name varchar(10),
classes_id int,
foreign key (classes_id) references classes(id)
);
insert into student (name,classes_id) values
('小花',2),
('小张',1),
('小贺',1),
('小方',3),
('小乔',3);
drop table if exists course;
create table course (
id int primary key auto_increment,
name varchar(20)
);
insert into course (name) values
('java程序设计'),
('大学英语'),
('高等数学'),
('数据结构'),
('工程制图');
drop table if exists score;
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)
);
insert into score (score,student_id,course_id) values
-- 小花
(98.5,1,3),(80,1,5),
-- 小张
(99,2,1),(95,2,2),(96,2,3),(90,2,4),(93,2,5),
-- 小贺
(85,3,1),(86,3,2),(86,3,3),(95,3,4),
-- 小方
(70,4,3),(65,4,5),
-- 小乔
(65,5,5),(67,5,3),(68,5,2); 实际的开发中，数据来自不同的表，这时候需要多张表联合查询，多表查询是对多张表的数据取笛卡尔积。
笛卡尔积：
注意：关联查询可以对关联表使用别名
4.2.1 内连接
语法：
select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;示例：查询“小张”同学的成绩：
select
stu.id,
stu.name,
sco.score
from
student stu
join score sco on stu.id = sco.student_id
and stu.name='小张';结果：
示例：查询所有同学的总成绩，及同学的个人信息：
select
stu.id,
stu.name,
sum(sco.score)
from
student stu
join score sco on stu.id = sco.student_id
group by
stu.id;结果：
示例：查询所有同学的成绩，及同学的个人信息：
select
stu.id,
stu.name,
sco.score,
sco.course_id,
cou.name
from
student stu
join score sco on stu.id = sco.student_id
join course cou on sco.course_id = cou.id;结果：
4.2.2 外连接
外连接分为左外连接和右外连接，如果联合查询，左侧的表完全显示就是左外连接，右侧的表完全显示就是右外连接。
语法：
-- 左外连接，表1完全显示
select 字段名 from 表名1 left join 表名2 on 连接条件;
-- 右外连接，表2完全显示
select 字段 from 表名1 right join 表名2 on 连接条件;4.2.3 自连接
自连接是指在同一张表连接自身进行查询
示例：显示所有“大学英语”比“高等数学”成绩高的信息：
select
stu.*,
s1.score 大学英语,
s2.score 高等数学
from
score s1
join score s2 on s1.student_id = s2.student_id
join student stu on s1.student_id = stu.id
join course c1 on s1.course_id = c1.id
join course c2 on s2.course_id = c2.id
and s1.score > s2.score
and c1.name = '大学英语'
and c2.name = '高等数学';结果：
4.2.4 子查询
子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询
· 单行子查询：返回一行记录的子查询
示例：查询与“小张”在同一个班的同学
select
*
from
student
where
classes_id = (
select
classes_id
from
student
where
name = '小张'
);结果：
· 多行子查询：返回多行记录的子查询
示例：查询“高等数学”或“大学英语”课程的成绩信息
1. [NOT] IN 关键字
-- 使用IN
select * from score where course_id in (
select id from course where name='大学英语' or name='高等数学'
);
-- 使用NOT IN
select * from score where course_id not in (
select id from course where name != '高等数学' and name != '大学英语'
);2. [NOT] EXISTS 关键字
-- 使用exists
select
*
from
score sco
where
exists (
select
sco.id
from
course cou
where
(
name = '高等数学'
or name = '大学英语'
)
and cou.id = sco.course_id
);
-- 使用not exists
select
*
from
score sco
where
not exists (
select
sco.id
from
course cou
where
(
name != '高等数学'
and name != '大学英语'
)
and cou.id = sco.course_id
);· 在from子句中使用子查询：子查询语句出现在from子句中。这里要用到数据查询的技巧，把一个 子查询当作一个临时表来使用
注意：临时表必须起别名，临时表也可以当作一张虚拟表来关联查询
示例：查询所有比自动化2班平均分高的成绩信息
select
*
from
score sco,
(
select
avg(sco.score) score
from
score sco
join student stu on sco.student_id = stu.id
join classes cls on stu.classes_id = cls.id
where
cls.name = '自动化2班'
) tmp
where
sco.score > tmp.score;结果：
4.2.5 合并查询
在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all。使用UNION和UNION ALL时，前后查询的结果集中，字段需要一致。
· UNION
该操作符取得两个结果集的并集，会自动去掉结果集中的重复行
示例：查询id小于3，或者名字为“高等数学”的课程
select * from course where id < 3
union
select * from course where name = '高等数学';结果：
· UNION ALL
该操作符取两个结果集的并集，但是不会去掉结果集中的重复行
示例：查询id小于3，或者名字为“java程序设计”的课程
select * from course where id < 3
union all
select * from course where name = 'java程序设计';结果：