数据库原理

Posted on

SQL 语言的功能

SQL 是结构化查询语言（Structured Query Language）的缩写，其功能包括四个部分：

  • 数据查询，数据库中最常见的操作，通过 select 语句可以得到所需的信息；
  • 数据操纵，DML（Data Manipulation Language）主要包括插入数据、修改数据、删除数据；
  • 数据定义，DDL（Data Definition Language）实现数据定义功能，可对数据库用户、基本表、视图、索引进行定义与撤销；
  • 数据控制，DCL（Data Control Language）用于对数据库进行统一的控制管理，保证数据在多用户共享的情况下能够安全。

基本的 SQL 语句：

delete 与 truncate 命令的区别

相同点：都可以用来删除一个表中的数据。
不同点：

  1. truncate 是一个 DDL，它会被隐式地提交，一旦执行后将不能回滚。delete 将删除的操作以日志的形式进行保存，以便将来进行回滚操作；
  2. 用 delete 操作后，被删除的数据占用的存储空间还在。而用 truncate 操作删除数据后，被删除的数据会立即释放占用的存储空间；
  3. truncate 的执行速度比 delete 快。

内连接与外连接的区别

内连接（INNER JOIN）也被称为自然连接，只有两个表相匹配的行才能在结果集中出现，可能会造成信息的丢失；
SQL 的外连接共有 3 种 类型：左外连接（LEFT OUTER JOIN）、右外连接（RIGHT OUTER JOIN）、全外连接（FULL OUTER JOIN），外连接的用法和内连接一样。

事务

事务是数据库中一个单独的执行单元，通常由高级数据库操作语言或编程语言编写的用户程序的执行所引起。
事务必须满足四个属性：原子性（atomicity）、一致性（consistency）、隔离性（isolation）、持久性（durability）。

严格来说，ACID 都是由数据库管理系统来保证的，在整个应用程序运行过程中，应用无需去考虑数据库的 ACID 实现。

原子性

事务是一个不可分割的整体，避免了只执行操作的一部分而带来的错误，原子性要求事务必须被完整执行。

一致性

一个事务执行之前和执行之后，数据库数据必须保持一致性状态，一致性由用户来负责，由并发控制机制实现。

隔离性

将一个事务内部的操作与事务的操作隔离起来，不被其他正在进行的事务看到。数据库有 4 种类型的事务隔离级别：未提交读、已提交读、可重复读、可序列化（最高级别）。

持久性

事务完成以后，DBMS 保证它对数据库中数据的修改是永久性的，持久性一般通过数据库备份与恢复来保证。

存储过程

SQL 语句在执行时要先编译，然后再被执行。
在大型数据库系统中，为了提高效率，将为了完成特定功能的 SQL 语句集进行编译优化后，存储在数据库服务器中，用户通过指定存储过程的名字来调用执行。
创建存储过程的常用语法：

使用存储过程可以增强 SQL 语言的功能和灵活性；同时，存储过程可以使没有权限的用户间接地存取数据库，也保证了数据的安全。

与函数的不同

  1. 存储过程一般是作为一个独立的部分来执行，而函数可以作为查询语句的一部分来调用；
  2. 存储过程实现的过程较复杂，而函数实现的功能针对性较强；
  3. 函数只能返回一个值或表对象，而存储过程可以返回多个参数；
  4. 函数不能直接操作实体表，只能操作内建表；
  5. 存储过程在创建时即在服务器上进行了编译，其执行速度比函数快。

各种范式的区别

使用正确的数据结构，不仅有助于对数据库进行相应的存取操作，还可以极大地简化应用程序中的其他内容。
范化是在识别数据库中的数据元素、关系以及定义所需的表和各表中的项目，这些初始工作之后的一个细化过程。常见的范式有 1NF、2NF、3NF、BCNF、4NF。
各范式的关系图：

1NF

第一范式是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。
简而言之，第一范式就是无重复的列。

2NF

第二范式是在第一范式的基础上建立起来的，即满足第二范式必须先满足第一范式。要求数据库表中的每个实例或行必须可以被唯一地区分。
如果关系模式 R 为第一范式，并且 R 中的每一个非主属性完全函数依赖于 R 的某个候选键，则称 R 为第二范式。

3NF

如果关系模式 R 是第二范式，且每个非主属性都不传递依赖于 R 的候选键，则称 R 是第三范式的模式。

BCNF

构建在第三范式的基础上，如果关系模式 R 是第三范式，且每个属性都不传递依赖于 R 的候选键，那么称 R 为 BCNF 的模式。
排除关键字段决定关键字段的情况。

4NF

假设 R 是一个关系模式，D 是 R 上的多值依赖集合，如果 D 中但凡存在多值依赖 X→Y，X 必是 R 的超键，那么称 R 是第四范式的模式。

触发器

触发器是一种特殊类型的存储过程，它由事件触发，而不是程序调用或手工启动。
触发器与存储过程的区别：

触发器的作用

  1. 增加安全性；
  2. 利用触发器，记录所进行的修改以及相关信息，跟踪用户对数据库的操作，实现审计；
  3. 完成比约束更复杂的数据约束，例如，同步实时地复制表中的数据。

事前触发和事后触发的区别

  • 事前触发发生在事件发生之前，用于验证一些条件或进行一些准备工作，可以获得之前的字段值；
  • 事后触发发生在事件发生之后，做一些收尾工作，可以保证事务的完整性。

游标

数据库中，游标提供了一种对从表中检索出的数据，进行操作的灵活手段，它实际上是一种能从包含多条数据记录的结果集中，每次提取一条记录的机制。
游标是由结果集和结果集中指向特定记录的游标位置组成的。

游标总是与一条 SQL 选择语句相关联。

游标的优点

  1. 在使用游标的表中，对行提供删除和更新的功能；
  2. 游标将面向集合的数据库管理系统和面向行的程序设计连接了起来。

日志

日志文件记录所有对数据库数据的修改，主要是保护数据库以防故障发生，以及恢复数据时使用。
一旦数据库中日志满了，就只能执行查询等读操作，不能执行更改、备份等操作，基本上处于不能使用的状态（任何写操作都要记录日志）。

union 和 union all 的区别

  • union 在进行表求并集后，会去掉重复的元素，会对所产生的结果进行排序运算，删除重复的记录再返回结果；
  • union all 只是简单地将两个结果集，合并后就返回结果，可能会包含重复的数据。

在执行查询操作时，union all 要比 union 快很多。所以，如果可以确认合并的两个结果集中不包含重复的数据，最好使用 union all。

视图

视图是由从数据库的基本表中选取出来的数据，组成的逻辑窗口，与基本表不同，它是一个虚表。
在数据库中，存放的只是视图的定义，而不存放视图包含的数据项，这些项目仍然存放在原来的基本表结构中。

视图的作用

  1. 可以简化数据查询语句（不能提高查询的效率）；
  2. 可以使用户能从多角度看待同一数据；
  3. 通过引入视图，可以提高数据的安全性；
  4. 视图还提供了一定程度的逻辑独立性。