《SQL必知必会》学习笔记重点！

有印象较简单的

检索不同值：distinct

使用distinct在字段名前，只返回不同值。distinct作用于所有列，除非两列完全相同，否则所有行都将被检索出来。

select distinct vend_id from Products;

排序：order by(desc降序，默认是asc升序)

要保证order by是select语句的最后一条子句.
用非检索的字段排序数据是ok的.
关键词DESC(descending)和ASC(ascending)只作用直接位于前面的列名.

select prod_name
from Products
order by prod_name desc, prod_price asc; --desc降序，asc升序

过滤数据

where子句在表名(from子句)后给出.order by子句在其后.
单引号限定字符串，数值不用引号.
between检索两个值范围区间,包括开始值和结束值.

select prod_name, prod_price
from Products
where prod_price between 5 and 10;

空值检查:IS NULL

select cust_name
from CUSTOMERS
where cust_email IS NULL;

不匹配条件过滤时，NULL是未知的意思，不会返回NULL值的记录.

#多条件过滤–AND和OR
select prod_id, prod_price, prod_name
from Products
where vend_id = ‘DLL01’ AND prod_price <= 4;

求值顺序：()>AND>OR

IN取一组由逗号分隔、括在()中的合法值.

select prod_name, prod_price
from Products
where vend_id IN (‘DLL01’, ‘BRS01’)
order by prod_name;

NOT用来否定其后条件

#等价于检索 where vend_id <> ‘DLL01’
select prod_name
from Products
where NOT vend_id = ‘DLL01’
order by prod_name;

用通配符过滤(模糊查询！！！重要哦)

LIKE操作符，后跟的搜索模式利用通配符匹配而不是相等匹配。
通配符搜索只适用文本字段(字符串).
通配符：%,__, [].

#%搜索串中，任何字符出现任意次数，唯独不匹配NULL.
#%匹配0、1和多个字符.
select prod_id, prod_name
from Products
where prod_name LIKE ‘Fish%’;
#where prod_name LIKE ‘%bean bag%’; --%代表任意字符除了Null
#where prod_name LIKE ‘F%y’; 案例：搜索邮箱地址
#匹配Fish后跟任何字符的行

![[Pasted image 20240420195543.png]]

![[Pasted image 20240420195520.png]]

#_ 只匹配一个字符
select prod_id, prod_name
from Products
where prod_name LIKE ‘__ inch teddy bear’;

#[]指定字符集，匹配通配符位置的一个字符
#[]通配符的否，可以在[]内的字符前^
select cust_contact
from customers
where cust_contact LIKE ‘[JM]%’
order by cust_contact;

无印象的

计算字段：不实际存在于数据库表中，运行select语句内创建的。

只有数据库知道select语句中哪些是表列和计算字段，客户端中计算字段数据和表列数据返回方式相同。

拼接字段：列值联结构成单个值(一个值附加到另一个值)

方法：使用 + 或者 ||

#+可直接替换成||
#mysql和mariaDB使用函数，而不是+或||
#列名可被AS替换成别名，别名可以是单词/字符串(字符串要在引号内)
#客户端可直接按vend_title引用这个虚拟列
#TRIM()函数去掉字符串左右两边空格，(L)RTRIM去掉字符串(左)右边空格.
#vend_name供应商名，vend_country供应商所属国家,Vendors供应商表

select RTRIM(vend_name) + ’ (’ + RTRIM(vend_country) + ‘)’ as vend_title
from Vendors
order by vend_name;

算术计算

#计算订单中的物品总价
#prod_id订单号，quantity数量, item_price物品单价
#客户端应用可以直接使用expanded_price

select prod_id, quantity, item_price, quantity*item_price as expanded_price
from orderItems
where order_num = 2008;

![[Pasted image 20240104193105.png]]

函数 (不同DBMS函数兼容不同，注意做好注释)

#UPPER()函数将文本转换为大写

select vend_name, UPPER(vend_name) AS vend_name_upcase
from Vendors
order by vend_name;

