【MySQL】MySQL表的增删改查（进阶篇）——之查询操作（超级详解）

用户11288949

发布于 2024-09-24 13:59:37

740

发布于 2024-09-24 13:59:37

文章被收录于专栏：用户11288949的专栏

📚️1.引言

Hello!!!家人们，小编上期期讲解了关于增删查改的进阶之约束条件，关于数据库表的增删查改有了一定的认知，小编这期就将深入讲解另一部分内容，即关于数据库增删查改的进阶操作之查询进阶，大家准备好了吗~~~🥳🥳🥳；

且听小编进行讲解，包你学会！！！ 📚️2.查询搭配插入

SQL执行语句：insert into 表名1 select * from 表名2

当然这里的意思是将表2的所有值插入到表1当中去；

代码如下：

mysql> insert into student values(1,'孙悟空'),(2,'猪八戒'),(3,'沙悟净');


mysql> create table student2(student2_id int,name varchar(10));


mysql> insert into student2 select * from student;

此时就将表student的所有值插入到了表student2当中去；

注意：此时的被插入的表和插入的表的列数，和类型都要匹配，否则会报错；

📚️3.聚合查询

由于我们之前所说的表达式查询是针对表达式进行列和列的操作，所以在行和行之间我们就要用到聚合查询，所以聚合查询是针对行和行之间的运算；

3.1 聚合函数

1.count函数

SQL执行语句：select count(*) from 表名

代码实例如下：

mysql> insert into student values(1,'孙悟空'),(2,'猪八戒'),(3,'沙悟净');
mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
|        3 |
+----------+

注意：这里的count计算的是有多少行，并且也会把null算进行数；但是当指定列的时候，如果存在null则null不算入行数中；并且在指定列的时候可以通过distinct进行去重操作；

2.sum函数

SQL执行语句：select sum(int类型的列名) from 表名

代码实例如下：

mysql> select sum(student_id) from student;
+-----------------+
| sum(student_id) |
+-----------------+
|               6 |
+-----------------+

注意：这里的sum代表的是每行指定列的数值相加，这里会自动排除掉NULL，若这里的指定的列不为整型，那么输出为0，并发出警告；

如下：

mysql> select sum(name) from student;
+-----------+
| sum(name) |
+-----------+
|         0 |
+-----------+
1 row in set, 4 warnings (0.00 sec)

这时候，进入警告查看，可以了解到MySQL会自动将指定列转为double类型的数据，如果转失败了，就会报错，即发出警告；

3.avg函数，max函数，min函数

这里的三个函数的操作基本和上述的写法基本一致

SQL执行语句：select avg(整数类型的列) from 表名；

SQL执行语句：select max(整数类型的列) from 表名；

SQL执行语句：select min(整数类型的列) from 表名；

注意：这里的函数分别代表求平均值，最大值，最小值；这里的类型要求都为数字类型，不要搞错了，否则这里的函数就没有意义了；

3.2GROUP BY子句

这里的group by代表的是分组查询，一般来搭配聚合查询函数来使用；

SQL执行语句：select 列名，列名 from 表名 group by 列名；

代码实现如下：

mysql> select * from emp;
+------+-----------------+--------------+--------+
| id   | name            | role         | salary |
+------+-----------------+--------------+--------+
|    1 | 孙悟空          | 程序猿       |  10000 |
|    2 | 猪八戒          | 程序猿       |  11000 |
|    3 | 沙悟净          | 程序猿       |  14000 |
|    4 | 唐三藏          | 老板         | 100000 |
|    5 | 白龙马          | 产品经理     |   9000 |
|    6 | 女儿国国王      | 产品经理     |  10000 |
+------+-----------------+--------------+--------+

mysql> select role,avg(salary) from emp group by role;
+--------------+-------------+
| role         | avg(salary) |
+--------------+-------------+
| 产品经理     |   9500.0000 |
| 程序猿       |  11666.6667 |
| 老板         | 100000.0000 |
+--------------+-------------+

首先前面是小编创建的一张表，通过分组查询，会将指定的分组列进行分组，让后通过聚合函数将对应的角色的薪水求平均值；

注意：

📍若直接求平均值，由于老板的薪水很高，会造成很大误差，分完组后，就不会产生误差；

📍若指定了分组的列，那么在进行查找时也应该写进去，方便阅读；

