复制数据到另一个表
1. 问题
你想使用查询语句把一些数据从一个表复制到另一个表里去。该查询语句可能很复杂,也 可能很简单,但你希望最终把数据插入到另一个表。
例如,你希望把 DEPT 表的部分数据复 制到 DEPT_EAST 表。假设 DEPT_EAST 表已经被创建好了,其结构与 DEPT 表相同(有同样的 列和数据类型),而且该表当前不含任何数据。
2. 解决方案
在 INSERT 语句后面附加一个用来检索目标数据的查询语句。
1 insert into dept_east (deptno,dname,loc)
2 select deptno,dname,loc
3 from dept
4 where loc in ( 'NEW YORK','BOSTON' )
3. 讨论
只需在 INSERT 后面附加一个用来检索目标数据的查询语句就可以解决这一问题。
如果你希 望复制表里的全部数据,那就要去掉 WHERE 子句。类似于正常的 INSERT 语句,你也不必明 确指定要插入哪些列。
但是,如果你选择不指明目标列,你就必须为所有列都插入数据, 你也必须注意 SELECT 列表里各列的顺序。
复制表定义
1. 问题
你想创建一个新表,该表和当前已存在的表保持相同的结构定义。例如,你希望为 DEPT 表 创建一个副本,命名为 DEPT_2。但是,你只想复制它的表结构,而不复制数据。
2. 解决方案
DB2使用 CREATE TABLE 语句和 LIKE 子句。
create table dept_2 like dept
Oracle、MySQL 和 PostgreSQL
使用 CREATE TABLE 语句和一个不返回任何数据的子查询。
1 create table dept_2
2 as
3 select *
4 from dept
5 where 1 = 0
SQL Server
使用 SELECT 语句和 INTO 子句,但要保证该查询不返回任何数据。
1 select *
2 into dept_2
3 from dept
4 where 1 = 0
3. 讨论
DB2
DB2 的 CREATE TABLE...LIKE 语句能以现有的表为模板快速创建一个新表。只要把模板表的 名字放在 LIKE 关键字的后面即可。
Oracle、MySQL 和 PostgreSQL
使用 Create Table As Select ( 简写为 CTAS) 语句时,除非为 WHERE 子句指定一个不可能为 真的条件,否则,查询结果集将会被写入新表。本例中,WHERE 子句后面的表达式 1=0 会 导致查询不返回任何结果。因此,上述 CTAS 语句的执行结果就是一张空表,该表的列取决 于 SELECT 子句的查询结果。
SQL Server
使用 INTO 子句复制表定义时,除非为 WHERE 子句指定一个不可能为真的条件,否则的话查 询结果集将会被写入新表。本例中,WHERE 子句后面的表达式 1=0 会导致查询不返回任何 结果。上述语句的执行结果是一张空表,该表的列取决于 SELECT 子句的查询结果。
当相关行存在时更新记录
1. 问题
你想更新一个表的部分行,而更新条件取决于另一个表中是否有与之相关的行。
例如,如 果一个员工出现在 EMP_BONUS 表中,你希望把他的工资(在 EMP 表中)上涨 20%。下面的 结果集显示了 EMP_BONUS 表当前的数据。
select empno, ename from emp_bonus
EMPNO ENAME
---------- ----------
7369 SMITH
7900 JAMES
7934 MILLER
2. 解决方案
在 UPDATE 语句的 WHERE 子句里使用一个子查询来检索同时存在于 EMP 表和 EMP_BONUS 表的 员工。这样 UPDATE 语句就能只检索那些员工的记录,并为其增加 20% 的工资。
3.讨论
上述子查询的结果集代表了 EMP 表中将要被更新的行。IN 谓词用于评估 EMP 表中的 EMPNO 列是否存在于上述子查询返回的 EMPNO 列表里。如果是的话,相应的 SAL 值就会被更新。
除了 IN 谓词,也可以使用 EXISTS。
update emp set sal = sal*1.20
where exists ( select null from emp_bonus where emp.empno=emp_bonus.empno )
你可能会惊讶于 EXISTS 子查询的 SELECT 列表只有一个 Null。不必担心,那个 Null 对更新 操作没有负面影响。我认为这样做反而提高了查询语句的可读性,因为它强调了这样一个 事实:真正决定更新操作的(例如,哪些行会被更新)是子查询里的 WHERE 子句,而不是 SELECT 列表。这与本解决方案里使用的 IN 谓词和子查询不同。