PostgreSQL 中的 SELECT 语句的用法

一，基本用法

SQL语句是不区分大小写的。
SQL language is case insensitive.
按照惯例，我们用大写的形式来写SQL关键字，使得代码更容易阅读。
By convention, we will use SQL keywords in uppercase to make the code easier to read.

1.查看当前数据库中有哪些表

select * from pg_tables where schemaname = ‘public’;
末尾的分号不可以缺失，用于向引擎发送SQL语句结束的信号。
It is used to signal PostgreSQL engine the end of an SQL statement.

2，不建议使用*

读取了很多不需要的列，而且增加了通信负担。明确定义需要查询的列名，是一种很好的做法。
it is a good practice to specify the column names explicitly in the SELECT clause whenever possible to get only necessary data from a table.

3，把几列的值拼接成一列，用别名显示查询出的数据

使用 || 操作符，可以把两个或多个字段拼接起来，还可以加自己的 ‘字符串’。
SELECT first_name || ‘–’ || last_name AS all_name ,email FROM customer;

To concatenate two or more strings into one, you use the string concatenation operator || .

4，可以直接只使用表达式

SELECT 5 * 3 AS result;

we can skip the FROM clause if the statement does not refer to any table.

5.别名

可以给一个表或列，一个别名，用 as，有什么也不用加，直接跟一个别名。
The main purpose of the column alias is to make the output of a query more meaningful.
A PostgreSQL alias assigns a table or a column a temporary name in a query. The aliases only exist during the execution of the query.
表的别名，可以如下：
First, if you must qualify a column name with a long table name, you can use the table alias to save some keystrokes and make your query more readable.

Second, when you join a table to itself a.k.a self-join, you must use table aliases. Because PostgreSQL does not allow you to reference the same table multiple times within a query.

二，ORDER BY

Sort rows usingORDER BY clause.

不用 order by ,查询出的行按插入时的顺序排列。

SELECT
 column_1,
 column_2
FROM
   tbl_name
ORDER BY
   column_1 ASC,
   column_2 DESC;
  默认按从小到大（ASC）的形式排序。

PostgreSQL允许使用不在SELECT子句中出现的字段来排序。

Notice that the SQL standard only allows you to sort rows based on the columns that appear in the SELECTclause. However, PostgreSQL allows you to sort rows based on the columns that even does not appear in the selection list.

三，DISTINCT

Select distinct rows using DISTINCT operator.

The DISTINCT clause keeps one row for each group of duplicates.
If you specify multiple columns, the DISTINCT clause will evaluate the duplicate based on the combination of values of these columns.
若是多个字段，那就多个字段的联合是相等的，才算重复。

PostgreSQL also provides the DISTINCT ON (expression) to keep the “first” row of each group of duplicates.
这个 DISTINCT ON （字段1，[可以多个字段]）特性很有意思，需要搭配ORDER BY 来使用，返回每个字段1结果集的第一个。所以结果集需要有确定的顺序好选择所需要的行。括号外的别的字段不参与去重。
The order of rows returned from the SELECT statement is unpredictable therefore the “first” row of each group of the duplicate is also unpredictable. It is good practice to always use the ORDER BY clause with the DISTINCT ON(expression) to make the result set obvious.

 SELECT
 DISTINCT ON
 (bcolor) bcolor,
 fcolor
FROM
 t1
ORDER BY
 bcolor,
 fcolor;

四，WHERE

Filter rows using WHERE clause.

Besides the SELECT statement, you can use the WHERE clause in the UPDATE and DELETE statement to specify rows to be updated or deleted.

IN

If you want to match a string with any string in a list, you can use the IN operator.
Also NOT IN is on the contrary.You can also rewrite the NOT IN operator by using the not equal (<>) and the AND operators
The expression returns true if the value matches any value in the list i.e., value1 and value2. The list of values can be a list of numbers or strings or the result set of a SELECT statement 。
The query that uses the IN operator is shorter and more readable than the query that uses equal (=) and OR operators.

SELECT
  first_name,
  last_name
FROM
  customer
WHERE
  customer_id IN (
     SELECT
        customer_id
     FROM
        rental
     WHERE
        CAST (return_date AS DATE) = '2005-05-27'
  );

A cast specifies how to perform a conversion between two data types by invoking a previously specified function . (If no suitable cast has been defined, the conversion fails.)

To find a string that matches a specified pattern, you use the LIKE operator. The % is called a wildcard that matches any string.

