db2与oracle不同的常用sql写法记录(原创)

1、查看前3行数据，列别名如果没有AS 子句，派生的列会命名为 2，这表示它是结果集中的第二列。

db2 => select name,salary+comm from staff fetch first 3 rows only
NAME      2        
--------- ----------
Sanders            -
Pernal      78783.70
Marenghi           -
  3 record(s) selected.

2、db2支持通过“，”分隔在同一个insert语句中插入多个值

db2 => insert into tt values(1),(1)
DB20000I  The SQL command completed successfully.
3、db2没有"create table TABLE_A as select ****"语句，创建表结构一致的表只能通过

db2 => create table pers like staff
DB20000I  The SQL command completed successfully.

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

db2 => update staff set (dept,salary)=(51,70000) where id=150
DB20000I  The SQL command completed successfully.
oraclehedb2都支持下列写法

update tttt set id=3,name='c' where id=1;
5、数据修改操作（插入、更新或删除）的目标中的列变成中间结果表中的列，可以在查询的选择列表中按名称引用这些列

db2 => select salary from old table(update staff set salary=salary*0.2 where id=10)
SALARY  
---------
 19671.50
  1 record(s) selected.

db2 => select salary from old table(update staff set salary=salary*0.2 where id=10)
SALARY  
---------
  3934.30
  1 record(s) selected.

6、事务处理上，db2默认进行自动提交，如需要显示创建事务，则应显示指定"+c"参数，这里应注意，如果指定了事务，即使ddl语句db2也需要提交或者回滚，db2不会显示或隐示的回滚事务。

会话1

$ db2 +c
db2 => connect to sample
   Database Connection Information
 Database server        = DB2/LINUX 9.7.5
 SQL authorization ID   = DB2INST1
 Local database alias   = SAMPLE

db2 => create table czm_1(id int)
DB20000I  The SQL command completed successfully.
db2 => insert into czm_1 values(1)
DB20000I  The SQL command completed successfully.
db2 => savepoint savepoint1 on rollback retain cursors
DB20000I  The SQL command completed successfully.
db2 => insert into czm_1 values(2)
DB20000I  The SQL command completed successfully.
db2 => savepoint savepoint2 on rollback retain cursors
DB20000I  The SQL command completed successfully.
db2 => select * from czm_1
ID        
-----------
          1
          2
  2 record(s) selected.

会话2,执行下面语句无法查询czm_1，hang住不能继续执行

db2 => connect to sample
   Database Connection Information
 Database server        = DB2/LINUX 9.7.5
 SQL authorization ID   = DB2INST1
 Local database alias   = SAMPLE

db2 => select * From czm_1

会话1
db2 => rollback to savepoint savepoint1
DB20000I  The SQL command completed successfully.
db2 => select * from czm_1
ID        
-----------
          1
  1 record(s) selected.

db2 => rollback
DB20000I  The SQL command completed successfully.
db2 => select * from czm_1
SQL0204N  "DB2INST1.CZM_1" is an undefined name.  SQLSTATE=42704

可以看到会话1在执行完rollback语句后，找不到czm_1这个对象了，

会话2 hang住的select语句也执行完毕
db2 => select * from czm_1
SQL0204N  "DB2INST1.CZM_1" is an undefined name.  SQLSTATE=42704
7、通过如下命令执行外部sql脚本

db2 -td@ -vf createSQLproc.db2

db2 命令指定 -td 选项标志，这让命令行处理程序使用 @ 作为语句终止字符（因为在过程体内已经使用分号作为语句终止字符）；-v 选项标志让命令行处理程序将命令文本回显到标准输出；-f 选项标志让命令行处理程序从指定的文件（而不是标准输入）读取命令输入。
8、在指定表空间内创建表

db2 => create table books(bookid integer,bookname varchar(100),isbn char(10)) in userspace1

9、对表列特征的修改
以下语句将 BOOKNAME 列的 DATATYPE 从 VARCHAR(100) 改为 VARCHAR(200)，并将 ISBN 列的可空性改为 NOT NULL：

