mysql多表联合查询

最新推荐文章于 2025-05-02 18:23:07 发布
原创 最新推荐文章于 2025-05-02 18:23:07 发布 · 160 阅读 · 0 ·
本内容遵循CC 4.0 BY-SA版权协议
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
标签
#mysql #数据库 #sql
本文详细介绍了MySQL中交叉连接、内连接、左连接、右连接的查询方式，包括CROSS JOIN、INNER JOIN、LEFT JOIN、RIGHT JOIN的使用示例，并结合GROUP BY和GROUP_CONCAT()函数展示了如何进行数据聚合和分组查询。

1.交叉连接
#SELECT <字段名> FROM <表1> CROSS JOIN <表2> [WHERE子句];
#SELECT <字段名> FROM <表1>, <表2> [WHERE子句];
字段名：需要查询的字段名称。
<表1><表2>：需要交叉连接的表名。
WHERE 子句：用来设置交叉连接的查询条件
#创建school库 创建tb_students_info表

mysql> create database school;
 Query OK, 1 row affected (0.01 sec)
 ​
 mysql> use school;
 Database changed
 mysql> create table tb_students_info(id int not null primary key auto_increment,name varchar(50),age tinyint,sex varchar(10),height int,course_id int);
 Query OK, 0 rows affected (0.01 sec)
 mysql> desc tb_students_info;
 +-----------+-------------+------+-----+---------+----------------+
 | Field     | Type        | Null | Key | Default | Extra          |
 +-----------+-------------+------+-----+---------+----------------+
 | id        | int(11)     | NO   | PRI | NULL    | auto_increment |
 | name      | varchar(50) | YES  |     | NULL    |                |
 | age       | tinyint(4)  | YES  |     | NULL    |                |
 | sex       | varchar(10) | YES  |     | NULL    |                |
 | height    | int(11)     | YES  |     | NULL    |                |
 | course_id | int(11)     | YES  |     | NULL    |                |
 +-----------+-------------+------+-----+---------+----------------+
 6 rows in set (0.01 sec)

#插入表tb_students_info数据

mysql> insert tb_students_info(name,age,sex,height,course_id) values('Dany',25,'Male',160,1),('Green',23,'Ma,le',158,2),('Henry',23,'Female',185,1),('Jane',22,'Male',162,3),('Jim',24,'Female',175,2),('John',21,'Male',)172,4),('Lily',22,'Male',165,4),('Susan',23,'Male',170,5),('Thomas',22,'Female',178,5),('Tom',23,'Female',165,5);
 Query OK, 10 rows affected (0.01 sec)
 Records: 10  Duplicates: 0  Warnings: 0
 mysql> select * from tb_students_info;
 +----+--------+------+--------+--------+-----------+
 | id | name   | age  | sex    | height | course_id |
 +----+--------+------+--------+--------+-----------+
 |  1 | Dany   |   25 | Male   |    160 |         1 |
 |  2 | Green  |   23 | Male   |    158 |         2 |
 |  3 | Henry  |   23 | Female |    185 |         1 |
 |  4 | Jane   |   22 | Male   |    162 |         3 |
 |  5 | Jim    |   24 | Female |    175 |         2 |
 |  6 | John   |   21 | Male   |    172 |         4 |
 |  7 | Lily   |   22 | Male   |    165 |         4 |
 |  8 | Susan  |   23 | Male   |    170 |         5 |
 |  9 | Thomas |   22 | Female |    178 |         5 |
 | 10 | Tom    |   23 | Female |    165 |         5 |
 +----+--------+------+--------+--------+-----------+
 10 rows in set (0.00 sec)

#创建tb_course表
#插入tb_course表数据

mysql> create table tb_course(id int not null primary key auto_increment,course_name varchar(50));
 Query OK, 0 rows affected (0.00 sec)
