一、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)
SQL语句高级(六)
最新推荐文章于 2023-12-21 18:35:23 发布