mysql 金额加 mysql数字加法函数

文章目录

• 1. 数字函数
• 2. 字符函数
• 3. 日期函数
• 4. 条件函数

1. 数字函数

序号	函数	功能	用例 （SELECT 函数;能返回结果）
1	ABS	绝对值	ABS(-100)
2	ROUND	四舍五入	ROUND(4.1)
3	FLOOR	向下取整	FLOOR(4.1)
4	CEIL	向上取整	CEIL(4.1)
5	POWER	幂函数	POWER(2, 3), 是$2^3$
6	LOG	对数函数	LOG(7, 3)，是$log_7^3$
7	LN	对数函数	LN(10)
8	SQRT	开平方	SQRT(9)
9	PI	圆周率	PI()
10	SIN	三角函数，单位弧度	SIN(1)
11	COS	三角函数，单位弧度	COS(1)
12	TAN	三角函数，单位弧度	TAN(1)
13	COT	三角函数，单位弧度	COT(1)
14	RADIANS	角度转换弧度	RADIANS(30)
15	DEGREES	弧度转换角度	DEGREES(1)

2. 字符函数

序号	函数	功能	用例 （参数可以是字段）	备注
1	LOWER	转成小写字符	LOWER(str)
2	UPPER	转换大写字符	UPPER(str)
3	LENGTH	字符数量	LENGTH(str)
4	CONCAT	连接str1, str2…	CONCAT(str1, str2， str3…)
5	INSTR	字符出现的位置	INSTR(str, “A”)	字符串从1开始不是0 返回0，代表找不到该字符 返回n，代表该字符在第n个位置
6	INSERT	插入/替换字符	INSERT(“你好”, 1, 0, “先生”)	1代表插入的位置 0代表不替换直接插入 如果把0改成n的话就是替换之后的n个字符
7	REPLACE	替换字符	REPLACE(“你好先生”, “先生”, “女士”)	例中返回你好女士
8	SUBSTR	截取字符串	SUBSTR(“你好世界, 3, 4”)	经实验，我没发现这个函数和下面那个函数有区别
9	SUBSTRING	截取字符串	SUBSTRING(“你好世界”, 3, 2)	都是第一个参数开始往后截取第二个参数个字符
10	LPAD	左侧填充字符	LPAD(“Hello”, 10, “*”)	*****Hello用*在字符串左侧填充字符，使字符串的长度=10
11	RPAD	右侧填充字符	RPAD(“Hello”, 10,“*”)
11	TRIM	去除首尾空格	TRIM(" 你好 先生 ")	返回：你好 先生

• INSERT的例子

SELECT INSERT("你好", 1, 0, "先生") #返回先生你好
SELECT INSERT("你好", 1, 1, "先生") #返回先生好
SELECT INSERT("你好", 1, 2, "先生") #返回先生

• LPAD可以用来保留手机号的后四位

SELECT LPAD(SUBSTRING("13312345678", 8, 4), 11, "*"); #返回*******5678

• RPAD保留名字姓氏，名用星号，需注意一个汉字的占3个字符比英文大，因此需要除3

SELECT RPAD(SUBSTRING("李娜娜", 1, 1), LENGTH("李娜娜")/3, "*"); #返回李**

3. 日期函数

• MySQL数据库里，两个日期不能直接加减，日期也不能与数字加减
• 函数

序号	函数	功能	备注
1	NOW()	获取当前系统日期和时间	返回格式：yyyy-MM-dd hh:mm:ss，不支持毫秒
2	CURDATE()	获取当前系统日期	返回格式：yyyy-MM-dd
3	CURTIME()	获取当前系统时间	返回格式：hh:mm:ss
4	DATE_FORMAT(日期, 表达式)	用于格式化日期，返回用户想要的日期格式	SELECT DATE_FORMAT(hiredate, "%Y") FROM t_emp;可以得到年份 表达式里其他占位符可以看下面那张表
5	DATE_ADD(日期, INTERVAL 偏移量 时间单位)	实现日期偏移计算，且单位灵活	向前1天：DATE_ADD(NOW(), INTERVAL 1 DAY);向后1天：DATE_ADD(NOW(), INTERVAL -1 DAY) 时间单位：YEAR, MONTH, DAY, HOUR, MINUTE, SECOND
6	DATEDIFF(日期1, 日期2)	计算两个日期相差多少天

