52、像编程语言利用函数封装业务功能一样,数据库
MySQL的函数分为四类
数字函数:FORMAT、ABS、MOD、CEIL、FLOOR、ROUND、EXP......
字符函数:UPPER、LOWER、CHAR_LENGTH、CONCAT、INSTR......
日期函数和条件函数:NOW、DATE_FORMAT、DATE_ADD、DATEDIFF、IF、IFNULL.....
53、数字函数(一)
函数 | 功能 | 用例 | 结果 |
ABS | 绝对值 | ABS(-1) | 1 |
ROUND | 四舍五入 | ROUND(4.1) | 4 |
FLOOR | 强制舍位到最近的整数 | FLOOR(9.9) | 9 |
CEIL | 强制进位到最近的整数 | CEIL(3.1) | 4 |
POWER | 幂函数 | POWER(2,3) | 8 |
LOG | 对数函数 | LOG(2,8) | 3 |
LN | 对数函数 | LN(10) | 2.302585 |
数字函数(二)
函数 | 功能 | 用例 | 结果 |
SQRT | 开平方 | SQRT(3) | 3 |
PI | 圆周率 | PI() | 3.1415926535 |
SIN | 三角函数 | SIN(1) | 0.84147 |
COS | 三角函数 | COS() | |
TAN | 三角函数 | TAN() | |
COT() | 三角函数 | COT() | |
RADIANS | 角度转换弧度 | RADIANS() | |
DEGREES | 弧度转换角度 | DEGREES() |
54、时间函数
获取系统时间函数
NOW()函数能获取系统日期和时间,格式为:2018-10-22 18:45:31
CURDATE()函数能获得当前系统日期,格式:2018-10-22
CURTIME()函数能获得当前系统时间,格式:18:45:31
日期格式化函数
占位符 | 作用 | 占位符 | 作用 |
%Y | 年份 | %m | 月份 |
%d | 日期 | %w | 星期(数字) |
%W | 星期(名称) | %j | 本年第几天 |
%U | 本年第几周 | %H | 小时(24制) |
%h | 小时(12制) | %i | 分钟 |
%s | 秒 | %r | 时间(24制) |
%T | 时间(12制) |
DATE_FORMAT()函数用于格式化日期,返回用户想要的日期格式
DATE_FORMAT(日期,表达式)
例:SELECT ename,DATE_FORMAT(hiredate,"%Y") FROM t_emp;
日期计算的注意事项
MySQL数据库里面,两个日期不能直接加减,日期也不能与数字加减
55、日期偏移计算
DATE_ADD()函数可以实现日期的偏移计算,而且时间单位灵活
DATE_ADD(日期,INTERVAL 偏移量 时间单位)
SELECT DATE_ADD(NOW(),INTERVAL 1 MONTH); #往后移一个月
SELECT DATE_ADD(NOW(),INTERVAL -20 DAY); #向前移20天
56、DATEDIFF()函数用来计算日期之间相差的天数
DATEDIFF(日期,日期)
若要计算相差的年份则可用:DATEDIFF(日期,日期)/365
57、字符函数(一)
函数 | 功能 | 用例 |
LOWER | 转换小写字符 | LOWER(name) |
UPPER | 转换成大写字符 | UPPER(name) |
LENGTH | 字符长度(数量) | LENGTH(name) |
CONCAT | 连接字符串 | CONCAT(sal,"$") |
INSTR | 字符出现的位置 | INSTR(name,"a") |
INSERT | 插入/替换字符 | INSERT("你好",1,0,"先生") |
REPLACE | 替换字符 | REPLACE("你好先生","先生","女士") |
58、字符函数(二)
函数 | 功能 | 用例 |
SUBSTR | 截取字符串 | SUBSTR("你好时光",3,4) |
SUBSTRING | 截取字符串 | SUBSTRING("你好时光",3,2) |
LPAD | 左侧填充字符 | LPAD("Hello",10,"^") |
RPAD | 右侧填充字符 | RPAD("Hello",10,"^") |
TRIN | 去除首位空格 | TRIM(" 你好时光 ") |
59、条件函数
SQL语句可用条件函数来实现编程语言里的条件判断
IFNULL(表达式,值) #若表达式为NULL,则NULL换成值
IF(表达式,值1,值2) #先判断表达式,若满足则执行值1,否则执行值2
复杂的条件判断可以用CASE WHEN...THEN..语句实现
CASE
WHEN 表达式 THEN 值1,
WHEN 表达式 THEN 值2,
......,
ELSE 值N
END
60、事务机制(一)
如果数据的写入直接操作数据文件的非常危险的事情
事务机制可以利用日志实现间接写入,避免写入直接操作数据文件
MySQL有五种日志,其中只有redo日志和undo日志与事务有关
61、事务机制(二)
RDBMS=SQL语句+事务(ACID)
事务是一个或者多个SQL语句组成的整体,要么全部执行成功,要么全部执行失败
默认情况下,MySQL执行每条SQL语句都会自动开启和提交事务,为了让多条SQL语句纳入到一个事务之下,可以手动管理事务
START TRANSACTION;
SQL语句
[COMMIT/ROLLBACK]; #执行或回滚
62、事务的ACID属性
A:原子性:一个事务中的所有操作要么全部完成,要么全部失败。事务执行后不允许停留在中间某个状态
C:一致性:不管任何时间并发事务有多少,事务必须保证运行结果的一致性,事务可以并发执行,但最终MySQL却串行执行
I:隔离性:隔离性要求事务不受其他并发事务影响,如在给定的时间内,该事务是数据库唯一运行的事
D:持久性:事务一旦提交,结果便是永久性的,即便发生宕机,仍然可以依靠事务日志完成数据持久化
63、事务的四个隔离级别
序号 | 隔离级别 | 功能 |
1 | read uncommitted | 读取未提交数据 |
2 | read committed | 读取已提交数据 |
3 | repeatable read | 重复读取 |
4 | serializable | 系列化 |
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; #设置事务隔离级别为可读取未提交数据
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; #设置事务隔离级别为可读取已提交数据
64、数据的导出与备份
数据导出,导出的纯粹是业务数据
数据备份,备份的是数据文件、日志文件、索引文件等
数据备份第一次为全量备份,以后备份为增量备份
65、导出SQL文件
mysqldump用来把业务数据导出成SQL文件,其中也包含了表结构
如果业务数据非常多,建议只导出表结构,然后用SELECT INTO OUTFILE把数据导出成文本文档
66、导入SQL文件
source用于导入SQL文件,包括创建数据表,写入记录等
例:
USE demo;
SOURCE backup.sql;
67、数据加密
数据加密可分为对称加密和非对称加密
对称加密:DES加密和AES加密
非对称加密:RSA加密、DSA加密和ECC加密
68、AES加密
MySQL数据库提供了AES加密和解密的函数
AES_ENCRYPT(原始数据,密钥字符串) #对原始数据进行加密
AES_DECRYPT(加密结果,密钥字符串) #对加密数据进行解密
/*一般情况下为了不出现乱码,都会把加密的二进制结果转换成十六进制,解密时把十六进制转换为二进制再解密*/
HEX(AES_ENCRYPT(原始数据,密钥字符串)) #对原始数据进行加密,加密结果为十六进制数
AES_DECRYPT(UNHEX(加密结果),密钥字符串) #对十六进制加密结果数据进行解密