db2 => alter table books alter bookname set data type char(150) alter isbn set not null
DB20000I  The SQL command completed successfully.
修改表列的限制：
在修改字符串列的长度时，只能增加长度。
在修改列的数据类型时，新的数据类型必须与现有的数据类型兼容。例如，可以将 CHAR 列转换为 VARCHAR 列，但是不能将它们转换为 GRAPHIC 或数字列。数字列可以转换为任何其他数字数据类型，只要新数据类型的长度足以容纳其中的值。例如，可以将 INTEGER 列转换为 BIGINT，但是 DECIMAL(10,2) 列不能转换为 SMALLINT。
固定长度的字符串可以转换为可变长度的字符串，可变长度的字符串也可以转换为固定长度的字符串。例如，CHAR(100) 可以转换为 VARCHAR(150)。对于可变长度的图形字符串也有类似的限制。
表的某些特征不可以更改。例如，不可以修改某些列的数据类型、表驻留的表空间或列的次序。要更改这样的特征，必须保存表数据，删除表，然后重新创建表。

10、列的空值
在默认情况下，列允许空值。如果不想允许空值，可以为列指定 NOT NULL 子句。还可以使用 WITH DEFAULT 子句和一个默认值来指定默认值。使用 GENERATED ALWAYS AS IDENTITY 子句指定由 DB2 生成 BOOKID，而不必让应用程序生成标识符，以下语句令 每个记录生成一个 BOOKID。生成的第一个值是 1，通过在前面的值上加 1 生成后续的值。

db2 => create table books(bookid integer not null generated always as identity(start with 1,increment by 1),bookname varchar(100) with default 'TBD',isbn char(10))

还可以使用 GENERATED ALWAYS 选项让 DB2 自动计算列的值，以下语句把FICTBOOKS 和 NONFICTBOOKS 列相加来计算TOTALBOOKS 列：

db2 => create table authors(authorid integer not null ,fictbooks integer,nonfictbooks integer,totalbooks integer generated always as(fictbooks+nonfictbooks))
11、带检查选项的嵌套视图
在定义嵌套视图时，检查选项可以用于限制操作。但是，还可以指定其他子句来定义如何继承限制。检查选项可以定义为 CASCADED 或 LOCAL。如果没有指定关键字，CASCADED 是默认值。为说明 CASCADED 和 LOCAL 行为的不同，我们来看几个可能的场景。
当用 WITH CASCADED CHECK OPTION 创建视图时，所有针对该视图执行的语句都必须满足视图和所有底层视图的条件 —— 即使那些视图不是带检查选项定义的，也是如此。假设在创建 NONFICTIONBOOKS 时没有带检查选项，也可以使用 CASCADED 关键字在视图 NONFICTIONBOOKS 的基础上创建视图 NONFICTIONBOOKS1：
CREATE VIEW NONFICTIONBOOKS AS
       SELECT * FROM BOOKS WHERE BOOKTYPE = 'N'
CREATE VIEW NONFICTIONBOOKS1 AS
       SELECT * FROM NONFICTIONBOOKS WHERE BOOKID > 100
       WITH CASCADED CHECK OPTION

将不允许下列 INSERT 语句，因为它们不满足其中至少一个视图的条件：

INSERT INTO NONFICTIONBOOKS1 VALUES( 10,..,'N')
INSERT INTO NONFICTIONBOOKS1 VALUES(120,..,'F')
INSERT INTO NONFICTIONBOOKS1 VALUES( 10,..,'F')

但是，会允许下面的 INSERT 语句，因为这两个视图的条件它都满足：

INSERT INTO NONFICTIONBOOKS1 VALUES(120,...,'N')

接下来，假设用 WITH LOCAL CHECK OPTION 在视图 NONFICTIONBOOKS 的基础上创建视图 NONFICTIONBOOKS2。现在，针对这个视图执行的语句只需要满足指定了检查选项的视图的条件：

CREATE VIEW NONFICTIONBOOKS AS
       SELECT * FROM BOOKS WHERE BOOKTYPE = 'N'
CREATE VIEW NONFICTIONBOOKS2 AS
       SELECT * FROM NONFICTIONBOOKS WHERE BOOKID > 100
       WITH LOCAL CHECK OPTION

在这种情况下，将不允许下面的 INSERT 语句，因为它们不满足 NONFICTIONBOOKS2 视图的 BOOKID > 100 这个条件：

INSERT INTO NONFICTIONBOOKS2 VALUES(10,..,'N')
INSERT INTO NONFICTIONBOOKS2 VALUES(10,..,'F')

但是，尽管值 N 不满足 NONFICTIONBOOKS 视图的 BOOKTYPE = 'N' 这个条件，也会允许下面的 INSERT 语句：

