Relearn MySQL in few minutes

This post will be very similar to my previous post about Java learning. The content excerpts from book: Learn SQL (using MySQL) in One Day and Learn It Well written by Jamie Chan. Again, this post is the full recap of what I learn through this book and it might not cover every single aspect of MySQL. Without further ado, let's get started!

SQL Intro

SQL (Structured Query Language) is a language used to manage data stored in a relational database.

A database is a collection of data organized in some format so that the data can be easily accessed, managed and updated.

The software application that we use to manage our database is called DBMS (Database Management System).

Tools that we will be using is MySQL Workbench.

Steps to setup:

Launch MySQL Workbench and click on MySQL Connections to connect to MySQL
Click File > New Query Tab and then save this file (File > Save Script As...).
Enjoy!

# Using SELECT to display messages 
SELECT 'Hello World'; 
SELECT 'MySQL is fun!';

# this is comment
-- this is comment too!
/* 
    this is
    comment block!
*/

# create database
CREATE DATABASE my_database;

# let DBMS know we want to use this database because
# DBMS might manage more than one databases concurrently
USE my_database;

# rename database might be easier to just create new database
# and delete the old one
DROP DATABASE [IF EXISTS] my_database;

# create table with constraints
CREATE TABLE table_name (
    column_name1 datatype [column constraints],
    column_name2 datatype [column constraints],
    ...
    [table constraints],
    [table constraints]
);

Data Types in MySQL

Name	Type	Length	Additional Notes
CHAR(size)	Textual	up to 255 characters	- Fixed length with right-padded whitespaces - CHAR(3) stores string "NY" as "NY "
VARCHAR(size)	Textual	up to 255 characters	- More flexible and use less storage but slower than CHAR(size) - VARCHAR(3) to store string "NY" will be "NY" - If storing strings are of fixed length, CHAR(size) is better choice
INT	Numeric	-2147483648 to 2147483647	- INT UNSIGNED is from 0 to 4294967295
FLOAT(m,d)	Numeric	up to 7 decimal places	- m: total number of digits - d: number of digits after the decimal point
FLOAT(m,d)	Numeric	up to 7 decimal places (4 bytes)	- m: total number of digits - d: number of digits after the decimal point
Double(m,d)	Numeric	up to 14 decimal places (8 bytes)	- m: total number of digits - d: number of digits after the decimal point
Decimal(m,d)	Numeric		- m: total number of digits - d: number of digits after the decimal point - store non-integers as exact values
YEAR	Date/time	2 digit format: 1 - 69 (2001 - 2069) 70 - 99 (1970 - 1999) 4 digit format: 1901 - 2155
DATE	Date/time	'1001-01-01' to '9999-12-31'	- store date in YYYY-MM-DD format
TIME	Date/time	'-838:59:59' to '838:59:59'	- store time in HH:MI:SS format
DATETIME	Date/time	'-1000:01:01 00:00:00' to '9999-12-31 23:59:59'	- store date/time in YYYY-MM-DD HH:MI:SS format
TIMESTAMP	Date/time	'-1970:01:01 00:00:01 UTC' to '2038-01-09 03:14:07 UTC'	- store date/time in YYYY-MM-DD HH:MI:SS format

More Data Types can be found here.

Difference between DATETIME and TIMESTAMP

TIMESTAMP value converts from current timezone to UTC for storage, and back from UTC to current timezone for retrieval while DATETIME does no conversion, value is stored as it is.

Column Constraints in MySQL

Name	Description
NOT NULL	Specify values in column cannot be empty.
UNIQUE	Specify values in column must be unique.
DEFAULT	Set default value for column when no value is specified.
PRIMARY KEY	Specify the column is a primary key (each table has only one primary key). NOTE: a primary key by default NOT NULL and UNIQUE
AUTO_INCREMENT	Specify values in column should be automatically incremented by 1 for each new record. Often used to generate a primary key for the table. Each table can only have one auto increment column. That column must be defined as key such as primary key or unique key.

# create employees table with columns and constraints
CREATE TABLE co_employees ( 
    id INT PRIMARY KEY AUTO_INCREMENT, 
    em_name VARCHAR(255) NOT NULL, 
    gender CHAR(1) NOT NULL, 
    contact_number VARCHAR(255), 
    age INT NOT NULL, 
    date_created TIMESTAMP NOT NULL DEFAULT NOW() 
);

# combine 2 or more columns and used as primary key assuming no two columns 
# have the same combination values
PRIMARY KEY(mentor_id, mentee_id, project)

# foreign key is a column (or a collection of columns) in one table that links to 
# primary key in another table
# specify mentor_id as foreign key, record cannot be added if mentor_id doesn't exist
FOREIGN KEY(mentor_id) REFERENCES co_employees(id)


# ON DELETE CASCADE means if the record in parent table is deleted, the record in 
# child table that has such foreign key will all be deleted as well
# NOTE: ON UPDATE clause is used as ON DELETE clause will update parent table
# ON UPDATE RESTRICT prevents us from updating the primary key in parent table if
# there is at least one row in the child table that references it.
FOREIGN KEY(mentor_id) REFERENCES co_employees(id) ON DELETE CASCADE ON UPDATE RESTRICT

# the row in parent table cannot be deleted if there is a row in child table
# references that parent row
ON DELETE RESTRICT

# the child row foreign key value will be set to NULL if parent row is deleted
# NOTE: for this to work, the relevant column in the child row must allow NULL values
ON DELETE SET NULL

# child row foreign key value will be set to default value if parent row is deleted
ON DELETE SET DEFAULT

# ensure there is no one row with same mentor_id, mentee_id combination
UNIQUE(mentor_id, mentee_id)

# NOTE: each table can have only one primary key but can have many unique keys!

# add name constraints, easier to refer in future if we need to modify it
CONSTRAINT mm_constraint UNIQUE(mentor_id, mentee_id)

# add name constraints for foreign keys
CONSTRAINT fk1 FOREIGN KEY(mentor_id) REFERENCES co_employees(id) ON DELETE CASCADE ON UPDATE RESTRICT

# create mentorships table with column and constraints
CREATE TABLE mentorships ( 
    mentor_id INT NOT NULL, 
    mentee_id INT NOT NULL, 
    status VARCHAR(255) NOT NULL, 
    project VARCHAR(255) NOT NULL, 
    PRIMARY KEY (mentor_id, mentee_id, project), 
    CONSTRAINT fk1 FOREIGN KEY(mentor_id) REFERENCES co_employees(id) ON DELETE CASCADE ON UPDATE RESTRICT, 
    CONSTRAINT fk2 FOREIGN KEY(mentee_id) REFERENCES co_employees(id) ON DELETE CASCADE ON UPDATE RESTRICT, 
    CONSTRAINT mm_constraint UNIQUE(mentor_id, mentee_id) 
);

# rename a table
RENAME TABLE co_employees TO employees;

# other alterations available
ALTER TABLE table_name
    ADD CONSTRAINT [name of constraint] details_of_constraint,
    DROP INDEX name_of_constraint, # exclude foreign key constraint
    DROP FOREIGN KEY name_of_foreign_key,
    MODIFY COLUMN column_name data_type [constraints] ,
    DROP COLUMN column_name,
    ADD COLUMN column_name data_type [constraints] ;
    
# NOTE: when adding a column, we can specify the position of new column by keyword:
# FIRST - insert new column at the first column
# AFTER column_name - insert new column after column_name

# alter the table
ALTER TABLE employees 
    DROP COLUMN age, 
    ADD COLUMN salary FLOAT NOT NULL AFTER contact_number, 
    ADD COLUMN years_in_company INT NOT NULL AFTER salary
;

# describe the table
DESCRIBE employees;

# drop foreign key constraint
ALTER TABLE mentorships
    DROP FOREIGN KEY fk2;
    