SELECT
   first_name,
   LENGTH(first_name) name_length
FROM
   customer
WHERE 
   first_name LIKE 'A%' AND
   LENGTH(first_name) BETWEEN 3 AND 5
ORDER BY
   name_length;

we used the LENGTH() function returns the number of characters of the input string.

Note that you can use the != operator instead of <> operator. They have the same effect.

BETWEEN，选取查询区间

You use the BETWEEN operator to match a value against a range of values.
If you want to check if a value is out of a range, you combine the NOT operator with the BETWEEN operator
You often use the BETWEEN operator in the WHERE clause of a SELECT, INSERT, UPDATE or DELETE statement.

字符串匹配：LIKE

You construct a pattern by combining a string with wildcard characters and use the LIKE or NOT LIKE operator to find the matches. PostgreSQL provides two wildcard characters:
Percent ( %) for matching any sequence of characters.
Underscore ( _) for matching any single character.
If the pattern does not contain any wildcard character, the LIKE operator acts like the equal ( =) operator.
PostgreSQL provides the ILIKE operator that acts like the LIKE operator. In addition, the ILIKE operator matches value case-insensitively.(ILIKE 不区分大小写)

使用 NULL：有的字段还没有值，就可以先插入NULL

In the database world, NULL means missing or not applicable information。
在查询时，需要使用 IS NULL 和 IS NOT NULL
Even though there is a NULL in the phone column, the expression NULL = NULL returns false. This is because NULL is not equal to any value even itself.

To check whether a value is NULL or not, you use the IS NULL operator instead

五，LIMIT or FETCH

Select a subset of rows from a table using LIMIT or FETCH clause.

limit 0 返回零行
limut NULL ;与不加limit的效果一样。

跳过几行，再显示（用OFFSET）
If you want to skip a number of rows before returning the n rows, you use OFFSET clause placed after the LIMIT clause。
OFFSET 的行依然会在后端计算，只是不返回。

Because the order of the rows in the database table is unspecified, when you use the LIMIT clause, you should always use the ORDER BY clause to control the row order. If you don’t do so, you will get a result set whose rows are in an unspecified order.

However, the LIMIT clause is not a SQL-standard.

To conform with the SQL standard, PostgreSQL provides the FETCH clause to retrieve a portion of rows returned by a query.
Note that the OFFSET clause must come before the FETCH clause in SQL:2008. However, OFFSET and FETCH clauses can appear in any order in PostgreSQL.

SELECT
   film_id,
	title
FROM
   film
ORDER BY
   title 
OFFSET 5 ROWS 
FETCH FIRST 5 ROW ONLY; 

六，GROUP BY

分组，把选出的行，按某些类别分组，
如果不加 计算函数，会像 DISTINCT 一样。
一般可以与 SUM，COUNT 一起使用。
Group rows into groups using GROUP BY clause

one column or a list of comma-separated columns.

The GROUP BY clause divides the rows returned from the SELECT statement into groups. For each group, you can apply an aggregate function

七，HAVING

Filter groups using HAVING clause.

以 GROUP BY 为界，HAVING在其后筛选，WHERE在其前筛选。

We often use the HAVINGclause in conjunction with the GROUP BY clause to filter group rows that do not satisfy a specified condition.

The HAVINGclause sets the condition for group rows created by the GROUP BY clause after the GROUP BY clause applies while the WHERE clause sets the condition for individual rows before GROUP BY clause applies. This is the main difference between the HAVINGand WHEREclauses.
当不与GROUP BY 连用时；
In PostgreSQL, you can use the HAVINGclause without the GROUP BY clause. In this case, the HAVINGclause will turn the query into a single group. In addition, the SELECTlist and HAVINGclause can only refer to columns from within aggregate functions. This kind of query returns a single row if the condition in the HAVINGclause is true or zero row if it is false.

八，JOIN

.把几个表的列，根据表之间的共同列的值，给组合起来
PostgreSQL join is used to combine columns from one (self-join) or more tables based on the values of the common columns between the tables. The common columns are typically the primary key columns of the first table and foreign key columns of the second table.

PostgreSQL supports inner join, left join, right join, full outer join, cross join, natural join, and a special kind of join called self-join.

INNER JOIN

