This page focuses on queries that create new tables (CREATE), drop existing tables (DROP), or modify the structure of existing tables (ALTER). In the end, it also describes various types of MySQL columns.
A CREATE query can create both tables and databases. Similarly, a DROP query can delete both tables and databases.
We begin by creating a database since tables are stored as part of a database. In our example of Blue Sky Toys, we have been using the blue_sky_toys database. To create this database from start, we can use the "CREATE DATABASE" query. We can use "IF NOT EXISTS" clause to ensure that we do not get an error if the database already exists.
mysql> CREATE DATABASE IF NOT EXISTS blue_sky_toys; Query OK, 1 row affected, 1 warning (0.00 sec) mysql> SHOW WARNINGS; +-------+------+--------------------------------------------------------+ | Level | Code | Message | +-------+------+--------------------------------------------------------+ | Note | 1007 | Can't create database 'blue_sky_toys'; database exists | +-------+------+--------------------------------------------------------+ 1 row in set (0.00 sec) mysql> USE blue_sky_toys; Database changed
In our case, the blue_sky_toys database exists and so the above output shows a warning when we try to do that: "Query OK, 1 row affected, 1 warning (0.00 sec)". We can use "SHOW WARNINGS" query to show the current warning.
MySQL would not create the database even if we were to not specify "IF NOT EXISTS" clause. But, the important difference is that without "IF NOT EXISTS", MySQL would throw an error instead of a warning. So, using "IF NOT EXISTS" helps us avoid the error.
To drop a database, we can use "DROP DATABASE <name_of_database>" query. Thus, if we needed to drop the blue_sky_toys, the query would be "DROP DATABASE blue_sky_toys;".
Next, let us create all the 4 tables (assuming that they do not exist and we are starting from scratch). To create a table, we use "CREATE TABLE" query followed by the name of the new table. If the table exists, then MySQL would throw an error and to avoid that, once again, we use the "IF NOT EXISTS" qualifier.
mysql> SHOW TABLES;
Empty set (0.00 sec)
mysql> CREATE TABLE IF NOT EXISTS items (item_code INT PRIMARY KEY, item_name CHAR (128),
-> item_type TINYINT UNSIGNED, item_price DECIMAL(10,2));
Query OK, 0 rows affected (0.09 sec)
mysql> CREATE TABLE IF NOT EXISTS employees (employee_id INT PRIMARY KEY,
-> employee_name CHAR (128),
-> date_of_birth DATE, store_id SMALLINT, employee_address CHAR(255));
Query OK, 0 rows affected (0.06 sec)
mysql> CREATE TABLE IF NOT EXISTS stores (store_id SMALLINT PRIMARY KEY,
-> store_location CHAR (128));
Query OK, 0 rows affected (0.07 sec)
mysql> CREATE TABLE IF NOT EXISTS inventory (store_id SMALLINT, item_code INT,
-> item_quantity SMALLINT);
Query OK, 0 rows affected (0.07 sec)
mysql> SHOW TABLES;
+-------------------------+
| Tables_in_blue_sky_toys |
+-------------------------+
| employees |
| inventory |
| items |
| stores |
+-------------------------+
4 rows in set (0.00 sec)
Typically, most of the databases are given a lot of consideration during the design stage so that there would be no changes to the table structures once the system goes live. But, sometimes, we do need to change the structure of a table to accommodate a new requirement that was not present in the initial design.
Fortunately, SQL's ALTER query allows us to modify the structure of existing tables and yet, at the same time, we also get to retain the existing data stored in the table.
The ALTER query works by specifying various various modes: ADD, MODIFY, DROP, and CHANGE. The general format is "ALTER TABLE <table_name> <alter_mode> ...", where the alter_mode is one of the above modes. Thus, to add a new column (column1) to table (table1), we can use: "ALTER TABLE table1 ADD column1;".
Let us use an ALTER query to update the Blue Sky Toys company. Let us say that we need to track the number of leaves per employee in our Blue Sky Toys company. Since we do not have any column to store number of leaves, we can create one using the "ALTER TABLE" query and use the "ADD" mode.
Here is how we can do that using an ALTER query:
mysql> SELECT * FROM employees; +-------------+-----------------+---------------+----------+------------------------------------+ | employee_id | employee_name | date_of_birth | store_id | employee_address | +-------------+-----------------+---------------+----------+------------------------------------+ | 101 | Homer Simpson | 1955-07-23 | 1 | 742 Evergreen Terrace, Springfield | | 103 | Peter Griffin | 1967-06-28 | 2 | 31 Spooner Street, Quahog | | 112 | Ned Flanders | 1960-03-07 | 2 | 470 Evergreen Lane, Quahog | | 120 | Marge Simpson | 1965-05-21 | 1 | 742 Evergreen Terrace, Springfield | | 121 | Lois Griffin | 1968-01-18 | 2 | 31 Spooner Street, Quahog | | 110 | Stan Smith | 1970-12-05 | 3 | 43 Cherry Street, Langely Falls | | 122 | Francine Smith | 1972-07-09 | 3 | 43 Cherry Street, Langely Falls | | 123 | Moe Szylak | 1957-07-21 | 1 | 200 Main Street, Springfield | | 124 | Abraham Simpson | 1935-08-08 | 1 | 200 Lost Road, Springfield | +-------------+-----------------+---------------+----------+------------------------------------+ 9 rows in set (0.00 sec) mysql> ALTER TABLE employees ADD num_of_leaves INT DEFAULT 0; Query OK, 6 rows affected (0.08 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM employees; +-----------+---------------+-------------+--------+-----------------------------------+-------------+ |employee_id|employee_name |date_of_birth|store_id|employee_address |num_of_leaves| +-----------+---------------+-------------+--------+-----------------------------------+-------------+ | 101 |Homer Simpson | 1955-07-23 | 1 | 742 Evergreen Terrace, Springfield| 0 | | 103 |Peter Griffin | 1967-06-28 | 2 | 31 Spooner Street, Quahog | 0 | | 112 |Ned Flanders | 1960-03-07 | 2 | 470 Evergreen Lane, Quahog | 0 | | 120 |Marge Simpson | 1965-05-21 | 1 | 742 Evergreen Terrace, Springfield| 0 | | 121 |Lois Griffin | 1968-01-18 | 2 | 31 Spooner Street, Quahog | 0 | | 110 |Stan Smith | 1970-12-05 | 3 | 43 Cherry Street, Langely Falls | 0 | | 122 |Francine Smith | 1972-07-09 | 3 | 43 Cherry Street, Langely Falls | 0 | | 123 |Moe Szylak | 1957-07-21 | 1 | 200 Main Street, Springfield | 0 | | 124 |Abraham Simpson| 1935-08-08 | 1 | 200 Lost Road, Springfield | 0 | +-----------+---------------+-------------+--------+-----------------------------------+-------------+ 9 rows in set (0.00 sec)
The above query, "ALTER TABLE employees ADD num_of_leaves INT DEFAULT 0;" adds a new column named num_of_leaves and its storage is an integer type with a default value of 0. Please note that for existing records, SQL adds the default value automatically.
If we wanted to keep this column as the first column, then we can also specify the "FIRST" clause towards the end of the "ALTER TABLE" query. However, now that the column is already added by the above query, we need to use the "MODIFY" qualifier (instead of "ADD") in the "ALTER TABLE" statement. The following output shows this modification.
mysql> ALTER TABLE employees MODIFY num_of_leaves INT DEFAULT 0 FIRST; Query OK, 6 rows affected (0.08 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM employees; +-------------+-----------+---------------+-------------+--------+----------------------------------+ |num_of_leaves|employee_id|employee_name |date_of_birth|store_id|employee_address | +-------------+-----------+---------------+-------------+--------+----------------------------------+ | 0 | 101 |Homer Simpson | 1955-07-23 | 1 |742 Evergreen Terrace, Springfield| | 0 | 103 |Peter Griffin | 1967-06-28 | 2 |31 Spooner Street, Quahog | | 0 | 112 |Ned Flanders | 1960-03-07 | 2 |470 Evergreen Lane, Quahog | | 0 | 120 |Marge Simpson | 1965-05-21 | 1 |742 Evergreen Terrace, Springfield| | 0 | 121 |Lois Griffin | 1968-01-18 | 2 |31 Spooner Street, Quahog | | 0 | 110 |Stan Smith | 1970-12-05 | 3 |43 Cherry Street, Langely Falls | | 0 | 122 |Francine Smith | 1972-07-09 | 3 |43 Cherry Street, Langely Falls | | 0 | 123 |Moe Szylak | 1957-07-21 | 1 |200 Main Street, Springfield | | 0 | 124 |Abraham Simpson| 1935-08-08 | 1 |200 Lost Road, Springfield | +-------------+-----------+---------------+-------------+--------+----------------------------------+ 9 rows in set (0.00 sec)
The "MODIFY" clause also allows us to modify the storage type for a given column. As an example, if we decide that having an INT (4 bytes) of space for number of leaves might be an overkill and it would be okay just to have a SMALLINT (2 bytes), then we can easily do this with "MODIFY".
mysql> SHOW COLUMNS FROM employees; +-------------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------+-------------+------+-----+---------+-------+ | num_of_leaves | int(11) | YES | | 0 | | | employee_id | int(11) | NO | PRI | NULL | | | employee_name | char(128) | YES | | NULL | | | date_of_birth | date | YES | | NULL | | | store_id | smallint(6) | YES | | NULL | | | employee_address | char(255) | YES | | NULL | | +-------------------+-------------+------+-----+---------+-------+ 6 rows in set (0.03 sec) mysql> ALTER TABLE employees MODIFY num_of_leaves SMALLINT DEFAULT 0; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW COLUMNS FROM employees; +-------------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------+-------------+------+-----+---------+-------+ | num_of_leaves | smallint(6) | YES | | 0 | | | employee_id | int(11) | NO | PRI | NULL | | | employee_name | char(128) | YES | | NULL | | | date_of_birth | date | YES | | NULL | | | store_id | smallint(6) | YES | | NULL | | | employee_address | char(255) | YES | | NULL | | +-------------------+-------------+------+-----+---------+-------+ 6 rows in set (0.00 sec)
Let us say that we would like to reorder an existing column, then we can use "AFTER" clause with "MODIFY" or even with "ADD" to adjust the column. For example, if we decided that it is probably not a good idea to keep num_of_leaves column as the first column, then we can use "AFTER employee_address" qualifier to reorder the table.
mysql> ALTER TABLE employees MODIFY num_of_leaves SMALLINT DEFAULT 0 AFTER employee_address; Query OK, 6 rows affected (0.38 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> SHOW COLUMNS FROM employees; +-------------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------+-------------+------+-----+---------+-------+ | employee_id | int(11) | NO | PRI | NULL | | | employee_name | char(128) | YES | | NULL | | | date_of_birth | date | YES | | NULL | | | store_id | smallint(6) | YES | | NULL | | | employee_address | char(255) | YES | | NULL | | | num_of_leaves | smallint(6) | YES | | 0 | | +-------------------+-------------+------+-----+---------+-------+ 6 rows in set (0.00 sec)
Or, if we evaluate that based on the requirement, if a column is not needed, then we can always use "ALTER TABLE" with "DROP" mode to drop that column (and its data as well).
mysql> SELECT * FROM employees; +-----------+---------------+-------------+--------+----------------------------------+-------------+ |employee_id|employee_name |date_of_birth|store_id|employee_address |num_of_leaves| +-----------+---------------+-------------+--------+----------------------------------+-------------+ | 101 |Homer Simpson |1955-07-23 | 1 |742 Evergreen Terrace, Springfield| 0 | | 103 |Peter Griffin |1967-06-28 | 2 |31 Spooner Street, Quahog | 0 | | 112 |Ned Flanders |1960-03-07 | 2 |470 Evergreen Lane, Quahog | 0 | | 120 |Marge Simpson |1965-05-21 | 1 |742 Evergreen Terrace, Springfield| 0 | | 121 |Lois Griffin |1968-01-18 | 2 |31 Spooner Street, Quahog | 0 | | 110 |Stan Smith |1970-12-05 | 3 |43 Cherry Street, Langely Falls | 0 | | 122 |Francine Smith |1972-07-09 | 3 |43 Cherry Street, Langely Falls | 0 | | 123 |Moe Szylak |1957-07-21 | 1 |200 Main Street, Springfield | 0 | | 124 |Abraham Simpson|1935-08-08 | 1 |200 Lost Road, Springfield | 0 | +-----------+---------------+-------------+--------+----------------------------------+-------------+ 9 rows in set (0.00 sec) mysql> ALTER TABLE employees DROP num_of_leaves; Query OK, 6 rows affected (0.08 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM employees; +-------------+-----------------+---------------+----------+------------------------------------+ | employee_id | employee_name | date_of_birth | store_id | employee_address | +-------------+-----------------+---------------+----------+------------------------------------+ | 101 | Homer Simpson | 1955-07-23 | 1 | 742 Evergreen Terrace, Springfield | | 103 | Peter Griffin | 1967-06-28 | 2 | 31 Spooner Street, Quahog | | 112 | Ned Flanders | 1960-03-07 | 2 | 470 Evergreen Lane, Quahog | | 120 | Marge Simpson | 1965-05-21 | 1 | 742 Evergreen Terrace, Springfield | | 121 | Lois Griffin | 1968-01-18 | 2 | 31 Spooner Street, Quahog | | 110 | Stan Smith | 1970-12-05 | 3 | 43 Cherry Street, Langely Falls | | 122 | Francine Smith | 1972-07-09 | 3 | 43 Cherry Street, Langely Falls | | 123 | Moe Szylak | 1957-07-21 | 1 | 200 Main Street, Springfield | | 124 | Abraham Simpson | 1935-08-08 | 1 | 200 Lost Road, Springfield | +-------------+-----------------+---------------+----------+------------------------------------+ 9 rows in set (0.00 sec)
If we were to change the name of the column store_id to store_location_id, then we could do that easily with the ALTER query: "ALTER TABLE employees CHANGE store_id store_location_id SMALLINT;"
MySQL allows us to select from a range of column types from which we can select the appropriate type that suits our requirement. Let us go through some of the commonly used column types.
We can use an integer type for a column to store number that do not have any fractional values; basically numbers like, -3, -2, -1, 0, 1, 2, 3 etc. Sometimes, however, we do need values that are also fractions and so for such cases, we can use float types; basically values like -3.52, 0.25, 100.2525 etc.
For integers, MySQL provides options of TINYINT (requires 1 byte), SMALLINT (requires 2 bytes), INT (requires 4 bytes), and BIGINT (requires 8 bytes). Each of these types accept both signed and unsigned numbers. To store the sign, MySQL uses one bit from the specified storage.
For cases, where we know that we would never use a negative number, then we can always use an UNSIGNED qualifier for that; specifying "INT UNSIGNED" for a column would mean that we can only add non-zero numbers (also known as "whole numbers") to that column. Since we don't need the sign bit, UNSIGNED means that we can use the sign's 1 bit for storage as well; thus, we can store more UNSIGNED numbers than signed numbers for the same type.
For storing fractional numbers, MySQL provides three types: DECIMAL (variable size), FLOAT (requires 4 bytes) and DOUBLE (uses 8 bytes). Once again, we can use the UNSIGNED qualifier, if we are certain that we would not require negative values and thus, increase the storage for that column.
Another important thing to note about DECIMAL is that we need to specify the fractional width as well. For example, DECIMAL(3,2) means that we can keep the integer part as 3 and the fractional width as 2 -- this would allow us to store values from the range 999.99 to -999.99. If we do not specify the fractional width, then a number like "999.99" would be truncated to 999 -- probably something that we did not design it for!
What happens when we try to insert a negative value to a field that is UNSIGNED? MySQL would not allow to do that, though this might happen in a more subtle manner that one would expect!
Let us understand this error handling using a simple example. In our database, the items table has a field type item_type which is UNSIGNED TINYINT; let us try to insert a record that passes a negative value for the item_type. From the output below, we find that MySQL issues a warning complaining about the provided input and replaces the negative value with 0. In fact, MySQL tries to store this as a value closest to the allowed range. Since the closest UNSIGNED value to -2 is 0, MySQL simply replaces -2 with 0! Needless to say, we should avoid inserting negative values to fields that have UNSIGNED type!
mysql> INSERT INTO items VALUES(1010, "Dummy Item", -2, 25.00); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> SHOW WARNINGS; +---------+------+----------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------+ | Warning | 1264 | Out of range value for column 'item_type' at row 1 | +---------+------+----------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM items WHERE item_code = 1010; +-----------+------------+-----------+------------+ | item_code | item_name | item_type | item_price | +-----------+------------+-----------+------------+ | 1010 | Dummy Item | 0 | 25.00 | +-----------+------------+-----------+------------+ 1 row in set (0.00 sec)
MySQL also provides a BOOLEAN type which stores a value of 1 byte; BOOLEAN is also known as BOOL or BIT type. If all we have to store is a bit value (1 or 0), then we can be more efficient by using CHAR(0) which only requires a storage of 1 bit.
MySQL provides various storage types for character/text data.
The first type is CHAR() which takes a width as input. Thus, specifying CHAR(100) means the input can hold as much as 100 bytes of data; the maximum storage for CHAR type is 255. If we do not specify the width, then MySQL would take 1 char as default and therefore, it is equivalent of CHAR(1).
If we want storage type to go beyond 255, then we can use VARCHAR() where the total size can go as much as 65,535 bytes.
An alternative to using VARCHAR is that of BLOB or TEXT. Both of which are identical to VARCHAR() and can store upto 65,535 bytes. However, unlike VARCHAR(), BLOB/TEXT do not remove trailing spaces. There are other variants of BLOB/TEXT as well. We have TINYBLOB/TINYTEXT which offers a storage of 255 bytes; MEDIUMBLOB/MEDIUMTEXT, that offers a storage of 16,777,215 bytes and LONGBLOB/LONGTEXT where the storage capacity is upto 4 Gigabytes.
Lastly, let us take a look at field types for time and date related input.
Date: MySQL provides several formats for specifying DATE. The common format is to provide it as "YYYY-MM-DD" and within quotes. As an example, the query, "INSERT INTO employees VALUES(101, "Homer Simpson", "1955-07-23", 1, "742 Evergreen Terrace, Springfield");", inserts a record into employee table and specifies the date of birth of the employee as "1955-07-23". There are other formats to specify date as well (as in YY-MM-DD or YYYY-M-D or YYYY-M-DD), but using "YYYY-MM-DD" provides a consistent and confusion-free formatting.
Time: MySQL provides a handful of format for specifying time as well. The common format is to use "HH:MM:SS". so a value of "14:30:00" represents a time of 2.30PM. We can also use another format of "DD HH:MM:SS", where DD is two digits for specifying the day.
Timestamp: Sometimes, we need to store the current time for an event and for such requirements, MySQL provides a TIMESTAMP time. The format is "YYYY-MM-DD HH:MM:SS". In other words, a TIMESTAMP field records both the date and the time. It is worth noting that for a TIMESTAMP field, if we insert NULL, then it automatically updates the NULL value to the current time.