SQL完整性与安全

SQL数据类型

SQL内建数据类型

date: 日期，包括年 (4 digit)，月，日

例:'2005-7-27'

time: 一天中的时间，包括时、分、秒

例: '09:00:30'

timestamp: date和time的组合

例: '2005-7-27 09:00:30.75'

interval: 间隔，表示时间区间

• 例: 间隔 '1'天
• 基本的时间、日期类型通过减法运算得到间隔
• 间隔与这些类型通过加法运算得到新的时间、日期

用户自定义类型

定义新类型(相当于是struct?)

create type

eg

create type dollars as numeric(12,2)

定义域(需要注意)

create domain p_name char(20) not null

类型和域的区别：

• 域上可以声明约束
• 域不是强类型

大对象类型

用于存储数据量比较大的数据照片、视频、文
档、地图、影像等

• Blob-二进制大对象
  使用二进制保存数据

• Clob-字符大对象
  使用字符保存数据表

查询大对象的时候，往往返回一个指向该对象的指针，而不是数据本身

完整性约束

概念
完整性约束条件是数据模型的组成部分，对数据库中数
据的语义进行约束

• 功能:保证数据的正确性和相容性
• 依据:是否真实的反映现实世界
• 目的: 防止不符合规范的数据进入数据库

数据完整性控制机制

$$IntegrityConstraint::=(O，P，A，R)$$

O：数据集合：约束的对象

P：谓词条件：什么样的约束

A：触发条件：什么时候检查

R：响应动作：不满足时怎么办

具体来说:

1. 定义完整性约束条件
2. 完整性检查
3. 违约处理

约束的对象

列：对属性的取值类型、范围、精度等的约束条件

元组：对元组中各个属性列间的联系的约束

关系：对若干元组间、关系集合上以及关系之间的联系的约束

对象的状态

• 静态 DB在任一时候均应满足的约束；例如：Sage>0
• 动态 DB从一状态变为另一状态时应满足的约束；例如:调整工资时只能增加不能减少

对象约束条件分类

静态列级约束

对列的取值域的说明

包括:

• 数据类型约束
• 数据格式约束eg: 学号：前四位表示入学年份，后四位为顺序编号
• 取值范围或取值范围集合的约束
• 对空值的约束(是否可为空)
• 其他约束(关于列的排序说明，组合列?)
  sql示例
  

静态元组约束

• 规定元组的各个列之间的约束关系
  sql示例
  

静态关系约束

关系的各个元组之间，或若干关系之间存在的联系或约束

实体完整性约束

指定关系的主码

• 在列级使用Primary Key子句
• 在表级使用Constraint子句

eg: 表级指定主码

Create Table Student
(Sno CHAR(9) Primary Key,
Sname CHAR(20) Not Null,
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20));
Constraint PK_SNO Primary Key (Sno))

检查和违约处理

• 完整性检查
  • 用户程序对主码列进行更新操作时，系统自动进行完整性检查
• 违约操作
  • 使主属性值为空值的操作
  • 使主码值在表中不唯一的操作
• 违约处理
  • 系统拒绝此操作，从而保证了实体完整性

参照完整性约束

当关系中指定了外码时,参照关系和被参照关系要满足参照完整性约束
参照完整性将两个表中相应的元组联系起来了，对被参照表和参照表进行增删改操作有可能破坏参照完整性

Create Table SC
(Sno CHAR(9) NOT NULL,
Cno CHAR(4) NOT NULL,
Grade SMALLINT,
Primary Key (Sno, Cno),
Foreign Key (Sno) References Student(Sno),
Foreign Key (Cno) References Course(Cno))

可能出现的违背参照完整性约束的情况

被参照关系删除元组

参照关系插入元组

修改被参照关系主码

修改参照关系主码

关于处理

动态列级约束

动态元组约束

动态关系约束

断言

授权

通常情况下，自主安全性是通过授权机制来实现的

授权者：决定用户权利的人

授权：授予用户访问的权利

授权的权限 : 存取权限

SQL授权与回收

授权

Grant <权限> [, <权限>]…
On <对象类型> <对象名> [, <对象类型> <对象名>]…
To <用户> [, <用户>]…
With Grant Option; //允许传播权限

要求:

回收

Revoke <权限> [, <权限>]…
On <对象类型> <对象名> [, <对象类型> <对象名>]…
From <用户> [, <用户>]…[Cascade | Restrict];

关于Cascade和Restrict

"Cascade"：如果使用 "Cascade" 选项，那么当取消权限时，系统会自动将该权限级联取消，同时也会取消依赖于该权限的其他权限。这意味着，如果用户被授予了某个权限，并且使用 "Cascade" 选项来取消该权限，系统将不仅取消该权限，还会取消所有基于该权限的其他权限。这可以在某些情况下帮助维护数据库的一致性。

"Restrict"：如果使用 "Restrict" 选项，那么取消权限将受到限制，系统只会取消指定的权限，而不会影响依赖于该权限的其他权限。这意味着，只有明确指定的权限会被取消，而不会影响其他权限。这是默认行为，如果不显式指定 "Cascade"，通常情况下会使用 "Restrict"

角色

角色: 权限集合

示例

ADDON

关于约束

"约束"（constraint）在SQL中用于定义表格中数据的规则和条件，以确保数据的完整性、一致性和正确性。约束可以应用于表格中的列或多列，以限制数据的插入、更新和删除操作。

1. 主键约束（Primary Key Constraint）：

   主键约束用于唯一标识表格中的每一行数据，确保每个行具有唯一的标识。它通常应用于一个或多个列，用来防止重复的数据。

   CREATE TABLE Students (
       StudentID INT PRIMARY KEY,
       FirstName VARCHAR(50),
       LastName VARCHAR(50)
   );
   

2. 外键约束（Foreign Key Constraint）：

   外键约束用于建立表格之间的关联关系。它定义了一个列或多列，这些列的值必须引用另一个表格中的主键。

   CREATE TABLE Orders (
       OrderID INT PRIMARY KEY,
       CustomerID INT,
       OrderDate DATE,
       FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
   );
   

3. 唯一约束（Unique Constraint）：

   唯一约束确保指定的列或列组中的值是唯一的，但与主键不同，它允许空值。

   CREATE TABLE Employees (
       EmployeeID INT PRIMARY KEY,
       Email VARCHAR(100) UNIQUE
   );
   

4. 检查约束（Check Constraint）：

   检查约束定义了一种条件，确保插入、更新或删除操作满足指定的条件。

   CREATE TABLE Products (
       ProductID INT PRIMARY KEY,
       Price DECIMAL(10, 2),
       CONSTRAINT CHK_Price CHECK (Price >= 0)
   );
   

5. 默认约束（Default Constraint）：

   默认约束为列指定一个默认值，如果插入行时未提供该列的值，则将使用默认值。

   CREATE TABLE Orders (
       OrderID INT PRIMARY KEY,
       OrderDate DATE DEFAULT GETDATE()
   );