mysql from多表查询_MySQL多表查询

1、挑选出courses表中没有被student中CID2学习的课程的课程名称：

查看courses表中内容：

mysql> SELECT * FROM courses;

+-----+-------------------+-----+

| CID | Cname             | TID |

+-----+-------------------+-----+

|   1 | Hamagong          |   2 |

|   2 | TaiJiquan         |   3 |

|   3 | Yiyangzhi         |   6 |

|   4 | Jinshejianfa       |   1 |

|   5 | Qianzhuwandushou    |   4 |

|   6 | Qishangquan       |   5 |

|   7 | Qiankundanuoyi     |   7 |

|   8 | Wanliduxing       |   8 |

|   9 | Pixiejianfa       |   3 |

|  10 | Jiuyinbaiguzhua    |   7 |

+-----+-------------------+-----+

查看教师表内容：

mysql> SELECT * FROM tutors;

+-----+--------------+--------+------+

| TID | Tname        | Gender | Age  |

+-----+--------------+--------+------+

|   1 | HongQigong   | M      |   93 |

|   2 | HuangYaoshi  | M      |   63 |

|   3 | Miejueshitai | F      |   72 |

|   4 | OuYangfeng   | M      |   76 |

|   5 | YiDeng       | M      |   90 |

|   6 | YuCanghai    | M      |   56 |

|   7 | Jinlunfawang | M      |   67 |

|   8 | HuYidao      | M      |   42 |

|   9 | NingZhongze  | F      |   49 |

+-----+--------------+--------+------+

查看student表中内容：

mysql> SELECT * FROM student;

+------+--------------+------+--------+------+------+------+---------------------+

| SID  | Name         | Age  | Gender | CID1 | CID2 | TID  | CreatTime           |

+------+--------------+------+--------+------+------+------+---------------------+

|    1 | Guojing       |   19 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |

|    2 | Yangguo       |    1 | M      |    2 |    3 |    1 | 2013-04-06 10:00:00 |

|    3 | Dingdian      |   25 | M      |    2 |    3 |    1 | 2012-04-04 10:00:00 |

|    4 | Hufei        |   31 | M      |    8 |   10 |    5 | 2012-04-06 10:00:00 |

|    5 | Huangrong      |   16 | F      |    5 |    9 |    9 | 2012-04-06 10:00:00 |

|    6 | YueLingshang    |   18 | F      |    8 |    4 | NULL | 2014-04-06 10:00:00 |

|    7 | Zhangwuji      |   20 | M      |    1 |    7 | NULL | 2012-04-06 10:00:00 |

|    8 | Xuzhu         |   26 | M      |    2 |    4 | NULL | 2012-04-06 10:00:00 |

|    9 | LingHuchong     |   22 | M      |   11  | NULL   | NULL   | 2012-04-06 10:00:00 |

|   10 | Yilin         |   19 | F      |   18  | NULL    | NULL  | 2012-04-06 10:00:00 |

| 3907 | stu2          |   23 | F      |    4 |    1 |    6 | 2012-04-06 10:00:00 |

| 3908 | stu3          |   23 | F      |    4 |    1 |    6 | 2012-04-06 10:00:00 |

+------+--------------+------+--------+------+------+------+---------------------+

mysql> SELECT Cname FROM courses WHERE CID IN (SELECT DISTINCT CID2 FROM student WHERE CID2 IS NOT NULL);

+-----------------+

| Cname           |

+-----------------+

| Hamagong        |

| Yiyangzhi       |

| Jinshejianfa    |

| Qiankundaluoyi   |

| Pixiejianfa     |

| Jiuyinbaguzhang  |

+-----------------+

2、挑选没有教授任何课程的老师：

mysql> SELECT Tname FROM tutors WHERE TID NOT IN (SELECT DISTINCT TID FROM courses);

+-------------+

| Tname       |

+-------------+

| Ningzhongze |

+-------------+

3、找出students表中CID1有两个或两个以上同学学习了同一门课程的课程名称

mysql> select CID1,COUNT(CID1) FROM students group by CID1 HAVING COUNT(CID1)>1;

+------+-------------+

| CID1 | COUNT(CID1) |

+------+-------------+

|   2 |        3 |

|   8 |        2 |

+------+-------------+

mysql> SELECT Cname FROM courses WHERE CID IN (SELECT COUNT(CID1) FROM students group by CID1 HAVING COUNT(CID1)

>1);+-----------+

| Cname     |

+-----------+

| TaiJiquan |

| Yiyangzhi |

+-----------+

4、显示每一个老师及其所教授的课程：没有教授的课程保持为NULL:

mysql> SELECT tutors.Tname,courses.Cname FROM tutors,courses WHERE tutors.TID=courses.TID;

+--------------+-------------------+

| Tname        | Cname             |

+--------------+-------------------+

| HuangYaoshi    | Hamagong          |

| Miejueshitai   | TaiJiquan         |

| YuCanghai     | Yiyangzhi         |

| HongQigong    | Jinshejianfa      |

| OuYangfeng    | Qianzhuwandushou    |

| YiDeng       | Qishangquan       |

| Jinlunfawang   | Qiankundanuoyi    |

| HuYidao      | Wanliduxing       |

| Miejueshitai   | Pixiejianfa       |

| Jinlunfawang   | Jiuyinbaiguzhua   |

+--------------+-------------------+

5、显示每一个课程及其相关的老师，没有老师教授的课程及其老师显示为NULL

mysql> SELECT courses.Cname,tutors.Tname FROM courses LEFT JOIN tutors ON courses.TID=tutors.TID;

+-------------------+--------------+

| Cname             | Tname        |

+-------------------+--------------+

| Hamagong          | HuangYaoshi  |

| TaiJiquan         | Miejueshitai |

| Yiyangzhi         | YuCanghai    |

| Jinshejianfa      | HongQigong   |

| Qianzhuwandushou    | OuYangfeng   |

| Qishangquan       | YiDeng       |

| Qiankundanuoyi    | Jinlunfawang |

| Wanliduxing       | HuYidao      |

| Pixiejianfa       | Miejueshitai |

| Jiuyinbaiguzhua   | Jinlunfawang |

+-------------------+--------------+

6、显示每位同学CID1课程及其教授了相关课程的老师名称：

mysql> SELECT courses.Cname,tutors.Tname FROM courses RIGHT JOIN tutors ON courses.TID=tutors.TID;

+-------------------+--------------+

| Cname             | Tname        |

+-------------------+--------------+

| Jinshejianfa      | HongQigong   |

| Hamagong         | HuangYaoshi  |

| TaiJiquan        | Miejueshitai |

| Pixiejianfa       | Miejueshitai |

| Qianzhuwandushou    | OuYangfeng   |

| Qishangquan        | YiDeng       |

| Yiyangzhi         | YuCanghai    |

| Qiankundanuoyi     | Jinlunfawang |

| Jiuyinbaiguzhua     | Jinlunfawang |

| Wanliduxing       | HuYidao      |

| NULL            | NingZhongze  |

+-------------------+--------------+