DQL语言学习进阶四（常见函数）
一、概念
类似于Java的方法，将一组逻辑语句封装在方法体中，对外暴漏方法名
二、优点
1、隐藏了实现细节
2、提高代码的复用性
调用：select 函数名（实参列表）【from 表】；
三、特点
1、叫什么（函数名）
2、干什么（函数功能）
四、分类
1、单行函数
2、分组函数
功能：做统计使用，又称为统计函数、聚合函数、组函数
3、常见函数
一、单行函数
（一）字符函数
length：获取字节长度
concat：连接
substr：截取子串
instr：获取子串第一次出现的索引
trim：去重
upper：变大写
lower：变小写
lpad：左填充
rpad：右填充
replace：替换
（二）数学函数
round：四舍五入
ceil：向上取整
floor：向下取整
truncate：截断
mod：取模
rand：获取随机数，返回0-12间的小数
（三）日期函数
now：返回当前日期+时间
curdate：返回当前日期
curtime：返回当前时间
year：返回年
month：返回月
monthname：以英文形式返回月
day：天
hour：小时
minute：分钟
second：秒
str_to_date：将字符转换成日期
date_format：将日期转换成字符
datediff：返回两个日期相差的天数
（四）其他函数
version：当前数据库服务器的版本
database：当前打开的数据库
user：当前用户
password（‘字符’）：返回该字符的密码形式
md5（‘字符’）：返回该字符的md5加密形式
（五）流程控制函数
1、
if（条件表达式，表达式1，表达式2）：如果条件表达式成立，返回表达式1，否则返回表达式2
2、case情况一：
case 变量或表达式或字段 when 常量1 then 常量1 when 常量2 then 常量2 …… else 值n end
case情况二：
case when 条件1 then 常量1 when 条件2 then 常量2 …… else 值n end
（一）字符函数
1、length：获取参数值的字节个数
2、concat：拼接字符
3、upper、lower：upper变大写，lower变小写
例：将姓变大写，名变小写，然后拼接
select concat（upper（last_name), lower(first_name)）姓名 from employee；
4、substr、substring
注：索引从1开始
截取从指定索引处后面所有字符
select substr（'我和我的祖国都很棒'，7） out_put;显示都很棒
截取从指定索引处指定字符长度的字符
select substr（'我和我的祖国都很棒'，1，3）out_put； 显示我和我
5、instr：返回子串第一次出现的索引，如果找不到，返回0
select instr（'我和我的祖国都很棒'，'都超棒'）as out_put;
6、trim：去空格
select length（trim（' 我 '））as out_put;
7、lpad：用指定的字符实现左填充指定长度
select lpad('请求权'，2，'*')as out_put;
8、rpad：用指定的字符实现右填充指定长度
select rpad('请求权'，12，'ab')as out_put;
9、replace：替换
select replace('我和我的祖国都很棒'，'祖国'，'地球')as out_put;
（二）数学函数
1、round：四舍五入
select round(-1，55);select round(1，567，2);
2、ceil：向上取整，返回>=该参数的最小整数
select ceil(1，00);
3、floor：向下取整，返回<=该参数的最大整数
select floor(-9，99);
4、truncate：截断
select truncate(1，65，1);
5、mod：取余
select mod(10，3);
（三）日期函数
1、now：返回当前系统日期+时间
select now();
2、curdate：返回当前系统日期，不包含时间
select curdate();
3、curtime：返回当前时间，不包含日期
select curtime();
获取指定的部分，年、月、日、小时、分钟、秒
selectyear( now()) 年；
str_to_date：将日期格式的字符转换成指定格式的日期
str_to_date ('9-13-1999','%m-%d-%Y');
date_format：将日期转换成字符
date_format ('2018/6/6','%Y年%m月%d日');
（四）其他函数
select version();select database();select user();
（五）流程控制函数
1、if函数：if else 的效果
2、case函数的使用一：switch case的效果
语法：
case 要判断的字段或表达式 when 常量：then 要显示的值1或语句1;when 常量：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.1when 40 then salary *1.2when 50 then salary *1.3else salary end as 新工资 from employee;
3、case函数的使用二：类似于多重if
语法：
case when 条件1 then 要显示的值1或语句1;when 条件2 then 要显示的值1或语句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 employee;
例1：显示系统时间（注：日期+时间）
select now();
例2：查询员工号、姓名、工资、工资提高百分之二十后的结果
select employee_id，last_name, salary, salary*1.2"new salary"from employee;
例3、将员工的姓名按首字母排序，并写出姓名的长度
select length(last_name) 长度,substr(last_name,1,1) 首字符,last_name from employee orderby 首字符;
例4、做一个查询，产生下面的结果
select concat (last_name,'earns',salary,'monthly but wants', salary*3)as'dream salary'from employee where salary=24000;
5、使用case-when，按照下面的条件
select job_id as job case job_id when 'AD_PRES' then 'A'when 'ST_MAN' then 'B'when 'IT_PRCC' then 'C'end as grade from employee where job_id ='AD_PRES';
二、分组函数
（一）功能
用作统计使用，又称为聚合函数或统计函数或组函数
（二）分类
sum求和，avg平均值，max最大值，min最小值，count计算个数
（三）特点
1、语法：
select max(字段)from 表名;
2、支持的类型：
sum、avg一般用于处理数值型
max、min、count可以处理任何类型
3、以上分组函数都忽略null值
4、都可以和distinct搭配使用，实现去重的设计
select max(distinct 字段)from 表名;
5、count函数的单独介绍
一般使用count（*）用作统计函数
count（字段）：统计该字段非空值的个数
count（*）：统计结果集的行数
count（1）：统计结果集的行数
效率上：
MyISAM存储引擎：count（*）效率最高
InnoDB存储引擎：count（*）和count（1）效率>count（字段）
6、和分组函数一同查询的字段要求是group by后出现的字段
1、简单的使用
select sum(salary)from employee;select avg(salary)from employee;select max(salary)from employee;select min(salary)from employee;selectcount(salary)from employee;select sum(salary) 和, avg(salary) 平均, max(salary) 最高,min(salary)最低,count(salary) 个数 from employee;
2、参数支持哪些类型
select sum(last_name), avg(last_name)from employee;select sum(hiredate), avg(hiredate)from employee;select max(last_name), min(last_name)from employee;select max(hiredate), min(hiredate)from employee;selectcount(commission_pct)from employee;selectcount(last_name)from employee;
3、是否忽略null
select sum(commission_pct), avg(commission_pct)，sum(commission_pct)/35, sum(commission_pct)/107from employee;
4、和distinct搭配
select sum(distinct salary)from employee；
5、count函数详细介绍
selectcount(salary)from employee;selectcount(*)from employee;selectcount(1)from employee;
效率：
myisan存储引擎下，count（*）的效率高
innodb存储引擎下，count（*）和count（1）的效率差不多，比count（字段）效率要高一些
6、和分组函数一同查询的字段有限制
select avg(salary)，employee_id from employee;
例1、查询公司员工工资的最大值、最小值、平均值、总和
select max(salary) mx_sal, min(salary) min_sal, avg(salary) avg_sal, sum(salary) sm_sal from employee;
例2、查询员工表中的最大入职时间和最小入职时间的相差天数（difference）
select datediff(max(hiredate) min(hiredate)) difference from employee;
例3、查询部门编号为90的员工个数
selectcount(*)from employee where department_id=90;
