MySQL基础入门（2）

2021-02-02 本文已影响0人小铮冲冲冲

六、事务

将一组MySQL放在一个批次中执行 以转账为例
事务原则：ACID原则：原子性、一致性、隔离性、持久性（脏读、幻读…）
原子性（Atomicity）
事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
要么都成功，要么都失败！
一致性（Consistency）
事务前后数据完整性保持一致。
隔离性（Isolation）
多个用户并访问数据库时，数据库为每一个用户开启的事务，不能被其它事务的操作数据所干扰，多个并发事务之间要相互隔离。
持久性（Durability）
一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。
事务的隔离级别（隔离导致的一些问题）：
脏读：
指一个事务读取了另一个事务未提交的数据。
不可重复读：
在一个事务内读取表中的某一行数据，多次读取结果不同。（这不一定是错误，只是某些场合不对。）
虚读（幻读）：
是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。（一般是行影响，多了一行。）
参考CSDN博客链接：https://blog.csdn.net/dengjili/article/details/82468576

image

执行事务（原理）：
mysql是默认开启事务自动提交的
关闭：

SET autocommit = 0

开启（默认的）：

SET autocommit = 1

--手动处理事务
SET autocommit = 0 --关闭自动提交
--事务开启
START TRANSACTION --标记一个事务的开始，从这个之后的sql都在同一个事物内
INSERT…
INSERT… --只要有失败的，事务就提交不上去
--提交：持久化（成功！）
COMMIT
--回滚：回到原来的样子（失败！）
ROLLBACK
--事务结束
SET autocommit = 1 --开启自动提交
--事务过长，中途可利用保存点
SAVEPOINT 保存点名 --设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 --回滚到保存点
RELEASE SAVEPOINT 保存点名 --撤销保存点

image

模拟转账：

--创建账户数据库和表并插入数据
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 --开启一个事务（一组事务）
--A转账500元给B
UPDATE account SET money=money-500 WHERE `name`='A' --A减500元
UPDATE account SET money=money+500 WHERE `name`='B' --B加500元
COMMIT; --提交事务后被持久化
ROLLBACK; --回滚
SET autocommit = 1 --恢复默认值

七、索引

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

参考博客园链接：https://www.cnblogs.com/tgycoder/p/5410057.html
原文链接：https://blog.codinglabs.org/articles/theory-of-mysql-index.html

image

1.索引的分类

在一个表中，主键索引只能有一个，唯一索引可以有多个。
1）主键索引（PRIMARY KEY）
唯一的标识，主键不可重复，只能有一个列作为主键。
2）唯一索引（UNIQUE KEY）
避免重复的列出现，唯一索引可以重复，多个列都可以表识为唯一字符。
3）常规索引（KEY/INDEX）
默认的，可以用index或key关键字来设置。
4）全文索引（FullText）
在特定的数据库引擎下才有，以前是在MyISAM。
作用：快速定位数据。
索引的使用：
①在创建表的时候给字段增加索引。
②创建完毕后，增加索引。
例：给student表中的学生姓名增加全文索引。

--显示所有的索引信息
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('刘'); --用全文索引查询学生姓名中含刘的人的情况

image

2.测试索引

app_user用户测试表用于测试数据，可下载使用：https://share.weiyun.com/I8ERfGwR，也可以直接复制下方。

