Structured Query Language (SQL)

The Language you talk with DBMS.

Some International standard:

  • Database definition - Create new DB and tables, modify table definition
    • CREATE TABLE , ALTER TABLE
  • Manipulation - retrieval and modification of rows
    • Select, insert, update, delete
  • Control - integrity and security constraints
    • Grant, revoke

Data Types in SQL

  • boolean : BOOLEAN
  • character : CHAR, VARCHAR
  • bit : BIT, BIT VARYING
  • exact numeric : NUMERIC,DECIMAL, INTEGER, SMALLINT
  • approximate numeric : FLOAT, REAL, DOUBLE PRECISION
  • datetime : DATE, TIME, TIMESTAMP
  • interval : INTERVAL
  • large objects : CHARACTER LARGE OBJECT, BINARY LARGE OBJECT

Creating Databases

When creating a database, it is important to understand the differences between database schemas and database instances.

Database Schemas (Tables)

  • The entire configuration of the database, including all of its tables, relations, index, etc.
  • is specified during database design

Database Instance (Datas of Table)

  • The data in the database at any particular point in time

Creating / Deleting Databases

Syntax:

1
CREATE {DATABASE || SCHEMA} [IF NOT EXISTS] db_name [create_specification]...
1
DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ... [RESTRICT | CASCADE]

InnoDB

InnoDB is a general-purpose storage engine that balances high reliability and high performance. In MySQL 5.7, InnoDB is the default MySQL storage engine. Unless you have configured a different default storage engine, issuing a CREATE TABLE statement without an ENGINE= clause creates an InnoDB table.

1
2
3
-- Default storage engine = InnoDB.
CREATE TABLE t1 (a INT, b CHAR (20), PRIMARY KEY (a)); -- Backward-compatible with older MySQL.
CREATE TABLE t2 (a INT, b CHAR (20), PRIMARY KEY (a)) ENGINE=InnoDB;

Constratints on Foreign Keys

1
2
ON DELETE NO ACTION
ON UPDATE NO ACTION

The reference option can be one of the following:

  1. NO ACTION: Server rejects the delete or update operation for the parent table if there is a related foreign key value in the child table.
  2. RESTRICT: Rejects the delete or update operation for the parent table.
  3. CASCADE: Delete or update the row from the parent table, and automatically delete or update the matching rows in the child table.
  4. SET NULL: Delete or update the row from the parent table, and set the foreign key column or columns in the child table to NULL.

Creating Tables

SELECT Statement: Retrieval

Syntax :

1
2
3
4
5
6
SELECT <list of column expressions>
FROM <list of tables and join operations> 
WHERE <list of logical expressions for rows> 
GROUP BY <list of grouping columns> 
HAVING <list of logical expressions for groups> 
ORDER BY <list of sorting specifications>

Use of DISTINCT

Syntax :

1
SELECT DISTINCT < Database Instance > FROM < Database Schemas >;

Use of Alias

You can give a Alias (Second Name) to the objects.

Syntax :

1
SELECT < Database Instance > AS < alias > FROM < Database Schemas >;

Use of ORDER BY

The ORDER BY keyword is used to sort the result-set in ascending or descending order.

The ORDER BY keyword sorts the records in ascending order by default (ASC) keyword. To sort the records in descending order, use the DESC keyword.

Syntax :

1
2
SELECT < Database Instance > FROM < Database Schemas >
ORDER BY < Database Instance > DESC;

Note < Database Instance > can be multiple.

Data Manipulation Statements

  • INSERT
  • UPDATE
  • DELETE

Use of INSERT

The INSERT keyword is used to adds one or more rows.

Syntax :

1
2
3
4
INSERT INTO < Database Schemas >
( < A Collection of Database Instance > )
VALUES
( < Value(s) of those Database Instance > );

Use of UPDATE

The UPDATE keyword is used to modify the existing records in a table.

use SET to give the data a new value.

It can be used with a WHERE clause

Syntax :

