【测试岗】2024测试岗最全面试题（三）MySQL篇

美少女战士1@

已于 2024-03-07 11:52:14 修改

阅读量1.2k

点赞数 59

分类专栏：测试岗文章标签： mysql oracle 数据库

于 2024-03-07 11:41:53 首次发布

本文链接：https://blog.csdn.net/weixin_48134870/article/details/136528449

版权

测试岗专栏收录该内容

24 篇文章 0 订阅

订阅专栏

MySQL基础

1. 数据库与表

数据库：

数据库是存储数据的容器，通常包含多个表、视图、存储过程等对象。
每个数据库在物理上对应于磁盘上的一个或多个文件。

表：

表是数据库的基本存储单元，用于存储具体的数据记录。
表由行（记录）和列（字段）组成，每一列具有一个特定的数据类型。
表的创建和管理是数据库设计的重要部分，涉及字段的选择、数据类型的确定以及约束的设置等。

2. 数据类型

MySQL支持多种数据类型，用于定义表中字段的数据类型：

整数类型：

TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT：分别表示不同范围的整数。

浮点数和定点数类型：

FLOAT, DOUBLE：用于存储浮点数，即带有小数点的数值。
DECIMAL(M,N)：用于存储精确的定点数，其中M表示数字的最大位数，N表示小数点后的位数。

字符串类型：

CHAR(M)：定长字符串，长度为M个字符。
VARCHAR(M)：可变长字符串，最大长度为M个字符。
TEXT, BLOB：用于存储长文本或二进制数据。

日期和时间类型：

DATE：日期值，格式为’YYYY-MM-DD’。
TIME：时间值，格式为’HH:MM:SS’。
DATETIME, TIMESTAMP：日期和时间组合值，用于存储完整的日期和时间信息。

枚举和集合类型：

ENUM：包含一个预定义的值集合的字段，只能从集合中取值。
SET：类似于ENUM，但允许选择多个值。

3. SQL语言

SQL是MySQL数据库的核心语言，用于管理数据库中的数据。SQL语言包括多个部分：

DDL（数据定义语言）：

CREATE：用于创建数据库、表、视图等对象。
ALTER：用于修改现有数据库对象的结构。
DROP：用于删除数据库、表等对象。

DML（数据操作语言）：

INSERT：用于向表中插入新的数据记录。
UPDATE：用于修改表中的数据记录。
DELETE：用于从表中删除数据记录。

DQL（数据查询语言）：

SELECT：用于从表中检索数据记录，并可以对结果进行排序、分组、过滤等操作。

DCL（数据控制语言）：

GRANT：用于授予用户或角色对数据库对象的访问权限。
REVOKE：用于撤销已授予的权限。

4. 索引

索引：

索引是数据库性能优化的关键工具，可以加速查询操作的速度。
通过在表的某些字段上创建索引，数据库系统可以更快地定位到满足查询条件的记录。
常用的索引类型包括普通索引、唯一索引、主键索引等。
索引的创建和使用需要根据实际情况进行权衡，过多的索引可能会影响写入操作的性能。

5. 主键和外键

主键：

主键是表中的特殊字段，用于唯一标识表中的每一行记录。
一个表只能有一个主键，主键的值必须是唯一的且不能为空。

外键：

外键用于建立和加强两个表数据之间的链接，是一列或多列的组合，其值基于另一个表的主键。
外键的主要作用是保持数据的一致性和完整性。
通过设置外键约束，可以确保引用完整性，即子表中引用的父表记录必须在父表中存在。

6. 存储过程和触发器

存储过程：

存储过程是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。
存储过程可以接受参数，并可以返回结果集或状态值。
通过调用存储过程，可以执行复杂的数据库操作，提高代码的复用性和可维护性。

触发器：

触发器是一种特殊的存储过程，它不能被直接调用，而是在满足某种条件时自动执行。
触发器通常用于在插入、更新或删除记录时自动执行某些操作，如检查数据的完整性、更新相关表的数据等。

