SQL必知必会第五版学习笔记：第16节-第18节_创建一个名为 customerswithorders的视图,其中包括customers表中的所有列,-CSDN博客

本文链接：https://blog.csdn.net/VaIOReTto1/article/details/132412539

文章目录

第16节更新和删除数据
第17节创建和操纵表
第18节使用视图

第16节更新和删除数据

更新数据

1.UPDATE 语句由三部分组成

要更新的表；
列名和它们的新值；
确定要更新哪些行的过滤条件。

2. 下述例子中，要更新的表名为 Customers。SET 命令用来将新值赋给被更新的列。

UPDATE Customers 
SET cust_contact = 'Sam Roberts', 
    cust_email = 'sam@toyland.com' 
WHERE cust_id = 1000000006;

3. UPDATE 语句中可以使用子查询，使得能用 SELECT 语句检索出的数据更新列数据。

4. 要删除某个列的值，可设置它为 NULL

删除数据

1. 下述例子中DELETE FROM 要求指定从中删除数据的表名， WHERE 子句过滤要删除的行。

DELETE FROM Customers 
WHERE cust_id = 1000000006;

2. DELETE 不需要列名或通配符。DELETE 删除整行而不是删除列。要删除指定的列，请使用 UPDATE 语句。

3. DELETE 不删除表本身。

4. 如果想从表中删除所有行，不要使用 DELETE。可使用 TRUNCATE TABLE 语句，它完成相同的工作，而速度更快（因为不记录数据的变动）。

5. 使用外键确保引用完整性的一个好处是，DBMS 通常可以防止删除某个关系需要用到的行。

更新和删除的指导原则

1. 除非确实打算更新和删除每一行，否则绝对不要使用不带 WHERE 子句的 UPDATE 或 DELETE 语句。

2. 保证每个表都有主键，尽可能像 WHERE 子句那样使用它。

3. 在 UPDATE 或 DELETE 语句使用 WHERE 子句前，应该先用 SELECT 进行测试，保证它过滤的是正确的记录，以防编写的 WHERE 子句不正确。

4. 使用强制实施引用完整性的数据库，这样 DBMS 将不允许删除其数据与其他表相关联的行。

5. 有的 DBMS 允许数据库管理员施加约束，防止执行不带 WHERE 子句的 UPDATE 或 DELETE 语句。如果所采用的 DBMS 支持这个特性，应该使用它。

挑战题

1. 美国各州的缩写应始终用大写。编写 SQL语句来更新所有美国地址，包括供应商状态（Vendors 表中的 vend_state）和顾客状态（Customers 表中的 cust_state），使它们均为大写。

UPDATE Vendors
SET vend_state=UPPER(vend_state);

UPDATE Customers
SET cust_state=UPPER(cust_state);

2. 第 15 课的挑战题 1 要求你将自己添加到 Customers 表中。现在请删除自己。确保使用 WHERE 子句（在 DELETE 中使用它之前，先用 SELECT 对其进行测试），否则你会删除所有顾客！

DELETE FROM Customers
WHERE cust_id = 1000000010;

第17节创建和操纵表

创建表

1. 利用 CREATE TABLE 创建表，必须给出下列信息：

新表的名字，在关键字 CREATE TABLE 之后给出；
表列的名字和定义，用逗号分隔；
有的 DBMS 还要求指定表的位置。

2. 下述例子中每列的定义以列名（它在表中必须是唯一的）开始，后跟列的数据类型。

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)   , 
);

3. 每个表列要么是 NULL 列，要么是 NOT NULL 列，这种状态在创建时由表的定义规定。NULL 为默认设置，如果不指定 NOT NULL，就认为指定的是 NULL。

4. 只有不允许NULL 值的列可作为主键，允许 NULL 值的列不能作为唯一标识。

5. 。默认值在 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 
);

更新表

1. 更新表定义，可以使用 ALTER TABLE 语句。

