MySQL的增删改查4(进阶)

还是基于我之前创建的学生成绩表

这是之前的博客：https://blog.csdn.net/question_mark/article/details/102809161

子查询：嵌套查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询

---

单行子查询：返回一行计入的子查询
案例：查询与张三同学的同班同学
👇👇👇

 select * from student where 
 classes_id = (select classes_id from student where name = '张三');
+----+------+--------+-----------------+------------+
| id | sn   | name   | qq_mail         | classes_id |
+----+------+--------+-----------------+------------+
|  1 |    1 | 张三   | zhangsan@qq.com |          1 |
|  2 |    2 | 李四   | lisi@qq.com     |          1 |
|  3 |    3 | 王五   | wangwu@qq.com   |          1 |
+----+------+--------+-----------------+------------+

👆👆👆

---

多行子查询：返回多行记录的子查询
案例：查询”C语言“或”高数“课程的成绩信息

👇👇👇1.[NOT] IN 关键字

--关联的方法
mysql> select * from score sco join course cou on sco.course_id=cou.id 
		where cou.name='C语言' or cou.name = 'Java';
+----+-------+------------+-----------+----+---------+
| id | score | student_id | course_id | id | name    |
+----+-------+------------+-----------+----+---------+
|  1 |  70.5 |          1 |         1 |  1 | C语言   |
|  5 |  60.0 |          2 |         1 |  1 | C语言   |
|  7 |  33.0 |          3 |         1 |  1 | C语言   |
| 10 |  67.0 |          4 |         1 |  1 | C语言   |
| 14 |  81.0 |          5 |         1 |  1 | C语言   |
|  3 |  33.0 |          1 |         5 |  5 | Java    |
|  6 |  59.5 |          2 |         5 |  5 | Java    |
|  9 |  99.0 |          3 |         5 |  5 | Java    |
| 12 |  56.0 |          4 |         5 |  5 | Java    |
| 15 |  37.0 |          5 |         5 |  5 | Java    |
+----+-------+------------+-----------+----+---------+


--使用IN
mysql> select * from score where course_id 	
		in 
		(select id from course where name = 'C语言' or name = '高数');
+----+-------+------------+-----------+
| id | score | student_id | course_id |
+----+-------+------------+-----------+
|  1 |  70.5 |          1 |         1 |
|  5 |  60.0 |          2 |         1 |
|  7 |  33.0 |          3 |         1 |
| 10 |  67.0 |          4 |         1 |
| 14 |  81.0 |          5 |         1 |
+----+-------+------------+-----------+
5 rows in set (0.35 sec)


--使用 not in 关键字  in改成not in   or改成and    = 改成!=
mysql>	select * from score where course_id not in 
		(select id from course where name!= 'C语言' and name != '高数');

上面三个方法，第一个是将两张表关联起来了，第二个第三个实际只访问了score这个表，没有关联，只是子查询
👆👆👆

---

👇👇👇1.[NOT] EXISTS 关键字

mysql> select * from score sco 
		where exists(   -- exists里面是子查询
		select * from course cou 
		where cou.id = sco.course_id and 
		(cou.name = 'C语言' or cou.name = 'Java'))
		;
+----+-------+------------+-----------+
| id | score | student_id | course_id |
+----+-------+------------+-----------+
|  1 |  70.5 |          1 |         1 |
|  3 |  33.0 |          1 |         5 |
|  5 |  60.0 |          2 |         1 |
|  6 |  59.5 |          2 |         5 |
|  7 |  33.0 |          3 |         1 |
|  9 |  99.0 |          3 |         5 |
| 10 |  67.0 |          4 |         1 |
| 12 |  56.0 |          4 |         5 |
| 14 |  81.0 |          5 |         1 |
| 15 |  37.0 |          5 |         5 |
+----+-------+------------+-----------+

相当于把外边的表score和里边的表course关联了。

---

在from子句中使用子查询：子查询语句出现在from子句中。这里要用到数据查询的技巧，把一个子查询当作一个临时表使用。
案例：查询所有比“机械172班”平均分高的成绩信息：

先查询机械172班的平均分

mysql> 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 = '机械172';
+----------+
| score    |
+----------+
| 68.83333 |
+----------+
1 row in set (0.01 sec)

连接了三张表查出来平均成绩
上面那个表可以作为临时表
最终在查询所有比“机械172班”平均分高的成绩信息：、

mysql> select * from score sco,
    -> (
    -> select
    -> avg(sco.score) avg_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 = '机械172') tmp
    -> where sco.score>tmp.avg_score;
+----+-------+------------+-----------+-----------+
| id | score | student_id | course_id | avg_score |
+----+-------+------------+-----------+-----------+
|  1 |  70.5 |          1 |         1 |  68.83333 |
|  2 |  98.5 |          1 |         3 |  68.83333 |
|  4 |  98.0 |          1 |         6 |  68.83333 |
|  9 |  99.0 |          3 |         5 |  68.83333 |
| 11 |  70.5 |          4 |         3 |  68.83333 |
| 13 |  72.0 |          4 |         6 |  68.83333 |
| 14 |  81.0 |          5 |         1 |  68.83333 |
+----+-------+------------+-----------+-----------+
7 rows in set (0.00 sec)

合并查询

mysql> select * from score where id = 1 
		union select * from score where id = 2;
+----+-------+------------+-----------+
| id | score | student_id | course_id |
+----+-------+------------+-----------+
|  1 |  70.5 |          1 |         1 |
|  2 |  98.5 |          1 |         3 |
+----+-------+------------+-----------+
2 rows in set (0.00 sec)

union要使用，前后列要一致

---

uinion
案例：查询id小于3，或者名字为“高数”的课程。
👇

mysql> select * from course where id<3
    -> union
    -> select * from course where name = '高数';
+----+--------------------+
| id | name               |
+----+--------------------+
|  1 | C语言              |
|  2 | 中国传统文化       |
|  4 | 高数               |
+----+--------------------+
3 rows in set (0.00 sec)

👆

---

union all
该操作符用于取得两个结果集的并集，使用该操作符时，不会去掉结果集中的重复行。
案例：查询id小于3或者名字为“C语言”的课程

👇👇👇
mysql> select * from course where id<3
    -> union
    -> select * from course where name = 'C语言';
+----+--------------------+
| id | name               |
+----+--------------------+
|  1 | C语言              |
|  2 | 中国传统文化       |
+----+--------------------+
2 rows in set (0.00 sec)
👆👆👆

👇👇👇
--带有All的
mysql> select * from course where id<3
    -> union all
    -> select * from course where name = 'C语言';
+----+--------------------+
| id | name               |
+----+--------------------+
|  1 | C语言              |
|  2 | 中国传统文化       |
|  1 | C语言              |
+----+--------------------+
3 rows in set (0.00 sec)
👆👆👆