MySQL基础(五)——DQL之常见函数

本篇文章主要是对MySQL学习时的一些总结，作为学习笔记记录。

数据来源

数据部分来自于b站尚硅谷MySQL课程

常见函数

MySQL中的函数与编程语言如C/C++中的函数类似，都是将某些功能进行封装，而只对外暴露函数名，提高了代码的重用性

调用

SELECT funcname(args) FROM tablename;

分类

MySQL中的函数主要有单行函数和分组函数，分组函数主要做统计使用，又称为统计函数、聚合函数或组函数，这里主要说明单行函数。

字符函数

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

SELECT LENGTH('abc');
SELECT LENGTH('一二三abc');
SHOW VARIABLES LIKE '%char%';

上面的代码中，由于MySQL使用的字符集为utf-8，因此单个汉字的长度为3。

2. concat()可以用来拼接字符串

SELECT CONCAT(last_name, ' ', first_name) FROM employees;

3. upper() lower()可以用来进行大小写转换

SELECT UPPER('abc');
SELECT LOWER('ABC');

#示例：将姓变大写，名变小写，然后拼接
SELECT CONCAT(UPPER(last_name),LOWER(first_name)) FROM employees;

4. substr()可以提取子字符串

#截取从指定索引处后面所有字符，索引从1开始
SELECT SUBSTR('abcdefghijklmn',7);

#截取从指定索引处指定字符长度的字符，索引从1开始
SELECT SUBSTR('abcdefghijklmn',1,3);

#案例：姓名中首字符大写，其他字符小写然后用_拼接，显示出来，索引从1开始
SELECT CONCAT(UPPER(SUBSTR(last_name, 1, 1)),LOWER(SUBSTR(last_name, 2))) FROM employees;

5. instr()返回子字符串第一次出现时的索引，找不到则返回0

SELECT INSTR('abcdefghijklmn','f');

6. trim()去除字符串前后的空格

SELECT LENGTH(TRIM('   abc   '));
SELECT TRIM('x' FROM 'xxxabcxxxabcxxx');

7. lpad()，rpad()使用指定的字符左填充或右填充指定长度

SELECT LPAD('abcd',6,'*');
SELECT LPAD('abcd',2,'*');
SELECT RPAD('abcd',6,'*');
SELECT RPAD('abcd',2,'*');

8. replace()可以实现字符串替换

SELECT REPLACE('abcabcabc','a','x');

数学函数

1. round()可以进行四舍五入

SELECT ROUND(1.55);
SELECT ROUND(-1.55)
SELECT ROUND(1.567,2);

上方round(1.567,2)中的2说明的保留的小数位数。

2. ceil()向上取整，返回不小于该参数的最小整数

SELECT CEIL(-1.02);

3. floor()向下取整，返回不大于该参数的最小整数

SELECT FLOOR(-1.02);

4. truncate()可以实现小数截断

SELECT TRUNCATE(1.685,1);

5. mod()可以进行取余

SELECT MOD(10,-3);
SELECT 10%3;

日期函数

1. now()返回当前系统日期和时间

SELECT NOW();

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

SELECT CURDATE();

3. curtime()返回当前系统时间，不包含日期

SELECT CURTIME();

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

SELECT YEAR(NOW());
SELECT YEAR('1998-1-1');
SELECT YEAR(hiredate) FROM employees;
SELECT MONTH(NOW());
SELECT MONTHNAME(NOW());

5. str_to_date()将字符通过指定的格式转换成日期

SELECT STR_TO_DATE('1998-1-1','%Y-%c-%d');

#查询入职日期为1992--4-3的员工信息
SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');

6. date_format()将日期转换为字符

SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日');

#查询有奖金的员工名和入职日期(xx月/xx日 xx年)
SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %Y年') FROM employees WHERE commission_pct IS NOT NULL;

其它函数

1. version()返回当前MySQL的版本号

SELECT VERSION();

2. database()返回当前使用的数据库

SELECT DATABASE();

3. user()返回当前用户

SELECT USER();

流程控制函数

1. if()函数第一个参数为条件表达式，第二个参数为条件表达式为真时的执行语句，第三个参数为条件表达式为假时的执行语句

SELECT IF(10<5,'<','>');
SELECT last_name,commission_pct,IF(commission_pct,'NULL','NOT NULL') FROM employees;

2. case语句有两种使用情况

第一种情况的语法为：

case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
...
else 要显示的值n或语句n;
end

第二种情况的语法为：

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

两种情况下的示例为：

# 情况一
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 new_salary
FROM employees;

# 情况二
SELECT salary,
CASE
WHEN salary > 20000 THEN 'A'
WHEN salary > 15000 THEN 'B'
WHEN salary > 10000 THEN 'C'
ELSE 'D'
END AS leval
FROM employees;