《SQL必知必会·第4版》阅读笔记

###检索数据###

##检索单个列
SELECT XX
FROM XXX；

##检索多个列
SELECT XX1,XX2,XX3（没有逗号）
FROM XXX;

##检索所有列（未知列可以被显示）
SELECT *（通配符）
FROM XXX；

##检索不同的值
SELECT DISTINCT XX
FROM XXX;

##限制结果
#SQL Sever和Access中
SELECT TOP 5 XX
FROM XXX;
#DB2中
SELECT XX
FROM XXX
FETCH FIRST 5 ROWS ONLY;
#Oracle中
SELECT XX
FROM XXX
WHERE ROWNUM <=5;
#MySQL、MariaDB、PostgreSQL或者SQLite中
SELECT XX
FROM XXX
LIMIT 5；#得到前面5行的数据
SELECT XX
FROM XXX
LIMIT 5 OFFSET 5; #得到从第5行起的5行数据
SELECT XX
FROM XXX
LIMIT 1 OFFSET 1；#检索第2行数据，注意第一个被检索的行是第0行

##注释表示方法
①行内注释：
–XXXX或#XXXX
②多行注释（常用于代码加注释）：
/XXXX
XXXX/

###排序检索数据

##排序数据（默认升序#ASC#排序）
SELECT XX1
FROM XXX
ORDER BY XX1;（保证这个是最后的子句）
#按多个列排序
SELECT XX1,XX2,XX3
FROM XXX
ORDER BY XX2,XX3;
#按列位置排序
SELECT XX1,XX2,XX3
FROM XXX
ORDER BY 2,3; #列名和位置可以混合使用
#指定排序方向
SELECT XX1,XX2,XX3
FROM XXX
ORDER BY XX2 DESC, XXX3;（设定某列降序排序，未设定的依旧升序排序）

##过滤数据
#WHERE子句
SELECT XX1,XX2
FROM XXX
WHERE XXX2 = 3.49；（操作符更换即可变成别的情况）
#同时使用ORDER BY与WHERE的时候，必须让ORDER BY位于WHERE之后
#操作符：
= 等于
<>或!= 不等于
< 小于

大于
!> 不大于 即 <= 小于等于
!< 不小于 即 >= 大于等于
BETWEEN 在指定的两个值之间
IS NULL 为NULL值
#不匹配检查（<>或!=）
SELECT XX1,XX2
FROM XXX
WHERE XXX1 <> ‘XX’；#字符串用单引号限定
或
SELECT XX1,XX2
FROM XXX
WHERE XXX1 != ‘XX’； #同上
#范围值检查（BETWEEN ）
SELECT XX1,XX2
FROM XXX
WHERE XX2 BETWEEN 5 AND 10;
#空值检查
SELECT XX1
FROM XXX
WHERE XX2 IS NULL;（空字段，不是为0）

