Mysql數據庫理論基礎之五--SELECT單多表查詢、子查詢、別名

一、簡介

由MySQL AB公司開發，是最流行的開放源碼SQL數據庫管理系統，主要特點：

• 1、是一種數據庫管理系統

• 2、是一種關聯數據庫管理系統

• 3、是一種開放源碼軟件，且有大量可用的共享MySQL軟件

• 4、MySQL數據庫服務器具有快速、可靠和易于使用的特點

• 5、MySQL服務器工作在客戶端/服務器模式下，或嵌入式系統中

• 
  

• InnoDB存儲引擎將InnoDB表保存在一個表空間內，該表空間可由數個文件創建。這樣，表的大小就能超過單獨文件的最大容量。表空間可包括原始磁盤分區，從而使得很大的表成為可能。表空間的最大容量為64TB。

• 
  

二、MySQL 查詢引擎

數據庫實驗環境：如附件jiaowu.sql ，以下為導入方法

[root@lamp ~]# ll -h

-rw-r--r--.  1 root root  5.2K Jan  7  2015 jiaowu.sql

[root@lamp ~]# mysql -uroot -p < /root/jiaowu.sql 

Enter password: 

mysql> use jiaowu

Database changed

mysql> show tables;

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

| Tables_in_jiaowu |

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

| courses   |

| scores   |

| students  |

| tutors   |

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

4 rows in set (0.00 sec)

2.1.SELECT：查詢

    SELECT select-list FROM tb WHERE qualification  根據標準qualification查找對應的行

  查詢語句類型：  qualification條件  field領域  distinct獨特的，沒有重復的

    簡單查詢：

    多表查詢：

    子查詢：

SELECT * FROM tb_name:               #查詢tb_name表的所有信息

SELECT field1,field2 FROM tb_name:    #投影顯示所設定的領域條目（field），一個field就是一列

SELECT [DISTINCT] * FROM tb_name WHERE qualification；#從表中選擇符合條件的獨特的不重復的條目

FROM子句：

    表、多個表、其他SELECT語句

WHERE子句：

    布爾關系表達式  >= 、<= 、> 、< 、= 表示大于等于，小于等于，大于，小于，等于。

SELECT用法歸納：

2.2.邏輯關系：

    AND(與&&)   OR(或||)    NOT(非!)    XOR(異或)

mysql> SELECT Name,Age,Gender FROM students WHERE NOT Age>20 AND NOT Gender='M';

        #選擇查詢年齡不大于20，且性別不是男的數據,也可以寫成下面的條件

mysql> SELECT Name,Age,Gender FROM students WHERE NOT ( Age>20 OR Gender='M' );

2.3.特殊關系： BETWEEN ... AND ...  在兩者之間之間。

2.4. LIKE ''

       %：任意長度任意字符

       _：任意單個字符

Usage: 

SELECT Name FROM students WHERE Name LIKE 'Y%';   查找Name字段以Y開頭的數據。

SELECT Name FROM students WHERE Name LIKE '%ing%';  查找Name中必須包括ing的字段

SELECT Name FROM students WHERE Name LIKE 'Y____';  查找Name中Y后面至少跟著4個字符

mysql> SELECT Name FROM students WHERE Name LIKE 'Y%';

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

| Name         |

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

| YangGuo      |

| YueLingshang |

| YiLin        |

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

3 rows in set (0.00 sec)

mysql> SELECT Name FROM students WHERE Name LIKE '%ing%';

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

| Name         |

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

| GuoJing      |

| DingDian     |

| YueLingshang |

| LingHuchong  |

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

4 rows in set (0.00 sec)

mysql> SELECT Name FROM students WHERE Name LIKE 'Y____';

+-------+

| Name  |

+-------+

| YiLin |

+-------+

1 row in set (0.00 sec)

2.5.支持正則表達式：REGEXP或者RLIKE 

   Usage:#查找Name字段中，行尾為g的數據，行首為X或Y的數據

