MySQL数据库|MySQL--增删改查（进阶） MySQL数据库|mysql

MySQL--增删改查（进阶--上）

一丶数据库约束
- （1）约束类型
- - 前三种约束示范
  - 主键约束和外键约束
- （2）新增
二丶查询
- （1）聚合查询
- - 1>聚合函数
  - 2>group by 子句
  - 3>having子句
- （2）联合查询
- - 1> 内连接
  - 2> 外连接
  - 3> 自连接
  - 4>子查询
  - 5>合并查询

一丶数据库约束我们在查询数据库的时候，通常要进行约束，不然就会出现各种各样意外的情况，导致我们的查询遇到困难。
（1）约束类型通常我们的约束有以下几种：

NOT NULL - 指示某列不能为 NULL。 UNIQUE - 保证某列的每行不能重复 DEFAULT - 规定没有给列赋值时的默认值。 PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。 FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。 CHECK - 保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略CHECK子句。

前三种约束示范

文章图片

首先，上面就是关于非空约束，唯一约束，默认值约束的正确写法，那么我们来实验一下，是否真的约束成功。
非空约束 \color{red}{非空约束} 非空约束

文章图片

唯一约束 \color{red}{唯一约束} 唯一约束

文章图片

默认值约束 \color{red}{默认值约束} 默认值约束
这里有一个问题需要注意：

文章图片

我们在插入值的时候一定要指定是那一列，不然数据库识别不出来，所以上面的写法会报错。
正确的写法如下：

文章图片

主键约束和外键约束
主键约束 \color{red}{主键约束} 主键约束
首先什么是主键约束呢？
主键约束就是 unique + not null。
那么主键约束有什么用呢？

用来标识数据库中的数据，数据是在不同设备中流转的，一般来说，web开发中的数据是在：
<1>用户浏览器
<2>java程序
<3>数据库
中流转，当我们使用主键约束时候，就知道是哪条数据了。

那这里有一点需要特别说明的，如果主键为整形数字，可以指定为auto_increment表示从1开始自增，但是插入数据的时候，不要指定插主键字段。
具体形式如下：

文章图片

可以看到第一行，我除了加入约束主键之外，我还加入了自增，那么有什么用呢？

文章图片

可以看到，每当我插入一个数据的时候，主键id就开始了自增，这也是auto_increment的作用
外键约束 \color{red}{外键约束} 外键约束
关于外键约束，目的是在于使用内外连接的时候，关联其他表的主键或者说是唯一键。
具体用法如下，这里我们重写一下student表。

文章图片

这样我们就把班级表的id和学生表的id关联起来了。
（2）新增这里的新增指的是把查询结果新增到一个表当中。
那我们先创建一张表用来测试：

文章图片

创建成功之后，接下来我们把查询结果新增到一个表当中。

insert into test_user(name, email) select name, qq_mail from student;

二丶查询这里的查询也是这里的重难点。
（1）聚合查询 1>聚合函数
首先我们常用的聚合函数有以下几种

1、AVG函数；2、COUNT函数；3、MAX函数；4、MIN函数；5、SUM函数；

其作用分别是

1.平均值 2.求数据量 3.求最大值 4.求最小值 5.求总和

具体的写法如下：

select 函数名（字段名） from 表名

比如就像下面这样：

文章图片

2>group by 子句
select 中使用 group by子句对指定列进行分组查询，这里需要注意：使用group by进行分组查询的时候，select指定的字段必须是分组依据字段，其他字段若想出现在select中则必须包含在聚合函数中。
接下来对group by子句进行试验。
首先准备一张测试表：

文章图片

然后我们对其进行分组+聚合查询。
那么这里特别注意的就是分组时候选择的字段。是name还是role呢？
注意是role，因为name是唯一的，所以没必要进行分组。

文章图片

3>having子句
这里注意了，使用group by子句进行分组以后，如果想要继续进行条件过滤，就不可以使用where了，因为使用where在某些条件下会造成查询结果的错误。那么这里我们就需要使用having子句。

文章图片

（2）联合查询在讲联合查询之前，首先要引入笛卡尔积的概念，

