MySQL入门-day05-函数与事务

学习目标：

• 掌握常用的函数
• 学习数据库事务
  • 了解事务相关信息
  • 数据库锁

函数

数学函数

函数	说明
ABS(X)	返回X的绝对值。
FLOOR(X)	返回不大于X的最大整数。
CEIL(X)、CEILING(X)	返回不小于X的最小整数。
TRUNCATE(X,D)	返回数值X保留到小数点后D位的值，截断时不进行四舍五入。
ROUND(X)	返回离X最近的整数，截断时要进行四舍五入。
ROUND(X,D)	保留X小数点后D位的值，截断时要进行四舍五入。
RAND()	返回0~1的随机数。
SIGN(X)	返回X的符号(负数，零或正)对应-1，0或1。
PI()	返回圆周率的值。默认的显示小数位数是6位。
POW(x,y)、POWER(x,y)	返回x的y次乘方的值。
SQRT(x)	返回非负数的x的二次方根。
EXP(x)	返回e的x乘方后的值。
MOD(N,M)	返回N除以M以后的余数。
LOG(x)	返回x的自然对数，x相对于基数2的对数。
LOG10(x)	返回x的基数为10的对数。
RADIANS(x)	返回x由角度转化为弧度的值。
DEGREES(x)	返回x由弧度转化为角度的值。
SIN(x)、ASIN(x)	前者返回x的正弦，其中x为给定的弧度值；后者返回x的反正弦值，x为正弦。
COS(x)、ACOS(x)	前者返回x的余弦，其中x为给定的弧度值；后者返回x的反余弦值，x为余弦。
TAN(x)、ATAN(x)	前者返回x的正切，其中x为给定的弧度值；后者返回x的反正切值，x为正切。
COT(x)	返回给定弧度值x的余切。

数学函数是MySQL中常用的一类函数。其主要用于处理数字，包括整型和浮点数等等。
只说明常用的函数，其他的请看表

向上取整：CEIL(X)、CEILING(X)

具体案例：

//结果为2
select ceil(1.2); 

向下取整：FLOOR(X)

具体案例：

//结果为1
select FLOOR(1.2); 

四舍五入：ROUND(X)、ROUND(X,D)

具体案例：

//结果为2
SELECT ROUND(1.5);; 
//对1.45四舍五入，保留一位小数--结果为1.5
SELECT ROUND(1.45,1);	

随机数：RAND()返回一个0-1之间的随机小数

具体案例：

//结果为0-1的随机数
select RAND(); 
//一个固定的0-1的随机数
select RAND(1); 

由此可知括号内是随机种子,那如何获得指定范围的数字呢？

扩展：获得指定范围的随机数

语法：ROUND(RAND()*范围)返回一个0-范围的随机数

//获得一个0-9的随机小数，在通过round来四舍五入即可获得0-10的随机数
SELECT ROUND(RAND()*10)

平方数：POW(x,y)、POWER(x,y)//返回x和y平方

二者是否有区别，有知道的可以说一下
具体案例：

//结果为27
select POW(3,3); 
//结果也为27
select POWER(3,3); 

平方根：SQRT(x)//返回x的平方根

具体案例：

//结果为2
select SQRT(4); 

两个常用的数

PI()返回圆周率的值。默认的显示小数位数是6位。
EXP(x)返回e的x乘方后的值，x为1即为e

字符串函数

字符串函数是MySQL中最常用的一类函数。字符串函数主要用于处理表中的字符串。