mysql> insert tb_course(course_name) values('Java'),('Mysql'),('Python'),('Go'),('C++');
 Query OK, 5 rows affected (0.00 sec)
 Records: 5  Duplicates: 0  Warnings: 0
 ​
 mysql> select * from tb_crouse;
 ERROR 1146 (42S02): Table 'school.tb_crouse' doesn't exist
 mysql> select * from tb_course;
 +----+-------------+
 | id | course_name |
 +----+-------------+
 |  1 | Java        |
 |  2 | Mysql       |
 |  3 | Python      |
 |  4 | Go          |
 |  5 | C++         |
 +----+-------------+
 5 rows in set (0.00 sec)

#使用 CROSS JOIN 查询出两张表中的笛卡尔积 5*10 返回50条记录

 mysql> SELECT * FROM tb_course CROSS JOIN tb_students_info;
 +----+-------------+----+--------+------+--------+--------+-----------+
 | id | course_name | id | name   | age  | sex    | height | course_id |
 +----+-------------+----+--------+------+--------+--------+-----------+
 |  1 | Java        |  1 | Dany   |   25 | Male   |    160 |         1 |
 |  2 | Mysql       |  1 | Dany   |   25 | Male   |    160 |         1 |
 |  3 | Python      |  1 | Dany   |   25 | Male   |    160 |         1 |
 |  4 | Go          |  1 | Dany   |   25 | Male   |    160 |         1 |
 |  5 | C++         |  1 | Dany   |   25 | Male   |    160 |         1 |
 |  1 | Java        |  2 | Green  |   23 | Male   |    158 |         2 |
 |  2 | Mysql       |  2 | Green  |   23 | Male   |    158 |         2 |
 |  3 | Python      |  2 | Green  |   23 | Male   |    158 |         2 |
 |  4 | Go          |  2 | Green  |   23 | Male   |    158 |         2 |
 |  5 | C++         |  2 | Green  |   23 | Male   |    158 |         2 |
 |  1 | Java        |  3 | Henry  |   23 | Female |    185 |         1 |
 |  2 | Mysql       |  3 | Henry  |   23 | Female |    185 |         1 |
 |  3 | Python      |  3 | Henry  |   23 | Female |    185 |         1 |
 |  4 | Go          |  3 | Henry  |   23 | Female |    185 |         1 |
 |  5 | C++         |  3 | Henry  |   23 | Female |    185 |         1 |
 |  1 | Java        |  4 | Jane   |   22 | Male   |    162 |         3 |
 |  2 | Mysql       |  4 | Jane   |   22 | Male   |    162 |         3 |
 |  3 | Python      |  4 | Jane   |   22 | Male   |    162 |         3 |
 |  4 | Go          |  4 | Jane   |   22 | Male   |    162 |         3 |
 |  5 | C++         |  4 | Jane   |   22 | Male   |    162 |         3 |
 |  1 | Java        |  5 | Jim    |   24 | Female |    175 |         2 |
 |  2 | Mysql       |  5 | Jim    |   24 | Female |    175 |         2 |
 |  3 | Python      |  5 | Jim    |   24 | Female |    175 |         2 |
 |  4 | Go          |  5 | Jim    |   24 | Female |    175 |         2 |
 |  5 | C++         |  5 | Jim    |   24 | Female |    175 |         2 |
 |  1 | Java        |  6 | John   |   21 | Male   |    172 |         4 |
 |  2 | Mysql       |  6 | John   |   21 | Male   |    172 |         4 |
 |  3 | Python      |  6 | John   |   21 | Male   |    172 |         4 |
 |  4 | Go          |  6 | John   |   21 | Male   |    172 |         4 |
 |  5 | C++         |  6 | John   |   21 | Male   |    172 |         4 |
 |  1 | Java        |  7 | Lily   |   22 | Male   |    165 |         4 |
 |  2 | Mysql       |  7 | Lily   |   22 | Male   |    165 |         4 |
 |  3 | Python      |  7 | Lily   |   22 | Male   |    165 |         4 | 
