MySQL by Examples for Beginners

Introduction

MySQL is an open-source, relational database management system (RDMS) created by Michael "Monty" Widenius and David Axmark in 1995. It was owned by a Swedish company called MySQL AB, which was bought over by Sun Microsystems in 2008. Sun Microsystems was acquired by Oracle in 2010. MySQL is successful, not only because it is free (there are many free and open-source databases), but also for its reliability, speed, performance and features.

The mother site for MySQL is www.mysql.com. The ultimate reference for MySQL is the "MySQL Reference Manual", available at http://dev.mysql.com/doc/#manual. The PDF file has over 3700 pages!!

Installing MySQL and Get Started

Read "How to Install MySQL and Get Started" on how to install, customize, and get started with MySQL.

An Example for the Beginners (But not for the dummies)

A MySQL database server contains many databases (or schemas). Each database consists of one or more tables. A table is made up of columns (fields) and rows (records).

The SQL keywords and commands are NOT case-sensitive. They are shown in uppercase in this tutorial for clarity. The names (database names, table names, column names, etc.) are case-sensitive in Unix, but NOT case-sensitive in Windows. Hence, it is best to treat names as case-sensitive.

You can use SHOW DATABASES to list all the existing databases in the server.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

The databases "mysql", "information_schema" and "performance_schema" are system databases used by MySQL. A "test" database is provided during installation for your testing.

Let us begin with a simple example - a "Product Sales" database. A product sales database typically consists of many tables, e.g., products, customers, suppliers, employees, orders, among others. Let's call our database "southwind" (inspired from Microsoft Access' NorthWind sample database), and begin with the first table called "products" with the following columns and rows:

Creating and Deleting a Database - CREATE DATABASE & DROP DATABASE

You can create a new database using SQL statement "CREATE DATABASE databaseName"; and delete a database using "DROP DATABASE databaseName". You could optionally apply condition "IF EXISTS" or "IF NOT EXISTS" to these commands. For example,

mysql> CREATE DATABASE southwind;
Query OK, 1 row affected (0.03 sec)
   
mysql> DROP DATABASE southwind;
Query OK, 0 rows affected (0.11 sec)
   
mysql> CREATE DATABASE IF NOT EXISTS southwind;
Query OK, 1 row affected (0.01 sec)
   
mysql> DROP DATABASE IF EXISTS southwind;
Query OK, 0 rows affected (0.00 sec)

IMPORTANT: Use DROP or DELETE commands with extreme care, as the deleted entity is irrecoverable. THERE IS NO UNDO!!!

MySQL CREATE DATABASE statement uses some defaults. You can issue a "SHOW CREATE DATABASE databaseName" to display the full command and check these default values. We use \G instead of ';' to send the command to the server, and display the results vertically (instead of horizontally). [You may compare the outputs produced by ';' and \G.] Take note that names (such as database name, table name and column name) are enclosed in a pair of back-quote in the form of `name` to allow blank and special characters. The /*!40100 ...... */ is known as version comment, which will only be run if the server is at or above this version number 4.01.00. To check the version of your MySQL server, issue query "SELECT version()".

mysql> CREATE DATABASE IF NOT EXISTS southwind;
Query OK, 1 row affected (0.01 sec)
   
mysql> SHOW CREATE DATABASE southwind \G
*************************** 1. row ***************************
       Database: southwind
Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */
1 row in set (0.00 sec)

Setting the Default Database - USE

The command "USE databaseName" selects a particular database as the default (or current) database. You can refer to a table in the default database directly using tableName; but you need to use the fully-qualified databaseName.tableName to refer to a table not in the default database. In our example, we have a database named "southwind" with a table named "products". If we issue "USE southwind" to set southwind as the default database, we can call the table as "products". Otherwise, we need to reference the table as "southwind.products".

To display the current default database, issue command "SELECT DATABASE()".

Creating and Deleting a Table - CREATE TABLE & DROP TABLE

You can create a new table in the default database using command "CREATE TABLE tableName" and "DROP TABLE tableName". You can also apply condition "IF EXISTS" or "IF NOT EXISTS". To create a table, you need to define all its columns, by providing the columns' name, type, and other properties.

Let's create the table "products" for our database "southwind".

1
  
 
2
 
 
3
 
 
 
 
 
  
 
4
 
 
5
 
 
 
 


6
 
 
7
  
 
 
 
 
 
 
 
 
8
 
 
 
 
 
 
9
 
 
 
 
  
 
 
 
 
 
10
 
 
 
 
 
 
 
 
 
 

mysql> DROP DATABASE IF EXISTS southwind;
Query OK, 1 rows affected (0.31 sec)
   
mysql> CREATE DATABASE southwind;
Query OK, 1 row affected (0.01 sec)
   
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| southwind          |
| ......             |
+--------------------+
   
mysql> USE southwind;
Database changed
   
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| southwind  |
+------------+
   
mysql> SHOW TABLES;
Empty set (0.00 sec)
   
mysql> CREATE TABLE IF NOT EXISTS products (
         productID INT UNSIGNED NOT NULL AUTO_INCREMENT,
         productCode CHAR(3) NOT NULL DEFAULT '',
         name VARCHAR(30) NOT NULL DEFAULT '',
         quantity INT UNSIGNED NOT NULL DEFAULT 0,
         price DECIMAL(10,2) NOT NULL DEFAULT 9999999.99,
         PRIMARY KEY (productID)
       );
Query OK, 0 rows affected (0.08 sec)
   
mysql> SHOW TABLES;
+---------------------+
| Tables_in_southwind |
+---------------------+
| products            |
+---------------------+
   
mysql> DESCRIBE products;
+-------------+------------------+------+-----+------------+----------------+
| Field       | Type             | Null | Key | Default    | Extra          |
+-------------+------------------+------+-----+------------+----------------+
| productID   | int(10) unsigned | NO   | PRI | NULL       | auto_increment |
| productCode | char(3)          | NO   |     |            |                |
| name        | varchar(30)      | NO   |     |            |                |
| quantity    | int(10) unsigned | NO   |     | 0          |                |
| price       | decimal(10,2)    | NO   |     | 9999999.99 |                |
+-------------+------------------+------+-----+------------+----------------+
    
mysql> SHOW CREATE TABLE products \G
*************************** 1. row ***************************
       Table: products
