MySQL多表查询

最新推荐文章于 2023-08-16 12:23:18 发布

HanghangzZ

最新推荐文章于 2023-08-16 12:23:18 发布

阅读量204

点赞数

分类专栏：学习数据库笔记文章标签： mysql 数据库

本文链接：https://blog.csdn.net/HanghangzZ/article/details/109552863

版权

学习数据库笔记专栏收录该内容

12 篇文章 0 订阅

订阅专栏

MySQL多表查询

笛卡尔积的形式

将几个表的每一个记录都组合，详情见下面的测试。

这个形式无法满足我们的需求。

内连接的形式

查询两个表中符合连接条件的记录。内连接的使用比外连接要多的多。

SELECT 字段名称,... 
FROM tbl_name1
[INNER] JOIN tbl_name2
ON 连接条件

外连接的形式

左外连接

SELECT 字段名称,... FROM tbl_name1
LEFT [OUTER] JOIN tbl_name2
ON 连接条件

先显示左表中的全部记录，再去右表中查询符合条件的记录，不符合的以NULL代替。其中OUTER可以省略。

tbl_name1是左表，以它作为主表。tbl_name2是右表，以它作为从表。

右外连接

SELECT 字段名称,... FROM tbl_name1
RIGHT [OUTER] JOIN tbl_name2
ON 连接条件

先显示右表中的全部记录，再去左表中查询符合条件的记录，不符合的以NULL代替。其中OUTER可以省略。

tbl_name1是左表，以它作为从表。tbl_name2是右表，以它作为主表。

测试

-- 员工表
CREATE TABLE IF NOT EXISTS emp(
	id INT UNSIGNED AUTO_INCREMENT KEY COMMENT '用户编号',
    username VARCHAR(20) NOT NULL UNIQUE COMMENT '用户名',
    age TINYINT NOT NULL DEFAULT 18 COMMENT '年龄',
    sex ENUM('男','女','保密') NOT NULL DEFAULT '保密' COMMENT '性别，默认为保密',
    addr VARCHAR(50) NOT NULL DEFAULT '北京' COMMENT '住址',
    depId TINYINT UNSIGNED NOT NULL COMMENT '部门对应的编号'
);
INSERT emp(username,age,depId) VALUES('king',25,1),
('queen',26,2),
('imooc',27,1),
('lily',28,1),
('rose',29,3),
('john',30,3);

-- 部门表
CREATE TABLE IF NOT EXISTS dep(
id TINYINT UNSIGNED KEY AUTO_INCREMENT,
depName VARCHAR(50) NOT NULL UNIQUE,
depDesc VARCHAR(100) NOT NULL DEFAULT ''
);
INSERT dep(depName,depDesc) VALUES('PHP教学部','研发PHP课件'),
('JAVA教学部','研发JAVA课件'),
('WEB前端教学部','研发WEB前端课件'),
('IOS教学部','研发IOS课件');

上面的两个表供下面测试用

测试笛卡尔积的形式

-- 查询emp id，username，age    部门名称 dep  depName
mysql> SELECT id,username,age,depName FROM emp,dep;
1052 - Column 'id' in field list is ambiguous

像上面这样子查询，我们会发现有些字段是有争议的，因为在员工表和部门表同时存在id这个字段，这个时候我们就可以用上表名.字段名的形式来说明是哪一个表的字段

SELECT emp.id,username,age,depName FROM emp,dep;

+----+----------+-----+---------------+
| id | username | age | depName       |
+----+----------+-----+---------------+
|  1 | king     |  25 | IOS教学部     |
|  1 | king     |  25 | JAVA教学部    |
|  1 | king     |  25 | PHP教学部     |
|  1 | king     |  25 | WEB前端教学部 |
|  2 | queen    |  26 | IOS教学部     |
|  2 | queen    |  26 | JAVA教学部    |
|  2 | queen    |  26 | PHP教学部     |
|  2 | queen    |  26 | WEB前端教学部 |
|  3 | imooc    |  27 | IOS教学部     |
|  3 | imooc    |  27 | JAVA教学部    |
|  3 | imooc    |  27 | PHP教学部     |
|  3 | imooc    |  27 | WEB前端教学部 |
|  4 | lily     |  28 | IOS教学部     |
|  4 | lily     |  28 | JAVA教学部    |
|  4 | lily     |  28 | PHP教学部     |
|  4 | lily     |  28 | WEB前端教学部 |
|  5 | rose     |  29 | IOS教学部     |
|  5 | rose     |  29 | JAVA教学部    |
|  5 | rose     |  29 | PHP教学部     |
|  5 | rose     |  29 | WEB前端教学部 |
|  6 | john     |  30 | IOS教学部     |
|  6 | john     |  30 | JAVA教学部    |
|  6 | john     |  30 | PHP教学部     |
|  6 | john     |  30 | WEB前端教学部 |
+----+----------+-----+---------------+
24 rows in set (0.52 sec)

