Hefery 的个人网站

数据库简介

数据库简介

数据库：用于存储和管理数据的仓库

• DB：数据库（DataBase）长期存储在计算机内的、有组织的、有共享的、统一管理的数据集合，存储一系列有组织数据的仓库。按数据结构来存储和管理数据的计算机软件系统

• DBMS：数据库管理系统（Database Management System）数据库是通过DBMS创建和操作的容器

  基于客户机C/S（MySQL、SQL Server、Oracle、DB2、SQLite）、基于共享文件系统（Access）

• SQL ：结构化查询语言（Structure Query Language）专门用来与数据库通信的语言

特点：

• 持久化存储数据（数据永久保存）
• 方便存储和管理
• 使用统一的方式 SQL 操作数据库（使用SQL语句，查询方便效率高）
• "方言"：每一种数据库操作方式存在的差异

常见数据库管理软件：

• MySQL：开源免费的数据库，小型，已被 Oracle 收购
• Oracle：收费的大型数据库，Oracle公司的产品
• DB2 ：IBM公司的数据库产品,收费的。常应用在银行系统中
• SQL Server：MicroSoft公司收费的中型的数据库（C#、.net 等语言常使用）
• SQLite: 嵌入式的小型数据库，应用在手机端，如：Android

数据库访问接口：

• ODBC：为访问不同的SQL数据库提供了一个共同的接口，作为访问数据的标准
• JDBC：用于Java应用程序连接数据库的标准方法，用于执行SQL语句的 Java API
• ADO.NET：微软在.NET框架下开发设计的一组用于和数据源进行交互的面向对象类库
• PDO：为PHP访问数据库定义了一个轻量级的、一致性的接口，它提供了一个数据访问抽象层

事务TCL

事务基本概念

事务：包含多个步骤的业务操作。如果这个业务操作被事务管理，则这多个步骤要么同时成功，要么同时失败

PS：在关系数据库中，一个事务可以是一条SQL语句，一组SQL语句或整个程序

操作：

1. 开启：BEGIN TRANSACTION
2. 回滚：ROLLBACK TRANSACTION
3. 提交：COMMIT TRANSACTION

# MySQL查看事务提交方式：
SELECT @@autocommit;  -- MySQL中事务默认提交方式：自动(1：自动、0：手动)
# 修改事务提交方式：
SET @@autocommit = 1/0;

事务四大特征

ACID特性：

• 原子性(atomicity) ：事务是一个不可分割的工作单位，事务中包括的操作要么都做，要么都不做
  转账转到一半，系统出问题了，回滚事务，不会出现只跑一半的情况
• 一致性(consistency)：事务执行会使数据从一个状态切换到另一个状态,数据总量不变
  能量守恒：钱转来转去，不会多也不会少
• 隔离性(isolation) ：事务内部操作及使用数据对其他并发事务是隔离的，并发执行的事务之间不相干扰
  转账过程中，要对账号金额进行汇总，由于转账事务未提交，汇总的事务是不可见的
• 持久性(durability) ：事务一旦提交，它对数据库中数据的改变就应该是永久性

事务隔离级别

多个事务是相互隔离的，若多个事务要操作同一份数据可能引起问题，设置不同的隔离级别可以解决(安全级别越高，效率越低)

数据问题：

• 脏读：一个事务读取到另一个事务没有提交的数据
• 不可重复读：同一事务中，两次读到的数据不一样
• 幻读：一个事务操作(DML)数据表所有记录，另一个事务添加一条数据，第一个事务查不到自己的修改

事务隔离级别

• 未提交读(READ UNCOMMITED)：事务可以提交为提交的数据，及脏读
• 已提交读(READ COMMITED)：Oracle默认，一个事务从开始到提交前对数据所做的修改对其他事务是不可见的
• 可重复读(REPEATABLE READ)：MySQL默认，一个事务内，多次读同一个数据，在这个事务还没结束时，其他事务不能访问该数据
• 可串行化(SERIALIZABLE)：事务序列化执行，事务只能一个接着一个地执行，但不能并发执行

MySQL InnoDB存储引擎的默认⽀持的隔离级别是 REPEATABLE-READ（可重读）

# MySQL查看当前隔离级别
SELECT @@tx_isolation;
# 设置隔离级别
SET GLOBAL|SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE;

事务管理

操作：

• 开启事务 BEGIN TRANSACTION
• 提交事务 COMMIT TRANSACTION
• 回滚事务 ROLLBACK TRANSACTION

使用Connection对象来管理事务

• 开启事务：setAutoCommit(boolean autoCommit) ：调用该方法设置参数为false，即开启事务，在执行sql之前开启事务
• 提交事务：commit()，当所有sql都执行完提交事务
• 回滚事务：rollback()，在catch中回滚事务

数据库锁

按照锁粒度划分：表级锁、行级锁、页级锁
按照锁级别划分：共享锁、排它锁
按加锁方式划分：自动锁、显示锁
按锁的操作划分：DML锁、DDL锁
按锁的使用划分：乐观锁、悲观锁

数据库设计

概念设计

数据库结构优化的目的

• 减少数据冗余尽量
• 避免数据维护中出现更新、插入和删除异常
  插入异常：不该插入的数据被插入
  更新异常：不该更新的数据被更新
  删除异常：不该删除的数据被删除
• 节约数据存储空间

逻辑设计

多表之间的关系：

• 1:1 人:身份证 在任意一方创建唯一外键指向另一方的主键
• 1:N 部门:员工 在多地一方创建外键指向低的一方
• N:M 学生:课程 借住第三张表，至少有两个字段分别指向两张表的主键

范式：

• 第一范式(1NF)：数据表的每列都是不可分割的基本数据项，同一列中不能有多个值，不能存在重复的属性
• 第二范式(2NF)：数据表中的每条记录必须唯一。为了区分，通常要为表加上一个主键列用来存储唯一标识
• 第三范式(3NF)：每列都与主键有直接关系，不存在传递依赖（爸爸—儿子—女儿—女儿的玩具）

物理设计

• 命名：数据库、表、字段
• 合适的存储引擎：Innodb、MyISAM
• 为字段选择合适的数据类型
  • 当一个列可以选择多种数据类型时，应该优先考虑数字类型，其次是日期或二进制类型，最后是字符类型。对于相同级别的数据类型，应该优先选择占用空间小的数据类型
  • 整数类型：标志位：tinyint
  • 浮点类型：金额：decimal
  • 字符类型
    • varchar：不定长，很多字符串列很少被更新
    • char：定长，适合存储经常更新的字符串列
  • 布尔类型：在MySQL里面对应的是 tinyint(1)
  • 日期类型：选择时间戳、date、time存储
• 如何为Innodb选择主键
  • 主键应该尽可能的小
  • 主键应该是顺序增长的
  • Innodb的主键和业务主键可以不同