Updates VIEW

student_notes

, increases author's points by one in

students

table based on note's ID. First we need to create the VIEW

CREATE VIEW IF NOT EXISTS 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;

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;

Updatable VIEW can not include:

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

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

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)

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

Extracts original string from compressed binary string

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

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

Encrypts text using

AES

algorithm

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

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