SAP HANA 数据库 SQL 参考手册

需积分: 10 152 浏览量更新于2024-07-21 收藏 817KB PDF 举报

SAP HANA 数据库 SQL 参考手册 SAP HANA 是一种基于内存的关系数据库管理系统，旨在提供高速的数据处理和分析能力。下面是对 SAP HANA 数据库 SQL 参考手册的总结： **Target Audience** 本手册面向以下几类人群： * Consultants：咨询顾问，需要了解 SAP HANA 数据库的架构和实现细节，以便更好地设计和实施解决方案。 * Administrators：数据库管理员，需要了解 SAP HANA 数据库的管理和维护方法，以确保数据库的稳定运行。 * SAP Hardware Partner：SAP 硬件合作伙伴，需要了解 SAP HANA 数据库的硬件需求和优化方法，以便更好地支持客户。 * Others：其他人群，可能是开发人员、数据分析师、业务用户等，需要了解 SAP HANA 数据库的基本概念和操作方法。 **Copyright Information** 本手册的所有权归 SAP AG 所有，未经许可，不得复制、传输或用于任何目的。 **Software Components** SAP HANA 数据库可能包含其他软件供应商的专有软件组件，例如 Microsoft、IBM 等。 ** Trademarks** 本手册中提到的商标和注册商标归属各自的所有者，例如 Microsoft、IBM 等。 **Database Concepts** SAP HANA 数据库支持多种数据库概念，包括： * 数据库 schema：定义数据库的结构和组织方式。 * 数据表：用于存储和管理数据的基本结构单元。 * 索引：用于提高数据检索速度的数据结构。 * 视图：用于提供数据的逻辑视图。 * 存储过程：用于实现复杂的业务逻辑。 **SQL Language** SAP HANA 数据库支持标准 SQL 语言，包括： * SELECT 语句：用于检索数据。 * INSERT 语句：用于插入数据。 * UPDATE 语句：用于更新数据。 * DELETE 语句：用于删除数据。 * CREATE 语句：用于创建数据库对象。 **Database Administration** SAP HANA 数据库管理员需要了解的管理和维护方法，包括： * 数据库备份和恢复：用于确保数据的安全和可用性。 * 数据库性能优化：用于提高数据库的查询速度和响应能力。 * 数据库安全性：用于保护数据库免受未经授权的访问和恶意攻击。 **Hardware Requirements** SAP HANA 数据库需要特定的硬件配置，包括： * 内存：用于存储数据和程序。 * CPU：用于处理数据和执行查询。 * 存储设备：用于存储数据和程序。 **Conclusion** SAP HANA 数据库是基于内存的关系数据库管理系统，旨在提供高速的数据处理和分析能力。本手册提供了 SAP HANA 数据库的基本概念、数据库概念、SQL 语言、数据库管理和维护方法等内容，为使用 SAP HANA 数据库的用户提供了有价值的参考资料。

INTEGER

The INTEGER data type stores a 32-bit signed integer. The minimum value is -2,147,483,648 and the

maximum value is 2,147,483,647 for INTEGER.

BIGINT

The BIGINT data type stores a 64-bit signed integer. The minimum value is -

9,223,372,036,854,775,808 and the maximum value is 9,223,372,036,854,775,807 for BIGINT.

DECIMAL(precision, scale) or DEC(p,s)

The DECIMAL(p, s) data type specifies a fixed-point decimal number with precision p and scale s. The

precision is the total number of significant digits and can range from 1 to 34.

The scale is the number of digits from the decimal point to the least significant digit and can range

from -6,111 to 6,176, which means scale specifies the range of the exponent in the decimal number

from 10

-6111

to 10

6176

. If scale is not specified, it defaults to 0. Scale is positive when the number has

significant digits to the right of the decimal point and negative when the number has significant digits

to the left of the decimal point.

Examples: 0.0000001234 (1234 x 10-10) has the precision 4 and the scale 10. 1.0000001234

(10000001234 x 10-10) has the precision 11 and scale 10. 1234000000 (1234x106) has the precision 4

and scale -6.

When precision and scale are not specified, DECIMAL becomes a floating-point decimal number. In

this case, precision and scale can vary within the range described above, 1~34 for precision and -

6,111~6,176 for scale depending on the stored value.

SMALLDECIMAL

The SMALLDECIMAL is a floating-point decimal number. The precision and scale can vary within the

range, 1~16 for precision and -369~368 for scale depending on the stored value. SMALLDECIMAL is

supported only on column store.

DECIMAL and SMALLDECIMAL are floating-point types. For instance, a decimal column can store any

of 3.14, 3.1415, 3.141592 whilst maintaining their precision.