Create Table: CREATE TABLE `products` (
  `productID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `productCode` char(3) NOT NULL DEFAULT '',
  `name` varchar(30) NOT NULL DEFAULT '',
  `quantity` int(10) unsigned NOT NULL DEFAULT '0',
  `price` decimal(10,2) NOT NULL DEFAULT '9999999.99',
  PRIMARY KEY (`productID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Explanation:

1. Remove the database "southwind", if exists. Take note that DROP and DELETE statements are not recoverable.
2. Create the database "southwind" via CREATE DATABASE statement.
3. The "SHOW DATABASES" statement lists all the existing databases in the server. The output confirms that "southwind" database has been created.
4. We set "southwind" as the default database via the USE statement, so as to reference its table directly.
5. The "SELECT DATABASE()" statement displays the current database.
6. The "SHOW TABLES" statement lists all the tables in the default database. At this point of time, "southwind" contains no table (empty set).
7. We create the table "products" via CREATE TABLE statement. We define five columns: productID, productCode, name, quantity and price.
   The data type of column productID is INT UNSIGNED (non-negative integers). The data type of column productCode is CHAR(3) - a fixed-length alphanumeric string of 3 characters. The data type of column name is VARCHAR(30) - a variable-length string of up to 30 characters. We use fix-length string for productCode, as we assume that the productCode contains exactly 3 characters. On the other hand, we use variable-length string for name, as its length varies. The data type of quantity is also INT. The data type of price is DECIMAL(10,2) - a decimal number with 2 decimal places.
   The "NOT NULL" specifies that the column cannot contain a NULL value. We also set the default values of all the columns.
   We set the column productID as the so-called primary key. Values of the primary-key column must be unique. Primary key facilitates fast search. It is also used to reference other tables.
   We also set the column productID to AUTO_INCREMENT. with default starting value of 1. When you insert a NULL (recommended), or 0, or a missing value, into an AUTO_INCREMENT column, the maximum value of that column plus 1 would be inserted. You can also insert a valid value to an AUTO_INCREMENT column, bypassing the auto-increment.
8. The "SHOW TABLES" statement confirms that "products" table has been created.
9. The statement "DESCRIBE tableName" lists all the columns (and their attributes) of a particular table. Take note of the field name and type.
10. Show the complete CREATE TABLE statement used by MySQL to create this table.

The syntax for CREATE TABLE is:

CREATE [[GLOBAL|LOCAL] TEMPOARY] TABLE tableName (
  {columnName {type|domainName}
    [size]
    [columnConstraint...]
    [DEFAULT defaultValue]}, ...
  [tableConstraint], ...
  [ON COMMIT {DELETE|PRESERVE} ROWS]
)

In the syntax, [] means optional; {} means required, | indicates options; ... indicates repeat.

UNIQUE:

• When used as a table constraint, UNIQUE(column1,..., columnN) specifies that no two rows should have the same combination for column1 to columnN.
• When used as a column constraint, it disallows duplicate value within the column.

PRIMARY KEY:

• When used as a table constraint, e.g., PRIMARY KEY(column1,..., columnN) specifies more than one columns as the primary key, i.e., the combination of these columns is unique.
• When used as a column constraint, it designates that column as the primary key for the table, e.g., productID INT PRIMARY KEY.

Inserting Rows - INSERT INTO

Use the INSERT INTO command to insert a row, with the following syntax:

INSERT INTO tableName VALUES (column1Value, column2Value, ...)

You need to list the values in the same order in which the columns are defined in the table, separated by comma. For columns of string data type (CHAR and VARCHAR), enclosed the value with a pair of single-quotes (or double quotes). For columns of numeric data type (INT and FLOAT), simply put in the number.

You can also insert multiple rows in one INSERT INTO command:

INSERT INTO tableName VALUES (column1Row1Value, column2Row1Value, ...),
                             (column1Row2Value, column2Row2Value, ...), ... 

To insert values on selected columns only, use:

INSERT INTO tableName (column1Name, ..., columnNName) VALUES (column1Value, columnNValue, ...)

The remaining columns will receive their default values (such as NULL or AUTO_INCREMENT).

Let's fill up our "products" table with rows. We set the productID of the first record to 1001, and use AUTO_INCREMENT for the rests, by inserting a NULL, or with a missing column value. Take note that strings must be enclosed with a pair of single quotes.

mysql> INSERT INTO products VALUES (1001, 'PEN', 'Pen Red', 5000, 1.23);
Query OK, 1 row affected (0.04 sec)
 
mysql> INSERT INTO products (productCode, name, quantity, price) 
                     VALUES ('PEN', 'Pen Blue', 8000, 1.25);
Query OK, 1 row affected (0.03 sec)
 
mysql> INSERT INTO products VALUES (null, 'PEN', 'Pen Black', 2000, 1.25),
                                   (null, 'PEC', 'Pencil 2B', 10000, 0.48);
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

Querying the Database - SELECT

The most common, important and complex task is to query a database for a subset of data that meets your needs, with the SELECT command. The SELECT command has the following syntax:

-- List all the rows of the SELECTED columns
SELECT column1Name, column2Name, ... FROM tableName
   
-- List all the rows of ALL columns, '*' is a wildcard which denotes all columns
SELECT * FROM tableName
  
-- List only if the rows meet the specified criteria
SELECT column1Name, column2Name,... FROM tableName WHERE criteria
SELECT * FROM tableName WHERE criteria

For examples,

-- List all rows for the specified columns
mysql> SELECT name, price FROM products;
+-----------+-------+
| name      | price |
+-----------+-------+
| Pen Red   |  1.23 |
| Pen Blue  |  1.25 |
| Pen Black |  1.25 |
| Pencil 2B |  0.48 |
+-----------+-------+
4 rows in set (0.00 sec)
   
-- List all rows of ALL the columns, * denotes ALL columns
mysql> SELECT * FROM products;
+-----------+-------------+-----------+----------+-------+
| productID | productCode | name      | quantity | price |
+-----------+-------------+-----------+----------+-------+
|      1001 | PEN         | Pen Red   |     5000 |  1.23 |
|      1002 | PEN         | Pen Blue  |     8000 |  1.25 |
|      1003 | PEN         | Pen Black |     2000 |  1.25 |
|      1004 | PEC         | Pencil 2B |    10000 |  0.48 |
+-----------+-------------+-----------+----------+-------+
4 rows in set (0.00 sec)

WHERE Clause

You can use a WHERE clause to specify the selection criteria. For numbers, you could use comparison operators: '=' (equal to), '<>' or '!=' (not equal to), '>' (greater than), '<' (less than), '>=' (greater than or equal to), '<=' (less than or equal to), to compare two numbers. For example, price > 1.0, quantity < 500. (Caution: Do not use '=' or '!=' to compare floating point numbers, as they are not precise.)

mysql> SELECT name, price FROM products WHERE price > 1.0;
+-----------+-------+
| name      | price |
+-----------+-------+
| Pen Red   |  1.23 |
| Pen Blue  |  1.25 |
| Pen Black |  1.25 |
+-----------+-------+
3 rows in set (0.04 sec)
   
mysql> SELECT name, quantity FROM products WHERE quantity = 2000;
+-----------+----------+
| name      | quantity |
+-----------+----------+
| Pen Black |     2000 |
+-----------+----------+
1 row in set (0.00 sec)

For strings, you could also use '=', '<>', '>', '<', '>=', '<=' to compare two strings (e.g., productCode = 'PEC'). Take note that strings must be enclosed with a pair of single quote (or double quotes). The strings are normally ordered in an alphanumeric order and case insensitive. For example,

mysql> SELECT name, price FROM products WHERE productCode = 'PEC';
+-----------+-------+
| name      | price |
+-----------+-------+
| Pencil 2B |  0.48 |
+-----------+-------+
1 row in set (0.00 sec)

String Pattern Matching - LIKE and NOT LIKE

For strings, in addition to full matching using operators like '=' and '!=', we can perform pattern matching using operator LIKE (or NOT LIKE). In pattern matching, we could use wildcard '_' to match any single character, and '%' to match an arbitrary number of characters (including zero character). For example,

• 'abc%' for strings beginning with 'abc';
• '%xyz' for strings ending with 'xyz';
• '%aaa%' for strings containing 'aaa';
• '___' for strings containing exactly three characters; and
• 'a_b%' for strings beginning with 'a', followed by any single character, followed by 'b', followed by zero or more characters.

-- name beginning with 'PENCIL'
mysql> SELECT name, price FROM products WHERE name LIKE 'PENCIL%';
+-----------+-------+
| name      | price |
+-----------+-------+
| Pencil 2B |  0.48 |
+-----------+-------+
   
-- productCode beginning with 'P', followed by any two characters
mysql> SELECT name, price FROM products WHERE productCode LIKE 'P__';
+-----------+-------+
| name      | price |
+-----------+-------+
| Pen Red   |  1.23 |
| Pen Blue  |  1.25 |
| Pen Black |  1.25 |
| Pencil 2B |  0.48 |
+-----------+-------+

AND, OR, NOT

You can combine multiple conditions with boolean operators AND and OR. You can also invert a condition using operator NOT. For examples,

mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %';
+-----------+-------------+----------+----------+-------+
| productID | productCode | name     | quantity | price |
+-----------+-------------+----------+----------+-------+
|      1001 | PEN         | Pen Red  |     5000 |  1.23 |
|      1002 | PEN         | Pen Blue |     8000 |  1.25 |
+-----------+-------------+----------+----------+-------+
   
mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %';
+-----------+-------------+---------+----------+-------+
| productID | productCode | name    | quantity | price |
+-----------+-------------+---------+----------+-------+
|      1001 | PEN         | Pen Red |     5000 |  1.23 |
+-----------+-------------+---------+----------+-------+
   
mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %');
+-----------+-------------+-----------+----------+-------+
| productID | productCode | name      | quantity | price |
+-----------+-------------+-----------+----------+-------+
|      1003 | PEN         | Pen Black |     2000 |  1.25 |
|      1004 | PEC         | Pencil 2B |    10000 |  0.48 |
+-----------+-------------+-----------+----------+-------+

IN, NOT IN, BETWEEN, NOT BETWEEN

You can select from members of a set with IN operator, or NOT IN. It is easier and more convenient to use IN than the equivalent AND-OR expression.

mysql> SELECT * FROM products WHERE name IN ('Pen Red', 'Pen Black');
+-----------+-------------+-----------+----------+-------+
| productID | productCode | name      | quantity | price |
+-----------+-------------+-----------+----------+-------+
|      1001 | PEN         | Pen Red   |     5000 |  1.23 |
|      1003 | PEN         | Pen Black |     2000 |  1.25 |
+-----------+-------------+-----------+----------+-------+

To check if the value of a column is within a range, you could use BETWEEN operator as follow. Again, it is more convenient to use BETWEEN than the equivalent AND-OR expression. BETWEEN and NOT BETWEEN are useful in checking a DATE range (to be discussed later).

mysql> SELECT * FROM products 
       WHERE (price BETWEEN 1.0 AND 2.0) AND (quantity BETWEEN 1000 AND 2000);
+-----------+-------------+-----------+----------+-------+
| productID | productCode | name      | quantity | price |
+-----------+-------------+-----------+----------+-------+
|      1003 | PEN         | Pen Black |     2000 |  1.25 |
+-----------+-------------+-----------+----------+-------+

IS NULL, IS NOT NULL

You can checking if a column contains NULL values by IS NULL or IS NOT NULL. For example,

mysql> SELECT * FROM products WHERE productCode IS NULL
Empty set (0.00 sec)

DISTINCT

A column may have duplicate values, you could use keyword DISTINCT to select only distinct values, or combination of distinct values of multiple columns. For examples,

mysql> SELECT price FROM products;
+-------+
| price |
+-------+
|  1.23 |
|  1.25 |
|  1.25 |
|  0.48 |
+-------+
4 rows in set (0.00 sec)
   
-- Distinct price
mysql> SELECT DISTINCT price FROM products;
+-------+
| price |
+-------+
|  1.23 |
|  1.25 |
|  0.48 |
+-------+
3 rows in set (0.00 sec)
   
-- Distinct combination of price and name
mysql> SELECT DISTINCT price, name FROM products;
+-------+-----------+
| price | name      |
+-------+-----------+
|  1.23 | Pen Red   |
|  1.25 | Pen Blue  |
|  1.25 | Pen Black |
|  0.48 | Pencil 2B |
+-------+-----------+
4 rows in set (0.00 sec)

ORDER BY Clause

You can re-order the rows selected using ORDER BY clause, with the following syntax:

SELECT ... FROM tableName WHERE criteria ORDER BY columnAName ASC|DESC, columnBName ASC|DESC, ...

The selected row will be ordered according to the values in columnA, in either ascending (ASC) (default) or descending (DESC) order. If multiple rows have the same value in columnA, it will be ordered according to columnB, and so on. For strings, the ordering could be case-sensitive or case-insensitive, depending on the configuration (the so-called character collating sequence). For examples,

-- Order the results by price in descending order
mysql> SELECT * FROM products WHERE name LIKE 'Pen %' ORDER BY price DESC;
+-----------+-------------+-----------+----------+-------+
| productID | productCode | name      | quantity | price |
+-----------+-------------+-----------+----------+-------+
|      1002 | PEN         | Pen Blue  |     8000 |  1.25 |
|      1003 | PEN         | Pen Black |     2000 |  1.25 |
|      1001 | PEN         | Pen Red   |     5000 |  1.23 |
+-----------+-------------+-----------+----------+-------+
   
-- Order by price in descending order, followed by quantity in ascending (default) order
mysql> SELECT * FROM products WHERE name LIKE 'Pen %' ORDER BY price DESC, quantity;
+-----------+-------------+-----------+----------+-------+
| productID | productCode | name      | quantity | price |
+-----------+-------------+-----------+----------+-------+
|      1003 | PEN         | Pen Black |     2000 |  1.25 |
|      1002 | PEN         | Pen Blue  |     8000 |  1.25 |
|      1001 | PEN         | Pen Red   |     5000 |  1.23 |
+-----------+-------------+-----------+----------+-------+

LIMIT Clause

A SELECT query on a large database may product many rows. You could use the LIMIT clause to limit the number of rows displayed, e.g.,

-- Display the first two rows
mysql> SELECT * FROM products ORDER BY price LIMIT 2;
+-----------+-------------+-----------+----------+-------+
| productID | productCode | name      | quantity | price |
+-----------+-------------+-----------+----------+-------+
|      1004 | PEC         | Pencil 2B |    10000 |  0.48 |
|      1001 | PEN         | Pen Red   |     5000 |  1.23 |
+-----------+-------------+-----------+----------+-------+
2 rows in set (0.00 sec)

To continue to the following records, you could specify the number of rows to be skipped, followed by the number of rows to be displayed in the LIMIT clause, as follows:

-- Skip the first two rows and display the next 1 row
mysql> SELECT * FROM products ORDER BY price LIMIT 2, 1;
+-----------+-------------+----------+----------+-------+
| productID | productCode | name     | quantity | price |
+-----------+-------------+----------+----------+-------+
|      1002 | PEN         | Pen Blue |     8000 |  1.25 |
+-----------+-------------+----------+----------+-------+
1 row in set (0.00 sec)

GROUP BY clause

The GROUP BY clause allows you to collapse multiple records with a common value into groups. GROUP BY is often used together with functions, e.g., to find the average price of books by each author. See example below.

Functions

To count the rows selected, you could use the function COUNT(*). For example,

mysql> SELECT COUNT(*) FROM products;
+----------+
| COUNT(*) |
+----------+
|        4 |
+----------+
1 row in set (0.03 sec)
   
mysql> SELECT COUNT(*) FROM products WHERE name LIKE 'Pen %';
+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

You can use the COUNT(*) function and GROUP BY clause to count each group of rows. For example,

-- Count rows of different prices
mysql> SELECT price, COUNT(*) FROM products GROUP BY price;
+-------+----------+
| price | COUNT(*) |
+-------+----------+
|  0.48 |        1 |
|  1.23 |        1 |
|  1.25 |        2 |
+-------+----------+
3 rows in set (0.00 sec)

Besides COUNT(), there are many other functions such as AVG(), MAX(), MIN(), SUM(), etc. For example,

mysql> SELECT MAX(price), MIN(price), AVG(price), SUM(quantity) FROM products;
+------------+------------+------------+---------------+
| MAX(price) | MIN(price) | AVG(price) | SUM(quantity) |
+------------+------------+------------+---------------+
|       1.25 |       0.48 |   1.052500 |         25000 |
+------------+------------+------------+---------------+
1 row in set (0.00 sec)

AS - Alias

You could use the keyword AS to create an alias for a name (such as column name, table name), which could be used in displaying the name, and future reference. For example,

mysql> SELECT MAX(price) AS `Highest Price`, MIN(price) AS `Lowest Price` FROM products;
+---------------+--------------+
| Highest Price | Lowest Price |
+---------------+--------------+
|          1.25 |         0.48 |
+---------------+--------------+
1 row in set (0.06 sec)

In the above example, we need to back-quote the names `...` because the names contain a special character (blank).

You can also concatenate a few columns as one (e.g., joining the last name and first name) using function CONCAT(). For example,

mysql> SELECT CONCAT(productCode, ' - ', name) AS `Product Description`, price FROM products;
+---------------------+-------+
| Product Description | price |
+---------------------+-------+
| PEN - Pen Red       |  1.23 |
| PEN - Pen Blue      |  1.25 |
| PEN - Pen Black     |  1.25 |
| PEC - Pencil 2B     |  0.48 |
+---------------------+-------+
4 rows in set (0.00 sec)

Modifying Data - UPDATE ... SET

To modify existing data, use UPDATE command, with the following syntax:

UPDATE tableName SET columnName = {value|NULL|DEFAULT}, ... WHERE criteria

For example,

-- Increase the price by 10% for all products
mysql> UPDATE products SET price = price * 1.1;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0
   
mysql> SELECT * FROM products;
+-----------+-------------+-----------+----------+-------+
| productID | productCode | name      | quantity | price |
+-----------+-------------+-----------+----------+-------+
|      1001 | PEN         | Pen Red   |     5000 |  1.35 |
|      1002 | PEN         | Pen Blue  |     8000 |  1.38 |
|      1003 | PEN         | Pen Black |     2000 |  1.38 |
|      1004 | PEC         | Pencil 2B |    10000 |  0.53 |
+-----------+-------------+-----------+----------+-------+
4 rows in set (0.00 sec)
   
-- Modify selected rows
mysql> UPDATE products SET quantity = quantity - 100 WHERE name = 'Pen Red';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
   
mysql> SELECT * FROM products WHERE name = 'Pen Red';
+-----------+-------------+---------+----------+-------+
| productID | productCode | name    | quantity | price |
+-----------+-------------+---------+----------+-------+
|      1001 | PEN         | Pen Red |     4900 |  1.35 |
+-----------+-------------+---------+----------+-------+
1 row in set (0.00 sec)
   
-- You can modify more than one values
mysql> UPDATE products SET quantity = quantity + 50, price = 1.23 WHERE name = 'Pen Red';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
   
mysql> SELECT * FROM products WHERE name = 'Pen Red';
+-----------+-------------+---------+----------+-------+
| productID | productCode | name    | quantity | price |
+-----------+-------------+---------+----------+-------+
|      1001 | PEN         | Pen Red |     4950 |  1.23 |
+-----------+-------------+---------+----------+-------+
1 row in set (0.00 sec)

IMPORTANT: If the WHERE clause is omitted in the UPDATE command, all rows will be updated. Hence, it is a good practice to issue a SELECT query, using the same criteria, to check the result before issuing the UPDATE. This also applies to the DELETE command in the next section.

Deleting Rows - DELETE FROM

Use the DELELE FROM command to delete row(s) from a table, with the following syntax:

-- Delete all rows from the table, use with extreme care! Not recoverable!!!
DELETE FROM tableName
-- Delete only row(s) that meets the criteria
DELETE FROM tableName WHERE criteria

For example,

mysql> DELETE FROM products WHERE name LIKE 'Pencil%';
Query OK, 1 row affected (0.00 sec)
   
mysql> SELECT * FROM products;
+-----------+-------------+-----------+----------+-------+
| productID | productCode | name      | quantity | price |
+-----------+-------------+-----------+----------+-------+
|      1001 | PEN         | Pen Red   |     4950 |  1.23 |
|      1002 | PEN         | Pen Blue  |     8000 |  1.38 |
|      1003 | PEN         | Pen Black |     2000 |  1.38 |
+-----------+-------------+-----------+----------+-------+
  
-- Use this with extreme care, as the deleted records are irrecoverable!
mysql> DELETE FROM products;
Query OK, 3 rows affected (0.00 sec)
   
mysql> SELECT * FROM products;
Empty set (0.00 sec)

Loading Data from a Text File and Export Data To a Text File - LOAD DATA LOCAL INFILE & SELECT INTO OUTFILE

Besides using INSERT statement to insert rows, you could keep your data in a text file, and load them into the table via the LOAD DATA command. For example, create the following text file called "products_in.csv", where the values are separated by ','. The file extension ".csv" stands for Comma-Separated Values text file.

\N,PEC,Pencil 3B,500,0.52
\N,PEC,Pencil 4B,200,0.62
\N,PEC,Pencil 5B,100,0.73
\N,PEC,Pencil 6B,500,0.47

You can load the data into the products table as follows:

mysql> LOAD DATA LOCAL INFILE 'd:/path-to/products_in.csv' INTO TABLE products
         COLUMNS TERMINATED BY ','
         LINES TERMINATED BY '\r\n';
Query OK, 4 rows affected (0.00 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0
   
mysql> SELECT * FROM products;
+-----------+-------------+-----------+----------+-------+
| productID | productCode | name      | quantity | price |
+-----------+-------------+-----------+----------+-------+
|      1005 | PEC         | Pencil 3B |      500 |  0.52 |
|      1006 | PEC         | Pencil 4B |      200 |  0.62 |
|      1007 | PEC         | Pencil 5B |      100 |  0.73 |
|      1008 | PEC         | Pencil 6B |      500 |  0.47 |
+-----------+-------------+-----------+----------+-------+

Notes:

• You need to provide the path (absolute or relative) and the filename. Use Unix-style forward-slash '/' as the directory separator, instead of Windows-style back-slash '\'.
• The default line delimiter (or end-of-line) is '\n' (Unix-style). If the text file is prepared in Windows, you need to include LINES TERMINATED BY '\r\n'. For Mac, use LINES TERMINATED BY '\r'.
• The default column delimiter is "tab". If you use another delimiter, e.g. ',', include COLUMNS TERMINATED BY ','.
• You must use \N (back-slash + uppercase 'N') for NULL.

Complimenting LOAD DATA command, you can use SELECT ... INTO OUTFILE fileName FROM tableName to export data from a table to a text file. For example,

mysql> SELECT * FROM products INTO OUTFILE 'd:/path-to/products_out.csv' 
         COLUMNS TERMINATED BY ','
         LINES TERMINATED BY '\r\n';
Query OK, 4 rows affected (0.00 sec)

You can also use utility program "mysqldump" to export table data to an external file, for replication or backup (to be discussed later).

Running a SQL Script

Instead of manually enter each of the SQL commands, you can store SQL statements in a text file, called SQL script and run the script. For example, use a text editor to prepare the following script and save as "beginners_script.sql" in a directory of your choice (e.g., "d:\myproject").

DELETE FROM products;
INSERT INTO products VALUES (1005, 'PEC', 'Pencil 3B', 500, 0.52),
                            (1006, 'PEC', 'Pencil 4B', 200, 0.62),
                            (1007, 'PEC', 'Pencil 5B', 100, 0.73),
                            (1008, 'PEC', 'Pencil 6B', 500, 0.47);
SELECT * FROM products;

You can then use the "source" command to run the script:

mysql> source d:/myproject/beginners_script.sql

Notes:

• You should provide the full-path filename for the script.
• Use Unix's forward slash '/' as directory separator in the source command, instead of Windows' back slash '\'.

More Than One Tables

Our example so far involves only one table "products". A practical database contains many related tables.

Products have suppliers. If each product has one supplier, and each supplier supplies only one product (known as one-to-one relationship), we can simply add the supplier's data (name, address, phone number) into the products table. Suppose that each product has one supplier, and a supplier may supply zero or more products (known as one-to-many relationship). Putting the supplier's data into the Product table results in duplication of data (one supplier may supply many products, hence, the same supplier's data appear in many rows), which not only wastes the storage but also easily leads to inconsistency (because all duplicate data must be updated simultaneously). The situation is even more complicated if one product has many suppliers, and each supplier can supply many products, in a many-to-many relationship.

One-To-Many Relationship

Suppose that each product has one supplier, and each supplier supplies one or more products. We could create a table called suppliers to store supplier's information (e.g., name, address and phone number). We also create a column with unique value called supplierID to identify every suppliers. We choose supplierID as the primary key for the table suppliers (to ensure uniqueness and facilitate fast search).

To relate the suppliers table to the products table, we add the primary key of the suppliers table, i.e., supplierID, as a new column in products table. For example,

We shall first create the suppliers table (because the products table now references the suppliers table) as follows:

mysql> USE southwind;
Database changed
   
mysql> DROP TABLE IF EXISTS suppliers;
Query OK, 0 rows affected (0.05 sec)
   
mysql> CREATE TABLE suppliers (
         supplierID INT UNSIGNED NOT NULL AUTO_INCREMENT, 
         name VARCHAR(30) NOT NULL DEFAULT '', 
         phone CHAR(8) NOT NULL  DEFAULT '',
         PRIMARY KEY (supplierID)
       );
Query OK, 0 rows affected (0.13 sec)
   
mysql> DESCRIBE suppliers;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| supplierID | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name       | varchar(30)      | NO   |     |         |                |
| phone      | char(8)          | NO   |     |         |                |
+------------+------------------+------+-----+---------+----------------+
   
mysql> INSERT INTO suppliers VALUE (501, 'ABC Traders', '88881111'), 
                                   (502, 'XYZ Company', '88882222'), 
                                   (503, 'QQ Crop', '88883333');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
   
mysql> SELECT * FROM suppliers;
+------------+-------------+----------+
| supplierID | name        | phone    |
+------------+-------------+----------+
|        501 | ABC Traders | 88881111 |
|        502 | XYZ Company | 88882222 |
|        503 | QQ Crop     | 88883333 |
+------------+-------------+----------+
3 rows in set (0.00 sec)

ALTER TABLE

Instead of deleting and re-creating the products table, we shall use the statement "ALTER TABLE" to add a new column supplierID to the products table, and set the default value to one of the valid supplierID from the suppliers table (e.g., 501). We need a valid default value because we will be adding a so-called foreign key constraint on this column to reference the suppliers table. Values in the foreign-key column must match one of the values in the corresponding column in the referenced table.

mysql> ALTER TABLE products
         ADD COLUMN supplierID INT UNSIGNED NOT NULL DEFAULT 501;
Query OK, 4 rows affected (0.13 sec)
Records: 4  Duplicates: 0  Warnings: 0
   
mysql> DESCRIBE products;
+-------------+------------------+------+-----+------------+----------------+
| Field       | Type             | Null | Key | Default    | Extra          |
+-------------+------------------+------+-----+------------+----------------+
| productID   | int(10) unsigned | NO   | PRI | NULL       | auto_increment |
| productCode | char(3)          | NO   |     |            |                |
| name        | varchar(30)      | NO   |     |            |                |
| quantity    | int(10) unsigned | NO   |     | 0          |                |
| price       | decimal(10,2)    | NO   |     | 9999999.99 |                |
| supplierID  | int(10) unsigned | NO   |     | 501        |                |
+-------------+------------------+------+-----+------------+----------------+

Now, add a foreign key constraint to relate the two tables: the supplierID in products table is a foreign key references supplierID of suppliers table.

mysql> ALTER TABLE products
         ADD FOREIGN KEY (supplierID) REFERENCES suppliers (supplierID);
Query OK, 4 rows affected (0.26 sec)
Records: 4  Duplicates: 0  Warnings: 0
  
mysql> DESCRIBE products;
+-------------+------------------+------+-----+------------+----------------+
| Field       | Type             | Null | Key | Default    | Extra          |
+-------------+------------------+------+-----+------------+----------------+
  ......
| supplierID  | int(10) unsigned | NO   | MUL | 501        |                |
+-------------+------------------+------+-----+------------+----------------+
 
mysql> UPDATE products SET supplierID = 501 WHERE productID IN (1005, 1006, 1007);
Query OK, 3 rows affected (0.04 sec)
Rows matched: 3  Changed: 3  Warnings: 0
   
mysql> UPDATE products SET supplierID = 502 WHERE productID IN (1008);
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
   
mysql> SELECT * FROM products;
+-----------+-------------+-----------+----------+-------+------------+
| productID | productCode | name      | quantity | price | supplierID |
+-----------+-------------+-----------+----------+-------+------------+
|      1005 | PEC         | Pencil 3B |      500 |  0.52 |        501 |
|      1006 | PEC         | Pencil 4B |      200 |  0.62 |        501 |
|      1007 | PEC         | Pencil 5B |      100 |  0.73 |        501 |
|      1008 | PEC         | Pencil 6B |      500 |  0.47 |        502 |
+-----------+-------------+-----------+----------+-------+------------+

The syntax for ALTER TABLE is as follows:

ALTER TABLE tableName
  {ADD [COLUMN] columnName columnDefinition}
  {ALTER|MODIFY [COLUMN] columnName columnDefinition
    {SET DEFAULT columnDefaultValue} | {DROP DEFAULT}}
  {DROP [COLUMN] columnName [RESTRICT|CASCADE]}
  {ADD tableConstraint}
  {DROP tableConstraint [RESTRICT|CASCADE]}

SELECT with JOIN

SELECT command can be used to query and join data from two related tables. For example, to list the product's name and supplier's name, we could use the WHERE clause to link the products table's supplierID to Supplier table's supplierID, as follows:

mysql> SELECT products.name, price, suppliers.name FROM products, suppliers 
       WHERE products.supplierID = suppliers.supplierID;
+-----------+-------+-------------+
| name      | price | name        |
+-----------+-------+-------------+
| Pencil 3B |  0.52 | ABC Traders |
| Pencil 4B |  0.62 | ABC Traders |
| Pencil 5B |  0.73 | ABC Traders |
| Pencil 6B |  0.47 | XYZ Company |
+-----------+-------+-------------+

Notes:

• Since both tables have a column called name, to differentiate the columns, we need to qualify them with the table name, e.g., products.name, suppliers.name. Since the column price can be found in products table only, there is no need to use the table name to reference this column.
• We need to include both tables in the FROM clause.
• The WHERE clause links the two tables.

In the above query, two of the columns have the same heading "name". You could create an alias to a column using keyword AS.

mysql> SELECT products.name AS `Product Name`, price, suppliers.name AS `Supplier Name` 
       FROM products, suppliers
       WHERE products.supplierID = suppliers.supplierID;
+--------------+-------+---------------+
| Product Name | price | Supplier Name |
+--------------+-------+---------------+
| Pencil 3B    |  0.52 | ABC Traders   |
| Pencil 4B    |  0.62 | ABC Traders   |
| Pencil 5B    |  0.73 | ABC Traders   |
| Pencil 6B    |  0.47 | XYZ Company   |
+--------------+-------+---------------+

Take note that we have to enclosed the `Product Name` and `Supplier Name` by a pair of back-quote `...`, because there is a blank in the name.

The database diagram is as follows. The link indicates a one-to-many relationship.

Many-To-Many Relationship

Suppose that a product has many suppliers; and a supplier supplies many products in a so-called many-to-many relationship. The above solution breaks. You cannot include the supplierID in the products table, as you cannot determine the number of suppliers, and hence, the number of columns needed for the suppliers. Similarly, you cannot include the productID in the suppliers table, as you cannot determine the number of products.

To resolve this problem, you need to create a new table, known as a junction table, to provide the linkage. Let's call the junction table products_suppliers.

Let's create the products_suppliers table. The primary key of the table consists of two columns: productID and supplierID, as the combination uniquely identify each rows. This primary key is defined to ensure uniqueness. Two foreign keys are defined to set the constraint to two tables.

mysql> CREATE TABLE products_suppliers (
         productID INT UNSIGNED NOT NULL,
         supplierID INT UNSIGNED NOT NULL,
         PRIMARY KEY (productID, supplierID),
         FOREIGN KEY (productID) REFERENCES products (productID),
         FOREIGN KEY (supplierID) REFERENCES suppliers (supplierID)
       );
Query OK, 0 rows affected (0.09 sec)
   
mysql> DESCRIBE products_suppliers;
+------------+------------------+------+-----+---------+-------+
| Field      | Type             | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| productID  | int(10) unsigned | NO   | PRI | NULL    |       |
| supplierID | int(10) unsigned | NO   | PRI | NULL    |       |
+------------+------------------+------+-----+---------+-------+
  
mysql> INSERT INTO products_suppliers VALUES (1005, 501), (1006, 501),
       (1007, 501), (1008, 502), (1005, 503);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0
-- Values in the foreign-key columns must match one of the values in the columns they reference.
   
mysql> SELECT * FROM products_suppliers;
+-----------+------------+
| productID | supplierID |
+-----------+------------+
|      1005 |        501 |
|      1006 |        501 |
|      1007 |        501 |
|      1008 |        502 |
|      1005 |        503 |
+-----------+------------+

Next, remove the supplierID column from the products table. [This column was added to establish the one-to-many relationship. It is not needed in the many-to-many relationship.]

Before this column can be removed, you need to remove the foreign key that builds on this column. To remove a key in MySQL, you need to know its constraint_name, which was generated by the system. To find out the constraint_name, issue a "SHOW CREATE TABLE products" and take note of the foreign key's constraint_name in clause "CONSTRAINT constraint_name FOREIGN KEY ....". You can then drop the foreign key by "ALTER TABLE products DROP FOREIGN KEY constraint_name"

mysql> SHOW CREATE TABLE products;
Create Table: CREATE TABLE `products` (
  `productID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `productCode` char(3) NOT NULL DEFAULT '',
  `name` varchar(30) NOT NULL DEFAULT '',
  `quantity` int(10) unsigned NOT NULL DEFAULT '0',
  `price` decimal(10,2) NOT NULL DEFAULT '9999999.99',
  `supplierID` int(10) unsigned NOT NULL DEFAULT '501',
  PRIMARY KEY (`productID`),
  KEY `supplierID` (`supplierID`),
  CONSTRAINT `products_ibfk_1` FOREIGN KEY (`supplierID`) 
     REFERENCES `suppliers` (`supplierID`)
) ENGINE=InnoDB AUTO_INCREMENT=1012 DEFAULT CHARSET=latin1
 
mysql> ALTER TABLE products DROP FOREIGN KEY products_ibfk_1;
Query OK, 4 rows affected (0.21 sec)
Records: 4  Duplicates: 0  Warnings: 0

Now, you can remove the column supplierID.

mysql> ALTER TABLE products DROP supplierID;
Query OK, 4 rows affected (0.13 sec)
Records: 4  Duplicates: 0  Warnings: 0

You can use SELECT command to join the data together from the three tables:

mysql> SELECT products.name AS `Product Name`, suppliers.name AS `Supplier Name`
       FROM products, suppliers, products_suppliers 
       WHERE products.productID = products_suppliers.productID 
         AND products_suppliers.supplierID = suppliers.supplierID;
+--------------+---------------+
| Product Name | Supplier Name |
+--------------+---------------+
| Pencil 3B    | ABC Traders   |
| Pencil 3B    | QQ Crop       |
| Pencil 4B    | ABC Traders   |
| Pencil 5B    | ABC Traders   |
| Pencil 6B    | XYZ Company   |
+--------------+---------------+
    
mysql> SELECT products.name AS `Product Name`, suppliers.name AS `Supplier Name`
       FROM products, suppliers, products_suppliers 
       WHERE products.productID = products_suppliers.productID 
         AND products_suppliers.supplierID = suppliers.supplierID
         AND products.name = 'Pencil 3B';
+--------------+---------------+
| Product Name | Supplier Name |
+--------------+---------------+
| Pencil 3B    | ABC Traders   |
| Pencil 3B    | QQ Crop       |
+--------------+---------------+
    
mysql> SELECT products.name AS `Product Name`, suppliers.name AS `Supplier Name`
       FROM products, suppliers, products_suppliers 
       WHERE products.productID = products_suppliers.productID 
         AND products_suppliers.supplierID = suppliers.supplierID
         AND suppliers.supplierID = 501;
+--------------+---------------+
| Product Name | Supplier Name |
+--------------+---------------+
| Pencil 3B    | ABC Traders   |
| Pencil 4B    | ABC Traders   |
| Pencil 5B    | ABC Traders   |
+--------------+---------------+

The database diagram is as follows. Both products and suppliers tables exhibit a one-to-many relationship to the junction table.

One-to-one Relationship

Suppose that some products have optional information (e.g., photos, comments). Instead of keeping these optional data in the products table, we could create another table called product_details, and link to products with a one-to-one relationship.

mysql> CREATE TABLE product_details (
          productID INT UNSIGNED NOT NULL,
          comment VARCHAR(1024) NOT NULL DEFAULT '',
          PRIMARY KEY (productID),
          FOREIGN KEY (productID) REFERENCES products (productID)
       );
Query OK, 0 rows affected (0.09 sec)

mysql> DESCRIBE product_details;
+-----------+------------------+------+-----+---------+-------+
| Field     | Type             | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| productID | int(10) unsigned | NO   | PRI | NULL    |       |
| comment   | varchar(1024)    | NO   |     |         |       |
+-----------+------------------+------+-----+---------+-------+
2 rows in set (0.04 sec)

mysql> SHOW CREATE TABLE product_details;
*************************** 1. row ***************************
       Table: product_details
Create Table: CREATE TABLE `product_details` (
  `productID` int(10) unsigned NOT NULL,
  `comment` varchar(1024) NOT NULL DEFAULT '',
  PRIMARY KEY (`productID`),
  CONSTRAINT `product_details_ibfk_1` FOREIGN KEY (`productID`) 
    REFERENCES `products` (`productID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
 
mysql> quit

Backup and Restore

Backup: Before we conclude this example, let us run the mysqldump utility program to dump out (i.e., backup) the entire southwind database.

-- Change the current working directory to <MYSQL_HOME>\bin
D:\myproject\mysql\bin> mysqldump -u guser -p --databases southwind > "d:\myproject\backup_southwind.sql"

Study the output file, which contains CREATE DATABASE, CREATE TABLE and INSERT statements to recreate the tables dumped.

The syntax for the mysqldump program is as follows:

-- Dump selected databases with --databases option
prompt> mysqldump -u username -p --databases database1Name [database2Name ...] > backupFile.sql
-- Dump all databases in the server with --all-databases option, except mysql.user table (for security)
prompt> mysqldump -u root -p --all-databases --ignore-table=mysql.user > backupServer.sql
  
-- Dump all the tables of a particular database
prompt> mysqldump -u username -p databaseName > backupFile.sql
-- Dump selected tables of a particular database
prompt> mysqldump -u username -p databaseName table1Name [table2Name ...] > backupFile.sql

Restore: You can restore from the backup by running the "source" command in an interactive client. For example, to restore the southwind backup earlier:

-- Start and login to client
D:\myproject\mysql\bin> mysql -u username -p
......
-- Run the backup script to recreate the database
mysql> source d:/myproject/backup_southwind.sql

Summary of MySQL Commands Used in this Tutorial

For detailed syntax, check MySQL manual "SQL Statement Syntax" @ http://dev.mysql.com/doc/refman/5.5/en/sql-syntax.html.

// Database-level
DROP DATABASE databaseName                 // Delete the database (irrecoverable!)
DROP DATABASE IF EXISTS databaseName       // Delete if it exists
CREATE DATABASE databaseName               // Create a new database
CREATE DATABASE IF NOT EXISTS databaseName // Create only if it does not exists
SHOW DATABASES                             // Show all the databases in this server
   
// Set default database.
// Otherwise you need to use the full name, i.e., databaseName.tableName, to refer to a table.
USE databaseName     // Set the default database
SELECT DATABASE()    // Show the default database
   
// Table-level
DROP TABLE [IF EXISTS] tableName
CREATE TABLE [IF NOT EXISTS] tableName (
   columnDefinition, ...
   PRIMARY KEY(columnName),
   FOREIGN KEY (columnNmae) REFERENCES tableName (columnNmae)
)
SHOW TABLES                // Show all the tables in the default database
DESCRIBE|DESC tableName    // Describe the details for a table
ALTER TABLE tableName ...  // Modify a table, e.g., ADD COLUMN, CHANGE COLUMN, DROP COLUMN, etc.
ALTER TABLE tableName ADD columnDefinition
ALTER TABLE tableName ADD FOREIGN KEY (columnNmae) REFERENCES tableName (columnNmae)
ALTER TABLE tableName DROP columnName
ALTER TABLE tableName DROP FOREIGN KEY constraintName
  
// Record-level
INSERT INTO tableName VALUES (column1Value, column2Value,...)
INSERT INTO tableName (column1Name, ..., columnNName) VALUES (column1Value, ..., columnNValue)
DELETE FROM tableName WHERE criteria
UPDATE tableName SET columnName = expression WHERE criteria
SELECT column1Name AS displayName1, ..., columnNName AS displayNameN FROM tableName WHERE criteria
    ORDER BY columnAName ASC|DESC, columnBName ASC|DESC, ...
 
// Miscellaneous
SHOW CREATE TABLE tableName        // Show the CREATE TABLE statement for this tableName
SHOW CREATE DATABASE databaseName

Primary Key, Foreign Key and Index

Primary Key

In the relational model, a table shall not contain duplicate rows, because that would create ambiguities in retrieval. To ensure uniqueness, each table should have a column (or a set of columns), called primary key, that uniquely identifies every records of the table. For example, an unique number customerID can be used as the primary key for the customers table; productCode for products table; isbn for books table. A primary key is called a simple key if it is a single column; it is called a composite key if it is made up of several columns. Most RDBMSs build an index on the primary key to facilitate fast search. The primary key is also used to relate to other tables.

Foreign Key

A foreign key of a child table is a primary key of a parent table, used to reference the parent table. Foreign key constraints are imposed to ensure so-called referential integrity - the value in the foreign key in the child table must be present in the primary key of the parent table.

You define the foreign key when defining the child table, which references the primary key of a parent table, as follow:

-- Child table definition --
CREATE TABLE tableName (
   ......
   ......
   CONSTRAINT ConstraintName FOREIGN KEY (foreignKeyColumName) REFERENCES parentTableName (primaryKeyColumnName)
   [ON DELETE RESTRICT | CASCADE | SET NULL | NO ACTION]   -- On DELETE reference action
   [ON UPDATE RESTRICT | CASCADE | SET NULL | NO ACTION]   -- On UPDATE reference action
)

You can specify the reference action for UPDATE and DELETE via the optional ON UPDATE and ON DELETE clauses.

1. RESTRICT (default): disallow DELETE or UPDATE of the key value in the parent table, if there is matching rows in child table(s).
2. CASCADE: cascade the DELETE or UPDATE action to the matching rows in the child table(s).
3. SET NULL: set the foreign key value in the child table(s) to NULL (only if NULL is allowed).
4. NO ACTION: a SQL term which means no action on the "parent" record. Same as RESTRICT in MySQL, which disallow DELETE or UPDATE (do nothing).

Try deleting a record in the suppliers table (parent) that is referenced by products_suppliers table (child), e.g.,

mysql> SELECT * FROM products_suppliers;
+-----------+------------+
| productID | supplierID |
+-----------+------------+
|      1005 |        501 |
|      1006 |        501 |
|      1007 |        501 |
|      1008 |        502 |
|      1005 |        503 |
+-----------+------------+
 
mysql> DELETE FROM suppliers WHERE supplierID = 501;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails 
(`southwind`.`products_suppliers`, CONSTRAINT `products_suppliers_ibfk_2` 
FOREIGN KEY (`supplierID`) REFERENCES `suppliers` (`supplierID`))

The record cannot be deleted as "ON DELETE RESTRICT" constraint was imposed in the table definition (by default).

Index

Indexes can be created on selected columns to facilitate fast search. However, these indexes deplete the performance on modifying the table, and need to be justified. In MySQL, indexes are automatically built on the primary-key column. You can build additional index on a column or a set of columns, while creating your table. [In MySQL, INDEX is synonym for KEY.]

CREATE TABLE tableName (
   ......
   [UNIQUE] INDEX indexName (indexColumnName...),
   ......
);
 
CREATE INDEX indexName ON tableName(indexColumnName);
 
SHOW INDEX FROM tableName;

More SQL

Sub-Query

Results of one query can be used in the query criteria of another query in the WHERE clause.

SELECT ... FROM tableName 
   WHERE columnNmae IN (SELECT ... FROM tableName)
  
SELECT ... FROM tableName 
   WHERE columnNmae NOT IN (SELECT ... FROM tableName)

Example: In the many-to-many example, how to find the suppliers that do not supply any product. You can query for the suppliers that supply at least one product in the products_suppliers table, and then query the suppliers table for those that are not in the previous result.

SELECT suppliers.name from suppliers 
   WHERE suppliers.supplierID 
      NOT IN (SELECT DISTINCT supplierID from products_suppliers);

Can you do this without sub-query?

You can also use a sub-query with other SQL statements such as INSERT. For example,

INSERT INTO another_books
   (isbn, title)
   SELECT isbn, title FROM books;

Date/Time

Date and time are of particular interest for database applications. It is because business records often carry date/time information (e.g., orderDate, deliveryDate, paymentDate), as well as the need to time-stamp the last update of the records for auditing and security.

MySQL provides these built-in functions for getting the current date and/or time:

• NOW(): returns the current date and time in the format of 'YYYY-MM-DD HH:MM:SS'.
• CURDATE() (or CURRENT_DATE(), or CURRENT_DATE): returns the current date in the format of 'YYYY-MM-DD'.
• CURTIME() (or CURRENT_TIME(), or CURRENT_TIME): returns the current time in the format of 'HH:MM:SS'.

For examples,

mysql> select now(), curdate(), curtime();
+---------------------+------------+-----------+
| now()               | curdate()  | curtime() |
+---------------------+------------+-----------+
| 2010-04-08 16:28:06 | 2010-04-08 | 16:28:06  |
+---------------------+------------+-----------+

MySQL provides these date/time data type:

• DATETIME: stores both date and time in the format of 'YYYY-MM-DD HH:MM:SS'. The valid range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. You can set a value in the valid format (e.g., '2011-08-15 00:00:00'). You could also apply function NOW() or CURDATE() (time will be set to '00:00:00'), but not CURTIME().
• DATE: stores date only in the format of 'YYYY-MM-DD'. The range is '1000-01-01' to '9999-12-31'. You could set a value or use CURDATE() or NOW() (the time discarded) for this field.
• TIME: stores time only in the format of 'HH:MM:SS'. You could set a value or use CURTIME() or NOW() (the date discarded) for this field.
• YEAR(4|2): in 'YYYY' or 'YY'. The range of years is 1901 to 2155. Use DATE type for year outside this range. You could also use CURDATE() to retrieve the current year value for this field (month and day discarded).

• TIMESTAMP: similar to DATETIME but stored the number of seconds since January 1, 1970 UTC (Unix-style). The range is '1970-01-01 00:00:00' to '2037-12-31 23:59:59'.
  The differences between DATETIME and TIMESTAMP are:
  1. the range,
  2. support for time zone,
  3. TIMESTAMP column could be declared with DEFAULT CURRENT_TIMESTAMP to set the default value to the current date/time. (All other data types' default, including DATETIME, must be a constant and not a function return value). You can also declare a TIMESTAMP column with "ON UPDATE CURRENT_TIMESTAMP" to capture the timestamp of the last update.

The date/time value can be entered manually as a string literal (e.g., '2010-12-31 23:59:59' for DATAETIME). MySQL will issue a warning and insert all zeros (e.g., '0000-00-00 00:00:00' for DATAETIME), if the value of date/time to be inserted is invalid or out-of-range. '0000-00-00' is called a "dummy" date.

Example:

1. Create a table with various date/time columns. Only the TIMESTAMP column can have the DEFAULT and ON UPDATE as CURRENT_TIMESTAMP.

   mysql> CREATE TABLE IF NOT EXISTS `datetime_arena` (
            `description` VARCHAR(50) DEFAULT NULL,
            `cDateTime`   DATETIME    DEFAULT '0000-00-00 00:00:00',
            `cDate`       DATE        DEFAULT '0000-00-00',
            `cTime`       TIME        DEFAULT '00:00:00',
            `cYear`       YEAR        DEFAULT '0000',
            `cYear2`      YEAR(2)     DEFAULT '00',
            `cTimeStamp`  TIMESTAMP   DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
          );
   Query OK, 0 rows affected (0.05 sec)
      
   mysql> DESCRIBE `datetime_arena`;
   +-------------+-------------+------+-----+---------------------+-----------------------------+
   | Field       | Type        | Null | Key | Default             | Extra                       |
   +-------------+-------------+------+-----+---------------------+-----------------------------+
   | description | varchar(50) | YES  |     | NULL                |                             |
   | cDateTime   | datetime    | YES  |     | 0000-00-00 00:00:00 |                             |
   | cDate       | date        | YES  |     | 0000-00-00          |                             |
   | cTime       | time        | YES  |     | 00:00:00            |                             |
   | cYear       | year(4)     | YES  |     | 0000                |                             |
   | cYear2      | year(2)     | YES  |     | 00                  |                             |
   | cTimeStamp  | timestamp   | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
   +-------------+-------------+------+-----+---------------------+-----------------------------+

2. Insert values manually using string literals.

   mysql> INSERT INTO `datetime_arena` (`description`, `cDateTime`, `cDate`, `cTime`, `cYear`, `cYear2`)
            VALUES ('Manual Entry', '2001-01-01 23:59:59', '2002-02-02', '12:30:30', '2004', '05');
   Query OK, 1 row affected (0.00 sec)
      
   mysql> SELECT * FROM `datetime_arena` WHERE description='Manual Entry';
   +--------------+---------------------+------------+----------+-------+--------+---------------------+
   | description  | cDateTime           | cDate      | cTime    | cYear | cYear2 | cTimeStamp          |
   +--------------+---------------------+------------+----------+-------+--------+---------------------+
   | Manual Entry | 2001-01-01 23:59:59 | 2002-02-02 | 12:30:30 |  2004 |     05 | 2010-04-08 14:44:37 |
   +--------------+---------------------+------------+----------+-------+--------+---------------------+

3. Checking the on-update for TIMSTAMP.

   mysql> UPDATE `datetime_arena` SET `cYear2`='99' WHERE description='Manual Entry';
   Query OK, 1 row affected (0.00 sec)
   Rows matched: 1  Changed: 1  Warnings: 0
      
   mysql> SELECT * FROM `datetime_arena` WHERE description='Manual Entry';
   +--------------+---------------------+------------+----------+-------+--------+---------------------+
   | description  | cDateTime           | cDate      | cTime    | cYear | cYear2 | cTimeStamp          |
   +--------------+---------------------+------------+----------+-------+--------+---------------------+
   | Manual Entry | 2001-01-01 23:59:59 | 2002-02-02 | 12:30:30 |  2004 |     99 | 2010-04-08 14:44:48 |
   +--------------+---------------------+------------+----------+-------+--------+---------------------+

4. Insert values using MySQL built-in functions now(), curdate(), curtime().

   mysql> INSERT INTO `datetime_arena` (`description`, `cDateTime`, `cDate`, `cTime`, `cYear`, `cYear2`)
            VALUES ('Built-in Functions', now(), curdate(), curtime(), now(), now());
   Query OK, 1 row affected, 2 warnings (0.00 sec)
      
   mysql> SELECT * FROM `datetime_arena` WHERE description='Built-in Functions';
   +--------------------+---------------------+------------+----------+-------+--------+---------------------+
   | description        | cDateTime           | cDate      | cTime    | cYear | cYear2 | cTimeStamp          |
   +--------------------+---------------------+------------+----------+-------+--------+---------------------+
   | Built-in Functions | 2010-04-08 14:45:48 | 2010-04-08 | 14:45:48 |  2010 |     10 | 2010-04-08 14:45:48 |
   +--------------------+---------------------+------------+----------+-------+--------+---------------------+

5. Insert invalid or out-of-range values. MySQL replaces with all zeros.

   mysql> INSERT INTO `datetime_arena` (`description`, `cDateTime`, `cDate`, `cTime`, `cYear`, `cYear2`)
            VALUES ('Error Input', '2001-13-31 23:59:59', '2002-13-31', '12:61:61', '99999', '999');
   Query OK, 1 row affected, 5 warnings (0.00 sec)
      
   mysql> SELECT * FROM `datetime_arena` WHERE description='Error Input';
   +-------------+---------------------+------------+----------+-------+--------+---------------------+
   | description | cDateTime           | cDate      | cTime    | cYear | cYear2 | cTimeStamp          |
   +-------------+---------------------+------------+----------+-------+--------+---------------------+
   | Error Input | 0000-00-00 00:00:00 | 0000-00-00 | 00:00:00 |  0000 |     00 | 2010-04-08 14:46:10 |
   +-------------+---------------------+------------+----------+-------+--------+---------------------+

6. An useful built-in function INTERVAL can be used to compute a future date, e.g.,

   mysql> SELECT `cDate`, `cDate` + INTERVAL 30 DAY, `cDate` + INTERVAL 1 MONTH FROM `datetime_arena`;
   +------------+---------------------------+----------------------------+
   | cDate      | `cDate` + INTERVAL 30 DAY | `cDate` + INTERVAL 1 MONTH |
   +------------+---------------------------+----------------------------+
   | 2002-02-02 | 2002-03-04                | 2002-03-02                 |
   | 2010-04-08 | 2010-05-08                | 2010-05-08                 |
   | 0000-00-00 | NULL                      | NULL                       |
   +------------+---------------------------+----------------------------+

Example: An "Item Rental" database (e.g., books, cars) with rental period and timestamp. [TODO]

REFERENCES & RESOURCES

• MySQL Mother Site @ www.mysql.com.
• MySQL 5.5 "Reference Manual", available at http://dev.mysql.com/doc/refman/5.5/en.
• MySQL 5.5 "SQL Statement Syntax" @ http://dev.mysql.com/doc/refman/5.5/en/sql-syntax.html.

Latest version tested: MySQL 5.5.15
Last modified: September 14, 2011