MySQL quick tutorial
last modified January 10, 2023
The MySQL quick tutorial will quickly introduce you to the basics of the MySQL database.
The world database
The MySQL documentation website offers some example databases. We are going to use a world database. The data is outdated but it does not matter for our test purposes.
$ wget http://downloads.mysql.com/docs/world.sql.gz
We download the compressed file with the wget tool.
$ ls -sh world.sql.gz 92K world.sql.gz
The compressed file has about 92KB.
$ gunzip world.sql.gz
We unzip the file. We have a world.sql file.
$ ls -hs world.sql 392K world.sql
The uncompressed file has 392KB.
$ mysql -uroot -p
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.sql
We build the tables of the world database by executing
world.sql 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 | +-----------------+ 3 rows in set (0,00 sec)
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 | | +-------------+----------+------+-----+---------+----------------+ 5 rows in set (0,00 sec)
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 -u root -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 | +-----------------+ 2 rows in set (0,00 sec)
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 | +-----------------+ 3 rows in set (0,00 sec)
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 | +----+----------------+------------+ 10 rows in set (0,00 sec)
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 | +----+-------------------+------------+ 5 rows in set (0,00 sec)
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 go the the default setting, simply
hit the pager without any 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
The COUNT(), MAX(), MIN() are MySQL aggregate functions which compute some value from aggregate data.
mysql> SELECT COUNT(Id) AS '# of cities' FROM city; +-------------+ | # of cities | +-------------+ | 4079 | +-------------+ 1 row in set (0,00 sec)
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 |
+-----------------+------------+
1 row in set (0,08 sec)
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 |
+-----------+------------+
1 row in set (0,02 sec)
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 |
| Yerevan | 1248700 |
| Sydney | 3276207 |
...
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, 23, 432, 2021); +------------+------------+ | Name | Population | +------------+------------+ | Amsterdam | 731200 | | Dordrecht | 119811 | | Eunápolis | 96610 | | Jining | 265248 | +------------+------------+ 4 rows in set (0,05 sec)
This SQL code returns cities and their populations for rows with ID 5, 23, 432, and 2021.
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,03 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
thirteen 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,03 sec)
We find the ten 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,02 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,09 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 FROM city WHERE District = 'New York'; +-----------+----------+ | Name | District | +-----------+----------+ | New York | New York | | Buffalo | New York | | Rochester | New York | | Yonkers | New York | | Syracuse | New York | | Albany | New York | +-----------+----------+ 6 rows in set (0,00 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,28 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,12 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 = 'Donald Trump'
-> 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 | Donald Trump | +---------------+--------------+ 1 row in set (0,02 sec)
We have successfully updated the row.
mysql> CREATE TABLE toptencities 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 toptencities; +----+----------------+-------------+---------------+------------+ | 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 toptencities table.
mysql> DELETE FROM toptencities WHERE ID IN (2, 4, 6, 8, 10);
With the DELETE FROM statement and the WHERE
clause we delete every second row from the toptencities table.
mysql> SELECT * FROM toptencities; +----+-----------+-------------+---------------+------------+ | 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 toptencities; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM toptencities; Empty set (0,00 sec)
We delete all rows from the table with the TRUNCATE statement.
There is no data left.
mysql> INSERT INTO toptencities VALUES(1, 'Kabul', 'AFG', 'Kabol', 1780000); mysql> SELECT * FROM toptencities;; +----+-------+-------------+----------+------------+ | 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 toptencities; Query OK, 0 rows affected (0,06 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.