笛卡尔乘积是指在数学中，两个集合X和Y的笛卡尔积（Cartesian product），又称直积，表示为X×Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员。

什么意思呢？就是说如果集合A有两个元素：a，b。集合B有两个元素：c，d。那么他们笛卡尔积就是：ac，ad，bc，cd。
有了这个概念，我们接下来引入接下来的内容。
首先我们要重新创建表，用来演示接下里的内容。

文章图片

文章图片

总共四张表，其关系如下：

文章图片

我们把其数据处理完毕之后，然后开始进行我们接下来的内容。
首先验证一下笛卡尔积

文章图片

当然，这里的id和name可能看起来不是很清晰，所以我们可以给后面取别名加以分辨。
1> 内连接
我们可以发现，上面的查询结果有很多我们不想要的，因为笛卡尔积是把两个对象的元素两两互相匹配，就导致有很多其他的以外的元素。
这个时候就需要使用到内连接。内连接有两种写法：

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

首先试一下第二种：

文章图片

然后再试一下第一种：

文章图片

这就是内连接的两种写法。
然后具体应用场景可以有以下几种。
（1）查询某位同学的成绩

文章图片

可以发现，我们联合学生表和成绩表就可以查出某位同学的具体成绩，但是问题是我们不知道这四门成绩分别对应的哪一门，这个时候我们就有了更高的需求。
（2）查询某位同学的成绩，带上课程名称
这里我们就需要第三张表了，也就是我们的课程表。

文章图片

（3）查询所有同学的总成绩，以及同学的个人信息
首先如果说我们要满足上面的需求，那么先要查询成绩表，和学生表

文章图片

这里每一门成绩都有了，但是我们不需要，我们需要的是总成绩。
所以这里需要换一种写法。

文章图片

2> 外连接
这里内连接其实是有一个小问题的，那就是如果说没有stu.id对应相等的sco.student_id,那么这个数据是不会显示出来的，所以为了应对这种情况，我们就需要使用外连接。
所谓外连接：

【MySQL数据库|MySQL--增删改查（进阶）】外连接：左外连接，左表叫外表，右外连接，右边的表叫外表
– 两张表关联，外表中在按其他条件过滤后的数据，会全部显示（按连接条件，另一张表没有的数据，也会显示）
– 要显示没有分数的学生，需要使用外连接

是什么意思呢？首先我们用内连接来看一下：

文章图片

如果使用内连接，我们的“老外学中文”同学的成绩因为没找到对应的sco.student_id就不会显示。
为了应对这种情况。我们就需要使用内连接。

文章图片

但是我们还可以继续加其他的约束条件，比如说我们想要成绩在60分以上的成绩，就想看看那些成绩是合格的。

文章图片

3> 自连接
所谓自连接，就是自己和自己连接，就是在同一张表连接自身进行查询。举个例子，查询一下“计算机原理”成绩比“java”高成绩

SELECT s1.* FROM score s1 JOIN score s2 ON s1.student_id = s2.student_id AND s1.score < s2.score AND s1.course_id = 1 AND s2.course_id = 3;

对应查询结果如下

文章图片

当然我们可以发现上面的只显示了成绩信息，并且执行分布，我们如果说要显示成绩等信息还需要增加额外的条件。

SELECT stu.*, s1.score Java, 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 = 'Java' AND c2.NAME = '计算机原理';

对应查询结果如下

文章图片

4>子查询
子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询
1.单行子查询
单行子查询：返回一行记录的子查询。比如说查询与“不想毕业” 同学的同班同学：

select * from student where classes_id=(select classes_id from student where name='不想毕业');

文章图片

2.多行子查询
多行子查询：返回多行记录的子查询，比如说：查询“语文”或“英文”课程的成绩信息

select * from score where course_id in (select id from course where name='语文' or name='英文');

文章图片

3.在from子句中使用子查询
子查询语句出现在from子句中。这里要用到数据查询的技巧，把一个子查询当做一个临时表使用。比如查询所有比“中文系2019级3班”平均分高的成绩信息：

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 = '中文系2019级3班' ) tmp WHERE sco.score > tmp.score;