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

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.