MySQL数据库-DQL（多表查询）
多表设计
一对一
分析
人和身份证。一个人只有一个身份证，一个身份证只能对应一个人！
实现原则
在任意一个表建立外键，去关联另外一个表的主键。
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;
练习题
MySQL数据库-DQL的练习题_Tensorflow-CSDN博客
https://blog.csdn.net/weixin_43715214/article/details/123072433
