MySQL(4)：事务+视图+触发器+索引+三大范式+数据库优化+数据的导入导出

文章目录一、事务二、视图三、触发器四、索引五、关系型数据库三大范式六、Mysql数据库的优化七、数据的导入和导出一、事务1.什么是事物将一组增删改查看成一个执行单元要么全成功要么有一个失败数据库就会回滚所有操作都不生效。2.事务的特性原子性将一组增删改看成一个整体要不全成功要不有一个失败数据库就回滚所有操作都不生效一致性事务开始前的状态和事务执行后的状态保持一致隔离性当多个用户并发访问数据库时线程会为用户开启一个事务事务与事务相互隔离互不干扰持久性一旦事务执行生效会永久运用到数据库即使设备故障也不影响3.如果不考虑隔离性会导致以下问题脏读当事务A访问了事务B未提交的数据并使用了它幻读当事务A访问了数据表事务B修改或删除了数据导致事务A读取与实际不符合类似出现幻觉不可重复读当事务A多次访问同一条数据事务B修改了它导致事务A两次读取的数据不一样4.四个隔离级别读未提交Read uncommitted最低隔离级别可能会出现所有情况读提交Read committed可以避免幻读可重复读Repeatable read可以避免幻读和不可重复读串行化Serialzable可以避免脏读、幻读和不可重复读。5.事务的三个基本操作开启事务begin提交事务commit回滚事务rollback6.事务的演示开启两个sql窗口,在cmd1窗口1开启一个事务插入一条数据但是先不提交在cmd窗口2查询user表发现数据没有发生变化当窗口1执行提交操作在窗口②重新查询user表发现新数据添加成功在窗口①重新开启一个事务执行删除id100的数据因为窗口1未提交所以窗口②查询不到在表1处执行回滚操作数据恢复注意是未提交之前二、视图1.为什么使用视图当查询的复杂度很高时指多字段多关系查询会导致sql语句混乱视图是一张虚拟的表有字段和数据只存放查询语句但是视图是基于源表的2.优点简单视图构建了一个虚拟的表表里的数据是来源于复杂的查询语句我们将复杂的查询语句存入视图使用是直接调用视图安全数据具有对库和表的权限管理但是没有对字段权限可以通过视图来实现权限的功能数据的独立性视图基于源表当源表的结构发生变化时不会对视图的结构产生影响3.缺点使用视图时一般存的都是复杂的查询如果存的是简单的查询在使用视图时会作为复杂查询来去执行会降低查询效率会增加数据库的维护和管理成本会对数据迁移造成很大影响4.视图的命令操作操作标准写法等价替代写法说明创建视图CREATE VIEW 视图名 AS SELECT 语句;CREATE OR REPLACE VIEW 视图名 AS SELECT 语句;后者会覆盖已存在的同名视图更安全修改视图ALTER VIEW 视图名 AS SELECT 语句;CREATE OR REPLACE VIEW 视图名 AS SELECT 语句;两种写法完全等价删除视图DROP VIEW IF EXISTS 视图名;无IF EXISTS避免视图不存在时报错查看视图定义SHOW CREATE VIEW 视图名;SELECT * FROM information_schema.VIEWS WHERE table_schema库名 AND table_name视图名;后者可批量查询所有视图查看视图结构DESC 视图名;DESCRIBE 视图名;简写 / 全写等价5.使用例如我们在school数据库中的user表创建一个视图user_show来查看我们user的所有信息添加视图create view user_show as(select * from user);查看所有的视图show table status where commentview\G;使用视图select * from user_show;删除视图drop view user_show;三、触发器1.定义触发器(trigger)也叫触发程序触发器是一种特殊的存储过程但是触发器不需要语句调用也不需要手动启动通过有增删改事件触发当指定表发生指定动作将激活触发器。2.触发器的命令操作操作标准写法创建触发器CREATE TRIGGER 触发器名 触发时机(BEFORE/AFTER) 触发事件(INSERT/UPDATE/DELETE) ON 表名 FOR EACH ROW 触发器逻辑;修改触发器无需先删后建删除触发器DROP TRIGGER IF EXISTS 触发器名;查看所有触发器SHOW TRIGGERS;查看指定触发器SELECT * FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA库名 AND TRIGGER_NAME触发器名;四、索引1.为什么要使用索引优点索引是一种高效获取数据的存储结构类似于子字典的目录。可以提升数据的查询速度减少io操作缺点创建索引时数据库会额外的创建一张表来维护索引会占用磁盘资源一般索引的空间比数据大注意添加索引可以增加查询的速度但是不是索引越多越好2.索引的分类索引分类特点普通索引index可以为空 可以重复 当我们使用物理外键时就默认给该字段添加了普通索引唯一索引unique可以为空 不能重复主键索引primary key不能为空且唯一多列索引index()将多个字段绑定到一起添加一个索引3. 索引的操作操作标准写法等价替代写法说明创建普通索引CREATE INDEX idx_name ON 表名(字段名);ALTER TABLE 表名 ADD INDEX idx_name(字段名);不指定索引名时默认以字段名为索引名创建唯一索引CREATE UNIQUE INDEX uk_name ON 表名(字段名);ALTER TABLE 表名 ADD UNIQUE uk_name(字段名);不能用于主键主键是特殊唯一索引创建主键索引ALTER TABLE 表名 ADD PRIMARY KEY(字段名);无一张表只能有一个主键必须唯一非空创建组合索引CREATE INDEX idx_name_age ON 表名(字段1, 字段2);ALTER TABLE 表名 ADD INDEX idx_name_age(字段1, 字段2);遵循最左前缀原则删除普通索引DROP INDEX idx_name ON 表名;ALTER TABLE 表名 DROP INDEX idx_name;两种写法完全等价删除主键索引ALTER TABLE 表名 DROP PRIMARY KEY;无自增主键需先删除AUTO_INCREMENT再执行查看索引SHOW INDEX FROM 表名;SELECT * FROM information_schema.STATISTICS WHERE table_schema库名 AND table_name表名;后者是通用写法兼容所有 MySQL 版本五、关系型数据库三大范式范式名称英文缩写前置条件核心要求解决问题核心目标第一范式1NF表拥有唯一主键列属性值为不可再分的原子单位无复合 / 多值列、无重复列组解决列的拆分问题保证数据存储的原子性是所有范式的基础第二范式2NF满足第一范式1NF非主键列完全依赖于整个主键而非仅依赖联合主键的某一部分解决部分依赖问题消除联合主键场景下的部分数据冗余第三范式3NF满足第二范式2NF非主键列之间无传递关系仅直接依赖于主键解决传递依赖问题彻底消除非主键列引发的冗余和更新异常实际业务中无需严格死守 3NF可根据查询效率需求做反范式设计适度冗余非主键列减少多表联查平衡「数据一致性」和「查询性能」。六、Mysql数据库的优化1.为什么要优化一个应用吞吐量瓶颈往往出现在数据库的处理速度上随着应用程序的使用和业务的拓展数据库数据量逐渐增多数据库处理压力逐渐增大关系型数据库数据存放在磁盘上的读写速度较慢与内存中的数据相比2.优化方式架构优化使用缓存把经常访问到的数据而且不需要经常变化的数据放在缓存中能节约磁盘IO;主从分离读写;采用主从复制把数据库的读操作和写入操作分离开来;优化硬件;库和表结构的优化垂直分库分表把一些不经常读的数据或者结果复杂的表拆分成多张表较少磁盘I/O操作;水平分库分表于数据量庞大的表使用水平分库分表语句优化设计表的时候严格根据数据库的设计范式来设计数据库;select 后尽量不使用*尽量不使用嵌套查询使用连接查询或者where查询sql关键词尽量使用大写尽量使用逻辑外交不使用物理外键给查询频繁的字段添加索引并且遵循最左原则尽量将首选关键字段放在最前边七、数据的导入和导出1.数据表导出备份表mysqldump-u root-p 库名 表名备份的表名.sql2.数据库的导出实际上是一次导入多张表只不过表在一个sql文件备份库mysqldump-u root-p 库名备份的库名.sql3.数据的导入导入库需要先创建输入库因为实际是导入表必须重新创入个新的数据库然后再导入mysql-uroot-p 库名备份的文件.sql