MySQL学习(三)

MySQL函数

官网：https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html

1、常用函数

1-- ======================= 常用函数 =====================
2
3-- 数学运算
4SELECT ABS(-8); -- 绝对值
5SELECT CEILING(9.4); -- 向上取整
6SELECT FLOOR(9.4); -- 向下取整
7SELECT RAND(); -- 返回一个0-1之间的随机数
8SELECT SIGN(10); -- 判断一个数的符号 负数返回-1 整数返回1
9
10-- 字符串函数
11SELECT CHAR_LENGTH('abcdefghijkl'); -- 字符串长度
12SELECT CONCAT('i','love','you');
13SELECT INSERT('我爱编程helloworld',1,2,'超级热爱') -- 从替换某段字符串 1是起始，2是长度
14SELECT LOWER('LenStar'); -- 转为小写
15SELECT UPPER('Lenstar'); -- 转为大写
16SELECT INSTR('Lenstar','h'); -- 返回第一次出现的字符串的索引
17SELECT REPLACE('坚持就能成功','坚持','努力'); -- 替换指定的字符串
18SELECT SUBSTR('坚持就能成功',4,6); -- 截取指定的字符串 4是起始位置，6是长度
19SELECT REVERSE('上海自来水来自海上'); -- 反转
20
21-- 查询姓周的同学，并将周改为邹
22SELECT REPLACE(studentname,'周','邹') FROM student 
23WHERE studentname LIKE '周%';
24
25-- 时间和日期函数（记住）
26SELECT CURRENT_DATE(); -- 获取当前日期
27SELECT CURDATE(); -- 获取当前日期
28SELECT NOW(); -- 获取当前的时间
29SELECT LOCALTIME(); -- 本地时间
30SELECT SYSDATE() -- 系统时间
31
32SELECT YEAR(NOW()); -- 年
33SELECT MONTH(NOW()); -- 月
34SELECT DAY(NOW());  -- 日
35SELECT HOUR(NOW());  -- 时
36SELECT MINUTE(NOW());  -- 分 
37SELECT SECOND(NOW()); -- 秒
38
39-- 系统
40SELECT SYSTEM_USER(); -- 系统用户
41SELECT USER(); -- 用户
42SELECT VERSION(); -- 版本

2、聚合函数（常用）

函数名称

描述

AONUT()

计数

SUM()

求和

AVG()

平均值

MAX()

最大值

MIN()

最小值

...

...

1-- ================ 聚合函数 =================
2-- 都能够统计表中的数据（想查询一个表中有多少个记录，就使用这个count()）
3SELECT COUNT(`BornDate`) FROM student; -- count(段),会忽略所有的null值
4SELECT COUNT(*) FROM student; -- count(*),不会忽略null值，本质：计算行数
5SELECT COUNT(1) FROM result; -- count(1), 不会忽略忽略所有的null值，本质 计算函数
6
7SELECT SUM(`StudentResult`) AS 总分 FROM result;
8SELECT AVG(`StudentResult`) AS 平均分 FROM result;
9SELECT MAX(`StudentResult`) AS 最高分 FROM result;
10SELECT MIN(`StudentResult`) AS 最低分 FROM result;

3、数据库级别的MD5加密

什么是MD5？

主要增强算法复杂度和不可逆性

MD5不可逆，具体的值的MD5值是一样的

MD5破解网站的原理，背后有一个字典，MD5加密后的值---加密前的值

1-- ============== 测试MD5加密 ==============
2
3CREATE TABLE `testmd5`(
4	`id` INT(4) NOT NULL,
5	`name` VARCHAR(20) NOT NULL,
6	`pwd` VARCHAR(50) NOT NULL,
7	PRIMARY KEY(`id`)
8)ENGINE = INNODB DEFAULT CHARSET = utf8;
9
10-- 密文密码
11INSERT INTO testmd5 VALUES(1,'zhangsan','123456'),(2,'lisi','123456'),(3,'wangwu','123456');
12
13-- 加密
14UPDATE testmd5 SET pwd=MD5(pwd) WHERE id =1; 
15
16UPDATE testmd5 SET pwd=MD5(pwd); -- 加密全部的密码
17
18-- 插入的时候加密
19INSERT INTO testmd5 VALUES(4,'xiaoming',MD5('123456'));
20
21-- 如何校验：将用户传递进来的密码，进行md5加密，然后比对加密后的值
22SELECT * FROM `testmd5` 
23WHERE `name`='xiaoming' AND `pwd` = MD5('123456');

事务

要么都成功，要么都失败

1、SQL执行：A给B转账 A 1000 ---> 200 B 200

2、SQL执行：B给A的钱 A 800 ---> 800 -->B 400

核心：将一组SQL放在一个批次中去执行

事物原则：ACID 原则： 原子性、一致性、隔离性、持久性

参考博客链接： https://blog.csdn.net/dengjili/article/details/82468576

