MySQL复习笔记（五）：字符函数、数学函数、日期函数、其他函数、流程控制函数

常见函数

1. 概念：类似于java方法，将一组逻辑语句封装到方法体中，可以使用方法名进行调用
2. 好处：1、隐藏了实现具体细节。2、提高了代码重用性
3. 分类

• 单行函数：如concat、length、ifnull等
• 统计函数：主要用于统计，又称为分组函数、聚合函数、组函数

4. 写法：select 函数名(参数列表) 【from 表名】，函数可以嵌套调用，即参数也可以是函数

单行函数

一、字符函数

1、 length：用于获取参数书值的字节数

select length('abc');   #3
select length('啦啦啦'); #9  (在utf8字符集下，一个汉字三个字节)

2、concat：拼接字符串

select concat('first_name', '_', 'last_name') from employees;

3、upper、lower：转换大小写

#大写名 拼接小写姓
select concat(upper('first_name'), '_', lower('last_name')) from employees;

4、substr、substring：截取字符串(按照字符计数，与length不同)

• substr(str,pos): 从第pos个开始往后截，包括pos，而且从1开始。

select substr('啦啦啦啦啦',2)  #啦啦啦啦

• substr(str, pos, len): 从第pos个开始往后截len个

select substr('123456',2,2) #23

5、INSTR(str,substr): 返回子串在字符串中首次出现的位置，如果没有返回0

select INSTR('赛博朋克2077', '朋克');  #3

6、trim: 去除前后的空格，除了空格可以指定要去除的字符

#去空格
select trim('   12    3       ');    #12   3
#去自定义字符
select trim('a' from 'aabcabcaaa')  #bcabc

7、LPAD(str,len,padstr)：用指定字符在左边填充够指定长度，如果str长度超过len则从右边截取

select LPAD('啊啊啊',5,'拉拉');   #拉拉啊啊啊

8、RPAD(str,len,padstr)：用指定字符在右边填充够指定长度

9、REPLACE(str,from_str,to_str)：将str中的from_str替换为to_str

select replace('动物之森真好玩', '动物之森','房贷之森');

二、数学函数

1、ROUND(X【,D】)：四舍五入，D为想要保留的小数点的位数，可省略。

select ROUND(1.55)  #2
select ROUND(1.547, 2)  # 1.55

2、ceil(x):向上取整，返回>=该参数的最小整数

select ceil(1.001)  # 2
select ceil(-1.001) # -1

3、floor(x): 向下取整，返回<=该参数的最大整数

select floor(1.99)  # 1
select floor(-1.99) # -2

4、TRUNCATE(x,d)：从x的第d位之后阶段

select TRUNCATE(1.222999, 1)  # 1.2

5、mod(a,b):取余，可以利用公司 a-a/b*b计算。计算带符号的mod时，可以简单记成和a的正负号有关

select mod(10, 3)   # 1
select mod(10, -3)   # 1
select mod(-10, -3)   # -1

三、日期函数

1、now:返回当前的日期+时间

select now();  #2020-04-24 10:41:36

2、curdate:返回当前的日期

select curdate();   #2020-04-24

3、curtime：返回当前时间

select curtime();  #10:43:46

4、可以指定年、月、日、小时、分钟、秒

select year(now());  #2020
select month('2020-3-12')   # 3
...

5、STR_TO_DATE(str,format):先根据format指定的格式解析str代表的日期，然后将日期输出

select STR_TO_DATE('3-2020-12', '%m-%Y-%d')   #2020-3-12

6、DATE_FORMAT(date,format)：将日期date转换为format指定的格式

select DATE_FORMAT('2020-3-12','%Y年%m月%d日')   #2020年03月12日

格式符	功能	例子
%Y	四位的年份	2019
%y	两位的年份	97
%m	月份（补零）	07，01，11
%c	月份	1，2，3，11
%d	日	01，02…31
%H	小时(24小时制)	18, 24
%h	小时(12小时制)	1,12
%i	分钟	01，02，59
%s	秒	01，04，59

四、其他函数

SELECT VERSION();  	#查看数据库版本
SELECT DATABASE();	#查看当前使用的书库
SELECT USER();		#查看当前用户

五、流程控制函数

1、IF(expr1,expr2,expr3)：如果expr1为true,则返回expr2的结果，否则返回expr3的结果。

#输出所有员工有无奖金的信息
SELECT last_name, commission_pct,IF(commission_pct is null,'没有奖金','有奖金') as 备注 from employees

2、CASE使用方法一(类似于switch…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 last_name, department_id, salary,
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

3、CASE的使用方法二(类似于if…else结构)：

case 
when 条件1 then 要显示的值1（或者语句1;）
when 条件2 then 要显示的值2（或者语句2;）
...
else 要显示的值n（或者语句n;）
end

例子：查询员工工资情况
如果工资>20000，显示A级别
否则，如果工资>15000，显示B级别
否则，如果工资>10000，显示C级别
否则，显示D级别

SELECT last_name, salary, 
CASE 
 WHEN salary > 20000 THEN 'A'
 WHEN salary > 15000 THEN 'B'
 WHEN salary > 10000 THEN 'C'
 ELSE 'D'
END as '工资级别'
FROM employees;

练习

#1.显示系统时间（日期+时间）
SELECT NOW();



#2.查询员工号、姓名、工资、以及工资提高20%后的结果（new salary）
SELECT employee_id, CONCAT(first_name,' ',last_name), salary, salary*1.2 as 'new salary' FROM employees;



#3.将员工姓名按照首字母排序，并写出姓名长度
SELECT CONCAT(first_name,' ',last_name) as 姓名, LENGTH(CONCAT(first_name,last_name)) as 长度 FROM employees ORDER BY SUBSTR(first_name,1,1);




#4.做一个查询产生下列结果
#  <last_name> earns <salary> monthly but wants <salary*3> Dream salary
#    e.g.:King earns 20000 monthly but wants 60000;
SELECT CONCAT(last_name,' earns ',salary, ' monthly but wants ', salary*3) as 'Dream salary' FROM employees




#5.使用case...when，按照下面条件输出：
#    job_id   grade
#    AD_PRES   A
#    ST_MAN   B
#    IT_PROG   C
SELECT last_name,job_id,
case job_id
WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN' THEN 'B'
WHEN 'IT_PROG' THEN 'C'
ELSE 'D'
END as '级别'
FROM employees