# 进阶4 SQL常见函数
分类:
1/单行函数:
字符函数:
concat(),length(),ifnull(__,default) ,instr(),
trim(),upper(),lower(),lpad(),rpad(),replace()
数学函数:
round,ceil,floor,truncate,mod;
日期函数:
now,curdate,curtime, year,month,monthname,day,hour,
minute,second,str_to_date, date_format
其他函数:
2/多行函数: 做统计用
/*
概念:类似于java的方法,将一组逻辑语句封装在方法体中,对外仅暴露方法名
好处:1/隐藏了实现的细节
2.提高了代码的重用性
调用: select 函数名(实参列表) [from 表];
分类:
1/单行函数: concat(),length(),ifnull(__,default)
2/多行函数: 做统计用 */
#单行函数:
#1.字符函数
#length
SELECT LENGTH('六六'); #结果: 6
SELECT LENGTH(''); #结果: 3
#显示字符集
SHOW VARIABLES LIKE '%char%' ; #2.concat() #3.upper()/lower() 大写函数/小写函数
SELECT UPPER('asdd');
#实例:将姓变为大写,名变为小写,然后拼接
SELECT CONCAT(UPPER(last_name),' ', LOWER(first_name)) 姓名
FROM employees; #4 .substr(str,pos, len) / substring
#注意索引从1开始
SELECT SUBSTR('李莫愁爱上了陆展元',7) out_put; SELECT SUBSTR('李莫愁爱上了陆展元',1,3) out_put; #案例: 姓名中首字母字符大写,其他字符小写,然后用_拼接 ,显示出来 SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)) ,'_',LOWER(SUBSTR(last_name,2))) out_put
FROM employees; #5.instr(s1,s2 ) 返回子串s2在母串中出现的第一次索引(从1开始),找不到返回0
SELECT INSTR('从前的那个从其那','从前') out_put; #6. trim #python的用法一致
SELECT TRIM(' 张三 ') AS out_put;
SELECT TRIM('a' FROM 'aaaaa张aaaaaaa三aaaaaaaaaaaaaaaa') AS out_put; #7.lpad 用指定字符实现左填充直指定长度
SELECT LPAD('殷素素',10,'*') AS out_put; #*******殷素素
SELECT LPAD('殷素素',2,'*') AS out_put; #殷素 #8.rpad 用指定字符实现右填充直指定长度
SELECT RPAD('殷素素',10,'*') AS out_put; #殷素素*******
SELECT RPAD('殷素素',2,'*') AS out_put; #殷素 #9.replace 全部替换
SELECT REPLACE('张无忌爱上了周芷若','周芷若','赵敏') AS put; #张无忌爱上了赵敏 ########数学函数 (第二个参数表示保留小数点后位数) #1.round 四舍五入 ,第二个参数表示保留小数点后位数
SELECT ROUND(1.65); #2
SELECT ROUND(1.35); #1
SELECT ROUND(1.567,2); #1.57 #2.ceil 绝对向上取整 ,返回>=该参数的最小整数
SELECT CEIL(1.0); #1
SELECT CEIL(1.0001); #2
SELECT CEIL(-1.02); #1 #3.floor 向下取整,返回<=该参数的最大整数
SELECT FLOOR(-9.99); #-10
SELECT FLOOR(9.99); #9 #4.truncate 截断取整
SELECT TRUNCATE(1.699999,1) ; #1.6 #5.mod取余
/*
mod(a,b) : a-a/b*b即为取余结果
*/
SELECT MOD(-10,-3); #-1 #三.日期函数:
#1.now 返回系统当前日期+时间
SELECT NOW(); #2018-10-23 17:18:08 #2.curdate #返回当前的日期
SELECT CURDATE(); #2018-10-23 #3.curtime 返回当前的时分秒
SELECT CURTIME(); #17:17:47 #4.可以获取指定的时间部分: 年(YEAR)/月(MONTH)/日(day)/小时(hour)/分钟(minute)/秒(second)
SELECT YEAR(NOW()); #2018
SELECT YEAR('2008-10-10'); #2008 #5.str_to_date : 将字符串转换成标准格式的日期
#标准匹配符: 年(%Y.%y)/月(%m)/日(%d)/小时(%H.%h)/分钟(%i)/秒(%s)
SELECT STR_TO_DATE('9-13-1999','%c-%d-%Y'); #1999-09-13 #6.DATE_format :将日期转换成标准格式的字符串
SELECT DATE_FORMAT('2018/6/6','%Y年%m月%d日'); #2018年06月06日 #案例.查询入职日期为 1992-4-3的员工信息
SELECT * FROM employees WHERE hiredate = '1992-4-3';
SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%m-%d %Y'); #案例:查询有奖金的员工名和入职时间(xx月/xx日 xx年)
SELECT CONCAT(last_name," ",first_name),DATE_FORMAT(hiredate,'%m月/%d日 %Y年')
FROM employees
WHERE commission_pct IS NOT NULL; #四 .其他函数
SELECT VERSION(); #8.0.12
SELECT DATABASE(); #myemployees
SELECT USER(); #root@localhost #五. 流程控制函数 #1.if函数 : if else 的效果
SELECT IF(10<5,'大','小');
SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金,呵呵','有奖金,嘻嘻') 备注
FROM employees; #2.case 函数/结构 的使用1: switch case的效果
/*
mysql 中:
{ case 要判断的字段或者表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
...
else 要显示的值n或者语句n;
end }
*/
#举个栗子
#部门号为30,显示工资为1.1倍;
#部门号为40,显示工资为1.2倍;
#部门号为50,显示工资为1.3倍;
#其他部分不变!
SELECT salary 原工资 ,department_id, CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 40 THEN salary*1.3
ELSE salary
END AS 新工资
FROM employees; #2.case 函数/结构 的使用二: 类似于多重if 语句
/*
mysql 中:
{ case
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
...
else 要显示的值n或者语句n;
end }
*/
#举个栗子
#工资>20000, 显示A等级
#工资>15000, 显示A等级
#工资>10000, 显示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;