数据库系统的基本概念

从数据到数据库

数据

数据的概念

数据：数据的内容是信息；数据的表现形式是符号记录

数据的分类

• 按数据加工程度分：分为三次数据、二次数据、一次数据、零次数据（加工程度由高到低）

• 按结构化程度分：

类型	含义	本质	举例
结构化数据	直接可用关系型数据库存储和管理的数据	先有结构，后有数据	关系数据库中的数据
半结构化数据	经过一定的加工处理以后可以用关系数据库存储和管理的数据	先有数据，后有结构	HTML、XML
非结构化数据	无法用关系型数据库存储和管理的数据	没有（或难以发现）统一结构的数据	语音、图像文件

数据库

数据库（Database, DB）：按一定结构存管数据的库

数据库的特点：（1）集成性：特定的数据和联系集中在一起按一定结构存储；（2）共享性：数据库中的数据能被多个应用程序的用户使用

数据库系统

数据库系统（Database System, DBS)：又称数据库应用系统，是具有数据库技术支持的应用系统。如：网银系统、点餐系统、教务系统

数据库管理系统（Database Management System，DBMS）：是建立、操作、管理、控制数据库和数据的应用软件。如：SQL Server，MySQL，Oracle

注：DBS包含DB和DBMS及其它应用软件

数据模型

数据从现实世界进入数据库，需要经历3个阶段

数据模型

概念模型：E-R图的设计

E-R图设计的基本方法： （1）确定实体 （2）确定联系类型（1:1, 1:n, n:m） （3）把实体类型和联系类型组成E-R图 （4）确定实体类型和联系类型的属性 （5）确定实体类型的主键

E-R图示例：餐馆点餐系统

ER图

数据库系统的模式结构

数据库系统通过3个层次的抽象，构成了三级模式结构

（1）外模式or子模式：对应视图 （2）逻辑模式or概念模式：对应概念数据库 （3）内模式or存储模式：对应物理数据库

三级模式结构的优点： （1）是数据库系统中最本质的系统结构 （2）数据共享 （3）简化用户接口 （4）数据安全

T-SQL常用操作语言的分类

1. 数据定义语言（Data Definition Language，DDL） 对数据库、视图、表、索引的创建、修改、删除，主要是CREATE, ALTER, DROP
2. 数据操作语言（Data Manipulation Language，DML） 插入数据INSERT，更新数据UPDATE，删除数据DELETE，数据查询SELECT
3. 数据控制语言（Data Control Language，DCL） 对数据库进行安全管理和权限控制，如赋予权限GRANT，禁止赋予的权限DENY，收回权限REVOKE
4. 事务管理语言（Transact Management Language，TML） 用于事务管理，提交事务COMMIT，撤销事务ROLLBACK
5. 其它附加的语言：包括可编程性（函数、存储过程、触发器等）

数据定义语言

数据库的创建、修改、删除

数据库的创建

主数据文件.mdf（仅一个），次数据文件.ndf（可有多个），日志文件.ldf（可以有多个，不属于任何文件组）

/****** 建立数据库 ******/
CREATE DATABASE restaurant
ON 
PRIMARY   --指定文件组，默认为PRIMARY
(
    NAME = restaurant_data,  --指定数据文件逻辑名，不可省
    FILENAME = 'C:\sql\restaurant_data.mdf',   --指定物理文件名
    SIZE = 5 MB,   --初始大小
    MAXSIZE = 500 MB,   --最大大小
    FILEGROWTH = 10 %   --增长方式
),
FILEGROUP group01   --指定文件组 group01
(
    NAME = restaurant_data_group_01_01,  --指定数据文件逻辑名，不可省
    FILENAME = 'C:\sql\restaurant_data_group01_01.ndf',   --指定物理文件名
    SIZE = 5 MB,   --初始大小
    MAXSIZE = 500 MB,   --最大大小
    FILEGROWTH = 10 %   --增长方式
)

