主頁 > 資料庫 > MySQL資料庫多表查詢

MySQL資料庫多表查詢

2020-09-22 02:54:06 資料庫

MySQL資料庫多表查詢

目錄
  • MySQL資料庫多表查詢
    • 多表查詢
      • 子查詢
      • 聯合查詢
      • 交叉連接
      • 內連接
      • 外連接
      • 左外連接 left outer
      • 右外鏈接：right outer
      • 完全外連接
      • 三張表查詢
      • 自連接

多表查詢

  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陳述句的關鍵字執行順序
在這里插入圖片描述

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/101305.html

標籤：MySQL

上一篇：mysql登錄錯誤：'Access denied for user 'root'@'localhost'

下一篇：在cnetos7上搭建mysql主從服務

標籤雲
其他(157675) Python(38076) JavaScript(25376) Java(17977) C(15215) 區塊鏈(8255) C＃(7972) AI(7469) 爪哇(7425) MySQL(7132) html(6777) 基礎類(6313) sql(6102) 熊猫(6058) PHP(5869) 数组(5741) R(5409) Linux(5327) 反应(5209) 腳本語言(PerlPython)(5129) 非技術區(4971) Android(4554) 数据框(4311) css(4259) 节点.js(4032) C語言(3288) json(3245) 列表(3129) 扑(3119) C++語言(3117) 安卓(2998) 打字稿(2995) VBA(2789) Java相關(2746) 疑難問題(2699) 细绳(2522) 單片機工控(2479) iOS(2429) ASP.NET(2402) MongoDB(2323) 麻木的(2285) 正则表达式(2254) 字典(2211) 循环(2198) 迅速(2185) 擅长(2169) 镖(2155) 功能(1967) .NET技术(1958) Web開發(1951) python-3.x(1918) HtmlCss(1915) 弹簧靴(1913) C++(1909) xml(1889) PostgreSQL(1872) .NETCore(1853) 谷歌表格(1846) Unity3D(1843) for循环(1842)

