四、常见函数
- 概念:类似于java的方法,将一组逻辑语句封装在方法体中,对外暴露方法名
- 好处:1、隐藏了实现细节 2、提高代码的重用性
- 调用:select 函数名(实参列表) 【from 表】;
- 特点:
① 叫什么(函数名)
② 干什么(函数功能) - 分类:
① 单行函数:如 concat、length、ifnull等
② 分组函数。功能:做统计使用,又称为统计函数、聚合函数、组函数。
一、单行函数
1、字符函数
concat拼接
substr截取子串
upper转换成大写
lower转换成小写
trim去前后指定的空格和字符
ltrim去左边空格
rtrim去右边空格
replace替换
lpad左填充
rpad右填充
instr返回子串第一次出现的索引
length 获取字节个数
2、数学函数
round 四舍五入
rand 随机数
floor向下取整
ceil向上取整
mod取余
truncate截断
3、日期函数
now当前系统日期+时间
curdate当前系统日期
curtime当前系统时间
str_to_date 将字符转换成日期
date_format将日期转换成字符
year
month
monthname
day
hour
minute
second
4、流程控制函数
if 处理双分支
case语句 处理多分支
情况1:处理等值判断
情况2:处理条件判断
5、其他函数
version版本
database当前库
user当前连接用户
1、单行函数
(1)字符函数:
① length( ) :获取参数值的字节个数
SELECT LENGTH('john'); #返回4
SELECT LENGTH('张三丰hahaha'); #返回15,一个汉字代表三个字节
SHOW VARIABLES LIKE '%char%' #查看字节相关信息
② concat() 拼接字符串
SELECT CONCAT(last_name,'_',first_name) as 姓名
FROM employees;
#返回lastname_firstname
③ upper()转换成大写
SELECT UPPER('john'); #返回JOHN
④ lower()转换成小写
SELECT LOWER('joHn'); #返回john
案例:将姓变大写,名变小写,然后拼接
SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名
FROM employees;
⑤ substr ()/substring()截取子串
- 注意:索引从1开始
#截取从指定索引处后面所有字符
SELECT SUBSTR('李莫愁爱上了陆展元哈哈哈',7) as out_put;
#从第7个字符开始,截取后面的所有字符;返回 陆展元哈哈哈
#截取从指定索引处指定字符长度的字符
SELECT SUBSTR('李莫愁爱上了陆展元',1,3) out_put;
#从第一个索引开始,截取三个字符长度。返回 李莫愁;
案例:姓名中首字符大写,其他字符小写然后用_拼接,显示出来
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2)))
as out_put
FROM employees;
#从last_name的第一索引开始,截取一个字符,将其大写;
#从last_name的第二索引开始,截取后面的所有字符,将其小写。
⑥ instr ()返回子串第一次出现的索引,如果找不到返回0
SELECT INSTR ('杨不殷六侠悔爱上了殷六侠','殷六侠')AS out_put;
#殷六侠第一出现是在第三个索引处,返回3
SELECT INSTR('杨不殷六侠悔爱上了殷六侠','殷八侠') AS out_put;
#没有殷八侠,返回0
⑦ trim()去左右边空格
SELECT TRIM(' 张翠山 ') AS out_put;
#去掉左右两边空格;返回“张翠山”
SELECT LENGTH(TRIM(' 张翠山 ')) AS out_put;
#张翠山三个字符,9个字节。返回 9;
SELECT TRIM('aa' FROM 'aaaaa张aaaa翠山aaaaa') AS out_put;
#去掉左边的两个‘aa’,右边的两个‘aa’;返回‘a张aaaa翠山a’
SELECT TRIM('ab' FROM 'abab abddabcabab') AS haha;
#返回abddabc,去掉左右两边的ab
⑦ lpad ()用指定的字符实现左填充指定长度
SELECT LPAD('殷素素',5,'*') AS out_put;
#用*实现左填充殷素素到5个字符长度;返回 **殷素素
SELECT LPAD('殷素素',2,'*') AS out_put;
#返回 殷素
⑧ rpad ()用指定的字符实现右填充指定长度
SELECT RPAD('殷素素',8,'ab') AS out_put;
#用ab实现右填充殷素素到8个字符长度;返回 殷素素ababa
SELECT RPAD('殷素素',2,'*') AS out_put;
#返回 殷素
⑨ replace() 替换
SELECT REPLACE('周芷若周芷若张无忌爱上了周芷若','周芷若','赵敏')
AS out_put;
#将所有周芷若替换赵敏;返回为:赵敏赵敏张无忌爱上了赵敏,
(2)数学函数
① round ()四舍五入
SELECT ROUND(-1.55); #返回-2
SELECT ROUND(1.567,2); #保留小数点后两位,返回1.57
② floor()向下取整
SELECT FLOOR(-9.99); #返回<=该参数的最大整数;返回-10
③ ceil()向上取整,
SELECT CEIL(-1.02); #返回>=该参数的最小整数;返回-1
④ mod取余
- mod(a,b) : a-a/b*b
- mod(-10,-3): -10- (-10)/(-3)*(-3)=-1
SELECT MOD(10,-3); #10除以-3,取余数;返回1
SELECT 10%3; #返回1
⑥ truncate截断
SELECT TRUNCATE(1.69999,1);
#从小数点后一位开始截断;返回1.6
(3)日期函数
① now()当前系统日期+时间
SELECT NOW(); #返回当前时间;2021-06-19 22:16:41
② curdate()当前系统日期,不包含时间
SELECT CURDATE(); #返回2021-06-19
③ curtime()当前系统时间,不包含日期
SELECT CURTIME(); #返回22:16:41
④ str_to_date() 将字符转换成日期
SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d')
AS out_put;
#返回1998-03-02
案例:查询入职日期为1992–4-3的员工信息
SELECT * FROM employees WHERE hiredate = '1992-4-3';
SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');
⑤ date_format()将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS out_put;
#返回21年06月19日
#查询有奖金的员工名和入职日期(xx月/xx日 xx年)
SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') 入职日期
FROM employees
WHERE commission_pct IS NOT NULL;
⑥ year、month、 monthname、day、 hour、 minute、second
#可以获取指定的部分,年、月、日、小时、分钟、秒
SELECT YEAR(NOW()) as 年 ; #返回2021
SELECT YEAR('1998-1-1') as 年; #返回1998
SELECT YEAR(hiredate) 年 FROM employees;
SELECT MONTH(NOW()) 月; #返回6
SELECT MONTHNAME(NOW()) 月; 返回June
(4)其他函数
(5) 流程控制函数
① if函数: if else
语句:
SELECT IF(条件语句,'A','B'); #如何条件语句为true,返回A,为false,返回B
#案例
SELECT IF(10<5,'大','小'); #,条件语句为false;返回小
SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金,呵呵','有奖金,嘻嘻') as 备注
FROM employees;
#返回员工姓名和奖金,如果没有奖金,返回“没奖金,呵呵”有奖金返回“有奖金,嘻嘻”。
② case函数的使用一
语句:
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 as 原始工资,department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END AS 新工资
FROM employees;
③ case 函数的使用二
语句:
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
。。。
else 要显示的值n或语句n
end
案例1:查询员工的工资的情况:如果工资>20000,显示A级别;如果工资>15000,显示B级别;如果工资>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;
案例2:查询员工号,姓名,工资,以及工资提高百分之20%后的结果(new salary)
SELECT employee_id,last_name,salary,salary*1.2 "new salary"
FROM employees;
案例3:将员工的姓名按首字母排序,并写出姓名的长度(length)
SELECT LENGTH(last_name) 长度,SUBSTR(last_name,1,1) 首字符,last_name
FROM employees
ORDER BY 首字符;
案例4:一个查询,产生下面的结果<last_name> earns monthly but wants <salary3> as Dream Salary;如:King earns 24000 monthly but wants 72000*
SELECT CONCAT(last_name,' earns ',salary,' monthly but wants ',salary*3) AS "Dream Salary"
FROM employees
WHERE salary=24000;
案例5: 使用case-when,按照下面的条件:
job grade
AD_PRES A
ST_MAN B
IT_PROG C
SA_REP D
ST_CLERK E
产生下面的结果
Last_name Job_id Grade
king AD_PRES A
SELECT last_name,job_id AS job,
CASE job_id
WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN' THEN 'B'
WHEN 'IT_PROG' THEN 'C'
WHEN 'SA_PRE' THEN 'D'
WHEN 'ST_CLERK' THEN 'E'
END
AS Grade
FROM employees
WHERE job_id = 'AD_PRES';
2、分组函数
-
功能:用作统计使用,又称为聚合函数或统计函数或组函数
-
分类:sum 求和、avg 平均值、max 最大值 、min 最小值 、count 计算个数
-
特点:
① sum、avg一般用于处理数值型;max、min、count可以处理任何类型
② 以上分组函数都忽略null值 -
可以和distinct搭配实现去重的运算
-
count函数的单独介绍;一般使用count(*)用作统计行数
-
和分组函数一同查询的字段要求是group by后的字段
(1)简单的使用
SELECT SUM(salary) AS 和,AVG(salary) AS 平均,MAX(salary) AS 最高,MIN(salary) AS 最低,COUNT(salary) AS 个数
FROM employees;
(2)参数支持哪些类型
SELECT SUM(last_name) ,AVG(last_name) FROM employees;
#返回数值0;0
SELECT SUM(hiredate) ,AVG(hiredate) FROM employees;
#将hiredate看作数值型,做数值运算
SELECT MAX(last_name),MIN(last_name) FROM employees;
SELECT MAX(hiredate),MIN(hiredate) FROM employees;
SELECT COUNT(commission_pct) FROM employees;
SELECT COUNT(last_name) FROM employees;
(3)是否忽略null
SELECT SUM(commission_pct) ,AVG(commission_pct),SUM(commission_pct)/35,SUM(commission_pct)/107 FROM employees;
SELECT MAX(commission_pct) ,MIN(commission_pct) FROM employees;
SELECT COUNT(commission_pct) FROM employees;
SELECT commission_pct FROM employees;
(4)和distinct搭配
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;
(5)count函数的详细介绍
SELECT COUNT(salary) FROM employees;
SELECT COUNT(*) FROM employees;
SELECT COUNT(1) FROM employees;
- 效率:
① MYISAM存储引擎下 ,COUNT()的效率高
② INNODB存储引擎下,COUNT()和COUNT(1)的效率差不多,比COUNT(字段)要高一些
(6)和分组函数一同查询的字段有限制
SELECT AVG(salary),employee_id FROM employees;
案例.查询员工表中的最大入职时间和最小入职时间的相差天数 (DIFFRENCE)
SELECT MAX(hiredate) 最大,MIN(hiredate) 最小,(MAX(hiredate)-MIN(hiredate))/1000/3600/24 DIFFRENCE
FROM employees;
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) DIFFRENCE
FROM employees;
SELECT DATEDIFF('1995-2-7','1995-2-6');
案例:查询部门编号为90的员工个数
SELECT COUNT(*) FROM employees WHERE department_id = 90;