数据库内连接应用

        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;#课堂练习 1)列出客户信息和订单日期，并按订单日期排序；#FROM_UNIXTIME() 2)列出被购买过的商品的商品名称,类型名称与交易价格; 3)显示各订单下每个商品与商品类型以及该商品的销售额，按照订单号排序； 4)列出被购买过的商品的商品名称,品牌名称与客户信息; 5)找出ecshop用户所有的订单信息;