7. 视图

视图：

视图是一个虚拟的表，其内容由查询定义。
视图本身不存储数据，而是基于一个或多个表的结果集。
通过视图，可以简化复杂的SQL操作，隐藏数据的复杂性，提高数据的安全性。
视图还可以用于限制用户对基础数据的访问权限。

8. 权限与安全

在MySQL中，权限与安全是数据库管理中不可或缺的部分，它们确保了数据的机密性、完整性和可用性。MySQL通过用户账户、权限系统、访问控制列表（ACL）以及其他安全特性来实现这一目标。下面将详细讲解MySQL的权限与安全机制。

8.1 用户账户管理

MySQL使用用户名和密码来验证客户端的连接请求。管理员可以创建用户账户，并为每个账户分配唯一的用户名和密码。用户账户可以关联到特定的主机，以限制从哪些主机可以连接到数据库。

8.2 权限系统

MySQL的权限系统允许管理员为用户账户分配特定的权限，以控制他们对数据库的访问和操作。权限可以分为全局权限、数据库权限、表权限和列权限等多个级别。

全局权限：适用于服务器上的所有数据库。例如，CREATE USER、DROP USER、SHUTDOWN等权限都是全局权限。
数据库权限：适用于特定的数据库。例如，CREATE、DROP、ALTER等权限可以应用于某个特定的数据库。
表权限：适用于特定的表。例如，SELECT、INSERT、UPDATE、DELETE等权限可以应用于某个特定的表。
列权限：适用于表的特定列。这允许管理员进一步细化对数据的访问控制。

管理员可以使用GRANT语句为用户账户分配权限，使用REVOKE语句撤销已分配的权限。

8.3 访问控制列表（ACL）

MySQL使用访问控制列表（ACL）来存储用户账户和它们的权限信息。ACL是一个内部的数据结构，用于快速检索和验证用户的权限。当客户端尝试连接到MySQL服务器并执行操作时，服务器会查询ACL来确定用户是否有相应的权限。

8.4 其他安全特性

除了用户账户和权限系统外，MySQL还提供了其他安全特性来增强数据库的安全性：

加密连接：MySQL支持使用SSL/TLS加密客户端和服务器之间的连接，以防止数据在传输过程中被截获。
密码策略：管理员可以设置密码策略，要求用户账户使用强密码，并定期更换密码。
审计日志：MySQL可以配置为记录所有数据库操作的审计日志，以便管理员可以追踪和审查潜在的恶意活动。
只读用户：可以为某些用户账户设置只读权限，限制他们对数据库的修改操作，从而保护数据的完整性。
视图和存储过程：通过创建视图和存储过程，可以隐藏数据的复杂性，并限制用户对基础数据的直接访问。
防火墙：在网络层面，可以使用防火墙来限制对MySQL服务器的访问，只允许来自受信任主机的连接请求。

8.5 最佳实践

为了确保MySQL数据库的安全性，以下是一些最佳实践建议：

为每个应用程序或服务使用单独的数据库用户账户，并仅授予必要的权限。
定期审查和更新用户账户的权限，确保没有过多的权限被授予不必要的用户。
使用强密码，并定期更换密码。
启用加密连接，确保数据在传输过程中的安全性。
定期备份数据库，并测试备份的完整性和可恢复性。
监控和审查数据库活动，及时发现并应对潜在的安全威胁。

通过遵循这些最佳实践，并结合MySQL提供的权限与安全机制，可以有效地保护数据库免受未经授权的访问和恶意攻击。

MySQL查询练习

练习 1: 选择数据

问题：

查询所有学生的姓名和年龄。
查询年龄大于20岁的学生姓名。
查询姓名以"张"开头的学生信息。
查询男生和女生的数量。

答案：

SELECT name, age FROM students;
SELECT name FROM students WHERE age > 20;
SELECT * FROM students WHERE name LIKE '张%';
SELECT gender, COUNT(*) as count FROM students GROUP BY gender;

