软件设计之MySQL(3)-CSDN博客

本文链接：https://blog.csdn.net/thm19990903/article/details/141219050

软件设计之MySQL(3)

此篇应在JavaSE之后进行学习:
路线图推荐：
【Java学习路线-极速版】【Java架构师技术图谱】
Navicat可以在软件管家下载
使用navicat连接mysql数据库创建数据库、表、转储sql文件，导入sql数据

学习内容：

函数概述
数值函数
字符串函数
日期和时间函数
流程控制函数
加密与解密函数
MySQL信息函数
聚合函数

1、函数概述

1、DBMS(数据库管理系统)函数之间的差异性很大，很多是不通用的
2、MySQL提供的内置函数从实现的功能角度可以分为数值函数、字符串函数、日期和时间函数、流程控制函数、加密与解密函数、获取MySQL信息函数、聚合函数等。这里，我将这些丰富的内置函数再分为两类：单行函数、聚合函数（或分组函数）。

单行函数

单行函数：接收参数返回一个结果，只对一行进行变换，每行返回一个结果，其中参数可以是一列或者一个值

2、数值函数

基本函数

**> 这里是引用**

SELECT ABS(-10),SIGN(-23),PI(),CEIL(32.32),CEILING(-43.23),FLOOR(32.32),
FLOOR(-43.23),MOD(12,5)
#10	-1	3.141593 33	-43	32	-44	2
SELECT RAND(),RAND(10),RAND(10)
#0.7992655269794776	0.6570515219653505	0.6570515219653505 
SELECT ROUND(123.4),ROUND(123.456,1),ROUND(123,-1)
#123 123.5(保留1位小数) 120
SELECT TRUNCATE(12.55,1)
# 12.5（截断）

三角函数

角度与弧度互换函数：
1、RADIANS(x)：将角度化为弧度
2、DEGREES(x)：将弧度化为角度
3、圆周长：2πr，一段r长度对应1弧度，所以整个圆的弧度是2π

在这里插入图片描述

指数和对数

在这里插入图片描述

进制间的转换

在这里插入图片描述

#1111从2进制转换为8进制
SELECT CONV(1111,2,8)

3、字符串函数

在这里插入图片描述

SELECT ASCII('abc')
# 97
SELECT CHAR_LENGTH('hello'),CHAR_LENGTH('我'),LENGTH('hello'),LENGTH('我')
#5 1 5 3
#CONCAT
SELECT CONCAT(emp.last_name,' worded for',mgr.last_name)
FROM employees emp JOIN employees mgr
WHERE emp.manager_id = mgr.employee_id;
#CONCAT_WS  HELLO-WORLD
SELECT CONCAT_WS('-','HELLO','WORLD') 
#INSERT 字符串索引从1开始，按索引替换
SELECT INSERT('helloword',2,3,'aaa') #haaaoword
#REPLACE 将选定字符串替换
SELECT REPLACE('hello','ll','mew')#hemewo
#UPPER,LOWER 改变大小写
SELECT UPPER('a'),LOWER('A')

在这里插入图片描述

#LPAD:右对齐效果，若不到10个字符，则左补*(字符数10和*都可以改)
SELECT employee_id,last_name,LPAD(salary,10,' ')
FROM employees;
#RPAD:左对齐效果，若不到10个字符，则右补*(字符数10和*都可以改)
SELECT employee_id,last_name,RPAD(salary,10,' ')
FROM employees;
#TRIM(s1 FROM s)去除首尾指定s1
SELECT TRIM('o'FROM'ooheollo')#heoll
#REPEAT返回重复n次的结果
SELECT REPEAT('a',4)#aaaa
#STRCMP比字符串ASCII码大小，前者大返回1，后者大返回-1
SELECT STRCMP('ABC','ABA')#1
#SUBSTR返回从位置2开始返回的3个字符
SELECT SUBSTR('hello',2,3)#ell
#LOCATE返回指定字符串在另一字符串首次出现的位置
SELECT LOCATE('l','hello')#3

4、日期和时间函数

获取日期、时间

在这里插入图片描述

日期与时间戳的转换

在这里插入图片描述

#UNIX_TIMESTAMP返回的是毫秒数
#FROM_UNIXTIME将输入的毫秒数转化为UNIX时间戳形式(2024-08-15 17:01:18)
SELECT UNIX_TIMESTAMP(),FROM_UNIXTIME(1723712478)
SELECT UNIX_TIMESTAMP('2022-10-10 11:11:11')

