SQL学习day5

写在前面：这节课跨越了好几天才来继续听（ 

11.事务

transaction是代表单个工作单元的一组SQL语句。

对数据库进行多次更改的情况下使用transaction，并且这些更改是作为一个单元【一起成功or失败】

属性：

1.原子性A：一个工作单元要么全成功，要么全失败

2.一致性C：使用transaction可以使数据库保持一致的状态

3.独立性I：多个transaction是独立的，当有同样的数据被更改时各自受到保护

【若多个transaction想更新相同的数据，需要一个一个运行】

4.持久性D：一旦被提交，其产生的更改是永久的

（2）创建transaction

STAR TRANSACTION；

INSERT INTO 表名(列名1，列名2)

VALUES（列值）；

INSERT INTO 表2名【同样的列可不用再写列名】

VALUES（LAST_INSERT_ID(),列值）；

COMMIT；【关闭此transaction】

或者 ROLLBACK；【进行错误检查，并手动退回transaction撤销所有更改】

（3）并发和锁定

并发【一个用户修改其他用户正在检索或修改的数据】

常见的并发问题

1.丢失更新数据

【两个事务尝试更新相同的数据并且没有上锁】

较晚提交的事务会覆盖较早事务做的更改

解决方法：上锁

对应的事务隔离机制：

2.dirty reads

【读取了尚未被提交的数据】

解决方法：建立事务隔离机制，保证事务只读取已提交的数据

对应的事务隔离机制：

3.不可重复读

【读取了多次数据，但结果都不同】

解决方法：建立事务隔离机制，保证事务读取的数据是可重复和一致的

对应的事务隔离机制：（1）READ COMMITTED

4.幻读

【查询时没看到的数据】

解决方法：建立事务隔离机制，保证有别的事务在更新数据时这个事务能知晓变动，若有所变动则该事务会等到变动结束后再执行

事务隔离机制

1.READ UNCOMMITTED

最低等的，可能会遇到各种并发问题

2.READ COMMITTED

只读取提交的数据，可以防止dirty reads

3.REPEATABLE READ

保证不同的读取会返回同样的结果

4.SERIALIZABLE

可以阻止以上所有常见问题，可以防止幻读

【隔离级别越高，限制了并发，但会存在越重的性能和可扩展性问题】

【MySQL中默认的级别是REPEATABLE READ】

查找事务隔离机制

SHOW VARIABLES  LIKE 'transaction_level'

更改事务隔离机制

SET GLOBAL/SESSION/CONNECTION TRANSACTION ISOLATION LEVEL ‘隔离级别名称’

【设定session或connection该会话所有未来的transaction都会是这个隔离级别】

【设定global，则所有会话中的所有新事务都是这个隔离级别】

死锁deadlocks

当不同事务均因握住了别的事务需要的锁而无法完成的情况

12.数据类型

1.字符串

CHAR 存储固定长度字符串

VARCHAR（n） 存储可变长度为n的字符串

【可以存数值；最多存储65535个字符】

MEDIUMTEXT 可以存储最多1600万个字符

LONGTEXT 可以存储4gb的字符

TINYTEXT

TEXT

2.数值

整数类型

TINYINT [-128.127]

UNSIGNED TINYINT [0,255]

SMALLINT

MEDIUMINT

INT

BIGINT

ZEROFILL

带小数点的数字

DECIMAL(P,S) p代表精度，明确最大位数 ，介于1和65之间；S代表小数位数，确定小数点后的位数

DOUBLE 存储非常大的数字

FLOAT 存储非常小的数字

3.日期时间

DATE

TIME

DATETIME

TIMESTAMP

YEAR

4.布尔值

BOOL

BOOLEAN

【TRUE=1,FALSE=0】

5.空间数据

枚举类型：ENUM【尽量避免使用】

集合类型：SET

6二进制数据【存文件】

TINYBLOB

BLOB

MEDIUMBLOB

LONGBLOB

7.JSON数据

会使用一个大括号定义一个对象，大括号内会有很多键值对

创建JSON对象的函数

JSON_OBJECT

提取JSON对象中单独的键值对

JSON_EXTRACT(JSON文档，'$.键'）

JSON文档->'$.键'

如果值为数值的话，可以加方括号并在括号内指定想要检索的项目的索引

如果值为嵌套的JSON对象，也用点运算，若不想带引号，则改为JSON文档->>'$.键.子键'

更新JSON对象的现有属性or添加新属性

JSON_SET（JSON文档，'$.键'，值）

删除一个或多个属性

JSON_REMOVE（JSON文档，'$.键'）

13.涉及数据库

1.数据建模

了解业务需求【重要】

构建业务的概念模型【包括识别业务中的实体、事物或概念以及它们之间的关系】

构建逻辑模型【生成一个数据模型或数据结构用以存储数据，不涉及数据的抽象模型】

构建实体模型【围绕特定数据库技术的逻辑模型】

2.概念模型

目的：表示业务中的实体、事务或概念以及他们之间的关系

创建实体关系图 

3.逻辑模型

目的：为概念模型增加更多细节，匹配对应实体的属性

实体之间的关系：一对一；一对多；多对多

4.实体模型

NEW MODEL

primary key：唯一识别给定表里每条记录的列【也可以是多列唯一识别】

foreign key（外键）：引用了父表的主键

两表的关系：一段称为父表或主键表，另一端称为子表或外键表

外键约束：CASCADE【主键更新，外键也随之更新】

RESTRICE/NO ACTION 【不随之更改】

标准化：审查涉及，防止数据重复【消除冗余】

第一范式：

要求每一行中的每一个单元格都应该有单一值，且不能出现重复列

【使用link table 来解决多对多的关系】

第二范式：

要求每张表都应该有一个单一目的，即只能代表一种且仅有一种实体类型

【表中的数据不会重复生成，否则更改数据时需要变动好几个地方】

第三范式：

表中的列不应派生自其他列【表中的列应该独立】

模型的正向工程

DATABASE->Forward engineer

数据库同步模型

DATABASE->synchronize model

 模型的逆向工程【更改没有模型的数据库】

DATABASE->Reverse engineer