|  4 | Go          |  7 | Lily   |   22 | Male   |    165 |         4 | 
|  5 | C++         |  7 | Lily   |   22 | Male   |    165 |         4 | 
|  1 | Java        |  8 | Susan  |   23 | Male   |    170 |         5 | 
|  2 | Mysql       |  8 | Susan  |   23 | Male   |    170 |         5 | 
|  3 | Python      |  8 | Susan  |   23 | Male   |    170 |         5 | 
|  4 | Go          |  8 | Susan  |   23 | Male   |    170 |         5 | 
|  5 | C++         |  8 | Susan  |   23 | Male   |    170 |         5 | 
|  1 | Java        |  9 | Thomas |   22 | Female |    178 |         5 | 
|  2 | Mysql       |  9 | Thomas |   22 | Female |    178 |         5 | 
|  3 | Python      |  9 | Thomas |   22 | Female |    178 |         5 | 
|  4 | Go          |  9 | Thomas |   22 | Female |    178 |         5 | 
|  5 | C++         |  9 | Thomas |   22 | Female |    178 |         5 | 
|  1 | Java        | 10 | Tom    |   23 | Female |    165 |         5 | 
|  2 | Mysql       | 10 | Tom    |   23 | Female |    165 |         5 | 
|  3 | Python      | 10 | Tom    |   23 | Female |    165 |         5 | 
|  4 | Go          | 10 | Tom    |   23 | Female |    165 |         5 | 
|  5 | C++         | 10 | Tom    |   23 | Female |    165 |         5 | 
+----+-------------+----+--------+------+--------+--------+-----------+ 
50 rows in set (0.01 sec)

#查询 tb_course 表中的 id 字段和 tb_students_info 表中的 course_id 字段相等的内容

mysql> select * from tb_course cross join tb_students_info where tb_students_info.course_id = tb_course.id; 
+----+-------------+----+--------+------+--------+--------+-----------+ 
| id | course_name | id | name   | age  | sex    | height | course_id | 
+----+-------------+----+--------+------+--------+--------+-----------+ 
|  1 | Java        |  1 | Dany   |   25 | Male   |    160 |         1 | 
|  2 | Mysql       |  2 | Green  |   23 | Male   |    158 |         2 | 
|  1 | Java        |  3 | Henry  |   23 | Female |    185 |         1 | 
|  3 | Python      |  4 | Jane   |   22 | Male   |    162 |         3 | 
|  2 | Mysql       |  5 | Jim    |   24 | Female |    175 |         2 | 
|  4 | Go          |  6 | John   |   21 | Male   |    172 |         4 | 
|  4 | Go          |  7 | Lily   |   22 | Male   |    165 |         4 | 
|  5 | C++         |  8 | Susan  |   23 | Male   |    170 |         5 | 
|  5 | C++         |  9 | Thomas |   22 | Female |    178 |         5 | 
|  5 | C++         | 10 | Tom    |   23 | Female |    165 |         5 | 
+----+-------------+----+--------+------+--------+--------+-----------+ 
10 rows in set (0.00 sec)

#更换位置查询结果相同