包含：文本字符串、数值、日期时间处理函数、汇总聚集函数
![[Pasted image 20240106155645.png]]

#avg()只用于单个数值列，列名作为函数参数。多个列平均值须使用多个avg函数.
#avg()忽略列值NULL的行.
#DLL01是供应商id
select avg(prod_price) AS avg_price
from Products
where vend_id = ‘DLL01’;

count() 计算表的行数

count(*/列名)函数，参数是*时，数目计数包含Null。参数是列名时，忽略Null值.

参数为*检索出5，cust_email检索出3，表示5个顾客3个留邮箱地址

select count(*/cust_email) AS num_cust
from Customers;

MAX(列名)返回指定列最大值，参数列名必选

MAX()用于文本数据时，返回列排序后最后一行

MAX()忽略列值为NULL的行

MIN()函数用于文本数据时，返回列排序后最前一行，忽略列值为NULL的行

select MAX(prod_price) AS max_price
from Products;

聚集不同值需使用distinct，all则对所有行执行(all是默认参数行为).

#distinct指定不同价格进行avg()计算
#distinct不用于count(*),但可用于count(列名)

select avg(DISTINCT prod_price) AS avg_price
from Products
where vend_id = ‘DLL01’；

组合聚集
select COUNT(*) as num_items, --计数所有记录
MIN(prod_price) as price_min, --聚集出价格最小值
MAX(prod_price) as price_max,
AVG(prod_price) as price_avg
from Products;

数据分组

#group by子句按vend_id排序并分组数据，对每个vend_id而不是整个表计算num_prods.
#group by子句列出的每一列必须是检索列/有效表达式，不能是聚集函数
#除聚集计算语句外，SELECT 语句中的每一列都必须在 GROUP BY 子句中给出。
#如果分组列中包含具有 NULL 值的行，则 NULL 将作为一个分组返回.

GROUP BY 必须在 WHERE 后, ORDER BY 前.

select vend_id, COUNT(*) AS num_prods
from Products
GROUP BY vend_id;

过滤分组 (包括哪些分组，排除哪些分组)

where过滤行，Having过滤分组
WHERE 在数据分组前进行过滤，HAVING 在数据分组后进行过滤

过滤分组

检索并根据客户id进行分组，基于分组聚集值过滤行数>=2的分组数据.

orders订单表，每一行具备唯一订单id

select cust_id, COUNT(*) as orders
from Orders
Group by cust_id
Having COUNT(*) >= 2;

产品表，具有唯一产品id

检索供应商id，筛选产品价格>=4，以供应商id分组，过滤分组后产品id>=2.

select vend_id, COUNT(*) AS num_prods
from Products
where prod_price >= 4
group by vend_id
HAVING COUNT(*) >= 2;

唯一不重复字段是quantity(数量), group by按订单号分组，

COUNT(*)返回每个订单物品数量，HAVING只返回>=3数量的订单

SELECT order_num, COUNT(*) AS items
FROM OrderItems
GROUP BY order_num
HAVING COUNT(*) >= 3
ORDER BY items, order_num;

子查询（常用于Where子句的IN操作符中，和填充计算列）

子查询SELECT语句只能查询单个列。企图检索多个列将返回错误.

select cust_name, cust_contact
from custmers
where cust_id IN ( select cust_id
from orders
where order_num IN (select roder_num
from orderitems
where prod_id=‘RGAN01’) );

计算字段使用子查询

完全限定列名Orders.cust_id = Customers.cust_id

检索顾客订单数orders，以顾客名排序

SELECT cust_name, cust_state, (SELECT COUNT(*)
FROM Orders
WHERE Orders.cust_id = Customers.cust_id) AS orders
# 替换成cust_id = cust_id，会结果出错检索所有订单数
FROM Customers
ORDER BY cust_name;

完全限定列名
两个表Orders、Custmers，在select语句中操作多个表使用Orders.cusi_id与Custmers.cust_id就是完全限定列名.

联结表 (联结是在运行select中构造的，表无法进行定义)

简单联结

