DB2 SQL Cookbook：Graeme Birchall的数据库查询指南

4星 · 超过85%的资源需积分: 3 13 浏览量更新于2024-07-26 收藏 1.26MB PDF 举报

"DB2V91CooKbook工具是一个集成的参考资料，专注于DB2数据库系统的SQL用法和关键字应用。这本书对初学者和经验丰富的开发人员都非常有帮助，尤其适用于使用DB2 for Linux, UNIX, 和 Windows版本的用户。作者Graeme Birchall频繁更新内容，确保提供的例子是最新的，即使针对的是旧版DB2，也依然具有参考价值。" DB2 SQL Cookbook的内容可能包括以下几个关键知识点： 1. **SQL基础**：SQL是Structured Query Language的缩写，是用于管理和处理数据库的标准语言。在DB2中，读者可以学习如何创建、修改和查询数据库对象，如表、视图、索引等。 2. **数据操作**：书中可能涵盖了INSERT、UPDATE和DELETE语句的使用，用于插入新数据、更新现有数据和删除不需要的数据记录。 3. **选择查询（SELECT语句）**：详细解释了如何使用SELECT语句从一个或多个表中检索数据，包括使用WHERE子句进行条件筛选，JOIN操作来合并不同表的数据，以及GROUP BY和HAVING子句进行数据分组和过滤。 4. **函数和运算符**：介绍了一系列SQL内置函数，如聚合函数（COUNT, SUM, AVG, MAX, MIN）和转换函数（UPPER, LOWER, DATE, TIME等），以及逻辑运算符（AND, OR, NOT）和比较运算符。 5. **存储过程和触发器**：DB2支持创建存储过程，这是一种预编译的SQL代码集合，可以封装复杂的业务逻辑。触发器则是在特定数据库事件（如INSERT, UPDATE, DELETE）发生时自动执行的程序。 6. **事务管理**：讨论了事务的概念，包括ACID属性（原子性、一致性、隔离性和持久性），以及COMMIT, ROLLBACK和SAVEPOINT命令的使用。 7. **安全性与权限**：如何设置和管理用户权限，包括GRANT和REVOKE语句，以及角色（ROLE）的概念。 8. **性能优化**：可能涵盖索引策略、查询优化器的工作原理、性能监视工具的使用，以及如何通过EXPLAIN计划分析和改进SQL查询的性能。 9. **高级特性**：可能涉及分区、XML支持、游标、临时表、子查询、窗口函数等DB2特有的高级特性。 10. **错误处理与调试**：介绍如何处理SQL执行中的错误，以及调试技巧，帮助开发者解决在实际工作中遇到的问题。请注意，由于DB2的版本不断更新，某些特定于DB2 V9.1的功能或限制可能已经改变或不再适用。因此，使用此Cookbook时最好参考最新的官方文档或资料以获取最准确的信息。此外，虽然作者强调了书中可能存在错误，但这个资源仍能提供宝贵的实践经验和技术洞察。

Graeme Birchall ©

16 Index of Concepts

Append Rows

To add (append) one set of rows to another set of rows, use a union (see page 237).

EMP_NM EMP_JB SELECT * ANSWER

+----------+ +--------+ FROM emp_nm =========

|ID|NAME | |ID|JOB | WHERE name < 'S' ID 2

|--|-------| |--|-----| UNION -- ------

|50|Hanes | +--------+ ORDER BY 1,2; 20 Pernal

+----------+ 50 Hanes

Figure 4, Union example

Assign Output Numbers

To assign line numbers to SQL output, use the ROW_NUMBER function (see page 100).

EMP_JB SELECT id

+--------+ ,job ANSWER

|ID|JOB | ,ROW_NUMBER() OVER(ORDER BY job) AS R ==========

|--|-----| FROM emp_jb ID JOB R

|10|Sales| ORDER BY job; -- ----- -

|20|Clerk| 20 Clerk 1

+--------+ 10 Sales 2

Figure 5, Assign row-numbers example

Assign Unique Key Numbers

The make each row inserted into a table automatically get a unique key value, use an identity

column, or a sequence, when creating the table (see page 255).

If-Then-Else Logic

To include if-then-else logical constructs in SQL stmts, use the CASE phrase (see page 43).

EMP_JB SELECT id ANSWER

+--------+ ,job ===============

|ID|JOB | ,CASE ID JOB STATUS

|--|-----| WHEN job = 'Sales' -- ----- ------

