MySQL查询语言与事务处理基础
SQL基础语法
数据查询语言(DQL)
常用比较运算符
在SQL查询中，比较运算符用于过滤数据：
- = : 等于
- != 或 <> : 不等于
- > : 大于
- < : 小于
- >= : 大于等于
- <= : 小于等于
- BETWEEN : 在指定范围内
- IN : 在指定集合中
- LIKE : 模糊匹配
- IS NULL : 为空
- IS NOT NULL : 不为空
常用逻辑运算符
逻辑运算符用于组合多个条件：
- AND : 与
- OR : 或
- NOT : 非
常用函数
字符串函数
CONCAT(str1, str2, ...); -- 连接字符串
CHAR_LENGTH(str); -- 返回字符串长度
UCASE(str); -- 转换为大写
LCASE(str); -- 转换为小写
MID(str, start, length); -- 提取子字符串
LTRIM(str); -- 去除左侧空格
RTRIM(str); -- 去除右侧空格
REPLACE(str, find_str, replace_str); -- 替换字符串
LEFT(str, length); -- 从左侧提取指定长度
RIGHT(str, length); -- 从右侧提取指定长度
数值函数
ROUND(number, decimals); -- 四舍五入
CEIL(number); -- 向上取整
FLOOR(number); -- 向下取整
ABS(number); -- 绝对值
SQRT(number); -- 平方根
RAND(); -- 生成随机数(0-1)
MOD(x, y); -- 取模运算
日期和时间函数
NOW(); -- 当前日期和时间
CURRENT_DATE(); -- 当前日期
CURRENT_TIME(); -- 当前时间
DATE(datetime); -- 提取日期部分
TIME(datetime); -- 提取时间部分
YEAR(date); -- 提取年份
MONTH(date); -- 提取月份
DAY(date); -- 提取日期
DATE_ADD(date, INTERVAL value unit); -- 日期加法
DATE_SUB(date, INTERVAL value unit); -- 日期减法
DATEDIFF(date1, date2); -- 计算日期差
STR_TO_DATE(str, format); -- 字符串转日期
DATE_FORMAT(date, format); -- 日期格式化
基本查询语法
SELECT 列名列表 FROM 表名 WHERE 条件表达式;
单表查询
基本查询操作
查询指定字段
SELECT 列1, 列2, 列3 FROM 表名;
查询所有字段
SELECT * FROM 表名;
字段别名设置
SELECT 列1 [AS] 别名1, 列2 [AS] 别名2 FROM 表名;
注意：AS关键字可省略，若别名包含特殊字符需用单引号或双引号包裹。
去除重复记录
SELECT DISTINCT 列名 FROM 表名;
分组查询
基础语法
SELECT 聚合函数(列名) FROM 表名;
常用聚合函数
- COUNT() : 计数
- SUM() : 求和
- AVG() : 平均值
- MAX() : 最大值
- MIN() : 最小值
分组扩展语法
SELECT 列名列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段 [HAVING 分组后过滤条件];
WHERE与HAVING的区别
- 执行时机不同：WHERE在分组前过滤，HAVING在分组后过滤
- 功能不同：WHERE不能使用聚合函数，HAVING可以
排序查询
基础语法
SELECT 列名列表 FROM 表名 [WHERE 条件] [GROUP BY 分组字段] ORDER BY 列名1 [排序方式1], 列名2 [排序方式2];
排序方式
- ASC : 升序（默认）
- DESC : 降序
分页查询
基础语法
SELECT 列名列表 FROM 表名 LIMIT 起始索引, 查询记录数;
注意事项
- 起始索引从0开始
- LIMIT子句通常放在查询语句的最后
多表查询
连接查询
内连接
内连接返回两个表中满足连接条件的记录。
隐式内连接
SELECT 列名列表 FROM 表1, 表2 WHERE 表1.关联字段 = 表2.关联字段;
显式内连接
SELECT 列名列表 FROM 表1 [INNER] JOIN 表2 ON 表1.关联字段 = 表2.关联字段;
注意：INNER关键字可以省略。
外连接
外连接返回一个表中的所有记录，以及另一个表中匹配的记录。
左外连接
SELECT 列名列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 表1.关联字段 = 表2.关联字段;
注意：OUTER关键字可以省略。
右外连接
SELECT 列名列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 表1.关联字段 = 表2.关联字段;
注意：OUTER关键字可以省略。
子查询
子查询是嵌套在其他SQL查询中的SELECT语句。
标量子查询
返回单个值（一行一列）。
SELECT * FROM 表1 WHERE 列名 = (SELECT 列名 FROM 表2 WHERE 条件);
列子查询
返回单列多行数据（一列多行）。
SELECT * FROM 表1 WHERE 列名 IN (SELECT 列名 FROM 表2 WHERE 条件);
行子查询
返回单行多列数据（一行多列）。
SELECT * FROM 表1 WHERE (列1, 列2) = (SELECT 列1, 列2 FROM 表2 WHERE 条件);
表子查询
返回多行多列数据，可作为虚拟表使用。
SELECT * FROM 表1 WHERE 列名 IN (SELECT 列1, 列2 FROM 表2 WHERE 条件);
事务控制语言(TCL)
事务概念
事务是一组SQL操作的集合，这些操作要么全部成功执行，要么全部回滚。事务保证了数据库操作的原子性、一致性、隔离性和持久性（ACID特性）。
注意事项
在MySQL中，默认情况下每条DML语句（INSERT、UPDATE、DELETE）都会自动提交事务。要使用事务，需要先禁用自动提交模式。
主要语法
START TRANSACTION; -- 开启事务
-- 执行一系列SQL操作
COMMIT; -- 提交事务，使更改永久生效
ROLLBACK; -- 回滚事务，撤销所有更改
事务控制语句扩展
SAVEPOINT 保存点名; -- 设置保存点
ROLLBACK TO 保存点名; -- 回滚到指定保存点
SET autocommit = 0; -- 禁用自动提交
SET autocommit = 1; -- 启用自动提交
SAVEPOINT 保存点名; -- 设置保存点
ROLLBACK TO 保存点名; -- 回滚到指定保存点
SET autocommit = 0; -- 禁用自动提交
SET autocommit = 1; -- 启用自动提交
