Contents

THIS DOCUMENT IS STILL UNDER DEVELOPMENT

Introduction

This documentation is designed for Oracle Developers. Terms are defined for existing Oracle functionality, and then with the comparable MySQL functionality.

All examples are based on the MySQL Sakila Sample Database, which has been migrated to Oracle for the purposes of this article.

Acknowledgements

Thanks to Morgan Tocker, Jon Stephens, Mark Leith, Mike Hillyer, the Brisbane MySQL Users Group for input from the MySQL Side and old friends and collegues David Spanevello, Tony Obermeit and Blair Layton from the Oracle Side for their assistance in ensuring this content is correct and accurate.

Conventions

Both the Oracle and MySQL Products are available on a variety of operating system platforms. While information provided should be compatible on these platforms, some Operating System Dependances may exist. All examples are specified for use with the Linux Operating System. Any specific differences will be noted.

Oracle Specific Syntax

$ sqlplus {username}/{password}[@netname]
SQL> DESC {tablename}
SQL> EXIT;

MySQL Specific Syntax

$ mysql -u{username} -p{password} {database}
mysql> DESC {tablename}
mysql> EXIT;

MySQL Example Code

SELECT column1, column2
FROM table
WHERE column3 = condition
⇑ TOP

Reference Software

This documentation will make reference to syntax and functionality that is available in the current production versions of MySQL and Oracle as at April 2006.

Production Versions

MySQL 5.0 GA (5.0.20)
     http://www.mysql.com/products/database/

Oracle 10g Release 2 Express Edition (10.2.0.1)
     http://www.oracle.com/technology/products/database/xe

Other Versions

Development Products

⇑ TOP

SQL*Plus

Introduction

SQL*Plus is the Oracle SQL command line client. SQL*Plus is used to execute SQL Statements and provide results to the user via the current screen.

Command Syntax

The default operation to access the SQL command line without any database connection.

$ sqlplus /nolog

$ mysql

Example

$ mysql

References

MySQL Documentation - Program Options Oracle Documentation - SQL*Plus Program Syntax

Normal Command Operation

Under normal circumstances, connection via a SQL command line client will include appropiate user authenication (User/Password) and the appropiate Database.

$ sqlplus [username]/[password][@{netname}]

NOTE: the Environment Variable ORACLE_SID by default dictates which Oracle Database is referenced.

$ mysql [-u{username}] [-p{password}] [database] [-h{hostname}]

Example

$ mysql -usakila -pdolphin sakila
$ mysql -usakila -p sakila
Enter password:
$ mysql -p sakila
Enter password:

NOTE: for this example, the user will default to your logged in system user.

⇑ TOP

Command Help

$ sqlplus {-H|--help}

$ mysql {-?|--help}

Example

$ mysql --help         Sample Output

Navigation

SQL> help
SQL> exit
SQL> spool {file}
SQL> { ; / }
SQL> @ {file}

mysql>  { help | \h | \?  | ? }
mysql>  { exit | quit | \q }
mysql>  { clear | \c }
mysql>  { edit | \e }
mysql>  { source | \. } {file}
mysql>  { system | \! }
mysql>  { tee | \T } {file}
mysql>  { status | \s }
mysql>  { go | \g | ; }

Example

mysql> help
mysql> help contents
mysql> help functions
mysql> help string functions
mysql> help UPPER


        Sample Output
⇑ TOP

Describe Table Definition

Syntax

SQL> DESC[RIBE] {tablename};

mysql> DESC[RIBE] {tablename};

Example

$ mysql -usakila -pdolphin sakila
mysql> DESC actor;
+-------------+----------------------+------+-----+-------------------+----------------+
| Field       | Type                 | Null | Key | Default           | Extra          |
+-------------+----------------------+------+-----+-------------------+----------------+
| actor_id    | smallint(5) unsigned | NO   | PRI | NULL              | auto_increment |
| first_name  | varchar(45)          | NO   |     |                   |                |
| last_name   | varchar(45)          | NO   | MUL |                   |                |
| last_update | timestamp            | YES  |     | CURRENT_TIMESTAMP |                |
+-------------+----------------------+------+-----+-------------------+----------------+

References

MySQL Documentation - DESCRIBE Oracle Documentation - DESCRIBE MySQL Documentation - SHOW CREATE TABLE

SHOW

mysql> SHOW {COMMAND};
$ mysql -usakila -pdolphin sakila
mysql> SHOW TABLES;
mysql> SHOW TABLES LIKE 'film%';

References

MySQL Documentation - SHOW

SET

