-- 查询年龄大于18岁的 select * from emp where age>19; -- 查询年龄小于20岁的 select * from emp where age<20; -- 查询没有身份证号的员工信息 select * from emp where card is null; -- 查询有身份证的员工信息 select * from emp where card is not null; -- 查询年龄在19(包括)和20(包括)岁之间的职员信息 select * from emp where age>=19 && age<=20; -- BETWEEN...and.. 在某个范围之间(最小值 and 最大值) select * from emp where age between 19 and 20;
-- 查询性别为女，年龄小于20岁的职员信息 select * from emp where sex='女' and age<20;
-- 查询eid等于 2或者3或者6的员工信息 select * from emp where eid=2 or eid=3 or eid=6; -- in 在in之后列表中的值 多选 select * from emp where eid in(2,3,6);
SELECT s.StuName, c.ClassName FROM StudentInfo s INNERJOIN ClassInfo c ON s.SClassID = c.ClassID
隐式内连接
使用 WHERE 子句指定连接条件（不推荐）：
1 2 3
SELECT s.StuName, c.ClassName FROM StudentInfo s, ClassInfo c WHERE s.SClassID = c.ClassID
特点：
只返回两表中匹配的行
显式写法更清晰，推荐使用
2. 外连接 (OUTER JOIN)
左外连接 (LEFT JOIN)
返回左表所有行，右表无匹配则显示NULL：
1 2 3
SELECT s.StuName, e.ExamName FROM StudentInfo s LEFTJOIN StudentExam e ON s.StuID = e.EStuID
右外连接 (RIGHT JOIN)
返回右表所有行，左表无匹配则显示NULL：
1 2 3
SELECT s.StuName, e.ExamName FROM StudentInfo s RIGHTJOIN StudentExam e ON s.StuID = e.EStuID
内外连接对比
特性
内连接
外连接
结果集
只返回匹配行
返回匹配行+未匹配行(补NULL)
性能
通常更快
稍慢
用途
需要精确匹配时
需要保留所有记录时
NULL处理
不包含NULL
包含NULL
特点：
左连接保留左表全部数据
右连接保留右表全部数据
实际开发中左连接使用更频繁
3. 自连接 (SELF JOIN)s
表与自身连接，常用于层级数据查询：
1 2 3 4
-- 查询员工及其经理信息 SELECT e1.EmpName AS 员工, e2.EmpName AS 经理 FROM Employee e1 LEFTJOIN Employee e2 ON e1.ManagerID = e2.EmpID
特点：
必须使用表别名区分
常用于组织结构、分类层级等场景
4. 交叉连接 (CROSS JOIN)
返回两表的笛卡尔积（所有可能的组合）：
1 2 3
SELECT s.StuName, c.ClassName FROM StudentInfo s CROSSJOIN ClassInfo c
特点：
结果行数 = 表1行数 × 表2行数
谨慎使用，可能产生大量数据
5. 子查询 (SUBQUERY)
WHERE 子句中的子查询
1 2 3 4
-- 查询高于平均分的学员 SELECT StuName, Score FROM StudentExam WHERE Score > (SELECTAVG(Score) FROM StudentExam)
FROM 子句中的子查询（派生表）
1 2 3 4 5 6 7 8 9
-- 查询每个班级的平均分 SELECT c.ClassName, t.AvgScore FROM ClassInfo c JOIN ( SELECT SClassID, AVG(Score) AS AvgScore FROM StudentInfo s JOIN StudentExam e ON s.StuID = e.EStuID GROUPBY SClassID ) t ON c.ClassID = t.SClassID
SELECT 子句中的子查询
1 2 3 4 5 6 7 8 9 10
-- 查询学生及其班级平均分 SELECT s.StuName, (SELECTAVG(Score) FROM StudentExam e WHERE e.EStuID IN ( SELECT StuID FROM StudentInfo WHERE SClassID = s.SClassID )) AS ClassAvgScore FROM StudentInfo s
子查询类型：
类型
说明
示例
标量子查询
返回单个值
SELECT ... WHERE col > (SELECT AVG(col) FROM ...)
列子查询
返回单列多行
SELECT ... WHERE col IN (SELECT col FROM ...)
行子查询
返回单行多列
SELECT ... WHERE (col1,col2) = (SELECT col1,col2 FROM ...)
表子查询
返回多行多列
FROM (SELECT ...) AS t
七、高级特性
1. 事务控制
1 2 3 4 5 6 7
START TRANSACTION; UPDATE accounts SET balance = balance - 1000 WHERE user_id = 1; UPDATE accounts SET balance = balance + 1000 WHERE user_id = 2; COMMIT; -- 或 ROLLBACK;
2. 视图创建
1 2 3 4
CREATE VIEW emp_dept_view AS SELECT e.name, e.salary, d.department_name FROM employees e JOIN departments d ON e.department_id = d.id;
3. 索引优化
1 2 3 4 5 6 7
-- 创建索引 CREATE INDEX idx_product_name ON products(name);
-- 联合索引 CREATE INDEX idx_emp_name_dept ON employees(name, department_id);
-- 查看执行计划 EXPLAIN SELECT * FROM products WHERE price > 100;
-- 开启慢查询日志 SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 2;
3. 安全设置
1 2 3 4
-- 创建用户并授权 CREATE USER 'app_user'@'%' IDENTIFIED BY 'password123'; GRANT SELECT, INSERT ON shop.* TO 'app_user'@'%'; FLUSH PRIVILEGES;
十、实战练习
1. 多表联合查询
1 2 3 4 5 6 7
SELECT o.order_id, c.customer_name, p.product_name FROM orders o JOIN customers c ON o.customer_id = c.customer_id JOIN order_items oi ON o.order_id = oi.order_id JOIN products p ON oi.product_id = p.product_id WHERE o.order_date > '2023-01-01' ORDER BY o.order_date DESC;
2. 复杂统计报表
1 2 3 4 5 6 7 8 9 10
SELECT YEAR(order_date) AS '年份', MONTH(order_date) AS '月份', COUNT(*) AS '订单数', SUM(amount) AS '总金额', AVG(amount) AS '平均金额' FROM orders GROUP BY YEAR(order_date), MONTH(order_date) HAVING COUNT(*) > 10 ORDER BY 年份 DESC, 月份 ASC;
3. 数据清洗转换
1 2 3 4 5 6 7 8
-- 将旧数据迁移到新表 INSERT INTO new_employees(id, full_name, contact) SELECT emp_id, CONCAT(first_name, ' ', last_name), COALESCE(mobile, email, '无联系方式') FROM old_employee_data WHERE status = 'active';
