数据库系统原理实战：从经典SQL考题到高频面试题精解

1. SQL查询基础：从排序到模糊匹配

SQL查询是数据库操作中最基础也是最常用的功能。我们先来看几个简单的查询例子，这些例子在期末考试和面试中经常出现。

升序排列查询是最基础的排序操作：

SELECT * FROM afinfo ORDER BY birth;

这条语句会按照birth字段的值从小到大排列结果。在实际工作中，我们经常需要对结果进行排序，比如按照时间先后显示订单，或者按照销售额高低排列产品。

模糊查询在需要匹配部分字符串时非常有用：

SELECT * FROM afinfo WHERE name LIKE '徐%';

这里的LIKE '徐%'表示查找所有姓"徐"的人员。百分号(%)是通配符，代表任意数量的任意字符。这种查询在搜索功能中很常见，比如电商网站的商品搜索。

数据更新操作需要注意条件准确性：

UPDATE afinfo SET age=45 WHERE name="陈晓";

这个例子中，我们更新了"陈晓"的年龄为45岁。在实际操作中，一定要确保WHERE条件准确，否则可能会误改大量数据。我曾经在一次项目中就因为漏写了WHERE条件，导致整张表的数据都被更新，造成了严重的数据事故。

删除操作更要谨慎：

DELETE FROM afinfo WHERE name="王芳芳";

删除是不可逆的操作，执行前最好先做备份。在生产环境中，我们通常会采用逻辑删除（标记删除状态）而不是物理删除。

2. 多表连接查询实战

实际业务中，数据往往分散在多个表中，这就需要我们掌握多表连接查询的技巧。让我们看一个学生-社团数据库的例子。

表结构定义是理解查询的基础：

CREATE TABLE 社团 ( 编号 NUMBER(4) PRIMARY KEY, 名称 VARCHAR(10), 负责人 NUMBER(4), 办公地点 VARCHAR(20), CONSTRAINT FK_FZR FOREIGN KEY (负责人) REFERENCES 学生(学号) );

这里定义了社团表的主键和外键约束。外键确保了数据的完整性，防止出现无效的负责人学号。

视图创建可以简化复杂查询：

CREATE VIEW 社团负责人 AS SELECT 社团.编号, 名称, 负责人, 姓名, 性别 FROM 学生, 社团, 参加 WHERE 学生.学号=参加.学号 AND 社团.编号=参加.编号;

视图就像是一个虚拟表，存储了查询定义而不是数据本身。合理使用视图可以简化应用程序代码。

多表连接查询有多种写法：

-- 查询参加科协的学生信息 SELECT 学号, 姓名, 性别 FROM 学生, 社团, 参加 WHERE 学生.学号=参加.学号 AND 社团.编号=参加.编号 AND 名称='科协';

这种写法使用WHERE子句进行表连接，是SQL-92标准之前的语法。现在更推荐使用JOIN语法：

SELECT 学号, 姓名, 性别 FROM 学生 JOIN 参加 ON 学生.学号=参加.学号 JOIN 社团 ON 社团.编号=参加.编号 WHERE 名称='科协';

JOIN语法更清晰，也更容易理解各表之间的关系。

分组聚合是统计分析的基础：

SELECT 编号, COUNT(学号) FROM 参加 GROUP BY 编号;

这个查询计算每个社团的参加人数。GROUP BY子句指定分组依据，COUNT是聚合函数。其他常用聚合函数还包括SUM、AVG、MAX、MIN等。

3. 权限管理与复杂查询

权限控制是数据库安全的重要部分：

GRANT INSERT, DELETE ON 社团 TO 李平 WITH GRANT OPTION;

这条语句将社团表的插入和删除权限授予用户李平，并允许他将这些权限授予其他用户。在实际项目中，权限管理需要非常谨慎，遵循最小权限原则。

复杂条件查询需要灵活运用SQL语法：

-- 查找所有女科长 SELECT * FROM 职工 WHERE 性别='女' AND 职务='科长';

多表连接查询的两种写法：

-- 查找办公室的科长姓名和地址（多表连接） SELECT 姓名, 家庭地址 FROM 职工, 部门 WHERE 职工.部门编号=部门.部门编号 AND 部门名称='办公室' AND 职务="科长"; -- 使用子查询 SELECT 姓名, 家庭地址 FROM 职工 WHERE 职务='科长' AND 部门编号 IN ( SELECT 部门编号 FROM 部门 WHERE 部门名称='办公室' );

两种写法结果相同，但执行计划可能不同。在数据量大时，性能差异会很明显。

视图的创建与使用：

-- 创建健康状况为差的职工视图 CREATE VIEW VW AS SELECT * FROM 职工 WHERE 职工号 IN ( SELECT 职工号 FROM 保健 WHERE 健康状况='差' );

视图可以简化复杂查询，也可以用于权限控制，只允许用户访问视图而不是基表。

