概念:类似于Java中的方法,将一组逻辑语句封装在方法体中,对外暴露方法名 好处:
调用:
SELECT 函数名(实参列表) [FROM 表名]
分类:
单行函数:CONCAT
,LENGTH
,IFNULL
分组函数
功能:做统计使用,又称为统计函数,聚合函数,组函数
1 单行函数
1.1 字符函数
函数名 功能 LENGTH
获取参数值的字节 个数,不同字符集不同长度 CONCAT
拼接字符串 UPPER
转大写 LOWER
转小写 SUBSTR
或SUBSTRING
截取字符串,SQL中索引从1开始的字符 长度 INSTR
返回子串第一次出现的索引,如果找不到返回0 TRIM
去掉前后空格或指定字符 LPAD
用指定的字符实现左填充指定长度,长度不够时从左侧开始截取 RPAD
用指定的字符实现右填充指定长度,长度不够时从左侧开始截取 REPLACE
替换指定子串
# 1 LENGTH(str)
SELECT LENGTH('AAA');
# 2 CONCAT(str1,str2,...)
SELECT CONCAT('A','B','C');
# 3 UPPER(str) & LOWER(str)
SELECT UPPER('aAa'),LOWER('BbB');
# 4 SUBSTR(str,pos) & SUBSTR(str,pos,len)
SELECT SUBSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ',10),SUBSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ',1,6);
# 5 INSTR(str,substr)
SELECT INSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ','NO'),INSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ','CF');
# 6 TRIM([remstr FROM] str)
SELECT TRIM('A' FROM 'ABCDEFGHIJKLMNOPQRSTUVWXYZAAAA'),LENGTH(TRIM(' AAAAA '));
# 7 LPAD(str,len,padstr) & RPAD(str,len,padstr)
SELECT LPAD('AAA',10,'B'),RPAD('BBB',10,'C');
SELECT LPAD('ABC',2,'B'),RPAD('BCD',1,'C');
# 8 `REPLACE`(str,from_str,to_str)
SELECT REPLACE('AAABBBCCCDDDEEEAAABBBCCCDDDEEE','AAA','FFF');
1.2 数学函数
函数名 功能 ROUND
四舍五入 CEIL
向上取整,返回>=该参数的最小整数 FLOOR
向下取整 TRUNCATE
截断 MOD
取余(计算公式:a-a/b*b)
# 1 ROUND(X) & ROUND(X,D)(保留小数点后D位)
SELECT ROUND(1.69),ROUND(1.123456,4);
# 2 CEIL(X)
SELECT CEIL(1.6589),CEIL(-1.2562);
# 3 FLOOR(X)
SELECT FLOOR(1.5695),FLOOR(-1.5862);
# 4 `TRUNCATE`(X,D)(只针对与小数)
SELECT TRUNCATE(1.23654,2),TRUNCATE(123456798,5);
# 5 `MOD`(N,M)
SELECT MOD(10,3),MOD(-10,3),MOD(10,-3),MOD(-10,-3);
1.3 日期函数
函数名 功能 NOW
返回当前系统日期 + 时间 CURDATE
返回当前系统日期 CURTIME
返回当前系统时间 YEAR
,MONTH
,DAY
,HOUR
,MINUTE
,SECOND
返回年月日时分秒 MONTHNAME
返回月份的英文名称 STR_TO_DATE
将日期格式的字符转换成指定格式的日期 DATE_FORMAT
将日期转换成字符
格式符 功能 %Y
四位年份 %y
两位年份 %m
月份(01,02,03…) %c
月份(1,2,3,4…) %d
日(01,02…) %H
小时(24小时制) %h
小时(12小时制) %i
分钟(00,01,02…) %s
秒(00,01,02…)
# 1 NOW() & CURDATE() & CURTIME()
SELECT NOW(),CURDATE(),CURTIME();
# 2 YEAR(date),MONTH(date),DAY(date),HOUR(time),MINUTE(time),SECOND(time)
SELECT YEAR(NOW()),MONTH(NOW()),DAY(NOW()),HOUR(NOW()),MINUTE(NOW()),SECOND(NOW());
# 3 MONTHNAME(date)
SELECT MONTHNAME(NOW());
# 4 STR_TO_DATE(str,format)
SELECT emp.first_name,emp.hiredate FROM employees emp WHERE emp.hiredate = STR_TO_DATE('4-3 1992','%m-%d %Y');
# 5 DATE_FORMAT(date,format)
SELECT DATE_FORMAT(NOW(),'%m-%d %Y');
# 6 DATEDIFF(expr1,expr2),查询员工表中的最大入职时间和最小入职时间相差的天数
SELECT DATEDIFF(MAX(emp.hiredate),MIN(emp.hiredate)) FROM employees emp;
2 流程控制函数
# 1 IF(expr1,expr2,expr3)
SELECT
emp.first_name,
emp.commission_pct,
IF (
emp.commission_pct IS NULL,
'NO COM',
'HAVE COM'
) AS "COM"
FROM
employees emp;
/* 2
查询员工的工资,要求
部门号=30,显示的工资为1.1倍
部门号=40,显示的工资为1.2倍
部门号=50,显示的工资为1.3倍
其他部门,显示的工资为原工资
*/
SELECT
emp.employee_id,
emp.department_id,
emp.salary,
CASE emp.department_id
WHEN '30' THEN
emp.salary * 1.1
WHEN '40' THEN
emp.salary * 1.2
WHEN '50' THEN
emp.salary * 1.3
ELSE
emp.salary
END
FROM
employees emp;
/* 3
查询员工的工资的情况
如果工资>20000,显示A级别
如果工资>15000,显示B级别
如果工资>10000,显示C级别
否则,显示D级别
*/
SELECT emp.employee_id,emp.salary,
CASE
WHEN emp.salary > 20000 THEN 'A'
WHEN emp.salary > 15000 THEN 'B'
WHEN emp.salary > 10000 THEN 'C'
ELSE 'D'
END AS "LEVEL"
FROM employees emp;
3 分组函数
功能:用作统计使用,又称为聚合函数、统计函数或组函数 分类
SUM
求和AVG
平均值MAX
最大值MIN
最小值COUNT
计算不为NULL的个数 参数支持的类型
SUM
,AVG
一般用于处理数值型MAX
,MIN
,COUNT
可以处理任何类型 所有分组函数均忽略 NULL 值 可以和DISTINCT
搭配使用,实现去重(AVG
和 COUNT
)
# 1 SUM(expr),AVG([DISTINCT] expr),MAX(expr),MIN(expr),COUNT(DISTINCT expr,[expr...])
SELECT
SUM(emp.salary),
AVG(emp.salary),
MIN(emp.salary),
MAX(emp.salary),
COUNT(emp.salary)
FROM
employees emp;
# 2 忽略NULL值测试
SELECT
AVG(emp.commission_pct),
SUM(emp.commission_pct) / 107,
SUM(emp.commission_pct) / 35
FROM
employees emp;
SELECT
COUNT(emp.commission_pct)
FROM
employees emp;
# 3 DISTINCT搭配使用
SELECT
COUNT(DISTINCT emp.commission_pct),
AVG(DISTINCT emp.commission_pct)
FROM
employees emp;
3.1 COUNT()函数的使用
# 4 COUNT函数的使用
SELECT COUNT(emp.first_name) FROM employees emp;
SELECT COUNT(*) FROM employees emp;
SELECT COUNT(1) FROM employees emp;
以上三种是常用的统计行数的方法 性能:
MYISAM
存储引擎下:COUNT(*)
的效率高,因为引擎自身有计数器,直接返回结果INNODB
存储引擎下:COUNT(*)
和COUNT(1)
效率差不多,但是都比COUNT(常量)
高 和分组函数一同查询的字段要求是GROUP BY
后的字段