【mysql 函数语句 03】

#进阶4:常见函数

概念:类似于Java的方法,将一组逻辑语句封装到方法体中,对外暴露方法名
调用:select 函数名(实参列表) 【from 表】;
特点:1、叫什么(函数名)2、干什么(函数功能)

分类:1、单行函数
    如:concat  length  ifnull等
      2、分组函数(做统计使用),又称为统计函数、聚合函数、组函数

---------单行函数---------------
#一、字符函数:concat,length,upper,lower
 #案例1、将姓变大写,名变小写,然后拼接

SELECT CONCAT(UPPER(first_name),'_',LOWER(last_name)) FROM employees;

#1)sbustr  substring 截取的意思,注意:索引从1开始,后面那个3指的是字符长度

 SELECT SUBSTR('杨过与小龙女打秋千',4,3) out_put;

#案例:姓名中首字符大写,其他字符小写然后_拼接,显示出来

SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) out_put FROM employees;

#2)instr  返回字串第一次出现的索引,如果找不到返回0

SELECT INSTR('郭靖爱上了黄蓉','黄蓉') AS out_put;

#3)trim 去除首尾内容

SELECT LENGTH(TRIM('    张无忌    ')) AS out_put;
 SELECT TRIM('a' FROM 'aaaaaaa张aaaa无aaaaa忌aaaaaaa') AS out_put; #张aaaa无aaaaa忌 只会去除首尾的a

#4)lpad 用指定的字符实现左填充指定长度

SELECT LPAD('殷素素',2,'*') out_put; #-->殷素

#5)rpad 用指定的字符实现右填充指定长度

SELECT RPAD('殷素素',5,'##') out_put; #-->殷素素##
 

#6)replace  替换

SELECT REPLACE('大侠爱上了小龙女小龙女小龙女','小龙女','周芷若') out_put;

二、数学函数
 #round  四舍五入

 SELECT ROUND(-1.65); #-2
 SELECT ROUND(1.5678,2); #1.57  取两位小数

#ceil 向上取整,返回>=该参数的最小整数

 SELECT CEIL(-1.02);

#floor 向下取整,返回<=该参数的最大整数

 SELECT FLOOR(9.99);

#truncate 截断

SELECT    TRUNCATE(1.65,1);

 #mod取余

 SELECT MOD(10,-3)

三、日期函数

#now  返回当前日期

 SELECT NOW();

#curdate  返回当前系统日期,不包含时间

 SELECT CURDATE();

#curtime 返回当前系统时间,不包含日期

 SELECT CURTIME();

 #获取指定的部分,年、月、日、小时、分、秒

 SELECT YEAR(NOW()); 
 SELECT YEAR(hiredate) FROM employees;
 SELECT YEAR('1997-12-12');
 SELECT MONTH(NOW());

#str_to_date  将日期格式的字符转换成指定格式的日期

 SELECT STR_TO_DATE('09-08-2020','%d-%c-%y');

#date_format 将日期转换成字符

 #案例:查询有奖金的员工名和入职日期(xx月/xx日 xx年)
 SELECT last_name,DATE_FORMAT(hiredate,'%c/%d %y') 入职日期 FROM employees;

#datediff:返回两个日期相差的天数

四、流程控制语句函数

#if函数,if(表达书,表达式1,表达式2)  如果条件表达式成立,返回表达式1,否则返回表达式2

 SELECT IF(10<5,'','');
 SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金,呜呜','有奖金,哈哈') 备注 FROM employees;

#case函数的使用一:switch case的效果
 java中switch语法
switch(变量或表达式){
    case 常量1:语句1;
    break;
    case 常量2:语句2;
    break;
    ...
    default:语句break;
}
mysql中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 新工资
 FROM employees;

#case函数的使用二:类似于  多重if
java中的if语句
if(条件1){
    语句1;
}else if(条件2){
    语句2;
}else if(条件3){
    语句3;
}else{
    语句n
}

mysql中
case
when 条件1 then 要显示的值1或语句1;
when 条件2 then 要显示的值1或语句1;
...
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 工资等级
FROM employees;

 ---------分组函数-----------