理想情况下，不要在表中包含数据时对其进行更新。应该在表的设计过程中充分考虑未来可能的需求，避免今后对表的结构做大改动。
所有的 DBMS 都允许给现有的表增加列，不过对所增加列的数据类型有所限制。
许多 DBMS 不允许删除或更改表中的列。
多数 DBMS 允许重新命名表中的列。
许多 DBMS 限制对已经填有数据的列进行更改，对未填有数据的列几乎没有限制。

2. 使用 ALTER TABLE 更改表结构，必须给出下面的信息：

在 ALTER TABLE 之后给出要更改的表名（该表必须存在，否则将出错）；
列出要做哪些更改。

3. 第一个为增加列，第二个为删除列

ALTER TABLE Vendors 
ADD vend_phone CHAR(20);

ALTER TABLE Vendors 
DROP COLUMN vend_phone;

4. 复杂的表结构更改一般需要手动删除过程，它涉及以下步骤：

用新的列布局创建一个新表；
使用 INSERT SELECT 语句从旧表复制数据到新表。有必要的话，可以使用转换函数和计算字段；
检验包含所需数据的新表；
重命名旧表；
用旧表原来的名字重命名新表；
根据需要，重新创建触发器、存储过程、索引和外键。

删除表

1.DROP TABLE

DROP TABLE CustCopy;

挑战题

1. 在 Vendors 表中添加一个网站列（vend_web）。你需要一个足以容纳 URL 的大文本字段。

ALTER TABLE Vendors
ADD vend_web CHAR(255);

2. 使用 UPDATE 语句更新 Vendor 记录，以便加入网站（你可以编造任何地址）。

UPDATE Vendors
SET vend_web='https://www.google.com.hk'
WHERE vend_id='BRE02';

第18节使用视图

视图

1.视图：虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。

2.视图的优势：

重用 SQL 语句。
简化复杂的 SQL 操作。在编写查询后，可以方便地重用它而不必知道其基本查询细节。
使用表的一部分而不是整个表。
保护数据。可以授予用户访问表的特定部分的权限，而不是整个表的访问权限。
更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

3.视图的规则和限制：

与表一样，视图必须唯一命名。
对于可以创建的视图数目没有限制。
创建视图，必须具有足够的访问权限。这些权限通常由数据库管理人员授予。
视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造视图。所允许的嵌套层数在不同的 DBMS中有所不同。
许多 DBMS 禁止在视图查询中使用 ORDER BY 子句。
有些 DBMS 要求对返回的所有列进行命名，如果列是计算字段，则需要使用别名。
视图不能索引，也不能有关联的触发器或默认值。
有些 DBMS 把视图作为只读的查询，这表示可以从视图检索数据，但不能将数据写回底层表。
有些 DBMS 允许创建这样的视图，它不能进行导致行不再属于视图的插入或更新。

创建视图

1.CREATE VIEW

2.删除视图为 DROP VIEW viewname

3. 下述例子创建一个名为 ProductCustomers 的视图，它联结三个表，返回已订购了任意产品的所有顾客的列表。

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;

SELECT cust_name, cust_contact 
FROM ProductCustomers 
WHERE prod_id = 'RGAN01';

挑战题

1. 创建一个名为 CustomersWithOrders 的视图，其中包含 Customers 表中的所有列，但仅仅是那些已下订单的列。提示：可以在 Orders 表上使用 JOIN 来仅仅过滤所需的顾客，然后使用 SELECT 来确保拥有正确的数据。

CREATE VIEW CustomersWithOrders AS
SELECT Customers.cust_id,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email
FROM Customers
INNER JOIN Orders ON Orders.cust_id = Customers.cust_id;

2. 下面的 SQL 语句有问题吗？（尝试在不运行的情况下指出。）

CREATE VIEW OrderItemsExpanded AS 
SELECT order_num, prod_id, quantity, item_price, 
    quantity*item_price AS expanded_price 
FROM OrderItems 
ORDER BY order_num;