mysql> SET {VARIABLE ASSIGNMENT};
$ mysql -usakila -pdolphin sakila
mysql> SET @start = NOW();
mysql> SET @end = NOW();
mysql> SELECT @start, @end, NOW();
+---------------------+---------------------+---------------------+
| @start              | @end                | NOW()               |
+---------------------+---------------------+---------------------+
| 2006-04-14 15:40:09 | 2006-04-14 15:40:16 | 2006-04-14 15:40:25 |
+---------------------+---------------------+---------------------+

References

MySQL Documentation - SET

Connect to Database

Syntax

SQL> CONNECT {user}/{password}

mysql> USE {database};

Example

$ mysql
mysql> USE mysql;
mysql> SELECT DATABASE();
mysql> SHOW TABLES;
mysql> USE sakila;
mysql> SELECT DATABASE();
mysql> SHOW TABLES;

References

MySQL Documentation - USE ⇑ TOP

SQL

Overview

MySQL Documentation - SQL Statement Syntax Oracle Documentation - Database SQL Reference

Reserved Words

Syntax

SQL> SELECT * FROM V$RESERVED_WORDS;

N/A

Unlike Oracle, MySQL will allow you to use Reserved Words within table structures. This is achieved in a default installation by enclosing the element within backquotes (`).

Example

mysql> CREATE TABLE `group` (
mysql> `insert` CHAR(1) NOT NULL
mysql> );

In addition, you can use double quotes (") using an appropiate sql_mode command.

mysql> SET sql_mode="ANSI_QUOTES"
mysql> CREATE TABLE "group" (
mysql> "insert" CHAR(1) NOT NULL
mysql> );

References

MySQL Documentation - Reserved Words Oracle Documentation - Reserved Words Oracle Documentation - V$RESERVED_WORDS

Select

Insert

Single Row Insert Statement

mysql> INSERT INTO actor(first_name,last_name)
mysql> VALUES ('Russell','Crowe');
mysql> SELECT *
mysql> FROM actor
mysql> WHERE actor_id=LAST_INSERT_ID();
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      201 | Russell    | Crowe     | 2006-04-14 14:43:08 |
+----------+------------+-----------+---------------------+

NOTE: It is not recommended in a production application to use SELECT *. This has been used in this example for simplicity, and should not be taken as a best programming practice.

AUTO_INCREMENT Alternatives

As in the previous example, the AUTO_INCREMENT column while mandatory (NOT NULL), it is not required in the INSERT statement, as it has an implied DEFAULT value. Alternatively an AUTO_INCREMENT column can be specified, and values of NULL and 0 are valid values that produce the DEFAULT functionality.

mysql> INSERT INTO actor(actor_id,first_name,last_name)
mysql> VALUES (NULL,'Tom','Hanks');
mysql> SELECT *
mysql> FROM actor
mysql> WHERE actor_id=LAST_INSERT_ID();
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      202 | Tom        | Hanks     | 2006-04-14 14:58:22 |
+----------+------------+-----------+---------------------+
mysql> INSERT INTO actor(actor_id,first_name,last_name)
mysql> VALUES (0,'Dennis','Quaid');
mysql> SELECT *
mysql> FROM actor
mysql> WHERE actor_id=LAST_INSERT_ID();
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      203 | Dennis     | Quaid     | 2006-04-14 14:58:22 |
+----------+------------+-----------+---------------------+

AUTO_INCREMENT columns can also be overridden, however LAST_INSERT_ID() is no longer applicable.

mysql> INSERT INTO actor(actor_id,first_name,last_name)
mysql> VALUES (1000,'Sylvester','Stallone');
mysql> SELECT *
mysql> FROM actor
mysql> WHERE actor_id=LAST_INSERT_ID();
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      203 | Dennis     | Quaid     | 2006-04-14 14:58:22 |
+----------+------------+-----------+---------------------+
mysql> SELECT *
mysql> FROM actor
mysql> WHERE actor_id=1000;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|     1000 | Sylvester  | Stallone  | 2006-04-14 15:04:02 |
+----------+------------+-----------+---------------------+
mysql> INSERT INTO actor(first_name,last_name)
mysql> VALUES ('Steven','Seagal');
mysql> SELECT *
mysql> FROM actor
mysql> WHERE actor_id=LAST_INSERT_ID();
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|     1001 | Steven     | Seagal    | 2006-04-14 15:09:19 |
+----------+------------+-----------+---------------------+

Multiple Row Insert Statement

mysql> INSERT INTO actor(first_name,last_name)
mysql> VALUES ('Morgan','Freedman'),('Charlie','Sheen');
mysql> SELECT *
mysql> FROM actor
mysql> WHERE actor_id=LAST_INSERT_ID();
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      204 | Morgan     | Freedman  | 2006-04-14 14:51:39 |
+----------+------------+-----------+---------------------+

NOTE: This is the First Row Inserted, now the Last.

In our sample single user enviroment, we can confirm the INSERT with the following statement, however this will not be valid in a normal multi-user environment.

SELECT * FROM actor WHERE actor_id>=LAST_INSERT_ID() LIMIT 2; 
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      204 | Morgan     | Freedman  | 2006-04-14 14:51:39 |
|      205 | Charlie    | Sheen     | 2006-04-14 14:51:39 |
+----------+------------+-----------+---------------------+
INSERT INTO actor
SET first_name='Nicole',last_name='Kidman';
SELECT *
FROM actor
WHERE actor_id=LAST_INSERT_ID();
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      206 | Nicole     | Kidman    | 2006-04-14 14:44:40 |
+----------+------------+-----------+---------------------+

Handling of Default Values

mysql> DESC film; 
+----------------------+---------------------------------------------------------------------+------+-----+-------------------+----------------+
| Field                | Type                                                                | Null | Key | Default           | Extra          |
+----------------------+---------------------------------------------------------------------+------+-----+-------------------+----------------+
| film_id              | smallint(5) unsigned                                                | NO   | PRI | NULL              | auto_increment |
| title                | varchar(255)                                                        | NO   | MUL |                   |                |
| description          | text                                                                | YES  |     | NULL              |                |
| release_year         | year(4)                                                             | YES  |     | NULL              |                |
| language_id          | tinyint(3) unsigned                                                 | NO   | MUL |                   |                |
| original_language_id | tinyint(3) unsigned                                                 | YES  | MUL | NULL              |                |
| rental_duration      | tinyint(3) unsigned                                                 | NO   |     | 3                 |                |
| rental_rate          | decimal(4,2)                                                        | NO   |     | 4.99              |                |
| length               | smallint(5) unsigned                                                | YES  |     | NULL              |                |
| replacement_cost     | decimal(5,2)                                                        | NO   |     | 19.99             |                |
| rating               | enum('G','PG','PG-13','R','NC-17')                                  | YES  |     | G                 |                |
| special_features     | set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') | YES  |     | NULL              |                |
| last_update          | timestamp                                                           | YES  |     | CURRENT_TIMESTAMP |                |
+----------------------+---------------------------------------------------------------------+------+-----+-------------------+----------------+
mysql> INSERT INTO film(title,description,release_year,language_id)
mysql> VALUES('Gladiator','When a Roman general is betrayed and his family murdered by a corrupt prince, he comes to Rome as a gladiator to seek revenge.',2000,1);
mysql> SELECT film_id,title,rental_duration, rental_rate,replacement_cost,rating,special_features
mysql> FROM film
mysql> WHERE film_id=LAST_INSERT_ID();
+---------+-----------+-----------------+-------------+------------------+--------+------------------+
| film_id | title     | rental_duration | rental_rate | replacement_cost | rating | special_features |
+---------+-----------+-----------------+-------------+------------------+--------+------------------+
|    1001 | Gladiator |               3 |        4.99 |            19.99 | G      | NULL             |
+---------+-----------+-----------------+-------------+------------------+--------+------------------+
mysql> INSERT INTO film(title,description,release_year,language_id,rental_duration, rental_rate, replacement_cost)
mysql> VALUES('The Day After Tomorrow','A climatologist tries to figure out a way to save the world from abrupt global warming. He must get to his young son in New York, which is being taken over by a new ice age',2004,1,1,5.50,22.95);
mysql> SELECT film_id,title,rental_duration, rental_rate,replacement_cost,rating,special_features
mysql> FROM film
mysql> WHERE film_id=LAST_INSERT_ID();
+---------+------------------------+-----------------+-------------+------------------+--------+------------------+ 
| film_id | title                  | rental_duration | rental_rate | replacement_cost | rating | special_features |
+---------+------------------------+-----------------+-------------+------------------+--------+------------------+
|    1002 | The Day After Tomorrow |               1 |        5.50 |            22.95 | G      | NULL             |
+---------+------------------------+-----------------+-------------+------------------+--------+------------------+

ENUM and SET Data Types

//TODO SET @id=LAST_INSERT_ID(); SELECT * FROM actor WHERE actor_id=@id;

Update

Delete

Replace

⇑ TOP

PL/SQL

Procedures

Functions

Packages

Triggers

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7004.htm#i2235611 http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html

Explain

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14211/ex_plan.htm http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9010.htm ⇑ TOP

SQL DDL

Create Table

Create Index

Create View

Create Database

Drop Database

List Databases

⇑ TOP

System Administration

Cross Reference

Technology http://otn.oracle.com/ http://dev.mysql.com/
http://dev.mysql.com/doc/refman/5.0/en/show-table-status.html SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']