DQL 4_常见函数

学的像个弟弟

已于 2022-07-23 17:43:03 修改

阅读量150

点赞数

分类专栏： mysql--SQLyog 文章标签： sql 数据库 mysql database

于 2022-07-22 16:41:32 首次发布

本文链接：https://blog.csdn.net/weixin_45151033/article/details/125935380

版权

mysql--SQLyog 专栏收录该内容

18 篇文章 0 订阅

订阅专栏

4_常见函数

/*
好处：

1.隐藏实现细节
2.提高代码重用性
调用： select 函数名(实参列表) 【from 表】函数参数 需要 用到表中字段;

特点：

1.叫什么(函数名)
2.干什么（功能）

分类：

1.单行函数
	字符函数
	数学函数
	日期函数
	其他函数
	流程控制函数
	如 concat length，ifnull
2.分组函数 统计函数 聚合函数 组函数
	功能：统计使用

1.单行函数

一.字符函数

1.length 获取参数值的字节个数

SELECT LENGTH('JOHN');
SELECT LENGTH('张三丰hahaha'); #一个汉字 三个字节 一个英文字母 一个字节 3*3+6=15
SHOW VARIABLES LIKE '%char%'; #utf8 一个汉字 三个字节 ，jdk 一个汉字 两个字节

2.concat 拼接字符串

SELECT CONCAT(LAST_NAME,'_',FIRST_NAME)FROM employees;

3.upper lower

SELECT UPPER('john'); #变大写
SELECT LOWER('SDA');  #变小写

案例：姓大写名小写拼接

SELECT CONCAT(UPPER(LAST_NAME),'_',LOWER(FIRST_NAME))FROM employees; # 函数可以嵌套函数

4.substr、substring 截取字符下标从1开始

SELECT SUBSTR('李莫愁爱上路展元',6); #返回 从6开始的所有字符 路展元

SELECT SUBSTR('李莫愁爱上路展元',4,2)#返回 爱上, 截取 指定索引处(4) 指定字符长度(2) 的 字符

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

SELECT CONCAT(UPPER(SUBSTR(LAST_NAME,1,1)),'_',LOWER(SUBSTR(LAST_NAME,2))) FROM EMPLOYEES;

5. instr 返回字串第一次出现的索引，如果找不到返回 0

SELECT INSTR('杨不悔爱上了刘霞哥','刘霞哥') #返回 7
SELECT INSTR('杨不刘霞哥悔爱上了刘霞哥','刘霞哥') #返回 3
SELECT INSTR('杨不悔爱上了刘霞哥','刘哥') #返回 0

6.trim 祛前后空格

SELECT LENGTH(TRIM('     海洋    ')); #返回6 而不是15
SELECT TRIM('a' FROM 'aaaaaaaaaaaaa海aaaaaaaaa洋aaaaaaaa') 名; #区分大小写 返回 海aaaaaaaaa洋

7. lpad 用指定的字符实现左填充指定长度

SELECT LPAD('苏苏',10,'*') #********苏苏

8. rpad 用指定的字符实现右填充指定长度

SELECT RPAD('苏苏',10,'-') #苏苏--------

9.replace 替换

SELECT REPLACE('阿珍爱上了阿强','阿强','你爹') # 阿珍爱上了你爹

SELECT REPLACE('阿强阿强阿强阿强阿强阿强阿珍爱上了阿强','阿强','你爹') # 阿强 全部替换为 你爹

二.数学函数

1.round 四舍五入

SELECT ROUND(1.45)# 返回1
SELECT ROUND(1.55)# 返回2
SELECT ROUND(1.456,2)# 返回1.46   2 表示 保留小数点后2位

2.ceil 向上取整，返回>=该参数的最小整数

SELECT CEIL(1.003) #返回2
SELECT CEIL(2.56) #返回3
SELECT CEIL(-1.2) #返回 -1

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

SELECT FLOOR(1.22)#返回1
SELECT FLOOR(-1.22)#返回 -2

4.truncate 截断

SELECT TRUNCATE(1.2563,2)#返回1.25

5. mod 取余

mod(a,b) : a-a/b*b (a/b取整) a为正数结果为正数 a为负数结果为负数 a是被除数

SELECT MOD(10,-3) #10-10/(-3)*(-3)=10-(-3)*(-3)=10-9=1
SELECT MOD(10,3) #10-10/3*3=10-3*3=1
SELECT MOD(-10,-3) #-10-(-10)/(-3)*(-3)=-10-3*(-3)=-10-(-9)=-10+9=-1
SELECT MOD(-10,3) #-10-(-10)/(3)*(3)=-10-(-3)*3=-10-(-9)=-1

三.日期函数

1.now 返回当前系统日期和时间

SELECT NOW();

2.curdate 返回当前系统日期没有时间

SELECT CURDATE();

3.curtime 返回当前时间，没有日期

SELECT CURTIME();

4.可以获取指定的部分、年、月、日、小时、分钟、秒

SELECT YEAR(NOW()) #年数 返回2022
SELECT YEAR('2023-12-3') #返回2023
SELECT YEAR(HIREDATE) 年 FROM employees;

SELECT MONTH(NOW()) #月数 返回7
SELECT MONTHNAME(NOW()) #返回 英文月数 July

SELECT DAY(NOW()) #天数 返回22
SELECT DAYNAME(NOW()) #返回 英文天数 Friday

SELECT HOUR(NOW()) #小时 返回9
SELECT MINUTE(NOW()) #分钟 
SELECT SECOND(NOW()) #秒

5.str_to_date 将字符通过指定的格式转化为日期

SELECT STR_TO_DATE('7-2022-8','%c-%Y-%d') AS OUT_PUT;#输出 2022-07-08

例：查询入职日期为 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');
SELECT *FROM EMPLOYEES WHERE hiredate=STR_TO_DATE('4.3 1992','%c.%d %Y');

6.date_format 日期转化为字符

SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') #22年07月22日

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

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

7.两个日期差多少天

SELECT DATEDIFF('2022-10-1','2022-7-22')# 10月1与7月22日相差71天

四.其他函数

SELECT VERSION() #mysql版本
SELECT DATABASE() #当前数据库
SELECT USER() #当前用户
SELECT MD5('王诗雨');#返回该字符密码形式

五.流程控制函数

1.if函数 if else 效果 if（,,_）如果第一个参数为真，那么输出第二个参数，否则输出第三个参数

SELECT IF(5>3,'大于','小于')# 输出大于

案例：输出有奖金的人与无奖金的人并备注

SELECT LAST_NAME,commission_pct,IF(commission_pct IS NULL,'没奖金 白给','有奖金 海底捞！')
FROM employees;

2.case函数控制结构

/*
语法1：

case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
...
else 要显示的值n或语句n;
end

/
/
案例1

语法1：查询员工的工资，要求
部门号为30 ，显示工资为原工资1.1倍
40	             1.2
50                   1.3
其他部门                    原工资
按升序排列

案例1

SELECT `department_id`,salary 原工资, 
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
ORDER BY `department_id` ASC;

/*
语法2：类似于多重if

case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
...
else 要显示的值n或语句n
end

/
/
案例2：查询员工的工资的情况

	如果工资>20000 显示A级别
	如果工资>15000 ,显示B级别
	如果工资>10000 显示C级别
	否则，显示D级别

案例2

SELECT salary,
CASE 
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 级别
FROM employees 
ORDER BY salary DESC;

六、练习

例1：将员工的姓名按首字母排序，并写出姓名长度

SELECT SUBSTR(LAST_NAME,1,1)AS 首字符,LAST_NAME,LENGTH(LAST_NAME) AS 姓名长度
FROM EMPLOYEES ORDER BY 首字符 ASC;

2.分组函数

/*
功能：用作统计使用，又称聚合函数统计函数组函数
分类：sum 求和，avg 平均值， max 最大值， min 最小值， count 计算个数
特点：

1	sum avg  处理 数值型
max min count 可以处理 任何类型

2.	是否忽略null值
以上 分组函数 都忽略 null值

3. 	可以和 distinct 搭配实现 去重 运算

4. 	count 函数 单独介绍
一般使用count(*) 用作 统计行数

5.	和分组函数一同查询的字段 要求是 group by 后的 字段

一、简单使用

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;
SELECT SUM(SALARY),TRUNCATE(AVG(SALARY),2),MAX(SALARY),MIN(SALARY),COUNT(SALARY) FROM EMPLOYEES;

二、参数支持哪些类型

SELECT SUM(LAST_NAME),AVG(LASE_NAME) FROM employees;#对名字 求和，求平均，没意义
SELECT SUM(HIREDATE),AVG(HIREDATE) FROM employees;#对日期 求和，求平均，没意义
SELECT MAX(LAST_NAME),MIN(LAST_NAME)FROM employees;#返回 Zlotkey Abel
SELECT MAX(hiredate),MIN(HIREDATE)FROM employees;#返回 2016-03-03 00:00:00 1992-04-03 00:00:00
SELECT COUNT(commission_pct)FROM employees; #返回36（返回不为空的值的个数） 因为有的人的奖金率为空
SELECT COUNT(last_NAME)FROM employees; #返回108 108个人

三、是否忽略null值？

#输出结果 ：7.95 0.220833 0.220833 0.073611
SELECT SUM(commission_pct),AVG(commission_pct),SUM(commission_pct)/36,SUM(commission_pct)/108 FROM EMPLOYEES;
#表示：忽略null值 对 sum 来说 如果没有忽略null 那么 null+任意数值应该为0 而不是7.95 因此 表示忽略null值
#对 avg 来说 如果没有忽略null值 那应该是总数除以108 得到0.073611 而答案是 0.220833 表示 除以的人数为36 因此 计算忽略 null值

SELECT MAX(commission_pct),MIN(commission_pct)FROM EMPLOYEES;

输出结果：0.40 0.10 没有null值表示忽略了 null值

SELECT COUNT(commission_pct)FROM employees;

输出结果36 表示忽略了 null 值（count本身就是计算非空值）

四、和 distinct 搭配使用

SELECT SUM(DISTINCT salary),SUM(SALARY)FROM EMPLOYEES;#去重后总计397900.00 没有去重总计698400.00
SELECT COUNT(DISTINCT salary),COUNT(SALARY)FROM EMPLOYEES;#去重后总计57，没有去重 总计108

五、 count函数的详细介绍

SELECT COUNT(SALARY) FROM employees;

SELECT COUNT(*) FROM employees; #一行中 只要有一个字段 的 值 不为0 count值就加1

SELECT COUNT(1)FROM employees; #相当于 加了一列1 行数为表的行数 ，返回这列1有多少行

六、和分组函数一同查询的字段有限制

SELECT AVG(salary),EMPLOYEE_ID FROM EMPLOYEES;#输出6466.666667和100 这个100没有意义 因为avg函数平均的值是一行，而EMPLOYEE_ID字段行数就是它在表中的行数，但为了统一，所以用了一行。

七、练习

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

SELECT DATEDIFF('2022-10-1','2022-7-22')# 10月1与7月22日相差71天
SELECT DATEDIFF(MAX(HIREDATE),MIN(HIREDATE)) AS "difference" FROM EMPLOYEES;#8735天

2.例2 查询部门编号为90的员工个数

SELECT COUNT(*) FROM employees WHERE DEPARTMENT_ID = 90;