[MySQL]复杂查询(进阶)

心已赠人 2024-04-01 18:38 103阅读 0赞

![28e94b863e0a4112a47c940cd31abbe5.gif][]

--------------------

> ![c4304daa3a1846adadc000eea43efbf4.png][]专栏简介 :MySql数据库从入门到进阶.
> 
> ![1254c5b119a44cf0ae350489fb7736ad.png][]题目来源:leetcode,牛客,剑指offer.
> 
> ![4366a93aaa3142b5a0f16c7a2200339b.png][]创作目标:记录学习MySql学习历程
> 
> ![52a3437a932f4621b25533c330f7c6cb.png][]希望在提升自己的同时,帮助他人,,与大家一起共同进步,互相成长.
> 
> ![14aa2f0648ae44a299b8f9832af57d2c.png][]学历代表过去,能力代表现在,学习能力代表未来!

--------------------

## 目录 ##

1.新增

2. 聚合查询

2.1 聚合函数

3. 分组查询(grop by)

4. having

5. 联合查询

5.1 内连接

5.2 外连接

5.3 自连接

6. 子查询

7. 合并查询

--------------------

## 1.新增 ##

将查询结果作为values, 插入到指定表中.

**语法:**

insert into 表1 select * from 表2;

**示例:**

将学生表1中的数据插入到学生表2中.

mysql> insert into student1 select * from student2;
    
    mysql> select * from student1;
    +------+------+
    | id   | name |
    +------+------+
    |    1 | 张三 |
    |    2 | 李四 |
    |    3 | 王五 |
    |    4 | 老六 |
    +------+------+
    4 rows in set (0.00 sec)
    
    mysql> select * from student2;
    +------+------+
    | id   | name |
    +------+------+
    |    1 | 张三 |
    |    2 | 李四 |
    |    3 | 王五 |
    |    4 | 老六 |
    +------+------+
    4 rows in set (0.00 sec)

**Tips**:

>  *  查询表与插入表的列数和类型要匹配.
>  *  所有select查询都可以和该操作组合使用.

--------------------

## 2. 聚合查询 ##

之前提到的条件查询都是基于列和列之间的查询, 而聚合查询针对某个列中所有的行来运算.

### 2.1 聚合函数 ###

<table> 
 <tbody> 
  <tr> 
   <td>函数</td> 
   <td>说明</td> 
  </tr> 
  <tr> 
   <td>count</td> 
   <td>返回查询到的数据的数量</td> 
  </tr> 
  <tr> 
   <td>sum</td> 
   <td>返回查询到数据的总和&nbsp;(不是数字没有意义)</td> 
  </tr> 
  <tr> 
   <td>avg</td> 
   <td>返回查询到数据的平均值&nbsp;(不是数字没有意义)</td> 
  </tr> 
  <tr> 
   <td>max</td> 
   <td>返回查询到数据的最大值&nbsp;(不是数字没有意义)</td> 
  </tr> 
  <tr> 
   <td>min</td> 
   <td>返回查询到数据的最小值&nbsp;(不是数字没有意义)</td> 
  </tr> 
 </tbody> 
</table>

*  ***count***

**语法:**

select count(表达式) from 表名;

>  *  Tips: count(\*)包含null所在的行.

**示例:**

统计班级有多少学生

![d16f16452e084759b94a9826af25852d.png][]

--------------------

*  ***sum***

**语法:**

select sum(表达式) from 表名

**示例:**

统计数学总分

![c02a48c20c964a248fd209a15e8427a1.png][]

--------------------

*  ***avg***

**语法:**

select avg(表达式) from student;

**示例:**

统计平均总分

![33de57fbc120403ab7ef7247b4f51313.png][]

--------------------

*  ***max***

**语法:**

select max(表达式) from 表名;

**示例:**

返回英语最高分

![692969c55b404822a4490e76b3e61191.png][]

--------------------

*  ***min***

**语法:**

select min(表达式) from 表名;

**示例:**

返回大于60分以上的数学最低分

![19d46bda4de44496ae56c4b1715f0a4b.png][]

