Question

This chapter will quickly introduce you to the basics of the MySQL database.

The world database

We are going to use a world database , which can be found on the MySQL official website. The data is outdated, but it does not affect us.

There are two options. A world database with the MyISAM storage engine or with the InnoDB engine. We choose the latter.

$ ls -sh world_innodb.sql.gz 
92K world_innodb.sql.gz

The compressed file has about 92 KB.

$ gunzip world_innodb.sql.gz
$ ls
world_innodb.sql

We unzip the file. We have a world_innodb.sql file.

$ mysql -uroot -p
Enter password: 

mysql> 

We connect to the server with the root account. We need the root account to create a new database and give permissions to our test account for the new database.

mysql> CREATE DATABASE world;

The world database is created.

mysql> USE world;

We change to the world database. Now the world database is the current database.

mysql> source world_innodb.sql

We build the tables of the world database by executing this SQL script. It takes some time.

mysql> GRANT ALL ON world.* TO user12@localhost;

We grant privileges to all objects of the world database to the user12 .

mysql> quit
Bye

$ mysql -u user12 -p
Enter password: 

mysql> USE world;

We quit the connection. Reconnect with the user12 test account and change to the world database. We are ready for work.

Examining the database

In this section, we are going to look at the tables of the world database in general.

mysql> SHOW TABLES;
+-----------------+
| Tables_in_world |
+-----------------+
| City      |
| Country     |
| CountryLanguage |
+-----------------+

We show all available tables with the SHOW TABLES statement. There are three.

mysql> DESCRIBE City;
+-------------+----------+------+-----+---------+----------------+
| Field     | Type   | Null | Key | Default | Extra      |
+-------------+----------+------+-----+---------+----------------+
| ID      | int(11)  | NO   | PRI | NULL  | auto_increment |
| Name    | char(35) | NO   |   |     |        |
| CountryCode | char(3)  | NO   | MUL |     |        |
| District  | char(20) | NO   |   |     |        |
| Population  | int(11)  | NO   |   | 0     |        |
+-------------+----------+------+-----+---------+----------------+

With the DESCRIBE statement, we can see the table structure of the City table. We see the column names and their data types. Plus other important information.

mysql> SHOW CREATE TABLE City;

If we wanted to find out the SQL to create the City table, we would issue the SHOW CREATE TABLE City statement.

$ mysqldump -uroot -p world City > city.sql

Here we use the mysqldump tool to back up the City table.

mysql> DROP TABLE City;

mysql> SHOW TABLES;
+-----------------+
| Tables_in_world |
+-----------------+
| Country     |
| CountryLanguage |
+-----------------+

We use the DROP TABLE statement to drop the City table. Subsequent statement verifies that the table was removed.

mysql> source city.sql

mysql> SHOW TABLES;
+-----------------+
| Tables_in_world |
+-----------------+
| City      |
| Country     |
| CountryLanguage |
+-----------------+

We recreate the City table from the backup. The source command executes the backup city.sql script.

Queries

Queries are used to look up data from the database tables.

Limiting data output

There are thousands of rows in the tables of the database. They cannot be displayed all on the screen. We can control the number of rows to be displayed with the LIMIT clause.

mysql> SELECT Id, Name, Population FROM City LIMIT 10;
+----+----------------+------------+
| Id | Name       | Population |
+----+----------------+------------+
|  1 | Kabul      |  1780000 |
|  2 | Qandahar     |   237500 |
|  3 | Herat      |   186800 |
|  4 | Mazar-e-Sharif |   127800 |
|  5 | Amsterdam    |   731200 |
|  6 | Rotterdam    |   593321 |
|  7 | Haag       |   440900 |
|  8 | Utrecht    |   234323 |
|  9 | Eindhoven    |   201843 |
| 10 | Tilburg    |   193238 |
+----+----------------+------------+

In the above query, we show three of the five columns of the City table. There are lots of rows in the table. We limit the query to the first 10 rows.

mysql> SELECT Id, Name, Population FROM City LIMIT 15, 5;
+----+-------------------+------------+
| Id | Name        | Population |
+----+-------------------+------------+
| 16 | Haarlem       |   148772 |
| 17 | Almere      |   142465 |
| 18 | Arnhem      |   138020 |
| 19 | Zaanstad      |   135621 |
| 20 | ´s-Hertogenbosch  |   129170 |
+----+-------------------+------------+

