Teradata数据库学习资源与参考手册

4星 · 超过85%的资源需积分: 10 150 浏览量更新于2024-08-02 收藏 2.18MB PDF 举报

"Teradata Database SQL Reference Data Manipulation Statements Release 12.0 B035-1146-067A September 2007" Teradata是全球知名的数据库管理系统，尤其在大数据处理和企业级数据仓库解决方案中占有重要地位。本资料《Teradata Database SQL Reference》专注于Teradata数据库的SQL语言使用，特别是数据操纵语句（Data Manipulation Statements），适用于Teradata Database的12.0版本。 SQL，全称Structured Query Language，是用于管理和处理关系数据库的标准语言。在Teradata中，SQL被用来执行各种任务，如创建、修改和查询数据库对象，以及插入、更新和删除数据。本参考文档详细阐述了如何在Teradata环境中使用这些语句，帮助用户更有效地管理他们的数据。在12.0版本中，可能涵盖的内容包括： 1. 数据定义语句（DDL）：创建、修改和删除数据库表、视图、索引等对象。 2. 数据操作语句（DML）：插入新数据、更新现有数据和删除数据。 3. 数据查询语句（DQL）：使用SELECT语句来检索和分析数据，可能涉及联接、子查询、聚合函数和分组等高级功能。 4. 事务处理：包括BEGIN、COMMIT、ROLLBACK等语句，确保数据的一致性和完整性。 5. 游标和存储过程：用于控制查询的执行流程和实现复杂业务逻辑。 6. 触发器：自动执行特定操作，如在数据更改时触发的事件。 7. 并行处理和负载均衡：Teradata的一大特色，通过其独特的并行架构，可以高效地处理大规模数据。 8. 安全性与权限管理：包括用户账户管理、角色、权限和访问控制列表。此外，文档还会包含错误处理、性能优化、SQL编程接口（如ODBC和JDBC）的使用指南，以及与其他系统（如IBM CICS、DB2、HP-UX、Oracle等）的集成方法。该文档可能还涉及到Teradata特有的特性，如FastLoad、MultiLoad、Parallel FastLoad等快速数据加载工具，以及SQL Assistant、Teradata Studio等开发工具的使用。由于标签中提到了"teradata"，这表明资料可能包含了Teradata数据库的全面信息，对学习和使用Teradata SQL的人员来说是非常有价值的参考资料。如果你在使用Teradata过程中遇到问题，或者想要提升你的SQL技能，这份文档将提供宝贵的指导和支持。

Chapter 1: The SELECT Statement

SELECT

16 SQL Reference: Data Manipulation Statements

SELECT

FROM

expression

table_name.*

expression_alias_name

SEL DISTINCT

ALL

Seed Statement

1101C293

table_name

correlation_name

JOIN ON

joined_table search_conditionjoin_table_name

(subquery) derived_table_name

INNER

LEFT

RIGHT OUTER

FULL

CROSS JOIN

WHERE

Single

Tables

Joined

Tables

Derived

Tables

search_condition

ordinary_group_set

GROUP BY

HAVING

conditional_expression

empty_grouping_set

rollup_list

cube_list

grouping_sets_specification

column_name

()

QUALIFY

search_condition

ORDER BY

DESC

ASC

column_name_alias

column_position

column_name

expression

Chapter 1: The SELECT Statement

SELECT

18 SQL Reference: Data Manipulation Statements

select_expression a non-recursive SELECT statement that retrieves the row data to store

in the named query.

You can specify an expression that returns a UDT in a column list only

if its transform group has a fromsql routine. The system automatically

converts the expression from its UDT value to the external type via the

fromsql routine before returning it to a client application. See “Rules

for UDTs” on page 32 for details.

Recursive

The WITH RECURSIVE clause defines a named query that can refer to itself in the query definition

and in the SELECT statement that the WITH RECURSIVE clause precedes. The named query

definition consists of at least one non-recursive, or seed, statement and at least one recursive

statement.

For details, see “WITH RECURSIVE Clause” on page 51.

query_name the name of the recursive query.

column_name the name of a column in the named query definition.

UNION ALL the operator that adds results of iterative operations to the named

query.

Because UNION ALL permits duplicate rows, it is not a true relational

operator.

DISTINCT that only one row is to be returned from any set of duplicates that

might result from a given expression list.

Two rows are considered duplicates only if each value in one is equal to

the corresponding value in the other.

You cannot specify DISTINCT if any member of the select column list

is a LOB column.

ALL that all rows, including duplicates, are to be returned in the results of

the expression list. This is the default value.

table_name the name of a table for which all the attributes of all its structured

UDT columns are to be returned.

column_name the name of a structured UDT column for which all its attributes are to

be returned.

.ALL that all attributes for the specified UDT column or for all structured