获取月份、星期、星期数、天数等函数

在这里插入图片描述

日期的操作函数

EXTRACT(type FROM date):返回指定日期中特定的部分，type指定返回的值

在这里插入图片描述

#获取秒数
SELECT EXTRACT(SECOND FROM NOW())

时间和秒钟转换的函数

在这里插入图片描述

SELECT TIME_TO_SEC(CURTIME()),TIME_TO_SEC('12:12:12')
SELECT SEC_TO_TIME(1000)#00:16:40

计算日期和时间的函数

在指定的日期时间进行加减操作

在这里插入图片描述

#指定日期减一年
SELECT DATE_ADD(NOW(),INTERVAL -1 YEAR)

在这里插入图片描述

日期的格式化与解析

格式化：日期转化为字符串
解析：字符串转化为日期

#格式化：
SELECT DATE_FORMAT(CURRENT_DATE,'%Y:%c:%e'),
TIME_FORMAT(CURRENT_TIME,'%k:%i:%s')
FROM DUAL;
#解析：
SELECT STR_TO_DATE('2014-04-22 15:47:06','%Y-%m-%d %H:%i:%s')
FROM DUAL;

5、流程控制函数

流程处理函数可以根据不同的条件，执行不同的处理流程，可以在SQL语句中实现不同的条件选择。MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数。

#IF 对高低工资进行分类
SELECT last_name,salary,IF(salary>=6000,'高工资','低工资')
FROM employees;
#IF 如果commission_pct字段为NULL，输出0
SELECT last_name,commission_pct,IFNULL(commission_pct,0)
FROM employees;
#CASE WHEN THEN END 类似ifelse
SELECT last_name,salary,CASE 
	WHEN salary>=15000 THEN'有钱'
	WHEN salary>=10000 THEN'还行'
	ELSE '穷人'END 'details'
FROM employees;
#类似switch case
SELECT employee_id,last_name,department_id,salary, 
CASE department_id
WHEN department_id=10 THEN salary*1.1
WHEN department_id=20 THEN salary*1.2
ELSE salary*1.4 END 'details'
FROM employees;

6、加密与解密函数

在这里插入图片描述

#MD5加密方式与SHA加密方式
SELECT MD5('123456'),SHA('123456')
#8.0 ENCODE、DECODE、PASSWORD被弃用

7、MySQL信息函数

在这里插入图片描述

8、聚合函数

输入是一组数据的集合，输出是单个值

常见聚合函数

AVG、SUM、MAX、MIN

分别对应平均值、总和、最大值、最小值
AVG、SUM只适用于数值类型的字段
MAX、MIN适用于数值类型、字符串类型、日期时间类型的字段

SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary)
FROM   employees

COUNT

作用：计算指定字段在查询结构中出现的个数(不包含空值NULL)
计算表中有多少条记录的方法：
1、COUNT(*)
2、COUNT(1)

SELECT COUNT(commission_pct)
FROM   employees

GROUP BY

聚合字段：COUNT、MAX等
SELECT中出现的非聚合字段必须出现在GROUP BY中，反之不需满足
GROUP BY声明在FROM后、WHERE后、ORDER BY前、LIMIT前
当使用ROLLUP时，不能同时使用ORDER BY进行结果排序

#查询各部门、各工种的平均工资
SELECT department_id,job_id,AVG(salary)
FROM   employees
GROUP BY department_id,job_id;
#不同点在于，会多一条记录计算总体平均值(因为此处是AVG)
SELECT department_id,AVG(salary)
FROM   employees
GROUP BY department_id WITH ROLLUP；

HAVING的使用

如果过滤条件中使用了聚合函数，则必须使用HAVING来替换WHERE；
如果过滤条件中没有聚合函数，则此过滤条件声明在WHERE中或HAVING中，但最好WHERE中
HAVING必须声明在GROUP BY后面

SELECT department_id,MAX(salary)
FROM   employees
GROUP BY department_id
HAVING MAX(salary)>10000;

SQL底层执行原理

SELECT语句完整结构

在这里插入图片描述

SQL语句执行过程

FROM–>ON–>(LEFT/RIGHT JOIN) -->WHERE
WHERE–>GROUP BY -->HAVING -->SELECT
SELECT -->DISTINCT–>ORDER BY–>LIMIT