数据库 = MySQL函数与事务安全

mysql函数运用，事务安全TCL

一.MySQL函数

为了简化操作， mysql 提供了大量的函数给程序员使用（比如你想输入当前时间，可以调用 now()函数）函数可以出现的位置：插入语句的 values() 中，更新语句中，删除语句中， 查询语句 及其子句中。可以在菜鸟教程中查看（1）字符串函数

1.  函数：CONCAT(s1,s2...sn)
	描述：字符串 s1,s2 等多个字符串合并为一个字符串
	实例：select concat('中国','-','香港');
	
2.  函数：CHAR_LENGTH(str)
	描述：返回字符串 str 的字符数
	实例：select char_length('你好，树先生');
	
3.  函数：LENGTH(str)
	描述：返回字符串 s 的字节数
	编码：UTF8（一个中文字符占3个字节）
	实例：select length('你好,树先生h');
	
4.  函数：UCASE(s) | UPPER(s)
	描述：将字符串转换为大写
	实例：select ucase('wsl');
	
5.  函数：LCASE(s) | LOWER(s)
	描述：将字符串转换为小写
	实例：select lcase('WSL');
	
6.  函数：LOCATE(s1,s)
	描述：从字符串 s 中获取 s1 的开始位置
	注意：从1开始
	实例：select locate('ws','laa');
	
7.  函数：TRIM(str) | LTRIM(str) | RTRIM(str)
	描述：字符串去空格
	实例：select trim('   莘莘学子    ');
	
8.  函数：REPLACE(s,s1,s2)
	描述：将字符串 s2 替代字符串 s 中的字符串 s1
	实例：select replace('abc','b','x');
	
9.  函数：SUBSTR(s, start, length)
	描述：从字符串 s 的 start 位置截取长度为 length 的子字符串
	注意：从1开始
	实例：select substr('wsll','2','3');
	
10. 函数：STRCMP(str1,str2)
	描述：比较字符串大小,左大于右时返回1，左等于右时返回0，，左小于于右时返回-1，
	实例：select strcmp('a','b');

案例：

-- 将所有员工的昵称改为大写
SELECT UCASE(nickname) FROM emp;

-- 显示所有员工的姓氏，截取
SELECT ename,SUBSTR(ename,1,1) FROM emp;


-- 显示所有员工姓名字符长度
SELECT CHAR_LENGTH(ename) FROM emp;

-- 显示所有员工姓名字节长度
SELECT LENGTH(ename) FROM emp;

-- 将所有姓李的员工，姓氏替换为li
SELECT REPLACE(ename,'李','li') FROM emp;


-- 将所有员工的姓名和昵称拼接在一起
SELECT CONCAT(ename,nickname) FROM emp;

（2）日期函数

1.  函数：NOW() | CURDATE() | CURTIME()
	描述：获取系统当前日期时间、日期、时间
	实例：select now();
	
2.  函数：YEAR(DATE) | MONTH(DATE) | DAY(DATE)
	描述：从日期中选择出年、月、日
	实例：select year(now());
	
3.  函数：LAST_DAY(DATE)
	描述：返回月份的最后一天
	实例：select last_day(now());
	
4.  函数：ADDDATE(DATE,n) | SUBDATE(DATE,n)
	描述：计算起始日期 DATE 加（减） n 天的日期
	实例：select subdate(now(),10);
	
5.  函数：QUARTER(DATE)
	描述：返回日期 DATE 是第几季节，返回 1 到 4
	实例：select quarter(now());
	
6.  函数：DATEDIFF(d1,d2)
	描述：计算日期 d1->d2 之间相隔的天数
	实例：select datediff(now(),'1999-1-1');
	
7.  函数：DATE_FORMAT(d,f)
	描述：按表达式 f的要求显示日期 d
	实例：select date_format(now(),'%Y-%m-%d');

案例

-- 统计每个员工入职的天数
SELECT ename,DATEDIFF(NOW(),joindate) FROM emp;

-- 统计每个员工的工龄
SELECT ename,DATEDIFF(NOW(),joindate)/365 FROM emp;

-- 查询2011年入职的员工
SELECT * FROM emp WHERE YEAR(joindate) = '2011';

-- 统计入职10年以上的员工信息
SELECT * FROM emp WHERE DATEDIFF(NOW(),joindate)/365 >10;

