MySQL和Oracle对比学习之数据字典

mysql与oracle在查询数据字典以及mysql和sqlplus工具的使用上面还是有不少区别的：

1.查看当前数据库：

mysql> select database();
+------------+
| database() |
+------------+
| mysql      |
+------------+
1 row in set (0.00 sec)

oracle查询数据库的名字有多种方法，可以从系统参数和数据库级、实例级和内置函数中获得：

SQL> show parameter instance_name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      orcl
SQL> select name from v$database;

NAME
---------
ORCL

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
orcl

SQL>  select sys_context('USERENV','instance_name') from dual;

SYS_CONTEXT('USERENV','INSTANCE_NAME')
--------------------------------------------------------------------------------
orcl

2.得到创建数据库的脚本 

mysql> show create database mysql;
+----------+------------------------------------------------------------------+
| Database | Create Database                                                  |
+----------+------------------------------------------------------------------+
| mysql    | CREATE DATABASE `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)

oracle中创建数据库的脚本可以从控制文件中提取。

3.查看当用的用户

mysql不是show user，而是select user();oracle是show user以及内置函数中可以获得

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.05 sec)

SQL> show user;

USER is "L5M"
SQL> select sys_context('USERENV','current_user') from dual;

SYS_CONTEXT('USERENV','CURRENT_USER')
--------------------------------------------------------------------------------
L5M

4.查看当前表的信息

mysql> show tables; 
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
...
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
33 rows in set (0.00 sec)

 oracle可以从内置视图和数据字典中提取：

SQL> select * from cat where rownum<3;

TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
ACCESS_DETAIL                  TABLE
ALL_DB_PARAMETERS              TABLE

SQL> select * from tab where rownum<3;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
ACCESS_DETAIL                  TABLE
ALL_DB_PARAMETERS              TABLE

SQL> select table_name from user_tables where rownum<3;

TABLE_NAME
------------------------------
BATCH_VENDOR_PROGRAM
BATCH_TACTICVISION

5.查看表结构，终于有一个操作是通用的了，都是用desc。不过oracle另外还可以从all_tab_cols视图查询

6.查看建表语句

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

oracle是通过内置函数获得的，这个sql是得到所有非系统用户的脚本。

SELECT  'SELECT dbms_metadata.get_ddl(''USER'', '''||USERNAME||''') FROM dual; 'FROM DBA_USERS WHERE DEFAULT_TABLESPACE NOT IN ('SYSTEM','SYSAUX','TEMP','TOOLS','DRSYS','INDX','XDB','USERS') ORDER BY USER_ID;

6.查看表的索引

mysql> create index i_t1 on t1(c1);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1    |          1 | i_t1     |            1 | c1          | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

mysql> show index from t1 \G
*************************** 1. row ***************************
        Table: t1
   Non_unique: 1
     Key_name: i_t1
 Seq_in_index: 1
  Column_name: c1
    Collation: A
  Cardinality: 0

oracle中是通过user_indexes中查询得到的。

7.查询数据库所有user

mysql> grant select,insert on *.* to abc identified by 'abc';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select user,host from user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| abc           | %         |
| root          | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
5 rows in set (0.00 sec)

oracle是通过系统视图来查找的

SQL> select username from all_users;

USERNAME
------------------------------
PRO
MRI
NPM

8.查看进程的信息

mysql> show processlist
    -> ;
+----+------+-----------+-------+---------+------+----------+------------------+
| Id | User | Host      | db    | Command | Time | State    | Info             |
+----+------+-----------+-------+---------+------+----------+------------------+
|  4 | root | localhost | mysql | Query   |    0 | starting | show processlist |
+----+------+-----------+-------+---------+------+----------+------------------+
1 row in set (0.00 sec)

oracle是通过 v$session,v$processe来查看的。

9.查看数据字典信息

mysql> use information_schema;
Database changed
mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| ENGINES                               |
| EVENTS                                |
| FILES                                 |
| GLOBAL_STATUS                         |
| GLOBAL_VARIABLES                      |
| KEY_COLUMN_USAGE                      |
| OPTIMIZER_TRACE                       |
| PARAMETERS                            |
| PARTITIONS                            |
| PLUGINS                               |
| PROCESSLIST                           |
| PROFILING                             |
| REFERENTIAL_CONSTRAINTS               |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| SESSION_STATUS                        |
| SESSION_VARIABLES                     |
| STATISTICS                            |
| TABLES                                |
| TABLESPACES                           |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |
| INNODB_LOCKS                          |
| INNODB_TRX                            |
| INNODB_SYS_DATAFILES                  |
| INNODB_FT_CONFIG                      |
| INNODB_SYS_VIRTUAL                    |
| INNODB_CMP                            |
| INNODB_FT_BEING_DELETED               |
| INNODB_CMP_RESET                      |
| INNODB_CMP_PER_INDEX                  |
| INNODB_CMPMEM_RESET                   |
| INNODB_FT_DELETED                     |
| INNODB_BUFFER_PAGE_LRU                |
| INNODB_LOCK_WAITS                     |
| INNODB_TEMP_TABLE_INFO                |
| INNODB_SYS_INDEXES                    |
| INNODB_SYS_TABLES                     |
| INNODB_SYS_FIELDS                     |
| INNODB_CMP_PER_INDEX_RESET            |
| INNODB_BUFFER_PAGE                    |
| INNODB_FT_DEFAULT_STOPWORD            |
| INNODB_FT_INDEX_TABLE                 |
| INNODB_FT_INDEX_CACHE                 |
| INNODB_SYS_TABLESPACES                |
| INNODB_METRICS                        |
| INNODB_SYS_FOREIGN_COLS               |
| INNODB_CMPMEM                         |
| INNODB_BUFFER_POOL_STATS              |
| INNODB_SYS_COLUMNS                    |
| INNODB_SYS_FOREIGN                    |
| INNODB_SYS_TABLESTATS                 |
+---------------------------------------+
61 rows in set (0.00 sec)