where子句不能落下，会形成笛卡尔积错误检索数据

where的过滤条件要正确

SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;

内联结(INNER JOIN)

与上面Vendors.vend_id = Products.vend_id例子相同的select语句

两个表以INNER JOIN指定内联结关系成为from子句的一部分

联结条件ON子句给出

select vend_name, prod_name, prod_price
from Vendors INNER JOIN Products
ON Vendors.vend_id = Products.vend_id;

联结多个表(SQL不限制select可联结表数目，但性能消耗递增)

与往前第4个的检索一致，此例使用联结

select cust_name, cust_contact
from Customers, Orders, OrderItems
Where Customers.cust_id = Orders.cust_id --联结条件
AND OrderItems.order_num = Orders.order_num --联结条件
AND prod_id = ‘RGAN01’; --过滤条件

表别名(与列别名不一样，表别名不返回到客户端)

将表名起为别名

SELECT cust_name, cust_contact
FROM Customers AS C, Orders AS O, OrderItems AS OI
WHERE C.cust_id = O.cust_id
AND OI.order_num = O.order_num
AND prod_id = ‘RGAN01’;

三种其他联结:自联结(self-join)、自然联结(natural join)、外联结(outer join)

自联结

处理联结比子查询快

虽然两个表相同，但引用时会有歧义性，使用表别名比较好

select c1.cust_id, c1.cust_name, c1.cust_contact
FROM Customers AS c1, Customers AS c2
WHERE c1.cust_name = c2.cust_name
AND c2.cust_contact = ‘Jim Jones’;

自然联结

第一个使用*，剩下其他列明确指出

内联结都是自然联结

SELECT C.*, O.order_num, O.order_date,
OI.prod_id, OI.quantity, OI.item_price
FROM Customers AS C, Orders AS O, OrderItems AS OI
WHERE C.cust_id = O.cust_id
AND OI.order_num = O.order_num
AND prod_id = ‘RGAN01’;

外联结：包含联结相关表中无关联的行(左外联结、右外联结)

内联结法，检索所有顾客及其订单

select Customers.cust_id, Orders.order_num
FROM Customers INNER JOIN Orders
ON Customers.cust_id = Orders.cust_id;

外联结法，检索包含没有订单顾客在内的所有顾客

RIGHT/LEFT关键字指定包含所有行的表

select Customers.cust_id, Orders.order_num
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id;

全外联结：包含两个表的不关联所有行

#部分数据库不支持FULL OUTER JOIN
select Customers.cust_id, Orders.order_num
From Orders FULL OUTER JOIN Customers
ON Orders.cust_id = Customers.cust_id;

带聚集函数的联结

聚集函数与内联结

select Customers.cust_id, COUNT(Orders.order_num) AS num_ord
FROM Customers INNER JOIN Orders
ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;
![[Pasted image 20240121162815.png]]

聚集函数与外联结

使用左外联结包含所有顾客(包含无订单顾客)

select Customers.cust_id, COUNT(Orders.order_num) AS num_ord
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Order.cust_id
GROUP BY Customers.cust_id;
![[Pasted image 20240121163426.png]]

注意：具体联结语法需查阅具体DBMS文档，联结条件要正确否则返回错误数据

组合查询(UNION)

将多条select语句用union组合起来

select cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN (‘IL’, ‘IN’, ‘MI’);

select cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = ‘Fun4All’;

#union会自动取消两个查询重复的行
#不想取消可以使用Union All
select cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN(‘IL’, ‘IN’, ‘MI’)
UNION
select cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = ‘Fun4All’;

---

#等同以下
select cust_name, cust_contact, cust_email
FROM Customers
where cust_state IN (‘IL’, ‘IN’, ‘MI’)
OR cust_name = ‘Fun4All’;

union使用规则
1.必须有2条及以上select语句组成
2.组合中的每个查询必须包含相同列、表达式/聚集函数
3.列数据类型必须兼容，类型可不同，但需DBMS可以隐含转换的类型(例如不同的数值类型/日      期类型)