LOG ON   --指明日志文件，不可省
(
    NAME = restaurant_log,  --指定逻辑名，不可省
    FILENAME = 'C:\sql\restaurant_log.ldf',
    SIZE = 5 MB,
    MAXSIZE = 100 MB,
    FILEGROWTH = 2 MB
)
GO

数据库的修改

/****** 重命名数据库 ******/
ALTER DATABASE restaurant
MODIFY NAME = restaurant_new   --将数据库restaurant重命名为restaurant_new
GO

/****** 增加数据文件 ******/
ALTER DATABASE restaurant_new
	--向主文件组增加数据文件
ADD FILE 
ON PRIMARY	
(
    NAME = restaurant_data_02,  --指定数据库逻辑名，不可省
    FILENAME = 'C:\sql\restaurant_data_02.ndf',   --指定物理文件名
    SIZE = 5 MB,   --初始大小
    MAXSIZE = 500 MB,   --最大大小
    FILEGROWTH = 10 %   --增长方式
)

	--向某一文件组增加数据文件
ADD FILE
(
    NAME = restaurant_data_group_01_02,  --指定数据文件逻辑名，不可省
    FILENAME = 'C:\sql\restaurant_data_group01_02.ndf',
    SIZE = 5 MB,
    MAXSIZE = 500 MB,
    FILEGROWTH = 10 % 
)
TO FILEGROUP group01
GO

/****** 修改数据文件 ******/
使用MODIFY FILE filename

/****** 增加、修改日志文件 ******/
类似，使用ADD LOG FILE, MODIFY FILE(注意没有MODIFY LOG FILE)

数据库的删除

DROP DATABASE restaurant_new
GO

表的创建、修改、删除

表的创建

以餐馆点餐系统为例

CREATE TABLE Customer
(
    customer_no INT IDENTITY(1000000,1) PRIMARY KEY,
	nickname CHAR(20) NOT NULL UNIQUE,
	phone CHAR(16) NOT NULL UNIQUE,
	pass VARCHAR(255) NOT NULL,
    is_vip CHAR(2) CHECK(is_vip='是' OR is_vip='否') DEFAULT '否'
)

CREATE TABLE Menu
(
    dish_no CHAR(8) PRIMARY KEY,
    dish_name CHAR(20) UNIQUE NOT NULL,
	discount NUMERIC(3,2) DEFAULT 1,
    price NUMERIC(6,2) NOT NULL,
    is_spicy CHAR(8) CHECK(is_spicy='不辣' OR is_spicy='微辣' OR is_spicy='辣' OR is_spicy='很辣')
)

CREATE TABLE Orders
(
    order_no INT IDENTITY(1000000,1) PRIMARY KEY,
    customer_no INT FOREIGN KEY REFERENCES Customer(customer_no) NOT NULL,
    employee_no CHAR(8) FOREIGN KEY REFERENCES Employee(employee_no),
    order_time DATETIME,
    people_num INT,
    discount NUMERIC(3,2) DEFAULT 1,
	total_price NUMERIC(6,2),
    order_status CHAR(6) CHECK(order_status IN ('未提交','待付款','已接单','已完成','已评价')),
    /**************************************** 
    如果想给约束起个名字，可以使用表级约束，然后使用CONSTRAINT起名
    CONSTRAINT CK_order_status    CHECK(order_status IN ('未提交','待付款','已接单','已完成','已评价'))
    *****************************************/
)

CREATE TABLE Orders_content
(
	order_no INT FOREIGN KEY REFERENCES Orders(order_no),
	dish_no CHAR(8) FOREIGN KEY REFERENCES Menu(dish_no),
	PRIMARY KEY (order_no,dish_no)
    /*****************************************
    使用表级外键约束
    FOREIGN KEY (order_no) REFERENCES Orders(order_no),
    FOREIGN KEY (dish_no) REFERENCES Menu(dish_no)
    ******************************************/
)

*补充：使用查询结果来创建新表：SELECT INTO

