数据库系统操作备忘（二）

• 联结（join）表

  1. 相同的数据在一张表内出现多次不是一件好事，此因素是关系型数据库设计的基础。关系表的设计就是要保证把信息分解成多个表。
  2. FROM···WHERE···可换为FROM···INNER JOIN···ON···
  3. 联结多个表，考虑的仅仅是几张表是通过哪些键进行联结的，写出联结后，便可直接取所有表里想要的信息了。

SELECT vend_name,prod_name,prod_price
FROM Vendors,Products
WHERE Vendors.vend_id = Products.vend_id; //限定性语句

FROM Vendors INNER JOIN Products ON Vendors.vend_id = Products.vend_id;

SELECT cust_name,cust_contact
FROM Customers,Orders,OrderItems
WHERE Customers.cust_id = Orders.cust_id
  AND OrderItems.order_num = Oders.order_num
  AND prod_id = 'RGAN01'  //此语句目的在于取订购了RGAN01产品的用户信息

• 自联结、自然联结、外部联结

  1. 自联结就是对同一张表进行联结。
  2. 自然联结。排除返回列的多次出现的情况，使每个列只返回一次。
  3. 外部联结用于联结那些在相关表中没有关联行的行。比如列出客户订单数，包括没下订单的客户。FROM···LEFT OUTER JOIN···ON···（LEFT指的是从OUTER JOIN左边的表选择所有行）；也可用*= 表示左联结。

//自联结
SELECT c1.Cust_id,c1.Cust_name,c1.Cust_contact
FROM Customers AS c1 , Customers AS c2   //同一张表取两次别名
WHERE c1.cust_name = c2.cust_name
AND   c2.cust_contact = 'Jim Jones'

//外部联结
SELECT Customers.cust_id,Orders.order_num
FROM Customers,Orders        // FROM Customers RIGHT OUTER JOIN Orders ON Orders.cust_id=Orders.cust_id
WHERE Customers.cust_id *= Orders.order_num

• 组合查询

  1. UNION，可以用于连接两条SELECT语句，给出所有筛选出的数据项。
  2. 使用UNION中，每个查询必须包含相同的列、表达式或聚合函数。
  3. UNION默认会自动去除重复行。也可使用UNION ALL 来返回所有。
  4. 可以使用ORDER BY进行排序，不过只能使用一次，并且应将其放置在最后一个SELECT后。

• 插入数据INSERT

  1. 可以插入完整的行、行的一部分、以及某些查询的结果
  2. INSERT (···) SELECT (···) FROM ···。此语句可以先用SELECT筛选出另一张表里的数据，再插入到现表中。
  3. SELECT INTO，此语句是导入一张新表

INSERT IN TO Customers 
VALUES ('1006','Toy Land',···,NULL) 
//基本语法，插入完整的行。更可靠的做法是在Customers表名后，列出插入的项名，可以不按表的实际顺序。

SELECT * INTO CustCopy FROM Customers //创建出了一个名为CustCopy的新表
CREATE TABLE CustCopy AS SELECT * FROM Customers //MySQL、Oracle专用

• 更新及删除数据

  1. 更新数据： UPDATE 。不要省略WHERE语句，不然更新整张表。允许一次更新多个值，用逗号隔开，最后一行不用逗号。
  2. 删除数据： DELETE 。同样不要省略WHERE。后果自负。注意，DELETE不删除表本身。
  3. 通常习惯是，在使用UPDATE或者DELETE使用WHERE子句之前，应该先用SELECT进行测试，保证它过滤的是正确的记录，以防止WHERE子句不正确。

UPDATE Customers
SET cust_contact = 'Sam Roberts',    
    cust_email = 'sam@toyland.com'   //若要清空，可设置为NULL
WHERE cust_id = '100006';

DELETE FROM Customers
WHERE cust_id = '100006';

• 表的创建及操纵

  1. CREATE TABLE。使用CREATE TABLE来创建，创建时，要保证指定的表名不存在。
  2. 创建时，每列取值可以给出 NULL 或者 NOT NULL 。后者就是指，在插入或者更新新行时，该列必须有值，否则返回错误或者插入失败。
  3. 注意，NULL是默认值，也就是不指定的话，默认就当做NULL，即可以插入空值。
  4. ALTER TABLE。此语句可以更新表的结构，比如给列改名、增加列等。
  5. DROP TABLE。可以删除表。

CREATE TABLE Orders
{
    order_num   INTEGER   NOT NULL,
    order_date  DATETIME  NOT NULL,
    cust_id     CHAR(10)  NOT NULL,
    cust_city   CHAR(50)  NULL，
    quantity    INTEGER   ,          DEFAULT 1,
};

ALTER TABLE Vendors
ADD vend_phone CHAR(20);  //ADD可以换成其他，如DROP COLUMN

• 视图

  1. 视图不包含数据，它包含的是一个查询条件。它仅仅是用来查看存储在别处的数据的一种设施。
  2. 如果视图里的规则过于复杂，则检索性能可能会下降的很厉害。
  3. CREATE VIEW，可以用来简化语句，可供多个地方调用，更简单。

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 cust_name, cust_contact
FROM ProductCustomers
WHERE prod_id = 'RGAN01'; //使用视图进行检索

• 存储过程

  1. 它实质就是为以后的重复使用而保存的一条或者多条SQL语句的集合，可视为批处理文件，但其作用不仅限于批处理。
  2. EXCUTE，接受存储过程名以及需要传递给它的任何参数。

CREATE PROCEDURE NewOrder @cust_id CHAR(10)
AS
DECLARE @order_num INTEGER
SELECT @order_num=MAX(order_num)
FROM Orders
SELECT @order_num=@order_num+1
INSERT INTO Orders(order_num,order_date,cust_id)
VALUES(@order_num,GETDATE(),@cust_id)
RETURN @order_num;  //创建一个存储过程，它只用接收一个参数，即cust_id