order by只能位于最后一条select语句之后，对所有select语句的结果集进行排序.

select cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN(‘IL’, ‘IN’, ‘MI’)
UNION
select cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = ‘Fun4All’;
ORDER BY cust_name, cust_contact;

数据插入(insert)

插入完整行

insert into Customers(cust_id, cust_name, cust_address, cust_city,
cust_state, cust_state, cust_zip, cust_country,cust_contact, cust_email)
values(‘10086’, ‘Toy Land’, ‘123 Any Street’, ‘New York’, ‘NY’, ‘11111’,
‘USA’, NULL, NULL);

插入部分行

如果表的定义允许，可以在insert中省略某些列.
eg: 1. 列定义允许NULL值. 2. 表定义中给出默认值，如果不给出值，将使用默认值.

insert into Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip,
cust_country)
values(‘1006’, ‘Toy Land’, ‘123 Any Street’, ‘New York’, ‘NY’, ‘11111’, ‘USA’);

插入检索出的数据(insert select)

insert一般插入一行，insert select可插入多行.

insert into Customers(cust_id, cust_contact, cust_emal, cust_name, cust_address,
cust_city, cust_state, cust_zip, cust_country)
select cust_id, cust_contact, cust_email, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country
FROM CustNew;

从一个表复制到另一个新表(SELECT INTO)

#将customers表的内容复制到新表custcopy
select *
into CustCopy
From Customers;

#MySQL、Oracle、PostgreSQL、SQLite语法不同
CREATE table CustCopy AS
Select * FROM Customers;

更新和删除数据(Update、delete)

Update

Update Customers --要更新的表
set cust_email = ‘wwdqylh@foxmail.com’ --列名和它们的新值
where cust_id = ‘1005’ --确定要更新哪些行的过滤条件

#更新多个列
Update Customers
set cust_contact = ‘Sam Roberts’,
cust_email = ‘wwdqylh@foxmail.com’
where cust_id = ‘1005’;

#删除某列的值
Update Customers
set cust_email = NULL --假定此列允许NULL，去除此列的值.
where cust_id = ‘1005’;

Delete(只能删除行，不能删除列，只删除表内容，不删除表本身)

#删除Customers表中cust_id=1005的行
Delete From Customers
where cust_id = ‘1005’;

注意：truncate table删除表所有行，速度更快(不记录数据变动)

创建和操纵表(Create)

create table Products
(
prod_id char(10) NOT NULL,
vend_id char(10) NOT NULL,
prod_name char(254) NOT NULL,
prod_price decimal(8 , 2) NOT NULL,
prod_desc varchar(1000) NULL --NULL为默认设置，如果不指定NOT NULL
);

注意：替换现有的表，要先删除该表再创建.

指定默认值(default)

create table OrderItems
(
order_num INTEGER NOT NULL,
order_item INTEGER NOT NULL,
prod_id CHAR(10) NOT NULL,
quantity INTEGER NOT NULL DEFAULT 1,
item_price DECIMAL(8, 2) NOT NULL
);

更新表列(alter)

注意：alter表有许多限制，具体DBMS需查阅具体文档.

ALTER TABLE Vendors
add vend_phone char(20);

#删除表的vend_phone列
alter table Vendors
drop column vend_phone;

删除表(DROP)

#完整删除表CustCopy，要先确认，执行此语句永久删除无法撤销.
drop table CustCopy;

重命名表

DB2、MariaDB、MySQL、Oracle、PostgreSQL使用rename语句
SQL Server使用sp_rename存储过程
SQLite使用Alter table

使用视图

视图（虚拟的表）：只包含动态检索数据的查询，不包含数据本身

注意： 创建视图用create view只能用于创建不存在的视图；删除视图使用Drop view viewname；覆盖或更新视图，必须先删除再重新创建.

select cust_name, cust_contact
FROM ProductCustomers
WHERE prod_id = ‘RGAN01’;

