函数的分类:
A.单行函数:对每一条记录输入值进行计算,得到相应的计算结果,返回给用户,也就是说,每条记录作为一个输入参数,经过函数计算得到每条记录的计算结果。
B.多行函数:对多条记录输入值进行计算,得到多条记录对应的单个结果。
单行函数细分:
A.字符函数
B.数学函数
C.日期函数
D.其他函数
E.流程控制函数
单行函数 - 字符函数
一、字符函数
-
length 获取参数的字节长度
SELECT LENGTH(‘john’); -
concat 拼接字符
SELECT CONCAT(last_name,’_’,first_name) FROM employees; -
upper,lower
SELECT UPPER(‘john’);
SELECT LOWER(‘JOHN’);
案例:将姓变大写,将名变小写,然后拼接
SELECT CONCAT(UPPER(first_name), LOWER(first_name)) AS “姓名” FROM employees;
- substr
4个重载的方法
注意:SQL语言的索引从1开始
作用:截取从pos开始的所有字符,包括pos
SELECT SUBSTR(‘zhangjin’, 6); # jin
作用:截取从pos开始, 字符长度为len的子串
SELECT SUBSTR(‘zhangjin’,1,5); # zhang
案例:姓名中首字母大写,其他字符小写
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)), LOWER(SUBSTR(last_name,2)))
AS “姓”
FROM employees;
- instr 返回子串第一次出现的起始索引,没有出现则返回0
SELECT INSTR(‘zhangjin’,‘shay’); # 0
SELECT INSTR(‘zhangjin’,‘jin’); # 6
SELECT INSTR(‘zhangjinjinjinjinjinjin’,‘jin’); # 6
6、trim
默认是trim左右两边的空格
SELECT TRIM(’ zhangjin ');
可以指定要trim的字符
SELECT TRIM(‘a’ FROM ‘aaaaaazhangjinaaaa’);
7、lpad
使用指定字符进行左填充,填充后的字符个数为len
len,字符个数,并不是字节长度
SELECT LPAD(‘张’,3,’*’); # **张
最终长度由len决定
SELECT LPAD(‘张三丰’,2,’*’); # 张三
8、rpad
使用指定字符进行右填充,填充后的字符个数为len
len,字符个数,并不是字节长度
SELECT RPAD(‘张’,3,’’); # 张*
最终长度由len决定
SELECT RPAD(‘张三丰’,2,’*’); # 张三
9、replace
replace(str,from_str,to_str) 全部替换
SELECT REPLACE(‘zhangjin’, ‘zhang’, ‘shay’);
SELECT REPLACE(‘zhangjinzhangjinzhangjin’, ‘zhang’, ‘shay’);
二、数学函数
round 四舍五入
SELECT ROUND(1.45);
SELECT ROUND(-1.65);
SELECT ROUND(1.567,2); #保留2位小数
ceil 向上取整, 返回大于等于该参数的最小整数
SELECT CEIL(1.002); #2
SELECT CEIL(1.00); #1
floor 向下取整,返回小于等于该参数的最大整数
SELECT FLOOR(1.002); #1
truncate 截断
SELECT TRUNCATE(1.65,1); #保留1位小数
mod 取余数
SELECT MOD(10,3); # 10%3=1
SELECT MOD(-10,-3); # -1, 被除数为负数,结果为负数
一些练习
三、日期函数
now 返回当前系统日期+时间
select now();
curdate 返回当前系统日期,不包括日期
select curdate();
curtime 返回当前时间,不包括日期
select curtime();
可以获取指定的部分:年,月,日,时,分,秒
获取年
select year(now()) as 年;
select year(‘1986-1-1’);
select year(hiredate) from employees;
获取月
select month(now()) as 月; # 11
select monthname(now()) as 月; # November
获取日
select day(now()) as 日; # 24
select dayname(now()) as 星期; # Friday
其他一些日期函数
-- datediff(date1,date2):两个日期相减 date1 - date2,返回天数
SELECT DATEDIFF('2017-06-05','2017-05-29');-- 7
SELECT LAST_DAY('2016-02-01');-- 2016-02-29 (返回月份中最后一天)
SELECT LAST_DAY('2016-05-01');-- 2016-05-31
-- DATE_ADD(date,INTERVAL expr type) 从日期加上指定的时间间隔
-- type参数可参考:http://www.w3school.com.cn/sql/func_date_sub.asp
SELECT DATE_ADD('2017-05-15 10:37:14.123456',INTERVAL 1 YEAR);-- 表示:2018-05-15 10:37:14.123456
SELECT DATE_ADD('2017-05-15 10:37:14.123456',INTERVAL 1 QUARTER);-- 表示:2017-08-15 10:37:14.123456
SELECT DATE_ADD('2017-05-15 10:37:14.123456',INTERVAL 1 MONTH);-- 表示:2017-06-15 10:37:14.123456
SELECT DATE_ADD('2017-05-15 10:37:14.123456',INTERVAL 1 WEEK);-- 表示:2017-05-22 10:37:14.123456
SELECT DATE_ADD('2017-05-15 10:37:14.123456',INTERVAL 1 DAY);-- 表示:2017-05-16 10:37:14.123456
SELECT DATE_ADD('2017-05-15 10:37:14.123456',INTERVAL 1 HOUR);-- 表示:2017-05-15 11:37:14.123456
SELECT DATE_ADD('2017-05-15 10:37:14.123456',INTERVAL 1 MINUTE);-- 表示:2017-05-15 10:38:14.123456
SELECT DATE_ADD('2017-05-15 10:37:14.123456',INTERVAL 1 SECOND);-- 表示:2017-05-15 10:37:15.123456
SELECT DATE_ADD('2017-05-15 10:37:14.123456',INTERVAL 1 MICROSECOND);-- 表示:2017-05-15 10:37:14.123457
-- DATE_SUB(date,INTERVAL expr type) 从日期减去指定的时间间隔
SELECT DATE_SUB('2017-05-15 10:37:14.123456',INTERVAL 1 YEAR);-- 表示:2016-05-15 10:37:14.123456
SELECT DATE_SUB('2017-05-15 10:37:14.123456',INTERVAL 1 QUARTER);-- 表示:2017-02-15 10:37:14.123456
SELECT DATE_SUB('2017-05-15 10:37:14.123456',INTERVAL 1 MONTH);-- 表示:2017-04-15 10:37:14.123456
SELECT DATE_SUB('2017-05-15 10:37:14.123456',INTERVAL 1 WEEK);-- 表示:2017-05-08 10:37:14.123456
SELECT DATE_SUB('2017-05-15 10:37:14.123456',INTERVAL 1 DAY);-- 表示:2017-05-14 10:37:14.123456
SELECT DATE_SUB('2017-05-15 10:37:14.123456',INTERVAL 1 HOUR);-- 表示:2017-05-15 09:37:14.123456
SELECT DATE_SUB('2017-05-15 10:37:14.123456',INTERVAL 1 MINUTE);-- 表示:2017-05-15 10:36:14.123456
SELECT DATE_SUB('2017-05-15 10:37:14.123456',INTERVAL 1 SECOND);-- 表示:2017-05-15 10:37:13.123456
SELECT DATE_SUB('2017-05-15 10:37:14.123456',INTERVAL 1 MICROSECOND);-- 表示:2017-05-15 10:37:14.123455
SELECT DATE_SUB(CURDATE(),INTERVAL 1 DAY);-- 前一天:2017-05-11
SELECT DATE_SUB(CURDATE(),INTERVAL -1 DAY);-- 后一天:2017-05-13
SELECT DATE_SUB(CURDATE(),INTERVAL 1 MONTH);-- 一个月前日期:2017-04-12
SELECT DATE_SUB(CURDATE(),INTERVAL -1 MONTH);-- 一个月后日期:2017-06-12
SELECT DATE_SUB(CURDATE(),INTERVAL 1 YEAR);-- 一年前日期:2016-05-12
SELECT DATE_SUB(CURDATE(),INTERVAL -1 YEAR);-- 一年后日期:20178-06-12
一些练习
0.查询每个员工截止到现在一共入职多少天?
1.查询服务器当前时间
-- 查询部门10,20的员工截止到2000年1月1日,工作了多少个月,入职的月份。
select ename,month(hiredate), (year('2000-01-01') -year(hiredate))*12 + month('2000-01-01') -month(hiredate) from emp;
-- 如果员工试用期6个月,查询职位不是MANAGER的员工姓名,入职日期,转正日期,入职日期后的第一个星期一,入职当月的最后一天日期。
select ename,hiredate,DATE_ADD(hiredate,INTERVAL 6 MONTH),DATE_ADD(hiredate,INTERVAL 7-DAYOFWEEK(hiredate)+2 DAY),LAST_DAY(hiredate)from emp
类型转换函数
image
将日期格式的字符串 -> 日期
str_to_date(‘24-11-2017’, ‘%d-%m-%Y’);
select str_to_date(‘24-11-2017’, ‘%d-%m-%Y’);
案例:将用户输入的4-3 1992作为查询依据,找出在1992年4月3日入职的员工
select * from emp
where hiredate = str_to_date(‘12-17 1980’, ‘%m-%d %Y’);
日期 -> 字符
date_format(‘2018/6/6’, ‘%Y年%m月%d日’)
select date_format(‘2018/6/6’, ‘%Y年%m月%d日’);
案例:查询有奖金的员工名和入职日期(XX月/XX日 XX年)
select ename,date_format(hiredate, ‘%m月/%d日 %y年’) as 入职日期
from emp
where comm is not null;
mysql 字符串数字转换
//字符串转数字
//数字转字符串
FORMAT函数
FORMAT函数在mysql中是数据内容格式化的,格式化后得到结果:###,###,#####。
一些练习
1.显示服务器系统当前时间,格式为2007-10-12 17:11:11
2.显示ename、hiredate 和 雇员开始工作日是星期几,列标签DAY
3.查询员工姓名,工资,格式化的工资(¥999,999.99)
4.把字符串2015-3月-18 13:13:13 转换成日期格式,并计算和系统当前时间间隔多少天。
case控制结构的使用一:switch case的效果
/*
适合:等值判断
mysql中case控制结构:
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
when 常量3 then 要显示的值3或语句3;
when 常量4 then 要显示的值4或语句4;
else 要显示的值n或语句n;
end
*/
/案例:查询员工的工资,要求
部门号=30,显示的工资为1.1倍
部门号=40,显示的工资为1.2倍
部门号=50,显示的工资为1.3倍
其他部门,显示工资为原工资
/
SELECT salary,department_id,
CASE department_id
WHEN 30 THEN 1.1salary
WHEN 40 THEN 1.2salary
WHEN 50 THEN 1.3*salary
ELSE salary
END
AS “新工资”
FROM employees;
case控制结构的使用二:类似多重if
/*
mysql中case控制结构:
适合:区间判断(大小)
case
when 条件1(true,false) 要显示的值1(尾部不加分号)或语句1(尾部加分号)
when 条件2(true,false) 要显示的值2(尾部不加分号)或语句2(尾部加分号)
…
else 要显示的值n(尾部不加分号)或语句n(尾部加分号)
end
*/
案例:查询员工的工资情况
/*
如果工资大于2万,显示A级别
如果工资大于1万5,显示B级别
如果工资大于1万,显示C级别
否则,显示D级别
*/
SELECT salary,
CASE
WHEN salary>20000 THEN ‘A’
WHEN salary>15000 THEN ‘B’
WHEN salary>10000 THEN ‘C’
ELSE ‘D’
END
AS “工资级别”
FROM employees;
在这里推荐一个软件测试交流群,qq :642830685,群中会不定期的分享软件测试资源,面试题以及测试行业资讯。