《SQL必知必会》笔记8-建表create、插入insert、更

2017-07-23 本文已影响0人 hufengreborn

1 创建和操纵表（CREATE）

1.1 创建表CREATE

利用CREATE TABLE创建表，需给出以下信息：

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

创建产品Products表：

CREATE TABLE Products
(
 prod_id      char(10)       NOT NULL ,
 vend_id      char(10)       NOT NULL ,
 prod_name    char(255)      NOT NULL ,
 prod_price   decimal(8,2)   NOT NULL ,
 prod_desc    text           NULL 
);

添加默认值的时候，使用关键字DEFAULT。

创建订单明细OrderItems表：

CREATE TABLE OrderItems
(
 order_num    int             NOT NULL ,
 order_item   int             NOT NULL ,
 prod_id      char(10)        NOT NULL ,
 quantity     int             NOT NULL      DEFAULT 1,
 item_price   decimal(8,2)    NOT NULL 
);

1.2 更新表ALTER

利用ALTER TABLE更新表，要考虑：

理想情况下，不要在表中包含数据的时候对其进行更新，应该在创建表的时候，考虑未来的扩展性，避免对表的结构做大改动。
所有的DBMS都允许对现有表增加列，不过对所增加列的数据类型（以及NULL和DEFAULT的使用）有所限制。
多数DBMS允许重命名表中的列，不允许删除或更改表中的列。

给供应商Vendors表添加vend_phone列：

ALTER TABLE Vendors
ADD vend_phone CHAR(20);

删除供应商Vendors表的vend_phone列：

ALTER TABLE Vendors
DROP COLUMN vend_phone;

2 插入数据（INSERT）

2.1 数据插入

INSERT用来将行插入（或添加）到数据库表。

插入有几种方式：

插入完整的行。
插入行的一部分。
插入某些查询的结果。

2.2 插入完整的行

INSERT指定表名和插入到新行中的值。

INSERT INTO Customers
VALUES('1000000006','Toy Land','123 Any Street',
       'New York','NY','11111','USA',NULL,NULL);

插入数据时，各列必须以它们在表定义中出现的次序填充。

上面的SQL语句高度依赖于表中列的定义次序，还依赖于其容易获得的次序信息。即使可以得到这种次序信息，也不能保证各列在下一次表结构变动后保持完全相同的次序。因此，编写依赖于特定列次序的SQL语句是很不安全的。

实际上，在插入数据的时候，最好给出填充列的顺序。

INSERT INTO Customers(cust_id,cust_contact,cust_email,
                      cust_name,cust_address,cust_city,
                      cust_state,cust_zip)
VALUES('1000000006',NULL,NULL,'Toy Land','123 Any Street',
       'New York','NY','11111');

注意：不管使用哪种INSERT语法，VALUES的数目都必须正确。

2.3 插入部分行

插入部分行，表示可以只给某些列提供值，其他列不提供值。

INSERT INTO Customers(cust_id,cust_name,cust_address,
                      cust_city,cust_state,cust_zip)
VALUES('1000000006','Toy Land','123 Any Street',
       'New York','NY','11111');

省略的列必须满足以下某个条件：

该列定义为允许NULL值（无值或空值）。
在表定义中给出默认值。这表示如果不给出值，将使用默认值。

2.4 插入检索出的数据

INSERT可以将SELECT语句的查询结果插入表中，这就是所谓的INSERT SELECT。它由一条INSERT语句和一条SELECT语句组成。

假如想把另一个表中的顾客列合并到Customers表中。

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 SELECT中SELECT语句可以包含WHERE子句，以过滤插入的数据。

INSERT通常只插入一行，需要插入多行，必须执行多个INSERT语句。INSERT SELECT是个例外，它可以用一条INSERT插入多行，不管SELECT语句返回多少行，都将被INSERT插入。

2.5 从一个表复制到另一个表

有一种数据插入不使用INSERT语句。要将一个表的内容复制到一个全新的表（运行中创建的表），可以使用SELECT INTO语句。

MariaDB、MySQL、Oracle、PostgreSQL和SQLite的语法如下：

CREATE TABLE CustCopy AS SELECT * FROM Customers;

在使用SELECT INTO时，需要知道的事情：

任何SELECT选项和子句都可以使用，包括WHERE和GROUP BY。
可利用联结从多个表插入数据。
不管从多少个表中检索数据，数据都只能插入到一个表中。

3 更新和删除数据（UPDATE、DELETE）

3.1 更新数据UPDATE

两种使用UPDATE的方式：

更新表中的特定行。
更新表中的所有行。

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

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

UPDATE 表名
SET key1 = value1, key2 = value2
WHERE  满足条件、过滤条件

顾客ID为1000000005现在有了Email，需要更新他的记录。

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（加入表定义允许NULL值）。

UPDATE Customers
SET cust_email = NULL
WHERE cust_id = '1000000005';

注意：UPDATE语句也可使用子查询，使得能用SELECT语句检索出的数据更新列数据。

3.2 删除数据DELETE

两种使用DELETE的方式：

从表中删除特定的行。
从表中删除所有行。

基本的DELETE语句由两部分组成：

要删除的表。
确定要删除哪些行的过滤条件。

DELETE FROM 表名
WHERE  满足条件、过滤条件

DELETE FROM Customers
WHERE cust_id = '1000000006';

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

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

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

3.3 使用UPDATE和DELETE的指导原则

除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句。
在UPDATE或DELETE语句使用WHERE子句前，应该先用SELETE进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确。
增加DBMS更新和删除数据库的权限，防止执行不带WHERE子句的UPDATE或DELETE语句。

如果您发现文中有不清楚或者有问题的地方，请在下方评论区留言，我会根据您的评论，更新文中相关内容，谢谢！