DB2 SQL宝典：函数、语法与语句块解析

4星 · 超过85%的资源需积分: 20 105 浏览量更新于2024-08-01 1 收藏 1.01MB PDF 举报

"DB2 SQL语法和语句块大全，涵盖了DB2关系数据库管理系统的SQL语言使用，包括函数、查询和语句结构等核心内容。由Graeme Birchall编写的DB2UDBV8.1 Cookbook，适用于Windows、UNIX、Linux、OS/2等平台，不适用于DB2 for z/OS或DB2 for AS/400，因为这两个平台的SQL语法有所不同。此书不断更新，建议获取最新版本以获取最佳示例。注意，文档提供的信息可能存在误差，使用时应谨慎对待，作者对误操作不承担责任。" DB2 SQL语法是用于与DB2数据库进行交互的标准化查询语言，它是SQL（结构化查询语言）的一个实现。SQL在DB2中被广泛用于创建、查询、更新和管理数据库对象。以下是一些主要的SQL知识点： 1. **数据定义语言 (DDL)**: DDL用于创建和修改数据库结构，如创建表、视图、索引、存储过程等。例如： - `CREATE TABLE` 用来创建新的表。 - `ALTER TABLE` 用于修改已存在的表结构，如添加或删除列。 - `DROP TABLE` 用于删除不再需要的表。 2. **数据操纵语言 (DML)**: DML用于插入、更新和删除数据。常见的DML语句包括： - `INSERT INTO` 用于向表中插入新记录。 - `UPDATE` 用于修改现有记录的数据。 - `DELETE FROM` 用于删除满足特定条件的记录。 3. **数据查询语言 (DQL)**: DQL用于检索数据，最常用的是`SELECT`语句，可以用于组合各种条件、函数和连接操作来查询复杂的数据。 - `SELECT`语句可以与`WHERE`子句结合，根据指定条件过滤结果。 - `GROUP BY` 和 `HAVING` 用于对结果进行分组和筛选。 - `JOIN` 语句用于合并来自多个表的数据。 4. **事务处理语言 (TPL)**: TPL用于控制事务，确保数据的一致性和完整性。 - `BEGIN TRANSACTION` 开始一个事务。 - `COMMIT` 提交事务，保存所有更改。 - `ROLLBACK` 回滚事务，撤销所有更改。 5. **函数和运算符**: DB2支持多种内置函数，如数学函数、字符串函数、日期时间函数等，以及比较和逻辑运算符，如`SUM()`, `AVG()`, `COUNT()`, `LIKE`, `AND`, `OR`等。 6. **存储过程和触发器**: 存储过程是预编译的SQL语句集合，可以在需要时调用。触发器则是在特定数据库事件（如INSERT, UPDATE, DELETE）发生时自动执行的程序。 7. **游标和变量**: 游标允许逐行处理查询结果，而变量则可以存储和传递数据。 8. **视图**: 视图是从一个或多个表中选择的虚拟表，可以简化复杂的查询并提供安全性。 9. **索引**: 索引用于加速数据检索，通过创建索引可以提高查询性能。 10. **分区和集群**: 在大型系统中，通过分区和集群可以将数据分布在多台服务器上，以提高并发性能和可用性。请注意，虽然这些知识点适用于DB2UDBV8.1，但随着DB2版本的更新，可能会引入新的特性和优化，因此，使用时务必参考最新的文档或官方指南。在实际应用中，理解和熟练掌握这些SQL语法和语句块对于有效管理和操作DB2数据库至关重要。

Graeme Birchall ©

16 Index of Concepts

Summarize Column Contents

Use a column function (see page 67) to summarize the contents of a column.

EMP_NM SELECT AVG(id) AS avg ANSWER

+----------+ ,MAX(name) AS maxn =================

|ID|NAME | ,COUNT(*) AS #rows AVG MAXN #ROWS

|--|-------| FROM emp_nm; --- ------- -----

|10|Sanders| 26 Sanders 3

|20|Pernal |

|50|Hanes |

+----------+

Figure 12, Column Functions example

Subtotals and Grand Totals

To obtain subtotals and grand-totals, use the ROLLUP or CUBE statements (see page 167).

SELECT job ANSWER

,dept =======================

,SUM(salary) AS sum_sal JOB DEPT SUM_SAL #EMP

,COUNT(*) AS #emps ----- ---- -------- ----

FROM staff Clerk 15 24766.70 2

WHERE dept < 30 Clerk 20 27757.35 2

AND salary < 20000 Clerk - 52524.05 4

