16、更新和删除数据
- 更新数据:UPDATE语句
–UPDATE [TABLE] SET [COL1] = [VAL1]... WHERE [COL2] = [VAL2]
– 注意不要省略WHERE子句,否则可能更新所有的值
–更新多个列时,只需要一条SET命令
– 删除某列的值时,可将其设置为NULL - 删除数据:DELETE语句
–DELETE FROM [TABLE] WHERE [COL] = [VAL]
– 注意不要省略WHERE子句,否则会删除所有行
– 定义外键时,DBMS实施引用完整性保证DELETE时数据的完整性。
– FROM关键字在某些DBMS中可选
– 注意UPDATE更新的是列,而DELETE处理的是行
– 若想删除所有行,可以使用TRUNCATE TABLE语句 - 指导规则P144
17、创建和操纵表
-
创建表:CREATE TABLE语句
–CREATE TABLE [TABLE NAME] ( [COL NAME] [DATA TYPE] NULL/NOT NULL ...);
– 需要指定表名、列名和数据类型
– 注意表名不要与现有的表重复,否则出错
– 数据类型包括CHAR(10)、DECIMAL(8,2)、VARCHAR(100)等
– 除了主键不能指定为NULL之外,其他均可;若没有指定的话,DBMS会默认其一 -
指定默认值:DEFAULT关键字
–CREATE TABLE [TABLE NAME] ( [COL NAME] [DATA TYPE] NOT NULL DEFAULT [VAL] ...);
– 默认值经常用于日期或时间戳的列,如指定系统日期作为默认日期 -
更新表:ALTER TABLE语句
– DMBS对列的增加、删除、更改、重命名有不同的规定
– 增加列:ALTER TABLE [TABLE] ADD [COL] [DATATYPE]
– 删除列:ALTER TABLE [TABLE] DROP COLUMN [COL]
– 更改表结构的过程P152 -
删除表:DROP TABLE语句
–DROP TABLE [TABLE]
-
重命名表:各DBMS差异大
18、视图
- 视图:即虚拟的表,用于查看查询得到的数据
- 视图的使用规则P157
- 视图的创建:
CREATE VIEW [VIEW]
– 视图的删除:DROP VIEW [VIEW]
;更新时应该先删除
– 可用于简化复杂的联结,便于重用SQL语句
– 可用视图重新格式化检索出的数据
– 可用于过滤数据
– 可结合计算字段使用
19、使用存储过程
- 存储过程:可以理解为为了方便后期使用所保存的SQL语句
- 优势:
– 将处理封装,简化操作
– 保证数据一致性
– 简化对变动的管理
– 以编译的形式存储,提高性能
– 可以编写功能更强的代码 - 劣势:不同DBMS间的可移植性差;受限
- 以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、管理事务处理
- 事务处理:通过确保一批SQL语句的完全执行或完全不执行,维护数据库的完整性。
– 事务:一组SQL语句
– 回退:撤销指定SQL语句的过程
– 提交:将未存储的SQL语句结果写入数据库表
– 保留点:在事务处理中设置的临时占位符,可以对它发布回退,不同于回退整个事务处理
– 事务处理管理的语句包括INSERT、UPDATE、DELETE。 - 事务处理的逻辑块:以下均以SQL Server为例
BEGIN TRANSACTION ... COMMIT TRANSACTION
- 回退语句:ROLLBACK命令
DELECT FROM [TABLE]; ROLLBACK;
- 明确的提交:COMMIT语句
– 一般SQL语句都是针对数据库表直接执行和编写,即隐式提交
– 进行明确的提交则使用COMMIT语句 - 保留点:SAVE语句 ,并取唯一的标识名字
SAVE TRANSACTION [NAME]; ... ROLLBACK TRANSACTION [NAME];
- 完整的事务处理案例:
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