函数	说明
CHAR_LENGTH(str)	计算字符串字符个数。
LENGTH(str)	返回值为字符串str的长度，单位为字节。
CONCAT(s1,s2，…)	返回连接参数产生的字符串，一个或多个待拼接的内容，任意一个为NULL则返回值为NULL。
SUBSTRING(s,n,len)、MID(s,n,len)	两个函数作用相同，从字符串s中返回一个第n个字符开始、长度为len的字符串。
CONCAT_WS(x,s1,s2,…)	返回多个字符串拼接之后的字符串，每个字符串之间有一个x。
INSERT(s1,x,len,s2)	返回字符串s1，其子字符串起始于位置x，被字符串s2取代len个字符。
INSTR(s1, s2)	返回字符串s2，在字符串s1中开始的位置，下标从1开始，没有找到返回0。
LOWER(str)、LCASE(str)	将str中的字母全部转换成小写。
UPPER(str)、UCASE(str)	将字符串中的字母全部转换成大写。
LEFT(s,n)、RIGHT(s,n)	前者返回字符串s从最左边开始的n个字符，后者返回字符串s从最右边开始的n个字符。
LPAD(s1,len,s2)、RPAD(s1,len,s2)	前者返回s1，其左边由字符串s2填补到len字符长度，假如s1的长度大于len，则返回值被缩短至len字符；前者返回s1，其右边由字符串s2填补到len字符长度，假如s1的长度大于len，则返回值被缩短至len字符。
LTRIM(s)、RTRIM(s)	前者返回字符串s，其左边所有空格被删除；后者返回字符串s，其右边所有空格被删除。
TRIM(s)	返回字符串s删除了两边空格之后的字符串。
TRIM(s1 FROM s)	删除字符串s两端所有子字符串s1，未指定s1的情况下则默认删除空格。
REPEAT(s,n)	返回一个由重复字符串s组成的字符串，字符串s的数目等于n。
SPACE(n)	返回一个由n个空格组成的字符串。
REPLACE(s,s1,s2)	返回一个字符串，用字符串s2替代字符串s中所有的字符串s1。
STRCMP(s1,s2)	若s1和s2中所有的字符串都相同，则返回0；根据当前分类次序，第一个参数小于第二个则返回-1，其他情况返回1。
LOCATE(str1,str)、POSITION(str1 IN str)、INSTR(str,str1)	三个函数作用相同，返回子字符串str1在字符串str中的开始位置（从第几个字符开始）。
REVERSE(s)	将字符串s反转。
ELT(N,str1,str2,str3,str4,…)	返回第N个字符串。
FIELD(s,s1,s2,…)	返回第一个与字符串s匹配的字符串的位置。
FIND_IN_SET(s1,s2)	返回在字符串s2中与s1匹配的字符串的位置。
MAKE_SET(x,s1,s2,…)	按x的二进制数从s1，s2…，sn中选取字符串

获取某个字符串的长度LENGTH(str)

不同字符编码的相同汉字返回长度不同
具体案例：

//结果为9
SELECT LENGTH(123456789); 
//结果为9
SELECT LENGTH('啊啊啊');

计算字符串字符个数CHAR_LENGTH(str)

具体案例：

//结果为9
select CHAR_LENGTH(123456789); 
//结果为3
SELECT CHAR_LENGTH('啊啊啊');

连接字符串CONCAT(s1,s2，...)

将括号内的字符串连接起来，若有一个为null，则返回值为空
具体案例：

//结果为3
select '1'+'2'; 
//结果为12
select CONCAT('1','2');

截取字符串SUBSTRING(s,n,len)、MID(s,n,len)

两个函数作用相同，从字符串s中返回一个第n个字符开始、长度为len的字符串。

具体案例：

//结果均为234
SELECT SUBSTRING('123456',2,3),MID('123456',2,3);

删除空格TRIM(s)、TRIM(s1 FROM s)

TRIM(s)：返回字符串s删除了两边空格之后的字符串。
TRIM(s1 FROM s)：删除字符串s两端所有子字符串s1，未指定s1的情况下则默认删除空格。
具体案例：

//结果为1 1
SELECT TRIM('   1 1 ');
//结果为2
SELECT TRIM('1' FROM '121');
//结果为  121  ,因为开头和结尾是空格不是1
SELECT TRIM('1' FROM '  121  ');

日期和时间函数

日期和时间函数是MySQL中另一最常用的函数。其主要用于对表中的日期和时间数据的处理。

