认真学SQL——MySQL入门之DQL多表查询

多表查询

==本质: 把多个表通过主外键关联关系连接(join)合并成一个大表,再去查询==

知识点：

外键 foreign key

 

外键概念:

在从表(多方)创建一个字段，引用主表(一方)的主键,对应的这个字段就是外键。

外键特点：

1:从表外键的值是对主表主键的引用。
2:从表外键类型，必须与主表主键类型一致。

外键约束优点:

在插入数据时，保证了数据的准确性。
在删除数据时，保证了数据的完整性。

外键约束[了解]

修改存储引擎

-- 分类表
CREATE TABLE category
(
    cid   VARCHAR(32) PRIMARY KEY,
    cname VARCHAR(100) #分类名称
);

-- 商品表
CREATE TABLE products
(
    pid         varchar(32) PRIMARY KEY,
    pname       VARCHAR(40),
    price       DOUBLE,
    category_id varchar(32)
);

-- 演示没有外键约束出现的问题

-- 演示往从表中插入主表不存在的数据

insert into products values('p001','联想笔记本',4999,'c001'); # 插入成功,因为没有约束
insert into products values('p002','华为笔记本',4999,'c001'); # 插入成功,因为没有约束
insert into products values('p003','小米笔记本',4999,'c001'); # 插入成功,因为没有约束

-- 往主表中插入数据
insert into category values('c001','电脑');

-- 演示主表删除从表已经引用的数据
delete from category where cid = 'c001'; # 删除成功,因为没有约束

问题1:以上问题如何解决? 添加外键约束

问题2:如何添加外键约束? 前提是innodb存储引擎,myisam存储引擎不支持外键约束

注意: 如果修改了存储引擎,需要重新建表才能生效,否则还是原来的存储引擎

查看之前建表语句

show create table category; -- ENGINE=MyISAM ...
show create table products; -- ENGINE=MyISAM ...

1.交叉连接查询

(基本不会使用-得到的是两个表的乘积) [了解]（不要记住）
语法：select * from A,B; # 这个结果有问题是错误的

显式格式：select * from A cross join B

隐式格式：select * from A , B

交叉连接就是表数据交叉的乘积，直接这么用会造成笛卡尔积增大太多，服务器会爆

2.交集运算：内连接查询(使用的关键字 inner join  -- inner可以省略)

显式内连接格式：select * from A inner join B on 条件;

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

特点：内连接是通过匹配两个表之间的共同字段来获取结果集，只返回两个表中有匹配的数据行。内连接的结果集中只包含匹配的行。

简记：查询出多个表的交集部分

如果不加条件或者条件无效也等于使用交叉连接

-- 需求1.查询哪些分类的商品已经上架
SELECT DISTINCT c.cname FROM category c
 INNER JOIN products p ON c.cid = p.category_id
 WHERE p.flag = '1';

3.差集运算：外连接查询(使用的关键字 outer join -- outer可以省略)

-- 方式一 左外连接—left outer join

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

特点：左连接是返回左表中所有记录和右表中匹配的记录，如果右表中没有匹配的记录，则返回NULL值。左连接的结果集中包含左表中所有的行，但只包含右表中与左表匹配的行。

简记：查询出左表跟右表的交集部分和左表所有内容，没匹配到的就是null

-- 需求2.查询所有分类商品的个数

SELECT cname,COUNT(category_id) FROM category c
 LEFT OUTER JOIN products p
  ON c.cid = p.category_id
 GROUP BY cname;

-- 方式二 右外连接—right outer join

格式：select * from A right outer join B on 条件;

特点：右连接与左连接相反，返回右表中所有记录和左表中匹配的记录，如果左表中没有匹配的记录，则返回NULL值。右连接的结果集中包含右表中所有的行，但只包含左表中与右表匹配的行。

简记：查询出右表跟左表的交集部分和右表所有内容，没匹配到的就是null

子查询

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

格式：select ....查询字段 ... from ... 表.. where ... 查询条件

-- 需求：查询“化妆品”分类上架商品详情

SELECT * FROM products p
 WHERE p.category_id =
 (
  SELECT c.cid FROM category c
   WHERE c.cname='化妆品'
 );

-- 需求2.查询化妆品和家电分类商品详情

select p.*
from day03.category as c
join products p on c.cid = p.category_id where c.cname in ('化妆品','化妆品');

自连接

核心思想：就是特殊的内外连接，特殊指出子啊与左表和右表是同一个表
最大特点：左右表是同一个表，那就必须要给他们起别名，才能区分
应用场景：自连接应用场景非常局限，一班都是省市县区域表或者上下级员工表

-- 需求：查询河北省下所有的城市

select city.id,city.title
from areas as city
inner join areas as prov
on city.pid=prov.id
where prov.title='河北省';

拓展：开窗函数排序

三大排序开窗函数：row_number() rank() dense_rank()
-- row_number():巧记1234 特点: 唯一且连续
-- rank(): 巧记1224 特点:并列不连续
-- dense_rank(): 巧记1223 特点: 并列且连续

开窗特点：每个开窗函数默认在表最后加一列，存储对应的开窗结果

-- 需求1.获取商品价格最低的top1商品
select *,
       row_number() over (order by price) as r1,
       rank() over (order by price) as r2,
       dense_rank() over (order by price) as r3
from day03.products;

-- 嵌套
select *
from (select *,
       row_number() over (order by price) as r1,
       rank() over (order by price) as r2,
       dense_rank() over (order by price) as r3
from day03.products) as tmp
where r2=1;

select *
from (select *,dense_rank() over (order by price desc) as r4
from products where pname like '___' or pname like '__') as t1 limit 3;