MySQL 常用函数/多表联查基础/事务学习笔记

常用函数

字符串函数

CONCAT(s1, s2): 连接两个字符串
LOWER,UPPER: 把字符串转换为全小/大写字母
SUBSTR(str, index, len): 取字符字串，index标记的位置从1开始
INSTR(orig, tofind): 搜索字符串，返回的位置同样是从1开始，找不到返回0
TRIM(str)，移除字符串左右空格
LPAD RPAD(str, minStrLen, charToFill): str不满指定长度时用charToFill填充
REPLACE(str, target, replacement): 替换字符串

数字函数

ROUND(int, int) 四舍五入
CEIL 向上取整
FLOOR 向下取整
TRUNCATE(int, i) i:保留几位小数
MOD 取余数

时间函数

NOW(): 返回当前日期+时间
CURDATE(): 返回当前日期
CURTIME(): 返回当前时间
YEAR,MONTH,DAY,HOUR,MINUTE,SECOND(date or time)，取日期或时间的对应属性（如年，月，日）

STR_TO_DATE(date, format): 从字符串读取日期
DATE_FORMAT(date, format): 日期转换为字符串
这两个函数中的format的格式:

符号	内容
%Y	四位数年份
%y	二位数年份
%m	带前导0的月份
%c	月份
%d	带前导0的日期
%H	24小时制的小时
%h	12小时制的小时
%i	带前导0的分钟
%s	带前导0的秒

分组函数/统计函数

SUM,COUNT,MAX,MIN,AVG
这类函数会忽略NULL值，典型的就是AVG函数

DISTINCT: 去重之后再统计
如SUM(DISTINCT val)

和统计函数一同查询的字段必须是GROUP BY后面的字段

GROUP BY

GROUP BY 分组语句，通常和统计函数一起使用
分组后用条件筛选必须用GROUP BY … HAVING，HAVING后面接的条件中，*指的是这个分组中的所有数据
少用HAVING，多用WHERE

SELECT COUNT(*), `group` FROM table GROUP BY `group` HAVING COUNT(*) > 2;
SELECT COUNT(*), `group` FROM table GROUP BY `group` HAVING MAX(num) >= 5;

多表联查

首先，在多表联查时一定要带上有效的连接条件，否则将引发笛卡尔积效应，导致效率低下。

第一种连接方式

SELECT * FROM table1 t, table2 q WHERE t.id=q.id
SELECT * FROM table1 t INNER JOIN table2 q ON t.id=q.id

该语句只会选定满足筛选条件的数据。

第二种方式

SELECT * FROM table1 t LEFT/RIGHT JOIN table2 q ON t.id=q.id

LEFT代表左边的表是主表，RIGHT代表右边的表是主表，主表中的内容，无论是否满足匹配都会被筛选出来，连接后若没有匹配的数据，该数据的值全部为NULL。

事务

事务的ACID属性

原子性(Atomicity)，事务是不可分割的工作单位，要么事务全部执行要么别执行
一致性(Consistency)，事务必须使数据库从一个一致性状态变为另外一种一致性状态
隔离性(Isolation)，一个事务不能被其他事务干扰，一个事务内部的操作和使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不相互干扰
持久性(Durability)，事务一旦被提交，对数据库的改变即为持久性的，接下来的其他操作和数据库故障不应对其有任何影响

AUTOCOMMIT 设置

要使用事务，必须关闭AUTOCOMMIT。

SHOW VARIABLES LIKE 'AUTOCOMMIT';  # 显示 AUTOCOMMIT 设置
SET AUTOCOMMIT=0/1;  # 开启或关闭 AUTOCOMMIT

事务相关语句

BEGIN; START TRANSCATION; # 开始事务
ROLLBACK; # 回滚
COMMIT; # 提交
SAVEPOINT name; # 设置保存点
ROLLBACK TO name; # 回滚到保存点

并发事务带来的问题

脏读，T1读取了T2中更新但未提交的字段，如果T2回滚则T1中读取的数据就是临时且无效的
不可重复读，T1读取了一个字段，T2更新了该字段，T1再次读取同一字段将导致值不一样
幻读，T1从一个表中读取了一个字段，T2在该表中插入了一些新的行，如果T1再次读取同一张表就会多出几行

隔离级别

隔离级别	会出现的问题
READ UNCOMMITED(读未提交数据)	脏读，不可重复读，幻读
READ COMMITED(读已提交数据)	不可重复读，幻读
REPEATABLE READ(可重复读)	幻读
SERIALIZABLE(串行化)	性能低下

设置当前连接的隔离级别
SET TRANSACTION ISOLATION LEVEL level
全局设置: SET GLOBAL TRANSACTION ISOLATION LEVEL level