根据左表，扫描右表
满足条件的的，两行合一行，加入结果集
要是两个表中有 同名列，需要使用表名区分，一般用加上表的别名来区分
要是连接三个表，只需再加一个INNER JOIN 即可。（需要根据表的关系来）
For each row in the A table, PostgreSQL scans the B table to check if there is any row that matches the condition i.e., A.pka = B.fka. If it finds a match, it combines columns of both rows into one row and add the combined row to the returned result set.
The primary key column ( pka) and foreign key column ( fka) are typically indexed; therefore, PostgreSQL only has to check for the match in the indexes, which is very fast.
To join the three tables, you place the second INNER JOIN clause after the first INNER JOIN clause

LEFT JOIN

左表都有，右表中没有的用NULL表示

If you want to select rows from the A table which may or may not have corresponding rows in the B table, you use the LEFT JOIN clause. In case, there is no matching row in the B table, the values of the columns in the B table are substituted by the NULL values.
The LEFT JOIN clause returns all rows in the left table ( A) that are combined with rows in the right table ( B) even though there is no corresponding rows in the right table ( B).

The LEFT JOIN is also referred as LEFT OUTER JOIN.

This technique is useful when you want to select data in one table that does not have a match in another table.

与左连接相似，只不过是反过来

自连接

A self-join is a query in which a table is joined to itself.
Self-joins are useful for comparing values in a column of rows within the same table.

SELECT
f1.title,
f2.title,
f1. length
FROM
film f1
INNER JOIN film f2 ON f1.film_id <> f2.film_id
AND f1. length = f2. length;

FULL JOUN

两个都包含，没有的用NULL填充

The full outer join combines the results of both left join and right join. If the rows in the joined table do not match, the full outer join sets NULL values for every column of the table that lacks a matching row. For the matching rows , a single row is included in the result set that contains columns populated from both joined tables.

CROSS JOIN ，交叉连接

进行笛卡尔积，就是 一个表的一行，与另一个表的每一行，都组成一个新的行，加入到结果集中。

A CROSS JOIN clause allows you to produce the Cartesian Product of rows in two or more tables. Different from the other JOIN operators such as LEFT JOIN or INNER JOIN, the CROSS JOIN does not have any matching condition in the join clause.
结果集的个数是笛卡尔积的值。
a cartesian product, the result set will contain a row that consists of all columns in the T1 table followed by all columns in the T2 table. If T1 has N rows, T2 has M rows, the result set will have N x M rows.

The following illustrates the syntax of the PostgreSQL CROSS JOIN clause:

SELECT * 
FROM T1
CROSS JOIN T2;

The following statement is also equivalent to the CROSS JOIN above:

SELECT * 
FROM T1, T2;

You can use the INNER JOIN clause with the condition evaluates to true to perform the cross join as follows:

SELECT *
FROM T1
INNER JOIN T2 ON TRUE;

NATURAL JOIN

隐含的根据两个表相同的列，进行连接，不用指明，一般有一列相同时使用较方便
若有多列相同，可能出现不期望的结果。

The convenience of the NATURAL JOIN is that it does not require you to specify the join clause because it uses an implicit join clause based on the common column.

SELECT *
FROM T1
NATURAL [INNER, LEFT, RIGHT] JOIN T2;

A natural join can be an inner join, left join, or right join. If you do not specify a join explicitly e.g., INNER JOIN, LEFT JOIN, RIGHT JOIN, PostgreSQL will use the INNER JOIN by default.

However, you should avoid using the NATURAL JOIN whenever possible because sometimes it may cause an unexpected result.

总结图

九，UNION，EXCEPT，INTERSECT

Perform set operations using UNION, INTERSECT, and EXCEPT.

UNION

两个查询的行，相加到结果集

The UNION operator combines result sets of two or more SELECT statements into a single result set.

Both queries must return the same number of columns.
The corresponding columns in the queries must have compatible data types.

The UNION operator removes all duplicate rows unless the UNION ALL is used.

行结果排列顺序是随机的，可以用ORDER BY来排序，仅放在最后一个SELECT后就好了

INTERSECT

两个查询都有的行，才进入到结果集，不重复的结果集
Like the UNION and EXCEPT operators, the PostgreSQL INTERSECT operator combines the result sets of two or more SELECT statements into a single result set. The INTERSECT operator returns any rows that are available in both result set or returned by both queries.

The number of columns and their order in the SELECT clauses must the be the same.
The data types of the columns must be compatible.

EXCEPT

在第一个，不在第二个 的行。

The EXCEPT operator returns distinct rows from the first (left) query that are not in the output of the second (right) query.

两个条件同上。

三个的ORDER BY 都是放在最后就好了。

以上内容参考于网站：http://www.postgresqltutorial.com/postgresql-select/