什么是PostgreSQL？
PostgreSQL是一个使用SQL查询语言的开源数据库管理系统。 PostgreSQL或者简单的“Postgres”是一个在VPS服务器上非常有用的工具，因为它可以处理网站和其他应用程序的数据存储需求。
在本指南中，我们将讨论如何查询PostgreSQL数据库。 这将允许我们指示Postgres返回它所管理的所有符合我们正在寻找的条件的数据。
本教程假定您已在计算机上安装Postgres。 我们将使用Ubuntu 12.04，但任何现代Linux分发应该工作。
登录PostgreSQL
我们将下载一个从互联网工作的示例数据库。
首先，使用以下命令登录到默认的Postgres用户：
sudo su - postgres
我们将通过键入以下内容获取数据库文件：
wget http://pgfoundry.org/frs/download.php/527/world-1.0.tar.gz
提取gzip压缩的归档文件并更改到内容目录：
tar xzvf world-1.0.tar.gz cd dbsamples-0.1/world
创建数据库以将文件结构导入到：
createdb -T template0 worlddb
最后，我们将使用.sql文件作为新创建的数据库的输入：
psql worlddbWe are now ready to log into our newly create environment:
psql worlddbHow to Show Data in PostgreSQL
Before we begin, let's get an idea of what kind of data we just imported. To see the list of tables, we can use the following command:
\d+List of relations Schema | Name | Type | Owner | Size | Description --------+-----------------+-------+----------+--------+------------- public | city | table | postgres | 264 kB | public | country | table | postgres | 48 kB | public | countrylanguage | table | postgres | 56 kB | (3 rows)We have three tables here. If we want to see the columns that make up the "city" table, we can issue this command:
\d cityTable "public.city" Column | Type | Modifiers -------------+--------------+----------- id | integer | not null name | text | not null countrycode | character(3) | not null district | text | not null population | integer | not null Indexes: "city_pkey" PRIMARY KEY, btree (id) Referenced by: TABLE "country" CONSTRAINT "country_capital_fkey" FOREIGN KEY (capital) REFERENCES city(id)We can see information about each of the columns, as well as this table's relationship with other sets of data.
How to Query Data with Select in PostgreSQL
We query (ask for) information from Postgres by using "select" statements. These statements use this general syntax:
SELECT columns_to_return FROM table_name;For example, if we issue "\d country", we can see that the "country" table has many columns. We can create a query that lists the name of the country and the continent it is on with the following:
SELECT name,continent FROM country;name | continent ----------------------------------------------+--------------- Afghanistan | Asia Netherlands | Europe Netherlands Antilles | North America Albania | Europe Algeria | Africa American Samoa | Oceania Andorra | Europe . . .To view all of the columns in a particular table, we can use the asterisk (*) wildcard character. This means "match every possibility" and, as a result, will return every column.
SELECT * FROM city;id | name | countrycode | district | population ------+-----------------------------------+-------------+-------------------------------+------------ 1 | Kabul | AFG | Kabol | 1780000 2 | Qandahar | AFG | Qandahar | 237500 3 | Herat | AFG | Herat | 186800 4 | Mazar-e-Sharif | AFG | Balkh | 127800 5 | Amsterdam | NLD | Noord-Holland | 731200 6 | Rotterdam | NLD | Zuid-Holland | 593321 7 | Haag | NLD | Zuid-Holland | 440900 . . .Here, we see the "city" table in its entirety.Ordering Query Results in PostgreSQL
You can organize the results of your query by using the "order by" clause. This allows you to specify a sort order to the returned data.
The "order by" clause comes after the normal select statement. This is the general syntax:
SELECT columns FROM table ORDER BY column_names [ ASC | DESC ];If we wanted to select the country and continent from the country table, and then order by continent, we can give the following:
SELECT name,continent FROM country ORDER BY continent;name | continent ----------------------------------------------+--------------- Algeria | Africa Western Sahara | Africa Madagascar | Africa Uganda | Africa Malawi | Africa Mali | Africa Morocco | Africa Côte d\u0092Ivoire | Africa . . .As you can see, by default the order statement organizes data in ascending order. This means that it starts at the beginning of lettered organizations and the lowest number for numerical searches.
If we want to reverse the sort order, we can type "desc" after the "order by" column declaration:
SELECT name,continent FROM country ORDER BY continent DESC;name | continent ----------------------------------------------+--------------- Paraguay | South America Bolivia | South America Brazil | South America Falkland Islands | South America Argentina | South America Venezuela | South America Guyana | South America Chile | South America . . .We can also choose to sort by more than one column. We can have a primary sort field, and then additional sort fields that are used if multiple records have the same value in the primary sort field.
For instance, we can sort by continent, and then by country to get an alphabetical list of country records in each continent:
SELECT name,continent FROM country ORDER BY continent,name;name | continent ----------------------------------------------+--------------- Angola | Africa Burundi | Africa Benin | Africa Burkina Faso | Africa Botswana | Africa Central African Republic | Africa Côte d\u0092Ivoire | Africa Cameroon | Africa Congo, The Democratic Republic of the | Africa . . .We now have alphabetical sorting in two columns.
Filtering Query Results in PostgreSQL
We have learned how to select only certain information from a table by specifying the columns we want, but Postgres provides more fine-grained filtering mechanisms.
We can filter results by including a "where" clause. A where clause is followed by a description of the results we would like to receive.
For example, if we wanted to select all of the cities in the United States, we could tell Postgres to return the name of cities where the three digit country code is "USA":
SELECT name FROM city WHERE countrycode = 'USA';name ------------------------- New York Los Angeles Chicago Houston Philadelphia Phoenix San Diego Dallas San Antonio . . .String values, like USA above, must be placed in single-quotations to be interpreted correctly by Postgres.
The previous query used "=" to compare whether the column value is an exact match for the value given on the right of the expression. We can search more flexibly though with the "like" comparison operator.
The like operator can use "_" as a wildcard to match a single character and "%" as a wildcard that matches zero or more characters.
We can also combine filtering terms with either "and" or "or". Let's combine some filtering to find cities in the US that have names starting with "N":
SELECT name FROM city WHERE countrycode = 'USA' AND name LIKE 'N%';name -------------------- New York Nashville-Davidson New Orleans Newark Norfolk Newport News Naperville New Haven North Las Vegas Norwalk New Bedford Norman (12 rows)We can, of course, sort these results just like with regular, unfiltered select data.SELECT name FROM city WHERE countrycode = 'USA' AND name LIKE 'N%' ORDER BY name;name -------------------- Naperville Nashville-Davidson Newark New Bedford New Haven New Orleans Newport News New York Norfolk Norman North Las Vegas Norwalk (12 rows)Advanced Select Operations in PostgreSQL
We are going to examine some more complex queries. Consider the following:
SELECT country.name AS country,city.name AS capital,continent FROM country JOIN city ON country.capital = city.id ORDER BY continent,country;country | capital | continent ---------------------------------------+-----------------------------------+--------------- Algeria | Alger | Africa Angola | Luanda | Africa Benin | Porto-Novo | Africa Botswana | Gaborone | Africa Burkina Faso | Ouagadougou | Africa Burundi | Bujumbura | Africa Cameroon | Yaoundé | Africa Cape Verde | Praia | Africa Central African Republic | Bangui | Africa Chad | N´Djaména | Africa . . .This query has a few different parts. Let's start at the end and work backwards.
The "order by" section of the statement (ORDER BY continent,country) should be familiar.
This section of the statement tells Postgres to sort based on continent first, and then sort the entries with matching continent values by the country column.
To explain the next portion, the table specification, we will learn about table joins.
Selecting Data From Multiple Tables in PostgreSQL with Join
Postgres allows you to select data from different, related tables using the "join" clause. Tables are related if they each have a column that can that refers to the same data.
In our example database, our "country" and "city" table share some data. We can see that the "country" table references the "city" table by typing:
\d country. . . . . . Foreign-key constraints: "country_capital_fkey" FOREIGN KEY (capital) REFERENCES city(id) . . . . . .This statement tells us that the "capital" column within the "country" table is a reference to the "id" column within the "city" table. This means that we can almost treat these two tables as one giant table by matching up the values in those columns.
In our query, the table selection reads "FROM country JOIN city ON country.capital = city.id".
In this statement, we are telling Postgres to return information from both tables. The "join" statement specifies the default join, which is also called an "inner join".
An inner join will return the information that is present in both tables. For example, if we were matching columns that were not explicitly related as foreign-keys, we could run into a situation where one table had values that weren't matched in the other table. These would not be returned using a regular join.
The section after the "on" keyword specifies the columns that the tables share so that Postgres knows how the data is related. This information is given by specifying:
table_name.column_nameIn our case, we are selecting records that have matching values in both tables, where the capital column of the country table should be compared to the id column of the city table.
Naming Selection Criteria For Table Joins in PostgreSQL
We are now to the beginning of our query statement. The part the selects the columns. This part should be fairly simple to decipher based on the last section.
We now have to name the table that the columns are in if we are trying to select a column name that is present in both tables.
For instance, we've selected the "name" column from both the country table and the city table. If we were to leave off the "table_name." portion of the selection, the match would be ambiguous and Postgres would not know which data to return.
We get around this problem by being explicit about which table to select from when there are naming collisions. It is sometimes helpful to name the tables regardless of whether the names are unique in order to maintain readability.,/p>
Conclusion
You should now have a basic idea of how to formulate queries. This gives you the ability to return specific data from various sources. This is helpful for building or using applications and interactive webpages around this technology.
我们现在可以登录到我们新创建的环境：
psql worlddb
如何在PostgreSQL中显示数据
在开始之前，让我们了解刚刚导入的数据类型。 要查看表的列表，我们可以使用以下命令：
\d+
List of relations Schema | Name | Type | Owner | Size | Description --------+-----------------+-------+----------+--------+------------- public | city | table | postgres | 264 kB | public | country | table | postgres | 48 kB | public | countrylanguage | table | postgres | 56 kB | (3 rows)
我们这里有三个表。 如果我们想查看组成“城市”表的列，我们可以发出以下命令：
\d city
Table "public.city"
Column | Type | Modifiers
-------------+--------------+-----------
id | integer | not null
name | text | not null
countrycode | character(3) | not null
district | text | not null
population | integer | not null
Indexes:
"city_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "country" CONSTRAINT "country_capital_fkey" FOREIGN KEY (capital) REFERENCES city(id)
我们可以看到有关每个列的信息，以及此表与其他数据集的关系。
如何在PostgreSQL中使用Select查询数据
我们使用“select”语句从Postgres查询（请求）信息。 这些语句使用以下一般语法：
SELECT columns_to_return FROM table_name;
例如，如果我们发出“\ d country”，我们可以看到“country”表有很多列。 我们可以创建一个查询，其中列出了国家/地区及其所在的大陆的名称：
SELECT name,continent FROM country;
name | continent ----------------------------------------------+--------------- Afghanistan | Asia Netherlands | Europe Netherlands Antilles | North America Albania | Europe Algeria | Africa American Samoa | Oceania Andorra | Europe . . .
要查看特定表中的所有列，我们可以使用星号（*）通配符。 这意味着“匹配每个可能性”，因此，将返回每一列。
SELECT * FROM city;
id | name | countrycode | district | population
------+-----------------------------------+-------------+-------------------------------+------------
1 | Kabul | AFG | Kabol | 1780000
2 | Qandahar | AFG | Qandahar | 237500
3 | Herat | AFG | Herat | 186800
4 | Mazar-e-Sharif | AFG | Balkh | 127800
5 | Amsterdam | NLD | Noord-Holland | 731200
6 | Rotterdam | NLD | Zuid-Holland | 593321
7 | Haag | NLD | Zuid-Holland | 440900
. . .
在这里，我们看到“城市”表的完整。
在PostgreSQL中排序查询结果
您可以使用“order by”子句来组织查询的结果。 这允许您为返回的数据指定排序顺序。
“order by”子句位于正常的select语句之后。 这是一般的语法：
SELECT columns FROM table ORDER BY column_names [ ASC | DESC ];
如果我们想从国家表中选择国家和大陆，然后由大陆订购，我们可以给出以下内容：
SELECT name,continent FROM country ORDER BY continent;
name | continent ----------------------------------------------+--------------- Algeria | Africa Western Sahara | Africa Madagascar | Africa Uganda | Africa Malawi | Africa Mali | Africa Morocco | Africa Côte d\u0092Ivoire | Africa . . .
可以看到，默认情况下，order语句按升序组织数据。 这意味着它从字母组织的开始和数字搜索的最小数字。
如果我们想反转排序顺序，我们可以在“order by”列声明后面输入“desc”：
SELECT name,continent FROM country ORDER BY continent DESC;
name | continent ----------------------------------------------+--------------- Paraguay | South America Bolivia | South America Brazil | South America Falkland Islands | South America Argentina | South America Venezuela | South America Guyana | South America Chile | South America . . .
我们还可以选择按多个列进行排序。 我们可以有一个主排序字段，然后是多个记录在主排序字段中具有相同值时使用的其他排序字段。
例如，我们可以按大陆排序，然后按国家排序，以获得每个大陆国家记录的按字母顺序排列的列表：
SELECT name,continent FROM country ORDER BY continent,name;
name | continent ----------------------------------------------+--------------- Angola | Africa Burundi | Africa Benin | Africa Burkina Faso | Africa Botswana | Africa Central African Republic | Africa Côte d\u0092Ivoire | Africa Cameroon | Africa Congo, The Democratic Republic of the | Africa . . .
我们现在有两列字母排序。
过滤PostgreSQL中的查询结果
我们已经学习了如何通过指定我们想要的列来从表中选择某些信息，但Postgres提供了更细粒度的过滤机制。
我们可以通过包含一个“where”子句来过滤结果。 一个where子句后面是我们想要接收的结果的描述。
例如，如果我们想选择美国的所有城市，我们可以告诉Postgres返回三位数国家代码为“美国”的城市名称：
SELECT name FROM city WHERE countrycode = 'USA';
name ------------------------- New York Los Angeles Chicago Houston Philadelphia Phoenix San Diego Dallas San Antonio . . .
字符串值，如上面的美国，必须放在单引号，以便由Postgres正确解释。
上一个查询使用“=”来比较列值是否与表达式右侧给定的值完全匹配。 我们可以通过“like”比较运算符更灵活地搜索。
like操作符可以使用“_”作为通配符来匹配单个字符，“％”可以作为匹配零个或多个字符的通配符。
我们还可以将过滤术语与“和”或“或”组合。 让我们结合一些过滤，找出美国以“N”开头的城市：
SELECT name FROM city WHERE countrycode = 'USA' AND name LIKE 'N%';
name -------------------- New York Nashville-Davidson New Orleans Newark Norfolk Newport News Naperville New Haven North Las Vegas Norwalk New Bedford Norman (12 rows)当然，我们可以对这些结果进行排序，就像使用常规的未过滤的选择数据一样。
SELECT name FROM city WHERE countrycode = 'USA' AND name LIKE 'N%' ORDER BY name;
name -------------------- Naperville Nashville-Davidson Newark New Bedford New Haven New Orleans Newport News New York Norfolk Norman North Las Vegas Norwalk (12 rows)
高级选择PostgreSQL中的操作
我们将检查一些更复杂的查询。 考虑以下：
SELECT country.name AS country,city.name AS capital,continent FROM country JOIN city ON country.capital = city.id ORDER BY continent,country;
country | capital | continent ---------------------------------------+-----------------------------------+--------------- Algeria | Alger | Africa Angola | Luanda | Africa Benin | Porto-Novo | Africa Botswana | Gaborone | Africa Burkina Faso | Ouagadougou | Africa Burundi | Bujumbura | Africa Cameroon | Yaoundé | Africa Cape Verde | Praia | Africa Central African Republic | Bangui | Africa Chad | N´Djaména | Africa . . .
此查询有几个不同的部分。 让我们从结束开始，向后工作。
语句的“order by”部分（ORDER BY大陆，国家）应该是熟悉的。
语句的这一部分告诉Postgres首先基于大陆排序，然后按国家列对具有匹配大陆值的条目排序。
为了解释下一部分，表的规范，我们将了解表连接。
在PostgreSQL中使用Join从多个表中选择数据
Postgres允许您使用“join”子句从不同的相关表中选择数据。 如果表各自具有可引用相同数据的列，则它们是相关的。
在我们的示例数据库中，我们的“国家”和“城市”表共享一些数据。 我们可以看到，“国家”表通过键入以下内容引用“城市”表：
\d country
. . .
. . .
Foreign-key constraints:
"country_capital_fkey" FOREIGN KEY (capital) REFERENCES city(id)
. . .
. . .
此语句告诉我们，“国家”表中的“资本”列是对“城市”表中的“id”列的引用。 这意味着我们几乎可以通过匹配这些列中的值来将这两个表视为一个巨型表。
在我们的查询中，表选择显示为“FROM country JOIN city ON country.capital = city.id”。
在这个语句中，我们告诉Postgres从这两个表返回信息。 “join”语句指定默认联接，也称为“内部联接”。
内连接将返回两个表中存在的信息。 例如，如果我们匹配没有显式相关的列作为外键，我们可能遇到一个表的值在另一个表中不匹配的情况。 这些不会使用常规连接返回。
“on”关键字后面的部分指定表共享的列，以便Postgres知道数据的相关性。 此信息通过指定：
table_name.column_name
在我们的示例中，我们选择在两个表中具有匹配值的记录，其中国家表的资本列应与城市表的id列进行比较。
PostgreSQL中的表连接的命名选择标准
我们现在到我们的查询语句的开始。 部分选择列。 这部分应该相当简单，基于最后一节进行解密。
我们现在必须命名列所在的表，如果我们试图选择两个表中存在的列名。
例如，我们从国家表和城市表中选择了“名称”列。 如果我们离开了“table_name”。 部分的选择，匹配将是不明确的，Postgres不知道返回哪些数据。
我们通过显式地说明当存在命名冲突时从哪个表中选择来解决这个问题。 无论名称是否唯一，为了保持可读性，命名表有时会很有帮助。/ p>
结论
您现在应该具有如何制定查询的基本概念。 这使您能够从各种来源返回特定数据。 这有助于构建或使用此技术周围的应用程序和交互式网页。
