处理 DB2 数据数据操纵语言（Data Manipulation Language，DML）

SELECT 语句用来检索表或查看数据。最简单形式的 SELECT 语句可以用来检索一个表中的所有数据。例如，要从 SAMPLE 数据库中检索所有 STAFF 数据，应该发出以下命令：

SELECT * FROM staff

下面是这个查询返回的部分结果集：

ID	NAME	DEPT	JOB	YEARS	SALARY	COMM
10	Sanders	20	Mgr	7	18357.50	-
20	Pernal	20	Sales	8	18171.25	612.45
30	Marenghi	38	Mgr	5	17506.75	-

要限制结果集中行的数量，可以使用 FETCH FIRST 子句，例如：

SELECT * FROM staff FETCH FIRST 10 ROWS ONLY

可以通过指定选择列表 从表中检索特定的列，选择列表由逗号分隔的列名组成。例如：

SELECT name, salary FROM staff

使用 DISTINCT 子句消除结果集中的重复行。例如：

SELECT DISTINCT dept, job FROM staff

使用 AS 子句给选择列表中的表达式或项目分配一个有意义的名称。例如：

SELECT name, salary + comm AS pay FROM staff

如果没有 AS 子句，派生的列会命名为 2，这表示它是结果集中的第二列。

回页首

使用 WHERE 子句和谓词限制查询返回的数据量

使用 WHERE 子句指定一个或多个搜索标准（即搜索条件），从而从表或视图选择特定的行。搜索条件 由一个或多个谓词组成。谓词指定关于行的某一情况（参见 SQL 的组成部分）。在构建搜索条件时，要确保：

算术操作只应用于数字数据类型
只在可比较的数据类型之间进行比较
将字符值包围在单引号中
字符值应该指定为与数据库中的值完全一样

我们来看几个示例。

寻找工资超过 $20,000 的职员的姓名：
"SELECT name, salary FROM staff WHERE salary > 20000"
将语句包围在双引号中，可以防止操作系统错误地解释特殊字符，比如 * 或 >；如果不这么做，大于号会被解释为输出重定向请求。

列出工资超过 $20,000 的不是经理的职员的姓名、头衔和工资：

"SELECT name, job, salary FROM staff
                WHERE job <> 'Mgr'
                AND salary > 20000"

寻找以字母 S 开头的所有姓名：
SELECT name FROM staff WHERE name LIKE 'S%'
在这个示例中，百分号（%）是一个通配符，代表零个或多个字符的字符串。

子查询（subquery） 是主查询的 WHERE 子句中出现的 SELECT 语句，它将结果集提供给 WHERE 子句。例如：

"SELECT lastname FROM employee
            WHERE lastname IN
            (SELECT sales_person FROM sales
            WHERE sales_date < '01/01/1996')"

相关名称（correlation name） 是在查询的 FROM 子句中定义的，可以作为表的简短名称。相关名称还可以消除对来自不同表的相同列名的二义性引用。例如：

"SELECT e.salary FROM employee e
            WHERE e.salary <
            (SELECT AVG(s.salary) FROM staff s)"

回页首

使用 ORDER BY 子句对结果进行排序

使用 ORDER BY 子句按照一个或多个列中的值对结果集进行排序。ORDER BY 子句中指定的列名不一定在选择列表中指定。例如：

"SELECT name, salary FROM staff
            WHERE salary > 20000
            ORDER BY salary"

在 ORDER BY 子句中指定 DESC 可以对结果集进行降序排序：

ORDER BY salary DESC

回页首

使用联结从多个表中检索数据

联结（join） 是一种将来自两个或更多表中的数据组合起来的查询。常常需要从两个或更多的表中选择信息，因为所需的数据常常是分散的。联结将列添加到结果集中。例如，对两个具有三列的表进行完全联结，会产生具有六列的结果集。

最简单的联结中没有指定条件。例如：

SELECT deptnumb, deptname, manager, id, name, dept, job
            FROM org, staff

这个语句从 ORG 表和 STAFF 表返回列的所有组合。前三列来自 ORG 表，后四列来自 STAFF 表。这样的结果集（两个表的叉积（cross product））没什么用处。需要用一个联结条件（join condition） 来调整结果集。例如，下面这个查询标识出那些是经理的职员：