mysql> SELECT Name,Age FROM students WHERE Name RLIKE '.*g$';

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

| Name     | Age  |

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

| GuoJing      |   19 |

| HuangRong    |   16 |

| YueLingshang |   18 |

| LingHuchong  |   22 |

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

4 rows in set (0.00 sec)

mysql> SELECT Name,Age FROM students WHERE Name RLIKE '^[xy]';

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

| Name         | Age  |

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

| YangGuo      |   17 |

| YueLingshang |   18 |

| Xuzhu        |   26 |

| YiLin        |   19 |

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

4 rows in set (0.00 sec)

2.6.離散條件的查找：IN

   Usage: 查找Age字段為18或20或25歲的相關數據

mysql> SELECT Name,Age FROM students WHERE Age IN (18,20,25);

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

| Name     | Age  |

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

| DingDian     |   25 |

| YueLingshang |   18 |

| ZhangWuji    |   20 |

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

3 rows in set (0.00 sec)

2.7.有空值的時候，比較：IS NULL ,NOT NULL  ORDER BY ... 以...某字段升序排列

   Usage: 查找Name字段為空值的數據  

mysql> SELECT Name,Age FROM students WHERE Age IS NOT NULL ;

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

| Name     | Age  |

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

| GuoJing      |   19 |

| YangGuo      |   17 |

| DingDian     |   25 |

| HuFei        |   31 |

| HuangRong    |   16 |

| YueLingshang |   18 |

| ZhangWuji    |   20 |

| Xuzhu        |   26 |

| LingHuchong  |   22 |

| YiLin        |   19 |

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

10 rows in set (0.00 sec)

2.8.按照升序或者降序排列出查找的數據：ORDER BY field_name {ASC|DESC}; 

         ASC升序排列(默認值)  DESC降序排列

   SELECT Name,Age FROM students WHERE CID IS NOT NULL ORDER BY Name;

         #查找CID字段不為空的數據并按字段Name的升序排列；

  SELECT Name,Age FROM students WHERE CID IS NOT NULL ORDER BY Name DESC;

         #查找CID字段不為空的數據并按字段Name的降序排列；

mysql> SELECT Name,Age FROM students ORDER BY Age desc;

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

| Name     | Age  |

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

| HuFei        |   31 |

| Xuzhu        |   26 |

| DingDian     |   25 |

| LingHuchong  |   22 |

| ZhangWuji    |   20 |

| GuoJing      |   19 |

| YiLin        |   19 |

| YueLingshang |   18 |

| YangGuo      |   17 |

| HuangRong    |   16 |

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

10 rows in set (0.00 sec)

2.9.字段別名：AS

     Usage: SELECT Name AS Student_Name FROM student;

mysql> SELECT Name FROM student;

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

| Name   |

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

| Li Lianjie |

| Cheng Long |

| Yang Guo |

| Guo Jing |

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

4 rows in set (0.00 sec)

mysql> SELECT Name AS Student_Name FROM student;

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

|Student_Name |     #AS別名為Student_Name

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

| Li Lianjie |

| Cheng Long |

| Yang Guo  |

| Guo Jing  |

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

4 rows in set (0.00 sec)

2.10.LIMIT子句：LIMIT [offset,]Count  offset偏移多少，Count顯示多少

  Usage: SELECT Name AS Student_Name FROM student LIMIT 3;

           #查找顯示Name別名為Student_Name,只顯示前3個值

  Usage: SELECT Name AS Student_Name FROM student LIMIT 2,2;

           #查找顯示Name別名為Student_Name,偏移掉前2個數據不顯示，顯示第2個后的2個值

mysql> SELECT Name AS Student_Name FROM student LIMIT 3;

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

| Student_Name |

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

| Li Lianjie |

| Cheng Long |

| Yang Guo   |

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

3 rows in set (0.00 sec)

mysql> SELECT Name AS Student_Name FROM student LIMIT 2,2;

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

| Student_Name |

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

| Yang Guo   |

| Guo Jing   |

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

