MySQL高级查询和数据操作教程（第二阶段）-CSDN博客

本文链接：https://blog.csdn.net/m0_54490473/article/details/140675045

MySQL高级查询和数据操作教程

在数据库管理系统中，MySQL是最流行和广泛使用的开源关系数据库。掌握中级至高级的MySQL查询和数据操作技能，能够帮助你更高效地管理和操作数据。本教程将详细讲解MySQL的高级查询技巧，包括联接、子查询、聚合函数、数据分组、索引、视图以及事务管理，伴随实用的案例和详细的讲解。

一、复杂查询

1. 联接（JOIN）

JOIN操作允许我们从多个表中组合并查询数据。主要的JOIN类型包括内联接、外联接、自联接。

1.1 内联接（INNER JOIN）

内联接只返回两个表中匹配的行。

示例：
假设我们有两个表：students（学生表）和courses（课程表）。

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE courses (
    id INT PRIMARY KEY,
    student_id INT,
    course_name VARCHAR(50),
    FOREIGN KEY (student_id) REFERENCES students(id)
);

INSERT INTO students (id, name) VALUES (1, 'Alice'), (2, 'Bob');
INSERT INTO courses (id, student_id, course_name) VALUES (1, 1, 'Math'), (2, 1, 'Science'), (3, 2, 'History');

现在我们想查询每个学生及其课程：

SELECT students.name, courses.course_name
FROM students
INNER JOIN courses ON students.id = courses.student_id;

结果：

name	course_name
Alice	Math
Alice	Science
Bob	History

1.2 外联接（OUTER JOIN）

外联接可以返回匹配的行和不匹配的行。外联接分为左外联接和右外联接。

左外联接示例：

SELECT students.name, courses.course_name
FROM students
LEFT JOIN courses ON students.id = courses.student_id;

结果：

name	course_name
Alice	Math
Alice	Science
Bob	NULL

右外联接示例：

SELECT students.name, courses.course_name
FROM students
RIGHT JOIN courses ON students.id = courses.student_id;

结果：

name	course_name
Alice	Math
Alice	Science
Bob	History

1.3 自联接（SELF JOIN）

自联接是将表连接到自身的操作。

示例：

假设我们在students表中增加一列表示学生的推荐人，展示具有推荐人的学生及其推荐人。

ALTER TABLE students ADD COLUMN recommender_id INT;

UPDATE students SET recommender_id = 1 WHERE id = 2;

SELECT s1.name AS student, s2.name AS recommender
FROM students s1
LEFT JOIN students s2 ON s1.recommender_id = s2.id;

结果：

student	recommender
Alice	NULL
Bob	Alice

2. 子查询与嵌套查询

子查询是嵌套在其他查询中的查询。可以在SELECT、FROM、WHERE子句中使用。

示例：

查询选修了“Math”课程的学生名称：

SELECT name
FROM students
WHERE id IN (SELECT student_id FROM courses WHERE course_name = 'Math');

3. 聚合函数

聚合函数用于对一组值进行计算，返回单个值。常用的聚合函数包括COUNT、SUM、AVG、MAX、MIN。

示例：

统计每位学生选修的课程数量：

SELECT student_id, COUNT(course_name) AS course_count
FROM courses
GROUP BY student_id;

4. 分组数据（GROUP BY与HAVING）

使用GROUP BY语句将结果集分组，HAVING用于过滤分组后的结果。

示例：

查询选修课程数量超过一门的学生：

SELECT student_id, COUNT(course_name) AS course_count
FROM courses
GROUP BY student_id
HAVING course_count > 1;

二、索引与视图

1. 创建与使用索引

索引可以提高查询效率。创建索引的基本语法如下：

CREATE INDEX index_name ON table_name(column_name);

示例： 在students表的name列上创建索引。

CREATE INDEX idx_name ON students(name);

2. 视图的创建与使用

视图是一个虚拟表，可以简化复杂查询。

创建视图的语法：

CREATE VIEW view_name AS
SELECT ...
FROM ...
WHERE ...;

示例： 创建一个视图，显示学生及其课程信息。

CREATE VIEW student_courses AS
SELECT students.name AS student_name, courses.course_name
FROM students
INNER JOIN courses ON students.id = courses.student_id;

查询视图：

SELECT * FROM student_courses;

三、事务管理

事务是一组操作，它们被视为一个单一的工作单元。事务遵循ACID特性（原子性、一致性、隔离性、持久性）。

1. 事务的特性（ACID）

原子性：事务是一个不可分割的工作单位。
一致性：事务使数据库从一个一致状态变更到另一个一致状态。
隔离性：并发事务之间的隔离。
持久性：一旦事务提交，其结果是永久性的。

2. 完整性约束

确保数据的有效性和一致性，可以通过主键、外键和其他约束实现数据的完整性。

3. 事务命令

BEGIN：开始一个事务。
COMMIT：提交事务。
ROLLBACK：回滚事务。
SAVEPOINT：设置一个保存点以便于回滚。

示例：

START TRANSACTION;

INSERT INTO students (id, name) VALUES (3, 'Charlie');
INSERT INTO courses (id, student_id, course_name) VALUES (4, 3, 'Biology');

-- 如果插入成功，提交事务
COMMIT;

-- 如果需要撤销，可以使用ROLLBACK
-- ROLLBACK;