CASE

used in

SELECT

statement determines student's gender based

gender_id

column

SELECT
    id,
    first_name,
    CASE gender
    	WHEN 1 THEN 'Male'
    	WHEN 2 THEN 'Female'
        ELSE 'Undefined'
    END AS student_gender
FROM students

Decrypts encrypted text. So we can get back the original string. In this example string

12000

is encrypted and then decrypted back

SELECT 
    '12000' AS original_string,
    AES_ENCRYPT('12000', 'some secret key') AS encrypted_string,
    AES_DECRYPT(AES_ENCRYPT('12000', 'some secret key'), 'some secret key') decrypted_string

Encrypts text using

AES

algorithm

SELECT 
    '12000' AS original_string,
    AES_ENCRYPT('12000', 'some secret key') AS encrypted_string

Calculates HASH of the given string. Length of the returned HASH is always the same.

SELECT
    SHA2('passwd', 256) password_hash

The Result of the function with

256

will always be ``64 characters long alphanumeric string. In this example the result will be:

0d6be69b264717f2dd33652e212b173104b4a647b7c11ae72e9885f11cd312fb

Note: We can never get back to the original value. There is no way to undo the hashing calculation

Extracts original string from compressed binary string

SELECT 
    (REPEAT('abc-123', 10) = UNCOMPRESS(COMPRESS(REPEAT('abc-123', 10)))) comparison

Compresses the string to take less space than the original string would take. In this example the original string takes

70

bytes and compressed string takes

22

bytes

SELECT 
    REPEAT('abc-123', 10) full_string,
    LENGTH(REPEAT('abc-123', 10)) full_string_length,
    COMPRESS(REPEAT('abc-123', 10)) compressed_string,
    LENGTH(COMPRESS(REPEAT('abc-123', 10))) compressed_string_lengtha

Repeats the given string (the first parameter) a number of times (the second parameter) In the example below, the string

abc-123

will be repeated

10

times

SELECT 
    REPEAT('abc-123', 10)

If any column name changes after creating a VIEW, selecting the VIEW will get the following error:

 #1356 - View 'university.student_notes' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

In this case we need to drop and recreate the VIEW

DROP VIEW IF EXISTS student_notes;

CREATE VIEW student_notes AS
SELECT 
    students.first_name,
    students.last_name,
    students.points,
    notes.*
FROM students
JOIN notes ON notes.student_id = students.id

Updatable VIEW can not include:

- UNION
- DISTINCT
- HAVING
- LEFT / RIGHT JOINs
- Sub-queries
- GROUP BY
- Aggregate Functions

1. 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'

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

3. 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
    )

4. Updates students table based on notes table column -

notes.id

using

VIEW

First we need to create the VIEW

DROP VIEW IF EXISTS student_notes;

CREATE VIEW student_notes AS
SELECT 
    students.first_name,
    students.last_name,
    students.points,
    notes.*
FROM students
JOIN notes ON notes.student_id = students.id

Then we update the VIEW as a regular table

UPDATE `student_notes` 
SET points = points + 1 
WHERE id = 1;

5. Alternative of the above four updates is to run two completely different queries:

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