Mysql函数

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事务新增的，就想看到了幻象一样，这就是幻读。