oracle db2 mysql 命令_db2,oracle,mysql常用命令比较

《oracle,db2,mysql类比》作为三种数据库一个类比，目的在于通过类比，了解现在数据库相似点与异同点，同时可以帮助大家在了解一种

数据库，能够迅速地学会其他数据库。初步定义为oracle,db2,mysql三种数据库，以后可能还是追加informix,sysbase,sql

server等数据库。

本篇作为首篇，目的是让大家对这三种数据库常用的知识点有一个感知的认识。

一、常用知识点

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 rownumdb2: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

db2:db2 connect to dbname user user_name using passwd

mysql:use database