Home arrow MySQL arrow Page 5 - Data Definition Language, Part 1

4.5 CREATE DATABASE and DROP DATABASE - MySQL

Studying for the MySQL Certification exam? This article, the first of two parts, covers roughly 10 percent of the material that will appear on the exam, including general database and table properties, storage engines and table types, and more. It is excerpted from chapter four of the book MySQL Certification Guide written by Paul Dubois et. al. (Sams, 2004, ISBN: 0672326329).

TABLE OF CONTENTS:
  1. Data Definition Language, Part 1
  2. 4.1 General Database and Table Properties
  3. 4.3 Limits on Number and Size of Database Components
  4. 4.4 Identifier Syntax
  5. 4.5 CREATE DATABASE and DROP DATABASE
  6. 4.7 DROP TABLE
  7. 4.9 Creating and Dropping Indexes
  8. 4.10 Column Types
  9. 4.10.2 String Column Types
  10. 4.10.3 Date and Time Column Types
  11. 4.10.4 Column Options
  12. 4.10.5 Using the AUTO_INCREMENT Column Option
  13. 4.10.6 Automatic Type Conversion and Value Clipping
By: Sams Publishing
Rating: starstarstarstarstar / 41
January 19, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

To create a new database, use the CREATE DATABASE statement. The following statement creates a database named mydb:

CREATE DATABASE mydb;

After a database has been created, you can create new tables in it using the CREATE TABLE statement, which is described in section 4.6, "CREATE TABLE."

If you try to create a database that already exists, an error occurs. If you simply want to ensure that the database exists, add an IF NOT EXISTS clause to the statement:

CREATE DATABASE IF NOT EXISTS mydb;

With the additional clause, the statement creates the database only if it does not already exist. Otherwise, the statement does nothing and no error occurs. This can be useful in applications that need to ensure that a given database is available, without disrupting any existing database with the same name.

Creating a database has no effect on the database that's currently selected as the default database. To make the new database the default database, issue a USE statement:

USE mydb;

To see a list of available databases, use the SHOW DATABASES statement. This statement will not show the names of databases to which you have no access. To see a list of tables in a database, use SHOW TABLES FROM db_name (or just SHOW TABLES if db_name is the name of the current database). The SHOW command is described in more detail in section 4.11, "Using SHOW and DESCRIBE to Review Table Structures."

When you no longer need a database, you can remove it with DROP DATABASE:

DROP DATABASE mydb;

It's unnecessary to remove the tables in a database before dropping it. DROP DATABASE does not require the database to be empty, so it does not fail if the database contains tables. DROP DATABASE removes the tables in the process of removing the database.

DROP DATABASE is a dangerous statement and you should use it with care. There is no statement to "undo" DROP DATABASE. If you drop a database by mistake, your only option is to recover it from your backups.

4.6 CREATE TABLE

Use the CREATE TABLE statement to create a new table. A table's definition includes its name and a list of columns, each of which has a name and a definition. The table definition may also include index definitions.

This section describes basic CREATE TABLE syntax using simple column definitions. More information on column datatypes and properties can be found in section 4.10, "Column Types."

To create a table, give its name followed by a list of column definitions within parentheses:

CREATE TABLE table_name (definition1, definition2, ...);

In the simplest case, a table contains only one column. The following statement creates a table named t with a single column named id that will contain INT (integer) values:

CREATE TABLE t (id INT);

A column definition may include options to define the column data more precisely. For example, to disallow NULL values in the column, include NOT NULL in the definition:

CREATE TABLE t (id INT NOT NULL);

More complex tables have multiple columns, with the column definitions separated by commas. The following table definition includes, in addition to the id column, two 30-byte character columns for storing last names and first names, and a column for storing date values. All columns are declared NOT NULL to indicate that they require non-NULL values.

CREATE TABLE t
(
id     INT NOT NULL,
last_name CHAR(30) NOT NULL,
first_name CHAR(30) NOT NULL,
d     DATE NOT NULL
);

Every table must belong to a database. That is, you cannot create a table that is not located within some database. If the table named in the CREATE TABLE statement isn't qualified with a database name, the table is created in the default database. To indicate explicitly where to create the table, you can qualify the table name with the name of the desired database, using db_name.table_name syntax. For example, if you want to create a table called mytable in the test database, write the CREATE TABLE statement like this:

CREATE TABLE test.mytable (i INT);

The qualified identifier syntax is helpful when there's no default database or when some other database is currently selected as the default. (If test happens to be the default database, the statement still works. In that case, the database name is unnecessary but harmless.)

When you create a table, you can provide index definitions in addition to the column definitions. Indexes are useful for increasing query performance by reducing lookup time. Here's a simple example that includes two index definitions. The first creates an index on the id column and requires each id value to be unique. The second index definition creates a two- column index on the last_name and first_name columns of the table:

CREATE TABLE t
(
id     INT NOT NULL,
last_name CHAR(30) NOT NULL,
first_name CHAR(30) NOT NULL,
UNIQUE (id),
INDEX (last_name, first_name)
);

Section 4.9, "Creating and Dropping Indexes," discusses index creation further.

If you try to create a table that already exists, an error occurs. If you simply want to ensure that the table exists, add an IF NOT EXISTS clause to the statement:

CREATE TABLE IF NOT EXISTS t (i INT);

Note, however, that MySQL does not perform any check on the table structure when you add this clause. In particular, MySQL will issue no warning if a table with the given name exists but has a structure different from the one you've defined in the CREATE TABLE statement.

A temporary table can be created by adding the keyword TEMPORARY to the CREATE TABLE statement:

CREATE TEMPORARY TABLE t (i INT);

Temporary tables exist only for the duration of the current connection. The server drops temporary tables when you disconnect, if you haven't already dropped them explicitly. This is convenient because you need not remember to remove the table yourself. A temporary table is visible only to the client that creates it, so different clients can create temporary tables in the same database, using the same name, without conflicting with one another.

This chapter excerpt is from MySQL Certification Guide by Paul Dubois et al. (Sams, 2004, ISBN: 0672326329 ). Check it out at your favorite bookstore today. Buy this book now.



 
 
>>> More MySQL Articles          >>> More By Sams Publishing
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: