MySQL学习记录 - 04- 常见函数学习

最新推荐文章于 2022-03-25 14:57:51 发布

大贝祺的海

最新推荐文章于 2022-03-25 14:57:51 发布

阅读量115

点赞数

分类专栏： MYSQL 文章标签： mysql

本文链接：https://blog.csdn.net/Becky_Jia/article/details/108100133

版权

MYSQL 专栏收录该内容

28 篇文章 1 订阅

订阅专栏

根据尚硅谷的视频教程学习MySQL，学习记录-04-常见函数学习。
所有操作开始之前，建议先打开对应的库：

USE 库名;
USE employees;

=>基本概念
将一组逻辑语句封装在方法体中，对外暴露方法名
好处：隐藏了实现的细节，提高代码的重用性
特点：函数名是什么 + 实现什么功能

SELECT 函数名(实参列表) FROM 表名

=>分类

1 单行函数：如 concat, length, isfull等，包含：

字符函数: length, concat, substr, instr, trim, upper, lower, ipad, rpad, replace
数学函数: round, ceil, floor, truncate, mod
日期函数: now, curdate, curtime, year, month, str_to_date, date_format
其他函数: version, user
流程控制函数: if else, case when

2 分组函数：做统计使用，或称为统计函数、聚合函数：

sum, avg, max, min, count, mean

=>具体分类

一、字符函数

length：获取参数值的字节个数

SELECT LENGTH('John')；
SELECT LENGTH('张三丰hahaha');---一个汉字占2/3个字节

concat：拼接字符串

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

upper , lower：改变大小写

SELECT UPPER('john');
SELECT LOWER('john');

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

substr, substring：截取字符串

注意，索引从1开始，不是0
1个参数：截取从索引处开始到后面所有字符
2个参数：截取从索引处指定字符长度的字符

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;

instr：返回子串在原字符串中第一处出现的索引，找不到返回0

SELECT INSTR('杨不悔爱上了殷六侠','殷六侠') AS out_put; --> 7
SELECT INSTR('杨不悔爱上了殷六侠','殷八侠') AS out_put; --> 0

trim：去除字符前后的指定信息，默认为空格

SELECT TRIM('      张翠山    ') as result; -->张翠山
SELECT TRIM('a' FROM 'aaaaaaa张aa翠aaaa山aaaaaaaaaaaaa') as result; -->张aa翠aaaa山

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

SELECT LPAD('殷素素',5,'*'); --> ***殷素素，左侧填充3个*，实现整体长度为5
SELECT LPAD('殷素素',2,'*'); --> 殷素，原字符长度已经超过指定数据，需要截断

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

SELECT RPAD('殷素素',12,'ab'); --> 殷素素ababababa,右侧填充ab实现指定长度

replace：将所有指定字符替换为目标字符

SELECT REPLACE('张无忌爱上了周芷若','周芷若','赵敏') as result; -->将周芷若替换为赵敏

二、数学函数

round：四舍五入

SELECT ROUND(1.65);   
SELECT ROUND(1.658,2);

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

SELECT CEIL(1.52);
SELECT CEIL(1.00); ---->1
SELECT CEIL(-1.25); ---->-1

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

SELECT  FLOOR(1.52); ----1
SELECT  FLOOR(-1.52);  ---->-2

truncate 截断，截断要求小数点位数后的内容

SELECT  TRUNCATE(1.6999,1); ----> 1.6

mod 取余

计算思路：mod(a,b):   a-a/b*b
SELECT  MOD(10,3); ---> 1
SELECT 10%3;
SELECT  MOD(-10,-3);--->-1
SELECT  MOD(10,-3); --->1

三、日期函数

now 返回当前系统日期+时间

SELECT NOW()

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

SELECT CURDATE();

curtime 返回当前时间，不包含日期

SELECT CURTIME();

year，month, hour 获取指定的部分，年、月、日、小时、分钟、秒

SELECT YEAR(NOW()) 年;
SELECT MONTH(NOW()) 月;
SELECT YEAR('1998-8-29');
SELECT MONTHNAME('1998-08-23') 月;

str_to_date: 将日期格式的字符转化成日期格式

SELECT STR_TO_DATE('9-13-1999','%m-%d-%Y') date;  -- 1999-09-13
SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') as out_put;--1998-03-02

%Y --> 四位的年份：1998
%y --> 两位的年份：98
%m -->月份（01，02，…11,12)
%c -->月份（1，2，…11,12)
%d --> 日（01，02，…11,12)
%H --> 小时（24小时制）
%h -->小时（12小时制）
%i --> 分钟（00，01，02，…59)
%s --> 秒(00,01,02,…59)

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

date_format 将日期转换成字符

SELECT DATE_FORMAT(NOW(),'%y年-%m月-%d日') as out_put; ---20年-08月-22日
SELECT last_name, DATE_FORMAT(hiredate,'%m月/%d日 %y年') 入职日期 FROM employees;

四、其他函数

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

五、流程控制函数

if 函数： if else 的效果

SELECT IF(10 >5,'大','小');
SELECT last_name, commission_pct, 
IF (commission_pct IS NULL, '没奖金 呵呵','有奖金 哈哈')  备注
FROM employees;

case 函数（控制结构） - 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 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 new_salary
FROM employees;

case 函数（控制结构） - 类似于多重if，判断区间

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;

六、单行函数小节练习

显示系统时间（注：日期+时间）

SELECT NOW();

查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）

SELECT employee_id, last_name, salary, salary*1.2 as "new salary" FROM employees;

将员工姓名按照首字母排序，并写出名字的长度（Length）

SELECT last_name, SUBSTR(last_name, 1,1) 首字符,
LENGTH(last_name) as len FROM employees
ORDER BY 首字符;

七、分组函数

简单使用

SELECT SUM(salary） FROM employees;
SELECT  sum(salary) 和, AVG(salary) 平均, COUNT(salary) 计数 FROM employees;

参数支持类型：
SUM , AVG 只处理数值型数据
MAX, MIN, COUNT 可以处理所有类型的数据

SELECT SUM(last_name) , AVG(last_name) FROM employees;
SELECT MAX(last_name) , MIN(last_name) FROM employees;
SELECT MAX(hiredate) , MIN(hiredate) FROM employees;
SELECT COUNT(hiredate) FROM employees;
SELECT COUNT(commission_pct) FROM employees;

以上分组函数都忽略NULL值

SELECT SUM(commission_pct), AVG(commission_pct), SUM(commission_pct)/35, SUM(commission_pct)/107
FROM employees;

和DISTINCT 搭配

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

COUNT函数详细介绍

SELECT COUNT(*) FROM employees;  -->统计行数，只要一条记录中有一个数据不为null，就会被统计
SELECT COUNT(1) FROM employees; -->依旧统计行数，在前面加入一列常量值，然后统计个数

效率：
MYISAM存储引擎下，COUNT() 的效率最高
INNODB存储引擎下，COUNT() 和COUNT(1)的效率一致，但是高于COUNT(字段)的效率

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

SELECT AVG(salary), employee_id FROM employees; -->虽然有结果，但是对应平均薪资的员工号毫无意义

八、分组函数小节练习

查询员工工资的最大值，最小值，平均值，总和

SELECT max(salary), min(salary), avg(salary), sum(salary) FROM employees;

查询员工表中的最大入职时间和最小入职时间之间的相差天数（DIFFERENCE)

SELECT max(hiredate), min(hiredate), DATEDIFF(max(hiredate),min(hiredate))  DIFFERENCE from employees;
--使用DATEDIFF函数，用来计算日期差

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

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