MySQL 单表与多表操作详解

目录

一、单表查询整合

（一）通用模板展示

（二）举例说明

1. 简单查询

2. 条件查询

3. 高级查询

（三）注意事项

（四）Mapper 简单举例（以 MyBatis 为例）

1. 简单查询

2. 条件查询

3. 高级查询（分组查询）

4. 表和字段取别名

二、多表操作说明

（一）多表操作的基本模板展示

1. 外键约束模板

2. 操作关联表模板

3. 连接查询模板

4. 子查询模板

（二）简单案例展示

1. 两张表情况（学生表和学生选课表）

2. 三张表情况（学生表、课程表、学生选课表）

3. 多表情况（三张以上，假设增加教师表teacher：teacher_id（主键）、teacher_name，课程表course的teacher改为teacher_id外键关联教师表）

（三）注意事项

三、总结

一、单表查询整合

（一）通用模板展示

在 MySQL 中，单表查询主要通过SELECT语句实现，其通用模板如下：

SELECT [DISTINCT] 列名1 [AS 别名1], 列名2 [AS 别名2], ...
FROM 表名 [AS 表别名]
[WHERE 条件表达式]
[GROUP BY 分组列名 [WITH ROLLUP]]
[HAVING 分组筛选条件]
[ORDER BY 排序列名 [ASC/DESC]]
[LIMIT 偏移量, 数量];

各部分说明：

• SELECT：指定要查询的列，DISTINCT用于去重。

• FROM：指定查询的表，可给表取别名方便后续操作。

• WHERE：筛选符合条件的行，条件表达式包含比较运算符、逻辑运算符等。

• GROUP BY：按指定列分组，WITH ROLLUP可生成汇总行。

• HAVING：对分组结果进行筛选。

• ORDER BY：对查询结果排序，ASC为升序（默认），DESC为降序。

• LIMIT：限制查询结果的数量，偏移量指定从第几行开始（默认从 0 开始）。

（二）举例说明

1. 简单查询

• 查询表中所有列（以学生表student为例，包含id、name、age、gender列）：

SELECT * FROM student;

• 查询指定列（如查询学生姓名和年龄）：

SELECT name, age FROM student;

2. 条件查询

使用WHERE子句添加条件，常见条件运算符有：

• 比较运算符：=（等于）、!=或<>（不等于）、>（大于）、<（小于）、>=（大于等于）、<=（小于等于）。

• • 查询年龄大于 20 岁的学生：

SELECT * FROM student WHERE age > 20;

• IN：指定一个值列表，匹配其中任意一个值。

• • 查询性别为男（' 男 '）或女（' 女 '）的学生：

SELECT * FROM student WHERE gender IN ('男', '女');

• BETWEEN AND：匹配某个范围内的值（包含边界值）。

• • 查询年龄在 18 到 25 岁之间的学生：

SELECT * FROM student WHERE age BETWEEN 18 AND 25;

• LIKE：模糊匹配，%表示任意多个字符，_表示任意一个字符。

• • 查询姓名以 "张" 开头的学生：

SELECT * FROM student WHERE name LIKE '张%';

• 查询姓名包含 "三" 字的学生：

SELECT * FROM student WHERE name LIKE '%三%';

• IS NULL/IS NOT NULL：判断字段是否为 NULL（空值）。

• • 查询邮箱为空的学生：

SELECT * FROM student WHERE email IS NULL;

3. 高级查询

• 聚合函数：用于对一组值进行计算并返回一个单一值，常见聚合函数有：

• • COUNT()：统计行数。

• • • 统计学生表中的总人数：

SELECT COUNT(*) FROM student;

• SUM()：计算数值列的总和。

• • 假设学生表有score列，计算所有学生的成绩总和：

SELECT SUM(score) FROM student;

• AVG()：计算数值列的平均值。

• • 计算学生的平均成绩：

SELECT AVG(score) FROM student;

• MAX()：获取列中的最大值。

• • 获取学生的最高成绩：

SELECT MAX(score) FROM student;

• MIN()：获取列中的最小值。

• • 获取学生的最低成绩：

SELECT MIN(score) FROM student;

• 分组查询（GROUP BY）：将数据按照指定列分组，以便对每个组进行聚合操作。

• • 按性别分组，统计每组的学生人数：

SELECT gender, COUNT(*) AS 人数 FROM student GROUP BY gender;

• HAVING 子句：对分组后的结果进行筛选，作用类似于WHERE，但WHERE作用于行，HAVING作用于分组。

• • 按性别分组，筛选出人数大于 5 的组：

SELECT gender, COUNT(*) AS 人数 FROM student GROUP BY gender HAVING 人数 > 5;

