Data Definition Language, Part 1 - 4.5 CREATE DATABASE and DROP DATABASE
(Page 5 of 13 )
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.
|
Next: 4.7 DROP TABLE >>
More MySQL Articles
More By Sams Publishing
/ 36 
