MySQL高級查詢

為了便于說明下文中的例子，首先創(chuàng)建如下三張表：

mysql> SELECT * FROM student;
+----+------+-----+-----+
| id | name | age | sex |
+----+------+-----+-----+
|  1 | 張三 |  18 | 男  |
|  2 | 李四 |  20 | 女  |
+----+------+-----+-----+
2 rows in set (0.00 sec)

mysql> SELECT * FROM subject;
+------+---------+---------+------------------+
| id   | subject | teacher | description      |
+------+---------+---------+------------------+
| 1001 | 語文    | 王老師  | 本次考試比較簡單 |
| 1002 | 數學    | 劉老師  | 本次考試比較難單 |
+------+---------+---------+------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM score;
+----+------------+------------+-------+
| id | student_id | subject_id | score |
+----+------------+------------+-------+
|  1 |          1 |       1001 |    80 |
|  2 |          2 |       1001 |    60 |
|  3 |          1 |       1002 |    70 |
|  4 |          2 |       1002 |  60.5 |
+----+------------+------------+-------+

子查詢

子查詢可以把一個查詢嵌套在另一個查詢當中的查詢。

SELECT colunm FROM table1 WHERE condition

子查詢一般分為內部查詢和外部查詢，內部查詢即為condition語句，外部查詢即上例中的SELECT colunm FROM table1 WHERE..

如查詢score表中的語文成績：

mysql> SELECT score FROM score WHERE subject_id = (
    -> SELECT id FROM subject WHERE subject='語文'
    -> );
+-------+
| score |
+-------+
|    80 |
|    60 |
+-------+

該例中SELECT score FROM score WHERE subject_id =..為外部查詢，SELECT id FROM subject WHERE subject='語文'為內部查詢。

子查詢的結果均為外部查詢的表，而不能包括內部查詢的結果。對于上例，也就說是，我們查詢到的結果，均為score中的內容，而不能獲得subject表中的內容。

聯(lián)結查詢

子查詢只能顯示一張表的數據，但我們想要同時查詢顯示多張表的數據時，就可以使用聯(lián)結查詢。

內聯(lián)結

又叫等值聯(lián)結，基于兩個表之間的相等測試。查詢兩個或多個表的交集。
語法：

SELECT * FROM table1 AS t1 INNER JOIN table2 AS t2 ON t1.column = t2.column;
  • 例子
    同時顯示學生姓名，科目和成績
mysql> SELECT name , subject , score
    -> FROM score AS s
    -> INNER JOIN student AS stu ON s.student_id = stu.id
    -> INNER JOIN subject AS sub ON s.subject_id = sub.id;
+------+---------+-------+
| name | subject | score |
+------+---------+-------+
| 張三 | 語文    |    80 |
| 李四 | 語文    |    60 |
| 張三 | 數學    |    70 |
| 李四 | 數學    |  60.5 |
+------+---------+-------+

description列只存在于subject表中，不是三張表的交集。因此，當我們內聯(lián)結三張表后是查詢不到description的。

mysql> SELECT desctiption
    -> FROM score AS s
    -> INNER JOIN student AS stu ON s.student_id = stu.id
    -> INNER JOIN subject AS sub ON s.subject_id = sub.id;
ERROR 1054 (42S22): Unknown column 'desctiption' in 'field list'

外聯(lián)結

以某張表為主,取出里面的所有記錄, 然后每條與另外一張表進行連接: 不管能不能匹配上條件,最終都會保留: 能匹配,正確保留; 不能匹配,其他表的字段都置空NULL.

外聯(lián)結分為左外聯(lián)結和右外聯(lián)結。左外聯(lián)結即以左邊的表為主表；右外聯(lián)結即以右邊的表為主表。所謂左右，即為代碼中表的順序，第一個為左，最后一個為右。

  • 例子

為了使外聯(lián)結的示例更明顯，刪除student表中id=1的記錄

mysql> DELETE FROM student WHERE id = 1;
Query OK, 1 row affected (0.20 sec)

mysql> SELECT * FROM student;
+----+------+-----+-----+
| id | name | age | sex |
+----+------+-----+-----+
|  2 | 李四 |  20 | 女  |
+----+------+-----+-----+

以score為主表，左外聯(lián)結subject和student

mysql> SELECT * FROM score AS s
    -> LEFT JOIN subject AS sub ON s.subject_id = sub.id
    -> LEFT JOIN student AS stu ON s.student_id = stu.id;