mysql> select * from tb_course cross join tb_students_info where tb_course.id = tb_students_info.course_id; 
+----+-------------+----+--------+------+--------+--------+-----------+ 
| id | course_name | id | name   | age  | sex    | height | course_id | 
+----+-------------+----+--------+------+--------+--------+-----------+ 
|  1 | Java        |  1 | Dany   |   25 | Male   |    160 |         1 | 
|  2 | Mysql       |  2 | Green  |   23 | Male   |    158 |         2 | 
|  1 | Java        |  3 | Henry  |   23 | Female |    185 |         1 | 
|  3 | Python      |  4 | Jane   |   22 | Male   |    162 |         3 | 
|  2 | Mysql       |  5 | Jim    |   24 | Female |    175 |         2 | 
|  4 | Go          |  6 | John   |   21 | Male   |    172 |         4 | 
|  4 | Go          |  7 | Lily   |   22 | Male   |    165 |         4 | 
|  5 | C++         |  8 | Susan  |   23 | Male   |    170 |         5 | 
|  5 | C++         |  9 | Thomas |   22 | Female |    178 |         5 | 
|  5 | C++         | 10 | Tom    |   23 | Female |    165 |         5 | 
+----+-------------+----+--------+------+--------+--------+-----------+ 
10 rows in set (0.00 sec) 
mysql> select * from tb_students_info cross join tb_course where tb_course.id = tb_students_info.course_id; 
+----+--------+------+--------+--------+-----------+----+-------------+ 
| id | name   | age  | sex    | height | course_id | id | course_name | 
+----+--------+------+--------+--------+-----------+----+-------------+ 
|  1 | Dany   |   25 | Male   |    160 |         1 |  1 | Java        | 
|  2 | Green  |   23 | Male   |    158 |         2 |  2 | Mysql       | 
|  3 | Henry  |   23 | Female |    185 |         1 |  1 | Java        | 
|  4 | Jane   |   22 | Male   |    162 |         3 |  3 | Python      | 
|  5 | Jim    |   24 | Female |    175 |         2 |  2 | Mysql       | 
|  6 | John   |   21 | Male   |    172 |         4 |  4 | Go          | 
|  7 | Lily   |   22 | Male   |    165 |         4 |  4 | Go          | 
|  8 | Susan  |   23 | Male   |    170 |         5 |  5 | C++         | 
|  9 | Thomas |   22 | Female |    178 |         5 |  5 | C++         | 
| 10 | Tom    |   23 | Female |    165 |         5 |  5 | C++         | 
+----+--------+------+--------+--------+-----------+----+-------------+ 
10 rows in set (0.00 sec)

#使用order by排序course_id

mysql> select * from tb_students_info cross join tb_course where tb_course.id = tb_students_info.course_id order by tb_course.id; 
+----+--------+------+--------+--------+-----------+----+-------------+ 
| id | name   | age  | sex    | height | course_id | id | course_name | 
+----+--------+------+--------+--------+-----------+----+-------------+ 
|  1 | Dany   |   25 | Male   |    160 |         1 |  1 | Java        | 
|  3 | Henry  |   23 | Female |    185 |         1 |  1 | Java        | 
|  5 | Jim    |   24 | Female |    175 |         2 |  2 | Mysql       | 
|  2 | Green  |   23 | Male   |    158 |         2 |  2 | Mysql       | 
|  4 | Jane   |   22 | Male   |    162 |         3 |  3 | Python      | 
|  7 | Lily   |   22 | Male   |    165 |         4 |  4 | Go          | 
|  6 | John   |   21 | Male   |    172 |         4 |  4 | Go          | 
|  9 | Thomas |   22 | Female |    178 |         5 |  5 | C++         | 
|  8 | Susan  |   23 | Male   |    170 |         5 |  5 | C++         | 
| 10 | Tom    |   23 | Female |    165 |         5 |  5 | C++         | 
+----+--------+------+--------+--------+-----------+----+-------------+ 
10 rows in set (0.00 sec)

#查询学习某个语言的人名的单独列出

mysql> select tb_students_info.name from tb_students_info cross join tb_course where tb_course.id = tb_students_info.course_id and tb_course.course_name = 'Go'; 
+------+ 
| name | 
+------+ 
| John | 
| Lily | 
+------+ 
2 rows in set (0.00 sec)

2.内连接
#SELECT <字段名> FROM <表1> INNER JOIN <表2> [ON子句];
字段名：需要查询的字段名称。
<表1><表2>：需要内连接的表名。
INNER JOIN ：内连接中可以省略 INNER 关键字，只用关键字 JOIN。
ON 子句：用来设置内连接的连接条件。
#tb_students_info别名为s tb_course别名为c 取s的name和c的course_name值 条件是s的course_id = c的id;

mysql> select s.name,c.course_name from tb_students_info as s inner join tb_course as c on s.course_id = c.id;
 +--------+-------------+
 | name   | course_name |
 +--------+-------------+
 | Dany   | Java        |
 | Green  | Mysql       |
 | Henry  | Java        |
 | Jane   | Python      |
 | Jim    | Mysql       |
 | John   | Go          |
 | Lily   | Go          |
 | Susan  | C++         |
 | Thomas | C++         |
 | Tom    | C++         |
 +--------+-------------+
 10 rows in set (0.00 sec)