函数	说明
CURDATE()、CURRENT_DATE()	返回当前日期，格式：yyyy-MM-dd。
CURTIME()、CURRENT_TIME()	返回当前时间，格式：HH:mm:ss。
**NOW()、CURRENT_TIMESTAMP()、**LOCALTIME()、SYSDATE()、LOCALTIMESTAMP()	返回当前日期和时间，格式：yyyy-MM-dd HH:mm:ss。
DATEDIFF(d1,d2)	计算日期d1与d2之间相隔的天数。
ADDDATE(d,n)	计算起始日期d加上n天的日期，n为正数向后推，负数向前。
DATE_FORMAT(d,f)	按照表达式 f 的要求显示日期d。
TIME_FORMAT(t,f)	按照表达式 f 的要求显示时间t。
STR_TO_DATE(s,f)	按照表达式 f 的要求，转换字符串s成日期时间类型。
UNIX_TIMESTAMP()	返回一个格林尼治标准时间1970-01-01 00:00:00到现在的秒数。
UNIX_TIMESTAMP(date)	返回一个格林尼治标准时间1970-01-01 00:00:00到指定时间的秒数。
FROM_UNIXTIME(date)	和UNIX_TIMESTAMP互为反函数，把UNIX时间戳转换为普通格式的时间。
UTC_DATE()	返回当前UTC（世界标准时间）日期值，其格式为"YYYY-MM-DD"或"YYYYMMDD"。
UTC_TIME()	返回当前UTC时间值，其格式为"YYYY-MM-DD"或"YYYYMMDD"。具体使用哪种取决于函数用在字符串还是数字语境中
MONTH(d)	返回日期d中的月份值，范围是1~12。
MONTHNAME(d)	返回日期d中的月份名称，如：January、February等。
DAYNAME(d)	返回日期d是星期几，如：Monday、Tuesday等。
DAYOFWEEK(d)	返回日期d是星期几，如：1表示星期日，2表示星期一等。
WEEKDAY(d)	返回日期d是星期几，如：0表示星期一，1表示星期二等。
WEEK(d)	计算日期d是本年的第几个星期，范围是0~53。
WEEKOFYEAR(d)	计算日期d是本年的第几个星期，范围是1~53。
DAYOFYEAR(d)	计算日期d是本年的第几天。
DAYOFMONTH(d)	计算日期d是本月的第几天。
YEAR(d)	返回日期d中的年份值。
QUARTER(d)	返回日期d是第几季度，范围是1~4。
HOUR(t)	返回时间t中的小时值。
MINUTE(t)	返回时间t中的分钟值。
SECOND(t)	返回时间t中的秒钟值。
EXTRACT(type FROM date)	从日期中提取一部分，type可以是YEAR、YEAR_MONTH、DAY_HOUR、DAY_MICROSECOND、DAY_MINUTE、DAY_SECOND
TIME_TO_SEC(t)	将时间t转换为秒。
SEC_TO_TIME(s)	将以秒为单位的时间s转换为时分秒的格式。
TO_DAYS(d)	计算日期d至0000年1月1日的天数。
FROM_DAYS(n)	计算从0000年1月1日开始n天后的日期。
ADDDATE(d,INTERVAL expr type)	计算起始日期d加上一个时间段后的日期。
DATE_ADD(d,INTERVAL expr type)	同ADDDATE(d,INTERVAL expr type)
SUBDATE(d,n)	计算起始日期d减去n天的日期。
SUBDATE(d,INTERVAL expr type)	计算起始日期d减去一个时间段后的日期。
ADDTIME(t,n)	计算起始时间t加上n秒的时间。
SUBTIME(t,n)	计算起始时间t减去n秒的时间。
DATE_FORMAT(d,f)	按照表达式 f 的要求显示日期d。
TIME_FORMAT(t,f)	按照表达式 f 的要求显示时间t。
GET_FORMAT(type, s)	根据字符串s获取type类型数据的显示格式。

参数说明：

SECOND 秒 SECONDS

MINUTE 分钟 MINUTES

HOUR 时间 HOURS

DAY 天 DAYS

MONTH 月 MONTHS

YEAR 年 YEARS

MINUTE_SECOND 分钟和秒 “MINUTES:SECONDS”

HOUR_MINUTE 小时和分钟 “HOURS:MINUTES”

DAY_HOUR 天和小时 “DAYS HOURS”

YEAR_MONTH 年和月 “YEARS-MONTHS”

HOUR_SECOND 小时, 分钟， “HOURS:MINUTES:SECONDS”

DAY_MINUTE 天, 小时, 分钟 “DAYS HOURS:MINUTES”

DAY_SECOND 天, 小时, 分钟, 秒 “DAYS HOURS:MINUTES:SECONDS”

当前时间NOW()、CURRENT_TIMESTAMP()

返回当前时间还有很多，now比较常用，CURRENT_TIMESTAMP()最多到2039年最好别用
具体案例：

//结果为2022-08-11 16:45:57
SELECT now();

条件判断函数

条件函数用来在SQL语句中进行条件判断。根据不同的条件，执行不同的SQL语句。MySQL支持的条件判断函数及作用。

