MySQL基础 | Others

1.事务

• 要么都成功要么都失败
• A给B转钱要确定B能收到，A的钱同时减少
• mysql默认开启事务

1.1AOID原则

1. 原子性Atomicity、事务里的操作要么全做，要么全不做
2. 一致性Consistency、数据无论怎么去操作最终的数据要保持一致
3. 独立性Isolation、并发的事务之间不存在互相干扰
4. 持久性Durablity、一但事务提交，他所做的修改会永远保存在数据库上，出现宏机也不会丢失

1.2并发控制

独立性所导致的问题：

1. 脏读：指一个事务读取另外一个事务未提交的数据.

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

3. 虚读（幻读）：是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致.

1.3事务代码层

-- 事务操纵
set autocommit = 0; -- 关闭自动提交
start transaction;  -- 开启一个事务
-- 事务里面操纵的数据(原子性)
--1减少50元
update cusinfo set money = money-50 where id='1';
--2增加50元
update cusinfo set money = money+50 where id='2';

commit; -- 提交(持久性)
rollback; -- 回滚(恢复操作前数据)

set autocommit = 1; -- 开启自动提交

2.索引

• 索引是帮助MySQL高效获取数据的数据结构
• 小量数据体验不到索引的作用，大量数据使用索引的效率会大幅度提升
• 索引并不是越多越好,不要对进程变动数据加索引
• 小数据量的表不要加索引,索引一般加在常用字段上

2.1索引分类

1. 主键索引(PRIMARY KEY)、唯一的标识，主键不可重复，只能有一个列作为主键
2. 唯一索引(UNIQUE KEY)、避免出现重复的列，唯一索引可以重复，多个列都可以标识位唯一索引
3. 常规索引(KEY/INDEX)、默认的，index，key关键词来设置
4. 全文索引(FULLTEXT)、MYSIAM引擎下才有，快速定位数据

2.2创建方式

一、创建表时创建

CREATE TABLE `student` (
  `StudentNo` int(4) NOT NULL COMMENT '学号',
  `IdentityCard` varchar(18) DEFAULT NULL COMMENT '身份证号',
  -- 添加索引
  PRIMARY KEY (`StudentNo`),
  UNIQUE KEY `IdentityCard` (`IdentityCard`),
  KEY `Email` (`Email`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

二、通过ALTER语句

ALTER TABLE 数据库.表 ADD 索引类型 INDEX `索引名` (`列名`);

三、通过CREATE INDEX语句

CREATE INDEX `索引名` ON 表名 (`列名`);

3.用户与备份

用户操作语句

-- 创建用户
create user 用户名 identified by '密码';

-- 设置指定用户密码
set password for 用户名 = password('密码');

-- 设置当前用户密码
set password = password('密码');

-- 重命名用户名
RENAME USER 旧用户名 TO 新用户名;

-- 给用户赋予最高权限除GRANT
GRANT ALL PRIVILEGES ON *.* TO 用户名;

-- 查看用户权限
SHOW GRANTS FOR 用户名;

-- 撤销权限 撤销哪些权限 在哪个库撤销 给谁撤
revoke all privileges on *.* from 用户名;

-- 删除指定用户
drop user 用户名;

备份语句

-- 除了以下语句或指令集操作外，还可使用SQLyog等可视化工具完成
-- 备份
-- 备份指定数据库下的表
mysqldump -h主机名 -u用户名 -p密码 数据库 表1 表2 表3 >物理磁盘位置/名称.sql
-- 备份整个数据库
mysqldump -h主机名 -u用户名 -p密码 数据库 >物理磁盘位置/名称.sql

-- 恢复在sql语句种操作
-- 先登录
mysql -u -p
-- 选择要恢复到的数据库下面
use db
-- 恢复
source 盘符:/备份文件

4.规范设计

1. DB的设计是软硬件结合，技术与管理的界面就是干件
2. DB的设计需和应用系统相结，整个设计过程中结构(数据)设计和行为(处理)设计即使分别进行的，又是密不可的
3. DB的设计涉及多学科，当构建一个大型数据库时其开发周期长，耗资多，风险大

4.1规范与性能

• 阿里开发手册 关联的表不能超过三张
• 考虑商业化需求和目的，数据库的性能更重要
• 规范性能问题时候，需适当考虑规范性
• 故意给某些表增加一些冗余的字段(从多表查询到单表查询)
• 故意增加一些计算列(大数据量降为小数据量的查询:索引)

4.2三大范式

• 1NF：满足原子性:保证每一列不可再分
• 2NF：满足1NF，且每张表只做一件事情
• 3NF：满足1NF和2NF，且确保数据库中的每一列数据都和主键直接关联，而不是间接关联