DQL---常用函数

DQL—常用函数

4. 常用函数

• 调用方法： select 函数名(实参列表) [from 表名];

• 特点：注意函数名与函数功能，与普通语言无太大区别

• 分类：

  1. 单行函数:
     字符函数: length,concat,upper\lower,substr\instr,trim,lpad\rpad,replace
     数学函数: round,ceil,floor,truncate,mod
     日期函数: now,curtime,curdate,str_to_date,date_format
     其他函数: version,database,user
     流程控制函数: if,case(两种)

  2. 分组函数：用来统计一组值的结果,又称聚合函数/统计函数/组函数
     求和函数sum，平均值函数avg, 最大最小值max/min, 计算个数函数count
     特点：

     • sum,avg 函数参数为数值型。max,min,count函数参数为数值型和字符型

     • 以上函数再计算时忽略null值

     • 都可以与distinct搭配使用

     • count()/count(1)可以用来统计表格行数，一般使用count()

     • 和分组函数一同查询的字段有限制，一般为group-by的语句

------------------------------------单行函数----------------------------------------
## 字符函数
# length 获取参数值字节数
SELECT LENGTH('john');
SELECT LENGTH('杰兰特geraint');#utf8下汉字3个字节，字母一个字节；
# concat 拼接字符串
SELECT CONCAT(last_name,'_',first_name) AS 姓名 FROM employees;
# upper\lower 大小写转换
SELECT UPPER('john');
SELECT CONCAT(UPPER(first_name),'_',LOWER(last_name)) AS 姓名 FROM employees;
# substr=substring 截取字符串
###字符索引从1开始
SELECT SUBSTR('geraint',7) AS out_put;
SELECT SUBSTR('geraint',1,3) AS out_put;
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) AS 姓名 FROM employees;
# instr 返回字串第一次出现的索引
SELECT INSTR('geraint','ain') AS out_put;
# trim 去除前后空格
SELECT LENGTH(TRIM('    geraint       ')) AS out_put;
SELECT TRIM('a' FROM 'aaaaageraintaaaaa') AS out_put;
# lpad、rpad 左、右填充指定长度
SELECT LPAD('geraint',10,'*') AS out_put;
SELECT RPAD('geraint',10,'*') AS out_put;
# replace 替换
SELECT REPLACE ('gergergeraint','ger','xin');

## 数学函数
# round四舍五入
SELECT ROUND(-1.55);
SELECT ROUND(1.5657,2);
# ceil 向上取整
SELECT CEIL(1.002);
# floor 向下取整
SELECT FLOOR(9.87);
# truncate 截断
SELECT TRUNCATE(1.6997,2);
# mod 取余
/*
mod(a,b): a-a/b*b
*/
SELECT MOD(10,3);

## 日期函数
# now 返回当前系统日期时间
SELECT NOW();
# curdate 返回当前日期
SELECT CURDATE();
# curtime 返回当前时间
SELECT CURTIME();
# 获取指定部分(year,mouth,day,hour,minute,second)
SELECT MONTH(NOW()) AS 月份;
SELECT MONTHNAME(NOW()) AS 月份;
# str_to_date 将字符转换为日期
SELECT STR_TO_DATE('1997-3-8','%Y-%c-%d') AS out_put;
SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');
# date_format 将日期转换为字符
SELECT DATE_FORMAT(NOW(),'%Y 年 %m 月 %d 日') AS out_put;

## 其他函数
SELECT VERSION();
SELECT DATABASE();
SELECT USER();

## 流程控制函数
# if 函数
SELECT IF(10<5,'小','大') AS out_put;
SELECT last_name,commission_pct,IF(commission_pct IS NULL, '没奖金','有奖金') AS 备注 FROM employees;
# case函数一：switch case效果
/*
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 新工资
FROM employees;

# case 函数使用二： 类似于多种if 
/*
case 
when 条件1 then 要显示的值1
when 条件2 then 要显示的值2
when 条件3 then 要显示的值3
...
else 要显示的值n
end
*/
SELECT salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END
AS 工资级别
FROM employees;

------------------------------------分组函数----------------------------------------
# 简单使用
SELECT SUM(salary) FROM employees;
SELECT SUM(salary),AVG(salary),MAX(salary),MIN(salary),COUNT(salary) FROM employees;
SELECT ROUND(AVG(salary),2) FROM employees;

# 参数支持类型
SELECT SUM(last_name),AVG(last_name) FROM employees;
SELECT MAX(last_name),COUNT(last_name) FROM employees;

# 是否忽略null
SELECT SUM(commission_pct),AVG(commission_pct) FROM employees;#忽略null
SELECT MAX(commission_pct),MIN(commission_pct) FROM employees;
SELECT COUNT(commission_pct) FROM employees;

# 与distinct搭配
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;

# count函数详细介绍
SELECT COUNT(salary) FROM employees;
SELECT COUNT(*) FROM employees; #统计行数.效率最高
SELECT COUNT(1) FROM employees; 

# 和分组函数一同查询的字段有限制，一般为group-by的语句
SELECT AVG(salary),job_id FROM employees;# 无意义