search
Search
Login
Unlock 100+ guides
menu
menu
web
search toc
close
Comments
Log in or sign up
Cancel
Post
account_circle
Profile
exit_to_app
Sign out
What does this mean?
Why is this true?
Give me some examples!
search
keyboard_voice
close
Searching Tips
Search for a recipe:
"Creating a table in MySQL"
Search for an API documentation: "@append"
Search for code: "!dataframe"
Apply a tag filter: "#python"
Useful Shortcuts
/ to open search panel
Esc to close search panel
to navigate between search results
d to clear all current filters
Enter to expand content preview
icon_star
Doc Search
icon_star
Code Search Beta
SORRY NOTHING FOUND!
mic
Start speaking...
Voice search is only supported in Safari and Chrome.
Navigate to
chevron_leftMySQL
Common questions2 topics
Cookbooks7 topics
Documentation5 topics
Getting startedAPI referenceRecipes reference
check_circle
Mark as learned
thumb_up
0
thumb_down
1
chat_bubble_outline
0
Comment
auto_stories Bi-column layout
settings

Getting started with MySQL

schedule Aug 10, 2023
Last updated
local_offer
MySQL
Tags
mode_heat
Master the mathematics behind data science with 100+ top-tier guides
Start your free 7-days trial now!

What is MySQL?

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.

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