Pros

Fast SELECT

Cons

Slow UPDATE, INSERT, DELETE
Hard to maintain
Needs a lot of space

Without guid_field INDEX Tests against SELECT statements. Selects Individual rows using GUID field. Time comparison:

0.0020 - 0.0037 seconds

/

35 - 36 seconds

SELECT * FROM `students123` WHERE guid_field = '90f2c8af-7ead-11eb-9a81-b4b52f79163f'
SELECT * FROM `students123` WHERE guid_field = '90f2c35d-7ead-11eb-9a81-b4b52f79163f'

Selects all rows and orders by the GUID field. Time comparison:

0.0020 seconds

/

35 - 37 seconds

SELECT * FROM `students123` ORDER BY `students123`.`guid_field` DESC

Tests against INSERT statements. Inserts 1.000.000 rows. Time comparison:

34.1974 - 44.1248 seconds

/

18.3200 - 17.4967 seconds

INSERT INTO `students123` (guid_field, student_id, first_name, last_name, points)
SELECT UUID(), student_id, first_name, last_name, points FROM students123 WHERE id < 1000001

Tests against UPDATE statements. Updates 1M rows. Time comparison:

57.2683 - 66.9459 seconds

/

10.2285 - 12.5088 seconds

UPDATE `students123` SET guid_field = UUID() WHERE id > 1000000 AND id < 2000001

Tests against DELETE statements. Deletes 1M rows. Time comparison:

70.3673 - 76.8969 seconds

/

7.1480 - 19.2191 seconds

DELETE FROM `students123` WHERE id > 1000000 AND id < 2000001

To create the table and run the above tests

