gbarreiro

# Use an Ubuntu image as base image
FROM ubuntu:latest

# Metadata for the container
LABEL description="This is a dummy container" author="Guillermo Barreiro"

# Set the working directory inside the container for the following Dockerfile instructions
WORKDIR /root

# Use an argument sent to the "docker build" command inside the Dockerfile

gbarreiro

SET GLOBAL event_scheduler = ON; -- ensures that the MySQL event scheduler is on
    
-- One-time events
CREATE EVENT IF NOT EXISTS my_event
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 12 HOUR -- the event will be run in 12h and then will be deleted
DO
    DELETE * FROM Logs;  -- something random

CREATE EVENT IF NOT EXISTS my_event
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 24 MINUTE -- the event will be run in 24 minutes...

gbarreiro

-- Triggered after a INSERT operation on "Students" table
CREATE TRIGGER my_trigger AFTER INSERT ON Students FOR EACH ROW
BEGIN
    -- ... instructions to be performed ... --

END;

-- Triggered before a DELETE operation on "Teachers" table
CREATE TRIGGER my_trigger BEFORE DELETE ON Teachers FOR EACH ROW
BEGIN

gbarreiro

DELIMITER // -- we need to change it, in order to be able of writing ; in the instructions
CREATE PROCEDURE get_age(IN user_id INT, OUT user_age INT) -- the procedure has one parameter and returns one variable
BEGIN
    SELECT age INTO user_age FROM Students WHERE id = user_id;
    -- more instructions... --
END //
DELIMITER ; -- don't forget to restore the original delimiter !

CALL get_age(12, @age); -- call the procedure, send a parameter and store the result into the session variable age

gbarreiro

-- if-else
IF boolean_condition THEN
    -- ... instructions ...
[ELSEIF boolean_condition THEN] 
    -- ... instructions ...
ELSE
    -- ... instructions ...
END IF

-- normal loop

gbarreiro

-- Declaring the cursor
DECLARE end_cursor BOOLEAN DEFAULT FALSE; -- will turn true when the cursor reaches the last tuple
DECLARE my_cursor CURSOR FOR SELECT * from Students; -- a cursor iterates through the result of a SELECT or CALL statement
DECLARE CONTINUE HANDLER FOR NOT FOUND SET end_cursor = TRUE;

-- Iterating through the cursor
WHILE NOT end_cursor DO
    FETCH cursor INTO @my_student;
    -- Do something with the current row --
END WHILE;

gbarreiro

-- Local variables (must be declared inside a STORED PROCEDURE)
DECLARE john_age INT; -- defines the variable
SET john_age = 21; -- sets a hard-coded value to a variable
SELECT age INTO jonh_age FROM Students WHERE id = 10; -- stores the result of a query into a variable

-- Session (user-defined) variables
SET @current_age = 21; -- we don't need to define it before
SELECT age INTO @current_age FROM Students WHERE id = 10; -- stores the result of a query into a session variable

gbarreiro

-- Enabling and disabling automatic transactions

SET AUTOCOMMIT = 1; -- each SQL sentence is a transaction; i.e. it's automatically comitted into the actual DB (enabled by default)
SET AUTOCOMMIT = 0; -- SQL sentences are not automatically commited into the DB, so you must do it manually with the COMMIT instruction
COMMIT; -- commits a transaction

-- Running a transaction inside a stored procedure
START TRANSACTION;
    -- ... instructions of the transaction ...
COMMIT;

gbarreiro

-- Basic query
SELECT * FROM Students;

-- Get only some columns, and filter the results
SELECT name, country FROM Students WHERE age>28;

-- Limit the number of results and sort them alphabetically
SELECT * FROM Students SORT BY name LIMIT 20;

-- Get the number of students for each country

gbarreiro

-- Insert a single row, providing a value for each column
INSERT INTO Students VALUES (1, 'Peter', 25);

-- Insert a single row, not providing a value for every column
INSERT INTO Students(id, name) VALUES (1, 'Peter');

-- Insert multiple rows at the same time
INSERT INTO Students VALUES (1, 'Peter', 25), (2, 'Frank', 19), (3, 'William', 21);