函数	说明
IF(expr,v1,v2)	如果表达式expr成立，则执行v1；否则执行v2。
IFNULL(v1,v2)	如果v1不为空，则显示v1的值；否则显示v2的值。

重要函数示例

SELECT IF(TRUE,'A','B'); -- 输出结果：A
SELECT IFNULL(NULL,'B'); -- 输出结果：B
SELECT IFNULL('博客','B'); -- 输出结果：博客

系统信息函数

系统信息函数用来查询MySQL数据库的系统信息。

函数	说明
VERSION()	获取数据库的版本号。
CONNECTION_ID()	获取服务器的连接数。
DATABASE()、SCHEMA()	获取当前数据库名。
USER()、SYSTEM_USER()、SESSION_USER()	获取当前用户名。
CURRENT_USER()、CURRENT_USER	获取当前用户名。
CHARSET(str)	获取字符串str的字符集。
COLLATION(str)	获取字符串str的字符排序方法。
LAST_INSERT_ID()	获取最近生成的AUTO_INCREMENT值。

一般常用的只有version

加密函数

加密函数是MySQL中用来对数据进行加密的函数。因为数据库中有些很敏感的信息不希望被其他人看到，所以就可以通过加密的方式来使这些数据变成看似乱码的数据。

函数	说明
PASSWORD(str)	对字符串str进行加密。经此函数加密后的数据是不可逆的。其经常用于对普通数据进行加密。
MD5(str)	对字符串str进行MD5加密。经常用于对普通数据进行加密。
ENCODE(str,pass_str)	使用字符串pass_str来加密字符串str。加密后的结果是一个二进制数，必须使用BLOB类型的字段来保存它。
DECODE(crypt_str,pass_str)	使用字符串pass_str来为crypt_str解密。

MD5加密是不可逆的，网上的MD5解密本质上是穷举

其他函数

MySQL中除了上述内置函数以外，还包含很多函数。例如，数字格式化函数FORMAT(x,n)，IP地址与数字的转换函数INET_ATON(ip)，还有加锁函数GET_LOCT(name,time)、解锁函数RELEASE_LOCK(name)等等。

函数	说明
FORMAT(X,D)	将数字X格式化，将X保留到小数点后D位，截断时要进行四舍五入。
CONV(N,from_base,to_base)	不同进制数之间的转换，返回值为数值N的字符串表示，由from_base进制转换为to_base进制。
INET_ATON(expr)	给出一个作为字符串的网络地址的点地址表示，返回一个代表该地址数值的整数，地址可以使4或8比特。
INET_NTOA(expr)	给定一个数字网络地址（4或8比特），返回作为字符串的该地址的点地址表示。
BENCHMARK(count,expr)	重复执行count次表达式expr，它可以用于计算MySQL处理表达式的速度，结果值通常是0（0只是表示很快，并不是没有速度）。另一个作用是用它在MySQL客户端内部报告语句执行的时间。
CONVERT(str USING charset)	使用字符集charset表示字符串str。

limit关键字与分页查询

在我们使用查询语句的时候，经常要返回前几条或者中间某几行数据，这个时候怎么办呢？不用担心，mysql已经为我们提供了这样一个功能。
语法：

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset  

limit最常见的用法是用来分页

select ... from 表名 where ... order by ... limit (页号*每页记录条数), 每页记录条数

数据库事务

事务的操作

什么是数据库事务

事务(Transaction)，一般是指要做的或所做的事情。在计算机术语中是指访问并可能更新数据库中各种数据项的一个程序执行单元(unit)。在计算机术语中，事务通常就是指数据库事务。
>INNODB是支持事务的引擎，而myisam引擎是不支持事务的。

为什么要用事务

1. 为数据库操作提供了一个从失败中恢复到正常状态的方法，同时提供了数据库即使在异常状态下仍能保持一致性的方法。
2. 当多个应用程序在并发访问数据库时，可以在这些应用程序之间提供一个隔离方法，以防止彼此的操作互相干扰。

事务的特性(重要)

• 原子性：事务是一个整体，即要么都执行，要么都不执行
• 一致性：应确保事务是从一个一致性转变成另外一个一致性
  如张三有1000元，李四有1000元，总计2000，张三转给李四500，张三有500，李四有1500，总计还是2000