练习 2: 排序和限制结果

问题：

查询所有学生信息，并按年龄从大到小排序。
查询年龄最大的前5名学生的信息。
查询年龄最小的学生的姓名和年龄。

答案：

SELECT * FROM students ORDER BY age DESC;
SELECT * FROM students ORDER BY age DESC LIMIT 5;
SELECT name, age FROM students ORDER BY age ASC LIMIT 1;

练习 3: 聚合函数

问题：

查询学生的平均年龄。
查询男生和女生的平均年龄。
查询每个年级的学生数量。

答案：

SELECT AVG(age) as average_age FROM students;
SELECT gender, AVG(age) as average_age FROM students GROUP BY gender;
SELECT grade, COUNT(*) as student_count FROM students GROUP BY grade;

练习 4: 分组和过滤

问题：

查询每个年级的平均年龄，并按平均年龄从高到低排序。
查询平均年龄大于20岁的年级。

答案：

SELECT grade, AVG(age) as average_age FROM students GROUP BY grade ORDER BY average_age DESC;
SELECT grade FROM students GROUP BY grade HAVING AVG(age) > 20;

练习 5: 连接查询

问题：

假设你还有一个名为courses的表，包含字段：course_id, course_name, teacher_id。以及一个名为teachers的表，包含字段：teacher_id, teacher_name。

查询每门课程的名称和对应的教师姓名。
查询教授课程数量最多的教师的姓名和教授的课程数量。

答案：

SELECT courses.course_name, teachers.teacher_name FROM courses JOIN teachers ON courses.teacher_id = teachers.teacher_id;
SELECT teachers.teacher_name, COUNT(courses.course_id) as course_count FROM teachers JOIN courses ON teachers.teacher_id = courses.teacher_id GROUP BY teachers.teacher_name ORDER BY course_count DESC LIMIT 1;

练习 6: 子查询

问题：

查询年龄大于所有女生平均年龄的男生姓名。
查询没有被任何学生选修的课程名称。

答案：

SELECT name FROM students WHERE gender = '男' AND age > (SELECT AVG(age) FROM students WHERE gender = '女');
假设有一个名为student_courses的表，用于存储学生和课程的关联关系，字段为student_id, course_id。
SELECT course_name FROM courses WHERE course_id NOT IN (SELECT course_id FROM student_courses);

练习 7: 插入、更新和删除数据

问题：

向students表中插入一条新的学生记录。
更新某个学生的年龄。
删除某个学生的记录。

答案：

请注意，以下操作会直接修改数据库中的数据，请在执行之前确保了解它们的影响，并在测试环境中进行。

插入数据：INSERT INTO students (name, age, gender, grade) VALUES ('新学生', 20, '男', '三年级');
更新数据：UPDATE students SET age = 21 WHERE name = '某个学生';
删除数据：DELETE FROM students WHERE name = '某个学生';

请确保将上述示例中的“新学生”、“某个学生”等替换为实际的值，并根据你的数据库结构进行相应的调整。

万年学生表经典面试题示例

假设有三个表：students（学生表）、courses（课程表）和scores（成绩表）。其中scores表包含student_id（学生ID）、course_id（课程ID）和score（成绩）字段，用于记录学生选课及成绩信息。“

以下是针对上述问题提供的SQL答案：

基础查询

查询所有学生信息的SQL语句：

SELECT * FROM students;

查询所有课程信息的SQL语句：

SELECT * FROM courses;

查询名为“张三”的学生所选修的所有课程名称：

SELECT c.course_name
FROM courses c
JOIN scores s ON c.course_id = s.course_id
JOIN students st ON s.student_id = st.student_id
WHERE st.name = '张三';

联接查询

使用内连接（INNER JOIN）查询每个学生及其对应的成绩信息：

