Mysql 简明教程

MySQL - Literals

在 MySQL 中， literals 是可用于 SELECT、INSERT、UPDATE 和 DELETE 等 SQL 语句中的固定值（常量）。我们可以使用 SQL 语句中的文字，而无需由变量或表达式表示。

In MySQL, literals are fixed values (constants) that can be used in SQL statements such as SELECT, INSERT, UPDATE, and DELETE. We can use a literal in SQL statements without needing to be represented by a variable or an expression.

以下是某些常见的 MySQL 文字：

Following are some common MySQL literals:

  1. Numeric Literals

  2. String Literals

  3. Boolean Literals

  4. Date and Time Literals

  5. NULL Literals

Numeric Literals

MySQL 数值文字是可表示正数或负数的数值，包括整数和浮点数。

The MySQL numeric literals are numeric values that can represent positive or negative numbers, including both integers and floating-point values.

如果我们未对数值指定任何符号（即正号 (+) 或负号 (-)），则表示正值。

If we do not specify any sign (i.e. positive (+) or negative (-)) to a numeric value, then a positive value is assumed.

让我们通过在 SQL 查询中使用各种数值文字来看一些示例。

Let us see some examples by using various numeric literals in SQL queries.

Example

以下示例显示不带符号的整数文字（默认情况下会考虑正号）

Following example displays an integer literal with no sign (by default positive sign will be considered)

SELECT 100 AS 'numeric literal';

Output

得到的输出如下 -

The output is obtained as follows −

Example

以下示例显示带正符号 (+) 的整数文字 -

Following example displays an integer literal with positive sign (+) −

SELECT -100 AS 'numeric literal';

Output

得到的输出如下 -

The output is obtained as follows −

Example

以下示例显示带负符号 (-) 的整数文字 -

Following example displays an integer literal with negative sign (-) −

SELECT +493 AS 'numeric literal';

Output

得到的输出如下 -

The output is obtained as follows −

Example

以下示例显示一个浮点数文字 -

Following example displays a floating point literal −

SELECT 109e-06 AS 'numeric literal';

Output

得到的输出如下 -

The output is obtained as follows −

Example

以下示例显示一个十进制文字 -

Following example displays a decimal literal −

SELECT 793.200 AS 'numeric literal';

Output

得到的输出如下 -

The output is obtained as follows −

String Literals