• 隔离性：有多个事务时，一个事务不能被其他的事务所干扰
  如A给B转100，C给D转200，这两者不会互相影响
• 持久性：事务正常完成后的影响应该是永久的
  不能说我转了500，之后又判定我没有转

事务的开始、提交和回滚

MySQL是默认开启自动提交的，若不需要可以通过以下命令来更改

show session variables like 'autocommit';  --查看自动提交状态
SET AUTOCOMMIT=0 -- 禁止自动提交
SET AUTOCOMMIT=1 -- 启动自动提交

开始：begin;表示开始事务，开始后必须要以一个提交或回滚来结束
提交：commit;表示提交事务，类似确认更改，会结束事务
回滚：rollback;表示将数据退回到开始事务之前的状态，也会结束事务

begin;//开始
update student set sname='王军' where id=1;//操作语句
rollback;//回滚
commit;//提交--因为前面回滚了，这个提交并不会造成任何改变

事务实现的原理：

通过日志来保存开始事务之前的数据，若需要回滚则调用日志内的数据来还原，若提交则不影响覆盖

事务的隔离级别(重要)

并发事务带来的问题

当同时多个事务对同一/多个进行操作时会发生的问题即为并发事务带来的问题

• 更新丢失（Lost Update）
  当两个或多个事务选择同一行，然后基于最初选定的值更新改行时，由于每个事务都不知道其他事务的存在，就会发生更新问题：最后的更新覆盖了由其他事务所做的更新。
  事务A和事务B同时操作，事务B覆盖了事务A做的操作，导致事务A的更新丢失了，不符合一致性
• 脏读（Dirty Reads）
  一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致的状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此进一步的处理，就会产生未提交的数据依赖关系。这种现象被称为“脏读”。
  事务A读取到事务B已经修改但未提交的数据，还在这个数据基础上做了修改。此时，如果事务B回滚了，事务A的数据无效，不符合一致性要求。
• 不可重读（Non-Repetable Reads）
  一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现起读出的数据已经发生了改变、或某些记录已经被删除。这种现象叫做“不可重读”。
  事务A读取到了事务B已经提交的修改数据，不符合隔离性。
• 幻读（Phantom Reads）
  一个事务按照相同的查询条件读取以前检索过的数据，却发现某些事务插入了满足其查询条件的新数据，这种现象称为“幻读”。
  事务A读取了事务B提交的新增数据，不符合隔离性。

事务的隔离级别

为了避免上面的这些情况，然而完全的隔离性会导致系统并发性能很低,降低对资源的利用率,因而实际上对隔离性的要求会有所放宽,这也会一定程度造成对数据库一致性要求降低，所以定义了不同的隔离级别

select @@transaction_isolation;  --查看当前隔离级别

隔离级别从低到高依次是：

• 读未提交(READ UNCOMMITTED)：能读取到其它事务未提交的数据

• 读已提交(READ COMMITTED)：只能读取到其它事务已提交的数据

• 可重复读(REPEATABLE READ)：数据库默认的隔离等级，事务A在同一事务的不同时间段内，读取同一数据，得到的值一样（即便其它事务修改了该值）

• 串行化(SERIALIZABLE)：事务依次执行，不存在并发问题

mysql的Innodb存储引擎通过Next-Key Locking技术在可重复读级别就消除了幻读的可能，因此可重复读就已经不会触发并发异常

为什么要有隔离等级

因为并发异常的存在，经常会获得与我们想象完全不同的结果，那为什么不直接用最高的串行？
事务的隔离级别越低,可能出现的并发异常越多,但是通常而言系统能提供的并发能力越强，因此才有四种不同的隔离等级。

数据库锁

用来处理并发异常，使得事务实现的限制即为锁

锁的分类

• 按作用对象来分：
  • 共享锁：即为没有锁
  • 排他锁：除了自己谁都不能用
• 按数据访问类型来分：
  • 读锁：读取时的锁
  • 写锁：写入数据时的锁
• 按对数据的态度来分：
  • 乐观锁：对事务保持乐观态度，认为不会有其它事务来修改数据，仅在更新前查询是否修改
  • 悲观锁：对事务保持悲观态度，认为一定有其它事务来修改数据，所以操作时会加锁，不让其它事务操作。
• 按数据范围来分：
  • 行锁：这一行数据不允许操作
  • 表锁：这一个表都不允许操作