How to retrieve data from postgreSQL table

To select all data from a table.
>> SELECT * FROM [table_name];

To select some contents of the columns in a table.
>> SELECT [column_name_1], [column_name_2], ..., [column_name_N]
      FROM [table_name];

To select some contents of the columns in a table.
>> SELECT [column_name_1], [column_name_2], ..., [column_name_N]
      FROM [table_name];

To list only distinct (different) values in a column from a table.
>> SELECT DISTINCT [column_name] FROM [table_name];

To extract only those data that fulfil specified criterion(s).
>> SELECT [column_name_1], [column_name_2], ..., [column_name_N]
      FROM [table_name]
      WHERE [column_name_K] <operator> [value_1] 
      AND|OR [column_name_L] <operator> [value_2];
List of operators:

=	Equal
<>	Not equal
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
BETWEEN	Between an inclusive range
LIKE	Search for a pattern
IN	To specify multiple possible values for a column

 IS                Same to

List of values:
NULL
NOT NULL

To search for a specified pattern in a column.
>> SELECT [column_name_1], [column_name_2], ..., [column_name_N]
      FROM [table_name]
      WHERE [column_name_K] LIKE <pattern>;

Wildcard	Description
%	A substitute for zero or more characters
_	A substitute for exactly one character
[characters_list]	Any single character in characters_list
[^characters_list]or [!characters_list]	Any single character not in characters_list

 
 Example:
 's%'                - String starts with character 's'
'%love%'        - String contents substring 'love'
's_n'                - String which has 3 characters only which starts with 's' and end with 'n'
'[a-z][A-Z]%' - String that starts with character a-z or A-Z.
'[^0-9]%'        - String that not starts with character 0-9.

To list rows in a table where the specified column in a multiple values.
>>  SELECT [column_name_1], [column_name_2], ..., [column_name_N]
      FROM [table_name]
      WHERE [column_name_k] IN (value_1, value_2, ...., value_N);
 example:
>> SELECT * FROM "children" WHERE "fatherName" IN (SELECT "name" FROM "parents");

To sort the result-set by a specified column.
>> SELECT [column_name_1], [column_name_2], ..., [column_name_N]
      FROM [table_name]
      ORDER BY [column_name_K] ASC|DESC;

To specific the number of records to return.
>>  SELECT [column_name_1], [column_name_2], ..., [column_name_N]
      FROM [table_name]
      LIMIT [number_of_records];

To select data from one table and inserts into a different table.
>> SELECT [column_name_1], [column_name_2], ..., [column_name_N]
     INTO [new_table_name] IN [external_database]
     FROM [old_table_name];

To combine result-set of two or more SELECT statement.
>> SELECT [column_name_1], [column_name_2], ..., [column_name_N]
     FROM [table_name_1]
     UNION 
     SELECT [column_name_1], [column_name_2], ..., [column_name_N]
     FROM [table_name_2];

To give an alias name to a table.
>> SELECT [column_name_1], [column_name_2], ..., [column_name_N]
     FROM [table_name] AS [alias_name];

To give an alias name to a column.
>> SELECT [column_name_1], 
     [column_name_2] AS [alias_name_1], ..., 
     [column_name_N] AS [alias_name_2]
     FROM [table_name];

 *************************************************************
 Different PostgreSQL JOIN

JOIN               : Return rows when there is at least one match in both tables (INNER JOIN)
LEFT JOIN   : Return all rows from the left table, even if there are no matches in the right table
RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
FULL JOIN  : Return rows when there is a match in one of the tables
To join 2 tables.
>> SELECT [table_x].[column_name_1],..., [table_y].[column_name_K]
     FROM [table_x]
     JOIN|INNER JOIN|LEFT JOIN|RIGHT JOIN|FULL JOIN [table_y]
     ON [table_x].[column_name_A]=[table_y].[column_name_B];



 select  1 from ...， sql语句中的1代表什么意思？查出来是个什么结果？

        select 1 from table;与select anycol(目的表集合中的任意一行） from table;与select * from table 从作用上来说是没有差别的，都是查看是否有记录，一般是作条件查询用的。select 1 from 中的1是一常量（可以为任意数值），查到的所有行的值都是它，但从效率上来说，1>anycol>*，因为不用查字典表。

 

测试场景：（转自网络文献）
table表是一个数据表，假设表的行数为10行。
1：select  1 from table       增加临时列，每行的列值是写在select后的数，这条sql语句中是1
2：select count(1)  from table   管count(a)的a值如何变化，得出的值总是table表的行数
3：select sum(1) from table   计算临时列的和

 在SQL SERVER中用 1 测试了一下，发现结果如下：
1：测试结果，得出一个行数和table表行数一样的临时列（暂且这么叫，我也不知道该叫什么），每行的列值是1；
2：得出一个数，该数是table表的行数；
3：得出一个数，该数是table表的行数；
然后我又用“2”测试，结果如下：
1：得出一个行数和table表行数一样的临时列，每行的列值是2；
2：得出一个数，该数是table表的行数；
3：得出一个数，该数是table表的行数×2的数
然后我又用更大的数测试：
1：得出一个行数和table表行数一样的临时列，每行的列值是我写在select后的数；
2：还是得出一个数，该数是table表的行数；
3：得出一个数，该数是table表的行数×写在select后的数
    综上所述：第一种的写法是增加临时列，每行的列值是写在select后的数；第二种是不管count(a)的a值如何变化，得出的值总是table表的行数；第三种是计算临时列的和。