MySQL is a freely available open-source relational database management system (RDBMS). A RDBMS is a system that helps individuals and organizations store and access the data they have available, and maintain relationships between those pieces of data.

In MySQL, we use SQL (Structured Query Language) to interact with our database to retrieve, update or insert data. Although the majority of the syntax used in MySQL is standard SQL, be aware that some is unique to MySQL and cannot be used with other RDBMS.

NOTE

SQL is a standard language defined by the ANSI/ISO standard that is used to access and manipulate information that is stored in relational database management systems. Some of the most common commands include SELECT, UPDATE, DELETE, INSERT, WHERE.

Installing MySQL

You can install MySQL from the following link: https://dev.mysql.com/doc/mysql-installation-excerpt/8.0/en/open_in_new. The website provides instructions on how MySQL can be installed across various operating systems.

Key Terminology

Database

A database is a collection of tables that contain information relating to a certain area.

Create a database

To create a database called people:


        
        
            
                
                
                    CREATE DATABASE people;
                
            
            Query OK, 1 row affected (0.01 sec)

Show all databases

To show all databases use the below query:


        
        
            
                
                
                    SHOW DATABASES;
                
            
            +--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| people             |
| performance_schema |
| sys                |
+--------------------+

We can see we have 5 databases available.

Selecting a database

To select the people database to use:


        
        
            
                
                
                    USE people;
                
            
            Database changed

Checking tables part of a database

To show all tables in the currently selected database:


        
        
            
                
                
                    SHOW TABLES;
                
            
            +------------------+
| Tables_in_people |
+------------------+
| info             |
| students         |
+------------------+

Table

A table has columns and rows of data.

A table representing some information about some students may look something like the below:


        
        
            
                
                
                    +------------+----------+--------+--------------+------+----------+
| student_id | fname    | lname  | day_enrolled | age  | username |
+------------+----------+--------+--------------+------+----------+
|          1 | Sky      | Towner | 2015-12-03   |   17 | stowner1 |
|          2 | Ben      | Davis  | 2016-04-20   |   19 | bdavis2  |
|          3 | Travis   | Apple  | 2018-08-14   |   18 | tapple3  |
|          4 | Arthur   | David  | 2016-04-01   |   16 | adavid4  |
|          5 | Benjamin | Town   | 2014-01-01   |   17 | btown5   |
+------------+----------+--------+--------------+------+----------+

Refer here for how this table was created.

Create a table

Consider the following information about some people:

id	name	age	hobby
1	alex	30	Programming
2	bob	15	Programming
3	cathy	20	Swimming

To create the above table in MySQL and call it info:


        
        
            
                
                
                    CREATE TABLE info (id INT, name VARCHAR(20), age INT, hobby VARCHAR(20));

To add records for the three people into the table info:


        
        
            
                
                
                    INSERT INTO info (id, name, age, hobby) VALUES (1, 'alex', 30, 'Programming');
INSERT INTO info (id, name, age, hobby) VALUES (2, 'bob', 15, 'Programming');
INSERT INTO info (id, name, age, hobby) VALUES (3, 'cathy', 20, 'Swimming');

SELECT * FROM info;
                
            
            +------+-------+------+-------------+
| id   | name  | age  | hobby       |
+------+-------+------+-------------+
|    1 | alex  |   30 | Programming |
|    2 | bob   |   15 | Programming |
|    3 | cathy |   20 | Swimming    |
+------+-------+------+-------------+

Show all fields of a table

Suppose we create a table called students using the below query:


        
        
            
                
                
                    CREATE TABLE students (
   id INT UNSIGNED AUTO_INCREMENT,

   name VARCHAR(30) NOT NULL, 
   age INT DEFAULT 20,
   PRIMARY KEY (id)
);

To check the fields of the table at a later point in time, use the DESCRIBE statement:


        
        
            
                
                
                    DESCRIBE students;
                
            
            +-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(30)  | NO   |     | NULL    |                |
| age   | int          | YES  |     | 20      |                |
+-------+--------------+------+-----+---------+----------------+

Delete a table

To delete an existing table called students:


        
        
            
                
                
                    DROP TABLE IF EXISTS students;
                
            
            Query OK, 0 rows affected (0.01 sec)

Data manipulation

Selecting data from a table

Consider the following table about some students:

student_id	fname	lname	day_enrolled	age	username
1	Sky	Towner	2015-12-03	17	stowner1
2	Ben	Davis	2016-04-20	19	bdavis2
3	Travis	Apple	2018-08-14	18	tapple3
4	Arthur	David	2016-04-01	16	adavid4
5	Benjamin	Town	2014-01-01	17	btown5

The above sample table can be created using the code here.

To retrieve data for all columns from the table, use MySQL's SELECT statement:


        
        
            
                
                
                    SELECT * FROM students;
                
            
            +------------+----------+--------+--------------+------+----------+
