04常见函数
常见函数
概念：类似于Java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处：1、隐藏了实现细节 2、提高代码的重用性
调用：
SELECT 函数名(实参列表) FROM 表；
特点：
1、叫什么（函数名）
2、干什么（函数功能）
分类
单行函数
如concat、length、ifnull等
字符函数
一、ength：用于获取参数值的字节个数
SELECT LENGTH('john');
SELECT LENGTH('张三丰hahaha')//15。一个字母一个字节，一个汉字三个字节
二、concat：用于拼接字符串
SELECT CONCAT(last_name,',',first_name) 姓名 FROM employees;
三、upper(变大写)、lower(变小写)
SELECT UPPER('john');//JOHN
四、substr、substring
注意索引从1开始
截取从指定索引处后面所有字符
SELECT SUBSTR('李莫愁爱上了陆展元',7) out_put;//陆展元
截取从指定索引处**指定字符长度**的字符
SELECT SUBSTR('李莫愁爱上了陆展元',1，3) out_put;//李莫愁
该例子中3为要截取字符的长度
五、instr
返回子串第一次出现的索引，如果找不到返回0.
SELECT INSTR('杨不悔爱上了殷六侠','殷六侠') AS out_put
FROM employees;
六、trim
去除前后的所指定的字符
SELECT TRIM('a', FROM 'aaaaa张aaa翠山aaaaaaaaaaaaaaa') AS out_put;//张aaa翠山
七、lpad
用指定的字符实现左填充指定长度
SELECT LPAD('殷素素',10，'*') AS out_put;
八、rpad
用指定的字符是想右填充指定长度
SELECT RPAD ('殷素素',12，'ab') AS out_put;//殷素素ababababa
九、replace 替换
SELECT REPLACE ('周芷若周芷若周芷若张无忌在上周芷若'，‘周芷若’，‘赵敏’)AS out_put;//周芷若全改为赵敏
数学函数
～round 四舍五入
SELECT ROUND（1.45）；//1SELECT ROUND（1.567,2）；//1.57
～ceil 向上取整:返回>=该参数的最小整数
SELECT CEIL(1.32);//2SELECT CEIL(1.00);//1
～floor向下取整：返回<=该参数的最大整数
SELECT FLOOR(-9.99)//-10
～truncate 截断
要小数点后几位
SELECT TRUNCATE(1.659999,1);//1.6
～mod取余
SELECT MOD(10,-3);//1
日期函数
~now 返回当前系统日期+时间
~curdate返回当前系统日期，不包含时间
~curtime返回当前时间，不包含日期
~可以获得指定的部分，年，月，日，小时，分钟，秒
SELECT YEAR(NOW());
SELECT MONTH(NOW());
~str_to_date 将字符通过指定的格式转换成日期
SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d')AS out_put;
~date_format:将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS out_put;
其他函数
SELECT VERSION(); 当前数据库服务器的版本
SELECT DATABASE();当前打开的数据库
SELECT USER();当前用户
password('字符');返回该字符的密码形式
md5('字符');返回该字符的md5加密形式
流程控制函数
一、IFSELECT IF(10<5,'大'，'小')；//小
二、case（类似Java中的switch语句）放值后面不用分号，放语句才需要。
case 要判断的字段或表达式
when 常量一 then 要显示的值1或语句1；
when 常量二 then 要显示的值2或语句2；
···
else 要显示的值n或者语句n；
end //没其他情况可以省略end
Java中
if(条件一){
语句一
}else if(条件二){
语句二
}
···
else{
语句n；
}
mysql中
case
when 条件一 then 要显示的值1 或语句1；
when 条件二 then 要显示的值2 或语句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 employees;
分组函数
功能：做统计使用，又称为统计函数、聚合函数、组函数
分类：
sum 求和、avg 平均值、max 最大值、min最小值、count计算非空个数
1、简单的使用
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;
特点：
1、sum、avg一般用于处理数值型
max、min、count可以处理任何类型
2、是否忽略null值
以上分组函数都忽略null值
3、可以和distinct搭配 去重
SELECT SUM(DISTINCT salary) FROM employees;//去重求和
SELECT COUNT(DISTINCT salary) FROM employees;//计算工资的种类
分组查询
语法：
select 分组函数，列（要求出现在group by的后面）
from 表
[where 筛选条件]
group by 分组的列表
[order by 子句]
注意 ： 查询列表必须特殊，要求是分组函数和group by后出现的字段
特点 一、分组查询中的筛选条件分为两类
数据源 位置 关键字
分组前筛选 原始表 group by前 where
分组后筛选 分组后的结果集 group by后 having
1、分组函数的条件肯定是放在having子句中
2、能用分组前筛选的，就优先考虑使用分组前筛选
二、group by 子句支持单个字段分组，多个字段分组（多个字段之间用逗号隔开没有顺序要求），表达式或函数
三、也可以添加排序（排序放在整个分组查询的最后
简单的分组查询
例子：查询每个工种的最高工资
SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id;
例子：查询每个位置上的部门个数
SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;
添加筛选条件
例子：查询邮件中包含a字符的，每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
WHERE email like'%a%'
GROUP BY department_id;
例子：查询有奖金的每个领导手下员工的最高工资
SELECT MAX(salary),manager_id
FROM employees
WHERE commission_pct is not null
GROUP BY manager_id;
添加复杂筛选条件
例子：查询哪个部门的员工个数>2
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;
例子：查询每个工种有奖金的员工的最高工资大于12000的工种编号和最高工资
SELECT MAX(salary),job_id
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY job_id
HAVING MAX(salary)>12000;
例子：查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，以及其最低工资是多少
SELECT min(salary),manager_id
FROM employees
WHERE manager_id>102
ORDER BY manager_id
HAVING min(salary)>5000;
按表达式或函数分组
例子：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些
SELECT COUNT(*),LENGTH(last_name)
FROM employees
ORDER BY LENGTH(last_name)
HAVING COUNT(*)>5;
按多个字段分组
例子：查询每个部门每个工种的员工平均工资
SELECT AVG(salary),department_id,job_id
FROM employees
ORDER BY department_id,job_id;
添加排序
例子：查询每个部门每个工种的员工平均工资，并且按平均工资的高低显示
SELECT AVG(salary),department_id,job_id
FROM employees
ORDER BY department_id,job_id
ORDER BY AVG(salary) DESC;
浙公网安备 33010602011771号