MySQL复习（二）常见函数，日期函数，其他函数，流程控制函数

MySQL复习（二）常见函数，日期函数，其他函数，流程控制函数

基础函数

分类:
1.单行函数
如 CONCAT、LENGTH、IFNULL等
2.分组函数
功能:做统计使用,又称统计函数、聚合函数、组函数。

一、字符函数

1.LENGTH(str) 获取参数值的字节个数

SELECT LENGTH('CALW');
 思考一下占用几个字节?(跟字符集相关)
SELECT LENGTH('写作业学SQL');
查看数据库字符集
SHOW VARIABLES LIKE '%char%'

2.CONCAT(str1,str2,…) 拼接字符串

SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees;
# 结果:
Kochhar_Neena

3.UPPER(str) LOWER(str) 大写转换,小写转换

# 结果:XZY
SELECT UPPER('xzy');
# 结果:xzy
SELECT LOWER('XZY');

# 示例:将姓变大写，名变小写，然后拼接
# 结果:KOCHHAR_neena
SELECT CONCAT(UPPER(last_name),'_',LOWER(first_name)) FROM employees;

4.SUBSTR 截取

SUBSTR(str FROM pos FOR len) ;
SUBSTR(str FROM pos) ;
SUBSTR(str,pos) ;

# 注意:索引从1开始
SELECT SUBSTR('不会只有我还在学SQL吧?',6) 一个别名
# 结果:还在学SQL吧?
SUBSTR(str,pos)
#SUBSTRING(str FROM pos FOR len) 截取从指定索引处指定字符长度的字符
# 结果:还在学
SELECT SUBSTR('不会只有我还在学SQL吧?',6,3) 一个别名
#案例: 姓名中首字符大写,其他字符小写.
# 结果:NEENA
SELECT UPPER(SUBSTR(first_name,1)) 姓名 FROM employees

5.SUBSTR 截取

SUBSTRING(str FROM pos) ;
SUBSTRING(str,pos) ;
SUBSTRING(str,pos,len);
同理SUBSTR 的用法，不再示例。

6.INSTR(str,substr) 返回字符串第一次出现的索引,如果找不到返回0

# 结果:1
SELECT INSTR('学好SQL是有必要的','学');
# 结果:10
SELECT INSTR('学好SQL是有必要的','的');
# 结果:0
SELECT INSTR('学好SQL是有必要的','无');

7.TRIM([remstr FROM] str) 去除前后空格或者指定字符

# 结果:CLAW
SELECT TRIM( '     CLAW    ');
# 取字节长度时仍以原长度为主
# 结果:4
SELECT LENGTH(TRIM('     CLAW    '));
## 结果:CLAWAAACLAWAAACLAW
SELECT TRIM('A' FROM 'AAAAAAAAACLAWAAACLAWAAACLAWAAAAAAAAAAAAA')

8.TRIM([remstr FROM] str) 去除前后空格或者指定字符

# 结果:FIVE*
SELECT RPAD('FIVE',5,'*');

9.LPAD(str,len,padstr) 用指定的字符实现左填充指定长度

# 指定了长度为5,用*左填充了一个,一共5个字符长度 
# 结果:*FIVE
SELECT LPAD('FIVE',5,'*');
# 如果长度超过本来的长度,将不会填充,反而会被截取.
# 结果:aa
SELECT LPAD('aaa',2,'*');

10.RPAD(str,len,padstr) 用指定的字符实现右填充指定长度

SELECT RPAD('FIVE',5,'*');