#按照course_name来排序

mysql> select s.name,c.course_name from tb_students_info as s inner join tb_course as c on s.course_id = c.id order by c.course_name;
 +--------+-------------+
 | name   | course_name |
 +--------+-------------+
 | Thomas | C++         |
 | Tom    | C++         |
 | Susan  | C++         |
 | John   | Go          |
 | Lily   | Go          |
 | Dany   | Java        |
 | Henry  | Java        |
 | Green  | Mysql       |
 | Jim    | Mysql       |
 | Jane   | Python      |
 +--------+-------------+
 10 rows in set (0.00 sec)

3.左连接
左外连接又称为左连接，使用 LEFT OUTER JOIN 关键字连接两个表，并使用 ON 子句来设置连接条件。

#SELECT <字段名> FROM <表1> LEFT OUTER JOIN <表2> <ON子句>;
字段名：需要查询的字段名称。
<表1><表2>：需要左连接的表名。
LEFT OUTER JOIN：左连接中可以省略 OUTER 关键字，只使用关键字 LEFT JOIN。
ON 子句：用来设置左连接的连接条件，不能省略。
#在tb_course表插入Html

mysql> insert tb_course(course_name) value('Html');
 Query OK, 1 row affected (0.00 sec)

#以tb_students_info表为基础查询tb_students_info表的course_id 匹配 tb_course表的id 没有显示6号Html 因为基础表中没有6

mysql> select * from tb_students_info left join tb_course on tb_students_info.course_id = tb_course.id;
 +----+--------+------+--------+--------+-----------+------+-------------+
 | id | name   | age  | sex    | height | course_id | id   | course_name |
 +----+--------+------+--------+--------+-----------+------+-------------+
 |  1 | Dany   |   25 | Male   |    160 |         1 |    1 | Java        |
 |  2 | Green  |   23 | Male   |    158 |         2 |    2 | Mysql       |
 |  3 | Henry  |   23 | Female |    185 |         1 |    1 | Java        |
 |  4 | Jane   |   22 | Male   |    162 |         3 |    3 | Python      |
 |  5 | Jim    |   24 | Female |    175 |         2 |    2 | Mysql       |
 |  6 | John   |   21 | Male   |    172 |         4 |    4 | Go          |
 |  7 | Lily   |   22 | Male   |    165 |         4 |    4 | Go          |
 |  8 | Susan  |   23 | Male   |    170 |         5 |    5 | C++         |
 |  9 | Thomas |   22 | Female |    178 |         5 |    5 | C++         |
 | 10 | Tom    |   23 | Female |    165 |         5 |    5 | C++         |
 +----+--------+------+--------+--------+-----------+------+-------------+
 10 rows in set (0.00 sec)

#添加Liming

mysql> insert tb_students_info(name,age,sex,height,course_id) value('Liming',22,'Male',180,7);
 Query OK, 1 row affected (0.00 sec)
 mysql> select * from tb_students_info ;
 +----+--------+------+--------+--------+-----------+
 | id | name   | age  | sex    | height | course_id |
 +----+--------+------+--------+--------+-----------+
 |  1 | Dany   |   25 | Male   |    160 |         1 |
 |  2 | Green  |   23 | Male   |    158 |         2 |
 |  3 | Henry  |   23 | Female |    185 |         1 |
 |  4 | Jane   |   22 | Male   |    162 |         3 |
 |  5 | Jim    |   24 | Female |    175 |         2 |
 |  6 | John   |   21 | Male   |    172 |         4 |
 |  7 | Lily   |   22 | Male   |    165 |         4 |
 |  8 | Susan  |   23 | Male   |    170 |         5 |
 |  9 | Thomas |   22 | Female |    178 |         5 |
 | 10 | Tom    |   23 | Female |    165 |         5 |
 | 11 | Liming |   22 | Male   |    180 |         7 |
 +----+--------+------+--------+--------+-----------+
 11 rows in set (0.00 sec)

