SQL Server, Oracle, and DB2 database SQL statement comparison

Reposted from: longtang Bay

1.1.1 obtain the first N records

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 obtain the current date

SQL Server: Select getdate ()

ORACLE: Select sysdate from dual

DB2: Select current timestamp from sysibm. sysdummy1

1.1.3 connection string

SQL Server: Select Hello + toone

ORACLE: Select Hello | toone from dual

DB2: Select Hello | toone from sysimb. sysdummy1

1.1.4 null value conversion

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 type conversion

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

Note 1:

■ Changing the style parameter for the conversion date format in SQL Server: 20;

■ In Oracle, the format of the conversion date changes the formatting parameter: yyyy-mm-dd hh24: MI: SS 'yyyy', 'mm', 'dd', 'hh12', 'hh24', 'mi', and 'ss;

■ Changing the date format in DB2: Current timestamp 'current date', 'current time', and so on;

NOTE 2:

■ Changing the Data Type of SQL Server to "data type parameters": int and varchar;

■ Oracle Data Type Change functions: to_char (), to_date (), to_number (), and so on;

■ DB2 data type change functions: varchar (), INT (), date (), time (), etc;

1.1.6 value judgment

SQL Server: Select case convert = case when G. master_type = system then Administrator when G. master_type = roletype then special role else normal user end from global_code g

ORACLE: Select case G. master_type when system then Administrator when roletype then special role else common user end as caseconvert from global_code g

DB2: Select case G. master_type when system then Administrator when roletype then special role else common user end as caseconvert from global_code g

1.1.7 location 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 other functions SQL Server Oracle DB2

Length Len () length ()

Substring () substr ()

1.2

Appendix

1.2.1 db2v8.

1 Common commands

■ Create a database and execute DB2 create dB oatemp on the server

■ Delete the database and execute DB2 drop dB oatemp on the server

■ Create a tablespace DB2 create user temporary tablespace userspace1 managed by system using (userspace1)

■ Start database switch user Su-db2inst1 start Database: db2start

■ Shut down database switch user Su-db2inst1 shut down database db2stop [force]

■ Create a remote management node and enter the DB2 command window: DB2 catalog TCPIP node asnode remote 10.1.22.176 server 50000 DB2 catalog dB oadb2 as oadb2 at node asnode

■ Connect to the database DB2 connect to oadb2 user db2inst1 using ibmdb2

■ Shut down database connection DB2 terminate

■ Execute the script DB2-Td! -VF ioa2.db2-Z info. Log

■ Export script db2look-D oadb2-I db2inst1-W ibmdb2-e-o putsql. DB2-T auth_user db2look-D oadb2-I db2inst1-wibmdb2-e-o putsq. DB2

■ View the port netstat-A in use

■ Query System Table select count (*) from syscat. tables where tabschema = 'db2inst1' select count (*) from syscat. procedures where procschema = 'db2inst1'

■ Common command DB2 select drop talbe | tabname from syscat. tables where T abschema = db2inst1 and type = T> db2droptables. DB2 DB2 select drop View | tabname from syscat. tables where T abschema = db2inst1 and type = V> db2dropviews. DB2 DB2-VF db2droptables. DB2-Z info. log DB2 select username from auth_user fetch first 3 rows only

■ Start JDBC db2jstrt JDBC Applet Server 6789.

1.1 precautions for database migration

1.1.1 obtain the first N records

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 obtain the current date

SQL Server: Select getdate ()

ORACLE: Select sysdate from dual

DB2: Select current timestamp from sysibm. sysdummy1

1.1.3 connection string

SQL Server: Select 'hello' + 'toone'

ORACLE: Select 'hello' | 'toone' from dual

DB2: Select 'hello' | 'toone' from sysimb. sysdummy1

1.1.4 null value conversion

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 type conversion

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

Note 1:

■ Changing the style parameter for the conversion date format in SQL Server: 20;

■ In Oracle, the format of the conversion date changes the formatting parameter: yyyy-mm-dd hh24: MI: ss xml: namespace prefix = o ns = "urn: Schemas-Microsoft-com: Office: office "/>

'Yyyy', 'mm', 'dd', 'hh12', 'hh24', 'mi', and 'ss;

■ Changing the date format in DB2: Current Timestamp

'Current date', 'current time', etc;

NOTE 2:

■ Changing the Data Type of SQL Server to "data type parameters": int and varchar;

■ Oracle Data Type Change functions: to_char (), to_date (), to_number (), and so on;

■ DB2 data type change functions: varchar (), INT (), date (), time (), etc;

1.1.6 value judgment

SQL Server:

Select caseconvert =

Case when G. master_type = 'system' then 'admin'

When G. master_type = 'Role type' then' special role'

Else 'normal users'

End

From global_code g

ORACLE:

Select case G. master_type

When 'system' then' administrator'

When 'Role type' then' special role'

Else 'normal users'

End

As caseconvert

From global_code g

DB2:

Select case G. master_type

When 'system' then' administrator'

When 'Role type' then' special role'

Else 'normal users'

End

As caseconvert

From global_code g

1.1.7 location

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 other functions

SQL Server Oracle DB2

Length Len () length ()

Substring () substr ()

1.2 attachment

1.2.1 Common commands for db2v8.1

■ Create a database and execute DB2 create dB oatemp on the server

■ Delete the database and execute DB2 drop dB oatemp on the server

■ Create a tablespace

DB2 "create user temporary tablespace userspace1 managed by system using ('userspace1 ')"

■ Start database switch user Su-db2inst1

Start Database: db2start

■ Shut down database switch user Su-db2inst1 shut down database db2stop [force]

■ Create a remote management node and enter the DB2 command window

DB2 catalog TCPIP node asnode remote 10.1.22.176 server 50000

DB2 catalog dB oadb2 as oadb2 at node asnode

■ Connect to the database

DB2 connect to oadb2 user db2inst1 using ibmdb2

■ Close database connection

DB2 terminate

■ Execute scripts

DB2-Td! -VF ioa2.db2-Z info. Log

■ Export scripts

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

■ View the port in use

Netstat-

■ Query system table

Select count (*) from syscat. tables where tabschema = 'db2inst1' select count (*) from syscat. procedures where procschema = 'db2inst1'