MySQL和Oracle对比学习之数据字典元数据-mysql教程-PHP中文网

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;--动态性能视图表

PHP高级开发技巧与范例

PHP是一种功能强大的网络程序设计语言，而且易学易用，移植性和可扩展性也都非常优秀，本书将为读者详细介绍PHP编程。全书分为预备篇、开始篇和加速篇三大部分，共9章。预备篇主要介绍一些学习PHP语言的预备知识以及PHP运行平台的架设；开始篇则较为详细地向读者介绍PKP语言的基本语法和常用函数，以及用PHP如何对MySQL数据库进行操作；加速篇则通过对典型实例的介绍来使读者全面掌握PHP。本书