MySQL-DQL之数据多表操作

数据查询语言：简称DQL(Data Query Language)

一. 多表操作

实际开发中，一个项目通常需要很多张表才能完成。

例如：一个商城项目就需要分类表(category)、商品表(products)、订单表(orders)等多张表。且这些表的数据之间存在一定的关系，接下来我们将在单表的基础上，一起学习多表方面的知识。

1. 表与表之间的关系

一对多关系：
常见实例：客户和订单，分类和商品，部门和员工。
一对多建表原则：在从表(多方)创建一个字段，字段作为外键指向主表(一方)的主键。

2. 外键约束

现在我们有两张表“分类表”和“商品表”，为了表明商品属于哪个分类，通常情况下，我们将在商品表上添加一列，用于存放分类cid的信息，此列称为：外键。

此时“分类表category”称为：主表，“cid”我们称为主键。“商品表products”称为：从表，category_id称为外键。我们通过主表的主键和从表的外键来描述主外键关系，呈现就是一对多关系。

在从表(多方)创建一个字段，字段作为外键指向主表(一方)的主键。
指向谁,谁是主表

外键特点：
从表外键的值是对主表主键的引用。
从表外键类型，必须与主表主键类型一致。

外键优点:
在插入数据时，保证了数据的准确性。
在删除数据时，保证了数据的完整性。

从表中引用了主表中的数据，主表中数据不可被删除。
主表中没有数据，从表外键也无法被插入。

3. 创建外键约束表(一对多操作)

category分类表，为一方，也就是主表，必须提供主键cid
products商品表，为多方，也就是从表，必须提供外键category_id

创建分类表

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),constraint foreign key (category_id) references category(cid)
);

向分类表中添加数据

insert into category(cid, cname) values ('c001','服装');
insert into category(cid, cname) values ('c002','电器');

向商品表添加普通数据,没有外键数据，默认为null

insert into products(pid,pname) values ('p002','商品名称2');
insert into products(pid,pname,category_id) values ('p003','商品名称3',null);

向商品表添加普通数据，含有外键信息(category表中存在这条数据)

insert into products(pid,pname,category_id) values ('p001','商品名称1','c001');

向商品表添加普通数据，含有外键信息(category表中不存在这条数据) – 失败,异常

insert into products(pid,pname,category_id) values ('p004','商品名称4','c009');

删除指定分类(分类未被商品使用)

delete  from category where cid='c002';

删除指定分类(分类被商品使用) – 执行异常

delete  from category where cid='c001';

外键约束参考链接
on update cascade

二. 多表查询

1. 多表查询

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

语法：select * from A,B;

这个结果有问题是错误的

② 交集运算：内连接查询(join)

显示内连接：

select * from A inner join B on 条件;
select * from A join B on 条件;

③ 差集运算：外连接查询

Ⅰ. 左外连接：(left join)

显示左连接:

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

Ⅱ. 右外连接：(right join )

显示右连接:

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

④ 栗子

Ⅰ.栗子1(没有外键约束)

• 准备数据

  INSERT INTO hero VALUES(1, '鸠摩智', 9),(3, '乔峰', 1),(4, '虚竹', 4),(5, '段誉', 12);
  

  INSERT INTO kongfu VALUES(1, '降龙十八掌'),(2, '乾坤大挪移'),(3, '猴子偷桃'),(4, '天山折梅手');
  

• 内连接(左表存在，右表也存在的数据被保留)

  SELECT hname,kname FROM hero INNER JOIN kongfu ON hero.kongfu_id = kongfu.kid; 
  

  

• 左连接(左表存在的数据被保留)

  SELECT hname,kname FROM hero LEFT OUTER JOIN kongfu ON hero.kongfu_id = kongfu.kid;
  

  

• 右连接(左表存在的数据被保留)

  SELECT hname,kname FROM hero RIGHT OUTER JOIN kongfu ON hero.kongfu_id = kongfu.kid
  

  

Ⅱ. 栗子2(有外键约束)

• 准备数据
  

  CREATE TABLE category (cid VARCHAR(32) PRIMARY KEY ,cname VARCHAR(50)
  );
  

  CREATE TABLE products(pid VARCHAR(32) PRIMARY KEY ,pname VARCHAR(50),price INT,flag VARCHAR(2),    #是否上架标记为：1表示上架、0表示下架category_id VARCHAR(32),CONSTRAINT products_fk FOREIGN KEY (category_id) REFERENCES category (cid)
  );
  

  #分类
  INSERT INTO category(cid,cname) VALUES('c001','家电');
  INSERT INTO category(cid,cname) VALUES('c002','服饰');
  INSERT INTO category(cid,cname) VALUES('c003','化妆品');
  INSERT INTO category(cid,cname) VALUES('c004','奢侈品');

  #商品
  INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p001','联想',5000,'1','c001');
  INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p002','海尔',3000,'1','c001');
  INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p003','雷神',5000,'1','c001');
  INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p004','JACK JONES',800,'1','c002');
  INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p005','真维斯',200,'1','c002');
  INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p006','花花公子',440,'1','c002');
  INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p007','劲霸',2000,'1','c002');
  INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p008','香奈儿',800,'1','c003');
  INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p009','相宜本草',200,'1','c003');
  

• 查询哪些分类的商品已经上架,内连接

  SELECT DISTINCT c.cname FROM category c INNER JOIN products p ON c.cid = p.category_id WHERE p.flag = '1';
  

• 查询所有分类商品的个数
  COUNT(category_id)会过滤掉null

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

  COUNT(*)不会过滤掉null

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

2. 子查询,左连接

子查询：一条select语句结果作为另一条select语法一部分（查询条件，查询结果，表等）。
select …查询字段 … from … 表… where … 查询条件

栗子:查询“化妆品”分类上架商品详情

• 子查询(作为查询条件)

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

• 作为另一张表

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

• 查询“化妆品”和“家电”两个分类上架商品详情

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

3. 自查询

自查询：左表和右表是同一个表，根据连接查询条件查询两个表中的数据
自查询(自连接)要起别名

• 准备数据

  create table tb_areas(id varchar(30) not null primary key ,title varchar(30),pid varchar(30)
  );
  

  INSERT INTO test.tb_areas (id, title, pid) VALUES ('1', '广东省', 'null');
  INSERT INTO test.tb_areas (id, title, pid) VALUES ('2', '河南省', 'null');
  INSERT INTO test.tb_areas (id, title, pid) VALUES ('3', '深圳市', '1');
  INSERT INTO test.tb_areas (id, title, pid) VALUES ('4', '广州市', '1');
  INSERT INTO test.tb_areas (id, title, pid) VALUES ('5', '南山区', '3');
  INSERT INTO test.tb_areas (id, title, pid) VALUES ('6', '宝安区', '3');
  INSERT INTO test.tb_areas (id, title, pid) VALUES ('7', '越秀区', '4');
  INSERT INTO test.tb_areas (id, title, pid) VALUES ('8', '天河区', '4');
  

  

  select  a.title,b.title,c.title from tb_areas as ainner join tb_areas as b on a.id=b.pidleft join tb_areas as c on b.id=c.pid
  where a.pid = 'null';
  

  select a.title,b.title,c.title from tb_areas aleft join tb_areas b on a.id=b.pidleft join tb_areas c on b.id=c.pid
  where b.id is not null and c.id is not null ;
  

4. case when

5. 窗口函数