1
2
3
UPDATE < Database Schemas >
SET < Database Instances = 'new value' >
WHERE < condition >;

Use of DELETE

The DELETE keyword is used to remove one or more rows.

It can be used with a WHERE clause

Delete specific datas

Syntax :

1
2
DELETE FROM < Database Schemas >
WHERE < Database Instances = '' >;

Delete all rows in a table

Syntax :

1
DELETE FROM < Database Schemas >;

Advanced SQL

Joining Tables

Use of JOIN

Look at here

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Example:

List the order number, the order date, the customer number, the customer name, the customer’s state, and the shipping state in which the customer’s state differs from the shipping’s state.

1
2
3
4
5
SELECT OrderNo, OrderDate, CustNo, `Order`.CustName, Customer.State AS 'Customer State', `Order`.State AS 'Shipping State' 
FROM Customer
INNER JOIN `Order` 
ON Customer.CustNo = `Order`.Customer_CustNo
WHERE Customer.State <> Order.State;

Use of Cross Join

Example:

List the order number, the order date, the customer number, the customer name, the customer’s state, and the shipping state in which the customer’s state differs from the shipping’s state.

1
2
3
4
SELECT OrderNo, OrderDate, CustNo, `Order`.CustName, Customer.State AS 'Customer State', `Order`.State AS 'Shipping State' 
FROM Customer,`Order`
WHERE Customer.CustNo = `Order`.Customer_CustNo
AND Customer.State <> Order.State;

Use of Multiple Join

Example:

It is also ok to do something like this:

1
2
3
4
5
6
7
8
9
10
SELECT CustNo, FirstName, LastName,
COUNT(DISTINCT `Order`.OrderNo) AS 'No. of Orders'
FROM Customer
INNER JOIN `Order` ON Customer.CustNo = `Order`.Customer_CustNo
INNER JOIN ProductInOrder ON `Order`.OrderNo = ProductInOrder.Order_OrderNo
INNER JOIN Product ON Product.ProdNo = ProductInOrder.Product_ProdNo
WHERE (Customer.State = 'CO' AND Product.Mfg = 'Connex')
	AND ((STR_TO_DATE(`Order`.OrderDate,'%m/%d/%Y') >= '2007-01-01') 
		AND (STR_TO_DATE(`Order`.OrderDate,'%m/%d/%Y') <= '2007-01-31'))
GROUP BY CustNo;

OR something like this:

1
2
3
4
5
6
7
8
9
10
SELECT CustNo, FirstName, LastName,
COUNT(DISTINCT `Order`.OrderNo) AS 'No. of Orders'
FROM Customer, `Order`, ProductInOrder, Product
WHERE Customer.CustNo = `Order`.Customer_CustNo
AND `Order`.OrderNo = ProductInOrder.Order_OrderNo
AND Product.ProdNo = ProductInOrder.Product_ProdNo
AND (Customer.State = 'CO' AND Product.Mfg = 'Connex')
	AND ((STR_TO_DATE(`Order`.OrderDate,'%m/%d/%Y') >= '2007-01-01') 
		AND (STR_TO_DATE(`Order`.OrderDate,'%m/%d/%Y') <= '2007-01-31'))
GROUP BY CustNo;

Different Types of SQL JOINs

(INNER) JOIN: Returns records that have matching values in both tables

LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table

RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table

FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

In mysql, We use LEFT JOIN UNION RIGHT JOIN to get FULL JOIN.

E.g.

1
2
3
4
5
6
7
SELECT * FROM `Customer`
LEFT JOIN `Employee` ON (Employee.FirstName = Customer.FirstName
						AND Employee.LastName = Customer.LastName)
UNION 
SELECT * FROM `Customer`
RIGHT JOIN `Employee` ON (Employee.FirstName = Customer.FirstName
						AND Employee.LastName = Customer.LastName);

Nested Queries

Syntax:

1
2
3
4
5
6
7
8
9
10
11
SELECT <Database Instance>
FROM 
		< Database Schemas >