（3）数字函数

1.  函数：ABS(x)
	描述：返回 x 的绝对值　　
	实例：select abs(-10);
	
2.  函数：CEIL(x) | FLOOR(x)
	描述：向上（下）取整
	实例：select ceil(1.5);
	
3.  函数：MOD(x,y)
	描述：返回x mod y的结果，取余
	实例：select mod(5,4);
	
4.  函数：RAND()
	描述：返回 0 到 1 的随机数
	实例：select rand();
	
5.  函数：ROUND(x)
	描述：四舍五入
	实例：select round(1.2345);
	
6.  函数：TRUNCATE(x,y)
	描述：返回数值 x 保留到小数点后 y 位的值
	实例：select truncate(533.123324,2);

-- 统计每个员工的工龄，超过半年的算一年
SELECT ename,ROUND( DATEDIFF(NOW(),joindate)/365) FROM emp;

-- 统计每个部门的平均薪资,保留2位小数
SELECT dept_id,TRUNCATE( AVG(salary),2 )FROM emp GROUP BY dept_id;

-- 统计每个部门的平均薪资,小数向上取整
SELECT dept_id,CEIL( AVG(salary) )FROM emp GROUP BY dept_id;

-- 统计每个部门的平均薪资,小数向下取整
SELECT dept_id,FLOOR( AVG(salary) )FROM emp GROUP BY dept_id;

（4）高级函数

CASE表达式：相当于java中的switch语句

语法：

	SELECT 
		CASE [字段,值] 
			WHEN 判断条件1 
				THEN 希望的到的值1
			WHEN 判断条件2 
				THEN 希望的到的值2
			ELSE 前面条件都没有满足情况下得到的值 
		END
	FROM
		table_name;

案例：

-- 查询每个员工的工资等级并排序
	-- 工资等级在1显示为 '努力赚钱'
	-- 工资等级在2显示为 '小康生活'
	-- 工资等级在3显示为 '可以买车'
	-- 工资等级在4显示为 '可以买房'
	-- 工资等级在5显示为 '可以娶媳妇'
	-- 工资等级不在以上列表中显示为  '土豪'
	
-- 1.确定几张表
SELECT * FROM emp e INNER JOIN salarygrade sg;

-- 2.确定连接条件
SELECT * FROM emp e INNER JOIN salarygrade sg ON e.`salary` BETWEEN sg.`losalary` AND sg.`hisalary`;

-- 3.确定显示字段
SELECT e.ename,e.`salary`,sg.`grade` FROM emp e INNER JOIN salarygrade sg ON e.`salary` BETWEEN sg.`losalary` AND sg.`hisalary`;

-- 4.确定业务条件
SELECT e.ename,e.`salary`,
	CASE sg.`grade` 
		WHEN 1 THEN '努力赚钱'
		WHEN 2 THEN '小康生活'
		WHEN 3 THEN '可以买车'
		WHEN 4 THEN '可以买房'
		WHEN 5 THEN '可以娶媳妇'
		ELSE '土豪'
	END AS '生活状态'
FROM emp e INNER JOIN salarygrade sg ON e.`salary` BETWEEN sg.`losalary` AND sg.`hisalary` ORDER BY sg.`grade` ASC;

IF表达式：相当于java中的三元运算符

语法：

SELECT IF(1 > 0,'真','假') from 表名;

-- 工资+奖金大于20000的员工 显示家幸福，否则显示单身
SELECT ename,IF(salary+IFNULL(bonus,0) > 20000,'家幸福','单身') AS 家里有啥 FROM emp;

案例展示：

-- 计算员工的日薪(按30天)，保留二位小数
SELECT ename,TRUNCATE(salary/30,2) FROM emp;

-- 计算出员工的年薪（12月），并且以年薪排序 降序
SELECT ename,(salary + IFNULL(bonus,0)) * 12 AS 年薪 FROM emp ORDER BY 年薪 DESC;

-- 找出奖金少于5000或者没有获得奖金的员工的信息
SELECT * FROM emp WHERE IFNULL(bonus,0) < 5000;


