多表设计
一对一
分析
实现原则
SQL演示
一对多
分析
实现原则
SQL演示
多对多
分析
实现原则
SQL演示
在多个数据表中要修改表数据怎么办？
方法一
方法二
多表查询
数据准备
总述
SQL语句
架构器图解
笛卡尔积
内连接查询
原理
显式内连接语法
SQL语句
隐式内连接语法
SQL语句
外连接查询
左外连接
右外连接
子查询（嵌套）
（1）子查询-结果是单行单列的
（2）子查询-结果是多行单列的
（3）子查询-结果是多行多列的
自关联查询
什么是自关联查询？
数据准备
自关联查询数据演示
练习题
多表设计
一对一
分析
人和身份证。一个人只有一个身份证，一个身份证只能对应一个人！
实现原则
在任意一个表建立外键，去关联另外一个表的主键。
SQL演示
-- 创建db5数据库
CREATE DATABASE db5;
-- 使用db5数据库
USE db5;
CREATE TABLE person(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
INSERT INTO person VALUES (1,'张三'),(2,'李四');
CREATE TABLE card(
id INT PRIMARY KEY AUTO_INCREMENT,
number VARCHAR(20) UNIQUE NOT NULL,
pid INT UNIQUE,
CONSTRAINT cp_fk1 FOREIGN KEY (pid) REFERENCES person(id)
);
INSERT INTO card VALUES (NULL,'12414',1),(NULL,'45344',2);执行SQL后，我们可以在SQLyog的右上角的“新架构设计器”，将两个表拖入，如下所示：
该软件，会自动显示出2张表的关联关系——1：1
一对多
分析
例如：
用户和订单。一个用户可以有多个订单！
商品分类和商品。一个分类下可以有多个商品！
实现原则
在多的一方，建立外键约束，来关联一的一方主键
SQL演示
CREATE TABLE USER(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
INSERT INTO USER VALUES (NULL,'张三'),(NULL,'李四');
CREATE TABLE orderlist(
id INT PRIMARY KEY AUTO_INCREMENT,
number VARCHAR(20),
uid INT,
CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER (id)
);
INSERT INTO orderlist VALUES (NULL,'hm001',1),
(NULL,'hm002',1),
(NULL,'hm003',2),
(NULL,'hm004',2);在多的一方，建立外键约束，来关联一的一方主键。
订单表中的uid指向用户表的id。
在架构设计器中是 1 ：n 的关系。
注意：
uid是orderlist的，id是user的，在orderlist表（多的一方）中建立外键，去关联user表（少的一方）的主键。
CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER (id)
多对多
分析
学生和课程，一个学生可以选择多个课程，一个课程也可以被多个学生选择！
实现原则
需要借助第三张表中间表，中间表至少包含两个列，这两个列作为中间表的外键，分别关联两张表的主键
SQL演示
学生表-student表，课程表-course表。
-- 创建student表
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
-- 添加数据
INSERT INTO student VALUES (NULL,'张三'),(NULL,'李四');
-- 创建course表
CREATE TABLE course(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10)
);
-- 添加数据
INSERT INTO course VALUES (NULL,'语文'),(NULL,'数学');中间表
sid 用于和student表的id进行外键关联
cid 用于和course表的id进行外键关联
-- 创建中间表
CREATE TABLE stu_course(
id INT PRIMARY KEY AUTO_INCREMENT,
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,1),(NULL,1,2),(NULL,2,1),(NULL,2,2);在架构设计器中，结果如下：
在多个数据表中要修改表数据怎么办？
方法一
按照上述案例，我们先要删除外键约束
ALTER TABLE stu_course DROP FOREIGN KEY sc_fk1;
ALTER TABLE stu_course DROP FOREIGN KEY sc_fk2;这时候，可以去架构设计器看一下（刷新一下！），可以发现表与表之间的“线”没有了
然后再重新添加外键约束，并在后面加上级联关系
ALTER TABLE stu_course ADD
CONSTRAINT sc_fk1 FOREIGN KEY (sid) REFERENCES student(id)
ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE stu_course ADD
CONSTRAINT sc_fk2 FOREIGN KEY (cid) REFERENCES course(id)
ON UPDATE CASCADE ON DELETE CASCADE;然后再执行修改语句就可以了。
例如：
UPDATE student SET id=3 WHERE id=2;student表中的id 和stu_course表中的 sid原来是2的都被改为3了。
方法二
当然了，如果需要，在建表的时候就可以直接添加级联
CREATE TABLE stu_course(
id INT PRIMARY KEY AUTO_INCREMENT,
sid INT, -- 用于和student表的id进行外键关联
cid INT, -- 用于和course表的id进行外键关联
CONSTRAINT sc_fk1 FOREIGN KEY (sid) REFERENCES student(id)
ON UPDATE CASCADE ON DELETE CASCADE, -- 添加外键约束
CONSTRAINT sc_fk2 FOREIGN KEY (cid) REFERENCES course(id)
ON UPDATE CASCADE ON DELETE CASCADE -- 添加外键约束
);多表查询
数据准备
总述
（1）用户表——user表
（2）订单表——orderlist表
orderlist表中设置了外键uid指向user表；user表与orderlist表是 1：n的关系
（3）商品分类表——category表
（4）商品表——product表
product表中设置了外键cid指向categroy表；categroy表与product表是 1：n的关系
（5）中间表——us_pro表
us_pro表中甚至连外键uid和pid，分别与user表和product表的主键进行关联。
SQL语句
-- 创建db6数据库
CREATE DATABASE db6;
-- 使用db6数据库
USE db6;
-- （1）创建user表
CREATE TABLE USER(
id INT PRIMARY KEY AUTO_INCREMENT, -- 用户id
NAME VARCHAR(20), -- 用户姓名
age INT -- 用户年龄
);
-- 添加数据
INSERT INTO USER VALUES (1,'张三',23);
INSERT INTO USER VALUES (2,'李四',24);
INSERT INTO USER VALUES (3,'王五',25);
INSERT INTO USER VALUES (4,'赵六',26);
-- （2）订单表
CREATE TABLE orderlist(
id INT PRIMARY KEY AUTO_INCREMENT, -- 订单id
number VARCHAR(30), -- 订单编号
uid INT, -- 外键字段
CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id)
);
-- 添加数据
INSERT INTO orderlist VALUES (1,'hm001',1);
INSERT INTO orderlist VALUES (2,'hm002',1);
INSERT INTO orderlist VALUES (3,'hm003',2);
INSERT INTO orderlist VALUES (4,'hm004',2);
INSERT INTO orderlist VALUES (5,'hm005',3);
INSERT INTO orderlist VALUES (6,'hm006',3);
INSERT INTO orderlist VALUES (7,'hm007',NULL);
-- （3）商品分类表
CREATE TABLE category(
id INT PRIMARY KEY AUTO_INCREMENT, -- 商品分类id
NAME VARCHAR(10) -- 商品分类名称
);
-- 添加数据
INSERT INTO category VALUES (1,'手机数码');
INSERT INTO category VALUES (2,'电脑办公');
INSERT INTO category VALUES (3,'烟酒茶糖');
INSERT INTO category VALUES (4,'鞋靴箱包');
-- （4）商品表
CREATE TABLE product(
id INT PRIMARY KEY AUTO_INCREMENT, -- 商品id
NAME VARCHAR(30), -- 商品名称
cid INT, -- 外键字段
CONSTRAINT cp_fk1 FOREIGN KEY (cid) REFERENCES category(id)
);
-- 添加数据
INSERT INTO product VALUES (1,'华为手机',1);
INSERT INTO product VALUES (2,'小米手机',1);
INSERT INTO product VALUES (3,'联想电脑',2);
INSERT INTO product VALUES (4,'苹果电脑',2);
INSERT INTO product VALUES (5,'中华香烟',3);
INSERT INTO product VALUES (6,'玉溪香烟',3);
INSERT INTO product VALUES (7,'计生用品',NULL);
-- （5）中间表
CREATE TABLE us_pro(
upid INT PRIMARY KEY AUTO_INCREMENT, -- 中间表id
uid INT, -- 外键字段。需要和用户表的主键产生关联
pid INT, -- 外键字段。需要和商品表的主键产生关联
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);
INSERT INTO us_pro VALUES (NULL,1,2);
INSERT INTO us_pro VALUES (NULL,1,3);
INSERT INTO us_pro VALUES (NULL,1,4);
INSERT INTO us_pro VALUES (NULL,1,5);
INSERT INTO us_pro VALUES (NULL,1,6);
INSERT INTO us_pro VALUES (NULL,1,7);
INSERT INTO us_pro VALUES (NULL,2,1);
INSERT INTO us_pro VALUES (NULL,2,2);
INSERT INTO us_pro VALUES (NULL,2,3);
INSERT INTO us_pro VALUES (NULL,2,4);
INSERT INTO us_pro VALUES (NULL,2,5);
INSERT INTO us_pro VALUES (NULL,2,6);
INSERT INTO us_pro VALUES (NULL,2,7);
INSERT INTO us_pro VALUES (NULL,3,1);
INSERT INTO us_pro VALUES (NULL,3,2);
INSERT INTO us_pro VALUES (NULL,3,3);
INSERT INTO us_pro VALUES (NULL,3,4);
INSERT INTO us_pro VALUES (NULL,3,5);
INSERT INTO us_pro VALUES (NULL,3,6);
INSERT INTO us_pro VALUES (NULL,3,7);
INSERT INTO us_pro VALUES (NULL,4,1);
INSERT INTO us_pro VALUES (NULL,4,2);
INSERT INTO us_pro VALUES (NULL,4,3);
INSERT INTO us_pro VALUES (NULL,4,4);
INSERT INTO us_pro VALUES (NULL,4,5);
INSERT INTO us_pro VALUES (NULL,4,6);
INSERT INTO us_pro VALUES (NULL,4,7);架构器图解
笛卡尔积
- 有两张表，获取这两个表的所有组合情况
- 要完成多表查询，需要消除这些没有用的数据
- 多表查询格式
内连接查询
原理
内连接查询的是两张表有交集的部分数据（有主外键关联的数据）
显式内连接语法
SELECT 列名 FROM 表名1 [INNER] JOIN 表名2 ON 条件;
其中inner 是可以省略的！
SQL语句
（1）查询用户信息和对应的订单信息
我们先看一下user表和orderlist表中的数据：
user表
orderlist表
SELECT * FROM USER INNER JOIN orderlist ON user.id=orderlist.uid;
SELECT * FROM USER JOIN orderlist ON user.id=orderlist.uid;这两条语句效果是一样的，其中一个省略了inner 。
ON的后面就是关联的条件。
观察表中数据，显然，执行内连接后，user表中id为4的，orderlist表中uid为null的数据没有！这就是内连接的特点——取交集
（2）查询用户信息和对应的订单信息，起别名
SELECT * FROM USER u INNER JOIN orderlist o ON u.id=o.uid;结果一样，但是简化了代码！
（3）查询用户姓名，年龄。和订单编号（指定列查询）
SELECT
u.name, -- 姓名
u.age, -- 年龄
o.number -- 订单编号
FROM
USER u -- 用户表
JOIN
orderlist o -- 订单表
ON
u.id=o.uid;
不使用“*”，为了避免输出冗余的列。
隐式内连接语法
SELECT 列名 FROM 表名1,表名2 WHERE 条件;
SQL语句
（1）查询用户姓名，年龄。和订单编号
SELECT
u.name,
u.age,
o.number
FROM
USER u,orderlist o
WHERE
u.id=o.uid;简单的来说就是不使用inner join...on ，和之前的单表查询一样，只不过from后面跟上多个表。
外连接查询
有2种，分成左外连接和右外连接。
左外连接
原理
查询左表的全部数据，和左右两张表有交集部分的数据
语法
SELECT 列名 FROM 表名(左) LEFT [OUTER] JOIN 表名(右) ON 条件;
SQL语句
（1）查询所有用户信息，以及用户对应的订单信息（左外）
获取user表中的全部数据，以及与orderlist表有交集的数据。
SELECT
u.name, -- 姓名
u.age, -- 年龄
o.number -- 订单编号
FROM
USER u -- 用户表
LEFT OUTER JOIN
orderlist o -- 订单表
ON
u.id=o.uid;
name是user表的字段，显然已经全部获取到了。
右外连接
原理
查询右表的全部数据，和左右两张表有交集部分的数据
语法
SELECT 列名 FROM 表名(右) RIGHT [OUTER] JOIN 表名(左) ON 条件;
SQL语句
（1）查询所有用户信息，以及用户对应的订单信息（右外）
获取orderlist表中的全部数据，以及与user表有交集的数据。
SELECT
u.name, -- 姓名
u.age, -- 年龄
o.number -- 订单编号
FROM
USER u -- 用户表
RIGHT OUTER JOIN
orderlist o -- 订单表
ON
u.id=o.uid;
不难发现外连接，不论是左外连接，还是右外连接，都是相对的，都是由其位置决定的！即将两个表的位置换一下，那么“左外”就变成了“右外” 。
子查询（嵌套）
查询语句中嵌套了查询语句。我们就将嵌套查询称为子查询！有3种情况！
（1）子查询-结果是单行单列的
语法
SELECT 列名 FROM 表名 WHERE 列名=(
SELECT 聚合函数(列名)
FROM 表名 [WHERE 条件]
);
可以作为条件，使用运算符进行判断！
查询年龄最高的用户姓名
SELECT NAME,age FROM USER WHERE age = (SELECT MAX(age) FROM USER);使用聚合函数，求age的最大值，将求出的结果传回去进行查询。
（2）子查询-结果是多行单列的
语法
SELECT 列名 FROM 表名 WHERE 列名 [NOT] IN (
SELECT 列名 FROM 表名 [WHERE 条件]
);
可以作为条件，使用运算符in或not in进行判断！
查询张三和李四的订单信息
SELECT number,uid
FROM orderlist
WHERE uid IN (
SELECT id
FROM USER
WHERE NAME='张三' OR NAME='李四'
);实际上就是uid是不是在（in）id里面
（3）子查询-结果是多行多列的
可以作为一张虚拟表参与查询！
语法
SELECT 列名 FROM 表名 [别名],(
SELECT 列名 FROM 表名 [WHERE 条件]) -- 相当于第二张数据表
[别名] [WHERE 条件];
查询订单表中id大于4的订单信息 和所属用户信息
SELECT
u.name,
o.number
FROM
USER u,
(SELECT * FROM orderlist WHERE id > 4) o
WHERE
u.id=o.uid;
自关联查询
什么是自关联查询？
同一张表中有数据关联。可以多次查询这同一个表！
数据准备
-- 创建员工表
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
mgr INT,
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);mgr是代表上级的意思，即：“孙悟空”，“猪八戒”，“沙和尚”，“小白龙”的上级都是唐僧。
自关联查询数据演示
查询所有员工的姓名及其直接上级的姓名，没有上级的员工也需要查询
分析：
员工姓名 employee表 直接上级姓名 employee表
条件：employee.mgr = employee.id （核心）
查询左表的全部数据，和左右两张表交集部分数据，使用左外连接
SELECT
t1.name, -- 员工姓名
t1.mgr, -- 上级编号
t2.id, -- 员工编号
t2.name -- 员工姓名
FROM
employee t1 -- 员工表
LEFT OUTER JOIN
employee t2 -- 员工表
ON
t1.mgr = t2.id;
