MySQL实操基础全过程学习笔记（三）

（八）union和not in的使用

26.查询计算机系和电子工程系不同职称教师的tname和prof
理解：就是俩个系中俩个职称不一样的教师tname和prof
思路：
取完交集取并集

（1）找出计算机系中教师的职称
mysql> select prof from teacher where depart='计算机系';
+--------+
| prof   |
+--------+
| 副教授 |
| 助教   |
+--------+

（2）用not in找出电子工程系中不存在（1）中职称的tname与prof
select tname,prof from teacher where prof not in (select prof from teacher where depart='计算机系');
+-------+------+
| tname | prof |
+-------+------+
| 张旭  | 讲师 |
+-------+------+

（3）同理，找到计算机系中不同职称的教师
select tname,prof from teacher where prof not in (select prof from teacher where depart='电子工程系');
+-------+--------+
| tname | prof   |
+-------+--------+
| 李诚  | 副教授 |
+-------+--------+

（4）最后用union将两个结果放在一起
select tname,prof from teacher where prof not in (select prof from teacher where depart='计算机系')
union 
select tname,prof from teacher where prof not in (select prof from teacher where depart='电子工程系');
+-------+--------+
| tname | prof   |
+-------+--------+
| 张旭  | 讲师   |
| 李诚  | 副教授 |
+-------+--------+

涉及知识点：
（1）联合查询是多表查询的一种方式，在保证多个SELECT语句的查询字段数相同的情况下，合并多个查询的结果。联合查询经常应用在行分表操作中。
（2）union默认去除完全重复记录
（3）除此之外，若要对联合查询的记录进行排序等操作，需要使用圆括号“()”包裹每一个SELECT语句，在SELECT语句内或在联合查询的最后添加ORDER BY语句。并且若要排序生效，必须在ORDER BY后添加LIMIT限定联合查询排序的数量，通常推荐使用大于表记录数的任意值。

知识点（3）举例
以联合查询的方式对category_id为3的商品价格升序排序，其他降序排序
mysql> (SELECT id, name, price FROM sh_goods WHERE category_id <> 3
    -> ORDER by price DESC LIMIT 7)
    -> UNION
    -> (SELECT id, name, price FROM sh_goods WHERE category_id = 3
    -> ORDER by price ASC LIMIT 3);
+----+------------+---------+
| id | name       | price   |
+----+------------+---------+
|  4 |            | 5999.00 |
|  8 |            | 2000.00 |
|  5 |            | 1999.00 |
|  9 |            |  299.00 |
|  7 |            |  109.00 |
|  6 |            |   69.00 |
| 10 |            |   48.00 |
|  1 |            |    0.50 |
|  3 |            |    1.00 |
|  2 |            |   15.00 |
+----+------------+---------+

（九）any表示至少一个—desc降序

27.查询选修编号为3-105课程且成绩至少高于选修编号3-245同学的cno，sno和degree，并按degree从高到低次序排列

（1）查看课程3-245的成绩信息
select degree from score where cno='3-245';
+--------+
| degree |
+--------+
|     86 |
|     75 |
|     68 |
+--------+

（2）至少一个就是大于3-245的其中一个
select degree from score where degree>any(select degree from score where cno='3-245');
+--------+
| degree |
+--------+
|     86 |
|     75 |
|     92 |
|     76 |
|     91 |
|     85 |
|     79 |
|     81 |
|     88 |
|     78 |
+--------+

（3）利用order，以degree作为依据进行desc排序
select cno,sno,degree from score where degree>any(select degree from score where cno='3-245') order by degree desc;
+-------+-----+--------+
| cno   | sno | degree |
+-------+-----+--------+
| 3-105 | 103 |     92 |
| 3-105 | 105 |     91 |
| 3-105 | 105 |     88 |
| 3-245 | 103 |     86 |
| 6-166 | 103 |     85 |
| 6-166 | 109 |     81 |
| 6-166 | 105 |     79 |
| 3-105 | 109 |     78 |
| 3-105 | 109 |     76 |
| 3-245 | 105 |     75 |
+-------+-----+--------+

（十）ALL表示所有

28.查询选修编号为3-105课程且成绩高于选修编号3-245同学的cno，sno和degree
解读：
与27题不同的是本题是过于3-245成绩而不是至少高于

select cno,sno,degree from score where degree>ALL(select degree from score where cno='3-245');
+-------+-----+--------+
| cno   | sno | degree |
+-------+-----+--------+
| 3-105 | 103 |     92 |
| 3-105 | 105 |     91 |
| 3-105 | 105 |     88 |
+-------+-----+--------+

（十一）as取别名—union取并集

29.求所有学生和老师的name、sex和birthday

（1）用union进行联合查询即可
select sname,ssex,sbirthday from student 
union 
select tname,tsex,tbirthday from teacher;
+--------+------+---------------------+
| sname  | sse