# add new constraint and drop other constraint in one statement
# NOTE: we can't drop and add foreign key with same name in one ALTER statement
ALTER TABLE mentorships 
    ADD CONSTRAINT fk2 FOREIGN KEY(mentee_id) REFERENCES employees(id) ON DELETE CASCADE ON UPDATE CASCADE, 
    DROP INDEX mm_constraint
;

Insert, Update and Delete Data

# insert data into table
INSERT INTO table_name (column1, column2, ...) VALUES
(value1, value2, ...),
...;

# insert data into table with example
# NOTE: column name is optional if we are inserting data for ALL columns
INSERT INTO employees (em_name, gender, contact_number, salary, years_in_company) VALUES 
('James Lee', 'M', '516-514-6568', 3500, 11), 
('Peter Pasternak', 'M', '845-644-7919', 6010, 10), 
('Clara Couto', 'F', '845-641-5236', 3900, 8), 
('Walker Welch', 'M', NULL, 2500, 4)
;

# NOTE: quotation marks are needed for textual information (ex: 'James Lee')

# NOTE: we use word NULL for empty value

# update data in table
UPDATE table_name
    SET column1 = value1, colume2 = value2...
    WHERE condition
;

# update data in table with example
# NOTE: where performing UPDATE statement, always remember the WHERE conditions!
UPDATE employees
    SET contact_number = '123-456-7890'
    WHERE id = 1
;

# delete data in table with example
DELETE FROM employees 
    WHERE id = 5
;

# === testing constraints ====

INSERT INTO mentorships VALUES (4,21,'Ongoing','Flynn Tech');
> Result: Failed # cannot insert a row with invalid mentee_id restricted by foreign key

UPDATE employees SET id = 12 WHERE id = 1;
> Result: Failed # ON UPDATE RESTRICT in fk1 prevents mentor_id in parent table to be updated as there are records of mentor_id in child table

UPDATE employees SET id = 11 WHERE id = 4;
> Result: Success # id 4 is not in mentor_id, it's in mentee_id but the fk2 doesn't prevent parent id update

# === end of testing ===

Select Data

# select all columns in table
SELECT * FROM table_name;

# select specific column with defined column name
# NOTE: we can use backtick `, single quote ', or double quote " to enclose the
# alias that has more than a word
# NOTE: if the Alias name is singled word, nothing is needed to enclose it
SELECT em_name as "Employee Name", gender AS Gender FROM employees;

# limit the number of returned rows in sequence order
SELECT em_name AS "Employee Name" FROM employees LIMIT 3;

# filter the returned rows to remove duplicates
SELECT DISTINCT(gender) FROM employees;

# more filters on returned rows
SELECT * FROM employees WHERE
    (
        id != 1 OR
        id < 10 OR
        id BETWEEN 1 AND 3 OR # 1 and 3 are included, must be used on NUMERIC data type
        id IN (6,8,9) OR
        id NOT IN (4,5) 
    )
    AND
    (
        em_name LIKE '%er' OR # name ends with 'er' are selected
        em_name LIKE '%er%' OR # name contains 'er' are selected
        em_name LIKE '__e%' OR # name has 'e' as third character    
    )
;

# subqueries
SELECT em_name FROM employees WHERE
    id IN(
        SELECT mentor_id FROM mentorships WHERE
        project = 'SQF Limited'
    )
;

# sort by one or more columns 
# NOTE: by default, sorting is ascending (ASC), unless specified otherwise (DESC)
SELECT gender, em_name FROM employees 
    ORDER BY gender, em_name # sort gender first, then name
;

# MySQL built-in functions

# concatenate strings into one
SELECT CONCAT('Hello', ' ' , 'World');
> Hello World

# extract substring out from original string
# NOTE: the last paramater is not last index, but it is length!
SELECT SUBSTRING('Programming', 2, 6);
> rogram # begin at position 2 (index 1), with a length of 6 (not index 5!)

# return current date and time when the function is called
SELECT NOW();
> 2019-02-24 20:10:42

# return current date
SELECT CURDATE();
> 2019-02-24

# return current time
SELECT CURTIME();
> 20:11:33