INSERT INTO NONFICTIONBOOKS2 VALUES(120,..,'N')
INSERT INTO NONFICTIONBOOKS2 VALUES(120,..,'F')

12、DB2上的索引

在默认情况下，索引按升序创建，但也可以创建降序索引。甚至可以为索引中的各个列指定不同的顺序。下面的语句在 AUTHORID 和 BOOKNAME 列上定义一个索引。AUTHORID 列的值按降序排序，在同一个 AUTHORID 值中 BOOKNAME 列的值按升序排序：

db2 => create index i2bookname on books(bookid desc,isbn asc)

在数据库中创建索引时，按照指定的顺序存储键。索引要求数据处于指定的顺序，从而帮助提高查询的性能。升序索引还用于确定 MIN 列函数的结果；降序索引用于确定 MAX 列函数的结果。如果应用程序还需要数据按与索引相反的顺序排序，那么 DB2 允许创建双向索引。双向 索引使您不必创建逆向索引，而且它使优化器不需要按逆向对数据进行排序。它还允许高效地获得 MIN 和 MAX 函数值。要创建双向索引，应该在 CREATE INDEX 语句中指定 ALLOW REVERSE SCANS 选项：

db2 => create index bookname on books(bookname) allow reverse scans

DB2 不允许创建具有相同定义的多个索引。即使对于为支持主键或惟一性约束而隐式创建的索引，这一点也适用。所以，既然 BOOKS 表已经有了一个在 BOOKID 列上定义的主键，那么尝试在 BOOKID 列上创建索引将失败。

创建一个索引花费的时间比较长。DB2 必须读取每一行来提取键，对这些键进行排序，然后将键值列表写到数据库中。如果表比较大，那么将使用临时表空间对键进行排序。

索引存储在表空间中。如果表驻留在数据库管理的表空间中，就可以选择将索引放在不同的表空间中。 在创建表时，使用 INDEX IN 子句定义这一点。表索引的位置在创建表时设置，除非删除并重新创建表，否则无法改变索引的位置。

DB2 还提供了DROP INDEX SQL 语句从数据库中删除索引。索引是无法修改的。如果需要更改索引，例如向键中添加另一个列，必须删除并重新创建该索引。

聚集索引

在每个表上，可以将一个索引创建为聚集索引。如果常常以某一次序引用表数据，那么聚集索引比较有用定义数据在数据库中存储的次序。在插入期间，DB2 会试图将新的行放置得靠近有相似键的行。这样的话，在查询以聚集索引序列请求数据期间，可以更快地检索数据。

要将索引创建为聚集索引，应该在 CREATE INDEX 语句上指定 CLUSTER 子句：

CREATE INDEX IAUTHBKNAME ON BOOKS (AUTHORID,BOOKNAME) CLUSTER

这个语句在 AUTHORID 和 BOOKNAME 列上创建一个索引，并将其作为聚集索引。如果编写的查询要求列出作者及其所写的所有书籍，这个索引会提高查询的性能。

在索引中使用包含的列

在创建索引时，可以选择包含额外的列数据，这些额外的列数据将与键存储在一起，但实际上它们不是键本身的一部分， 所以不被排序。在索引中包含额外列的主要原因是为了提高某些查询的性能：因为索引页面中已经提供了数据值，DB2 就不需要访问数据页面。只能为惟一索引定义包含的列。但是，在强制实施索引的惟一性时不考虑包含的列。

假设我们经常需要获得按 BOOKID 排序的书名列表。查询将如下所示：

SELECT BOOKID,BOOKNAME FROM BOOK ORDER BY BOOKID

下面的语句会创建一个可能提高性能的索引：

CREATE UNIQUE INDEX IBOOKID ON BOOKS (BOOKID) INCLUDE(BOOKNAME)

这样的话，查询结果所需的所有数据都出现在索引中，不需要检索数据页面。

参考至:http://www.ibm.com/developerworks/cn/education/data/db2-cert7305/section6.html

本文原创，转载请注明出处、作者

如有错误，欢迎指正

邮箱:[e-mail]

评论

1 楼 惢雨 2013-10-12  

[align=left][/align][size=xx-small][/size]

[img][/img]

引用

[flash=200,200][/flash][/url][url][/url][url][img][/img]

[*]

引用

[u][/u][i][/i][b][/b][/size][size=x-small][/size][size=small][/size][size=medium][/size][size=x-large][size=xx-large][/size]