表结构修改：

ALTER TABLE 保健 ADD 备注 CHAR(20);

这条语句为保健表增加了一个备注字段。在生产环境修改表结构需要谨慎，可能影响正在运行的应用程序。

4. 高级查询技巧：子查询与关系代数

关系代数与SQL的对应是理解复杂查询的关键。让我们看一个教学数据库的例子。

检索至少选修C02和C06课程的学生学号：

-- 关系代数表达式 πsno(σ1=4∧2='C02'∧5='C06'(elective×elective)) -- SQL实现 SELECT FIRST.sno FROM elective FIRST, elective SECOND WHERE FIRST.sno=SECOND.sno AND FIRST.cno='C02' AND SECOND.cno='C06';

这个查询需要同一个学生选修两门指定课程，因此需要自连接elective表。

检索没有选修C06课程的学生：

SELECT sname, class FROM student WHERE NOT EXISTS ( SELECT * FROM elective WHERE sno=student.sno AND cno='C06' );

NOT EXISTS是处理"不存在"条件的强大工具。这种查询在业务中很常见，比如"查找从未下单的客户"。

检索学习全部课程的学生：

SELECT sname FROM student WHERE NOT EXISTS ( SELECT * FROM course WHERE NOT EXISTS ( SELECT * FROM elective WHERE sno=student.sno AND cno=course.cno ) );

这是典型的"除"操作，找出满足"对于所有课程，该学生都选修了"条件的学生。这种查询在面试中经常出现。

关系除法的另一种写法：

SELECT sname FROM student WHERE (SELECT COUNT(*) FROM elective WHERE sno=student.sno) = (SELECT COUNT(*) FROM course);

这种写法假设没有重复选课，且只关心选课数量是否等于课程总数。

视图的应用：

CREATE VIEW 客户产品 AS SELECT 客户号, 产品号 FROM 订单, 订单明细 WHERE 订单明细.订单号=订单.订单号;

视图可以简化后续查询，比如查询至少购买了01号客户购买的所有产品的客户号：

SELECT 客户号 FROM 客户产品 客户产品1 WHERE NOT EXISTS ( SELECT * FROM 客户产品 客户产品2 WHERE 客户号='01' AND NOT EXISTS ( SELECT * FROM 客户产品 客户产品3 WHERE 客户产品1.客户号=客户产品3.客户号 AND 客户产品2.产品号=客户产品3.产品号 ) );

这种嵌套NOT EXISTS的写法是解决"所有-都"类问题的标准模式。

5. 综合应用：电商场景SQL实战

让我们看一个电商数据库的实战例子，包含客户、产品、订单和订单明细表。

查询一次订购产品号为02且数量大于10的客户号：

SELECT 客户号 FROM 订单, 订单明细 WHERE 订单明细.订单号=订单.订单号 AND 产品号='02' AND 数量>10;

这是典型的多表连接查询，在电商后台管理系统中很常见。

按客户购买总额降序输出：

SELECT 客户.客户名, SUM(金额) AS 总额 FROM 客户, 订单 WHERE 客户.客户号=订单.客户号 GROUP BY 客户.客户号 ORDER BY 总额 DESC;

这个查询使用了GROUP BY分组和SUM聚合函数，是销售分析的基础。

查询购买了特定产品的客户邮件：

SELECT cust_email FROM Customers WHERE cust_id IN ( SELECT cust_id FROM Orders WHERE order_num IN ( SELECT order_num FROM OrderItems WHERE prod_id='BR01' ) );

这种嵌套子查询的写法逻辑清晰，但性能可能不如连接查询：

SELECT c.cust_email FROM OrderItems a JOIN Orders b ON a.order_num=b.order_num JOIN Customers c ON b.cust_id=c.cust_id WHERE a.prod_id='BR01';

在数据量大时，连接查询通常性能更好。

计算每个客户的总订单金额：

SELECT b.cust_id, SUM(a.quantity*a.item_price) AS total_ordered FROM OrderItems a JOIN Orders b ON a.order_num=b.order_num GROUP BY cust_id ORDER BY total_ordered DESC;

这个查询计算了每个客户的消费总额，是客户价值分析的基础。

使用HAVING筛选分组结果：

SELECT order_num, COUNT(*) AS items FROM OrderItems GROUP BY order_num HAVING COUNT(*) >= 3 ORDER BY items, order_num;

HAVING类似于WHERE，但它作用于分组后的结果而不是单行记录。

6. 常见面试题解析

查找最晚入职员工是经典面试题：

SELECT * FROM employees ORDER BY hire_date DESC LIMIT 1;

这种写法简单直接，但如果有多人同一天最晚入职，只会返回其中一个。

查找薪水第二高的员工：

SELECT emp_no, salary FROM salaries ORDER BY salary DESC LIMIT 1 OFFSET 1;

