[MySQL]复杂查询(进阶)

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

28e94b863e0a4112a47c940cd31abbe5.gif

c4304daa3a1846adadc000eea43efbf4.png专栏简介 :MySql数据库从入门到进阶.

1254c5b119a44cf0ae350489fb7736ad.png题目来源:leetcode,牛客,剑指offer.

4366a93aaa3142b5a0f16c7a2200339b.png创作目标:记录学习MySql学习历程

52a3437a932f4621b25533c330f7c6cb.png希望在提升自己的同时,帮助他人,,与大家一起共同进步,互相成长.

14aa2f0648ae44a299b8f9832af57d2c.png学历代表过去,能力代表现在,学习能力代表未来!

目录

1.新增

  1. 聚合查询

2.1 聚合函数

  1. 分组查询(grop by)

  2. having

  3. 联合查询

5.1 内连接

5.2 外连接

5.3 自连接

  1. 子查询

  2. 合并查询

1.新增

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

语法:

  1. insert into 表1 select * from 表2;

示例:

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

  1. mysql> insert into student1 select * from student2;
  3. mysql> select * from student1;
  4. +------+------+
  5. | id   | name |
  6. +------+------+
  7. |    1 | 张三 |
  8. |    2 | 李四 |
  9. |    3 | 王五 |
  10. |    4 | 老六 |
  11. +------+------+
  12. 4 rows in set (0.00 sec)
  14. mysql> select * from student2;
  15. +------+------+
  16. | id   | name |
  17. +------+------+
  18. |    1 | 张三 |
  19. |    2 | 李四 |
  20. |    3 | 王五 |
  21. |    4 | 老六 |
  22. +------+------+
  23. 4 rows in set (0.00 sec)

Tips:

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

2. 聚合查询

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

2.1 聚合函数




























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

语法:

  1. select count(表达式) from 表名;
  • Tips: count(*)包含null所在的行.

示例:

统计班级有多少学生

d16f16452e084759b94a9826af25852d.png

  • sum

语法:

  1. select sum(表达式) from 表名

示例:

统计数学总分

c02a48c20c964a248fd209a15e8427a1.png

  • avg

语法:

  1. select avg(表达式) from student;

示例:

统计平均总分

33de57fbc120403ab7ef7247b4f51313.png

  • max

语法:

  1. select max(表达式) from 表名;

示例:

返回英语最高分

692969c55b404822a4490e76b3e61191.png

  • min

语法:

  1. select min(表达式) from 表名;

示例:

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

19d46bda4de44496ae56c4b1715f0a4b.png

3. 分组查询(grop by)

语法:

  1. select 字段 from 表名 group by 字段

示例:

06c845517ae14c7d8e079da85b20a03c.png

  • 单个字段分组

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

  1. select grade from student group by grade;

e761eba11f084972a2e1e350aa41eeca.png

  • 多个字段分组

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

  1. select name from student group by name,grade;

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

593b0f2f24ff4368b1d6b5bad7b1098b.png

  • 搭配聚合函数分组

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

  1. 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才能进行聚合操作.















字名 作用
where 子句

1)对查询结果进行分组前, 将不符合where条件的行去掉, 即在分组之前过滤数据.

2)where 后面不可以使用聚合函数

3)过滤行
having 子句

1)having 子句的作用是筛选满足条件的组, 即在分组之后过滤数据,.

2)having 后面可以使用聚合函数

3)过滤组

4)支持所有的where操作.

MySQL语句执行顺序:

1c359b7a83e24dc4bef510a674ade5a0.png

