MySQL和Oracle对比学习之数据字典元数据-MySQL-Tutorial-php.cn

Heim

Datenbank

MySQL-Tutorial

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

Jun 07, 2016 pm 04:07 PM

MySQL和Oracle虽然在架构上有很大的不同，但是如果从某些方面比较起来，它们有些方面也是相通的。毕竟学习的主线是MySQL，所以会

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 rownumTABLE_NAME TABTYPE
------------------ ---------------------
AAA TABLE
AAAA TABLE
方法二：通过tab同义词来实现
SQL> select *from tab where rownumTNAME TABTYPE CLUSTERID
------------------ --------------------- ----------
AAA TABLE
AAAA TABLE
方法三：通过数据字典user_tables来实现
SQL> select table_name from user_tables where rownumTABLE_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;--动态性能视图表

本文永久更新链接地址：

Erklärung dieser Website

Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn