一、关系类型与实现方式

1. 一对多（One-to-Many）

定义：一个表中的一条记录可以关联到另一个表中的多条记录，而另一表中的每条记录仅关联到前者的一条记录。

实现方式：

• 多的一方（Many side）建立外键，指向一的一方（One side）的主键。

示例：

假设有两个表：authors（作者）和 books（书籍）。一个作者可以有多本书，但每本书只有一个作者。

创建表结构：

CREATE TABLE authors (
    author_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE books (
    book_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    author_id INT,
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

插入示例数据：

INSERT INTO authors (name) VALUES ('J.K. Rowling'), ('George R.R. Martin');

INSERT INTO books (title, author_id) VALUES 
('Harry Potter and the Philosopher\'s Stone', 1),
('Harry Potter and the Chamber of Secrets', 1),
('A Game of Thrones', 2),
('A Clash of Kings', 2);

查询示例：获取每本书的作者名称。

SELECT books.title, authors.name
FROM books
JOIN authors ON books.author_id = authors.author_id;

2. 多对多（Many-to-Many）

定义：两个表中的记录可以相互关联，每一方的记录可以关联到另一方的多条记录。

实现方式：

• 建立一个中间表，该表包含两个外键，分别指向两个关联表的主键。

示例：

假设有两个表：students（学生）和 courses（课程）。一个学生可以选修多门课程，一门课程也可以被多个学生选修。

创建表结构：

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE courses (
    course_id INT PRIMARY KEY AUTO_INCREMENT,
    course_name VARCHAR(100) NOT NULL
);

CREATE TABLE student_courses (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

插入示例数据：

INSERT INTO students (name) VALUES ('Alice'), ('Bob'), ('Charlie');

INSERT INTO courses (course_name) VALUES ('Mathematics'), ('Physics'), ('Literature');

INSERT INTO student_courses (student_id, course_id) VALUES 
(1, 1), (1, 2),
(2, 1), (2, 3),
(3, 2), (3, 3);

查询示例：获取每个学生选修的课程列表。

SELECT students.name, GROUP_CONCAT(courses.course_name) AS courses
FROM students
JOIN student_courses ON students.student_id = student_courses.student_id
JOIN courses ON student_courses.course_id = courses.course_id
GROUP BY students.name;

3. 一对一（One-to-One）

定义：两个表中的每条记录一一对应。

实现方式：

• 在其中一方表中建立外键，指向另一方表的主键，并通常设置为唯一约束，确保一对一关系。

示例：

假设有两个表：users（用户）和 user_profiles（用户资料）。每个用户只有一个资料，每个资料仅对应一个用户。

创建表结构：

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE user_profiles (
    profile_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT UNIQUE,
    address VARCHAR(255),
    phone VARCHAR(20),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

插入示例数据：

INSERT INTO users (username) VALUES ('john_doe'), ('jane_smith');

INSERT INTO user_profiles (user_id, address, phone) VALUES 
(1, '123 Main St', '555-1234'),
(2, '456 Elm St', '555-5678');

查询示例：获取用户及其资料。

SELECT users.username, user_profiles.address, user_profiles.phone
FROM users
JOIN user_profiles ON users.user_id = user_profiles.user_id;

二、多表查询的使用方式

多表查询通常通过 JOIN 操作实现，主要包括以下几种类型：

1. 内连接（INNER JOIN）

返回两个表中匹配的记录。

语法：

SELECT columns
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

示例：

获取所有有书籍的作者及其书籍。

SELECT authors.name, books.title
FROM authors
INNER JOIN books ON authors.author_id = books.author_id;

2. 左连接（LEFT JOIN）

返回左表中的所有记录，以及右表中匹配的记录。如果右表没有匹配，则结果为 NULL。

语法：

SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

示例：

获取所有作者及其书籍，即使某些作者没有书籍。

SELECT authors.name, books.title
FROM authors
LEFT JOIN books ON authors.author_id = books.author_id;

3. 右连接（RIGHT JOIN）

返回右表中的所有记录，以及左表中匹配的记录。如果左表没有匹配，则结果为 NULL。

语法：

SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

示例：

获取所有书籍及其作者，即使某些书籍没有对应的作者。

SELECT books.title, authors.name
FROM books
RIGHT JOIN authors ON books.author_id = authors.author_id;

4. 全外连接（FULL OUTER JOIN）

MySQL 不直接支持全外连接，但可以通过 UNION 模拟实现。

示例：

获取所有作者和所有书籍，即使某些作者没有书籍，某些书籍没有作者。

SELECT authors.name, books.title
FROM authors
LEFT JOIN books ON authors.author_id = books.author_id
UNION
SELECT authors.name, books.title
FROM authors
RIGHT JOIN books ON authors.author_id = books.author_id;

5. 自连接（SELF JOIN）

将一个表与自身进行连接，通常用于表中存在层级或递归关系的情况。

示例：

假设有一个 employees 表，其中包含 manager_id 指向同一表中的 employee_id，表示员工与其经理的关系。

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);

INSERT INTO employees (name, manager_id) VALUES 
('Alice', NULL),
('Bob', 1),
('Charlie', 1),
('David', 2);

查询示例：获取每个员工及其经理的名称。

SELECT e1.name AS Employee, e2.name AS Manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id;

三、子查询

子查询（Subquery）是在一个查询内部嵌套的查询。根据子查询结果的不同，子查询可以分为以下几种类型：

1. 标量子查询（Scalar Subquery）

定义：子查询的结果为单个值（单行单列）。

使用场景：用于需要一个具体值的地方，如 SELECT 列、WHERE 条件或 HAVING 条件中。

语法：

SELECT column1, 
       (SELECT single_value FROM table2 WHERE condition) AS alias
FROM table1
WHERE column2 = (SELECT single_value FROM table2 WHERE condition);

示例：

获取每个学生的姓名及其所属班级的名称。

SELECT students.name,
       (SELECT class_name FROM classes WHERE classes.class_id = students.class_id) AS class_name
FROM students;

2. 列子查询（Column Subquery）

定义：子查询的结果为一列（多行）。

使用场景：用于 WHERE 子句中的 IN、NOT IN、ANY、SOME、ALL 等操作。

常用操作符：

• IN：检查某值是否在子查询返回的结果集之中。
• NOT IN：检查某值是否不在子查询返回的结果集之中。
• ANY / SOME：检查某值是否满足子查询返回结果中的任一值。
• ALL：检查某值是否满足子查询返回结果中的所有值。

语法：

SELECT column1, column2
FROM table1
WHERE column3 IN (SELECT columnX FROM table2 WHERE condition);

SELECT column1, column2
FROM table1
WHERE column3 NOT IN (SELECT columnX FROM table2 WHERE condition);

SELECT column1, column2
FROM table1
WHERE column3 > ANY (SELECT columnX FROM table2 WHERE condition);

SELECT column1, column2
FROM table1
WHERE column3 > ALL (SELECT columnX FROM table2 WHERE condition);

示例：

使用 IN：

获取选修“Mathematics”课程的所有学生姓名。

SELECT name
FROM students
WHERE student_id IN (
    SELECT student_id
    FROM student_courses
    WHERE course_id = 1
);

使用 NOT IN：

获取没有选修“Mathematics”课程的所有学生姓名。

SELECT name
FROM students
WHERE student_id NOT IN (
    SELECT student_id
    FROM student_courses
    WHERE course_id = 1
);

使用 ANY / SOME：

获取选修至少一门“Physics”或“Chemistry”课程的学生。

SELECT name
FROM students
WHERE student_id = ANY (
    SELECT student_id
    FROM student_courses
    WHERE course_id IN (2, 4)
);

使用 ALL：

获取选修所有课程的学生。

SELECT name
FROM students
WHERE student_id = ALL (
    SELECT student_id
    FROM student_courses
);

3. 行子查询（Row Subquery）

定义：子查询的结果为一行（多列）。

使用场景：用于需要与多列进行比较的地方，如 WHERE 子句中的元组比较。

语法：

SELECT column1, column2
FROM table1
WHERE (column3, column4) = (SELECT columnX, columnY FROM table2 WHERE condition);

示例：

假设每个学生有一个唯一的姓名和班级组合，获取特定学生的信息。

SELECT *
FROM students
WHERE (name, class_id) = (
    SELECT name, class_id
    FROM students
    WHERE student_id = 1
);

4. 表子查询（Table Subquery）

定义：子查询的结果为多行多列，相当于一个临时表。

使用场景：通常在 FROM 子句中使用，作为临时表进行进一步的查询。

语法：

SELECT a.column1, b.column2
FROM table1 a
JOIN (SELECT columnX, columnY FROM table2 WHERE condition) b
ON a.column = b.columnX;

示例：

获取每个班级的学生数量。

SELECT classes.class_name, student_counts.count
FROM classes
JOIN (
    SELECT class_id, COUNT(*) AS count
    FROM students
    GROUP BY class_id
) AS student_counts ON classes.class_id = student_counts.class_id;

四、联合查询与去重

1. 联合查询（UNION 和 UNION ALL）

定义：UNION 和 UNION ALL 用于将多个 SELECT 查询的结果组合成一个结果集。

• UNION：合并两个或多个 SELECT 语句的结果，自动去除重复的记录。
• UNION ALL：合并两个或多个 SELECT 语句的结果，包括重复的记录。

使用场景：

• 当需要从不同表或相同表的不同条件下获取数据并组合在一起时使用。

语法：

SELECT column1, column2, ...
FROM table1
WHERE condition1
UNION [ALL]
SELECT column1, column2, ...
FROM table2
WHERE condition2;

示例：

假设有两个表 domestic_customers 和 international_customers，希望获取所有客户的姓名。

SELECT name FROM domestic_customers
UNION
SELECT name FROM international_customers;

上述查询将返回所有国内和国际客户的姓名，并去除重复的姓名。

如果希望包括重复的姓名，可以使用 UNION ALL：

SELECT name FROM domestic_customers
UNION ALL
SELECT name FROM international_customers;

2. 去重操作

定义：在查询结果中移除重复的记录。

实现方式：

• 使用 DISTINCT 关键字。
• 使用 GROUP BY 子句。
• 结合 UNION 来自动去重。

示例：

使用 DISTINCT：

获取所有作者的唯一姓名（假设有重复姓名的情况）。

SELECT DISTINCT name
FROM authors;

使用 GROUP BY：

获取每个作者的姓名及其书籍数量，自动按姓名分组，从而去除重复姓名。

SELECT name, COUNT(book_id) AS book_count
FROM authors
JOIN books ON authors.author_id = books.author_id
GROUP BY name;

结合 UNION 去重：

获取所有作者和书籍标题的唯一组合。

SELECT authors.name, books.title
FROM authors
JOIN books ON authors.author_id = books.author_id
UNION
SELECT authors.name, books.title
FROM authors
JOIN books ON authors.author_id = books.author_id;

上述查询通过 UNION 去除了重复的（作者名，书籍标题）组合。

五、子查询与联合查询实例综合讲解

结合上述关系类型、子查询和联合查询，以下是一个综合性的数据库设计与查询实例。

实例场景

设计一个学校数据库，包含学生、课程、教师和班级的信息。具体要求：

• 每个学生属于一个班级（一对多）。
• 每个班级有多个学生。
• 学生可以选修多门课程，课程也可以被多个学生选修（多对多）。
• 每门课程由一个教师教授（一对多）。
• 每个教师教授多门课程。
• 每个教师有一个详细资料（如办公室地址等，一对一）。

创建表结构

-- 班级表
CREATE TABLE classes (
    class_id INT PRIMARY KEY AUTO_INCREMENT,
    class_name VARCHAR(50) NOT NULL
);

-- 学生表
CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    class_id INT,
    FOREIGN KEY (class_id) REFERENCES classes(class_id)
);

-- 教师表
CREATE TABLE teachers (
    teacher_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100)
);

-- 教师资料表
CREATE TABLE teacher_profiles (
    profile_id INT PRIMARY KEY AUTO_INCREMENT,
    teacher_id INT UNIQUE,
    office VARCHAR(100),
    phone VARCHAR(20),
    FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id)
);