##高级数据过滤
#组合WHERE子句
#AND操作符
SELECT prod_id,prod_price,prod_name
FROM Products
WHERE vend_id = ‘DLL01’ AND pord_price <= 4;
#OR操作符
SELECT prod_price,prod_name
FROM Products
WHERE vend_id = ‘DLL01’ OR vend_id =‘BRS01’;
#求值顺序（圆括号>AND>OR）
SELECT prod_price,prod_name
FROM Products
WHERE (vend_id = ‘DLL01’ OR vend_id =‘BRS01’)
AND prod_privce >=10;
#IN操作符（功能同OR，可以包含其他SELECT语句）
SELECT prod_name,prod_price
FROM Products
WHERE vend_id IN (‘DLL01’,'BRS01)
ORDER BY prod_name;
同
SELECT prod_price,prod_name
FROM Products
WHERE vend_id = ‘DLL01’ OR vend_id =‘BRS01’
ORDER BY prod_name;
#NOT操作符
SELECT prod_name
FROM Products
WHERE NOT vend_id = ‘DLL01’
ORDER BY prod_name;
同
SELECT prod_name
FROM Products
WHERE vend_id <> ‘DLL01’
ORDER BY prod_name;

##用通配符进行过滤
#LIKE操作符
#%（Microsoft Access中，需要使用*而不是%）（单个多个字符都可以匹配）#不会匹配NULL
①单个%
SELECT prod_id,prod_name
FROM Products
WHERE prod_name LIKE ‘Fish%’；#启用了搜索模式‘Fish%’#找出所有以词Fish起头的作品，区分大小写
②首尾使用%
SELECT prod_id,prod_name
FROM Products
WHERE prod_name LIKE ‘%bean bag%’; #启用了搜索模式’%bean bag%’
③中间使用%
SELECT prod_name
FROM Products
WHERE prod_name LIKE ‘F%y’; #启用了搜索模式F开头y结尾的prod_name
#（只匹配单个字符）（DB2不支持）（Microsoft Access中，需要使用?而不是）
SELECT prod_id,prod_name
FROM Products
WHERE prod_name LIKE ‘__ inch teddy bear’; #12、18可以显示，但是8不行
SELECT prod_id,prod_name
FROM Products
WHERE prod_name LIKE ‘% inch teddy bear’; #两位数之内都可以显示
#[]（只匹配单个字符）（只有Microsoft Access和SQL Sever支持）
SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE ‘[JM]%’
ORDER BY cust_contact; #找出所有名字以J或M开头的联系人
SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE ‘[^JM]%’
ORDER BY cust_contact; #^（脱字号）Microsoft Access中，需要使用!而不是^）找出所有名字以除J或M开头的联系人
SELECT cust_contact
FROM Customers
WHERE NOT cust_contact LIKE ‘[JM]%’
ORDER BY cust_contact; #意义同上
#注意：通配符处理时间长
#技巧：①不要过度使用通配符，能用操作符代替的尽量用操作符代替；②必须使用通配符时，尽量不要用在搜索模式的开始处；③仔细注意通配符的位置。

###创建计算字段
##拼接字段
①SQL Sever和Access使用+：
SELECT vend_name + ‘(’ + vend_country + ‘)’
FROM Vendors
ORDER BY vend_name;
②DB2、Oracle、PostgreSQL、SQLite和Open Office Base使用||：
SELECT vend_name || ‘(’ || vend_country || ‘)’
FROM Vendors
ORDER BY vend_name;
③MySQL或MariaDB使用,
SELECT vend_name , ‘(’ , vend_country , ‘)’
FROM Vendors
ORDER BY vend_name;
#RIRIM()去掉字符串右边的空格
LTRIM() 去掉字符串左边的空格
TRIM()去掉字符串左右两边的空格
SELECT RTRIM(vend_name) + ‘(’ + vend_country + ‘)’
FROM Vendors
ORDER BY vend_name;
或
SELECT RTRIM(vend_name) || ‘(’ || vend_country || ‘)’
FROM Vendors
ORDER BY vend_name;
#使用别名，用AS关键字赋予
SELECT RTRIM(vend_name) + ‘(’ + RTRIM(vend_country) + ‘)’
AS vend_title
FROM Vendors
ORDER BY vend_name;
或
SELECT RTRIM(vend_name) || ‘(’ || RTRIM(vend_country) || ‘)’
AS vend_title
FROM Vendors
ORDER BY vend_name;
或
SELECT RTRIM(vend_name) , ‘(’ , RTRIM(vend_country) , ‘)’
AS vend_title
FROM Vendors
ORDER BY vend_name;

##执行算术计算±/
SELECT prod_id,
quantity,
item_price,
quantityitem_price AS expanded_price
FROM OrderItems
WHERE order_num = 20008;

###使用函数处理数据

##函数介绍：SQL函数不是可移植的，注意做好代码注释
①提取字符串的组成部分
Access使用MID()
DB2、Oracle、PostgreSQL和SQLite使用SUBSTR()
MySQL和SQL Sever使用SUBSTRING()
②数据类型转换
Access和Oracle使用多个函数，每种类型的转换有一个函数；
DB2和PostgreSQL使用CAST()；
MariaDB、MySQL和SQL Sever使用CVONVERT()
③取当前日期
Access使用NOW()
DB2和PostgreSQL使用CURRENT_DATE
MariaDB、MySQL使用CURDATE()
Oracle使用SYSDATE
SQL Sever使用GETDATE()
SQLite使用DATE()

##大多数SQL实现支持以下类型的函数
①用于处理文本字符串（如删除或填充值，转换值为大写或小写）的文本函数
②用于在数值数据上进行算术操作（如返回绝对值，进行代数运算）的数值函数
③用于处理日期和时间值并从这些值中提取特定成分（如返回两个日期之差，检查日期有效性）的日期和时间函数
④返回DBMS正使用的特殊信息（如返回用户登录信息）的系统函数
#常用的文本处理函数
LEFT()（或使用子字符串函数）#返回字符串左边的字符
RIGHT()（或使用子字符串函数）#返回字符串右边的字符
UPPER() （Access使用UCASE()）#将字符串转换为大写
LENGTH()（也是用DATALENGTH()或LEN()）#返回字符串的长度
LOWER()（Access使用LCASE()）#将字符串转换为小写
LTRIM() #去掉字符串左边的空格
RTRIM() #去掉字符串右边的空格
SELECT vend_name, UPPER(vend_name) AS vend_name_upcase
FROM Vendors
ORDER BY vend_name;
SOUNDEX() #返回字符串的SOUNDEX值
注解：SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。SOUNDEX考虑了类似的发音字符和音节，使得能对字符串进行发音比较而不是字母比较。虽然SOUNDEX不是SQL概念，但多数DBMS都提供对SOUNDEX的支持（Microsoft Access和PostgreSQL、多数SQLite实现不支持）。
SELECT cust_name, cust_contact
FROM Customers
WHERE cust_contact = ‘Michael Green’; #如果数据错误则不能显示
SELECT cust_name, cust_contact
FROM Customers
WHERE SOUNDEX(cust_contact) =SOUNDEX( ‘Michael Green’); #匹配所有发音类似于Michael Green的联系名
#日期和时间处理函数（不同的不一样，可移植性差）
#数值处理函数（最一致、最统一的函数），常用：
ABS() #返回一个数的绝对值
COS() #返回一个角度的余弦
EXP() #返回一个数的指数值
PI() #返回圆周率
SIN() #返回一个角度的正弦
SQRT() #返回一个数的平方根
TAN() #返回一个角度的正切

###汇总数据

##SQL聚集函数
#AVG()函数——平均值（只能用于单个列，忽略列值为NULL的行）
SELECT AVG(prod_price) AS avg_price
FROM Products; #求prod_price列平均值
SELECT AVG(prod_price) AS avg_price
FROM Products
WHERE vend_id = ‘DLL01’; #特定条件下，求prod_price列平均值
#COUNT()函数——计数
SELECT COUNT() AS num_cust
FROM Customers; #对所有行（）计数，不忽略值为空的行
SELECT COUNT(cust_email) AS num_cust
FROM Customers; #对特定行计数，忽略值为空的行
#MAX()函数——最大值
SELECT MAX(prod_price) AS max_price
FROM Products;
注解：用于文本数据的时，MAX()返回该列排序后的最后一行；忽略列值为NULL的行。
#MIN()函数——最小值
SELECT MIN(prod_price) AS max_price
FROM Products;
注解：用于文本数据的时，MAX()返回该列排序后的最前面的行；忽略列值为NULL的行。
#SUM()函数——求和（总计）
SELECT SUM(quantity) AS items_ordered
FROM OrderItems
WHERE order_num = 20005; #返回订单中所有物品数量之和，WHERE子句保证只统计某个物品订单中的物品
注解：在多个列上进行计算；忽略列值为NULL的行。
##聚集不同值（Microsoft Access在聚集函数中不支持DISTINCT）
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM Products
WHERE vend_id = ‘DLL01’;
注解：如果指定列名，DISTINCT只能用于COUNT()。DISTINCT不能用于COUNT(*)。即DISTINCT必须使用列名，不能用于计算或表达式。

##组合聚集函数
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;

###分组数据

##创建分组
SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vemd_id;
#GROUP BY 出现在WHERE子句之后，ORDER BY子句之前。

##过滤分组
#WHEREzz在数据分组前进行过滤，HAVING在数据分组后进行过滤
SELECT cust_id, COUNT() AS orders
FROM orders
GROUNP BY cust_id
HAVING COUNT() >=2; #过滤COUNT() >=2（两个以上订单）的那些分组
SELECT vent_id, COUNT() AS num_prods
FROM Products
WHERE prod_price >=4
GROUNP BY vend_id
HAVING COUNT() >=2; #WHERE子句先过滤所有prod_price 至少为4 的行，HAVING子句过滤计数为2或2以上的分组。
SELECT order_num, COUONT() AS items
FROM OrderItems
GROUP BY order_num
HAVING COUNT(*) >=3
ORDER BY items, order_num;#分组域排序组合

###使用子查询

##利用子查询进行过滤（作为子查询的SELECT 语句只能查询单个列）
SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num
FROM OrderItems
WHERE prod_id = ‘RGAN01’); #包含子查询的SELECT 语句难以阅读和调试，把子查询分解为多行并进行适当的缩进，能极大地简化子查询的使用（颜色编码）

