当前位置:首页 > 开发 > 数据库 > 正文

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

发表于: 2012-02-06   作者:czmmiao   来源:转载   浏览次数:
摘要: 1、查看前3行数据,列别名如果没有AS 子句,派生的列会命名为 2,这表示它是结果集中的第二列。 db2 => select name,salary+comm from staff fetch first 3 rows only NAME      2       &

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 允许创建双向索引。双向 索引使您不必创建逆向索引,而且它使优化器不需要按逆向对数据进行排序。它还允许高效地获得 MINMAX 函数值。要创建双向索引,应该在 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

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

如有错误,欢迎指正

邮箱:czmcj@163.com

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

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
查找表名: select * from tabs t where lower(t.table_name) like '%service%' select a.TABLE_NAME
创建表空间 CREATE TABLESPACE mydb DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\mydb ' SIZE
交集/差集/合集 select * from tb_a intersect minus union all select * from tb_b 条件分支 decod
交集/差集/合集 select * from tb_a intersect minus union all select * from tb_b 条件分支 decod
  前几天客户提出一个月报,经过了解需求及公式等过程长达20小时,总算基本模型出来了,贴出来啥
目录 1)基本 2)数学函数 3)rownum 4)分页 5)时间处理 6)字符函数 7)to_number 8)聚合函数 9
还是几位读我的书 (《Oracle大型数据库系统在AIX UNIX上的实战详解》) 的读者来邮件咨询问题,很抱
还是几位读我的书 (《Oracle大型数据库系统在AIX UNIX上的实战详解》) 的读者来邮件咨询问题,很抱
写了一个job 很简单的,一分钟内 想一个表内插入 一条时间数据 数据 1 先创建一个表 create table g
Oracle 行转列 的写法: /* DROP TABLE FOO; */ /**创建一张演示的表**/ CREATE TABLE FOO ( BBBID
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号