DB2LUW V9.5 SQL Cookbook by Graeme Birchall

需积分: 9 8 浏览量更新于2024-07-22 收藏 1.34MB PDF 举报

"DB2V95 sql cooke book - 一本关于DB2V9.5的SQL查询实用手册，由Graeme Birchall撰写，包含了丰富的SQL语句示例和解释，适用于Linux、Unix和Windows环境下的DB2数据库系统。" 在《DB2 V9.5 SQL Cookbook》中，作者Graeme Birchall提供了大量的SQL语句实例，旨在帮助用户更好地理解和运用DB2 for Linux, Unix, and Windows (LUW)平台上的SQL语言。这本书不仅适合初学者，也适合有一定经验的DB2数据库管理员和开发人员，通过各种实用的技巧和示例，深入浅出地讲解了SQL在DB2环境中的应用。尽管这本书以DB2 V9.5为焦点，但作者强调，即使你使用的是旧版本的DB2，特别是DB2 for z/OS（大型机版），如果你运行在新功能模式下（如DB2 8或更好的DB2 9），书中很多例子依然具有很高的参考价值。这是因为许多SQL语法和概念在不同版本之间是通用的。书中的内容可能包括但不限于以下知识点： 1. **SQL基本语法**：涵盖了SELECT、INSERT、UPDATE、DELETE等基本操作，以及如何进行数据查询、插入、修改和删除。 2. **数据类型与模式**：介绍DB2支持的数据类型，以及如何创建和管理数据库模式。 3. **表与索引**：包括如何创建、修改和删除表，以及如何设计和优化索引来提高查询性能。 4. **视图与存储过程**：讲解如何创建和使用视图来抽象数据，以及如何编写和调用存储过程来封装复杂的业务逻辑。 5. **联接与子查询**：深入解析不同类型的联接（如内联接、外联接）以及子查询的使用。 6. **聚合函数与分组**：介绍SUM、AVG、COUNT、MAX、MIN等聚合函数，以及GROUP BY和HAVING子句的运用。 7. **事务与并发控制**：讨论事务的概念、ACID属性，以及如何处理并发问题，如死锁和锁定机制。 8. **性能优化**：提供查询优化策略，如使用EXPLAIN分析查询计划，调整查询语句以提高执行效率。 9. **安全性与权限**：阐述如何设置用户权限，管理角色和授权，以确保数据库的安全性。 10. **备份与恢复**：涵盖数据库备份策略，以及在数据丢失或系统故障时如何进行恢复。这本书还包含了一些来自读者、朋友、同事以及IBM员工（非官方）的贡献，他们提供了许多宝贵的建议和案例，使得内容更加丰富和实用。最后，作者提醒读者，虽然他尽力确保信息的准确性，但错误在所难免，因此不应完全依赖书中的内容做决策，特别是在涉及生产环境的操作时。同时，这本书的版权属于Graeme Birchall，未经许可不得复制或分发。《DB2 V9.5 SQL Cookbook》是一本宝贵的参考资料，无论你是刚开始接触DB2还是已经在使用中，都能从中受益匪浅，提升你的SQL技能和DB2管理能力。

Graeme Birchall ©

DB2 V9.5 Cookbook ©

Quick Find 17

Quick Find

This brief chapter is for those who want to find how to do something, but are not sure what

the task is called. Hopefully, this list will identify the concept.

Index of Concepts

Join Rows

To combine matching rows in multiple tables, use a join (see page 223).

EMP_NM EMP_JB SELECT nm.id ANSWER

+----------+ +--------+ ,nm.name ================

|ID|NAME | |ID|JOB | ,jb.job ID NAME JOB

|--|-------| |--|-----| FROM emp_nm nm -- ------- -----

|50|Hanes | +--------+ ORDER BY 1;

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

Figure 1, Join example

Outer Join

To get all of the rows from one table, plus the matching rows from another table (if there are

any), use an outer join (see page 226).

EMP_NM EMP_JB SELECT nm.id ANSWER

+----------+ +--------+ ,nm.name ================

|ID|NAME | |ID|JOB | ,jb.job ID NAME JOB

|--|-------| |--|-----| FROM emp_nm nm -- ------- -----

|50|Hanes | +--------+ ON nm.id = jb.id 50 Hanes -

+----------+ ORDER BY nm.id;

Figure 2, Left-outer-join example

To get rows from either side of the join, regardless of whether they match (the join) or not,

use a full outer join (see page 230).

Null Values - Replace

Use the COALESCE function (see page 136) to replace a null value (e.g. generated in an

outer join) with a non-null value.

Select Where No Match

To get the set of the matching rows from one table where something is true or false in another

table (e.g. no corresponding row), use a sub-query (see page 245).

EMP_NM EMP_JB SELECT * ANSWER

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

|ID|NAME | |ID|JOB | WHERE NOT EXISTS ID NAME

|--|-------| |--|-----| (SELECT * == =====

|50|Hanes | +--------+ ORDER BY id;

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

Figure 3, Sub-query example

Graeme Birchall ©

18 Index of Concepts

Append Rows

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

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 111).

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 277).

If-Then-Else Logic

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

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 307).

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 407).

DB2 V9.5 Cookbook ©

Quick Find 19

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 408).

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 35).

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 112).

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 112).

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.

20 Index of Concepts

Summarize Column Contents

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

SELECT job ANSWER

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

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

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

FROM staff Clerk 15 84766.70 2

WHERE dept < 30 Clerk 20 77757.35 2

AND salary < 90000 Clerk - 162524.05 4

AND job < 'S' Mgr 10 243453.45 3

GROUP BY ROLLUP(job, dept) Mgr 15 80659.80 1

ORDER BY job Mgr - 324113.25 4

,dept; - - 486637.30 8

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 277), 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 be 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 339 for more information about the above.

Hide Complex SQL

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

剩余471页未读，继续阅读

juliet0622

粉丝: 0
资源: 6

DB2LUW V9.5 SQL Cookbook by Graeme Birchall

db2-32位安装包

DB2数据库SQL注入手册1

DB2V81 SQL Cook BOOK.PDF

IBM通用数据库DB2 SQL入门DB2V7SQL

IBM_Universal_Database_DB2_SQL_entry_DB2V7SQL.rar_universal

DB2_V95中文官方文档

AS400_DB2_SQL.rar_AS400 db2 sql mod_as400 db2 da_as400 sql_db2

db2用sql助手生成sql

DB2-SQL.rar_db2

DB2 UDB SQL入门

最新资源