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 }

注意：事务隔离级别越高，数据越安全，但是性能越低。