• 占位符表

占位符	作用	占位符	作用
%Y	年份	%m	月份
%d	日期	%w	星期（数字） 星期1-星期六：1-6 星期天：0
%W	星期（名称）	%j	本年第几天
%U	本年第几周	%H	小时(24)
%h	小时（12）	%i	分钟
%s	秒	%r	时间（12）
%T	时间（24）

• 利用日期函数，查询日期是星期几

SELECT DATE_FORMAT("2022-05-20", "%W");

• 查询1981年上半年入职的员工有多少人

SELECT COUNT(*) FROM t_emp
WHERE DATE_FORMAT(hiredate, "%Y")=1981
	AND DATE_FORMAT(hiredate,"%m")<=6;

• 当前日期向前偏移三天六个月

SELECT DATE_ADD(DATE_ADD(NOW(), INTERVAL -6 MONTH), INTERVAL -3 DAY);

4. 条件函数

序号	函数	功能	备注
1	IFNULL(表达式, 值)	如果表达式为NULL则取值
2	IF(表达式, 值1, 值2)	表达式为真则值1，假则值2
3	CASE WHEN 表达式1 THEN 值1 WHEN 表达式2 THEN 值2 … ELSE 值N END	根据条件返回对应值 INSERT, DELETE, UPDATE, SELECT语句中都可以用

• SALES部门发礼品A，其他部门发礼品B，打印每名员工获得的礼品（因为每名员工都要打印，所以条件不能写在WHERE里）

SELECT e.empno, e.ename, d.dname, IF(d.dname="SALES", "礼品A", "礼品B")
FROM t_emp e JOIN t_dept d ON e.deptno = d.deptno;

• SALES部门取P1地点，ACCOUNTING部门去P2地点，RESEARCH部门去P3地点，查询每名员工的旅行地点

# 第一种写法
SELECT 
	e.empno, e.ename,
	CASE 
	WHEN d.dname="SALES" THEN "p1"
	WHEN d.dname="ACCOUNTING" THEN "p2"
	WHEN d.dname="RESEARCH" THEN "p3"
END AS place
FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno;

# 第二种写法
SELECT 
	e.empno, e.ename,
	CASE d.dname
	WHEN "SALES" THEN "p1"
	WHEN "ACCOUNTING" THEN "p2"
	WHEN "RESEARCH" THEN "p3"
END AS place
FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno;

• 按表中要求写

序号	条件	涨幅
1	SALES部门中工龄超过20年	10%
2	SALES部门中工龄不满20年	5%
3	ACCOUNTING部门	+300元
4	RESEARCH部门里低于部门平均底薪	+200元
5	没有部门的员工	+100元

UPDATE t_emp e 
LEFT JOIN t_dept d ON e.deptno=d.deptno
LEFT JOIN (SELECT deptno, AVG(sal) avg FROM t_emp GROUP BY deptno) t ON e.deptno=t.deptno
SET e.sal=(
	CASE
		WHEN d.dname="SALES" AND DATEDIFF(NOW(), e.hiredate)/365>=20 THEN e.sal*1.1
		WHEN d.dname="SALES" AND DATEDIFF(NOW(), e.hiredate)/365<20 THEN e.sal*1.05
		WHEN d.dname="ACCOUNTING" THEN e.sal+300
		WHEN d.dname="RESEARCH" AND e.sal<t.avg THEN e.sal+200
		WHEN e.deptno IS NULL THEN e.sal+100
		ELSE e.sal
	END
);