MySQL五种约束类型(普通 /自增主键,外键等) + 进阶查询(聚合查询,内 /外连接查询,自连接查询,子查询,合并查询)

---

前言

本人是一个普通程序猿!分享一点自己的见解,如果有错误的地方欢迎各位大佬莅临指导,如果你也对编程感兴趣的话，互关一下，以后互相学习，共同进步。这篇文章能够帮助到你的话,劳请大家点赞转发支持一下!

---

一、五种约束

约束类型	说明
NOT NULL	指示某列不能存储 NULL 值
UNIQUE	保证某列的每行必须有唯一的值
DEFAULT	规定没有给列赋值时的默认值
PRIMARY KEY	NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录
FOREIGN KEY	保证一个表中的数据匹配另一个表中的值的参照完整性

---

NOT NULL 约束

• NOT NULL 约束，在创建表时，指定某个字段不能为NULL。

创建一个学生表，有三个字段，id(学号)，name(名字)，age(年龄)；指定id字段不能为NULL。

create table student (id int NOT NULL,name varchar(10),age int);

表结构

插入示例

---

UNIQUE 约束

• UNIQUE 约束，在创建表时，指定某个字段数据是唯一的，不能重复。

创建一个学生表，有三个字段，id(学号)，name(名字)，age(年龄)；指定id字段是唯一的，不能重复。

create table student (id int UNIQUE,name varchar(10),age int);

表结构

插入示例

---

DEFAULT 约束

• DEFAULT 约束，在创建表时，设置某个字段插入数据时为空的默认值。
  (默认值是insert语句指定字段插入时，其他未被指定到的字段就是按照默认值来填充)

创建一个学生表，有三个字段，id(学号)，name(名字)，age(年龄)；指定name字段为空时，给定一个 “无名氏” 默认值。

create table student(id int,name varchar(10) DEFAULT "无名氏",age int);

表结构

插入示例

---

PRIMARY KEY 主键约束(重点)

普通主键

每个表中只能有一个主键

• PRIMARY KEY 主键约束，在创建表时，指定 某个字段或几个字段综合起来 插入数据不能为NULL，不能重复。

主键类似于NOT NULL + UNIQUE；此时这个主键就等同于这条数据独一无二的身份标识。
独一无二的身份标识，就类似于咱们的身份证号码，即使重名，出生日期也是同一天，依旧可以凭借身份证号码，找到那个独一无二的你。

创建一个学生表，有三个字段，id(学号)，name(名字)，age(年龄)；指定id字段为主键。

create table student (id int PRIMARY KEY,name varchar(10),age int);

表结构

插入示例

当然，此处MySQL只是实现了检查校验的工作，当插入数据粗心时，仍会报错。
但是对于字段类型为int的主键，MySQL贴心的提供了自增主键，来帮你解决。

---

自增主键

只有int类型的字段可以设置为自增主键

• 自增主键与普通主键的区别是当你插入数据时， 自增主键的字段为NULL或没有为这个字段插入数据时 ，会 自动给定一个值：当前该列最大值+1(如该列没有数据时，默认是0)

创建一个学生表，有三个字段，id(学号)，name(名字)，age(年龄)；指定id字段为自增主键。

create table student (id int PRIMARY KEY auto_increment,name varchar(10),age int);

表结构

插入示例

---

FOREIGN KEY 外键约束(重点)

• 外键用于关联其他表的主键或唯一键。

说明
假设：每个班级有一个班级表(class)，每个学生有一个学生表(student)

class表中的学号字段是主键，每个同学都有唯一不重复的学号。
student表当中的学号必须在对应的班级表中的学号字段中存在。

此时，student表当中的学号字段就可以当成外键来处理，关联的是class表中的学号字段。
如果插入数据时， 外键关联的表中的对应字段中没有匹配到相同数据，则插入失败报错 在。

class是student的父表 ，
student是class的子表 。

创建一个班级表，有两个字段，class_id(班级表中的学号)，name(名字)；指定class_id字段为自增主键。

create table class(class_id int PRIMARY KEY auto_increment,name varchar(10));

创建一个学生表，有三个字段，student_id(学生表中的学号)，name(名字)，score(成绩)，class_id(班级表中的学号)；指定class_id字段为外键，关联class表中的class_id字段。

create table student(student_id int PRIMARY KEY auto_increment,name varchar(10),score int,class_id int,FOREIGN KEY(class_id) REFERENCES class(class_id));

表结构
class表

student表

插入示例

向子表插入数据时，MySQL会拿着你要插入的外键字段数据，去绑定的父表中的字段去匹配，匹配成功，才能完成后续插入，匹配失败，插入失败。

子表约束父表
以上都是父表在约束子表，但是同时，子表也在约束父表。

如果父表中被关联的字段，已经被匹配成功过，那么这个字段就不可修改删除 。

同样， 一旦父表被关联，那么只有子表被删除后，才能删除父表 (无论父表中的字段有没有被匹配成功过)。

因此如果在某场景下要删除父表中的某条数据，没办法直接删除，建议在建表时，添加一个字段，来标记这个字段是否被删除。

---

二、进阶查询

更高阶的MySQL查询语句。

聚合查询

聚合函数

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

函数	说明
COUNT([DISTINCT] expr)	返回查询到的数据的 数量
SUM([DISTINCT] expr)	返回查询到的数据的 总和，不是数字没有意义
AVG([DISTINCT] expr)	返回查询到的数据的 平均值，不是数字没有意义
MAX([DISTINCT] expr)	返回查询到的数据的 最大值，不是数字没有意义
MIN([DISTINCT] expr)	返回查询到的数据的 最小值，不是数字没有意义