MySQL 字符串文字是用单引号 (') 或双引号 (") 引起来的一串字符。

The MySQL string literals are character strings that are enclosed within the single quotes (') or double quotes (").

让我们来看一些在 SQL 查询中以不同方式使用字符串文字的示例。

Let us see some examples where string literals in SQL queries are used in different ways.

Example

在此示例中，我们显示用单引号引起来的一串字符串 -

In this example, we are displaying a string literal enclosed in single quotes −

SELECT 'tutorialspoint' AS 'string literal';

我们可以使用双引号将字符串文字括起来，如下所示 -

We can use double quotes to enclose a string literal as follows −

SELECT "tutorialspoint" AS 'string literal';

Output

在这两种情况下都获得了以下输出 -

Following output is obtained in both cases −

Example

在此示例中，我们显示用单引号括起的带有空格的字符串文字 -

In this example, we are displaying a string literal with spaces enclosed in single quotes −

SELECT 'tutorials point india' AS 'string literal';

我们还可以在双引号中括起该字符串文字（包括空格） -

We can also enclose this string literal (spaces included) in double quotes −

SELECT "tutorials point india" AS 'string literal';

Output

两个查询都获得了以下输出 -

Following output is obtained with both queries −

Boolean Literals

MySQL 布尔文字是求值为 1 或 0 的逻辑值。让我们看一些示例以更好地理解。

The MySQL Boolean literals are logical values that evaluate to either 1 or 0. Let us see some example for a better understanding.

Example

布尔值在 MySQL 中求值为 true 有多种方式。在此，我们使用整数 1 作为布尔文字 -

There are various ways a boolean value is evaluated to true in MySQL. Here, we use the integer 1 as a boolean literal −

SELECT 1 AS 'boolean literal';

我们还可以使用关键字 TRUE 将布尔文字求值为 1。

We can also use the keyword TRUE to evaluate the boolean literal to 1.

SELECT TRUE AS 'boolean literal';

我们还可以使用关键字 TRUE 的小写形式 true，将布尔文字求值为 1。

We can also use the lowercase of the keyword TRUE, as true, to evaluate the boolean literal to 1.

SELECT true AS 'boolean literal';

Output

获得了以下输出 -

Following output is obtained −

Example

类似地，布尔值在 MySQL 中求值为 false 有多种方式。在此，我们使用整数 0 作为布尔文字 -

Similarly, there are multiple ways a boolean value is evaluated to false in MySQL. Here, we use the integer 0 as a boolean literal −

SELECT 0 AS 'boolean literal';

我们还可以使用关键字 FALSE 将布尔文字求值为 0。

We can also use the keyword FALSE to evaluate the boolean literal to 0.

SELECT FALSE AS 'boolean literal';

我们还可以使用关键字 FALSE 的小写形式 false，将布尔文字求值为 0。

We can also use the lowercase of the keyword FALSE, as false, to evaluate the boolean literal to 0.

SELECT false AS 'boolean literal';

Output

获得了以下输出 -

Following output is obtained −

Date and Time Literals

MySQL 日期和时间文字表示日期和时间值。让我们看一些示例，以了解日期和时间值在 MySQL 中以各种方式表示的方式。

The MySQL date and time literals represent date and time values. Let us see examples to understand how date and time values are represented in various ways in MySQL.

Example

在此示例中，我们将显示格式化为“YYYY-MM-DD”的日期文字

In this example, we will display a date literal formatted as 'YYYY-MM-DD'

SELECT '2023-04-20' AS 'Date literal';

Output

获得了以下输出 -

Following output is obtained −

Example

在此示例中，我们将显示格式化为“YYYYMMDD”的日期文字

In this example, we will display a date literal formatted as 'YYYYMMDD'

SELECT '20230420' AS 'Date literal';

Output

获得了以下输出 -

Following output is obtained −

Example

在此示例中，我们将显示格式化为 YYYYMMDD 的日期文字

In this example, we will display a date literal formatted as YYYYMMDD

SELECT 20230420 AS 'Date literal';

Output

获得了以下输出 -

Following output is obtained −

Example

在此示例中，我们将显示格式化为“YY-MM-DD”的日期文字

In this example, we will display a date literal formatted as 'YY-MM-DD'

SELECT '23-04-20' AS 'Date literal';

Output

获得了以下输出 -

Following output is obtained −

Example

在此示例中，我们将显示格式化为“YYMMDD”的日期文字

In this example, we will display a date literal formatted as 'YYMMDD'

SELECT '230420' AS 'Date literal';

Output

获得了以下输出 -

Following output is obtained −

Example

在此示例中，我们将显示以 YYMMDD 格式设置的日期日期字面值

In this example, we will display a date literal formatted as YYMMDD

SELECT 230420 AS 'Date literal';

Output

获得了以下输出 -

Following output is obtained −

Example

在此示例中，我们显示了以“HH:MM:SS”格式设置的时间字面值。

In this example, we are displaying a time literal formatted as 'HH:MM:SS'.

SELECT '10:45:50' AS 'Time literal';

Output

获得了以下输出 -

Following output is obtained −

Example

在此示例中，我们显示了以 HHMMSS 格式设置的时间字面值。

In this example, we are displaying a time literal formatted as HHMMSS.

SELECT 104550 AS 'Time literal';

Output

获得了以下输出 -

Following output is obtained −

Example

在此示例中，我们将显示以“HH:MM”格式设置的时间字面值。

In this example, we are displaying a time literal formatted as 'HH:MM'.

SELECT '10:45' AS 'Time literal';

Output

获得了以下输出 -

Following output is obtained −

Example

在此示例中，我们显示了以“MMSS”格式设置的时间字面值。

In this example, we are displaying a time literal formatted as 'MMSS'.

SELECT '4510' AS 'Time literal';

Output

获得了以下输出 -

Following output is obtained −

Example

在此示例中，我们正在显示以“SS”格式设置的时间字面值。

In this example, we are displaying a time literal formatted as 'SS'.

SELECT '10' AS 'Time literal';

在这里，让我们显示以 SS 格式设置的时间字面值。

Here, let us display a time literal formatted as SS.

SELECT 10 AS 'Time literal';

Output

获得了以下输出 -

Following output is obtained −

Example

在此示例中，我们显示了以“D HH:MM:SS”格式设置的时间字面值，其中 D 可为 0 到 34 之间的日期值。

In this example, we are displaying a time literal formatted as 'D HH:MM:SS' where D can be a day value between 0 and 34.

SELECT '4 09:30:12' AS 'Time literal';

Output

获得了以下输出 -

Following output is obtained −

Example

在此示例中，我们显示了以“D HH:MM”格式设置的时间字面值，其中 D 可为 0 到 34 之间的日期值。

In this example, we are displaying a time literal formatted as 'D HH:MM' where D can be a day value between 0 and 34.

SELECT '4 09:30' AS 'Time literal';

Output

获得了以下输出 -

Following output is obtained −

Example

在此示例中，我们显示了以“D HH”格式设置的时间字面值，其中 D 可为 0 到 34 之间的日期值。

In this example, we are displaying a time literal formatted as 'D HH' where D can be a day value between 0 and 34.

SELECT '4 09' AS 'Time literal';

Output

获得了以下输出 -

Following output is obtained −

Example

在此示例中，我们显示了以“YYYY-MM-DD HH:MM:SS”格式设置的 Datetime 字面值。

In this example, we are displaying a Datetime literal formatted as 'YYYY-MM-DD HH:MM:SS'.

SELECT '2023-04-20 09:45:10' AS 'datetime literal';

Output

获得了以下输出 -

Following output is obtained −

Example

在此示例中，我们显示了以“YYYYMMDDHHMMSS”格式设置的 Datetime 字面值。

In this example, we are displaying a Datetime literal formatted as 'YYYYMMDDHHMMSS'.

SELECT '20230420094510' AS 'datetime literal';

现在，我们显示了以 YYYYMMDDHHMMSS 格式设置的 Datetime 字面值。

Now, we are displaying a Datetime literal formatted as YYYYMMDDHHMMSS.

SELECT 20230420094510 AS 'datetime literal';

Output

两个查询会产生以下相同的输出 −

Both queries produce the same output as follows −

Example

在此示例中，我们显示了以“YY-MM-DD HH:MM:SS”格式设置的 Datetime 字面值。

In this example, we are displaying a Datetime literal formatted as 'YY-MM-DD HH:MM:SS'.

SELECT '23-04-20 09:45:10' AS 'datetime literal';

Output

获得了以下输出 -

Following output is obtained −

Example

在此示例中，我们显示了以“YYMMDDHHMMSS”格式设置的 Datetime 字面值。

In this example, we are displaying a Datetime literal formatted as 'YYMMDDHHMMSS'.

SELECT '230420094510' AS 'datetime literal';

在这里，我们显示了以 YYMMDDHHMMSS 格式设置的 Datetime 字面值。

Here, we are displaying a Datetime literal formatted as YYMMDDHHMMSS.

SELECT 230420094510 AS 'datetime literal';

Output

两个查询都会给出以下相同的输出 −

Both queries give the same following output −

Null Literals

MySQL Null 字面值表示不存在值。它不区分大小写。

The MySQL Null literals represents the absence of a value. It is case in-sensitive.

Example

下面是一些有效的 NULL literals − 的示例

Following are some examples of valid NULL literals −

SELECT NULL AS 'NULL literals';

小写 −

In lowercase −

SELECT null AS 'NULL literals';

Output

获得了以下输出 -

Following output is obtained −

Client Program

我们还可以在 MySQL 数据库中使用客户端程序使用文字。

We can also use Literals in a MySQL database using a Client Program.

Syntax

Example

以下是这些程序 −

Following are the programs −