##作为计算字段使用子查询
SELECT cust_name,
cust_state,
(SELECT COUNT(*)
FROM Orders
WHERE Orders.cust_id = Customers.cust_id) AS orders
FROM Customers
ORDER BY cust_name; #用一个句点分隔表名和列名，在有可能混淆列名时必须使用这种语法（完全限定列名）

###联结表

##创建联结
SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;
#由没有联结条件的表关系返回的结果为笛卡儿积，即叉联结(cross join)，所有联结都必须有WHERE 子句
SELECT vend_name, prod_name, prod_price
FROM Vendors INNER JOIN Products
ON Vendors.vend_id = Products.vend_id; #两个表之间的关系是以INNER JOIN 指定的部分FROM 子句
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’;
注解：Oracle 中没有AS，其中应该Customers C。

##使用不同类型的联结
#自联结
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 LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id; #从FROM 子句左边的表
中选择所有行
SELECT Customers.cust_id, Orders.order_num
FROM Customers RIGHT OUTER JOIN Orders
ON Orders.cust_id = Customers.cust_id; #从FROM 子句右边的表
中选择所有行
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 LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;

###组合查询

##创建组合查询
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;
#UNION从查询结果集中自动去除了重复的行
#只能使用一条ORDER BY 子句，且必须位于最后一条SELECT 语句之后
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN (‘IL’,‘IN’,‘MI’)
UNION ALL
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = ‘Fun4All’; #用UNION ALL返回所有的匹配行，不取消重复的行