但是像上面这条语句出来的结果有24条，相当于把员工表中的记录与部门表中的记录每一个都组合了一遍，这并不是我们想要的结果。所以笛卡尔积的形式无法满足我们的需求。

测试内连接的形式

-- 查询emp id，username，age    部门名称 dep  depName depDesc
SELECT e.id,e.username,e.age,d.depName,d.depDesc
FROM emp AS e
INNER JOIN dep AS d
ON e.depId=d.id;

+----+----------+-----+---------------+-----------------+
| id | username | age | depName       | depDesc         |
+----+----------+-----+---------------+-----------------+
|  1 | king     |  25 | PHP教学部     | 研发PHP课件     |
|  2 | queen    |  26 | JAVA教学部    | 研发JAVA课件    |
|  3 | imooc    |  27 | PHP教学部     | 研发PHP课件     |
|  4 | lily     |  28 | PHP教学部     | 研发PHP课件     |
|  5 | rose     |  29 | WEB前端教学部 | 研发WEB前端课件 |
|  6 | john     |  30 | WEB前端教学部 | 研发WEB前端课件 |
+----+----------+-----+---------------+-----------------+

这里的e.depId和d.id一一对应，根据emp表中记录的depId来匹配dep表中的id，这就是ON e.depId=d.id的作用

接下来我们来尝试插入一条错误的记录，它的depId是超出dep表的

INSERT emp(username,age,depId) VALUES('测试用户',39,6);

我们用上面内连接语句查询出来结果与上面一致，因为内连接查出来的是符合两个表连接条件的记录，是交集，而上面这条记录是不符合的，它也是一条脏记录，我们后面会提到。

测试左外连接

-- 查询emp id，username，age    部门名称 dep  depName depDesc
SELECT e.id,e.username,e.age,d.depName,d.depDesc
FROM emp AS e
LEFT OUTER JOIN dep AS d
ON e.depId=d.id;

+----+----------+-----+---------------+-----------------+
| id | username | age | depName       | depDesc         |
+----+----------+-----+---------------+-----------------+
|  1 | king     |  25 | PHP教学部     | 研发PHP课件     |
|  2 | queen    |  26 | JAVA教学部    | 研发JAVA课件    |
|  3 | imooc    |  27 | PHP教学部     | 研发PHP课件     |
|  4 | lily     |  28 | PHP教学部     | 研发PHP课件     |
|  5 | rose     |  29 | WEB前端教学部 | 研发WEB前端课件 |
|  6 | john     |  30 | WEB前端教学部 | 研发WEB前端课件 |
|  7 | 测试用户 |  39 | NULL          | NULL            |
+----+----------+-----+---------------+-----------------+

先显示左表emp中的全部记录，再去右表dep中查询符合条件的记录，不符合的以NULL代替。

对于第七条记录来说，在右表dep中并没有符合条件的记录，所以depName和depDesc以NULL代替。

但是这样子的记录是没有什么意义的，以后我们可以通过外键去除这种记录。

测试右外连接

-- 查询emp id，username，age    部门名称 dep  depName depDesc
-- 此处省略OUTER
SELECT e.id,e.username,e.age,d.depName,d.depDesc
FROM emp AS e
RIGHT JOIN dep AS d
ON e.depId=d.id;

+------+----------+------+---------------+-----------------+
| id   | username | age  | depName       | depDesc         |
+------+----------+------+---------------+-----------------+
|    1 | king     |   25 | PHP教学部     | 研发PHP课件     |
|    2 | queen    |   26 | JAVA教学部    | 研发JAVA课件    |
|    3 | imooc    |   27 | PHP教学部     | 研发PHP课件     |
|    4 | lily     |   28 | PHP教学部     | 研发PHP课件     |
|    5 | rose     |   29 | WEB前端教学部 | 研发WEB前端课件 |
|    6 | john     |   30 | WEB前端教学部 | 研发WEB前端课件 |
| NULL | NULL     | NULL | IOS教学部     | 研发IOS课件     |
+------+----------+------+---------------+-----------------+

先显示右表dep中的全部记录，再去左表emp中查询符合条件的记录，不符合的以NULL代替。

对于第七条记录来说，在左表emp表中并没有符合条件的记录，所以id，username字段以NULL代替。

但是这样子的记录是没有什么意义的，以后我们可以通过外键去除这种记录。