--------------------

## 3. 分组查询(grop by) ##

**语法:**

select 字段 from 表名 group by 字段

**示例:**

![06c845517ae14c7d8e079da85b20a03c.png][]

*  **单个字段分组**

查出学生等级的种类:(按等级划分,去除重复的)

select grade from student group by grade;

![e761eba11f084972a2e1e350aa41eeca.png][]

--------------------

*  **多个字段分组**

按名字和等级划分去除重复的同学:

select name from student group by name,grade;

> **Tip**:此时将名字和等级看做是一个整体, 只有名字和等级都相同的才能分成一组, 其中一个不同就不是一组.

![593b0f2f24ff4368b1d6b5bad7b1098b.png][]

--------------------

*  **搭配聚合函数分组**

查看表中相同人名的个数:

select name,count(*) from student group by name;

![03479da457084c709b1d591690b43779.png][]

--------------------

## 4. having ##

where 与having都是设定筛选条件的语句,有相似点也有不同点.

>  *  group by子句进行分组以后,, 分组之前筛选用**where**, 分组之后筛选用**having**.
>  *  having必须和group by 一起使用.
>  *  where之后不能使用聚合函数, having之后可以使用聚合函数.
>  *  having可以看做是对where的补充, where筛选出合适的数据having才能进行聚合操作.

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>字名</td> 
   <td>作用</td> 
  </tr> 
  <tr> 
   <td>where 子句</td> 
   <td> <p>1)对查询结果进行分组前, 将不符合where条件的行去掉, 即在<strong>分组之前过滤数据.</strong></p> <p>2)where 后面不可以使用聚合函数</p> <p>3)过滤<span style="color:#fe2c24;"><span style="background-color:#fef2f0;">行</span></span></p> </td> 
  </tr> 
  <tr> 
   <td>having 子句</td> 
   <td> <p>1)having 子句的作用是筛选满足条件的组, 即在<strong>分组之后过滤数据,.</strong></p> <p>2)having 后面可以使用聚合函数</p> <p>3)过滤<span style="color:#fe2c24;"><span style="background-color:#fef2f0;">组</span></span></p> <p>4)支持所有的where操作.</p> </td> 
  </tr> 
 </tbody> 
</table>

**MySQL语句执行顺序:**

![1c359b7a83e24dc4bef510a674ade5a0.png][]

**示例:**

显示平均工资高于1500的人的平均工资.

select name,avg(salary) from student group by name having avg(salary)>1500;

![341c1df6e8904722951d6357b9e5f38e.png][]

--------------------

## 5. 联合查询 ##

实际开发中数据往往来自不同的表, 所以需要多表联合查询, 多表联合查询本质是对多张表的数据取**笛卡尔积**(也就是全排列).列数是两个表列数之和, 行数是两个表行数之积.因此其中只有一部分数据是有效的, 需要后续相关操作进行筛选.

![ef3f43f5d8fb42be94dfb2cefcfab8e8.png][]

> Tips:关联表查询时可以对关联表使用别名, 更加直观.

**筛选前:**

![089b435fe0754e819c74befeef6e5a69.png][] **筛选后:**

![3a4cacaf651846f094ee9a0f35401db2.png][]

> Tips:如果两张表中字段重名, 不加成员访问修饰符" . ", 会报错 ambigous(模糊不清的)

![1370d601f937474b9a23808472fb1e8c.png][] 为了使查询数据更加直观明了, 本文提供以下代码供后续案例使用.

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','张三','zhangsan@qq.com',1),
    ('00835','李四',null,1),
    ('00391','王五',null,1),
    ('00031','老六','xuxian@qq.com',1),
    ('00054','老师我作业忘带了',null,1),
    ('51234','芝士雪豹','xuebao@qq.com',2),
    ('83223','tellme',null,2),
    ('09527','老外学英文','foreigner@qq.com',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),
    -- 老外学英文
    (80, 7, 2),(92, 7, 6);

--------------------

### 5.1 内连接 ###

> 内连接就是指结果仅包含符合连接条件的行, 参与连接的两个表都应符合连接条件.