###插入数据

##数据插入
#插入完整的行
INSERT INTO Customers
VALUES(‘1000000006’,
‘Toy Land’,
‘123 Any Street’,
‘New York’,
‘NY’,
‘11111’,
‘USA’,
NULL,
NULL); #不安全
INSERT INTO Customers(cust_id,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country,
cust_contact,
cust_email)
VALUES(‘1000000006’,
‘Toy Land’,
‘123 Any Street’,
‘New York’,
‘NY’,
‘11111’,
‘USA’,
NULL,
NULL); #更安全，但是繁琐
#插入部分行
INSERT INTO Customers(cust_id,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
VALUES(‘1000000006’,
‘Toy Land’,
‘123 Any Street’,
‘New York’,
‘NY’,
‘11111’,
‘USA’); #省略列（该列定义为允许NULL 值，在表定义中给出默认值）
#插入检索出的数据
INSERT INTO Customers(cust_id,
cust_contact,
cust_email,
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 CustCopy
FROM Customers;
CREATE TABLE CustCopy AS
SELECT * FROM Customers; #MariaDB、MySQL、Oracle、ostgreSQL 和SQLite 中

###更新和删除数据

##更新数据
UPDATE Customers
SET cust_email = ‘kim@thetoystore.com’
WHERE cust_id = ‘1000000005’; #单个列
UPDATE Customers
SET cust_contact = ‘Sam Roberts’,
cust_email = ‘sam@toyland.com’
WHERE cust_id = ‘1000000006’; #多个列
UPDATE Customers
SET cust_email = NULL
WHERE cust_id = ‘1000000005’; #删除列

##删除数据（DELETE 不需要列名或通配符）
DELETE FROM Customers
WHERE cust_id = ‘1000000006’; #删除一行

###创建和操纵表
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值就是没有值或缺值
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
); #指定默认值
#获得系统日期
Access：NOW()
DB2：CURRENT_DATE
MySQL：CURRENT_DATE()
Oracle：SYSDATE
PostgreSQL：CURRENT_DATE
SQL Server：GETDATE()
SQLite：date(‘now’)

##更新表
ALTER TABLE Vendors
ADD vend_phone CHAR(20); #增加列
ALTER TABLE Vendors
DROP COLUMN vend_phone; #删除列

##删除表
DROP TABLE CustCopy;

##重命名表

###使用视图

##创建视图（CREATE VIEW XXX）
#利用视图简化复杂的联结
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;

##删除视图（DROP VIEW XXX）

###使用存储过程

##创建存储过程
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; #调用Oracle
DECLARE @ReturnValue INT
EXECUTE @ReturnValue=MailingListCount;
SELECT @ReturnValue; #调用SQL Server

###管理事务处理

##控制事务处理
BEGIN TRANSACTION
…
COMMIT TRANSACTION #SQL Server中
START TRANSACTION
… #MariaDB和MySQL中
SET TRANSACTION
… #Oracle中
BEGIN
… #PostgreSQL中
#ROLLBACK命令（回退/撤销）（整个）
DELETE FROM Orders;
ROLLBACK;
#COMMIT命令（提交）（整个）
BEGIN TRANSACTION
DELETE OrderItems WHERE order_num = 12345
DELETE Orders WHERE order_num = 12345
COMMIT TRANSACTION #SQL Server
SET TRANSACTION
DELETE OrderItems WHERE order_num = 12345;
DELETE Orders WHERE order_num = 12345; #Oracle中
#保留点（回退部分）（占位符）
SAVEPOINT delete1; #MariaDB、MySQL和Oracle中
SAVE TRANSACTION delete1; #SQL Server中
ROLLBACK TRANSACTION delete1; #SQL Server
完整的SQL Server例子：
输入▼
BEGIN TRANSACTION
INSERT INTO Customers(cust_id, cust_name)
VALUES(‘1000000010’, ‘Toys Emporium’);
SAVE TRANSACTION StartOrder;
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20100,‘2001/12/1’,‘1000000010’);
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
#注解：保留点越多越好。