熱門瀏覽
  • GPU虛擬機創建時間深度優化

    **?桔妹導讀：**GPU虛擬機實體創建速度慢是公有云面臨的普遍問題，由于通常情況下創建虛擬機屬于低頻操作而未引起業界的重視，實際生產中還是存在對GPU實體創建時間有苛刻要求的業務場景。本文將介紹滴滴云在解決該問題時的思路、方法、并展示最終的優化成果。 從公有云服務商那里購買過虛擬主機的資深用戶，一 ......

    uj5u.com 2020-09-10 06:09:13 more
  • 可編程網卡芯片在滴滴云網路的應用實踐

    **?桔妹導讀：**隨著云規模不斷擴大以及業務層面對延遲、帶寬的要求越來越高，采用DPDK 加速網路報文處理的方式在橫向縱向擴展都出現了局限性。可編程芯片成為業界熱點。本文主要講述了可編程網卡芯片在滴滴云網路中的應用實踐，遇到的問題、帶來的收益以及開源社區貢獻。 #1. 資料中心面臨的問題 隨著滴滴 ......

    uj5u.com 2020-09-10 06:10:21 more
  • 滴滴資料通道服務演進之路

    **?桔妹導讀：**滴滴資料通道引擎承載著全公司的資料同步，為下游實時和離線場景提供了必不可少的源資料。隨著任務量的不斷增加，資料通道的整體架構也隨之發生改變。本文介紹了滴滴資料通道的發展歷程，遇到的問題以及今后的規劃。 #1. 背景 資料，對于任何一家互聯網公司來說都是非常重要的資產，公司的大資料 ......

    uj5u.com 2020-09-10 06:11:05 more
  • 滴滴AI Labs斬獲國際機器翻譯大賽中譯英方向世界第三

    **桔妹導讀：**深耕人工智能領域，致力于探索AI讓出行更美好的滴滴AI Labs再次斬獲國際大獎，這次獲獎的專案是什么呢？一起來看看詳細報道吧！ 近日，由國際計算語言學協會ACL（The Association for Computational Linguistics）舉辦的世界最具影響力的機器 ......

    uj5u.com 2020-09-10 06:11:29 more
  • MPP (Massively Parallel Processing)大規模并行處理

    1、什么是mpp？ MPP (Massively Parallel Processing)，即大規模并行處理，在資料庫非共享集群中，每個節點都有獨立的磁盤存盤系統和記憶體系統，業務資料根據資料庫模型和應用特點劃分到各個節點上，每臺資料節點通過專用網路或者商業通用網路互相連接，彼此協同計算，作為整體提供 ......

    uj5u.com 2020-09-10 06:11:41 more
  • 滴滴資料倉庫指標體系建設實踐

    **桔妹導讀：**指標體系是什么？如何使用OSM模型和AARRR模型搭建指標體系？如何統一流程、規范化、工具化管理指標體系？本文會對建設的方法論結合滴滴資料指標體系建設實踐進行解答分析。 #1. 什么是指標體系 ##1.1 指標體系定義 指標體系是將零散單點的具有相互聯系的指標，系統化的組織起來，通 ......

    uj5u.com 2020-09-10 06:12:52 more
  • 單表千萬行資料庫 LIKE 搜索優化手記

    我們經常在資料庫中使用 LIKE 運算子來完成對資料的模糊搜索，LIKE 運算子用于在 WHERE 子句中搜索列中的指定模式。 如果需要查找客戶表中所有姓氏是“張”的資料，可以使用下面的 SQL 陳述句： SELECT * FROM Customer WHERE Name LIKE '張%' 如果需要 ......

    uj5u.com 2020-09-10 06:13:25 more
  • 滴滴Ceph分布式存盤系統優化之鎖優化

    **桔妹導讀：**Ceph是國際知名的開源分布式存盤系統，在工業界和學術界都有著重要的影響。Ceph的架構和演算法設計發表在國際系統領域頂級會議OSDI、SOSP、SC等上。Ceph社區得到Red Hat、SUSE、Intel等大公司的大力支持。Ceph是國際云計算領域應用最廣泛的開源分布式存盤系統， ......

    uj5u.com 2020-09-10 06:14:51 more
  • es~通過ElasticsearchTemplate進行聚合~嵌套聚合

    之前寫過《es~通過ElasticsearchTemplate進行聚合操作》的文章，這一次主要寫一個嵌套的聚合，例如先對sex集合，再對desc聚合，最后再對age求和，共三層嵌套。 Aggregations的部分特性類似于SQL語言中的group by，avg，sum等函式,Aggregation ......

    uj5u.com 2020-09-10 06:14:59 more
  • 爬蟲日志監控 -- Elastc Stack（ELK）部署

    傻瓜式部署，只需替換IP與用戶 導讀： 現ELK四大組件分別為：Elasticsearch（核心）、logstash（處理）、filebeat（采集）、kibana（可視化） 下載均在https://www.elastic.co/cn/downloads/下tar包，各組件版本最好一致，配合fdm會 ......

    uj5u.com 2020-09-10 06:15:05 more
