【MySQL】常用函数，事务

5. MySQL函数

5.1 常用函数

-- 字符串函数
SELECT CHAR_LENGTH('即使希岛爱理') -- 返回字符串长度
SELECT CONCAT('xidao','aili')  -- 拼接字符串
SELECT INSERT('woaichina',1,2,'ni') -- 替换字符串
SELECT LOWER('Abc') -- 大小写转换
-- 时间和日期函数（记住）
SELECT CURRENT_DATE() -- 获取当前日期
SELECT CURDATE() -- 获取当前日期
SELECT NOW() -- 获取当前日期和时间
SELECT LOCALTIME() 
SELECT SYSDATE()
SELECT YEAR(NOW())

-- 系统
SELECT SYSTEM_USER()
SELECT VERSION()

5.2 聚合函数（常用）

函数名称	描述
COUNT()	计数
SUM()
AVG()
MAX()
MIN()

5.3 数据库级别的MD5加密

增强算法复杂度
MD5破解网站的原理，背后有一个字典，MD5加密后和加密前的值

-- ================== 测试MD5 加密 =================

CREATE TABLE `testmd5` (
id INT(4) NOT NULL,
`name` VARCHAR(20) NOT NULL,
pwd VARCHAR(50) NOT NULL,
PRIMARY KEY(id)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 明文密码
INSERT INTO testmd5 VALUES(1,'zhangsan','123456'),(2,'lisi','123456'),(3,'lisi','wangwu')

-- 加密
UPDATE testmd5 SET pwd=MD5(pwd)  -- 加密全部的密码

-- 插入的时候加密
INSERT INTO testmd5 VALUES(4,'xiaoming',MD5(123456))

-- 如何校验：用户使用md5加密
SELECT * FROM testmd5 WHERE pwd=MD5('123456')

6.事务

6.1 什么是事务

事务原则：acid
原子性：要么都成功，要么都失败
一致性：事务前后保持一致
隔离性：不同的事务之间互不影响
脏读：一个事务使用了另一个事务没有提交的数据
不可重复读：多次读取表中的数据，结果不同
虚读：在一个事务中读取了别的事务插入的数据（多了一条）
持久性：事务没有提交恢复原状，事务一旦提交，不可逆

-- ================== 事务 =================
-- 事务自动提交默认开启
SET autocommit=0 -- 关闭
SET autocommit=1

-- 手动处理事务
SET autocommit=0
-- 事务开启
START TRANSACTION

-- 提交
COMMIT
-- 回滚
ROLLBACK

-- 事务结束
SET autocommit=1

-- 了解
SAVEPOINT 保存点名 -- 设置事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名 -- 撤销保存点



-- 转账
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
USE shop

CREATE TABLE account(
id INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL,
money DECIMAL(9,2) NOT NULL,
PRIMARY KEY(id)
)ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO account (`name`,money) VALUES ('A',2000.00),('B',10000.00)

-- 模拟转账
SET autocommit=0;
START TRANSACTION
UPDATE account SET money=money-500 WHERE `name`='A'
UPDATE account SET money=money+500 WHERE `name`='B'

COMMIT;
ROLLBACK;
SET autocommit=1;

7.索引

通过索引可以高效获取数据

7.1 索引的分类：

• 主键索引
  唯一的标识，主键不可重复，只能有一个列作为索引
• 唯一索引
  避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引
• 常规索引KEY
  默认的
• 全文索引
  在特定的数据库引擎下才有

-- ================== 索引 =================
-- 1.创建的时候使用
-- 2. 创建完毕后增加

-- 显示索引
SHOW INDEX FROM student

-- 增加索引
ALTER TABLE school.student ADD FULLTEXT INDEX studentanme(studentname)

-- 分析sql执行的状况
EXPLAIN SELECT * FROM student -- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(studentname) AGAINST('刘')

7.2 测试索引

快是因为索引能够直接定位

-- 创建索引的第三种方式
CREATE INDEX 索引名 ON 表名（字段）
CREATE INDEX id_user ON app_user(`name`)

SELECT * FROM app_user WHERE `name`='用户9999'
-- 加索引后查询时间明显缩短

在大数据的时候区别十分明显

7.3 索引不是越多越好

• 索引不是越多越好
• 小数据量不要加索引
• 用在经常查询的字段

索引的数据结构

innodb默认的数据类型：Btree