CREATE TABLE `app_user` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) DEFAULT'' COMMENT'用户昵称',
  `email` VARCHAR(50) NOT NULL COMMENT'用户邮箱',
  `phone` VARCHAR(20) DEFAULT'' COMMENT'手机号',
  `gender` TINYINT(4) UNSIGNED DEFAULT '0'COMMENT '性别（0：男;1:女）',
  `password` VARCHAR(100) NOT NULL COMMENT '密码',
  `age` TINYINT(4) DEFAULT'0'  COMMENT '年龄',
  `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表'

插入100万条数据：

DELIMITER $$ --写函数之前必须要写的标志
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
  DECLARE num INT DEFAULT 1000000;
  DECLARE i INT DEFAULT 0;
  WHILE i<num DO
    INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`) VALUES(CONCAT('用户',i),'2585801995@qq.com',CONCAT('18',FLOOR(rand()*((999999999-100000000)+100000000))),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
    SET i=i+1;
  END WHILE;
  RETURN i;
END;
--执行函数，生成100万条数据
SELECT mock_data();

例：查询用户9999的信息，对比其有无索引的查询时间。

SELECT * FROM app_user WHERE `name`='用户9999'; --查询用户9999的信息
EXPLAIN SELECT *FROM app_user WHEWE `name`='用户9999'; --用户9999被查询时的情况
--创建常规索引：CREATE INDEX 索引名 on 表(字段)
CREATE INDEX id_app_user_name ON app_user(`name`);

image

注：索引名一般用id_表名_字段名表示。
索引在小数据量的时候，用处不大；但是在大数据的时候，区别十分明显。

3.索引原则

索引不是越多越好。
不要对经常变动的数据加索引。
小数据量的表不需要加索引。
索引一般加在常用来查询的字段上！
索引的数据结构
Hash类型的索引，但不是默认，InnoDB默认的数据结构是Btree。
继续阅读上述博客园文章：MySQL索引背后的数据结构及算法原理

八、权限管理和备份

1.用户管理

1）SQLyog可视化操作
尝试添加新用户和删除用户。选择的主机是什么，登录的时候就是什么。
创建新用户：

image

删除用户：

image

2）SQL命令操作
用户表：mysql.user
本质：对这张表进行增删改查。
例：

--创建用户：CREATE USER 用户名 IDENTIFIED BY 密码
CREATE USER programmer IDENTIFIED BY '123456'
--修改密码
SET PASSWORD = PASSWORD('123456') --修改当前用户密码
SET PASSWORD FOR programmer = PASSWORD('123456') --修改指定用户密码
--重命名：RENAME USER 原名字 TO 新名字
RENAME USER programmer TO programmer2
--用户授权：GRANT ALL PRIVILEGES ON 库.表 TO 用户
GRANT ALL PRIVILEGES ON *.* TO programmer2 --给programmer2全部的授权
--ALL PRIVILEGES除了给别人授权，其它都能够干
--查询权限
SHOW GRANTS FOR programmer2 --查看指定用户的权限
--ROOT用户权限：GRANT ALL PRIVILEGES ON *.* TO 'root@localhost' WITH GRANT OPTION
--撤销权限
REVOKE ALL PRIVILEGES ON *.* TO programmer2 --撤销programmer2的全部权限
--删除用户：DROP USER 用户名
DROP USER programmer

2.MySQL备份

1）为什么要备份？
①保证重要的数据不丢失。
②数据转移
2）MySQL数据库备份
①直接拷贝物理文件。
②在Sqlyog等可视化工具中手动导出：在想要导出的表或者库中，右击选择备份/导出——>备份数据库，转储到SQL…

image

③使用命令行导出：使用mysqldump

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

④导入sql文件
Ⅰ登录的情况下，切换到指定的数据库。

#source 备份文件
source d:/b.sql

Ⅱ没有登录的情况下（本质上还是要输入用户名、密码登录）。

#mysql -u用户名 -p密码 库名<备份文件
mysql -uroot -p123456 school<D:/b.sql

导入和导出的使用范围：
Ⅰ备份数据库，防止数据丢失。
Ⅱ把数据库中内容给别人，可提供导出的sql文件。

九、规范数据库设计

1.为什么需要设计？

1）糟糕的数据库设计：
①数据冗余，浪费空间。
②数据库插入和删除都会很麻烦、出现异常（屏蔽使用物理外键）。
③程序的性能差。
2）良好的数据库设计：
①节省内存空间。
②保证数据库的完整性。
③方便开发系统。
3）软件开发中，关于数据库的设计：
①分析需求：分析业务和需要处理的数据库需求。
②设计概要：设计关系图E-R图。
4）设计数据库的步骤： 以个人博客为例
①收集信息，分析需求。
Ⅰ用户表（用户登录注销、用户的个人信息、写博客、创建分类）
Ⅱ分类表（文章分类、谁创建的）
Ⅲ文章表（文章的信息）
Ⅳ友链表（友链信息）
Ⅴ自定义表（系统信息、某个关键字或一些主字段）
②标识实体（把需求落实到每个字段）。
③标识实体之间的关系。
Ⅰ写博客：user——>blog
Ⅱ创建分类：user——>category
Ⅲ关注：user——>user
Ⅳ友链：links
Ⅴ评论：user——>user——>blog

2.三大范式

为什么需要数据规范？
不规范可能造成的问题：信息重复、更新异常、插入异常（无法正常显示信息）、删除异常（丢失有效信息）
1）第一范式（1NF）
原子性：保证每一列不可再分。
2）第二范式（2NF）
前提：满足第一范式。
每张表只描述一件事情。
3）第三范式（3NF）
前提：满足第一范式和第二范式。
需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
（规范数据库的设计）
参考博客园链接：https://www.cnblogs.com/wsg25/p/9615100.html

image

4）规范性和性能的问题

阿里规定：关联查询的表不得超过三张表。

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

参考博客：https://www.jianshu.com/p/56f5d2b9f7a5
参考博客：https://www.jianshu.com/p/218dcf24ad01
参考博客：https://www.jianshu.com/p/58ffb642926b
参考博客：https://www.jianshu.com/p/de543310c431
Ping开源：https://www.jianshu.com/u/50ea99bfc2b2

MySQL基础入门（2）

六、事务

七、索引

1.索引的分类

2.测试索引

3.索引原则

八、权限管理和备份

1.用户管理

2.MySQL备份

九、规范数据库设计

1.为什么需要设计？

2.三大范式

猜你喜欢

热点阅读