MySQL进阶操作（大量实例带你入坑）

目录​​​​​​​​​​​​​​

1.数据库约束

1.1null键约束

1.2unique键约束

1.3default默认值约束

1.4primary key 主键约束

1.5foreign key 外键约束

2.新增

3.查询

3.1聚合查询

3.2group by 语句

3.3having 语句

4.联合查询

前置知识点：

4.1内连接：

4.2外连接

4.3自连接

4.4子查询

4.5联合查询：

---

1.数据库约束

1.1null键约束

创建表时，表示某列默认为空。

创建表时，表示某列不能为空。

1.2unique键约束

表示这一列的值是唯一的，不重复的。

 由上可知，如果插入两个相同值sn到被unique约束的列里，会出错。

1.3default默认值约束

规定给没有给列赋值时的默认值

 

1.4primary key 主键约束

主键约束可以看作是unique和not null的结合，它确保了某列（或两个列/多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定记录。

 

对于整数类型的主键，常配搭自增长auto_increment来使用。插入数据对应字段不给值时，使用最大值+1。

 

如果上一次插入的是8，那么下次自增之后是以最大的数值8来自增的，就是9。 

1.5foreign key 外键约束

        foreign key 用来关联其他表的主键或唯一键，保证一个表中的数据匹配另一个表中的值的参照完整性。

        详细地说就是如果表B的一个字段（外键）依赖以表A的主键，则称表A为父表，表B为子表。

        原则：外键必须依赖于数据库中已经存在的附件的主键，外键可以为空。

        作用：建立该表与父表的关联关系。

示例：

创建班级表，id为主键：

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

 具体用处在后文会提及~~

2.新增

新增就是插入查询结果：

语法：
insert into table_name [(column [, column ... ])] select ...;

案例：创建一张用户表，设计有name姓名、email邮箱、sex性别、mobile手机号字段。需要把已有的 学生数据复制进来，可以复制的字段为name、qq_mail

 

3.查询

3.1聚合查询

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

函数	说明
count([distinct]exper)	返回查询到的数据的 数量
sum([distinct]exper)	返回查询到的数据的 总和，不是数字没有意义
avg([distinct]exper)	返回查询到的数据的 平均值，不是数字没有意义
max([distinct]exper)	返回查询到的数据的 最大值，不是数字没有意义
min([distinct]exper)	返回查询到的数据的 最小值，不是数字没有意义

案例：

count:

统计班级共有多少同学

统计班级收集的 qq_mail 有多少个，qq_mail 为 NULL 的数据不会计入结果

 sum:

统计数学成绩总分

不及格 < 60 的总分，没有结果，返回 NULL

avg：

统计平均总分

 

max:

返回英语最高分

min:

返回 > 70 分以上的数学最低分

3.2group by 语句

select 中使用group by子句可以对指定列进行分组查询。需要满足：

1.使用group by进行分组查询时，select指定的字段必须是“分组依据字段”。

2.其他字段若想出现在select中则必须包含在聚合函数中。

语法：
select column1, sum(column2), ... from table group by column1[, column3];

案例：

准备测试表及数据：职员表，有id（主键）、name（姓名）、role（角色）、salary（薪水）

查询每个角色的最高工资、最低工资和平均工资

3.3having 语句

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

语法：
select column1, sum(column2) from table_name group by column1 [, column3]
having avg(column1) < 数值;

案例：

显示平均工资低于1500的角色和它的平均工资

4.联合查询

前置知识点：

        在我们实际的开发应用中，需要使用到的数据来自不同的表，这时候我们就需要进行多表查询。多表查询是对多张表的数据取笛卡尔积：

        通过笛卡尔积，我们把两张表汇聚成了一张大表，里面有一些无效的信息，因此我们需要将这些信息进行过滤。

前置操作：

建student、classes、course、score表：

 

 

 

 

我们先插入数据到student、classes、course、score表，插入数据后结果如下：

 

4.1内连接：

内连接：仅显示两个表中匹配行，即两表中都有才显示。

 

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

示例：

我们直接查询student表和score表的连接

 那么就会产生大量的数据，其中还有许多无用数据：

 

因此我们就需要加上一些限制条件来过滤无用信息：

 

 

案例:

1.查询“许仙”同学的 成绩：

 

 

2.查询所有同学的总成绩，及同学的个人信息：

 

3.查询所有同学的成绩，及同学的个人信息：

三张表联合查询：

 

建议使用别名，如果不使用别名，如果表1的column与表2的column有重名，那么就有可能辨别不出来：

 若不使用重名，也可以使用全名：

 

 

4.2外连接

外连接分为左外连接和右外连接：

左外连接：左表有就显示，不论右表。

右外连接：右表有就显示，不论左表。

 

语法：
左外连接，表1完全显示
select 字段名 from 表名1 left join 表名2 on 连接条件;

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

案例：

查询所有同学的成绩，及同学的个人信息，如果该同学没有成绩，也需要显示

左外连接：

 对应的右外连接：

 

学生表、成绩表、课程表3张表关联查询

 

4.3自连接

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

案例：

显示所有“计算机原理”成绩比“Java”成绩高的成绩信息

先查询“计算机原理”和“Java”课程的id

 再查询成绩表中，“计算机原理”成绩比“Java”成绩 好的信息

也可以使用join on 语句来进行自连接查询

 

以上查询只显示了成绩信息，并且是分布执行的。现在我们要显示学生及成绩信息，并在一条语句显示：

 

4.4子查询

        子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询 。就是将当前查询的结果作为另一个查询的where的选项。

案例：

单行子查询：

返回一行记录的子查询 查询与“不想毕业” 同学的同班同学：

 

多行子查询：

返回多行记录的子查询 案例：查询“语文”或“英文”课程的成绩信息

使用in

 使用not in

 

可以使用多列包含：

我们先插入重复的分数：score, student_id, course_id列重复：

insert into score(score, student_id, course_id) values
-- 黑旋风李逵
(70.5, 1, 1),(98.5, 1, 3),
-- 菩提老祖
(60, 2, 1);

查询重复的分数

使用[not]exists关键字：

exists

 not exists

 

        在from子句中使用子查询：子查询语句出现在from子句中。这里要用到数据查询的技巧，把一个 子查询当做一个临时表使用。

查询所有比“中文系2019级3班”平均分高的成绩信息：

我们先获取“中文系2019级3班”的平均分，将其看作临时表

查询成绩表中，比以上临时表平均分高的成绩：

 

 

4.5联合查询：

联合查询用来显示多个查询结果的并集，前后查询结果集中，字段要一致。

union 该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。

案例：查询id小于3，或者名字为“英文”的课程：

其实用or也不是不行：

 

 union all 和union的区别就是union all不会去掉结果重复的行：

 

好啦，以上就是本篇的全部内容啦~~