五~九.MySQL函数，事务，索引，权限管理和备份，规范数据库设计（b站狂神学习笔记）

五.MySQL函数

参考

原文链接：https://blog.csdn.net/a951273629/article/details/107094285/

https://blog.csdn.net/qq_33369905/article/details/105872921

5.1常用函数（不常用）

--  =============常用函数=========================

-- 数学运算

SELECT ABS(-8)  -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND() -- 返回0-1 随机数
SELECT SIGN(-10) -- 判断一个数的符号 负数返回-1 整数返回1


-- 字符串函数

SELECT CHAR_LENGTH('字符串长度') -- 字符串长度
SELECT CONCAT('李','是一个','张三') -- 拼接字符串
SELECT INSERT('超级人',1,2,'我是一个') -- 替换字符串,insert("原字符",'开始位置','替换长度','用作替换的字符')
我是一个人
SELECT LOWER('WuDiMengNan') -- 小写字母
SELECT UPPER('WuDiMengNan') -- 大写字母
SELECT INSTR('chaojiwudimengnan','wudi') -- 指定字符第一次出现的位置
SELECT REPLACE('ccccc','c','mengnan') -- 替换指定的字符串
SELECT SUBSTR('超级猛男无人能挡',3,6) -- 返回指定的子字符串(源字符串,起始位置,截取长度)
SELECT REVERSE('超级猛男') -- 翻转字符串


 -- 查询姓周的同学,改成邹
 SELECT REPLACE(studentname,'周','邹') AS 新名字
 FROM student WHERE studentname LIKE '周%';

-- 时间和日期函数 （记一下）
SELECT CURRENT_DATE() -- 获取当前日期
SELECT CURDATE() -- 获取当前日期
SELECT NOW() -- 获取当前的时间
SELECT LOCALTIME() -- 本地时间
SELECT SYSDATE() -- 系统时间

SELECT YEAR(NOW()) -- 年
SELECT MONTH(NOW()) -- 月
SELECT DAY(NOW()) -- 日
SELECT HOUR(NOW()) -- 时
SELECT MINUTE(NOW()) -- 分
SELECT SECOND(NOW()) -- 秒

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

5.2 聚合函数（常用）

函数名称	描述
COUNT()	返回满足Select条件的记录总和数，如 select count(*) 【不建议使用 *，效率低】
SUM()	返回数字字段或表达式列作统计，返回一列的总和。
AVG()	通常为数值字段或表达列作统计，返回一列的平均值
MAX()	可以为数值字段，字符字段或表达式列作统计，返回最大的值
MIN()	可以为数值字段，字符字段或表达式列作统计，返回最小的值
…	…

-- 统计表中的数 (想查询一个表中有多少个记录就是用count)
SELECT COUNT(`studentname`) FROM `student`; -- count(字段),会忽略所有的null值 --指定列 
SELECT COUNT(*) FROM `student`; -- count(*) 不会忽略null值
SELECT COUNT(1) FROM `result`;-- count(1) 本质 计算行数

SELECT SUM(`studentresult`) AS 总和 FROM `result`;
SELECT AVG(`studentresult`) AS 平均分 FROM `result`
SELECT MAX(`studentresult`) AS 最大分 FROM `result`
SELECT MIN(`studentresult`) AS 最低分 FROM `result`

-- 分组
-- 查询不同课程的平均分，最高分，最低分
SELECT `subjectname`,AVG(r.`studentresult`) AS 平均分,MAX(r.`studentresult`) AS 最大分,MIN(r.`studentresult`) AS 最低分
FROM `result` AS r
INNER JOIN `subject` AS s
ON r.`subjectno`=s.`subjectno`
GROUP BY s.`subjectno` -- group by 通过什么字段来分组
HAVING 平均分>80  -- having 分组后必须满足的次要条件


--where条件里不能有聚合函数 ，要使用HAVING..

where写在group by前面.
 要是放在分组后面的筛选
 要使用HAVING..
 因为having是从前面筛选的字段再筛选，而where是从数据表中的字段直接进行的筛选的

六.事务

事务原则 : ACID 原子性 一致性 隔离性 持久性

原子性(Atomic)

整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（ROLLBACK）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性(Consist)

一个事务可以封装状态改变（除非它是一个只读的）。事务必须始终保持系统处于一致的状态，不管在任何给定的时间并发事务有多少。也就是说：如果事务是并发多个，系统也必须如同串行事务一样操作。其主要特征是保护性和不变性(Preserving an Invariant)，以转账案例为例，假设有五个账户，每个账户余额是100元，那么五个账户总额是500元，如果在这个5个账户之间同时发生多个转账，无论并发多少个，比如在A与B账户之间转账5元，在C与D账户之间转账10元，在B与E之间转账15元，五个账户总额也应该还是500元，这就是保护性和不变性。
~~守恒原理~~

隔离性(Isolated)

隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。

持久性(Durable)

在事务完成以后，该事务对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。 commit之后就不能回滚了

基本语法

-- 使用set语句来改变自动提交模式
SET autocommit = 0;   /*关闭*/
SET autocommit = 1;   /*开启*/

-- 注意:
---  1.MySQL中默认是自动提交
---  2.使用事务时应先关闭自动提交

-- 开始一个事务,标记事务的起始点
START TRANSACTION  

-- 提交一个事务给数据库
COMMIT