-- 返回员工职务名称及其从事此职务的最低工资
---- 确定几张表
SELECT * FROM emp e INNER JOIN job j;
---- 确定连接条件
SELECT * FROM emp e INNER JOIN job j ON e.`job_id` = j.`id`;
---- 确定显示字段
SELECT j.`jname` FROM emp e INNER JOIN job j ON e.`job_id` = j.`id`;
---- 确定业务条件（分组+最低工资）
SELECT j.`jname`,MIN(e.`salary`) FROM emp e INNER JOIN job j ON e.`job_id` = j.`id` GROUP BY j.`jname`;



-- 返回工龄超过10年，且2月份入职的员工信息
SELECT * FROM emp WHERE DATEDIFF(NOW(),joindate)/365 > 10 AND MONTH(joindate) = 2;



-- 返回与 林冲 同一年入职的员工
SELECT YEAR(joindate) FROM emp WHERE ename = '林冲';
SELECT * FROM emp WHERE YEAR(joindate) = (SELECT YEAR(joindate) FROM emp WHERE ename = '林冲');



-- 返回每个员工的名称及其上级领导的名称（自关联）
SELECT a.`ename`,b.`ename` FROM emp  a LEFT OUTER JOIN emp b ON a.`mgr` = b.`id`;



-- 返回工资为二等级（工资等级表）的职员名字（员工表）、部门名称（部门表）
----确定几张表
SELECT * FROM emp e
	INNER JOIN dept d
	INNER JOIN salarygrade sg;
----确定连接条件
SELECT * FROM emp e
	INNER JOIN dept d ON e.`dept_id` = d.`id`
	INNER JOIN salarygrade sg ON e.`salary` BETWEEN sg.`losalary` AND sg.`hisalary`;

----确定显示字段
SELECT sg.`grade`,e.`ename`,d.`dname` FROM emp e
	INNER JOIN dept d ON e.`dept_id` = d.`id`
	INNER JOIN salarygrade sg ON e.`salary` BETWEEN sg.`losalary` AND sg.`hisalary`;
	
----确定业务条件
SELECT sg.`grade`,e.`ename`,d.`dname` FROM emp e
	INNER JOIN dept d ON e.`dept_id` = d.`id`
	INNER JOIN salarygrade sg ON e.`salary` BETWEEN sg.`losalary` AND sg.`hisalary`
	WHERE sg.`grade` = 2;
	

--涨工资：董事长2000 经理1500 其他800
----确定几张表和连接条件
SELECT * FROM emp e INNER JOIN job j ON e.`job_id` = j.`id`;
----显示字段（case表达式）
SELECT e.`ename`,j.`jname`,e.`salary` AS 涨前,
       CASE j.`jname`
	WHEN '董事长' THEN e.salary + 2000
	WHEN '经理' THEN e.salary + 1500
	ELSE e.salary + 800
       END AS 涨后 
	FROM emp e INNER JOIN job j ON e.`job_id` = j.`id`;

二.事务安全TCL

概述：如果一个包含了多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。

比如：用户转账

（1）数据准备：

-- 创建库
create database crmpro;

-- 使用库
use crmpro;

-- 创建数据表
CREATE TABLE account (  -- 账户表
	id INT PRIMARY KEY AUTO_INCREMENT,
	`name` VARCHAR(32),
	money DOUBLE 
);

-- 添加数据
INSERT INTO account (`name`, money) VALUES ('网名', 1000), ('李甜甜', 1000);

模拟转账：

-- 扣钱（转出）
UPDATE account SET money = money -100 WHERE  id = 2;

-- 机器故障了


-- 加钱（转入）
UPDATE account SET money = money + 100 WHERE id = 1;

此时就会造成数据的不一致，造成混乱。

（2）操作事务

手动提交事务：

1. 开启事务
		begin
		
2. 提交事务
		commit
		
3. 回滚事务
		rollback

转账成功：

-- 1. 开启事务
	begin;
-- 2. 扣钱
	UPDATE account SET money = money -100 WHERE  id = 2;
-- 3. 加钱
	UPDATE account SET money = money + 100 WHERE id = 1;
-- 4. 提交事务
	commit;

转账失败

-- 1.开启事务
	begin;
-- 2.扣钱
	UPDATE account SET money = money -100 WHERE  id = 2;
-- 3.机器故障

-- 4.回滚事务
	rollback;

注意：

