MySQL索引约束设计事物视图
一、索引
MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。
1.普通索引
- 创建索引
CREATE INDEX indexName ON table_name (column_name)- 修改表结构（添加索引）
ALTER table tableName ADD INDEX indexName(columnName)- 创建表的时候直接指定
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) );- 删除索引
DROP INDEX indexName ON mytable;2.唯一索引
它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：
- 创建索引
CREATE UNIQUE INDEX indexName ON mytable(username(length))- 修改表结构
ALTER table mytable ADD UNIQUE [indexName] (username(length))- 创建表的时候直接指定
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) );- 使用alter命令添加和删除索引
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): -- 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL， ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): -- 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。 ALTER TABLE tbl_name ADD INDEX index_name (column_list): -- 添加普通索引，索引值可出现多次。 ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list): -- 该语句指定了索引为 FULLTEXT ，用于全文索引。 -- add换为drop就是删除- 使用alter命令添加和删除主键
主键作用于列上（可以一个列或多个列联合主键），添加主键索引时，你需要确保该主键默认不为空（NOT NULL）：
ALTER TABLE 表名 MODIFY 主键名 INT NOT NULL; ALTER TABLE 表名 ADD PRIMARY KEY (主键名); -- add改为drop就是删除ALTER TABLE 表名 DROP PRIMARY KEY;删除主键的时候只需要指定primary key，但在删除索引的时候必须要知道索引名。
- 显示索引信息
你可以使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 \G 来格式化输出信息。
SHOW INDEX FROM table_name;二、约束
对于表中的数据进行限定，保证数据的正确性，有效性和完整性
分类：
- 主键约束：primary key
- 非空约束：not null
- 唯一约束：unique
- 外键约束：foreign key
1.非空约束：not null，值不能为null
这里以具体例子讲述sql语言，会比较好理解一点
- 创建表时添加约束
CREATE TABLE stu( id INT, NAME VARCHAR(20) NOT NULL -- name为非空 );- 建立表后，添加非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL- 删除name非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(25);2.唯一约束：unique，值不能重复
- 创建表时添加唯一约束
CREATE TABLE stu( id INT, phone_num VARCHAR(20) UNIQUE -- 添加了唯一的约束 );注意mysql中，唯一约束限定的列值可以有多个null
mysql默认也会对unique的列建立索引
- 删除唯一约束
alter table stu modify phone_num varchar(20); -- 若无法删除可先将索引删除 ALTER TABLE stu DROP INDEX phone_num;- 创建表后，添加唯一约束
ALTER TABLE stu MODIFY phone_nume VARCHAR(20) UNIQUE;3.主键约束：primary key
主键是非空且唯一的，就是表中记录的唯一标识
- 创建表时，添加主键约束
CREATE TABLE stu ( id INT PRIMARY KEY, -- 给id添加主键约束 NAME VARCHAR(20) );- 删除主键
ALTER TABLE stu DROP PRIMARY KEY; -- 去除主键 alter table stu modify id int; -- 移除not null的限约束- 创建完表后，添加主键
ALTER TABLE stu MODIFY id INT PRIMARY KEY;- 自动增长
含义：主键自动增长 = 插入数据时，主键自动+1，不用手动指定，使用auto_increment完成自动增长
CREATE TABLE stu( id INT PRIMARY KEY AUTO_INCREMENT, -- 给id添加主键约束 并完成主键自动增长 NAME VARCHAR(20) );1）删除自动增长
ALTER TABLE stu MODIFY id INT;2）添加自动增长
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;4.外键约束：foreign key，让表与表产生关系，从而保证数据的正确性
- 创建表时，添加外键
create table 表名( ... 外键列 constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称) );- 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;- 创建表后添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键列名称) REFERENCES 主表名称(主表列名称) ;- 级联操作
1）添加级联操作
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键列名称) REFERENCES 主表名称（主表列名称） ON UPDATE CASCADE ON DELETE CASCADE;2）分类
级联更新：ON UPDATE CASCADE 级联删除：ON DELETE CASCADE三、数据库的设计
1.多表之间的关系
2.实现关系
3.数据库的设计的范式
设计数据库时，需要遵循的一些规范。要遵循后边的范式要求，必须先遵循前边的所有范式要求。
基本表及其字段之间的关系, 应尽量满足第三范式。
但是，满足第三范式的数据库设计，往往不是最好的设计。
为了提高数据库的运行效率，常常需要降低范式标准：适当增加冗余，达到以空间换时间的目的。
- 第一范式(确保每列保持原子性)
第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。
第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式，如下表所示。
该表遵循了第一范式的要求，这样用户使用城市进行分类的时候就非常方便，也提高了数据库的性能
- 第二范式(确保表中的每列都和主键相关)
第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
比如要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键，如下表所示。
这个表中是以订单编号和商品编号作为联合主键，这样在该表中商品名称，单位，商品价格等信息不与这个表的主键相关，而仅仅是与商品编号相关，这里违反了第二范式的设计原则。
对上表进行拆分，把商品信息分离到另一个表中，把订单项目表也分离到另一个表中
如上，就很大程度减小了数据库的冗余，获取订单的商品信息使用商品编号到商品信息表中查询就行
- 第三范式(确保每列都和主键列直接相关,而不是间接相关)
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。如下面这两个表所示的设计就是一个满足第三范式的数据库表。
查询订单信息的时候，就可以使用客户编号来引用客户信息表中的记录，也不用再订单信息表中多次输入客户信息的内容，减小数据冗余
四、事务
如果一个包含多个步骤的业务操作，被事物管理，那么这些操作要么同时成功，要么同时失败
1.开启事物&回滚&提交
--建表 CREATE TABLE account( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(10), balance DOUBLE ); --插入数据 INSERT INTO account(NAME,balance) VALUES ('zhangsan',1000),('lisi',1000); SELECT * FROM account; -- 张三给李四转账500元 -- 0.开启事务 START TRANSACTION; -- 1.张三账户 -500 UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan'; -- 2.李四账户 + 500 UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi'; -- 出错了/没出错... -- 发现没有问题了，提交事务 COMMIT; -- 发现出问题了，回滚事务 ROLLBACK;2.mysql数据库中事物默认自动提交，事物提交有两种方式
3.修改事物的默认提交方式
--查看事务的默认提交方式： SELECT @@autocommit; -- 1 代表自动提交 0 代表手动提交 --修改默认提交方式： SET @@autocommit = 0;4.事物的四大特征ACID
5.事物的隔离级别（了解）
多个事务之间隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。
注意：隔离级别从低到高到安全性越来越高，但是效率越来越低
查询数据库隔离级别：select @@tx_isolation;
数据库设置隔离级别：set global transaction isolation level 级别字符串;
- 脏读：是指在一个事务处理过程中读取了另一个未提交的事物中的数据
- 不可重复读：不可重复读是指在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了。
- 虚读（幻读）：是事务非独立执行时发生的一种现象。例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。
幻读和不可重复读都是读取了另一条已经提交的事务，与脏读不同，不可重复查询的都是同一个数据，而幻读针对的是一批数据的整体。
五、视图
视图是基于 SQL 语句的结果集的可视化的表，即视图是一个虚拟存在的表，可以包含表的全部或者部分记录，也可以由一个表或者多个表来创建。使用视图就可以不用看到数据表中的所有数据，而是只想得到所需的数据。当我们创建一个视图的时候，实际上是在数据库里执行了SELECT语句，SELECT语句包含了字段名称、函数、运算符，来给用户显示数据。使用视图查询可以使查询数据相对安全，通过视图可以隐藏一些敏感字段和数据，从而只对用户暴露安全数据。视图查询也更简单高效，如果某个查询结果出现的非常频繁或经常拿这个查询结果来做子查询，将查询定义成视图可以使查询更加便捷。
1.视图的用法
视图创建
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = user] [SQL SECURITY { DEFINER | INVOKER }] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]- OR REPLACE：表示替换已有视图，如果该视图不存在，则CREATE OR REPLACE VIEW与CREATE VIEW相同。
- ALGORITHM：表示视图选择算法，默认算法是UNDEFINED(未定义的)：MySQL自动选择要使用的算法 ；merge合并；temptable临时表，一般该参数不显式指定。
- DEFINER：指出谁是视图的创建者或定义者，如果不指定该选项，则创建视图的用户就是定义者。
- SQL SECURITY：SQL安全性，默认为DEFINER。
- select_statement：表示select语句，可以从基表或其他视图中进行选择。
- WITH CHECK OPTION：表示视图在更新时保证约束，默认是CASCADED。
一般用法创建
CREATE VIEW 视图名 AS SELECT 查询语句;修改视图
ALTER VIEW 视图名 AS SELECT 查询语句;删除视图
DROP VIEW 视图名;注意：
要通过视图更新基本表数据，必须保证视图是可更新视图，即可以在INSET、UPDATE或DELETE等语句当中使用它们。对于可更新的视图，在视图中的行和基表中的行之间必须具有一对一的关系。还有一些特定的其他结构，这类结构会使得视图不可更新。一般情况下不建议对视图做DML操作。