MySQL多表查询

最新推荐文章于 2025-07-27 23:30:00 发布

玖语巴黎

最新推荐文章于 2025-07-27 23:30:00 发布

阅读量918

点赞数 1

CC 4.0 BY-SA版权

分类专栏：数据库文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/jiu_yu/article/details/130372504

数据库专栏收录该内容

4 篇文章

订阅专栏

1.多表连接的基本

如果将多张表直接拼接在一起查询，MySQL会拼接为一张笛卡尔集。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BqfLIQ6Y-1682422960984)(0716_随堂笔记.assets/image-20200716102137159.png)]

拼接两张数据表使用的关键字是 inner join

语法：

SELECT * FROM table_name INNER JOIN join_table;

示例：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2loDMoxH-1682422960985)(0716_随堂笔记.assets/image-20200716102518432.png)]

mysql> select * from commodity inner join commoditytype;
+------+------------------------+----------+--------+-----------+------------+-------+-------+---------+
| c_id | c_name                 | c_madein | c_type | c_inprice | c_outprice | c_num | ct_id | ct_name |
+------+------------------------+----------+--------+-----------+------------+-------+-------+---------+
|    1 | 变形金刚-擎天柱        | 中国     |      1 |        20 |         50 |    60 |     1 | 玩具    |
|    1 | 变形金刚-擎天柱        | 中国     |      1 |        20 |         50 |    60 |     2 | 文具    |
|    1 | 变形金刚-擎天柱        | 中国     |      1 |        20 |         50 |    60 |     3 | 书籍    |
|    2 | 变形金刚-霸天虎        | 中国     |      1 |        20 |         45 |    50 |     1 | 玩具    |
|    2 | 变形金刚-霸天虎        | 中国     |      1 |        20 |         45 |    50 |     2 | 文具    |
|   59 | 三国演义               | 中国     |      3 |        20 |         37 |    37 |     2 | 文具    |
|   59 | 三国演义               | 中国     |      3 |        20 |         37 |    37 |     3 | 书籍    |
|   60 | 红楼梦                 | 中国     |      3 |        14 |         16 |    15 |     1 | 玩具    |
|   60 | 红楼梦                 | 中国     |      3 |        14 |         16 |    15 |     2 | 文具    |
|   60 | 红楼梦                 | 中国     |      3 |        14 |         16 |    15 |     3 | 书籍    |
+------+------------------------+----------+--------+-----------+------------+-------+-------+---------+
177 rows in set (0.10 sec)

每一条数据和其他所有数据都拼接一次的原因是MySQL不知道c_type=ct_id

2.内连接

上面的拼接形式为内连接，除了inner join关键字还需要使用on关键字来指定拼接条件！

语法：

SELECT * FROM table_name INNER JOIN join_table
ON table_name.field=join_table.field;

示例：

mysql> select * from commodity inner join commoditytype
    -> on c_type=ct_id;
+------+------------------------+----------+--------+-----------+------------+-------+-------+---------+
| c_id | c_name                 | c_madein | c_type | c_inprice | c_outprice | c_num | ct_id | ct_name |
+------+------------------------+----------+--------+-----------+------------+-------+-------+---------+
|    1 | 变形金刚-擎天柱        | 中国     |      1 |        20 |         50 |    60 |     1 | 玩具    |
|    2 | 变形金刚-霸天虎        | 中国     |      1 |        20 |         45 |    50 |     1 | 玩具    |
|   59 | 三国演义               | 中国     |      3 |        20 |         37 |    37 |     3 | 书籍    |
|   60 | 红楼梦                 | 中国     |      3 |        14 |         16 |    15 |     3 | 书籍    |
+------+------------------------+----------+--------+-----------+------------+-------+-------+---------+
59 rows in set (0.00 sec)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Wa88uIvc-1682422960986)(0716_随堂笔记.assets/image-20200716103215265.png)]

表的输出顺序由拼接顺序来决定

mysql> select c_name,c_inprice,ct_name
    -> from commodity inner join commoditytype
    -> on c_type=ct_id;
+------------------------+-----------+---------+
| c_name                 | c_inprice | ct_name |
+------------------------+-----------+---------+
| 变形金刚-擎天柱        |        20 | 玩具    |
| 变形金刚-霸天虎        |        20 | 玩具    |
| 变形金刚-威震天        |       120 | 玩具    |
| 魔仙玩偶1              |         6 | 玩具    |
| 名家演讲赏析           |        22 | 书籍    |
| 三国演义               |        20 | 书籍    |
| 红楼梦                 |        14 | 书籍    |
+------------------------+-----------+---------+
59 rows in set (0.00 sec)

3.外连接查询

外连接查询分2种，左连接和右连接。

语法：

SELECT * 
FROM table_name LEFT|RIGHT JOIN join_table 
ON join_condition;

左连接查询和右连接查询的区别是，以执行语中的哪个表为主表。

a left join b -> a是主表 b是副表 <- b right join a

