MySQL tutorial by examples

MySQL tutorial

In this tutorial, you’ll see several MySQL examples and tips that will help jump start your journey of becoming a MySQL database developer.

Before you begin

A GNU Linux/Mac OS/Windows machine with a MySQL command line client is good enough to follow this guide. To first connect to the MySQL server you could use the following command:

$ mysql -h <MYSQL_SERVER> -P <3306?> -u<MYSQL_USER> -p<MYSQL_PASS>

Note: From now and in the following examples we’ll avoid all previous parameters and simulate a root-local MySQL connection using only $ mysql. Obviously this kind of connection is not recommended for production purposes, just for learning.

Table of content

1. MySQL databases
2. MySQL tables
3. MySQL insert
4. MySQL select
5. MySQL dates
6. MySQL string functions
7. MySQL aggregate functions
8. MySQL update
9. MySQL delete
10. MySQL truncate
11. MySQL join
12. MySQL where
13. MySQL union
14. MySQL conditionals
15. MySQL views
16. MySQL describe
17. MySQL alter
18. MySQL encryption and hashing
19. MySQL version

1. MySQL databases

1.1. MySQL create database

$ mysql
mysql> CREATE DATABASE foo;
mysql> use foo;
Database changed
mysql>

Another option to create a MySQL database is to use its client for admin purposes mysqladmin:

$ mysqladmin -u<MYSQL_USER> -p<MYSQL_PASS> create foo

1.2. MySQL list all databases

$ mysql foo
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| foo                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

foo is the database that we’ve created before.

information_schema database contains the metadata information of the all elements contained in the server such as databases, tables and privileges.

mysql database contains users and privileges.

performance_schema database contains runtime information about current executions.

sys database is a bunch of objects (views, procedures and functions) that help to interpret data collected in previous database by the performance_schema.

1.3. MySQL get current database

$ mysql foo
mysql> SELECT DATABASE() FROM DUAL;
+------------+
| DATABASE() |
+------------+
| foo        |
+------------+

1.4. MySQL change/select another database

Syntax:

USE database_name

$ mysql foo
mysql> SELECT DATABASE() FROM DUAL;
+------------+
| DATABASE() |
+------------+
| foo        |
+------------+
mysql> USE sys
Database changed
mysql> select database() from dual;
+------------+
| database() |
+------------+
| sys        |
+------------+

1.5. MySQL drop database

$ mysql foo
mysql> DROP DATABASE foo;

Another option to drop a MySQL database is to use its client for admin purposes mysqladmin:

$ mysqladmin -u<MYSQL_USER> -p<MYSQL_PASS> drop foo

2. MySQL tables

2.1. MySQL create table

Syntax:

# Create a brand new MySQL table
CREATE TABLE [IF NOT EXISTS] table_name \
(field1 type, field2 type, ..., fieldN type);

# or create a table from existing MySQL tables
CREATE TABLE [IF NOT EXISTS] table_name AS \
SELECT field1, ..., fieldN FROM table_names [WHERE conditions];

Create a new MySQL table users:

$ mysql
mysql> CREATE DATABASE foo;
mysql> use foo;
mysql> CREATE TABLE IF NOT EXISTS users \
    -> (id int PRIMARY KEY, name CHAR(50), surname CHAR(50));

2.2. MySQL show tables

Syntax:

# Show all tables from the current database
SHOW TABLES

# Show all tables from the current database matching the pattern
SHOW TABLES [LIKE 'pattern']

$ mysql foo
mysql> SHOW TABLES;
+---------------+
| Tables_in_foo |
+---------------+
| users         |
+---------------+

mysql> SHOW TABLES LIKE 'users%';
+------------------------+
| Tables_in_foo (users%) |
+------------------------+
| users                  |
+------------------------+

In MySQL, it is also possible to list all tables from a database using a SQL query:

$ mysql foo
mysql> SELECT table_name FROM information_schema.tables \
    -> WHERE table_schema = 'foo';
+------------+
| table_name |
+------------+
| users      |
+------------+

2.3. MySQL drop table

$ mysql foo
mysql> DROP TABLE IF EXISTS users;
mysql> exit
Bye

3. MySQL insert

Syntax:

# To insert one row at a time
INSERT INTO table_name (field1, field2, ..., fieldN) \
VALUES ('value1', 'value2', ..., 'valueN');

