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 XML:namespace prefix = o ns = “urn:schemas-microsoft-com:Office:office” /> ‘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 caseConvert = 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: when ’system’ then ‘管理员’ when ‘roletype’ then ‘特殊角色’ else ‘普通用户’ 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 -w ibmdb2 -e -o putsq.db2 ■ 查询系统表 select count(*) from SYSCAT.TABLES where TABSCHEMA=’DB2INST1’ select count(*) from SYSCAT.PROCEDURES where PROCSCHEMA=’DB2INST1’ |