DECIMAL(p, s) is the SQL standard notation for fixed-point decimal. For instance, 3.14, 3.1415,

3.141592 are stored in a decimal(5, 4) column as 3.1400, 3.1415, 3.1416, respectively keeping the

specified precision(5) and scale(4).

REAL

The REAL data type specifies a single-precision 32-bit floating-point number.

DOUBLE

The DOUBLE data type specifies a single-precision 64-bit floating-point number. The minimum value is

-1.79769 x 10308 and the maximum value is 1.79769x10308 . The smallest positive DOUBLE value is

2.2207x10-308 and the largest negative DOUBLE value is -2.2207x10-308.

FLOAT(n)

The FLOAT(n) data type specifies a 32-bit or 64-bit real number, where n specifies the number of

significant bits and can range between 1 and 53.

When you use the FLOAT(n) data type, if n is smaller than 25, it becomes a 32-bit REAL data type. If n

is greater than or equal to 25, it then becomes a 64-bit DOUBLE data type. If n is not declared, it

becomes a 64-bit double data type by default.

Character String Types

The character string data types are used to store values that contain character strings. While VARCHAR data

types contain ASCII character strings, NVARCHAR are used for storing Unicode character strings.

VARCHAR

The VARCHAR(n) data type specifies a variable-length ASCII character string, where n indicates the

maximum length and is an integer between 1 and 5000.

NVARCHAR

The NVARCHAR(n) data type specifies a variable-length Unicode character set string, where n

indicates the maximum length and is an integer between 1 and 5000.

ALPHANUM

The ALPHANUM(n) data type specifies a variable-length character string which contains alpha-

numeric characters, where n indicates the maximum length and is an integer between 1 and 127.

SHORTTEXT

The SHORTTEXT(n) data type specifies veriable-length character string which supports textsearch-

SAP HANA Database - SQL Reference Manual

16 2012-05-09

features and stringsearch-features. This is not a standalone sql-type. Selecting a SHORTTEXT(n)-

column yields a column of type NVARCHAR(n).

<shorttext_type> ::= SHORTTEXT '(' int_const ')' <elem_list_shorttext>

<elem_list_shorttext> ::= <elem_shorttext> [... ',' <elem_shorttext>]

<elem_shorttext> ::= <fulltext_elem> | SYNC[HRONOUS]

Binary Types

Binary types are used to store bytes of binary data.

VARBINARY

The VARBINARY(n) data type is used to store binary data of a specified maximum length in bytes,

where n indicates the maximum length and is an integer between 1 and 5000.

Large Object (LOB) Types

LOB (large objects) data types, CLOB, NCLOB and BLOB, are used to store a large amount of data such as text

documents and images. The maximum size of an LOB is 2 GB.

BLOB

The BLOB data type is used to store large binary data.

CLOB

The CLOB data type is used to store large ASCII character data.

NCLOB

The NCLOB data type is used to store a large Unicode character object.

TEXT

The TEXT data type specifies which supports textsearch-features. This is not a standalone sql-type.

Selecting a TEXT-column yields a column of type NCLOB.

<text_type> ::= TEXT <opt_fulltext_elem_list_text>

<opt_fulltext_elem_list_text> ::=

<fulltext_elem_text> [... ',' <fulltext_elem_text>]

<fulltext_elem_text> ::= <fulltext_elem>