# To insert multiple rows
INSERT INTO table_name (field1, field2, ..., fieldN) \
VALUES ('value1', 'value2', ..., 'valueN'), \
       ('value1', 'value2', ..., 'valueN'), \
   ... ('value1', 'value2', ..., 'valueN');

# To insert rows by selecting them from another table
INSERT INTO table_name (field1, field2, ..., fieldN) \
SELECT (field1, field2, ..., fieldN) \
FROM table_name2;

Inserts one single user to table users:

$ mysql foo
mysql> INSERT INTO users (id, name, surname) \
    -> VALUES (1, 'John', 'Doe');

4. MySQL select

Syntax:

SELECT field1, field2, ..., fieldN \
FROM table_name [WHERE conditions] [LIMIT N[,K]];

Select all users:

$ mysql foo
mysql> SELECT id, name, surname FROM users;
+----+------+---------+
| id | name | surname |
+----+------+---------+
|  1 | John | Doe     |
+----+------+---------+

We can combine the standard LIMIT SQL statement with RAND() MySQL function to select only one single random result:

$ mysql foo
mysql> SELECT * FROM (
    -> SELECT 'apple' as fruit UNION \
    -> SELECT 'banana' as fruit UNION \
    -> SELECT 'orange')fruits ORDER BY RAND() LIMIT 1;
+--------+
| fruit  |
+--------+
| orange |
+--------+

5. MySQL dates

5.1. MySQL datetime

Date and time in UTC:

$ mysql foo
mysql> SELECT UTC_TIMESTAMP();
+---------------------+
| UTC_TIMESTAMP()     |
+---------------------+
| 2020-01-06 07:54:21 |
+---------------------+

Date and time in local time:

$ mysql foo
mysql> SET time_zone = 'America/New_York';
mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2020-01-06 02:54:33 |
+---------------------+
mysql> SET time_zone = '+00:00';
mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2020-01-06 07:54:40 |
+---------------------+

6. MySQL string functions

6.1. MySQL concat

$ mysql foo
mysql> SELECT CONCAT(name, ' ', surname) AS fullname \
    -> FROM users WHERE id=1;
+----------+
| fullname |
+----------+
| John Doe |
+----------+

6.2. MySQL locate

Position of first occurrence of a substring in a string (case insensitive):

$ mysql foo
mysql> SELECT CONCAT(name, ' ', surname) AS fullname, \
    -> LOCATE('doe', CONCAT(name, ' ', surname)) AS doe_position \
    -> FROM users;
+----------+--------------+
| fullname | doe_position |
+----------+--------------+
| John Doe |            6 |
+----------+--------------+

7. MySQL aggregate functions

7.1. MySQL count

Syntax:

SELECT COUNT(1) AS name1 \
    -> FROM table_name;

SELECT COUNT(distinct field1) AS name1 \
    -> FROM table_name;

$ mysql foo
mysql> SELECT COUNT(1) AS num_users \
    -> FROM users;
+-----------+
| num_users |
+-----------+
| 1         |
+-----------+

8. MySQL update

8.1 MySQL update table

Syntax:

UPDATE table_name SET field1=value1, ..., fieldN=valueN \
 [WHERE conditions];

Update users:

$ mysql foo
mysql> UPDATE users SET name='Jane' WHERE id=1;

8.2 MySQL replace into

Syntax:

# Inserts new values or updates them if they already exist
#  based on PRIMARY KEY or UNIQUE KEY indexes.
REPLACE [INTO] table_name (field1, field2, ..., fieldN) \
VALUES ('value1', 'value2', ..., 'valueN');

Replaces the surname of an existing user Jan Doe by Jane Smith and adds a new user Engima Doe:

$ mysql foo
mysql> REPLACE INTO users (id, name, surname) \
    -> VALUES (1, 'Jane', 'Smith');
mysql> REPLACE INTO users (id, name, surname) \
    -> VALUES (2, 'Enigma', 'Doe');

9. MySQL delete

Syntax:

DELETE FROM table_name [WHERE conditions];

Delete users:

$ mysql foo
mysql> DELETE FROM users WHERE id=1;

10. MySQL truncate

10.1 MySQL truncate table

Syntax:

TRUNCATE table_name;

