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;

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;

Creates

VIEW

called

student_notes

that selects all notes with their authors

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

After creating the

VIEW

then we can use it as regular table like this example:

SELECT * 
FROM student_notes

Creates function

full_name

that concatenates the two parameters and returns the result

DROP FUNCTION IF EXISTS full_name;

CREATE FUNCTION full_name ( first_name VARCHAR(100), last_name VARCHAR(100) )
RETURNS VARCHAR(210)
RETURN CONCAT(first_name, ' ', last_name);

After creating the function we can call it:

SELECT 
    full_name('John', 'Swift');

Function

DATE_FORMAT

returns formatted version of the datetime string passed as the first parameter

SELECT 
    DATE_FORMAT('2022-03-25 18:24:14', '%Y-%m-%d %H:%i:%s')

In this example we only need to get month and day with underscore between the values

SELECT 
    DATE_FORMAT('2022-03-25 18:24:14', '%m_%d')