MySQL和Oracle雖然在架構上有很大的不同，但是如果從某些方面比較起來，它們有些方面也是相通的。
畢竟學習的主線是MySQL，所以會從MySQL的角度來對比Oracle的一些功能。大體總結了以下的內容，歡迎大家拍磚，
檢視當前的資料庫名
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| test   |
+------------+
1 row in set (0.00 sec)

+++ Oracle的實現方法 ++++
因為架構的不同，所以列舉了資料庫，例項級的查詢方法。
方法一，透過資料庫引數來檢視
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
instance_name string TRUABP4

方法二：透過資料字典來檢視
資料庫級
SQL> select name from v$database;
NAME
---------------------------
TRUABP4

例項級
SQL> select instance_name from v$instance;
INSTANCE_NAME
------------------------------------------------
TRUABP4
方法三：透過內建函式來實現，這種方法相比前兩種更為通用。
SQL> select sys_context('USERENV','instance_name') from dual;
SYS_CONTEXT('USERENV','INSTANCE_NAME')
----------------------------------------------------
TRUABP4

得到資料庫建立的指令碼
得到資料庫名為mysql的建立指令碼，畢竟在架構實現上不同，有點類似oracle中的使用者級別。
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的實現方法 ++++
Oracle中的實現方式相比要複雜很多。叫法一樣，但是實現還是有很大的差別。
CREATE DATABASE mynewdb
USER SYS IDENTIFIED BY pz6r58
USER SYSTEM IDENTIFIED BY y1tz5p
LOGFILE GROUP 1 ('/u01/oracle/oradata/mynewdb/redo01.log') SIZE 100M,
GROUP 2 ('/u01/oracle/oradata/mynewdb/redo02.log') SIZE 100M,
GROUP 3 ('/u01/oracle/oradata/mynewdb/redo03.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/u01/oracle/oradata/mynewdb/system01.dbf' SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/u01/oracle/oradata/mynewdb/sysaux01.dbf' SIZE 325M REUSE
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/u01/oracle/oradata/mynewdb/temp01.dbf'
SIZE 20M REUSE
UNDO TABLESPACE undotbs
DATAFILE '/u01/oracle/oradata/mynewdb/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

檢視當前的使用者
mysql> SELECT USER();
+----------------+
| USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

+++ Oracle的實現方法 ++++
方法一，透過sql*plus中的show user命令傑克得到
SQL> show user
USER is "N1"
方法二：透過內建函式來實現，比較通用的方式。
SQL> select sys_context('USERENV','current_user') from dual;
SYS_CONTEXT('USERENV','CURRENT_USER')
--------------------------------------------------
N1

檢視含有的表資訊
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| time_zone_transition_type |
| user |
+---------------------------+
28 rows in set (0.00 sec)

+++ Oracle的實現方法 ++++
方法一：透過cat同義詞來實現
SQL> select *from cat where rownum<3;
TABLE_NAME TABTYPE
------------------ ---------------------
AAA TABLE
AAAA TABLE
方法二：透過tab同義詞來實現
SQL> select *from tab where rownum<3;
TNAME TABTYPE CLUSTERID
------------------ --------------------- ----------
AAA TABLE
AAAA TABLE
方法三：透過資料字典user_tables來實現
SQL> select table_name from user_tables where rownum<3;
TABLE_NAME
------------------
AAA
AAAA

檢視指定資料庫中的表資訊
比如查詢資料庫名為mysql裡面含有的表。
mysql> show tables from mysql;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| time_zone_transition_type |
| user |
+---------------------------+
28 rows in set (0.00 sec)

+++ Oracle的實現方法 ++++
Oracle中的實現還是根據資料字典表*_tables
SQL> select table_name from all_tables where owner='REFWORK';
TABLE_NAME
------------------------------
OFFER

檢視test資料庫中的表temp結構
>mysqlshow test temp
Database: test Table: temp
+---------+-------------+-----------------+------+-----+---------+--------------
| Field | Type | Collation | Null | Key | Default | Extra
+---------+-------------+-----------------+------+-----+---------+--------------
| id | int(11) | | NO | PRI | | auto_incremen
| char | char(50) | utf8_general_ci | NO | MUL | |
| varchar | varchar(50) | utf8_general_ci | NO | MUL | |
| text | text | utf8_general_ci | NO | MUL | |
+---------+-------------+-----------------+------+-----+---------+--------------

+++ Oracle的實現方法 ++++
可以透過all_tab_cols來實現
select table_name,column_name from all_tab_cols where owner='N1' and table_name='TEST';

檢視錶的建表語句
mysql> show create table event\G
*************************** 1. row ***************************
Table: event
Create Table: CREATE TABLE `event` (
`db` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`name` char(64) NOT NULL DEFAULT '',
`body` longblob NOT NULL,
`definer` char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`execute_at` datetime DEFAULT NULL,
`body_utf8` longblob,
PRIMARY KEY (`db`,`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Events'
1 row in set (0.00 sec)

+++ Oracle的實現方法 ++++
oracle中一直直觀的方式是使用dbms_metadata.get_ddl來實現
SQL> select DBMS_METADATA.GET_DDL(object_type=>'TABLE',name=>'CSM_OFFER')from dual
DBMS_METADATA.GET_DDL(OBJECT_TYPE=>'TABLE',NAME=>'CSM_OFFER')
--------------------------------------------------------------------------------
CREATE TABLE "REFWORK"."OFFER"
( "OWNER" VARCHAR2(30) NOT NULL ENABLE,
"OBJECT_NAME" VARCHAR2(30) NOT NULL ENABLE,
"SUBOBJECT_NAME" VARCHAR2(30),
"OBJECT_ID" NUMBER NOT NULL ENABLE,
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(19),
"CREATED" DATE NOT NULL ENABLE,
"LAST_DDL_TIME" DATE NOT NULL ENABLE,
"TIMESTAMP" VARCHAR2(19)...

得到表結構的資訊
mysql> desc columns_priv
-> ;
+-------------+----------------------------------------------+------+-----+-----
| Field | Type | Null | Key | Defa
+-------------+----------------------------------------------+------+-----+-----
| Host | char(60) | NO | PRI |
| Db | char(64) | NO | PRI |
| User | char(16) | NO | PRI |
| Table_name | char(64) | NO | PRI |
| Column_name | char(64) | NO | PRI |
| Timestamp | timestamp | NO | | CURR
| Column_priv | set('Select','Insert','Update','References') | NO | |
+-------------+----------------------------------------------+------+-----+-----
7 rows in set (0.01 sec)

+++ Oracle的實現方法 ++++
這一點完全一樣
SQL> desc offer
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED NOT NULL DATE
LAST_DDL_TIME NOT NULL DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)

得到表中的列資訊
mysql> show columns from columns_priv;
+-------------+----------------------------------------------+------+-----+-----
| Field | Type | Null | Key | Defa
+-------------+----------------------------------------------+------+-----+-----
| Host | char(60) | NO | PRI |
| Db | char(64) | NO | PRI |
| User | char(16) | NO | PRI |
| Table_name | char(64) | NO | PRI |
| Column_name | char(64) | NO | PRI |
| Timestamp | timestamp | NO | | CURR
| Column_priv | set('Select','Insert','Update','References') | NO | |
+-------------+----------------------------------------------+------+-----+-----
7 rows in set (0.01 sec)

+++ Oracle的實現方法 ++++
透過user_tab_cols來實現。
SQL> select column_name from user_tab_cols where table_name='OFFER';
COLUMN_NAME
------------------------------
OWNER
OBJECT_NAME
SUBOBJECT_NAME
OBJECT_ID
DATA_OBJECT_ID
OBJECT_TYPE
CREATED
LAST_DDL_TIME

得到索引的資訊
mysql> show index from columns_priv;
+--------------+------------+----------+--------------+-------------+-----------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation
+--------------+------------+----------+--------------+-------------+-----------
| columns_priv | 0 | PRIMARY | 1 | Host | A
| columns_priv | 0 | PRIMARY | 2 | Db | A
| columns_priv | 0 | PRIMARY | 3 | User | A
| columns_priv | 0 | PRIMARY | 4 | Table_name | A
| columns_priv | 0 | PRIMARY | 5 | Column_name | A
+--------------+------------+----------+--------------+-------------+-----------
5 rows in set (0.00 sec)

+++ Oracle的實現方法 ++++
透過User_indexes來實現
SQL> select index_name,index_type from user_indexes where table_name='OFFER';
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
INX_OFFER NORMAL

基於列的模糊查詢
可能在這方面MySQL提供的直觀方式要多一些。
mysql> show columns from columns_priv like '%ab%';
+------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| Table_name | char(64) | NO | PRI | | |
+------------+----------+------+-----+---------+-------+
1 row in set (0.01 sec)

+++ Oracle的實現方法 ++++
Oracle裡面還是用user_tab_cols，一用倒底。
SQL> select column_name from user_tab_cols where table_name='OFFER' and column_name like '%OBJE%';
COLUMN_NAME
------------------------------
OBJECT_NAME
SUBOBJECT_NAME
OBJECT_ID
DATA_OBJECT_ID
OBJECT_TYPE

精確查詢列名
mysql> show columns from columns_priv where field='User';
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| User | char(16) | NO | PRI | | |
+-------+----------+------+-----+---------+-------+
1 row in set (0.01 sec)

+++ Oracle的實現方法 ++++
還是使用usre_tab_cols，一用倒底。
SQL> select column_name from user_tab_cols where table_name='OFFER' and column_name= 'OBJECT_NAME';
COLUMN_NAME
------------------------------
OBJECT_NAME

檢視程式相關的資訊
mysql> show processlist
-> ;
+----+------+-----------------+-------+---------+------+-------+----------------
| Id | User | Host | db | Command | Time | State | Info
+----+------+-----------------+-------+---------+------+-------+----------------
| 3 | root | localhost:49479 | mysql | Query | 0 | init | show processlis
+----+------+-----------------+-------+---------+------+-------+----------------
1 row in set (0.00 sec)

+++ Oracle的實現方法 ++++
Oracle中提供了比較全面的檢視，可以透過v$session,v$process來查詢
USERNAME MACHINE PROGRAM SID
------------------------------ -------------------- ------------------------- ----------
  REFWORK rac1 sqlplus@rac1 (TNS V1-V3) 257

select *from v$process;

檢視資料字典的資訊
MySQL中的資料字典資訊都包含在schema information_schema裡面
>mysqlshow information_schema
Database: information_schema
+---------------------------------------+
| Tables |
+---------------------------------------+
| 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 |
+---------------------------------------+

+++ Oracle的實現方法 ++++
Oracle中的檢視更加豐富，除了常說的資料字典表，還有動態效能檢視，調優診斷很是方便。
select *from dict; --資料字典表
select *from v$fixed_table;--動態效能檢視表

MySQL和Oracle對比學習之資料字典後設資料

相關文章