CREATE TABLE `students123` (
  `id` int(11) NOT NULL,
  `guid_field` varchar(100) NOT NULL,
  `student_id` int(11) NOT NULL,
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  `points` int(11) NOT NULL,
  `mail` varchar(255) DEFAULT NULL,
  `mail2` varchar(255) NOT NULL,
  `santa_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

NATURAL

join tries to find the fields with the same name and joins them

SELECT 
    students.*,
    notes.*
FROM `notes`
NATURAL JOIN `students` 

Note: using

NATURAL

join is dangerous, because we don't know explicitly which fields will be joint.

SELECT students.*, notes.* 
FROM `notes` 
JOIN `students`
	ON notes.student_id = students.student_id
    AND notes.id = students.id

Returns the same result as the above query

If the fields names that we want to join are the same, we can use

USING

keyword

SELECT 
    students.first_name, 
    notes.*
FROM `students` 
LEFT JOIN notes USING(student_id)

The alternative of the above query using

ON

clause:

SELECT 
    students.first_name, 
    notes.*
FROM `students` 
LEFT JOIN notes ON notes.student_id = students.student_id

There is no such

FULL JOIN

in MySQL but we can get the same result using

UNION

SELECT 
    students.*, 
    notes.*
FROM `students` 
LEFT JOIN notes ON notes.student_id = students.id
UNION
SELECT 
    students.*, 
    notes.*
FROM `students` 
RIGHT JOIN notes ON notes.student_id = students.id

As default,

UNION

removes duplicate rows. If we want to get all the rows including duplicates, we can use

UNION ALL

SELECT 
    students.*, 
    notes.*
FROM `students` 
LEFT JOIN notes ON notes.student_id = students.id
UNION ALL
SELECT 
    students.*, 
    notes.*
FROM `students` 
RIGHT JOIN notes ON notes.student_id = students.id

Divides all the students into different classes

SELECT *, 'Brilliant' AS class
FROM `students`
WHERE points >= 90
UNION
SELECT *, 'Gold'
FROM `students`
WHERE points >= 80 
    AND points < 90
UNION
SELECT *, 'Silver'
FROM `students`
WHERE points >= 60 
    AND points < 80
UNION
SELECT *, 'Lazy'
FROM `students`
WHERE points < 60
ORDER BY points DESC

There are two types of

OUTER

JOINS: 1. LEFT JOIN - same as

LEFT OUTER JOIN

2. RIGHT JOIN - same as

RIGHT OUTER JOIN

LEFT JOIN example

SELECT 
    students.first_name, 
    notes.*
FROM `students` 
LEFT JOIN notes ON notes.student_id = students.id

These two joins are the same, they produce the same result

LEFT OUTER JOIN

example:

SELECT 
    students.first_name, 
    notes.*
FROM `students` 
LEFT OUTER JOIN notes ON notes.student_id = students.id

Note: The same goes for

RIGHT JOIN

and

RIGHT OUTER JOIN

, they produce the same result

LIMIT

clause format: Starts from

X

rows, Selects

Y

rows

-- LIMIT Y
-- LIMIT X Y

LIMIT

clause is used to limit the number of rows returned as a result

SELECT
    id,
    first_name
FROM students
LIMIT 2

LIMIT

clause for pagination. If two arguments are passed to LIMIT clause, the first one

offset

, which indicates how many rows we want to skip. In this example, we want to skip the first 10 records and get the following 5 records. In this case we want to get 3'th page

SELECT 
    first_name
    last_name
FROM students
ORDER BY points DESC
LIMIT 10, 5

Selects the student with the highest points

SELECT *
FROM students
ORDER BY points DESC
LIMIT 1

ASC

is optional because it's default value

ORDER BY

clause in

SELECT

statement

SELECT first_name, last_name 
FROM students
ORDER BY id DESC

Orders the result by

last_name

with

ascending

order

SELECT first_name, last_name 
FROM students
ORDER BY last_name ASC

ORDER BY

with several fields

SELECT first_name, last_name 
FROM students
ORDER BY last_name ASC, first_name ASC

Grouped by

last_name

and Ordered by

Average points

SELECT 
    last_name,
    AVG(points) average_points
FROM students
GROUP BY last_name
ORDER BY average_points DESC

Mathematical expression used to order the result

SELECT *
FROM `orders` 
ORDER BY price * quantity DESC

Order the result using

alias

SELECT *, price * quantity AS total
FROM `orders` 
ORDER BY total DESC

Orders the result by first name with

ASC

and by id with

DESC

using column numbers

SELECT
    id,
    first_name
FROM `students`
ORDER BY 2, 1 DESC

%

- represents any number of characters

_

- represents one single character Select all students which lastname contains 'უა' with

LIKE

operator

SELECT *
FROM students
WHERE last_name LIKE '%უა%'

Select all students which

lastname

ends with 'უა' using

LIKE

operator

SELECT *
FROM students
WHERE last_name LIKE '%უა'

Select all students which

lastname

does not contain 'უა' with

NOT LIKE

operator

SELECT *
FROM students
WHERE last_name NOT LIKE '%უა%'

Select all students where

lastname

starts with any two character followed with

უა

and we don't care what the other following symbols are (with

_

)

SELECT *
FROM students
WHERE last_name LIKE '__უა%'

Select all students where

lastname

is exactly 5 characters long and the second symbol is

უ

SELECT *
FROM students
WHERE last_name LIKE '_უ___'

Select all students where

lastname

is exactly 5 characters, starts with

a

and ends with

y

and has exacly any three letters between them

SELECT *
FROM students
WHERE last_name LIKE 'a___y'

Select all students where

lastname

ends with either

ძე

or

შვილი

SELECT *
FROM students
WHERE last_name LIKE '%ძე' OR last_name LIKE '%შვილი'

Deletes all the records FROM

notes

table. This is too dangerous command because there is no undo when it comes to databases. If we update or delete something, the original values are gone.

DELETE FROM notes;

Deletes the row with the specified private key -

id

. We should always use

WHERE

clause, not to delete everything from the database

DELETE FROM `requests` WHERE `id` = '1548630';

Deletes the student with the specified first name and last name

DELETE FROM `requests` WHERE (first_name = 'John' AND last_name = 'Doe');