#再次以tb_students_info表为基础查询tb_students_info表的course_id 匹配 tb_course表的id 因为没有 值会为空null

mysql> select * from tb_students_info left join tb_course on tb_students_info.course_id = tb_course.id;
 +----+--------+------+--------+--------+-----------+------+-------------+
 | id | name   | age  | sex    | height | course_id | id   | course_name |
 +----+--------+------+--------+--------+-----------+------+-------------+
 |  1 | Dany   |   25 | Male   |    160 |         1 |    1 | Java        |
 |  2 | Green  |   23 | Male   |    158 |         2 |    2 | Mysql       |
 |  3 | Henry  |   23 | Female |    185 |         1 |    1 | Java        |
 |  4 | Jane   |   22 | Male   |    162 |         3 |    3 | Python      |
 |  5 | Jim    |   24 | Female |    175 |         2 |    2 | Mysql       |
 |  6 | John   |   21 | Male   |    172 |         4 |    4 | Go          |
 |  7 | Lily   |   22 | Male   |    165 |         4 |    4 | Go          |
 |  8 | Susan  |   23 | Male   |    170 |         5 |    5 | C++         |
 |  9 | Thomas |   22 | Female |    178 |         5 |    5 | C++         |
 | 10 | Tom    |   23 | Female |    165 |         5 |    5 | C++         |
 | 11 | Liming |   22 | Male   |    180 |         7 | NULL | NULL        |
 +----+--------+------+--------+--------+-----------+------+-------------+
 11 rows in set (0.00 sec)

#s为tb_students_info表 c为tb_course表 以s表为基础表查询course_id匹配c表的id c表没有对应值 所有为空null值

mysql> select s.name,c.course_name from tb_students_info s left join tb_course c on s.course_id=c.id;
 +--------+-------------+
 | name   | course_name |
 +--------+-------------+
 | Dany   | Java        |
 | Green  | Mysql       |
 | Henry  | Java        |
 | Jane   | Python      |
 | Jim    | Mysql       |
 | John   | Go          |
 | Lily   | Go          |
 | Susan  | C++         |
 | Thomas | C++         |
 | Tom    | C++         |
 | Liming | NULL        |
 +--------+-------------+
 11 rows in set (0.00 sec)

4.右连接
右外连接又称为右连接，右连接是左连接的反向连接。使用 RIGHT OUTER JOIN 关键字连接两个表，并使用 ON 子句来设置连接条件。

#SELECT <字段名> FROM <表1> RIGHT OUTER JOIN <表2> <ON子句>;
字段名：需要查询的字段名称。
<表1><表2>：需要右连接的表名。
RIGHT OUTER JOIN：右连接中可以省略 OUTER 关键字，只使用关键字 RIGHT JOIN。
ON 子句：用来设置右连接的连接条件，不能省略。
#s为tb_students_info表 c为tb_course表 以c表为基础表查询id匹配s表的course_id c表没有对应值 所有为空null值

mysql> select s.name,c.course_name from tb_students_info s right join tb_course c on s.course_id=c.id;
 +--------+-------------+
 | name   | course_name |
 +--------+-------------+
 | Dany   | Java        |
 | Green  | Mysql       |
 | Henry  | Java        |
 | Jane   | Python      |
 | Jim    | Mysql       |
 | John   | Go          |
 | Lily   | Go          |
 | Susan  | C++         |
 | Thomas | C++         |
 | Tom    | C++         |
 | NULL   | Html        |
 +--------+-------------+
 11 rows in set (0.00 sec)

5.GROUP BY单独使用
单独使用 GROUP BY 关键字时，查询结果会只显示每个分组的第一条记录。

mysql> SELECT name,sex FROM tb_students_info GROUP BY sex;
 +-------+--------+
 | name  | sex    |
 +-------+--------+
 | Henry | Female |
 | Dany  | Male   |
 +-------+--------+
 2 rows in set (0.00 sec)

