两表联查
笛卡儿积
先创建两个表分别是员工表和部门表，并插入几条记录：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
CREATE DATABASE IF NOT EXISTS test2 DEFAULT CHARACTER SET 'UTF8'; USE test2; CREATE TABLE emp( id INT UNSIGNED AUTO_INCREMENT KEY, username VARCHAR(20) NOT NULL UNIQUE COMMENT '编号', age TINYINT UNSIGNED NOT NULL DEFAULT 18 COMMENT '年龄', sex ENUM('男','女','保密') NOT NULL DEFAULT '保密' COMMENT '性别', addr VARCHAR(20) NOT NULL DEFAULT '北京', depId TINYINT UNSIGNED NOT NULL COMMENT '部门对应的编号' )ENGINE=INNODB CHARSET=UTF8; INSERT emp(username,age,depId) VALUES('king',24,1), ('queen',25,2), ('imooc',26,1), ('lily',27,1), ('rose',28,3), ('john',29,3); INSERT emp(username,age,depId) VALUES('测试用户',39,6); CREATE TABLE dep( id TINYINT UNSIGNED AUTO_INCREMENT KEY, depName VARCHAR(50) NOT NULL UNIQUE, depDesc VARCHAR(100) NOT NULL DEFAULT '' )ENGINE=INNODB CHARSET=UTF8; INSERT dep(depName,depDesc) VALUES('PHP教学部','研发PHP课件'), ('JAVA教学部','研发JAVA课件'), ('WEB前端教学部','研发WEB前端课件'), ('IOS教学部','研发IOS课件'); |
查询员工表emp中的id、username、age对应dep部门表中的部门名称depname。
|
1 |
SELECT emp.id,emp.username,emp.age,dep.depName FROM emp,dep; |
这样就得到了笛卡儿积，笛卡儿积相当于是一个外循环（表一的元素）+内循环（表二的记录），第一个用户在第二个表中一个个的查。笛卡儿积的形式满足不了我们的需求，这里知识介绍一下，用得比较少。
内连接
- 查询两个表中符合连接条件的记录。
- 查询的是两个表中的交集部分，比如说第一个表中有depId=6，但是第二个表中并没有id=6，那么就会查不出来，不会报错，不会以NULL代替。
- 使用最多
|
1 2 3 |
SELECT 字段名称,... FROM tbl_name1 [INNER] JOIN tbl_name2 ON 连接条件 |
我们的案例中，员工表emp中的depid对应的是dep的id，则就是连接条件，所以：
|
1 2 3 4 |
SELECT e.id,e.username,e.age,d.depName FROM emp AS e INNER JOIN dep AS d ON e.depId=d.id; |
外连接
-
左外连接：先显示左表中的全部记录，再去右表中查询符合条件的记录，不符合的以NULL代替。
|
1 2 3 |
SELECT 字段名称,...FROM tbl_name1 [OUTER] LEFT JOIN tbl_name2 ON 条件 |
测试：
|
1 2 3 4 5 |
-- 测试左外连接 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; |
-
右外连接：先显示右表中的全部记录，再去左表中查询符合条件的记录，不符合的以NULL代替。
|
1 2 3 |
SELECT 字段名称,... FROM tbl_name1 OUTER RIGHT JOIN tbl_name2 ON 条件; |
测试：
|
1 2 3 4 5 |
-- 测试右外连接 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; |
多表联查的意义
意义：如果给每个用户设置一个省份在用户表中比如北京，需要把北京改成首都，那么我们假如有100000个用户，那么我们需要进行非常多次数的修改（只用操作一个，但是程序需要进行狠多次的修改），会很大的影响服务器在修改期间的性能，因此我们可以使用把城市放在另一个表中，修改的时候我们只需要只改城市列表，这样修改一次所有用户的数据就修改了。大大提高了性能。
注意：多表联查最好是两个表之间，三个表的时候性能会大幅下降。
先创建两个表：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
-- 创建省份表 CREATE TABLE provinces( id TINYINT UNSIGNED AUTO_INCREMENT KEY, proName VARCHAR(10) NOT NULL UNIQUE )ENGINE=INNODB CHARSET=UTF8; INSERT provinces(proName) VALUES('北京'), ('上海'), ('深圳'); --创建用户表 CREATE TABLE user( id TINYINT UNSIGNED AUTO_INCREMENT KEY, username VARCHAR(20) NOT NULL UNIQUE, email VARCHAR(50) NOT NULL DEFAULT '382771946@qq.com', proId TINYINT UNSIGNED NOT NULL )ENGINE=INNODB CHARSET=UTF8; INSERT user(username,proId) VALUES('a',1); INSERT user(username,proId) VALUES('b',1); INSERT user(username,proId) VALUES('c',1); INSERT user(username,proId) VALUES('d',2); INSERT user(username,proId) VALUES('e',3); INSERT user(username,proId) VALUES('f',1); INSERT user(username,proId) VALUES('g',1); |
查询用户对应的省份：
|
1 2 3 4 5 |
-- 查询user id ,username provinces proName SELECT u.id,u.username,p.proName FROM user AS u JOIN provinces AS p ON u.proId=p.id; |
现在我们把北京改成首都，这个时候不用改用户表，因为我们用户的指向了北京。
|
1 |
UPDATE provinces SET proName='首都' WHERE id=1; |
然后用刚才的查询，可以看到每个用户对应的城市已经修改过来了。
三表联查
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
-- 管理员admin id username email proId CREATE TABLE admin( id TINYINT UNSIGNED AUTO_INCREMENT KEY, username VARCHAR(20) NOT NULL UNIQUE, email VARCHAR(50) NOT NULL DEFAULT '382771946@qq.com', proId TINYINT UNSIGNED NOT NULL )ENGINE=INNODB CHARSET=UTF8; INSERT admin(username,proId) VALUES('king',1); INSERT admin(username,proId) VALUES('queen',2); -- 商品分类cate id cateName cateDesc CREATE TABLE cate( id TINYINT UNSIGNED AUTO_INCREMENT KEY, cateName VARCHAR(50) NOT NULL UNIQUE, cateDesc VARCHAR(100) NOT NULL DEFAULT '' )ENGINE=INNODB CHARSET=UTF8; INSERT cate(cateName) VALUES('母婴'); INSERT cate(cateName) VALUES('服装'); INSERT cate(cateName) VALUES('电子'); -- 商品表products id productName, price,cateId CREATE TABLE products( id INT UNSIGNED AUTO_INCREMENT KEY, productName VARCHAR(50) NOT NULL UNIQUE, price FLOAT(8,2) NOT NULL DEFAULT 0, cateId TINYINT UNSIGNED NOT NULL, adminId TINYINT UNSIGNED NOT NULL )ENGINE=INNODB CHARSET=UTF8; INSERT products(productName,price,cateId,adminId) VALUES('iphone9',9888,3,1), ('adidas',388,2,2), ('nike',888,2,2), ('奶瓶',288,1,1); |
连接商品表和分类表，他们的关系是cateid相等
|
1 2 3 4 5 |
-- 查询products id productName price --- cate cateName SELECT p.id,p.productName,p.price,c.cateName FROM products AS p JOIN cate AS c ON p.cateId=c.id; |
查三张表并且按照价格降序排列，并且只显示前两条记录，他们的关系是：
p.adminId = a.id（商品表中的管理员id=管理员表中的管理员id）
p.cateId = c.id（商品分类表的id=产品的cateId）
|
1 2 3 4 5 6 7 8 9 |
SELECT p.id,p.productName,p.price,c.cateName,a.username,a.email FROM products AS p JOIN admin AS a ON p.adminId=a.id JOIN cate AS c ON p.cateId=c.id WHERE p.price<1000 ORDER BY p.price DESC LIMIT 0,2; |
