广告

扫码关注官方微信

扫码下载APP
返回顶部
首页 > 资讯 > 数据库 >MySQL数据库多表查询
  • 177

0
分享到

MySQL数据库多表查询

MySQL数据库多表查询 2017-06-26 00:06:08 177人浏览 才女
摘要

目录 Mysql数据库多表查询 多表查询 子查询 联合查询 交叉连接 内连接 外连接

目录

  • Mysql数据库多表查询
    • 多表查询
      • 子查询
      • 联合查询
      • 交叉连接
      • 内连接
      • 外连接
      • 左外连接 left outer
      • 右外链接：right outer
      • 完全外连接
      • 三张表查询
      • 自连接

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语句的关键字执行顺序
在这里插入图片描述

您可能感兴趣的文档:
点击免费下载>>软考高级考试备考技巧/历年真题/备考精华资料

--结束END--

本文标题: MySQL数据库多表查询

本文链接: https://www.lsjlt.com/news/2581.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com    QQ/279061341

本篇文章演示代码以及资料文档资料下载

下载Word文档到电脑，方便收藏和打印～

下载Word文档
去做题
猜你喜欢
  • MySQL数据库多表查询
    目录 MySQL数据库多表查询 多表查询 子查询 联合查询 交叉连接 内连接 外连接 ...
    99+
    2017-06-26
    MySQL数据库多表查询
  • 【MySQL数据库】- 多表查询
    🍁博客主页：👉@不会压弯的小飞侠 ✨欢迎关注：👉点赞👍收藏⭐留言✒ ✨系列专栏：👉MySQL数据库专栏 ✨欢迎加入社区： 👉不会压弯的小...
    99+
    2023-08-17
    数据库 mysql sql
  • MySQL数据库查询之多表查询总结
    目录多表关系表与表之间的联系：一对多(多对一)多对多一对一多表查询多表查询的分类1.连接查询：2.子查询内连接外连接自连接联合查询子查询标量子查询列子查询行子查询表子查询多表查询案例...
    99+
    2022-11-13
    mysql多表查询语句 mysql多表连接查询方式 mysql多表查询面试题
  • MySQL数据库：多表连接查询
    多表连接查询 注意：使用连接技术建议将表经行重命名！ # explain 检索连接是否达标 # 内连接 # 语法1 from 表1 inner join 表2 on 主键字段=外键字段 [where 条件表达式] # 语法2 ...
    99+
    2022-01-30
    MySQL数据库：多表连接查询
  • MySQL数据库中的多表查询
    这期内容当中小编将会给大家带来有关MySQL数据库中的多表查询，以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。多表查询查询结果来自于多张表，即多表查询子查询：在SQL语句嵌套着查询语句，...
    99+
    2022-10-18
  • mysql数据库表的多条件查询
    mysql数据库表的多条件查询 一、select语句基本查询 SELECT 字段1,字段2....FROM 表名[WHERE 条件] [LIMIT N][ OFFSET M] select可以返回多条数据也可以返回一条数据...
    99+
    2023-08-22
    数据库 mysql sql
  • MySQL数据库查询进阶之多表查询详解
    目录一、多表查询1.引出2.笛卡尔积3. 笛卡尔积的解决方法二、多表查询分类1.等值连接和非等值连接2.自连接和非自连接3.内连接和外连接4.自然连接5.using连接三、子查询1....
    99+
    2022-11-13
  • MySQL数据库查询中怎么实现多表查询
    今天小编给大家分享一下MySQL数据库查询中怎么实现多表查询的相关知识点，内容详细，逻辑清晰，相信大部分人都还太了解这方面的知识，所以分享这篇文章给大家参考一下，希望大家阅读完这篇文章后有所收获，下面我们一起来了解一下吧。一、多表查询多表查...
    99+
    2023-06-29
  • mysql如何查询数据库有多少表
    这篇文章主要为大家展示了“mysql如何查询数据库有多少表”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“mysql如何查询数据库有多少表”这篇文章吧。 ...
    99+
    2022-10-19
  • MySql数据库单表查询与多表连接查询效率对比
    这段时间在做项目的过程中，遇到一个模块，数据之间的联系很复杂，在建表的时候就很纠结，到底该怎么去处理这些复杂的数据呢，是单表查询，然后在业务层去处理数据间的关系，还是直接通过多表连接...
    99+
    2022-11-12
  • Mysql数据库入门基础篇--mysql 多表查询
    【Mysql数据库入门基础篇--mysql 多表查询 🔻一、mysql 多表查询1.1 🍃 7种sql joins 的实现1.2 🍃 错误写法-...
    99+
    2023-09-05
    数据库 mysql sql mysql多表查询 select linux 运维
  • mysql如何查询数据库有多少张表
    这篇文章主要介绍“mysql如何查询数据库有多少张表”的相关知识，小编通过实际案例向大家展示操作过程，操作方法简单快捷，实用性强，希望这篇“mysql如何查询数据库有多少张表”文章能帮助大家解决问题。 ...
    99+
    2022-10-19
  • MySQL之单表查询、多表查询
    一、单表查询： 单个表的查询方法及语法顺序需要通过实际例子来熟悉 先将表数据创建下： mysql> create database singe_t1; # 建个数据库singe_t1 Query OK, 1 row aff...
    99+
    2023-01-31
    MySQL
  • 【MySQL】MySQL表之联合查询（多表查询）
    📌前言：本篇博客介绍MySQL数据库的MySQL表之联合查询（多表查询），学习MySQL之前要先安装好MySQL，如果还没有安装的小伙伴可以看看博主前面的博客，里面有详细的安装教程。 那我们废话不多说，直接进入主体！...
    99+
    2023-08-16
    mysql 数据库 java
  • MySQL----多表查询
    MySQL----多表查询 多表关系表与表之间的联系：一对多(多对一)多对多一对一 多表查询多表查询的分类数据准备内连接外连接自连接联合查询子查询标量子查询列子查询行子查询表子查询 多表查询案例 多表关系 在进...
    99+
    2023-08-19
    mysql sql 数据库 多表查询 DB
  • 【MySQL】多表查询
    多表查询 多表关系 一对多（多对一）多对多一对一 一对多 案例：部门与员工 关系：一个部门对应多个员工，一个员工对应一个部门 实现：在多的一方建立外键，指向一的一方的主键 多对多 案例：学生与课程 关...
    99+
    2023-10-04
    mysql 数据库 sql
  • MySQL 多表查询
      union 结果集合并 使用多个select分别查询不同的表，把多个select查到的记录合并在一起 一个select查到m条记录，另一个select查到n条记录，合并之后就是m+n条记录   #查询全校...
    99+
    2017-05-29
    MySQL 多表查询
  • MySQL --- 多表查询
    多表查询、事务、以及提升查询效率最有手段的索引  一. 多表查询 1.1 多表查询 --- 概述 1.1.1 数据准备 将资料中准备好的多表查询数据准备的SQL脚本导入数据库中。 部门表： 员工表： 1.1.2 介绍 多表查询：指从多张表...
    99+
    2023-09-02
    mysql 数据库
  • MySQL多表查询
    文章目录 一、什么是多表查询1、概述2、笛卡尔积现象 二、多表查询的分类1、等值连接 vs非等值连接2、自连接 vs 非自连接3、内连接 vs 外连接4、满外连接（FULL OUTE...
    99+
    2023-09-01
    mysql 数据库 sql
  • mysql-多表查询
    准备工作：准备两张表，部门表（department）、员工表（employee）mysql> create database db2;Query OK, 1 row affected (0.00 sec)mysql> use d...
    99+
    2023-01-30
    mysql
软考高级职称资格查询
软考职称历年真题下载
软考职称资料下载
热门wiki
近期文章
推荐阅读
热门问答
热门标签
编程网，编程工程师的家园，是目前国内优秀的开源技术社区之一，形成了由开源软件库、代码分享、资讯、协作翻译、讨论区和博客等几大频道内容，为IT开发者提供了一个发现、使用、并交流开源技术的平台。

  • 官方手机版

  • 微信公众号

  • 商务合作