--16 更新和删除数据 UPDATE DELETE
--16.1 更新数据
--为了更新(修改)表中的数据,可使用UPDATE语句。可采用两种方式使用UPDATE
--更新表中的特定行
--更新表中的所有行
--不要省略WHERE子句 在使用UPDATE时一定要注意细心。因为稍不注意,就会更新表中的所有行。在使用这条语句前,请完整地阅读本节。
--UPDATE与安全 使用UPDATE语句可能需要特殊的安全权限。
--UPDATE非常容易使用,基本的UPDATE语句由3部分组成:
--要更新的表,列名和它们的新值,确定要更新哪些行的过滤条件。
--UPDATE Customers
--SET cust_email = 'kim@thetoystore.com'
--WHERE cust_id = '1000000005';
--UPDATE语句总是以要更新的表的名字开始。 SET命令用来将新值赋给被更新的列。 UPDATE语句以WHERE子句结束,它告诉DBMS更新哪一行。没有WHERE子句,将会更新Customers表中所有的行。
--更新多个列的语法稍有不同:
--UPDATE Customers
--SET cust_contact = 'Sam Roberts', cust_email = 'sam@toyland.com'
--WHERE cust_id = '1000000006';
--更新多个列时,只需要使用单个SET命令,每个“列 = 值”对之间用逗号分隔(最后一列之后不用逗号)。
--在UPDATE语句中使用子查询 UPDATE语句中可以使用子查询,使得能用SELECT语句检索出的数据更新列数据。
--FROM关键字 有的SQL实现在UPDATE语句中支持使用FROM子句,它用来自一个表的数据更新另一个表的行。
--为了删除某个列的值,可设置它为NULL(加入表定义允许NULL值)。
--UPDATE Customers
--SET cust_email = NULL
--WHERE cust_id = '1000000005';
--SELECT *
--16.2 删除数据
--FROM Customers;
--为了从一个表中删除(去掉)数据,使用DELETE语句。由两种方式使用DELETE:
--从表中删除特定的行
--从表中删除所有行
--不要省略WHERE子句 因为稍不注意,就会错误的删除表中所有行。
--DELETE与安全 使用DELETE语句可能需要特殊的安全权限。
--UPDATE非常容易使用,而DELETE更容易使用。
--SELECT *
--FROM Customers;
--DELETE FROM Customers
--WHERE cust_id = '1000000006';
--SELECT *
--FROM Customers;
--如果省略WHERE子句,它将删除表中每个客户。
--FROM关键字 最好提供这个关键字,保证SQL代码的之间的可移植性。
--DELETE不需要列名或通配符。DELETE删除整行而不是删除列。为了删除指定的列,请使用UPDATE语句。
--删除表的内容而不是表 DELETE语句从表中删除行,甚至是删除表中所有行。但是,DELETE不删除表本身。
--更快的删除 可使用TRUNCATE TABLE
--16.3 更新和删除的指导原则
--使用UPDATE和DELETE一定要注意使用WHERE,否则会更新或删除所有的行。
--使用UPDATE或DELETE时所遵循的习惯:
--除非确实打算更新或删除每一行,否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句。
--保证每个表都有主键尽可能想WHERE子句那样使用它(可以指定各主键,多个值或值的范围)。
--在对UPDATE或DELETE语句使用WHERE子句前,应该先用SELECT进行测试,保证它过滤的是正确的记录,以防编写的WHERE自己不正确。
--使用强制实施引用完整性的数据库,这样DBMS将不允许删除具有与其他表相关联的数据的行。
--有的DBMS允许数据库管理员施加约束,以防止执行不带WHERE子句的UPDATE或DELETE。如果所采用的DBMS支持这个特性,应该使用它。
--小心使用 SQL没有撤销(undo)按钮。 应谨慎的使用UPDATE和DELETE。
--16.4 小结
--WHERE子句对UPDATE和DELETE语句很重要。
--17 创建和操纵表
--17.1 创建表
--创建表的两种方式:
--多数DBMS都具有交互式创建和管理表的工具
--表也可以直接用SQL语句操纵
--为了用程序创建表,可使用SQL的CREATE TABLE语句。值得注意的是,在使用交互式工具时,实际上使用的时SQL语句。
--17.1.1 表创建基础
--为利用CREATE TABLE创建表,必须给出下列信息:
--新表的名字,在关键字CREATE TABLE之后给出;
--表列的名字和定义,用逗号分隔;
--有的DBMS还要求指定表的位置。
--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
--);
--实际的表定义(所有列)括在圆括号中之中。各列之间用逗号分隔。
--替换现有的表 在创建新表时,指定的表名必须不存在,否则将出错。如果要防止意外覆盖已有的表,SQL要求首先手工删除该表(请参阅后面的段落),然后再重建它,而不是简单地用创建表语句覆盖它。
--允许NULL值的列也允许再插入行时不给出该列的值。不允许NULL值的列不接受该列没有值的行,再插入或更新行时,该列必须有值。
--每个表列或者是NULL列,或者是NOT NULL列,这种状态再创建时由表的定义规定。
--CREATE TABLE Orders
--(
-- order_num INTEGER NOT NULL,
-- order_date DATETIME NOT NULL,
-- cust_id CHAR(10) NOT NULL
--);
--CREATE TABLE Vendors
--(
-- vend_id CHAR(10) NOT NULL,
-- vend_name CHAR(50) NOT NULL,
-- vend_address CHAR(50) ,
-- vend_city CHAR(50) ,
-- vend_state CHAR(5) ,
-- vend_zip CHAR(10) ,
-- vend_country CHAR(50)
--);
--NULL为默认设置,如果不指定NOT NULL,则认为指定的时NULL。
--主键是其值唯一标识表中每一行的列。只有不允许NULL值的列可用于主键。允许NULL值的列不能用于唯一标识。
--17.1.3 指定默认值
--SQL允许指定默认值,再插入行时如果不给出值,DBMS将自动采用默认值。默认值在CREATE TABLE语句的列定义中用关键字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
--);
--默认值经常用于日期或时间戳列。 SQL Server用户指定DEFAULT GETDATE()。 获得系统日期。
--使用DEFAULT而不是NULL值 许多数据库开发人员喜欢使用DEFAULT值而不是NULL列,特别时对用与计算或数据分组的列更是如此。
--17.2 更新表
--为了更新表,可使用ATLER TABLE语句。
--一般来说,在表中包含数据时不要对其进行更新。应该在表的设计过程中充分考虑未来的需求。
--所有DBMS都允许给现有的表增加列,不过对所增加列的数据类型(以及NULL和DEFAULT的使用)有所限制。
--许多DBMS不允许删除或更新表中的列。
--多数DBMS允许重新命名表中的列。
--许多DBMS对已经填有数据的列的更改有限制,对未填有数据的列几乎没有限制。
--为了使用ALTER TABLE更改表结构,必须给出下面的信息:
--在ALTER TABLE之后给出要更改的表名(该表必须存在,否则将出错)。
--所做更改的列表。
--给已有表增加列可能是所有DBMS都支持的唯一操作
--ALTER TABLE Vendors
--ADD vend_phone CHAR(20); --默认NULL
--SELECT * FROM Vendors;
--其他操作,如更改或删除列,增加约束或增加键,也使用类似的语法。
--ALTER TABLE Vendors DROP COLUMN vend_phone; -- ADD / (DROP COLUMN)
--小心使用ALTER TABLE,DBMS不能撤回。
--复杂的表结构更改一般需要手动删除过程:
--用新的列布局创建一个新表
--使用INSERT SELECT语句从旧表复制数据到新表。如果有必要,可使用转换函数和计算字段。
--检验包含所需数据的新表。
--重命名旧表(如果确定,可以删除它)。
--用旧表原来的名字重命名新表。
--根据需要,重新创建触发器、存储过程,索引和外键。
--17.3 删除表
--删除表(删除整个表而不是其内容)非常简单,使用DROP TABLE语句即可:
--DROP TABLE CustCopy;
--删除表没有确认,也不能撤销,执行这条语句将永久删除该表。
--使用关系规则防止意外删除 许多DBMS允许强制实施防止删除与其他表关联的表规则。
--17.4 重命名表
--SQL Server用户可使用sp_rename存储过程。
--所有重命名操作的基本语法都要求指定旧表名和新表名。
--17.5 小结
--CREATE TABLE用来创建新表,ALTER TABLE用来更改表列(或其他如约束或索引等对象),而DROP TABLE用来完整地删除一个表。
--使用视图
--18.1 视图
--视图是虚拟的表。与包含数据的表不一样,视图只包含使用时动态检索数据的查询。
--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';
--假如可以把整个查询包装成一个名为ProductsCustomers的虚拟表,则可以如下轻松地检索出相同的数据:
--SELECT cust_name, cust_contact
--FROM ProductsCustomers
--WHERE prod_id = 'RGAN01';
--这就是视图的作用。ProducstCustomers是一个视图,作为视图,它不包含任何列和数据,它包含的是一个查询(与上面用以正确联结表的相同的查询)。
--DBMS的一致支持 所有DBMS非常一致地支持视图创建语法。
--18.1.1 为什么使用视图
--视图的一些常见应用:
--重用SQL语句。
--简化复杂的SQL操作。在编写查询后,可以方便地重用它而不必知道它的基本查询细节。
--使用表的组成部分而不是整个表。
--保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。
--更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。
--在视图创建之后,可以用与表基本相同的方式利用它们。可以对视图执行SELECT操作,过滤和排序数据,将视图联结到其他视图或表,甚至能添加和更新数据(添加和更新数据存在某些限制)。
--重要的是知道视图仅仅是用来查看存储在别处的数据的一种设施。视图本身不包含数据,因此它们返回的数据是从其他表中检索出来的。在添加或更改这些表中的数据时,视图将返回改变过的数据。
--性能问题 因为视图不包含数据,所以每次使用视图时,都必须处理查询执行时所需的任一个检索。如果你用多个联结和过滤创建了复杂的视图或者嵌套了视图,可能会发现性能下降得很厉害。
--视图创建和使用的一些规则和限制:
--与表一样,视图必须唯一命名。
--对于可以创建的视图的数目没有限制。
--为了创建视图,必须具有足够的访问权限。
--视图可以嵌套。
--许多DBMS禁止在视图查询中使用ORDER BY子句。
--有的DBMS要求命名返回的所有列,如果列是计算字段,则需要使用别名。
--视图不能索引,也不能有关联的触发器或默认值。
--有些DBMS把视图作为只读的查询,这表示可以从视图检索数据,但不能将数据写回底层表。
--有的DBMS有些创建这样的视图,它不允许进行导致行不再属于视图的插入或更新。
--18.2 创建视图
--CTEATE VIEW语句来创建。与CREATE TABLE一样,CREATE VIEW只能用于创建不存在的视图。
--用DROP VIEW删除视图 其语法是DROP VIEW viewname;
--覆盖(或更新)视图 必须先DROP它,然后再重新创建它。
--18.2.1 利用视图简化复杂的联结
--视图的最常见的应用之一是隐藏复杂的SQL,这通常都会涉及联结。
--CREATE VIEW ProductsCustomers 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;
--SELECT * FROM ProductsCustomers;
--CREATE VIEW 和 SQL Server 与大多数SQL语句不一样,Microsoft SQL Server和 CREATE VIEW语句后不使用分号。
--SELECT cust_name, cust_contact
--FROM ProductsCustomers
--WHERE prod_id = 'RGAN01';
--视图极大地简化了复杂SQL语句的使用。利用视图,可一次性编写基础的SQL,然后根据需要多次使用。
--创建可重用的视图 创建不受特定数据限制的视图是一种好办法。
--18.2.2 用视图重新格式化检索出的数据
--视图的另一常见用途是重新格式化检索出的数据。
--SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')' AS vend_title
--FROM Vendors
--ORDER BY vend_name;
----假如经常需要这个格式的结果。不必在每次需要时执行联结,创建一个视图,每次需要时使用它即可。为把此语句转换为视图,可按如下进行:
--CREATE VIEW VendorLocations AS --'CREATE VIEW' 必须是查询批次中的第一个语句。
--SELECT RTRIM(vend_name) +' (' + RTRIM(vend_country) + ')' AS vend_title
--FROM Vendors;
--SELECT *
--FROM VendorLocations;
--SELECT约束全部使用 本章早些时候说过,各种DBMS中用来创建视图的语法相当一直。那么,为什么会有多种创建视图的语句版本呢?因为视图只包含一个SELECT语句,而该SELECT语句的语法必须遵循具体DBMS的所有规则和约束。
--用视图过滤不想要的数据
--视图对于应用普通的WHERE子句也很有用。
--CREATE VIEW CustomerEMailList AS
--SELECT cust_id, cust_name, cust_email
--FROM Customers
--WHERE cust_email IS NOT NULL;
--SELECT * FROM CustomerEMailList;
--WHERE子句与子句 如果从视图检索数据时使用了一条WHERE子句,则两组子句(一组在视图中,另一组是传递给视图的)将自动组合。
--18.2.4 使用视图与计算字段
--视图对于简化计算字段的使用特别有用。
--SELECT prod_id, quantity, item_price, quantity * item_price AS expanded_price
--FROM OrderItems
--WHERE order_num = 20008;
--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 = 20008;
--18.3 小结
---视图为虚拟的表。它们包含的不是数据而是根据需要检索数据的查询。视图提供了一种封装SELECT语句的层次,可用来简化数据处理以及重新格式化基础数据或保护基础数据。
--19 使用存储过程
--19.1 存储过程
--迄今为止,使用的大多数SQL语句都是针对一个或多个表的单条语句。并非所有操作都这么简单,经常会有一些复杂的操作需要多条语句才能完成。
--例如:
--为了处理订单,需要核实以保证库存中有相应的物品。
--如果库存有物品,这些物品需要预定以便不将它们再卖给别人,并且要减少物品数据以反映正确的库存量。
--库存中没有的物品需要订购;这需要与供应商进行某种交互。
--关于那些物品入库(并且可以立即发货)和那些物品退订,需要通知相应的客户。
--执行这个处理需要针对许多表的多条SQL语句。此外,需要执行的具体SQL语句及其次序也不是固定的;它们可能会(和将)根据那些物品再库存中哪些不在而变化。
--ALTER TABLE Orders ADD test INTEGER; --创建列 ADD后面不能跟COLUMN
--ALTER TABLE Orders DROP test; --'test' 不是约束。
--ALTER TABLE Orders DROP COLUMN test; --删除列
--CREATE VIEW viewname AS
--SELECT columns, ...
--FROM tables, ...
--[WHERE ...]
--[GROUP BY ...]
--[HAVING ...];