![0a19efc2b37f4712b50f406e02e3d937.png][]

**语法:**

**1.from多个表简单明了,但只能实现内连接不能实现外连接.**

select * from 表1,表2...;

**2.join on可以既可以实现内连接也可以实现外连接.**

select * from 表1 join 表2 on...;

select * from 表1 inner join 表2 on...;

**示例:**

查询老六同学的全科成绩.

查询步骤:

> 1.  **分析需要联合的表**(学生表和分数表), 然后**计算笛卡尔积**.
> 2.  **合法性筛选**(分数表id对应学生id).
> 3.  **根据需求加入必要条件.xue**
> 4.  **去要的列.**

1.计算学生表和分数表的笛卡尔积.(160多行数据, 大部分无效)

![72b139a0ec394a81a4e2fd3c132760d6.png][]

2.分数表中id对应学生表中id

![1af20c87f9b7455091973a987067455d.png][]

3.学生姓名为老六

![115a3cbfa33243949e9b4fe1a1a07ca9.png][]

4.去除不必要的列

![d809c655ecb24320b93fbf1dd1e092c8.png][]

--------------------

查询所有同学的成绩, 及同学的个人信息.(学生表,课程表,分数表)

select student.name as 姓名,course.name as 课程名称,score.score from student,course,score where student.id = score.student_id and score.course_id = course.id;

![e33e2d92c1574e5facc1419238adcd18.png][]

--------------------

### 5.2 外连接 ###

> 连接结果不仅包含符合连接条件的行, 同时也包含不符合连接条件的行, 分为左外连接和右外连接.

![831a84a7e1314d00ace997d3e09b8e74.png][]

**语法:**

**左外连接:左表有多少数据就显示多少数据, 右边没有的就用null表示**

select * from 表1 left join 表2 on 连接条件;

**右外连接:右表有多少数据就显示多少数据, 左表没有的就用null表示**

select * from 表1 right join 表2 on 条件;

**示例:**

假如王五同学转专业, 数据表还未同步他的成绩信息.

mysql> select * from student;
    +------+------+
    | id   | name |
    +------+------+
    |    1 | 张三 |
    |    2 | 李四 |
    |    3 | 王五 |
    +------+------+
    mysql> select * from score;
    +------------+-------+
    | student_id | score |
    +------------+-------+
    |          1 |    90 |
    |          2 |    89 |
    |          4 |    77 |
    +------------+-------+
    --左外连接
    mysql> select * from student left join score on student.id = score.student_id;
    +------+------+------------+-------+
    | id   | name | student_id | score |
    +------+------+------------+-------+
    |    1 | 张三 |          1 |    90 |
    |    2 | 李四 |          2 |    89 |
    |    3 | 王五 |       NULL |  NULL |
    +------+------+------------+-------+
    --右外连接
    mysql> select * from student right join score on student.id = score.student_id;
    +------+------+------------+-------+
    | id   | name | student_id | score |
    +------+------+------------+-------+
    |    1 | 张三 |          1 |    90 |
    |    2 | 李四 |          2 |    89 |
    | NULL | NULL |          4 |    77 |
    +------+------+------------+-------+

--------------------

### 5.3 自连接 ###

自连接顾名思义就是自己和自己笛卡尔积, 自连接的效果就是把行转成列.不管和where子句还是having子句都是针对不同列之间的行进行操作, 如果只有一个列并且想要行与行之间进行比较, 就必须自连接.

**语法:**

select * from 表名 as 别名1,表名 as 别名2;

> Tips:自连接必须给表起个别名, 否则两个相同的表连接会报错.

![a9abfe29e507410d9529932f43402b2a.png][]

**示例:**

查询java成绩高于计算机原理成绩的同学.

由图可以看出, java成绩和计算机原理成绩之间的比较在同一列, 所以需要行转列.

![46178e45e91f4468a813ece7e5ccc7d6.png][]

自连接之后可以发现, 可以进行列与列之间的比较了, 本题针对的是同一同学不同课程, 所以筛选条件是两表的学生id相同.