11.REPLACE`(str,from_str,to_str) 替换

#结果:小C爱上了小B
SELECT REPLACE('小A爱上了小B','小A','小C');

数学函数

1.ROUND(X) 四舍五入

# 结果:3
SELECT ROUND(3.1111111)
# 结果:3.23
SELECT ROUND(3.22929999922222,2)

2.CEIL(X) 向上取整,返回>=该参数的最小整数

# 结果:3
# 结果:2
SELECT CEIL(1.0000000000001)
# 结果:-1
SELECT CEIL(-1.0000000000001)

3.FLOOR(X) 向下取证,返回<=该参数的最大整数

# 结果: -9
SELECT FLOOR(9.9)
# 结果: -10
SELECT FLOOR(-9.9)

4.TRUNCATE(X,D) 截断,小数点后保留位数。

# 结果: 9.9
SELECT TRUNCATE(-9.9999999,1)
# 结果: 9.99
SELECT TRUNCATE(-9.9999999,2)

5.MOD(N,M) 取余

SELECT MOD(10,3);
# 思考,如果其中一个为负数,mod的结果是怎样的?
# 结果:1
SELECT MOD(10,-3)
# 结果:-1
SELECT MOD(-10,-3)
# 结果:1
SELECT MOD(10,3)
# 为什么会这样?
# mod底层的计算 MOD(a,b) :a-a/b*b
# 比如 MOD(10,-3), 10-(-10)/(-3)*(-3),(-10)/(-3)的结果为3,3 * (-3)的结果为-9,10-(-9)的结果为1
# 所以最终结果为1

日期函数

1.NOW() 返回当前系统日期+时间

# 结果:2021-04-11 21:54:24
SELECT NOW();

2.CURDATE() 返回当前系统日期,不包含时间

# 结果: 2021-04-11
SELECT CURDATE() ;

3.CURTIME() 返回当前时间,不包含日期

# 结果:21:56:53
SELECT CURTIME();

4.获取指定的部分，年、月、日、小时、分钟、秒

SELECT YEAR(NOW())
# 结果:2021
SELECT YEAR('2021-04-11');
# 结果:1992(多结果集)
SELECT YEAR(hiredate) FROM employees;
# 结果:4
SELECT MONTH(NOW());
# 结果:April
SELECT MONTHNAME(NOW());

5.STR_TO_DATE(str,format) 将日期格式的字符转换成指定格式的日期

根据格式符，来进行格式转换。

# 结果:2021-04-11
SELECT STR_TO_DATE('2021-4-11 22:05:39','%Y-%m-%d');
# 结果:1992-04-03
SELECT STR_TO_DATE(hiredate,'%Y-%m-%d') FROM employees;
# 结果: 1992-04-03 00:00:00
SELECT hiredate FROM employees WHERE hiredate = '1992-4-3';
# 结果: 1992-04-03
SELECT STR_TO_DATE('4-3 1992','%c-%d %Y')
# 实际场景 假设传入参数为 4-3 1992 这样的日期格式,不转换是查询不到结果的(数据库里没有这样的格式)
# 那么在条件查询后使用日期格式转换 就很有必要了
SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y')

6.DATE_FORMAT(date,format) 将日期格式转换为指定格式的字符串

# 结果:4-03 1992
SELECT DATE_FORMAT(hiredate,'%c-%d %Y') FROM employees
# 结果:2021年04月11日
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日') FROM employees

流程控制函数

1.IF(expr1,expr2,expr3) 处理双分支

相当于三元表达式，如果表达式为true，则显示前一个表达式，如果为flase，则显示后一个表达式。

# 结果:1>2是假的
SELECT IF(1>2,"1>2是真的","1>2是假的");

2.CASE 结构 处理多分支

使用上分为两种情况：

• 情况1：处理等值判断
• 情况2：处理条件判断

情况1示例：处理等值判断(switch case 的效果)

语法：
CASE要判断的字段或者表达式
WHEN 常量1 then 要显示的值1或者语句1
WHEN 常量2 then 要显示的值2或者语句2
END
…
ELSE要显示的值n或语句

/*
案例:查询员工工资,要求:
部门号=30,显示工资为1.1倍
部门号=40,显示工资为1.2倍
部门号=50,显示工资为1.3倍
**/
SELECT 
salary 原始工资,department_id,
CASE department_id
when department_id = 30 THEN salary *1.1
when department_id = 40 THEN salary *1.2
when department_id = 50 THEN salary *1.3
ELSE salary 
END as 新工资
FROM employees

情况1示例： switch case 的效果

语法:
CASE
WHEN 条件1 THEN 要显示的值1或语句1
WHEN 条件2 THEN 要显示的值2或语句2
…
ELSE 要显示值n或语句n
END

/*
案例:
查询员工的工资的情况
如果工资>20000,显示A级别
如果工资>15000,显示B级别
如果工资>10000,显示C级别
否则,显示D级别
**/
SELECT salary as 工资,
case 
when salary >20000 then 'A' 
when salary >15000 then 'B' 
when salary >10000 then 'C' 
ELSE 'D'
END as 工资级别
FROM
employees