MySQL 多表查询与连接

技术老小子 2024-04-01

在数据库中，多表查询与连接是非常常见且重要的操作。通过多表查询与连接，我们可以将多个表中的数据进行关联，从而获取更加丰富和准确的结果。本文将介绍多表查询与连接的概念、常见的连接类型以及一些实例。

表结构与测试数据脚本

在开始之前，我们先定义一些示例数据的表结构和测试数据脚本。以下是两个示例表的结构：

表1：学生表（students）

字段名	数据类型	说明
id	INT	学生ID
name	VARCHAR(50)	学生姓名
age	INT	学生年龄
class_id	INT	所属班级的ID

表2：班级表（classes）

字段名	数据类型	说明
id	INT	班级ID
name	VARCHAR(50)	班级名称
teacher	VARCHAR(50)	班主任姓名

以下是创建示例表以及插入测试数据的SQL脚本：

-- 创建学生表
CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  class_id INT
);


-- 创建班级表
CREATE TABLE classes (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  teacher VARCHAR(50)
);


-- 插入测试数据
INSERT INTO students (id, name, age, class_id) VALUES
(1, '张三', 18, 1),
(2, '李四', 19, 2),
(3, '王五', 20, 1),
(4, '赵六', 18, 2);


INSERT INTO classes (id, name, teacher) VALUES
(1, '一班', '张老师'),
(2, '二班', '李老师');
复制

复制

连接类型

在多表查询与连接中，常见的连接类型有以下几种：

1. 内连接（INNER JOIN）

内连接是最常用的连接类型，它会返回两个表中满足连接条件的交集。语法如下：

SELECT 列名
FROM 表1
INNER JOIN 表2
ON 连接条件;
复制

复制

例如，我们可以通过内连接查询学生表和班级表中学生所属的班级信息：

SELECT students.name, classes.name AS class_name
FROM students
INNER JOIN classes
ON students.class_id = classes.id;
复制

复制

2. 左连接（LEFT JOIN）

左连接会返回左表中的所有记录，以及满足连接条件的右表记录。如果右表中没有匹配的记录，则返回NULL。语法如下：

SELECT 列名
FROM 表1
LEFT JOIN 表2
ON 连接条件;
复制

复制

例如，我们可以通过左连接查询学生表中的所有学生以及他们所属的班级信息：

SELECT students.name, classes.name AS class_name
FROM students
LEFT JOIN classes
ON students.class_id = classes.id;
复制

复制

3. 右连接（RIGHT JOIN）

右连接会返回右表中的所有记录，以及满足连接条件的左表记录。如果左表中没有匹配的记录，则返回NULL。语法如下：

SELECT 列名
FROM 表1
RIGHT JOIN 表2
ON 连接条件;
复制

复制

例如，我们可以通过右连接查询班级表中的所有班级以及属于这些班级的学生信息：

SELECT students.name, classes.name AS class_name
FROM students
RIGHT JOIN classes
ON students.class_id = classes.id;
复制

复制

4. 全连接（FULL JOIN）

全连接会返回左表和右表中的所有记录，如果某个表中没有匹配的记录，则返回NULL。语法如下：

SELECT 列名
FROM 表1
FULL JOIN 表2
ON 连接条件;
复制

复制

需要注意的是，MySQL并不直接支持全连接，但我们可以通过左连接和右连接的组合来模拟实现全连接。例如：

SELECT students.name, classes.name AS class_name
FROM students
LEFT JOIN classes
ON students.class_id = classes.id
UNION
SELECT students.name, classes.name AS class_name
FROM students
RIGHT JOIN classes
ON students.class_id = classes.id;
复制

复制

示例

以下是一些使用不同连接类型的示例：

示例1：查询学生表和班级表中学生所属的班级信息

SELECT students.name, classes.name AS class_name
FROM students
INNER JOIN classes
ON students.class_id = classes.id;
复制

复制

示例2：查询学生表中的所有学生以及他们所属的班级信息

SELECT students.name, classes.name AS class_name
FROM students
LEFT JOIN classes
ON students.class_id = classes.id;
复制

复制

示例3：查询班级表中的所有班级以及属于这些班级的学生信息

SELECT students.name, classes.name AS class_name
FROM students
RIGHT JOIN classes
ON students.class_id = classes.id;
复制

复制

示例4：查询学生表和班级表中所有记录

SELECT students.name, classes.name AS class_name
FROM students
LEFT JOIN classes
ON students.class_id = classes.id
UNION
SELECT students.name, classes.name AS class_name
FROM students
RIGHT JOIN classes
ON students.class_id = classes.id;
复制

复制

结论

多表查询与连接是数据库中非常重要的操作，通过连接不同的表，我们可以获取到更加丰富和准确的查询结果。本文介绍了多表查询与连接的概念、常见的连接类型以及一些示例。希望本文对你理解多表查询与连接有所帮助。

文章转载自技术老小子，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。