10.2 Database Creation

The first thing you do with any database engine is to create a database instance to work with. It is therefore quite ironic that no standard mechanism for creating database instances is supported. For the most part, you can create a database instance in most database engines using some variation of the CREATE DATABASE statement. Its simplest form is common to all database engines:

CREATE DATABASE name

In essence, this statement creates a brand new, blank database instance. It is all you need to create a MySQL or PostgreSQL database. PostgreSQL does offer the option of specifying where you place the database files for the instance:

CREATE DATABASE name WITH LOCATION = 'path'

The more complex database engines require more complex database creation statements. Oracle, for example, allows you to specify options such as log file specifications, datafile specifications, and character set information. When in doubt, you can get away with the basic syntax listed earlier. However, you rarely will find any default database creation values suitable to a production environment. In places like this, you will find the help of a good DBA (database administrator) with expertise in your database engine of choice invaluable.

Once you have a database to work with, you can work with that database using the CONNECT statement:

CONNECT [TO]
DEFAULT | { [server] [AS name] [USER user] }

For example, to connect to the PostgreSQL database instance library on the server carthage, you would execute the following SQL:

CONNECT TO library@carthage AS libconn USER webuser

In MySQL, this statement is slightly different:

CONNECT dbname [server [AS user]]

Oracle also provides an alternate syntax:

CONNECT [ [user/password] [AS [SYSOPER | SYSDBA] ] ]

You are now set to begin using your new database instance. In the examples in this chapter, I will be using a database called jtest.

Once you are done with a database and no longer have use for the data it contains, you can get rid of the instance from your server using the DROP DATABASE command:

DROP DATABASE dname

Dropping databases—or anything else for that matter—from a database is a very destructive operation. The only way to recover from an accidental DROP command is to restore from a backup!

Oracle, however, does not support the DROP DATABASE command. To get rid of a database in Oracle, issue the CREATE DATABASE command with no parameters using the name of the existing database that should be dropped.