SELECT st.name AS student_name, c.course_name, s.score
FROM students st
JOIN scores s ON st.student_id = s.student_id
JOIN courses c ON s.course_id = c.course_id;

使用左连接（LEFT JOIN）查询所有学生及其对应的成绩信息，即使某些学生没有成绩记录：

SELECT st.name AS student_name, c.course_name, s.score
FROM students st
LEFT JOIN scores s ON st.student_id = s.student_id
LEFT JOIN courses c ON s.course_id = c.course_id;

条件查询与聚合

查询成绩高于90分的学生信息：

SELECT st.*
FROM students st
JOIN scores s ON st.student_id = s.student_id
WHERE s.score > 90;

查询选修了超过三门课程的学生ID：

SELECT student_id
FROM scores
GROUP BY student_id
HAVING COUNT(DISTINCT course_id) > 3;

计算每门课程的平均成绩：

SELECT c.course_name, AVG(s.score) AS average_score
FROM courses c
JOIN scores s ON c.course_id = s.course_id
GROUP BY c.course_id, c.course_name;

分组与排序

将学生按成绩从高到低排序，并显示其姓名和成绩：

SELECT st.name AS student_name, s.score
FROM students st
JOIN scores s ON st.student_id = s.student_id
ORDER BY s.score DESC;

查询每个学生的总分数，并按总分数从高到低排序：

SELECT st.name AS student_name, SUM(s.score) AS total_score
FROM students st
JOIN scores s ON st.student_id = s.student_id
GROUP BY st.student_id, st.name
ORDER BY total_score DESC;

子查询与嵌套查询

查询成绩高于平均分的学生的姓名和成绩：

SELECT st.name AS student_name, s.score
FROM students st
JOIN scores s ON st.student_id = s.student_id
WHERE s.score > (SELECT AVG(score) FROM scores);

查询没有选修“数学”这门课的学生信息：

SELECT st.*
FROM students st
LEFT JOIN scores s ON st.student_id = s.student_id
LEFT JOIN courses c ON s.course_id = c.course_id AND c.course_name = '数学'
WHERE c.course_id IS NULL;

复杂查询与逻辑

查询选修了所有课程的学生信息（假设所有课程都已在courses表中列出）：

SELECT st.name AS student_name
FROM students st
WHERE NOT EXISTS (
  SELECT 1
  FROM courses c
  LEFT JOIN scores s ON c.course_id = s.course_id AND s.student_id = st.student_id
  WHERE s.course_id IS NULL
);

查询既选修了“数学”又选修了“英语”的学生信息：

SELECT st.name AS student_name
FROM students st
WHERE st.student_id IN (
  SELECT s.student_id
  FROM scores s
  JOIN courses c ON s.course_id = c.course_id AND c.course_name = '数学'
) AND st.student_id IN (
  SELECT s.student_id
  FROM scores s
  JOIN courses c ON s.course_id = c.course_id
  AND c.course_name = '英语'
);

性能优化

假设查询学生成绩时性能不佳，你会如何优化这个查询？

性能优化的方法有很多，具体取决于数据库的结构、数据量、索引、查询的复杂性等因素。以下是一些可能的优化措施：

确保有合适的索引：在经常用于查询条件的字段（如student_id、course_id和score）上创建索引，可以显著提高查询速度。
**避免SELECT ***：只选择需要的字段，而不是使用SELECT *来选择所有字段。
使用EXPLAIN分析查询：大多数数据库系统都提供了EXPLAIN或类似的工具，它可以显示查询的执行计划，帮助识别性能瓶颈。
优化JOIN操作：尽量减少JOIN的数量，并确保JOIN的条件是高效的。
避免在WHERE子句中使用函数：这会导致数据库无法进行索引查找，从而降低性能。
考虑数据库设计：有时，性能问题可能源于数据库设计本身。例如，过度规范化可能导致过多的JOIN操作。在这种情况下，可能需要重新考虑数据库设计。
分区和分片：对于非常大的表，可以考虑使用分区或分片来提高查询性能。
硬件和配置优化：确保数据库服务器具有足够的RAM、CPU和存储资源，并根据需要进行配置优化。

请注意，每个数据库系统和每个应用场景都是独特的，因此需要根据具体情况来制定优化策略。在进行任何优化之前，最好先对现有的查询性能进行基准测试，以便能够衡量优化后的效果。

数据库企业真题

以下是针对上述数据库企业真题的详细答案：

一、SQL查询

基础查询

查询学生表中所有学生的姓名和年龄。
```
SELECT name, age FROM students;
```

查询成绩表中分数大于90的学生姓名和分数。

SELECT s.name, s.score 
FROM students s
JOIN scores sc ON s.student_id = sc.student_id
WHERE sc.score > 90;

连接查询

查询选修了“数学”课程的学生姓名和成绩。

SELECT s.name, sc.score 
FROM students s
JOIN scores sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id
WHERE c.course_name = '数学';

查询每个学生的姓名、选课总数和平均成绩。

SELECT s.name, COUNT(sc.course_id) AS course_count, AVG(sc.score) AS avg_score
FROM students s
LEFT JOIN scores sc ON s.student_id = sc.student_id
GROUP BY s.student_id, s.name;

子查询与嵌套查询

查询成绩高于平均分的学生姓名。

SELECT s.name
FROM students s
JOIN scores sc ON s.student_id = sc.student_id
WHERE sc.score > (SELECT AVG(score) FROM scores);

查询没有选修任何课程的学生姓名。

SELECT name
FROM students
WHERE student_id NOT IN (SELECT DISTINCT student_id FROM scores);

聚合与分组

查询每门课程的平均成绩和最高成绩。

SELECT c.course_name, AVG(sc.score) AS avg_score, MAX(sc.score) AS max_score
FROM courses c
JOIN scores sc ON c.course_id = sc.course_id
GROUP BY c.course_id, c.course_name;

查询每个班级的学生人数。

SELECT class_id, COUNT(*) AS student_count
FROM students
GROUP BY class_id;

排序与限制

查询成绩前10名的学生姓名和成绩，按成绩降序排列。

SELECT s.name, sc.score
FROM students s
JOIN scores sc ON s.student_id = sc.student_id
ORDER BY sc.score DESC
LIMIT 10;

查询每个班级成绩最高的学生姓名和成绩。

SELECT s.class_id, s.name, sc.score
FROM students s
JOIN scores sc ON s.student_id = sc.student_id
WHERE (s.class_id, sc.score) IN (
    SELECT class_id, MAX(score)
    FROM scores
    GROUP BY class_id
);

LEFT JOIN和JOIN的区别
（通常指的是INNER JOIN）在数据库查询中的主要区别体现在它们如何处理两个表中不匹配的记录。

结果集内容：

LEFT JOIN（左连接）返回左表中的所有记录，以及与之匹配的右表中的记录。如果左表中的某条记录在右表中没有匹配项，则结果集中该记录对应的右表部分将包含空值。因此，LEFT JOIN返回的是左表和右表的并集，包括左表中没有对应记录的右表记录。
JOIN（INNER JOIN）只返回两个表中都有的记录，即那些在两个表中都能找到匹配项的记录。如果左表中的某条记录在右表中没有匹配项，那么这条记录将不会出现在结果集中。因此，JOIN返回的是两个表的交集。

用途：

由于LEFT JOIN会返回左表中的所有记录，所以它常用于寻找那些在右表中没有匹配项的记录。这在某些场景下非常有用，比如当你想要查看一个列表中的所有项目，以及这些项目与另一个列表中的匹配项时。
JOIN则更适用于那些你只需要两个表中都存在的记录的场景。

总的来说，LEFT JOIN和JOIN的主要区别在于它们处理不匹配记录的方式不同，这导致了它们返回的结果集内容有所不同。在选择使用哪种连接时，应根据你的具体需求和数据模型来决定。

