MySQL資料庫多表查詢

来源:https://www.cnblogs.com/www233ii/archive/2019/11/23/11919522.html
-Advertisement-
Play Games

MySQL資料庫多表查詢 [toc] 多表查詢 1. 查詢結果來自於多張表，即多表查詢 子查詢 常用在WHERE子句中的子查詢 1. 用於比較表達式中的子查詢；子查詢僅能返回單個值(查詢s1表中大於平均年齡的人) 2. 查詢結果嵌入到另一個表裡,小數轉換整數會四捨五入 3. 多表查詢: 用子迴圈查看 ...


目錄

MySQL資料庫多表查詢

多表查詢

  1. 查詢結果來自於多張表，即多表查詢
子查詢：在SQL語句嵌套著查詢語句，性能較差，基於某語句的查詢結果再次進行的查詢
聯合查詢：UNION   
交叉連接：笛卡爾乘積   
內連接：
       等值連接：讓表之間的欄位以“等值”建立連接關係  
       不等值連接:不等值連接查詢就是無條件判斷，若查詢多個表內的數據，其中的數據不會同步，各自把各自的展現出來，沒有任何關聯。
       自然連接:去掉重覆列的等值連接   
外連接：
   左外連接：FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col
   右外連接：FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col
自連接：本表和本表進行連接查詢

子查詢

常用在WHERE子句中的子查詢
  1. 用於比較表達式中的子查詢；子查詢僅能返回單個值(查詢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)
  1. 查詢結果嵌入到另一個表裡,小數轉換整數會四捨五入
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)
  1. 多表查詢:
    用子迴圈查看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)

聯合查詢

  1. 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)
  1. 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)
  1. 使用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）
  1. 兩張表使用交叉連接就是這張表的每一行去和另一張表的所有行組合一遍，形成新的行。
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 |
  1. 挑出兩張表的個別欄位。
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  |
                  （省略）
  1. 對錶起別名利用表的別名來查詢數據。（定義別名是在欄位的最後用在前面用，只在當前欄位生效）
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'

內連接

等值連接：讓表之間的欄位以“等值”建立連接關係
  1. 把兩個表有交集的地方連接起來
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)
  1. 內連接之後過濾：先連接再過濾，顯示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)
  1. 查詢完之後過濾
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   （排在後面的）

左外連接 left outer

在這裡插入圖片描述

  1. 學生表全留下來老師的只留下來有交集的地方。 (沒有交集的地方空值代替)
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)
  1. 左外連接擴展用法
    在這裡插入圖片描述
  • 取出沒有老師教的學生 (用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)

右外鏈接：right outer

在這裡插入圖片描述

  1. 老師表全留下來，學生表有交集的地方留下來。
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)
  1. 右外連接擴展用法
    在這裡插入圖片描述
  • 沒有教學生的老師留下來。 (和左外連接的邏輯是一樣的)
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

在這裡插入圖片描述

  1. 把左外連接和右外連接用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)
  1. 完全外連接擴展用法：有交集的地方去除掉，只留外連接。
    在這裡插入圖片描述
  • 把沒有老師的學生，和沒有學生的老師取出來。
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)

三張表查詢

取學生姓名，成績，科目。
  1. 先取出學生的姓名和成績 （分兩步做思路清晰一點）
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)
  1. 在連接一次取出學生姓名，成績，科目。
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的情況

自連接

自聯結顧名思義就是把一張表假設為兩張一樣的表，然後在做“多表查詢”
  1. 先構建一張表
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)
  1. 查詢表裡的上級的姓名。
  • 想象為兩張表 員工表 和上司表 起別名做成
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)

SQL語句的關鍵字執行順序
在這裡插入圖片描述


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 精通awk系列(6)：awk命令結構和awk語法結構
    回到： "Linux系列文章" "Shell系列文章" "Awk系列文章" awk命令行結構和語法結構 awk命令行結構 其中： awk語法結構 awk語法結構即awk代碼部分的結構。 awk的語法充斥著 的模式，它們稱為awk rule。 例如： 上面示例中，有BEGIN語句塊，有END語句塊，還 ...
  • MySQL語句使用。
    MySQL的DDL、DML、DQL語句和單表增、刪、改、查 [toc] 實驗準備： 1. 初始化設置過的centos虛擬機 2. 安裝MySQL資料庫 實驗開始： DDL語句 1. 創建和刪除資料庫 2. 查詢資料庫字元集 MariaDB [(none)] show create database ...
  • 精通awk系列(3)：鋪墊知識：讀取文件的幾種方式
    回到： "Linux系列文章" "Shell系列文章" "Awk系列文章" 讀取文件的幾種方式 讀取文件有如下幾種常見的方式： 下麵使用Shell的read命令來演示前4種讀取文件的方式(第五種按位元組數讀取的方式read不支持)。 按字元數量讀取 read的 n選項和 N選項可以指定一次性讀取多少個 ...
  • CentOS7 忘記Root密碼解決方法
    1- 在啟動grub菜單，選擇編輯選項啟動 ​ 2 - 按鍵盤e鍵，來進入編輯界面 ​ 3 - 找到Linux 16的那一行，將ro改為rw init=/sysroot/bin/sh ​ 4 - 現在按下 Control+x ，使用單用戶模式啟動 ​ 5 - 現在，可以使用下麵的命令訪問系統 chr ...
  • 【解決 FTP】windows訪問Linux的vsftpd(FTP伺服器）問題200 Switching to ASCII mode，227 Entering Passive Mode
    轉載：關於FTP主動模式(active mode)與被動模式(passive mode)的工作原理： 主動模式（伺服器向客戶端敲門，然後客戶端開門）FTP：客戶機與伺服器之間建立連接時，客戶機是大於1024的埠上，伺服器是20埠。客戶機的埠是大於1024的，而伺服器的埠是21埠接收請求，而 ...
  • Apache限制虛擬主機最大連接數和帶寬
    1.下載所需安裝包： ​ 2.解壓到伺服器，編譯安裝： ​ 安裝中可能遇到的問題1 提示如下錯誤：/mnt/vdd/mod_bw.c: In function 'get_bw_rate':/mnt/vdd/mod_bw.c:567:59: error: 'conn_rec' has no membe ...
  • Linux：DNS伺服器搭建
    DNS簡介 DNS（Domain Name System）功能變數名稱系統： 是一種採用客戶端/伺服器機制，負責實現電腦名稱與IP地址轉換的系統。DNS作為一種重要的網路服務，既是國際互聯網工作的基礎，同時在企業內部網路中也得到了廣泛地應用。 DNS伺服器的作用 正向解析：根據主機名稱（功能變數名稱）查找對應的I ...
  • 關於Linux下進程的詳解【進程查看與管理】
    一、關於進程 進程： 已經啟動的可執行程式的運行實力 進程的組成：一個進程包含內核中的一部分地址空間和一系列數據結構。其中地址空間是內核標記的一部分記憶體以供進程使用，而數據結構則用來紀錄每個進程的具體信息。 最主要的進程信息包括： 進程的地址空間圖 進程當前的狀態（ sleeping、stopped ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類