|
|
Creating and dropping database objects is the first subject you'll examine in this chapter. Databases are complex animals, and each database product has its own way of managing and storing internal data. We'll touch on the most common features and some specific product particularities, but for a closer look at them, you'll need an advanced, specialized book about your database product.
SQL Server, Oracle, MySQL, and DB2 enable you to create whole databases using a SQL statement. The following syntax will work on all of these database systems:
CREATE DATABASE MyDatabase;
Each database system provides different options for CREATE DATABASE, which gives you extra control over certain properties of the database. However, for most purposes, you simply need to specify a name, and then the server will make all the other decisions for you.
Having said this, creating a database is a sensitive task that shouldn't be done hastily. The database is a complex object, and when you create a database, you have many options to fine-tune its performance and behavior, depending on for what purpose you'll use it. In most cases, it's much easier to use the tools that ship with the database system because they can do many of the tasks for you.
For example, if you're using Oracle, the easiest way to create a new database is via the Oracle Installer utility (when installing Oracle). When installing Oracle, you have the option to create a new database. The installer creates a create-database script, which can be then used as a starting point for your own scripts. You can typically find the scripts created by the Installer utility in the dbs subdirectory of the Oracle home directory.
If you're using Oracle, be careful because CREATE DATABASE can easily delete an existing database if this isn't used with caution. It's common for Oracle instances to contain only one database (or for a single database to be spread over several instances) and to use schemas rather than databases to group related tables and other objects.
With SQL Server, you can use Enterprise Manager, which offers both a New Database command and a Create Database Wizard. These handy tools allow for the quick creation of databases.
You can create databases in DB2 either with the Control Center application or via SQL commands using Command Center. If you use Command Center, it's not possible to create a new database if you're already connected to an existing database.
Using an open connection to the database server, you connect to an existing database using the USE command for SQL Server and MySQL like this:
On the other systems, you can reissue a CONNECT command to connect to the new database.
| Note |
Appendix A, "Executing SQL Statements," covers the CONNECT command. |
Removing databases in SQL Server, MySQL, and DB2 is equally easy:
DROP DATABASE MyDatabase;
Oracle doesn't support the DROP DATABASE command. If you want to drop a database in Oracle, use CREATE DATABASE, specifying the name of the database you want to delete. You can also use the Oracle Database Assistant to drop databases.
Access doesn't support either of these commands. To create an Access database, use File Ø New in the Access application window. To delete an Access database, just delete the database's .mdb file.
|
|