The LIMIT clause can be followed by two numbers. The first one is the offset and the second one is the number of rows to display. Our query shows rows 16-20.

mysql> pager less
PAGER set to 'less'
mysql> SELECT * FROM City;
+------------------------------------+------------+
| Name                 | Population |
+------------------------------------+------------+
| Kabul                |  1780000 |
| Qandahar               |   237500 |
| Herat                |   186800 |
...
:

Since the City table has more than four thousand rows, we cannot see them in one screen. We can use the pager command to show the data in a less program. We can navigate through the data with the cursor keys or page down, page up keys. If we want to use no pager program, simply hit the pager without an argument.

$ mysql -u user12 -p world -e "SELECT * FROM City" > city
Enter password: 
$ ls -sh city
144K city

The mysql command tool can be used in a non-interactive way. We specify the SQL statement after the -e option and redirect the result to the city file. Now we can use any text editor to display the data.

The COUNT(), MAX(), MIN() functions

mysql> SELECT COUNT(Id) AS 'Number of rows' FROM City;
+----------------+
| Number of rows |
+----------------+
|       4079 |
+----------------+

There are 4079 cities in the table. We use the built-in COUNT() function to find out the number of rows.

mysql> SELECT Name, Population FROM City
  -> WHERE Population = (SELECT Max(Population) FROM City);
+-----------------+------------+
| Name      | Population |
+-----------------+------------+
| Mumbai (Bombay) |   10500000 |
+-----------------+------------+

The above query shows the most populated city in the table. The SQL is a special type of a query called a subquery. The outer query uses the data returned by the inner query. The inner query is bounded by parentheses.

mysql> SELECT Name, Population FROM City 
  -> WHERE Population = (SELECT Min(Population) FROM City);
+-----------+------------+
| Name    | Population |
+-----------+------------+
| Adamstown |     42 |
+-----------+------------+

This subquery shows the least populated city in the table.

Selecting specific rows with the WHERE clause

The WHERE clause can be used to filter the results. It provides a selection criteria to select only specific rows from the data.

mysql> SELECT Name, Population FROM City 
  -> WHERE Population > 1000000;
+--------------------------+------------+
| Name           | Population |
+--------------------------+------------+
| Kabul          |  1780000 |
| Alger          |  2168000 |
| Luanda           |  2022000 |
| Buenos Aires       |  2982146 |
| La Matanza         |  1266461 |
| Córdoba          |  1157507 |
...

The above SQL statement returns all cities with a population above one million people.

mysql> SELECT Name FROM City WHERE Name LIKE 'Kal%';
+-------------+
| Name    |
+-------------+
| Kalookan  |
| Kalyan    |
| Kalemie   |
| Kallithea   |
| Kalisz    |
| Kaliningrad |
| Kaluga    |
+-------------+
7 rows in set (0.00 sec)

Here we select all city names which begin with Kal. We have found seven cities in the table. We can look for a specific pattern in the column with the LIKE clause.

mysql> SELECT Name, Population FROM City 
  -> WHERE ID IN (5, 32, 344, 554);
+-------------------+------------+
| Name        | Population |
+-------------------+------------+
| Amsterdam     |   731200 |
| Alkmaar       |    92713 |
| Guarapuava    |   160510 |
| Santiago de Chile |  4703954 |
+-------------------+------------+
4 rows in set (0.00 sec)

This SQL code returns cities and their populations for rows with Id 5, 32, 344, and 554.

mysql> SELECT * FROM City WHERE Name = 'Bratislava';
+------+------------+-------------+------------+------------+
| ID   | Name     | CountryCode | District   | Population |
+------+------------+-------------+------------+------------+
| 3209 | Bratislava | SVK     | Bratislava |   448292 |
+------+------------+-------------+------------+------------+
1 row in set (0.00 sec)

With the above SQL statement we select all columns for one specific city, namely Bratislava.

mysql> SELECT Name, Population FROM City 
  -> WHERE Population BETWEEN 670000 AND 700000;
