PostgreSQL入门(2)
TIP
接上篇
ORDER BY 语句
在 PostgreSQL 中，ORDER BY 用于对一列或者多列数据进行升序（ASC）或者降序（DESC）排列。
语法
ORDER BY 子句的基础语法如下：
SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];
您可以在 ORDER BY 中使用一列或者多列，但是必须保证要排序的列必须存在。
ASC 表示升序，DESC 表示降序。
实例
-- The default order is from low to high.
SELECT * FROM sales_item WHERE discount > .15 ORDER BY discount;
-- The following gives results from high to low
SELECT * FROM sales_item WHERE discount > .15 ORDER BY discount DESC;
LIMIT 子句
PostgreSQL 中的 limit 子句用于限制 SELECT 语句中查询的数据的数量。
语法
带有 LIMIT 子句的 SELECT 语句的基本语法如下：
SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows]
下面是 LIMIT 子句与 OFFSET 子句(偏移量)一起使用时的语法：
SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows] OFFSET [row num]
实例
-- LIMIT limits the number of rows in the result. Get just the top 5. You could use LIMIT 5, 10 to get the next 5
SELECT * FROM sales_item WHERE discount > .15 ORDER BY discount DESC LIMIT 5;
HAVING 子句
HAVING 子句可以让我们筛选分组后的各组数据。
WHERE 子句在所选列上设置条件，而 HAVING 子句则在由 GROUP BY 子句创建的分组上设置条件。
语法
下面是 HAVING 子句在 SELECT 查询中的位置：
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
HAVING 子句必须放置于 GROUP BY 子句后面，ORDER BY 子句前面，下面是 HAVING 子句在 SELECT 语句中基础语法：
SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2
实例
SELECT EXTRACT(MONTH FROM birth_date) AS Month, COUNT(*)
FROM customer
GROUP BY Month
HAVING COUNT(*) > 1
ORDER BY Month;
GROUP BY 语句
在 PostgreSQL 中，GROUP BY 语句和 SELECT 语句一起使用，用来对相同的数据进行分组。
GROUP BY 在一个 SELECT 语句中，放在 WHRER 子句的后面，ORDER BY 子句的前面。
语法
下面给出了 GROUP BY 子句的基本语法:
SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN
GROUP BY 子句必须放在 WHERE 子句中的条件之后，必须放在 ORDER BY 子句之前。
在 GROUP BY 子句中，你可以对一列或者多列进行分组，但是被分组的列必须存在于列清单中。
实例
SELECT product_id, SUM(price) AS Total FROM item WHERE product_id=1 GROUP BY product_id;
DISTINCT 关键字
在 PostgreSQL 中，DISTINCT 关键字与 SELECT 语句一起使用，用于去除重复记录，只获取唯一的记录。
我们平时在操作数据时，有可能出现一种情况，在一个表中有多个重复的记录，当提取这样的记录时，DISTINCT 关键字就显得特别有意义，它只获取唯一一次记录，而不是获取重复记录。
语法
用于去除重复记录的 DISTINCT 关键字的基本语法如下：
SELECT DISTINCT column1, column2,.....columnN
FROM table_name
WHERE [condition]
实例
-- The IN phrase can be used to test if a value is in a list. Find customer states that are in my list. You can also use NOT IN.
SELECT DISTINCT state
FROM customer
WHERE state IN ('CA', 'NJ')
ORDER BY state;
连接(JOIN)
PostgreSQL JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。
在 PostgreSQL 中，JOIN 有五种连接类型：
- CROSS JOIN ：交叉连接
- INNER JOIN：内连接
- LEFT OUTER JOIN：左外连接
- RIGHT OUTER JOIN：右外连接
- FULL OUTER JOIN：全外连接
内连接
内连接（INNER JOIN）根据连接谓词结合两个表（table1 和 table2）的列值来创建一个新的结果表。查询会把 table1 中的每一行与 table2 中的每一行进行比较，找到所有满足连接谓词的行的匹配对。
当满足连接谓词时，A 和 B 行的每个匹配对的列值会合并成一个结果行。
内连接（INNER JOIN）是最常见的连接类型，是默认的连接类型。
INNER 关键字是可选的。
下面是内连接（INNER JOIN）的语法：
SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_filed = table2.common_field;
实例：
-- You can define multiple join conditions with logical operators :
SELECT item_id, price
FROM item INNER JOIN sales_item
ON item.id = sales_item.item_id
AND price > 120.00
ORDER BY item_id;
交叉连接
交叉连接（CROSS JOIN）把第一个表的每一行与第二个表的每一行进行匹配。如果两个输入表分别有 x 和 y 行，则结果表有 x*y 行。
由于交叉连接（CROSS JOIN）有可能产生非常大的表，使用时必须谨慎，只在适当的时候使用它们。
下面是 CROSS JOIN 的基础语法：
SELECT ... FROM table1 CROSS JOIN table2 ...
实例：
SELECT sales_order_id, quantity, product_id
FROM item CROSS JOIN sales_item
ORDER BY sales_order_id;
左外连接
外部连接是内部连接的扩展。SQL 标准定义了三种类型的外部连接: LEFT、RIGHT 和 FULL, PostgreSQL 支持所有这些。
对于左外连接，首先执行一个内连接。然后，对于表 T1 中不满足表 T2 中连接条件的每一行，其中 T2 的列中有 null 值也会添加一个连接行。因此，连接的表在 T1 中每一行至少有一行。
下面是左外连接（ LEFT OUTER JOIN ）的基础语法：
SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...
实例：
SELECT name, supplier, price
FROM product LEFT JOIN item
ON item.product_id = product.id
ORDER BY name;
UNION 操作符
PostgreSQL UNION 操作符合并两个或多个 SELECT 语句的结果。
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。
语法
UNIONS 基础语法如下：
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
这里的条件语句可以根据您的需要设置任何表达式。
实例
-- 假设我们想给所有客户和销售人员发送12月份的生日贺卡，我们可以这样做。总是把Order By语句放在最后。列名取自第一个选择语句中提供的列名。(我们使用Extract来获取从出生日期开始的月份)
SELECT first_name, last_name, street, city, zip, birth_date
FROM customer
WHERE EXTRACT(MONTH FROM birth_date) = 12
UNION
SELECT first_name, last_name, street, city, zip, birth_date
FROM sales_person
WHERE EXTRACT(MONTH FROM birth_date) = 12
ORDER BY birth_date;
View（视图）
View（视图）是一张假表，只不过是通过相关的名称存储在数据库中的一个 PostgreSQL 语句。
View（视图）实际上是一个以预定义的 PostgreSQL 查询形式存在的表的组合。
View（视图）可以包含一个表的所有行或从一个或多个表选定行。
View（视图）可以从一个或多个表创建，这取决于要创建视图的 PostgreSQL 查询。
View（视图）是一种虚拟表，允许用户实现以下几点：
- 用户或用户组认为更自然或直观查找结构数据的方式。
- 限制数据访问，用户只能看到有限的数据，而不是完整的表。
- 汇总各种表中的数据，用于生成报告。
PostgreSQL 视图是只读的，因此可能无法在视图上执行 DELETE、INSERT 或 UPDATE 语句。但是可以在视图上创建一个触发器，当尝试 DELETE、INSERT 或 UPDATE 视图时触发，需要做的动作在触发器内容中定义。
CREATE VIEW（创建视图）
在 PostgreSQL 用 CREATE VIEW 语句创建视图，视图创建可以从一张表，多张表或者其他视图。
CREATE VIEW 基础语法如下：
CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];
您可以在 SELECT 语句中包含多个表，这与在正常的 SQL SELECT 查询中的方式非常相似。如果使用了可选的 TEMP 或 TEMPORARY 关键字，则将在临时数据库中创建视图。
实例
现在，下面是一个从 COMPANY 表创建视图的实例。视图只从 COMPANY 表中选取几列：
CREATE VIEW COMPANY_VIEW AS
SELECT ID, NAME, AGE
FROM COMPANY;
现在，可以查询 COMPANY_VIEW，与查询实际表的方式类似。下面是实例：
SELECT * FROM COMPANY_VIEW;
DROP VIEW （删除视图）
要删除视图，只需使用带有 view_name 的 DROP VIEW 语句。DROP VIEW 的基本语法如下：
DROP VIEW view_name;
SQL Functions
您可以编写类似于传统编程语言的程序。有不同类型的存储程序。存储函数可以通过SQL语句执行。
创建函数后，它们出现在functions文件夹中。你可以通过使用函数的属性来查看函数的信息。
实例：
CREATE OR REPLACE FUNCTION fn_add_ints(int, int)
RETURNS int as
$body$
--$1 refers to 1st parameter and $2 the 2nd
SELECT $1 + $2;
$body$
LANGUAGE SQL