2017年07月07号课堂笔记

2017年07月07号 星期五 晴 空气质量：良

内容：MySQL第六节课

事务的隔离级别：几种不确定的情况(更新丢失、脏读、不可重复读)，SQL规范定义4个事务隔离级别（未授权读取、授权读取、可重复读取、序列化），事务和JDBC事务隔离级别；

视图（注意3补充说明）；

索引；类型、原则、注意事项；

 sql 语句的执行 顺序；

数据库的恢复和备份；

数据库设计：设计步骤、E-R图、三大范式

 

一、事务的隔离级别

1、为什么引入了事务隔离级别？？

在数据库操作中，为了有效保证并发读取数据的正确性，提出的事务隔离级别。

2、数据库操作过程中可能出现以下几种不确定的情况：

1）更新丢失

两个事务都同时更新一行数据，一个事务对数据的更新把另一个事务对数据的更新覆盖了。这是因为系统没有执行任何的锁操作，因此并发事务并没有被隔离开来。
2）脏读
一个事务读取到了另一个事务未提交的数据操作结果。这是相当危险的，因为很可能所有的操作都被回滚。

 

3）不可重复读
不可重复读（Non-repeatable Reads）：一个事务对同一行数据重复读取两次，但是却得到了不同的结果。
包括以下情况：
①虚读：事务T1读取某一数据后，事务T2对其做了修改，当事务T1再次读该数据时得到与前一次不同的值。
②幻读（Phantom Reads）：事务在操作过程中进行两次查询，第二次查询的结果包含了第一次查询中未出现的数据或者缺少了第一次查询中出现的数据（这里并不要求两次查询的SQL语句相同）。这是因为在两次查询过程中有另外一个事务插入数据造成的。

 

3、解决方案：  使用事务隔离级别

 

1）案例一： 允许脏读取

-- 查询mysql默认的事务隔离级别 (可重复读取 repeatable-read )
SELECT @@tx_isolation;

 

-- 修改事务 隔离级别
SET tx_isolation='read-uncommitted'; 允许脏读取，不允许更新丢失

2）案例二： 禁止更新丢失

在dos窗口中
bengin
update 一张表的数据
不回滚 也不 提交

 

在 sqlyong中
操作同一张表，发现不允许，在等待！

 

4、在标准SQL规范中，定义了4个事务隔离级别

 

1）未授权读取

也称为读未提交（Read Uncommitted）：允许脏读取，但不允许更新丢失。如果一个事务已经开始写数据，则另外一个事务则不允许同时进行写操作，但允许其他事务读此行数据。该隔离级别可以通过“排他写锁”实现。

 

2）授权读取
也称为读提交（Read Committed）：允许不可重复读取，但不允许脏读取。这可以通过“瞬间共享读锁”和“排他写锁”实现。读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。

3）可重复读取（Repeatable Read）
可重复读取（Repeatable Read）：禁止不可重复读取和脏读取，但是有时可能出现幻读数据。这可以通过“共享读锁”和“排他写锁”实现。读取数据的事务将会禁止写事务（但允许读事务），写事务则禁止任何其他事务。

4）序列化（Serializable）
序列化（Serializable）：提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，不能并发执行。仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。

 

 

隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed。它能够避免脏读取，而且具有较好的并发性能。尽管它会导致不可重复读、幻读和第二类丢失更新这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。

 

5.事务和JDBC事务隔离级别(转，比较全面)
博文连接 ：http://lvwenwen.iteye.com/blog/2045951

 

二、视图

1、视图 是一张虚拟的表

01.表示一张表的部分数据或者是多张表的综合数据！
02.结构和数据都是建立在对真表的查询基础之上的！
03.视图中存放的数据其实就是对真实表的引用！
对视图中的数据进行添加，更新删除都会影响到真实的表！
04.一个真实的表可以创建N个视图！
05.如果视图关联了多表，不允许增 删！单表可以增删改
06.视图一般都是使用查询！

2、demo

-- 创建一个视图 只获取 学生姓名 编号 以及考试成绩
CREATE VIEW view_student_result
AS
SELECT s.studentNo 学生编号,studentName 学生姓名,
studentResult 考试成绩
FROM student s,result r--多表视图
WHERE s.`studentNo`=r.`studentNo`
-- 查询视图中的内容
SELECT * FROM view_student_result
-- 查询mysql数据库中所有的视图
SELECT * FROM information_schema.views;
-- 删除视图
DROP VIEW view_student_result;