+----------------+------------+
| Name       | Population |
+----------------+------------+
| Teresina     |   691942 |
| Natal      |   688955 |
| Bandar Lampung |   680332 |
| Gwalior    |   690765 |
| Kermanshah   |   692986 |
| Palermo    |   683794 |
| Toronto    |   688275 |
| Huainan    |   700000 |
| Jixi       |   683885 |
| Antananarivo   |   675669 |
| Chihuahua    |   670208 |
| Kano       |   674100 |
| Tunis      |   690600 |
+----------------+------------+
13 rows in set (0.00 sec)

Say we wanted to find out cities with a population between two specific values. There is a BETWEEN operator for this. We have found 13 cities with a population in the range 670,000 and 700,000.

Ordering data

Ordering data can be done with the ORDER BY clause.

mysql> SELECT Name, Population FROM City
  -> ORDER BY Population DESC LIMIT 10;
+-------------------+------------+
| Name        | Population |
+-------------------+------------+
| Mumbai (Bombay)   |   10500000 |
| Seoul       |  9981619 |
| São Paulo     |  9968485 |
| Shanghai      |  9696300 |
| Jakarta       |  9604900 |
| Karachi       |  9269265 |
| Istanbul      |  8787958 |
| Ciudad de México  |  8591309 |
| Moscow      |  8389200 |
| New York      |  8008278 |
+-------------------+------------+
10 rows in set (0.00 sec)

We find the 10 most populated cities. We order the data by population from the most populated to the least populated city. We limit the output with the LIMIT clause.

mysql> SELECT Name, Population FROM City 
  -> ORDER BY Population ASC LIMIT 10;
+---------------------+------------+
| Name        | Population |
+---------------------+------------+
| Adamstown       |     42 |
| West Island     |    167 |
| Fakaofo       |    300 |
| Città del Vaticano  |    455 |
| Bantam        |    503 |
| Yaren         |    559 |
| The Valley      |    595 |
| Alofi         |    682 |
| Flying Fish Cove  |    700 |
| Kingston      |    800 |
+---------------------+------------+
10 rows in set (0.01 sec)

Here we get the least populated cities. This time we order the data in the ascending order. For this we use the ASC keyword.

mysql> SELECT Name, Population FROM City 
  -> ORDER BY Name LIMIT 10;
+------------------------+------------+
| Name           | Population |
+------------------------+------------+
| A Coruña (La Coruña)   |   243402 |
| Aachen         |   243825 |
| Aalborg        |   161161 |
| Aba          |   298900 |
| Abadan         |   206073 |
| Abaetetuba       |   111258 |
| Abakan         |   169200 |
| Abbotsford       |   105403 |
| Abeokuta         |   427400 |
| Aberdeen         |   213070 |
+------------------------+------------+
10 rows in set (0.01 sec)

In the above SQL statement we order data by city name and get the first ten cities.

Grouping data

The GROUP BY clause is used to combine database records with identical values into a single record. It is often used with the aggregation functions.

mysql> SELECT District, SUM(Population) FROM City
  -> WHERE District = 'New York' GROUP BY District;
+----------+-----------------+
| District | SUM(Population) |
+----------+-----------------+
| New York |     8958085 |
+----------+-----------------+
1 row in set (0.00 sec)

The above SQL statement returns the total number of people in the towns of the New York district, which are listed in our database.

mysql> SELECT Name, District, Population FROM City
  -> WHERE District = 'New York';
+-----------+----------+------------+
| Name    | District | Population |
+-----------+----------+------------+
| New York  | New York |  8008278 |
| Buffalo   | New York |   292648 |
| Rochester | New York |   219773 |
| Yonkers   | New York |   196086 |
| Syracuse  | New York |   147306 |
| Albany  | New York |    93994 |
+-----------+----------+------------+
6 rows in set (0.01 sec)

The previous number is a sum of these six cities.

mysql> SELECT District, SUM(Population) FROM City
  -> WHERE CountryCode = 'USA' GROUP BY District
  -> HAVING SUM(Population) > 3000000;
