Comparison of Oracle, PostgreSQL, and MySQL: Features, Installation, and Basic Operations

1. Database Overview and Core Differences

Feature

Oracle

PostgreSQL

MySQL

Type

Commercial (enterprise‑grade)

Open‑source (strong enterprise extensibility)

Open‑source (lightweight)

Typical Scenarios

Large enterprises, finance, high‑concurrency transactions

Complex queries, geospatial data, JSON support

Web applications, small‑to‑medium systems

Transaction Support

ACID, strong consistency

ACID, advanced transaction features

ACID (InnoDB engine)

Storage Engine

Single engine

Single engine (extendable via plugins)

Multiple engines (InnoDB, MyISAM, …)

License

Commercial license

MIT

GPL (some commercial editions)

2. Installation and Configuration (Simplified)

1. Oracle

Install : Download Oracle XE (free) from the official site; run the installer on Windows or configure kernel parameters on Linux.

Verify Installation : sqlplus sys as sysdba -- login SELECT * FROM v$version; -- view version

2. PostgreSQL

Install : Download from the PostgreSQL website; after installation a default postgres user is created. Use the psql command‑line client.

Verify Installation : psql -U postgres -- login SELECT version(); -- view version

3. MySQL

Install : Download the community edition or, on macOS, use Homebrew: brew install mysql .

Verify Installation : mysql -u root -p -- login SELECT VERSION(); -- view version

3. Basic Operations Comparison (Student Table Example)

1. Create Table

-- Oracle
CREATE TABLE student (
    id NUMBER(5) PRIMARY KEY,
    name VARCHAR2(50) NOT NULL,
    birthdate DATE DEFAULT SYSDATE
);

-- PostgreSQL
CREATE TABLE student (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    birthdate TIMESTAMP DEFAULT NOW()
);

-- MySQL
CREATE TABLE student (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    birthdate DATETIME DEFAULT CURRENT_TIMESTAMP
);

Key differences: Oracle requires an explicit sequence for auto‑increment, PostgreSQL uses SERIAL , and MySQL uses AUTO_INCREMENT .

2. Insert Data

-- Oracle (must specify id)
INSERT INTO student (id, name) VALUES (1, 'Zhang San');

-- PostgreSQL (id generated automatically)
INSERT INTO student (name) VALUES ('Li Si');

-- MySQL (id generated automatically)
INSERT INTO student (name) VALUES ('Wang Wu');

3. Query Data

-- Oracle
SELECT * FROM student WHERE id = 1;

-- PostgreSQL (case‑insensitive)
SELECT * FROM student WHERE name ILIKE 'Li%';

-- MySQL (case‑sensitive)
SELECT * FROM student WHERE name LIKE 'Wang%';

String matching: PostgreSQL supports ILIKE for case‑insensitive searches, while MySQL only provides LIKE (case‑sensitive by default).

4. Core Concept Comparison

Feature

Oracle

PostgreSQL

MySQL

Transaction Isolation Level

Supports all SQL standard levels

Supports all levels

Default REPEATABLE‑READ

JSON Support

Supported from 12c+

Native JSON type

Supported from 5.7+

Geospatial Data

Requires Spatial plugin

Native PostGIS support

Requires third‑party extension

Stored Procedures

PL/SQL

PL/pgSQL

SQL‑like syntax

5. Daily Exercise

Install one database (recommended PostgreSQL or MySQL) and create the student table.

Insert three records into the table.

Write a query to find students whose name contains “Zhang”.

Reflection: If you need a database with geospatial capabilities, which would you choose? Why do auto‑increment implementations differ among the three systems?

6. Learning Recommendations

Key Understanding : Grasp the design philosophy of the three databases – enterprise‑grade, feature‑rich, and lightweight.

Hands‑On Practice : Execute all examples yourself and observe error messages (e.g., Oracle’s VARCHAR2 length limits).

Further Reading : Oracle Docs, PostgreSQL exercises, MySQL tutorial.

Tomorrow the focus will shift to advanced SQL queries such as JOINs and sub‑queries.