$ mysql foo
mysql> TRUNCATE TABLE users;

11. MySQL join

Creating a new table hobbies for testing purposes:

$ mysql foo
mysql> CREATE TABLE IF NOT EXISTS hobbies \
    -> (id int AUTO_INCREMENT, \
    -> userid int, \
    -> description TEXT, \
    -> PRIMARY KEY (id));
mysql> INSERT INTO hobbies (userid, description) \
    -> VALUES (1, 'Hiking');
mysql> INSERT INTO hobbies (userid, description) \
    -> VALUES (1, 'Astronomy');
mysql> INSERT INTO hobbies (userid, description) \
    -> VALUES (99, 'Chess');
mysql> INSERT INTO users (id, name, surname) \
    -> VALUES (2, 'Baby', 'Doe');

Note: We are using MySQL AUTO_INCREMENT to avoid the need to pass hobbies.id for each record.

Once we have two tables we can perform the different MYSQL JOIN queries:

11.1. MySQL join or inner join

$ mysql foo
mysql> SELECT u.name, h.description AS hobby \
    -> FROM users u, hobbies h where u.id = h.userid;
mysql> SELECT u.name, h.description AS hobby \
    -> FROM users u JOIN hobbies h ON u.id = h.userid;
mysql> SELECT u.name, h.description AS hobby \
    -> FROM users u INNER JOIN hobbies h ON u.id = h.userid;
+------+-----------+
| name | hobby     |
+------+-----------+
| Jane | Hiking    |
| Jane | Astronomy |
+------+-----------+

All three previous queries perform a inner join giving the same result.

11.2. MySQL left join

$ mysql foo
mysql> SELECT u.name, h.description AS hobby \
    -> FROM users u LEFT JOIN hobbies h ON u.id = h.userid;
mysql> SELECT u.name, h.description AS hobby \
    -> FROM users u LEFT OUTER JOIN hobbies h ON u.id = h.userid;
+------+-----------+
| name | hobby     |
+------+-----------+
| Jane | Hiking    |
| Jane | Astronomy |
| Baby | NULL      |
+------+-----------+

MySQL LEFT JOIN and LEFT OUTER JOIN are the same.

11.3. MySQL right join

$ mysql foo
mysql> SELECT u.name, h.description AS hobby \
    -> FROM users u RIGHT JOIN hobbies h ON u.id = h.userid;
mysql> SELECT u.name, h.description AS hobby \
    -> FROM users u RIGHT OUTER JOIN hobbies h ON u.id = h.userid;
+------+-----------+
| name | hobby     |
+------+-----------+
| Jane | Hiking    |
| Jane | Astronomy |
| NULL | Chess     |
+------+-----------+

MySQL RIGHT JOIN and RIGHT OUTER JOIN are the same.

12. MySQL where

$ mysql foo
mysql> SELECT id, name, surname FROM users \
    -> WHERE surname='Doe';
+----+------+---------+
| id | name | surname |
+----+------+---------+
|  1 | Jane | Doe     |
|  2 | Baby | Doe     |
|  3 | John | Doe     |
+----+------+---------+

12.1. MySQL in list

Syntax:

SELECT field(s)
FROM table_name
WHERE field1
[NOT] IN ('value1', 'value2', ..., 'valueN');

In the first query we are selecting users with ids 1 or 3. In the second example we are listing all the users with id different than 2.

$ mysql foo
mysql> SELECT id, name, surname FROM users WHERE id IN (1,3);
+----+------+---------+
| id | name | surname |
+----+------+---------+
|  1 | Jane | Doe     |
|  3 | John | Doe     |
+----+------+---------+

mysql> SELECT id, name, surname FROM users WHERE id NOT IN (2);
+----+------+---------+
| id | name | surname |
+----+------+---------+
|  1 | Jane | Doe     |
|  3 | John | Doe     |
+----+------+---------+

12.2. MySQL like

$ mysql foo
mysql> SELECT id, name, surname FROM users \
    -> WHERE name LIKE 'J%';
+----+------+---------+
| id | name | surname |
+----+------+---------+
|  1 | Jane | Doe     |
|  3 | John | Doe     |
+----+------+---------+

mysql> SELECT id, name, surname FROM users \
    -> WHERE name NOT LIKE 'J%';
