从0到1学数据库：多表查询

点击上方“罗晓胜”，马上关注，您的支持对我帮助很大

上期文章

 

 

• 从0到1学数据库：where条件查询和Order By数据排序

• 从0到1学数据库：Function函数

 

/   前言   /

 

一张表的数据肯定是不够我们发挥的，接下来我们试试多张表一起查询

 

/   正文   /

 

本章要点

• 多表连接的定义
• 笛卡尔积
• 等值连接
• 不等值连接
• 外连接
• Union，Union All(集合运算符)

准备表和数据

• 例：新建student表，SQL如下

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_class
-- ----------------------------
DROP TABLE IF EXISTS `t_class`;
CREATE TABLE `t_class` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '班级名称',
  `c_create_time` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- ----------------------------
-- Records of t_class
-- ----------------------------
BEGIN;
INSERT INTO `t_class` VALUES (1, '一班', '2000-12-15 18:27:14');
INSERT INTO `t_class` VALUES (2, '二班', '2001-12-15 18:27:29');
INSERT INTO `t_class` VALUES (3, '三班', '2002-12-15 18:27:54');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

mysql支持的多表查询

列举如下：

• 连接查询

  • 左外连接（left outer join 或left join）：检索结果为满足连接条件的数据行+左表中不满足连接条件的数据行，然后再根据过滤条件过滤即为查询结果（先连接后过滤）注意：此处过滤条件很容易犯错，一般要以左表的条件进行过滤，不然容易犯错。

  • 右外连接（right outer join 或right join）：与左外连接相反。

  • 全外连接 full join mysql不支持 oracle支持

  • 等值连接：使用等号来作为连接条件(与内连接连接效果是相同)

  • 自然连接：natural join，通过在两张表里寻找列名和数据类型都相同的字段（长度不管），然后再根据这些相同的字段进行连接（内连接）。并返回所有符合条件的结果。

  • 不等值连接：就是在连接条件中使用除等号以外的其他比较运算符，例如：on c.id between s.XXX and s.XXX;注意：可以使用using关键字简化连接：不等值使用条件：1.查询必须是等值连接 2.等值连接中的列必须具有相同的名称和数据类型。

  • 笛卡尔积

  • 内连接 [inner] join,取两张表交集 --(默认的连接方式)

  • 外连接 outer join

  • 交叉连接 cross join 即两表的所有数据行的笛卡尔积。

• 联合查询

  • UNION 会去掉重复项(默认)

  • UNION ALL 不会去掉重复项

• 子查询

笛卡尔积

笛卡尔积是把表中所有的记录作乘积操作，生成大量的结果， 而通常结果中可用的值有限。笛卡尔积出现的原因多种多样， 通常是由于连接条件缺失造成的。

示例: 查询出的结果条数为两张表的乘积

SELECT student.name, age, c_class,t_class.name 
FROM student, t_class

内连接

内连接 inner join

内连接和外连接的区别：内连接就是满足连接条件的结果集，这是相对于外连接而言。外连接即使找不到满足条件的记录，另一方的记录还是要输出

释义：内连接就是两张表的并集

示例，查询 所有用户和他们的班级信息

select * 
from student inner join t_class  on student.c_class = t_class.id;

在ON子句中写连接条件

• on和where的区别：

  • on...and...：on只能用于多表查询语句中进行关系条件限制，且on先执行，where后执行

  • where...and...：where是SQL条件判断，也可以对on之后的记录进行筛选

等值连接

• 等值连接又称简单连接或内连接，是内连接的子集。就是当两个表的公共字段相等的时候把两个表连接在一起。公共字段是两个表中有相同含 义的列。

补充说明: INNER JOIN可以不等：select * from student inner join t_class on student.c_class<>t_class.id; 从集合论角度看：等值连接是内连接的子集

• 等值连接的语法结构

SELECT table1.column, table2.column 
FROM table1, table2 
WHERE table1.column1 = table2.column2;

– 在 WHERE 子句中写连接条件 – 当多个表中有重名列时，必须在列的名字前加上表名作为 前缀

等值连接的示例：

SELECT student.name, age, c_class,t_class.name 
FROM student, t_class
WHERE student.c_class=t_class.id

等值连接中的记录筛选示例：

• 多表连接中，记录筛选语句同样写在WHERE语句中，用逻辑 AND和连接判断语句写在一起。

SELECT student.name, age, c_class,t_class.name 
FROM student, t_class
WHERE student.c_class=t_class.id AND age LIKE '%2%';

表别名

• 等值连接表别名示例

SELECT a.name, age, c_class,b.name 
FROM student a, t_class b
WHERE a.c_class=b.id;

这里 a，b就是表别名，它们不存在真实表，就是给当前表起的一个昵称，一个代号而已

• 关于表别名需要注意以下几点：

