文本Literals

字面量(也称为常量)表示固定数据值。A literal (also known as a constant) represents a fixed data value.

字符串文本String literals

字符串字面量用于指定字符串值。A string literal is used to specify a character string value.

语法Syntax

'char [ ... ]' | "char [ ... ]"

参数Parameters

  • charchar

    字符集中的一个字符。One character from the character set. 使用 \ 来转义特殊字符(例如 '\)。Use \ to escape special characters (e.g., ' or \).

示例Examples

SELECT 'Hello, World!' AS col;
+-------------+
|          col|
+-------------+
|Hello, World!|
+-------------+
```sql
SELECT "SPARK SQL" AS col;
+---------+
|      col|
+---------+
|Spark SQL|
+---------+
SELECT 'it\'s $10.' AS col;
+---------+
|      col|
+---------+
|It's $10.|
+---------+

二进制文本Binary literals

二进制字面量用于指定字节序列值。A binary literal is used to specify a byte sequence value.

语法Syntax

X { 'num [ ... ]' | "num [ ... ]" }

参数Parameters

  • numnum

    0 到 F 之间的任何十六进制数字。Any hexadecimal number from 0 to F.

示例Examples

SELECT X'123456' AS col;
+----------+
|       col|
+----------+
|[12 34 56]|
+----------+

null 文本Null literals

null 字面量用于指定 null 值。A null literal is used to specify a null value.

语法Syntax

NULL

示例Examples

SELECT NULL AS col;
+----+
| col|
+----+
|NULL|
+----+

布尔值文字Boolean literals

布尔值字面量用于指定布尔值。A Boolean literal is used to specify a Boolean value.

语法Syntax

TRUE | FALSE

示例Examples

SELECT TRUE AS col;
+----+
| col|
+----+
|true|
+----+

数值文字Numeric literals

数字字面量用于指定固定值或浮点数。A numeric literal is used to specify a fixed or floating-point number.

整数文本Integer literals

语法Syntax

[ + | - ] digit [ ... ] [ L | S | Y ]

参数Parameters

  • 数字:0 到 9 的任意数字。digit: Any numeral from 0 to 9.
  • L:不区分大小写,指示 BIGINT,一个 8 个字节的带符号整数。L: Case insensitive, indicates BIGINT, which is an 8-byte signed integer number.
  • S:不区分大小写,指示 SMALLINT,一个 2 个字节的带符号整数。S: Case insensitive, indicates SMALLINT, which is a 2-byte signed integer number.
  • Y:不区分大小写,指示 TINYINT,一个 1 个字节的带符号整数。Y: Case insensitive, indicates TINYINT, which is a 1-byte signed integer number.
  • 默认值(无后缀) :指示 4 个字节的带符号整数。default (no postfix): Indicates a 4-byte signed integer number.

示例Examples

SELECT -2147483648 AS col;
+-----------+
|        col|
+-----------+
|-2147483648|
+-----------+

SELECT 9223372036854775807l AS col;
+-------------------+
|                col|
+-------------------+
|9223372036854775807|
+-------------------+

SELECT -32Y AS col;
+---+
|col|
+---+
|-32|
+---+

SELECT 482S AS col;
+---+
|col|
+---+
|482|
+---+

小数型字面量Fractional literals

语法Syntax

  • 十进制字面量:Decimal literals:

decimal_digits { [ BD ] | [ exponent BD ] } | digit [ ... ] [ exponent ] BD

  • 双精度字面量:Double literals:

decimal_digits { D | exponent [ D ] } | digit [ ... ] { exponent [ D ] | [ exponent ] D }

  • 浮点数字面量:Float literals:

decimal_digits { F | exponent [ F ] } | digit [ ... ] { exponent [ F ] | [ exponent ] F }

其中 decimal_digits 定义为 [ + | - ] { digit [ ... ] . [ digit [ ... ] ] | . digit [ ... ] }where decimal_digits is defined as [ + | - ] { digit [ ... ] . [ digit [ ... ] ] | . digit [ ... ] }

exponent 定义为 E [ + | - ] digit [ ... ]and exponent is defined as E [ + | - ] digit [ ... ]

参数Parameters

  • 数字:0 到 9 的任意数字。digit: Any numeral from 0 to 9.
  • D:不区分大小写,指示 DOUBLE,一个 8 个字节的双精度浮点数。D: Case insensitive, indicates DOUBLE, which is an 8-byte double-precision floating point number.
  • F:不区分大小写,指示 FLOAT,一个 4 个字节的单精度浮点数。F: Case insensitive, indicates FLOAT, which is a 4-byte single-precision floating point number.
  • BD:不区分大小写,指示 DECIMAL,其总位数为精度,小数点右边的位数为小数位数。BD: Case insensitive, indicates DECIMAL, with the total number of digits as precision and the number of digits to right of decimal point as scale.

示例Examples

SELECT 12.578 AS col;
+------+
|   col|
+------+
|12.578|
+------+

SELECT -0.1234567 AS col;
+----------+
|       col|
+----------+
|-0.1234567|
+----------+

SELECT -.1234567 AS col;
+----------+
|       col|
+----------+
|-0.1234567|
+----------+

SELECT 123. AS col;
+---+
|col|
+---+
|123|
+---+

SELECT 123.BD AS col;
+---+
|col|
+---+
|123|
+---+

SELECT 5E2 AS col;
+-----+
|  col|
+-----+
|500.0|
+-----+

SELECT 5D AS col;
+---+
|col|
+---+
|5.0|
+---+

SELECT -5BD AS col;
+---+
|col|
+---+
| -5|
+---+

SELECT 12.578e-2d AS col;
+-------+
|    col|
+-------+
|0.12578|
+-------+

SELECT -.1234567E+2BD AS col;
+---------+
|      col|
+---------+
|-12.34567|
+---------+

SELECT +3.e+3 AS col;
+------+
|   col|
+------+
|3000.0|
+------+

SELECT -3.E-3D AS col;
+------+
|   col|
+------+
|-0.003|
+------+

日期/时间字面量Datetime literals

日期/时间字面量用于指定日期/时间值。A Datetime literal is used to specify a datetime value.

日期字面量Date literal

语法Syntax

DATE { 'yyyy' |
       'yyyy-[m]m' |
       'yyyy-[m]m-[d]d' |
       'yyyy-[m]m-[d]d[T]' }

备注

如果未指定月或日,则默认为 01defaults to 01 if month or day is not specified.

示例Examples

SELECT DATE '1997' AS col;
+----------+
|       col|
+----------+
|1997-01-01|
+----------+

SELECT DATE '1997-01' AS col;
+----------+
|       col|
+----------+
|1997-01-01|
+----------+

SELECT DATE '2011-11-11' AS col;
+----------+
|       col|
+----------+
|2011-11-11|
+----------+

时间戳字面量Timestamp literals

语法Syntax

TIMESTAMP { 'yyyy' |
            'yyyy-[m]m' |
            'yyyy-[m]m-[d]d' |
            'yyyy-[m]m-[d]d ' |
            'yyyy-[m]m-[d]d[T][h]h[:]' |
            'yyyy-[m]m-[d]d[T][h]h:[m]m[:]' |
            'yyyy-[m]m-[d]d[T][h]h:[m]m:[s]s[.]' |
            'yyyy-[m]m-[d]d[T][h]h:[m]m:[s]s.[ms][ms][ms][us][us][us][zone_id]'}

备注

如果未指定小时、分钟或秒,则默认为 00defaults to 00 if hour, minute or second is not specified.

zone_id 应为以下形式之一:zone_id should have one of the forms:

  • Z - 祖鲁时区 UTC+0Z - Zulu time zone UTC+0
  • +|-[h]h:[m]m
  • 具有 UTC+、UTC-、GMT+、GMT-、UT+ 或 UT- 前缀的 ID,和具有以下格式的后缀:An id with one of the prefixes UTC+, UTC-, GMT+, GMT-, UT+ or UT-, and a suffix in the formats:
    • +|-h[h]
    • +|-hh[:]mm
    • +|-hh:mm:ss
    • +|-hhmmss
  • 格式为 area/city 的基于区域的区域 ID,如 Europe/ParisRegion-based zone IDs in the form area/city, such as Europe/Paris

备注

如果未指定 zone_id,则默认为会话本地时区(通过 spark.sql.session.timeZone 进行设置)。defaults to the session local timezone (set via spark.sql.session.timeZone) if zone_id is not specified.

示例Examples

SELECT TIMESTAMP '1997-01-31 09:26:56.123' AS col;
+-----------------------+
|                    col|
+-----------------------+
|1997-01-31 09:26:56.123|
+-----------------------+

SELECT TIMESTAMP '1997-01-31 09:26:56.66666666UTC+08:00' AS col;
+--------------------------+
|                      col |
+--------------------------+
|1997-01-30 17:26:56.666666|
+--------------------------+

SELECT TIMESTAMP '1997-01' AS col;
+-------------------+
|                col|
+-------------------+
|1997-01-01 00:00:00|
+-------------------+

间隔字面量Interval literals

间隔字面量用于指定固定时间段。An interval literal is used to specify a fixed period of time.

INTERVAL interval_value interval_unit [ interval_value interval_unit ... ] |
INTERVAL 'interval_value interval_unit [ interval_value interval_unit ... ]' |
INTERVAL interval_string_value interval_unit TO interval_unit

参数Parameters

  • interval_valueinterval_value

    语法:Syntax:

    [ + | - ] number_value | ‘[ + | - ] number_value’[ + | - ] number_value | ‘[ + | - ] number_value’

  • interval_string_valueinterval_string_value

    年月/日时间间隔字符串。year-month/day-time interval string.

  • interval_unitinterval_unit

    语法:Syntax:

    YEAR[S] | MONTH[S] | WEEK[S] | DAY[S] | HOUR[S] | MINUTE[S] | SECOND[S] | MILLISECOND[S] | MICROSECOND[S]YEAR[S] | MONTH[S] | WEEK[S] | DAY[S] | HOUR[S] | MINUTE[S] | SECOND[S] | MILLISECOND[S] | MICROSECOND[S]

示例Examples

SELECT INTERVAL 3 YEAR AS col;
+-------+
|    col|
+-------+
|3 years|
+-------+

SELECT INTERVAL -2 HOUR '3' MINUTE AS col;
+--------------------+
|                 col|
+--------------------+
|-1 hours -57 minutes|
+--------------------+

SELECT INTERVAL '1 YEAR 2 DAYS 3 HOURS';
+----------------------+
|                   col|
+----------------------+
|1 years 2 days 3 hours|
+----------------------+

SELECT INTERVAL 1 YEARS 2 MONTH 3 WEEK 4 DAYS 5 HOUR 6 MINUTES 7 SECOND 8
    MILLISECOND 9 MICROSECONDS AS col;
+-----------------------------------------------------------+
|                                                        col|
+-----------------------------------------------------------+
|1 years 2 months 25 days 5 hours 6 minutes 7.008009 seconds|
+-----------------------------------------------------------+

SELECT INTERVAL '2-3' YEAR TO MONTH AS col;
+----------------+
|             col|
+----------------+
|2 years 3 months|
+----------------+

SELECT INTERVAL '20 15:40:32.99899999' DAY TO SECOND AS col;
+---------------------------------------------+
|                                          col|
+---------------------------------------------+
|20 days 15 hours 40 minutes 32.998999 seconds|
+---------------------------------------------+