+------------+-----------------+
| District   | SUM(Population) |
+------------+-----------------+
| Arizona  |     3178903 |
| California |    16716706 |
| Florida  |     3151408 |
| Illinois   |     3737498 |
| New York   |     8958085 |
| Texas    |     9208281 |
+------------+-----------------+
6 rows in set (0.00 sec)

We select all districts which have population over 3 million people. When we work with groups of data, we use the HAVING clause instead of the WHERE clause.

Updating, deleting, and inserting data

Next we will concern ourselves with updating, deleting, and inserting data.

mysql> SELECT Name, HeadOfState FROM Country
  -> WHERE Name = 'United States';
+---------------+----------------+
| Name      | HeadOfState  |
+---------------+----------------+
| United States | George W. Bush |
+---------------+----------------+
1 row in set (0.00 sec)

As we have already stated, the world database is outdated. George Bush is not the president of the USA anymore.

mysql> UPDATE Country SET HeadOfState = 'Barack Obama'
  -> WHERE Name = 'United States';

With the UPDATE statement we change the row to the actual data.

mysql> SELECT Name, HeadOfState FROM Country WHERE Name = 'United States';
+---------------+--------------+
| Name      | HeadOfState  |
+---------------+--------------+
| United States | Barack Obama |
+---------------+--------------+
1 row in set (0.00 sec)

We have successfully updated the row.

mysql> CREATE TABLE First10 engine=MEMORY SELECT * FROM City LIMIT 10;

We create a temporary table in the memory. It will contain first ten cities from the City table.

mysql> SELECT * FROM First10;
+----+----------------+-------------+---------------+------------+
| ID | Name       | CountryCode | District    | Population |
+----+----------------+-------------+---------------+------------+
|  1 | Kabul      | AFG     | Kabol     |  1780000 |
|  2 | Qandahar     | AFG     | Qandahar    |   237500 |
|  3 | Herat      | AFG     | Herat     |   186800 |
|  4 | Mazar-e-Sharif | AFG     | Balkh     |   127800 |
|  5 | Amsterdam    | NLD     | Noord-Holland |   731200 |
|  6 | Rotterdam    | NLD     | Zuid-Holland  |   593321 |
|  7 | Haag       | NLD     | Zuid-Holland  |   440900 |
|  8 | Utrecht    | NLD     | Utrecht     |   234323 |
|  9 | Eindhoven    | NLD     | Noord-Brabant |   201843 |
| 10 | Tilburg    | NLD     | Noord-Brabant |   193238 |
+----+----------------+-------------+---------------+------------+
10 rows in set (0.00 sec)

This is the contents of the First10 table.

mysql> DELETE FROM First10 WHERE ID IN (2, 4, 6, 8, 10);

With the DELETE FROM statement and the WHERE clause we delete every second row from the First10 table.

mysql> SELECT * FROM First10;
+----+-----------+-------------+---------------+------------+
| ID | Name    | CountryCode | District    | Population |
+----+-----------+-------------+---------------+------------+
|  1 | Kabul   | AFG     | Kabol     |  1780000 |
|  3 | Herat   | AFG     | Herat     |   186800 |
|  5 | Amsterdam | NLD     | Noord-Holland |   731200 |
|  7 | Haag    | NLD     | Zuid-Holland  |   440900 |
|  9 | Eindhoven | NLD     | Noord-Brabant |   201843 |
+----+-----------+-------------+---------------+------------+
5 rows in set (0.00 sec)

We have five rows left in the table.

mysql> TRUNCATE TABLE First10;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM First10;
Empty set (0.00 sec)

We delete all rows from the table with the TRUNCATE statement. There is no data left.

mysql> INSERT INTO First10 VALUES(1, 'Kabul', 'AFG', 'Kabol', 1780000);

mysql> SELECT * FROM First10;
+----+-------+-------------+----------+------------+
| ID | Name  | CountryCode | District | Population |
+----+-------+-------------+----------+------------+
|  1 | Kabul | AFG     | Kabol  |  1780000 |
+----+-------+-------------+----------+------------+
1 row in set (0.00 sec)

With the INSERT INTO statement, we insert one row into the table.

mysql> DROP TABLE First10;
Query OK, 0 rows affected (0.00 sec)

We drop the table from the database.

In this chapter, we have quickly introduced some basics of the MySQL database. We go into more details in the following chapters.