为什么要多表？

CREATE TABLE IF NOT EXISTS user(
	id TINYINT UNSIGNED KEY AUTO_INCREMENT COMMENT '编号',
    username VARCHAR(20) NOT NULL UNIQUE COMMENT '用户名',
    email VARCHAR(50) NOT NULL DEFAULT '123@qq.com' COMMENT '邮箱',
    proName VARCHAR(10) NOT NULL DEFAULT '北京' COMMENT '用户所在省份'
);

如果我们建立了上面这张表，然后里面有3000万条记录的省份是哈尔滨。

（1）这里省份使用的是VARCHAR进行存储，相对于使用TINYINT类型的编号储存更加浪费空间

（2）我们想把哈尔滨改成大东北，那么我们需要更新省份的名字，利用编号可以增加效率

-- 创建省份表 
CREATE TABLE IF NOT EXISTS provinces(
	id TINYINT UNSIGNED KEY AUTO_INCREMENT COMMENT '编号',
    proName VARCHAR(10) NOT NULL UNIQUE COMMENT '省份名称'
);
INSERT provinces(proName) VALUES('北京'),
('上海'),
('深圳');
-- 创建人员表
CREATE TABLE IF NOT EXISTS user3(
	id TINYINT UNSIGNED KEY AUTO_INCREMENT COMMENT '编号',
    username VARCHAR(20) NOT NULL UNIQUE COMMENT '用户名',
    email VARCHAR(50) NOT NULL DEFAULT '123@qq.com' COMMENT '邮箱',
    proId TINYINT UNSIGNED NOT NULL COMMENT '用户所在省份'
);
INSERT user3(username,proId) VALUES('a','1'),
('b','1'),
('c','1'),
('d','2'),
('e','3'),
('f','1'),
('g','1');

-- 内连接查询
-- 查询user id,username   provinces proName
SELECT u.id,u.username,p.proName
FROM user3 AS u
INNER JOIN provinces AS p
ON u.proId=p.id;

+----+----------+---------+
| id | username | proName |
+----+----------+---------+
|  1 | a        | 北京    |
|  2 | b        | 北京    |
|  3 | c        | 北京    |
|  4 | d        | 上海    |
|  5 | e        | 深圳    |
|  6 | f        | 北京    |
|  7 | g        | 北京    |
+----+----------+---------+

如果这时候我们想去更改北京为首都，我们可以直接去更改provinces表中的一条记录，如果我们不去分表，那么我们将要更改5条记录。

UPDATE provinces SET proName='首都' WHERE id=1;

我们再执行一次内连接查询

+----+----------+---------+
| id | username | proName |
+----+----------+---------+
|  1 | a        | 首都    |
|  2 | b        | 首都    |
|  3 | c        | 首都    |
|  4 | d        | 上海    |
|  5 | e        | 深圳    |
|  6 | f        | 首都    |
|  7 | g        | 首都    |
+----+----------+---------+

这样子我们的效率也就高了很多，这也就是为什么我们要进行分表。

四表联查

这里只是单纯为了演示多表查询，在实际使用的时候，不要使用三个表及以上的复合查询，因为它的性能不是很好，以后在做测试和优化时会发现，使用多表联查的效率没有从单独表中查出来再进行合并的效率高。所以在这里只是为了演示多个表都是差不多的语句。

商品表和商品分类表存在商品分类的关系，商品表和管理员表存在管理与被管理关系，管理员与省份表存在故乡关系

-- 创建省份表 
CREATE TABLE IF NOT EXISTS provinces(
	id TINYINT UNSIGNED KEY AUTO_INCREMENT COMMENT '编号',
    proName VARCHAR(10) NOT NULL UNIQUE COMMENT '省份名称'
);
INSERT provinces(proName) VALUES('北京'),
('上海'),
('深圳');

-- 创建商品分类表 
CREATE TABLE IF NOT EXISTS cate(
	id TINYINT UNSIGNED AUTO_INCREMENT KEY COMMENT '编号',
    cateName VARCHAR(50) NOT NULL UNIQUE COMMENT '商品分类名称',
    cateDesc VARCHAR(100) NOT NULL DEFAULT '电子' COMMENT '商品分类的描述'
);
INSERT cate(cateName) VALUES('母婴'),
('服装'),
('电子');

