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

本是古典何须时尚 2022-05-30 06:51 148阅读 0赞

## 游标 ##

游标（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;