函数的分类：
1，单行函数：对每一条记录输入值进行计算，得到相应的计算结果，返回给用户，也就是说，每条记录作为一个输入参数，经过函数计算得到每条记录的计算结果。
2，多行函数：对多条记录输入值进行计算，得到多条记录对应的单个结果。
单行函数细分
1、字符函数
2、数学函数
3、日期函数
4、其他函数
5、流程控制函数
单行函数 - 字符函数
一、字符函数
1. length 获取参数的字节长度
SELECT LENGTH('john');
2. concat 拼接字符
SELECT CONCAT(last_name,'_',first_name) FROM employees;
3. upper,lower
SELECT UPPER('john');
SELECT LOWER('JOHN');
案例：将姓变大写，将名变小写，然后拼接
SELECT CONCAT(UPPER(first_name), LOWER(first_name)) AS "姓名" FROM employees;
4. 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;
5. 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');
一些练习
-- 写一个查询,用首字母大写，其它字母小写显示雇员的 ename，显示名字的长度，并给每列一个适当的标签，条件是满足所有雇员名字的开始字母是J、A 或 M 的雇员，并对查询结果按雇员的ename升序排序。（提示：使用initcap、length、substr）
select concat(SUBSTR(ename,1,1),LOWER(SUBSTR(ename,2,LENGTH(ename)-1))),LENGTH(ename) from emp
where SUBSTR(ename,1,1) in ('J','A','M')
order by ename
-- 查询员工姓名中中包含大写或小写字母A的员工姓名。
select ename from emp
where INSTR(ename,'A') > 0
2.查询部门编号为10或20，入职日期在81年5月1日之后，并且姓名中包含大写字母A的员工姓名，员工姓名长度（提示，要求使用INSTR函数，不能使用like进行判断)
3.查询每个职工的编号,姓名，工资
要求将查询到的数据按照一定的格式合并成一个字符串.
前10位：编号,不足部分用*填充,左对齐
中间10位：姓名，不足部分用*填充,左对齐
后10位：工资，不足部分用*填充,右对齐
二、数学函数
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, 被除数为负数，结果为负数
一些练习
1.写一个查询，分别计算100.456 四舍五入到小数点后第2位，第1位，整数位的值。
2.写一个查询，分别计算100.456 从小数点后第2位，第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 字符串数字转换
//字符串转数字
1 方法一：SELECT CAST('123' AS SIGNED);
2 方法二：SELECT CONVERT('123',SIGNED);
3 方法三：SELECT '123'+0;
//数字转字符串
CONCAT()
FORMAT函数
FORMAT函数在mysql中是数据内容格式化的，格式化后得到结果：＃＃＃，＃＃＃，＃＃＃＃＃。
SELECT FORMAT(100000,2);//100,000.00
一些练习
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;
课后作业
1.计算2000年1月1日到现在有多少月，多少周（四舍五入）。
2.查询员工ENAME的第三个字母是A的员工的信息(使用2个函数)。
3.使用trim函数将字符串‘hello’、‘ Hello ’、‘bllb’、‘ hello ’分别处理得到下列字符串ello、Hello、ll、hello。
4.将员工工资按如下格式显示：123,234.00 RMB 。
5.查询员工的姓名及其经理编号，要求对于没有经理的显示“No Manager”字符串。
6.将员工的参加工作日期按如下格式显示：月份/年份。
7.在员工表中查询出员工的工资，并计算应交税款：如果工资小于1000,税率为0，如果工资大于等于1000并小于2000，税率为10％，如果工资大于等于2000并小于3000，税率为15％，如果工资大于等于3000，税率为20％。
8.创建一个查询显示所有雇员的 ename和 sal。格式化sal为 15 个字符长度，用 $ 左填充，列标签 SALARY。
网友评论