分类:sum求和,avg平均值,max最大值,min最小值,count计算个数
#简单使用

SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT COUNT(salary) FROM employees;

SELECT SUM(salary) 和,AVG(salary) 平均值,MAX(salary) 最大值, MIN(salary) 最小值,COUNT(salary) 个数 FROM employees;

#特点
1、SUM AVG 一般用于处理数值型
2、MAX,MIN,COUNT可以处理任何类型
3、以上函数都忽略NULL值
4、可以和DISTINCT搭配使用
5、一般使用COUNT(*)用作统计行数
6、和分组函数一同查询的字段要求是GROUP BY后的字段

#和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; # 常用,原理如果有多列的时候,只要多列内对应的每一行有一个部位null都能计数
SELECT COUNT(1) FROM employees; # 原理:在列的最左侧添加一列,并且列内的每一行都为1,select count(5)表明列内的每一行都为5

#查询员工表中的最大入职时间和最小入职时间的相差天数(DIFFRENCE)

SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) DIFFRENCE FROM employees;

#进阶5:分组查询

语法:
   select 分组函数,列(要求出现在group by的后面)
   from 表
   【where 筛选条件】
   group by 分组列表
   【order by 子句】
   
 注意:查询列表必须特殊,要求是分组函数和group by后出现的字段

特点:
 1、分组查询中的筛选条件分为两种
        数据源        位置            关键字
 分组前筛选    原始表        group by子句的前面    where
 分组后的筛选    分组后的结果集    group by子句的后面    having
 
 1) 分组函数做条件肯定是放在having子句中
 2)能用分组前筛选的,就有限考虑使用分组前筛选

2、group by子句支持单个字段分组,多个字段分组(多个字段之间用逗号隔开没有顺序要求),表达式或函数

3、也可以排序,放在整个查询的最后面
#案例1:查询每个工种的最高工资

SELECT MAX(salary) 最高工资,job_id 工种 FROM employees GROUP BY job_id;

#案例2:查询每个位置上的部门个数

SELECT  COUNT(*) ,location_id FROM departments GROUP BY location_id;

#添加筛选条件
#案例1、查询邮箱中包含a字符,每个部门的平均工资

SELECT AVG(salary),department_id FROM employees WHERE email LIKE '%a%' GROUP BY department_id;

#案例2、查询有奖金的每个领导手下员工的最高工资

SELECT MAX(salary),`manager_id` FROM `employees` WHERE commission_pct IS NOT NULL GROUP BY `manager_id`;

#添加复杂的筛选条件
#案例1:查询那个部门的员工个数>2

分步1:查询每个部门的员工个数
SELECT COUNT(*) 个数 ,department_id FROM employees GROUP BY department_id;
分步2:根据1的结果进行筛选,查询那个部门的员工个数大于2
SELECT COUNT(*) 个数 ,department_id FROM employees GROUP BY department_id  HAVING COUNT(*)>2 ;  #HAVING关键字

#案例2:查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

分步1:每个工种有奖金的员工的最高工资。分步2:根据1的结果筛选,最高工资>12000
SELECT MAX(salary),job_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY job_id HAVING  MAX(salary)>12000;

#案例3:查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个,以及其最低工资

SELECT MIN(salary),manager_id FROM employees WHERE manager_id>102 GROUP BY manager_id HAVING MIN(salary)>5000;

 #按表达式或函数分组
#案例:按员工姓名的长度分组,查询每一组的员工个数,帅选员工个数>5的有哪些

SELECT COUNT(*) 个数 FROM employees GROUP BY LENGTH(last_name) HAVING 个数>5;

#按多个字段分组+添加排序
#查询每个部门每个工种的员工的平均工资,按平均工资的高低分组

SELECT
  AVG(salary)
FROM
  employees
  WHERE department_id IS NOT NULL
GROUP BY department_id,
  job_id
HAVING  AVG(salary)>10000
ORDER BY AVG(salary) DESC ;

 

posted @ 2021-03-15 18:03  尘封~~  阅读(69)  评论(0编辑  收藏  举报