MySQL多表查询和子查询

多表查询：
    交叉链接：笛卡尔乘积
    自然连接：两个表建立等值关系
    外连接：两个表没有等值关系
        左外连接：以左边的表为准，不管右边的表有没有值
            ...IEFT JOIN... ON ...
        右外链接：以右边的表为准，不管左边的表有没有值
            ...RIGHT JOIN... ON ...
    自连接：自己连接自己
    
子查询：所有的数据都来自于一张表
    比较操作中使用子查询：子查询只能返回单个值；
    IN(): 使用子查询；
    在FROM中使用子查询；


实例：
显示students表和courses表的数据，默认是笛卡尔乘积显示
mysql> SELECT * FROM students,courses;
显示量太大，结果略。

显示students表中CID1字段等于courses表中CID字段的数据

mysql> SELECT * FROM students,courses WHERE students.CID1 = courses.CID;
+-----+--------------+------+--------+------+------+------+---------------------+-----+------------------+-----+
| SID | Name         | Age  | Gender | CID1 | CID2 | TID  | CreateTime          | CID | Cname            | TID |
+-----+--------------+------+--------+------+------+------+---------------------+-----+------------------+-----+
|   1 | GuoJing      |   19 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |   2 | TaiJiquan        |   3 |
|   2 | YangGuo      |   17 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |   2 | TaiJiquan        |   3 |
|   3 | DingDian     |   25 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |   6 | Qishangquan      |   5 |
|   4 | HuFei        |   31 | M      |    8 |   10 |    5 | 2012-04-06 10:00:00 |   8 | Wanliduxing      |   8 |
|   5 | HuangRong    |   16 | F      |    5 |    9 |    9 | 2012-04-06 10:00:00 |   5 | Qianzhuwandushou |   4 |
|   6 | YueLingshang |   18 | F      |    8 |    4 | NULL | 2012-04-06 10:00:00 |   8 | Wanliduxing      |   8 |
|   7 | ZhangWuji    |   20 | M      |    1 |    7 | NULL | 2012-04-06 10:00:00 |   1 | Hamagong         |   2 |
|   8 | Xuzhu        |   26 | M      |    2 |    4 | NULL | 2012-04-06 10:00:00 |   2 | TaiJiquan        |   3 |
+-----+--------------+------+--------+------+------+------+---------------------+-----+------------------+-----+

显示选修第一门课的学生姓名和课程姓名
mysql> SELECT Name,Cname FROM students,courses WHERE students.CID1 = courses.CID;  
+--------------+------------------+
| Name         | Cname            |
+--------------+------------------+
| GuoJing      | TaiJiquan        |
| YangGuo      | TaiJiquan        |
| DingDian     | Qishangquan      |
| HuFei        | Wanliduxing      |
| HuangRong    | Qianzhuwandushou |
| YueLingshang | Wanliduxing      |
| ZhangWuji    | Hamagong         |
| Xuzhu        | TaiJiquan        |
+--------------+------------------+

使用表别名做多表连接
mysql> SELECT s.Name,c.Cname FROM students AS s,courses AS c WHERE s.CID1=c.CID;
+--------------+------------------+
| Name         | Cname            |
+--------------+------------------+
| GuoJing      | TaiJiquan        |
| YangGuo      | TaiJiquan        |
| DingDian     | Qishangquan      |
| HuFei        | Wanliduxing      |
| HuangRong    | Qianzhuwandushou |
| YueLingshang | Wanliduxing      |
| ZhangWuji    | Hamagong         |
| Xuzhu        | TaiJiquan        |
+--------------+------------------+


使用左外链接显示每名同学选修的第一门课程，如果没有选修的课程，那么显示空
mysql> SELECT s.Name,c.Cname FROM students AS s LEFT JOIN courses AS c ON s.CID1=c.CID;
+--------------+------------------+
| Name         | Cname            |
+--------------+------------------+
| GuoJing      | TaiJiquan        |
| YangGuo      | TaiJiquan        |
| DingDian     | Qishangquan      |
| HuFei        | Wanliduxing      |
| HuangRong    | Qianzhuwandushou |
| YueLingshang | Wanliduxing      |
| ZhangWuji    | Hamagong         |
| Xuzhu        | TaiJiquan        |
| LingHuchong  | NULL             |
| YiLin        | NULL             |
+--------------+------------------+


右外链接显示没门课程选择的同学名称，如果没有人选择那么显示空值
mysql> SELECT s.Name,c.Cname FROM students AS s RIGHT JOIN courses AS c ON s.CID1=c.CID;    
+--------------+------------------+
| Name         | Cname            |
+--------------+------------------+
| ZhangWuji    | Hamagong         |
| GuoJing      | TaiJiquan        |
| YangGuo      | TaiJiquan        |
| Xuzhu        | TaiJiquan        |
| NULL         | Yiyangzhi        |
| NULL         | Jinshejianfa     |
| HuangRong    | Qianzhuwandushou |
| DingDian     | Qishangquan      |
| NULL         | Qiankundanuoyi   |
| HuFei        | Wanliduxing      |
| YueLingshang | Wanliduxing      |
| NULL         | Pixiejianfa      |
| NULL         | Jiuyinbaiguzhua  |
+--------------+------------------+

自链接学生和老师的名字都来自一张表
TID为SID的老师，条件TID=SID
mysql> SELECT c.Name AS student,s.Name AS teacher FROM students AS c,students AS s WHERE c.TID=s.SID;
+-----------+-------------+
| student   | teacher     |
+-----------+-------------+
| GuoJing   | DingDian    |
| YangGuo   | GuoJing     |
| DingDian  | ZhangWuji   |
| HuFei     | HuangRong   |
| HuangRong | LingHuchong |
+-----------+-------------+

查询同学的年龄大于平均年龄的
mysql> SELECT Name FROM students WHERE Age > (SELECT AVG(age)FROM students);
+-------------+
| Name        |
+-------------+
| DingDian    |
| HuFei       |
| Xuzhu       |
| LingHuchong |
+-------------+


FROM中使用子查询
mysql> SELECT Name,Age FROM (SELECT Name,Age FROM students) AS t WHERE t.Age >= 20;
+-------------+------+
| Name        | Age  |
+-------------+------+
| DingDian    |   25 |
| HuFei       |   31 |
| ZhangWuji   |   20 |
| Xuzhu       |   26 |
| LingHuchong |   22 |
+-------------+------+

复合查询，将学生表中的学生年龄和老师表中的老师年龄在一张表中显示出来
mysql> (SELECT Name,Age FROM students) UNION (SELECT Tname,Age FROM tutors);         
+--------------+------+
| Name         | Age  |
+--------------+------+
| GuoJing      |   19 |
| YangGuo      |   17 |
| DingDian     |   25 |
| HuFei        |   31 |
| HuangRong    |   16 |
| YueLingshang |   18 |
| ZhangWuji    |   20 |
| Xuzhu        |   26 |
| LingHuchong  |   22 |
| YiLin        |   19 |
| HongQigong   |   93 |
| HuangYaoshi  |   63 |
| Miejueshitai |   72 |
| OuYangfeng   |   76 |
| YiDeng       |   90 |
| YuCanghai    |   56 |
| Jinlunfawang |   67 |
| HuYidao      |   42 |
| NingZhongze  |   49 |
+--------------+------+

本文出自 “梅花香自苦寒来” 博客，请务必保留此出处http://wangjunkang.blog.51cto.com/8809812/1582666