创建一个学生成绩表，有三个字段，id(学号)，name(名字)，score(成绩)；并插入了四条数据。

使用示例

COUNT(*)： 代表查询出来的数据的个数 。 COUNT(字段)： 代表查询出来的数据中该字段不为NULL的个数 。

下面的示例中的字段必须是数字 。

SUM(字段)： 返回所有符合条件的字段的总和 。
AVG(字段)： 返回所有符合条件的字段的平均值 。

MAX(字段)： 返回所有符合条件的字段中的最大值 。

MIN(字段)： 返回所有符合条件的字段中的最小值 。

GROUP BY子句

• SELECT 中使用 GROUP BY 子句可以对某个字段进行分组查询或多个字段综合起来进行分组查询。

注意！

SELECT 中使用 GROUP BY 子句对“分组依据字段”重复的数据，只显示一次。
SELECT指定查询的字段最好是GROUP BY 子句的“分组依据字段”，如果想查询其他字段最好包含在聚合函数中。

创建一个学生成绩表，有四个字段，id(学号)，name(名字)，course(课程名)，score(成绩)；并插入了五条数据。

如果查询字段中包含非“分组依据字段”，那么他就只会返回查询到的第一条，不会返回所有条可以理解为他针对“分组依据字段”去重了。如下

查询张三的总成绩

---

HAVING

• GROUP BY 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 WHERE 语句，而需要用HAVING，用法相同。

还拿上面的student_score表举例。

查询张三哪科的成绩最高

---

联合查询

下面不同的联合查询均用以下两表为例

创建一个student表(本校学生表)，有三个字段，id(学号)，name(名字)，age(年龄)。
创建一个score表(多校联考成绩表)，有四个字段，id(学号)，name(名字)，course(课程名)，score(成绩)。
并插入了一些数据。

此处建表一切从简，没有考虑任何实际场景，在实际应用中，要根据不同的场景需求，建立不同的表及约束。

---

笛卡尔积

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

对多张表的数据取笛卡尔积：就是 对多张表的字段进行拼接，将所有的数据可能组合都拼接出来 即A表中的每条数据都要和其他表的所有数据进行拼接。

对student表，score表取笛卡尔积，共查出72条数据

---

内连接

共有两种语法：

select 字段... from 表1 别名1 join 表2 别名2 on 连接条件 and 其他条件;
select 字段... from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;

不同表中相同名称字段的表示，格式： 表名.字段 。

内连接是公平的查询，不会偏袒任何一方，当两张表拼接出来的数据符合条件才会返回。

如下图，只会取两表拼接数据符合条件的部分。

使用内连接查询本校学生参加考试的科目成绩为例：

 select student.id,student.name,student.age,score.course,score.score from student,score where student.name = score.name;

---

外连接

外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示我们就说是左外连接；右侧的表完全显示我们就说是右外连接。

-- 左外连接，表1完全显示
select 字段名  from 表A left join 表B on 连接条件;
-- 右外连接，表2完全显示
select 字段 from 表A right join 表B on 连接条件;

左外连接： 如下图，左边表的全部都会显示，右边表只显示符合条件部分，不符合条件部分显示为：NULL

右外连接： 如下图，右边表的全部都会显示，左边表只显示符合条件部分，不符合条件部分显示为：NULL

左外连接查询本校学生的所有科目成绩

select student.id,student.name,student.age,score.course,score.score from student left join score on student.name = score.name;

孙七和周八没参加任何考试，但是也显示出来了。

右外连接查询联考所有科目成绩的本校学生信息

select student.id,student.name,student.age,score.course,score.score from student right join score on student.name = score.name;

有四门科目的考生不是本校学生，也显示出来了。

---

自连接

自连接是指在同一张表连接自身进行查询。

找到英语I的成绩比高等数学I的成绩高的同学。

select s1.id,s1.name,s1.course,s1.score,s2.course,s2.score from score as s1,score as s2 where s1.name = s2.name and s1.course = "英语I" and s2.course = "高等数学I" and s1.score > s2.score;

---

子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询。
子查询就是把()中select语句返回的信息当作条件使用。

---

单行子查询：返回一行记录的子查询

查询本校学生张三的各科成绩。
子查询，把()中select语句返回的信息当作条件使用。

select * from score where id = (select id from student where name = "张三");

此时()中的select语句至多有一条结果，超过一条会报错。

---

多行子查询：返回多行记录的子查询

使用 IN关键字，就可以 在多条信息中进行检索 。

使用 NOT IN关键字，就可以 排除这多条信息 。

查询本校学生单科成绩大于等于80的学生信息。

select * from student where id in (select id from score where score >= 80);

---

查询本校学生单科成绩小于80的学生信息。

select * from student where id not in (select id from score where score >= 80);

---

合并查询

在实际应用中，为了 合并多个select的执行结果，可以使用集合操作符 UNION，UNION ALL 。使用UNION和UNION ALL时， 前后查询的结果集中，字段需要一致 。

---

查询联考中单科成绩小于80，与参加高等数学I科目的同学信息。

select id,name from score where score <= 80 
union
select id,name from score where course = "高等数学I";

union关键字自动进行去重

select id,name from score where score <= 80 
union all
select id,name from score where course = "高等数学I";

union all 关键字不进行去重

---

总结

以上就是今天要讲的内容，本篇文章还是比较繁重的，希望大家可以静下心来认真阅读。本篇文章所建的表都是没有适配任何应用场景的，只是为了体现要讲的知识点，实际场景建表时要考虑许多因素。

路漫漫不止修身，也养性。