IBM DB2 Version 9.5 SQL Reference for Linux, UNIX and Windows

需积分: 10 170 浏览量更新于2024-07-27 收藏 7.99MB PDF 举报

"DB2 SQL Reference 1_950" 这篇文档是IBM官方提供的DB2 Version 9.5针对Linux, UNIX, 和Windows操作系统的SQL参考手册，卷1，编号SC23-5861-00。这份资料详细阐述了DB2数据库管理系统在这些平台上的SQL语法和使用方法，是学习DB2数据库不可或缺的参考资料。 DB2是IBM推出的一款关系型数据库管理系统，广泛应用于企业级数据存储和管理。在SQL Reference中，你可以找到关于SQL语句的详细解释，包括但不限于： 1. **数据定义语言(DDL)**：如CREATE TABLE用于创建数据库表，ALTER TABLE用于修改已存在的表结构，DROP TABLE用于删除表等。 2. **数据操纵语言(DML)**：包括INSERT用于插入数据，UPDATE用于更新数据，DELETE用于删除数据，以及SELECT用于查询数据的基本语法和高级用法。 3. **数据控制语言(DCL)**：如GRANT和REVOKE用于权限管理和访问控制，确保数据库的安全性。 4. **事务处理**：包括BEGIN、COMMIT、ROLLBACK等语句，用于管理数据库中的事务，确保数据的一致性和完整性。 5. **视图和索引**：如何创建和使用视图来简化复杂查询，以及如何通过索引来优化查询性能。 6. **存储过程和函数**：定义和调用自定义的存储过程和函数，提高代码复用和数据库操作的效率。 7. **游标和批处理**：游标用于逐行处理查询结果，批处理则可以一次性执行多条SQL语句，提升性能。 8. **连接和JOIN操作**：如何将多个表的数据关联起来，进行复杂的联接查询。 9. **子查询和聚合函数**：利用子查询实现更复杂的逻辑，使用COUNT、SUM、AVG、MAX、MIN等聚合函数对数据进行统计分析。 10. **触发器**：自动执行特定操作的数据库对象，通常用于实现业务规则或数据验证。 11. **日期和时间函数**：处理日期和时间类型的函数，如DATE(), TIME(), TIMESTAMP()等。 12. **错误处理和诊断**：提供SQLSTATE和SQLCODE等信息用于识别和解决问题。这份文档还可能包含示例、最佳实践和性能调优建议，帮助开发者和DBA更好地理解和应用DB2的SQL特性。由于是IBM的官方文档，其权威性和准确性都得到了保证。用户可以通过在线或联系当地的IBM代表来获取这份出版物，以便深入学习和使用DB2数据库系统。

v IBM SQL Reference

– This book contains all the common elements of SQL that span IBM’s database

products. It provides limits and rules that assist in preparing portable

programs using IBM databases. This manual provides a list of SQL extensions

and incompatibilities among the following standards and products: SQL92E,

XPG4-SQL, IBM-SQL, and the IBM relational database products.

v American National Standard X3.135-1992, Database Language SQL

– Contains the ANSI standard definition of SQL.

ISO/IEC 9075:1992, Database Language SQL

– Contains the 1992 ISO standard definition of SQL.

ISO/IEC 9075-2:2003, Information technology -- Database Languages -- SQL -- Part 2:

Foundation (SQL/Foundation)

– Contains a large portion of the 2003 ISO standard definition of SQL.

ISO/IEC 9075-4:2003, Information technology -- Database Languages -- SQL -- Part 4:

Persistent Stored Modules (SQL/PSM)

– Contains the 2003 ISO standard definition for SQL procedure control

statements.

Related documentation

xiv SQL Reference, Volume 1

Chapter 1. Concepts

Databases

A DB2 database is a relational database. The database stores all data in tables that are

related to one another. Relationships are established between tables such that data

is shared and duplication is minimized.

A relational database is a database that is treated as a set of tables and manipulated

in accordance with the relational model of data. It contains a set of objects used to

store, manage, and access data. Examples of such objects are tables, views, indexes,

functions, triggers, and packages. Objects can be either defined by the system

(system-defined objects) or defined by the user (user-defined objects).

A distributed relational database consists of a set of tables and other objects that are

spread across different but interconnected computer systems. Each computer

system has a relational database manager to manage the tables in its environment.