+----+------+---------+
| id | name | surname |
+----+------+---------+
|  2 | Baby | Doe     |
+----+------+---------+

13. MySQL union

Syntax:

SELECT field(s) FROM table_name1 \
UNION [ALL] SELECT field(s) FROM table_name2 \
[UNION [ALL] SELECT field(s) FROM table_nameN];

$ mysql foo
mysql> CREATE TABLE IF NOT EXISTS superheroes \
    -> (id INT PRIMARY KEY, name TEXT);
mysql> INSERT INTO superheroes (id, name) \
    -> VALUES (1, 'Batwoman');
mysql> INSERT INTO superheroes (id, name) \
    -> VALUES (2, 'Batman');
mysql> INSERT INTO superheroes (id, name) \
    -> VALUES (3, 'Batman');
mysql> SELECT CONCAT(name, ' ', surname) AS name \
    -> FROM users UNION ALL SELECT name FROM superheroes;
+----------+
| name     |
+----------+
| Jane Doe |
| Baby Doe |
| Batwoman |
| Batman   |
| Batman   |
+----------+

mysql> SELECT CONCAT(name, ' ', surname) AS name \
    -> FROM users UNION SELECT name FROM superheroes;
mysql> SELECT CONCAT(name, ' ', surname) AS name \
    -> FROM users UNION DISTINCT SELECT name FROM superheroes;
+----------+
| name     |
+----------+
| Jane Doe |
| Baby Doe |
| Batwoman |
| Batman   |
+----------+

Note: MySQL UNION ALL includes duplicate records, UNION or UNION DISTINCT don’t.

14. MySQL conditionals

14.1. MySQL case

Syntax:

SELECT CASE \
WHEN condition1 THEN value1
WHEN condition2 THEN value2
...
WHEN conditionN THEN valueN
ELSE valueK END AS name
FROM table_name;

Uses MySQL CASE to determine what hobbies are more popular:

$ mysql foo
mysql> INSERT INTO users (id, name, surname) \
    -> VALUES (3, 'John', 'Doe');
mysql> UPDATE hobbies SET userid=2 WHERE userid=99;
mysql> INSERT INTO hobbies (userid, description) \
    -> VALUES (1, 'Chess');
mysql> INSERT INTO hobbies (userid, description) \
    -> VALUES (3, 'Chess');
mysql> INSERT INTO hobbies (userid, description) \
    -> VALUES (3, 'Astronomy');
mysql> SELECT id, userid, description FROM hobbies;
+----+--------+-------------+
| id | userid | description |
+----+--------+-------------+
|  1 |      1 | Hiking      |
|  2 |      1 | Astronomy   |
|  3 |      2 | Chess       |
|  4 |      1 | Chess       |
|  5 |      3 | Chess       |
|  6 |      3 | Astronomy   |
+----+--------+-------------+

mysql> SELECT description AS hobby, \
    -> CASE WHEN COUNT(1) > 2 THEN 'Popular' \
    -> WHEN COUNT(1) > 1 THEN 'Regular' \
    -> ELSE 'Unpopular' END AS result \
    -> FROM hobbies GROUP BY description;
+-----------+-----------+
| hobby     | result    |
+-----------+-----------+
| Astronomy | Regular   |
| Chess     | Popular   |
| Hiking    | Unpopular |
+-----------+-----------+

15. MySQL views

15.1. MySQL create view

Syntax:

CREATE [OR REPLACE] VIEW view_name AS \
SELECT field1, field2, ..., fieldN \
FROM table_names [WHERE conditions]

Create a new view called people as a union from users and superheroes tables:

$ mysql foo
mysql> CREATE OR REPLACE VIEW people AS \
    -> SELECT CONCAT(name, ' ', surname) AS name \
    -> FROM users UNION SELECT name FROM superheroes;
mysql> SELECT name FROM people;
+----------+
| name     |
+----------+
| Jane Doe |
| Baby Doe |
| John Doe |
| Batwoman |
| Batman   |
+----------+

16. MySQL describe

16.1. MySQL describe table

$ mysql foo
mysql> DESCRIBE users;
+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| id      | int(11)  | NO   | PRI | NULL    |       |
| name    | char(50) | YES  |     | NULL    |       |
| surname | char(50) | YES  |     | NULL    |       |
+---------+----------+------+-----+---------+-------+

17. MySQL alter