2 rows in set (0.00 sec)

2.11.聚合運算：

  SUM() 求和,   MIN() 最小值,   MAX() 最大值,

  AVG() 平均值,  COUNT() 統計字段中相同數值的個數；

  新建立一張表：

mysql>CREATE TABLE class(ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,Name CHAR(20) NOT NULL, Age TINYINT NOT NULL);

mysql>INSERT INTO class (Name,Age) VALUES ('Yang Guo',22),('Guo Jing',46),('Xiao Longnv',18),('Huang Rong',40);

mysql>  DESC class;

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

| Field | Type   | Null | Key | Default | Extra |

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

| ID  | int(11)   | NO   | PRI | NULL | auto_increment |

| Name | char(20)  | NO   |     | NULL |    |

| Age  | tinyint(4) | NO   |     | NULL |    |

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

3 rows in set (0.00 sec)

mysql> SELECT * FROM class;

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

| ID | Name        | Age |

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

|  1 | Yang Guo    |  22 |

|  2 | Guo Jing    |  46 |

|  3 | Xiao Longnv |  18 |

|  4 | Huang Rong  |  40 |

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

4 rows in set (0.00 sec)

mysql> SELECT SUM(Age) FROM class;

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

| SUM(Age) |

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

|   126 |

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

1 row in set (0.02 sec)

mysql> SELECT MAX(Age) FROM class;

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

| MAX(Age) |

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

|  46 |

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

1 row in set (0.00 sec)

mysql> SELECT MIN(Age) FROM class;

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

| MIN(Age) |

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

|   18 |

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

1 row in set (0.00 sec)

mysql> SELECT AVG(Age) FROM class;

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

| AVG(Age) |

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

|  31.5000 |

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

1 row in set (0.00 sec)

2.12.分組：GROUP BY ... HAVING qualification 根據...分組且滿足條件qualification

 對于GROUP BY的條件選擇需用HAVING作為條件篩選，而不是用WHERE

mysql> SELECT Age,Gender FROM class1 GROUP BY Gender; #以性別分組顯示

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

| Age | Gender |

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

|  18 | F      |

|  22 | M      |

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

2 rows in set (0.00 sec)

mysql> SELECT AVG(Age),Gender FROM class1 GROUP BY Gender; #求男同學和女同學的平均年齡。

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

| AVG(Age) | Gender |

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

|  29.0000 | F      |

|  34.0000 | M      |

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

2 rows in set (0.00 sec)

mysql> SELECT Name,AVG(Age) FROM class1 GROUP BY Gender HAVING Name RLIKE 'Y.*'; 

#求以Gender分組的年齡平局值，且只顯示以Y開頭的Name字段的平均值和姓名

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

| Name     | AVG(Age) |

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

| Yang Guo |  34.0000 |

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

1 row in set (0.00 sec)

2.13.多表查詢 (復合查詢)： 連接：

   2.13.1.交叉連接： （笛卡爾乘積）

SELECT * FROM students,course;   #查詢students和course表的內容

   2.13.2.自然連接：

查詢students表和courses表CID1=CID的選項，并顯示名稱,只保留具有等值關系的

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        |

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

8 rows in set (0.01 sec)

   2.13.3.外連接： 

         左外連接：左表 LEFT JOIN 右表 ON 條件

         右外連接：左表 RIGHT JOIN 右表 ON 條件

顯示左表中所有學生，并查看他們所選修的課程名稱，沒有選修課程的直接顯示NULL

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             |

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

10 rows in set (0.00 sec)

查看右表中所有選修課程，并查看有哪些學生選修了，課程沒有被選修的直接顯示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  |

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

13 rows in set (0.00 sec)

   2.13.4.自連接： 對于一個表自己的多個字段進行連接查詢

查看students表中TID和SID相同的字段，并顯示他們的學生名和老師名。AS取別名

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 |

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

5 rows in set (0.01 sec)

2.14.子查詢：