| [SYNC[HRONOUS]

| [ASYNC[HRONOUS] FLUSH [QUEUE]

EVERY <n> MINUTES [[OR] AFTER <m> DOCUMENTS] ]

Syntax-rules common to TEXT and SHORTTEXT

<fulltext_elem> ::= LANGUAGE COLUMN <column_name>

| LANGUAGE DETECTION '(' <str_const_list> ')'

| MIME TYPE COLUMN <column_name>

| FUZZY SEARCH INDEX [ON|OFF]

| PHRASE INDEX RATIO [ON|OFF]

| CONFIGURATION <str_const>

| SEARCH ONLY [ON|OFF]

| FAST PREPROCESS [ON|OFF]

LOB types are provided for storing and retrieving such large data. LOB types support the following

operations.

The length() function returns the LOB length in bytes.

LIKE can be used to search LOB columns.

The LOB types have the following restrictions:

LOB columns cannot appear in ORDER BY or GROUP BY clauses.

LOB columns cannot appear in FROM clauses as a join predicate.

LOB columns cannot appear in WHERE clauses as a predicate except LIKE, CONTAINS, =, or <>.

LOB columns cannot appear in SELECT clauses as an aggregate function argument.

LOB columns cannot appear in SELECT DISTINCT clauses.

LOB columns cannot be used in set operations such as EXCEPT. UNION ALL is an exception.

LOB columns cannot be used as a primary key.

LOB columns cannot be used in CREATE INDEX statements.

LOB columns cannot be used in statistics update statements.

SAP HANA Database - SQL Reference Manual

17 2012-05-09

Mapping between SQL Data Type and Column Store Data Type

SQL Type Column Store Type

Integer Types TINYINT, SMALLINT, I NT CS_INT

BIGINT CS_FIXED(18,0)

Approximate Types REAL CS_FLOAT

DOUBLE CS_DOUBLE

FLOAT CS_DOUBLE

FLOAT(p) CS_FLOAT, CS_DOUBLE

Decima l Type s DECIMAL CS_DECIMAL_FLOAT

DECIMAL(p,s ) CS_FIXED(p-s ,s)

SMALLDECIMAL CS_SDFLOAT

Cha racter Types VARCHAR CS_STRING,CS_ALPHANUM,CS_UNITDECFLOAT,CS_DATE,CS_TIME

NVARCHAR CS_STRING,CS_ALPHANUM,CS_UNITDECFLOAT

CLOB, NCLOB CS_STRI NG

ALPHANUM CS_ALPHANUM

Bina ry Type s BLOB CS_RAW

VARBINARY CS_RAW

Da te /Ti me Type s DATE CS_DAYDATE,CS_DATE

TIME CS_SECONDTIME,CS_TIME

TIMESTAMP CS_LONGDATE,CS_DATE,CS_SECONDDATE

SECONDDATE CS_SECONDDATE

Data Type Conversion

This section describes the data type conversion allowed in SAP HANA Database.

Explicit type conversion

The type of an expression result, for example a field reference, a function on fields, or literals can be

converted using the following functions: CAST, TO_ALPHANUM, TO_BIGINT, TO_VARBINARY,

TO_BLOB, TO_CLOB, TO_DATE, TO_DATS, TO_DECIMAL, TO_DOUBLE, TO_INTEGER, TO_INT,

TO_NCLOB, TO_NVARCHAR, TO_REAL, TO_SECONDDATE, TO_SMALLINT, TO_TINYINT, TO_TIME,

TO_TIMESTAMP, TO_VARCHAR.

Implicit type conversion

When a given set of operand/argument types does not match what an operator/function expects, a

type conversion is carried out by the SAP HANA Database. This conversion only occurs if a relevant

conversion is available and if it makes the operation/function executable. For instance, a comparison

of BIGINT and VARCHAR is performed by implicitly converting VARCHAR to BIGINT. The entire explicit

conversions can be used for implicit conversion except for the TIME and TIMESTAMP data types. TIME

and TIMESTAMP can be converted to each other using TO_TIME(TIMESTAMP) and

TO_TIMESTAMP(TIME).

Examples

Table 8: Implicit Type conversion Examples

Input Expression Transformed Expression with Implicit Conversion

BIGINT > VARCHAR BIGINT > BI GINT(VARCHAR)

BIGINT > DECIMAL DECIMAL(BIGINT) > DECIMAL

TIMESTAMP > DATE TIMESTAMP > TIMESTAMP(DATE)

DATE > TIME Error be ca us e the re i s no convers i on a vai l abl e betwe e n DATE a nd TIME

In the tables below,

Boxes with "OK" means data type conversions are allowed without any checks.

Boxes with "CHK" means the data type can be converted if the data is valid for the target type.

Boxes with "-" indicates that data type conversion is not allowed.

The rules shown are applicable to both implicit and explicit conversion except for Time to Timestamp

conversion. Only explicit conversions are allowed for converting the Time data type to Timestamp using the

TO_TIMESTAMP or CAST functions.

SAP HANA Database - SQL Reference Manual

18 2012-05-09

剩余126页未读，继续阅读

hx038

粉丝: 1

SAP HANA 数据库 SQL 参考手册

SAP_HANA_SQL_Reference_Guide_en.pdf

SAP_HANA_SQL_Script_Reference_en.pdf

SAP_HANA_SQL_and_System_Views_Reference_en

SAP_HANA_Developer_Guide_for_SAP_HANA_Studio_en.pdf

SAP_HANA_Administration_Guide_en.pdf

SAP_HANA_Search_Developer_Guide_en.pdf

HANA_Performance_Developer_Guide_en.pdf

SAP_HANA_Predictive_Analysis_Library_PAL_en.pdf

SAP_HANA_Administration_Guide_2.0SPS5_v1.0.pdf

HA150_EN_Col15 SAP HANA 2.0 SPS03 SQL and SQL Script for SAP HANA

最新资源