Mysql數(shù)據(jù)庫理論基礎(chǔ)之五--SELECT單多表查詢、子查詢、別名

發(fā)布時(shí)間：2020-08-29 15:53:29 來源：網(wǎng)絡(luò) 閱讀：4690 作者：風(fēng)過_無痕欄目：數(shù)據(jù)庫

一、簡介

由MySQL AB公司開發(fā)，是最流行的開放源碼SQL數(shù)據(jù)庫管理系統(tǒng)，主要特點(diǎn)：

1、是一種數(shù)據(jù)庫管理系統(tǒng)
2、是一種關(guān)聯(lián)數(shù)據(jù)庫管理系統(tǒng)
3、是一種開放源碼軟件，且有大量可用的共享MySQL軟件
4、MySQL數(shù)據(jù)庫服務(wù)器具有快速、可靠和易于使用的特點(diǎn)
5、MySQL服務(wù)器工作在客戶端/服務(wù)器模式下，或嵌入式系統(tǒng)中
InnoDB存儲(chǔ)引擎將InnoDB表保存在一個(gè)表空間內(nèi)，該表空間可由數(shù)個(gè)文件創(chuàng)建。這樣，表的大小就能超過單獨(dú)文件的最大容量。表空間可包括原始磁盤分區(qū)，從而使得很大的表成為可能。表空間的最大容量為64TB。

二、MySQL 查詢引擎

數(shù)據(jù)庫實(shí)驗(yàn)環(huán)境：如附件jiaowu.sql ，以下為導(dǎo)入方法

[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 根據(jù)標(biāo)準(zhǔn)qualification查找對(duì)應(yīng)的行

查詢語句類型： qualification條件 field領(lǐng)域 distinct獨(dú)特的，沒有重復(fù)的

簡單查詢：

多表查詢：

子查詢：

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

SELECT field1,field2 FROM tb_name: #投影顯示所設(shè)定的領(lǐng)域條目（field），一個(gè)field就是一列

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

FROM子句：

表、多個(gè)表、其他SELECT語句

WHERE子句：

布爾關(guān)系表達(dá)式 >= 、<= 、> 、< 、= 表示大于等于，小于等于，大于，小于，等于。

Mysql數(shù)據(jù)庫理論基礎(chǔ)之五--SELECT單多表查詢、子查詢、別名

SELECT用法歸納：

2.2.邏輯關(guān)系：

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

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

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

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

2.3.特殊關(guān)系： BETWEEN ... AND ... 在兩者之間之間。

2.4. LIKE ''

%：任意長度任意字符

_：任意單個(gè)字符

Usage:

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

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

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

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.支持正則表達(dá)式：REGEXP或者RLIKE

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

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歲的相關(guān)數(shù)據(jù)

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.有空值的時(shí)候，比較：IS NULL ,NOT NULL ORDER BY ... 以...某字段升序排列

Usage: 查找Name字段為空值的數(shù)據(jù)

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.按照升序或者降序排列出查找的數(shù)據(jù)：ORDER BY field_name {ASC|DESC};

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

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

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

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

#查找CID字段不為空的數(shù)據(jù)并按字段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個(gè)值

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

#查找顯示Name別名為Student_Name,偏移掉前2個(gè)數(shù)據(jù)不顯示，顯示第2個(gè)后的2個(gè)值

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.聚合運(yùn)算：

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

AVG() 平均值, COUNT() 統(tǒng)計(jì)字段中相同數(shù)值的個(gè)數(shù)；

新建立一張表：

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;

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

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

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

| 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 根據(jù)...分組且滿足條件qualification

對(duì)于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; #求男同學(xué)和女同學(xué)的平均年齡。

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

| 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.多表查詢 (復(fù)合查詢)： 連接：

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

SELECT * FROM students,course; #查詢students和course表的內(nèi)容

2.13.2.自然連接：

查詢students表和courses表CID1=CID的選項(xiàng)，并顯示名稱,只保留具有等值關(guān)系的

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 條件

顯示左表中所有學(xué)生，并查看他們所選修的課程名稱，沒有選修課程的直接顯示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)

查看右表中所有選修課程，并查看有哪些學(xué)生選修了，課程沒有被選修的直接顯示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.自連接：對(duì)于一個(gè)表自己的多個(gè)字段進(jìn)行連接查詢

查看students表中TID和SID相同的字段，并顯示他們的學(xué)生名和老師名。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.查詢語句里面嵌套其他的子查詢，比較操作中使用子查詢，子查詢只能返回單個(gè)值；

查詢年齡大于平均年齡的學(xué)生姓名

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 (): 使用子查詢

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

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查詢的結(jié)構(gòu)的表中再從中查詢其他符合條件的數(shù)據(jù)

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.聯(lián)合查詢： UNION ：把兩張表連接成一張表查詢顯示出來

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

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.實(shí)例：

mysql> desc courses;

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

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

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

| 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;

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

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

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

| 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;

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

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

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

| 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學(xué)習(xí)的課程的課程名稱；

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

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

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

| Cname |

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

| TaiJiquan |

| Qianzhuwandushou |

| Qishangquan |

| Wanliduxing |

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

4 rows in set (0.01 sec)

3.2.找出students表中CID2有兩個(gè)或者以上的同學(xué)學(xué)習(xí)了的，同一門課程的課程名稱；

查找一門課程至少2個(gè)同學(xué)學(xué)習(xí)，并顯示課程名稱。

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.顯示每一個(gè)課程及其相關(guān)的老師，沒有老師教授的課程將其老師顯示為空；

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.顯示每位同學(xué)的CID1課程名及其講授了相關(guān)課程的老師名稱；

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文本內(nèi)容：

-- 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

向AI問一下細(xì)節(jié)

Mysql數(shù)據(jù)庫理論基礎(chǔ)之五--SELECT單多表查詢、子查詢、別名

一、簡介

二、MySQL 查詢引擎

猜你喜歡

最新資訊

相關(guān)推薦

相關(guān)標(biāo)簽

Mysql數(shù)據(jù)庫理論基礎(chǔ)之五--SELECT單多表查詢、子查詢、別名