mysql SELECT查詢

一、單表查詢

 

1、一般查詢。2、聚合函數、排序 3、別名。4、分組。5、分組過濾。6、限制顯示條目。7、雜項。

 

二、多表查詢

 

1、聯結查詢。2、子查詢。3、聯合查詢。

 

資料庫版本:5.5.46-MariaDB

 

說明一下這幾張表,這是在上馬哥課程的時候給的生成表的sql備份文件。

在文章最後我把它放到附件中。

 

註意:在linux上表名是區分大小寫的。

 

如果搞不清語句順序請看:help select

 

一、單表查詢

1、一般查詢

 

MariaDB [hellodb]> SELECT * FROM students;

+——-+—————+—–+——–+———+———–+

| StuID | Name          | Age | Gender | ClassID | TeacherID |

+——-+—————+—–+——–+———+———–+

|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |

|     2 | Shi Potian    |  22 | M      |       1 |         7 |

|     3 | Xie Yanke     |  53 | M      |       2 |        16 |

|     4 | Ding Dian     |  32 | M      |       4 |         4 |

|     5 | Yu Yutong     |  26 | M      |       3 |         1 |

|     6 | Shi Qing      |  46 | M      |       5 |      NULL |

|     7 | Xi Ren        |  19 | F      |       3 |      NULL |

|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |

|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |

|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |

|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |

|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |

|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |

|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |

|    15 | Duan Yu       |  19 | M      |       4 |      NULL |

|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |

|    17 | Lin Chong     |  25 | M      |       4 |      NULL |

|    18 | Hua Rong      |  23 | M      |       7 |      NULL |

|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |

|    20 | Diao Chan     |  19 | F      |       7 |      NULL |

|    21 | Huang Yueying |  22 | F      |       6 |      NULL |

|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |

|    23 | Ma Chao       |  23 | M      |       4 |      NULL |

|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |

|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |

+——-+—————+—–+——–+———+———–+

25 rows in set (0.05 sec)

 

MariaDB [hellodb]> SELECT StuID,Name,Age FROM students WHERE Age > 25;

+——-+————–+—–+

| StuID | Name         | Age |

+——-+————–+—–+

|     3 | Xie Yanke    |  53 |

|     4 | Ding Dian    |  32 |

|     5 | Yu Yutong    |  26 |

|     6 | Shi Qing     |  46 |

|    13 | Tian Boguang |  33 |

|    24 | Xu Xian      |  27 |

|    25 | Sun Dasheng  | 100 |

+——-+————–+—–+

7 rows in set (0.02 sec)

SELECT中的WHERE子句就是一個佈爾條件表達式,來判斷行是否區配表達式。隻要返回的為真,也就是不為0,則WHERE子句就為真,就會顯示匹配的行。

 

佈爾條件表達式操作符:

= 等於,用於數值或字符都可以。

<=> 也是等值比較,不過不會跟空產生意外情況。是跟空值比較的安全方式。

<> 不等於,這個就隻能用於數值瞭。

<

<=

>

>=

 

空字符跟空是不一樣的。 空字符也是一種字符串,也是有自己的ASCII碼和值的。           

IS NULL 判斷是否為空

IS NOT NULL 判斷是否為不空

LIKE

模糊匹配,支持通配符,% 百分號表示任意個任意字符。_ 下劃線任意單個字符。在能用等值比較或不等值比較的情況下不要用LIKE,性能差的多。

RLIKE,REGEXP

支持使用正則表達式。性能更低。LIKE, RLIKE隻能用來做字符的比較。

也可以完整的匹配數值,不過也沒有意義。

IN 

