浅谈MySQL数据库多表查询

数据库  /  管理员 发布于 8年前   416

本篇文章和大家一起了解一下MySQL数据库多表查询。有一定的参考价值，有需要的朋友可以参考一下，希望对大家有所帮助。

多表查询

查询结果来自于多张表，即多表查询

子查询：在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)

2、查询结果嵌入到另一个表里,小数转换整数会四舍五入

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: 0MariaDB [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: 0MariaDB [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: 0MariaDB [hellodb]> select * from teachers;+-----+---------------+-----+--------+| TID | Name          | Age | Gender |+-----+---------------+-----+--------+|   1 | Song Jiang    |  25 | M      ||   2 | Zhang Sanfeng |  25 | M      ||   3 | Miejue Shitai |  25 | F      ||   4 | Lin Chaoying  |  25 | F      |+-----+---------------+-----+--------+4 rows in set (0.00 sec)

多表查询:

用子循环查看s1表，显示teachers表年龄大于s1表平均年龄的人的信息。

MariaDB [hellodb]> update teachers set age=45 where tid=1;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0MariaDB [hellodb]> update teachers set age=94 where tid=2;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0MariaDB [hellodb]> update teachers set age=77 where tid=3;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0MariaDB [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: 0MariaDB [hellodb]> select * from teachers where age > (select avg(age) from s1);   （最后一条也大于25就显示出来了）+-----+---------------+-----+--------+ | TID | Name          | Age | Gender |+-----+---------------+-----+--------+|   1 | Song Jiang    |  45 | M      ||   2 | Zhang Sanfeng |  94 | M      ||   3 | Miejue Shitai |  77 | F      ||   4 | Lin Chaoying  |  26 | F      |+-----+---------------+-----+--------+4 rows in set (0.00 sec)

联合查询

union 纵向合并两张表，表头来自第一条查询记录.

MariaDB [hellodb]> select * from teachers    -> union    -> select stuid,name,age,gender from s1;+-----+---------------+-----+--------+| TID | Name          | Age | Gender |+-----+---------------+-----+--------+|   1 | Song Jiang    |  45 | M      ||   2 | Zhang Sanfeng |  94 | M      ||   3 | Miejue Shitai |  77 | F      ||   4 | Lin Chaoying  |  26 | F      ||   1 | Shi Zhongyu   |  22 | M      ||   2 | Shi Potian    |  22 | M      ||   3 | Xie Yanke     |  53 | M      ||   4 | Ding Dian     |  32 | M      ||   5 | Yu Yutong     |  26 | M      ||   6 | Shi Qing      |  46 | M      ||   7 | Xi Ren        |  19 | F      ||   8 | Lin Daiyu     |  17 | F      ||   9 | Ren Yingying  |  20 | F      ||  10 | Yue Lingshan  |  19 | F      ||  11 | Yuan Chengzhi |  23 | M      ||  12 | Wen Qingqing  |  19 | F      ||  13 | Tian Boguang  |  33 | M      ||  14 | Lu Wushuang   |  17 | F      ||  15 | Duan Yu       |  19 | M      ||  16 | Xu Zhu        |  21 | M      ||  17 | Lin Chong     |  25 | M      ||  18 | Hua Rong      |  23 | M      ||  19 | Xue Baochai   |  18 | F      ||  20 | Diao Chan     |  19 | F      ||  21 | Huang Yueying |  22 | F      ||  22 | Xiao Qiao     |  20 | F      ||  23 | Ma Chao       |  23 | M      ||  24 | Xu Xian       |  27 | M      ||  25 | Sun Dasheng   | 100 | M      ||  26 | xietingfeng   |  23 | M      ||  27 | liudehua      |  18 | F      ||  28 | mahuateng     |  20 | M      ||  29 | wuyanzu       |  19 | M      ||  30 | wuzetian      |  21 | F      ||  31 | Song Jiang    |  18 | M      ||  32 | Zhang Sanfeng |  18 | M      ||  33 | Miejue Shitai |  18 | F      ||  34 | Lin Chaoying  |  18 | F      ||  38 | abc           |  20 | M      |+-----+---------------+-----+--------+39 rows in set (0.00 sec)MariaDB [hellodb]> select tid as id ,name,age,gender from teachers union select stuid,name,age,gender from s1;  (起个别名替换掉表头的tid并纵向合并两张表)+----+---------------+-----+--------+| id | name          | age | gender |+----+---------------+-----+--------+|  1 | Song Jiang    |  45 | M      ||  2 | Zhang Sanfeng |  94 | M      ||  3 | Miejue Shitai |  77 | F      ||  4 | Lin Chaoying  |  26 | F      ||  1 | Shi Zhongyu   |  22 | M      ||  2 | Shi Potian    |  22 | M      ||  3 | Xie Yanke     |  53 | M      ||  4 | Ding Dian     |  32 | M      ||  5 | Yu Yutong     |  26 | M      ||  6 | Shi Qing      |  46 | M      ||  7 | Xi Ren        |  19 | F      ||  8 | Lin Daiyu     |  17 | F      ||  9 | Ren Yingying  |  20 | F      || 10 | Yue Lingshan  |  19 | F      || 11 | Yuan Chengzhi |  23 | M      || 12 | Wen Qingqing  |  19 | F      || 13 | Tian Boguang  |  33 | M      || 14 | Lu Wushuang   |  17 | F      || 15 | Duan Yu       |  19 | M      || 16 | Xu Zhu        |  21 | M      || 17 | Lin Chong     |  25 | M      || 18 | Hua Rong      |  23 | M      || 19 | Xue Baochai   |  18 | F      || 20 | Diao Chan     |  19 | F      || 21 | Huang Yueying |  22 | F      || 22 | Xiao Qiao     |  20 | F      || 23 | Ma Chao       |  23 | M      || 24 | Xu Xian       |  27 | M      || 25 | Sun Dasheng   | 100 | M      || 26 | xietingfeng   |  23 | M      || 27 | liudehua      |  18 | F      || 28 | mahuateng     |  20 | M      || 29 | wuyanzu       |  19 | M      || 30 | wuzetian      |  21 | F      || 31 | Song Jiang    |  18 | M      || 32 | Zhang Sanfeng |  18 | M      || 33 | Miejue Shitai |  18 | F      || 34 | Lin Chaoying  |  18 | F      || 38 | abc           |  20 | M      |+----+---------------+-----+--------+39 rows in set (0.00 sec)

union 自己和自己相连可以去重。

MariaDB [hellodb]> create table t2 select * from teachers;  （先导一张表出来做实验不能有主键，所以只用了这种方法导了数据没有把主键导过来）Query OK, 4 rows affected (0.01 sec)Records: 4  Duplicates: 0  Warnings: 0MariaDB [hellodb]> select * from t2;+-----+---------------+-----+--------+| TID | Name          | Age | Gender |+-----+---------------+-----+--------+|   1 | Song Jiang    |  45 | M      ||   2 | Zhang Sanfeng |  94 | M      ||   3 | Miejue Shitai |  77 | F      ||   4 | Lin Chaoying  |  26 | F      |+-----+---------------+-----+--------+4 rows in set (0.00 sec)MariaDB [hellodb]> desc t2;+--------+----------------------+------+-----+---------+-------+| Field  | Type     | Null | Key | Default | Extra |+--------+----------------------+------+-----+---------+-------+| TID    | smallint(5) unsigned | NO   |     | 0       |       || Name   | varchar(100)         | NO   |     | NULL    |       || Age    | tinyint(3) unsigned  | NO   |     | NULL    |       || Gender | enum('F','M')        | YES  |     | NULL    |       |+--------+----------------------+------+-----+---------+-------+4 rows in set (0.00 sec)（添加重复的行做实验）MariaDB [hellodb]> insert into t2 set tid=4,name='linchaoying',age=26,gender='F';  （这条记录添加的只有name不一样少了个空格）Query OK, 1 row affected (0.00 sec)MariaDB [hellodb]> insert into t2 set tid=4,name='lin chaoying',age=26,gender='F';  （完全一样加了一行）Query OK, 1 row affected (0.00 sec)MariaDB [hellodb]> select * from t2;+-----+---------------+-----+--------+| TID | Name          | Age | Gender |+-----+---------------+-----+--------+|   1 | Song Jiang    |  45 | M      ||   2 | Zhang Sanfeng |  94 | M      ||   3 | Miejue Shitai |  77 | F      ||   4 | Lin Chaoying  |  26 | F      ||   4 | linchaoying   |  26 | F      ||   4 | lin chaoying  |  26 | F      |+-----+---------------+-----+--------+6 rows in set (0.00 sec)MariaDB [hellodb]> select * from t2 union select * from t2;  （用union过滤掉重复的行，少一个空格的那条记录过滤不了）+-----+---------------+-----+--------+| TID | Name          | Age | Gender |+-----+---------------+-----+--------+|   1 | Song Jiang    |  45 | M      ||   2 | Zhang Sanfeng |  94 | M      ||   3 | Miejue Shitai |  77 | F      ||   4 | Lin Chaoying  |  26 | F      ||   4 | linchaoying   |  26 | F      |+-----+---------------+-----+--------+5 rows in set (0.00 sec)

使用all 简单连接两张表不去重

MariaDB [hellodb]> select * from t2 union all select * from t2;+-----+---------------+-----+--------+| TID | Name          | Age | Gender |+-----+---------------+-----+--------+|   1 | Song Jiang    |  45 | M      ||   2 | Zhang Sanfeng |  94 | M      ||   3 | Miejue Shitai |  77 | F      ||   4 | Lin Chaoying  |  26 | F      ||   4 | linchaoying   |  26 | F      ||   4 | lin chaoying  |  26 | F      ||   1 | Song Jiang    |  45 | M      ||   2 | Zhang Sanfeng |  94 | M      ||   3 | Miejue Shitai |  77 | F      ||   4 | Lin Chaoying  |  26 | F      ||   4 | linchaoying   |  26 | F      ||   4 | lin chaoying  |  26 | F      |+-----+---------------+-----+--------+12 rows in set (0.00 sec)

交叉连接

两张表横向组合，类似于笛卡尔乘积。  （cross join）

两张表使用交叉连接就是这张表的每一行去和另一张表的所有行组合一遍，形成新的行。

MariaDB [hellodb]> select * from s1 cross join teachers;+-------+---------------+-------+-----+--------+---------+-----------+-----+---------------+-----+--------+| StuID | Name          | phone | Age | Gender | ClassID | TeacherID | TID | Name          | Age | Gender |+-------+---------------+-------+-----+--------+---------+-----------+-----+---------------+-----+--------+|     1 | Shi Zhongyu   |  NULL |  22 | M      |       2 |         3 |   1 | Song Jiang    |  25 | M      ||     1 | Shi Zhongyu   |  NULL |  22 | M      |       2 |         3 |   2 | Zhang Sanfeng |  25 | M      ||     1 | Shi Zhongyu   |  NULL |  22 | M      |       2 |         3 |   3 | Miejue Shitai |  25 | F      ||     1 | Shi Zhongyu   |  NULL |  22 | M      |       2 |         3 |   4 | Lin Chaoying  |  25 | F      ||     2 | Shi Potian    |  NULL |  22 | M      |       1 |         7 |   1 | Song Jiang    |  25 | M      ||     2 | Shi Potian    |  NULL |  22 | M      |       1 |         7 |   2 | Zhang Sanfeng |  25 | M      ||     2 | Shi Potian    |  NULL |  22 | M      |       1 |         7 |   3 | Miejue Shitai |  25 | F      ||     2 | Shi Potian    |  NULL |  22 | M      |       1 |         7 |   4 | Lin Chaoying  |  25 | F      ||     3 | Xie Yanke     |  NULL |  53 | M      |       2 |        16 |   1 | Song Jiang    |  25 | M      ||     3 | Xie Yanke     |  NULL |  53 | M      |       2 |        16 |   2 | Zhang Sanfeng |  25 | M      ||     3 | Xie Yanke     |  NULL |  53 | M      |       2 |        16 |   3 | Miejue Shitai |  25 | F      ||     3 | Xie Yanke     |  NULL |  53 | M      |       2 |        16 |   4 | Lin Chaoying  |  25 | F      ||     4 | Ding Dian     |  NULL |  32 | M      |       4 |         4 |   1 | Song Jiang    |  25 | M      ||     4 | Ding Dian     |  NULL |  32 | M      |       4 |         4 |   2 | Zhang Sanfeng |  25 | M      ||     4 | Ding Dian     |  NULL |  32 | M      |       4 |         4 |   3 | Miejue Shitai |  25 | F      ||     4 | Ding Dian     |  NULL |  32 | M      |       4 |         4 |   4 | Lin Chaoying  |  25 | F      ||     5 | Yu Yutong     |  NULL |  26 | M      |       3 |         1 |   1 | Song Jiang    |  25 | M      ||     5 | Yu Yutong     |  NULL |  26 | M      |       3 |         1 |   2 | Zhang Sanfeng |  25 | M      ||     5 | Yu Yutong     |  NULL |  26 | M      |       3 |         1 |   3 | Miejue Shitai |  25 | F      ||     5 | Yu Yutong     |  NULL |  26 | M      |       3 |         1 |   4 | Lin Chaoying  |  25 | F      ||     6 | Shi Qing      |  NULL |  46 | M      |       5 |      NULL |   1 | Song Jiang    |  25 | M      ||     6 | Shi Qing      |  NULL |  46 | M      |       5 |      NULL |   2 | Zhang Sanfeng |  25 | M      ||     6 | Shi Qing      |  NULL |  46 | M      |       5 |      NULL |   3 | Miejue Shitai |  25 | F      ||     6 | Shi Qing      |  NULL |  46 | M      |       5 |      NULL |   4 | Lin Chaoying  |  25 | F      ||     7 | Xi Ren        |  NULL |  19 | F      |       3 |      NULL |   1 | Song Jiang    |  25 | M      ||     7 | Xi Ren        |  NULL |  19 | F      |       3 |      NULL |   2 | Zhang Sanfeng |  25 | M      ||     7 | Xi Ren        |  NULL |  19 | F      |       3 |      NULL |   3 | Miejue Shitai |  25 | F      ||     7 | Xi Ren        |  NULL |  19 | F      |       3 |      NULL |   4 | Lin Chaoying  |  25 | F      ||     8 | Lin Daiyu     |  NULL |  17 | F      |       7 |      NULL |   1 | Song Jiang    |  25 | M      ||     8 | Lin Daiyu     |  NULL |  17 | F      |       7 |      NULL |   2 | Zhang Sanfeng |  25 | M      ||     8 | Lin Daiyu     |  NULL |  17 | F      |       7 |      NULL |   3 | Miejue Shitai |  25 | F      ||     8 | Lin Daiyu     |  NULL |  17 | F      |       7 |      NULL |   4 | Lin Chaoying  |  25 | F      ||     9 | Ren Yingying  |  NULL |  20 | F      |       6 |      NULL |   1 | Song Jiang    |  25 | M      ||     9 | Ren Yingying  |  NULL |  20 | F      |       6 |      NULL |   2 | Zhang Sanfeng |  25 | M      ||     9 | Ren Yingying  |  NULL |  20 | F      |       6 |      NULL |   3 | Miejue Shitai |  25 | F      ||     9 | Ren Yingying  |  NULL |  20 | F      |       6 |      NULL |   4 | Lin Chaoying  |  25 | F      ||    10 | Yue Lingshan  |  NULL |  19 | F      |       3 |      NULL |   1 | Song Jiang    |  25 | M      ||    10 | Yue Lingshan  |  NULL |  19 | F      |       3 |      NULL |   2 | Zhang Sanfeng |  25 | M      ||    10 | Yue Lingshan  |  NULL |  19 | F      |       3 |      NULL |   3 | Miejue Shitai |  25 | F      ||    10 | Yue Lingshan  |  NULL |  19 | F      |       3 |      NULL |   4 | Lin Chaoying  |  25 | F      ||    11 | Yuan Chengzhi |  NULL |  23 | M      |       6 |      NULL |   1 | Song Jiang    |  25 | M      ||    11 | Yuan Chengzhi |  NULL |  23 | M      |       6 |      NULL |   2 | Zhang Sanfeng |  25 | M      ||    11 | Yuan Chengzhi |  NULL |  23 | M      |       6 |      NULL |   3 | Miejue Shitai |  25 | F      ||    11 | Yuan Chengzhi |  NULL |  23 | M      |       6 |      NULL |   4 | Lin Chaoying  |  25 | F      ||    12 | Wen Qingqing  |  NULL |  19 | F      |       1 |      NULL |   1 | Song Jiang    |  25 | M      ||    12 | Wen Qingqing  |  NULL |  19 | F      |       1 |      NULL |   2 | Zhang Sanfeng |  25 | M      ||    12 | Wen Qingqing  |  NULL |  19 | F      |       1 |      NULL |   3 | Miejue Shitai |  25 | F      ||    12 | Wen Qingqing  |  NULL |  19 | F      |       1 |      NULL |   4 | Lin Chaoying  |  25 | F      ||    13 | Tian Boguang  |  NULL |  33 | M      |       2 |      NULL |   1 | Song Jiang    |  25 | M      ||    13 | Tian Boguang  |  NULL |  33 | M      |       2 |      NULL |   2 | Zhang Sanfeng |  25 | M      ||    13 | Tian Boguang  |  NULL |  33 | M      |       2 |      NULL |   3 | Miejue Shitai |  25 | F      ||    13 | Tian Boguang  |  NULL |  33 | M      |       2 |      NULL |   4 | Lin Chaoying  |  25 | F      ||    14 | Lu Wushuang   |  NULL |  17 | F      |       3 |      NULL |   1 | Song Jiang    |  25 | M      ||    14 | Lu Wushuang   |  NULL |  17 | F      |       3 |      NULL |   2 | Zhang Sanfeng |  25 | M      ||    14 | Lu Wushuang   |  NULL |  17 | F      |       3 |      NULL |   3 | Miejue Shitai |  25 | F      ||    14 | Lu Wushuang   |  NULL |  17 | F      |       3 |      NULL |   4 | Lin Chaoying  |  25 | F      ||    15 | Duan Yu       |  NULL |  19 | M      |       4 |      NULL |   1 | Song Jiang    |  25 | M      ||    15 | Duan Yu       |  NULL |  19 | M      |       4 |      NULL |   2 | Zhang Sanfeng |  25 | M      ||    15 | Duan Yu       |  NULL |  19 | M      |       4 |      NULL |   3 | Miejue Shitai |  25 | F      ||    15 | Duan Yu       |  NULL |  19 | M      |       4 |      NULL |   4 | Lin Chaoying  |  25 | F      ||    16 | Xu Zhu        |  NULL |  21 | M      |       1 |      NULL |   1 | Song Jiang    |  25 | M      ||    16 | Xu Zhu        |  NULL |  21 | M      |       1 |      NULL |   2 | Zhang Sanfeng |  25 | M      ||    16 | Xu Zhu        |  NULL |  21 | M      |       1 |      NULL |   3 | Miejue Shitai |  25 | F      ||    16 | Xu Zhu        |  NULL |  21 | M      |       1 |      NULL |   4 | Lin Chaoying  |  25 | F      ||    17 | Lin Chong     |  NULL |  25 | M      |       4 |      NULL |   1 | Song Jiang    |  25 | M      ||    17 | Lin Chong     |  NULL |  25 | M      |       4 |      NULL |   2 | Zhang Sanfeng |  25 | M      ||    17 | Lin Chong     |  NULL |  25 | M      |       4 |      NULL |   3 | Miejue Shitai |  25 | F      ||    17 | Lin Chong     |  NULL |  25 | M      |       4 |      NULL |   4 | Lin Chaoying  |  25 | F      ||    18 | Hua Rong      |  NULL |  23 | M      |       7 |      NULL |   1 | Song Jiang    |  25 | M      ||    18 | Hua Rong      |  NULL |  23 | M      |       7 |      NULL |   2 | Zhang Sanfeng |  25 | M      ||    18 | Hua Rong      |  NULL |  23 | M      |       7 |      NULL |   3 | Miejue Shitai |  25 | F      ||    18 | Hua Rong      |  NULL |  23 | M      |       7 |      NULL |   4 | Lin Chaoying  |  25 | F      ||    19 | Xue Baochai   |  NULL |  18 | F      |       6 |      NULL |   1 | Song Jiang    |  25 | M      ||    19 | Xue Baochai   |  NULL |  18 | F      |       6 |      NULL |   2 | Zhang Sanfeng |  25 | M      ||    19 | Xue Baochai   |  NULL |  18 | F      |       6 |      NULL |   3 | Miejue Shitai |  25 | F      ||    19 | Xue Baochai   |  NULL |  18 | F      |       6 |      NULL |   4 | Lin Chaoying  |  25 | F      ||    20 | Diao Chan     |  NULL |  19 | F      |       7 |      NULL |   1 | Song Jiang    |  25 | M      ||    20 | Diao Chan     |  NULL |  19 | F      |       7 |      NULL |   2 | Zhang Sanfeng |  25 | M      ||    20 | Diao Chan     |  NULL |  19 | F      |       7 |      NULL |   3 | Miejue Shitai |  25 | F      ||    20 | Diao Chan     |  NULL |  19 | F      |       7 |      NULL |   4 | Lin Chaoying  |  25 | F      ||    21 | Huang Yueying |  NULL |  22 | F      |       6 |      NULL |   1 | Song Jiang    |  25 | M      ||    21 | Huang Yueying |  NULL |  22 | F      |       6 |      NULL |   2 | Zhang Sanfeng |  25 | M      ||    21 | Huang Yueying |  NULL |  22 | F      |       6 |      NULL |   3 | Miejue Shitai |  25 | F      ||    21 | Huang Yueying |  NULL |  22 | F      |       6 |      NULL |   4 | Lin Chaoying  |  25 | F      ||    22 | Xiao Qiao     |  NULL |  20 | F      |       1 |      NULL |   1 | Song Jiang    |  25 | M      ||    22 | Xiao Qiao     |  NULL |  20 | F      |       1 |      NULL |   2 | Zhang Sanfeng |  25 | M      ||    22 | Xiao Qiao     |  NULL |  20 | F      |       1 |      NULL |   3 | Miejue Shitai |  25 | F      ||    22 | Xiao Qiao     |  NULL |  20 | F      |       1 |      NULL |   4 | Lin Chaoying  |  25 | F      ||    23 | Ma Chao       |  NULL |  23 | M      |       4 |      NULL |   1 | Song Jiang    |  25 | M      ||    23 | Ma Chao       |  NULL |  23 | M      |       4 |      NULL |   2 | Zhang Sanfeng |  25 | M      ||    23 | Ma Chao       |  NULL |  23 | M      |       4 |      NULL |   3 | Miejue Shitai |  25 | F      ||    23 | Ma Chao       |  NULL |  23 | M      |       4 |      NULL |   4 | Lin Chaoying  |  25 | F      ||    24 | Xu Xian       |  NULL |  27 | M      |    NULL |      NULL |   1 | Song Jiang    |  25 | M      ||    24 | Xu Xian       |  NULL |  27 | M      |    NULL |      NULL |   2 | Zhang Sanfeng |  25 | M      ||    24 | Xu Xian       |  NULL |  27 | M      |    NULL |      NULL |   3 | Miejue Shitai |  25 | F      ||    24 | Xu Xian       |  NULL |  27 | M      |    NULL |      NULL |   4 | Lin Chaoying  |  25 | F      ||    25 | Sun Dasheng   |  NULL | 100 | M      |    NULL |      NULL |   1 | Song Jiang    |  25 | M      ||    25 | Sun Dasheng   |  NULL | 100 | M      |    NULL |      NULL |   2 | Zhang Sanfeng |  25 | M      ||    25 | Sun Dasheng   |  NULL | 100 | M      |    NULL |      NULL |   3 | Miejue Shitai |  25 | F      ||    25 | Sun Dasheng   |  NULL | 100 | M      |    NULL |      NULL |   4 | Lin Chaoying  |  25 | F      ||    26 | xietingfeng   |  NULL |  23 | M      |       2 |         1 |   1 | Song Jiang    |  25 | M      ||    26 | xietingfeng   |  NULL |  23 | M      |       2 |         1 |   2 | Zhang Sanfeng |  25 | M      ||    26 | xietingfeng   |  NULL |  23 | M      |       2 |         1 |   3 | Miejue Shitai |  25 | F      ||    26 | xietingfeng   |  NULL |  23 | M      |       2 |         1 |   4 | Lin Chaoying  |  25 | F      ||    27 | liudehua      |  NULL |  18 | F      |       1 |      NULL |   1 | Song Jiang    |  25 | M      ||    27 | liudehua      |  NULL |  18 | F      |       1 |      NULL |   2 | Zhang Sanfeng |  25 | M      ||    27 | liudehua      |  NULL |  18 | F      |       1 |      NULL |   3 | Miejue Shitai |  25 | F      ||    27 | liudehua      |  NULL |  18 | F      |       1 |      NULL |   4 | Lin Chaoying  |  25 | F      ||    28 | mahuateng     |  NULL |  20 | M      |       3 |      NULL |   1 | Song Jiang    |  25 | M      ||    28 | mahuateng     |  NULL |  20 | M      |       3 |      NULL |   2 | Zhang Sanfeng |  25 | M      ||    28 | mahuateng     |  NULL |  20 | M      |       3 |      NULL |   3 | Miejue Shitai |  25 | F      ||    28 | mahuateng     |  NULL |  20 | M      |       3 |      NULL |   4 | Lin Chaoying  |  25 | F      ||    29 | wuyanzu       |  NULL |  19 | M      |       4 |      NULL |   1 | Song Jiang    |  25 | M      ||    29 | wuyanzu       |  NULL |  19 | M      |       4 |      NULL |   2 | Zhang Sanfeng |  25 | M      ||    29 | wuyanzu       |  NULL |  19 | M      |       4 |      NULL |   3 | Miejue Shitai |  25 | F      ||    29 | wuyanzu       |  NULL |  19 | M      |       4 |      NULL |   4 | Lin Chaoying  |  25 | F      ||    30 | wuzetian      |  NULL |  21 | F      |    NULL |      NULL |   1 | Song Jiang    |  25 | M      ||    30 | wuzetian      |  NULL |  21 | F      |    NULL |      NULL |   2 | Zhang Sanfeng |  25 | M      ||    30 | wuzetian      |  NULL |  21 | F      |    NULL |      NULL |   3 | Miejue Shitai |  25 | F      ||    30 | wuzetian      |  NULL |  21 | F      |    NULL |      NULL |   4 | Lin Chaoying  |  25 | F      ||    31 | Song Jiang    |  NULL |  18 | M      |      45 |      NULL |   1 | Song Jiang    |  25 | M      ||    31 | Song Jiang    |  NULL |  18 | M      |      45 |      NULL |   2 | Zhang Sanfeng |  25 | M      ||    31 | Song Jiang    |  NULL |  18 | M      |      45 |      NULL |   3 | Miejue Shitai |  25 | F      ||    31 | Song Jiang    |  NULL |  18 | M      |      45 |      NULL |   4 | Lin Chaoying  |  25 | F      ||    32 | Zhang Sanfeng |  NULL |  18 | M      |      94 |      NULL |   1 | Song Jiang    |  25 | M      ||    32 | Zhang Sanfeng |  NULL |  18 | M      |      94 |      NULL |   2 | Zhang Sanfeng |  25 | M      ||    32 | Zhang Sanfeng |  NULL |  18 | M      |      94 |      NULL |   3 | Miejue Shitai |  25 | F      ||    32 | Zhang Sanfeng |  NULL |  18 | M      |      94 |      NULL |   4 | Lin Chaoying  |  25 | F      ||    33 | Miejue Shitai |  NULL |  18 | F      |      77 |      NULL |   1 | Song Jiang    |  25 | M      ||    33 | Miejue Shitai |  NULL |  18 | F      |      77 |      NULL |   2 | Zhang Sanfeng |  25 | M      ||    33 | Miejue Shitai |  NULL |  18 | F      |      77 |      NULL |   3 | Miejue Shitai |  25 | F      ||    33 | Miejue Shitai |  NULL |  18 | F      |      77 |      NULL |   4 | Lin Chaoying  |  25 | F      ||    34 | Lin Chaoying  |  NULL |  18 | F      |      93 |      NULL |   1 | Song Jiang    |  25 | M      ||    34 | Lin Chaoying  |  NULL |  18 | F      |      93 |      NULL |   2 | Zhang Sanfeng |  25 | M      ||    34 | Lin Chaoying  |  NULL |  18 | F      |      93 |      NULL |   3 | Miejue Shitai |  25 | F      ||    34 | Lin Chaoying  |  NULL |  18 | F      |      93 |      NULL |   4 | Lin Chaoying  |  25 | F      ||    38 | abc           |  NULL |  20 | M      |    NULL |      NULL |   1 | Song Jiang    |  25 | M      ||    38 | abc           |  NULL |  20 | M      |    NULL |      NULL |   2 | Zhang Sanfeng |  25 | M      ||    38 | abc           |  NULL |  20 | M      |    NULL |      NULL |   3 | Miejue Shitai |  25 | F      ||    38 | abc           |  NULL |  20 | M      |    NULL |      NULL |   4 | Lin Chaoying  |  25 | F      |+-------+---------------+-------+-----+--------+---------+-----------+-----+---------------+-----+--------+140 rows in set (0.00 sec)第一张表       0  1  2  3    1  2  3  4第二张表       1  3  4  5    2  3  4  5交叉连接后结果：    0 1 2 3   1 3 4 5    0 1 2 3   2 3 4 5    1 2 3 4   1 3 4 5    1 2 3 4   2 3 4 5两张表换下位置不影响数据只是显示效果变了而已:    1 3 4 5   0 1 2 3    1 3 4 5   1 2 3 4    2 3 4 5   0 1 2 3    2 3 4 5   1 2 3 4MariaDB [hellodb]> select * from teachers , s1;  （这个命令也可以交叉连接但是比较老了推荐使用第一种）+-----+---------------+-----+--------+-------+---------------+-------+-----+--------+---------+-----------+| TID | Name          | Age | Gender | StuID | Name          | phone | Age | Gender | ClassID | TeacherID |+-----+---------------+-----+--------+-------+---------------+-------+-----+--------+---------+-----------+|   1 | Song Jiang    |  25 | M      |     1 | Shi Zhongyu   |  NULL |  22 | M      |       2 |         3 ||   2 | Zhang Sanfeng |  25 | M      |     1 | Shi Zhongyu   |  NULL |  22 | M      |       2 |         3 ||   3 | Miejue Shitai |  25 | F      |     1 | Shi Zhongyu   |  NULL |  22 | M      |       2 |         3 ||   4 | Lin Chaoying  |  25 | F      |     1 | Shi Zhongyu   |  NULL |  22 | M      |       2 |         3 ||   1 | Song Jiang    |  25 | M      |     2 | Shi Potian    |  NULL |  22 | M      |       1 |         7 ||   2 | Zhang Sanfeng |  25 | M      |     2 | Shi Potian    |  NULL |  22 | M      |       1 |         7 ||   3 | Miejue Shitai |  25 | F      |     2 | Shi Potian    |  NULL |  22 | M      |       1 |         7 ||   4 | Lin Chaoying  |  25 | F      |     2 | Shi Potian    |  NULL |  22 | M      |       1 |         7 ||   1 | Song Jiang    |  25 | M      |     3 | Xie Yanke     |  NULL |  53 | M      |       2 |        16 ||   2 | Zhang Sanfeng |  25 | M      |     3 | Xie Yanke     |  NULL |  53 | M      |       2 |        16 ||   3 | Miejue Shitai |  25 | F      |     3 | Xie Yanke     |  NULL |  53 | M      |       2 |        16 ||   4 | Lin Chaoying  |  25 | F      |     3 | Xie Yanke     |  NULL |  53 | M      |       2 |        16 |

挑出两张表的个别字段。

MariaDB [hellodb]> select name,age,gender from teachers cross join s1; (这里有两个字段是重复的name，age两个表都有)ERROR 1052 (23000): Column 'name' in field list is ambiguousMariaDB [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    |