+----+------------+------------+-------+------+---------+---------+------------------+------+------+------+------+
| id | student_id | subject_id | score | id   | subject | teacher | description      | id   | name | age  | sex  |
+----+------------+------------+-------+------+---------+---------+------------------+------+------+------+------+
|  2 |          2 |       1001 |    60 | 1001 | 語文    | 王老師  | 本次考試比較簡單 |    2 | 李四 |   20 | 女   |
|  4 |          2 |       1002 |  60.5 | 1002 | 數學    | 劉老師  | 本次考試比較難單 |    2 | 李四 |   20 | 女   |
|  1 |          1 |       1001 |    80 | 1001 | 語文    | 王老師  | 本次考試比較簡單 | NULL | NULL | NULL | NULL |
|  3 |          1 |       1002 |    70 | 1002 | 數學    | 劉老師  | 本次考試比較難單 | NULL | NULL | NULL | NULL |
+----+------------+------------+-------+------+---------+---------+------------------+------+------+------+------+

注意到三四行的學生相關信息為NULL，因為該兩行的student_id為1，student表中不存在該學生，因此查不到。

而如果以student表為主表，則查詢的結果只有兩條記錄。這是因為student中只有id為2的學生，三張表中與該生有關的記錄只有兩條。

如下例，以student為主表，右外聯(lián)結score和subject

mysql> SELECT * FROM score AS s
    -> RIGHT JOIN subject AS sub ON s.subject_id = sub.id
    -> RIGHT JOIN student AS stu ON s.student_id = stu.id;
+------+------------+------------+-------+------+---------+---------+------------------+----+------+-----+-----+
| id   | student_id | subject_id | score | id   | subject | teacher | description      | id | name | age | sex |
+------+------------+------------+-------+------+---------+---------+------------------+----+------+-----+-----+
|    2 |          2 |       1001 |    60 | 1001 | 語文    | 王老師  | 本次考試比較簡單 |  2 | 李四 |  20 | 女  |
|    4 |          2 |       1002 |  60.5 | 1002 | 數學    | 劉老師  | 本次考試比較難單 |  2 | 李四 |  20 | 女  |
+------+------------+------------+-------+------+---------+---------+------------------+----+------+-----+-----+

因此，外聯(lián)結就是在主表的記錄基礎上，查詢聯(lián)結的表的并集。

組合查詢

組合多個查詢語句

  1. 對單個表執(zhí)行多個查詢，按照單個查詢返回數據。

查詢score表中分數大于70并且學生id 為1的記錄：

mysql> SELECT * FROM score WHERE score > 70
    -> UNION
    -> SELECT * FROM score WHERE student_id = 1;
+----+------------+------------+-------+
| id | student_id | subject_id | score |
+----+------------+------------+-------+
|  1 |          1 |       1001 |    80 |
|  3 |          1 |       1002 |    70 |
+----+------------+------------+-------+

當然也可以使用AND寫為一句：

mysql> SELECT * FROM score WHERE score > 70 AND student_id = 1;
+----+------------+------------+-------+
| id | student_id | subject_id | score |
+----+------------+------------+-------+
|  1 |          1 |       1001 |    80 |
+----+------------+------------+-------+
  • 在單個查詢中，從不同表中返回類似結構的數據。
    比如，復制student表的結構創(chuàng)建表student_2，并添加一條記錄：
mysql> CREATE TABLE student_2 LIKE student;
Query OK, 0 rows affected (0.96 sec)

mysql> INSERT INTO student_2 VALUES (3,'王五',17,'男');
Query OK, 1 row affected (0.10 sec)

組合查詢

mysql> SELECT * FROM student
    -> UNION
    -> SELECT * FROM student_2;
+----+------+-----+-----+
| id | name | age | sex |
+----+------+-----+-----+
|  2 | 李四 |  20 | 女  |
|  3 | 王五 |  17 | 男  |
+----+------+-----+-----+
?著作權歸作者所有,轉載或內容合作請聯(lián)系作者
【社區(qū)內容提示】社區(qū)部分內容疑似由AI輔助生成，瀏覽時請結合常識與多方信息審慎甄別。
平臺聲明：文章內容（如有圖片或視頻亦包括在內）由作者上傳并發(fā)布，文章內容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務。

相關閱讀更多精彩內容

友情鏈接更多精彩內容