MySQL資料庫多表查詢
目錄
-
- 子查詢
- 聯合查詢
- 交叉連接配接
- 内連接配接
- 外連接配接
- 左外連接配接 left outer
- 右外連結：right outer
- 完全外連接配接
- 三張表查詢
- 自連接配接
- 多表查詢
- 查詢結果來自于多張表，即多表查詢
子查詢：在SQL語句嵌套着查詢語句，性能較差，基于某語句的查詢結果再次進行的查詢
聯合查詢：UNION
交叉連接配接：笛卡爾乘積
内連接配接：
等值連接配接：讓表之間的字段以“等值”建立連接配接關系
不等值連接配接:不等值連接配接查詢就是無條件判斷，若查詢多個表内的資料，其中的資料不會同步，各自把各自的展現出來，沒有任何關聯。
自然連接配接:去掉重複列的等值連接配接
外連接配接：
左外連接配接：FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col
右外連接配接：FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col
自連接配接：本表和本表進行連接配接查詢 常用在WHERE子句中的子查詢 - 用于比較表達式中的子查詢；子查詢僅能傳回單個值(查詢s1表中大于平均年齡的人)
MariaDB [hellodb]> select * from s1 where age > (select avg(age) from s1);
+-------+--------------+-------+-----+--------+---------+-----------+
| StuID | Name | phone | Age | Gender | ClassID | TeacherID |
+-------+--------------+-------+-----+--------+---------+-----------+
| 3 | Xie Yanke | NULL | 53 | M | 2 | 16 |
| 4 | Ding Dian | NULL | 32 | M | 4 | 4 |
| 5 | Yu Yutong | NULL | 26 | M | 3 | 1 |
| 6 | Shi Qing | NULL | 46 | M | 5 | NULL |
| 13 | Tian Boguang | NULL | 33 | M | 2 | NULL |
| 24 | Xu Xian | NULL | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | NULL | 100 | M | NULL | NULL |
+-------+--------------+-------+-----+--------+---------+-----------+
7 rows in set (0.01 sec) - 查詢結果嵌入到另一個表裡,小數轉換整數會四舍五入
MariaDB [hellodb]> select avg(age) from s1 ; (檢視s1表平均年齡)
+----------+
| avg(age) |
+----------+
| 25.0857 |
+----------+
1 row in set (0.00 sec)
MariaDB [hellodb]> select * from teachers; （原來的表内容）
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 26 | F |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
MariaDB [hellodb]> update teachers set age=(select avg(age) from s1); （插入查詢結果的表内容，沒有指定字段會改掉所有）
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 25 | M |
| 2 | Zhang Sanfeng | 25 | M |
| 3 | Miejue Shitai | 25 | F |
| 4 | Lin Chaoying | 25 | F |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
MariaDB [hellodb]> update teachers set age=48 where tid=4; （把tid為4的age修改為48做下面實驗用）
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 25 | M |
| 2 | Zhang Sanfeng | 25 | M |
| 3 | Miejue Shitai | 25 | F |
| 4 | Lin Chaoying | 48 | F |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
MariaDB [hellodb]> update teachers set age=(select avg(age) from s1) where tid=4; （指定tid為4的age字段修改）
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 25 | M |
| 2 | Zhang Sanfeng | 25 | M |
| 3 | Miejue Shitai | 25 | F |
| 4 | Lin Chaoying | 25 | F |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec) -
多表查詢:
用子循環檢視s1表，顯示teachers表年齡大于s1表平均年齡的人的資訊。
MariaDB [hellodb]> update teachers set age=45 where tid=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [hellodb]> update teachers set age=94 where tid=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [hellodb]> update teachers set age=77 where tid=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 25 | F |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
（以上是把年齡修改回來做實驗）
MariaDB [hellodb]> select * from teachers where age > (select avg(age) from s1); （多表子循環查詢平均年齡大于25的人）
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
+-----+---------------+-----+--------+
3 rows in set (0.00 sec)
MariaDB [hellodb]> update teachers set age=26 where tid=4; （修改一下最後一條的年齡為26）
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [hellodb]> select * from teachers where age > (select avg(age) from s1); （最後一條也大于25就顯示出來了）
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 26 | F |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec) - union 縱向合并兩張表，表頭來自第一條查詢記錄.
MariaDB [hellodb]> select * from teachers
-> union
-> select stuid,name,age,gender from s1;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 26 | F |
| 1 | Shi Zhongyu | 22 | M |
| 2 | Shi Potian | 22 | M |
| 3 | Xie Yanke | 53 | M |
| 4 | Ding Dian | 32 | M |
| 5 | Yu Yutong | 26 | M |
| 6 | Shi Qing | 46 | M |
| 7 | Xi Ren | 19 | F |
| 8 | Lin Daiyu | 17 | F |
| 9 | Ren Yingying | 20 | F |
| 10 | Yue Lingshan | 19 | F |
| 11 | Yuan Chengzhi | 23 | M |
| 12 | Wen Qingqing | 19 | F |
| 13 | Tian Boguang | 33 | M |
| 14 | Lu Wushuang | 17 | F |
| 15 | Duan Yu | 19 | M |
| 16 | Xu Zhu | 21 | M |
| 17 | Lin Chong | 25 | M |
| 18 | Hua Rong | 23 | M |
| 19 | Xue Baochai | 18 | F |
| 20 | Diao Chan | 19 | F |
| 21 | Huang Yueying | 22 | F |
| 22 | Xiao Qiao | 20 | F |
| 23 | Ma Chao | 23 | M |
| 24 | Xu Xian | 27 | M |
| 25 | Sun Dasheng | 100 | M |
| 26 | xietingfeng | 23 | M |
| 27 | liudehua | 18 | F |
| 28 | mahuateng | 20 | M |
| 29 | wuyanzu | 19 | M |
| 30 | wuzetian | 21 | F |
| 31 | Song Jiang | 18 | M |
| 32 | Zhang Sanfeng | 18 | M |
| 33 | Miejue Shitai | 18 | F |
| 34 | Lin Chaoying | 18 | F |
| 38 | abc | 20 | M |
+-----+---------------+-----+--------+
39 rows in set (0.00 sec)
MariaDB [hellodb]> select tid as id ,name,age,gender from teachers union select stuid,name,age,gender from s1; (起個别名替換掉表頭的tid并縱向合并兩張表)
+----+---------------+-----+--------+
| id | name | age | gender |
+----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 26 | F |
| 1 | Shi Zhongyu | 22 | M |
| 2 | Shi Potian | 22 | M |
| 3 | Xie Yanke | 53 | M |
| 4 | Ding Dian | 32 | M |
| 5 | Yu Yutong | 26 | M |
| 6 | Shi Qing | 46 | M |
| 7 | Xi Ren | 19 | F |
| 8 | Lin Daiyu | 17 | F |
| 9 | Ren Yingying | 20 | F |
| 10 | Yue Lingshan | 19 | F |
| 11 | Yuan Chengzhi | 23 | M |
| 12 | Wen Qingqing | 19 | F |
| 13 | Tian Boguang | 33 | M |
| 14 | Lu Wushuang | 17 | F |
| 15 | Duan Yu | 19 | M |
| 16 | Xu Zhu | 21 | M |
| 17 | Lin Chong | 25 | M |
| 18 | Hua Rong | 23 | M |
| 19 | Xue Baochai | 18 | F |
| 20 | Diao Chan | 19 | F |
| 21 | Huang Yueying | 22 | F |
| 22 | Xiao Qiao | 20 | F |
| 23 | Ma Chao | 23 | M |
| 24 | Xu Xian | 27 | M |
| 25 | Sun Dasheng | 100 | M |
| 26 | xietingfeng | 23 | M |
| 27 | liudehua | 18 | F |
| 28 | mahuateng | 20 | M |
| 29 | wuyanzu | 19 | M |
| 30 | wuzetian | 21 | F |
| 31 | Song Jiang | 18 | M |
| 32 | Zhang Sanfeng | 18 | M |
| 33 | Miejue Shitai | 18 | F |
| 34 | Lin Chaoying | 18 | F |
| 38 | abc | 20 | M |
+----+---------------+-----+--------+
39 rows in set (0.00 sec) - union 自己和自己相連可以去重。
MariaDB [hellodb]> create table t2 select * from teachers; （先導一張表出來做實驗不能有主鍵，是以隻用了這種方法導了資料沒有把主鍵導過來）
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
MariaDB [hellodb]> select * from t2;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 26 | F |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
MariaDB [hellodb]> desc t2;
+--------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------------------+------+-----+---------+-------+
| TID | smallint(5) unsigned | NO | | 0 | |
| Name | varchar(100) | NO | | NULL | |
| Age | tinyint(3) unsigned | NO | | NULL | |
| Gender | enum('F','M') | YES | | NULL | |
+--------+----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
（添加重複的行做實驗）
MariaDB [hellodb]> insert into t2 set tid=4,name='linchaoying',age=26,gender='F'; （這條記錄添加的隻有name不一樣少了個空格）
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> insert into t2 set tid=4,name='lin chaoying',age=26,gender='F'; （完全一樣加了一行）
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> select * from t2;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 26 | F |
| 4 | linchaoying | 26 | F |
| 4 | lin chaoying | 26 | F |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)
MariaDB [hellodb]> select * from t2 union select * from t2; （用union過濾掉重複的行，少一個空格的那條記錄過濾不了）
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 26 | F |
| 4 | linchaoying | 26 | F |
+-----+---------------+-----+--------+
5 rows in set (0.00 sec) - 使用all 簡單連接配接兩張表不去重
MariaDB [hellodb]> select * from t2 union all select * from t2;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 26 | F |
| 4 | linchaoying | 26 | F |
| 4 | lin chaoying | 26 | F |
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 26 | F |
| 4 | linchaoying | 26 | F |
| 4 | lin chaoying | 26 | F |
+-----+---------------+-----+--------+
12 rows in set (0.00 sec) 兩張表橫向組合，類似于笛卡爾乘積。 （cross join） - 兩張表使用交叉連接配接就是這張表的每一行去和另一張表的所有行組合一遍，形成新的行。
MariaDB [hellodb]> select * from s1 cross join teachers;
+-------+---------------+-------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name | phone | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+---------------+-------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| 1 | Shi Zhongyu | NULL | 22 | M | 2 | 3 | 1 | Song Jiang | 25 | M |
| 1 | Shi Zhongyu | NULL | 22 | M | 2 | 3 | 2 | Zhang Sanfeng | 25 | M |
| 1 | Shi Zhongyu | NULL | 22 | M | 2 | 3 | 3 | Miejue Shitai | 25 | F |
| 1 | Shi Zhongyu | NULL | 22 | M | 2 | 3 | 4 | Lin Chaoying | 25 | F |
| 2 | Shi Potian | NULL | 22 | M | 1 | 7 | 1 | Song Jiang | 25 | M |
| 2 | Shi Potian | NULL | 22 | M | 1 | 7 | 2 | Zhang Sanfeng | 25 | M |
| 2 | Shi Potian | NULL | 22 | M | 1 | 7 | 3 | Miejue Shitai | 25 | F |
| 2 | Shi Potian | NULL | 22 | M | 1 | 7 | 4 | Lin Chaoying | 25 | F |
| 3 | Xie Yanke | NULL | 53 | M | 2 | 16 | 1 | Song Jiang | 25 | M |
| 3 | Xie Yanke | NULL | 53 | M | 2 | 16 | 2 | Zhang Sanfeng | 25 | M |
| 3 | Xie Yanke | NULL | 53 | M | 2 | 16 | 3 | Miejue Shitai | 25 | F |
| 3 | Xie Yanke | NULL | 53 | M | 2 | 16 | 4 | Lin Chaoying | 25 | F |
| 4 | Ding Dian | NULL | 32 | M | 4 | 4 | 1 | Song Jiang | 25 | M |
| 4 | Ding Dian | NULL | 32 | M | 4 | 4 | 2 | Zhang Sanfeng | 25 | M |
| 4 | Ding Dian | NULL | 32 | M | 4 | 4 | 3 | Miejue Shitai | 25 | F |
| 4 | Ding Dian | NULL | 32 | M | 4 | 4 | 4 | Lin Chaoying | 25 | F |
| 5 | Yu Yutong | NULL | 26 | M | 3 | 1 | 1 | Song Jiang | 25 | M |
| 5 | Yu Yutong | NULL | 26 | M | 3 | 1 | 2 | Zhang Sanfeng | 25 | M |
| 5 | Yu Yutong | NULL | 26 | M | 3 | 1 | 3 | Miejue Shitai | 25 | F |
| 5 | Yu Yutong | NULL | 26 | M | 3 | 1 | 4 | Lin Chaoying | 25 | F |
| 6 | Shi Qing | NULL | 46 | M | 5 | NULL | 1 | Song Jiang | 25 | M |
| 6 | Shi Qing | NULL | 46 | M | 5 | NULL | 2 | Zhang Sanfeng | 25 | M |
| 6 | Shi Qing | NULL | 46 | M | 5 | NULL | 3 | Miejue Shitai | 25 | F |
| 6 | Shi Qing | NULL | 46 | M | 5 | NULL | 4 | Lin Chaoying | 25 | F |
| 7 | Xi Ren | NULL | 19 | F | 3 | NULL | 1 | Song Jiang | 25 | M |
| 7 | Xi Ren | NULL | 19 | F | 3 | NULL | 2 | Zhang Sanfeng | 25 | M |
| 7 | Xi Ren | NULL | 19 | F | 3 | NULL | 3 | Miejue Shitai | 25 | F |
| 7 | Xi Ren | NULL | 19 | F | 3 | NULL | 4 | Lin Chaoying | 25 | F |
| 8 | Lin Daiyu | NULL | 17 | F | 7 | NULL | 1 | Song Jiang | 25 | M |
| 8 | Lin Daiyu | NULL | 17 | F | 7 | NULL | 2 | Zhang Sanfeng | 25 | M |
| 8 | Lin Daiyu | NULL | 17 | F | 7 | NULL | 3 | Miejue Shitai | 25 | F |
| 8 | Lin Daiyu | NULL | 17 | F | 7 | NULL | 4 | Lin Chaoying | 25 | F |
| 9 | Ren Yingying | NULL | 20 | F | 6 | NULL | 1 | Song Jiang | 25 | M |
| 9 | Ren Yingying | NULL | 20 | F | 6 | NULL | 2 | Zhang Sanfeng | 25 | M |
| 9 | Ren Yingying | NULL | 20 | F | 6 | NULL | 3 | Miejue Shitai | 25 | F |
| 9 | Ren Yingying | NULL | 20 | F | 6 | NULL | 4 | Lin Chaoying | 25 | F |
| 10 | Yue Lingshan | NULL | 19 | F | 3 | NULL | 1 | Song Jiang | 25 | M |
| 10 | Yue Lingshan | NULL | 19 | F | 3 | NULL | 2 | Zhang Sanfeng | 25 | M |
| 10 | Yue Lingshan | NULL | 19 | F | 3 | NULL | 3 | Miejue Shitai | 25 | F |
| 10 | Yue Lingshan | NULL | 19 | F | 3 | NULL | 4 | Lin Chaoying | 25 | F |
| 11 | Yuan Chengzhi | NULL | 23 | M | 6 | NULL | 1 | Song Jiang | 25 | M |
| 11 | Yuan Chengzhi | NULL | 23 | M | 6 | NULL | 2 | Zhang Sanfeng | 25 | M |
| 11 | Yuan Chengzhi | NULL | 23 | M | 6 | NULL | 3 | Miejue Shitai | 25 | F |
| 11 | Yuan Chengzhi | NULL | 23 | M | 6 | NULL | 4 | Lin Chaoying | 25 | F |
| 12 | Wen Qingqing | NULL | 19 | F | 1 | NULL | 1 | Song Jiang | 25 | M |
| 12 | Wen Qingqing | NULL | 19 | F | 1 | NULL | 2 | Zhang Sanfeng | 25 | M |
| 12 | Wen Qingqing | NULL | 19 | F | 1 | NULL | 3 | Miejue Shitai | 25 | F |
| 12 | Wen Qingqing | NULL | 19 | F | 1 | NULL | 4 | Lin Chaoying | 25 | F |
| 13 | Tian Boguang | NULL | 33 | M | 2 | NULL | 1 | Song Jiang | 25 | M |
| 13 | Tian Boguang | NULL | 33 | M | 2 | NULL | 2 | Zhang Sanfeng | 25 | M |
| 13 | Tian Boguang | NULL | 33 | M | 2 | NULL | 3 | Miejue Shitai | 25 | F |
| 13 | Tian Boguang | NULL | 33 | M | 2 | NULL | 4 | Lin Chaoying | 25 | F |
| 14 | Lu Wushuang | NULL | 17 | F | 3 | NULL | 1 | Song Jiang | 25 | M |
| 14 | Lu Wushuang | NULL | 17 | F | 3 | NULL | 2 | Zhang Sanfeng | 25 | M |
| 14 | Lu Wushuang | NULL | 17 | F | 3 | NULL | 3 | Miejue Shitai | 25 | F |
| 14 | Lu Wushuang | NULL | 17 | F | 3 | NULL | 4 | Lin Chaoying | 25 | F |
| 15 | Duan Yu | NULL | 19 | M | 4 | NULL | 1 | Song Jiang | 25 | M |
| 15 | Duan Yu | NULL | 19 | M | 4 | NULL | 2 | Zhang Sanfeng | 25 | M |
| 15 | Duan Yu | NULL | 19 | M | 4 | NULL | 3 | Miejue Shitai | 25 | F |
| 15 | Duan Yu | NULL | 19 | M | 4 | NULL | 4 | Lin Chaoying | 25 | F |
| 16 | Xu Zhu | NULL | 21 | M | 1 | NULL | 1 | Song Jiang | 25 | M |
| 16 | Xu Zhu | NULL | 21 | M | 1 | NULL | 2 | Zhang Sanfeng | 25 | M |
| 16 | Xu Zhu | NULL | 21 | M | 1 | NULL | 3 | Miejue Shitai | 25 | F |
| 16 | Xu Zhu | NULL | 21 | M | 1 | NULL | 4 | Lin Chaoying | 25 | F |
| 17 | Lin Chong | NULL | 25 | M | 4 | NULL | 1 | Song Jiang | 25 | M |
| 17 | Lin Chong | NULL | 25 | M | 4 | NULL | 2 | Zhang Sanfeng | 25 | M |
| 17 | Lin Chong | NULL | 25 | M | 4 | NULL | 3 | Miejue Shitai | 25 | F |
| 17 | Lin Chong | NULL | 25 | M | 4 | NULL | 4 | Lin Chaoying | 25 | F |
| 18 | Hua Rong | NULL | 23 | M | 7 | NULL | 1 | Song Jiang | 25 | M |
| 18 | Hua Rong | NULL | 23 | M | 7 | NULL | 2 | Zhang Sanfeng | 25 | M |
| 18 | Hua Rong | NULL | 23 | M | 7 | NULL | 3 | Miejue Shitai | 25 | F |
| 18 | Hua Rong | NULL | 23 | M | 7 | NULL | 4 | Lin Chaoying | 25 | F |
| 19 | Xue Baochai | NULL | 18 | F | 6 | NULL | 1 | Song Jiang | 25 | M |
| 19 | Xue Baochai | NULL | 18 | F | 6 | NULL | 2 | Zhang Sanfeng | 25 | M |
| 19 | Xue Baochai | NULL | 18 | F | 6 | NULL | 3 | Miejue Shitai | 25 | F |
| 19 | Xue Baochai | NULL | 18 | F | 6 | NULL | 4 | Lin Chaoying | 25 | F |
| 20 | Diao Chan | NULL | 19 | F | 7 | NULL | 1 | Song Jiang | 25 | M |
| 20 | Diao Chan | NULL | 19 | F | 7 | NULL | 2 | Zhang Sanfeng | 25 | M |
| 20 | Diao Chan | NULL | 19 | F | 7 | NULL | 3 | Miejue Shitai | 25 | F |
| 20 | Diao Chan | NULL | 19 | F | 7 | NULL | 4 | Lin Chaoying | 25 | F |
| 21 | Huang Yueying | NULL | 22 | F | 6 | NULL | 1 | Song Jiang | 25 | M |
| 21 | Huang Yueying | NULL | 22 | F | 6 | NULL | 2 | Zhang Sanfeng | 25 | M |
| 21 | Huang Yueying | NULL | 22 | F | 6 | NULL | 3 | Miejue Shitai | 25 | F |
| 21 | Huang Yueying | NULL | 22 | F | 6 | NULL | 4 | Lin Chaoying | 25 | F |
| 22 | Xiao Qiao | NULL | 20 | F | 1 | NULL | 1 | Song Jiang | 25 | M |
| 22 | Xiao Qiao | NULL | 20 | F | 1 | NULL | 2 | Zhang Sanfeng | 25 | M |
| 22 | Xiao Qiao | NULL | 20 | F | 1 | NULL | 3 | Miejue Shitai | 25 | F |
| 22 | Xiao Qiao | NULL | 20 | F | 1 | NULL | 4 | Lin Chaoying | 25 | F |
| 23 | Ma Chao | NULL | 23 | M | 4 | NULL | 1 | Song Jiang | 25 | M |
| 23 | Ma Chao | NULL | 23 | M | 4 | NULL | 2 | Zhang Sanfeng | 25 | M |
| 23 | Ma Chao | NULL | 23 | M | 4 | NULL | 3 | Miejue Shitai | 25 | F |
| 23 | Ma Chao | NULL | 23 | M | 4 | NULL | 4 | Lin Chaoying | 25 | F |
| 24 | Xu Xian | NULL | 27 | M | NULL | NULL | 1 | Song Jiang | 25 | M |
| 24 | Xu Xian | NULL | 27 | M | NULL | NULL | 2 | Zhang Sanfeng | 25 | M |
| 24 | Xu Xian | NULL | 27 | M | NULL | NULL | 3 | Miejue Shitai | 25 | F |
| 24 | Xu Xian | NULL | 27 | M | NULL | NULL | 4 | Lin Chaoying | 25 | F |
| 25 | Sun Dasheng | NULL | 100 | M | NULL | NULL | 1 | Song Jiang | 25 | M |
| 25 | Sun Dasheng | NULL | 100 | M | NULL | NULL | 2 | Zhang Sanfeng | 25 | M |
| 25 | Sun Dasheng | NULL | 100 | M | NULL | NULL | 3 | Miejue Shitai | 25 | F |
| 25 | Sun Dasheng | NULL | 100 | M | NULL | NULL | 4 | Lin Chaoying | 25 | F |
| 26 | xietingfeng | NULL | 23 | M | 2 | 1 | 1 | Song Jiang | 25 | M |
| 26 | xietingfeng | NULL | 23 | M | 2 | 1 | 2 | Zhang Sanfeng | 25 | M |
| 26 | xietingfeng | NULL | 23 | M | 2 | 1 | 3 | Miejue Shitai | 25 | F |
| 26 | xietingfeng | NULL | 23 | M | 2 | 1 | 4 | Lin Chaoying | 25 | F |
| 27 | liudehua | NULL | 18 | F | 1 | NULL | 1 | Song Jiang | 25 | M |
| 27 | liudehua | NULL | 18 | F | 1 | NULL | 2 | Zhang Sanfeng | 25 | M |
| 27 | liudehua | NULL | 18 | F | 1 | NULL | 3 | Miejue Shitai | 25 | F |
| 27 | liudehua | NULL | 18 | F | 1 | NULL | 4 | Lin Chaoying | 25 | F |
| 28 | mahuateng | NULL | 20 | M | 3 | NULL | 1 | Song Jiang | 25 | M |
| 28 | mahuateng | NULL | 20 | M | 3 | NULL | 2 | Zhang Sanfeng | 25 | M |
| 28 | mahuateng | NULL | 20 | M | 3 | NULL | 3 | Miejue Shitai | 25 | F |
| 28 | mahuateng | NULL | 20 | M | 3 | NULL | 4 | Lin Chaoying | 25 | F |
| 29 | wuyanzu | NULL | 19 | M | 4 | NULL | 1 | Song Jiang | 25 | M |
| 29 | wuyanzu | NULL | 19 | M | 4 | NULL | 2 | Zhang Sanfeng | 25 | M |
| 29 | wuyanzu | NULL | 19 | M | 4 | NULL | 3 | Miejue Shitai | 25 | F |
| 29 | wuyanzu | NULL | 19 | M | 4 | NULL | 4 | Lin Chaoying | 25 | F |
| 30 | wuzetian | NULL | 21 | F | NULL | NULL | 1 | Song Jiang | 25 | M |
| 30 | wuzetian | NULL | 21 | F | NULL | NULL | 2 | Zhang Sanfeng | 25 | M |
| 30 | wuzetian | NULL | 21 | F | NULL | NULL | 3 | Miejue Shitai | 25 | F |
| 30 | wuzetian | NULL | 21 | F | NULL | NULL | 4 | Lin Chaoying | 25 | F |
| 31 | Song Jiang | NULL | 18 | M | 45 | NULL | 1 | Song Jiang | 25 | M |
| 31 | Song Jiang | NULL | 18 | M | 45 | NULL | 2 | Zhang Sanfeng | 25 | M |
| 31 | Song Jiang | NULL | 18 | M | 45 | NULL | 3 | Miejue Shitai | 25 | F |
| 31 | Song Jiang | NULL | 18 | M | 45 | NULL | 4 | Lin Chaoying | 25 | F |
| 32 | Zhang Sanfeng | NULL | 18 | M | 94 | NULL | 1 | Song Jiang | 25 | M |
| 32 | Zhang Sanfeng | NULL | 18 | M | 94 | NULL | 2 | Zhang Sanfeng | 25 | M |
| 32 | Zhang Sanfeng | NULL | 18 | M | 94 | NULL | 3 | Miejue Shitai | 25 | F |
| 32 | Zhang Sanfeng | NULL | 18 | M | 94 | NULL | 4 | Lin Chaoying | 25 | F |
| 33 | Miejue Shitai | NULL | 18 | F | 77 | NULL | 1 | Song Jiang | 25 | M |
| 33 | Miejue Shitai | NULL | 18 | F | 77 | NULL | 2 | Zhang Sanfeng | 25 | M |
| 33 | Miejue Shitai | NULL | 18 | F | 77 | NULL | 3 | Miejue Shitai | 25 | F |
| 33 | Miejue Shitai | NULL | 18 | F | 77 | NULL | 4 | Lin Chaoying | 25 | F |
| 34 | Lin Chaoying | NULL | 18 | F | 93 | NULL | 1 | Song Jiang | 25 | M |
| 34 | Lin Chaoying | NULL | 18 | F | 93 | NULL | 2 | Zhang Sanfeng | 25 | M |
| 34 | Lin Chaoying | NULL | 18 | F | 93 | NULL | 3 | Miejue Shitai | 25 | F |
| 34 | Lin Chaoying | NULL | 18 | F | 93 | NULL | 4 | Lin Chaoying | 25 | F |
| 38 | abc | NULL | 20 | M | NULL | NULL | 1 | Song Jiang | 25 | M |
| 38 | abc | NULL | 20 | M | NULL | NULL | 2 | Zhang Sanfeng | 25 | M |
| 38 | abc | NULL | 20 | M | NULL | NULL | 3 | Miejue Shitai | 25 | F |
| 38 | abc | NULL | 20 | M | NULL | NULL | 4 | Lin Chaoying | 25 | F |
+-------+---------------+-------+-----+--------+---------+-----------+-----+---------------+-----+--------+
140 rows in set (0.00 sec)
第一張表
0 1 2 3
1 2 3 4
第二張表
1 3 4 5
2 3 4 5
交叉連接配接後結果：
0 1 2 3 1 3 4 5
0 1 2 3 2 3 4 5
1 2 3 4 1 3 4 5
1 2 3 4 2 3 4 5
兩張表換下位置不影響資料隻是顯示效果變了而已:
1 3 4 5 0 1 2 3
1 3 4 5 1 2 3 4
2 3 4 5 0 1 2 3
2 3 4 5 1 2 3 4
MariaDB [hellodb]> select * from teachers , s1; （這個指令也可以交叉連接配接但是比較老了推薦使用第一種）
+-----+---------------+-----+--------+-------+---------------+-------+-----+--------+---------+-----------+
| TID | Name | Age | Gender | StuID | Name | phone | Age | Gender | ClassID | TeacherID |
+-----+---------------+-----+--------+-------+---------------+-------+-----+--------+---------+-----------+
| 1 | Song Jiang | 25 | M | 1 | Shi Zhongyu | NULL | 22 | M | 2 | 3 |
| 2 | Zhang Sanfeng | 25 | M | 1 | Shi Zhongyu | NULL | 22 | M | 2 | 3 |
| 3 | Miejue Shitai | 25 | F | 1 | Shi Zhongyu | NULL | 22 | M | 2 | 3 |
| 4 | Lin Chaoying | 25 | F | 1 | Shi Zhongyu | NULL | 22 | M | 2 | 3 |
| 1 | Song Jiang | 25 | M | 2 | Shi Potian | NULL | 22 | M | 1 | 7 |
| 2 | Zhang Sanfeng | 25 | M | 2 | Shi Potian | NULL | 22 | M | 1 | 7 |
| 3 | Miejue Shitai | 25 | F | 2 | Shi Potian | NULL | 22 | M | 1 | 7 |
| 4 | Lin Chaoying | 25 | F | 2 | Shi Potian | NULL | 22 | M | 1 | 7 |
| 1 | Song Jiang | 25 | M | 3 | Xie Yanke | NULL | 53 | M | 2 | 16 |
| 2 | Zhang Sanfeng | 25 | M | 3 | Xie Yanke | NULL | 53 | M | 2 | 16 |
| 3 | Miejue Shitai | 25 | F | 3 | Xie Yanke | NULL | 53 | M | 2 | 16 |
| 4 | Lin Chaoying | 25 | F | 3 | Xie Yanke | NULL | 53 | M | 2 | 16 | - 挑出兩張表的個别字段。
MariaDB [hellodb]> select name,age,gender from teachers cross join s1; (這裡有兩個字段是重複的name，age兩個表都有)
ERROR 1052 (23000): Column 'name' in field list is ambiguous
MariaDB [hellodb]> select stuid,s1.name,tid,teachers.name from teachers cross join s1; （分别指定是哪個表的name）
+-------+---------------+-----+---------------+
| stuid | name | tid | name |
+-------+---------------+-----+---------------+
| 1 | Shi Zhongyu | 1 | Song Jiang |
| 1 | Shi Zhongyu | 2 | Zhang Sanfeng |
| 1 | Shi Zhongyu | 3 | Miejue Shitai |
| 1 | Shi Zhongyu | 4 | Lin Chaoying |
| 2 | Shi Potian | 1 | Song Jiang |
| 2 | Shi Potian | 2 | Zhang Sanfeng |
| 2 | Shi Potian | 3 | Miejue Shitai |
| 2 | Shi Potian | 4 | Lin Chaoying |
| 3 | Xie Yanke | 1 | Song Jiang |
| 3 | Xie Yanke | 2 | Zhang Sanfeng |
| 3 | Xie Yanke | 3 | Miejue Shitai |
| 3 | Xie Yanke | 4 | Lin Chaoying |
| 4 | Ding Dian | 1 | Song Jiang |
| 4 | Ding Dian | 2 | Zhang Sanfeng |
| 4 | Ding Dian | 3 | Miejue Shitai |
| 4 | Ding Dian | 4 | Lin Chaoying |
| 5 | Yu Yutong | 1 | Song Jiang |
| 5 | Yu Yutong | 2 | Zhang Sanfeng |
| 5 | Yu Yutong | 3 | Miejue Shitai |
| 5 | Yu Yutong | 4 | Lin Chaoying |
| 6 | Shi Qing | 1 | Song Jiang |
| 6 | Shi Qing | 2 | Zhang Sanfeng |
| 6 | Shi Qing | 3 | Miejue Shitai |
| 6 | Shi Qing | 4 | Lin Chaoying |
| 7 | Xi Ren | 1 | Song Jiang |
| 7 | Xi Ren | 2 | Zhang Sanfeng |
| 7 | Xi Ren | 3 | Miejue Shitai |
| 7 | Xi Ren | 4 | Lin Chaoying |
| 8 | Lin Daiyu | 1 | Song Jiang |
| 8 | Lin Daiyu | 2 | Zhang Sanfeng |
| 8 | Lin Daiyu | 3 | Miejue Shitai |
| 8 | Lin Daiyu | 4 | Lin Chaoying |
| 9 | Ren Yingying | 1 | Song Jiang |
| 9 | Ren Yingying | 2 | Zhang Sanfeng |
| 9 | Ren Yingying | 3 | Miejue Shitai |
| 9 | Ren Yingying | 4 | Lin Chaoying |
| 10 | Yue Lingshan | 1 | Song Jiang |
| 10 | Yue Lingshan | 2 | Zhang Sanfeng |
| 10 | Yue Lingshan | 3 | Miejue Shitai |
| 10 | Yue Lingshan | 4 | Lin Chaoying |
| 11 | Yuan Chengzhi | 1 | Song Jiang |
| 11 | Yuan Chengzhi | 2 | Zhang Sanfeng |
| 11 | Yuan Chengzhi | 3 | Miejue Shitai |
| 11 | Yuan Chengzhi | 4 | Lin Chaoying |
（省略了太長）
MariaDB [hellodb]> select stuid,s1.name,s1.age,tid,teachers.name,teachers.age from teachers cross join s1; （name和age都可以加在裡面指定）
+-------+---------------+-----+-----+---------------+-----+
| stuid | name | age | tid | name | age |
+-------+---------------+-----+-----+---------------+-----+
| 1 | Shi Zhongyu | 22 | 1 | Song Jiang | 25 |
| 1 | Shi Zhongyu | 22 | 2 | Zhang Sanfeng | 25 |
| 1 | Shi Zhongyu | 22 | 3 | Miejue Shitai | 25 |
| 1 | Shi Zhongyu | 22 | 4 | Lin Chaoying | 25 |
| 2 | Shi Potian | 22 | 1 | Song Jiang | 25 |
| 2 | Shi Potian | 22 | 2 | Zhang Sanfeng | 25 |
| 2 | Shi Potian | 22 | 3 | Miejue Shitai | 25 |
| 2 | Shi Potian | 22 | 4 | Lin Chaoying | 25 |
| 3 | Xie Yanke | 53 | 1 | Song Jiang | 25 |
| 3 | Xie Yanke | 53 | 2 | Zhang Sanfeng | 25 |
| 3 | Xie Yanke | 53 | 3 | Miejue Shitai | 25 |
| 3 | Xie Yanke | 53 | 4 | Lin Chaoying | 25 |
| 4 | Ding Dian | 32 | 1 | Song Jiang | 25 |
| 4 | Ding Dian | 32 | 2 | Zhang Sanfeng | 25 |
| 4 | Ding Dian | 32 | 3 | Miejue Shitai | 25 |
| 4 | Ding Dian | 32 | 4 | Lin Chaoying | 25 |
| 5 | Yu Yutong | 26 | 1 | Song Jiang | 25 |
| 5 | Yu Yutong | 26 | 2 | Zhang Sanfeng | 25 |
| 5 | Yu Yutong | 26 | 3 | Miejue Shitai | 25 |
| 5 | Yu Yutong | 26 | 4 | Lin Chaoying | 25 |
（省略了太長）
MariaDB [hellodb]> select stuid,s1.name as s1_name,tid,teachers.name as teachers_name from teachers cross join s1; (也可以加上别名來區分比較清晰)
+-------+---------------+-----+---------------+
| stuid | s1_name | tid | teachers_name |
+-------+---------------+-----+---------------+
| 1 | Shi Zhongyu | 1 | Song Jiang |
| 1 | Shi Zhongyu | 2 | Zhang Sanfeng |
| 1 | Shi Zhongyu | 3 | Miejue Shitai |
| 1 | Shi Zhongyu | 4 | Lin Chaoying |
| 2 | Shi Potian | 1 | Song Jiang |
| 2 | Shi Potian | 2 | Zhang Sanfeng |
| 2 | Shi Potian | 3 | Miejue Shitai |
| 2 | Shi Potian | 4 | Lin Chaoying |
| 3 | Xie Yanke | 1 | Song Jiang |
| 3 | Xie Yanke | 2 | Zhang Sanfeng |
| 3 | Xie Yanke | 3 | Miejue Shitai |
| 3 | Xie Yanke | 4 | Lin Chaoying |
（省略） - 對表起别名利用表的别名來查詢資料。（定義别名是在字段的最後用在前面用，隻在目前字段生效）
MariaDB [hellodb]> select stuid,s.name as s1_name,s.age,t.name as teachers_name,t.age from teachers t cross join s1 s;
+-------+---------------+-----+---------------+-----+
| stuid | s1_name | age | teachers_name | age |
+-------+---------------+-----+---------------+-----+
| 1 | Shi Zhongyu | 22 | Song Jiang | 25 |
| 1 | Shi Zhongyu | 22 | Zhang Sanfeng | 25 |
| 1 | Shi Zhongyu | 22 | Miejue Shitai | 25 |
| 1 | Shi Zhongyu | 22 | Lin Chaoying | 25 |
| 2 | Shi Potian | 22 | Song Jiang | 25 |
| 2 | Shi Potian | 22 | Zhang Sanfeng | 25 |
| 2 | Shi Potian | 22 | Miejue Shitai | 25 |
| 2 | Shi Potian | 22 | Lin Chaoying | 25 |
| 3 | Xie Yanke | 53 | Song Jiang | 25 |
| 3 | Xie Yanke | 53 | Zhang Sanfeng | 25 |
| 3 | Xie Yanke | 53 | Miejue Shitai | 25 |
| 3 | Xie Yanke | 53 | Lin Chaoying | 25 |
| 4 | Ding Dian | 32 | Song Jiang | 25 |
| 4 | Ding Dian | 32 | Zhang Sanfeng | 25 |
| 4 | Ding Dian | 32 | Miejue Shitai | 25 |
| 4 | Ding Dian | 32 | Lin Chaoying | 25 |
MariaDB [hellodb]> select stuid,s1.name s1_name,s1.age,tid,t.name teacher_name,teachers.age from teachers t cross join s1; （别名定義之後不能使用原始名字）
ERROR 1054 (42S22): Unknown column 'teachers.age' in 'field list' 等值連接配接：讓表之間的字段以“等值”建立連接配接關系 - 把兩個表有交集的地方連接配接起來
MariaDB [hellodb]> select * from s1 inner join t1 on s1.teacherid=t1.tid; （三個個老師各教一個學生）
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
3 rows in set (0.00 sec)
MariaDB [hellodb]> update s1 set teacherid=1 where stuid=25; （修改一下s1表的teacherid的值為1）
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [hellodb]> select * from s1 inner join t1 on s1.teacherid=t1.tid; （再次使用這條指令，檢視就是songjiang教兩個學生）
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M |
| 25 | Sun Dasheng | 100 | M | NULL | 1 | 1 | Song Jiang | 45 | M |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
MariaDB [hellodb]> select * from s1 , t1 where s1.teacherid=t1.tid; (不加 inner join 的老寫法)
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M |
| 25 | Sun Dasheng | 100 | M | NULL | 1 | 1 | Song Jiang | 45 | M |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
4 rows in set (0.01 sec) - 内連接配接之後過濾：先連接配接再過濾，顯示s1表大于30的人。
MariaDB [hellodb]> select * from s1 inner join t1 on s1.teacherid=t1.tid and s1.age >30;
+-------+-------------+-----+--------+---------+-----------+-----+--------------+-----+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+-------------+-----+--------+---------+-----------+-----+--------------+-----+--------+
| 25 | Sun Dasheng | 100 | M | NULL | 1 | 1 | Song Jiang | 45 | M |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F |
+-------+-------------+-----+--------+---------+-----------+-----+--------------+-----+--------+
2 rows in set (0.00 sec) - 查詢完之後過濾
MariaDB [hellodb]> select * from s1 inner join t1 on s1.teacherid=t1.tid where s1.a
+-------+-------------+-----+--------+---------+-----------+-----+--------------+--
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | A
+-------+-------------+-----+--------+---------+-----------+-----+--------------+--
| 25 | Sun Dasheng | 100 | M | NULL | 1 | 1 | Song Jiang |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying |
+-------+-------------+-----+--------+---------+-----------+-----+--------------+--
2 rows in set (0.00 sec) 外連接配接：
左外連接配接：FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col （排在前面的）
右外連接配接：FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col （排在後面的） 
- 學生表全留下來老師的隻留下來有交集的地方。 (沒有交集的地方空值代替)
MariaDB [hellodb]> select * from s1 left outer join t1 on s1.teacherid=t1.tid;
+-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |
| 2 | Shi Potian | 22 | M | 1 | 7 | NULL | NULL | NULL | NULL |
| 3 | Xie Yanke | 53 | M | 2 | 16 | NULL | NULL | NULL | NULL |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F |
| 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M |
| 6 | Shi Qing | 46 | M | 5 | NULL | NULL | NULL | NULL | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL | NULL | NULL | NULL | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL | NULL | NULL | NULL | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL | NULL | NULL | NULL | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL | NULL | NULL | NULL | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL | NULL | NULL | NULL | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL | NULL | NULL | NULL | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL | NULL | NULL | NULL | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL | NULL | NULL | NULL | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL | NULL | NULL | NULL | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL | NULL | NULL | NULL | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL | NULL | NULL | NULL | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL | NULL | NULL | NULL | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL | NULL | NULL | NULL | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL | NULL | NULL | NULL | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL | NULL | NULL | NULL | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL | NULL | NULL | NULL | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL | NULL | NULL | NULL | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL | NULL | NULL | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | 1 | 1 | Song Jiang | 45 | M |
+-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+
25 rows in set (0.00 sec) - 左外連接配接擴充用法
MySQL資料庫多表查詢
- 取出沒有老師教的學生 (用where指定)
MariaDB [hellodb]> select * from t1; （教師表）
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
MariaDB [hellodb]> select * from s1 left outer join t1 on s1.teacherid=t1.tid where tid is null; （前面兩個，老師表裡沒有這兩個老師的編号）
+-------+---------------+-----+--------+---------+-----------+------+------+------+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+---------------+-----+--------+---------+-----------+------+------+------+--------+
| 2 | Shi Potian | 22 | M | 1 | 7 | NULL | NULL | NULL | NULL |
| 3 | Xie Yanke | 53 | M | 2 | 16 | NULL | NULL | NULL | NULL |
| 6 | Shi Qing | 46 | M | 5 | NULL | NULL | NULL | NULL | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL | NULL | NULL | NULL | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL | NULL | NULL | NULL | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL | NULL | NULL | NULL | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL | NULL | NULL | NULL | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL | NULL | NULL | NULL | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL | NULL | NULL | NULL | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL | NULL | NULL | NULL | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL | NULL | NULL | NULL | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL | NULL | NULL | NULL | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL | NULL | NULL | NULL | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL | NULL | NULL | NULL | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL | NULL | NULL | NULL | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL | NULL | NULL | NULL | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL | NULL | NULL | NULL | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL | NULL | NULL | NULL | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL | NULL | NULL | NULL | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL | NULL | NULL | NULL | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL | NULL | NULL | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+------+------+------+--------+
21 rows in set (0.00 sec) - 老師表全留下來，學生表有交集的地方留下來。
MariaDB [hellodb]> select * from s1 right outer join t1 on s1.teacherid=t1.tid;
+-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F |
| 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M |
| 25 | Sun Dasheng | 100 | M | NULL | 1 | 1 | Song Jiang | 45 | M |
| NULL | NULL | NULL | NULL | NULL | NULL | 2 | Zhang Sanfeng | 94 | M |
+-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
5 rows in set (0.00 sec) - 右外連接配接擴充用法
MySQL資料庫多表查詢
- 沒有教學生的老師留下來。 (和左外連接配接的邏輯是一樣的)
MariaDB [hellodb]> select * from s1 right outer join t1 on s1.teacherid=t1.tid where s1.teacherid is null;
+-------+------+------+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+------+------+--------+---------+-----------+-----+---------------+-----+--------+
| NULL | NULL | NULL | NULL | NULL | NULL | 2 | Zhang Sanfeng | 94 | M |
+-------+------+------+--------+---------+-----------+-----+---------------+-----+--------+
1 row in set (0.00 sec) 和交叉連接配接不一樣 mysql不支援full outer join - 把左外連接配接和右外連接配接用union聯合起來，有交集的地方對應，沒有交集的也地方輸出出來。
MariaDB [hellodb]> select * from s1 left outer join t1 on s1.teacherid=t1.tid
-> union
-> select * from s1 right outer join t1 on s1.teacherid=t1.tid;
+-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |
| 2 | Shi Potian | 22 | M | 1 | 7 | NULL | NULL | NULL | NULL |
| 3 | Xie Yanke | 53 | M | 2 | 16 | NULL | NULL | NULL | NULL |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F |
| 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M |
| 6 | Shi Qing | 46 | M | 5 | NULL | NULL | NULL | NULL | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL | NULL | NULL | NULL | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL | NULL | NULL | NULL | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL | NULL | NULL | NULL | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL | NULL | NULL | NULL | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL | NULL | NULL | NULL | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL | NULL | NULL | NULL | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL | NULL | NULL | NULL | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL | NULL | NULL | NULL | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL | NULL | NULL | NULL | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL | NULL | NULL | NULL | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL | NULL | NULL | NULL | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL | NULL | NULL | NULL | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL | NULL | NULL | NULL | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL | NULL | NULL | NULL | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL | NULL | NULL | NULL | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL | NULL | NULL | NULL | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL | NULL | NULL | NULL | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL | NULL | NULL | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | 1 | 1 | Song Jiang | 45 | M |
| NULL | NULL | NULL | NULL | NULL | NULL | 2 | Zhang Sanfeng | 94 | M |
+-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+
26 rows in set (0.00 sec) - 完全外連接配接擴充用法：有交集的地方去除掉，隻留外連接配接。
MySQL資料庫多表查詢
- 把沒有老師的學生，和沒有學生的老師取出來。
MariaDB [hellodb]> select * from (select s.stuid,s.name s_name,s.teacherid,t.tid,t.name t_name from s1 s left outer join t1 t on s.teacherid=t.tid union select s.stuid,s.name,s.teacherid,t.tid,t.name from s1 s right outer join t1 t on s.teacherid=t.tid) as a where a.teacherid is null or a.tid is null;
+-------+---------------+-----------+------+---------------+
| stuid | s_name | teacherid | tid | t_name |
+-------+---------------+-----------+------+---------------+
| 2 | Shi Potian | 7 | NULL | NULL |
| 3 | Xie Yanke | 16 | NULL | NULL |
| 6 | Shi Qing | NULL | NULL | NULL |
| 7 | Xi Ren | NULL | NULL | NULL |
| 8 | Lin Daiyu | NULL | NULL | NULL |
| 9 | Ren Yingying | NULL | NULL | NULL |
| 10 | Yue Lingshan | NULL | NULL | NULL |
| 11 | Yuan Chengzhi | NULL | NULL | NULL |
| 12 | Wen Qingqing | NULL | NULL | NULL |
| 13 | Tian Boguang | NULL | NULL | NULL |
| 14 | Lu Wushuang | NULL | NULL | NULL |
| 15 | Duan Yu | NULL | NULL | NULL |
| 16 | Xu Zhu | NULL | NULL | NULL |
| 17 | Lin Chong | NULL | NULL | NULL |
| 18 | Hua Rong | NULL | NULL | NULL |
| 19 | Xue Baochai | NULL | NULL | NULL |
| 20 | Diao Chan | NULL | NULL | NULL |
| 21 | Huang Yueying | NULL | NULL | NULL |
| 22 | Xiao Qiao | NULL | NULL | NULL |
| 23 | Ma Chao | NULL | NULL | NULL |
| 24 | Xu Xian | NULL | NULL | NULL |
| NULL | NULL | NULL | 2 | Zhang Sanfeng |
+-------+---------------+-----------+------+---------------+
22 rows in set (0.00 sec) 取學生姓名，成績，科目。 - 先取出學生的姓名和成績 （分兩步做思路清晰一點）
MariaDB [hellodb]> select st.name,sc.courseid,sc.score from s1 st inner join scores sc on st.stuid=sc.stuid;
+-------------+----------+-------+
| name | courseid | score |
+-------------+----------+-------+
| Shi Zhongyu | 2 | 77 |
| Shi Zhongyu | 6 | 93 |
| Shi Potian | 2 | 47 |
| Shi Potian | 5 | 97 |
| Xie Yanke | 2 | 88 |
| Xie Yanke | 6 | 75 |
| Ding Dian | 5 | 71 |
| Ding Dian | 2 | 89 |
| Yu Yutong | 1 | 39 |
| Yu Yutong | 7 | 63 |
| Shi Qing | 1 | 96 |
| Xi Ren | 1 | 86 |
| Xi Ren | 7 | 83 |
| Lin Daiyu | 4 | 57 |
| Lin Daiyu | 3 | 93 |
+-------------+----------+-------+
15 rows in set (0.00 sec) - 在連接配接一次取出學生姓名，成績，科目。
MariaDB [hellodb]> select st.name,co.course,sc.score from s1 st inner join scores sc on st.stuid=sc.stuid inner join courses co on sc.courseid=co.courseid;
+-------------+----------------+-------+
| name | course | score |
+-------------+----------------+-------+
| Shi Zhongyu | Kuihua Baodian | 77 |
| Shi Zhongyu | Weituo Zhang | 93 |
| Shi Potian | Kuihua Baodian | 47 |
| Shi Potian | Daiyu Zanghua | 97 |
| Xie Yanke | Kuihua Baodian | 88 |
| Xie Yanke | Weituo Zhang | 75 |
| Ding Dian | Daiyu Zanghua | 71 |
| Ding Dian | Kuihua Baodian | 89 |
| Yu Yutong | Hamo Gong | 39 |
| Yu Yutong | Dagou Bangfa | 63 |
| Shi Qing | Hamo Gong | 96 |
| Xi Ren | Hamo Gong | 86 |
| Xi Ren | Dagou Bangfa | 83 |
| Lin Daiyu | Taiji Quan | 57 |
| Lin Daiyu | Jinshe Jianfa | 93 |
+-------------+----------------+-------+
15 rows in set (0.00 sec) 嚴禁出現四張表join的情況 自聯結顧名思義就是把一張表假設為兩張一樣的表，然後在做“多表查詢” - 先建構一張表
MariaDB [hellodb]> create table emp (id int, name char(20),leaderid int);
Query OK, 0 rows affected (0.01 sec)
MariaDB [hellodb]> insert emp value(1,'huangshang',null);
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> insert emp value(2,'taihou','huangshang');
ERROR 1366 (22007): Incorrect integer value: 'huangshang' for column `hellodb`.`emp`.`leaderid` at row 1
MariaDB [hellodb]> insert emp value(2,'taihou',1);
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> insert emp value(3,'guifei',2);
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> insert emp value(4,'shufei',3);
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> select * from emp;
+------+------------+----------+
| id | name | leaderid |
+------+------------+----------+
| 1 | huangshang | NULL |
| 2 | taihou | 1 |
| 3 | guifei | 2 |
| 4 | shufei | 3 |
+------+------------+----------+
4 rows in set (0.00 sec) - 查詢表裡的上級的姓名。
- 想象為兩張表 員工表 和上司表 起别名做成
MariaDB [hellodb]> select * from emp as e inner join emp as l on e.leaderid=l.id;
+------+--------+----------+------+------------+----------+
| id | name | leaderid | id | name | leaderid |
+------+--------+----------+------+------------+----------+
| 2 | taihou | 1 | 1 | huangshang | NULL |
| 3 | guifei | 2 | 2 | taihou | 1 |
| 4 | shufei | 3 | 3 | guifei | 2 |
+------+--------+----------+------+------------+----------+
3 rows in set (0.00 sec) - 取出來對應的上級，但是缺失了最上級。
MariaDB [hellodb]> select e.name emp,l.name leader from emp as e inner join emp as l on e.leaderid=l.id;
+--------+------------+
| emp | leader |
+--------+------------+
| taihou | huangshang |
| guifei | taihou |
| shufei | guifei |
+--------+------------+
3 rows in set (0.00 sec) - 取出每個人對應的上級的id。
MariaDB [hellodb]> select e.name emp,l.name leader from emp as e left join emp as l on e.leaderid=l.id;
+------------+------------+
| emp | leader |
+------------+------------+
| taihou | huangshang |
| guifei | taihou |
| shufei | guifei |
| huangshang | NULL |
+------------+------------+
4 rows in set (0.00 sec)