软件设计之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