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
子句指定一个或多个搜索标准(即搜索条件),从而从表或视图选择特定的行。搜索条件 由一个或多个谓词组成。谓词指定关于行的某一情况(参见 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
子句中指定的列名不一定在选择列表中指定。例如:
"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
子句对结果集中的行进行组织。每个组在结果集中由一行表示。例如:
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
语句用来在表或视图中添加新的行。在视图中插入一个新行也会在视图基于的表中插入这一行。
- 使用
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
语句用来修改表或视图中的数据。通过指定 WHERE
子句,可以修改满足条件的每一行的一个或多个列的值。例如:
UPDATE staff SET dept = 51, salary = 70000 WHERE id = 750 |
下面的语句是等效的:
UPDATE staff SET (dept, salary) = (51, 70000) WHERE id = 750 |
如果没有指定 WHERE
子句,DB2 就会更新表或视图中的每一行!
|
DELETE
语句用来从表中删除整行的数据。通过指定 WHERE
子句,删除满足条件的每一行。例如:
DELETE FROM staff WHERE id IN (1212, 1213) |
如果没有指定 WHERE
子句,DB2 就会删除表中的所有行!
|
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. |