2.14.1.查詢語句里面嵌套其他的子查詢，比較操作中使用子查詢，子查詢只能返回單個值；

查詢年齡大于平均年齡的學生姓名

mysql> SELECT Name FROM students WHERE Age > (select AVG(Age) FROM students);

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

| Name    |

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

| DingDian    |

| HuFei       |

| Xuzhu       |

| LingHuchong |

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

4 rows in set (0.00 sec)

2.14.2. IN (): 使用子查詢

查詢學生年齡中與老師年齡一樣的學生姓名，IN表示子查詢可以是一組數值。

mysql> SELECT Name FROM students WHERE Age IN(select Age FROM students);

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

| Name   |

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

| GuoJing      |

| YangGuo      |

| DingDian     |

| HuFei        |

| HuangRong    |

| YueLingshang |

| ZhangWuji    |

| Xuzhu        |

| LingHuchong  |

| YiLin        |

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

10 rows in set (0.00 sec)

2.14.3.FROM中也可以插入子查詢：

從SELECT查詢的結構的表中再從中查詢其他符合條件的數據

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 |

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

5 rows in set (0.00 sec)

  2.15.聯合查詢： UNION ：把兩張表連接成一張表查詢顯示出來

把courses表中CID和Couse字段跟class表中的Name和Age字段聯合起來顯示為一張表

mysql> SELECT Name,Age FROM class;

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

| Name  | Age |

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

| Yang Guo   |  22 |

| Guo Jing   |  46 |

| Xiao Longnv |  18 |

| Huang Rong  |  40 |

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

4 rows in set (0.00 sec)

mysql> SELECT CID,Couse FROM courses;

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

| CID | Couse  |

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

|   1 | physics   |

|   2 | english   |

|   3 | chemistry |

|   4 | maths   |

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

4 rows in set (0.00 sec)

mysql> (SELECT Name,Age FROM class) UNION (SELECT CID,Couse FROM courses);

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

| Name   | Age   |

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

| Yang Guo   | 22   |

| Guo Jing   | 46   |

| Xiao Longnv | 18   |

| Huang Rong  | 40   |

| 1    | physics  |

| 2    | english  |

| 3    | chemistry |

| 4    | maths   |

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

8 rows in set (0.00 sec)

3.實例：

mysql> desc courses;

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

| Field | Type  | Null | Key | Default | Extra  |

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

| CID   | smallint(5) unsigned | NO   | PRI | NULL | auto_increment |

| Cname | varchar(100)  | NO   |    | NULL |     |

| TID   | smallint(6)  | NO   |    | NULL |     |

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

3 rows in set (0.01 sec)

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 |

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

10 rows in set (0.00 sec)

mysql> desc students;

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

| Field  | Type  | Null | Key | Default  | Extra  |

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

| SID | smallint(5) unsigned | NO | PRI | NULL  | auto_increment |

| Name | varchar(50) | NO |     | NULL  |    |

| Age | tinyint(3) unsigned | YES|     | NULL  |    |

| Gender | enum('F','M') | YES|    | M  |    |

| CID1 | smallint(5) unsigned | YES  |    | NULL  |    |

| CID2 | smallint(5) unsigned | YES |    | NULL |    |

| TID | smallint(6) | YES |    | NULL  |    |

| CreateTime| datetime | YES |    | 2012-04-06 10:00:00 |    |

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

8 rows in set (0.00 sec)

mysql> select * from students;

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

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

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

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

| 2 | YangGuo| 17 | M | 2 | 3 | 1 | 2012-04-06 10:00:00 |

| 3 | DingDian| 25 | M | 6 | 1 | 7 | 2012-04-06 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 | 2012-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 |

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

10 rows in set (0.00 sec)

mysql> desc tutors;

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

| Field | Type  | Null | Key | Default | Extra  |

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

| TID | smallint(5) unsigned | NO  | PRI | NULL | auto_increment |

| Tname | varchar(50) | NO  |     | NULL |     |

| Gender| enum('F','M') | YES |     | M |    |

