• 数据库教程网
• 尚硅谷MySQL数据库教程, 相关资料
• 黑马MySQL教程

1.1 安装

1.2 配置

1.3 数据库相关文件

1.4 其他细节

• 输入规范：

  • 在windows中，命令关键字和表名、列名等大小写不敏感
  • 在linux中，命令关键字大小写不敏感，表名、列名等大小写敏感
  • 语句输入必须以;结尾，作为结束

    在MySQL命令行中，可以使用\G结尾，输出格式和;不同，\G是垂直显示

  • 在大多数数据库中单双引号'和"可以混用，但标准规定这两种符号含义是不同的

    Oracle就不支持引号混用，'用于指定字符串值，"用于指定列、表和别名等标识，如果在字符串中希望使用'，需要使用''

• 命令行注释：可以使用以下格式注释

  # MySQL独有的注释方式
  -- 单行注释
  /* 多行注释 */

• 命名规则：

  • 数据库名、表名、变量名有最大长度限制，比如MySQL要求不超过30个字符，变量要求不超过29个字符
  • 命名支持A-Z、a-z、0-9和_共63个字符，数据库名、表名、字段名、变量名最好都小写，用_格式命名
  • 如果命名与保留字冲突，应该使用反引号`包裹(MySQL)，引号"包裹(Oracle、PostgreSQL)
  • 在同一软件中数据库不能同名、同一数据库中表不能同名，同一表中字段不能同名

  可以通过数据库名.表名.字段名的方式定位字段

  阿里巴巴的JAVA开发手册命名规范

  • 【强制】表名必须用小写字母或数字，并以字母开头，中间用_连接，不允许在两个下划线间只出现数字
  • 【强制】禁用保留字命名
  • 【强制】表必备三个字段：主键id、数据插入时间gmt_create、数据更新时间gmt_modified
  • 【推荐】表命名最好遵循业务名称_表的作用
  • 【推荐】库命名与应用命名尽量一致
  • 【参考】应该选择合适的字符存储长度，节约数据表空间，节约索引存储，加快查询速度

2.1 数据类型

• 不同数据库可使用的类型不相同，但经常使用的类型还是基本相同的

---

• 在PostgreSQL中支持定义域，修改限制原有的基本类型

  # 当尝试将负值赋给uint4类型的列时，PostgreSQL会抛出错误
  CREATE DOMAIN uint4 AS integer CHECK (VALUE >= 0);

3.1 视图的作用

• 视图是一个或多个表的数据的逻辑显示，视图不存储数据，占用很少空间

  • 视图建立在已有表的基础上，称这些依赖的表为基表

  • 视图的创建删除操作只影响自身，不影响基表

  • 对视图增删查改操作对基表会造成影响

  • 视图提供数据内容的语句为查询语句，可以将视图理解为存储起来的SELECT语句

  • 要使视图可以更新，需要让视图中的行和表中数据是一对一的关系，绝大多数的视图都无法更新,即不支持对视图增删改操作

  • 小型项目一般不推荐使用视图，大型项目可以使用视图

  • 优点：视图可以简化查询、控制输出的方式、访问限制

  • 不足：如果修改了表结构，还需要修改对应的视图，增加了维护成本，可读性不好

4.1 索引功能和相关概念

• 索引用于提高查询性能，索引的数据结构以某种方式指向数据，并在这些数据结构的基础上实现高级查找算法，索引：
  • 可以降低IO搜索次数
  • 可以保证数据的唯一性
  • 可以提高表连接的查询速度
  • 可以显著减少分组和排序时间
  • 需要额外的创建和维护时间，在增加更新和删除数据时，需要动态地维护，降低表的更新速度
  • 需要占磁盘空间，如果有大量索引，索引可能比数据更大
• 索引的代价
  • 空间代价：索引会占用很大的存储空间，比如InnoDB使用的B+树索引，一个页默认占用16KB空间
  • 时间代价：在增删改操作时，都可能需要修改索引，同时进行修改时，可能涉及到记录移位、数据回收等操作，会对性能带来影响

• SQL存储函数用于完成特定计算，返回一个值，在SQL中，存储函数可以分为聚合函数和单行函数，不同的DBMS之间的差异很大，只有很少的函数是被DBMS同时支持的
  • 聚合函数是对一组数据进行汇总的函数，输入是多行值，输出单个值，在之前聚合查询中已经介绍过了，接下来的内容不包含聚合函数
  • 单行函数只对一行数据进行变换，每行返回一个结果，支持嵌套

  另外，函数类型还可以按用途分为流程控制、数值、字符串、时间等类型

• 事务：是将一组数据库操作打包起来形成一个逻辑独立的工作单元,这个工作单元不可分割,要么不发生,要么全发生

• 事务的特性(ACID):成为事务必须满足这特性

  • 原子性(Atomicity):事务不可分割的性质
  • 一致性(Consistency):事务在执行前后数据库都处于一致性状态,即满足完整性约束
  • 隔离性(Isolation):多个事务并发执行时必须独立
  • 持久性(Durability):已提交的事务对数据库的修改是永久的持续存在的

• 事务的状态

  • 活动的(active)：事务对应的数据库操作正在执行过程中时，我们就说该事务处在活动的状态
  • 部分提交的(partially committed)：当事务中的最后一个操作执行完成，但由于操作都在内存中执行，所造成的影响并没有刷新到磁盘时，我们就说该事务处在部分提交的状态
  • 失败的(failed)：当事务处在活动的或者部分提交的状态时，可能遇到了某些系统或硬件错误而无法继续执行，或者人为的停止当前事务的执行，我们就说该事务处在失败的状态
  • 中止的(aborted)：如果事务执行了一部分而变为失败的状态，那么就需要把已经修改的事务中的操作还原到事务执行前的状态，回滚执行完毕，我们就说该事务处在中止的状态

7.1 备份

• 物理备份：备份数据文件，转储数据库物理文件到某一目录。物理备份恢复速度比较快，但占用空间比较大

• 逻辑备份：对数据库对象利用工具进行导出工作，汇总入备份文件内。逻辑备份恢复速度慢，但占用空间小，更灵活。逻辑备份就是备份sql语句，在恢复的时候执行备份的sql语句实现数据库数据的重现

7.2 恢复

• 从备份恢复

  • 物理备份恢复

  • 逻辑备份恢复

• 触发器：相当于事件侦听器，当数据库发生特定的事件后，触发器被触发，完成相应的处理

  因为主键变化带来的外键更新不会触发从表触发器

• 创建触发器的语法：

• 查看触发器

  -- 查看触发器
  SHOW TRIGGERS;
  
  -- 查看某个触发器的定义
  SHOW CREATE TRIGGER 触发器名;
  
  -- 查看所有的触发器 (所有数据库)
  SELECT * FROM information_schema.TRIGGERS;

• 删除触发器：触发器无法停用，只能通过删除取消

  DROP TRIGGER IF EXISTS 触发器名;

• 触发器可以用于保证数据完整性、对插入的数据进行完整性验证，也可以记录操作日志，但触发器也有许多缺点：

  • 触发器可读性差，创建时不会验证触发器流程是否正确，可能会导致莫名其妙的错误，触发器中抛出的错误会导致触发触发器的事务回滚
  • 触发器和表结构相关，如果表结构更改，相关触发器可能报错
  • 触发器虽然无法触发自身，但可能导致间接无限循环触发
  • 触发可能影响大批量操作的性能，尤其是对每行触发

9.1 游标

• 虽然我们可以通过筛选条件WHERE和HAVING，或者是限定返回记录的关键字LIMIT返回一条记录，但是，却无法在结果集中随意定位到某条记录并进行修改处理

• 游标，提供了一种灵活的操作方式，让我们能够对结果集中的每一条记录进行定位，并对指向的记录中的数据进行操作的数据结构

  • 游标可以用于操作数据行，同时也可以滚动游标，指向结果集中的任意一行

• 创建游标

• 使用游标

  -- 打开游标
  OPEN 游标名;
  
  -- 获取游标指向的记录，变量应该在之前DECLARE提前定义
  -- 接收的变量对应查询语句的列名
  -- 如果已经到达结果集的末尾，FETCH 操作将返回 @@FETCH_STATUS = -1
  -- 我们可以通过检查 @@FETCH_STATUS 的值来判断是否已经遍历完结果集
  FETCH 游标名 INTO 变量名1, 变量名2, ...;
  
  -- 关闭游标
  CLOSE 游标名;
  
  -- 释放游标
  DEALLOCATE 游标名;