MySQL | 数据查询语言DQL数据连接语法及实例

select list
from left_tab join_type right_tab
on join_condition;
1.
2.
3.

#数学中
A = {1,2,3} B = {4,5}
AXB = {<1,4>,<1,5>,<2,4>,<2,5>,<3,4>,<3,5>}
#数据库中
表就是一个集合,每一行就是一个元素
A表:
a1 a2
1 1
1 2
1 3
B表:
b1 b2
2 2
2 3
2 4
a1 a2 b1 b2
1 1 2 2
1 1 2 3
1 1 2 4
1 2 2 2
1 2 2 3
1 2 2 4
1 3 2 2
1 3 2 3
1 3 2 4
left_tab: m rows x cols
right_tab: n rows y cols
left_tab*right_tab:
m*n rows x+y cols
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.

select list
from left_tab cross join right_tab;
1.
2.

#连接条件
on A.a2 = B.b2 #tab_name.col_name
#交叉连接返回的结果
a1 a2 b1 b2
1 1 2 2
1 1 2 3
1 1 2 4
1 2 2 2
1 2 2 3
1 2 2 4
1 3 2 2
1 3 2 3
1 3 2 4
#内连接返回的结果
a1 a2 b1 b2
1 2 2 2
1 3 2 3
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.

select list
from left_tab inner join right_tab
on join_condition;
1.
2.
3.

#连接条件
on A.a2 = B.b2 #tab_name.col_name
#内连接返回的结果
a1 a2 b1 b2
1 2 2 2
1 3 2 3
#左外连接返回的结果
a1 a2 b1 b2
1 2 2 2
1 3 2 3
1 1 null null
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

#连接条件
on A.a2 = B.b2 #tab_name.col_name
#内连接返回的结果
a1 a2 b1 b2
1 2 2 2
1 3 2 3
#右外连接返回的结果
a1 a2 b1 b2
1 2 2 2
1 3 2 3
null null 2 4
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

select list
from left_tab left|right outer join right_tab
on join_condition;
1.
2.
3.

#查询示例
SELECT * FROM a;
SELECT * FROM b;
SELECT * FROM a CROSS JOIN b;
SELECT * FROM a INNER JOIN b ON a.a2 = b.b2;
SELECT * FROM a LEFT OUTER JOIN b ON a.a2 = b.b2;
SELECT * FROM a RIGHT OUTER JOIN b ON a.a2 = b.b2;
1.
2.
3.
4.
5.
6.
7.

select list
from left_tab inner join right_tab
on join_condition
where search_condition
order by sort_columns;
1.
2.
3.
4.
5.

#哪些商品被用户购买过?
#思路
1.确定表
数据来源于哪些表中,或需要将哪些表结合起来查询
SELECT * FROM ecs_goods;
SELECT * FROM ecs_order_goods;
2.确定连接条件
找出左表与右表中有逻辑关系的列,将两个表中的列值进行比较来描述连接条件
3.确定连接类型
若仅返回满足连接条件的行则使用内连接;否则使用外连接
SELECT DISTINCT ecs_goods.goods_id, ecs_goods.goods_name, ecs_goods.shop_price
FROM ecs_goods INNER JOIN ecs_order_goods
ON ecs_goods.goods_id = ecs_order_goods.goods_id;
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.

select list from tab_name alias_name ... #表名 别名
select list from tab_name as alias_name ... #表名 AS 别名
1.
2.

SELECT DISTINCT g.goods_id, g.goods_name, g.shop_price
#此处不能写成 ecs_goods.goods_id
FROM ecs_goods AS g INNER JOIN ecs_order_goods AS og
ON g.goods_id = og.goods_id;
#此处不能写成 ecs_goods.goods_id
1.
2.
3.
4.
5.

#列出商品表中商品类型编号对应的类型名称
SELECT * FROM ecs_goods;
SELECT * FROM ecs_goods_types;
SELECT g.goods_id,g.goods_name,gt.cat_name,shop_price
FROM ecs_goods AS g INNER JOIN ecs_goods_type AS gt
ON g.goods_type = gt.cat_id;
1.
2.
3.
4.
5.
6.

#列出哪些用户购买了哪些商品,每一次购买该商品的花费是多少?
SELECT * FROM ecs_users;
SELECT * FROM ecs_order_info;
SELECT * FROM ecs_order_goods;
1.
2.
3.
4.

SELECT u.user_id,u.user_name,og.order_id,og.goods_name,
og.goods_price*og.goods_number amount
FROM (ecs_order_goods og INNER JOIN ecs_order_info oi
ON og.order_id = oi.order_id) INNER JOIN ecs_users u
ON oi.user_id = u.user_id
ORDER BY u.user_id,og.order_id;
假设有三个表:t1,t2,t3
(t1,t2),t3
t1,(t2,t3)
转换成连接语法的方法:
将","改为连接类型,在右表(连接类型后的表)后加上连接的条件
select list
from (t1 inner join t2 on jc1) inner join t3 on jc2;
select list
from t1 inner join (t1 inner join t3 on jc1) on jc2;
四个表: t1,t2,t3,t4
((t1,t2),t3),t4
(t1,t2),(t3,t4)
t1,((t2,t3),t4)
t1,(t2,(t3,t4))
select list
from ((t1 inner join t2 on jc1) inner join t3 on jc2) inner join t4 on jc3;
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.

select list
from left_tab left|right outer join right_tab
on join_condition
[where keyword is null] #过滤满足条件的数据,保留不满足条件的数据
order by sort_columns;
1.
2.
3.
4.
5.

#哪些商品没有被客户购买过?
SELECT * FROM ecs_goods;
SELECT * FROM ecs_order_goods;
SELECT DISTINCT g.goods_id, g.goods_name, g.shop_price,og.goods_id
FROM ecs_goods g LEFT OUTER JOIN ecs_order_goods og
ON g.goods_id = og.goods_id
WHERE og.goods_id IS NULL;
#商品类型表中哪些商品类型没有出现在商品表中
SELECT gt.*
FROM ecs_goods AS g RIGHT OUTER JOIN ecs_goods_type AS gt
ON g.goods_type = gt.cat_id
WHERE g.goods_type IS NULL;
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

#找出没有购买过诺基亚E66商品的客户信息
SELECT * FROM ecs_users;
SELECT * FROM ecs_order_info;
SELECT * FROM ecs_order_goods;
SELECT u.user_id,u.user_name,u.email
FROM (ecs_order_goods og INNER JOIN ecs_order_info oi ON
og.order_id = oi.order_id AND og.goods_name = '诺基亚E66') RIGHT OUTER JOIN ecs_users
u
ON oi.user_id = u.user_id
WHERE oi.user_id IS NULL;
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.