MySQL数据库04--事务、索引、备份及三大范式

好好编程不会胖！

于 2021-09-28 12:28:10 发布

阅读量84

点赞数

文章标签：数据库 mysql

本文链接：https://blog.csdn.net/m0_56161569/article/details/120502773

版权

一、事务

1、什么是事务

事务原则: ACID原则
1.原子性(Atomicity) : 要么都成功，要么都失败、
2.一致性(Consistency) : 事务前后的数据完整性要保证一致，1000
3.持久性(Durability) ------ 事务提交
事务一旦提交则不可逆，被持久化到数据库中!
4.隔离性(lsolation)
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，事务之间要相互隔离。
隔离所导致的一些问题
1.脏读 : 指一个事务读取了另外—个事务未提交的数据。
2.不可重复读 : 在一个事务内读取表中的某一行数据，多次读取结果不同。(这个不一定是错误，只是某些场合不对)
3.虚读(幻读) : 是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。
执行事务

-- mysql 是默认开启事务自动提交的
SET autocommit = 0    /*关闭*/
SET autocommit = 1   /*开启（默认的）*/

--手动处理事务
SET autocommit = 0   -- 关闭自动提交

--事务开启
START TRANSACTION   -- 标记一个事务的开始，从这个之后的sql 都在同一个事务内
INSERT XX
INSERT XX

-- 提交:持久化(成功!)
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'   -- A减500
UPDATE account sET money=money+500 WHERE `name`= 'B'    -- A加500

COMMIT;   -- 提交事务，就被持久化了!
ROLLBACK;   -- 回滚

SET autocommit = l;   -- 恢复默认值

二、索引

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

1、索引的分类
在一个表中，主键索引只能有一个，唯一索引可以有多个

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

-- 索引的使用
-- 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(`刘`);

2、索引原则

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

索引的数据结构

Hash类型的索引
Btree : InnoDB的默认数据结构

三、权限管理和备份

1、用户管理

SQL yog 可视化管理
本质 : 读这张表进行增删改查

-- 创建用户   CREATE USER 用户名 IDENTIFIED BY `密码`
CREATE USER juju IDENTIFIED BY `123456`

-- 修改密码 （修改当前用户密码)
SET PASSWORD = PASSWORD(`123456`)

-- 修改密码 （修改指定用户密码)
SET PASSWORD FOR juju = PASSWORD(`123456`)

-- 重命名 RENAME USER 原来名字 TO 新的名字
RENAME USER juju To juju2

-- 用户授权 ALL PRIVILEGES 全部的权限，库.表
-- ALL PRIVILEGES  除了给别人授权，其他都能够干
GRANT ALL PRIVILEGES ON *.* To juju2

-- 查询权限
SHOW GRANTS FOR juju2    -- 查看指定用户的权限
SHOW GRANTS FOR root@1ocalhost

-- ROOT用户权限: GRANT ALL PRTVILEGES ON *.* TO 'root' @'localhost' WITH GRANT OPTION

-- 撤销权限 REVOKE 哪些权限，在哪个库撤销,给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM juju2

-- 删除用户
DROP USER juju

2、MySQL备份

为什么要备份:
。保证重要的数据不丢失
。数据转移
MySQL数据库备份的方式
。直接拷贝物理文件
。在Sqlyog这种可视化工具中手动导出
在想要导出的表或者库中，右键，选择备份或导出
。* 使用命令行导出 mysqldump 命令行使用

# mysqldump -h 主机 -u 用户名 -p 密码  数据库  表名  >  物理磁盘位置/文件
mysqldump -hlocalhost -uroot -p123456 schoo1 student >D:/a.sql

# mysqldump -h 主机 -u 用户名 -p 密码  数据库 表1 表2 表3 > 物理磁盘位置/文件
mysqldump -hlocalhost -uroot -p123456 school student >D:/b.sql

# mysqldump -h 主机 -u 用户名 -p 密码  数据库 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school >D:/c.sql

# 导入
# 登录的情况下，切换到指定的数据库
# source备份文件
source d:/a.sql

mysql -u用户名 –p密码  库名< 备份文件

四、规范数据库设计

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

糟糕的数据库设计:
。数据冗余，浪费空间
。数据库插入和删除都会麻烦、异常【屏蔽使用物理外键】
。程序的性能差
良好的数据库设计:
。节省内存空间
。保证数据库的完整性
。方便我们开发系统
软件开发中，关于数据库的设计
。分析需求 : 分析业务和需要处理的数据库的需求
。概要设计:设计关系图E-R图
设计数据库的步骤 : (如个人博客)
。收集信息，分析需求
用户表（用户登录注销，用户的个人信息，写博客，创建分类)
分类表(文章分类，谁创建的)
文章表(文章的信息)
友链表(友链信息)
自定义表(系统信息，某个关键的字，或者一些主字段)key : value
说说表(发表心情… id… content…create_time)
。标识实体(把需求落地到每个字段)
。标识实体之间的关系
写博客 : user --> blog
创建分类 : user -> category。关注: user ->user
友链 : links
评论 : user-user-blog