# Aggregate function - performs calculation on a set of values and return the
# result of the calculation as a single value

# return total number of rows in table
SELECT COUNT(*) FROM employees;

# return number of non NULL values in that column (NULL values are skipped)
SELECT COUNT(contact_number) FROM employees;

# return non-duplicated number of rows in table
SELECT COUNT(DISTINCT gender) FROM employees;

# return the rounded average of a set of values
SELECT ROUND(AVG(salary),2) FROM employees;

# return maximum of a set of values
SELECT MAX(salary) FROM employees;

# return minimum of a set of values
SELECT MIN(salary) FROM employees;

# return the sum of a set of values
SELECT SUM(salary) FROM EMPLOYEES;

# perform calculation on different grouped data separately
SELECT gender, MAX(salary) FROM employees 
    GROUP BY gender;

# filter the results of grouped data
SELECT gender, MAX(salary) FROM employees 
    GROUP BY gender 
    HAVING MAX(salary) > 10000
;


# join data from different tables based on a related column between the tables
SELECT [table_names].column_names_or_other_info
    FROM left_table
    JOIN / INNER JOIN / LEFT JOIN / RIGHT JOIN
    right_table ON
    left_table.column_name = right_table.column_name
;

# inner join table (DEFAULT JOIN)
# NOTE: column B is named as one.B and two.B to avoid ambiguity since both tables 
# have column B.
SELECT A, C, one.B AS 'one B', two.B AS 'two B'
    FROM one INNER JOIN
    two ON A = C
;

# inner join table example
# NOTE: you don't need to select columns that you use for joining
SELECT 
    employees.id, 
    mentorships.mentor_id, 
    employees.em_name AS 'Mentor', 
    mentorships.project AS 'Project Name' FROM 
    mentorships JOIN employees ON 
    employees.id = mentorships.mentor_id
;

# combine the results of two or more SELECT statements
# NOTE: each SELECT statement must have same number of columns
SELECT statement_one UNION SELECT statement_two;

# select with union example:
SELECT em_name, salary FROM employees 
    WHERE gender = 'M' UNION 
    SELECT em_name, years_in_company FROM employees 
    WHERE gender = 'F'
;

# select with union all example
# NOTE: by default, UNION will remove any duplicates from result
SELECT mentor_id FROM mentorships 
    UNION ALL 
    SELECT id FROM employees 
    WHERE gender = 'F'
;

Views

