Working with Tables - Part II

Altering Table Structures

Beside creating table, MySQL allows you to alter existing table structures with a lot of options. Here are the ALTER TABLE statement:

ALTER [IGNORE] TABLE table_name options[, options...]
options:
       ADD [COLUMN] create_definition [FIRST | AFTER col_name ]
 or    ADD [COLUMN] (create_definition, create_definition,...)
 or    ADD INDEX [index_name] (index_col_name,...)
 or    ADD PRIMARY KEY (index_col_name,...)
 or    ADD UNIQUE [index_name] (index_col_name,...)
 or    ADD FULLTEXT [index_name] (index_col_name,...)
 or    ADD [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name,...)
           [reference_definition]
 or    ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
 or    CHANGE [COLUMN] old_col_name create_definition
              [FIRST | AFTER column_name]
 or    MODIFY [COLUMN] create_definition [FIRST | AFTER col_name]
 or    DROP [COLUMN] col_name
 or    DROP PRIMARY KEY
 or    DROP INDEX index_name
 or    DISABLE KEYS
 or    ENABLE KEYS
 or    RENAME [TO] new_table_name
 or    ORDER BY col_name
 or    table_options

Most of these option are obvious, we will explain some here:

• The CHANGE and MODIFY are the same, they allow you to change the definition of the column or its position in the table.
• The DROP COLUMN will drop the column of the table permanently, if the table contain data all the data of the column will be lost.
• The DROP PRIMARY KEY and DROP INDEX only remove the primary key or index of the column.
• The DISABLE and ENABLE KEYS turn off and on updating indexes for MyISAM table only.
• The RENAME Clause allows you the change the table name to the new one.

Dropping Tables

To delete table from the database, you can use DROP TABLE statement:

DROP [TEMPORARY] TABLE [IF EXISTS] table_name [, table_name,...]

TEMPORARY keyword is used for dropping temporary tables. MySQL allows you to drop multiple table at once by listing them and separated each by a comma. IF EXISTS is used to prevent you from deleting table which does not exist in the database.

Empty Table's Data

In some cases, you want to delete all table data in a fast way and reset all auto increment columns. MySQL provide you TRUNCATE table statement to do so. The statement is in this form:

TRUNCATE TABLE table_name

There are some points you should remember before using TRUNCATE TABLE statement:

• TRUNCATE TABLE statement drop table and recreate it so it is much faster than DELETE TABLE statement but it is not transaction-safe.
• The number of deleted rows is not return like DELETE TABLE statement.
• ON DELETE triggers are not invoked because TRUNCATE does not use DELETE statement.

Working with Tables - Part I

Creating Tables

To create table we use the CREATE TABLE statement. The usual form of this statement is:

CREATE TABLE [IF NOT EXISTS] table_name(
  column_list
  ) type=table_type

MySQL supports IF NOT EXISTS after CREATE TABLE statement to prevent you from error to create table which already exists on the database server. table_name is the name of table you would like to create. After that, you can define a set of columns which is usually in this form: column_name data_type(size) [NOT] NULL. And finally, you can specify the storage engine type you prefer to use for the table. MySQL supports various storage engines such as InnoDB, MyISAM... If you don't explicit declare storage engine type, MySQL will use MyISAM by default.

In our classicmodels sample database, to create employees table we can apply the statement above as follows:

CREATE TABLE employees (             
            employeeNumber into(11) NOT NULL,   
            lastName varchar(50) NOT NULL,     
            firstName varchar(50) NOT NULL,    
            extension varchar(10) NOT NULL,    
            email varchar(100) NOT NULL,       
            officeCode varchar(10) NOT NULL,   
            reportsTo int(11) default NULL,    
            jobTitle varchar(50) NOT NULL,     
            PRIMARY KEY  (employeeNumber)      
          );

You specify table name employees after CREATE TABLE statement. Then columns list with its characteristics followed such as data type, size, NOT NULL or not; And finally you can specify the primary key of the table, in this case the primary key is employeeNumber. If the table has more than one primary key, you can seperate them by a comma. For example, the payments table has two primary keys customerNumber and checkNumber, you can create it by execute following query:

CREATE TABLE payments (                       
           customerNumber int(11) NOT NULL,            
           checkNumber varchar(50) NOT NULL,           
           paymentDate datetime NOT NULL,              
           amount double NOT NULL,                     
           PRIMARY KEY  (customerNumber,checkNumber) 
         );

By default, we use MyISAM storage engine for the table we created.

Showing and Describing Tables in a Database

In order to show all tables in a database you use SHOW TABLES statment, the server will returns all tables name of the current selected database you work with.

SHOW TABLES

Here is the output of classicmodels database