📍分组查询也可以搭配where条件函数，只不过要分清条件在分组之前还是分组之后，若条件在分组之前那么就可以使用where

3.3having条件函数

SQL执行语句：select 列名，列名 from 表名 group by 列名 having 条件

上述说到若条件在分组之后，就要用到having条件描述：

mysql> select role,avg(salary) from emp group by role having avg(salary)<20000;
+--------------+-------------+
| role         | avg(salary) |
+--------------+-------------+
| 产品经理     |   9500.0000 |
| 程序猿       |  11666.6667 |
+--------------+-------------+

注意：这里就是当进行分组查询，并求平均薪资完成后，想要得到薪水小于两万的角色，此时就可以在分组查询完成后，进行having函数描述条件；

📚️4.联合查询

4.1内连接

所谓联合查询就是多表查询，作用于非一个表数据，通过建立笛卡尔集，然后进行筛选得出有效数据，得到最终的表；

例如：

得到的笛卡尔积就是:

但是其中也包括了无效的数据（即红色的×），这就是笛卡尔积，就是每条数据的组合；

SQL执行语句：selecct * from 表名，表名；

小编创建的表如下：

班级表：

学生表：

分数表：

课程表：

那么此时我们要查询许仙同学的成绩：那么就是学生表+成绩表；

1.创建笛卡尔积：

mysql> select * from student,score;

数据过多就不展示了；

2建立链接条件，筛选有效数据：

这里的链接条件就是，两个表的学生id要相同：

mysql> select * from student,score where student.id=score.student_id;

注意：通过where来建立链接条件，这里的id区分是通过表名.id的方式；

3.结合需求，添加条件：

通过需求我们只需要名字为‘许仙’即可：

mysql> select * from student,score where student.id=score.student_id and student.name='许仙';
+------+------+--------+---------------+------------+-------+------------+------------+
| id   | sn   | name   | qq_email      | classes_id | score | student_id | classes_id |
+------+------+--------+---------------+------------+-------+------------+------------+
|    4 |   31 | 许仙   | xuxian@qq.com |          1 |    67 |          4 |          1 |
|    4 |   31 | 许仙   | xuxian@qq.com |          1 |    23 |          4 |          3 |
|    4 |   31 | 许仙   | xuxian@qq.com |          1 |    56 |          4 |          5 |
|    4 |   31 | 许仙   | xuxian@qq.com |          1 |    72 |          4 |          6 |
+------+------+--------+---------------+------------+-------+------------+------------+

此时大体的需求就已经完成了；

4.精简表：

删除不需要的列名，达到表的简化：

mysql> select name,score from student,score where student.id=score.student_id and student.name='许仙';
+--------+-------+
| name   | score |
+--------+-------+
| 许仙   |    67 |
| 许仙   |    23 |
| 许仙   |    56 |
| 许仙   |    72 |
+--------+-------+

此时我们就要通过之前讲过的指定列查询进行简化表的列的数量；

5.笛卡尔积总结：

在两张或以上的表进行操作时，先通过两张表建立笛卡尔积，然后通过特殊条件进行1条件链接，然后通过需求设置条件进行简化；

当然不同的条件对应不同的条件函数，这里的笛卡尔积还可以使用group by来进行分组，和聚合函数来完成特殊的需求。

4.2外连接

当表中的数据一一对应的时候内外连接一致，但是存在不对应的数据的时候就会有区别了：

两个表如下：

mysql> select * from stu;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
|    2 | 李四   |
|    3 | 王五   |
+------+--------+
3 rows in set (0.00 sec)

mysql> select * from sco;
+------+-------+
| id   | score |
+------+-------+
|    1 |    44 |
|    2 |    77 |
|    4 |    88 |
+------+-------+

这里的id为4的数据是不对应的；

在上述的内连接中也可以写作如下：

mysql> select * from stu,sco where stu.id=sco.id;
+------+--------+------+-------+
| id   | name   | id   | score |
+------+--------+------+-------+
|    1 | 张三   |    1 |    44 |
|    2 | 李四   |    2 |    77 |
+------+--------+------+-------+
2 rows in set (0.00 sec)

