最近有些懈怠,得改。
第16章 更新和删除数据
16.1更新数据
UPDATE的使用方式:
- 更新表中的特定行
- 更新表中的所有行
基本UPDATE语句由三部分组成,分别是: - 要更新的表
- 列名和它们的新值
- 确定要更新哪些行的过滤条件
e.g:
UPDATE Customers
SET cust_email = 'kim@thetoystore.com'
WHERE cust_id = '1000000005';
UPDATE语句总是以要更新的表名开始。SET命令用来将新值赋给被更新的列,UPDATE语句以WHERE子句结束,它告诉DBMS更新哪一行。没有WHERE将会更新所有行,这不是我们所希望的。
更新多个列:
UPDATE Customers
SET cust_contact = 'Sam Roberts',
cust_email = 'sam@toyland.com'
WHERE cust_id = '1000000006';
要删除某个列的值,可设置它为NULL,如下进行:
UPDATE Customers
SET cust_email = NULL
WHERE cust_id = '1000000005';
16.2删除数据
从一个表中删除数据,使用DELETE语句。有两种删除的方式:
- 从表中删除特定的行
- 从表中删除所有行
下面语句从Customers表删除一行:
DELETE FROM Customers
WHERE cust_id = '100000006';
- DELETE删除整行而不是删除列
- 删除指定的列,可使用UPDATE语句
- DELETE不删除表本身
- 删除所有行,可使用TRUNCATE TABLE语句,速度更快(因为不记录数据的变动)
16.3更新和删除的指导原则
更新和删除的指导原则
- 除非确实打算更新和删除每一行,否则绝对要带有WHERE子句
- 保证每个表都有主键
- 在对UPDATE或DELETE语句使用WHERE子句前,应该先用SELECT进行测试,保证它过滤的是正确的记录
- 使用强制实施引用完整性的数据库,这样DBMS将不允许删除具有与其他表相关联的数据的行
第17章 创建和操作表
17.1 创建表
一般有两种创建表的方法:
- 多数具有交互式地创建和管理数据库表的工具;
- 也可以直接用SQL语句操纵
17.1.1表创建基础
利用CREATE TABLE创建表,必须给出下列信息
- 新表的名字,在关键字CREATE TABLE后给出;
- 表列的名字和定义,用逗号分隔;
- 有的DBMS还要求指定表的位置
下例是本书创建Products表的例子
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
);
17.1.2使用NULL值
每个表列要么是NULL列,要么是NOT NULL列,这种状态在创建时由表的定义规定。
CREATE TABLE Orders
(
order_num INTEGER NOT NULL ,
order_date DATETIME NOT NULL ,
cust_id CHAR(10) NOT NULL
);
这里每一列的定义都有NOT NULL。这就会组织没有插入值的列。如果插入没有值的列,将返回错误,且插入失败。
17.1.3指定默认值
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
);
17.2更新表
ALTER TABLE用来更改表结构,必须给出下面的信息:
- 在ALTER TABLE之后给出要更改的表名(该表必须存在,否则出错);
- 列出要做哪些更改
例如:
ALTER TABLE Vendors
ADD vend_phone CHAR(20);
这条语句给Vendors增加一个名为vend_phone的列,其数据类型是CHAR。
更改或者删除列、增加约束或增加键,这些操作也使用类似的语法:
ALTER TABLE Vendors
DROP COLUMN vend_phone
复杂的表结构更改一般需要手动删除过程,它涉及以下步骤:
- 用新的列布局创建一个新表
- 使用INSERT SELECT语句从旧表复制数据到新表。有必要的话可以使用转换函数和计算字段;
- 检验包含所需数据的新表
- 重命名旧表
- 用旧表原来的名字来命名新表
- 根据需要,重新创建触发器、存储过程、索引和外键
17.3删除表
DROPTABLE CustCopy;
17.4重命名表
- DB2、MySQL、Oracle和PostgreSQL可使用RENAME语句
- SQL Server和Sybase可使用sp_rename
18章 使用视图
18.1视图
视图是虚拟的表。与包含数据的表不一样,视图只包含使用时动态检索数据的查询。
如果我们需要查询订购了某种产品的顾客。我们可以这么做
SELECT cust_num,cust_contact
FROM Customers,Orders,OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num
AND prod_id = 'RGAN01'
任何需要使用这个数据的人是需要理解相关表的结构,知道如何创建查询和对表进行联结。检索其他产品或者多个产品的数据必须要修改最后的WHERE子句。
现在,加入可以把整个查询包装成一个名为ProductCustomers的虚拟表,则可以如下轻松地检索出相同的数据:
SELECT cust_name, cust_contact
FROM ProductCustomers
WHERE prod_id = 'RGAN01';
这就是视图的作用。ProductCustomers是一个视图,它不包含任何列或数据,包含的是一个查询。
18.1.1为什么使用视图
- 重用SQL语句
- 简化复杂的SQL操作
- 使用表的组成部分而不是整个表
- 保护数据,可以给用户授予表的特定部分的访问权限,而不是整个表的访问权限
- 更改数据格式和表示,视图可返回与底层表的表示和格式不同的数据
18.1.2视图的规则和限制
一些常见的规则和限制
- 视图必须唯一命名
- 对于可以创建的视图数目没有限制
- 为了创建视图,必须具有足够的访问权限
- 视图可以嵌套,即:可以利用从其他视图中检索数据的查询来构造一个视图
- 许多DBMS禁止在视图查询中使用ORDER BY子句
- 有的DBMS要求命名返回的所有列,如果列是计算字段,则需要使用别名
- 视图不能索引,也不能有关联的触发器或默认值
- 有的DBMS把视图作为只读的查询
- 有的DBMS允许创建这样的视图:它不允许进行导致行不再术语视图的插入或更新
18.2创建视图
视图用CREATE VIEW来创建,与CREATE TABLE一样,只能创建不存在的视图。
删除视图 DROP VIEW viewname。
18.2.1利用视图简化复杂的联结
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;
这条语句创建了一个名为ProductCustomers的视图,它联结了三个表,返回已订购了任意产品的所有顾客的列表。如果执行SELECT * FROM ProductCustomers,将列出订购了任意商品的顾客。
检索订购了产品RGAN01的顾客,可如下进行:
SELECT cust_name,cust_contact
FROM ProductCustomers
WHERE prod_id = 'RGAN01'
18.2.2用视图重新格式化检索出的数据
SELECT RTRIM(vend_name)||'('||
RTRIM(vend_country)||')'
AS vend_title
FROm Vendors
ORDER BY vend_name;
现在,假设经常需要这个格式的结果。我们不必每次需要时执行这种拼接,而是创建一个视图,使用它时即可。
CREATE VIEW VendorLocation AS
SELECT RTRIM(vend_name)||'('||
RTRIM(vend_country)||')'
AS vend_title
FROm Vendors
要检索数据,创建所有邮件的标签,可如下进行:
SELECT *
FROM VendorLocations;
18.2.3用视图过滤不想要的数据
可以定义CustomerEMailList视图,过滤没有电子邮件地址的顾客。为此可以使用下面语句
CREATE VIEW CustomerEMailList AS
SELECT cust_id,cust_name,cust_email
FROM Customers
WHERE cust_email IS NOT NULL;
18.2.4使用视图与计算字段
检索某个订单中的物品,并计算每种物品的总价格:
SELECT prod_id,
quantity,
item_price,
quantity*item_price AS expanded_price
FROM OrderItems
WHERE order_num = 20008;
要将其转换为一个视图,可以如下进行
CREATE VIEW OrderItemExpanded_price
SELECT order_num,
prod_id,
quantity,
item_price,
quantity*item_price AS expanded_price
FROM OrderItems
利用视图来检索订单20008的详细内容就可以如下进行:
SELECT *
FROM OrderItemExpanded
WHERE order_num=20008;
可以看到,视图非常容易创建,而且很好使用。