判斷指定的字段的值是否在給定的列表中, IN (‘abc','cc')

BETWEEN  AND

判斷指定的字段是否在給定的范圍之間。

如 x>=20 AND x<=40 這種,可以用 X BETWEEN 20 AND 40   

組合條件:  

NOT ,!

AND ,,&&

OR,||           

 

註意: 在mysql中隻要是字符型的在使用的時候都要加引號,而如果是數值型的,一定不能加引號。

 

例1:IS NULL, 判斷ClassID字段為空的記錄。隻顯示Name,Age,ClassID.

 

MariaDB [hellodb]> SELECT Name,Age,ClassID FROM students WHERE ClassID IS NULL;

+————-+—–+———+

| Name        | Age | ClassID |

+————-+—–+———+

| Xu Xian     |  27 |    NULL |

| Sun Dasheng | 100 |    NULL |

+————-+—–+———+

2 rows in set (0.00 sec)

 

例2:用LIKE來模糊匹配Name字段所有以X開頭的行。%通配任意個任意字符。

 

MariaDB [hellodb]> SELECT Name FROM students WHERE Name LIKE 'X%';

+————-+

| Name        |

+————-+

| Xie Yanke   |

| Xi Ren      |

| Xu Zhu      |

| Xue Baochai |

| Xiao Qiao   |

| Xu Xian     |

+————-+

6 rows in set (0.00 sec)

下面的效果跟上面的相同。這裡是用正則表達式匹配的。

1

MariaDB [hellodb]> SELECT Name FROM students WHERE Name RLIKE '^X.*';

 

例3:IN。下面是查找ClassID是1或3或5的記錄。隻顯示Name和ClassID字段。

 

MariaDB [hellodb]> SELECT Name,ClassID FROM students WHERE ClassID IN (1,3,5);

+————–+———+

| Name         | ClassID |

+————–+———+

| Shi Potian   |       1 |

| Yu Yutong    |       3 |

| Shi Qing     |       5 |

| Xi Ren       |       3 |

| Yue Lingshan |       3 |

| Wen Qingqing |       1 |

| Lu Wushuang  |       3 |

| Xu Zhu       |       1 |

| Xiao Qiao    |       1 |

+————–+———+

9 rows in set (0.00 sec)

 

MariaDB [hellodb]>

        

例4:BETWEEN  AND ,匹配一個范圍。年齡在30到50之間。

 

MariaDB [hellodb]> SELECT Name,Age FROM students WHERE Age BETWEEN 30 AND 50;

+————–+—–+

| Name         | Age |

+————–+—–+

| Ding Dian    |  32 |

| Shi Qing     |  46 |

| Tian Boguang |  33 |

+————–+—–+

3 rows in set (0.00 sec)

 

MariaDB [hellodb]>

 

例5:組合AND,gender為m,並且,Age大於30或等於20。這個括號是一定要有的,不然就變成“gender為M並且Age大於30,或者Age等於20。

 

MariaDB [hellodb]> SELECT * FROM students WHERE gender='M' AND (Age > 30 OR Age = 20);

+——-+————–+—–+——–+———+———–+

| StuID | Name         | Age | Gender | ClassID | TeacherID |

+——-+————–+—–+——–+———+———–+

|     3 | Xie Yanke    |  53 | M      |       2 |        16 |

|     4 | Ding Dian    |  32 | M      |       4 |         4 |

|     6 | Shi Qing     |  46 | M      |       5 |      NULL |

|    13 | Tian Boguang |  33 | M      |       2 |      NULL |

|    25 | Sun Dasheng  | 100 | M      |    NULL |      NULL |

+——-+————–+—–+——–+———+———–+

5 rows in set (0.01 sec)

 

想以年齡排序。可以用

ORDER BY  [ASC|DESC|字段]

 

ASC表示升序, DESC表示降序。  默認是ASC

以年齡降序排列。

 

MariaDB [hellodb]> SELECT * FROM students WHERE gender='M' AND (Age > 30 OR Age = 20) ORDER BY Age DESC;

+——-+————–+—–+——–+———+———–+

| StuID | Name         | Age | Gender | ClassID | TeacherID |

+——-+————–+—–+——–+———+———–+

|    25 | Sun Dasheng  | 100 | M      |    NULL |      NULL |

|     3 | Xie Yanke    |  53 | M      |       2 |        16 |

|     6 | Shi Qing     |  46 | M      |       5 |      NULL |

|    13 | Tian Boguang |  33 | M      |       2 |      NULL |

|     4 | Ding Dian    |  32 | M      |       4 |         4 |

+——-+————–+—–+——–+———+———–+

5 rows in set (0.01 sec)

 

MariaDB [hellodb]>

 

這些隻是一般的查詢,如果要統計數據,就要用聚合函數瞭。

 

2、聚合函數。

 

話說想統計下一共有多少人,或者女的有多少,男的有多少,平均年齡,最小最大年齡。

 

SUM(), AVG(), MAX(), MIN(), COUNT()

 

分別是求和、平均值、最大、最小、統計個數。這幾個是常用到的。

 

例6:SUM(),全體同學年齡總和。可以用WHERE加上條件,如男同學的年齡總和。

 

MariaDB [hellodb]> SELECT SUM(Age) FROM students;

+———-+

| SUM(Age) |

+———-+

|      685 |

+———-+

1 row in set (0.00 sec)

也可以顯示其它字段,不過也隻是一行。

 

例7:AVG(),全體同學的年齡平均值。

 

MariaDB [hellodb]> SELECT AVG(Age) FROM students;

+———-+

| AVG(Age) |

+———-+

|  27.4000 |

+———-+

1 row in set (0.00 sec)

 

例8:COUNT(), 統計一共多少學生。COUNT後面有的會使用*。COUNT(*),這樣也可以,不過性能差點。

 

MariaDB [hellodb]> SELECT COUNT(Name) FROM students;

+————-+

| COUNT(Name) |

+————-+

|          25 |

+————-+

1 row in set (0.00 sec)

 

我們也可以不讓它顯示上面的字段名稱,給它換一個名稱。 

 

3、AS  別名。

 

MariaDB [hellodb]> SELECT COUNT(Name) AS CC FROM students;

+—-+

| CC |

+—-+

| 25 |

+—-+

1 row in set (0.00 sec)

還有表也可以有別名,在多表查詢的時候再來說說。

 

下面男同學的最小年齡,並用別名顯示。

 

MariaDB [hellodb]> SELECT MIN(Age) AS Min_M FROM students WHERE gender = 'M';

+——-+

| Min_M |

+——-+

|    19 |

+——-+

1 row in set (0.00 sec)

可不可以一次性男女分開顯示各自的最小年齡。那就要用分組瞭。可以按性別gender來分組。這樣函數就會分別計算各組的數據。

 

4、分組。

 

GROUP BY 字段名

 

以字段的值分組。同一個值一個組。然後再通過用聚合函數來統計不同組中的信息。

 

現在以gender分組,也就是兩組。函數分別計算兩個組。

不過下面這個有點缺陷,不知道哪是女的,哪個是男的。

 

MariaDB [hellodb]> SELECT MIN(Age) FROM students GROUP BY gender;

+———-+

| MIN(Age) |

+———-+

|       17 |

|       19 |

+———-+

2 rows in set (0.00 sec)

 

MariaDB [hellodb]>

 

下面再顯示出來性別字段。

 

MariaDB [hellodb]> SELECT MIN(Age),gender FROM students GROUP BY gender;

+———-+——–+

| MIN(Age) | gender |

+———-+——–+

|       17 | F      |

|       19 | M      |

+———-+——–+

2 rows in set (0.00 sec)

 

MariaDB [hellodb]>

 

例:顯示不同班級的學生個數

 

MariaDB [hellodb]> SELECT Count(Name),ClassID FROM students GROUP BY classID;

+————-+———+

| Count(Name) | ClassID |

+————-+———+

|           2 |    NULL |

|           4 |       1 |

|           3 |       2 |

|           4 |       3 |

|           4 |       4 |

|           1 |       5 |

|           4 |       6 |

|           3 |       7 |

+————-+———+

8 rows in set (0.00 sec)

 

MariaDB [hellodb]>

 

不顯示沒有班級的。WHERE在分組之前先進行過濾,然後把數據再給GROUP BY來進行分組。

 

MariaDB [hellodb]> SELECT Count(Name),ClassID FROM students WHERE ClassID IS NOT NULL GROUP BY classID;

+————-+———+

| Count(Name) | ClassID |

+————-+———+

|           4 |       1 |

|           3 |       2 |

|           4 |       3 |

|           4 |       4 |

|           1 |       5 |

|           4 |       6 |

|           3 |       7 |

+————-+———+

7 rows in set (0.00 sec)

 

MariaDB [hellodb]>

 

例9:各個班級的平均年齡。

 

MariaDB [hellodb]> SELECT AVG(age),ClassID FROM students GROUP BY ClassID;

+———-+———+

| AVG(age) | ClassID |

+———-+———+

|  63.5000 |    NULL |

|  20.5000 |       1 |

|  36.0000 |       2 |

|  20.2500 |       3 |

|  24.7500 |       4 |

|  46.0000 |       5 |

|  20.7500 |       6 |

|  19.6667 |       7 |

+———-+———+

8 rows in set (0.00 sec)

 

MariaDB [hellodb]>

 

加上排序呢:

 

MariaDB [hellodb]> SELECT AVG(age),ClassID FROM students GROUP BY ClassID ORDER BY AVG(age);

+———-+———+

| AVG(age) | ClassID |

+———-+———+

|  19.6667 |       7 |

|  20.2500 |       3 |

|  20.5000 |       1 |

|  20.7500 |       6 |

|  24.7500 |       4 |

|  36.0000 |       2 |

|  46.0000 |       5 |

|  63.5000 |    NULL |

+———-+———+

8 rows in set (0.00 sec)

 

意思就是在分組之後,把各個分組重新排序瞭。以各個組的age字段的平均值來排序。

 

回來看分組:如果不想顯示平均年齡小於等於25的,怎麼辦呢。

 

5、分組過濾。

 

HAVING 用於對分組做條件過濾。

 

普及:WHERE是對表中的每一行做過濾,單位是行。 而HAVING是對每一個組做過濾,單位是組。

如:

 

MariaDB [hellodb]> SELECT AVG(age),ClassID FROM students GROUP BY ClassID HAVING AVG(age)>25;

+———-+———+

| AVG(age) | ClassID |

+———-+———+

|  63.5000 |    NULL |

|  36.0000 |       2 |

|  46.0000 |       5 |

+———-+———+

3 rows in set (0.00 sec)

 

MariaDB [hellodb]>

 

HAVING拿到手的都是一組一組的數據,所以也要求下平均值。然後不匹配的組,就刷掉。到瞭SELECT那裡,它求一下平均值是為瞭顯示。這是兩個不同的部分。

 

如果想找age小於AVG(age)之類的結果,這裡是查不出來的。在子查詢部分。

 

例10:顯示最少有3個同學的班級和該班級的人數。

 

MariaDB [hellodb]> SELECT ClassID,Count(Name) FROM students GROUP BY ClassID HAVING Count(Name) >= 3;

+———+————-+

| ClassID | Count(Name) |

+———+————-+

|       1 |           4 |

|       2 |           3 |

|       3 |           4 |

|       4 |           4 |

|       6 |           4 |

|       7 |           3 |

+———+————-+

6 rows in set (0.00 sec)

 

MariaDB [hellodb]>

 

這個表小,這樣顯示還可以,但是如果有上千上萬個的條目,一下子顯示出來就有點誇張瞭,占網絡帶寬不說,一下子出來這麼多,也看不完啊。

 

6、限制顯示條目的數量。

LIMIT

 

隻顯示3行。在最後加上limit 3就可以瞭。

 

MariaDB [hellodb]> SELECT * FROM students LIMIT 3;

+——-+————-+—–+——–+———+———–+

| StuID | Name        | Age | Gender | ClassID | TeacherID |

+——-+————-+—–+——–+———+———–+

|     1 | Shi Zhongyu |  22 | M      |       2 |         3 |

|     2 | Shi Potian  |  22 | M      |       1 |         7 |

|     3 | Xie Yanke   |  53 | M      |       2 |        16 |

+——-+————-+—–+——–+———+———–+

3 rows in set (0.00 sec)

 

這是從頭開始,顯示3行。如果想從中間開始。下面這個是從第5行開始,顯示3行。

 

MariaDB [hellodb]> SELECT * FROM students LIMIT 5,3;

+——-+———–+—–+——–+———+———–+

| StuID | Name      | Age | Gender | ClassID | TeacherID |

+——-+———–+—–+——–+———+———–+

|     6 | Shi Qing  |  46 | M      |       5 |      NULL |

|     7 | Xi Ren    |  19 | F      |       3 |      NULL |

|     8 | Lin Daiyu |  17 | F      |       7 |      NULL |

+——-+———–+—–+——–+———+———–+

3 rows in set (0.00 sec)

 

7、雜項。

 

DISTINCT  :  指定的結果相同的隻顯示一次。在SELECT 語句後面。

 

SQL_CACHE :  緩存此條語句至查詢緩存中。

 

SQL_NO_CACHE:  說明不緩存此條語句。

 

簡單的例子說明下第一個吧。如果顯示都有哪些年齡的同學,除瞭用分組外。還可以用DISTINCT。隻不過隻能顯示一個字段。

 

age字段重復的就不顯示,並且排序。 不排序也沒有關系。

 

MariaDB [hellodb]> SELECT DISTINCT age  FROM students ORDER BY age;

+—–+

| age |

+—–+

|  17 |

|  18 |

|  19 |

|  20 |

|  21 |

|  22 |

|  23 |

|  25 |

|  26 |

|  27 |

|  32 |

|  33 |

|  46 |

|  53 |

| 100 |

+—–+

15 rows in set (0.00 sec)

 

到這裡單表查詢就完瞭。我們來看看這麼多語句它的執行流程。

SELECT語句的執行流程:

FROM –> WHERE  –> GROUP BY –> HAVING  –> ORDER BY –> SELECT –> LIMIT

 

首先是FROM獲取表數據,然後WHERE篩選,再然後GROUP BY來分組,再然後HAVING給組再來一下過濾,再然後就是ORDER BY給剩下的組或是整張表的行排序,再然後才是SELECT把最終整理好的數據計算或者直接顯示出來,當然到達客戶端還要經過LIMIT限制。

 

二、多表查詢。

 

我們知道關系型資料庫就是為瞭降低冗餘,所以都是把內容記錄到多張表中,我們在查詢的時候要把多張表連起來才能查到所有數據。

 

說明一下,因為表的內容都貼出來的話就太多瞭,所以這裡就隻舉例子瞭,具體的表內容,朋友們自己下載看吧。

 

MariaDB [hellodb]> SHOW TABLES;

+——————-+

| Tables_in_hellodb |

+——————-+

| classes           |

| coc               |

| courses           |

| scores            |

| students          |

| teachers          |

| toc               |

+——————-+

7 rows in set (0.00 sec)

 

1、聯結查詢

 

聯結查詢: 先將幾張表join起來, 然後再根據join以後所產生的表,來進行查詢。

 

有:

 

交叉聯結、自然聯結、外聯結、自聯結。

 

交叉聯結:

 

就是各個表的各字段的值相乘的關系。各種連結,各種交叉。這裡也隻是提一下。

直接FROM表就是瞭。

1

MariaDB [hellodb]> SELECT * FROM students,coc,classes;

 

自然聯結: 

 

又叫內聯結或等值聯結,兩張表要有相同的字段可以建立聯結。用WHERE 聯結條件。一般情況下都是使用自然聯結。

 

舉例子先,上面的students表中有學生信息,而classes表中有班級的名稱。現在想顯示學生的名子和所對應的班級名稱。

 

那麼就需要student和classes建立結結。正好它們都有classID班級編號。所以:

 

MariaDB [hellodb]> SELECT students.Name,classes.Class FROM students,classes WHERE students.ClassID = classes.ClassID;

+—————+—————-+

| Name          | Class          |

+—————+—————-+

| Shi Zhongyu   | Emei Pai       |

| Shi Potian    | Shaolin Pai    |

| Xie Yanke     | Emei Pai       |

| Ding Dian     | Wudang Pai     |

*

*

23 rows in set (0.00 sec)

 

中間省略瞭,不然太多。  上面用WHERE來做兩個表的等值條件。

 

把字段全部顯示出來看看:

 

MariaDB [hellodb]> SELECT * FROM students,classes WHERE students.ClassID = classes.ClassID;

+——-+—————+—–+——–+———+———–+———+—————-+———-+

| StuID | Name          | Age | Gender | ClassID | TeacherID | ClassID | Class          | NumOfStu |

+——-+—————+—–+——–+———+———–+———+—————-+———-+

|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |       2 | Emei Pai       |        7 |

|     2 | Shi Potian    |  22 | M      |       1 |         7 |       1 | Shaolin Pai    |       10 |

|     3 | Xie Yanke     |  53 | M      |       2 |        16 |       2 | Emei Pai       |        7 |

|     4 | Ding Dian     |  32 | M      |       4 |         4 |       4 | Wudang Pai     |       12 |

|     5 | Yu Yutong     |  26 | M      |       3 |         1 |       3 | QingCheng Pai  |       11 |

|     6 | Shi Qing      |  46 | M      |       5 |      NULL |       5 | Riyue Shenjiao |       31 |

|     7 | Xi Ren        |  19 | F      |       3 |      NULL |       3 | QingCheng Pai  |       11 |

 

ClassID都是相等的。這裡還有一個問題就是,在多表連結的時候會有多個字段一樣的,所以在寫的時候要把表名也給寫上,就是這種格式students.ClassID之類的。但是有的表名又很長,這個時候就可以用別名瞭。在FROM後面的表名後面使用AS。FROM 表名 AS 別名

 

MariaDB [hellodb]> SELECT * FROM students AS STU,classes AS CLA WHERE STU.ClassID = CLA.ClassID;

+——-+—————+—–+——–+———+———–+———+—————-+———-+

| StuID | Name          | Age | Gender | ClassID | TeacherID | ClassID | Class          | NumOfStu |

+——-+—————+—–+——–+———+———–+———+—————-+———-+

|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |       2 | Emei Pai       |        7 |

|     2 | Shi Potian    |  22 | M      |       1 |         7 |       1 | Shaolin Pai    |       10 |

|     3 | Xie Yanke     |  53 | M      |       2 |        16 |       2 | Emei Pai       |        7 |

|     4 | Ding Dian     |  32 | M      |       4 |         4 |       4 | Wudang Pai     |       12 |

|     5 | Yu Yutong     |  26 | M      |       3 |         1 |       3 | QingCheng Pai  |       11 |

|     6 | Shi Qing      |  46 | M      |       5 |      NULL |       5 | Riyue Shenjiao |       31 |

 

多表連結也簡單,就是表多瞭以後會暈乎。所以主要問題就是要熟悉自己的各種表。

 

有內連結,自然就有外連結。內連結把表的字段的數值與另一張表連接起來,但是並不是所有記錄都可以連接起來,比如上面的students表中還有兩個人沒有顯示出來,因為他們沒有班級。

 

+——-+—————+—–+——–+———+———–+

| StuID | Name          | Age | Gender | ClassID | TeacherID |

+——-+—————+—–+——–+———+———–+

|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |

|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |

+——-+—————+—–+——–+———+———–+

如這兩位仁兄。那麼如果我也想把這兩個顯示出來。就要用到外連接瞭。

 

外聯結:外聯結又分為“左外連結和右外連結”。其實意思都一樣,就是以哪個為主,主表所有的都顯示出來,別一張表如果對不上就為NULL。

 

左外聯結    以左表為基準,右表沒有的為NULL.

 left_tb LEFT JOIN right_tb ON 連接條件

右外聯結    以右表為基準,左表沒有的為NULL.

 left_tb RIGHT JOIN right_tb ON 連接條件

全外聯結    以兩個表為基準,哪個沒有哪個為NULL.  mysql中沒有。

 

MariaDB [hellodb]> SELECT * FROM students LEFT JOIN classes ON students.ClassID=classes.ClassID;

+——-+—————+—–+——–+———+———–+———+—————-+———-+

| StuID | Name          | Age | Gender | ClassID | TeacherID | ClassID | Class          | NumOfStu |

+——-+—————+—–+——–+———+———–+———+—————-+———-+

|    18 | Hua Rong      |  23 | M      |       7 |      NULL |       7 | Ming Jiao      |       27 |

|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |       6 | Lianshan Pai   |       27 |

|    20 | Diao Chan     |  19 | F      |       7 |      NULL |       7 | Ming Jiao      |       27 |

|    21 | Huang Yueying |  22 | F      |       6 |      NULL |       6 | Lianshan Pai   |       27 |

|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |       1 | Shaolin Pai    |       10 |

|    23 | Ma Chao       |  23 | M      |       4 |      NULL |       4 | Wudang Pai     |       12 |

|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |    NULL | NULL           |     NULL |

|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |    NULL | NULL           |     NULL |

+——-+—————+—–+——–+———+———–+———+—————-+———-+

 

就是這樣的瞭。打個比方,如果classes的表有額外的ClassID,如8,9,10之類的,而students表中沒有,那麼也是不會顯示出來的。因為左外連接是以左表為準,管你右邊的表有什麼。而右外連接就是以右邊的表為準瞭。當然在寫表的時候把classes表寫左邊也是一樣的。

 

這些表還可以作三個表甚至四個表連接的操作。比如加上成績。大傢就自己試試吧。

 

2、子查詢:

 

在查詢中嵌套的查詢。

 

用於WHERE中的子查詢

 

1、用於比較表達式中的子查詢。子查詢的返回值隻能有一個

 

2、用於EXISTS中的子查詢,判斷存在與否。

 

3、用於IN中的子查詢,判斷存在於指定的列表中。

 

4、用於FROM中的子查詢,SELECT * FROM (SELECT clause) AS alias。這裡一定要用別名。

 

5、在SELECT中也可以用子語句的值來作為一個字段。

 

先解決查詢age>AVG(age)的問題。為什麼在上面那裡不能用,因為這種寫法就是錯的。一行還是一組呢。

 

1、放到WHERE後,WHERE語句的數據是一行一行的,age是可以表示當前行的age值。但是AVG(age)就有問題瞭,它隻能放在GROUP BY後面來計算組的平均值,或是SELECT後面全表的平均值。

 

2、放到HAVING後面,同樣的問題。是一組數據。

 

如果要查詢就要用子查詢先計算平均值。

 

查詢所有同學年齡大於平均年齡的。

 

MariaDB [hellodb]> SELECT Name,Age FROM students WHERE age > (SELECT AVG(age) FROM students);

+————–+—–+

| Name         | Age |

+————–+—–+

| Xie Yanke    |  53 |

| Ding Dian    |  32 |

| Shi Qing     |  46 |

| Tian Boguang |  33 |

| Sun Dasheng  | 100 |

+————–+—–+

5 rows in set (0.00 sec)

 

延伸一下:顯示平均年齡:

 

MariaDB [hellodb]> SELECT Name,Age,(SELECT AVG(age) FROM students) AS avg_age FROM students WHERE age > (SELECT AVG(age) FROM students);

+————–+—–+———+

| Name         | Age | avg_age |

+————–+—–+———+

| Xie Yanke    |  53 | 27.4000 |

| Ding Dian    |  32 | 27.4000 |

| Shi Qing     |  46 | 27.4000 |

| Tian Boguang |  33 | 27.4000 |

| Sun Dasheng  | 100 | 27.4000 |

+————–+—–+———+

5 rows in set (0.00 sec)

 

那麼再延伸一下,顯示在各個班級內同學,大於班級內年齡平均值的。

有點復雜,我這裡是這樣作的。

 

第一步:求出各個班內的平均年齡。

 

MariaDB [hellodb]> SELECT AVG(age),ClassID FROM students GROUP BY ClassID;

+———-+———+

| AVG(age) | ClassID |

+———-+———+

|  63.5000 |    NULL |

|  20.5000 |       1 |

|  36.0000 |       2 |

|  20.2500 |       3 |

|  24.7500 |       4 |

|  46.0000 |       5 |

|  20.7500 |       6 |

|  19.6667 |       7 |

+———-+———+

8 rows in set (0.00 sec)

 

第二步:以上面這個結果與students表建立連接。

 

MariaDB [hellodb]> SELECT * FROM students,(SELECT AVG(age),ClassID FROM students GROUP BY ClassID) AS avg_age WHERE students.ClassID=avg_age.ClassID;

+——-+—————+—–+——–+———+———–+———-+———+

| StuID | Name          | Age | Gender | ClassID | TeacherID | AVG(age) | ClassID |

+——-+—————+—–+——–+———+———–+———-+———+

|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |  36.0000 |       2 |

|     2 | Shi Potian    |  22 | M      |       1 |         7 |  20.5000 |       1 |

|     3 | Xie Yanke     |  53 | M      |       2 |        16 |  36.0000 |       2 |

|     4 | Ding Dian     |  32 | M      |       4 |         4 |  24.7500 |       4 |

|     5 | Yu Yutong     |  26 | M      |       3 |         1 |  20.2500 |       3 |

|     6 | Shi Qing      |  46 | M      |       5 |      NULL |  46.0000 |       5 |

|     7 | Xi Ren        |  19 | F      |       3 |      NULL |  20.2500 |       3 |

|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |  19.6667 |       7 |

|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |  20.7500 |       6 |

|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |  20.2500 |       3 |

|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |  20.7500 |       6 |

|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |  20.5000 |       1 |

|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |  36.0000 |       2 |

|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |  20.2500 |       3 |

|    15 | Duan Yu       |  19 | M      |       4 |      NULL |  24.7500 |       4 |

|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |  20.5000 |       1 |

|    17 | Lin Chong     |  25 | M      |       4 |      NULL |  24.7500 |       4 |

|    18 | Hua Rong      |  23 | M      |       7 |      NULL |  19.6667 |       7 |

|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |  20.7500 |       6 |

|    20 | Diao Chan     |  19 | F      |       7 |      NULL |  19.6667 |       7 |

|    21 | Huang Yueying |  22 | F      |       6 |      NULL |  20.7500 |       6 |

|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |  20.5000 |       1 |

|    23 | Ma Chao       |  23 | M      |       4 |      NULL |  24.7500 |       4 |

+——-+—————+—–+——–+———+———–+———-+———+

23 rows in set (0.00 sec)

 

第三步:這裡就直接作判斷就可以瞭。

 

MariaDB [hellodb]> SELECT * FROM students,(SELECT AVG(age) AS avg_age_col,ClassID FROM students GROUP BY ClassID) AS avg_age_tab WHERE students.ClassID=avg_age_tab.ClassID AND Age > avg_age_col ORDER BY students.ClassID;

+——-+—————+—–+——–+———+———–+————-+———+

| StuID | Name          | Age | Gender | ClassID | TeacherID | avg_age_col | ClassID |

+——-+—————+—–+——–+———+———–+————-+———+

|     2 | Shi Potian    |  22 | M      |       1 |         7 |     20.5000 |       1 |

|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |     20.5000 |       1 |

|     3 | Xie Yanke     |  53 | M      |       2 |        16 |     36.0000 |       2 |

|     5 | Yu Yutong     |  26 | M      |       3 |         1 |     20.2500 |       3 |

|    17 | Lin Chong     |  25 | M      |       4 |      NULL |     24.7500 |       4 |

|     4 | Ding Dian     |  32 | M      |       4 |         4 |     24.7500 |       4 |

|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |     20.7500 |       6 |

|    21 | Huang Yueying |  22 | F      |       6 |      NULL |     20.7500 |       6 |

|    18 | Hua Rong      |  23 | M      |       7 |      NULL |     19.6667 |       7 |

+——-+—————+—–+——–+———+———–+————-+———+

9 rows in set (0.00 sec)

 

MariaDB [hellodb]>

 

再來一個:要瘋瞭。這個我這樣寫總覺得有點復雜瞭。不知道大傢有沒有簡略點的。

如何顯示其成員數最少為3個的班級的同學中年齡大於同班同學平均年齡的同學?

 

MariaDB [hellodb]> SELECT SQL_NO_CACHE * FROM (SELECT AVG(age) AS A,ClassID FROM students WHERE ClassID IN (SELEct ClassID FROM students GROUP BY ClassID HAVING COUNT(*) >= 3) GROUP BY ClassID) AS s,students WHERE students.ClassID=s.ClassID AND age > A;

+———+———+——-+—————+—–+——–+———+———–+

| A       | ClassID | StuID | Name          | Age | Gender | ClassID | TeacherID |

+———+———+——-+—————+—–+——–+———+———–+

| 20.5000 |       1 |     2 | Shi Potian    |  22 | M      |       1 |         7 |

| 36.0000 |       2 |     3 | Xie Yanke     |  53 | M      |       2 |        16 |

| 24.7500 |       4 |     4 | Ding Dian     |  32 | M      |       4 |         4 |

| 20.2500 |       3 |     5 | Yu Yutong     |  26 | M      |       3 |         1 |

| 20.7500 |       6 |    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |

| 20.5000 |       1 |    16 | Xu Zhu        |  21 | M      |       1 |      NULL |

| 24.7500 |       4 |    17 | Lin Chong     |  25 | M      |       4 |      NULL |

| 19.6667 |       7 |    18 | Hua Rong      |  23 | M      |       7 |      NULL |

| 20.7500 |       6 |    21 | Huang Yueying |  22 | F      |       6 |      NULL |

+———+———+——-+—————+—–+——–+———+———–+

9 rows in set (0.00 sec)

 

MariaDB [hellodb]>

 

有人說mysql中對子查詢的優化不好,所以子查詢也要少用。

 

3、聯合查詢: 

 

把兩個或多個查詢語句的結果合並起來。UNION

這個簡單,就是一個結果附加在瞭另一個結果的下面。疊加起來瞭。

 

SELECT Name,Age FROM teachers UNION SELECT Name,Age FROM students;

把後面的語句結果連接在前面結果的下面。

UNION 可以有多個,可以連接多個查詢結果。

各個查詢結果的字段數要相同。

You May Also Like