AND job < ’S’ Mgr 10 19260.25 1

GROUP BY ROLLUP(job, dept) Mgr 20 18357.50 1

ORDER BY job Mgr - 37617.75 2

,dept; - - 90141.80 6

Figure 13, Subtotal and Grand-total example

Enforcing Data Integrity

When a table is created, various DB2 features can be used to ensure that the data entered in

the table is always correct:

• Uniqueness (of values) can be enforced by creating unique indexes.

• Check constraints can be defined to limit the values that a column can have.

• Default values (for a column) can be defined - to be used when no value is provided.

• Identity columns (see page 229), can be defined to automatically generate unique nu-

meric values (e.g. invoice numbers) for all of the rows in a table. Sequences can do the

same thing over multiple tables.

• Referential integrity rules can created to enforce key relationships between tables.

• Triggers can be defined to enforce more complex integrity rules, and also to do things

(e.g. populate an audit trail) whenever data is changed.

See the DB2 manuals for documentation about the above.

Hide Complex SQL

One can create a view (see page 18) to hide complex SQL that is run repetitively. Be warned

however that doing so can make it significantly harder to tune the SQL - because some of the

logic will be in the user code, and some in the view definition.

Summary Table

Some queries that use a GROUP BY can be made to run much faster by defining a summary

table (see page 217) that DB2 automatically maintains. Subsequently, when the user writes

the original GROUP BY against the source-data table, the optimizer substitutes with a much

simpler (and faster) query against the summary table.

Graeme Birchall ©

18 SQL Components

SQL Components

DB2 Objects

DB2 is a relational database that supports a variety of object types. In this section we shall

overview those items which one can obtain data from using SQL.

Table

A table is an organized set of columns and rows. The number, type, and relative position, of

the various columns in the table is recorded in the DB2 catalogue. The number of rows in the

table will fluctuate as data is inserted and deleted.

The CREATE TABLE statement is used to define a table. The following example will define

the EMPLOYEE table, which is found in the DB2 sample database.

CREATE TABLE employee

(empno CHARACTER (00006) NOT NULL

,firstnme VARCHAR (00012) NOT NULL

,midinit CHARACTER (00001) NOT NULL

,lastname VARCHAR (00015) NOT NULL

,workdept CHARACTER (00003)

,phoneno CHARACTER (00004)

,hiredate DATE

,job CHARACTER (00008)

,edlevel SMALLINT NOT NULL

,SEX CHARACTER (00001)

,birthdate DATE

,salary DECIMAL (00009,02)

,bonus DECIMAL (00009,02)

,comm DECIMAL (00009,02)

)

DATA CAPTURE NONE;

Figure 16, DB2 sample table - EMPLOYEE

View

A view is another way to look at the data in one or more tables (or other views). For example,

a user of the following view will only see those rows (and certain columns) in the EM-

PLOYEE table where the salary of a particular employee is greater than or equal to the aver-

age salary for their particular department.

CREATE VIEW employee_view AS

SELECT a.empno, a.firstnme, a.salary, a.workdept

FROM employee a

WHERE a.salary >=

(SELECT AVG(b.salary)

FROM employee b

WHERE a.workdept = b.workdept);

Figure 17, DB2 sample view - EMPLOYEE_VIEW

A view need not always refer to an actual table. It may instead contain a list of values:

CREATE VIEW silly (c1, c2, c3)

AS VALUES (11, ’AAA’, SMALLINT(22))

,(12, ’BBB’, SMALLINT(33))

,(13, ’CCC’, NULL);

Figure 18, Define a view using a VALUES clause

Selecting from the above view works the same as selecting from a table:

DB2 UDB/V8.1 Cookbook ©

Introduction to SQL 19

SELECT c1, c2, c3 ANSWER

FROM silly ===========

ORDER BY c1 aSC; C1 C2 C3

-- --- --

11 AAA 22

12 BBB 33

13 CCC -

Figure 19, SELECT from a view that has its own data

We can go one step further and define a view that begins with a single value that is then ma-

nipulated using SQL to make many other values. For example, the following view, when se-

lected from, will return 10,000 rows. Note however that these rows are not stored anywhere in

the database - they are instead created on the fly when the view is queried.

CREATE VIEW test_data AS

WITH temp1 (num1) AS

(VALUES (1)

UNION ALL

SELECT num1 + 1

FROM temp1

WHERE num1 < 10000)

SELECT *

FROM temp1;

Figure 20, Define a view that creates data on the fly

Alias

