MySQL学习笔记（五）

MySQL学习笔记（五）

5.1 事务

将一组sql放在一个批次中去执行

事务原则：ACID原则

原子性（Atomic）：

要么都成功，要么都失败

一致性（Consist）

事务前后的数据完整性要保证一致，假如有两个账户A,B，这两个账户的总额是1000，那么无论这两个账户怎么转账，最后的总额也应该还是1000

持久性（Durable）

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

隔离性（Isolated）

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

隔离所导致的一些问题

脏读：

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

不可重复读：

在一个事务内读取表中的某一行数据，多次读取结果不同（这个不一定是错误）

虚读：

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

执行事务

--  MySQL 是默认开启事务自动提交的
-- 使用事务时应先关闭自动提交
SET autocommit = 0 -- 关闭
SET autocommit =1 -- 开启
 
 
 -- 手动处理事务
 -- 事务开启
  START TRANSACTION -- 标记一个事务的开始，从这个之后的sql都在同一个事务内
 
-- 提交：持久化（成功：）
COMMIT
-- 回滚：回到原来的样子（失败：）
ROLLBACK
 -- 事务结束
 SET autocommit =1 -- 开启自动提交
  
 SAVEPOINT 保存点名称 -- 设置一个事务的保存点
 ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点
 RELEASE SAVEPOINT 保存点名称 -- 删除保存点

模拟场景

--  模拟转账：事务(一条语句一条语句的执行)
SET autocommit = 0 ;  -- 关闭自动提交
START TRANSACTION; -- 开启一个事务

UPDATE `account` SET `cash`=`cash`-500 WHERE `name`='A'-- A减500
UPDATE `account` SET `cash`=`cash`+500 WHERE `name`='B'-- B加500

COMMIT; -- 提交事务
ROLLBACK ;-- 回滚
SET autocommit =1;-- 恢复自动提交

5.2 索引

mysql官方对索引的定义为:索引(index)是帮助MySQL高效获取数据的数据结构,提取句子主干,就可以得到索引的本质:索引是数据结构

5.2.1索引的分类

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

• 主键索引(PRIMARY KEY)
  • 唯一的标识,主键不可重复,只能有一个列作为主键
• 唯一索引(UNIQUE)
  • 避免重复的列出现,唯一索引可以重复,多少列都可以标识为唯一索引
• 常规索引(INDEX)
  • 默认的,index,key关键字来设置
• 全文索引(FULLTEXT)
  • 在特定的数据库引擎下才有,MyISAM
  • 快速定位数据

-- 索引的使用
-- 1、在创建表的时候给字段增加索引
-- 2、创建完毕后增加索引

-- 显示所有的索引信息
SHOW INDEX FROM `student`

-- 增加一个索引
 ALTER TABLE school.`student`ADD FULLTEXT INDEX `studentname`(`studentname`)
 
 -- EXPLAIN 分析sql执行的状况
 EXPLAIN  SELECT *FROM `student` -- 非全文索引
 
 
 EXPLAIN SELECT *FROM student WHERE MATCH (studentname) AGAINST ('刘')

5.2.2 索引原则

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

索引的数据结构

Hash类型的索引

Btree：InnoDB默认的数据结构

5.3 权限管理和备份

5.3.1 权限管理

sql yog可视化管理

sql命令操作

用户表： mysql.user

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

-- 创建用户 CREATE USER 用户名 IDENTIFIED BY '密码'
 CREATE USER shm IDENTIFIED BY '123456'
 
-- 修改密码（修改当前用户密码）
SET PASSWORD= '111111';
SET PASSWORD ='123456';

-- 修改密码（修改指定用户密码）
SET PASSWORD FOR shm ='123456'
 
-- 重命名 RENAME USER 原来名字 TO 新名字
 RENAME USER shm TO shm1
 
-- 用户授权 ALL PRIVILEGES 全部的权限  库.表
--  ALL PRIVILEGES 除了给别人授权，其他都能干
 GRANT ALL PRIVILEGES  ON *.*TO shm1
 
 -- 查看权限
 SHOW GRANTS FOR shm1 -- 查看指定用户的
 SHOW GRANTS FOR root@localhost
 
 -- 撤销权限 REVOKE 那些权限 在哪个库撤销，撤销什么
 REVOKE  ALL PRIVILEGES ON *.* FROM shm1
 
 -- 删除用户 DROP USER 用户名
 DROP USER shm1

5.3.2 MySQL备份

为什么备份：

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

MySQL数据备份的方式

• 直接拷贝物理文件

• 在sqlyog可视化工具中手动导出

  • 在想要导出的表或者库中，右键，选择备份或导出

  

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

  # mysqldump -h主机名 -u用户名 -p密码 数据库 表名 >物理磁盘位置/文件名
  C:\Users\宋慧敏>mysqldump -hlocalhost -uroot -p123456 school student >E:/a.sql
  
  # mysqldump -h主机名 -u用户名 -p密码 数据库 表1 表2 表3 >物理磁盘位置/文件名
  C:\Users\宋慧敏>mysqldump -hlocalhost -uroot -p123456 school student result >E:/a.sql
  
  # mysqldump -h主机名 -u用户名 -p密码 数据库  >物理磁盘位置/文件名
  C:\Users\宋慧敏>mysqldump -hlocalhost -uroot -p123456 school >E:/a.sql
  
  # 导入
  #登录MySQL的情况下，切换到指定的数据库
  #source 备份文件
  source E:/a.sql
  
  mysql -u用户名 -p密码 库名 <备份文件
  

  适用情况：

  如果你要备份数据库，防止数据丢失

  把数据库给朋友！把sql文件给别人即可

5.4 规范数据库设计

5.4.1 规范数据库

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

糟糕的数据库设计：

• 数据冗余
• 数据库植入和删除都会麻烦，异常【屏蔽使用物理外键】
• 程序的性能差

良好的数据库设计：

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

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

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

设计数据库步骤：（个人博客）

• 收集信息：分析需求
  • 用户表（用户登录注销，用户的个人信息，写博客，创建分类）
  • 分类表（文章分类，谁创建的）
  • 文章表（文章的信息）
  • 评论表
  • 友链表（友链信息）
  • 自定义表（系统信息，某个关键的字，或者一些主字段）key：value
  • 说说表（发表心情）
• 标识实体（把需求落地到每个字段）
• 标识实体之间的关系
  • 写博客：user->blog
  • 创建分类：user->category
  • 关注：user->user
  • 友链：links

5.4.2 三大范式

为什么需要数据规范化

• 信息重复
• 更新异常
• 插入异常
  • 无法正常显示信息
• 删除异常
  • 丢失有效的信息

三大范式（了解）

第一范式（1NF）

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

第二范式（2NF）

前提：满足第一范式

每张表只描述一件事情

第三范式（3NF）

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

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

规范性 和 性能的问题：

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

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