二、数据库设计与关系

表结构设计
- 设计用户、订单、商品三个实体的数据库表结构，并说明它们之间的关系。
  - 用户表：user_id, username, password, email等字段。
  - 订单表：order_id, user_id, order_date, total_price等字段，其中user_id是外键关联用户表。
  - 商品表：product_id, product_name, price, stock等字段。
  - 订单明细表：detail_id, order_id, product_id, quantity等字段，其中order_id和product_id是外键。
  这些表之间的关系主要是：用户可以下多个订单，每个订单可以包含多个商品（通过订单明细表）。
关系完整性
- 实体完整性：确保主键的唯一性和非空性。
- 参照完整性：通过外键约束确保表之间的关系正确。
- 用户自定义完整性：根据业务需求定义其他约束，如检查约束。

三、性能优化

索引优化
- 索引能加快查询速度，但也会增加插入、更新和删除的开销。
- 选择经常用于查询条件的字段创建索引，如主键和外键字段。
- 对于包含大量数据的表，考虑使用复合索引来优化多字段的查询条件。
- 定期审查和优化索引，删除不再需要的索引，避免过多的索引导致性能下降。
查询优化
- 尽量避免在查询中使用SELECT *，而是选择需要的字段。
- 减少JOIN的数量和复杂性，尽量使用内连接（INNER JOIN）代替外连接（OUTER JOIN）。
- 使用EXPLAIN分析查询的执行计划，找出可能的性能瓶颈，如全表扫描、文件排序等。
- 对于复杂的查询，考虑将其分解为多个简单的查询，并在应用层进行组合。
- 对于经常需要查询的数据，考虑使用缓存机制来减少数据库访问次数。

四、事务与并发

事务概念
- 事务是一系列数据库操作的逻辑单元，要么全部成功执行，要么全部失败回滚。
- ACID属性指的是原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。
并发控制
- 并发问题包括脏读、不可重复读和幻读，这些问题可以通过锁和事务隔离级别来解决。
- 锁是数据库用来控制并发访问的一种机制，包括共享锁和排他锁等。
- 选择合适的事务隔离级别（如READ COMMITTED、REPEATABLE READ等）来平衡并发性和数据一致性。

五、数据库管理与维护

备份与恢复
- 定期备份数据库是确保数据安全的重要措施，备份策略可以根据业务需求和数据量来制定。
- 备份类型包括全量备份、增量备份和差异备份，可以根据实际情况选择使用。
- 在发生故障时，使用备份文件进行数据库恢复，确保数据的完整性和可用性。
日志与监控
- 数据库日志记录了数据库的运行状态和操作历史，对于故障排查和性能优化非常重要。
- 通过监控工具实时查看数据库的性能指标，如CPU使用率、内存占用、I/O读写速度等。
- 根据监控数据进行性能分析和调优，如调整参数、优化查询等。

以上是针对您提供的数据库企业真题的详细答案。在实际面试中，可能还需要根据具体情况进行更深入的讨论和扩展。希望这些答案对您有所帮助！

美少女战士1@

关注

59
点赞
踩
19

收藏

觉得还不错? 一键收藏
0
评论
【测试岗】2024测试岗最全面试题（三）MySQL篇

数据库：表：MySQL支持多种数据类型，用于定义表中字段的数据类型：整数类型：浮点数和定点数类型：字符串类型：日期和时间类型：枚举和集合类型：SQL是MySQL数据库的核心语言，用于管理数据库中的数据。SQL语言包括多个部分：DDL（数据定义语言）：DML（数据操作语言）：DQL（数据查询语言）：DCL（数据控制语言）：索引：主键：外键：存储过程：触发器：视图：在MySQL中，权限与安全是数据库管理中不可或缺的部分，它们确保了数据的机密性、完整性和可用性。MySQL通过用户账户、权限系统、访问控制列表（AC
复制链接

扫一扫

专栏目录