MySQL 笔记6 – 函数与事务

一、内置函数

1、函数

  • 事先编写的实现某些功能的代码集合，可以直接调用
  • 函数可以用在SELECT语句及其子句，也可以用在UPDATE,DELETE语句当中
  • 格式：SELECT 函数
  • 函数分类：字符串函数、数值函数、日期和时间函数、流程函数、其它函数

2、字符串函数

函数

说明

COMCAT(s1,s2…sn)

将传入的字符连接成一个字符串，任何字符串与null进行连接结果都是null

INSERT(str,x,y,instr)

将字符串str从x(从1开始)位置开始，y个字符长的子串替换为instr

LOWER(Str)/UPPER(str)

将字符串转成小/大写

LEFT(str,x)/RIGHT(str，x)

返回字符串最左/右边的x个字符，x=null 不返回任何字符

LPAD(str,n,pad)/RPAD(str,n,pad)

用字符串pad对str最左/右边进行填充，直到长度为n个字符

REPEAT(str,x)

返回str重复x次的结果

REPLACE(str,a,b)

用字符串b替换字符串str中所有出现的字符串a

SUBSTRING(str,x,y)

返回字符串str中第x位置起y个字符长度的字符

3、数值函数

函数

说明

ABS(x)

返回X的绝对值

CEIL(x)

小数不为零部分上取整，即向上取最近的整数

FLOOR(x)

小数部分下取整，即向下取最近的整数

MOD(X,Y)

返回X/Y的模

RAND()

返回0-1的随机值

RAND()*n

返回0-n的随机值

4、日期和时间函数

函数：

函数

说明

CURDATE()

返回当前日期，只包含年月日

CURTIME()

返回当前时间，只包含时分秒

NOW()

返回当前日期和时间，年月日时分秒都包含

UNIX_TIMESTAMP

返回当前日期的时间戳

FROM_UNIXTIME(unixtime)

将一个时间戳转换成日期

WEEK(DATE)

返回当前是一年中的第几周

YEAR(DATE)

返回所给日期是那一年

HOUR(TIME)

返回当前时间的小时

MINUTE(TIME)

返回当前时间的分钟

DATE_FORMAT(date,fmt)

按字符串格式化日期date值

DATE_ADD(date,interval expr type)

计算日期相加

DATEDIFF(date1,date2)

计算两个日期相差的天数

格式化日期的格式符：

  • 用在fmt中，形如’%M%D%Y’
  • 可以在格式符中插入自定义的符号，形如’%M-%D-%Y’

格式符

说明

%a

缩写星期名

%b

缩写月名

%c

月，数值

%D

带有英文前缀的月中的天

%d

月的天，数值(00-31)

%e

月的天，数值(0-31)

%f

微秒

%H

小时 (00-23)

%h

小时 (01-12)

%I

小时 (01-12)

%i

分钟，数值(00-59)

%j

年的天 (001-366)

%k

小时 (0-23)

%l

小时 (1-12)

%M

月名

%m

月，数值(00-12)

%p

AM 或 PM

%r

时间，12-小时（hh:mm:ss AM 或 PM）

%S

秒(00-59)

%s

秒(00-59)

%T

时间, 24-小时 (hh:mm:ss)

%U

周 (00-53) 星期日是一周的第一天

%u

周 (00-53) 星期一是一周的第一天

%V

周 (01-53) 星期日是一周的第一天，与 %X 使用

%v

周 (01-53) 星期一是一周的第一天，与 %x 使用

%W

星期名

%w

周的天 （0=星期日, 6=星期六）

%X

年，其中的星期日是周的第一天，4 位，与 %V 使用

%x

年，其中的星期一是周的第一天，4 位，与 %v 使用

%Y

年，4 位

%y

年，2 位

5、流程函数

函数

说明

IF(value,t,f)

如果value是真，返回t,否则返回f

IFNULL(value1,value2)

如果value1不为空，返回value1否者返回value2

CASE WHEN value THEN t ELSE f END

当value是真，返回t,否则返回f

6、其它函数

函数

说明

DATABASE()

返回当前数据库名

VERSION()

返回当前数据库版本

USER()

返回当前登陆用户名

PASSWORD(STR)

对str进行加密

MD5()

返回str的MD5值

二、自定义函数

1、修改命令结束符

  • 格式：DELIMITER x，将标准分隔符 ; 更改为自定义的 x
  • 因为定义函数时，函数是作为一个整体的，而函数内是多条单独的语句。为了不让让mysql遇到 ；号就解释语句，需要在定义函数开头修改分隔符，完成后把分隔符还原为 ；

2、自定义函数

DELIMITER 自定义分隔符
CREATE FUNCTION 函数名() RETURNS 数据类型
begin
语句1；
语句2；
....
return 和要求的数据类型一样的数据；
end 自定义分隔符
DELIMITER ;
  • 调用自定义函数格式跟内置函数一致

三、事务

1、事务

  • 不可分割的操作，假设某操作有多个步骤组成，其中任意一个步骤操作失败，则认为事务失败，所有步骤全完成该操作才是成功
  • 每条SQL语句都是一个事务，可以把多条SQL语句定义成一个事务
  • 事务只对DML语句有效,对于DQL无效

2、事务的ACID

  • 原子性（Atomicity）：事务包含的所有操作要么全部成功，要么全部失败回滚
  • 一致性（Consistency）：事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态，让数据保持一定上的合理，如：一个商品出库时，仓库商品数量减1，对应用户的购物车中商品加1
  • 隔离性（Isolation）：当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离
  • 持久性（Durability）：一个事务一旦被提交了，就不能再回滚了，已经把数据保存到数据库当中了

3、事务的使用

操作

语句

说明

开启事务

start transaction

回滚事务

rollback

销执行的sql语句，必须在提交事务之前

提交事务

commit

事务中的所有语句全部执行完毕时提交事务，若没有发生异常，更新到数据库中

mysql命令行怎么调用函数_内置函数

4、事务隔离级别

隔离级别：隔离级别越高，数据的完整性和一致性越好，并发性能越低

级别

说明

Read uncommitted（读未提交）

未提交事务B修改了数据，事务A读到就是事务B修改的数据；

Read committed（读已提交）

未提交事务B修改了数据，事务A读到的数据还是之前的；事务B提交后A读到的数据是B修改后的数据；

Repeatable read（可重复读）

未提交事务A开始读取数据后，在提交前读到的数据（select）都是此刻生成的版本，不管事务B是否修改数据是否提交；事务A提交前的insert、update和delete会更新版本为已提交事务B生成的版本；

Serializable（串行）

事务串行化顺序执行，事务A执行后才能执行事务B，同时执行会报错；

查看隔离级别：

  • select @@global.tx_isolation,@@tx_isolation;

设置隔离级别：

  • 全局的：set global transaction isolation level 隔离级别;
  • 当前会话： set session transaction isolation level 隔离级别;

5、事务的并发

问题：

问题

说明

脏读

事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

不可重复读

事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致

幻读

未提交事务A查询数据后，已提交事务B刷新了数据库，事务A提交后再查询与上次查询的数据不一致；

各隔离级别会出现的问题：

隔离级别

脏读

不可重复读

幻读

Read uncommitted

是

是

是

Read committed

否

是

是

Repeatable read

否

否

是

Serializable

否

否

否

GOOD LUCK!