-- 将事务回滚,数据回到本次事务的初始状态
ROLLBACK

-- 还原MySQL数据库的自动提交
SET autocommit =1;

-- 保存点
SAVEPOINT 保存点名称 -- 设置一个事务保存点
ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名称 -- 删除保存点

测试

/*
课堂测试题目
A在线买一款价格为500元商品,网上银行转账.
A的银行卡余额为2000,然后给商家B支付500.
商家B一开始的银行卡余额为10000
创建数据库shop和创建表account并插入2条数据
*/

CREATE DATABASE `shop`CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `shop`;

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

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

-- 转账实现-- 一条一条执行
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION;  -- 开始一个事务,标记事务的起始点
UPDATE account SET cash=cash-500 WHERE `name`='A';
UPDATE account SET cash=cash+500 WHERE `name`='B';
COMMIT; -- 提交事务

# rollback;

SET autocommit = 1; -- 恢复自动提交

隔离导致的一些问题

 脏读: 指一个事务读取了另一个事务未提交的数据
不可重复读: 在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）
虚读(幻读): 是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。
（一般是行影响，多了一行）

七.索引

定义：索引（index）是帮助MySQL高效获取数据的数据结构（有序）

索引就是数据结构 提高获取数据的速度

7.1 索引分类介绍

主键索引

主键 : 某一个属性组能唯一标识一条记录

特点 :

• 最常见的索引类型
• 确保数据记录的唯一性
• 确定特定数据记录在数据库中的位置

唯一索引 (unique key)

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

常规索引(key / index)

• 默认的 index key 关键字来设置

全文索引(fulltext)

• 在特定的数据库引擎下

-- 索引
-- 1 在创建表的时候给字段增加索引
-- 2 创建完毕后，增加索引
-- 显示所有的索引信息
SHOW INDEX FROM `student`

-- 增加一个索引
ALTER TABLE `student` ADD FULLTEXT INDEX `studentname`(`studentname`)


-- explain 分析sql执行的状况
EXPLAIN SELECT * FROM `student` -- 非全文索引

EXPLAIN SELECT * FROM `student` WHERE MATCH(`studentname`) AGAINST('张')

索引在大数据量的时候效率高

7.2索引原则

• 索引不是越多越好
• 不要对经常变得数据加索引
• 小数据量的表不需要加索引
• 索引一般加在常用来查询的字段上

索引的数据结构

hash 类型的索引

Btree: innoDB 的默认数据结构

八.权限管理和备份

8.1 权限管理

• 可视化管理工具（SQLyog ）

• 命令行

-- ================权限管理
-- 创建用户
CREATE USER dong  IDENTIFIED BY '123456'

-- 修改密码(修改当前用户密码)
SET PASSWORD =PASSWORD('123456')
新版本：ALTER USER 'dong'@'%' IDENTIFIED BY '123456'
只可以在上面的创建方式基础上使用

-- 修改密码(修改指定 用户密码)
SET PASSWORD FOR dong = PASSWORD('123')

-- 重命名
RENAME USER dong TO menmen

-- 用户授权 all privileges 全部权限 库 表
-- all privileges 除了给别人授权，其他都能干
GRANT ALL PRIVILEGES ON *.* TO dong


-- 查询权限
SHOW GRANTS FOR dong  -- 查看指定用户的权限

SHOW GRANTS FOR root@localhost 

-- 撤销权限 
REVOKE ALL PRIVILEGES ON *.* FROM dong

-- 删除用户
DROP USER dong

8.2MySQL 备份

数据库备份必要性

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

mysql数据库备份的方式

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

• 使用命令行导出 mysqldump 命令行使用

# mysqldump -h主机名 -u用户名 -p密码 数据库 表名 >导出到的 物理磁盘路径/文件名 
mysqldump -hlocalhost -uroot -p123456 school student >D:/1.sql

# mysqldump -h主机名 -u用户名 -p密码 数据库 表名1 表名2 表名3 >物理磁盘路径/文件名 
# 导出多张表
mysqldump -hlocalhost -uroot -p123456 school student result>D:/2.sql

# mysqldump -h主机名 -u用户名 -p密码 数据库>导出到的 物理磁盘路径/文件名 
mysqldump -hlocalhost -uroot -p123456 school  >D:/1.sql

# 导入 
# 先登陆 
mysql -uroot -p123456

#登陆的情况下
#切换到指定数据库
 use school
 
 #导入命令
 source d:/1.sql --将要导入文件的地址
 
 #未登录（不推荐）
 mysql -u用户名 -p密码 库名< 备份文件

九.规范数据库设计

9.1为什么要需要设计

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

糟糕的数据库设计

数据冗余，浪费空间
数据库插入和删除都会麻烦，[屏蔽使用物理外键]

良好的数据设计

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

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

分析需求
概要设计:设计关系图E-R图

设计数据库的步骤(个人博客)

收集信息，分析需求
    用户表(用户名登陆注销，用户的个人信息，写博客，创建分类)
    分类表(文章分类,谁创建的)
    文章表(文章的信息)
    有链表(友链信息)
    自定义表(系统信息，某个关键的字，或者一些字段)

标识实体()

9.2数据库设计的三大范式

三大范式

第一范式（1NF）

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

第二范式（2NF）

前提:满足第一范式

每张表只描述一件事情

第三范式（3NF）

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

规范数据库的设计

规范性和性能的问题

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

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