Postgresql 简明教程

PostgreSQL - Expressions

表达式是一或多个值、运算符和 PostgreSQL 函数的组合,它们的值为一个值。

An expression is a combination of one or more values, operators, and PostgresSQL functions that evaluate to a value.

PostgreSQL 表达式类似于公式,它们用查询语言编写。您还可以使用它来查询特定数据集合的数据库。

PostgreSQL EXPRESSIONS are like formulas and they are written in query language. You can also use to query the database for specific set of data.

Syntax

考虑以下 SELECT 语句的基本语法:

Consider the basic syntax of the SELECT statement as follows −

SELECT column1, column2, columnN
FROM table_name
WHERE [CONDITION | EXPRESSION];

有不同类型的 PostgreSQL 表达式,如下所述 -

There are different types of PostgreSQL expressions, which are mentioned below −

PostgreSQL - Boolean Expressions

PostgreSQL 布尔表达式基于匹配单个值来获取数据。以下是语法 -

PostgreSQL Boolean Expressions fetch the data on the basis of matching single value. Following is the syntax −

SELECT column1, column2, columnN
FROM table_name
WHERE SINGLE VALUE MATCHTING EXPRESSION;

考虑 COMPANY 表具有以下记录:

Consider the table COMPANY having records as follows −

testdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

这里有一个展示 PostgreSQL 布尔表达式用法的简单示例 -

Here is the simple example showing usage of PostgreSQL Boolean Expressions −

testdb=# SELECT * FROM COMPANY WHERE SALARY = 10000;

以上给出的 PostgreSQL 语句将产生以下结果 -

The above given PostgreSQL statement will produce the following result −

 id | name  | age | address  | salary
----+-------+-----+----------+--------
  7 | James |  24 | Houston  |  10000
(1 row)

PostgreSQL - Numeric Expression

这些表达式用于在任何查询中执行任何数学运算。以下为语法:

These expressions are used to perform any mathematical operation in any query. Following is the syntax −

SELECT numerical_expression as  OPERATION_NAME
[FROM table_name WHERE CONDITION] ;

这里 numerical_expression 用于数学表达式或任何公式。以下是一个显示 SQL 数值表达式用法的简单示例 -

Here numerical_expression is used for mathematical expression or any formula. Following is a simple example showing usage of SQL Numeric Expressions −

testdb=# SELECT (15 + 6) AS ADDITION ;

以上给出的 PostgreSQL 语句将产生以下结果 -

The above given PostgreSQL statement will produce the following result −

 addition
----------
       21
(1 row)

可以使用内置函数如 avg()、sum()、count(),针对表或特定表列执行被称为聚合数据计算的操作。

There are several built-in functions like avg(), sum(), count() to perform what is known as aggregate data calculations against a table or a specific table column.

testdb=# SELECT COUNT(*) AS "RECORDS" FROM COMPANY;

以上给出的 PostgreSQL 语句将产生以下结果 -

The above given PostgreSQL statement will produce the following result −

 RECORDS
---------
       7
(1 row)

PostgreSQL - Date Expressions

日期表达式返回当前系统日期和时间值,并且这些表达式在各种数据操作中使用。

Date Expressions return the current system date and time values and these expressions are used in various data manipulations.

testdb=#  SELECT CURRENT_TIMESTAMP;

以上给出的 PostgreSQL 语句将产生以下结果 -

The above given PostgreSQL statement will produce the following result −

              now
-------------------------------
 2013-05-06 14:38:28.078+05:30
(1 row)