| Age | tinyint(3) unsigned | YES |     | NULL|   |

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

4 rows in set (0.00 sec)

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 |

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

9 rows in set (0.00 sec)

  3.1.挑選出courses表中沒有被students表中的CID2學習的課程的課程名稱；

從students表中找出CID2不空且不重復的行，再從courses表中找到CID不在剛才查找的那些行里面的Cname值

mysql> SELECT Cname FROM courses WHERE CID NOT IN (SELECT DISTINCT CID2 FROM students WHERE CID2 IS NOT NULL);  #DISTINCT 除去重復

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

| Cname   |

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

| TaiJiquan   |

| Qianzhuwandushou |

| Qishangquan  |

| Wanliduxing  |

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

4 rows in set (0.01 sec)

  3.2.找出students表中CID2有兩個或者以上的同學學習了的，同一門課程的課程名稱；

查找一門課程至少2個同學學習，并顯示課程名稱。

mysql> SELECT Cname FROM courses WHERE CID IN (SELECT CID2 FROM students GROUP BY CID2 HAVING COUNT(CID2) >= 2);

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

| Cname   |

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

| Jinshejianfa |

| Qiankundanuoyi |

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

2 rows in set (0.01 sec)

  3.3.顯示每一個課程及其相關的老師，沒有老師教授的課程將其老師顯示為空；

msyql> SELECT t.Tname,c.Cname FROM tutors AS t RIGHT JOIN courses AS c ON t.TID=c.TID; 

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

| Tname     | Cname     |

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

| HuangYaoshi  | Hamagong   |

| Miejueshitai | TaiJiquan   |

| YuCanghai   | Yiyangzhi   |

| HongQigong  | Jinshejianfa  |

| OuYangfeng  | Qianzhuwandushou |

| YiDeng    | Qishangquan  |

| Jinlunfawang | Qiankundanuoyi  |

| HuYidao    | Wanliduxing   |

| Miejueshitai | Pixiejianfa    |

| Jinlunfawang | Jiuyinbaiguzhua  |

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

10 rows in set (0.01 sec)

  3.4.顯示每一位老師及其所教授的課程，沒有教授的課程保持為null;

msyql> SELECT t.Tname,c.Cname FROM tutors AS t LEFT JOIN courses AS c ON t.TID=c.TID; 

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

| Tname    | Cname     |

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

| HongQigong  | Jinshejianfa  |

| HuangYaoshi | Hamagong     |

| Miejueshitai | TaiJiquan    |

| Miejueshitai | Pixiejianfa   |

| OuYangfeng  | Qianzhuwandushou |

| YiDeng    | Qishangquan   |

| YuCanghai  | Yiyangzhi   |

| Jinlunfawang | Qiankundanuoyi  |

| Jinlunfawang | Jiuyinbaiguzhua  |

| HuYidao   | Wanliduxing  |

| NingZhongze  | NULL    |

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

11 rows in set (0.00 sec)

  3.5.顯示每位同學的CID1課程名及其講授了相關課程的老師名稱；

mysql>SELECT Name,Cname,Tname FROM students,courses,tutors WHERE students.CID1=courses.CID AND courses.TID=tutors.TID;

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

| Name   | Cname   | Tname   |

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

| GuoJing  | TaiJiquan  | Miejueshitai |

| YangGuo  | TaiJiquan  | Miejueshitai |

| DingDian | Qishangquan  | YiDeng  |

| HuFei   | Wanliduxing   | HuYidao |

| HuangRong | Qianzhuwandushou | OuYangfeng |

| YueLingshang | Wanliduxing  | HuYidao  |

| ZhangWuji  | Hamagong   | HuangYaoshi |

| Xuzhu   | TaiJiquan  | Miejueshitai |

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

8 rows in set (0.00 sec)

n0-end--78

以下為jiaowu.sql文本內容：

-- MySQL dump 10.11

--

-- Host: localhost    Database: jiaowu

-- ------------------------------------------------------

