多表查询的多种方式

多表查询的多种方式

insert into category (cname) values ('男装/女装/童装/内衣'),('女鞋/箱包/珠宝/钟表'),('食品/酒类/生鲜/特产'),('美妆/个护清洁/宠物'); 
INSERT INTO product VALUES(null,'七匹狼休闲裤男2018秋装新款纯棉男士直筒商务休闲长裤子男装 2775 黑色 32/80A',299,2);
INSERT INTO product VALUES(null,'真维斯JEANSWEST t恤男 纯棉圆领男士净色修身青年打底衫长袖体恤上衣 浅花灰 M',35,2);
INSERT INTO product VALUES(null,'PLAYBOY/花花公子休闲裤男弹力修身 秋季适中款商务男士直筒休闲长裤 黑色适中款 31(2.4尺)',128,2);
INSERT INTO product VALUES(null,'劲霸男装K-Boxing 短版茄克男士2018新款休闲舒适棒球领拼接青年夹克|FKDY3114 黑色 185',362,2); 
INSERT INTO product VALUES(null,'Chanel 香奈儿 女包 2018全球购 新款蓝色鳄鱼皮小牛皮单肩斜挎包A 蓝色',306830,3);
INSERT INTO product VALUES(null,'皮尔卡丹(pierre cardin)钱包真皮新款横竖款男士短款头层牛皮钱夹欧美商务潮礼盒 黑色横款（款式一）',269,3);
INSERT INTO product VALUES(null,'PRADA 普拉达 女士黑色皮质单肩斜挎包 1BD094 PEO V SCH F0OK0',28512,3);
INSERT INTO product VALUES(null,'好想你 干果零食 新疆特产 阿克苏灰枣 免洗红枣子 玛瑙红500g/袋',21.9,4);
INSERT INTO product VALUES(null,'三只松鼠坚果大礼包1588g每日坚果礼盒干果组合送礼火红A网红零食坚果礼盒8袋装',128,4);
INSERT INTO product VALUES(null,'三只松鼠坚果炒货零食特产每日坚果开心果100g/袋',32.8,4);
INSERT INTO product VALUES(null,'洽洽坚果炒货孕妇坚果零食恰恰送礼每日坚果礼盒（26g*30包） 780g/盒（新老包装随机发货)',149,4);
INSERT INTO product VALUES(null,'今之逸品【拍3免1】今之逸品双眼皮贴双面胶美目舒适隐形立显大眼男女通用 中号160贴',9.9,5);
INSERT INTO product VALUES(null,'自然共和国 原自然乐园 芦荟舒缓保湿凝胶300ml*2（约600g）进口补水保湿舒缓晒后修复面膜',72,5);

– 交叉连接查询(得到两个表乘积基本不会使用)

SELECT  * FROM category c,product p;

– 隐式内连接:精确关联条件,去掉不必要的数据!(使用的关键字 inner join – inner可以省略)

SELECT  * FROM category c,product p; WHERE  p.c_id = c.cid ;

– 显示内连接

SELECT * FROM category c INNER JOIN product p WHERE p.c_id = c.cid;

内连接原理 : 内连接根据我们连接条件大大的缩小的笛卡尔积的范围;
在内连接中只有主外键能够关联上的数据才能被查询出来!

– 现在商品类别表中有5个类别了

INSERT INTO category VALUES (NULL,'手机/运营商/数码');

– 将商品对应的5改成6

UPDATE category set cid = 6 WHERE cid = 5;

– 但是当我内关联查询的时候发现不止没有6也没有5

select DISTINCT c.* from category c inner join product p on c.cid = p.c_id;

– 只能查询出5个类别,这是为什么呢?
– 这是因为商品表中就就只有五个类别能和商品类别表匹配,所以内连接把能匹配的数据都查询出来了,
– 那么有没有什么办法可以解决这个问题呢?

– 外连接查询(使用的关键字 outer join – outer可以省略)

– 外连接可以把关联查询的两张表的一张表作为主表,另外一张作为从表,而外链接使用保证主表的数据完整;

– 左外连接：left outer join

select * from A left outer join B on 条件;

– 实例:左连接+条件+分组

SELECT * FROM category c LEFT JOIN product p ON c.cid = p.c_id ORDER BY c.cid DESC;

– 查询c表(左连接,)

select DISTINCT c.* from category c left join product p on c.cid = p.c_id;

– 查询价格在一万以内名字中包含 ‘想’ 的商品

解析:由题目可知我们要的是两张表共有的条件所以用内连接

– 隐式内连接+(连接条件+删选条件+模糊查询)

select * from category c,product p where c.cid = p.c_id and p.price <= 10000 and p.pname like ‘%想%’;

– 显示内连接+(连接条件+删选条件+模糊查询)

SELECT * FROM category c INNER JOIN product p ON c.cid = p.c_id WHERE p.price < 10000 AND p.pname LIKE '%想%';

– 查询所有分类商品的个数

解析:从题可知查询结果应为先满足分类商品再统计个数

– 左连接(连接条件+分组+统计函数)

SELECT c.cname '分类商品',COUNT(p.c_id) '个数' FROM category c LEFT JOIN product p ON c.cid = p.c_id GROUP BY c.cname; 

注意:此处不能写 count() 如果写count() 手机/运营商/数码将会是1 因为确实有一条记录与之对应虽然记录里的数据都是null
但是如果你统计外检表的任意一个列 count() 会忽略null值所以就是0了

– 单行单列子查询(一条select语句结果作为另一条select语法一部分（查询条件，查询结果，表等）。)

– 查询和海尔洗衣机同样价格的商品

解析:分解思想

– 第一步 : 找到海尔洗衣机

select * from product p where p.pname like '%海尔%洗衣机%';

– 第二步 : 记住价格 2499
– 第三步 : 根据价格查询

select * from product p where p.price = 2499;

– 子查询 三部整合一步

 SELECT * FROM product p INNER JOIN category c  ON c.cid = p.c_id WHERE p.price = (SELECT price FROM product WHERE pname LIKE '%海尔%洗衣机%');

– 单行多列子查询

– 查询所有商品的商品类别名称

解析:用商品表查类别表，先查询出外键，然后去重，接着子查询嵌套用cid查所有。

SELECT * FROM category WHERE cid in (SELECT DISTINCT c_id FROM product);

– 注意:SQL语句子查询部分返回了多条记录,这种子查询叫做单行多列子查询;

– 多行多例子查询

– 查询商业价格大于一万的产品名称和信息

SELECT * FROM (SELECT p.*,c.cname FROM product p INNER JOIN category c ON p.c_id = c.cid) a WHERE a.price >10000;

– 这条语句就是把一个查询结果直接封装为一个虚拟表a表 然后在封装的虚拟表a表的基础上又做查询 这种子查询叫做 多行多列子查询;