6.GROUP BY 与 GROUP_CONCAT()
GROUP BY 关键字可以和 GROUP_CONCAT() 函数一起使用。GROUP_CONCAT() 函数会把每个分组的字段值都显示出来。

GROUP BY 关键字可以和 GROUP_CONCAT() 函数一起使用。GROUP_CONCAT() 函数会把每个分组的字段值都显示出来。
#把性别sex男，女的名字都列出来

mysql> select sex, group_concat(name) from tb_students_info group by sex;
 +--------+----------------------------------------+
 | sex    | group_concat(name)                     |
 +--------+----------------------------------------+
 | Female | Henry,Jim,Thomas,Tom                   |
 | Male   | Dany,Green,Jane,John,Lily,Susan,Liming |
 +--------+----------------------------------------+

#输入别名names就会显示names

mysql> select sex, group_concat(name) names from tb_students_info group by sex;
 +--------+----------------------------------------+
 | sex    | names                                  |
 +--------+----------------------------------------+
 | Female | Henry,Jim,Thomas,Tom                   |
 | Male   | Dany,Green,Jane,John,Lily,Susan,Liming |
 +--------+----------------------------------------+
 2 rows in set (0.00 sec)

#根据 tb_students_info 表中的 age 和 sex 字段进行分组查询

mysql> select age,sex,group_concat(name) names from tb_students_info group by age,sex;
 +------+--------+------------------+
 | age  | sex    | names            |
 +------+--------+------------------+
 |   21 | Male   | John             |
 |   22 | Female | Thomas           |
 |   22 | Male   | Jane,Lily,Liming |
 |   23 | Female | Henry,Tom        |
 |   23 | Male   | Green,Susan      |
 |   24 | Female | Jim              |
 |   25 | Male   | Dany             |
 +------+--------+------------------+
 7 rows in set (0.00 sec)
