A very brief introduction to MySQL

We will assume that MySQL has already been installed and setup on your system. If not, please visit www.mysql.com.

Adding users

To setup a user called basil with full privileges to do anything to any database or table, with access only from the local host:

GRANT ALL PRIVILEGES ON *.* to 'basil'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;

We now create a user fred with SELECT only privileges only on a specific table, with access from any host:

GRANT SELECT ON my_database.my_table to 'fred'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

To see a list of all users and their privileges:

SELECT * FROM mysql.user;

Creating databases and tables

To create a database called site:

CREATE DATABASE site;

To use this database:

USE site;

To see a list of all databases:

SHOW DATABASES;

To see a list of tables for the current database:

SHOW TABLES;

Example creation of a table:

CREATE TABLE users (name VARCHAR(50), email VARCHAR(50), user VARCHAR(30), id INT(3) NOT NULL AUTO_INCREMENT, PRIMARY KEY (id));

The table users has 4 columns: name, email, user and id. id contains automatically incremented integers. Every row added to this table will be assigned a unique id.

To see how a table is setup:

DESCRIBE users;

will give for the above example:

+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| name  | varchar(50) | YES  |     | NULL    |                |
| email | varchar(50) | YES  |     | NULL    |                |
| user  | varchar(30) | YES  |     | NULL    |                |
| id    | int(3)      | NO   | PRI | NULL    | auto_increment |
+-------+-------------+------+-----+---------+----------------+

For storing numerical data, there are variety of choices. For integers, you can use TINYINT, SMALLINT, MEDIUMINT, INT or BIGINT, depending on the magnitude of the data. For positive only integers, you can specify UNSIGNED, for example TINYINT UNSIGNED. It's a good idea to try to choose the most appropriate data type in order to minimize the storage required. For example, if you require only numbers between 0 and 200, use TINYINT UNSIGNED rather than INT or BIGINT.

For floating point numbers, FLOAT or DOUBLE can be used.

If you want to specify the precision of the data to be stored, the DECIMAL type could be used. For example,

price DECIMAL(5,2)

Here the column price can contain numbers with 5 digits and 2 decimal places, i.e. numbers in the range -99.99 to 999.99.

If you want to store a date and time in a table in the YYYY-MM-DD HH:MM:SS format, there are two data type choices: TIMESTAMP or DATETIME. Be aware that TIMESTAMP has a side-effect, in that if you update a row, the TIMESTAMP column will automatically be updated. If you don't want this to happen, use DATETIME instead.

Adding data to a table

For the example table given above, we can easily insert a row into the table using INSERT. For example:

INSERT INTO users (name,email,user) VALUES ("John Smith", "john.smith@smith.com", "john.smith");

will add one row into the table, modifying the columns name, email and user. The table now contains the following:

+------------+----------------------+------------+----+
| name       | email                | user       | id |
+------------+----------------------+------------+----+
| John Smith | john.smith@smith.com | john.smith |  1 |
+------------+----------------------+------------+----+

Simple queries

Suppose you have a table containing a list of item names and prices, for example a table created using:

CREATE TABLE shop (item VARCHAR(50), price DECIMAL(5,2));

i.e. the table looks like this:

+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| item  | varchar(50)  | YES  |     | NULL    |       |
| price | decimal(5,2) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

To see all rows and columns in the table shop:

SELECT * FROM shop;

To only see a list of item names, not prices as well:

SELECT item FROM shop;

Finding the price of a specific item is very simple. We need to use a WHERE clause, which specifies what conditions need to be met for a row to be included in the set of results:

SELECT price FROM shop WHERE item = "Little toy train";

What above listing all products with a price greater than 10.00?

SELECT * FROM shop WHERE price > 10.00;

To show only the first 10 results, for example:

SELECT * FROM shop WHERE price > 10.00 LIMIT 10;

To show 10 results, starting from the 30th:

SELECT * FROM shop WHERE price > 10.00 LIMIT 30, 10;

To show results in descending order of price:

SELECT * FROM shop WHERE price > 10.00 ORDER BY price DESC;

If we just had ORDER BY price, the results would be listed in ascending order of price. However, we have included DESC as well, which specifies that we want the results in descending order.

What if you want to find the name of an item where the price is within specific limits?

