SQL Syntax(Ⅲ)

后面储存过程、事务处理、游标和一些高级特性感觉太遥远就只是翻了翻，没作整理。

联结表

创建联结

SELECT vend_name, prod_name, prod_price
FROM Venders, Products
WHERE Venders.vend_id = Products.id
　　简单解释一下，Venders和Products是一个数据库下的两张表，他们都有一个列叫Vend_id, prod_name和prod_price是表Products中的列，vend_name是表Venders中的列。 需要注意的是，如果在SELECT中添加了共有的列名，需要做完全限定，比如上面的例子中，如果需要把vend_id这个列提取出来，需要用Venders.vend_id或者Products.id来完全限定。 另外就是，WHERE子句非常重要，如果这里不用WHERE子句的话，输出的结果将会是指定的两部分内容的每一行的组合的结果，而忽略其中的逻辑，输出结果的行数将等于第一部分的列乘以第二个部分列的行数。这个结果也被称为笛卡尔积。所以，在联结表时为了避免输出的结果过长，千万不要忘了WHERE子句。

　　以上的联结称为等值联结(equijoin)，也称为内联结(inner join)，我们可以用内联结的写法来重新写与上面等价的SQL语句：

SELECT vend_name, prod_name, prod_price
FROM Venders INNER JOIN Products
 ON Venders.vend_id = Products.id

　　实际上，联结表的操作也可以用子查询完成，但SQL语句书写起来会麻烦许多。

创建高级联结

使用表别名

表别名还有以下这种有趣的用法：

SELECT cust_name, cust_contract
FROM Customers AS C, Orders AS O, OrderItems AS OI
WHERE C.cust_id = O.cust_id
 AND OI.order_num = O.order_num
 AND prod_id = 'RGAN01'
P.S.:Oracle中没有AS这个用法

其他类型的联结
  • 自联结(self-join) 使用别名的好处在于可以不止一次地引用同一个表。下面两段SQL语句是等价的：

SELECT cest_id, cust_name, cust_contact
FROM Customers
WHERE cust_name = (SELECT cust_name
                   FROM Customers
                   WHERE cust_contact = 'Jim Jones');
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_contract = 'Jim Jones';
　　第一段使用了子查询,而第二段使用了联结，他们的效果是一样的，但在许多DBMS中，处理联结远比子查询快得多。 　　

  • 自然联结(natural join)

    标准的联结返回所有的数据，相同的列甚至多次出现。自然联结排除多次出现，使每一列只返回一次。如：

SELECT C.*, O.order_num, O.order_date,
       OI.prod_id, OI.quantity, OI.item_price
FROM Customers AS C, Orders AS O, OrderItems AS OI
WHERE C.cust_id = O.cust_id
 AND OI.order_num = O.order_num
 AND prod_id = 'RGAN01';
  • 外联结 包含在相关表中没有关联行的行的联结，称为外联结(outer join)。用法如：

SELECT Customers.cust_id, Orders.order_num
FROM Customers LEFT OUTER JOIN Orders
 ON Customers.cust_id = Orders.cust_id;
注意，OUT JOIN前必须使用RIGHT或者LEFT关键字制定包括其所有行的表（RIGHT指出的是OUTER JOIN了FROM中右边的表，LEFT同理）。

  • 全外联结 检索两个表中的所有行并关联那些可以关联的行，这种外联结称为全外联结(full outer join),如：

SELECT Customers.cust_id, Orders.order_num
FROM Customers FULL OUTER JOIN Orders
 ON Customers.cust_id = Orders.cust_id;
当然，也可以在联结中使用聚集函数。

组合查询

SQL允许执行多个查询（多条SELECT语句），并将结果作为一个查询结果集返回。这些组合查询通常称为并(union)或复制查询(compound query)

使用UNION

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL', 'IN', 'MI')
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All';
　　当然这里可以用OR达成同样的效果，但对于较复杂的过滤条件，或者从多个表检索数据的情形，使用UNION可能会使处理更简单。另外需要注意,UNION每个查询必须包含相同的列，表达式或聚集函数。

　　此外，UNION会默认自动取消重复的行，当然如果有需要，也可以改变它，使用UNION ALL即可。如果需要对组合查询的结果进行排序，只需要在最后加上ORDER BY即可。

插入数据

使用INSERT

INSERT INTO table_name
VALUES(Value 1,
       Value 2,
       Value 3,
       ......);
　　这种用法很简单，但并不安全，应该尽量避免使用。更安全（也更烦琐）的用法应当是：

INSERT INTO table_name(column1,
                       column2,
                       column3,
                       ......)
VALUES(Value 1,
       Value 2,
       Value 3,
       ......);
　　Values将以指定的次序匹配指定的列名，不一定按各列出现在表中的实际次序。其优点是，即使表的结构改变，这条INSERT仍然能正确工作。 　　

插入检索出的数据

　　INSERT还存在另一种形式，可以利用它将SELECT语句的结果插入表中，这就是所谓的INSERT SELECT，如：

INSERT INTO table_name(column1,
                       column2,
                       column3,
                       ......)
SELECT column_a,
       column_b,
       column_c,
       ......
FROM another_table_name;
　　当然，利用INSERT SELECT，可以实现从一个表复制到另一个表： 
SELECT *
INTO table1
FROM table2;
在MariaDB, MySQL, Oracle, PostgreSQL 和 SQLite 使用的用法稍有不同：

CREATE TABLE table1 AS
SELECT * FROM table2;

更新和删除数据

更新数据
UPDATE table_name
SET column1=value1,
    column2=value2,
    ......
WHERE certain_column = certain_value;
删除数据
DELETE FROM table_name
WHERE ......

创建和操纵表

创建表

CREATE TABLE table_name
(
    column1_name	column_type		NULL/NOT NULL,
    column2_name	column_type		NULL/NOT NULL,
    column2_name	column_type		NULL/NOT NULL,
    ......
);
其中，NULL是默认设置，可以省略。

指定默认值

CREATE TABLE table_name
(
    column1_name  column_type  NULL/NOT NULL DEFAULT [value],
    column2_name  column_type  NULL/NOT NULL DEFAULT [value],
    column2_name  column_type  NULL/NOT NULL DEFAULT [value],
    ......
);
在实际中，常用的默认值会用到当前时间。

DBMS 函数/变量
Access NOW()
DB2 CURRENT_DATE
MySQL CURRENT_DATE()
Oracle SYSDATE
PostgreSQL CURRENT_DATE
SQL Server GETDATE()
SQLite date(‘now’)
更新表

以下展示了添加和删除列的用法：

ALTER TABLE table_name
ADD column_name column_type;
ALTER TABLE table_name
DROP column_name;

删除表
DROP TABLE table_name;

使用视图

视图(View)，其实就是封装起来的查询操作。【使用视图的好处大约就和贯彻面向对象思想的好处一样多。】

创建视图

CREATE VIEW view_name AS
.....
[SQL Query]
.....
之后需要再次执行该查询操作时，只需要直接用 
SELECT column1,column2,...
FROM view_name
WHERE ...
即可。

Contents


本作品采用知识共享署名-非商业性使用-禁止演绎 4.0 国际许可协议进行许可。

知识共享许可协议