WHERE < Database Instance > IN (
  SELECT
  		< Database Schemas >.< Database Instance >
  FROM
  		< Database Schemas >
  WHERE
  		< Condition >
);

Example:

1
2
3
4
5
6
SELECT CustNo, FirstName, LastName
FROM `Customer`
WHERE Customer.CustNo IN (
  	SELECT Order.Customer_CustNo
  	FROM `Order`
  	WHERE STR_TO_DATE(OrderDate, '%m/%d/%Y') > '2007-1-31'

Nested Queries for custom table

You can also use nested queries for FROM.

1
2
3
4
SELECT MAX(Total) 
FROM (
  SELECT COUNT(*) AS Total 
  FROM emp1 GROUP BY name) AS Results

Example:

1
2
3
4
5
SELECT SUM(CPD)/COUNT(orderNumber)
FROM (
  SELECT COUNT(DISTINCT productCode) AS 'CPD'
  FROM orderdetails
  GROUP BY orderNumber) AS `temp`, orderdetails;

Summarizing Tables

Use of COUNT

COUNT() function returns the number of rows that matches a specified criteria.

Syntax :

1
2
3
SELECT COUNT( < Database Instance > )
FROM < Database Schemas >
WHERE < Condition >;

Use of GROUP BY

indicate columns to summarize on.

The GROUP BY statement groups rows that have the same values into summary rows, like “find the number of customers in each country”.

The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.

1
2
3
4
5
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

Use of HAVING

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

Syntax :

1
2
3
4
5
6
SELECT < Database Instance >
FROM < Database Schemas >
WHERE < Condition >
GROUP BY < Database Instance >
HAVING < Condition >
ORDER BY < Database Instance >

Difference between WHERE and HAVING

More Functions

Use of BETWEEN

Syntax/Example:

1
2
3
SELECT *
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Use of CONCAT

Add serval strings together.

Syntax/Example:

1
SELECT CONCAT(firstName," ", lastName) AS 'Full Name'; -- Result: firstName lastName

Use of LENGTH

Syntax/Example:

1
SELECT LENGTH("SQL") AS LengthOfString;  -- Result: 3

Use of MAX and MIN

Syntax/Example:

1
2
3
SELECT MAX(Price) AS LargestPrice FROM Products;

SELECT MIN(Price) AS SmallestPrice FROM Products;

Use of SUBSTRING

Syntax/Example:

1
2
SELECT (SUBSTRING(Price, 2) AS 'Price without $' FROM Products;
-- Extract '$' from VARCHAR "Price"

Use of STR_TO_DATE

Syntax/Example:

1
2
3
SELECT * FROM `Order`
WHERE STR_TO_DATE(OrderDate,'%m/%d/%Y') > '2007-1-31'

Use of LIMIT

Syntax/Example:

Find top 5 creditLimit

1
2
3
SELECT creditLimit FROM customers
ORDER BY creditLimit DSEC
LIMIT 5

LIMIT IS VERY USEFUL IN MANY SITUATION.

Use of DATEDIFF

Syntax/Example:

1
SELECT DATEDIFF(largedate1, smallerdate2)

Use of MONTH and DAY and Year

Syntax/Example:

1
2
3
SELECT MONTH("2017-06-15"); -- Return the month part of a date
SELECT DAY("2017-06-15"); -- Return the day of the month for a date
SELECT YEAR("2017-06-15"); -- Return the year part of a date

View

In SQL, a view is a virtual table based on the result-set of an SQL statement.

A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.

1
2
3
4
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example

1
2
3
4
5
6
7
8
9
10
11
CREATE VIEW `Q1a_View` AS
	SELECT
		`Order` . *, PIO.Qty, P.ProdName
	FROM
		`Order`,
		ProductInOrder AS PIO
		Product AS P
	WHERE
		`Order`.OrderNo = PIO.Order_OrderNo
				AND PIO.Product_ProdNo = P.ProdNo
				AND STR_TO_DATE(OrderDate, '%m/%d/%Y') BETWEEN '2007-01-01' AND '2007-01-31';

More Advanced SQL

Programming in MySQL

Add procedural programming constructs to SQL.

  • SQL/PSM (Persistent Stored Modules)
    • Extension of SQL
  • PL/SQL (Procedural Language/SQL)
    • Oracle’s procedural extension to SQL

Defining Stored Procedure

Read Here

PROCEDURE

Each stored program contains a body that consists of an SQL statement. This statement may be a compound statement made up of several statements separated by semicolon (;) characters. For example, the following stored procedure has a body made up of a BEGIN ... END block that contains a SET statement and a REPEAT loop that itself contains another SET statement:

1
2
3
4
5
CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
  SET @x = 0;
  REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
END;

If you use the mysql client program to define a stored program containing semicolon characters, a problem arises. By default, mysql itself recognizes the semicolon as a statement delimiter, so you must redefine the delimiter temporarily to cause mysql to pass the entire stored program definition to the server.

DELIMITER

To redefine the mysql delimiter, use the delimiter command.

In mysql, ; denote the end of sql statement.

Now with delimiter, you can change the end of sql statement to other things else.

In this example, we are going to change the the end of sql statement to //.

After we finish writing the PROCEDURE,

We need to change back the delimiter to ; to avoid confusion.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysql> delimiter //

mysql> CREATE PROCEDURE dorepeat(p1 INT)
    -> BEGIN
    ->   SET @x = 0;
    ->   REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> CALL dorepeat(1000);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x;
+------+
| @x   |
+------+
| 1001 |
+------+
1 row in set (0.00 sec)

You can redefine the delimiter to a string other than //, and the delimiter can consist of a single character or multiple characters. You should avoid the use of the backslash (\) character because that is the escape character for MySQL.

The following is an example of a function that takes a parameter, performs an operation using an SQL function, and returns the result. In this case, it is unnecessary to use delimiter because the function definition contains no internal ; statement delimiters:

1
2
3
4
5
6
7
8
9
10
11
12
mysql> CREATE FUNCTION hello (s CHAR(20))
mysql> RETURNS CHAR(50) DETERMINISTIC
    -> RETURN CONCAT('Hello, ',s,'!');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world!  |
+----------------+
1 row in set (0.00 sec)

Declaration of Variables

Variables and constant variables must be declared before they can be referenced.

Syntax :

1
DECLARE variable_name datatype(size) DEFAULT default_value;

Example :

1
2
3
4
5
6
7
8
9
10
DELIMITER $$
DROP PROCEDURE IF EXISTS compute_sale $$ -- We need to remake one
CREATE PROCEDURE compute_sale()
BEGIN
        DECLARE total_sale DECIMAL(10,2) DEFAULT 0.0;
        -- DECIMAL(10,2) where 10 is the maximum number of digits, 2 decimal point
        DECLARE i INT DEFAULT 0;
END $$

DELIMITER ;
1
2
-- To use the procedure (Call the function)
CALL compute_sale();

Assign Values to Variables

Variables can be assigned in two ways:

  • Using the SET statement
  • Using an SQL SELECT or FETCH statement

Example Using the SET statement :

1
2
3
4
5
6
7
8
9
DELIMITER $$
DROP PROCEDURE IF EXISTS compute_sale $$
CREATE PROCEDURE compute_sale()
BEGIN
        DECLARE x,y INT DEFAULT 0;    /* Declare variabbles */
        SET x = x + 1;                            /* Assign procedure variable */
        SET @y = @y + 1;                        /* Assign session variable */
END $$
DELIMITER ;

What is the difference between procedure variable (y) and session variable (@y)?

  • session variable (@y) does not require declaration, can be used in any SQL query or statement, not visible to other sessions, and exists until the end of the current session.
  • procedure variable (y) is reinitialized to NULL each time the procedure is called, while the session-specific variable (@y) is not.
  • procedure variable (y) would be locally scoped variable (in a stored procedure)
  • while your connection with the database exists, session variable (@y) can still be used.

Example Using the SELECT or FETCH statement

In this case, We use INTO to store the selected value.

1
2
3
4
5
6
7
8
9
10
11
DROP PROCEDURE IF EXISTS compute_sale $$
CREATE PROCEDURE compute_sale()
BEGIN
        DECLARE x,y INT DEFAULT 0;    /* Declare variabbles */
        SET x = x + 1;                            /* Assign procedure variable */
        SET @y = @y + 1;                        /* Assign session variable */
        SELECT COUNT(*) INTO x            /* Assign no. of records */
                FROM `Customer`;                /* in `Customer` to x */
        SELECT x;                                        /* Display the value of x */
END $$
DELIMITER ;

Control Statements

Conditional IF statement

Click Here

The IF() function returns a value if a condition is TRUE, or another value if a condition is FALSE.

Example

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DELIMITER $$
DROP PROCEDURE IF EXISTS compute_sale $$
CREATE PROCEDURE compute_sale()
BEGIN
		DECLARE numOrders INT;
		SELECT COUNT(*) INTO numOrders FROM `Order`;
		IF(numOrders > 10) THEN
				SELECT 'Good Job' AS 'Comment';
		ELSE
				SELECT 'Need Improvement' AS 'Comment';
		END IF;
END $$
DELIMITER;
CALL compute_sale();

Conditional CASE statement

Click Here

The CASE statement goes through conditions and return a value when the first condition is met (like an IF-THEN-ELSE statement). So, once a condition is true, it will stop reading and return the result.

If no conditions are true, it will return the value in the ELSE clause.

If there is no ELSE part and no conditions are true, it returns NULL.

Syntax

1
2
3
4
5
6
CASE case_expression
		WHEN when_expression_1 THEN commands
		WHEN when_expression_2 THEN commands
		...
		ELSE commands
END CASE;

Example

Double/half the price if the total stock is larger/smaller than 500

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DELIMITER $$
DROP PROCEDURE IF EXISTS update_price $$
CREATE PROCEDURE update_price(IN stockThreshold INT)
BEGIN
		DECLARE totalStock INT;
		SELECT SUM(Product.Stock) INTO totalStock FROM Product;
		CASE
				WHEN totalStock > stockThreshold THEN
						UPDATE Product SET Price = CONCAT('$',(SUBSTRING(Price,2)*2));
				WHEN totalStock <= stockThreshold THEN
						UPDATE Product SET Price = CONCAT('$',(SUBSTRING(Price,2)*0.5));
		END CASE;
END $$
DELIMITER;

CALL update_price(500);

Return the number of days required for shipping out an order based on the recipient’s city

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
DELIMITER $$
DROP PROCEDURE IF EXISTS shipping_day
CREATE PROCEDURE shipping_day(IN p_orderNo CHAR(8), -- input parameter
                              OUT p_shipDay INT) -- output parameter
                              
BEGIN
		DECLARE city VARCHAR(50);
		SELECT `Order`.City INTO city FROM `Order`
		WHERE `Order`.OrderNo = p_orderNo;
		CASE city
				WHEN 'Hong Kong' THEN
						SET p_shipDay = 3;
				WHEN 'Denver' THEN
						SET p_shipDay = 2;
				ELSE
						SET p_shipDay = 1;
		END CASE;
END $$

DELIMITER ;

CALL shipping_day('01111111', @num_days); 	-- the variable will be updated
SELECT @num_days AS 'No. of Shipping Days';

Iteration statement (WHILE)

Syntax :

1
2
3
WHILE expression DO
    Statements
END WHILE

Example

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DELIMITER $$
DROP PROCEDURE IF EXISTS while_loop $$
CREATE PROCEDURE while_loop()
BEGIN
		DECLARE x INT;
		DECLARE str VARCHAR(255);
		SET x = 1;
		SET str = '';
		WHILE x <= 5 DO
				SET str = CONCAT(str, x, ',');
				SET x = x + 1;
		END WHILE;
		SELECT str;
END $$
DELIMITER;
CALL while_loop();

Iteration statement (REPEAT)

It is basically DO…WHILE Loop.

The statements will run at least once.

Syntax :

1
2
3
4
REPEAT
Statements;
UNTIL expression
END REPEAT

Example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
DELIMITER $$
DROP PROCEDURE IF EXISTS while_loop $$
CREATE PROCEDURE repeat_loop()
BEGIN
		DECLARE x INT;
		DECLARE str VARCHAR(255);
		SET x = 1;
		SET str = '';
		REPEAT
				SET str = CONCAT(str, x, ',');
				SET x = x + 1;
		UNTIL x > 5
		END REPEAT;
		SELECT str;
END$$
DELIMITER;
CALL repeat_loop();

Iteration statement (LOOP)

Example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
BEGIN
			DECLARE x INT;
			DECLARE str VARCHAR(255);
			SET x = 1;
			SET str = '';
			loop_label: LOOP
								 IF x > 10 THEN
								 			LEAVE loop_label; -- LEAVE = break in C++/Java
								 END IF;
								 SET x = x + 1;
								 IF (x mod 2) THEN
								 			ITERATE loop_label; -- ITERATE = continue in C++/Java
								 ELSE
								 		SET str = CONCAT(str, x, ',');
								 END IF;
			END LOOP;
			SELECT str;
END$$

Exception Handling in MySQL

Good for handling error caused by SQL operations

Syntax :

1
2
3
DECLARE {CONTINUE | EXIT} HANDLER FOR    
        {SQLSTATE sqlstate_code| MySQL error code| condition_name}
        handler_actions

Examples :

1
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET has_error = 1;

If an error occurs, set the value of the variable has_error to 1 and continue the execution.

1
2
DECLARE CONTINUE HANDLER FOR 1062
SELECT 'Error, duplicate key occurred';

Display an error message when ERROR 1062 occurs

Example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
DELIMITER $$
DROP PROCEDURE IF EXISTS add_product $$
CREATE PROCEDURE add_product(IN p_prodNo CHAR(8),
                             IN p_prodName CHAR(45))
BEGIN
		DECLARE duplicate_key INT DEFAULT 0;
		BEGIN
				DECLARE EXIT HANDLER FOR 1062
						SET duplicate_key = 1;	-- Execute when error 1062 occurs
				INSERT INTO Product (ProdNo, ProdName) 
						VALUES(p_prodNo, p_prodName);	-- error 1062 occured -> exit
				SELECT CONCAT('Product ', p_prodNo, ' created') -- wont execute if error occur
						AS 'Result';
		END;
		IF duplicate_key = 1 THEN
				SELECT CONCAT('Failed to insert ', p_prodNo, ': duplicated key')
						AS 'Result';
		END IF;
END $$

DELIMITER
CALL add_product('P0036566', 'My TV') -- Existed value

CALL add_product('P1234567', 'My TV') -- new value, will not cause error 1062

Cursors in MySQL

Useful for accessing result sets one row at a time.

What is Cursor ? (Like a Pointer)

  • Allows the rows of a query result to be accessed one at a time
  • Must be declared and opened before use
  • Must be closed to deactivate it after it is no longer required
  • Read-only - you cannot update data in the underlying table through the cursor.
  • Non-scrollable - you can only fetch rows in the order determined by the SELECT statement. You cannot fetch rows in the reversed order. In addition, you cannot skip rows or jump to a specific row in the result set.

Working with MySQL Cursors

Step 1: Declare a cursor (after any variable declaration):

1
DECLARE cursor_name CURSOR FOR SELECT_statement;

Step 2: Open a cursor

1
OPEN cursor_name;

Step 3: Use the FETCH statement to retrieve the next row pointed to by the cursor and move the cursor to the next row in the result set

1
FETCH cursor_name INTO variables list;

Step 4: call the CLOSE statement to deactivate the cursor and release the memory associated with it

1
CLOSE cursor_name;

Example:

To change the price of product depending on stock

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
DELIMITER $$
DROP PROCEDURE IF EXISTS change_price $$
CREATE PROCEDURE change_price(IN stockThreshold INT)
BEGIN
		DECLARE v_finished INTEGER DEFAULT 0;
		DECLARE v_prodNo CHAR(8);
		DECLARE v_stock INT DEFAULT 0;
		
		-- Declare CURSOR
		DECLARE product_cursor CURSOR FOR SELECT ProdNo, Stock FROM Product;
		DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;
		
		OPEN product_cursor -- Open CURSOR
		get_product: LOOP
				-- Fetch CURSOR
				FETCH product_cursor INTO v_prodNo, v_stock; -- map to ProdNo, Stock
				IF v_finished = 1 THEN
						LEAVE get_product; -- Break the loop
				END IF;
				CASE
						WHEN v_stock > stockThreshold THEN
								UPDATE Product SET Price = '$200' WHERE ProdNo = v_prodNo;
						WHEN v_stock <= stockThreshold THEN
								UPDATE Product SET Price = '$100' WHERE ProdNo = v_prodNo;
				END CASE;
		END LOOP get_product;
		
		CLOSE product_cursor; -- Close CURSOR
END $$
DELIMITER ;

CALL change_price(10);
SELECT ProdNo, Stock, Price FROM Product;

Triggers

Very useful for checking data and for ensure data integrity.

Defines an action that the database should take when some events occur in the application.

  • Triggers can be used for checking values to be inserted into a table or for calculating values involved in an update.
  • A trigger is defined to activate when a statement inserts, updates, or deletes rows in the associated table. These row operations are called “trigger events”.
  • Database trigger is a powerful tool for protecting the integrity of data

Working with Triggers

1
2
3
4
5
6
7
8
9
10
CREATE
	[DEFINER = { user | CURRENT_USER }]
	TRIGGER trigger_name
	trigger_name trigger event
	ON tbl_name FOR EACH ROW
	trigger_body

trigger_time: { BEFORE | AFTER }

trigger event: { INSERT | UPDATE | DELETE }
  • BEFORE INSERT - activated before data is inserted into the table.
  • AFTER INSERT - activated after data is inserted into the table.
  • BEFORE UPDATE - activated before data in the table is updated.
  • AFTER UPDATE - activated after data in the table is updated.
  • BEFORE DELETE - activated before data is removed from the table.
  • AFTER DELETE - activated after data is removed from the table.

Syntax

1
2
3
4
5
6
CREATE TRIGGER trigger_name trigger_time trigger_event
ON table_name
FOR EACH ROW
BEGIN
...
END

OLD keyword refers to the existing record before you change the data

NEW keyword refers to the new row after you change the data.

Example:

Create a table to store the old value on other table before update.

1
2
3
4
5
6
7
8
CREATE TABLE employees_audit (
  id INT(11) NOT NULL AUTO_INCREMENT,
  employeeNumber INT(11) NOT NULL,
  lastname VARCHAR(50) NOT NULL,
  changedon DATETIME DEFAULT NULL,
  action VARCHAR(50) DEFAULT NULL,
  PRIMARY KEY (id)
)

Create a BEFORE UPDATE trigger. It is triggered when a change made to employees table.

1
2
3
4
5
6
7
8
9
10
11
12
DELIMITER $$
CREATE TRIGGER before_employee_update
		BEFORE UPDATE ON employees
		FOR EACH ROW BEGIN
		
		INSERT INTO employees_audit
		SET action = 'update',
				employeeNumber = OLD.employeeNumber,
				lastname = OLD.lastname,
				changedon = NOW();
		END$$
DELIMITER ;

NOW when you do a update, the old record is putted into employees_audit.