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 tutorial. 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.

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

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 contains the metadata information of the all elements contained in the server such as databases, tables and privileges.

mysql contains users and privileges.

performance_schema 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.

MySQL select current database

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

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

MySQL create table

Syntax:

CREATE TABLE [IF NOT EXISTS] table_name \
(field1 type, field2 type, ..., fieldN type);

Create 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));

MySQL show tables

$ mysql foo
mysql> show tables;
+---------------+
| Tables_in_foo |
+---------------+
| 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      |
+------------+

MySQL drop table

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

MySQL insert

Syntax:

INSERT INTO table_name (field1, field2, ..., fieldN) \
VALUES (value1, value2, ..., valueN');

Insert into table users:

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

MySQL select

Syntax:

SELECT field1, field2, ..., fieldN FROM table_name;

Select users:

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

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 |
+---------------------+

MySQL concat

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

MySQL update

Syntax:

UPDATE table_name SET field1=value1, ..., fieldN=valueN \
 [WHERE field2=value2 ... AND fieldK=fieldK];

Update users:

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

MySQL delete

Syntax:

DELETE FROM table_name [WHERE field1=value1 ... \
AND fieldN=valueN];

Delete users:

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

MySQL truncate

$ mysql foo
mysql> TRUNCATE TABLE users;

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 table we can perform the different MYSQL JOIN queries:

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.

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.

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.

MySQL where

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

MySQL in list

$ 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     |
+----+------+---------+

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     |
+----+------+---------+

MySQL union

$ 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.

MySQL case

$ 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 * 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 |
+-----------+-----------+

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    |       |
+---------+----------+------+-----+---------+-------+

MySQL alter table

MySQL add column

$ mysql foo
mysql> ALTER TABLE users ADD COLUMN phone 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    |       |
+---------+-------------+------+-----+---------+-------+

MySQL 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)

MySQL version

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



Loading Comments