• 排序（ORDER BY）：对查询结果进行排序。

• • 按年龄升序排列学生信息：

SELECT * FROM student ORDER BY age ASC;

• 按成绩降序排列，成绩相同则按年龄升序排列：

SELECT * FROM student ORDER BY score DESC, age ASC;

• 限制结果数量（LIMIT）：

• • 查询前 5 条学生记录：

SELECT * FROM student LIMIT 5;

• 从第 3 条记录开始，查询 3 条记录（偏移量为 2，数量为 3）：

SELECT * FROM student LIMIT 2, 3;

（三）注意事项

1. WHERE子句不能使用聚合函数，因为聚合函数是在分组后计算的，而WHERE是在分组之前对行进行筛选，若要对分组结果筛选，需使用HAVING子句。

1. SELECT子句中若包含非聚合列，这些列必须出现在GROUP BY子句中，否则会导致错误（在 MySQL 的某些模式下可能允许，但不建议）。

1. 使用LIKE进行模糊查询时，%和_的位置会影响查询效率，以%开头的查询可能无法使用索引，导致性能下降。

1. 对NULL值进行比较时，必须使用IS NULL或IS NOT NULL，不能使用=或!=。

（四）Mapper 简单举例（以 MyBatis 为例）

1. 简单查询

接口方法：

List<Student> selectAllStudents();

Mapper XML：

<select id="selectAllStudents" resultType="Student">

SELECT * FROM student

</select>

2. 条件查询

接口方法：

List<Student> selectStudentsByAge(int age);

Mapper XML：

<select id="selectStudentsByAge" resultType="Student">

SELECT * FROM student WHERE age = #{age}

</select>

3. 高级查询（分组查询）

接口方法：

List<Map<String, Object>> countStudentsByGender();

Mapper XML：

<select id="countStudentsByGender" resultType="map">

SELECT gender, COUNT(*) AS count FROM student GROUP BY gender

</select>

4. 表和字段取别名

• 表取别名：为表指定一个简短的别名，方便书写和阅读。

SELECT s.name, s.age FROM student AS s;

• 字段取别名：为字段指定别名，使结果集的列名更清晰。

SELECT name AS 学生姓名, age AS 年龄 FROM student;

二、多表操作说明

（一）多表操作的基本模板展示

1. 外键约束模板

外键用于建立表与表之间的关联，保证数据的完整性和一致性。创建外键的语法如下：

-- 创建表时添加外键

CREATE TABLE 表名 (

列1 数据类型,

列2 数据类型,

...

FOREIGN KEY (外键列) REFERENCES 主表(主键列)

);

-- 对已存在的表添加外键

ALTER TABLE 表名 ADD FOREIGN KEY (外键列) REFERENCES 主表(主键列);

例如，学生表student（主键为id）和课程表course（主键为course_id），学生选课表student_course通过外键关联这两张表：

CREATE TABLE student_course (

student_id INT,

course_id INT,

FOREIGN KEY (student_id) REFERENCES student(id),

FOREIGN KEY (course_id) REFERENCES course(course_id)

);

2. 操作关联表模板

• 插入数据：在关联表中插入数据时，外键列的值必须是主表中存在的主键值，否则会报错。

INSERT INTO student_course (student_id, course_id) VALUES (1, 101); -- 假设student表存在id=1，course表存在course_id=101

• 更新数据：更新主表的主键值时，若关联表存在外键引用，默认会报错，可通过设置外键的ON UPDATE选项来指定更新行为（如级联更新CASCADE）。

-- 创建表时设置级联更新

CREATE TABLE student_course (

student_id INT,

course_id INT,

FOREIGN KEY (student_id) REFERENCES student(id) ON UPDATE CASCADE

);

• 删除数据：删除主表的记录时，若关联表存在外键引用，默认会报错，可通过设置ON DELETE选项来指定删除行为（如级联删除CASCADE、设置外键值为NULL等）。

3. 连接查询模板

连接查询用于将多个表中的数据按一定条件组合起来，常见的连接类型有：

• 内连接（INNER JOIN）：只返回两个表中满足连接条件的行。

SELECT 表1.列, 表2.列 FROM 表1 INNER JOIN 表2 ON 表1.关联列 = 表2.关联列;

• 左外连接（LEFT JOIN 或 LEFT OUTER JOIN）：返回左表的所有行，右表中满足连接条件的行，若右表无匹配行，右表列值为NULL。

SELECT 表1.列, 表2.列 FROM 表1 LEFT JOIN 表2 ON 表1.关联列 = 表2.关联列;

• 右外连接（RIGHT JOIN 或 RIGHT OUTER JOIN）：返回右表的所有行，左表中满足连接条件的行，若左表无匹配行，左表列值为NULL。

