# 找出工资最低的老师

select * from teacher
+----------------+-----------------+
| Name           | Money           |
+----------------+-----------------+
| Tom            |            1500 |
| David          |            1800 |
| Andy           |            2560 |
+----------------+-----------------+


Select Name From Teacher
Where Money <= all( Select Money From Teacher )

+----------------+
| Name           |
+----------------+
| Tom            |
+----------------+


# 找出高等数学课成绩不是最高的学生

mysql>select * from SC
+---------------+---------------+-----------------+
| SID           | CID           | Score           |
+---------------+---------------+-----------------+
| 001           | 高等数学      |              85 |
| 002           | 高等数学      |              90 |
| 003           | 高等数学      |              60 |
| 004           | 高等数学      |              75 |
+---------------+---------------+-----------------+


select SID from sc
where CID='高等数学' and Score < some( select Score From SC where CID='高等数学' )

+---------------+
| SID           |
+---------------+
| 001           |
| 003           |
| 004           |
+---------------+


# 找出所有课程都不及格的学生姓名

SC
+---------------+---------------+-----------------+
| SID           | CID           | Score           |
+---------------+---------------+-----------------+
| S001          | C001          |              40 |
| S001          | C002          |              65 |
| S002          | C001          |              59 |
| S002          | C002          |              45 |
| S002          | C003          |              18 |
+---------------+---------------+-----------------+
student
+---------------+----------------+
| SID           | Name           |
+---------------+----------------+
| S001          | Tom            |
| S002          | David          |
+---------------+----------------+


select Name from student
where 60 > all(select Score from SC where SID = student.SID )
+----------------+
| Name           |
+----------------+
| David          |
+----------------+


# 注意！

not in 等价于 <> all

posted @ 2019-03-08 16:15  Velscode  阅读(113)  评论(0编辑  收藏