| student_id | fname    | lname  | day_enrolled | age  | username |
+------------+----------+--------+--------------+------+----------+
|          1 | Sky      | Towner | 2015-12-03   |   17 | stowner1 |
|          2 | Ben      | Davis  | 2016-04-20   |   19 | bdavis2  |
|          3 | Travis   | Apple  | 2018-08-14   |   18 | tapple3  |
|          4 | Arthur   | David  | 2016-04-01   |   16 | adavid4  |
|          5 | Benjamin | Town   | 2014-01-01   |   17 | btown5   |
+------------+----------+--------+--------------+------+----------+

Selecting data based on a condition

To select only students older than 17 from the above students table, use MySQL's WHERE clause:


        
        
            
                
                
                    SELECT * FROM students
WHERE age > 17;
                
            
            +------------+--------+-------+--------------+------+----------+
| student_id | fname  | lname | day_enrolled | age  | username |
+------------+--------+-------+--------------+------+----------+
|          2 | Ben    | Davis | 2016-04-20   |   19 | bdavis2  |
|          3 | Travis | Apple | 2018-08-14   |   18 | tapple3  |
+------------+--------+-------+--------------+------+----------+

We can see that only two of the students Ben and Travis are older than 17.

Selecting data based on multiple conditions

MySQL's AND, OR, NOT, XOR operators used in conjunction with a WHERE clause allow us to retrieve records from a table based on multiple conditions.

For example, we can retrieve students satisfying two conditions using the AND operator.

Here is a reminder of the table we are working with for your convenience:

student_id	fname	lname	day_enrolled	age	username
1	Sky	Towner	2015-12-03	17	stowner1
2	Ben	Davis	2016-04-20	19	bdavis2
3	Travis	Apple	2018-08-14	18	tapple3
4	Arthur	David	2016-04-01	16	adavid4
5	Benjamin	Town	2014-01-01	17	btown5

The above sample table can be created using the code here.

To retrieve students who have student_id > 2 and age > 17:


        
        
            
                
                
                    SELECT *
FROM students
WHERE student_id > 2 AND age > 17;
                
            
            +------------+--------+-------+--------------+------+----------+
| student_id | fname  | lname | day_enrolled | age  | username |
+------------+--------+-------+--------------+------+----------+
|          3 | Travis | Apple | 2018-08-14   |   18 | tapple3  |
+------------+--------+-------+--------------+------+----------+

Only Travis satisfies both conditions so is returned.

Ordering results

MySQL's ORDER BY clause sorts the retrieved records and returns them in ascending order by default.

To retrieve students in alphabetical order of their first names:


        
        
            
                
                
                    SELECT *
FROM students
ORDER BY fname;
                
            
            +------------+----------+--------+--------------+------+----------+
| student_id | fname    | lname  | day_enrolled | age  | username |
+------------+----------+--------+--------------+------+----------+
|          4 | Arthur   | David  | 2016-04-01   |   16 | adavid4  |
|          2 | Ben      | Davis  | 2016-04-20   |   19 | bdavis2  |
|          5 | Benjamin | Town   | 2014-01-01   |   17 | btown5   |
|          1 | Sky      | Towner | 2015-12-03   |   17 | stowner1 |
|          3 | Travis   | Apple  | 2018-08-14   |   18 | tapple3  |
+------------+----------+--------+--------------+------+----------+

We can see that students are now returned in alphabetical order of their first names.

Limiting the number of rows returned

MySQL's LIMIT clause limits the number of returned results.

To return up to 2 records from the students table:


        
        
            
                
                
                    SELECT *
FROM students
WHERE age > 1
LIMIT 2;
                
            
            +------------+-------+--------+--------------+------+----------+
| student_id | fname | lname  | day_enrolled | age  | username |
+------------+-------+--------+--------------+------+----------+
|          1 | Sky   | Towner | 2015-12-03   |   17 | stowner1 |
|          2 | Ben   | Davis  | 2016-04-20   |   19 | bdavis2  |
+------------+-------+--------+--------------+------+----------+

Joining tables

Consider the following table about some students:

student_id	fname	lname	day_enrolled	age	username
1	Sky	Towner	2015-12-03	17	stowner1
2	Ben	Davis	2016-04-20	19	bdavis2
3	Travis	Apple	2018-08-14	18	tapple3
4	Arthur	David	2016-04-01	16	adavid4
5	Benjamin	Town	2014-01-01	17	btown5

The above sample table can be created using the code here.

Also consider the following table about students' extracurricular activities:

student_id	club	date_entered
1	Football	2016-02-13
2	Boxing	2016-05-25
3	Apple	2018-08-17
4	Fishing	2017-01-01
5	NULL	NULL

The above sample table can be created using the code here.

Inner Join

MySQL's INNER JOIN clause retrieves rows from both tables where there is a common matching column value.

To retrieve information about students and their extracurricular activities by using the common column student_id:


        
        
            
                
                
                    SELECT fname, lname, age, club
FROM students
INNER JOIN extracurricular
ON students.student_id = extracurricular.student_id;
                
            
            +----------+--------+------+----------+
| fname    | lname  | age  | club     |
+----------+--------+------+----------+
| Sky      | Towner |   17 | Football |
| Ben      | Davis  |   19 | Boxing   |
| Travis   | Apple  |   18 | Chess    |
| Arthur   | David  |   16 | Fishing  |
| Benjamin | Town   |   17 | NULL     |
+----------+--------+------+----------+

Here we return information on fname, lname and age from students table and the corresponding club information from the extracurricular table when there is a match between student_id value in both tables.

Left join

Consider the following table about products bought by some customers:

id	product	bought_by
1	computer	1
2	hair dryer	3

The above sample table can be created using the code here.

Also consider the following table containing customer information:

id	name	age
1	alex	15
2	bob	25
3	cathy	30

The above sample table can be created using the code here.

MySQL's LEFT JOIN clause returns all rows from the left table and records that match from the right table.

To perform a LEFT JOIN with product as the left (main) table and customer as the right table:


        
        
            
                
                
                    SELECT c.id, c.name, c.age, p.name AS product_name 
FROM customer c
LEFT JOIN product p
ON c.id = p.bought_by;
                
            
            +----+-------+------+--------------+
| id | name  | age  | product_name |
+----+-------+------+--------------+
|  1 | alex  |   15 | computer     |
|  2 | bob   |   25 | NULL         |
|  3 | cathy |   30 | hair dryer   |
+----+-------+------+--------------+

We return all three customer records from the left table customer and the corresponding products they have purchased from product (right table). As bob has not purchased any product, NULL is returned for his corresponding product_name value.

Right Join

MySQL's RIGHT JOIN clause returns all rows from the right table and matching records from the left table.

Using the same tables listed under Left Joinlink, to perform a RIGHT JOIN with product as the left table and customer as the right (main) table:


        
        
            
                
                
                    SELECT c.id, c.name, c.age, p.name AS product_name 
FROM product p
RIGHT JOIN customer c
ON c.id = p.bought_by;
                
            
            +----+-----------+------+--------------+
| id | name      | age  | product_name |
+----+-----------+------+--------------+
|  1 | alex      |   15 | computer     |
|  3 | cathy     |   30 | hair dryer   |
|  2 | bob       |   25 | NULL         |
+----+-----------+------+--------------+

We return all three customer records from the right table customer and the corresponding products they have purchased from product (left table). As bob has not purchased any product, NULL is returned for his corresponding product_name value.

Inserting new data to tables

To insert a row into a table in MySQL, use the INSERT INTO syntax:


        
        
            
                
                
                    CREATE TABLE info (name VARCHAR(20), age INT, hobby VARCHAR(20));
INSERT INTO info (name, age, hobby) VALUES ('alex', 30, 'Programming');

To confirm the newly inserted data:


        
        
            
                
                
                    SELECT * FROM info;
                
            
            +------+------+-------------+
| name | age  | hobby       |
+------+------+-------------+
| alex |   30 | Programming |
+------+------+-------------+

We can see that the record for Alex has indeed been inserted.

To insert multiple rows you can provide a comma separated list after the VALUES keyword:


        
        
            
                
                
                    CREATE TABLE info (name VARCHAR(20), age INT, hobby VARCHAR(20));
INSERT INTO info (name, age, hobby) 
VALUES ('alex', 30, 'Programming'),
('bob', 15, 'Programming'),
('cathy', 20, 'Swimming');

To confirm the newly inserted data:


        
        
            
                
                
                    SELECT * FROM info;
                
            
            +-------+------+-------------+
| name  | age  | hobby       |
+-------+------+-------------+
| alex  |   30 | Programming |
| bob   |   15 | Programming |
| cathy |   20 | Swimming    |
+-------+------+-------------+

Deleting data from tables

Consider the following table about products bought by some customers:

id	product	bought_by
1	computer	1
2	hair dryer	3

The above sample table can be created using the code here.

To delete rows from a table use a DELETE statement:


        
        
            
                
                
                    DELETE FROM product
WHERE bought_by = 3;

SELECT * FROM product;
                
            
            +----+----------+-----------+
| id | name     | bought_by |
+----+----------+-----------+
|  1 | computer |         1 |
+----+----------+-----------+

We can see that the record for bought_by = 3 is now deleted from the table.

Published by Arthur Yanagisawa

Edited by 0 others

Did you find this page useful?

thumb_up

thumb_down

Comment

Citation

Ask a question or leave a feedback...

thumb_up

thumb_down

chat_bubble_outline

settings

Enjoy our search

Hit / to insta-search docs and recipes!