快捷導(dǎo)航

MySQL自連接與子查詢方式

更新時(shí)間：2024年09月05日 09:25:36 作者：_周游

這篇文章主要介紹了MySQL自連接與子查詢方式,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教

1. 自連接

自連接是表自身與自身做笛卡爾積，在SQL中進(jìn)行條件查詢，都是指定某一列或多個(gè)列之間進(jìn)行關(guān)系運(yùn)算，無法進(jìn)行行與行之間的運(yùn)算，在某些情況下需要對(duì)行與行之間進(jìn)行關(guān)系運(yùn)算，就要使用到自連接。

自連接的本質(zhì)是將行轉(zhuǎn)為列；

示例：顯示所有“課程id為3”比“課程id為1”成績(jī)高的成績(jī)信息：

（成績(jī)信息在score表中）

（1）對(duì)score進(jìn)行自連接（別名求笛卡爾積）并刪除無效信息：

mysql> select* from score as s1, score as s2 where s1.student_id = s2.student_id;

（2）選出第一列id=1的課程與第二列id=3的課程：

mysql> select* from score as s1, score as s2
    -> where s1.student_id = s2.student_id
    -> and s1.course_id = 1
    -> and s2.course_id = 3;

（該結(jié)果表示有三個(gè)同學(xué)同時(shí)選修了這兩門課程）

（3）增加左列成績(jī)小于右列成績(jī)條件，SQL指令與查詢結(jié)果為：

mysql> select* from score as s1,score as s2
    -> where s1.student_id = s2.student_id
    -> and s1.course_id = 1
    -> and s2.course_id = 3
    -> and s1.score < s2.score;
+-------+------------+-----------+-------+------------+-----------+
| score | student_id | course_id | score | student_id | course_id |
+-------+------------+-----------+-------+------------+-----------+
|  70.5 |          1 |         1 |  98.5 |          1 |         3 |
|  33.0 |          3 |         1 |  68.0 |          3 |         3 |
+-------+------------+-----------+-------+------------+-----------+
2 rows in set (0.00 sec)

注：

（1）不能直接進(jìn)行自連接：

mysql> select* from score,score;
ERROR 1066 (42000): Not unique table/alias: 'score'

需要為表指定兩個(gè)別名，即：

mysql> select* from score as s1, score as s2;

2. 子查詢（嵌套查詢）

子查詢是指嵌入其他SQL語句中的select語句，即將多個(gè)查詢語句合并為一個(gè)語句；

2.1 子查詢分類

（1）單行子查詢：查詢結(jié)果只有一條記錄；

（2）多行子查詢：查詢結(jié)果為多條記錄；

2.2 單行子查詢示例1：查詢不想畢業(yè)同學(xué)的同班同學(xué)

（1）分步查詢SQL指令及查詢結(jié)果為：

mysql> select classes_id from student where name="不想畢業(yè)";
+------------+
| classes_id |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

mysql> select name from student where classes_id =1;
+------------+
| name       |
+------------+
| 黑旋風(fēng)李逵 |
| 菩提老祖   |
| 白素貞     |
| 許仙       |
| 不想畢業(yè)   |
+------------+
5 rows in set (0.00 sec)

（2）子查詢SQL指令及查詢結(jié)果為：

mysql> select name from student where classes_id = (select classes_id from student where name="不想畢業(yè)");
+------------+
| name       |
+------------+
| 黑旋風(fēng)李逵 |
| 菩提老祖   |
| 白素貞     |
| 許仙       |
| 不想畢業(yè)   |
+------------+
5 rows in set (0.00 sec)

即將條件查詢的某一個(gè)值替換為一個(gè)select查詢語句；

2.3 多行子查詢示例2：查詢語文或英語課程的信息成績(jī)

先查詢出兩個(gè)課程的課程id，再根據(jù)course_id在score表中查詢；

（1）分步查詢SQL指令及查詢結(jié)果為：

mysql> select id from course where name="語文" or name="英文";
+----+
| id |
+----+
|  4 |
|  6 |
+----+
2 rows in set (0.00 sec)

mysql> select* from score where course_id in(4,6);
+-------+------------+-----------+
| score | student_id | course_id |
+-------+------------+-----------+
|  98.0 |          1 |         6 |
|  72.0 |          4 |         6 |
|  43.0 |          6 |         4 |
|  79.0 |          6 |         6 |
|  92.0 |          7 |         6 |
+-------+------------+-----------+
5 rows in set (0.00 sec)

（2）子查詢SQL指令及查詢結(jié)果為：

mysql> select* from score where course_id in(select id from course where name="語文" or name="英文");
+-------+------------+-----------+
| score | student_id | course_id |
+-------+------------+-----------+
|  98.0 |          1 |         6 |
|  72.0 |          4 |         6 |
|  43.0 |          6 |         4 |
|  79.0 |          6 |         6 |
|  92.0 |          7 |         6 |
+-------+------------+-----------+
5 rows in set (0.00 sec)

3. 合并查詢

合并查詢就是將兩個(gè)查詢語句的結(jié)果合并到一起；

3.1 示例1：查詢id=3或者名字為英文的課程

（1）使用邏輯或?qū)崿F(xiàn)查詢：

mysql> select* from course where id<3 or name="英文";
+----+--------------+
| id | name         |
+----+--------------+
|  1 | Java         |
|  2 | 中國(guó)傳統(tǒng)文化 |
|  6 | 英文         |
+----+--------------+
3 rows in set (0.00 sec)

（2）使用union關(guān)鍵字進(jìn)行合并查詢：

mysql> select* from course where id<3 union select* from course where name="英文";
+----+--------------+
| id | name         |
+----+--------------+
|  1 | Java         |
|  2 | 中國(guó)傳統(tǒng)文化 |
|  6 | 英文         |
+----+--------------+
3 rows in set (0.00 sec)

注：

（1）union與邏輯或的區(qū)別：

邏輯或只能對(duì)一張表的查詢結(jié)果進(jìn)行合并，但union可以對(duì)多張表的查詢結(jié)果進(jìn)行合并（要求多個(gè)結(jié)果的列須對(duì)應(yīng)）；

（2）union與union all的區(qū)別：

使用union關(guān)鍵字對(duì)多個(gè)查詢結(jié)果進(jìn)行合并時(shí)會(huì)自動(dòng)去重，但unionall不會(huì)去重；