- 在teacher表中查询与刘伟教师职称相同的教师号、姓名
拓展练习：在course表中检索与“计算机组成原理”先选课相同的课程的cno,cname,cpno
mysql> select cno,cname,cpno from course where cpno=
-> (select cpno from course where cname='计算机组成原理');
+-----+-----------------------+------+
| cno | cname | cpno |
+-----+-----------------------+------+
| kc3 | 微机原理 | kc1 |
| kc4 | 计算机组成原理 | kc1 |
+-----+-----------------------+------+
2 rows in set (0.00 sec)- 在teacher表与tc表中查询讲授课程号为kc2的教师姓名
mysql> select sno,cno,grade from sc
-> where sno=any(select sno from student where sdept=
-> (select sdept from student where sname='李涛')
-> );
+-----+-----+-------+
| sno | cno | grade |
+-----+-----+-------+
| st2 | kc2 | 88 |
| st2 | kc3 | 92 |
| st3 | kc1 | 85 |
| st3 | kc2 | 90 |
| st3 | kc3 | 60 |
+-----+-----+-------+
5 rows in set (0.00 sec)不包含李涛的学生选课信息：
mysql> select sno,cno,grade from sc
-> where sno=any(select sno from student where sdept=
-> (select sdept from student where sname='李涛')
-> and sname!='李涛'); -- 注意and在哪层子查询内
+-----+-----+-------+
| sno | cno | grade |
+-----+-----+-------+
| st3 | kc1 | 85 |
| st3 | kc2 | 90 |
| st3 | kc3 | 60 |
+-----+-----+-------+
3 rows in set (0.00 sec)- 在teacher表中查询其他系中比数学系所有教师工资都高的教师的姓名和工资。
拓展练习：在student表中查询所有比信息工程系年龄大的学生的sname、sage
mysql> select sname,sage from student where sage>all
-> (select sage from student where sdept='信息工程')
-> and sdept!='信息工程';
+-----------+------+
| sname | sage |
+-----------+------+
| 张飞 | 21 |
| 刘鹏程 | 21 |
+-----------+------+
2 rows in set (0.00 sec)
或者使用max关键字：
mysql> select sname,sage from student where sage>
-> (select max(sage) from student where sdept='信息工程')
-> and sdept!='信息工程';
+-----------+------+
| sname | sage |
+-----------+------+
| 张飞 | 21 |
| 刘鹏程 | 21 |
+-----------+------+
2 rows in set (0.00 sec)4.在student表中查询与“”李涛”不在同一系的学生信息
mysql> select * from student
-> where sdept not in
-> (select sdept from student where sname='李涛');
+-----+-----------+------+------+--------------+
| sno | sname | ssex | sage | sdept |
+-----+-----------+------+------+--------------+
| st1 | 杨云 | 女 | 20 | 信息工程 |
| st4 | 张飞 | 男 | 21 | 体育 |
| st5 | 刘备 | 男 | 20 | 中文 |
| st6 | 赵梦 | 女 | 20 | 音乐 |
| st9 | 刘鹏程 | 男 | 21 | 音乐 |
+-----+-----------+------+------+--------------+
5 rows in set (0.01 sec)5.查询与“”刘备”一同上课的学生学号和姓名
mysql> select sno,sname from student where sno in
-> (select sno from sc where cno in
-> (select cno from sc where sno in
-> (select sno from student where sname='刘备')
-> )
-> );
+-----+--------+
| sno | sname |
+-----+--------+
| st1 | 杨云 |
| st2 | 李涛 |
| st3 | 王伟 |
| st5 | 刘备 |
+-----+--------+
4 rows in set (0.01 sec)或：
mysql> select distinct student.sno as 学号,sname as 姓名
-> from student,sc
-> where student.sno=sc.sno and cno in
-> (select cno from sc,student where sc.sno=
-> student.sno and student.sno in
-> (select sno from student where student.sname='刘备')
-> );
+--------+--------+
| 学号 | 姓名 |
+--------+--------+
| st1 | 杨云 |
| st2 | 李涛 |
| st3 | 王伟 |
| st5 | 刘备 |
+--------+--------+
4 rows in set (0.00 sec)