-- 创建商品表 
CREATE TABLE IF NOT EXISTS products(
	id INT KEY AUTO_INCREMENT COMMENT '编号',
    productName VARCHAR(50) NOT NULL UNIQUE COMMENT '商品名称',
    price FLOAT(8,2) NOT NULL DEFAULT 0 COMMENT '价格',
    cateId TINYINT UNSIGNED NOT NULL COMMENT '商品所在分类的编号',
    adminId TINYINT UNSIGNED NOT NULL COMMENT '管理员编号'
);
INSERT products(productName,price,cateId,adminId)
VALUES('iphone9',9888,3,1),
('adidas',388,2,2),
('nike',888,2,2),
('奶瓶',288,1,1);

-- 创建管理员表 
CREATE TABLE IF NOT EXISTS admin(
	id TINYINT UNSIGNED KEY AUTO_INCREMENT COMMENT '编号',
    username VARCHAR(20) NOT NULL UNIQUE COMMENT '用户名',
    email VARCHAR(50) NOT NULL DEFAULT '123@qq.com' COMMENT '邮箱',
    proId TINYINT UNSIGNED NOT NULL COMMENT '用户所在省份'
);
INSERT admin(username,proId)
VALUES('king',1),
('queen',2);

我们先进行两表

-- 查询products id,productName,price   --cate cateName
SELECT p.id,p.productName,p.price,c.cateName
FROM products AS p
INNER JOIN cate AS c
ON p.cateId=c.id;

+----+-------------+---------+----------+
| id | productName | price   | cateName |
+----+-------------+---------+----------+
|  1 | iphone9     | 9888.00 | 电子     |
|  2 | adidas      |  388.00 | 服装     |
|  3 | nike        |  888.00 | 服装     |
|  4 | 奶瓶        |  288.00 | 母婴     |
+----+-------------+---------+----------+

-- 查询管理员id，username，email  -- provinces proName
SELECT a.id,a.username,a.email,p.proName
FROM admin AS a
INNER JOIN provinces AS p
ON a.proId=p.id;

+----+----------+------------+---------+
| id | username | email      | proName |
+----+----------+------------+---------+
|  1 | king     | 123@qq.com | 北京    |
|  2 | queen    | 123@qq.com | 上海    |
+----+----------+------------+---------+

我们再进行三表

-- 查询 products id,productName,price
-- cate cateName
-- admin username email
SELECT p.id,p.productName,p.price,c.cateName,a.username,a.email
FROM products AS p
INNER JOIN cate AS c
ON p.cateId=c.id
INNER JOIN admin AS a
ON p.adminId=a.id;

+----+-------------+---------+----------+----------+------------+
| id | productName | price   | cateName | username | email      |
+----+-------------+---------+----------+----------+------------+
|  1 | iphone9     | 9888.00 | 电子     | king     | 123@qq.com |
|  2 | adidas      |  388.00 | 服装     | queen    | 123@qq.com |
|  3 | nike        |  888.00 | 服装     | queen    | 123@qq.com |
|  4 | 奶瓶        |  288.00 | 母婴     | king     | 123@qq.com |
+----+-------------+---------+----------+----------+------------+

我们还可以进行复合语句排序

SELECT p.id,p.productName,p.price,c.cateName,a.username,a.email
FROM products AS p
INNER JOIN cate AS c
ON p.cateId=c.id
INNER JOIN admin AS a
ON p.adminId=a.id
WHERE p.price<1000
ORDER BY p.price DESC
LIMIT 0,2;

+----+-------------+--------+----------+----------+------------+
| id | productName | price  | cateName | username | email      |
+----+-------------+--------+----------+----------+------------+
|  3 | nike        | 888.00 | 服装     | queen    | 123@qq.com |
|  2 | adidas      | 388.00 | 服装     | queen    | 123@qq.com |
+----+-------------+--------+----------+----------+------------+

最后进行四表

-- prdoucts id,productName,price
-- cate cateName
-- admin username,email
-- provinces proName
SELECT p.id,p.productName,p.price,c.cateName,a.username,a.email,prov.proName
FROM products AS p
INNER JOIN cate AS c
ON p.cateId=c.id
INNER JOIN admin AS a
ON p.adminId=a.id
INNER JOIN provinces AS prov
ON a.proId=prov.id;

+----+-------------+---------+----------+----------+------------+---------+
| id | productName | price   | cateName | username | email      | proName |
+----+-------------+---------+----------+----------+------------+---------+
|  1 | iphone9     | 9888.00 | 电子     | king     | 123@qq.com | 北京    |
|  2 | adidas      |  388.00 | 服装     | queen    | 123@qq.com | 上海    |
|  3 | nike        |  888.00 | 服装     | queen    | 123@qq.com | 上海    |
|  4 | 奶瓶        |  288.00 | 母婴     | king     | 123@qq.com | 北京    |
+----+-------------+---------+----------+----------+------------+---------+