SELECT deptnumb, deptname, id AS manager_id, name AS manager
            FROM org, staff
            WHERE manager = id
            ORDER BY deptnumb

下面是这个查询返回的部分结果集：

DEPTNUMB	DEPTNAME	MANAGER_ID	MANAGER
10	Head Office	160	Molinare
15	New England	50	Hanes
20	Mid Atlantic	10	Sanders

前面的语句是一个内部联结的示例。内部联结（inner join） 只返回叉积中满足联结条件的行。如果一行在一个表中存在，但是在另一个表中不存在，它就不包含在结果集中。要显式地指定内部联结，可以重新编写前面的查询，在 FROM 子句中添加 INNER JOIN 操作符：

...
            FROM org INNER JOIN staff
            ON manager = id
            ...

关键字 ON 为进行联结的表指定联结条件。DeptNumb 和 DeptName 是 ORG 表中的列，而 Manager_ID 和 Manager 基于 STAFF 表中的列（ID 和 Name）。在内部联结的结果集中，行的值分别匹配左表（ORG）以及右表（STAFF）中的 Manager 和 ID 列。（在两个表上执行联结时，可以任意指定一个表为左表，另一个表为右表。）

外部联结（Outer join） 返回内部联结操作产生的行，加上内部联结操作不会返回的行。有三种类型的外部联结：

左外部联结 包括内部联结，加上左 表中内部联结操作不会返回的行。这种联结在 FROM 子句中使用 LEFT OUTER JOIN（或 LEFT JOIN）操作符。
右外部联结 包括内部联结，加上右 表中内部联结操作不会返回的行。这种联结在 FROM 子句中使用 RIGHT OUTER JOIN（或 RIGHT JOIN）操作符。
完全外部联结 包括内部联结，加上左表和右表 中内部联结操作不会返回的行。这种联结在 FROM 子句中使用 FULL OUTER JOIN（或 FULL JOIN）操作符。

回答更复杂的问题需要构造更复杂的查询。下面的查询生成负责项目的职员的列表，通过列出他们管理的部门标识出是经理的那些职员：

SELECT empno, deptname, projname
            FROM (employee
            LEFT OUTER JOIN project
            ON respemp = empno)
            LEFT OUTER JOIN department
            ON mgrno = empno

第一个外部联结获得职员负责的任何项目的名称；这个外部联结包围在圆括号中并首先被解析。第二个外部联结获得是经理的职员的部门名称。

回页首

使用 UNION 集合操作符将两个或更多的查询组合成一个查询

使用 UNION 操作符、EXCEPT 或 INTERSECT 将两个或更多的查询组合成一个查询。集操作符 对查询的结果进行处理、消除重复并返回最终的结果集。

UNION 操作符将两个或更多的结果表组合在一起，生成一个结果表。
EXCEPT 集合操作符生成的结果表中包含第一个查询返回的所有行，但是去掉了第二个或任何后续查询返回的行。
INTERSECT 集合操作符生成的结果表只包含所有查询都返回的行。

下面是一个使用 UNION 集合操作符的查询示例。同样的查询可以使用 EXCEPT 或 INTERSECT 集合操作符替代关键字 UNION。

"SELECT sales_person FROM sales
            WHERE region = 'Ontario-South'
            UNION
            SELECT sales_person FROM sales
            WHERE sales > 3"

回页首

使用 GROUP BY 子句对结果进行汇总

使用 GROUP BY 子句对结果集中的行进行组织。每个组在结果集中由一行表示。例如：

SELECT sales_date, MAX(sales) AS max_sales FROM sales
            GROUP BY sales_date

这个语句从 SALES 表中返回销售日期的列表。SAMPLE 数据库中的 SALES 表包含销售数据，包括特定销售人员在特定日期完成的成功交易的数量。通常在每天有多个记录。GROUP BY 子句按日期对数据进行分组，这个示例中的 MAX 函数返回每个销售日期记录的最大销售数量。

另一个风格的 GROUP BY 子句要指定 GROUPING SETS 子句。分组集 可以用来在一遍中分析不同聚合层次上的数据。例如：