The database managers communicate and cooperate with each other in a way that

allows a given database manager to execute SQL statements on another computer

system.

A partitioned relational database is a relational database whose data is managed

across multiple database partitions. This separation of data across database

partitions is transparent to users of most SQL statements. However, some data

definition language (DDL) statements take database partition information into

consideration (for example, CREATE DATABASE PARTITION GROUP). DDL is the

subset of SQL statements used to describe data relationships in a database.

A federated database is a relational database whose data is stored in multiple data

sources (such as separate relational databases). The data appears as if it were all in

a single large database and can be accessed through traditional SQL queries.

Changes to the data can be explicitly directed to the appropriate data source.

Structured Query Language (SQL)

SQL is a standardized language for defining and manipulating data in a relational

database. In accordance with the relational model of data, the database is treated

as a set of tables, relationships are represented by values in tables, and data is

retrieved by specifying a result table that can be derived from one or more base

tables.

SQL statements are executed by a database manager. One of the functions of the

database manager is to transform the specification of a result table into a sequence

of internal operations that optimize data retrieval. The transformation occurs in

two phases: preparation and binding.

All executable SQL statements must be prepared before they can be executed. The

result of preparation is the executable or operational form of the statement. The

method of preparing an SQL statement and the persistence of its operational form

distinguish static SQL from dynamic SQL.

Queries and table expressions

A query is a component of certain SQL statements; it specifies a (temporary) result

table.

A table expression creates a temporary result table from a simple query. Clauses

further refine the result table. For example, you can use a table expression as a

query to select all of the managers from several departments, specify that they

must have over 15 years of working experience, and be located at the New York

branch office.

A common table expression is like a temporary view within a complex query. It can

be referenced in other places within the query, and can be used in place of a view.

Each use of a specific common table expression within a complex query shares the

same temporary view.

Recursive use of a common table expression within a query can be used to support

applications such as airline reservation systems, bill of materials (BOM) generators,

and network planning.

DB2 Call level interface (CLI) and open database connectivity (ODBC)

The DB2 call level interface is an application programming interface that provides

functions for processing dynamic SQL statements to application programs. CLI

programs can also be compiled using an open database connectivity Software

Developer’s Kit (available from Microsoft

or other vendors), which enables access

to ODBC data sources. Unlike embedded SQL, this interface requires no

precompilation. Applications can be run against a variety of databases without

having to be compiled against each of these databases. Applications use procedure

calls at run time to connect to databases, issue SQL statements, and retrieve data

and status information.

The DB2 CLI interface provides many features not available in embedded SQL. For

example:

v CLI provides function calls that support a way of querying database catalogs

that is consistent across the DB2 family. This reduces the need to write catalog

queries that must be tailored to specific database servers.

v CLI provides the ability to scroll through a cursor:

– Forward by one or more rows

– Backward by one or more rows

– Forward from the first row by one or more rows

– Backward from the last row by one or more rows

– From a previously stored location in the cursor.

Stored procedures called from application programs that were written using CLI

can return result sets to those programs.

Java database connectivity (JDBC) and embedded SQL for Java

(SQLJ) programs

DB2 implements two standards-based Java

™

programming APIs: Java database

connectivity (JDBC) and embedded SQL for Java (SQLJ). Both can be used to create

Java applications and applets that access DB2:

Queries and table expressions

2 SQL Reference, Volume 1

v JDBC calls are translated into DB2 CLI calls through Java native methods. JDBC

requests flow from the DB2 client through DB2 CLI to the DB2 server. JDBC

cannot use static SQL.

v SQLJ applications use JDBC as a foundation for such tasks as connecting to

databases and handling SQL errors, but can also contain embedded static SQL

statements in the SQLJ source files. An SQLJ source file must be translated by

the SQLJ translator before the resulting Java source code can be compiled.

Schemas

A schema is a collection of named objects; it provides a way to group those objects

logically. A schema is also a name qualifier; it provides a way to use the same

natural name for several objects, and to prevent ambiguous references to those

objects.

For example, the schema names ’INTERNAL’ and ’EXTERNAL’ make it easy to

distinguish two different SALES tables (INTERNAL.SALES, EXTERNAL.SALES).

Schemas also enable multiple applications to store data in a single database

without encountering namespace collisions.