-- 课程表
CREATE TABLE courses (
    course_id INT PRIMARY KEY AUTO_INCREMENT,
    course_name VARCHAR(100) NOT NULL,
    teacher_id INT,
    FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id)
);

-- 学生选课中间表
CREATE TABLE student_courses (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

插入示例数据

-- 插入班级
INSERT INTO classes (class_name) VALUES ('Class A'), ('Class B');

-- 插入学生
INSERT INTO students (name, class_id) VALUES 
('Alice', 1),
('Bob', 1),
('Charlie', 2),
('David', 2);

-- 插入教师
INSERT INTO teachers (name, email) VALUES 
('Prof. Smith', 'smith@school.edu'),
('Prof. Johnson', 'johnson@school.edu');

-- 插入教师资料
INSERT INTO teacher_profiles (teacher_id, office, phone) VALUES 
(1, 'Room 101', '555-0001'),
(2, 'Room 102', '555-0002');

-- 插入课程
INSERT INTO courses (course_name, teacher_id) VALUES 
('Mathematics', 1),
('Physics', 1),
('Literature', 2),
('Chemistry', 2);

-- 插入学生选课
INSERT INTO student_courses (student_id, course_id) VALUES 
(1, 1), (1, 2),
(2, 1), (2, 3),
(3, 2), (3, 4),
(4, 3), (4, 4);

复杂查询示例

1. 获取每个班级的学生及其选修的课程

   SELECT classes.class_name, students.name AS student_name, GROUP_CONCAT(courses.course_name) AS courses
   FROM classes
   JOIN students ON classes.class_id = students.class_id
   JOIN student_courses ON students.student_id = student_courses.student_id
   JOIN courses ON student_courses.course_id = courses.course_id
   GROUP BY classes.class_name, students.name;

2. 获取每个教师教授的课程及选修该课程的学生

   SELECT teachers.name AS teacher_name, courses.course_name, GROUP_CONCAT(students.name) AS students
   FROM teachers
   JOIN courses ON teachers.teacher_id = courses.teacher_id
   JOIN student_courses ON courses.course_id = student_courses.course_id
   JOIN students ON student_courses.student_id = students.student_id
   GROUP BY teachers.name, courses.course_name;

3. 获取每个教师的详细资料

   SELECT teachers.name, teachers.email, teacher_profiles.office, teacher_profiles.phone
   FROM teachers
   JOIN teacher_profiles ON teachers.teacher_id = teacher_profiles.teacher_id;

4. 获取没有选修任何课程的学生

   SELECT students.name
   FROM students
   LEFT JOIN student_courses ON students.student_id = student_courses.student_id
   WHERE student_courses.course_id IS NULL;

5. 使用 UNION 获取所有参与的人员（包括学生和教师）的姓名列表

   SELECT name FROM students
   UNION
   SELECT name FROM teachers;

6. 使用 UNION ALL 获取所有参与的人员（包括学生和教师）的姓名列表，包括重复姓名

   SELECT name FROM students
   UNION ALL
   SELECT name FROM teachers;

7. 使用子查询获取每个学生所属班级的名称

   SELECT name, 
          (SELECT class_name FROM classes WHERE classes.class_id = students.class_id) AS class_name
   FROM students;

8. 使用表子查询获取每个班级的学生数量

   SELECT classes.class_name, student_counts.count
   FROM classes
   JOIN (
       SELECT class_id, COUNT(*) AS count
       FROM students
       GROUP BY class_id
   ) AS student_counts ON classes.class_id = student_counts.class_id;

四、子查询详细讲解

1. 标量子查询（Scalar Subquery）

定义：子查询的结果为单个值（单行单列）。

使用场景：用于需要一个具体值的地方，如 SELECT 列、WHERE 条件或 HAVING 条件中。

语法：

SELECT column1, 
       (SELECT single_value FROM table2 WHERE condition) AS alias
FROM table1
WHERE column2 = (SELECT single_value FROM table2 WHERE condition);

示例：

获取每个学生的姓名及其所属班级的名称。

SELECT students.name,
       (SELECT class_name FROM classes WHERE classes.class_id = students.class_id) AS class_name
FROM students;

2. 列子查询（Column Subquery）

定义：子查询的结果为一列（多行）。

使用场景：用于 WHERE 子句中的 IN、NOT IN、ANY、SOME、ALL 等操作。

常用操作符：

• IN：检查某值是否在子查询返回的结果集之中。
• NOT IN：检查某值是否不在子查询返回的结果集之中。
• ANY / SOME：检查某值是否满足子查询返回结果中的任一值。
• ALL：检查某值是否满足子查询返回结果中的所有值。

语法：

SELECT column1, column2
FROM table1
WHERE column3 IN (SELECT columnX FROM table2 WHERE condition);

SELECT column1, column2
FROM table1
WHERE column3 NOT IN (SELECT columnX FROM table2 WHERE condition);

SELECT column1, column2
FROM table1
WHERE column3 > ANY (SELECT columnX FROM table2 WHERE condition);

SELECT column1, column2
FROM table1
WHERE column3 > ALL (SELECT columnX FROM table2 WHERE condition);

示例：

使用 IN：

获取选修“Mathematics”课程的所有学生姓名。

SELECT name
FROM students
WHERE student_id IN (
    SELECT student_id
    FROM student_courses
    WHERE course_id = 1
);

使用 NOT IN：

获取没有选修“Mathematics”课程的所有学生姓名。

SELECT name
FROM students
WHERE student_id NOT IN (
    SELECT student_id
    FROM student_courses
    WHERE course_id = 1
);

使用 ANY / SOME：

获取选修至少一门“Physics”或“Chemistry”课程的学生。

SELECT name
FROM students
WHERE student_id = ANY (
    SELECT student_id
    FROM student_courses
    WHERE course_id IN (2, 4)
);

使用 ALL：

获取选修所有课程的学生。

SELECT name
FROM students
WHERE student_id = ALL (
    SELECT student_id
    FROM student_courses
);

3. 行子查询（Row Subquery）

定义：子查询的结果为一行（多列）。

使用场景：用于需要与多列进行比较的地方，如 WHERE 子句中的元组比较。

语法：

SELECT column1, column2
FROM table1
WHERE (column3, column4) = (SELECT columnX, columnY FROM table2 WHERE condition);

示例：

假设每个学生有一个唯一的姓名和班级组合，获取特定学生的信息。

SELECT *
FROM students
WHERE (name, class_id) = (
    SELECT name, class_id
    FROM students
    WHERE student_id = 1
);

4. 表子查询（Table Subquery）

定义：子查询的结果为多行多列，相当于一个临时表。

使用场景：通常在 FROM 子句中使用，作为临时表进行进一步的查询。

语法：

SELECT a.column1, b.column2
FROM table1 a
JOIN (SELECT columnX, columnY FROM table2 WHERE condition) b
ON a.column = b.columnX;

示例：

获取每个班级的学生数量。

SELECT classes.class_name, student_counts.count
FROM classes
JOIN (
    SELECT class_id, COUNT(*) AS count
    FROM students
    GROUP BY class_id
) AS student_counts ON classes.class_id = student_counts.class_id;

五、最佳实践

1. 使用合适的索引：在外键列和经常用于查询的列上建立索引，可以显著提高查询性能。

2. 合理设计外键约束：确保数据的完整性，避免数据冗余和不一致。

3. 避免过度使用 JOIN：过多的表连接会影响查询性能，应根据实际需求合理设计查询。

4. 利用视图简化复杂查询：对于经常使用的复杂查询，可以创建视图，方便后续使用。

5. 使用事务管理：在进行多表操作时，使用事务确保数据的一致性和完整性。

6. 选择合适的联合查询：

   • 使用 UNION 时，注意去重带来的性能开销。
   • 在确定不需要重复记录时，优先使用 UNION，否则使用 UNION ALL 以提高性能。

7. 优化去重操作：

   • 使用 DISTINCT 和 GROUP BY 时，确保仅在必要时使用，以减少查询开销。
   • 在数据量大时，考虑通过其他方式（如预处理数据）减少重复记录的生成。

8. 合理使用子查询与连接：

   • 子查询在某些情况下可能效率较低，考虑使用 JOIN 替代。
   • 对于复杂的逻辑，子查询可以提高查询的可读性和维护性。