概念: 类似java中的方法,将一组逻辑语句封装在方法体中,对外暴露方法名
好处:1. 隐藏了函数的实现细节,2.提高了代码的重用性
调用:select 函数名(实参列表) [from 表];
特点 ①叫什么(函数名)
②干什么(函数的功能)
分类:
功能:做统计使用,又称为统计函数,聚合函数,组函数
length : 获取参数值的字节个数(中文utf-8是三个字节,gbk是两个字节)
concat : 拼接字符串
upper,lower 大小写切换
substr | substring 切割字符 注意mysql中的索引是从1开始
#截取从指定索引处后面的所有字符 select substr(“zhangbushuai” from 1 for 5); select substr(“zhangbushuai”,6);
instr 返回子串第一次出现的索引,如果找不到就返回0
select instr(“zhangbushuai”,“bu”) as ‘index’;
trim 去除前后空格
select length(trim(" zyd ")) as name; 声明去除指定字符 select length(trim(“a” from “aaaaaaaaazydaaaaaaaa”)) as name;
lpad rpad 用指定的字符实现左/右填充
select lpad(“zyd”,5,"*") as name; 结果 : **zyd 其中5表示总长度如果初始字符超过了总长度,会被自动截断
replace 替换原先字符串中的字符,为指定的字符串
select replace(“zydhahaha”,“ha”,“shuai”) as name;
round 四舍五入
select round(-1.55); //-2
负数可以看做先取整,四舍五入,然后加上符号
ceil 向上取整,返回>= 该参数的最小整数 -1
select ceil(-1.02);
floor 向下取整,返回<= 该参数的的最大整数-2
select floor(-1.02);
truncate 截断
select truncate(1.6999,2);
保留小数点后两位 1.69
mod 取余
select mod(-10,3);
结果 -1
余数的正负性和被除数有关,
now() 返回当前系统的日期和时间
select now();
curdate 返回当前系统的日期,不包含时间
select curdate();
curtime 返回当前时间,不包含日期
select curtime();
获取指定的部分,年,月,日,小时,分钟,秒
select year(now()) 年; select year(“1998-1-1”) 年
select year(hiredate) 年 from employees;
select month(now()) 月; select monthname(now()) 月; 返回的是英文
str_to_date 将字符通过指定的格式转换成日期
select * from employees where hiredate = str_to_date(“4-3 1992”,"%c-%d %y");
有的时候日期格式不是我们想要的,转换
date_format 将日期转换成字符
select date_format(now(),’%y年%m月%d日’) as 日期;
案例: 查询有奖金的员工名和入职日期(xx月/xx日 xx年)
select last_name,date_format(hiredate, ‘%m月/%d日 %y年’) 入职时间 from employees where commission_pct is not null;
查看版本
select version();
查看当前数据库
select database();
查看当前使用用户
select user();
if函数
相当于三元运算符 参数1 条件表达式,ture执行的表达式,false执行的表达式
案例 : 查询有 奖金的员工
select last_name,commission_pct, if(commission_pct is null,“没有奖金”,“有奖金”) 备注
case: 和java中的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倍
其他部门,显示的工资为原工资
select salary 原始工资, department_id, case department_id when 30 then salary1.1 when 40 then salary1.2 when 50 then salary*1.3 else salary end as 新工资
case 函数的使用二,类似于,多if
case when 条件1 then 要显示的值1或语句2 when 条件2 then 要显示的值2或语句2 else 要显示的值n或语句n
案例:查询员工的工资情况
如果工资>20000,显示a级别
如果工资>15000,显示b级别
如果工资>10000,显示c级别
否则,显示d级别
select salary, when salary>20000 then “a” when salary>15000 then ‘b’ when salary>20000 then ‘c’ else ‘0’ end as 工资级别
功能:用作统计使用,又称为聚合函数或统计函数或组函数
sum 求和 avg 平均值,max 最大值, min 最小值, count 计算个数
简单的使用
select sum(salary) from employees; select avg(salary) from employees; select min(salary) from employees; select max(salary) from employees; select count(salary) from employees;
参数支持哪些类型
sum,avg 处理数值型 max,min,count 可以处理任何的类型 以上的分组函数都忽略了null值
和distinct搭配使用
select sum(distinct salary), sum(salary)
count函数
查询表的总列数
select count(*) from employees; select count(1) from employees;
在mysql的存储引擎中
myisam存储引擎下,count(*)的效率高 ,因为程序内部有一个计数器 innodb存储引擎下啊,count(*)和count(1)差不多,比count(字段高)
注意:和分组函数一同查询的字段,要求group by 后的字段
案例: 查询员工表中的最大入职时间和最小入职时间的相差天数(difference),用到日期相差的函数
select datediff(max(hiredate),min(hiredate)) diff
语法:
select 分组函数,列(要求出现在 group by的后面) from 表 [where 筛选条件] group by 分组的列表 [order by 子句]
查询每个工种的最高工资
select max(salary), job_id group by job_id
查询每个位置上的部门个数
select count(*), location_id from departments group by location_id;
添加筛选条件的案例
案例1:查询邮箱中包含a字符的,每个部门的平均工资
select avg(salary), department_id where email like ‘%a%’ group by department_id
案例2:查询有奖金的,每个领导手下员工的最高工资
select max(salary),manager_id where commission_pct is not null group by manager_id
添加复杂分组查询的筛选
案例1:查询哪个部门的员工个数>2
当原始表中没有字段,分步操作
①查询每个部门的员工个数
select count(*) department_id group by department_id;
②根据①的结果进行筛选,查询哪个部门的员工个数>2
select count(), department_id having count()>2;
使用having,where只能对于表中的字段进行选择
案例2: 查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
①查询每个工种有奖金的员工的最高工资
group by job_id;
②根据①结果继续筛选,最高工资>12000
select max(salary)>12000, job_id having max(salary)>12000;
案例3 查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个,以及其最低工资
分割①查询每个领导手下的员工固定最低工资
select min(salary),manager_id
②添加筛选条件,编号>102
where manager_id>102
③添加筛选条件,最低工资大于5000
having min(salary)>5000;
特点:
分组查询中的筛选条件分为两类
数据源
位置
关键字
分组前筛选
原始表
group by 的前面
where
分组后筛选
分组后的结果集
group by 的后面
having
分组函数做的条件肯定是放在having子句中****
**性能方面:**能用分组前筛选的尽量使用分组前筛选
group by 后面支持语句使用别名以及表达式
案例: 按员工姓名的长度分组,查询每一组的员工个数,筛选员工个数>5的有哪些
①查询每个长度的员工个数
count(*),length(last_name) len_name from employees group by length(last_name);
②添加筛选条件
count(*) c,length(last_name) len_name group by length(last_name) having c>5;
按多个字段进行分组
查询每个部门每个工种的员工的平均工资
select avg(salary),department_id,job_id group by job_id,department_id;
特点2: group by 子句支持单个字段分组,多个字段分组(多个字段之间用逗号隔开,没有顺序要求),表达式或函数(用的较少)
特点3 使用order by 子句,放在最后面