db2,oracle,mysql常用命令比较

一、常用知识点 
1、查看可以登陆的数据库： 
oracle:查看tnsname.ora 或者 echo $ORACLE_SID 
db2: 
mysql:show databases --查看当前数据库：select database() 

2、查看用户表，视图，表索引，表列， 
oracle: 
select table_name from user_tables; 
select view_name from user_views; 
select constraint_name,constraint_type from user_constraints where table_name=''; 
select column_name from all_tab_columns where table_name=''; 
db2: 
list tables or select tabname from syscat.tables; 
select view_name from syscat.views; 
describe indexes for table table_name; 
select tabname from syscat.columns where tabname=''; 
mysql:information_schema 
select table_name from information_schema.tables where table_schema='USER'; or show tables; 
select table_name from information_schema.views where table_schema='USER'; or show table status where comment='view'; 
select constraint_name,constraint_type from information_schedma.table_constraints where table_name=''; 
select index_name,table_name from information_schema.statistics where table_name=''; 
select column_name from information_schedma.columns where table_name='' and table_schema='USER'; 
3、查看表空间 
oracle:select name from v$tablespace 
db2: list tablespaces 
mysql: 
4、查看表结构 
oracle:describe table_name 
db2:describe table table_name 
mysql:describe table_name 
5、取前n行数据 
oracle:select * from table_name where rownum<n 
db2:select * from table_name fetch first n rows only 
mysql:select * from table_name limit n 
6、load数据 
oracle: sqlldr username/passwd control=ctr.ctl data=data.txt 
db2:load from data.txt of del insert into table_name 
mysql:load data local infile 'e:Mysqlmysql.txt' into table test lines terminated by 'rn' 
7、运用sql语句 
oracle:@file.sql 
db2:db2 -tvf file.sql 
mysql: 
8、查看用户 
oracle:select user_name from all_users; 
db2: 
mysql:select schema_name from schemata; 
9、insert date类型 
oracle:insert into table_name (time_id) values (to_date('2009-09-01','YYYY-MM-DD')); 
db2:insert into table_name (time_id) values ('2009-09-01'); 
mysql:insert into table_name (time_id) values ('2009-09-01'); 
10、char to int 
oracle:select to_number('300')+200 from dual; 
db2:select cast(char_id as integer)+200 from table_name; 
mysql: 
11、把表的数据导出到文本 
oracle:spool data.txt ->select * from table_name 
db2:db2" export to "/test.sql" of del select * from test" 
mysql: 
12、查看当前数据库 
oracle:show parameter db_name 
db2:list active databases 
mysql:show databases; 
13、查看数据库目录 
oracle:echo $ORACLE_HOME 
db2:list database derictory 
mysql: 
14、查看数据库系统参数信息 
oracle:show parameter or v$parameter 
db2:get dbm cfg 
mysql: 
15、修改表结构 
oracle:alter table table_name add column_name number 
db2:alter table table_name alter column column_name set data type varchar(12) 
mysql: 
16、导出建表语句 
oracle: select dbms_metadata.get_ddl('TABLE',u.table_name) from user_tables u; 
db2:db2look -d database_name -e -z schedma [-t table_name] -o outfile.sql 
mysql:show create table table_nameG (--查看视图：show create view view_name) 
17、如何恢复已提交的数据 

18、模式，OWNER的区别 
模式 ：一组对象的集合，如表、视图、和序列号等。 
oracle: 
SQL> SHOW USER 
USER is "WGWH" 
SQL> create table etl.wgwh ( id number); 
Table created. 
查看wgwh所属的OWNER： 
SQL> select owner from all_tables where table_name = 'WGWH'; 
OWNER 
------------------------------ 
ETL 
备注：oracle模式由数据库的用户所拥有并且与用户具有相同的名字。其实，在实际使用中模式和用户是一回事。 
oracle查看当前模式与切换模式，相当简单，只需要查看用户或者切换用户。 
db2: 
db2 => select tabschema,owner from syscat.tables where tabname='WGWH' with ur 

TABSCHEMA OWNER 
-------------------------------------------------------------------- ----------------------------------------------------------------------------------- 
ETL ETL 
WGWH ETL 
备注：db2的OWNER区别于oracle的OWNER，即oracle同一个OWNER下，不能存在相同的object，但db2可以，同时增加模式以区分。 
查看当前模式： 
select current schema from sysibm.sysdummy1 
select current schema from sysibm.dual 
或 
select current sqlid from sysibm.sysdummy1 
select current sqlid from sysibm.dual 
或 
values current schema 
values current sqlid　 
切换模式: 
set current schema schedma_name 
mysql: 
19、显示数据库版本 
oracle:select * from product_component_version 
db2: db2level 显示db2的版本号 
mysql:select version() 
20、查看应用程序的端口号： 
oracle--em,isqlplus:$ORACLE_HOME/../../install/protlist.ini 
db2: 
mysql: 
21、应用程序服务： 
oracle:启动EM--emctl start dbconsole 
db2: 
mysql: 
22、重新配置应用程序 
oracle：配置em-- emca -config dbcontrol db 
db2: 
mysql: 
23、查看当前时间 
oracle:select sysdate from dual 
db2: 
mysql:select now() 

24、connect database 

oracle:sqlplus user/passwd@dbname 

db2:db2 connect to dbname user user_name using passwd 

mysql:use database