示例:

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

  1. 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 为了使查询数据更加直观明了, 本文提供以下代码供后续案例使用.

  1. drop table if exists classes;
  2. drop table if exists student;
  3. drop table if exists course;
  4. drop table if exists score;
  6. create table classes (id int primary key auto_increment, name varchar(20), `desc` varchar(100));
  8. create table student (id int primary key auto_increment, sn varchar(20),  name varchar(20), qq_mail varchar(20) ,
  9.         classes_id int);
  11. create table course(id int primary key auto_increment, name varchar(20));
  13. create table score(score decimal(3, 1), student_id int, course_id int);
  15. insert into classes(name, `desc`) values 
  16. ('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'),
  17. ('中文系2019级3班','学习了中国传统文学'),
  18. ('自动化2019级5班','学习了机械自动化');
  20. insert into student(sn, name, qq_mail, classes_id) values
  21. ('09982','张三','zhangsan@qq.com',1),
  22. ('00835','李四',null,1),
  23. ('00391','王五',null,1),
  24. ('00031','老六','xuxian@qq.com',1),
  25. ('00054','老师我作业忘带了',null,1),
  26. ('51234','芝士雪豹','xuebao@qq.com',2),
  27. ('83223','tellme',null,2),
  28. ('09527','老外学英文','foreigner@qq.com',2);
  30. insert into course(name) values
  31. ('Java'),('中国传统文化'),('计算机原理'),('语文'),('高等数学'),('英语');
  33. insert into score(score, student_id, course_id) values
  34. -- 张三
  35. (70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
  36. -- 李四
  37. (60, 2, 1),(59.5, 2, 5),
  38. -- 王五
  39. (33, 3, 1),(68, 3, 3),(99, 3, 5),
  40. -- 老六
  41. (67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
  42. -- 老师我作业忘带了
  43. (81, 5, 1),(37, 5, 5),
  44. -- 芝士雪豹
  45. (56, 6, 2),(43, 6, 4),(79, 6, 6),
  46. -- 老外学英文
  47. (80, 7, 2),(92, 7, 6);

5.1 内连接

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

0a19efc2b37f4712b50f406e02e3d937.png

语法:

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

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

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

  1. select * from 表1 join 表2 on...;
  3. 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

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

  1. 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表示

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

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

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

示例:

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

  1. mysql> select * from student;
  2. +------+------+
  3. | id   | name |
  4. +------+------+
  5. |    1 | 张三 |
  6. |    2 | 李四 |
  7. |    3 | 王五 |
  8. +------+------+
  9. mysql> select * from score;
  10. +------------+-------+
  11. | student_id | score |
  12. +------------+-------+
  13. |          1 |    90 |
  14. |          2 |    89 |
  15. |          4 |    77 |
  16. +------------+-------+
  17. --左外连接
  18. mysql> select * from student left join score on student.id = score.student_id;
  19. +------+------+------------+-------+
  20. | id   | name | student_id | score |
  21. +------+------+------------+-------+
  22. |    1 | 张三 |          1 |    90 |
  23. |    2 | 李四 |          2 |    89 |
  24. |    3 | 王五 |       NULL |  NULL |
  25. +------+------+------------+-------+
  26. --右外连接
  27. mysql> select * from student right join score on student.id = score.student_id;
  28. +------+------+------------+-------+
  29. | id   | name | student_id | score |
  30. +------+------+------------+-------+
  31. |    1 | 张三 |          1 |    90 |
  32. |    2 | 李四 |          2 |    89 |
  33. | NULL | NULL |          4 |    77 |
  34. +------+------+------------+-------+

5.3 自连接

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

语法:

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

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

a9abfe29e507410d9529932f43402b2a.png

示例:

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

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

46178e45e91f4468a813ece7e5ccc7d6.png

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

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

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

  1. 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;
  2. +-------+------------+-----------+-------+------------+-----------+
  3. | score | student_id | course_id | score | student_id | course_id |
  4. +-------+------------+-----------+-------+------------+-----------+
  5. |  98.5 |          1 |         3 |  70.5 |          1 |         1 |
  6. |  68.0 |          3 |         3 |  33.0 |          3 |         1 |
  7. +-------+------------+-----------+-------+------------+-----------+

6. 子查询

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

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

示例:

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

  1. --分步骤
  2. mysql> select * from student where name = "老六";
  3. +----+-------+------+---------------+------------+
  4. | id | sn    | name | qq_mail       | classes_id |
  5. +----+-------+------+---------------+------------+
  6. |  4 | 00031 | 老六 | xuxian@qq.com |          1 |
  7. +----+-------+------+---------------+------------+
  10. mysql> select * from student where classes_id = 1 and name!="老六";
  11. +----+-------+------------------+-----------------+------------+
  12. | id | sn    | name             | qq_mail         | classes_id |
  13. +----+-------+------------------+-----------------+------------+
  14. |  1 | 09982 | 张三             | zhangsan@qq.com |          1 |
  15. |  2 | 00835 | 李四             | NULL            |          1 |
  16. |  3 | 00391 | 王五             | NULL            |          1 |
  17. |  5 | 00054 | 老师我作业忘带了 | NULL            |          1 |
  18. +----+-------+------------------+-----------------+------------+
  20. --子查询一步完成
  21. mysql> select * from student where classes_id = (select classes_id from student where name = "老六" ) and name!="老六";
  22. +----+-------+------------------+-----------------+------------+
  23. | id | sn    | name             | qq_mail         | classes_id |
  24. +----+-------+------------------+-----------------+------------+
  25. |  1 | 09982 | 张三             | zhangsan@qq.com |          1 |
  26. |  2 | 00835 | 李四             | NULL            |          1 |
  27. |  3 | 00391 | 王五             | NULL            |          1 |
  28. |  5 | 00054 | 老师我作业忘带了 | NULL            |          1 |
  29. +----+-------+------------------+-----------------+------------+
  • 多行子查询:返回多行记录的子查询

示例:

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

  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 或者名字为”英文”的课程

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

示例:

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

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

发表评论

表情：
评论列表 （有 0 条评论，171人围观）

还没有评论，来说两句吧...

相关阅读

    相关 MySql--复杂查询

    MySql拥有着非常丰富的查询方式，除了之前总结的简单查询和子查询外，还提供复杂查询的方式，可以进行分组查询、多表查询和合并查询结果，现将之总结一下。 /\\\\

    Myth丶恋晨Myth丶恋晨/ 0 赞/ 352 阅读