(四)SQL常见函数
- 1. 基础
- 1.1 概念
- 1.2 调用
- 1.3 分类
- 2. 字符函数
- 3. 数学函数
- 4. 日期函数
- 5. 其他函数
- 6. 流程控制函数
- 7. 分组函数
- 7.1 功能
- 7.2 简单使用
- 7.3 参数支持类型
- 7.4 忽略 null
- 7.5 和 DISTINCT 搭配
- 7.6 count 函数详细介绍
- 7.7 和分组函数一同查询的字段有限制
1. 基础
1.1 概念
将一组逻辑语句封装在方法中,对外暴露方法名。
SQL 提供给了我们内置函数,同时,我们也可以自定义函数。
1.2 调用
SELECT 函数名(实参列表) [FROM 表];
1.3 分类
常见函数分为 单行函数 和 分组函数
单行函数 | 分组函数 |
---|---|
字符函数 | SUM |
日期函数 | AVG |
数学函数 | MAX |
其他函数 | MIN |
流程控制函数 | COUNT |
2. 字符函数
-
LENGTH
LENGTH()
获取参数的 字节 个数 -
CONCAT
CONCAT()
拼接字符串 -
uppser、lower
示例:姓变大写,名变小写,拼接SELECT CONCAT(UPPER(last_name),'_', LOWER(first_name)) AS 姓名 FROM employees;
-
SUBSTR、SUBSTRING
语法:- SUBSTR(str, start, len)
- SUBSTR(str, len)
注意:索引从1开始
SELECT SUBSTR('李莫愁爱上了陆展元', 7) AS output; SELECT SUBSTR('李莫愁爱上了陆展元', 1, 3) AS output; SELECT CONCAT(UPPER(SUBSTR(last_name, 1, 1)),'_', LOWER(SUBSTR(last_name, 2))) AS 姓名 FROM employees;
-
insert
返回子串第一次出现的位置,如果找不到则返回 0SELECT INSTR('杨殷六侠不悔爱上了殷六侠', '殷八侠') AS output;
-
TRIM
SELECT LENGTH(TRIM(' 张翠山 ')) AS output;#9 SELECT LENGTH(' 张翠山 ') AS output;#11 SELECT TRIM('aa' FROM 'aaa张aaaaa翠aaa山aaaaaaaa') as output;
-
LPAD
用指定字符左填充至指定长度SELECT LPAD('殷素素', 12, '*') AS output;
-
RPAD
用指定字符右填充至指定长度SELECT RPAD('殷素素', 12, '*') AS output;
-
REPLACE
SELECT REPLACE('张无忌爱上了周芷若', '周芷若','赵敏') AS output;
3. 数学函数
-
round 四舍五入
SELECT ROUND(-1.5); 重载:小数点后保留2位 SELECT ROUND(1.567, 2);
-
ceil 向上取整,返回>=该参数的最小整数
SELECT CEIL(-1.2) AS 结果;
-
floor 向下取整,返回<=该参数的最大整数
SELECT FLOOR(-1.7) AS 结果;
-
TRUNCATE 截断
SELECT TRUNCATE(1.65,1) AS 结果;
-
mod 取余
SELECT MOD(19,3);
4. 日期函数
返回当前系统日期+时间
SELECT NOW();
返回当前系统日期,不包含时间
SELECT CURDATE();
获取指定部分,年、月、日、小时、分钟、秒
SELECT YEAR(NOW()) AS 年;
SELECT YEAR(hiredate) FROM employees;
SELECT MONTH(NOW()) 月;
SELECT MONTHNAME(NOW()) 月;
5. 其他函数
SELECT VERSION(); --当前数据库服务器的版本database当前打开的数据库
SELECT USER(); --当前用户
SELECT PASSWORD('字符'); -- 返回该字符的密码形式md5(字符):返回该字符的md5
SELECT VERSION();
SELECT DATABASE();
6. 流程控制函数
-
IF
函数SELECT IF(10>5, '大', '小'); SELECT last_name, commission_pct, IF(commission_pct IS NULL,'没奖金呵呵','有奖金,哈哈') FROM employees;
-
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 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;
-
case
函数使用二:类似多重if
CASE WHEN 条件1 THEN 要显示的值1或语句1 WHEN 条件2 THEN 要显示的值2或语句2 ... ELSE 要显示的值n或语句n END
案例:查询员工的工资的情况
如果工资>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;
7. 分组函数
7.1 功能
功能:用作统计使用,又称为聚合函数或统计函数或组函数。
函数 | 含义 |
---|---|
SUM | 求和 |
AVG | 平均值 |
MAX | 最大值 |
MIN | 最小值 |
count | 计算个数 |
7.2 简单使用
SELECT SUM(salary) AS 工资和 FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT COUNT(salary) FROM employees;SELECT SUM(salary) AS 和, ROUND(AVG(salary),2) AS 平均,MAX(salary) 最大, MIN(salary) 最低, COUNT(salary) 个数
FROM employees;
7.3 参数支持类型
sum
、avg
只支持数值型max
、min
、count
支持所有类型
# 不支持以下类型,不合法
SELECT SUM(last_name), AVG(last_name) FROM employees;
# max和min支持字符型和日期型
SELECT MAX(last_name), MIN(last_name), MIN(hiredate) FROM employees;
# count 支持所有
SELECT COUNT(commission_pct), COUNT(last_name) FROM employees;
7.4 忽略 null
这些分组函数都忽略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;
7.5 和 DISTINCT 搭配
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
SELECT COUNT(DISTINCT salary), COUNT(salary) FROM employees;
7.6 count 函数详细介绍
统计行数:
SELECT COUNT(*) FROM employees;
参数为常量值,则也统计行数:
SELECT COUNT(1) FROM employees;
效率:
- MYISAM 存储引擎下,
count(*)
的效率最高 - INNODB 存储引擎下,
count(*)
和count(字段)
的效率差不多,比count(字段)
效率高
7.7 和分组函数一同查询的字段有限制
和分组函数一同查询的字段要求是 group by
后的字段
SELECT AVG(salary),employee_id FROM employees;