mysql> select * from stu inner join sco on stu.id=sco.id;
+------+--------+------+-------+
| id   | name   | id   | score |
+------+--------+------+-------+
|    1 | 张三   |    1 |    44 |
|    2 | 李四   |    2 |    77 |
+------+--------+------+-------+

此时两种写法，代表的意思都是一致的；下面的写法是将“逗号改为join,where改为on，并且在join前加入inner（可以省却）”

1.左外连接

SQL执行语句：select * from 表名 left join 表名 on 连接条件;

代码实例如下：

mysql> select * from stu left join sco on stu.id=sco.id;
+------+--------+------+-------+
| id   | name   | id   | score |
+------+--------+------+-------+
|    1 | 张三   |    1 |    44 |
|    2 | 李四   |    2 |    77 |
|    3 | 王五   | NULL |  NULL |
+------+--------+------+-------+

注意：所谓的左外连接就是，将前一个表的所有行展示，在另一个表中没有对应的数据，那么就用NULL来代替；

2.右外连接

SQL执行语句：select * from 表名 right join 表名 on 连接条件;

代码实例如下：

mysql> select * from stu right join sco on stu.id=sco.id;
+------+--------+------+-------+
| id   | name   | id   | score |
+------+--------+------+-------+
|    1 | 张三   |    1 |    44 |
|    2 | 李四   |    2 |    77 |
| NULL | NULL   |    4 |    88 |
+------+--------+------+-------+

注意：所谓的右外连接就是，将后一个表的所有行展示，在另一个表（前一个表）中没有对应的数据，那么就用NULL来代替；

4.3自连接

所谓的自连接就是自己和自己建立笛卡尔积，主要是通过需求进行自己表之间行和行之间的操作

SQL执行语句：select * from 表名，表名（同一个）

例如通过上述分数表中，查询谋和成绩比另一个成绩高的表时；

建立笛卡尔积：

 select * from score as s1,score as s2

注意：由于分不清哪个score，那么就需要进行别名的操作，否则就会发生报错；

然后合上述一样，建立条件连接，根据需求设置条件简化表；

4.4子查询

所谓的自查询就是套娃，但是小编不建议这么去编写MySQL数据库；

1.单行子查询

代码实例：

假如我们要查询许仙同学的同班同学：

第一步：找到许仙同学的班级id;

第二部：根据班级id找到其他同学；

mysql> select classes_id from student where name='许仙';
+------------+
| classes_id |
+------------+
|          1 |
+------------+


mysql> select name from student where classes_id=1;
+-----------------+
| name            |
+-----------------+
| 黑旋风李逵      |
| 菩提老祖        |
| 白素贞          |
| 许仙            |
| 不想毕业        |
+-----------------+

那么此时就找到了许仙同学的同班同学；

而子查询实例：

mysql> select name from student where classes_id=(select classes_id from student where name='许仙');

注意：单行子查询就是返回一行数据；这里就是将id=1中的1换成了上述通过许仙查询班级id的过程；

2.多行子查询

查询语文和英文的成绩信息；

返回两个课程id，然后再成绩表中找到对应的成绩信息；

代码如下：

mysql> select id from course where name='语文' or name='英文';
+------+
| id   |
+------+
|    4 |
|    6 |
+------+
2 rows in set (0.00 sec)

mysql> select * from score where score.classes_id in(4,6);

两者结合后就为多行子查询；

mysql> select * from score where score.classes_id in(select id from course where name='语文' or name='英文');

注意：在多行子查询中在嵌套时要用到‘in’这个关键词；

4.5合并查询

所谓合并查询，就是通过union操作符，用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。

1.在一个表中

SQL执行语句：select * from 表名 where 条件 union select * from 同一个表名where 条件;

代码实例：

mysql> select * from course where id<3 union select * from course where name='英文';
+------+--------------------+
| id   | name               |
+------+--------------------+
|    1 | Java               |
|    2 | 中国传统文化       |
|    6 | 英文               |
+------+--------------------+

这里表示，取名字是英文或者课程id小于3的数据；

2.在两个表中

SQL执行语句：select * from 表名 union select * from 表名;

代码实例：

mysql> select * from stu union select * from stu2;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
|    2 | 李四   |
|    3 | 王五   |
|    4 | 赵六   |
+------+--------+

这里就是两个表的所有数据；

注意：合并查询时，会将两个表相同的数据去重，若不想去重就使用union all操作符；