SQL学习笔记7 游标 约束 索引 触发器

游标

游标（cursor）是一个存储在DBMS服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动浏览其中的数据。
Access不支持游标
使用游标的步骤：

• 声明定义游标（定义要使用的SELECT语句）
• 打开游标（执行SELECT语句）
• 根据需要取出各行
• 关闭游标，可能的话，释放游标（某些DBMS）

创建游标

DECLARE custcursor CURSOR
FOR
SELECT * FROM customers
WHERE cust_email IS NULL

• DB2、MariaDB、MySQL、SQL server版本

DECLARE CURSOR custcursor
IS
SELECT * FROM customers
WHERE cust_email IS NULL

• Oracle、PostgreSQL版本

打开游标

OPEN CURSOR custcursor

使用游标
SQL Server

DECLARE @id INT
DECLARE @name NVARCHAR(100)
DECLARE c_department CURSOR FOR  -- 声明游标
  SELECT id,name from department
OPEN c_department  -- 打开游标
FETCH next FROM c_department INTO @id,@name -- 获取对应的行，并将数据赋给声明的变量中
WHILE @@FETCH_STATUS=0 -- 取不出更多行时终止循坏
BEGIN 
  --代码
  --代码
  FETCH next FROM c_department INTO @id,@name
END
CLOSE c_department -- 关闭游标
DEALLOCATE c_department --释放游标

• 当游标被打开时，行指针将指向该游标集第1行之前，如果要读取游标集中的第1行数据，必须移动行指针使其指向第1行
• 使用FETCH指出要检索哪些行，从何处检索以及将它们放到何处，可以加入循环

关闭游标

CLOSE custcursor

• 游标一旦关闭，若不再次打开，将不能使用，第二次使用无需声明，直接打开

释放游标

DEALLOCATE custcursor

• 某些DBMS要求释放游标，如SQL server

约束

管理如何插入或处理数据库数据的规则

使用PRIMARY KEY创建主键

CREATE TABLE vendors
(
    vend_id CHAR(10) NOT NULL PRIMARY KEY,
    vend_name CHAR(50) NOT NULL,
    vend_address CHAR(50) NULL
);

使用CONSTRAINT添加主键

ALTER TABLE vendors
ADD CONSTRAINT
PRIMARY KEY (vend_id);

使用REFERENCES添加外键

CREATE TABLE vendors
(
    vend_id CHAR(10) NOT NULL PRIMARY KEY,
    vend_name CHAR(50) NOT NULL,
    vend_address CHAR(50) NOT NULL REFERENCES customers (cust_id)
);

使用CONSTRAINT添加外键

ALTER TABLE orders
ADD CONSTRAINT
FOREIGN KEY (cust_id) REFERENCES customers (cust_id)

• 有些DBMS支持级联删除（cascading delete），该特性在从一个表删除行时，会自动删除所有相关的数据

唯一约束 UNIQUE
保证该列的数据是唯一的，类似于主键，但可以修改，且可包含NULL值。唯一约束不可用于定义外键

CREATE TABLE vendors
(
    vend_id CHAR(10) NOT NULL PRIMARY KEY,
    vend_name CHAR(50) NOT NULL,
    vend_address CHAR(50) NOT NULL UNIQUE
);

检查约束CHECK
保证某列或一组列的数据满足一组指定的条件

CREATE TABLE orderitems
(
    order_num INTEGER NOT NULL,
    prod_id CHAR(10) NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity > 0)
);

ALTER TABLE customers
ADD CONSTRAINT 
CHECK (gender LIKE '[MF]')

索引

排序数据用来加快搜索和排序操作的速度，可以在一个或多列上定义索引。

• 索引虽然加快了检索操作的性能，却降低了插入、修改和删除的性能。
• 索引数据会占据存储空间
• 索引名必须唯一命名

CREATE INDEX prod_name_ind
ON products（prod_name）

• 不同的DBMS创建索引的语句可能不同

触发器

特殊的存储过程，它在特定的数据库活动发生时自动执行，比如INSERT ，UPDATE ，DELETE操作等。触发器与单个表关联。

用途：

• 保持数据一致
• 基本某个表的变动在其他表上执行活动
• 进行额外的验证并根据需要回退数据
• 计算计算列的值或更新时间戳

SQL server

CREATE TRIGGER customer_state
ON customers
FOR INSERT, UPDATE
AS
UPDATE customers
SET cust_state = Upper(cust_state)
WHERE customers.cust_id = inserted.cust_id;