自动提交事务：在jdbc中将自动提交关闭

 默认情况下，在MySQL中每一条DML（增删改）语句，就是一个独立的事务

 查看MySQL是否开启自动提交
		show variables like 'autocommit';
		
 临时关闭自动提交（手动）
		set autocommit=off;

手动提交

-- 1扣钱
	UPDATE account SET money = money -100 WHERE  id = 2;
	
-- 2.改为手动提交/回滚
	commit / rollback ;

三.事务工作原理

在同一个事务中，出现bug（异常），必须执行rollback命令，不然会影响同一个事务中下一次提交

四.保存回滚点

当事务开启后，一部分sql执行成功，添加一个保存点，后续操作报错了，回滚到保存点，保证之前的操作可以成功提交

1. 设置保存点
		savepoint 保存点名;
		
2. 回滚到保存点
		rollback to 保存点名;

案例：某某有5个同事，都向某某转钱，如果前两个成功扣款了设置保存点，保证前两个都成功。

-- 1.开启事务
	begin;
-- 2.转钱一次
	UPDATE account SET money = money +100 WHERE  id = 2;
-- 3.转钱二次
	UPDATE account SET money = money +100 WHERE  id = 2;
-- 4.设置一个保存点
	savepoint ol;
-- 5.转钱三次
	UPDATE account SET money = money +100 WHERE  id = 2;
-- 6.机器故障

-- 7.回滚点保存点
	rollback to ol;
-- 8.提交事务
	commit;

总结：设置回滚点可以让我们在失败的时候回到回滚点，而不是回到事务开启的时候

五.事务的四大特性“

（1）

（2）事务的隔离级别

事务在操作时的理想状态：多个事务之间互不影响，如果隔离级别设置不当就可能引发并发读取问题。

1. 脏读 必须要避免的
		一个事务中，读取到另一个事务，未提交的数据
		
2. 不可重复读
		一个事务中，二次读取的内容不一致，另外一个事务做了update操作

3. 幻读
		一个事务中，二次读取的数量不一致，另外一个事务做了insert、delete操作

（3）数据库的隔离级别

级别	名字	隔离级别	脏读	不可重复读	幻读	数据库默认隔离级别
1	读未提交	read uncommitted	是	是	是
2	读已提交	read committed	否	是	是	Oracle和SQL Server
3	可重复读	repeatable read	否	否	是	MySQL
4	串行化	serializable	否	否	否

性能角度：1>2>3>4

安全角度：4>3>2>1

综合考虑：2 or 3

其他：

MySQL事务隔离级别相关命令

查看全局事务隔离级别

show variables like '%isolation%';

1. 查看当前数据库隔离级别
		show variables like '%isolation%';
		
2. 临时修改隔离级别
		set session transaction isolation level 级别字符串;

案例：

1.演示脏读问题

1. 演示脏读
	设置数据库隔离级别 read uncommitted;
		set session transaction isolation level read uncommitted;

脏读非常危险的，比如A向B购买商品，A开启事务，向B账号转入 500 块，然后打电话给B 说钱已经转了。B一查询钱到账了，发货给A。A收到货后回滚事务，B的再查看钱没了。

2. 解决脏读问题（引出：不可重复读问题）
	设置数据库隔离级别 read committed;
		set session transaction isolation level read committed;

结论： read committed 的方式可以避免脏读的发生

3. 解决不可重复读问题（出现幻读问题）
	设置数据库隔离级别 repeatable read;
		set session transaction isolation level repeatable read;

4. 解决幻读问题
	设置数据库隔离级别 serializable;
		set session transaction isolation level serializable;

总结：

所谓事务其实就是将多个操作看成同一个逻辑操作，要么全部成功，要么全部失败 企业中使用 read commited 的场景比较多 , 因为使用 repeatable read 在没有索引的情况下性能较差

更多推荐

数据库 = MySQL函数与事务安全

数据库 = MySQL函数与事务安全

一.MySQL函数

二.事务安全TCL

三.事务工作原理

四.保存回滚点

五.事务的四大特性“

发布评论取消回复

最近发表

热门文章

标签列表

数据库 = MySQL函数与事务安全

一.MySQL函数

二.事务安全TCL

三.事务工作原理

四.保存回滚点

五.事务的四大特性“

相关文章

发布评论取消回复

最近发表

热门文章

标签列表