mysql常用函数、查询和事务说明笔记
1.MySQL中内置了很多字符串函数，常用的几个如下：
运用示例：示例表里初始数据：
字段title和titleImage
concat : 字符串拼接
select concat(title,titleImage) as newtitle from testtable where id=65;
lower : 全部转小写
select lower(title) as newtitle from testtable where id=65;
upper : 全部转大写
select upper(titleImage) as newtitle from testtable where id=65;
lpad : 左填充
select lpad(title,16,'34567') as newtitle from testtable where id=65;(不够位数会依次循环字符串补齐位数)
使用场景：企业员工的工号，统一为5位数，目前不足5位数的全部在前面补0（lpad(workno, 5, '0')）
update worker set workno = lpad(workno, 5, '0');
rpad : 右填充
select rpad(title,16,'34567') as newtitle from testtable where id=65;(不够位数会依次循环字符串补齐位数)
trim : 去除空格
类似PHP的trim（）函数，去除头尾空格
substring : 截取字符串
select substring(titleImage,2,6) as newtitle from testtable where id=65;
2.数值函数：
ceil：向上取整
select ceil(1.1);
floor：向下取整
select floor(1.9);
mod：取模（取余是数学中的概念，取模是计算机中的概念，两者都是求两数相除的余数）
select mod(7,4);
rand：获取随机数（php中rand可以设定起始范围，mysql中随机生成0-1之内的16位小数）
select rand();
round：四舍五入
select round(2.344,2);
案例：通过数据库的函数，生成一个六位数的随机验证码。
思路： 获取随机数可以通过rand()函数，但是获取出来的随机数是在0-1之间的，所以可以在其基础
上乘以1000000，然后舍弃小数部分，如果长度不足6位，补0
select lpad(round(rand()*1000000 , 0), 6, '0');
3.日期函数
示例：
curdate：当前日期
select curdate();
2023-02-02
curtime：当前时间
select curtime();
10:10:10
now：当前日期和时间
select now();
2023-02-02 10:10:10
YEAR , MONTH , DAY：当前年、月、日
select YEAR(now());
select MONTH(now());
select DAY(now());
date_add：增加指定的时间间隔
select date_add(now(), INTERVAL 70 YEAR );
select date_add(now(), INTERVAL 70 MONTH);
select date_add(now(), INTERVAL 70 DAY );
datediff：获取两个日期相差的天数
select datediff('2021-10-01', '2021-12-01')
结果为-61，为方便统计，大的年份建议放到前面
案例：
查询所有员工的入职天数，并根据入职天数倒序排序。
思路： 入职天数，就是通过当前日期 - 入职日期，所以需要使用datediff函数来完成。
select name, datediff(curdate(), entrydate) as 'entrydays' from emp order by entrydays desc;
4.流程函数
流程函数也是很常用的一类函数，可以在SQL语句中实现条件筛选，从而提高语句的效率。
示例：
if
select if(false, 'Ok', 'Error');
ifnull
select ifnull('Ok','Default');
select ifnull('','Default');
select ifnull(null,'Default');
case when then else end
需求: 查询emp表的员工姓名和工作地址 (北京/上海 ----> 一线城市 , 其他 ----> 二线城市)
select
name,
( case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else
'二线城市' end ) as '工作地址'
from emp;
select
id,
name,
(case when math >= 85 then '优秀' when math >=60 then '及格' else '不及格' end )
'数学',
(case when english >= 85 then '优秀' when english >=60 then '及格' else '不及格'
end ) '英语',
(case when chinese >= 85 then '优秀' when chinese >=60 then '及格' else '不及格'
end ) '语文'
from score;
5.查询相关
内连接：相当于查询A、B交集部分数据
内连接的语法分为两种: 隐式内连接、显式内连接。
隐式内连接语法：SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ;
显式内连接语法：SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;
案例:
查询每一个员工的姓名 , 及关联的部门的名称
表结构: emp , dept
连接条件: emp.dept_id = dept.id
select e.name,d.name from emp e , dept d where e.dept_id = d.id;
select e.name, d.name from emp e join dept d on e.dept_id = d.id;（表分别取别名方便编写）
外连接：
左外连接：查询左表所有数据，以及两张表交集部分数据
左外连接语法：SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;
右外连接：查询右表所有数据，以及两张表交集部分数据
右外连接语法：SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;
案例:
查询emp表的所有数据, 和对应的部门信息
由于需求中提到，要查询emp的所有数据，所以是不能内连接查询的，需要考虑使用外连接查询。
表结构: emp, dept
连接条件: emp.dept_id = dept.id
select e.*, d.name from emp e left join dept d on e.dept_id = d.id;
自连接：当前表与自身的连接查询，自连接必须使用表别名
自连接语法：SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;
对于自连接查询，可以是内连接查询，也可以是外连接查询，但必须要取别名来区分表。
子查询：SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询
例：SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );
标量子查询：只返回一条数据，常用符号：= <> > >= < <=
列子查询：返回的结果是一列（可以是多行），常用符号：IN 、NOT IN 、 ANY 、SOME 、 ALL
例：查询比 财务部 所有人工资都高的员工信息
select * from emp where salary > all ( select salary from emp where dept_id =(select id from dept where name = '财务部') )
行子查询：返回的结果是一行（可以是多列），常用符号：= 、<> 、IN 、NOT IN
表子查询：返回结果多行多列，常用符号：IN
6.事务处理
示例：
-- 开启事务
start transaction（begin）
-- 1. 查询张三余额
select * from account where name = '张三';
-- 2. 张三的余额减少1000
update account set money = money - 1000 where name = '张三';
-- 3. 李四的余额增加1000
update account set money = money + 1000 where name = '李四';
-- 如果正常执行完毕, 则提交事务
commit;
-- 如果执行过程中报错, 则回滚事务
-- rollback;
事务四大特性
原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立
环境下运行。
持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。
上述就是事务的四大特性，简称ACID
并发事务问题：
1). 赃读：一个事务读到另外一个事务还没有提交的数据。
2). 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
3). 幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据
已经存在，好像出现了 "幻影"。
为了解决并发事务所引发的问题，在数据库中引入了事务隔离级别。主要有以下几种：
1）查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;
2）设置事务隔离级别
SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED |
READ COMMITTED | REPEATABLE READ | SERIALIZABLE }
注意：事务隔离级别越高，数据越安全，但是性能越低。
