MySQL基础 （SQLyog） 学习笔记整理——DQL常见函数、分组函数

MySQL基础 （SQLyog） 学习笔记整理——DQL常见函数、分组函数

DQL语言（数据查询语言 data query language)

基础查询
条件查询
排序查询
常见函数
分组函数
连接查询
子查询
分页查询
Union查询

---

---

本节学习内容：DQL

常见函数
分组函数

---

一、常见函数

概念：类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名 好处：1、隐藏了实现细节；2、提高代码的重用性；
调用：select 函数名（实参列表） from 表； 特点：
1.叫什么（函数名）
2.干什么（函数功能） 分类； 1.单行函数 如 concat 、 length 、ifnull 等 2.分组函数 功能：做统计处理，又称为统计函数、聚合函数、组函数

---

1.单行函数

字符函数：length获取字节长度、concat连接、substr截取子串、instr获取子串第一次出现的索引、trim去前后空格、upper变大写、lower变小写、lpad左填充、rpad右填充、replace替换
数学函数：round四舍五入 、ceil向上取整 、floor向下取整、truncate截断、mod取模、rand获取随机数，返回0-1之间的小数
日期函数：now当前日期+时间、curdate当前日期不包含时间、curtime返回当前时间、year返回年、month返回月、monthname以英文形式返回月、day返回日、hour小时、minute分钟、second秒、str_to_date将字符转换成日期、date_format将日期转换成字符、datediff返回两个日期相差的天数
其他函数【补充】：version当前数据库服务器的版本、database当前打开的数据库、user当前用户、password('字符')返回该字符的密码形式，自动加密，同MD5
流程控制函数【补充】:
1.if（条件表达式 ，表达式1，表达式2）：如果条件表达式成立，返回表达式1，否则返回表达式2
2.Case
(1)情况1：
Case 变量或表达式或字段
When 常量1 then 值1
When 常量2 then 值2
…
Else 值n
End;
 （2）情况2：
Case 
When 常量1 then 值1
When 常量2 then 值2
…
Else 值n
End;

2.分组函数

2.1字符函数

#（1）length 获取参数值的字节个数

SELECT LENGTH('john');
SELECT LENGTH('张三丰hahaha');    #一个汉字三个字节

SHOW VARIABLES LIKE '%char%'

#（2）concat 拼接字符串

SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees;

#（3）upper \ lower

SELECT UPPER('john');    #大写
SELECT LOWER('john');    #小写
#示例：将姓变大写，名变小写，然后拼接
SELECT CONCAT(UPPER(last_name),LOWER(first_name))姓名 FROM employees;

#（4）substr、substring 截取字符

注意：索引从1开始
SELECT SUBSTR('李莫愁爱上了陆展元',7) out_put;    #截取从指定索引处后面所有字符
SELECT SUBSTR('李莫愁爱上了陆展元',1,3) out_put;  #截取从指定索引处指定字符长度的字符

#案例：姓名中首字符大写，其他字符小写，然后用_拼接显示

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

#（5）instr（str1,str2)返回str2在str1中首次出现的索引 (其实索引) 如果找不到，返回0

SELECT INSTR('杨不悔爱上了殷六侠','殷六侠') AS out_put

#（6）trim 去首尾

SELECT TRIM('      张翠山      ') AS out_put;
SELECT TRIM('a' FROM 'aaaaaaaa张aaaaaa翠山aaaaaaa') AS out_put;

#（7）Lpad 用指定字符实现左填充指定长度

SELECT LPAD('殷素素',10,'*') out_put;    #用*左填充到10个字符     (一个汉字算3个字节但是1个字符）

#（8）RPAD 用指定字符实现右填充指定长度

SELECT RPAD('殷素素',12,'ab') out_put; 

#（9）replace 替换

SELECT REPLACE('张无忌爱上了周芷若','周芷若','赵敏') AS out_put;
SELECT REPLACE('周芷若周芷若周芷若周芷若张无忌爱上了周芷若','周芷若','赵敏') AS out_put;   #都改了

2.2 数学函数

#round 四舍五入

SELECT ROUND(1.65);
SELECT ROUND(1.567,2);  #小数点后保留两位

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

SELECT CEIL(1.52);

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

SELECT FLOOR(1.99);

#truncate 截断（后面是什么都不要了）

SELECT TRUNCATE(1.65,1);

#mod 取余

/*
mod (a,b) : a-a/b*b
mod (-10,-3) : -10-(-10)/(-3)*(-3)=-1
*/
SELECT MOD(10,-3);  #符号：被除数正为正，被除数负为负
SELECT 10%3;

2.3日期函数

#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 :将日期格式的字符转换成指定格式的日期
str_to_date ('9-13-1999','%m-%d-%y)
date_format :将日期转换成字符
*/
#str_to_date
SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put;

#例：查询入职日期为1992-4-3的员工信息

SELECT * FROM employees WHERE hiredate = '1992-4-3';
SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');

#date_format:将日期转换成字符

SELECT DATE_FORMAT(NOW(),'%y年%m月%d日')AS out_put;

#例：查询有奖金的员工名和入职日期（xx月/xx日/xx年）

SELECT last_name,DATE_FORMAT (hiredate,'%m月/%d日/%y年') FROM employees 
WHERE commission_pct IS NOT NULL;

2.4 其他函数

SELECT VERSION();
SELECT DATABASE();
SELECT USER();

2.5 流程控制函数

（1）If函数： if else的效果

SELECT IF(10>5,'大','小');     #命题true返回第二个结果expr2,命题FALSE返回第三个结果expr3；
SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金，哈哈','有奖金，嘿嘿') FROM employees;

（2）case 函数：等于或者区间判断
使用一：swith case 的效果

/* Java：switch(变量或表达式){ case 常量1：语句1；break； … default：语句n;break;}
MySQL： 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 AS 新工资
FROM employees;

（3）case 函数的使用二：类似于 多重if（判断区间）

/* java:if(条件1）{ 语句1； }else if(条件2）{ 语句2； } … else{ 语句n; }

MYSQL:区间判断 case when 条件1 then 要显示的值1（或语句1；） when 条件2 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.sum、avg一般用于处理数值型
max、min、count 可以处理任何类型
2.以上分组函数都忽略null值
3.可以和distinct搭配实现去重的运算
4.count 函数的单独介绍
一般使用count（*）统计行数
5.和分组函数一同查询的字段要求是group by 后的字段
*/

1.简单的使用

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;

SELECT SUM(salary) 和,AVG(salary) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数
FROM employees;

2.参数支持哪些类型

SELECT SUM(last_name),AVG(last_name) FROM emloyees;   X
SELECT MAX(last_name),MIN(last_name) FROM employees;
SELECT MAX(hiredate),MIN(hiredate) FROM employees;
SELECT COUNT(commission_pct) FROM employees;   #非空的35个
SELECT COUNT(last_name) FROM employees;    #107个

3.是否忽略Null

SELECT SUM(commission_pct),AVG(commission_pct) FROM employees;  

4.和distinct搭配

SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;

5.count函数的详细介绍

SELECT COUNT(*) FROM employees;    #查询表里的总行数，107行
SELECT COUNT(1) FROM employees;    #统计有多少行的1=统计有多少行

6.和分组函数一同查询的字段有限制

SELECT AVG(salary),employee_id FROM employees;   #结果没有意义

案例：查询员工表中的最大入职时间和最小入职时间的相差天数（difference）

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

---