原子性(Atomicity)

要么都成功，要么都失败

一致性(Consistency)

事物前后的数据完整性要保持一致

持久性(Durability)---事物提交

事务一旦提交则不可逆，被持久化到数据库中！

隔离性(lsolation)

事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之家要相互隔离。

隔离所导致的一些问题

脏读：

值一个事务读取了另外一个事务未提交的数据。

不可重复读：

在一个十五内读取表中的某一行数据，多次读取结果不同，（这个不一定时错误，知识某些场合不不对）

虚读(幻读)

是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。

执行事务

1-- ============ 事务 =================
2
3-- mysql是默认开启事务自动提交的
4SET autocommit = 0 -- 关闭
5SET autocommit = 1 -- 开启
6
7-- 手动处理事务 关闭自动提交
8SET autocommit = 0
9
10-- 事务开启
11START TRANSACTION -- 标记一个事务的开始吗，从这个之后的sql都在同一个事务内
12
13INSERT xx
14INSERT xx
15
16-- 提交：持久化（成功的时候）
17COMMIT
18-- 回滚：回到原来的样子（失败的时候）
19ROLLBACK
20-- 事务结束 开启自动提交
21SET autocommit = 1  

模拟场景

1-- 转账
2CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
3
4USE shop
5
6CREATE TABLE `account`(
7	`id` INT(3) NOT	NULL AUTO_INCREMENT,
8	`name` VARCHAR(30) NOT NULL,
9	`money` DECIMAL(9,2) NOT NULL,
10	PRIMARY KEY(`id`)
11)ENGINE=INNODB DEFAULT CHARSET=utf8
12
13INSERT INTO account(`name`,`money`)
14VALUES('a',2000.00),('B',10000.00)
15
16-- 模拟转账：事务
17SET autocommit = 0; -- 关闭自动提交
18START TRANSACTION -- 开启一个事务（一组事务）
19
20UPDATE account SET money=money-500 WHERE `name` = 'A' -- A减500
21UPDATE account SET money=money+500 WHERE `name` = 'B' -- A加500
22
23COMMIT; -- 提交事务，就被持久化了！
24ROLLBACK; -- 回滚
25
26SET autocommit = 1; -- 回复默认值。开启自动提交

索引

MySQL官方对缩影的而定义为：索引(index)时帮助MySQL高效获取数据的数据结构。

提取句子主干，可以得到索引的本质：索引就是数据结构。

1、索引的分类

在一个表中，主键索引只能有一个，唯一索引可以有多个

• 主键索引(PRIMARY KEY)

  • 唯一的标志，主键不可重复，之恶能有一个列作为主键

• 唯一索引(UNIQUE KEY)

  • 避免重复的列出现，唯一索引可以重复，多个列都乐意标志为唯一索引

• 常规索引(KEY/INDEX)

  • 默认的，index、keyguanjianzilaishezhi

• 全文索引(FULLTEXT)

  • 在特定的数据库引擎下才有，MAISAM
  • 快速定位数据

基础语法

1-- 索引的使用
2-- 1、在创建表的时候给字段增加索引
3-- 2、创建完毕后，增加索引
4
5-- 显示所有的所有信息
6SHOW INDEX FROM student
7
8-- 增加一个全文索引 索引名（列名）
9ALTER TABLE school.`student` ADD FULLTEXT INDEX `StudentName`(`StudentName`)
10
11-- EXPLAIN 分析sql执行的状况
12EXPLAIN SELECT * FROM student; -- 非全文索引
13EXPLAIN SELECT * FROM student WHERE MATCH(studentName) AGAINST('刘');-- match：获取； aginst：匹配

2、测试索引