SELECT INTO 字句根据 SELECT命令选择的列和WHERE字句选择的行创建新表。它和CREATE TABLE命令的最大区别就是：不必经历通常的数据定义过程，而是基于现有字段和数据直接生成新表。

注意：SELECT INTO创建的表是一个真实的、独立的、永久的表，这与视图所对应的虚表不同。

表的修改

ALTER TABLE Orders
	/****** 修改某列定义 ******/
	ALTER COLUMN people_num INT NOT NULL
	
	/****** 添加一列 ******/
	ADD remarks VARCHAR(200)
	
	/****** 删除某列/某约束 ******/
	DROP COLUMN discount
	DROP CONSTRAINT CK_order_status

不是所有列都可以被修改，也不可以对某列进行任意的修改，否则表中的数据可能发生错误。为了避免此种情况发生，SQL Server中规定：

具有以下特性的列不能被修改：

• 数据类型为timestamp的列
• 计算列
• 全局标识符列
• 用于索引的列
• 用于由CREATE STATISTICS生成统计的列
• 用于主键或外键约束的列
• 关联有默认值的列

改变列的数据类型时，要满足以下条件：

• 原数据类型必须能够转换至新类型
• 新类型不能为timestamp类型
• 如果被修改列属性中有“标识规范”属性，则新数据类型必须是有效的“标识规范”数据类型

总之，不要轻易修改表结构。

表的删除

DROP TABLE Orders_content

视图的创建、修改、删除

何为视图？

视图的定义是：为了得到另一个关系而对基关系进行一次或多次操作所得到的动态结果。

通俗地说：视图就是一个在已有表或视图中进行子查询得到的虚表，在数据库中并不存在，需要时根据要求临时生成。

视图对应三级模式中的外模式。

视图的种类：

1. 标准视图：用户定义的视图（内容为一个查询）
2. 索引视图：具体化的视图，可以为其建立索引（关键步骤：使用 WITH SCHEMABINDING 选项创建视图，并为视图创建唯一的聚集索引。）
3. 分区视图
4. 系统视图

注意：

1. 不能将规则或DEFAULT定义与视图关联
2. 不能将AFTER触发器与视图相关联，只有INSTEAD OF触发器可以与之相关联
3. 定义视图的查询不能包含ORDER BY子句，除非在SELECT语句中有TOP子句
4. 不能创建临时视图，也不能为临时表创建视图

创建视图

语法：

CREATE VIEW viewName [(newColumnName [,...] )]
[WITH ENCRYPTION | WITH SECHEMABINDING]
AS
SELECT ... FROM ...
[WITH [CASCADED|LOCAL] CHECK OPTION] 
--要求对该视图执行的所有数据修改语句都必须符合select语句中设置的条件。CASCADED CHECK OPTION指明级联检查是否符合视图的定义（比如B视图在A视图基础上创建，级联检查就是既检查是否符合B视图的定义，又检查是否符合A视图的定义。）LOCAL CHECK OPTION就是只检查是否符合当前视图的定义。

例子：

CREATE VIEW every_order (订单号, 菜品号, 菜品反馈)
AS
SELECT order_no, dish_no, dish_feedback
FROM Orders_content
GO

修改视图

语法：

ALTER VIEW viewName [(newColumnName [,...] )]
[WITH ENCRYPTION | WITH SECHEMABINDING]
AS
SELECT ... FROM ...
[WITH [CASCADED|LOCAL] CHECK OPTION]

注：其实就是把CREATE改为ALTER。

删除视图

语法：

DROP VIEW viewName

实际操作的时候可以先检查该名称的视图是否存在。检查的方法：利用系统表INFORMATION_SCHEMA.VIEWS中TABLE_NAME列（该列存放视图名字）

例子：

USE restaurant
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'every_order')
	DROP VIEW every_order
GO

利用视图更新数据

视图虽然是虚表，但由于它是最终都是建立在实表的基础上的，因此也承载了更新数据的功能。对视图中数据的更新最终要转化为对基本表的更新。

为了防止用户对不属于视图范围内的数据进行操作，定义视图时最好加上WITH CHECK OPTION选项