UDT columns in the specified table are to be returned in the results of

the expression list.

TOP n that the query return a specified number of rows or percentage of rows

available.

For details, see “TOP n Operator” on page 76.

integer [PERCENT] an INTEGER literal indicating the number of rows to return.

If the PERCENT option is specified, the system returns integer% of the

total rows available, where 100 ≥ integer > 0.

Syntax Element … Specifies …

Chapter 1: The SELECT Statement

SELECT

SQL Reference: Data Manipulation Statements 19

decimal [PERCENT] a DECIMAL literal indicating the number of rows to return.

If the PERCENT option is specified, the system return decimal% of the

total rows available, where 100 ≥ decimal > 0.

WITH TIES that the rows returned by the query include the specified number or

percentage of rows in the ordered set produced by the ORDER BY

clause, plus any additional rows where the value of the sort key is the

same as the value of the sort key in the last row that satisfies the

specified number or percentage of rows.

The WITH TIES option is ignored if the SELECT statement does not

also specify an ORDER BY clause.

* that all columns of all tables referenced in the FROM clause be

returned.

When qualified by table_name, specifies that all columns of

table_name only are to be returned.

View columns are explicitly enumerated when views are defined;

therefore, if a table is changed after a view is defined, those changes

will not appear if you perform a SELECT * query.

Note that SELECT * … is a column operation, projecting all the

columns of a table

, while SELECT COUNT(*)… is a row operation,

restricting and then counting all the rows of a table, reporting the

cardinality of the table in question. The semantics of the asterisk are

orthogonal in the two cases.

expression any valid SQL expression.

You can specify an expression that returns a UDT in a column list only

if its transform group has a fromsql routine. The system automatically

converts the expression from its UDT value to the external type via the

fromsql routine before returning it to a client application. See “Rules

for UDTs” on page 32 for details.

AS an optional introduction to derived_column_name.

derived_column_name an alias for the column expression that is derived from expression. You

must specify a derived_column_name for self-join operations.

table_name the name of a table, queue table, derived table, or view.

table_name.* in the SELECT list can define the table from which rows

are to be returned when two or more tables are referenced in the

FROM clause.

FROM Clause

FROM an introduction to the names of one or more tables, queue tables,

views, or derived tables from which expression is to be derived.

If the TABLE option is specified, the FROM clause invokes the

specified table user-defined function that returns row results as a

derived table.

For details, see “FROM Clause” on page 80.

Syntax Element … Specifies …

Chapter 1: The SELECT Statement

SELECT

20 SQL Reference: Data Manipulation Statements

Single Table

This option allows the FROM clause to specify single tables.

A FROM clause can include a sequence of single table references, which creates an implicit inner join;

to be distinguished from explicit inner joins in which the keyword JOIN is part of the syntax.

table_name the name of a single table, queue table, derived table, or view referred

to in the FROM clause.

AS an optional introduction to correlation_name.

correlation_name an alias for the table referenced in the FROM clause.

Correlation names are sometimes referred to as range variables.

Joined Tables

Options for joined tables allow the FROM clause to specify that multiple tables be joined in explicit

ways, described as follows:

join_table_name the name of a joined table.

INNER a join in which qualifying rows from one table are combined with

qualifying rows from another table according to some join condition.

Inner join is the default join type.

OUTER a join in which qualifying rows from one table that do not have

matches in the other table are included in the join result.

The rows from the first table are extended with nulls.

LEFT OUTER an outer join with the table that was listed first in the FROM clause.

In a LEFT OUTER JOIN, the rows from the left table that are not

returned in the result of the inner join of the two tables are returned in

the outer join result and extended with nulls.

RIGHT OUTER an outer join with the table that was listed second in the FROM clause.

In a RIGHT OUTER JOIN, the rows from the right table that are not

returned in the result of the inner join of the two tables are returned in

the outer join result and extended with nulls.

FULL OUTER that rows be returned from both tables.

In a FULL OUTER JOIN, rows from both tables that have not been

returned in the result of the inner join will be returned in the outer

join result and extended with nulls.

JOIN an introduction to the name of the second table to participate in the

join.

joined_table the name of the joined table.

ON search_condition one or more conditional expressions that must be satisfied by the

result rows.

An ON condition clause is required if the FROM clause specifies an

outer join.

Syntax Element … Specifies …

剩余491页未读，继续阅读

sbvfhpl123

粉丝: 0
资源: 1

Teradata数据库学习资源与参考手册

Teradata SQL基础教程：从入门到实践

Teradata官方培训资料：第七版课程概述

Teradata数据库基础与客户端安装指南

teradata 学习资料

Teradata学习资料

TERADATA 学习资料

Teradata培训资料

teradata数据学习资料

teradata数据库学习资料

超实用Teradata数据仓库培训资料

最新资源