一 事务
核心:要么都成功,要么都失败
原子性(Atomicity)
要么都成功,要么都失败
一致性(Consistency)
事务前后的数据完整性要保证一致 ,1000
原则:ACID
1.原子性(Atomicity)
要么都成功,要么都失败;
2.一致性(Consistency)
事务前后的数据完整性要保证一致;
3.持久性(Durability)
— 事务提交
事务一旦提交则不可逆,被持久化到数据库中!
4.隔离性(Isolation)
事务的隔离性是多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数据所干扰,多个并发事务之间要相互隔离。
5.隔离所导致的一些问题
-
脏读:
指一个事务读取了另外一个事务未提交的数据。 -
不可重复读:
在一个事务内读取表中的某一行数据,多次读取结果不同。(这个不一定是错误,只是某些场合不对) -
虚读(幻读)
是指在一个事务内读取到了别的事务插入的数据,导致前后读取不一致。
执行事务:
-- 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 保存点名 -- 撤销保存点
模拟转账事务
-- 转账
-- 1、SQL 执行 A 给 B 转账 A 1000 ---> B 500
-- 2、SQL 执行 B 收到 A 的钱 A 500 ---> B 500
-- 3.总共是1000总额不变
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 = 1; -- 恢复默认值
二 权限和备份
1.可视化
2.命令操作
-- 创建用户 CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER panghu IDENTIFIED BY '123456'
-- 修改密码 (修改当前用户密码)
SET PASSWORD = PASSWORD('123456')
-- 修改密码 (修改指定用户密码)
SET PASSWORD FOR panghu = PASSWORD('123456')
-- 重命名 RENAME USER 原来名字 TO 新的名字
RENAME USER panghu TO erdan
-- 用户授权 ALL PRIVILEGES 全部的权限 , 库.表
-- ALL PRIVILEGES 除了给别人授权,其他都能够干
GRANT ALL PRIVILEGES ON *.* TO erdan
-- 查询权限
SHOW GRANTS FOR erdan -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost
-- ROOT用户权限:GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
-- 撤销权限 REVOKE 哪些权限, 在哪个库撤销,给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM erdan
-- 删除用户
DROP USER erdan
3.备份
1.保证重要的数据不丢失或者数据转移;
2.MySQL 数据库备份的方式
- 直接拷贝物理文件
- 在Sqlyog 这种可视化工具中手动导出
使用命令行导出 mysqldump 命令行使用
# mysqldump -h 主机 -u 用户名 -p 密码 数据库 表名 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school 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密码 库名< 备份文件
三 索引
MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。提取句子主干,就可以得到索引的本质:索引是数据结构。
** 1、索引的分类**
在一个表中,主键索引只能有一个,唯一索引可以有多个
- 主键索引 (PRIMARY KEY )
- 唯一的标识,主键不可重复,只能有一个列作为主键
- 唯一索引 (UNIQUE KEY)
- 避免重复的列出现,唯一索引可以重复,多个列都可以标识位 唯一索引
- 常规索引 (KEY/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('刘');
2、测试索引
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万数据 55.176 sec
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),'24736743@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;
SELECT mock_data();
SELECT * FROM app_user WHERE `name` = '用户9999'; -- 0.993 sec
SELECT * FROM app_user WHERE `name` = '用户9999'; -- 1.098 sec
SELECT * FROM app_user WHERE `name` = '用户9999'; -- 0.788 sec
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';
SELECT * FROM student
-- id _ 表名 _ 字段名
-- CREATE INDEX 索引名 on 表(字段)
CREATE INDEX id_app_user_name ON app_user(`name`);
SELECT * FROM app_user WHERE `name` = '用户9999'; -- 0.001 sec
SELECT * FROM app_user WHERE `name` = '用户9999';
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';
索引在小数据量的时候,用户不大,但是在大数据的时候,区别十分明显~
3、索引原则
- 索引不是越多越好
- 不要对进程变动数据加索引
- 小数据量的表不需要加索引
- 索引一般加在常用来查询的字段上!
索引的数据结构:
Hash 类型的索引~
Btree : InnoDB 的默认数据结构~
四 规范
第一范式(1NF)
原子性 : 保证每一列不可再分
第二范式(2NF)
前提:满足第一范式
每张表只描述一件事情
第三范式(3NF)
前提:满足第一范式 和 第二范式
第三范式需要确保数据表中的每一列数据都和主键直接相关,而不能间接相关。
(规范数据库的设计)
规范性 和 性能的问题
关联查询的表不得超过三张表
- 考虑商业化的需求和目标,(成本,用户体验!) 数据库的性能更加重要
- 在规范性能的问题的时候,需要适当的考虑一下 规范性!
- 故意给某些表增加一些冗余的字段。(从多表查询中变为单表查询)
- 故意增加一些计算列 (从大数据量降低为小数据量的查询:索引)