a right join b -> b是主表 a是副表 <- b left join a

所谓主表就是以主表为准，主表中有的数据就显示，主表中没有的数据就算副表中有也不显示。左右连接作用是一样一样的仅仅是逻辑相反。

mysql> # 以商品表为主表
mysql> select * from commodity left join commoditytype
    -> on c_type=ct_id;
+------+------------------------+----------+--------+-----------+------------+-------+-------+---------+
| c_id | c_name                 | c_madein | c_type | c_inprice | c_outprice | c_num | ct_id | ct_name |
+------+------------------------+----------+--------+-----------+------------+-------+-------+---------+
|    1 | 变形金刚-擎天柱        | 中国     |      1 |        20 |         50 |    60 |     1 | 玩具    |
|   59 | 三国演义               | 中国     |      3 |        20 |         37 |    37 |     3 | 书籍    |
|   60 | 红楼梦                 | 中国     |      3 |        14 |         16 |    15 |     3 | 书籍    |
+------+------------------------+----------+--------+-----------+------------+-------+-------+---------+
59 rows in set (0.00 sec)

mysql> # 我们以商品类型表为主表
mysql> select * from commoditytype left join commodity
    -> on c_type=ct_id;
+-------+---------+------+------------------------+----------+--------+-----------+------------+-------+
| ct_id | ct_name | c_id | c_name                 | c_madein | c_type | c_inprice | c_outprice | c_num |
+-------+---------+------+------------------------+----------+--------+-----------+------------+-------+
|     1 | 玩具    |    1 | 变形金刚-擎天柱        | 中国     |      1 |        20 |         50 |    60 |
|     1 | 玩具    |    2 | 变形金刚-霸天虎        | 中国     |      1 |        20 |         45 |    50 |
|     3 | 书籍    |   58 | 名家演讲赏析           | 中国     |      3 |        22 |         50 |     4 |
|     3 | 书籍    |   59 | 三国演义               | 中国     |      3 |        20 |         37 |    37 |
|     3 | 书籍    |   60 | 红楼梦                 | 中国     |      3 |        14 |         16 |    15 |
|     4 | 服装    | NULL | NULL                   | NULL     |   NULL |      NULL |       NULL |  NULL |
+-------+---------+------+------------------------+----------+--------+-----------+------------+-------+
60 rows in set (0.00 sec)

主表有就显示，副表没有就以null填充。主表没有，就算副表有也不显示

4.内连接查询和外连接查询（左/右）的区别

一张图看懂区别

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FBhh3Y8k-1682422960986)(0716_随堂笔记.assets/image-20200716105429589.png)]

5.连接查询示例

-- 查询所有的玩具商品信息
select c_name,c_type,ct_name from commodity inner join commoditytype on c_type=ct_id where ct_name='玩具';
-- 查询不是玩具的商品信息
select c_name,c_type,ct_name from commodity inner join commoditytype on c_type=ct_id where ct_name<>'玩具';

6.子查询

子查询的意义在于，使用查询结果作为查询条件

-- 查询所有的玩具商品信息 c_type=? <- ct_id
select ct_id from commoditytype where ct_name='玩具';  -- 1
select c_name from commodity where c_type=1;  
-- 将上面的2句查询语句并为1句
select c_name from commodity where c_type=(select ct_id from commoditytype where ct_name='玩具');

对于上面的子查询，我们能不能举一反三？

-- 查询进价比商品id为1的商品进价高的所有商品信息
select c_inprice from commodity where c_id=1;  -- 20
select c_name,c_inprice from commodity where c_inprice>20;
-- 合并上面2句SQL
select c_name,c_inprice from commodity where c_inprice>(select c_inprice from commodity where c_id=1);

-- 玩具商品中比玩具平均售价还低的玩具商品信息
select ct_id from commoditytype where ct_name='玩具';  -- 1
select avg(c_outprice) from commodity where c_type=1;  -- 289.5789
-- 合并上面的SQL语句 嵌套子查询
select c_name,c_outprice from commodity 
where c_type=(select ct_id from commoditytype where ct_name='玩具') and c_outprice<(select avg(c_outprice) from commodity where c_type=(select ct_id from commoditytype where ct_name='玩具')) and c_outprice is not null;

复杂查询结构

mysql> select tb.t,tb.a  from (select c_type as t,avg(c_outprice) as a  from commodity group by c_type) as tb where tb.t=(select ct_id from commoditytype where ct_name='玩具');
+---+----------+
| t | a        |
+---+----------+
| 1 | 289.5789 |
+---+----------+
1 row in set (0.00 sec)

上面的子查询使用了关系运算符 要求子查询的语句返回的是单行单列的值

单列多行的

-- 查询出所有的玩具或文具商品
select ct_id from commoditytype where ct_name='玩具' or ct_name='文具';
select c_name from commodity where c_type in (1,2);
-- 合并上面的SQL语句
select c_name from commodity where c_type in (select ct_id from commoditytype where ct_name='玩具' or ct_name='文具');