Creating databases and tables using SQL commands

This article will show you how to create new databases and tables using SQL commands in a mysql client. It is assumed that this tool is running and connected to the MySQL database server.

Creating a new MySQL database

A new database is created using the SQL CREATE DATABASE operator, followed by the name of the database being created. For this purpose, the CREATE SCHEMA operator is also used. For example, to create a new database called MySampleDB, you need to enter the following query on the mysql command line:

CREATE DATABASE MySampleDB;

If all went well, the command will generate the following output:

Query OK, 1 row affected (0.00 sec)

If the specified database name conflicts with an existing MySQL database, an error message will be displayed:

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

In this situation, select a different database name or use the IF NOT EXISTS option. It only creates a database if it does not already exist:

CREATE DATABASE IF NOT EXISTS MySampleDB;

Create tables using SQL

New tables are added to an existing database using the SQL CREATE TABLE operator. The CREATE TABLE statement is followed by the name of the table being created and then by a comma in the list of names and definitions of each table column:

CREATE TABLE table name ( column_name definition, table_name definition ..., PRIMARY KEY= (column_name) ) ENGINE= engine_type;

Column definition defines the data type whether the column can be NULL, AUTO_INCREMENT. The CREATE TABLE operator also allows you to specify a column (or group of columns) as the primary key.

Before you can create a table, you must select the database. This is done using the SQL USE operator:

USE MySampleDB;

Let’s create a table consisting of three columns: customer_id, customer_name and customer_address. Columns customer_id and customer_name should not be empty (that is NOT NULL). customer_id contains an integer value, which will automatically increase when adding new rows.

Other columns will contain rows up to 20 characters long. The primary key is defined as 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;

NULL and NOT NULL values

If the column is set to NULL, then the empty rows will be added to the table. Conversely, if the column is defined as NOT NULL, then the empty rows will not be added.

Primary keys

The primary key is the column used to identify the records in the table. The value of the primary key column must be unique. If several columns are combined in the primary key, the combination of key values must be unique for each row.

The primary key is defined using the PRIMARY KEY operator during table creation. If several columns are used, they are separated by a comma:

PRIMARY KEY (column_name, column_name ... )

In the following example, a table is created using two columns as the primary key:

CREATE TABLE product
(
prod_code INT NOT NULL AUTO_INCREMENT,
prod_name char(30) NOT NULL,
prod_desc char(60) NULL,
PRIMARY KEY (prod_code, prod_name)
) ENGINE=InnoDB;

AUTO_INCREMENT

When a column is defined with AUTO_INCREMENT, its value is automatically increased every time a new record is added to the table.

This is useful when using a column as the primary key. With AUTO_INCREMENT, you do not need to write SQL instructions to calculate a unique identifier for each row.

AUTO_INCREMENT can only be assigned to one column in a table. And it must be indexed (e.g. declared as a primary key).

The AUTO_INCREMENT value for a column can be overridden by specifying a new one when executing the INSERT instruction.

You can query MySQL for the latest AUTO_INCREMENT value using last_insert_id() as follows:

SELECT last_insert_value();

Defining default values when creating a table

The default values are used when the value is not defined when you insert it into the database.
The default values are set using the DEFAULT keyword in the CREATE TABLE operator. For example, the SQL query below sets the default value for the sales_quantity column:

CREATE TABLE sales
{
sales_number int NOT_NULL,
sales_quantity int NOT_NULL DEFAULT 1,
sales_desc char(20) NOT_NULL,
PRIMARY KEY (sales_number)
) ENGINE=MyISAM;

Types of MySQL database engines

Each of the examples of table creation in this article up to this point included the definition ENGINE= . MySQL comes with several different database engines, each of which has its advantages.

Using the ENGINE= directive, you can choose which engine to use for each table. The following MySQL database engines are currently available:

• InnoDB – was introduced inMySQL version 4.0 and is classified as a secure transaction environment, its mechanism ensures that all transactions are 100% complete. In this case partially completed transactions (for example, as a result of server failure or power failure) will not be recorded. The disadvantage of InnoDB is the lack of support for full-text search.
• MyISAM is a high-performance engine with full text search support. This performance and functionality is ensured by the lack of transaction security.
• MEMORY – in terms of functionality is equivalent to MyISAM, except that all data is stored in RAM, not on the hard disk. This ensures high processing speed. The temporary nature of data stored in RAM makes the MEMORY engine more suitable for temporary table storage.

Sliders of different types can be combined in one database. For example, some tables may use the InnoDB engine, while others may use MyISAM. If no engine is specified during table creation, by default MySQL will use MyISAM.

To specify the type of engine that will be used for the table, place the appropriate definition ENGINE= after defining the columns of the table:

CREATE TABLE tmp_orders
{
tmp_number int NOT_NULL,
tmp_quantity int NOT_NULL,
tmp_desc char(20) NOT_NULL,
PRIMARY KEY (tmp_number)
) ENGINE=MEMORY;