Creating Databases and Tables Using SQL Commands

In the previous chapter (Creating New Databases and Tables with MySQL Administrator) we created a new database and table using a graphical tool that did not rquire us to use any SQL commands. In this chapter we will learn how to create new databases and tables by issuing SQL commands using the mysql client.

This chapter assumes that the mysql tool is running and connected to the the MySQL database server. If this is not the case and you are unsure as to how to achieve this refer to The mysql Command-Line Tool chapter. Alternatively, the SQL commands outlined in that chapter may be executed in the MySQL Query Browser.

Creating a New MySQL Database

A new database is created using the CREATE DATABASE SQL statement followed by the name of the database to be created. The CREATE SCHEMA statement may also be used for this purpose. For example, to create a new database called MySampleDB the following statement would be entered at the mysql> prompt:

CREATE DATABASE MySampleDB;

If successful, the command will generate output similar to the following:

Query OK, 1 row affected (0.00 sec)

If the database name specified conflicts with an existing database MySQL will display and error message reporting this fact:

ERROR 1007 (HY000): Can't create database 'MySampleDB'; database exists

In this situation, a different database name should be selected, or the IF NOT EXISTS option, which only creates the database if it does not already exists, and does not report an error if it does:

CREATE DATABASE IF NOT EXISTS MySampleDB;

Creating Tables with SQL

New tables are added to an existing database using the SQL CREATE TABLE statement.

CREATE TABLE customer 
( 
customer_id INT NOT NULL AUTO_INCREMENT, 
customer_name char(20) NOT NULL, 
customer_address char(20) NULL, 
PRIMARY KEY (customer_id) 
) ENGINE=InnoDB;