-- Server version5.5.20-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

/*!40103 SET TIME_ZONE='+00:00' */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--

-- Current Database: `jiaowu`

--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `jiaowu` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `jiaowu`;

--

-- Table structure for table `courses`

--

DROP TABLE IF EXISTS `courses`;

SET @saved_cs_client     = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `courses` (

  `CID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,

  `Cname` varchar(100) NOT NULL,

  `TID` smallint(6) NOT NULL,

  UNIQUE KEY `CID` (`CID`)

) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

SET character_set_client = @saved_cs_client;

--

-- Dumping data for table `courses`

--

LOCK TABLES `courses` WRITE;

/*!40000 ALTER TABLE `courses` DISABLE KEYS */;

INSERT INTO `courses` VALUES (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);

/*!40000 ALTER TABLE `courses` ENABLE KEYS */;

UNLOCK TABLES;

--

-- Table structure for table `scores`

--

DROP TABLE IF EXISTS `scores`;

SET @saved_cs_client     = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `scores` (

  `ID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,

  `SID` smallint(6) NOT NULL,

  `CID` smallint(6) NOT NULL,

  `Score` float DEFAULT NULL,

  UNIQUE KEY `ID` (`ID`)

) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

SET character_set_client = @saved_cs_client;

--

-- Dumping data for table `scores`

--

LOCK TABLES `scores` WRITE;

/*!40000 ALTER TABLE `scores` DISABLE KEYS */;

INSERT INTO `scores` VALUES (1,2,2,67),(2,2,3,71),(3,1,2,90),(4,1,7,45),(5,3,6,32),(6,3,1,99),(7,4,8,95),(8,4,10,36);

/*!40000 ALTER TABLE `scores` ENABLE KEYS */;

UNLOCK TABLES;

--

-- Table structure for table `students`

--

DROP TABLE IF EXISTS `students`;

SET @saved_cs_client     = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `students` (

  `SID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,

  `Name` varchar(50) NOT NULL,

  `Age` tinyint(3) unsigned DEFAULT NULL,

  `Gender` enum('F','M') DEFAULT 'M',

  `CID1` smallint(5) unsigned DEFAULT NULL,

  `CID2` smallint(5) unsigned DEFAULT NULL,

  `TID` smallint(6) DEFAULT NULL,

  `CreateTime` datetime DEFAULT '2012-04-06 10:00:00',

  UNIQUE KEY `SID` (`SID`)

) ENGINE=InnoDB AUTO_INCREMENT=3907 DEFAULT CHARSET=latin1;

SET character_set_client = @saved_cs_client;

--

-- Dumping data for table `students`

--

LOCK TABLES `students` WRITE;

/*!40000 ALTER TABLE `students` DISABLE KEYS */;

INSERT INTO `students` VALUES (1,'GuoJing',19,'M',2,7,3,'2012-04-06 10:00:00'),(2,'YangGuo',17,'M',2,3,1,'2012-04-06 10:00:00'),(3,'DingDian',25,'M',6,1,7,'2012-04-06 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,'2012-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');

/*!40000 ALTER TABLE `students` ENABLE KEYS */;

UNLOCK TABLES;

--

-- Table structure for table `tutors`

--

DROP TABLE IF EXISTS `tutors`;

SET @saved_cs_client     = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `tutors` (

  `TID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,

  `Tname` varchar(50) NOT NULL,

  `Gender` enum('F','M') DEFAULT 'M',

  `Age` tinyint(3) unsigned DEFAULT NULL,

  UNIQUE KEY `TID` (`TID`)

) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

SET character_set_client = @saved_cs_client;

--

-- Dumping data for table `tutors`

--

LOCK TABLES `tutors` WRITE;

/*!40000 ALTER TABLE `tutors` DISABLE KEYS */;

INSERT INTO `tutors` VALUES (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);

/*!40000 ALTER TABLE `tutors` ENABLE KEYS */;

UNLOCK TABLES;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2012-04-06  3:09:09