A schema is distinct from, and should not be confused with, an XML schema,

which is a standard that describes the structure and validates the content of XML

documents.

A schema can contain tables, views, nicknames, triggers, functions, packages, and

other objects. A schema is itself a database object. It is explicitly created using the

CREATE SCHEMA statement, with the current user or a specified authorization ID

recorded as the schema owner. It can also be implicitly created when another

object is created, if the user has IMPLICIT_SCHEMA authority.

A schema name is used as the high order part of a two-part object name. If the

object is specifically qualified with a schema name when created, the object is

assigned to that schema. If no schema name is specified when the object is created,

the default schema name is used (specified in the CURRENT SCHEMA special

For example, a user with DBADM authority creates a schema called C for user A:

CREATE SCHEMA C AUTHORIZATION A

User A can then issue the following statement to create a table called X in schema

C (provided that user A has the CREATETAB database authority):

CREATE TABLE C.X (COL1 INT)

Some schema names are reserved. For example, built-in functions belong to the

SYSIBM schema, and the pre-installed user-defined functions belong to the

SYSFUN schema.

When a database is created, if it is not created with the RESTRICTIVE option, all

users have IMPLICIT_SCHEMA authority. With this authority, users implicitly

create a schema whenever they create an object with a schema name that does not

already exist. When schemas are implicitly created, CREATEIN privileges are

granted which allows any user to create other objects in this schema. The ability to

create objects such as aliases, distinct types, functions, and triggers is extended to

Java database connectivity (JDBC) and embedded SQL for Java (SQLJ) programs

Chapter 1. Concepts 3

implicitly-created schemas. The default privileges on an implicitly-created schema

provide backward compatibility with previous versions.

If IMPLICIT_SCHEMA authority is revoked from PUBLIC, schemas can be

explicitly created using the CREATE SCHEMA statement, or implicitly created by

users (such as those with DBADM authority) who have been granted

IMPLICIT_SCHEMA authority. Although revoking IMPLICIT_SCHEMA authority

from PUBLIC increases control over the use of schema names, it can result in

authorization errors when existing applications attempt to create objects.

Schemas also have privileges, allowing the schema owner to control which users

have the privilege to create, alter, copy, and drop objects in the schema. This

provides a way to control the manipulation of a subset of objects in the database.

A schema owner is initially given all of these privileges on the schema, with the

ability to grant the privileges to others. An implicitly-created schema is owned by

the system, and all users are initially given the privilege to create objects in such a

schema. A user with SYSADM or DBADM authority can change the privileges that

are held by users on any schema. Therefore, access to create, alter, copy, and drop

objects in any schema (even one that was implicitly created) can be controlled.

Tables

Tables are logical structures maintained by the database manager. Tables are made

up of columns and rows.

At the intersection of every column and row is a specific data item called a value.

A column is a set of values of the same type or one of its subtypes. A row is a

sequence of values arranged so that the nth value is a value of the nth column of

the table.

An application program can determine the order in which the rows are populated

into the table, but the actual order of rows is determined by the database manager,

and typically cannot be controlled. Multidimensional clustering (MDC) provides

some sense of clustering, but not actual ordering between the rows.

Constraints

Within any business, data must often adhere to certain restrictions or rules. For

example, an employee number must be unique. The database manager provides

constraints as a way to enforce such rules.

The following types of constraints are available:

v NOT NULL constraints

v Unique (or unique key) constraints

v Primary key constraints

v Foreign key (or referential integrity) constraints

v (Table) Check constraints

v Informational constraints

Constraints

are only associated with tables and are either defined as part of the

table creation process (using the CREATE TABLE statement) or are added to a

table’s definition after the table has been created (using the ALTER TABLE

statement). You can use the ALTER TABLE statement to modify constraints. In

Schemas

4 SQL Reference, Volume 1

剩余1020页未读，继续阅读

西湘客

粉丝: 28
资源: 8

IBM DB2 Version 9.5 SQL Reference for Linux, UNIX and Windows

DB2 SQL Reference.pdf

db2SQL_Reference_2_950

DB2 Command Reference

SQL_reference

DB2 SQL-Reference

DB2 SQL Reference

DB2 V9 sql Reference

DB2 8 SQL Reference

IBM DB2 SQL Reference

DB2v9 SQL Reference Vol.1

最新资源