#创建视图，返回订购了任意产品所有顾客的列表
Create view ProductCustomers AS
select cust_name, cust_contact, prod_id
FROM Customers, Orders, OrderItems
Where Customers.cust_id = Orders.cust_id
And OrderItems.order_num = Orders.order_num;

#检索订购产品RGAN01的顾客
select cust_name, cust_contact
From ProductCustomers --这个是视图
where prod_id = ‘RGAN01’;

注意：创建不绑定特定数据的视图，让视图重用范围更广.

视图格式化检索数据

select RTRIM(vend_name) || ‘(’ || RTRIM(vend_country) || ‘)’ as vend_title
From Vendors
order by vend_name;

create view VendorLocations as
select RTRIM(vend_name) || ‘(’ || RTRIM(vend_country) || ‘)’ as vend_title
from vendors;

select *
from VendorLocations;

用视图过滤不要的数据

#过滤没有电子邮箱的顾客
create view CustomerEMailList AS
select cust_id, cust_name, cust_email
from Customers
where cust_email IS NOT NULL;

select *
from CustomerEMilList;

使用视图与计算字段

select prod_id, quantity, item_price, quantity * item_price AS expanded_price
From OrderItems
where order_num = 2008;

create view OrderItemsExpanded AS
select order_num, prod_id, quantity, item_price, quantity * item_price AS expanded_price
from OrderItems;

select *
from OrderItemsExpanded
where order_num = 2008;

存储过程(批文件，保存的SQL语句)

注意：编写和执行存储过程的权限是区分开的.

执行存储过程(EXECUTE)

execute接受存储过程名和需要传递的参数.

#将一个新产品添加到Products表中.
EXECUTE AddNewProduct(‘JTS01’, ‘Stuffed Eiffel Tower’, 6.49,
‘Plush stuffed toy with the text La Tour Eiffel in red white and blue’ );

创建存储过程

Oracle版本

#Oracle版本创建存储过程
#对邮件发送清单中具有邮件地址的顾客进行计数
#Oracle中IN(传递值给存储过程)，OUT(存储过程返回值),INOUT(即传递也返回值)
#存储过程代码在BEGIN和END语句中
#select语句检索具有邮件地址顾客，计数行数来设置ListCount.
Create PROCEDURE MailingListCount(
ListCount OUT INTEGER
)
IS
v_rows INTEGER;
BEGIN
select Count(*) INTO v_rows
From Customers
Where not cust_email is null;
ListCount := v_rows;
END;

var ReturnValue NUMBER --声明变量保存存储过程返回值
EXEC MailingListCount(:ReturnValue); --执行存储过程
select ReturnValue; --显示返回值

SQL Server版本

Create procedure MailingListCount
AS
DECLARE @cnt INTEGER --声明@cnt局部变量，SQLServer局部变量名以@起头
SELECT @cnt = COUNT(*)
from Customers
where not cust_email is null; --分号，查询语句到此结束
return @cnt; --return语句将计数返回调用程序

declare @ReturnValue INT --声明变量
execute @ReturnValue = MailingListCount; --执行存储过程
select @ReturnValue; --显示返回值

#Orders订单表插入新订单
Create PROCEDURE NewOrder @cust_id CHAE(10)
AS
– Declare variable for order number
DECLARE @order_num INTEGER
– Get current highest order number
select @order_num = MAX(order_num)
from Orders
– Determine next order number
select @order_num = @order_num + 1
– Insert new order
Insert into Orders(order_num, order_date, cust_id)
values(@order_num, GETDATE(), @cust_id)
– Return order number
RETURN @order_num; --返回订单号

另一种插入新订单

#SQL Server中自动增量的列为标识字段（identity field）
#其他DBMS称为自动编号（auto number）或序列（sequence）

create procedure Neworder @cust_id CHAR(10)
as
– insert new order
insert into Orders(cust_id)
values(@cust_id)
– return order number
/SQL server给我们提供了一个全局变量@@identity,它可以获得刚刚插入成功的自增列列的信息，需要注意，如果一次插入多条数据，这个@@identity就是最后一条记录的自增列的信息；如果表中没有自增长列，@@identity的值就是NULL。/
select order_num = @@IDENTITY; --全局变量@@IDENTITY

