gbarreiro

-- Create the database
CREATE DATABASE University;

-- Use a database
USE University;

-- Assign permissions to a database
GRANT ALL ON University TO mike
GRANT INSERT, UPDATE ON University.Students TO william
GRANT SELECT(id, name) ON University.Teachers TO john 

gbarreiro

CREATE TABLE Teachers (name VARCHAR(30), age INT);

-- Primary key:
CREATE TABLE Teachers (id INT PRIMARY KEY, name VARCHAR(30), age INT); # primary key: main index, can't be repeated
CREATE TABLE Students (id INT, name VARCHAR(30), age INT, PRIMARY KEY (code, name, age)); # primary key is compound
CREATE TABLE Subjects (id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, name VARCHAR(30)); # primary key is automatically generated, starting in 1 and following 2,3...

-- Restrictions:
CREATE TABLE Teachers (id INT PRIMARY KEY NOT NULL, name VARCHAR(30) NOT NULL, age INT NOT NULL); # NOT NULL: the column value can't be empty
CREATE TABLE Teachers (id INT PRIMARY KEY, name VARCHAR(30), age INT, CHECK (age>18 and age<70)); # CHECK: the column value must comply some conditions

gbarreiro

-- The sentence below...
SELECT (id, name, age) FROM Students UNION ALL SELECT (id, name, age) FROM Teachers;

-- ... is the same than...
CREATE VIEW People AS SELECT (id, name, age) FROM Students UNION ALL SELECT (id, name, age) FROM Teachers;
SELECT * from People;

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);

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

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

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

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

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

-- normal loop

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