-- 创建一个表的视图 学生姓名，地址，手机号
CREATE VIEW view_student
AS
SELECT studentName 学生姓名,address 地址,phone 手机号
FROM student

-- 查询视图

SELECT * FROM view_student

3、重要补充

一个表的视图增删改，真表随着改；

多表的视图无法增删改；

 

三、索引

1、概念和作用

-- 索引：是一种有效组合数据的方式！目的就是快速获得某个记录！快速访问数据库表中的特定信息！
提高了数据库的检索速度！
2、索引分类

1）mysql索引按存储类型分2种:
01.B-树索引（BTREE）:INNODB,MyISAM 支持
02.哈希索引
2）主要有以下6种类型：
01.普通索引：允许在定义为索引的列中 出现 重复值和空值！
02.唯一索引：索引列不允许出现重复值，但是可以出现一个空值！
03.主键索引：创建主键的时候，系统会默认创建一个主键索引！唯一！不能为空！
04.复合索引：将我们的多个列组合起来！（name,sid）姓名和身份证号！
05.全文索引：允许值的全文查询！允许空值和重复值！适合在一个内容比较多的列！text！
06.空间索引：对空间数据类型的列建立的索引！

3、demo
-- 查询指定表的索引
SHOW INDEX FROM student;
TABLE:索引所在的表
Non_unique:索引是否唯一 0:唯一 1：不唯一
key_name：索引名称
seq_in_index:该列在索引中的位置
column_name:定义所用的列名称
null：该列是否为空
index_type:索引类型

-- 给姓名增加 普通索引
CREATE INDEX index_studentName
ON student(studentName);

-- 给学生姓名和身份证号 增加 组合索引
CREATE INDEX index_name_sid
ON student(studentName,identityCard)

-- 删除索引
DROP INDEX index_name_sid ON student;

DROP INDEX index_studentName ON student;

4、创建索引的原则
01.经常被查询的列
02.经常用作选择的列
03.经常排序，分组的列
04.经常用作连接的列（主键/外键）

5、使用索引时的注意事项：
01.查询时减少使用*返回全部的列，不要返回不需要的列！
02.索引尽量要少，在字节数小的列上建立索引！
03.where字句中有多个条件表达式的时候，包含索引的列要放在其他表达式之前！
04.在order by的字句中避免使用表达式！

 

四、 sql 语句的执行 顺序

5.查询 select
1.找表 from
2.条件 where
3.分组 group by
4.分组条件 having
6.排序 order by
7.分页 limit

 

五、数据库的恢复和备份

为保证账户密码安全，命令中可不写密码，但参数“-p”必须有，回车后根据提示写密码！

1、数据库的备份

1）sql语句备份

cmd进入命令行

mysqldump -u用户名 -p 数据库名称 >指定的文件

2）另一种备份：建议sql语句备份，不建议手动复制data文件夹备份，且引擎为InnoDB时复制data文件夹备份无效！

2、数据库的恢复

前提是必须要有对应的数据库

mysql -u用户名 -p <指定的文件

1. mysql为DOS命令
2.在执行该语句之前，必须在MySQL服务器中创建新数据库，如果不存在恢复数据库过程将会出错

 

六、数据库设计

1、软件项目开发周期中数据库设计
01.需求分析阶段：分析客户的业务和数据处理需求
02.概要设计阶段：设计数据库的E-R模型图，确认需求信息的正确和完整
03.详细设计阶段：应用三大范式审核数据库结构
04.代码编写阶段：物理实现数据库，编码实现应用
05.软件测试阶段：……
06.安装部署：……

 

2、数据库设计步骤：

01.收集信息
02.标识实体
03.标识每个实体的属性
04.标识实体之间的关系

3、E-R图：

01.矩形 实体
02.椭圆形 属性
03.菱形 实体与实体的关系

1 ： 一对一
1：N： 一对多
M：N： 多对多

4、必须满足 三大范式

1)第一范式:确保每列的原子性
2)第二范式:要求每个表只描述一件事情
3)第三范式:要求表中各列必须和主键直接相关，不能间接相关

 

七、作业

1、手写四道题（考试本上）

01.谈谈你对JDBC的认识
02.JDBC常用的API以及各自的用途
03.jdbc连接数据库的步骤 代码带注释！
04.PreparedStatement的Statement的区别

2、预习JDBC的dao模式部分

 

八、老师辛苦了