小王学习MySQL之旅Day07

等我千分就网恋

于 2020-05-21 16:16:36 发布

阅读量394

点赞数

分类专栏： Mysql

本文链接：https://blog.csdn.net/weixin_46976101/article/details/106260266

版权

Mysql 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

学习数据库第七天

一.事务（Transaction）

1.什么是事务

要么都成功，要么都失败

SQL 执行 A 给 B 转账 A 1000 —> 200 B 200
SQL 执行 B 收到A 的钱 A 800 ----> B400

将一组SQL放在一个批次中执行

事务原则：ACID原则原子性（Atomicity），一致性（Consistency），隔离性（Isolation），持久性（Durability）

原子性（Atomicity）
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性（Consistency）
事务前后数据的完整性必须保持一致。
隔离性（Isolation）
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
持久性（Durability）
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

隔离导致的一些问题

脏读：

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

不可重复读：

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

虚读（幻读）：

是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。（一般是行影响，多了一行）

执行事务

-- 事务
-- MYSQL 是默认开启事务并自动提交的
SET autocommit = 0 -- 关闭
SET autocommit = 1 -- 开启
START TRANSACTION  -- 标记一个事务的开启，从这个之后的SQL都在同一个事务内
INSERT xx
INSERT xx
-- 提交 ： 持久化（成功！）
COMMENT
-- 回滚 ： 回到原来的样子（失败！）
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(20) 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'  -- B加500
COMMIT; -- 提交事务
ROLLBACK; -- 回滚
SET autocommit = 1; -- 开启自动提交

二：索引

1.索引的分类

MYSQL官方对索引的定义为：是帮助MYSQL高效的获取数据的数据结构

2.分类

主键索引（PRIMARY KEY）

.唯一的标识，主键不可重复，只能有一个列作为主键
唯一索引 (UNIQUE KEY)

避免重复的列出现，可以重复，多个列都可以表示为唯一索引
常规索引 (KEY/INDEX)

默认的，
全文索引（FullText）

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

基础语法

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

-- 增加一个全文索引
ALTER TABLE 表名 ADD FULLTEXT INDEX 索引名(列名);

-- 分析SQL执行的状况
EXPLAIN SELECT * FROM 表名 ;  -- 非全文索引
SELECT * FROM 表名 WHERE MATCH(索引名) AGAINST('');

3.测试索引

-- 测试索引

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 NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`)

) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 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),
	'1013771438@qq.com',
	CONCAT('182',FLOOR(RAND()*((99999999-10000000)+10000000))),
	FLOOR(RAND()*2),
	UUID(),
	FLOOR(RAND()*100)
 
	);
	SET i =i+1;
	
	END WHILE;
	RETURN i;
END;
 SELECT mock_data();
 -- 没加索引前
 SELECT * FROM app_user WHERE `name` = '用户9999'; -- 1.329 sec
 SELECT * FROM app_user WHERE `name` = '用户99999'; -- 1.439 sec
 SELECT * FROM app_user WHERE `name` = '用户999999'; -- 1.423 sec
 
 EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';
 -- id_表名_字段名
 -- CREATE INDEX 索引名 on 表（字段）
 CREATE INDEX id_app_user_name ON app_user(`name`);
 -- 加了索引后
 SELECT * FROM app_user WHERE `name` = '用户999999'; -- 0.006 sec

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

索引原则

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

索引的数据结构

Hsah 类型的索引

Btree ： INNODB 的默认的数据结构

三. 数据库权限管理和备份

1.用户管理

SQL yog 可视化管理

SQL 命令操作

用户表：mysql.user

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

-- 创建用户  CREATE USER 用户名 IDENTIFIED BY 密码
 CREATE USER wangxu IDENTIFIED BY '123456'
 
 -- 修改当前用户密码
 SET PASSWORD = PASSWORD('123456')
 -- 修改指定用户密码
 SET PASSWORD FOR wangxu = PASSWORD('654321')
 
 -- 重命名
 RENAME USER wangxu TO wangxu2
 
 -- 授予全部权限
 GRANT ALL PRIVILEGES ON *.* TO wangxu2
 
 -- 查看权限
  SHOW GRANTS FOR wangxu2   -- 查看指定用户权限（GRANT ALL PRIVILEGES ON *.* TO 'wangxu2'@'%'）
  SHOW GRANTS FOR root@localhost -- 查看管理员权限（GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION）
  
  -- 撤销权限
  REVOKE ALL PRIVILEGES ON *.* FROM wangxu2
  -- 删除用户
  DROP USER wangxu

2.数据库备份

保证重要的数据不丢失

可以做数据转移

MySQL数据库备份方式

拷贝物理文件

拷贝data文件
在SQLyog 这种可视化工具中手动导出

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

# mysqldump -h主机 -u 用户名 -p 密码 数据库 表名 >物理磁盘位置/文件名
C:\Users\lenovo>mysqldump -hlocalhost -uroot -p123456 school >D:/a.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

# 导入
# 先登录  然后再导入
source 备份文件
C:\Users\lenovo>mysqldump -hlocalhost -uroot -p123456 school <D:/a.sql

四.规范数据库设计

糟糕的数据库设计：

数据冗余，浪费空间
数据库插入和删除都会很麻烦，异常[屏蔽使用物理外键]
程序性能差

良好的数据库设计

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

软件项目开发周期中数据库设计 :

需求分析阶段: 分析客户的业务和数据处理需求

概要设计阶段:设计数据库的E-R模型图 , 确认需求信息的正确和完整.

设计数据库步骤

收集信息

与该系统有关人员进行交流 , 座谈 , 充分了解用户需求 , 理解数据库需要完成的任务.

标识实体[Entity]

标识数据库要管理的关键对象或实体,实体一般是名词

标识每个实体需要存储的详细信息[Attribute]

标识实体之间的关系[Relationship]
三大范式

第一范式 (1st NF)

第一范式的目标是确保每列的原子性,如果每列都是不可再分的最小数据单元,则满足第一范式

第二范式(2nd NF)
第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式
第二范式要求每个表只描述一件事情

第三范式(3rd NF)
如果一个关系满足第二范式,并且除了主键以外的其他列都不传递依赖于主键列,则满足第三范式.
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

规范化和性能的关系
为满足某种商业目标 , 数据库性能比规范化数据库更重要
在数据规范化的同时 , 要综合考虑数据库的性能
通过在给定的表中添加额外的字段,以大量减少需要从中搜索信息所需的时间
通过在给定的表中插入计算列,以方便查询

等我千分就网恋

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
小王学习MySQL之旅Day07

学习数据库第七天一.事务（Transaction）1.什么是事务要么都成功，要么都失败SQL 执行 A 给 B 转账 A 1000 —> 200 B 200SQL 执行 B 收到A 的钱 A 800 ----> B400将一组SQL放在一个批次中执行事务原则：ACID原则原子性（Atomicity），一致性（Consistency），隔离性（Isolation），持久性（Durability）原子性（Atomicity）原子性是指事务
复制链接

扫一扫