+-------------------------+
| Tables_in_classicmodels |
+-------------------------+
| customers               |
| employees               |
| offices                 |
| orderdetails            |
| orders                  |
| payments                |
| productlines            |
| products                |
+-------------------------+
8 rows in set (0.00 sec)

In some cases, you need to see the table characteristics or table metadata, you can use DESCRIBE statement as follows:

DESCRIBE employees;

for instance, we can describe employees table like this query

DESCRIBE employees;

The output return from the database server:

+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| employeeNumber | int(11)      | NO   | PRI | NULL    |       |
| lastName       | varchar(50)  | NO   |     | NULL    |       |
| firstName      | varchar(50)  | NO   |     | NULL    |       |
| extension      | varchar(10)  | NO   |     | NULL    |       |
| email          | varchar(100) | NO   |     | NULL    |       |
| officeCode     | varchar(10)  | NO   |     | NULL    |       |
| reportsTo      | int(11)      | YES  |     | NULL    |       |
| jobTitle       | varchar(50)  | NO   |     | NULL    |       |
+----------------+--------------+------+-----+---------+-------+
8 rows in set (0.02 sec)

Understanding MySQL Table Types

MySQL supports various of table types or storage engines to allow you to optimize your database. The table types are available in MySQL are:

• ISAM
• MyISAM
• InnoDB
• BerkeleyDB (BDB)
• MERGE
• HEAP

The most important feature to make all the table types above distinction is transaction-safe or not. Only InnoDB and BDB tables are transaction safe and only MyISAM tables support full-text indexing and searching feature. MyISAM is also the default table type when you create table without declaring which storage engine to use. Here are some major features of each table types:

ISAM

ISAM had been deprecated and removed from version 5.x. All of it functionality entire replace by MyISAM. ISAM table has a hard size 4GB and is not portable.

MyISAM

MyISAM table type is default when you create table. MyISAM table work very fast but not transaction-safe. The size of MyISAM table depends on the operating system and the data file are portable from system to system. With MyISAM table type, you can have 64 keys per table and maximum key length of 1024 bytes.

InnoDB

Different from MyISAM table type, InnoDB table are transaction safe and supports row-level locking. Foreign keys are supported in InnoDB tables. The data file of InnoDB table can be stored in more than one file so the size of table depends on the disk space. Like the MyISAM table type, data file of InnoDB is portable from system to system. The disadvantage of InnoDB in comparison with MyISAM is it take more disk space.

BDB

BDB is similar to InnoDB in transaction safe. It supports page level locking but data file are not portable.

MERGE

Merge table type is added to treat multiple MyISAM tables as a single table so it remove the size limitation from MyISAM tables.

HEAP

Heap table is stored in memory so it is the fastest one. Because of storage mechanism, the data will be lost when the power failure and sometime it can cause the server run out of memory. Heap tables do not support columns with AUTO_INCREMENT, BLOB and TEXT characteristics.

Manage Database in MySQL

Creating Database

To create a database in MySQL, you use the CREATE DATABASE statement as follows:

CREATE DATABASE [IF NOT EXISTS] database_name;

CREATE DATABASE statement will create the database with the given name you specified. IF NOT EXISTS is an option part of the statement, this part prevents you from error if there is a database with the given name exists on the database server. In our tutorial, for example, to create classicmodels database, you just only apply CREATE DATABASE statement above as follows:

 CREATE DATABASE classicmodels; 

After executing the statement, the MySQL will returns you a message to indicate whether the execution are successful or not.

Showing Databases

SHOW DATABASE statement will show all databases in your server. You can use this statement to check the database you've created or to see all the databases' name on the server before you create a new database.

SHOW DATABASES;

On my server, the output is :

+--------------------+
| Database           |
+--------------------+
| information_schema |
| classicmodels      |
| mysql              |
+--------------------+
8 rows in set (0.00 sec)

Selecting Database

To select a database which you will work with, you use this statement

USE database_name;

you can select our sample database by using USE statement as follows:

USE classicmodels;

From now you can query the tables' data and do whatever you want inside the selected database

Removing Database

Removing database means you delete the database, all the data and related objects inside the database permanently and cannot undo it. So it is very important to execute this query with cares. To remove the database you can use DROP DATABASE statement as follows :

DROP DATABASE [IF EXISTS] database_name;

Like CREATE DATABASE statement, IF EXIST part is an optional part to prevents you from removing database which is not existed. In order to practice with DROP DATABASE statement, you can create a temporary database, show the database on the database server, and drop it step by step as follows :

CREATE DATABASE IF NOT EXISTS temp_database;
SHOW DATABASES;
DROP DATABASE IF EXISTS temp_database;

MySQL Tutorial

As the world's most popular open source database, MySQL is used by a wide range of organizations to manage their data. With MySQLTutorial.Org website, you can find concise and easy to understand MySQL tutorials step by step with practical examples.