1.1.1 取前n条记录

???????SQL?Server：????? Select top n * from xtable

???????Oracle：????????? Select * from xtable where rownum <=n

?????? DB2：???????????? Select * from xtable fetch first n rows only

1.1.2 取当前日期

?????? SQL Server：????? Select getdate()

?????? Oracle：????????? Select sysdate from dual

?????? DB2：???????????? Select current?? timestamp from sysibm.sysdummy1

1.1.3 连接字符串

SQL Server：??? Select Hello+Toone

Oracle：??????? Select?? Hello||Toone from dual

DB2：?????????? Select?? Hello||Toone from sysimb.sysdummy1

1.1.4 空值转换

?????? SQL Server：????? SELECT?? userid,username,isnull(email,0) FROM AUTH_USER

?????? Oracle：????????? SELECT?? userid,username,nvl(email,0) FROM AUTH_USER

?????? DB2：???????????? SELECT?? userid,username,value(email,0) FROM AUTH_USER

1.1.5 类型转换

?????? SQL Server：????? SELECT convert(varchar,getdate(),20)

?????? Oracle：????????? SELECT?? to_char(sysdate,yyyy-mm-dd hh24:mi:ss) from dual

?????? DB2：???????????? SELECT?? varchar(current timestamp) from sysibm.sysdummy1

?????? 注1：

?? ■SQL?? Server中转换日期格式改变Style参数：20 ；????

?? ■Oracle中转换日期格式改变格式化参数：yyyy-mm-dd?? hh24:mi:ss?? ‘yyyy’、’mm’、’dd’、’hh12’、’hh24’、’mi’、’ss’等；?????

?? ■Db2中转换日期格式改变系统常量：current timestamp ‘current date’、’current?? time’等；

?????? 注2：?????

?? ■SQL Server数据类型改变“数据类型参数”：int、varchar等；?????

?? ■Oracle数据类型改变函数：to_char()、to_date()、to_number()等；?????

?? ■DB2数据类型改变函数：varchar()、int()、date()、time()等；

1.1.6 取值判断

SQL Server：?? select?? case Convert = case when g.master_type =system??? then 管理员???? when g.master_type =roletype then 特殊角色??? else 普通用户??? end from global_code g

Oracle：?????? select?? case g.master_type??? when system??? then 管理员??? when roletype then 特殊角色?? else 普通用户 end??? AS caseConvert from global_code g

DB2：????????? select?? case g.master_type?? when system??? then 管理员?? when roletype then 特殊角色 else 普通用户?? end??? AS caseConvert From global_code g

1.1.7 位置 SQL

?????? Server：????? select charindex(E,ABCDEF) select patindex(%E%,ABCDEF)

?????? Oracle：????? select instr(ABCDEF,E) from dual

?????? DB2：???????? Select locate(E,ABCDEF) from

?????? sysibm.sysdummy1

1.1.8?其他函数????????? SQL Server??? Oracle????? DB2

?? 长度??? Len()??????? Length()??? Length()

?? 取子串 Substring() Substr()??? Substr()??????????????????

1.2??????

?????? 附

1.2.1 DB2V8.

1常用命令

■?? 创建数据库?在服务器上执行????? db2 CREATE DB oatemp

■?? 删除数据库 在服务器上执行????? db2 DROP DB oatemp

■?? 创建表空间 db2 CREATE USER TEMPORARY TABLESPACE USERSPACE1 MANAGED BY SYSTEM USING ( USERSPACE1)

???????? ■?? 启动数据库 切换用户???????? su – db2inst1????? 启动数据库：????????? db2start

???????? ■?? 关闭数据库 切换用户???????? su – db2inst1????? 关闭数据库????????? db2stop [force]

???????? ■?? 创建远程管理节点????? 进入?? DB2命令窗口????? db2 CATALOG TCPIP NODE asnode REMOTE 10.1.22.176 SERVER 50000??? db2 CATALOG DB oadb2 AS oadb2 AT NODE asnode

■?? 连接数据库????????? db2 CONNECT?? TO oadb2 USER db2inst1 USING ibmdb2

???????? ■?? 关闭数据库连接????? db2 terminate

???????? ■?? 执行脚本??????????? db2 -td! -vf ioa2.db2 -z info.log

■?? 导出脚本??????????? db2look -d oadb2 -i db2inst1 -w ibmdb2 -e -o putsql.db2 -t auth_user db2look -d oadb2 -i db2inst1 -wibmdb2