什么样的视图是可更新视图？

1. 创建视图的SELECT语句中没有聚合函数，也没有TOP, GROUP BY, HAVING和DISTINCT关键字
2. 创建视图的SELECT语句中的各列必须是来自基表/视图的列，不能是表达式
3. 若视图包括多个基础表，且要更改的列属于不同基础表或为基础表的公共列，则不能通过视图更改基础表的数据

满足更新视图的要求的情况下，在视图上插入INSERT，更新UPDATE，删除REMOVE数据的语法和对表的操作完全相同。

关键在于思考我们在视图上修改表数据的操作是否是被允许的。

索引的创建、修改、删除

创建索引

创建索引的语法

CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED] INDEX
ON tableName (columnName [ASC|DESC] [,...])

例子：

CREATE CLUSTERED INDEX index_order_date
ON Orders(order_time DESC)
GO

聚集索引决定数据存放顺序，在一张表中只能有一个

非聚集索引在表中可以有多个

删除索引

语法：

DROP INDEX indexName

索引的分类

• 按存储结构：分为聚集索引、非聚集索引
• 按数据的唯一性：分为唯一索引和非唯一索引

使用规则、默认值和约束保证数据完整性

规则

创建规则

CREATE RULE rule_discount
AS
@discount <= 1.00 AND @discount > 0

绑定规则

将规则绑定至某张表的某一列，一列至多绑定一个规则。

规则与CHECK约束作用类似，但形式不同：

• 规则是依附于表的，而CHECK约束不依附于表（它可以对列应用，对表应用，也可以作为一个CONSTRAINT存在）
• CHECK约束优先级高于规则
• 一个列中可以有多个CHECK约束，但是只能有一个规则。可以同时有CHECK约束和规则。

绑定规则需要使用系统存储过程（SQL Server）

EXEC sp_bindrule rule_discount, 'Orders.discount'
-- 参数分别为 规则名, 表名.列名

解绑规则

使用系统存储过程（SQL Server）解绑规则

EXEC sp_unbindrule 'Orders.discount'
-- 参数名为列名

注：为何参数名只有列名？因为一个列只能绑定一个规则，所以给出列名足以使DBMS明白需要解绑的是哪个规则。

删除规则

DROP RULE rule_discount
-- 直接后接规则名即可，可以跟多个

默认值

创建默认值

语法：

CREATE DEFAULT defaultName 
AS const_expression

例子：

CREATE DEFAULT default_discount
AS 1.00

绑定默认值

只能使用系统存储过程，与绑定规则类似

EXEC sp_bindefault default_discount, 'Orders.discount'

❗ 注意是bindefault而不是binddefault，省略了一个d !

解绑默认值

（SQL Server）

sp_unbindefault 'Orders.discount'

删除默认值

DROP DEFAULT default_discount
-- 参数为 默认值名称

注意：删除默认值的时候必须先解绑，否则出错！

约束

前面实际上已经在创建表的时候使用过表级约束和列级约束，那时我们虽然没有使用CONSTRAINT关键字，但系统已经将其作为一个CONSTRAINT看待，并给它赋予了一个名称（不过这个名称通常异常复杂）。如果我们显式地使用CONSTRAINT关键字，那么可以给约束起名字，方便重复使用。

SQL Server中共有6中约束：

• 主键约束（PRIMARY KEY）
• 外键约束（FOREIGN KEY）
• 唯一性约束（UNIQUE）
• 检查约束（CHECK）
• 默认约束（DEFAULT）
• 非空值约束（NOT NULL）

你都还有印象吗？

尝试：试将下例中列级约束全部改为显式指定CONSTRAINT的表级约束

CREATE TABLE Menu
(
    dish_no CHAR(8) PRIMARY KEY,
    dish_name CHAR(20) UNIQUE NOT NULL,
	discount NUMERIC(3,2) DEFAULT 1,
    price NUMERIC(6,2) NOT NULL,
    is_spicy CHAR(8) CHECK(is_spicy='不辣' OR is_spicy='微辣' OR is_spicy='辣' OR is_spicy='很辣')
)

