玩转MYSQL数据库的约束、聚合查询、联合查询以及三种表的设计
一、MYSQL的那些约束你掌握了几种？
在MYQSL中，有那么一些约束。比如不能为null，或者是必须唯一的。就像身份证一样，每个人的身份证号一定是唯一的，性别可以是男，也可以是女，但不是为null。
下面介绍MYSQL中的6种约束
1、NOT NULL ：约束某列不能为空。
-- 创建一个图书表，id不能为空create table book(id int not null);
2、UNIQUE : 约束某列的某行有唯一值。
-- 创建一个图书表，id唯一，不能重复create table book(id int unique);
3、DEFAULT : 没有给列赋值时给一个默认值。
-- 创建一个语文书表，默认name为unknowncreate table book(name varchar(20) default'unknown');
4、PRIMARY KEY ：NOT NUL和 UNIQUE的结合，即不能为空， 值必须唯一。一般表的ID会设成primary key，这样的好处是为表创立一个唯一的标识。能够更方便的找到表中特殊的信息。一般常与auto_increment连用，表示自增主键，
-- 创建一个图书，每个图书有自己的名称，价格，但序列号是唯一的create table book(id int primary key auto_increment,name varchar(20),price int );
5、FOREIGN KEY :外键约束。一个表的数据和另一个表匹配，增加耦合性。
-- 创建班级表，create table class(id int primary key auto_increment,name varchar(20));-- 学生表,一个学生对应一个班级，一个班级可以有多个学生create table student(id int primary key auto_increment,name varchar(20),classes_id int ,foreign key (classes_id) references class(id));
6、CHECK : 保证列中的值符合特定的条件。
-- MySQL使用时不报错，但忽略该约束：create table student(id int primary key auto_increment,name varchar(20),sex varchar(1),check (sex='男' or sex='女'));
二、表与表之间的三种关系
1、一对一关系 eg：人和身份照号码
一个人只能拥有一个身份证号码 2、一对多关系 eg:学生和班级的关系
一个班级可以有多个学生，一个学生只属于一个班级 3、多对多关系 eg：老师和班级的关系
一个班级可以有多个老师，一个老师也可以教多个班。
三、查询
3.1 :聚合查询
聚合查询通过4中聚合函数完成。他们分别是：** 1、count() ：返回查询到的数量，和查询类型是不是数字无关。
-- 查询图书表有多少书select count(*) from book;
2、sum() ：返回查询数据的总和，如果数据不是数字就没有意义
-- 查询三年二班有多少学生select sum(student) from class;
3、avg() : 返回查询的数据的平均数，如果数据不是数字没有意义
-- 查询三年二班学生数学的平均分select avg(math) from class;
4、max() : 返回查询的数据的最大值，如果数据不是数字没有意义
-- 查询三年二班数学的最高分select max(math) from class
5、min(): 返回查询的数据的最小值，如果数据不是数字没有意义
-- 查询三年二班数学的最低分select min(math) from class;
3.2、GROUP BY
使用group by字句可以对指定列进行分组查询。但是需要满足，select后的 字段必须是分组依据的字段。如果其他字段想出现就必须使用聚合函数。
eg: 准备数据，创建一个学生表，含有学生id，姓名，数学和语文成绩。
create table student(-- 学生idid int primary key auto_increment,-- 学生姓名student_name varchar(20),-- 学生数学成绩math int,-- 学生语文成绩chinese int);
插入数据
insert into student(student_name,math,chinese) values('张三',66,67),('李四',44,98),('王五',88,89);
查看当前表的数据
使用GROUP BY查询，学生的最高、平均、最低数学成绩
3.3、HAVING和group by 搭配使用
在使用group by 字句后，如果还需要条件筛选的话，就不能用where了，需要用having来筛选条件。
-- 查询总成绩大于150的同学select student_name , sum(math+chinese) from student group by student_name having sum(math+chinses)>150;
结果只有王五同学总分大于150.
3.4、联合查询
上面的查询都是在一张表中进行查询，但实际应用的过程中，经常是在几张表中查询数据，这就用到了联合查询。多张表查询就是对这些表取笛卡尔积，在筛选有用的数据。学习联合查询之前，需要先了解什么笛卡尔积。
总得来说，笛卡尔积就是罗列出两张表所有可能出现的数据。
准备数据：
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','黑旋风李逵','[email protected]',1),('00835','菩提老祖',null,1),('00391','白素贞',null,1),('00031','许仙','[email protected]',1),('00054','不想毕业',null,1),('51234','好好说话','[email protected]',2),('83223','tellme',null,2),('09527','老外学中文','[email protected]',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);
3.4.1内连接
语法：1、select 字段名 from 表1 +别名(可不写) inner join 表2+别名(可不写) on 连接条件 and 其他条件
示例：查询白素贞同学的成绩查询所有同学的总成绩： 当我们写一些比较复杂的SQL语句时，非常建议大家不要一次性写完。这样很容易写错，而SQL调试不了，所以在实际写的时候可以像下面这样，一行一行的写。每次写一个条件，这样就很清楚了。
select student.sn,student.name,student.qq_mail，sum(score.score) from studentjoin scoreon student.id=score.student_idgroup by score.student_id;
2、select 字段名 from 表1 +别名(可以不写) ，表2+别名(可以不写) where 连接条件 and 其他条件
示例：查询白素贞同学的成绩：
查询所有学生的qq邮箱和总成绩:
这两种方式查询的结果相同。
3.4.2 外连接
内连接和外连接的区别，在于对"空值"的区别。如果表里没有"空值"(不是单纯的NULL，而是泛指两张表的数据对不上)，那内连接和外连接就没有区别了。
eg: 先删除成绩和学生表，新建一份在加上3个同学。
create table student(id int primary key auto_increment,name varchar(20));create table score(studentId int ,score int );insert into student values(null,'张三');insert into student values(null,'李四');insert into student values(null,'王五');
先来看一下当前表的数据
select * from student;再给成绩表中插入三行数据，但是做一点点区别。
insert into score values(1,55);insert into score values(2,85);insert into score values(4,91);
这里故意将本该是3的王五的id改成4。这时候发生了数据不对应的情况。王五同学在分数表中没有成绩，id为4的同学在学生表中，没有信息。
这时候如果我们查询两张表的结果会怎么样呢？查询每位同学的成绩，姓名信息。
select student.name,score.score from student joinscore on student.id=score.studentId;
内连接最后的结果既没有王五同学的成绩和姓名信息，也没有id为4的同学的信息。
内连接相当于对上面两张表取了交集。
外连接相对于内连接来说，分为左外连接和右外连接。在写法上和内连接很相似，只需要在原来的语句中对于join前再加上left或者是right就表示内连接或者外连接了
左外连接：最终结果以join左侧的表为主，尽可能的显示左侧表的信息。
select student.name,score.score from student leftjoinscore on student.id=score.studentId;
右外连接：最终结果以join右侧的表为主，尽可能的显示右侧表的信息。
select student.name,score.score from student rightjoinscore on student.id=score.studentId;
3.4.3 自连接
自连接就是同一张表连接自身进行查询。
eg：查询语文成绩比英语成绩高的信息
这次查询和以往不同的是这是针对行和行的查询，之前的例如查询每位同学的成绩，姓名信息。这都是列与列之间的关系。
既然不好解决问题，不妨换个思路，那我就不进行行和行比较了，我想办法转换成列和列比较。
1、先看看它的笛卡尔积长什么样 需要注意的是如果你直接select * from score,score ;查询的话是会报错的。需要你使用as起一个别名。
select * from score as a,score as b;最终结果有四百行，我这里放一部分。
接下来在进一步筛选有用的信息，挑选出同一个同学的信息
select * from score as a,score as b where a.student_id=b.student_id;现在缩减成了62行最后直接一步到位，但是需要先回头看看语文和英语的id，一个是4，另一个是6
insert into course(name) values('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');
这样我们在通过课程id筛选
select * from score as a,score as bwhere a.student_id=b.student_id and a.course_id=4 and b.course_id=6;
最终结果就只剩下一了。再来练习一下java比计算机原理分数高的同学的信息，我们这次直接到上图的那一步。
select * from score as a,score as bwhere a.student_id=b.student_id and a.course_id=1 and b.course_id=3;
course_id为1的是java的分数，course_id为3的是计算机原理的分数这样下来，一行不仅包含了java的列，还包含了计算机原理的列。通过自连接就完成了行转列的效果。
来看看最终结果，计算机原理成绩比JAVA高的信息
select * from score as a,score as bwhere a.student_id=b.student_id and a.course_id=1 and b.course_id=3 and a.score<b.score;
3.5、子查询
子查询就是其他sql语句中的查询语句。所以也叫做嵌套查询。
3.5.1单行子查询
返回一行子查询的就是单行子查询。eg：查询和“不想毕业”同学的同班同学。
还是一步步来，先查询不想毕业同学的班级id。
select classes_id from student where name='不想毕业'不想毕业同学在一班。在通过一班这个信息找他的同学。
select name from student where classes_id=1;我们用两次查询得到了想要的结果，那试试子查询怎么样。
select name from student whereclasses_id=(select classes_id from student where name='不想毕业');
在上面的SQL语句中先执行第二个SQL语句作为前面查询name的条件，这就是子查询。
3.5.2多行子查询
多行子查询返回的是多条记录
eg：查询语文或者英语成绩的信息 1、先查询语文或者英语成绩的课程id
select id from course where name='语文' or name='英文';2、再在课程表中，根据课程id找对应的信息
select * from score where course_id=4 or course_id=6;再用多行子查询试试多行子查询得到的是多条记录，外层查询需要使用in关键字。单行子查询只返回一条记录直接用=即可。
select * from score where course_id in(select id from course where name='语文' or name='英文');
3.6、合并查询
把多个select查询的结果合并成一个结果就是合并查询。有些像取并集。它包含两个关键字union : 针对重复的进行去重 union all 不会针对重复的去重 union这有一个前提是两边的查询得到的结果的列是一致的，包括数量和类型。
eg：查询id小于3，或者名字为“英文”的课程
select * from course where id<3 or name ='英文';使用union查询
select * from course where id<3 union select * from course where name='英文';效果是一样的。
四、结尾
上面就是这次的所有内容了，祝大家工作顺利，学习顺利。冲冲冲
作者：MR_SEEKS
链接：https://juejin.cn/post/7012543888913072142