17.1. MySQL alter table add column

$ mysql foo
mysql> ALTER TABLE users ADD COLUMN phone VARCHAR(15);
mysql> ALTER TABLE users ADD COLUMN to_delete VARCHAR(15);
mysql> DESCRIBE users;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id          | int(11)     | NO   | PRI | NULL    |       |
| name        | char(50)    | YES  |     | NULL    |       |
| surname     | char(50)    | YES  |     | NULL    |       |
| phone       | varchar(15) | YES  |     | NULL    |       |
| to_delete   | varchar(15) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+

17.2. MySQL alter table drop column

$ mysql foo
mysql> ALTER TABLE users DROP COLUMN to_delete;
mysql> DESCRIBE users;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | NO   | PRI | NULL    |       |
| name    | char(50)    | YES  |     | NULL    |       |
| surname | char(50)    | YES  |     | NULL    |       |
| phone   | varchar(15) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

17.3. MySQL alter table rename table

$ mysql foo
mysql> ALTER TABLE users RENAME TO users_foo;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW TABLES LIKE 'users%';
+------------------------+
| Tables_in_foo (users%) |
+------------------------+
| users_foo              |
+------------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE users_foo RENAME TO users;
Query OK, 0 rows affected (0.07 sec)

mysql> SHOW TABLES LIKE 'users%';
+------------------------+
| Tables_in_foo (users%) |
+------------------------+
| users                  |
+------------------------+
1 row in set (0.00 sec)

18. MySQL encryption and hashing

18.1. MySQL encrypt column

$ mysql foo
mysql> ALTER TABLE users ADD COLUMN email TEXT;
mysql> UPDATE users SET email=AES_ENCRYPT('jane_doe@example.com',\
    -> 'my_encryption_key') WHERE id=1;
mysql> UPDATE users SET email=AES_ENCRYPT('baby_doe@example.com',\
    -> 'my_encryption_key') WHERE id=2;
mysql> UPDATE users SET email=AES_ENCRYPT('john_doe@example.com',\
    -> 'my_encryption_key') WHERE id=3;
mysql> SELECT name, surname, email FROM users;
+------+---------+-----------------+
| name | surname | email           |
+------+---------+-----------------+
| Jane | Doe     | (binary_string) |
| Baby | Doe     | (binary_string) |
| John | Doe     | (binary_string) |
+------+---------+-----------------+

18.2. MySQL decrypt column

$ mysql foo
mysql> SELECT name, surname, AES_DECRYPT(email, \
    -> 'my_encryption_key') FROM users;
 name | surname |      email
------+---------+------------------
 Jane | Doe     | jdoe@example.com
 Baby | Doe     | bdoe@example.com

18.3. MySQL hash password

$ mysql foo
mysql> ALTER TABLE users ADD COLUMN password TEXT;
mysql> UPDATE users SET password=SHA2('my_password_for_jane_doe',\
    -> 256) WHERE id=1;
mysql> UPDATE users SET password=SHA2('my_password_for_babe_doe',\
    -> 256) WHERE id=2;
mysql> UPDATE users SET password=SHA2('my_password_for_john_doe',\
    -> 256) WHERE id=3;
mysql> SELECT name, surname, password FROM users;
+------+---------+---------------+
| name | surname | password      |
+------+---------+---------------+
| Jane | Doe     | ZfP6yj7V3ipR. |
| Baby | Doe     | hfonZFwhn7.HA |
| John | Doe     | nfDFNF/h.6rOM |
+------+---------+---------------+

18.4. MySQL check hashed password

$ mysql foo
mysql> SELECT name, surname FROM users \
    -> WHERE password=SHA2('my_password_for_jane_doe', 256);
+------+---------+
| name | surname |
+------+---------+
| Jane | Doe     |
+------+---------+
1 row in set (0.00 sec)

mysql> SELECT name, surname FROM users \
    -> WHERE password=SHA2('my_wrong_password', 256);
Empty set (0.00 sec)

19. MySQL version

mysql> SELECT version();
+-----------+
| version() |
+-----------+
| 5.7.28    |
+-----------+

Loading Comments

DevOps books:

Cloud providers:

DigitalOcean offers affordable pricing for VMs and many other public cloud services. You can sign up for DigitalOcean and receive a $100 free credit using this referral link.