SQL语句高级（六）

一、all
查询选修编号为3-105且成绩高于选修编号为3-245课程的同学的cno、sno和degree字段
mysql> select * from score where cno='3-105';
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 100 | 3-105 |     75 |
| 103 | 3-105 |     81 |
| 104 | 3-105 |     59 |
+-----+-------+--------+
3 rows in set (0.00 sec)

mysql> select * from score where cno='3-245';
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 100 | 3-245 |     75 |
| 102 | 3-245 |     95 |
| 103 | 3-245 |     89 |
| 104 | 3-245 |     98 |
| 105 | 3-245 |     89 |
+-----+-------+--------+
5 rows in set (0.00 sec)

mysql> select * from score where cno='3-105'
    -> and degree > all(select degree from score where cno='3-245');
Empty set (0.00 sec)

mysql> select * from score where cno='3-245'
    -> and degree > all(select degree from score where cno='3-105');
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 102 | 3-245 |     95 |
| 103 | 3-245 |     89 |
| 104 | 3-245 |     98 |
| 105 | 3-245 |     89 |
+-----+-------+--------+

二、as和union
查询所有教师和同学的name、sex和birthday：
mysql> select tname,tsex,tbirthday from teacher;
+-------+-------+---------------------+
| tname | tsex  | tbirthday           |
+-------+-------+---------------------+
| aaa   | man   | 1986-12-10 00:00:00 |
| ddd   | man   | 1984-05-05 00:00:00 |
| ccc   | man   | 1966-10-04 00:00:00 |
| bbb   | woman | 1945-02-09 00:00:00 |
+-------+-------+---------------------+
4 rows in set (0.03 sec)

mysql> select sname,ssex,sbirthday from student;
+--------+-------+---------------------+
| sname  | ssex  | sbirthday           |
+--------+-------+---------------------+
| Java   | man   | 1977-09-01 00:00:00 |
| C      | woman | 1975-08-21 00:00:00 |
| C++    | woman | 1976-02-11 00:00:00 |
| C#     | woman | 1974-12-01 00:00:00 |
| Python | man   | 1977-10-11 00:00:00 |
| JS     | woman | 1974-11-11 00:00:00 |
+--------+-------+---------------------+
6 rows in set (0.00 sec)

mysql> select tname,tsex,tbirthday from teacher
    -> union
    -> select sname,ssex,sbirthday from student;
+--------+-------+---------------------+
| tname  | tsex  | tbirthday           |
+--------+-------+---------------------+
| aaa    | man   | 1986-12-10 00:00:00 |
| ddd    | man   | 1984-05-05 00:00:00 |
| ccc    | man   | 1966-10-04 00:00:00 |
| bbb    | woman | 1945-02-09 00:00:00 |
| Java   | man   | 1977-09-01 00:00:00 |
| C      | woman | 1975-08-21 00:00:00 |
| C++    | woman | 1976-02-11 00:00:00 |
| C#     | woman | 1974-12-01 00:00:00 |
| Python | man   | 1977-10-11 00:00:00 |
| JS     | woman | 1974-11-11 00:00:00 |
+--------+-------+---------------------+
10 rows in set (0.03 sec)
--用as优化（别名！）
mysql> select tname as name,tsex as sex,tbirthday as birthday from teacher
    -> union
    -> select sname,ssex,sbirthday from student;
+--------+-------+---------------------+
| name   | sex   | birthday            |
+--------+-------+---------------------+
| aaa    | man   | 1986-12-10 00:00:00 |
| ddd    | man   | 1984-05-05 00:00:00 |
| ccc    | man   | 1966-10-04 00:00:00 |
| bbb    | woman | 1945-02-09 00:00:00 |
| Java   | man   | 1977-09-01 00:00:00 |
| C      | woman | 1975-08-21 00:00:00 |
| C++    | woman | 1976-02-11 00:00:00 |
| C#     | woman | 1974-12-01 00:00:00 |
| Python | man   | 1977-10-11 00:00:00 |
| JS     | woman | 1974-11-11 00:00:00 |
+--------+-------+---------------------+

三、union（只选女的）
mysql> select tname as name,tsex as sex,tbirthday as birthday from teacher
    -> where tsex='woman'
    -> union
    -> select sname,ssex,sbirthday from student where ssex='woman';
+------+-------+---------------------+
| name | sex   | birthday            |
+------+-------+---------------------+
| bbb  | woman | 1945-02-09 00:00:00 |
| C    | woman | 1975-08-21 00:00:00 |
| C++  | woman | 1976-02-11 00:00:00 |
| C#   | woman | 1974-12-01 00:00:00 |
| JS   | woman | 1974-11-11 00:00:00 |
+------+-------+---------------------+
5 rows in set (0.09 sec)