|10|Sales| THEN 'Fire' 10 Sales Fire

|20|Clerk| ELSE 'Demote' 20 Clerk Demote

+--------+ END AS STATUS

FROM emp_jb;

Figure 6, Case stmt example

Get Dependents

To get all of the dependents of some object, regardless of the degree of separation from the

parent to the child, use recursion (see page 285).

FAMILY WITH temp (persn, lvl) AS ANSWER

+-----------+ (SELECT parnt, 1 =========

|PARNT|CHILD| FROM family PERSN LVL

|-----|-----| WHERE parnt = 'Dad' ----- ---

|GrDad|Dad | UNION ALL Dad 1

|Dad |Dghtr| SELECT child, Lvl + 1 Dghtr 2

|Dghtr|GrSon| FROM temp, GrSon 3

|Dghtr|GrDtr| family GrDtr 3

+-----------+ WHERE persn = parnt)

SELECT *

FROM temp;

Figure 7, Recursion example

Convert String to Rows

To convert a (potentially large) set of values in a string (character field) into separate rows

(e.g. one row per word), use recursion (see page 382).

DB2 9 Cookbook ©

Quick Find 17

INPUT DATA Recursive SQL ANSWER

================= ============> ===========

"Some silly text" TEXT LINE#

----- -----

Some 1

silly 2

text 3

Figure 8, Convert string to rows

Be warned - in many cases, the code is not pretty.

Convert Rows to String

To convert a (potentially large) set of values that are in multiple rows into a single combined

field, use recursion (see page 383).

INPUT DATA Recursive SQL ANSWER

=========== ============> =================

TEXT LINE# "Some silly text"

----- -----

Some 1

silly 2

text 3

Figure 9, Convert rows to string

Fetch First "n" Rows

To fetch the first "n" matching rows, use the FETCH FIRST notation (see page 30).

EMP_NM SELECT * ANSWER

+----------+ FROM emp_nm =========

|ID|NAME | ORDER BY id DESC ID NAME

|--|-------| FETCH FIRST 2 ROWS ONLY; -- ------

|10|Sanders| 50 Hanes

|20|Pernal | 20 Pernal

|50|Hanes |

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

Figure 10, Fetch first "n" rows example

Another way to do the same thing is to assign row numbers to the output, and then fetch those

rows where the row-number is less than "n" (see page 101).

Fetch Subsequent "n" Rows

To the fetch the "n" through "n + m" rows, first use the ROW_NUMBER function to assign

output numbers, then put the result in a nested-table-expression, and then fetch the rows with

desired numbers (see page 101).

Fetch Uncommitted Data

To retrieve data that may have been changed by another user, but which they have yet to

commit, use the WITH UR (Uncommitted Read) notation.

EMP_NM SELECT * ANSWER

+----------+ FROM emp_nm ==========

|ID|NAME | WHERE name like 'S%' ID NAME

|--|-------| WITH UR; -- -------

|10|Sanders| 10 Sanders

|20|Pernal |

|50|Hanes |

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

Figure 11, Fetch WITH UR example

Using this option can result in one fetching data that is subsequently rolled back, and so was

never valid. Use with extreme care.

Graeme Birchall ©

18 Index of Concepts

Summarize Column Contents

Use a column function (see page 83) 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 189).

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 255), 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 or page 317 for more information about the above.

Hide Complex SQL

One can create a view (see page 20) 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 241) 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.

20 SQL Components

When using the DB2 Command Processor (batch) script, the default statement terminator can

be set using the "-tdx" option, where "x" is the value have chosen.

NOTE: See the section titled Special Character Usage on page 38 for notes on how to re-

fer to the statement delimiter in the SQL text.

Statement Delimiter

DB2 SQL does not come with a designated statement delimiter (terminator), though a semi-

colon is often used. A semi-colon cannot be used when writing a compound SQL statement

(see page 73) because that character is used to terminate the various sub-components of the

statement.

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 17, 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.

剩余442页未读，继续阅读

wxycwfj

粉丝: 0
资源: 1

DB2 SQL Cookbook：Graeme Birchall的数据库查询指南

DB2V91CooKbook工具.pdf

DB2 V9.1 复制技术新特性及改进展示

建荣A602328/602329量产工具v91最新

Toad for DB2 安装

kettle连接db2jar包

toad db2 freeware

pythoncookbook

springboot中连接DB2

DBeaver 下载db2驱动报错

db2数据库的分组聚合

最新资源