View is virtual table that do not contain data, instead the data is retrieved using SELECT statements.
Advantage:
- Write relatively complex SELECT statements that join multiple tables into one virtual table and access it like regular table.
- Allow us to restrict access to certain data in tables (hide the columns we don't wish to show)

# create a view
SELECT VIEW AS SELECT statement;

# create a view example
CREATE VIEW myView AS 
    SELECT employees.id, 
    mentorships.mentor_id, 
    employees.em_name AS 'Mentor', 
    mentorships.project AS 'Project Name' FROM mentorships 
    JOIN employees ON employees.id = mentorships.mentor_id
;

# view all the data
SELECT * FROM myView;

# view selected columns 
# NOTE: we can only use backtick ` in this case for selecting the column 
# (naming alias for column can use backtick, single quote or double quote)
SELECT mentor_id, `Project Name` FROM myView;

# alter view
ALTER VIEW name_of_view AS SELECT statement;

# alter view example
ALTER VIEW myView AS 
    SELECT employees.id, 
    mentorships.mentor_id, 
    employees.em_name AS 'Mentor', 
    mentorships.project AS 'Project' FROM mentorships 
    JOIN employees ON employees.id = mentorships.mentor_id
;

# delete a view
DROP VIEW IF EXISTS myView;

Triggers

A trigger is a series of actions(CRUD) that is activated when a defined event occurs for a specific table.

# create a new table to hold ex employees
CREATE TABLE ex_employees (
    em_id INT PRIMARY KEY, 
    em_name VARCHAR(255) NOT NULL, 
    gender CHAR(1) NOT NULL, 
    date_left TIMESTAMP DEFAULT NOW() 
);

# create a trigger
DELIMITER $$
CREATE TRIGGER name_of_trigger 
    BEFORE/AFTER UPDATE/DELETE/INSERT ON
    name_of_table FOR EACH ROW
BEGIN
    -- actions to take
END $$
DELIMITER ;

# create a trigger example
CREATE TRIGGER update_ex_employees 
    BEFORE DELETE ON employees FOR EACH ROW 
BEGIN 
    INSERT INTO ex_employees (em_id, em_name, gender) VALUES
        (OLD.id, OLD.em_name, OLD.gender)
    ;
END $$ 
DELIMITER ;

# NOTE:
# DELETE event uses OLD keyword to retrieve deleted values (or data to be deleted)
# INSERT event uses NEW keyword to retrieve inserted values (or data to be inserted)
# UPDATE event uses OLD keyword to retrieve original values, NEW keyword to retrieve the updated values

# delete a trigger
DROP TRIGGER IF EXISTS update_ex_employees;

Variables

Variable is a name given to data that we use in SQL statements.

SET @em_id = 1;
SELECT * FROM mentorships WHERE mentor_id = @em_id;

SET @price = 10;
SET @price = @price +2;

SET @result = SQRT(9);
SELECT @result; # 3

# NOTE: execute SELECT statement and SET statement must use := as assignment
SELECT @result := SQRT(9);

Stored Routines

A stored routine is a set of SQL statements that are grouped, named and stored together in the server.
Two types of stored routines:
- Stored Procedure
- Stored Function

# create procedure is very similar to create trigger
# NOTE: parameters are optional in stored procedures
DELIMITER $$ 
CREATE PROCEDURE name_of_procedure([parameters, if any]) 
BEGIN 
    -- SQL Statements 
END $$ 
DELIMITER ;

# create procedure example
DELIMITER$$
CREATE PROCEDURE select_info()
BEGIN 
    SELECT * FROM employees; 
    SELECT * FROM mentorships; 
END $$ 
DELIMITER ;

# call procedure
CALL select_info();

# create procedure with parameter example
# NOTE: no need prefix @ as the variable here is a parameter
# Three types of parameters: IN (pass info in), OUT(get info out), INOUT(in and out)
CREATE PROCEDURE employee_info(IN p_em_id INT) 
BEGIN 
    SELECT * FROM mentorships WHERE mentor_id = p_em_id; 
    SELECT * FROM mentorships WHERE mentee_id = p_em_id; 
    SELECT * FROM employees WHERE id = p_em_id; 
END $$ 
DELIMITER ;

# pass variable 1 into the procedure
CALL select_info(1);

# pass in for IN, OUT, INOUT parameter
DELIMITER $$ 
CREATE PROCEDURE employee_name_gender(
    IN p_em_id INT, 
    OUT p_name VARCHAR(255), 
    INOUT p_gender CHAR(1)
) 
BEGIN 
    SELECT em_name, gender INTO p_name, p_gender FROM employees 
        WHERE id = p_em_id
    ; 
END $$
DELIMITER ; 

# variable v_name and v_gender are declared and assigned value automatically
@v_gender = 'M';
CALL employee_name_gender(1, @v_name, @v_gender);

# create function
DELIMITER $$ 
CREATE FUNCTION function_name([parameters, if any]) 
    RETURNS data_type characteristics_of_function 
BEGIN 
    -- Details of function 
RETURN result; 
END $$ 
DELIMITER ;

# create function with two parameters example
# NOTE: stored functions can only have IN parameters 
DELIMITER $$ 
CREATE FUNCTION calculateBonus(p_salary DOUBLE, p_multiple DOUBLE) 
    RETURNS DOUBLE DETERMINISTIC  # always return same result given same input parameter
BEGIN 
    DECLARE bonus DOUBLE(8, 2); 
    SET bonus = p_salary*p_multiple; 
    RETURN bonus; 
END 
$$ DELIMITER ;

# Other Characteristics of functions
# NOT DETERMINISTIC - may return different result given same input parameters
# NO SQL - indicates that function does not contain SQL statements
# CONTAINS SQL - indicates that function contains SQL instructions (no R/W)
# READS SQL DATA - indicates that function will only read data from database
# MODIFIES SQL DATA - indicates that function will write data to database

# deterministic and contains SQL statements that modify SQL data
CREATE FUNCTION demo_function(p_demo DOUBLE) 
    RETURNS DOUBLE DETERMINISTIC MODIFIES SQL DATA
    
# NOTE:
# Declaring characteristics of function is based on the "honestly" of creator.
# It still works if declared wrongly, though it may affect results or performance.

# declare local variable inside a stored routine that can be only used within it
DECLARE name_of_variable data_type [DEFAULT default_value];

# declare local variable example
DECLARE bonus DOUBLE(8,2);

# to call function, we use it within SELECT statements
# NOTE: we didn't use @ indicates that salary is a column not a user defined variable
SELECT id, em_name, salary, calculateBonus(salary, 1.5) AS bonus FROM employees;

# delete a stored procedure
DROP PROCEDURE [IF EXISTS] name_of_procedure;

# delete a stored function
DROP FUNCTION [IF EXISTS] name_of_function;

Control Flow

# IF-ELSE statement
# NOTE: if you omit ELSE statement and there exists a case that fulfills ELSE 
# statements, MySQL will give you an error.
IF condition 1 is met THEN do task A; 
    ELSEIF condition 2 is met THEN do task B; 
    ELSEIF condition 3 is met THEN do task C; 
    … 
    ELSE do task Z; 
END IF;

# IF-ELSE statement example
DELIMITER $$ 
CREATE FUNCTION if_demo_A(x INT) 
    RETURNS VARCHAR(255) DETERMINISTIC 
BEGIN 
    IF x > 0 THEN RETURN 'x is positive'; 
    ELSEIF x = 0 THEN RETURN 'x is zero'; 
    ELSE RETURN 'x is negative'; 
    END IF; 
END $$
DELIMITER ;

# run this function
SELECT if_demo_A(2);

# CASE statement 1
CASE case_variable 
    WHEN value_1 THEN do task A; 
    WHEN value_2 THEN do task B; 
    ... 
    ELSE do task Z; 
END CASE; 

# CASE statement 2 (perform more complex matches)
CASE 
    WHEN condition 1 is met THEN do task A; 
    WHEN condition 2 is met THEN do task B; 
    ... 
    ELSE do task Z; 
END CASE;

# CASE statement example:
DELIMITER $$ 
CREATE FUNCTION case_demo_A(x INT) 
    RETURNS VARCHAR(255) DETERMINISTIC 
BEGIN 
    CASE x 
        WHEN 1 THEN RETURN 'x is 1'; 
        WHEN 2 THEN RETURN 'x is 2'; 
        ELSE RETURN 'x is neither 1 nor 2'; 
    END CASE; 
END $$ 
DELIMITER ;

# WHILE statement
[name of while statement : ] WHILE condition is true DO 
    -- some tasks 
END WHILE;

# WHILE statement example:
DELIMITER $$ 
CREATE FUNCTION while_demo(x INT, y INT) 
    RETURNS VARCHAR(255) DETERMINISTIC 
BEGIN 
    DECLARE z VARCHAR(255); 
    SET z = ''; 
    while_example: WHILE x<y DO 
        SET x = x + 1; 
        SET z = concat(z, x); 
    END WHILE; 
    RETURN z; 
END $$ 
DELIMITER ;

# call while function:
SELECT while_demo(1,5); 
> 2345 # concat returns output as string

# REPEAT statement repeats tasks until condition is met (keep execute if not met)
# NOTE: REPEAT statement will always execute once as the check is done after task
[name of repeat statement :] REPEAT 
    -- do some tasks UNTIL stop condition is met 
END REPEAT;

# REPEAT statment example
DELIMITER $$ 
CREATE FUNCTION repeat_demo(x INT, y INT) 
    RETURNS VARCHAR(255) DETERMINISTIC 
BEGIN 
    DECLARE z VARCHAR(255);
    SET z = ''; 
    REPEAT 
        SET x = x + 1; 
        SET z = concat(z, x); 
        UNTIL x>=y 
    END REPEAT; 
    RETURN z; 
END $$ 
DELIMITER ;

# call repeat function:
SELECT repeat_demo(1,5);
> 2345

# LOOP statement similar to WHILE and REPEAT except it doesn't come with a condition 
# to exit the loop, it uses ITERATIVE or LEAVE keywords to exit it.
[name of loop statement :] LOOP    
    -- some tasks 
END LOOP;

# LOOP statement example:
# NOTE: ITERATE skips the current loop, LEAVE exits the loop
DELIMITER $$ 
CREATE FUNCTION loop_demo_A(x INT, y INT) 
    RETURNS VARCHAR(255) DETERMINISTIC 
BEGIN 
    DECLARE z VARCHAR(255); 
    SET z = ''; 
    simple_loop: LOOP  
        SET x = x + 1;        
        IF x = 3 THEN ITERATE simple_loop;
        ELSEIF x > y THEN LEAVE simple_loop;
        END IF; 
        SET z = concat(z, x);       
    END LOOP; 
    RETURN z;   
END $$ 
DELIMITER ;

# call loop function:
SELECT loop_demo_A(1,5);
> 245

Cursor

A cursor is a mechanism that allows us to step through the rows returned by a SQL statement.
A handler defines what the cursor should do when it reaches the last row of results, for example, set variable @v_done = 1

# declare a cursor
DECLARE cursor_name CURSOR FOR
    SELECT statement
    
# declare a handler
DECLARE CONTINUE HANDLER FOR NOT FOUND 
    SET variable_name = value;

# steps to use cursor:
OPEN cursor_name;
FETCH cursor_name INTO variable_names;
...
CLOSE cursor_name;

# use cursor with example:
DELIMITER $$ 
CREATE FUNCTION get_employees () 
    RETURNS VARCHAR(255) DETERMINISTIC 
BEGIN 
    DECLARE v_employees VARCHAR(255) DEFAULT ''; 
    DECLARE v_name VARCHAR(255); 
    DECLARE v_gender CHAR(1); 
    DECLARE v_done INT DEFAULT 0; 
    
    DECLARE cur CURSOR FOR 
        SELECT em_name, gender FROM employees;    
        
    DECLARE CONTINUE HANDLER FOR NOT FOUND 
        SET v_done = 1; 
        
    OPEN cur;
    
    employees_loop: LOOP 
        FETCH cur INTO v_name, v_gender; 
        IF v_done = 1 THEN LEAVE employees_loop; 
        ELSE SET v_employees = concat(v_employees, ', ', v_name, ': ', v_gender); 
        END IF; 
    END LOOP; 
    
    CLOSE cur;       
    
    RETURN substring(v_employees, 1); 
    
END $$ 
DELIMITER ;

I hope you guys enjoy reading this post, perhaps also learn something new from it. That's it for now. I will see you guys soon, adios!

Post was published on 24 Feb 2019, last updated on 26 Jan 2020.

Like the content? Support the author by paypal.me!

Relearn MySQL in few minutes

SQL Intro

Data Types in MySQL

Difference between DATETIME and TIMESTAMP

Column Constraints in MySQL

Insert, Update and Delete Data

Select Data

Views

Triggers

Variables

Stored Routines

Control Flow

Cursor

Relearn PHP in few minutes

Relearn Java in few minutes

💡 Tips: You can press CTRL-G to pull up search dialog.

SQL Intro

Data Types in MySQL

Difference between DATETIME and TIMESTAMP

Column Constraints in MySQL

Insert, Update and Delete Data

Select Data

Views

Triggers

Variables

Stored Routines

Control Flow

Cursor

Subscribe to The Daily Awesome

Subscribe to The Daily Awesome