或者使用子查询：

SELECT emp_no, salary FROM salaries WHERE salary = ( SELECT DISTINCT salary FROM salaries ORDER BY salary DESC LIMIT 1 OFFSET 1 );

第二种写法能处理多人薪水相同的情况。

统计各部门工资记录数：

SELECT dept_no, COUNT(*) FROM dept_emp JOIN salaries ON dept_emp.emp_no=salaries.emp_no GROUP BY dept_no;

这类分组统计问题在业务分析中非常常见。

使用JOIN查询找出没有分类的电影：

SELECT film.film_id, film.title FROM film LEFT JOIN film_category ON film.film_id=film_category.film_id WHERE film_category.category_id IS NULL;

LEFT JOIN配合IS NULL是查找"不存在"关系的标准模式。

批量插入数据：

INSERT INTO actor (actor_id, first_name, last_name) VALUES (1, 'PENELOPE', 'GUINESS'), (2, 'NICK', 'WAHLBERG'), (3, 'ED', 'CHASE');

批量插入比单条插入效率高很多，特别是在数据量大时。

创建唯一索引：

CREATE UNIQUE INDEX uniq_idx_firstname ON actor(first_name);

唯一索引确保字段值不重复，同时也能提高查询速度。

7. 性能优化与最佳实践

EXISTS与IN的选择：

-- 使用EXISTS SELECT * FROM employees e WHERE EXISTS ( SELECT 1 FROM titles t WHERE t.emp_no=e.emp_no AND t.title='Manager' ); -- 使用IN SELECT * FROM employees WHERE emp_no IN ( SELECT emp_no FROM titles WHERE title='Manager' );

在大多数情况下，EXISTS性能优于IN，特别是当子查询结果集大时。

避免在WHERE子句中使用函数：

-- 不推荐 SELECT * FROM orders WHERE YEAR(order_date)=2023; -- 推荐 SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

在字段上使用函数会导致索引失效。

合理使用索引：

-- 创建复合索引 CREATE INDEX idx_name ON employees(last_name, first_name); -- 索引使用情况 EXPLAIN SELECT * FROM employees WHERE last_name='Smith' AND first_name='John';

复合索引的顺序很重要，应该将选择性高的字段放在前面。

分页查询优化：

-- 简单分页 SELECT * FROM products ORDER BY price DESC LIMIT 10 OFFSET 20; -- 大数据量分页优化 SELECT * FROM products WHERE id > 1000 ORDER BY id LIMIT 10;

OFFSET在大数据量时性能很差，使用WHERE条件过滤更高效。

事务处理：

START TRANSACTION; UPDATE accounts SET balance=balance-100 WHERE user_id=1; UPDATE accounts SET balance=balance+100 WHERE user_id=2; COMMIT;

事务确保多个操作要么全部成功，要么全部失败，保证数据一致性。

8. 实战案例：图书馆管理系统

图书馆数据库典型查询：

-- 查询借阅次数最多的图书 SELECT book_id, title, COUNT(*) AS borrow_count FROM books JOIN borrow_records ON books.id=borrow_records.book_id GROUP BY book_id, title ORDER BY borrow_count DESC LIMIT 10;

查询逾期未还的图书：

SELECT b.title, r.borrower_name, r.due_date FROM borrow_records r JOIN books b ON r.book_id=b.id WHERE r.return_date IS NULL AND r.due_date < CURRENT_DATE;

查询热门图书类别：

SELECT c.name, COUNT(*) AS borrow_count FROM categories c JOIN books b ON c.id=b.category_id JOIN borrow_records r ON b.id=r.book_id GROUP BY c.id, c.name ORDER BY borrow_count DESC;

使用存储过程处理借书：

CREATE PROCEDURE borrow_book( IN p_book_id INT, IN p_borrower_id INT, OUT p_result VARCHAR(100) ) BEGIN DECLARE available INT; SELECT COUNT(*) INTO available FROM books WHERE id=p_book_id AND status='available'; IF available > 0 THEN INSERT INTO borrow_records(book_id, borrower_id, borrow_date, due_date) VALUES(p_book_id, p_borrower_id, CURRENT_DATE, DATE_ADD(CURRENT_DATE, INTERVAL 30 DAY)); UPDATE books SET status='borrowed' WHERE id=p_book_id; SET p_result = '借书成功'; ELSE SET p_result = '图书不可借'; END IF; END;

存储过程封装了业务逻辑，可以减少网络往返，提高性能。

使用触发器记录操作日志：

CREATE TRIGGER log_book_changes AFTER UPDATE ON books FOR EACH ROW BEGIN IF NEW.status != OLD.status THEN INSERT INTO book_audit_log(book_id, old_status, new_status, change_time) VALUES(NEW.id, OLD.status, NEW.status, NOW()); END IF; END;

触发器可以自动记录数据变更，用于审计追踪。