学习目标

能够使用内连接进行多表查询
能够使用外连接进行多表查询
能够使用子查询进行多表查询

第一章表之间的关系

1.1 表与表之间的关系

一对一关系：
- 在实际的开发中应用不多.因为一对一可以创建成一张表.
- 常见实例：身份证表与员工信息表
一对多关系：
- 常见实例：客户和订单，分类和商品，部门和员工, 省份和城市
多对多关系：
- 常见实例：学生和课程、用户和角色, 演员和电影, 商品和订单

1.2 外键约束

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

此时“分类表category”称为：主表，“cid”我们称为主键。“商品表products”称为：从表，category_id称为外键。我们通过主表的主键和从表的外键来描述主外键关系，就这让表与表之间产生了关系。

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

声明外键约束

语法：

alter table 从表 add [constraint][外键名称] foreign key (从表外键字段名) references 主表 (主表的主键);

[外键名称]用于删除外键约束的，一般建议“_fk”结尾

alter table 从表 drop foreign key 外键名称

使用外键目的：
- 保证数据完整性
  - 要考虑从表添加数据
  - 要考虑主键删除数据

提供表结构如下：

# 分类表
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)
);

初始化数据

#分类
INSERT INTO category(cid,cname) VALUES('c001','家电');
INSERT INTO category(cid,cname) VALUES('c002','服饰');
INSERT INTO category(cid,cname) VALUES('c003','化妆品');
#商品
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');

第二章多表关系实战

2.1 实战1：省和市

方案1：多张表，一对多

-- 创建省份表
create table province(
	pid int PRIMARY KEY,
	pname varchar(32), -- 省份名称
	description varchar(100) -- 描述
);

-- 创建城市表
create table city (
	cid int PRIMARY KEY,
	cname varchar(32), -- 城市名称
	description varchar(100), -- 描述
	province_id int,
	CONSTRAINT city_province_fk foreign key(province_id) references province(pid)
);

方案2：一张表，自关联一对多

create table area (
	id int PRIMARY key AUTO_INCREMENT,
	`name` varchar(32),
	description varchar(100),
	parent_id int,
	CONSTRAINT area_area_fk FOREIGN KEY(parent_id) REFERENCES area(id)
);

INSERT into area values(null, '辽宁省', '这是一个省份', null);
INSERT into area values(null, '大连市', '这是一个城市', 1);
INSERT into area values(null, '沈阳市', '这是一个城市', 1);
INSERT into area values(null, '河北省', '这是一个省份', null);
INSERT into area values(null, '石家庄市', '这是一个城市', 4);
INSERT into area values(null, '保定市', '这是一个城市', 4);

2.2 实战2：用户和角色

多对多关系

-- 用户表
create table `user` (
	uid varchar(32) PRIMARY KEY,
	username varchar(32),
	`password` varchar(32)
);

-- 角色表
create table role (
	rid varchar(32) PRIMARY KEY,
	rname varchar(32)
);

-- 中间表
create table user_role(
	user_id varchar(32),
	role_id varchar(32),
	CONSTRAINT user_role_pk PRIMARY KEY(user_id,role_id),
	CONSTRAINT user_id_fk FOREIGN KEY(user_id) REFERENCES `user`(uid),
	CONSTRAINT role_id_fk FOREIGN KEY(role_id) REFERENCES role(rid)
);

第三章多表查询

提供表结构如下：

# 分类表
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(1), #是否上架标记为：1表示上架、0表示下架
  category_id VARCHAR(32),
  CONSTRAINT products_category_fk FOREIGN KEY (category_id) REFERENCES category (cid)
);

3.1 初始化数据

#分类
INSERT INTO category(cid,cname) VALUES('c001','家电');
INSERT INTO category(cid,cname) VALUES('c002','服饰');
INSERT INTO category(cid,cname) VALUES('c003','化妆品');
#商品
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');

3.2 多表查询

交叉连接查询(开发中不使用-得到的是两个表的乘积) [了解]

语法：select * from A,B;

select * from category,products ORDER BY cid, pid;

效果: category表中每条记录, 与 products表中每条记录分别连接

常用的多表查询方式:

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

隐式内连接：select * from A,B where 条件;
显示内连接：select * from A inner join B on 条件;

-- 查询那些分类的商品已经上架
-- 隐式内连接
SELECT DISTINCT
	*
FROM
	category c,
	products p
WHERE
	c.cid = p.category_id;

-- 显示内连接
SELECT DISTINCT
	*
FROM
	category c INNER JOIN products p 
ON 
	c.cid = p.category_id;

效果

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

左外连接：left outer join
- select * from A left outer join B on 条件;
右外连接：right outer join
- select * from A right outer join B on 条件;

#2.查询所有分类商品的个数
#左外连接
INSERT INTO category(cid,cname) VALUES('c004','奢侈品');

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

效果

3.3 子查询

子查询概述

子查询：

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

select ....查询字段 ... from ... 表.. where ... 查询条件

子查询代码演示

-- 子查询, 查询“化妆品”分类上架商品详情
-- 内连接方式
select 
	p.*
from 
	category c, products p
WHERE
	c.cid = p.category_id and c.cname = '化妆品'

-- 子查询方式 第一种(作为查询条件值使用)
select 
	* 
from
	products p
where
	p.category_id = (SELECT cid from category where cname='化妆品') 

-- 子查询方式 第二种(作为 一张表 使用)
select 
	p.*
FROM
	products p, (select * from category where cname='化妆品') c
WHERE
	p.category_id = c.cid;

效果

子查询练习

#查询“化妆品”和“家电”两个分类上架商品详情
select
	*
from 
	products 
WHERE
	category_id in (select cid from category where cname='家电' or cname='化妆品'); 

-- select cid from category where cname='家电' or cname='化妆品';
-- select cid from category where cname in ('家电', '化妆品');

效果

作者：能阅读至此, 注定了我们今生有缘! 我已从事Java教育十多年, 怀着教书育人的情怀, 正在让更多的同学们少走弯路, 改变千万IT人的命运!

欢迎关注我的B站，可观看本文章配套视频~~~ 欢迎关注我的公众号，获取更多资料~~~

MySQL实战系列 -- 3. SQL多表查询