mysql> select * from score as s1, score as s2 where s1.student_id = s2.student_id;

![30a79469ee0341b7820dca7d4ccead77.png][] 最后添加条件完成比较.

mysql> select * from score as s1, score as s2 where s1.student_id = s2.student_id and s1.course_id = 3 and s2.course_id = 1 and s1.score>s2.score;
    +-------+------------+-----------+-------+------------+-----------+
    | score | student_id | course_id | score | student_id | course_id |
    +-------+------------+-----------+-------+------------+-----------+
    |  98.5 |          1 |         3 |  70.5 |          1 |         1 |
    |  68.0 |          3 |         3 |  33.0 |          3 |         1 |
    +-------+------------+-----------+-------+------------+-----------+

--------------------

## 6. 子查询 ##

子查询是指嵌套在其他sql语句中的select语句, 也叫嵌套查询.(本质上就是套娃)

*  **单行子查询**:返回一行记录的子查询

示例:

返回与"老六"同学同班的同学.

--分步骤
    mysql> select * from student where name = "老六";
    +----+-------+------+---------------+------------+
    | id | sn    | name | qq_mail       | classes_id |
    +----+-------+------+---------------+------------+
    |  4 | 00031 | 老六 | xuxian@qq.com |          1 |
    +----+-------+------+---------------+------------+
    
    
    mysql> select * from student where classes_id = 1 and name!="老六";
    +----+-------+------------------+-----------------+------------+
    | id | sn    | name             | qq_mail         | classes_id |
    +----+-------+------------------+-----------------+------------+
    |  1 | 09982 | 张三             | zhangsan@qq.com |          1 |
    |  2 | 00835 | 李四             | NULL            |          1 |
    |  3 | 00391 | 王五             | NULL            |          1 |
    |  5 | 00054 | 老师我作业忘带了 | NULL            |          1 |
    +----+-------+------------------+-----------------+------------+
    
    --子查询一步完成
    mysql> select * from student where classes_id = (select classes_id from student where name = "老六" ) and name!="老六";
    +----+-------+------------------+-----------------+------------+
    | id | sn    | name             | qq_mail         | classes_id |
    +----+-------+------------------+-----------------+------------+
    |  1 | 09982 | 张三             | zhangsan@qq.com |          1 |
    |  2 | 00835 | 李四             | NULL            |          1 |
    |  3 | 00391 | 王五             | NULL            |          1 |
    |  5 | 00054 | 老师我作业忘带了 | NULL            |          1 |
    +----+-------+------------------+-----------------+------------+

--------------------

*  **多行子查询:**返回多行记录的子查询

示例:

查询语文或英语课程的成绩信息.

1. (not) in 关键字

--分步骤查询
    mysql> select * from course where name = "语文" or name = "英语";
    +----+------+
    | id | name |
    +----+------+
    |  4 | 语文 |
    |  6 | 英语 |
    +----+------+
    
    mysql> select * from score where course_id = 4 or course_id = 6;
    +-------+------------+-----------+
    | score | student_id | course_id |
    +-------+------------+-----------+
    |  98.0 |          1 |         6 |
    |  72.0 |          4 |         6 |
    |  43.0 |          6 |         4 |
    |  79.0 |          6 |         6 |
    |  92.0 |          7 |         6 |
    +-------+------------+-----------+
    
    --多行子查询
    mysql> select * from score where course_id in (select id from course where name = "语文" or name = "英语");
    +-------+------------+-----------+
    | score | student_id | course_id |
    +-------+------------+-----------+
    |  98.0 |          1 |         6 |
    |  72.0 |          4 |         6 |
    |  43.0 |          6 |         4 |
    |  79.0 |          6 |         6 |
    |  92.0 |          7 |         6 |
    +-------+------------+-----------+

2. (not) exists 关键字

> 由于 in 关键字查询结果在内存中, 如果内存中存不下可以考虑使用exists关键字, 但exists关键字执行效率低下, 且可读性差.不如分步查询.

--------------------

## 7. 合并查询 ##

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

Tips:

>  *  union查询结果会去重, union all 可以保留多份.
>  *  or 只能联合一个表中的结果, union 可以联合多个表中的.

*  union

示例:

查询id<3 或者名字为"英文"的课程

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

*  union all

示例:

查询id<3 或者名字为"java"的课程

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

[28e94b863e0a4112a47c940cd31abbe5.gif]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/eb99ad3bb3e848bcbaf3e24ec7834f17.gif
[c4304daa3a1846adadc000eea43efbf4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/365795fdf7324a268a0d4cadced89f67.png
[1254c5b119a44cf0ae350489fb7736ad.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/7f44a32efb144e1ea80772add8d37d1f.png
[4366a93aaa3142b5a0f16c7a2200339b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/31b55742e25f4bf4bf91a08a3d14ddad.png
[52a3437a932f4621b25533c330f7c6cb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/759410d2f3374ddbb47236ee3432c29a.png
[14aa2f0648ae44a299b8f9832af57d2c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/b2499a7560b3499998b7b052dfa2b1f4.png
[d16f16452e084759b94a9826af25852d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/04c2425506ad4f81974b66858280f879.png
[c02a48c20c964a248fd209a15e8427a1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/75a18e506b454ece889860516a10c66f.png
[33de57fbc120403ab7ef7247b4f51313.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/a88098c3a3e049a9a66ccf6a9d1b3ac2.png
[692969c55b404822a4490e76b3e61191.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/859ac811ea6e458fa2d69c3e5f7a60b0.png
[19d46bda4de44496ae56c4b1715f0a4b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/a0671b310b854a59b7d38c311b77f510.png
[06c845517ae14c7d8e079da85b20a03c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/13dd99255a04402193f5b63ac35d19fa.png
[e761eba11f084972a2e1e350aa41eeca.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/d9a186086eda4cf583148b9c84619f9a.png
[593b0f2f24ff4368b1d6b5bad7b1098b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/12942e8676ca4779b0417a691e7aa554.png
[03479da457084c709b1d591690b43779.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/bb89378779534a8b82b386d820066ac6.png
[1c359b7a83e24dc4bef510a674ade5a0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/f7766393acac4b2ab9f3da9477082f3b.png
[341c1df6e8904722951d6357b9e5f38e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/59485d1043bc43a0afd965fa8471e1ae.png
[ef3f43f5d8fb42be94dfb2cefcfab8e8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/3ad98b371d6344e9b67fbaaa9709fc69.png
[089b435fe0754e819c74befeef6e5a69.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/2542b37b208c41cea800d4128cb23fbe.png
[3a4cacaf651846f094ee9a0f35401db2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/af44323941f74645a95cb92f7c80ece7.png
[1370d601f937474b9a23808472fb1e8c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/f15b914ada494305b8fb363fd1f288ed.png
[0a19efc2b37f4712b50f406e02e3d937.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/73158b04260c401d86fe238943e3c8a2.png
[72b139a0ec394a81a4e2fd3c132760d6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/729bbeac1f2f423c8ec457b02c1694dc.png
[1af20c87f9b7455091973a987067455d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/7cf3bba2797f4dd9ac7152b693b73aac.png
[115a3cbfa33243949e9b4fe1a1a07ca9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/b53b64fb5a9445cbaf974efe45213b59.png
[d809c655ecb24320b93fbf1dd1e092c8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/55f2e0fe256f47dcba6434d64be3975c.png
[e33e2d92c1574e5facc1419238adcd18.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/23c093caaf8741d3aee16aac75c512a3.png
[831a84a7e1314d00ace997d3e09b8e74.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/f839e3c085c949bf9fbc01cbf997f576.png
[a9abfe29e507410d9529932f43402b2a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/b84c8ec27a044ab59dc8744e9e9abb7e.png
[46178e45e91f4468a813ece7e5ccc7d6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/b581a54c7d31484baf33a32df69ef53b.png
[30a79469ee0341b7820dca7d4ccead77.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/fc1f5283f7e5423697fb60176830497a.png