Contrast and
Resemblances |
Contrast and resemblances among Oracle, MySQL and PostgreSQL |
Objectives:
- The main objective of this document is to display the salient
features of three database applications, namely , Oracle, MySQL and
PostgreSQL.
- These three databases can be queried using PHP and Java.
|
Data Storage: |
**~MySQL "database" as a schema/user in Oracle. If you have the
privileges, you can query the DBA_USERS view to see the list of schemas
|
Oracle Super User and User with their own tables
The oracle database, renders numerous attributes or properties to
each user, and therefore each users is empowered with extensive
makeover potentials.
|
|
PostgreSQL Overview:
- Server Groups
- Servers
- DataBases
- Database
- Catalogs
- Extensions
- Schemas
- public
- Functions
- Sequences
- Tables
- Trigger Functions
- Views
- TableSpaces
- login roles
|
PostgreSQL : Can hold more than one server
, pgaAdmin3.exe uploads the links for both the servers.
object hierarchies in postgresql
|
MYSQL :
Made of database schemas
MyISAM |
The default
non-transactional storage engine that with full-text indexing.It
is highly portable, stores data in
stores data in .FRM, .MYD and .MYI files. |
MERGE |
A non-transactional
storage engine that allows a collection of MyISAM tables with
identical column and index information to be used as one |
MEMORY (HEAP) |
A non-transactional
storage engine that stores data in memory |
BDB (Berkeley
DB) |
The first
transactional-safe storage engine |
InnoDB |
This has a
transactional-safe storage engine designed for maximum
performance,row-level locking, and stores data in a common
tablespace or individual tablespaces per table. |
FEDERATED |
A storage engine
that accesses data in tables of remote databases rather than in
local tables |
ARCHIVE |
This storage engine
that can store large amount of data without indexes in very
small footprint. |
CSV |
A storage engine
that stores data in text file using comma-separated-values
format |
BLACKHOLE |
A temporary
storage engine that acts as a "black hole" that accepts data but
won't store and would discard the data later on. |
EXAMPLE |
A "stub" engine
that does nothing. Its purpose is to serve as an example that
illustrates how to begin writing new engines. |
ISAM |
The original MySQL
storage engine that has been deprecated in favor of the MyISAM
storage engine as of version 5.0 |
|
Now let us use MySQL Workbench, note list of schemas in the
database. |
It is two early
<?php
//$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
$mysqli = new mysqli("Manas9", "Manas9", "Manas9237", "test");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$query = "SELECT CURRENT_USER();";
$query .= "SELECT fname, lname FROM emp1 ORDER BY fname";
/* execute multi query */
if ($mysqli->multi_query($query)) {
do{
/* store first result set */
if ($result = $mysqli->use_result()) {
while ($row = $result->fetch_row()) {
echo("<br/>". $row[0]." ". $row[1]);
}
$result->close();
}
} while ($mysqli->next_result());
}
/* close connection */
$mysqli->close();
?>
|
phpMyAdmin much more users friendly and
|
Now use Browse menu to view the table
You may also use php script, order of data display was different,
than the logical view, due a filter "order by fname" in the
selection query.
|
|
|
|
|
|
|