事务处理(commit,rollback,确保成批SQL操作完全(不)执行)

概念：事务处理是一种机制，用来管理必须成批执行的 SQL 操作，保证数据库不包含不完整的操作结果。

术语：
事务（transaction）一组 SQL 语句
回退（rollback）撤销指定 SQL 语句的过程
提交（commit）将未存储的 SQL 语句结果写入数据库表
保留点（savepoint）指事务处理中设置的临时占位符（placeholder），可以对它发布回退（与回退整个事务处理不同）

注意：事务处理可回退insert,update,delete.不可回退select,create,drop操作.

控制事务处理

有的DBMS明确标识事务处理块的开始和结束：

– SQL Server版
begin transaction
…
commit transaction
– MariaDB和MySQL版本
start transaction
…
– Oracle版本
set transaction
…
–PostgreSQL版本使用ANSI SQL语法
begin
…

commit–保存更改，rollback–撤销.

–SQL中使用rollback撤销SQL语句
delete from Orders;
ROLLBACK;

隐式提交：SQL语句提交(写/保存)操作自动进行.
事务处理块中，提交不会隐式进行.

–SQL Server明确提交，使用commit
–系统中删除订单12345
–更新两个表，用事务处理保证订单不被部分删除.
–如果第一条delete起作用，第二条失败，则delete不会提交.
begin transaction
delete OrderItems where order_num = 12345
delete Orders where order_num = 12345
commit transaction

–Oracle版本
set transaction
delete OrderItems where order_num = 12345;
delete Orders where order_num = 12345;
commit;

保留点(savepoint):回退部分事务到保留点位置.

#创建占位符
savepoint delete1;

#在SQL Server中，创建保留点
save transaction delete1;

–回退到保留点,SQL Server版本
rollback transaction delete1;

–MariaDB、MySQL、Oracle版本
rollback to delete1;

begin transaction
insert into Customers(cust_id, cust_name)
values(‘1005’, ‘Toys’);
save transaction StartOrder; --新建保留点
insert into Orders(order_num, order_date, cust_id)
value(20100, ‘2001/12/1’, ‘1000010’);
– 如果@@ERROR 返回一个非 0 的值，表示有错误发生，事务处理回退到保留点
IF @@ERROR <> 0 rollback transaction StartOrder;
insert into OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20100, 1, ‘BR01’, 100, 5.49);
IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity,item_price)
VALUES(20100, 2, ‘BR03’, 100, 10.99);
IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;
commit transaction --整个事务处理成功，发布 COMMIT 以保留数据.

游标(被定义的select语句检索出来的结果集)

使用游标

步骤：
	1.声明游标，定义使用的select语句
	2.打开游标，用前面select语句把实际数据检索出来
	3.将填有数据游标，取出需要的记录
	4.关闭游标，释放游标.

创建游标

#DB2、MariaDB、MySQL、SQL Server版本
declare CustCursor CURSOR --declare声明游标
for
select * from Customers --定义select语句
where cust_email is null

#Oracle、PostgreSQL版本
declare CURSOR CustCursor
IS
select * from Customers
where cust_email is null

使用游标

open CURSOR CustCursor --执行查询，存储检索出的数据供浏览和滚动

#Oracle版本，从游标中检索第一行的记录
declare type CustCursor is REF CURSOR
return Customers%ROWTYPE
begin
open CustCursor;
fetch CustCursor into CustRecord; --从游标取出一行记录放入变量
close CustCursor;
end;

#Oracle版本
declare type CustCursor is ref CURSOR
return Customers%ROWTYPE;
declare CustRecord Customers%ROWTYPE --rowtype等于记录一行的意思
begin
open CustCursor;
LOOP --循环
FETCH CustCursor INTO CustRecord;
EXIT when CustCursor%NOTFOUND; --循环结束条件，当游标没有下一行了推出
…
END LOOP; --循环结束语句
CLOSE CustCursor;
end;

