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

　　连接
　　合并思想. N个表 --> 1个表
　　连接的定义
　　将2个或2个以上的表通过一定的条件拼接起来形成一个虚表的过程。连接是from子句的一部分.
　　从2个表的连接开始.
　　左表: 放置在你左手边的表. left_tab
　　右表: 放置在你右手边的表. right_tab
　　连接语法 select list from left_tab join_type right_tab on join_condition;1、连接类型
　　三种基本类型:  交叉连接,内连接与外连接
　　交叉连接
　　返回的就是一个笛卡尔积. #数学中 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
　　交叉连接语法 select list from left_tab cross join right_tab;
　　内连接
　　在交叉连接的基础上,仅返回满足连接条件的行.
　　连接条件
　　1.使用on关键字来指定.
　　2.on的用法与where类似.
　　3.on可以使用连接的所有表中的列值进行判断 #连接条件 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
　　内连接的语法 select list from left_tab inner join right_tab on join_condition;
　　外连接
　　在内连接的基础上,返回一些额外的行.
　　左外连接(左连接)
　　在内连接的基础上,左表除了返回满足连接条件的行之外,剩下的所有不满足连接条件的行也要一并返回,右表中与之没有匹配的行的列值设置为null #连接条件 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
　　右外连接(右连接)
　　在内连接的基础上,右表除了返回满足连接条件的行之外,剩下的所有不满足连接条件的行也要一并返回,左表中与之没有匹配的行的列值设置为null #连接条件 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
　　外连接语法 select list from left_tab left|right outer join right_tab on join_condition;#查询示例 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;2、内连接应用
　　语法: select list from left_tab inner join right_tab on join_condition where search_condition order by sort_columns;#哪些商品被用户购买过? #思路 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;
　　表的别名 select list from tab_name alias_name ... #表名 别名 select list from tab_name as alias_name ... #表名 AS 别名
　　注意: 一旦给表设置了别名,则以后只能通过别名引用表中的列.别名会隐藏表名. 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#列出商品表中商品类型编号对应的类型名称 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;
　　join嵌套
　　可以在一个join中嵌套另外一个join
　　一般来说,当连接的表的个数N>=3时需要使用嵌套join.
　　当连接的表的个数为N时,则至少需要N-1个连接条件. #列出哪些用户购买了哪些商品,每一次购买该商品的花费是多少? SELECT * FROM ecs_users; SELECT * FROM ecs_order_info; SELECT * FROM ecs_order_goods;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;3、外连接应用
　　语法: select list from left_tab left|right outer join right_tab on join_condition [where keyword is null] #过滤满足条件的数据,保留不满足条件的数据 order by sort_columns;#哪些商品没有被客户购买过? 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;
　　join嵌套
　　与内连接中的join嵌套用法类似. #找出没有购买过诺基亚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;