SELECT 表1.列, 表2.列 FROM 表1 RIGHT JOIN 表2 ON 表1.关联列 = 表2.关联列;

• 全外连接（FULL JOIN 或 FULL OUTER JOIN）：返回两个表中所有满足连接条件的行，以及至少一个表中不满足连接条件的行（MySQL 不支持全外连接，可通过左外连接和右外连接的并集实现）。

SELECT 表1.列, 表2.列 FROM 表1 LEFT JOIN 表2 ON 表1.关联列 = 表2.关联列

UNION

SELECT 表1.列, 表2.列 FROM 表1 RIGHT JOIN 表2 ON 表1.关联列 = 表2.关联列;

• 交叉连接（CROSS JOIN）：返回两个表的笛卡尔积（即左表每一行与右表每一行组合），若不指定连接条件，结果集行数为两表行数的乘积。

SELECT 表1.列, 表2.列 FROM 表1 CROSS JOIN 表2;

4. 子查询模板

子查询是指在一个查询语句中嵌套另一个查询语句，子查询可以出现在SELECT、FROM、WHERE、HAVING等子句中。

• 单行子查询：子查询返回一行数据，可使用单行比较运算符（如=、>、<等）。

• • 查询成绩等于班级平均成绩的学生：

SELECT * FROM student WHERE score = (SELECT AVG(score) FROM student);

• 多行子查询：子查询返回多行数据，需使用多行比较运算符（如IN、ANY、ALL等）。

• • 查询成绩大于所有男生成绩的女生：

SELECT * FROM student WHERE gender = '女' AND score > ALL (SELECT score FROM student WHERE gender = '男');

• 相关子查询：子查询依赖于外部查询的值，每次外部查询处理一行，子查询就执行一次。

• • 查询每个学生的选课数量：

SELECT s.name, (SELECT COUNT(*) FROM student_course sc WHERE sc.student_id = s.id) AS 选课数量 FROM student s;

（二）简单案例展示

假设存在以下三张表：

• 学生表student：id（主键）、name、age、gender

• 课程表course：course_id（主键）、course_name、teacher

• 学生选课表student_course：student_id（外键，关联student.id）、course_id（外键，关联course.course_id）、score（成绩）

1. 两张表情况（学生表和学生选课表）

• 查询学生姓名和对应的课程成绩：

SELECT s.name, sc.score FROM student s INNER JOIN student_course sc ON s.id = sc.student_id;

2. 三张表情况（学生表、课程表、学生选课表）

• 查询学生姓名、课程名称和成绩：

SELECT s.name, c.course_name, sc.score

FROM student s

INNER JOIN student_course sc ON s.id = sc.student_id

INNER JOIN course c ON sc.course_id = c.course_id;

3. 多表情况（三张以上，假设增加教师表teacher：teacher_id（主键）、teacher_name，课程表course的teacher改为teacher_id外键关联教师表）

• 查询学生姓名、课程名称、教师姓名和成绩：

SELECT s.name, c.course_name, t.teacher_name, sc.score

FROM student s

INNER JOIN student_course sc ON s.id = sc.student_id

INNER JOIN course c ON sc.course_id = c.course_id

INNER JOIN teacher t ON c.teacher_id = t.teacher_id;

（三）注意事项

1. 外键约束虽然能保证数据的完整性，但会对插入、更新和删除操作的性能产生一定影响，尤其是在大数据量的情况下，需谨慎使用。

1. 连接查询时，必须明确指定连接条件，否则会产生笛卡尔积，导致结果集过大，影响性能。

1. 子查询的嵌套层数不宜过多，否则会影响查询效率，可考虑使用连接查询来替代复杂的子查询。

1. 在多表操作中，要注意表的连接顺序，合理利用索引，以提高查询性能。

1. 当使用外连接时，要明确哪张表是主表（左表或右表），避免因连接顺序错误导致结果不符合预期。

三、总结

单表查询是 MySQL 数据库操作的基础，通过SELECT语句结合各种子句（如WHERE、GROUP BY、ORDER BY等）可以灵活地筛选、分组、排序和限制查询结果。多表操作则通过外键约束建立表之间的关联，利用连接查询和子查询将多个表的数据组合起来，实现更复杂的数据查询和分析。

在实际应用中，需要根据具体的业务需求选择合适的查询方式，同时注意查询性能和数据完整性。单表查询时要合理使用索引和条件筛选，多表操作时要正确设置外键约束和连接条件，避免出现性能问题和数据不一致的情况。通过不断练习和实践，能够更加熟练地掌握 MySQL 的单表和多表操作，提高数据库开发和管理的效率。

 🌟感谢支持 听忆.-CSDN博客