#SQL Server版本
/为每个列声明变量/
declare @cust_id char(10),@cust_name CHAR(50), @cust_address CHAR(50),
@cust_city CHAR(50), @cust_state CHAR(5), @cust_zip CHAR(10),
@cust_country CHAR(50), @cust_contact CHAR(50), @cust_email CHAR(255)
open CustCursor
/fetch检索一行并保存值到变量中/
fetch next from CustCursor
into @cust_id, @cust_name, @cust_address, @cust_city, @cust_state, @cust_zip, @cust_country, @cust_contact, @cust_email
while @@FETCH_STATUS = 0 --当取不出更多行时，终止处理
begin
fetch next from CustCursor
into @cust_id, @cust_name, @cust_address, @cust_city, @cust_state, @cust_zip, @cust_country, @cust_contact, @cust_email
end
close CustCursor

关闭游标

–DB2、Oracle、PostgreSQL版本
close CustCursor

SQL Server版本要求释放游标资源
close CustCursor
deallocate CURSOR CustCursor

注意：游标关闭后，再次使用需要open打开，但不需要再声明.

高级特性

约束(constraint):表定义中定义.

主键

create table Vendors
(
vend_id char(10) not null primary key, --约束主键
vend_name char(50) not null,
vend_address char(50) null,
vend_city char(50) null,
vend_state char(5) null,
vend_zip char(10) null,
vend_country char(50) null
);

alter table Vendors
add constraint primary key(vend_id);

注意：SQLite不允许alter table定义键，只能create table定义.

外键

#定义外键
create table Orders
(
order_num integer not null primary key,
order_date datetime not null,
–references表示cust_id的任何值必须是Customers表的cust_id中的值
cust_id char(10) not null references Customers(cust_id)
);

#添加外键
alter table Orders
add constraint
foreign key (cust_id) references Customers (cust_id)

级联删除：从一个表删除行会删除所有相关数据.

唯一约束(Unique)：保证一列(一组列)中的数据是唯一的.

与主键存在区别：
1.表可包含多个唯一约束，但每个表只允许一个主键。
2.唯一约束列可包含 NULL 值。
3.唯一约束列可修改或更新。
4.唯一约束列的值可重复使用。 
5.与主键不一样，唯一约束不能用来定义外键。

create table Orders
(
order_num integer not null primary key,
order_date datetime not null unique,
)

alter table Orders
add unique (order_date)

检查约束：保证一列(一组列)的数据满足指定条件.

create table OrderItems
(
order_num integer not null,
order_item integer not null,
prod_id char(10) not null,
quantity integer not null check (quantity > 0), --检查约束，保证物品数量大于0
item_price money not null
);

#检查gender列只包含M/F
add constraint check (gender like ‘[MF]’)

索引(Index)：用来排序数据以加快搜索和排序操作的速度

create index prod_name_ind --索引要唯一命名
on Products (prod_name);

索引分为聚集索引和非聚集索引

触发器：特殊存储过程，特定的数据库活动发生时自动执行.

与存储过程不同，触发器与单个表相关联.

触发器内的代码具有以下数据的访问权：
INSERT 操作中的所有新数据；
UPDATE 操作中的所有新数据和旧数据；
DELETE 操作中删除的数据.

注意：触发器可在特定操作执行之前(之后)执行

#SQL Server版本
–创建触发器，对所有insert、update操作，把表Customers的cust_state列转换为大写
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;

#Oracle、PostgreSQL版本
create trigger customer_state
after insert or update
for each row
begin
update Customers
set cust_state = Upper(cust_state)
where Customers.cust_id = :OLD.cust_id
end;

数据库安全(管理数据访问语句grant,revoke)

需要保护的操作有：
对数据库管理功能（创建表、更改或删除已存在的表等）的访问;
对特定数据库或表的访问;
访问的类型（只读、对特定列的访问等）；
仅通过视图或存储过程对表进行访问；
创建多层次的安全措施，从而允许多种基于登录的访问和控制；
限制管理用户账号的能力。