###使用游标

##创建游标
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 #打开游标
DECLARE TYPE CustCursor IS REF CURSOR
RETURN Customers%ROWTYPE;
DECLARE CustRecord Customers%ROWTYPE
BEGIN
OPEN CustCursor;
LOOP
FETCH CustCursor INTO CustRecord;
EXIT WHEN CustCursor%NOTFOUND;
…
END LOOP;
CLOSE CustCursor;
END; #Oracle语法
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 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 #Microsoft SQL Server语法
#关闭游标
CLOSE CustCursor #DB2、Oracle和PostgreSQL的语法
CLOSE CustCursor
DEALLOCATE CURSOR CustCursor #Microsoft SQL Server的版本

###高级SQL特性

##约束
#主键
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
); #使得vend_id列为主键
ALTER TABLE Vendors
ADD CONSTRAINT PRIMARY KEY (vend_id); #使得相同的列为主键
#外键（有助防止意外删除）
CREATE TABLE Orders
(
order_num INTEGER NOT NULL PRIMARY KEY,
order_date DATETIME NOT NULL,
cust_id CHAR(10) NOT NULL REFERENCES Customers(cust_id)
); #定义外键
ALTER TABLE Orders
ADD CONSTRAINT
FOREIGN KEY (cust_id) REFERENCES Customers (cust_id) #ALTER TABLE语句中
#检查约束
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),
item_price MONEY NOT NULL
); #保证所有物品的数量大于0
ADD CONSTRAINT CHECK (gender LIKE ‘[MF]’) #检查名为gender的列只包含M或F

##索引（效率随表数据的增加或改变而变化）
CREATE INDEX prod_name_ind
ON PRODUCTS (prod_name);

##触发器（比约束要慢，因此尽量使用约束）
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; #SQL Server版本
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; #Oracle和PostgreSQL的版本

##数据库安全