《SQL必知必会》第16课到第20课笔记

16、更新和删除数据

1. 更新数据：UPDATE语句
   – UPDATE [TABLE] SET [COL1] = [VAL1]... WHERE [COL2] = [VAL2]
   – 注意不要省略WHERE子句，否则可能更新所有的值
   –更新多个列时，只需要一条SET命令
   – 删除某列的值时，可将其设置为NULL
2. 删除数据：DELETE语句
   – DELETE FROM [TABLE] WHERE [COL] = [VAL]
   – 注意不要省略WHERE子句，否则会删除所有行
   – 定义外键时，DBMS实施引用完整性保证DELETE时数据的完整性。
   – FROM关键字在某些DBMS中可选
   – 注意UPDATE更新的是列，而DELETE处理的是行
   – 若想删除所有行，可以使用TRUNCATE TABLE语句
3. 指导规则P144

17、创建和操纵表

1. 创建表：CREATE TABLE语句
   – CREATE TABLE [TABLE NAME] ( [COL NAME] [DATA TYPE] NULL/NOT NULL ...);
   – 需要指定表名、列名和数据类型
   – 注意表名不要与现有的表重复，否则出错
   – 数据类型包括CHAR(10)、DECIMAL(8,2)、VARCHAR(100)等
   – 除了主键不能指定为NULL之外，其他均可；若没有指定的话，DBMS会默认其一

2. 指定默认值：DEFAULT关键字
   – CREATE TABLE [TABLE NAME] ( [COL NAME] [DATA TYPE] NOT NULL DEFAULT [VAL] ...);
   – 默认值经常用于日期或时间戳的列，如指定系统日期作为默认日期

3. 更新表：ALTER TABLE语句
   – DMBS对列的增加、删除、更改、重命名有不同的规定
   – 增加列：ALTER TABLE [TABLE] ADD [COL] [DATATYPE]
   – 删除列：ALTER TABLE [TABLE] DROP COLUMN [COL]
   – 更改表结构的过程P152

4. 删除表：DROP TABLE语句
   – DROP TABLE [TABLE]

5. 重命名表：各DBMS差异大

18、视图

1. 视图：即虚拟的表，用于查看查询得到的数据
2. 视图的使用规则P157
3. 视图的创建：CREATE VIEW [VIEW]
   – 视图的删除：DROP VIEW [VIEW]；更新时应该先删除
   – 可用于简化复杂的联结，便于重用SQL语句
   – 可用视图重新格式化检索出的数据
   – 可用于过滤数据
   – 可结合计算字段使用

19、使用存储过程

1. 存储过程：可以理解为为了方便后期使用所保存的SQL语句
2. 优势：
   – 将处理封装，简化操作
   – 保证数据一致性
   – 简化对变动的管理
   – 以编译的形式存储，提高性能
   – 可以编写功能更强的代码
3. 劣势：不同DBMS间的可移植性差；受限
4. 以SQL Server为例：
   – 局部变量以@字符开头；全局变量以@@字符开头
   – 标识字段（自动增量的列）；自动编号与序列

---

-- 例1：创建无参数的存储过程

CREATE PROCEDURE MailingListCount
AS
DECLARE @cnt INTEGER
SELECT @cnt = COUNT(*)
WHERE NOT cust_email IS NULL;
RETURN @cnt;

-- 例2：创建含参数的存储过程

CREATE PROCEDURE NewOrder @cust_id CHAR(10)
AS 
DECLARE @order_num INTEGER
SELECT @order_num = MAX(order_num)
FROM Orders
SELECT @order_num = @order_num+1
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(@order_num, GETDATE(), @cust_id) --当前系统日期
RETURN @order_num;

-- 例3：例2的另一写法

CREATE PROCEDURE NewOrder @cust_id CHAR(10)
AS 
INSERT INTO Orders(cust_id)
VALUES(@cust_id) --系统自动生成订单号；默认系统当前日期
SELECT @order_num = @@IDENTITY;  --获得订单号

20、管理事务处理

1. 事务处理：通过确保一批SQL语句的完全执行或完全不执行，维护数据库的完整性。
   – 事务：一组SQL语句
   – 回退：撤销指定SQL语句的过程
   – 提交：将未存储的SQL语句结果写入数据库表
   – 保留点：在事务处理中设置的临时占位符，可以对它发布回退，不同于回退整个事务处理
   – 事务处理管理的语句包括INSERT、UPDATE、DELETE。
2. 事务处理的逻辑块：以下均以SQL Server为例
   BEGIN TRANSACTION ... COMMIT TRANSACTION
3. 回退语句：ROLLBACK命令
   DELECT FROM [TABLE]; ROLLBACK;
4. 明确的提交：COMMIT语句
   – 一般SQL语句都是针对数据库表直接执行和编写，即隐式提交
   – 进行明确的提交则使用COMMIT语句
5. 保留点：SAVE语句 ，并取唯一的标识名字
   SAVE TRANSACTION [NAME]; ... ROLLBACK TRANSACTION [NAME];
6. 完整的事务处理案例：

BEGIN TRANSACTION
INSERT INTO Customers(id, name)
VALUES('001','Tina');
SAVE TRANSACTION StartOrder;
INSERT INTO Orders(oid, odate, id)
VALUES(1001,'2021/4/1','001');
IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;
INSERT INTO OrderItems(oid, item, pid, qty, price)
VALUES(1001,1,'BR01',100, 5.49);
IF @@ERROR <> ROLLBACK TRANSACTION StartOrder;
INSERT INTO OrderItems(oid, item, pid, qty, price)
VALUES(1001,2,'BR03',100, 6.49);
IF @@ERROR <> ROLLBACK TRANSACTION StartOrder;
COMMIT TRANSACTION