likes
comments
collection
share

数据库回顾(三) 多表查询概述

作者站长头像
站长
· 阅读数 10

大家好！ 我是慕歌，一只想教你学习 后端技术的野生coder！ 欢迎来到慕歌的数据库系列教程，希望通过这个教程带大家深入学习数据库技术，该教程所有知识点均来源于本人的日常学习与开发中，如有疑问请与我联系！

前言

在上一节的分享中，慕歌为大家带来了关于数据运算与分页知识，用于帮助处理数据库数据，进行进一步的整合与处理。其中使用比较频繁的是基础数据处理逻辑（+，-，*，/，%），以及一些简单的数据判别函数，特别需要注意的点是在数据处理中注意可能存在的空值（null）问题，空值的运算不同于在Java语言中的运算，与null值的运算都会变成null ，而无法进行数据处理。最后是数据排序，分页处理，其中涉及排序规则，以及分页规则是需要我们了解的。那么这一节，我们将围绕多表查询进行学习，这一节的内容会比较复杂，需要搭配示例进行综合理解。

准备工作

建立数据表

这里需要利用三张表进行后续的演示工作，需要大家在自己的navicat 中建立这样的三张表，并填入一些数据。

#部门表
DROP TABLE IF EXISTS `departments`;
CREATE TABLE `departments`  (
  `department_id` int(0) NOT NULL,
  `department_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `manger_id` int(0) NULL DEFAULT NULL,
  `location_id` int(0) NULL DEFAULT NULL,
  PRIMARY KEY (`department_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

#员工表
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees`  (
  `employee_id` int(0) NOT NULL AUTO_INCREMENT,
  `frist_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `last_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `phone_number` int(0) NULL DEFAULT NULL,
  `job_id` int(0) NULL DEFAULT NULL,
  `salary` decimal(10, 2) NULL DEFAULT NULL,
  `commission_pct` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `manager_id` int(0) NULL DEFAULT NULL,
  `department_id` int(0) NULL DEFAULT NULL,
  PRIMARY KEY (`employee_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

#位置表
DROP TABLE IF EXISTS `locations`;
CREATE TABLE `locations`  (
  `location_id` int(0) NOT NULL,
  `street_adress` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `postal_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `city` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `state_province` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `country_id` int(0) NULL DEFAULT NULL,
  PRIMARY KEY (`location_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

多表查询

概述

多表查询又称之为关联查询，指两个或者更多个表一起完成查询操作。这两个或多个表具有相关性，且具有关联字段，则这多个表可以建立起多表查询。

基础多表查询

那么一个标准的查询应该怎麽书写，则需要引入where 查询建立两个表之间的关联

#简单连接1
SELECT t1.col,t2.col FROM t1,t2 WHERE t1.col = t2.col;
# select 后的内容可以是来源于两个表中的任何字段
# where 字段后则是两个表中能够建立连接的字段
# 通过以上两个字段，即可建立起满足某些条件的相关数据

#简单连接2
SELECT t1.col, t2.col,t3.col
FROM t1
    JOIN t2 ON #t1 和 t2 的连接条件
        JOIN t3 ON #t2 和 t3 的连接条件

#示例：查询员工的姓名及其部门名称
SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;

以上就是最简单的多表关联查询，需要注意的点为，两个表中出现相同字段的时候，需要指明需要查询的是哪一个字段，否则sql 执行会报错，所以提出一种sql 规范：表有相同列时，列名之前加上表名前缀

复杂多表查询

分类1：

等值连接 VS 非等值连接：他们的区别在于连接条件，连接的条件为两个数据表之间的关联字段的值是否相同。如果相同，则是等值连接，为一个区间，则是非等值连接

#等值连接
SELECT t1.name FROM table_first AS t1 , table_second AS t2 WHERE t1.id = t2.id; 
#条件为两个值相等

#非等值连接
SELECT t1.name FROM table_first AS t1 , table_second AS t2 WHERE t1.id < t2.id; 
#条件为两个值不相等

#当连接N个表的时候，需要使用N-1个连接条件

分类2：

自连接 VS 非自连接：自连接实际使用的是一张表的数据进行处理，通过利用一张表中的条件，获取想要的数据，非自连接就是多表间的连接

#自连接查询
SELECT t1.col1,t2.col2 FROM t1,t2 where t1.col1 = t2.col2;
#这里的表t1，t2实际是一张表，只是使用不同的列数据进行限制

分类3：

内连接 VS 外连接：内连接所查询的数据中只包含符合两表条件的数据，外连接在满足连接条件的行外还包含左（右）表不满足条件的列

#内连接与基本简单连接相同，返回的是两个表中相同的数据

#外连接可分为左连接，右连接
#左连接
SELECT t1.col,t2.col
FROM t1 LEFT JOIN t2
ON 关联条件
WHERE 等其他子句;

#右连接
SELECT t1.col,t2.col
FROM t1 RIGHT JOIN t2
ON 关联条件
WHERE 等其他子句;

#示例：
#从员工表中获取全部的员工表数据，以及关联的职位表信息
SELECT e.last_name, e.department_id, d.department_name
FROM   employees e LEFT OUTER JOIN departments d
ON   (e.department_id = d.department_id) ;

集合查询

在某些情况下，我们需要将查询到的数据进行合并操作，利用union 关键字可以将多个结果集合并成单个结果集，但是对应的列必须相同

#连接
SELECT col1,... FROM t1
UNION [ALL]
SELECT col1,... FROM t2

#UNION 去除两个查询结果中的重复部分

这张表就是经典的表连接问题，通过组合不同的查询结果就可以得到我们想要的最终结果集数据库回顾(三) 多表查询概述

示例：
#左上图：左外连接
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;

#左中图：A - A∩B
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL

#左下图：满外连接
# 左中图 + 右上图  A∪B
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL  #没有去重操作，效率高
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

注意：

表连接的约束条件中：

  • where 适用于所有查询条件
  • on 连接条件只能适用于join 关键字一起使用，用于约束连接
  • using 只能与join 一起使用，用于约束关联字段的值相等

在使用多表查询的过程中需要注意控制表连接数量，表连接越多，需要的性能资源也就越多

结语

这一节的分享到这里就结束了，下一节中将带来SQL函数的分享学习！如果您觉得本文不错，欢迎点赞支持，您的关注是我坚持的动力！