文章目录
- 一、多表设计
- 1.一对一设计
- 2.一对多设计
- 3.多对多设计
- 二、多表查询
- 多表查询前的数据准备
- 1.内连接查询
- 2.外连接查询
- 3.子查询
- 4.自关联查询
- 三、多表查询练习
一、多表设计
多表设计即上一章的外键约束，通过外键约束将表之间建立联系，分为一对一、一对多、多对多等三种。
1.一对一设计
- 例如用户表person和身份证表card，用户表中的任意一条数据只能对应身份证表中的一条数据。
- 实现：在其中一张表建立外键，关联另一张表的主键。
CREATE DATABASE db5;
USE db5;
-- 创建person表
CREATE TABLE person(
id INT PRIMARY KEY AUTO_INCREMENT, -- 用户id 唯一且非空
name varchar(20) -- 用户姓名
);
-- 添加数据
INSERT INTO person VALUES (NULL, '甲'),(NULL,'乙'),(NULL,'丙');
-- 创建card表
CREATE TABLE card(
id INT PRIMARY KEY AUTO_INCREMENT, -- 用户id 唯一且非空
number VARCHAR(20) UNIQUE NOT NULL, -- 身份证号，唯一且非空
pid INT UNIQUE, -- 作为外键，引用person表中的主键id，唯一约束
CONSTRAINT cp_fk1 FOREIGN KEY (pid) REFERENCES person(id) -- 建立外键约束，此时card表的pid指向person表的主键id
);2.一对多设计
- 一张表中的数据对应另一张表中的多条数据。例如一个用户有多个订单，或者一个商品分类表中的数据对应商品表中的多条数据。
- 实现：在多的表中建立外键约束，关联唯一数据的表的主键。
- 一个用户对应多个订单：
-- 创建user表
CREATE TABLE users(
id INT PRIMARY KEY AUTO_INCREMENT, -- 用户id 唯一且非空
name varchar(20) -- 用户姓名
);
-- 添加数据
INSERT INTO users VALUES (NULL, '甲'),(NULL,'乙'),(NULL,'丙');
-- 创建orderForm订单表
CREATE TABLE orderForm(
id INT PRIMARY KEY AUTO_INCREMENT, -- 用户主键id 唯一且非空
number VARCHAR(20) UNIQUE NOT NULL, -- 订单编号，唯一且非空
uid INT, -- 作为外键，引用users表中的主键id，非唯一约束
CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES users(id) -- 建立外键约束，此时orderForm表的uid指向users表的主键id
);
-- 添加订单数据
INSERT INTO orderForm VALUES (NULL,'buyNo.001',1),
(NULL,'buyNo.002',1),
(NULL,'buyNo.003',2),
(NULL,'buyNo.004',2);
SELECT * FROM orderForm;- 一个商品分类对应多个商品
-- 创建category商品分类表
CREATE TABLE category(
id INT PRIMARY KEY AUTO_INCREMENT, -- 分类id 唯一且非空
name varchar(20) -- 分类名称
);
-- 添加分类数据
INSERT INTO category VALUES (NULL, '电脑'),(NULL,'手机'),(NULL,'数码配件');
-- 创建product商品信息表
CREATE TABLE product(
id INT PRIMARY KEY AUTO_INCREMENT, -- 商品主键id 唯一且非空
name VARCHAR(30), -- 商品名
cid INT, -- 作为外键，引用category表中的主键id，非唯一约束
CONSTRAINT pc_fk1 FOREIGN KEY (cid) REFERENCES category(id) -- 建立外键约束，此时product表的cid指向users表的主键id
);
-- 添加商品数据
INSERT INTO product VALUES (NULL,'戴尔XPS15',1),
(NULL,'联想拯救者',1),
(NULL,'红米K40',2),
(NULL,'紫米30W充电器',3);
SELECT * FROM product;3.多对多设计
- 例如一个大学生可以选多门课，一门课也可以被多个学生选择。
- 实现：借助第三张中间表，中间表至少包含两个列，这两列作为中间表的外键，分别关联两张表的主键。
-- 创建student表
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT, -- 主键id 唯一且非空
name varchar(20) -- 学生姓名
);
-- 添加数据
INSERT INTO student VALUES (NULL, '大学生小王'),
(NULL,'大学生小李'),
(NULL,'大学生小孙');
-- 创建course表
CREATE TABLE course(
id INT PRIMARY KEY AUTO_INCREMENT, -- 主键id 唯一且非空
name varchar(30) -- 课程名称
);
-- 添加数据
INSERT INTO course VALUES (NULL, '模拟电路'),
(NULL,'数字电路'),
(NULL,'电磁场与电磁波'),
(NULL,'电路分析');
-- 创建中间表
CREATE TABLE stu_course(
id INT PRIMARY KEY AUTO_INCREMENT, -- 主键id 唯一且非空
sid INT, -- 外键，关联student表的主键id
cid INT, -- 外键，关联course表的主键id
CONSTRAINT sc_fk1 FOREIGN KEY (sid) REFERENCES student(id),
CONSTRAINT sc_fk2 FOREIGN KEY (cid) REFERENCES course(id)
);
-- 插入数据
INSERT INTO stu_course VALUES (NULL,1,2),
(NULL,1,3),
(NULL,2,1),
(NULL,2,4),
(NULL,3,1),
(NULL,3,4);
SELECT * FROM stu_course;三张表之间的架构关系如图：
二、多表查询
多表查询有4类，分别是：
- 内连接查询
- 外连接查询
- 子查询
- 自关联查询
多表查询前的数据准备
首先简历五张表：用户信息表、订单信息表、商品分类表、商品表、用户和浏览商品的联系的中间表（一个用户可以浏览多个商品，一个商品也可以被多个用户浏览）。
注意：为了直观查看某些查询信息，特意设置了四处不同数据，见代码中注意1-4.
-- 创建数据库
CREATE DATABASE db4;
USE db4;
-- 创建user表
CREATE TABLE user(
id INT PRIMARY KEY AUTO_INCREMENT, -- 用户id 唯一且非空
name varchar(20), -- 用户姓名
age INT -- 用户年龄
);
-- 插入数据
INSERT INTO user VALUES (1,'用户小王',26),
(2,'用户小孙',25),
(3,'用户小李',24),
(4,'用户小刘',27); -- 注意1：用户小刘没有订单信息
-- 创建订单表
CREATE TABLE orderList(
id INT PRIMARY KEY AUTO_INCREMENT, -- 订单id
number varchar(30), -- 订单编号
uid INT, -- 外键字段,链接到用户id
CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES user(id)
);
-- 添加数据
INSERT INTO orderList VALUES (1,'goodsNum01',1),
(2,'goodsNum02',1),
(3,'goodsNum03',2),
(4,'goodsNum04',2),
(5,'goodsNum05',3),
(6,'goodsNum06',3),
(7,'goodsNum07',NULL);-- 注意2：这个订单没有所属用户
-- 创建商品分类表
CREATE TABLE category(
id INT PRIMARY KEY AUTO_INCREMENT, -- 商品分类名id
name varchar(20) -- 商品分类名
);
-- 添加数据
INSERT INTO category VALUES (1,'手机数码'),
(2,'电脑办公'),
(3,'烟酒糖茶'),
(4,'鞋靴箱包');-- 注意3：这个商品分类下没有商品
-- 创建商品表
CREATE TABLE product(
id INT PRIMARY KEY AUTO_INCREMENT,
name varchar(30),
cid INT, -- 外键，链接到商品分类
CONSTRAINT cp_fk1 FOREIGN KEY (cid) REFERENCES category(id) -- 链接到商品分类id
);
-- 插入数据
INSERT INTO product VALUES (1,'华为P40',1),
(2,'红米K40',1),
(3,'联想小新Pro15',2),
(4,'戴尔xps15',2),
(5,'煊赫门香烟',3),
(6,'飞天茅台',3),
(7,'杜蕾斯',NULL);-- 注意4：这个商品没有所属分类
-- 建立中间表 关联用户表user和商品表product
CREATE TABLE us_pro(
upid INT PRIMARY KEY AUTO_INCREMENT,
uid INT, -- 外键字段，关联用户表的主键id
pid INT, -- 外键字段，关联商品表的主键id
CONSTRAINT up_fk1 FOREIGN KEY (uid) REFERENCES user(id),
CONSTRAINT up_fk2 FOREIGN KEY (pid) REFERENCES product(id)
);
-- 添加数据
INSERT INTO us_pro VALUES (NULL,1,1),
(NULL,1,2),
(NULL,1,3),
(NULL,1,4),
(NULL,1,5),
(NULL,1,6),
(NULL,1,7),
(NULL,2,1),
(NULL,2,2),
(NULL,2,3),
(NULL,2,4),
(NULL,2,5),
(NULL,2,6),
(NULL,2,7),
(NULL,3,1),
(NULL,3,2),
(NULL,3,3),
(NULL,3,4),
(NULL,3,5),
(NULL,3,6),
(NULL,3,7),
(NULL,4,1),
(NULL,4,2),
(NULL,4,3),
(NULL,4,4),
(NULL,4,5),
(NULL,4,6),
(NULL,4,7);数据准备完毕后，当前5张表的架构关系如图所示：
1.内连接查询
- 显式内连接查询
-- 标准语法
SELECT 列名 FROM 表名1 [INNER] JOIN 表名2 ON 条件;-- 查询用户信息和对应的订单信息
SELECT * FROM user INNER JOIN orderList ON orderList.uid = user.id;-- 查询用户信息和对应的订单信息并取别名
SELECT * FROM user u INNER JOIN orderList o ON o.uid = u.id;这两种查询结果是一样的：
-- 查询用户姓名和年龄 订单编号
SELECT u.name, u.age, o.number -- 用户姓名 用户年龄 订单编号
FROM user u -- 查询用户表
INNER JOIN orderList o -- 查询订单表
ON u.id = o.uid;查询结果：
- 隐式内连接查询
SELECT u.name, u.age, o.number
FROM user u, orderList o
WHERE o.uid = u.id;查询结果：
2.外连接查询
- 左外连接查询
左外连接查询是查询左表的全部数据和左右两张表有交集的数据。
-- 左外连接 查询左表的全部数据和左右两表中交集部分
SELECT u.name, u.age, o.number
FROM user u
LEFT OUTER JOIN orderList o
ON u.id = o.uid;如用户表user和订单表中，第4个用户小刘没有订单信息，如果使用左外连接查询，将查到以下结果：
- 右外连接查询
右外连接查询是查询右表的全部数据和左右两张表有交集的数据。
- 右外连接 查询右表的全部数据和左右两表中交集部分
SELECT o.*, u.name
FROM user u
RIGHT OUTER JOIN orderList o
ON u.id = o.uid;如用户表user和订单表中，第7个订单没有所属用户，如果使用右外连接查询，将查到以下结果：
3.子查询
子查询就是在查询语句中嵌套了查询语句，被嵌套的查询语句返回的结果可以作为嵌套查询语句的查询目标。其中被嵌套查询语句的查询结果一般有3种：
- 单行单列
- 多行单列
- 多行多列
-- 1.子连接查询,被嵌套查询语句的查询结果是 单行单列
-- 查询年龄最大的人的姓名和年龄
SELECT name, age FROM user WHERE age=(SELECT MAX(age) FROM user);查询结果：
-- 2.子连接查询,被嵌套查询语句的查询结果是多行单列的
-- 查询某些人的订单信息
SELECT * FROM orderList WHERE uid IN (SELECT id FROM user WHERE name='wzh' OR name='txa');查询结果：
-- 3.子连接查询,被嵌套查询语句的查询结果是多行多列的，将查询结果当做一张虚拟表
-- 先查询订单表中id>4的订单信息和所属用户信息
SELECT * FROM orderList WHERE id>4;
-- 查询订单表中id>4的订单信息和所属用户信息
SELECT u.name, o.number
FROM user u, (SELECT * FROM orderList WHERE id>4) o -- 从两张表中查询
WHERE o.uid = u.id;查询结果：
4.自关联查询
- 同一张表中有数据关联，例如某员工所属的上级id也存在于此表中，需要多次查询此表。
-- 三、自关联查询
-- 创建员工表
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20), -- 姓名
mgr INT, -- 所属上级id
salary DOUBLE -- 工资
);
-- 添加数据
INSERT INTO employee VALUES (1001,'孙悟空',1005,9000.00),
(1002,'猪八戒',1005,8000.00),
(1003,'沙和尚',1005,8500.00),
(1004,'小白龙',1005,7900.00),
(1005,'唐僧',NULL,15000.00),
(1006,'武松',1009,7600.00),
(1007,'李逵',1009,7400.00),
(1008,'林冲',1009,8100.00),
(1009,'宋江',NULL,16000.00);
-- 查询所有员工的姓名及其直接上级的姓名，没有上级的员工也需要查询
/*
分析：
员工姓名 employee表 直接上级姓名 employee表
条件：employee.mgr = employee.id
查询左表的全部数据，和左右两张表交集部分数据，使用左外连接
*/
SELECT e1.name, -- 员工姓名
e1.mgr, -- 上级编号
e2.id, -- 员工编号
e2.name -- 员工姓名
FROM employee e1 -- 员工表
LEFT OUTER JOIN employee e2 -- 员工表
ON e1.mgr = e2.id;原表及最终查询结果：
三、多表查询练习
这里练习还是用的上面的user、orderList、product、category、us_pro五张表。
- 1.查询用户的编号、姓名、年龄、订单编号
-- 需要从两张表：user和orderList中查询，且user.id = orderList.uid
SELECT u.id,u.name,u.age,o.number
FROM user u,orderList o
WHERE u.id = o.uid;
- 2.查询所有的用户编号和姓名、年龄、订单编号
SELECT u.id,u.name,u.age,o.number
FROM user u
LEFT OUTER JOIN orderList o
ON u.id = o.uid;
- 3.查询所有的订单中用户的编号、姓名、年龄、订单编号
-- 从orderList和user中查 orderList.id = user.id
SELECT u.id,u.name,u.age,o.number
FROM user u
RIGHT OUTER JOIN orderList o
ON u.id = o.uid;
- 4.查询年龄>23岁的用户的所有信息加订单编号
-- 还是从这两张表中查 使用 左外连接+子查询 或 左外连接+WHERE
SELECT u.id,u.name,u.age,o.number
FROM (SELECT * FROM user WHERE user.age > 23) u
LEFT OUTER JOIN orderList o
ON u.id = o.uid;
SELECT u.id,u.name,u.age,o.number
FROM user u
LEFT OUTER JOIN orderList o
ON u.id = o.uid
WHERE u.age > 23;
- 5.查询用户小王和用户小孙的信息，显示用户编号、姓名、年龄、订单编号
SELECT u.id,u.name,u.age,o.number
FROM user u,orderList o
WHERE u.id = o.uid AND u.name IN ('用户小王','用户小孙');
- 6.查询有商品的商品分类的编号、分类名称、分类下的商品名
-- 从category和product表中查询，且category.id = product.cid
SELECT c.id,c.name,p.name
FROM category c, product p
WHERE c.id = p.cid;
- 7.查询所有的商品分类的编号、分类名称、分类下的商品名
ELECT c.id,c.name,p.name
FROM category c
LEFT OUTER JOIN product p
ON c.id = p.cid;
- 8.查询所有的商品信息及所属商品分类的编号、分类名称、分类下的商品名
SELECT c.id,c.name,p.name
FROM category c
RIGHT OUTER JOIN product p
ON c.id = p.cid;
- 9.查询所有的用户和其浏览过的商品，显示用户的编号、姓名、年龄、商品名称
SELECT u.id, u.name,u.age,p.name
FROM user u,product p, us_pro up
WHERE up.uid = u.id AND up.pid = p.id;
- 10.查询用户小王和用户小李浏览过的商品，显示用户编号、姓名、年龄、商品名称
SELECT u.id, u.name,u.age,p.name
FROM user u, product p, us_pro up
WHERE up.uid = u.id AND up.pid = p.id AND u.name IN ('用户小王','用户小李');
下面附上10道题完整版代码：
-- 1.查询产生了订单的用户的编号、姓名、年龄、订单编号
-- 需要从两张表：user和orderList中查询，且user.id = orderList.uid
SELECT u.id,u.name,u.age,o.number
FROM user u,orderList o
WHERE u.id = o.uid;
-- 2.查询所有的用户编号和姓名、年龄、订单编号
-- 需要从两张表：user和orderList中查询，且user.id = orderList.uid,且需要查询左表的全部数据，使用左外连接查询
SELECT u.id,u.name,u.age,o.number
FROM user u
LEFT OUTER JOIN orderList o
ON u.id = o.uid;
-- 3.查询所有的订单中用户的编号、姓名、年龄、订单编号
-- 从orderList和user中查 orderList.id = user.id
SELECT u.id,u.name,u.age,o.number
FROM user u
RIGHT OUTER JOIN orderList o
ON u.id = o.uid;
-- 4.查询年龄>23岁的用户的所有信息加订单编号
-- 还是从这两张表中查 使用 左外连接+子查询 或 左外连接+WHERE
SELECT u.id,u.name,u.age,o.number
FROM (SELECT * FROM user WHERE user.age > 23) u
LEFT OUTER JOIN orderList o
ON u.id = o.uid;
SELECT u.id,u.name,u.age,o.number
FROM user u
LEFT OUTER JOIN orderList o
ON u.id = o.uid
WHERE u.age > 23;
-- 5.查询用户小王和用户小孙的信息，显示用户编号、姓名、年龄、订单编号
SELECT u.id,u.name,u.age,o.number
FROM user u,orderList o
WHERE u.id = o.uid AND u.name IN ('用户小王','用户小孙');
-- 6.查询有商品的商品分类的编号、分类名称、分类下的商品名
-- 从category和product表中查询，且category.id = product.cid
SELECT c.id,c.name,p.name
FROM category c, product p
WHERE c.id = p.cid;
-- 7.查询所有的商品分类的编号、分类名称、分类下的商品名
SELECT c.id,c.name,p.name
FROM category c
LEFT OUTER JOIN product p
ON c.id = p.cid;
-- 8.查询所有的商品信息及所属商品分类的编号、分类名称、分类下的商品名
SELECT c.id,c.name,p.name
FROM category c
RIGHT OUTER JOIN product p
ON c.id = p.cid;
-- 9.查询所有的用户和其浏览过的商品，显示用户的编号、姓名、年龄、商品名称
SELECT u.id, u.name,u.age,p.name
FROM user u,product p, us_pro up
WHERE up.uid = u.id AND up.pid = p.id;
-- 10.查询用户小王和用户小李浏览过的商品，显示用户编号、姓名、年龄、商品名称
SELECT u.id, u.name,u.age,p.name
FROM user u, product p, us_pro up
WHERE up.uid = u.id AND up.pid = p.id AND u.name IN ('用户小王','用户小李');
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
