聚合函数——MySQL

聚合函数

常见函数导图

就相当于C语言的函数
好处；

1. 隐藏了实现细节 2. 提高了代码的重用性

分类：

1. 单行函数：const、length、ifnull等
2. 分组函数：
   • 字符函数
   • 日期函数
   • 数组函数
   • 其他函数
   • 流程控制函数

1 字符函数

1. length：获取参数值的字节个数
   select length('join'); 结果为4
   select length('数值为678'); 结果为12，因为一个字母占1个字节，一个汉字占3个字节

2. concat：拼接字符串
   select concat(last_name,'_',firts_name) from employees;
   会把所用last_name和firts_name拼接成last_name_firts_name的形式

3. upper：改大写、lower：改小写
   select upper('home'); 会把home变成HOME

   将姓变大写，名变成小写，然后拼接在一起，并该别名为姓名
   select concat ( upper ( last_name ) , lower ( firts_name ) ) as 姓名 from employees;

4. substr：索引
   注意：索引的从1开始
   select substr ( '一二三四五六七八'，5) as out_put;
   会输出五六七八，会截取从第5开始往后的所有字符。

   select substr('一二三四五六七八'，2,3) as out_put;
   会输出二三四，第1个字符代表截取的位置，第2个字符代表截取的个数。（包含截取位置字符）

案例：姓名中首字母大写(检索第一个然后转大写)，其他字符小写(检索从第二个开始然后转小写)，然后用_拼接显示出来。
select concat(upper(substr(last_name,1,1),'_',lower(substr(last_name,2))) as out_put

5. instr：返回子串，在大的串中的第一次出现的位置，如果没有显示0
   select instr('总在屏幕上打出','屏幕') as out_put;
   会返回2

6. trim：去掉前面和末尾指定的所有字符。
   select trim(' 路人甲 ');
   会删掉路人甲前面和后面的空格。

   去指定的字符
   select trim('a' from 'aaaa路aa人aa甲aaaa')
   会返回：aa人aa甲,只会去掉首尾的a，而不会去掉中间的。

7. lpad：左填充，用指定的字符实现左填充，是填充后的字符达到指定长度。
   select lpad('奔波霸',10,'*') as out_put;
   会输出：*******奔波霸

   select lpad('奔波霸',2,'*') as out_put;
   会输出：奔波，在最后面截断一个，使字符串达到指定长度。

8. rpad：右填充

9. replace： 替换，如果有重复的就都进行替换。
   select replace('路人甲,宋兵乙,炮灰丙','宋兵乙','土匪丁') as out_out;
   会输出路人甲,土匪丁,炮灰丙：用土匪丁 替换 宋兵乙

2.数字函数

1. round 四舍五入，保留结果的小数点位数

   1. 四舍五入
      select round(-1.55);
      结果为 -2，无论是正数还是负数，都可以把负号先去掉，然后进行四舍五入，再加上正负号。

   2. 保留小数点位数
      select round(1.567,2);
      结果为1.56,第2个参数2就是保留小数点后两位小数。

2. ceil 向上取整(返回>=该参数的最小整数)
   ceil(1.002);
   结果依然为2
   ceil(1.00);
   结果就为1。

3. floor 向下取整(返回<=该参数的最小整数)
   floor(1.002);
   结果依然为1
   floor(-9.7);
   结果就为-10。

4. mod 取余
   select mod(10,3);
   结果为1。10 % 3 = 1
   select mod(10,-3);
   结果为1。10 % -3 = 1
   select mod(-10,3);
   结果为1。-10 % 3 = -1

5. truncate 清空表，截断表
   只能用于表，删除表中的全部数据

6. rand 获取随机数
   返回[0-1)之间的随机数

3.日期函数

1. now 返回当前系统日期+时间
   select now();

2. curdate 返回当前系统日期,不包含时间
   select curate();

3. curtime 返回当前时间,不包含日期
   select curtime();

4. 可以获取指定部分时间,年、月、日、小时、分钟、秒
   select year(now()); 年
   select year('1998-1-1') 获得字符串中的年份1998

   获取数据库中某一个项中的年份。
   select year(hiredata) as 年 from employees;

   select mouth(now()) as 月 中文的月
   select mouthname(now()) as 英文月 英文的月

5. str_to_data:将日期格式的字符转换成指定格式的日期
   select str_to_date('1999-3-2','%Y+%c+%d') as out_put;
   会输出显示：1999+03+02

   格式符	功能
   %Y	4位的年份
   %y	两位的年份
   %m	月份(01,02…11,12)
   %c	月份(1,2…11,12)
   %d	日（01-31）
   %H	小时(24小时制)
   %h	小时(12小时制)
   %i	分钟（01-60）
   %s	秒（01-60）

   查询入职日期为1999-4-3的员工信息
   select * from employees where hiredate = '1999-4-3'

6. date_format:将日期转换成字符
   date_format('2018/6/6','%Y年%m月%d日');
select date_format(now(),'%Y年%m月%d日') as out_put;

查询有奖金的员工名和入职时间(xx月/xx日xx年)
select laxt_name,date_format(hiredate,'%m月%d日%Y年') as 入职时间
form employees
where commission_pct

4.其他函数

1. version() 查看版本号
2. database() 查看当前所在那个数据库
3. user() 查看当前用户
4. password(‘字符’) 返回输入字符的密码形式
5. md5(‘字符’) 返回输入字符的md5加密形式

5.流程控制函数

1. if函数: if else的效果相同
   select if(10>5,'大','小') 返回大

2. 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倍。
   case department_id
when 30 then salary*1.1
when 40 then salary*1.2
when 50 then salary*1.3
else salary
end;