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

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