《SQL必知必会》笔记三：15-17课

三、插入、更新、删除数据与创建和操纵表

3.1 插入数据

INSERT 用来将行插入（或添加）到数据库表。插入有几种方式：

　　 插入完整的行；

　　 插入行的一部分；

　　 插入某些查询的结果。

/*列名应与VALUES中的值相对应*/
INSERT INTO Customers(cust_id, 
 cust_name, 
 cust_address, 
 cust_city, 
 cust_state, 
 cust_zip, 
 cust_country, 
 cust_contact, 
 cust_email) 
VALUES('1000000006', 
 'Toy Land', 
 '123 Any Street', 
 'New York', 
 'NY', 
 '11111', 
 'USA', 
 NULL, 
 NULL);

 

如果表的定义允许，则可以在 INSERT 操作中省略某些列。省略的列必须满足以下某个条件。

　　 该列定义为允许 NULL 值（无值或空值）。

　　 在表定义中给出默认值。这表示如果不给出值，将使用默认值。

如果表中不允许有 NULL 值或者默认值，这时却省略了表中的值，DBMS 就会产生错误消息，相应的行不能成功插入。

 

/*INSERT可以和SELECT语句一起使用。
INSERT SELECT 中 SELECT 语句可以包含 WHERE 子句，以过滤插入的数据。*/
INSERT INTO Customers(cust_id, 
 cust_contact, 
 cust_email, 
 cust_name, 
 cust_address, 
 cust_city, 
 cust_state, 
 cust_zip, 
 cust_country) 
SELECT cust_id, 
 cust_contact, 
 cust_email, 
 cust_name, 
 cust_address, 
 cust_city, 
 cust_state, 
 cust_zip, 
 cust_country 
FROM CustNew;

INSERT 通常只插入一行。要插入多行，必须执行多个 INSERT 语句。

INSERT SELECT是个例外，它可以用一条INSERT插入多行，不管SELECT语句返回多少行，都将被 INSERT 插入。

 

/*与 INSERT SELECT 将数据添加到一个已经存在的表不同，SELECT INTO将数据复制到一个新表*/
/*这条 SELECT 语句创建一个名为 CustCopy 的新表，并把 Customers 表的整个内容复制到新表中*/
SELECT * 
INTO CustCopy 
FROM Customers;

/*SELECT INTO 是试验新 SQL 语句前进行表复制的很好工具。先进行复制，可在复制的数据上测试 SQL 代码，而不会影响实际的数据。*/

 

3.2 更新数据

有两种使用 UPDATE的方式：

　　 更新表中的特定行；

　　 更新表中的所有行。

基本的 UPDATE 语句由三部分组成，分别是：

　　 要更新的表；

　　 列名和它们的新值；

　　 确定要更新哪些行的过滤条件。

/*更新单列*/
UPDATE Customers 
SET cust_email = 'kim@thetoystore.com' 
WHERE cust_id = '1000000005';

/*更新多个列*/
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';

 

3.3 删除数据

有两种使用 DELETE的方式：

　　 从表中删除特定的行；

　　 从表中删除所有行。

/*删除某行*/
DELETE FROM Customers 
WHERE cust_id = '1000000006';

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

DELETE 语句从表中删除行，甚至是删除表中所有行。但是，DELETE不删除表本身。

 

下面是许多 SQL 程序员使用 UPDATE 或 DELETE 时所遵循的重要原则。

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

　　 保证每个表都有主键，尽可能像 WHERE 子句那样使用它（可以指定各主键、多个值或值的范围）。

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

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

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

 

3.4 创建表

一般有两种创建表的方法：

　　 多数 DBMS 都具有交互式创建和管理数据库表的工具；

　　 表也可以直接用 SQL 语句操纵。

 

利用 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 
);
/*在创建新的表时，指定的表名必须不存在，否则会出错。防止意外覆盖已有的表，SQL 要求首先手工删除该表（请参阅后面的内容），然后再重建它，而不是简单地用创建表语句覆盖它。*/

/*给定默认值*/
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 
);

 

3.5 更新表

以下是使用 ALTER TABLE 时需要考虑的事情。

　　 理想情况下，不要在表中包含数据时对其进行更新。应该在表的设计过程中充分考虑未来可能的需求，避免今后对表的结构做大改动。

　　 所有的 DBMS 都允许给现有的表增加列，不过对所增加列的数据类型（以及 NULL 和 DEFAULT 的使用）有所限制。

　　 许多 DBMS 不允许删除或更改表中的列。

　　 多数 DBMS 允许重新命名表中的列。

　　 许多 DBMS 限制对已经填有数据的列进行更改，对未填有数据的列几乎没有限制。

 

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

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

　　 列出要做哪些更改。

/*增加列*/
ALTER TABLE Vendors 
ADD vend_phone CHAR(20);

 

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

　　(1) 用新的列布局创建一个新表；

　　(2) 使用 INSERT SELECT 语句从旧表复制数据到新表。有必要的话，可以使用转换函数和计算字段；

　　(3) 检验包含所需数据的新表；

　　(4) 重命名旧表（如果确定，可以删除它）；

　　(5) 用旧表原来的名字重命名新表；

　　(6) 根据需要，重新创建触发器、存储过程、索引和外键。

 

3.6 删除表

DROP TABLE CustCopy;