Mysql函数
一、字符函数
1、length() 获取参数值得字节个数
select length('aa'); # 2
select length('对a'); # 3+1=4
# utf8一个汉字三个字节
# jbk 一个汉字两个字节
2、concat()拼接字符串
select concat(last_name,'_',first_name) 姓名
from employees; # Kochhar_Neena
3、upper()、lower()
select upper('john'); # JOHN
select lower('joHn'); # john
4、substr、substring 字符串截取(substr同于substring)
注意:索引从1开始
# 截取从指定索引处后所有的字符
select substring('李莫愁爱上了陆展元',7) output; # 陆展元
# 截取从指定索引处后指定长度的字符,注意,都是字符,而不是字节
select substring('李莫愁爱上了陆展元',1,3) output; # 李莫愁
5、instr ()返回子串第一次出现的索引,如果找不到,返回0
select instr('杨不殷爱上殷六侠','殷六侠') as output; # 6
6、trim() 删除两端指定字符
默认去除两端空格
select length(trim(" zhang ")) as output; # 5
select length(trim('a' from "aaaaazhang aaa")) as output; # 6 (去除两端a)
7、lpad 用指定字符实现左填充指定长度
长度不足,左填充
长度超过,截取
select lpad('殷素数',10,'*') as output; # *******殷素数
select lpad('殷素数',2,'*') as output; # 殷素
8、rpad 用指定字符实现左填充指定长度
长度不足,右填充
长度超过,截取
select rpad('殷素数',10,'*') as output; # 殷素数*******
select rpad('殷素数',2,'*') as output; # 殷素
9、replace 替换
出现多次同样替换
select replace("张无忌爱上周芷若,周芷若",'周芷若','赵敏');
# 张无忌爱上赵敏,赵敏
二、数学函数
1、round() 四舍五入
对于负数,先取绝对值四舍五入,再加负号
select round(-1.50); # -2
select round(1.567,2); # 1.57
2、ceil() 向上取整
select ceil(1.0); # 1
select ceil(1.2); # 2
select ceil(-1.2); # -1
3、floor() 向下取整
select floor(9.9); # 9
select floor(-9.9); # -10
select ceil(1.0); # 1
4、truncate 截断
select truncate(1.65,1); #小数点后保留1位 #1.6
5、mod 取余
select mod(10,3); # 1
三、 日期函数
1、now() 日期+时间
select now(); # 2020-01-14 15:33:16
2、curdate 日期
select curdate(); # 2020-01-14
3、curtime 时间
select curtime(); # 14:37:27
4、获取指定部分,年,月,日,小时,分钟,秒
select year(now()); # 2019
select month(now()); # 12
select day(now());
select hour(now());
select minute(now());
select second(now())
select monthname(now()); # December
5、str_to_date 将字符串转化为时间
select str_to_date('1998-03-02','%Y-%m-%d') as output;
# 1998-03-02
6、date_format 将时间转化为字符串
select date_format(now(),'%Y年%m月%d日') as output;
# 2020年01月14日
7、 datediff 日期差
select datediff(now(),'1998-06-03') 活了几天; # 9901
四、流程控制函数
1、if()函数
方法:if(条件,成立,不成立)
select if(10<5,'大','小'); # 小
2、case 1 类似switch
既可以当语句,也可以当表达式
方法:
case 字段或者条件
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
when 常量3 then 要显示的值3或语句3;
…
else 执行默认值或语句
end
作为表达式
select salary as 原始工资 ,department_id,
case department_id
when 30 then salary*1.1
when 40 then salary*1.2
when 50 then salary*1.3
else salary
end as 新工资
from employees;
3、case 2 类似多重if
与case 1 的不同之处在于case后不需要加东西,且是区间判断
case
when 条件1 then 要显示的值1或语句1;
when 条件2 then 要显示的值2或语句2;
…
else 执行默认值或语句
end
select salary,
case
when salary>20000 then 'A'
when salary>15000 then 'B'
when salary>10000 then 'C'
else 'D'
end as 工资级别
from employees;
五、事务
1、事务的四大特性(ACID)
①原子性
操作要么全部成功,要么全部失败回滚。
②一致性
事务执行前和执行后处于一致性状态。例如,转账前A、B共5000元,A、B之间转账后,两者之和仍应该是5000元。
③隔离性
事务之间互不干扰。
④持久性
事务一旦提交,数据的改变是永久性的,即使这时候数据库发生故障,数据也不会丢失。
2、事务的隔离级别
①读未提交(read uncommitted)
事务尚未提交,其他事务即可以看到该事务的修改结果。隔离级别最差,脏读、不可重复读、幻读都不能避免。
②读提交(read committed)
事务只能看到其他事务提交之后的数据。可避免脏读,不可重复读、幻读无法避免。
不可重复读原因:A事务修改,B事务查询,A提交前和提交后,B事务看到的数据是不一致的。
幻读原因:A事务修改,B事务新增,B事务提交前,A事务已经提交。B事务提交后,A发现仍有数据未修改。
③可重复读(repeatable read)-------innodb默认隔离级别
一个事务多次查询,无论其他事务对数据如何修改,看到的数据都是一致的。因为A事务查询数据时,若B同时在修改数据,A事务看到的永远是B事务执行前的数据。只有当A提交或者回滚之后,看到的才是最新的被B修改知乎的数据。可避免脏读、不可重复读,幻读无法避免。
④序列化(serializable)
事务顺序执行,可避免脏读、不可重复读、幻读,但效率最差。因为A事务执行时,其他事务必须等待。
3、与事务隔离级别的相关问题
①脏读
A事务对一条记录进行修改,尚未提交,B事务已经看到了A的修改结果。若A发生回滚,B读到的数据就是错误的,这就是脏读。
②不可重复读
A事务对一条记录进行修改,尚未提交,B事务第一次查询该记录,看到的是修改之后的结果,此时A发生回滚,B事务又一次查询该记录,看到的是回滚后的结果。同一个事务内,B两次查询结果不一致,这就是不可重复读。
③幻读
A事务对所有记录进行修改,尚未提交,此时B事务创建了一条新记录,A、B都提交。A查看所有数据,发现有一条数据没有被修改,因为这是B事务新增的,就想看到了幻象一样,这就是幻读。
A事务对一条记录进行修改,尚未提交,B事务已经看到了A的修改结果。若A发生回滚,B读到的数据就是错误的,这就是脏读。
②不可重复读
A事务对一条记录进行修改,尚未提交,B事务第一次查询该记录,看到的是修改之后的结果,此时A发生回滚,B事务又一次查询该记录,看到的是回滚后的结果。同一个事务内,B两次查询结果不一致,这就是不可重复读。
③幻读
A事务对所有记录进行修改,尚未提交,此时B事务创建了一条新记录,A、B都提交。A查看所有数据,发现有一条数据没有被修改,因为这是B事务新增的,就想看到了幻象一样,这就是幻读。