1. SQL查询基础从排序到模糊匹配SQL查询是数据库操作中最基础也是最常用的功能。我们先来看几个简单的查询例子这些例子在期末考试和面试中经常出现。升序排列查询是最基础的排序操作SELECT * FROM afinfo ORDER BY birth;这条语句会按照birth字段的值从小到大排列结果。在实际工作中我们经常需要对结果进行排序比如按照时间先后显示订单或者按照销售额高低排列产品。模糊查询在需要匹配部分字符串时非常有用SELECT * FROM afinfo WHERE name LIKE 徐%;这里的LIKE 徐%表示查找所有姓徐的人员。百分号(%)是通配符代表任意数量的任意字符。这种查询在搜索功能中很常见比如电商网站的商品搜索。数据更新操作需要注意条件准确性UPDATE afinfo SET age45 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(σ14∧2C02∧5C06(elective×elective)) -- SQL实现 SELECT FIRST.sno FROM elective FIRST, elective SECOND WHERE FIRST.snoSECOND.sno AND FIRST.cnoC02 AND SECOND.cnoC06;这个查询需要同一个学生选修两门指定课程因此需要自连接elective表。检索没有选修C06课程的学生SELECT sname, class FROM student WHERE NOT EXISTS ( SELECT * FROM elective WHERE snostudent.sno AND cnoC06 );NOT EXISTS是处理不存在条件的强大工具。这种查询在业务中很常见比如查找从未下单的客户。检索学习全部课程的学生SELECT sname FROM student WHERE NOT EXISTS ( SELECT * FROM course WHERE NOT EXISTS ( SELECT * FROM elective WHERE snostudent.sno AND cnocourse.cno ) );这是典型的除操作找出满足对于所有课程该学生都选修了条件的学生。这种查询在面试中经常出现。关系除法的另一种写法SELECT sname FROM student WHERE (SELECT COUNT(*) FROM elective WHERE snostudent.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_idBR01 ) );这种嵌套子查询的写法逻辑清晰但性能可能不如连接查询SELECT c.cust_email FROM OrderItems a JOIN Orders b ON a.order_numb.order_num JOIN Customers c ON b.cust_idc.cust_id WHERE a.prod_idBR01;在数据量大时连接查询通常性能更好。计算每个客户的总订单金额SELECT b.cust_id, SUM(a.quantity*a.item_price) AS total_ordered FROM OrderItems a JOIN Orders b ON a.order_numb.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_nosalaries.emp_no GROUP BY dept_no;这类分组统计问题在业务分析中非常常见。使用JOIN查询找出没有分类的电影SELECT film.film_id, film.title FROM film LEFT JOIN film_category ON film.film_idfilm_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_noe.emp_no AND t.titleManager ); -- 使用IN SELECT * FROM employees WHERE emp_no IN ( SELECT emp_no FROM titles WHERE titleManager );在大多数情况下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_nameSmith AND first_nameJohn;复合索引的顺序很重要应该将选择性高的字段放在前面。分页查询优化-- 简单分页 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 balancebalance-100 WHERE user_id1; UPDATE accounts SET balancebalance100 WHERE user_id2; COMMIT;事务确保多个操作要么全部成功要么全部失败保证数据一致性。8. 实战案例图书馆管理系统图书馆数据库典型查询-- 查询借阅次数最多的图书 SELECT book_id, title, COUNT(*) AS borrow_count FROM books JOIN borrow_records ON books.idborrow_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_idb.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.idb.category_id JOIN borrow_records r ON b.idr.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 idp_book_id AND statusavailable; 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 statusborrowed WHERE idp_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;触发器可以自动记录数据变更用于审计追踪。