day04
存储引擎（了解）
存储引擎是Mysql中特有的一个术语，其他数据库没有。（oracle中有，但不叫这个名字）它是一个表存储/组织数据的方式。不同的存储引擎，表存储数据的方式不同。
怎么给表添加/指定“存储引擎”呢？
show create table t_student;可以查看创建表的代码
可以在建表的时候给表指定存储引擎。
CREATE TABLE `t_user` (
`id` int DEFAULT NULL,
`name` varchar(32) DEFAULT NULL,
`birth` date DEFAULT NULL,
`create_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
在建表的时候可以在最后小括号‘）’的右边使用：ENGINE来指定存储引擎，CHARSET来指定这张表的字符编码方式。
mysql默认存储引擎是：InnoDB。
mysql默认的字符编码方式是：UTF8
如何查看mysql支持那些存储引擎呢？
命令：show engines \G
mysql常用的存储引擎
🌲MYISAM存储引擎
它管理的表具有以下特征：
1、使用三个文件表示每个表
- 格式文件：存储表结构的定义（mytable.frm）
- 数据文件：存储表行内容（mytable.MYD）
- 索引文件：存储表上索引（mytable.MYI）。索引是一本书的目录，可以缩小扫描范围，提高查询效率
2、可被转换为压缩、只读表来节省空间（优势）
MYISAM不支持事务机制，安全性低
对于一张表来说，只要是主键或者添加了unique约束的字段会自动创建索引。
🌲InnoDB存储引擎
这是mysql默认的存储引擎，同时也是一个重量级的存储引擎
InnoDB支持事务，支持数据库崩溃后自动恢复机制。它主要特点是：非常安全。
它管理的表具有的主要特征
- 每个InnoDB表在数据库目录中以.frm格式文件表示
- InnoDB表空间tablespace被用于存储表的内容。表空间是一个逻辑名称，表空间存储数据+索引
- 提供一组用来记录事务性活动的日常文件
- 用COMMIT（提交）、SAVEPOINT、及ROLLBACK（回滚）支持事务处理
- 提供全ACID兼容
- 在MYSQL服务器崩溃后提供自动恢复
- 多版本（MVCC）和行级锁定
- 支持外键及引用的完整性，包括级联删除和更新
InnoDB最大的特点：支持事务，以保证数据安全，效率低。并且也不能压缩，不能转换为只读，不能很好的节省存储空间。
🌲MEMORY存储引擎
使用MEMORY存储引擎的表，其数据存储在内存中，且长度固定，这两个特点使得MEMORY存储引擎非常快。
MEMORY存储引擎管理表的特征：
- 在数据库目录内，每个表均以.frm格式文件表示
- 表数据及索引被存储在内存中（目的就是查询快，不需要和硬盘交互）
- 表级锁机制
- 不能包含TEXT或BLOB字段
MEMORY存储引擎以前被称为HEAP引擎。
优点：查询效率最高
缺点：不安全，关机之后数据消失，因为数据和索引都在内存中。
内存是直接取，程序在内存中直接运行，光速，电流的速度。从硬盘上取，硬盘是一种机械行为。
事务（重要）
1、一个事物就是一个完整的业务逻辑，是一个最小的工作单元，不可再分。
什么完整的业务逻辑？
- 假设转账。从A账户向B账户转账1000元
- 将A账户的钱减去1000（update语句）
- 将B账户的钱加上1000（update语句）
以上操作是一个最小的工作单元，要么同时成功，要么同时失败，不可再分。
2、只有DML语句才会有事务这一说，其他语句与事务无关。
insert、delete、update
因为只有以上三个语句是数据库表中数据进行增删改的，只要操作数据，就一定要考虑安全问题。数据安全是第一位的。
3、假设所有的业务，只要一条DML语句就能完成，还有必要存在事务机制吗？
没有。正是因为做某件事的时候，需要多条DML语句共同联合起来才能完成，所以需要事务的存在，如果任何一件复杂的事都能一条DML语句搞定，事务则没有存在的价值。
事务的本质：多条DML语句同时成功或者同时失败。
4、事务是如何做到多条DML语句同时成功或者同时失败的？
InnoDB存储引擎：提供一组用来记录事务性活动的日志文件。
事务开始了：
多条DML语句
事务结束了
🥝在事务的执行过程中，每一条DML的操作都会记录到“事务性活动的日志文件”中。
🥝在事务的执行过程中，我们可以提交事务，也可以回滚事务。
提交事务
- 清空事务性活动日志文件，将数据全部彻底持久化到数据库表中
- 标志着事务的结束，是一种全部成功的结束
回滚事务
- 将之前所有的DML操作全部撤销，并且清空事务性活动日志文件
- 标志着事务的结束，是一种全部失败的结束
5、怎么提交和回滚事务？
提交事务：commit;
回滚事务：rollback;回滚回到上一次提交的点。
事务：transaction
在mysql中默认的事务行为是：自动提交事务，即每执行一条DML语句，则提交一次。
这种自动提交实际上不符合开发习惯，因为一个业务通常是多条DML语句共同执行才能完成的，为了保证数据的安全，必须要求同时成功之后再提交，所以不能执行一条就提交一条。
怎么将mysql的自动提交机制关闭呢？
先执行命令：start transaction; 之后执行提交或者回滚操作
6、事务的特性
- A:原子性。
- 说明事务是最小的工作单元，不可再分。
- C:一致性。
- 在同一个事务中，所有操作必须同时成功或者同时失败，以保证数据的一致性。
- I：隔离性。（isolation）
- A事务和B事务之间有一定的隔离。A事务在操作一张表的时候，另一个事务B也操作这张表会怎样？多线程并发访问同一张表一样，会带来线程安全问题
- D：持久性。
- 事务最终结束的一个保障。事务提交，就相当于将没有保存到硬盘上的数据保存到硬盘上。
事务隔离级别
事务和事务之间的隔离级别
- 读未提交：read uncommitted（最低隔离级别）《没有提交就读到了》
- 事务A可以读取事务B未提交的数据，这种隔离级别存在的问题是：脏读现象，即读到了脏数据。这种隔离级别一般都是理论上的，大多数的数据库隔离级别都是二档起步。
- 读已提交：read committed 《提交之后才读到》
- 事务A只能读取到事务B提交之后的数据，这种隔离级别解决了脏读现象，但存在的问题是不可重复读取数据。因为在事务开启之后，假设第一次读到的数据是3条，当前事务还没结束，肯第二次再读取的时候，读到的数据是4条，3不等于4，所以称为不可重复读取。
- 这种隔离级别是比较真实的数据，第一次读到的数据是绝对真实，oracle数据库默认的隔离级别是：read committed
- 可重复读：repeatable read 《提交之后也读不到，永远读取的都是刚开启事务时的数据》
- 事务A开启后，不管是多久，每一次在事务A中读取到的数据都是一致的，即使事务B将数据已经修改，并且提交了，事务A读取到的数据还是没有发生改变，这就是可重复读了。解决了不可重复读取数据的问题。但可重复读存在的问题是可以会出现幻影读，每一次读到的数据都是幻想，不够绝对的真实！
- mysql中默认的事务级别是可重复读
- 序列化/串行化：serializable（最高隔离级别）
- 这是最高隔离级别，效率最低，解决了所有的问题。
- 这种隔离级别表示事务排队，不能并发。做了一个备份，可重复读，读的是备份数据。我操作你停，你操作时我不能操作。
- synchronized,线程同步（事务同步）
- 每一次读取到的数据都是最真实的，并且效率最低
- 例如：查询银行总账时，谁也不能存款和取款。
验证各种隔离级别
set global transaction isolation level read uncommitted;
设置全局的事务隔离级别为：读未提交
- read uncommitted(t_user是空表，事务A和事务B可以用两个命令行窗口表示)
| 事务A | 事务B |
|---|---|
| use bjpowernode; | |
| use bjpowernode; | |
| start transaction； | |
| select * from t_user;(空) | |
| start transaction; | |
| insert into t_user values(‘zhangsan’); | |
| select * from t_user;(有一条数据) |
- read committed(读已提交)一旦另一个事务提交了才能查到
set global transaction isolation level read uncommitted;
| 事务A | 事务B |
|---|---|
| use bjpowernode; | |
| use bjpowernode; | |
| start transaction; | |
| start transaction; | |
| select * from t_user;（空） | |
| insert into t_user values(‘zhangsan’); | |
| select * from t_user;（空） | |
| commit; | |
| select * from t_user;(有记录) |
- repeatable read（可重复读）可以重复读取，读到的数据都是幻想，是上一次提交之前的
set global transaction isolation level repeatable read;
| 事务A | 事务B |
|---|---|
| use bjpowernode; | |
| use bjpowernode; | |
| start transaction; | |
| start transaction; | |
| select * from t_user;（一条记录） | |
| insert into t_user values(‘zhangsan’); | |
| insert into t_user values(‘zhangsan’); | |
| commit; | |
| select * from t_user;（一条记录） |
只有事务A提交之后才能查到已被事务B更改的结果。
- serializable
set global transaction isolation level serializable；
| 事务A | 事务B |
|---|---|
| use bjpowernode; | |
| use bjpowernode; | |
| start transaction; | |
| start transaction; | |
| select * from t_user;(空) | |
| insert into t_user values(‘abc’); | |
| select * from t_user;（光标停了） | |
| commit; | |
| 事务Acommit;之后就可以查询到abc这条记了 |
exit可以在命令窗口的mysql退出来，cls清空
索引（index）
🌷索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。一张表的一个字段可以添加一个索引，多个字段联合起来也可以添加索引。
索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制。
select * from t_user where name = 'jack';
以上SQL语句会去name字段上扫描，因为查询条件是name = ‘jack’
如果name字段上没有添加索引，或者说没有给name字段创建索引，MySQL会进行全扫描，会将name字段上每一个值都对比一遍，效率比较低。
若name添加了索引，会根据索引进行检索，即有关name这一列的目录，索引需要排序，因为只有排序才会有区间查找，缩小扫描范围其实就是扫描某个区间。
MySQL在查询方面主要就是两种方式：
- 全表扫描
- 根据索引检索
在mysql数据库中，索引的排序和TreeSet数据结构相同。TreeSet（TreeMap）底层是一个自平衡的二叉树，在mysql当中索引是一个B-Tree数据结构。
遵循左小右大原则存放，采用中序遍历的方式遍历取数据。
🌷索引的实现原理
- 在任何数据库当中，主键上都会自动添加索引对象。另外，在mysql中，一个字段上如果有unique约束的话，也会自动创建索引对象。
- 在任何数据库当中，任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号
- 在mysql中，索引是一个单独的对象，不同的存储引擎以不同的形式存在，再MYISAM存储引擎中，索引存储在一个.MYI文件中；在InnoDB存储引擎中，索引存储在一个逻辑名称叫做tablespace当中；在MEMORY存储引擎中，索引被存储在内存中。不管索引存储在哪里，索引在mysql当中都是一个树的形式存在（自平衡二叉树：B-Tree）
- 表中每一行记录在硬盘上都有物理存储编号
- 表中字段不会动，索引对象会排序
select * from t_user where id = 101;
mysql发现id（主键）有索引对象，所以会通过索引对象index进行查找，索引是用二叉树进行排序，因此可以快速通idIndex索引对象定位到101，通过101得出物理编号，此时马上SQL语句转换。即：select * from t_user where 物理编号 = x;
索引的实现原理：；缩小扫描范围，避免全表扫描
什么条件下，我们会考虑给字段添加索引？
- 数据量庞大（多么庞大算庞大，需要测试，因为每一个硬件环境不同）
- 该字段经常出现在where后面，以条件的形式存在，也就是说这个字段总是被扫描
- 该字段很少进行DML操作（insert、delete、update）字段经常被修改新增删除，索引需要重新排序
建议不要随意添加索引，因为索引需要维护，添加多的话会拖累系统、降低系统的性能。
建议通过主键查询，或者通过unique约束的字段进行查询，效率会比较高。
🌷索引的创建和删除
创建索引：
create index ename_index on emp(ename);
🌷索引的创建和删除
给emp表的ename字段添加索引，起名：ename_index
删除索引：
drop index ename_index on emp;
将emp表上的ename_index索引对象删除
在mysql中，怎么查看一个SQL语句是否使用了索引进行检索？
用explain命令，在实际开发中，看看它底层有没有用索引进行检索。
explain select * from emp where ename = 'KING';
type = ALL 扫描记录为全表记录，则表明没有使用索引。
给ename添加索引之后，执行上面的语句
type = ref 扫描记录为1条，
🌷索引的失效
1、第一种情况
select * from emp where ename like '%T';
ename上即使添加索引，也不会走索引，为什么？
因为模糊查询以%开头的不能用索引进行检索，不知道第一个字母是啥。
尽量避免模糊查询的时候以‘%’开始，这是一种优化的手段/策略。
2、第二种情况
使用or的时候会失效，如果使用or，那么要求or两边的条件字段都要有索引，才会走索引，如果其中一边有一个字段没有索引，那么另一个字段上的索引就会失效。所以这就是为什么不建议使用or的原因。可以使用union
3、第三种情况
使用复合索引的时候，没有使用左侧的列查找，索引失效。
复合索引：两个字段，或者更多的字段联合起来添加一个索引
create index job_sal_index on emp(job,sal);
explain select * from emp where job = 'MANAGER';索引查找
explain select * from emp where sal = 800;全表查找
4、第四种情况
在where中索引列参加运算，索引失效
create index sal_index on emp(sal);
explain select * from emp where sal+1 = 801;
5、第五种情况
在where当中索引使用了函数
explain select * from emp where lower(ename) = 'smith';
索引是各种数据库进行优化的重要手段，优化的时候优先考虑的因数是索引。
🌷索引的分类
- 单一索引
- 复合索引
- 主键索引
- 唯一性索引（unique）
注意：唯一性比较的弱（可能会存在大量的重复数据）字段上添加索引用处不大，越唯一效率越高
视图（view）
1、视图的概述
view：站在不同的角度去看待同一份数据。
表复制
create table dept2 as select * from dept;
创建视图对象？
create view dept2_view as select * from dept2;
删除视图对象？
drop view dept2_view;
只有DQL语句才能以view的形式创建。
create view view_name as 这里的语句必须是DQL（select）语句。
2、视图的作用
我们可以面向视图对象进行增删改查，对视图对象的增删改查，会导致原表被操作。
面向视图查询
select * from dept2_view
面向视图插入
insert into dept2_view(deptno,dname,loc) values9(60,'SALES','BEIJING');
面向视图删除
delete from dept2_view;
查询原表数据
select * from dept2;(空)原表数据被更改
视图对象在实际开发中有什么用？方便，简化开发，利于维护
create view emp_dept_view as select e.ename,e.sal,d.dname from emp e join dept d on e.deptno = d.deptno;
假设有一条非常复杂的SQL语句，而这条SQL语句需要在不同的位置上反复使用。每一次使用这个SQL语句都需要重写编写，为了避免麻烦，怎么办？
可以把这条复杂的SQL语句的位置直接使用视图对象，可以大大简化开发。并且有利于后期的维护，因为修改的时候也只需修改一个位置就行，只需修改视图对象所映射的SQL语句。
面向视图开发的时候，使用视图的时候可以像使用table一样，可以对视图进行增删改查等操作，视图不是在内存当中，视图对象也是存储在硬盘上的，不会消失。
视图对应的语句只能是DQL，但是视图对象创建之后，可以对视图进行增删改查。
增删改查又叫做CRUD。CRUD是在公司中程序员之间的沟通术语，一般很少说增删改查。
- C:create(增)
- R:retrive(查、检索)
- U:update(改)
- D:delete(删)
DBA命令
导入数据
需要先登录到mysql数据库服务器上。
mysql -uroot -p123456
drop database bjpowernode;
create database bjpowernode;
use bjpowernode;
source d:\bjpowernode.sql初始化数据库
导出数据
在windows的DOS命令窗口中
mysqldump bjpowernode>d:\bjpowernode.sql -uroot -p123456
导出指定的emp这张表
mysqldump bjpowernode emp>d:\bjpowernode.sql -uroot -p123456
数据库设计的三范式
数据库设计范式：数据库表的设计依据。（面试官常问）
- 第一范式：要求任何一张表必须有主键，每一个字段原子性不可再分。
- 第二范式：建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，不要产生部分依赖
- 第三范式：建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，不要产生传递依赖
设计数据库表的时候，按照以上的范式进行，可以避免表中数据的冗余、空间浪费。
🥝数据库设计第一范式
最核心，最重要的范式，所有表的设计都需要满足。
必须有主键，并且每一个字段都是原子性不可再分。
| 学生编号 | 学生姓名 | 联系方式 |
|---|---|---|
| 1001 | 张三 | zs@163.com,13999999999 |
| 1002 | 李四 | ls@163.com,13599999999 |
| 1001 | 王五 | ww@163.com,13499999999 |
以上是学生表式，满足第一范式吗？
不满足，因为没有主键，联系方式可以分为邮箱地址和电话。修改，在学生编号加上主键，王五的学生编号改成1003，联系方式分成两个字段邮箱和电话。
🥝数据库设计第二范式
建立在第一范式的基础上，要求所有非主键字段必须完全依赖主键，不要产生部分依赖。
这张表描述了学生和老师的关系：一个学生可以有多个老师，一个老师可以有多个学生，多对多关系。
| 学生编号 | 教师编号 | 学生性名 | 教师姓名 |
|---|---|---|---|
| 1001 | 001 | 张三 | 王老师 |
| 1002 | 002 | 李四 | 赵老师 |
| 1003 | 001 | 王五 | 王老师 |
| 1001 | 002 | 张三 | 赵老师 |
满足第一范式吗？
不满足第一范式。修改：学生编号和教师编号联合做主键，复合主键（PK：学生编号+教师编号）
经过修改之后，以上表满足了第一范式，但是满足第二方式吗？
不满足，张三依赖1001，王老师依赖001，显然产生了部分依赖。产生部分依赖的缺点，数据冗余，空间浪费。比如：张三、王老师重复了。
为了让以上的表满足第二范式，需要这样设计：
使用三张表来表示多对多的关系！
学生表
| 学生编号（PK） | 学生姓名 |
|---|---|
| 1001 | 张三 |
| 1002 | 李四 |
| 1003 | 王五 |
教师表
| 教师编号（PK） | 教师姓名 |
|---|---|
| 001 | 王老师 |
| 002 | 赵老师 |
学生教师关系表
| id(PK) | 学生编号（FK） | 教师编号（FK） |
|---|---|---|
| 1 | 1001 | 001 |
| 2 | 1002 | 002 |
| 3 | 1003 | 001 |
| 4 | 1001 | 002 |
多对多怎么设计？
多对多，三张表，关系表两个外键。
🥝数据库设计第三范式
第三范式建立在第二范式的基础上，要求所有非主键字段必须直接依赖主键，不要产生传递依赖
| 学生编号（PK） | 学生姓名 | 班级编号 | 班级名称 |
|---|---|---|---|
| 1001 | 张三 | 01 | 一班 |
| 1002 | 李四 | 02 | 二班 |
| 1003 | 王五 | 03 | 三班 |
| 1004 | 赵六 | 04 | 三班 |
以上表是学生和班级的关系，一对多，一个教室中有多个学生。
- 满足第一范式，有主键
- 满足第二范式，因为主键不是复合主键，没有产生部分依赖，主键是单一主键
- 不满足第三范式，因为第三范式要求是，不要产生传递依赖，一班依赖01,01依赖1001，产生了传递依赖，不符合第三范式要求，产生了数据的冗余
班级表
| 班级编号（PK） | 班级名称 |
|---|---|
| 01 | 一班 |
| 02 | 二班 |
| 03 | 三班 |
学生表
| 学生编号（PK） | 学生姓名 | 班级编号（FK） |
|---|---|---|
| 1001 | 张三 | 01 |
| 1002 | 李四 | 02 |
| 1003 | 王五 | 03 |
| 1004 | 赵六 | 03 |
怎么设计一对多？
一对多，两张表，多的表加外键！
总结数据库表的设计
- 一对多：两张表，多的表加外键
- 多对多：三张表，关系表两外键
- 一对一：一张表，但是在实际开发中，可能存在一张表字段太多，这时要拆分表
例如：没有拆分之前，一张表
| id | login_name | login_pwd | real_name | address | ||
|---|---|---|---|---|---|---|
| 1 | zhangsan | 123 | 张三 | zs@163.com | … |
拆分之后：建议分为登录信息表和用户详细信息表
口诀：一对一，外键唯一（fk+unique）！
以满足客户需求为准
数据库设计三范式是理论上的，实践和理论有时候有偏差，最终的目的都是为了满足客户的需求，有时候会拿冗余换执行速度，因为SQL当中，表和表之间连接次数越多，效率越低。
有时候可能会存在冗余，但是为了减少表的连接次数，这样做也是合理的，并且对于开发人员来说，SQL语句的编写难度也会降低。
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