SELECT YEAR(sales_date) AS year, region, SUM(sales) AS tot_sales
            FROM sales
            GROUP BY GROUPING SETS (YEAR(sales_date), region, () )

在这里，YEAR 函数用来返回日期值的年份部分，SUM 函数用来返回每个分组的销售区间的总量。分组集列表 指定如何对数据进行分组，即聚合。在分组集列表中添加一对空的圆括号，可以获得结果集中的总量。这个语句返回以下结果：

YEAR	REGION	TOT_SALES
-	-	155
-	Manitoba	41
-	Ontario-North	9
-	Ontario-South	52
-	Quebec	53
1995	-	8
1996	-	147

如果一个语句与前面的语句几乎相同，但是指定 ROLLUP 子句或 CUBE 子句而不是 GROUPING SETS 子句，那么它返回的结果集会提供更详细的数据透视图。它可以根据位置或时间进行汇总。

HAVING 子句常常与 GROUP BY 子句一起使用，从而检索出满足特定条件的组的结果。HAVING 子句可以包含一个或多个谓词，将组的某一属性与组的另一个属性或常量进行比较。例如：

"SELECT sales_person, SUM(sales) AS total_sales FROM sales
            GROUP BY sales_person
            HAVING SUM(sales) > 25"

这个语句返回销售总量超过 25 的销售人员的列表。

回页首

使用 INSERT 语句在表或视图中添加新的行

INSERT 语句用来在表或视图中添加新的行。在视图中插入一个新行也会在视图基于的表中插入这一行。

使用 VALUES 子句为一行或多行指定列数据。例如：

INSERT INTO staff VALUES (1212,'Cerny',20,'Sales',3,90000.00,30000.00)
                INSERT INTO staff VALUES (1213,'Wolfrum',20,'Sales',2,90000.00,10000.00)

下面的语句是等效的：

INSERT INTO staff (id, name, dept, job, years, salary, comm)
                VALUES
                (1212,'Cerny',20,'Sales',3,90000.00,30000.00),
                (1213,'Wolfrum',20,'Sales',2,90000.00,10000.00)

可以指定完全选择来标识出要从其他表或视图复制的数据。完全选择（fullselect） 是产生结果表的语句。例如：

CREATE TABLE pers LIKE staff
                INSERT INTO pers
                SELECT id, name, dept, job, years, salary, comm
                FROM staff
                WHERE dept = 38

回页首

使用 UPDATE 语句修改表或视图中的数据

UPDATE 语句用来修改表或视图中的数据。通过指定 WHERE 子句，可以修改满足条件的每一行的一个或多个列的值。例如：

UPDATE staff
            SET dept = 51, salary = 70000
            WHERE id = 750

下面的语句是等效的：

UPDATE staff
            SET (dept, salary) = (51, 70000)
            WHERE id = 750

如果没有指定 WHERE 子句，DB2 就会更新表或视图中的每一行！

回页首

使用 DELETE 语句删除数据

DELETE 语句用来从表中删除整行的数据。通过指定 WHERE 子句，删除满足条件的每一行。例如：

DELETE FROM staff
            WHERE id IN (1212, 1213)

如果没有指定 WHERE 子句，DB2 就会删除表中的所有行！

回页首

使用 MERGE 语句将有条件更新、插入或删除操作组合起来

MERGE 语句使用来自源表的数据更新目标表或可更新视图。仅仅用一个操作，目标表中与源表匹配的行就可以被更新或删除，目标表中不存在的行被插入。

例如，将 EMPLOYEE 表作为目标表，其中包含某大公司的职员的最新信息。分支办公室通过维护自己的 EMPLOYEE 表版本 MY_EMP 来处理本地职员记录的更新。可以使用 MERGE 语句用 MY_EMP 表（合并操作的源表）中包含的信息来更新 EMPLOYEE 表。

以下语句将编号为 000015 的新职员的行插入 MY_EMP 表。

INSERT INTO my_emp (empno, firstnme, midinit, lastname, workdept,
            phoneno, hiredate, job, edlevel, sex, birthdate, salary)
            VALUES ('000015', 'MARIO', 'M', 'MALFA', 'A00',
            '6669', '05/05/2000', 'ANALYST', 15, 'M', '04/02/1973', 59000.00)

