1.多表查询

1.1 需求分析

需求 : 根据需求查询商品类别表和商品表的数据
表结构如下 :

1.2 初始化数据

1.2.1 向商品类别表中添加数据

insert into category (cname) values ('男装/女装/童装/内衣'),('女鞋/箱包/珠宝/钟表'),('食品/酒类/生鲜/特产'),('美妆/个护清洁/宠物'); 

1.2.2 向商品表中添加数据

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);

1.3 多表查询

1.3.1 交叉连接查询(基本不会使用-得到的是两个表的乘积) [了解]

语法：select * from A,B;
代码:

-- 商品类别 5条数据
select count(*) from category;
-- 商品 18条数据
select count(*) from product;
-- 交叉查询 90条数据
select count(*) from product,category;
-- 详细数据
select * from product,category;

结果 :

1.3.1.1 笛卡尔积 :

笛卡尔乘积是指在数学中，两个集合X和Y的笛卡尓积（Cartesian product），又称直积，表示为X×Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员[3] 。

假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。

数据库表连接数据行匹配时所遵循的算法就是以上提到的笛卡尔积，表与表之间的连接可以看成是在做乘法运算。

笛卡尔积的危害 :

以前五条数据为例,这五条关联数据中只有第一条是有用的,其他几条都是没有用的,所以在交叉查询中,数据库会浪费非常多的性能来查询这些没有用的数据,这种情况使我们不愿意见到的;

1.3.1.1 解决笛卡尔积:

精确关联条件,去掉不必要的数据!

1.3.2 内连接查询(使用的关键字 inner join – inner可以省略)

1.3.2.1 隐式内连接：select * from A,B where 条件;

-- 隐式内连接
select * from category,product where category.cid = product.c_id;
-- 每次用表名.列名是不是很烦 没关系可以起别名啊
select * from category c,product p where c.cid = p.c_id;

1.3.2.2 显式内连接：select * from A inner join B on 条件; (推荐写法)

-- 显式内连接
select * from category c inner join product p on c.cid = p.c_id;

1.3.2.2.1 内连接原理 :

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

1.3.2.2.2 内连接问题 :

向商品类别表插入一条数据

INSERT into category values (null,'手机/运营商/数码');

现在商品类别表中有6个类别

select * from category;

但是当我关联查询的时候

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

只能查询出5个类别,这是为什么呢?

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

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

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

1.3.3.1 左外连接：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;

效果 :

1.3.3.2 查询原理 :

左外连接查询是以left关键字左边的表为基准表,保证左表数据完整,如果右表没有与左表数据匹配的记录,那么右表将以一条null数据填充查询结果,保证左表的完整

左外链接正好能解决我们上面的问题
代码

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

1.3.4 右外连接：right outer join(了解)

右外链接与左外链接正好相反,它是保证右表数据完整;
此处需要注意一个问题就是所有的右外链接都能转为左外链接,所以右外链接应用并不广泛,了解即可

语法 :
select * from A right outer join B on 条件;

1.3.5 各种连接查询区别总结

1.3 多表查询综合案例

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

1.3.1.1 隐式内连接

-- 查询价格在一万以内名字中包含 '想' 的商品
select * from category c,product p where c.cid = p.c_id and p.price <= 10000 and p.pname like '%想%';

隐式内连接是借助where条件来设定关联关系,所以这样如果一旦where条件变多整体关联关系就很难把控,并且表越多,隐式内连接关联就越乱,所以这种方案我们并不采纳

1.3.1.2 显式内连接

-- 显式内连接 分工明确 不容易写乱套
select cname,pname,price -- 设置显示的类目名 --
from category c inner join product p on c.cid=p.c_id -- 此处设置管理的方式
where p.price<10000 and p.pname like '%想%';-- 设置查询的条件

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

1.3.2.1 左外连接 :

select cname,count(pid) 
from category c left join product p on c.cid = p.c_id
group by cname

结果 :
此处不能写 count() 如果写count() 手机/运营商/数码将会是1 因为确实有一条记录与之对应虽然记录里的数据都是null

但是如果你统计外检表的任意一个列 count() 会忽略null值所以就是0了

1.3.2.2 使用内连接统计，缺少一个类别，错误的

select cname,count(*) 
from category c inner join product p on c.cid = p.c_id
group by cname

1.3 子查询

定义 ：一条select语句结果作为另一条select语法一部分（查询条件，查询结果，表等）。
语法 :

SELECT column_name [, column_name ]
FROM   table1 [, table2 ]
WHERE  column_name OPERATOR(SELECT column_name [, column_name ]FROM table1 [, table2 ][WHERE])

案例 : 查询和海尔洗衣机同样价格的商品

连接查询 : …!@#$%^&*()_+…搞不了,搞不了…
没法连接呀,发生在一张表的查询,关联字段也找不到,你叫我怎么连接?啊?怎么连接?

1.3.1 单行单列 子查询 :

-- 第一步 : 找到海尔洗衣机
select * from product p where p.pname like '%海尔%洗衣机%';-- 第二步 : 记住价格 2499
-- 第三步 : 根据价格查询
select * from product p where p.price = 2499;-- 子查询 三部整合一步
select * from product p where p.price = 
(select p.price from product p where p.pname like '%海尔%洗衣机%');

上面的案例是把一条SQL语句的查询结果当做条件值传入到另一条SQL语句中,而且大家注意此时海尔洗衣机这条SQL返回一个结果,这种叫单行单列子查询

1.3.1 单行多列 子查询; :

比如查询所有商品的商品类别名称

select * from category where cid in (select DISTINCT c_id from product);

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

1.3.1 多行多列 子查询; :

还有的时候会把查询结果当成临时表存储起来然后在查询结果基础上再进行查询

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表的基础上又做查询 这种子查询叫做 多行多列子查询;