最新发布
  • day02-2-商鋪查詢快取

    功能02-商鋪查詢快取 3.商鋪詳情快取查詢 3.1什么是快取？ 快取就是資料交換的緩沖區（稱作Cache），是存盤資料的臨時地方，一般讀寫性能較高。 快取的作用： 降低后端負載 提高讀寫效率，降低回應時間 快取的成本： 資料一致性成本 代碼維護成本 運維成本 3.2需求說明 如下，當我們點擊商店詳 ......

    uj5u.com 2023-04-20 08:33:24 more
  • MySQL中binlog備份腳本分享

    關于MySQL的二進制日志（binlog），我們都知道二進制日志（binlog）非常重要，尤其當你需要point to point災難恢復的時侯，所以我們要對其進行備份。關于二進制日志（binlog)的備份,可以基于flush logs方式先切換binlog，然后拷貝&壓縮到到遠程服務器或本地服務器 ......

    uj5u.com 2023-04-20 08:28:06 more
  • day02-短信登錄

    功能實作02 2.功能01-短信登錄 2.1基于Session實作登錄 2.1.1思路分析 2.1.2代碼實作 2.1.2.1發送短信驗證碼 發送短信驗證碼： 發送驗證碼的介面為：http://127.0.0.1:8080/api/user/code?phone=xxxxx<手機號> 請求方式：PO ......

    uj5u.com 2023-04-20 08:27:27 more
  • 快取與資料庫雙寫一致性幾種策略分析

    本文將對幾種快取與資料庫保證資料一致性的使用方式進行分析。為保證高并發性能，以下分析場景不考慮執行的原子性及加鎖等強一致性要求的場景，僅追求最終一致性。 ......

    uj5u.com 2023-04-20 08:26:48 more
  • sql陳述句優化

    問題查找及措施 問題查找 需要找到具體的代碼，對其進行一對一優化，而非一直把關注點放在服務器和sql平臺 降低簡化每個事務中處理的問題，盡量不要讓一個事務拖太長的時間 例如檔案上傳時，應將檔案上傳這一步放在事務外面 微軟建議 4.啟動sql定時執行計劃 怎么啟動sqlserver代理服務-百度經驗 ......

    uj5u.com 2023-04-20 08:26:35 more
  • 云時代，MySQL到ClickHouse資料同步產品對比推薦

    ClickHouse 在執行分析查詢時的速度優勢很好的彌補了MySQL的不足，但是對于很多開發者和DBA來說，如何將MySQL穩定、高效、簡單的同步到 ClickHouse 卻很困難。本文對比了 NineData、MaterializeMySQL（ClickHouse自帶）、Bifrost 三款產品... ......

    uj5u.com 2023-04-20 08:26:29 more
  • sql陳述句優化

    問題查找及措施 問題查找 需要找到具體的代碼，對其進行一對一優化，而非一直把關注點放在服務器和sql平臺 降低簡化每個事務中處理的問題，盡量不要讓一個事務拖太長的時間 例如檔案上傳時，應將檔案上傳這一步放在事務外面 微軟建議 4.啟動sql定時執行計劃 怎么啟動sqlserver代理服務-百度經驗 ......

    uj5u.com 2023-04-20 08:25:13 more
  • Redis 報”OutOfDirectMemoryError“（堆外記憶體溢位)

    Redis 報錯“OutOfDirectMemoryError（堆外記憶體溢位) ”問題如下： 一、報錯資訊： 使用 Redis 的業務介面 ，產生 OutOfDirectMemoryError（堆外記憶體溢位），如圖： 格式化后的報錯資訊： { "timestamp": "2023-04-17 22: ......

    uj5u.com 2023-04-20 08:24:54 more
  • day02-2-商鋪查詢快取

    功能02-商鋪查詢快取 3.商鋪詳情快取查詢 3.1什么是快取？ 快取就是資料交換的緩沖區（稱作Cache），是存盤資料的臨時地方，一般讀寫性能較高。 快取的作用： 降低后端負載 提高讀寫效率，降低回應時間 快取的成本： 資料一致性成本 代碼維護成本 運維成本 3.2需求說明 如下，當我們點擊商店詳 ......

    uj5u.com 2023-04-20 08:24:03 more
  • day02-短信登錄

    功能實作02 2.功能01-短信登錄 2.1基于Session實作登錄 2.1.1思路分析 2.1.2代碼實作 2.1.2.1發送短信驗證碼 發送短信驗證碼： 發送驗證碼的介面為：http://127.0.0.1:8080/api/user/code?phone=xxxxx<手機號> 請求方式：PO ......

    uj5u.com 2023-04-20 08:23:11 more

Copyright © 2010-2020 有解無憂