MySQL函数&事务

最新推荐文章于 2022-12-15 18:58:34 发布

PETRICHOR03

最新推荐文章于 2022-12-15 18:58:34 发布

阅读量866

点赞数

分类专栏： MySQL学习文章标签：字符串数据库 mysql java sql

本文链接：https://blog.csdn.net/weixin_43616750/article/details/105912832

版权

MySQL学习专栏收录该内容

6 篇文章 0 订阅

订阅专栏

MySQL函数

为了简化使用，MySQL提供了大量的函数给程序员使用，使用与大多数的语句中

字符串函数

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

-- 字符串函数
-- 1.将所有员工的昵称改为大写
SELECT UCASE(nickname) FROM emp;
-- 2.显示所有员工的姓氏，截取
SELECT ename, SUBSTR(ename,1,1) AS 姓氏 FROM emp;
-- 3.显示所有员工姓名字符长度
SELECT CHAR_LENGTH(ename) FROM emp;
-- 4.显示所有员工姓名字节长度
SELECT LENGTH(ename) FROM emp;
-- 5.将所有姓李的员工，姓氏替换为li
SELECT REPLACE(ename,'李','li') FROM emp;
-- 6.将所有员工的姓名和昵称拼接在一起
SELECT CONCAT(ename,nickname)FROM emp;

日期函数

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

-- 日期函数
-- 1.统计每个员工入职的天数
SELECT ename,DATEDIFF(NOW(),joindate)FROM emp;
-- 2.统计每个员工的工龄
SELECT ename,DATEDIFF(NOW(),joindate)/365 FROM emp;
-- 3.查询2011年入职的员工
SELECT * FROM emp WHERE YEAR(joindate)='2011';
-- 4.统计入职10年以上的员工信息
SELECT *FROM emp WHERE DATEDIFF(NOW(),joindate)/365 > 10;

数字函数

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

-- 数字函数
-- 1.统计每个员工的工龄，超过半年的算一年
SELECT ename,ROUND(DATEDIFF(NOW(),joindate)/365) FROM emp;
-- 2.统计每个部门的平均薪资,保留2位小数
SELECT dept_id,TRUNCATE(AVG(salary),2) FROM emp GROUP BY dept_id;
-- 3.统计每个部门的平均薪资,小数向上取整
SELECT dept_id,CEIL(AVG(salary)) FROM emp GROUP BY dept_id;
-- 4.统计每个部门的平均薪资,小数向下取整
SELECT dept_id,FLOOR(AVG(salary)) FROM emp GROUP BY dept_id;

高级函数

CASE表达式

相当于java中的switch语句
语法：

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

-- CASE表达式
-- 查询每个员工的工资等级并排序
-- 工资等级在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 表名;

-- IF表达式
-- 工资+奖金大于20000的员工 显示家有娇妻，否则显示单身狗
SELECT ename, IF(salary+IFNULL(bonus,0)>20000,'有女朋友','单身狗')AS 个人状态 FROM emp;

综合练习

-- MYSQL综合案例
-- 1.计算员工的日薪(按30天)，保留二位小数
SELECT ename,TRUNCATE(salary/30,2) FROM emp;
-- 2.计算出员工的年薪（12月），并且以年薪排序 降序
SELECT ename,(salary+IFNULL(bonus,0))*12 AS 年薪 FROM emp ORDER BY 年薪 DESC;
-- 3.找出奖金少于5000或者没有获得奖金的员工的信息
SELECT * FROM emp WHERE IFNULL(bonus,0)< 5000;
-- 4.返回员工职务名称及其从事此职务的最低工资
-- 4.1确定几张表
SELECT *FROM emp e INNER JOIN job j;
-- 4.2确定连接条件
SELECT * FROM emp e INNER JOIN job j ON e.job_id=j.id;
-- 4.3确定显示字段
SELECT j.jname FROM emp e INNER JOIN job j ON e.job_id=j.id;
-- 4.4确定业务条件（分组+最低工资）
SELECT j.jname,MIN(e.salary) AS 最低工资 FROM emp e INNER JOIN job j ON e.job_id=j.id GROUP BY j.jname;
-- 5.返回工龄超过10年，且2月份入职的员工信息
SELECT *FROM emp WHERE DATEDIFF(NOW(),joindate)/365 >10 AND MONTH(joindate)=2;
-- 6.返回与 林冲 同一年入职的员工
-- 6.1查询名字是林冲的员工
SELECT YEAR(joindate) FROM emp WHERE ename='林冲';
-- 6.2查询同一年的员工
SELECT * FROM emp WHERE YEAR(joindate)=(SELECT YEAR(joindate) FROM emp WHERE ename='林冲');
-- 7.返回每个员工的名称及其上级领导的名称（自关联）
SELECT a.ename,b.ename FROM emp a LEFT OUTER JOIN emp b ON a.mgr=b.id;

-- 8.返回工资为二等级（工资等级表）的职员名字（员工表）、部门名称（部门表）
-- 8.1确定几张表
SELECT * FROM emp e 
INNER JOIN dept d
INNER JOIN salarygrade sg;
-- 8.2确定连接条件
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;
-- 8.3确定显示字段
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;
-- 8.4确定业务条件
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;

-- 9.涨工资：董事长2000 经理1500 其他800
-- 1.确定几张表
SELECT *FROM emp e INNER JOIN job j ON e.job_id=j.id;
-- 2.确定显示字段(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

概述

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

操作事务

手动提交事务

开启事务：begin
提交事务：commit
回滚事务：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执行成功，添加一个保存点，后续操作报错了，回滚到保存点，保证之前的操作可以成功提交

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

事务特性ACID(面试题)

原子性：A atomicity
如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败
一致性：C consistency
事务在执行前后，保证数据的一致性
隔离性：I isolation【重点】
多个事务之间，相互独立，互不干扰…
持久性：D durability
事务一旦成功提交，保存到磁盘文件，不可逆…

事务隔离性

多个事务之间隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题

MySQL数据库隔离级别

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

演示隔离级别产生的问题

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

PETRICHOR03

关注

0
点赞
踩
17

收藏

觉得还不错? 一键收藏
0
评论
MySQL函数&事务

MySQL函数为了简化使用，MySQL提供了大量的函数给程序员使用，使用与大多数的语句中字符串函数函数：CONCAT(s1,s2…sn)描述：字符串 s1,s2 等多个字符串合并为一个字符串实例：select concat(‘我爱’,’-’,‘中国’);函数：CHAR_LENGTH(str)描述：返回字符串 str 的字符数实例：select char_length(‘你好...
复制链接

扫一扫