CREATE TABLE Orders
(
    order_no INT IDENTITY(1000000,1) PRIMARY KEY,
    customer_no INT FOREIGN KEY REFERENCES Customer(customer_no) NOT NULL,
    employee_no CHAR(8) FOREIGN KEY REFERENCES Employee(employee_no),
    order_time DATETIME,
    people_num INT,
    service_feedback NUMERIC(2,1),
    discount NUMERIC(3,2) DEFAULT 1,
	total_price NUMERIC(6,2)
)

-- 修改后
CREATE TABLE Menu
(
    dish_no CHAR(8),
    dish_name CHAR(20),
	discount NUMERIC(3,2),
    price NUMERIC(6,2),
    is_spicy CHAR(8)，
    
    CONSTRAINT pk_dish_no PRIMARY KEY (dish_no),
    CONSTRAINT uni_dish_name UNIQUE (dish_name),
    CONSTRAINT default_discount DEFAULT 1 FOR (discount),
    CONSTRAINT notnull_price NOT NULL (dish_name, price),
    CONSTRAINT ck_is_spicy CHECK (is_spicy IN ('不辣', ‘微辣', '辣', '很辣') )
)

CREATE TABLE Orders
(
    order_no INT IDENTITY(1000000,1) PRIMARY KEY,
    customer_no INT FOREIGN KEY REFERENCES Customer(customer_no) NOT NULL,
    employee_no CHAR(8) FOREIGN KEY REFERENCES Employee(employee_no),
    order_time DATETIME,
    people_num INT,
    service_feedback NUMERIC(2,1),
	total_price NUMERIC(6,2),
    
    CONSTRAINT pk_order_no PRIMARY KEY (order_no),
    CONSTRAINT notnull_customer_no NOT NULL (customer_no),
	CONSTRAINT fk_customer_no FOREIGN KEY (customer_no) REFERENCES Customer(customer_no),
    CONSTRAINT fk_employee_no FOREIGN KEY (employee_no) REFERENCES Employee(employee_no),
)

数据操作语言

数据的添加、更新和删除

数据的添加

假设Menu表的逻辑结构是Menu(dish_no, dish_name, discount, price, is_spicy)

/******** 往所有列上插入数据 ********/
INSERT INTO Menu
VALUES
('001', '小炒黄牛肉', 1.00, 66.00, '辣'),
('002', '柠檬脆皮鸭', 0.85, 58.00, '不辣')
GO

/******** 往指定列上插入数据 ********/
-- 用括号给出指定列的列名
INSERT INTO Menu (dish_no, dish_name, price, is_spicy)
VALUES
('001', '小炒黄牛肉', 66.00, '辣'),
('002', '柠檬脆皮鸭', 58.00, '不辣')
GO

INSERT后面也可以直接跟一张表（要求表的字段名和INSERT INTO的那张表匹配），这样可以将表中数据直接全部添加进去。如：

INSERT INTO Menu
SELECT * FROM Menu2

数据的更新

UPDATE Menu
SET price = 45.00
WHERE dish_no = '001'

数据的删除

DELETE语句删除的内容会记入日志，而TRUNCATE语句不会把每一行的删除操作记入日志

使用DELETE语句删除

/******** 删除表中所有行 ********/
DELETE Orders 
-- 等价于DELETE FROM Orders，因为FROM可以省略

/******** 删除表中某些行 ********/

-- 用WHERE字句筛选
DELETE FROM Orders
WHERE Order_id = '1000001'

-- 用TOP筛选
DELETE TOP 10 FROM Orders

使用TRUNCATE语句删除

TRUNCATE语句一次删去表中所有数据

TRUNCATE TABLE Orders

数据查询

简单的数据查询此处略，复杂的放在可编程性之后

可编程性

变量及常量

此处注意@开头为用户定义的变量，@@开头为全局变量

程序控制流语句

灵活使用CASE函数

例子：

Menu表中is_spicy 有4种取值('不辣', '微辣', '辣', '很辣')，现希望对其进行如下查询

（1）输出形如

口味	数量
不辣	2
微辣	3
辣	5
很辣	4

SELECT is_spicy AS 口味, COUNT(*) AS 数量
FROM Menu
GROUP BY is_spicy

（2）输出形如

微辣	不辣	辣	很辣
3	2	5	4

SELECT 
COUNT(CASE WHEN is_spicy = '微辣' THEN 1 ELSE 0 END) AS '微辣'
COUNT(CASE WHEN is_spicy = '不辣' THEN 1 ELSE 0 END) AS '不辣'
COUNT(CASE WHEN is_spicy = '辣' THEN 1 ELSE 0 END) AS '辣'
COUNT(CASE WHEN is_spicy = '很辣' THEN 1 ELSE 0 END) AS '很辣'
FROM Menu

函数

标量函数

CREATE FUNCTION function_name (@parameter_name parameter_type)
RETURNS return_type
AS
	function_body
	RETURN var

内嵌表值函数

CREATE FUNCTION function_name (@parameter_name parameter_type)
RETURNS TABLE 
AS
	function_body
	RETURN (SELECT column1, column2, ... )

多语句表值函数

CREATE FUNCTION function_name (@parameter_name parameter_type)
RETURNS @return_tablename TABLE (
        Name char(8),
        sno char(10)
)
AS
BEGIN
        function_body
        RETURN   —返回的是@return_tablename那张表
END

注：AS后面的BEGIN...END...均可省略

执行用户自定义函数

使用时需要加上架构名（dbo） 如执行标量函数DatetoQuarter的语句为SELECT dbo.DatetoQuarter(‘2020-4-5’) 执行表值函数dish_ranking的语句为SELECT * FROM dbo.dish_ranking()

修改、删除函数

修改使用ALTER TABLE

sp_help function_name查看函数的信息

sp_helptext function_name查看函数的定义

例子：（待完善）字符串类型的聚合统计查询 将表中的人名（name列）进行聚合（按行访问使用游标） 不使用游标的做法：将每一次取得的name累加

DECLARE @name varchar(200)
SET @name = ‘’
SELECT @name = @name + ‘, ’ name FROM student  — 但是第一个人名字之前有逗号，使用substring库函数

SET @name = substring(@name, 2, LEN(@name) )

实现字符串类型的聚合统计查询 抽象成自定义标量值函数

CREATE FUNCTION mergenames (＠tdno int)
RETURNS varchar (512)
AS
BEGIN
    DECLARE @allnames VARCHAR(512)
    SET @allnames=’’
    IF @tdno is null
    SELECT @allnames=@allnames +’, ’+RTRIM(LTRIM(sname) FROM student 
    ELSE
    SELECT @allnames=@allnames+’, ’ + RTRIM(LTRIM(sname) ) FROM student WHERE dno= @tdno
    RETURN substring (@allnames, 2, len(@allnames) )
END
 
SELECT dno, dbo.mergenames(dno) 
FROM student
GROUP BY dno

游标

游标的基本使用

1. 定义游标

DECLARE cur_customer CURSOR
FOR SELECT id, name, addr, tel
FROM customer

2. 打开游标

OPEN cur_customer

3. 读取游标

FETCH NEXT FROM cur_customer
FETCH PRIOR FROM cur_customer
FETCH FIRST FROM cur_customer
FETCH LAST FROM cur_customer

4. 关闭游标

CLOSE cur_customer

5. 释放游标

DEALLOCATE cur_customer

注: DEALLOCATE 命令只删除游标对命名变量的引用

进一步使用游标

使用游标函数

1. @@CURSOR_ROWS 返回游标结果集里上次打开并读取的行数 打开游标后，该函数返回当前游标的总行数 static 的 cursor 才能使用 cursor rows
2. CURSOR_STATUS 返回指定游标的状态
3. @@FETCH_STATUS 返回被 FETCH 语句执行的最后游标的状态(不是当前被打开的游标的状态) 返回值: 0 FETCH 语句成功 -1 FETCH 语句失败或者此行不在结果集里 -2 被提取的行不存在

用游标读取数据的一般方式

获取数据并存储到变量里面

-- 定义变量用于保存数据
DECLARE @tmpId CHAR(8), @tmpName CHAR(20), @tmpAddr CHAR(20), @tmpTel CHAR(12)

-- 定义游标
DECLARE cur_customer CURSOR
FOR SELECT id, name, addr, tel
FROM customer

-- 打开游标
OPEN cur_customer

-- 读取游标
FETCH NEXT FROM cur_customer INTO @tmpId, @tmpName, @tmpAddr, @tmpTel

WHILE @@fetch_status = 0
    BEGIN
    PRINT(@tmpId + @tmpName + @tmpAddr + @tmpTel)
    FETCH NEXT FROM cur_customer INTO @tmpId, @tmpName, @tmpAddr, @tmpTel  -- 游标所在行的内容被按顺序读取至 4 个变量中
    END

-- 关闭游标
CLOSE cur_customer

-- 释放游标
DEALLOCATE cur_customer

使用变量作为游标名(游标变量)

-- 定义游标变量
DECLARE @cur_var CURSOR

-- 方式 1: 定义游标，再通过游标名赋值给游标变量
DECLARE cur_customer CURSOR
FOR SELECT * FROM customer

SET @cur_var = cur_customer

-- 方式 2: (常用)创建游标，直接赋值给游标变量
SET @cur_var = CURSOR FOR SELECT * FROM customer

滚动游标的控制

用 SCROLL 声明游标以后，在 FETCH 语句中可以用: NEXT滚动到下一条 PRIOR滚动到前一条 FIRST滚动到第一条 LAST滚动到最后一条 ABSOLUTE 滚动到第几行 RELATIVE 正值为向下滚动的行数，负值为向上滚动的行数

存储过程

存储过程是 SQL 语句或控制流语言的已命名集合。可以为常用功能创建存储过程以提高性能。SAP ASE 也提供系统过程来执行可更新系统表的管理任务。 存储过程可以:

• 带参数
• 调用其它过程
• 把状态值返回给调用过程或批处理,以指明成功或失败,以及失败的原因
• 把参数的值返回给调用过程或批处理
• 在远程服务器上执行

创建存储过程

CREATE PROCEDURE procedureName @param_in [,...], @param_out OUTPUT [,...]
AS
	procedure_body

例子：

CREATE PROCEDURE add_to_cart @dish_no CHAR(8) ,@order_no INT
AS
	INSERT INTO Orders_content
	VALUES(@order_no, @dish_no, NULL)
GO

执行存储过程

EXEC procedure_name value01 [,...], @var OUTPUT [,...]

例子：

DECLARE @order_no INT
EXEC add_to_cart '001', @order_no OUTPUT

触发器

触发器是一种特殊的存储过程

CREATE TRIGGER trigger_name
ON table_name
[WITH ENCRYPTION]
[FOR | AFTER | INSTEAD OF] [INSERT | DELETE | UPDATE]
AS
    ......

按发生的时间分: instead of，after 触发器 instead of 触发器: （事前预防） 生成两张临时表: inserted 和 deleted (如果合法，那么执行更新数据的操作) after 触发器：（事后检查）在操作完成后执行。 也生成两张临时表然后直接进行数据更改。可以撤销操作所造成的后果(触发器工作在事务模式下，如果检测出操作不合法，那么执行 rollback，如果合法，那么 after 触发器不执行任何操作) 在一张表中可以有多个

没有指定类型时是 after 类型的触发器

例子：

CREATE TRIGGER add_total_price
ON Orders_content
FOR INSERT
AS
	DECLARE @price NUMERIC(6,2)
	SELECT @price = SUM(price*discount)
	FROM inserted INNER JOIN Menu ON inserted.dish_no=Menu.dish_no
	UPDATE Orders
	SET total_price = (total_price + @price)*discount
	WHERE order_no IN (SELECT order_no FROM inserted)
GO

DML 触发器:

• 强制执行比 CHECK 约束更为复杂的约束
• 以多条语句的方式实行完整性检查
• CHECK Constraint 是在 CREATE TABLE 的时候设置的，只能针对一张表中的数据，跨越两张表的完整性检查只能通过 DML 触发器检查
• 一张表上可以定义多个同类型的 DML触发器
• 主外键约束的完整性(跨越表)。使用 DML 跨越数据库的完整性约束(引用完整性 DRI 是不能跨越数据库的)
• INSTEAD OF 触发器通过了，但触发器表的约束被违反，那么触发器运行后被回滚(执行顺序: INSTEAD OF 触发器->表约束->AFTER 触发器)

多个触发器的执行顺序

如果有多个 AFTER 触发器，那么可以通过sp_settriggerorder可以执行第一个和最后一个执行的触发器，中间其它触发器的顺序无法保证

同类型的触发器不会导致递归(不会出现一个触发器执行之后调用自身)

定义触发器的时候的规则

• CREATE TRIGGER 必须是批处理中的第一个语句
• 如果表有外键定义了级联 DELETE/UPDATE 操作(CASCADE)那么无法在此表定义 DELETE/UPDATE 触发器

删除触发器

DROP TRIGGER triggerName

事务管理语言

事务

事务的属性: 原子性、一致性、隔离性、持久性

原子性: 保证一个整体 一致性: 事务执行前数据一致、执行后也一致 隔离性: 事务之间互相不影响。事务完成之前，对数据库的修改对于其他事务不可见 持久性: 事务完成后，它对系统的影响是永久的

事务模式

显式事务，隐式事务，自动事务模式

启动事务语句

BEGIN TRANSACTION transaction_name

COMMIT TRANSACTION transaction_name

transaction_name 既可以用常量也可以使用变量

事务回滚(ROLLBACK)

一旦出错，进行回滚，恢复到事务执行之前的状态

使用 ROLLBACK 通常结合 IF 语句来写(若检测到事务无法继续的因素，则进行回滚处理)

ROLLBACK语法格式:

ROLLBACK TRANSACTION transaction_name

当T-SQL 语句产生运行时错误时，让 SQL SERVER 自动回滚当前事务: SET XACT_ABORT ON (不自动回滚为SET XACT_ABORT OFF 指的是回滚当前出错命令，但是该事务中已经执行的其它命令不会被回滚)

事务由大到小: 任务-进程-线程 线程是操作系统执行的最小单位

数据控制语言

角色与权限管理

例子：

EXEC sp_addrole 'customer_role'
EXEC sp_addrole 'employee_role'
EXEC sp_addrole 'manager_role'

GRANT SELECT, INSERT, UPDATE ON Customer TO customer_role
GRANT SELECT, INSERT, UPDATE ON Orders TO customer_role
GRANT SELECT, INSERT, UPDATE ON Orders_content TO customer_role

GRANT EXECUTE ON add_to_cart TO customer_role
GRANT EXECUTE ON check_is_ordering TO customer_role

登录账号和数据库用户

登录账号在服务器级创建，数据库用户在数据库级创建

数据库用户总是与某一登录账号相关联，一个登录账号可以关联多个数据库用户（一对多）

权限

权限的种类

1. 对象权限
2. 语句权限
3. 隐含权限

权限管理

GRANT赋予权限

DENY禁止权限

REVOKE收回已赋予的权限

角色

角色的分类

• 固定角色
  • 固定服务器角色
  • 固定数据库角色
• 用户定义数据库角色

数据库的备份与恢复

备份

恢复

SQL Server的三种恢复模型：

• 简单恢复模型：恢复到上次备份处
• 完全恢复模型：恢复到特定时间点
• 大容量日志记录恢复模型：介于简单恢复模型和完全恢复模型之间（在性能和日志占用空间方面找到平衡）