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. The CREATE TABLE statement is followed by the name of the table to be created followed by a comma separated list of the names and definitions of each table column:

CREATE TABLE table_name ( column_name definitions, table_name definitions ..., PRIMARY KEY=(column_name) ) ENGINE=engine_type;

The definitions field for each column define information such as the data type of the column, whether the column can be NULL and whether the column value auto increments. The CREATE TABLE statemnt also allows a column (or group of columns) to be specified as the primary key (see Database Basics for a description of primary keys).

Before a table can be created a database must first be selected so that MySQL knows where to create the table. This is achieved using the USE SQL statement:

USE MySampleDB;

Having selected a database, the following example creates a table consisting of three columns named customer_id, customer_name and customer_address. The customer_id and customer_name columns must contain values (i.e NOT NULL). The customer_id holds an integer value whichj will auto increment as new rows are added and the others hold character strings up to 20 characters in length. The primary key is defined to be the customer_id

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;