并发出以下语句更新 MY_EMP 表中现有职员 000010 的工资数据。

INSERT INTO my_emp (empno, firstnme, midinit, lastname, edlevel, salary)
            VALUES ('000010', 'CHRISTINE', 'I', 'HAAS', 18, 66600.00)

现在，插入的数据只存在于 MY_EMP 表中，因为它还没有与 EMPLOYEE 表进行合并。下面的 MERGE 语句获取 MY_EMP 表的内容并将它们合并到 EMPLOYEE 表中。

MERGE INTO employee AS e
            USING (SELECT
            empno, firstnme, midinit, lastname, workdept, phoneno,
            hiredate, job, edlevel, sex, birthdate, salary
            FROM my_emp) AS m
            ON e.empno = m.empno
            WHEN MATCHED THEN
            UPDATE SET (salary) = (m.salary)
            WHEN NOT MATCHED THEN
            INSERT (empno, firstnme, midinit, lastname, workdept, phoneno,
            hiredate, job, edlevel, sex, birthdate, salary)
            VALUES (m.empno, m.firstnme, m.midinit, m.lastname,
            m.workdept, m.phoneno, m.hiredate, m.job, m.edlevel,
            m.sex, m.birthdate, m.salary)

给源表和目标表都分配了相关名称，以避免搜索条件中的表引用产生二义性。这个语句指定了 MY_EMP 表中应该考虑的列。语句还指定当 MY_EMP 中的行在 EMPLOYEE 表中有匹配时或没有匹配时，应该采取什么操作。

现在，对 EMPLOYEE 表执行以下查询会返回职员 000015 的记录：

SELECT * FROM employee WHERE empno = '000015'

以下查询会返回职员 000010 的记录，其中 SALARY 列的值是更新后的值：

SELECT * FROM employee WHERE empno = '000010'

回页首

使用数据修改-表引用子句在同一个工作单元中获得中间结果集

假设您想在同一个工作单元（UOW）中给职员 000220 加薪 7% 并检索她原来的工资。可以使用 数据修改-表引用 子句来实现，这个子句是 SQL 语句中 FROM 子句的一部分。

SELECT salary FROM OLD TABLE (
            UPDATE employee SET salary = salary * 1.07
            WHERE empno = '000220'
            );
            SALARY
            -----------
            29840.00
            1 record(s) selected.

数据修改操作（插入、更新或删除）的目标中的列变成中间结果表中的列，可以在查询的选择列表中按名称引用这些列（在这个示例中是 Salary）。关键字 OLD TABLE 指定中间结果表应该包含数据修改操作之前的值。关键字 NEW TABLE 指定中间结果表应该包含数据修改操作之后（在发生引用完整性计算和触发操作后触发器之前）的值。关键字 FINAL TABLE 指定中间结果表应该包含数据修改操作、引用完整性计算和触发操作后触发器之后的值。

假设有一个 CUSTOMERS 表，其定义为：

CREATE TABLE customers (
            cust_id INTEGER GENERATED ALWAYS AS IDENTITY (
            START WITH 10001
            ),
            cust_name VARCHAR(12),
            PRIMARY KEY (cust_id)
            );

这个表的主键 Cust_ID 是自动生成的标识列。可以使用 数据修改-表引用 子句检索生成的标识列值，这个值用作顾客编号。

SELECT * FROM FINAL TABLE (
            INSERT INTO customers (cust_name) VALUES ('Lamarr')
            );
            CUST_ID     CUST_NAME
            ----------- ------------
            10001 Lamarr
            1 record(s) selected.

posted @ 2009-02-27 10:59 tiasys 阅读(493) 评论(0) 编辑收藏举报

刷新页面返回顶部

tiasys's blog

处理 DB2 数据数据操纵语言（Data Manipulation Language，DML）

公告

tiasys's blog

处理 DB2 数据 数据操纵语言（Data Manipulation Language，DML）

公告

处理 DB2 数据数据操纵语言（Data Manipulation Language，DML）