– 表别名长度不超过30个字符; – 表别名定义在FROM子句中；– 如果已经定义了表别名，那么只能使用表别名而不能使用 原表名；– 表别名的有效范围只是当前语句。

• SQL语句的书写顺序是：– SELECT FROM WHERE ORDER BY

• 而实际的执行顺序是：– FROM WHERE SELECT ORDER BY

两表以上的多表连接

原理跟两张表连接是一样的 • 三张表联合查询。

SELECT e.last_name, e.job_id, e.department_id, d.department_name,l.city 
FROM employees e, departments d, locations l 
WHERE e.department_id = d.department_id 
AND d.location_id = l.location_id 
AND l.city IN ('Southlake','Oxford');

自然连接

• NATURAL JOIN是SQL99中新增语句，连接条件是两个表中所 有的值和数据类型都相同的同名列。如果仅列名相同而数据类 型不同，则报错。

SELECT a.name, age, c_class,b.name 
FROM student a NATURAL JOIN t_class b

USING子句

• USING (column_name)子句也是SQL99新增子句，可以较灵 活的完成在多表连接，多列列名相同时，使用其中的一列同名 列连接，而不需写连接条件的功能。

• USING子句和NATURAL JOIN不能在一条语句中同时书写。

SELECT a.name, age, c_class,b.name 
FROM student a  JOIN t_class b using(id)

不等值连接

• 除了等号之外，在表连接语句中还可以使用其它的运算符。这 种使用除等号之外运算符的连接语句被称为不等值连接。

• 使用不等值连接查询可以查询两个表中具有非等值关系的数据。操作符可以是比较运算符，也可以是between···and 或者是in、 like。

示例：

SELECT a.name, age, c_class,b.name 
FROM student a,t_class b
where a.c_class in (1,2)

外连接

• 为了查找到所有记录，包括没有匹配的记录，需要用外连接语句来实现。

左外连接

• 左外连接：以左表为基准，左表中的每个记录都必须显示，即使右表 没有与之相匹配的记录。• 在LEFT [OUTER] JOIN中，会返回所有左边表中的行，即使在 右边的表中没有可对应的列值

例：查询用户信息，不管有没有班级
SELECT a.name, age, c_class,b.name 
FROM student a LEFT OUTER JOIN t_class b
on a.c_class = b.id

右外连接

• 右外连接：以右表为基准，右表中的每个记录都必须显示，即使左表 中没有与之相匹配的记录。• RIGHT OUTER JOIN，会返回所有右边表中的行，即使在左 边的表中没有可对应的列值。

例：查询班级信息，不管有没有用户
SELECT a.name, age, c_class,b.name 
FROM student a RIGHT OUTER JOIN t_class b
on a.c_class = b.id

注：当主表对应其他表的数据为多条时，会出现多条重复记录，所以要尽量保证主表对应其他表的的数据只存在一条

交叉连接

SELECT * FROM student CROSS JOIN t_class;

集合运算符Union和Union ALL

• UNION 操作符返回两个查询的结果集的并集（去掉重复值 后的结果）

• UNION ALL 操作符返回两个查询的结果集的并集以及两 个结果集的重复部分（不去掉重复值的结果）

UNION (联合)运算

– 用 UNION 运算从 多表返回所有行，但除去任何重复的行。

– 原则 • 被选择的列数和列的数据类型必须是与所有用在查询中的 SELECT 语句一致。列的名字不必相同。• 联合运算在所有被选择的列上进行。• 如果多个查询结果都有NULL值，整个结果中只包含一个 NULL值 • IN 运算有比 UNION 运算高的优先级。• 在默认情况下，输出以 SELECT 子句的第一列的升序排序。• 每个查询不能包含自己的Order by语句，只能在联合之后使 用Order by 。

示例：• 列出年龄大于22或者班级为2，3的用户

SELECT id, name,age,c_class FROM student Where age>22 
Union 
SELECT id, name,age,c_class FROM student Where c_class in (2,3)

Union ALL 操作符

• 完全联合 (UNION ALL) 运算

– 原则 • 和联合不同，重复的行不被过滤，并且默认情况下输出 不排序。• 如果每个查询结果中都有NULL值，也不被去掉。– 注意：使用UNION ALL会比UNION的速度快，因为省去了 去掉重复记录的时间。

示例：• 列出年龄大于22或者班级为2，3的用户(不去重)

SELECT id, name,age,c_class FROM student Where age>22 
Union ALL
SELECT id, name,age,c_class FROM student Where c_class in (2,3)

 

/   总结   /

 

本文主要讲了多表连接查询相关的知识，实战中连接查询也会用的比较频繁。

 

往期推荐：

如何入门做软件开发

为什么我不推荐入行程序员

做全栈开发很难吗

关注我的公众号，学习技术或投稿

长按上图，识别图中二维码即可关注