5、常见函数
- 概念:类似于java的方法,将一组逻辑语句封装在方法体中,对外暴露方法名
- 好处:1、隐藏了实现细节 2、提高代码的重用性
- 调用: select 函数名(实参列表) 【from 表】;
特点:
- 叫什么(函数名)
- 干什么(函数功能)
分类:
- 单行函数
如 concat 、length 、 ifnull等 - 2、分组函数
功能:做统计使用,又叫统计函数、聚合函数、组函数
常见函数:
字符函数:
length
concat
substr
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
其他函数:
version
database
user
控制函数:
if
case
5.1、字符函数
5.1.1、length 获取参数值的字节个数
SELECT LENGTH('lzb');
SELECT LENGTH('笨猪aaa'); #一个中文3个字节
5.1.2、查看字符集
SHOW VARIABLES LIKE '%char%';
5.1.3、 concat 拼接字符串
SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees;
5.1.4、转换大小写,upper、lower
SELECT UPPER('xlz');
SELECT LOWER('lDd');
5.1.5、示例:将姓变大写,名变小写,然后拼接
SELECT CONCAT(UPPER(last_name),'_',LOWER(first_name)) 姓名 FROM employees;
5.1.6、substr、substring
注意:索引从1开始
#截取从指定索引处后面所有字符
SELECT SUBSTR('你好啊他了是',3) out_put;
#截取从指定索引处指定字符长度的字符
SELECT SUBSTR('你好啊他了是',1,3) out_put;
5.1.7、案例1:姓名中首字符大写,其他字符小写然后用_拼接,显示出来
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)) , '_' , LOWER(SUBSTR(last_name,2))) out_put
FROM employees;
5.1.8、instr 返回子串第一次出现的索引,如果找不到返回0
SELECT INSTR('你好啊你','你') AS out_put;
5.1.9、trim 去除两边空格或去除两边字符
SELECT LENGTH(TRIM(' 你好 ')) AS out_put;
SELECT TRIM('a' FROM 'aaaaaaaaaaaaa你好aaaaa了aaaa') AS out_put;
5.1.10、lpad 用指定的字符实现左填充指定长度
SELECT LPAD('你好了',10,'*') AS out_put;
#如果指定的长度小于原来的,就会截取
SELECT LPAD('你好了',2,'*') AS out_put;
5.1.11、rpad 用指定的字符实现右填充指定长度
SELECT RPAD('你好啊',12,'ab') AS out_put;
5.1.12、replace 替换
#被替换的 #替换成 小明
SELECT REPLACE('阿真爱上了阿强','阿真','小明') AS out_put;
5.2、数学函数
5.2.1、round 四舍五入
SELECT ROUND(1.55);
SELECT ROUND(1.556,2); #保留后2位
5.2.2、ceil 向上取整,返回 >= 该参数的最小整数
SELECT CEIL(1.02); #结果 2
SELECT CEIL(0.1); #结果 1
5.2.3、floor 向下取整,返回 <= 该参数的最大整数
SELECT FLOOR(9.99); #结果 9
SELECT FLOOR(10.1); #结果 10
5.2.4、truncate 截断
SELECT TRUNCATE(1.666669,1); #结果1.6
5.2.5、mod 取余
/*
mod(a,b) : a-a/b*b
mod(-10,-3): -10- (-10)/(-3)*(-3) = -1
*/
SELECT MOD(10,-3); #结果 1 看第一位,如果是正数就是正数
SELECT 10 % 3;
5.3、日期函数
5.3.1、now 返回当前系统日期+时间
SELECT NOW();
5.3.2、curdate 返回当前系统日期,不包含时间
SELECT CURDATE();
5.3.3、curtime 返回当前时间,不包含日期
SELECT CURTIME();
5.3.4、year 可以获取指定的部分,年、月、日、小时、分钟、秒
SELECT YEAR(NOW()) 年; #当前年份2021
SELECT YEAR('1998-1-1') 年; # 1998
5.3.5、查询年份
SELECT YEAR(hiredate) 年 FROM employees;
SELECT MONTH(NOW()) 月; #当前月份
SELECT MONTHNAME(NOW()) 月; #返回 英文的月份
5.3.6、str_to_date 将日期格式的字符转换成指定格式的日期
SELECT STR_TO_DATE('1998-1-1','%Y-%c-%d') AS out_put;
5.3.7、查询入职日期为 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');
5.3.8、date_format 将日期转换成字符
SELECT DATE_FORMAT(NOW() , '%y年%m月%d日') AS out_put;
5.3.9、查询有奖金的员工名和入职日期(xx月/xx日 xx年)
SELECT last_name , DATE_FORMAT(NOW(),'%m月/%d日 %y年') 入职日期
FROM employees;
5.4、其他函数
SELECT VERSION(); #数据库版本
SELECT DATABASE(); #当前数据库
SELECT USER(); #当前用户
5.5、流程控制函数
5.5.1、if函数 :if else 的效果
SELECT IF(6>5 , '大','小');
SELECT IF(5<6 ,'小','大');
SELECT last_name,commission_pct,
IF(commission_pct IS NULL,'没有','有') AS 备注
FROM
employees;
5.5.2、case函数的使用一:swithch case 的效果
/*
mysql中的用法:
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1
when 常量2 then 要显示的值2或语句2
.....
else 要显示的值n或语句n
end
*/
5.5.3、案例:查询员工的工资,要求,部门号=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 50 THEN salary*1.3
ELSE salary
END AS 新工资
FROM employees;
5.5.4、case 函数的使用二:类似 多重if
/*
mysql 中的用法
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
....
else 要显示的值 n 或语句n
end
*/
5.5.4、案例:查询员工的工资的情况,要求:
如果工资 > 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;
5.5.5、练习测试
#测试
#1. 显示系统时间(注:日期+时间)
SELECT NOW();
#2. 查询员工号,姓名,工资,以及工资提高百分之 20%后的结果(new salary)
SELECT employee_id,last_name,salary ,salary * 0.2 AS "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 <salary> monthly but wants <salary*3>
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;
#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 job_id AS job ,
CASE job_id
WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN' THEN 'B'
WHEN 'IT_PROG' THEN 'C'
WHEN 'SA_REP' THEN 'D'
WHEN 'ST_CLERK' THEN 'E'
END AS Grade
FROM employees;