Sql 简明教程

SQL - Expressions

What is SQL Expression?

SQL 表达式是由一个或多个值、运算符和 SQL 函数组成的组合，它们都将计算出一个值。这些 SQL 表达式(们)类似于公式，且它们使用查询语言编写。您还可以使用它们来针对数据库查询一组特定的数据。

An SQL expression is a combination of one or more values, operators and SQL functions that are all evaluated to a value. These SQL EXPRESSION(s) are like formulae and they are written in query language. You can also use them to query the database for a specific set of data.

表达式在 SQL 查询的 WHERE 子句中使用。正如您可能已经知道，WHERE 子句指定为了筛选数据库表中的记录而需要满足的一个条件。此条件由单个或多个表达式组成。这些表达式进一步分为三类 -

Expressions are used in WHERE clause of an SQL query. As you might have already known, a WHERE clause specifies a condition that needs to be satisfied for the purpose of filtering records from a database table. This condition is comprised of either single or multiple expressions. These expressions are further classified into three types −

Boolean Expressions
Numeric Expressions
Date and time Expressions

让我们在本章中详细讨论这三个表达式中的每一个。

Let us discuss each of these expressions in detail further in this chapter.

Syntax

考虑以下包含一些表达式的 SELECT 语句的基本语法 -

Consider the basic syntax of the SELECT statement containing some expressions as follows −

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

SQL Boolean Expressions

SQL 布尔表达式是仅返回布尔数据类型的 SQL 表达式。这些表达式可以是两种类型 -

SQL Boolean Expressions are SQL expressions that return only Boolean Datatype as a result. These expressions can be of two types −

Boolean Expressions that check for equality of two values using SQL comparison operators. Here, equality of these values is a condition.
Boolean Expressions can also contain one value paired with an SQL logical operator. In this case, the logic specified acts like a condition.

它们返回 TRUE、FALSE 或 UNKNOWN 作为结果。如果满足条件，这些表达式返回 TRUE；否则返回 FALSE。当表达式中的任一操作数为 NULL 值时，将返回 UNKNOWN。

They return either TRUE, FALSE or UNKNOWN as the result. If the condition is met, these expressions return TRUE; and FALSE otherwise. UNKNOWN is returned when either of the operands in the expression is a NULL value.

Syntax

以下是布尔表达式的语法 -

Following is the syntax of Boolean Expression −

SELECT column1, column2, columnN
FROM table_name
WHERE BOOLEAN EXPRESSION;

Example

考虑包含以下记录的 CUSTOMERS 表：

Consider the CUSTOMERS table having the following records −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

以下查询是一个简单的示例，展示了如何使用 SQL 布尔表达式 -

The following query is a simple example showing the usage of an SQL Boolean Expression −

SELECT * FROM CUSTOMERS WHERE SALARY = 10000;

Output

输出将显示为：

The output will be displayed as −

NAME

AGE

ADDRESS

SALARY

Muffy

Indore

10000.00

SQL Numeric Expressions

SQL 数值表达式由两个操作数和一个 SQL 算术运算符组成。这些表达式用于在任何查询中执行任何数学运算。因此，操作数必须始终是数字，返回值也将始终是数字。

SQL Numeric expressions are comprised of two operands and an SQL Arithmetic Operator. These expressions are used to perform any mathematical operation in any query. Hence, the operands must always be numerals and the return value will always be a number as well.

Syntax

以下是语法 -

Following is the syntax −

SELECT numerical_expression as OPERATION_NAME
FROM table_name
WHERE NUMERICAL EXPRESSION ;

此处，numerical_expression 用于数学表达式或任何公式。

Here, the numerical_expression is used for a mathematical expression or any formula.

Example

下面是一个显示 SQL 数值表达式用法的简单示例 −

Following is a simple example showing the usage of SQL Numeric Expressions −

SELECT 15 + 6;

Output

检索的输出表为 −

The output table is retrieved as −

Example

有几个内置函数，如 avg()、sum()、count() 等，用于执行已知的针对表或特定表列的聚合数据计算。

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

SELECT COUNT(*) FROM CUSTOMERS;

Output

输出显示如下 −

The output is displayed as follows −

SQL Date Expressions

SQL 日期表达式用于比较与当前系统日期和时间值相关的日期值。例如，在一家制造公司中，可以使用 WHERE 子句中的日期表达式，根据年对制造的物品进行分类。从一年的第一天到最后一天计数，将检索到每个物品的数量；一旦收集到所需信息，公司就可以将此信息用于他们自己的目的。

SQL Date Expressions are used to compare date related values with current system date and time values. For instance, in a manufacturing company, items manufactured per year can be segregated by using date expressions in a WHERE clause. Counting from the first day of an year to the last day, the count of each item will be retrieved; once the required information is gathered, the company can use this information for their own purposes.

Syntax

以下是语法 -

Following is the syntax −

SELECT column_name(s)
FROM table_name
WHERE DATE EXPRESSION ;

Example

在此示例中，我们尝试使用 CURRENT_TIMESTAMP 简单地检索系统当前时间戳。

In this example we are trying to simply retrieve the current timestamp of the system using CURRENT_TIMESTAMP.

SELECT CURRENT_TIMESTAMP;

Output

输出表显示为 −

The output table is displayed as −

Current_Timestamp

2009-11-12 06:40:23

Example

考虑 ORDERS 表中的以下客户订单记录：

Consider the following Customer Order records in an ORDERS table:

ORDER_ID	CUSTOMER_ID	DATE	ORDER_AMOUNT	ITEM_COUNT
102	3	2009-10-08 00:00:00	3000	4
100	3	2009-10-08 00:00:00	1500	2
101	2	2009-11-20 00:00:00	1560	7
103	4	2008-05-20 00:00:00	2060	3

现在我们来检索 2008 年 6 月 1 日之前的记录：

Now let’s retrieve the records before 1st June, 2008:

SELECT * FROM ORDERS WHERE DATE < '2008/06/01';

Output

输出表显示为 −

The output table is displayed as −

ORDER_ID	CUSTOMER_ID	DATE	ORDER_AMOUNT	ITEM_COUNT
103	4	2008-05-20 00:00:00	2060	3