一、SQL 基础与 DDL1. 数据库操作-- 创建数据库 CREATE DATABASE dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; -- 删除数据库 DROP DATABASE IF EXISTS dbname; -- 选择数据库 USE dbname;2. 表操作-- 创建表 CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) NOT NULL UNIQUE, email VARCHAR(100) UNIQUE, age INT CHECK (age 0), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 修改表结构 ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email, MODIFY COLUMN username VARCHAR(100) NOT NULL, DROP COLUMN old_column, ADD INDEX idx_email (email); -- 删除表 DROP TABLE IF EXISTS users; -- 清空表不可回滚 TRUNCATE TABLE users;二、DML 数据操作1. 增删改查 CRUD-- INSERT INSERT INTO users (username, email, age) VALUES (john, johnexample.com, 25), (jane, janeexample.com, 30); -- 插入查询结果 INSERT INTO user_backup SELECT * FROM users WHERE age 20; -- UPDATE UPDATE users SET age age 1, updated_at NOW() WHERE id 1; -- 多表更新 UPDATE users u JOIN orders o ON u.id o.user_id SET u.order_count u.order_count 1 WHERE o.status completed; -- DELETE DELETE FROM users WHERE age 18; DELETE u FROM users u WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.user_id u.id );三、SELECT 查询核心先执行FROM子句确定要查询的数据来源接着执行WHERE子句筛选出符合条件的记录然后执行GROUP BY子句对筛选后的数据进行分组再执行HAVING子句对分组后的数据进行筛选之后执行SELECT子句选择要查询的列最后执行ORDER BY子句对查询结果进行排序。having只能跟在group by后面1. 基础查询-- 基本结构 SELECT DISTINCT column1, column2, ... FROM table_name WHERE conditions GROUP BY columns HAVING group_conditions ORDER BY columns LIMIT n OFFSET m;ASC​ - 升序默认从小到大A 到 Z。DESC​ - 降序从大到小Z 到 A语法SELECT 列1, 列2 FROM 表名 ORDER BY 列1 DESC; -- 按列1降序排列LIMIT {要返回的记录数} OFFSET {要跳过的记录数}或简化写法LIMIT {跳过的记录数}, {要返回的记录数}详细说明语法1使用 OFFSET 关键字SELECT * FROM 表名LIMIT 5 OFFSET 10;解释跳过前 10 条记录返回接下来的 5 条记录。2. WHERE 条件-- 比较运算符 SELECT * FROM products WHERE price 100; SELECT * FROM users WHERE age BETWEEN 18 AND 60; SELECT * FROM users WHERE name IN (John, Jane); -- 模糊查询 SELECT * FROM products WHERE name LIKE apple%; -- 以apple开头 SELECT * FROM products WHERE name LIKE %phone%; -- 包含phone SELECT * FROM products WHERE name LIKE c_mputer;-- 单个字符匹配 -- NULL处理 SELECT * FROM users WHERE email IS NULL; SELECT * FROM users WHERE email IS NOT NULL;3. 聚合函数-- 常用聚合函数 SELECT COUNT(*) as total_rows, COUNT(DISTINCT user_id) as unique_users, SUM(amount) as total_amount, AVG(price) as average_price, MAX(price) as max_price, MIN(price) as min_price FROM orders;聚合函数是对分组后的数据进行计算聚合函数不能直接用在where子句里。因为WHERE子句执行的时候还没进行分组和聚合操作四、JOIN 连接查询1. 连接类型对比from 表1 join 表2 on 条件-- INNER JOIN内连接 SELECT u.name, o.order_no FROM users u INNER JOIN orders o ON u.id o.user_id; -- LEFT JOIN左外连接 SELECT u.name, o.order_no FROM users u LEFT JOIN orders o ON u.id o.user_id; -- RIGHT JOIN右外连接 SELECT u.name, o.order_no FROM users u RIGHT JOIN orders o ON u.id o.user_id; -- FULL OUTER JOIN全外连接- MySQL不支持 -- 通过UNION模拟 SELECT u.name, o.order_no FROM users u LEFT JOIN orders o ON u.id o.user_id UNION SELECT u.name, o.order_no FROM users u RIGHT JOIN orders o ON u.id o.user_id; -- CROSS JOIN交叉连接 SELECT * FROM colors CROSS JOIN sizes;2. 自连接-- 查询每个员工的经理 SELECT e.name as employee, m.name as manager FROM employees e LEFT JOIN employees m ON e.manager_id m.id;五、子查询1. 标量子查询-- 返回单个值 SELECT name, (SELECT AVG(price) FROM products) as avg_price FROM products; SELECT * FROM products WHERE price (SELECT AVG(price) FROM products);2. 行子查询SELECT * FROM users WHERE (age, salary) ( SELECT MIN(age), MAX(salary) FROM users );3. 列子查询-- IN子查询 SELECT * FROM products WHERE category_id IN ( SELECT id FROM categories WHERE status active ); -- ANY/SOME子查询 SELECT * FROM products WHERE price ANY ( SELECT price FROM products WHERE category premium ); -- ALL子查询 SELECT * FROM products WHERE price ALL ( SELECT price FROM products WHERE category basic );4. 表子查询-- FROM子句中的子查询 SELECT * FROM ( SELECT user_id, COUNT(*) as order_count FROM orders GROUP BY user_id ) t WHERE order_count 5;六、窗口函数MySQL 8.01. 排名函数-- ROW_NUMBER() 连续排名 SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) as rank FROM employees; -- RANK() 并列有间隔 SELECT name, salary, RANK() OVER (ORDER BY salary DESC) as rank FROM employees; -- DENSE_RANK() 并列无间隔 SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rank FROM employees;2. 分布函数-- NTILE(n) 分组 SELECT name, salary, NTILE(4) OVER (ORDER BY salary DESC) as quartile FROM employees;3. 前后值函数SELECT date, revenue, LAG(revenue, 1) OVER (ORDER BY date) as prev_revenue, LEAD(revenue, 1) OVER (ORDER BY date) as next_revenue FROM daily_sales;4. 聚合窗口函数SELECT department, name, salary, SUM(salary) OVER (PARTITION BY department) as dept_total, AVG(salary) OVER (PARTITION BY department) as dept_avg, salary * 100.0 / SUM(salary) OVER (PARTITION BY department) as percentage FROM employees;七、高级查询技巧1. CASE表达式SELECT name, age, CASE WHEN age 18 THEN 未成年 WHEN age BETWEEN 18 AND 60 THEN 成年 ELSE 老年 END as age_group, CASE status WHEN 1 THEN 激活 WHEN 0 THEN 禁用 ELSE 未知 END as status_desc FROM users;2. 分组筛选-- 分组后筛选 SELECT department, COUNT(*) as emp_count FROM employees GROUP BY department HAVING COUNT(*) 5; -- HAVING 在分组后过滤 -- WHERE 在分组前过滤 SELECT department, AVG(salary) as avg_salary FROM employees WHERE hire_date 2020-01-01 GROUP BY department;3. 分页查询优化-- 传统分页大数据量慢 SELECT * FROM orders ORDER BY id LIMIT 10000, 20; -- 优化分页使用索引 SELECT * FROM orders WHERE id 10000 -- 记住上次的最大ID ORDER BY id LIMIT 20; -- 子查询优化 SELECT * FROM orders WHERE id (SELECT id FROM orders ORDER BY id LIMIT 10000, 1) ORDER BY id LIMIT 20;八、索引优化1. 索引类型-- 创建索引 CREATE INDEX idx_name ON users(name); CREATE UNIQUE INDEX idx_email ON users(email); CREATE INDEX idx_name_age ON users(name, age); -- 复合索引 CREATE FULLTEXT INDEX idx_content ON articles(content); -- 全文索引 -- 删除索引 DROP INDEX idx_name ON users;2. 最左前缀原则-- 假设有复合索引 (a, b, c) -- 有效使用索引 SELECT * FROM t WHERE a 1; SELECT * FROM t WHERE a 1 AND b 2; SELECT * FROM t WHERE a 1 AND b 2 AND c 3; SELECT * FROM t WHERE a 1 AND c 3; -- 只用到了a -- 无效使用索引 SELECT * FROM t WHERE b 2; -- 无法使用索引 SELECT * FROM t WHERE b 2 AND c 3; -- 无法使用索引九、事务控制1. ACID 特性-- 开启事务 START TRANSACTION; -- 或 BEGIN; -- 事务操作 UPDATE accounts SET balance balance - 100 WHERE id 1; UPDATE accounts SET balance balance 100 WHERE id 2; -- 提交或回滚 COMMIT; -- 提交事务 ROLLBACK; -- 回滚事务 -- 设置保存点 SAVEPOINT sp1; -- 回滚到保存点 ROLLBACK TO SAVEPOINT sp1;2. 事务隔离级别-- 查看当前隔离级别 SELECT transaction_isolation; -- 设置隔离级别 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 级别READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE十、视图与存储过程1. 视图-- 创建视图 CREATE VIEW user_orders AS SELECT u.id, u.name, COUNT(o.id) as order_count FROM users u LEFT JOIN orders o ON u.id o.user_id GROUP BY u.id, u.name; -- 使用视图 SELECT * FROM user_orders WHERE order_count 5; -- 修改视图 CREATE OR REPLACE VIEW user_orders AS ...; -- 删除视图 DROP VIEW IF EXISTS user_orders;2. 存储过程DELIMITER // CREATE PROCEDURE get_user_orders(IN user_id INT) BEGIN SELECT * FROM orders WHERE user_id user_id ORDER BY created_at DESC; END // DELIMITER ; -- 调用存储过程 CALL get_user_orders(123);十一、SQL 优化考点1. EXPLAIN 分析EXPLAIN SELECT * FROM users WHERE age 20;字段说明优化目标type访问类型至少range最好const/refkey使用的索引必须使用索引rows预估扫描行数越少越好Extra额外信息避免Using filesort/temporary2. 优化技巧-- 1. 避免 SELECT * SELECT id, name FROM users; -- 只选择需要的列 -- 2. 使用 LIMIT SELECT * FROM users LIMIT 100; -- 3. 避免在WHERE中对字段做计算 SELECT * FROM users WHERE YEAR(created_at) 2024; -- ❌ SELECT * FROM users WHERE created_at 2024-01-01; -- ✅ -- 4. 避免使用OR SELECT * FROM users WHERE age 20 OR age 30; -- ❌ SELECT * FROM users WHERE age IN (20, 30); -- ✅ -- 5. 使用EXISTS代替IN SELECT * FROM users WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id users.id);十二、常见面试题1. 排名问题-- 查询每个部门工资前三高的员工 SELECT * FROM ( SELECT department, name, salary, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank FROM employees ) t WHERE rank 3;2. 连续登录问题-- 查询连续登录7天的用户 SELECT user_id FROM ( SELECT user_id, login_date, DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) as diff FROM login_logs ) t GROUP BY user_id, diff HAVING COUNT(*) 7;3. 行转列/列转行-- 行转列PIVOT SELECT student_id, MAX(CASE WHEN subject Math THEN score END) as Math, MAX(CASE WHEN subject English THEN score END) as English FROM scores GROUP BY student_id; -- 列转行UNPIVOT SELECT student_id, Math as subject, Math as score FROM scores UNION ALL SELECT student_id, English as subject, English as score FROM scores;4. 删除重复数据-- 保留id最小的重复数据 DELETE FROM users WHERE id NOT IN ( SELECT MIN(id) FROM users GROUP BY email HAVING COUNT(*) 1 );十三、SQL 注入防护1. 预编译语句-- Java中使用PreparedStatement String sql SELECT * FROM users WHERE username ? AND password ?; PreparedStatement pstmt connection.prepareStatement(sql); pstmt.setString(1, username); pstmt.setString(2, password);2. 输入验证// 对输入进行验证 if (!username.matches(^[a-zA-Z0-9_]{3,20}$)) { throw new IllegalArgumentException(Invalid username); }面试准备要点熟练掌握JOIN的各种用法和区别深入理解子查询和连接的性能差异掌握窗口函数的应用场景熟悉EXPLAIN分析SQL执行计划了解索引优化原则准备实际优化案例熟悉事务和锁机制了解SQL注入防护高频考点多表JOIN查询子查询 vs JOIN性能分组聚合和HAVING窗口函数应用索引优化策略事务隔离级别慢SQL优化分页查询优化