Updates students table based on notes table column -

notes.id

using

explicit JOIN

UPDATE notes
INNER JOIN students ON notes.student_id = students.id
SET students.points = students.points + 1
WHERE notes.id = 1

Updates students table based on notes table column

notes.id

UPDATE 
  students 
SET 
  points = points + 1 
WHERE 
  student_id = (
    SELECT 
      student_id 
    FROM 
      notes 
    WHERE 
      notes.id = 1
  )

We can get the same result using explicit join

UPDATE notes
INNER JOIN students ON notes.student_id = students.id
SET students.points = students.points + 1
WHERE notes.id = 1

We can clone a table using

CREATE & SELECT

statement

CREATE TABLE students_archived AS
SELECT * FROM students

We can

SELECT

from one table and

INSERT

the selected data to another table using

INSERT & SELECT

statement

INSERT INTO `students` (`first_name`, `last_name`, `points`) 
SELECT first_name, last_name, points * 1.2 FROM students WHERE id = 3

The only thing we must consider is column positions (position must be the same)

Select all students with points between 80 and 90 (using BETWEEN AND operator)

SELECT *
FROM students
WHERE points BETWEEN 80 AND 90

We can get the same result by using

AND

logical operator

SELECT *
FROM students
WHERE points >= 80
    AND points <= 90

Updates all rows. Increases every student's points by one

UPDATE `students` SET `points` = points+1;

Updates several columns in one row using

private key

UPDATE `students` SET `first_name` = 'თენგიზ', `last_name` = 'ბოჭორიშვილი', mail = 'tengiz@gmail.com' WHERE `students`.`id` = 38;

Updates students table based on notes table column -

notes.id

using

Implicit JOIN

UPDATE notes, students
SET points = points+1
WHERE notes.student_id = students.id
	AND notes.note = 'My first note'

Updates students table based on notes table column -

notes.id

using

explicit JOIN

UPDATE notes
INNER JOIN students ON notes.student_id = students.id
SET students.points = students.points + 1
WHERE notes.id = 1

Updates students table based on notes table column -

notes.id

using sub-query

UPDATE students 
SET points = points + 1 
WHERE student_id = (
        SELECT student_id 
        FROM notes 
        WHERE notes.id = 1
    )

Alternative of the above three updates:

SELECT student_id FROM `notes` WHERE id = 5;
UPDATE students SET points = points + 1 WHERE id = 3;

Categorize students based on their points using

CASE WHEN

conditional statement

SELECT *, 
    CASE 
      	WHEN points>90 THEN "Brilliant"
      	WHEN points>80 THEN "Gold"
      	WHEN points>60 THEN "Silver"
      	ELSE "Lazy"
    END as 'class'
FROM `students`
ORDER BY points DESC

Conditionally checking columns Using

CASE

SELECT * 
FROM `students` 
WHERE 
    (CASE
        WHEN LENGTH(mail) THEN mail
        ELSE mail2
    END) LIKE '%gmail.com%'
ORDER BY points

CASE

in

ORDER BY

clause (if the current day of month is odd, orders by

last_name

, otherwise orders by

first_name

SELECT *
FROM students
ORDER BY (
    CASE DAY(CURDATE())%2
      	WHEN 0 THEN first_name
      	WHEN 1 THEN last_name
    END
) DESC

Creates

index

for

guid_field

column

CREATE INDEX guid_field ON students123 (guid_field);

Removes

guid_field

column

index

ALTER TABLE `students123` DROP INDEX `guid_field`;

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;