mysql复杂查询练习

1. 在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)           

1. 在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)           

1. 在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)