1CREATE TABLE `app_user` (
2  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
3  `name` VARCHAR(50) DEFAULT '' COMMENT '用户昵称',
4  `email` VARCHAR(50) NOT NULL COMMENT '用户邮箱',
5  `phone` VARCHAR(20) DEFAULT '' COMMENT '手机号',
6  `gender` TINYINT(4) UNSIGNED DEFAULT '0' COMMENT '性别（0:男；1：女）',
7  `password` VARCHAR(100) NOT NULL COMMENT '密码',
8  `age` TINYINT(4) DEFAULT '0' COMMENT '年龄',
9  `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
10  `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
11  PRIMARY KEY (`id`)
12) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'
13
14-- 插入100万数据  41.664 sec
15DELIMITER $$  -- 写函数之前必须要写，一个标识
16CREATE FUNCTION mock_data() 
17RETURNS INT
18BEGIN
19   DECLARE num INT DEFAULT 1000000;
20   DECLARE i INT DEFAULT 0;
21   WHILE i<num DO
22     INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)VALUES(CONCAT('用户',i),'24736743@qq.com',CONCAT('18',FLOOR(RAND()*((999999999-100000000)+100000000))),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
23     SET i = i+1;
24   END WHILE;
25   RETURN i;
26END;
27SELECT mock_data();
28
29SELECT * FROM app_user WHERE `name` = '用户9999'; -- 1.259 sec
30SELECT * FROM app_user WHERE `name` = '用户9999'; -- 1.256 sec
31SELECT * FROM app_user WHERE `name` = '用户9999'; -- 1.279 sec
32
33EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';
34
35-- 添加索引后再测试
36-- CREATE INDEX 索引名 ON 表（字段）
37CREATE INDEX id_app_user_name ON app_user(`name`);
38
39SELECT * FROM app_user WHERE `name` = '用户9999'; -- 0.003 sec
40
41EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';

索引再小数据量的时候，用处不打，但是再大数据的时候，区别十分明显

3、索引原则

• 索引不是越多越好
• 不要对进程变动数据加索引
• 小数据的表不需要加索引
• 索引一般加载常用来查询的字段上！

索引的数据结构

hash类型的索引

Btree：INNODB的默认数据结构

深入了解参考：http://blog.codinglabs.org/articles/theory-of-mysql-index.html

权限管理和备份

1、用户管理

SQLyog可视化管理

SQL命令操作

用户表：mysql.user

本质：对这张表进行增删改查

1-- 创建用户 CREATE USER 用户名 IDENTIFIED BY '密码'
2CREATE USER lenstar IDENTIFIED BY '123456'
3
4-- 修改密码 （修改当前用户密码）
5SET PASSWORD = PASSWORD('111111');
6
7-- 修改密码（修改指定用户密码）
8SET PASSWORD FOR lenstar = PASSWORD('111111');
9
10-- 重命名 RENAME USER 原来名字 TO 新的名字
11RENAME USER lenstar TO lenstar2
12
13-- 用户授权 ALL PRIVILEGES 全部的权限
14-- ALL PRIVILEGES 除了给别人授权，其他的都能干
15GRANT ALL PRIVILEGES ON *.* TO lenstar2
16
17-- 查询权限
18SHOW GRANTS FOR lenstar2 -- 查看指定用户的权限
19SHOW GRANTS FOR root@localhost
20
21-- root用户权限：grant all privileges on *.* to 'root@localhost' with grant optton
22
23-- 撤销权限 REVOKE 哪些权限，在哪个库撤销，给谁撤销
24RENAME ALL PRIVILEGES ON *.* FROM lenstar2
25
26-- 删除用户
27DROP USER lenstar2

2、MySQL备份

为什么要备份：

• 保证重要的数据不丢失
• 数据转移

MySQL数据库备份的方式

• 直接拷贝物理文件
• 再sql'yog这种可视化工具中手动导出
  • 在想要导出的表或者库中，右键，选择备份或导出

使用命令行导出 mysqldumo 命令行使用

1# mysqldumo -h主机  -u用户名 -p密码 数据库 表名 > 物理磁盘位置/文件名
2mysql -hlocahost -uroot -p123456 school student >D:/student.sql
3
4# mysqldumo -h主机  -u用户名 -p密码 数据库 表1 表2 表3 > 物理磁盘位置/文件名
5mysqldump -hlocahost -uroot -p123456 school student >D:/abc.sql
6
7# mysqldumo -h主机  -u用户名 -p密码 数据库 > 物理磁盘位置/文件名
8mysqldump -hlocahost -uroot -p123456 school >D:/school.sql
9
10# 导入
11# 登录的情况下，切换到指定的数据库
12# source 备份文件
13source d:/a.sql
14
15mysql -u用户名 -p密码 库名< 备份文件

规范数据库设计

1、为什么需要设计

当数据库比较复杂的时候，我们就需要设计了

糟糕的数据库设计：

• 数据冗余，浪费空间
• 数据库插入和删除都会很麻烦、异常
• 程序的性能差

良好的数据库设计：

• 节省内存空间
• 保证数据库的完整性
• 方便我们开发系统

软件开发中，关于数据库的设计：

• 分析需求：分析业务和需要处理的数据库的请求
• 概要设计：设计关系图E-R图

2、三大范式

为什么需要数据规范化？

• 信息重复

• 更新异常

• 插入异常

  • 无法正常显示信息

• 删除异常

  • 丢失有效的信息

三大范式

第一范式(1NF)

原子性：保证每一列不可再分

第二范式(2NF)

前提：满足第一范式

每张表只描述一件事情

第三范式(3NF)

前提：满足第一范式和第二范式

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接访问。

规范性和性能的问题

关联查询的表不得超过三张

• 考虑商业化的需求和目标，数据库的性能更加重要
• 在规范性能的问题的时候，需要适当的考虑一下规范性！
• 故意给某些表增加一些冗余的字段。（从对标查询中变为单表查询）
• 故意增加一些计算列（从大数据量降低为小数据量的查询：索引）