云卷山
关注
MySQL中的多表联合查询
weixin_44846862的博客
08-02 1125
多表联合查询的基本步骤： select 要查询的目标（可以是要查询的列，也可以是聚合函数） from 要查询的目标来自于哪个表 where 查询的条件 group by 分组的依据（分组的依据必须是查询的目标，或者是聚合函数） having 分组后要进行的限制条件 order by 排序（默认的是升序：asc，降序：desc） 现在有两个表，用来进行多表联合查询： 员工表 /* Navicat MySQL Data Transfer Source Server
MySQL联合查询（多表查询）
热门推荐
weixin_48891146的博客
03-02 4万+
1、内连接 select *from 表1 [ inner | cross ] join 表2 [ on过滤条件 ] [ where查询条件 ] ； [ inner | cross ]: join 内连接关键字(必须要有)； on从语法上可省略，但如果省略它，将查询的是多表的笛卡尔积； 1.select *from 表1 join 表2 [ on过滤条件 ] [ where查询条件 ] ； 2.select *from 表1，表2 [ where查询条件 ] ； 3.select *fro
MYSQL多表连查
Java_lookmi的博客
07-05 2953
在 SQL92 中采用（+）代表从表所在的位置。即左或右外连接中，(+) 表示哪个是从表。 Oracle 对 SQL92 支持较好，而 MySQL 则不支持 SQL92 的外连接。
MySQL的多表关联查询
星霖的博客
11-07 1万+
MySQL学习笔记
多表联合查询
kx0822的博客
06-29 6804
mysql 多表联合查询
mysql--多表查询
2301_76890677的博客
11-03 4534
作用：合并结果集就是把两个select语句的查询结果合并到一起！合并结果集有两种方式：UNION：合并并去除重复记录，例如：SELECT * FROM t1UNIONUNION ALL：合并但不去除重复记录，例如：SELECT * FROM t1UNION ALL。
mysql多表联合查询 去重_MySQL多表联合查询
weixin_39765057的博客
02-08 3964
MySQL这方面的资料比较少，手边的项目用到了多表的联合查询，干脆备忘下来。select a.*,b.*,c.* from aINNER JOIN b ON a.cid=b.cidINNER JOIN c ON c.cid=a.cid where a.cid=2 and a.id =3SELECT e_link.link_id, e_link.l_name, e_link.url,...
【MySQL】数据库多表操作通关教程(外键约束、多表联合查询)
黄小黄的博客
05-26 3728
本文全面总结了MySQL中的多表关系、外键约束以及多表联合查询相关知识。通过例子细致总结了交叉联合查询、内连接查询、左右全外连接查询、子查询、子查询的关键字以及自关联查询，适合收藏复习使用。...
MYSQL8多表联合查询
cnzzs的博客
10-28 1028
MYSQL8多表联合查询多表联合查询常用连接查询多表查询--数据准备交叉连接查询内连接查询：隐式内连接显示内连接内连接示例外连接：左外连接右外连接:外连接使用using子句相互关联子查询WITH AS子句满外连接不加`all`会去重`UNION all`数据会重复自关联查询进行关联查询自关联查询示例：多表联合查询通常情况下...
mysql中多表联合查询怎样加索引
weixin_41518094的博客
08-06 976
在 MySQL 中使用多表联合查询加索引 引言 在数据库开发中，性能优化是非常重要的一环。多表联合查询常常是造成性能问题的原因之一，因此合理地使用索引是非常必要的。本篇文章将逐步引导大家如何在 MySQL 中为多表联合查询添加索引，以提高查询性能。 流程概述 在进行多表联合查询时，我们可以按照以下步骤来添加索引： 步...
MySQL多表连接查询
weixin_63946209的博客
05-11 1751
多表连接查询实际上是通过各个表之间的共同列的关联性来查询数据的，它是关系数据库查询最重要的特征。#一、多表连接查询（1）内连接查询内连接查询是最典型、最常用的连接查询，它是根据表中的共同列来进行匹配。特别是两个表中存在主外键关系时通常会使用内连接查询。（2）外连接查询外连接查询至少返回一个表中的所有记录，根据匹配条件有选择性的返回另一张表的记录。外连接可以是在左外连接、右外连接。内连接和外连接都是用来联合两个表的查询结果，但是它们之间的区别在于内连接只返回满足条件的行，而外连接则会返回所有的行。
MySQL：联合查询
最新发布
zm3rttqs9f的博客
05-02 2600
联合查询
MySQL多表联合查询
mushuangpanny的博客
06-30 5728
2. 交叉连接(CROSS JOIN) 2.1 笛卡尔积 交叉连接(CROSS JOIN)：有两种，显式的和隐式的2种，一般用来返回连接表的笛卡尔积。 笛卡尔积（Cartesian product）是指两个集合 X 和 Y 的乘积。 A×B 和 B×A 的结果就叫做两个集合的笛卡尔积。两个集合相乘，不满足交换率，即 A×B≠B×A。A 集合和 B 集合的笛卡尔积是 A 集合的元素个数 × B 集合的元素个数。多表查询遵循的算法就是以上提到的笛卡尔积，表与表之间的连接可以看成是在做乘法运算 字段
【MySQL】多表查询(JOIN / UNION)
RiceVan的博客
02-07 1万+
结果包括employees表的全部行，employees表中的Grant没有department_id，departments表中无对应数值，仍显示在结果中（NULL）。结果包括departments表的全部行，employees表中无对应department_id，仍显示在结果中（NULL）。LEFT / RIGHT OUTER JOIN 表名 ON 条件。INNER JOIN 表名 ON 条件。
MySQL-联合查询
2301_80418043的博客
09-29 2681
在数据设计时由于范式的要求，数据被拆分到多个表中，那么要查询⼀个条数据的完整信息，就要从多个表中获取数据，如下图所⽰：要获取学⽣的基本信息和班级信息就要从学⽣表和班级表中获取，这时就需要使⽤联合查询，这⾥的联合指的是多个表的组合。
MYSQL多表查询
weixin_42894555的博客
04-13 4359
多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号”进行关联。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值