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 rownum

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 lin