SELECT item FROM shop WHERE price BETWEEN 10.00 AND 30.00;

Aggregrate functions

To determine the number of rows in a table, use COUNT, for example:

SELECT COUNT(*) FROM shop;

Sums and averages of columns can easily be calculated using SUM and AVG:

SELECT SUM(price) FROM shop;
SELECT AVG(price) FROM shop;

Similarly, the minimum and maximum values can be found easily:

SELECT MIN(price) FROM shop;
SELECT MAX(price) FROM shop;

Suppose we have a table for storing the prices of items in a shop, and we now have a category column:

CREATE TABLE shop (item VARCHAR(50), price DECIMAL(5,2), category VARCHAR(50));

Categories could include CD, DVD, and book, for example. How do we find the maximum price in each category? This can be done using GROUP BY. For example:

SELECT category, MAX(price) FROM shop GROUP BY category;

This will list the maximum price from each category.

Regular expressions

Regular expressions can be used in queries. Suppose we have a table called names with columns first and last. Then:

SELECT first, last FROM names;

gives, for example:

+-------+---------+
| first | last    |
+-------+---------+
| John  | Citizen |
| Jane  | Smith   |
+-------+---------+

What if we want to search for all first names that end in the character "n"?

SELECT first,last FROM names WHERE first REGEXP 'n$';

Other useful functions

The CONCAT function allows you to combine results from different columns together, with additional text if required. Again, suppose we have a table called names with columns first and last. What if we want to combine the first and last names together? To do this, we use CONCAT.

SELECT CONCAT(first,' ',last) FROM names;

gives:

+------------------------+
| CONCAT(first,' ',last) |
+------------------------+
| John Citizen           |
| Jane Smith             |
+------------------------+

Modifying rows

To delete all rows from a table:

DELETE FROM my_table;

To delete specific rows from a table we just introduce a WHERE clause:

DELETE FROM my_table WHERE name = "Bob";

Suppose we have a table with 5 columns: a, b, c, d, and e, containing numerical data.

Simple update of a single row in a single column:

UPDATE my_table SET b = 42 WHERE a = 21;

Updating an entire column:

UPDATE my_table SET b = a*c;

Using a WHERE clause enables you to easily modify only rows that satisfy a specified condition:

UPDATE my_table SET b = a*c WHERE d > 5;

Updating multiple columns is very straightforward:

UPDATE my_table SET b = a*c, d = a*c/e WHERE e > 0;

Modifying columns

The following:

ALTER TABLE my_table ADD name VARCHAR(50);

will insert a new column called name at the end of the existing columns. To insert a new column somewhere else, for example after a column called user:

ALTER TABLE my_table ADD name VARCHAR(50) AFTER user;

To insert a new column before all other columns:

ALTER TABLE my_table ADD name VARCHAR(50) FIRST;

We can also delete a column, for example:

ALTER TABLE my_table DROP COLUMN product_description;

The data type of a column can also be changed, for example, to change a column id to type TINYINT:

ALTER TABLE my_table MODIFY id TINYINT;

To rename a column, you need to specify the current column name, the new column name, and the current data type:

ALTER TABLE my_table CHANGE product_id id INT;

To add a default value to a column:

ALTER TABLE my_table ALTER COLUMN product_id SET DEFAULT 0;

Deleting databases and tables

To delete a database:

DROP DATABASE my_database;

To delete a table:

DROP TABLE my_table;

Note that this deletes the table itself, not just the contents of the table.

Copying a table

Suppose we want to make a copy of my_table1 called my_table2. Firstly, make my_table2:

CREATE my_table2 LIKE my_table1;

Then populate it:

INSERT INTO my_table2 SELECT * FROM my_table1;

Using INSERT INTO allows you to modify the data in some way if you want to, or copy from a table with a different number of columns.

Alternatively, copying a table can be done in one line:

CREATE TABLE my_table2 SELECT * FROM my_table1;

If the new table might already exist, it's safer to do this:

CREATE TABLE IF NOT EXISTS my_table2 SELECT * FROM my_table1;

Renaming a table

RENAME TABLE my_table1 TO my_table2;

A table can be moved from one database to another using RENAME TABLE, for example:

RENAME TABLE my_database1.my_table TO my_database2.my_table;