An alias is an alternate name for a table or a view. Unlike a view, an alias can not contain any

processing logic. No authorization is required to use an alias other than that needed to access

to the underlying table or view.

CREATE ALIAS employee_al1 FOR employee;

COMMIT;

CREATE ALIAS employee_al2 fOR employee_al1;

COMMIT;

CREATE ALIAS employee_al3 FOR employee_al2;

COMMIT;

Figure 21, Define three aliases, the latter on the earlier

Neither a view, nor an alias, can be linked in a recursive manner (e.g. V1 points to V2, which

points back to V1). Also, both views and aliases still exist after a source object (e.g. a table)

has been dropped. In such cases, a view, but not an alias, is marked invalid.

DB2 Data Types

DB2 comes with the following standard data types:

• SMALLINT, INT, and BIGINT (i.e. integer numbers).

• FLOAT, REAL, and DOUBLE (i.e. floating point numbers).

• DECIMAL and NUMERIC (i.e. decimal numbers).

• CHAR, VARCHAR, and LONG VARCHAR (i.e. character values).

• GRAPHIC, VARGRAPHIC, and LONG VARGRAPHIC (i.e. graphical values).

• BLOB, CLOB, and DBCLOB (i.e. binary and character long object values).

• DATE, TIME, and TIMESTAMP (i.e. date/time values).

• DATALINK (i.e. link to external object).

20 SQL Components

Below is a simple table definition that uses the above data types:

CREATE TABLE sales_record

(sales# INTEGER NOT NULL

GENERATED ALWAYS AS IDENTITY

(START WITH 1

,INCREMENT BY 1

,NO MAXVALUE

,NO CYCLE)

,sale_ts TIMESTAMP NOT NULL

,num_items SMALLINT NOT NULL

,payment_type CHAR(2) NOT NULL

,sale_value DECIMAL(12,2) NOT NULL

,sales_tax DECIMAL(12,2)

,employee# INTEGER NOT NULL

,CONSTRAINT sales1 CHECK(payment_type IN (’CS’,’CR’))

,CONSTRAINT sales2 CHECK(sale_value > 0)

,CONSTRAINT sales3 CHECK(num_items > 0)

,CONSTRAINT sales4 FOREIGN KEY(employee#)

REFERENCES staff(id)

ON DELETE RESTRICT

,PRIMARY KEY(sales#));

Figure 22, Sample table definition

In the above table, we have listed the relevant columns, and added various checks to ensure

that the data is always correct. In particular, we have included the following:

• The sales# is automatically generated (see page 229 for details). It is also the primary key

of the table, and so must always be unique.

• The payment-type must be one of two possible values.

• Both the sales-value and the num-items must be greater than zero.

• The employee# must already exist in the staff table. Furthermore, once a row has been

inserted into this table, any attempt to delete the related row from the staff table will fail.

Default Lengths

The following table has two columns:

CREATE TABLE default_values

(c1 CHAR NOT NULL

,d1 DECIMAL NOT NULL);

Figure 23, Table with default column lengths

The length has not been provided for either of the above columns. In this case, DB2 defaults

to CHAR(1) for the first column and DECIMAL(5,0) for the second column.

Data Type Usage

In general, use the standard DB2 data types as follows:

• Always store monetary data in a decimal field.

• Store non-fractional numbers in one of the integer field types.

• Use floating-point when absolute precision is not necessary.

A DB2 data type is not just a place to hold data. It also defines what rules are applied when

the data in manipulated. For example, storing monetary data in a DB2 floating-point field is a

no-no, in part because the data-type is not precise, but also because a floating-point number is

not manipulated (e.g. during division) according to internationally accepted accounting rules.

剩余346页未读，继续阅读

liyongcun120

粉丝: 0
资源: 1

DB2 SQL宝典：函数、语法与语句块解析

DB2数据库基本语法

DB2中常用SQL语句

db2数据库sql语句大全

db2 sql循环语句

如何将db2的sql语句转换为mysql的sql语句？

pageable 分页拼接的sql语句 怎么区分是拼接oracle语法还是拼接db2语法？

db2 sql语句 update set一个字段 值里面带有双引号 update报错

db2sql1042c 58004

在将Oracle数据库中的存储过程转换为DB2时，如何处理数据类型长度声明和动态SQL语句的差异？

db2 sql字段除

最新资源

pageable 分页拼接的sql语句怎么区分是拼接oracle语法还是拼接db2语法？

db2 sql语句 update set一个字段值里面带有双引号 update报错