More SQL

by K. Yue

1. More SQL Features

1.1 Prepared Statements

• MySQL supports server-side prepared statements.
• A prepared statement is used in the following sequence.
  1. The statement is prepared.
  2. The prepared statements are executed one to many times, including using different values on placeholders.
  3. The statement is deallocated.
• Prepared Statements have the following benefits:
  1. They are compiled once and can be executed many times.
  2. With the use of placeholders, they protect against SQL injections.
  3. They can be used to execute dynamic SQL statements.
     1. However, dynamic SQL statements can be serious security holes and should be used carefully.
• In MySQL, prepared statements can be used in
  1. Applications: such as SQL clients (e.g. MySQL prompt) or Python programs.
  2. SQL scripts: such as invoked by the source command.
• There are vendor-dependent restrictions in prepared statements.
• For examples, in MySQL,
  1. Constants or user variables should be used as the parameter values to the placeholders of the prepared statements.
  2. user variables should be used in the INTO clause of the SQL query in the prepared SELECT statement.

Example:

Try the following code in MySQL prompt.

-- Prepared statements.
SET @sql = "SELECT * FROM toyu.student";
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- with placeholders.
SET @sql = "SELECT * FROM toyu.student WHERE major = ? AND ach >= ?";
PREPARE stmt FROM @sql;
SET @major = 'CSCI';
SET @ach = 38;
EXECUTE stmt USING @major, @ach;

EXECUTE stmt USING 'CSCI', 38;

SET @major = 'CINF';
SET @ach = 15;
EXECUTE stmt USING @major, @ach;

SET @major = 'ITEC';
SET @ach = 25;
EXECUTE stmt USING @major, @ach;

DEALLOCATE PREPARE stmt;

2. Views

• Views are virtual tables derived from other tables.
• In MySQL, "views are stored queries that when invoked produce a result set. A view acts as a virtual table." See: https://dev.mysql.com/doc/refman/8.2/en/views.html
• Some advantages of using views:
  1. Better data abstraction: hiding unnecessary information.
  2. Logical data independence
  3. Better consistency
  4. More security control
  5. Possibly more efficient
• Some disadvantages:
  1. More work
  2. Complicated especially when views are updated.
  3. Performance: view processing may not be optimized, e.g.,
  • A view can be treated as a subquery by the db engine.
  • Cannot create index for view in MySQL.
• See the three layered DB architecture.
• MySQL Create View Manual: search for "mysql view manual"

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

• There are some limitations in views. For example:
  • In earlier versions of MySQL, subqueries are not allowed in the SELECT clause in the CREATE VIEW statement.
• You may need to define intermediate views accordingly.

Example:

Execute the following code and ensure that you understand the result.

CREATE OR REPLACE VIEW school_summary(
   schoolCode, schoolName, n_departments) AS
SELECT s.schoolCode, s.schoolName,
COUNT(d.deptCode) AS n_departments
FROM school AS s LEFT JOIN department AS d ON (s.schoolCode = d.schoolCode)
GROUP BY s.schoolCode, s.schoolName;

SHOW CREATE VIEW school_summary;
-- Note something like "ALGORITHM=UNDEFINED DEFINER=`yue`@`localhost` SQL SECURITY DEFINER"
-- (default values) may be added.

DESC school_summary;

SELECT *
FROM school_summary
WHERE n_departments > 0;

DROP VIEW school_summary;

Notes:

• "CREATE OR REPLACE" can replace existing view definition.
• There are no data types explicitly specified for the columns in the example.
• The column list is optional.
• MySQL supports specification of algorithm for processing views:
  • MERGE: view definition merged into the containing query (inlining)
  • TEMPTABLE: view results stored in temporary table to be used in the containing query.
• MySQL supports updatable views. Use them carefully. E.g., the view 'user' in 'mysql' is updatable.
  • In general, "for a view to be updatable, there must be a one-to-one relationship between the rows in the view and the rows in the underlying table."
    

3. Stored Subroutines

• Subroutines stored by the DBMS can be called.
• Subroutines can be
  1. functions: return a value, or
  2. procedures:
     1. do not return a value, and
     2. work by side effects.
• Known as SQL/PSM (SQL/Persistent Stored Modules) in MySQL.
  1. Can execute SQL statements.
  2. Include general programming constructs.
• Some advantages of stored subroutines:
  1. Enforcing application constraints and requirements.
  2. Providing consistency and security control.
  3. Possible performance optimization by both the developers and the DBMS.
  4. Sharing among DB applications, especially when they use different languages.
• Some disadvantages of stored procedures:
  1. Use up DB server's resources.
  2. Potentially inefficient because of limitations in language constructs.
  3. Potentially harder to develop because of relative lack of libraries and difficulty in debugging.
  4. Can be vendor specific.
• In general, stored subroutines should be used more often.
• Some general points:
  • A stored subroutine is associated with a particular database. 
  • Stored functions cannot be recursive.

3.1 Stored Procedures

Example:

Execute the following code and ensure that you understand the result.

-- A very simple stored procedure.
-- Redefine the delimiter to end the procedure.
DELIMITER //

CREATE OR REPLACE PROCEDURE deptInfo(IN dCode VARCHAR(4), OUT numFaculty INT)
BEGIN
   --  Display some information.
   SELECT d.deptName, d.SchoolCode, t1.n_majors, t2.n_minors
   FROM department AS d INNER JOIN
      (SELECT COUNT(stuId) AS n_majors
      FROM student
      WHERE major = dCode) AS t1 INNER JOIN
      (SELECT COUNT(stuId) AS n_minors
      FROM student
      WHERE minor = dCode) AS t2
   WHERE d.deptCode = dCode;

   -- MySQL does not direct sending output to console.
   -- It is necessary to use a SQL statement.
   SELECT 'Debuggin comment can be put here.';
   SELECT CONCAT('Faculty in the department: ', dCode) AS faculty;

   SELECT *
   FROM faculty AS f
   WHERE f.deptCode = dCode;

   SELECT COUNT(f.facId) INTO numFaculty
   FROM faculty AS f
   WHERE f.deptCode = dCode;
END //

DELIMITER ;

SHOW CREATE PROCEDURE deptInfo;

SET @numFaculty = 0;
SET @dCode = 'CSCI';
CALL deptInfo(@dCode, @numFaculty);
SELECT @dCode, @numFaculty;

SET @dCode = 'ITEC';
CALL deptInfo(@dCode, @numFaculty);
SELECT @dCode, @numFaculty;

DROP PROCEDURE deptInfo;

Note:

1. Use of the DELIMITER command to redefine '//' as the delimiter indicating the end of the stored procedure. Otherwise, the default ';' is the delimiter indicating the end of the stored procedure.
2. Two parameters for this procedure: one using IN and one using OUT as the parameter passing mechanism.
3. A procedure does not return any value and accomplish its goal through side effects.
4. Side effects include:
   1. Return SELECT results in the procedure body.
   2. Copy numFaculty out upon completion.

Example: using toyu

DELIMITER //
CREATE OR REPLACE PROCEDURE AddNewCourse(
    IN course_id INT,
    IN rubric CHAR(4),
    IN course_number CHAR(4),
    IN course_title VARCHAR(80),
    IN credits TINYINT
)
BEGIN
    INSERT INTO Course (courseId, rubric, number, title, credits)
    VALUES (course_id, rubric, course_number, course_title, credits);
END //
DELIMITER ;

CALL AddNewCourse(3009, 'CSCI', '4436', 'Systems Administration', 3);

3.2 Stored Functions

Example:

Execute the following code and ensure that you understand the result.


-- A simple function
DELIMITER //

CREATE OR REPLACE FUNCTION n_major(dCode varchar(4)) RETURNS INT
READS SQL DATA
BEGIN
   DECLARE count INT DEFAULT 0;

   SELECT COUNT(*) INTO count
   FROM student
   WHERE major = dCode;

   RETURN count;
END //

DELIMITER ;

SHOW CREATE FUNCTION n_major;

SELECT n_major('CSCI');
SELECT n_major('ITEC');

Note:

• A function returns a value and does not accomplish its goal through side effects.
• Note the argument passed into the function.
• Formal arguments of a function use the IN parameter passing mode.
• Note the variable declaration and assignment.
• The scope of the variable is within the function.
• 'READS SQL DATA' describes the characteristic of the subroutine: it reads but not writes SQL data.

Example:

The following example functions are included in the script of creating toyu. Function calls are added.

-- get the full name of a student.
DELIMITER //
CREATE OR REPLACE FUNCTION GetStudentFullName(
    student_id INT
)
RETURNS VARCHAR(61)
DETERMINISTIC
BEGIN
    DECLARE full_name VARCHAR(61);
    SELECT CONCAT(fname, ' ', lname) INTO full_name
    FROM Student
    WHERE stuId = student_id;
    RETURN full_name;
END //
DELIMITER ;

SELECT GetStudentFullName(100000);
SELECT GetStudentFullName(100001);

-- get the full name of a department code
DELIMITER //
CREATE OR REPLACE FUNCTION GetDepartmentName(
    dept_code CHAR(4)
)
RETURNS VARCHAR(30)
DETERMINISTIC
BEGIN
    DECLARE dept_name VARCHAR(30);
    SELECT d.deptName INTO dept_name
    FROM Department d
    WHERE d.deptCode = dept_code;
    RETURN dept_name;
END //
DELIMITER ;

SELECT GetDepartmentName('CSCI');
SELECT GetDepartmentName('ITEC');

-- Compute and return the GPA of a student
DELIMITER //
CREATE OR REPLACE FUNCTION GetStudentGPA(
    student_id INT
)
RETURNS DECIMAL(3,2)
DETERMINISTIC
BEGIN
    DECLARE gpa DECIMAL(3,2);
    SELECT ROUND(SUM(g.gradePoint * co.credits) / SUM(co.credits), 2) INTO gpa
    FROM Enroll e
    JOIN Class c ON e.classId = c.classId
    JOIN Course co ON c.courseId = co.courseId
    JOIN Grade g ON e.grade = g.grade
    WHERE e.stuId = student_id;
    RETURN gpa;
END //
DELIMITER ;

SELECT GetStudentGPA(100000);
SELECT GetStudentGPA(100001);
SELECT GetStudentGPA(100002);

-- Return the top n students in a major department with GPA.
DELIMITER //
CREATE OR REPLACE FUNCTION GetTopStudentsInDepartment(
    dept_code CHAR(4),
    top_count INT
)
RETURNS VARCHAR(1000)
DETERMINISTIC
BEGIN
    DECLARE student_list VARCHAR(1000);
    SET student_list = '';
   
    WITH temp AS(
        SELECT s.stuId, CONCAT(s.fname, ' ', s.lname) AS student,
            ROUND(SUM(g.gradePoint * co.credits) / SUM(co.credits), 2) AS gpa
        FROM Student s
        JOIN Department d ON s.major = d.deptCode
        JOIN Enroll e ON s.stuId = e.stuId
        JOIN Class c ON e.classId = c.classId
        JOIN Course co ON c.courseId = co.courseId
        JOIN Grade g ON e.grade = g.grade
        WHERE d.deptCode = dept_code
        GROUP BY s.stuId, student
        ORDER BY gpa DESC
        LIMIT top_count)
    SELECT GROUP_CONCAT(CONCAT(temp.student, '(', temp.stuId, '):', temp.gpa) SEPARATOR ', ')
        INTO student_list
    FROM temp
    LIMIT 1;

    RETURN student_list;
END //
DELIMITER ;

SELECT GetTopStudentsInDepartment('CSCI', 2);
SELECT GetTopStudentsInDepartment('CSCI', 3);

3.3 Cursors

• In stored subroutines in MySQL, various high level language constructs are supported, such as variable declarations, conditional statements, control statements, etc.
• In particular, cursors are supported for allowing iteration through the result sets.

Example:

Execute the following code and ensure that you understand the result. The example is artifically constructed as there are better ways.

-- using cursor.
DELIMITER //

CREATE FUNCTION major_students(dept VARCHAR(4))
RETURNS VARCHAR(1000)
READS SQL DATA
BEGIN
DECLARE result VARCHAR(1000) DEFAULT '';
DECLARE name VARCHAR(41) DEFAULT '';
DECLARE done INT DEFAULT FALSE;

DECLARE cursor_1 CURSOR FOR
SELECT DISTINCT CONCAT(fName, ' ', lName) AS name
FROM student
WHERE major = dept;

DECLARE continue handler FOR NOT FOUND SET done = TRUE;

OPEN cursor_1;
SET result = ''; 

compute_loop: LOOP
-- This is needed as there are other SQL statements that may set done to true.
SET done = false;
FETCH cursor_1 INTO name;
IF done THEN
LEAVE compute_loop;
END IF;

IF (result <> '') THEN
SET result = CONCAT(result, ', ');
END IF;

SET result = CONCAT(result, name);
END LOOP;

CLOSE cursor_1;

RETURN result;
END //

DELIMITER ;

SELECT major_students('CSCI');
SELECT major_students('CINF');

DROP FUNCTION major_students;

• Note that the example is only used to demonstrate cursors in a stored function. A much better solution is to simply use GROUP_CONCAT, such as:

SELECT GROUP_CONCAT(CONCAT(fName, ' ', lName) SEPARATOR ', ') AS majors
FROM student
WHERE major = 'CSCI';
 

4. Triggers

• Triggers allow event-driven programming.
• A trigger is activated when certain events occur. Unlike stored routines, triggers are not explicitly called.
• Four questions to ask for an event model:
  1. What are the events?
  2. What information can be obtained from the events?
  3. What actions can be performed to handle events?
  4. How do the events propagate?

4.1 The event model for triggers

• Events:
  1. Inserting a row
  2. Updating a row
  3. Deleting a row
• Actions can be executed:
  1. Before the event
  2. After the event
• Information can be obtained through two keywords:
  1. old:
     • the old value of the row before the event.
     • for update and delete events
  2. new:
  • the new value of the row after the event.
  • for update and insert events
• Triggers do not propagate.

Advantages of triggers

1. The event driven model suits certain kinds of tasks better.
2. Can ease the implementation of consistency check.
3. Can ease the implementation of business logic and integrity check.

Disadvantages:

1. Relatively invisible, and possibly overlooked by developers.
2. Relatively difficult to debug.
3. Potential performance issues.
4. Potential complicated interactions.

Example:

An example of using trigger is for auditing, e.g. https://vladmihalcea.com/mysql-audit-logging-triggers/

Execute the following code for auditing and ensure that you understand the result.

-- trigger

CREATE TABLE courseUpdate(
   cuId        INT NOT NULL AUTO_INCREMENT,
   `type`      CHAR(1),
    courseId    INT NOT NULL,
    rubric      CHAR(4) NOT NULL,
    number      CHAR(4) NOT NULL,
    title       VARCHAR(80) NOT NULL,
    credits     TINYINT NULL,
    ts          TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT CourseUpdate_courseId_pk PRIMARY KEY (cuId),
    CONSTRAINT CourseUpdate_deptCode_fk FOREIGN KEY (rubric)
        REFERENCES Department(deptCode));
     
     
-- trigger example.
DELIMITER $$

CREATE TRIGGER update_Course AFTER UPDATE ON Course FOR EACH ROW
BEGIN
   -- code should be more sophisticated.
   INSERT INTO courseUpdate(`type`, courseId, rubric, number, title, credits)
   VALUES('U', new.courseId, new.rubric, new.number, new.title, new.credits);
END $$


CREATE TRIGGER insert_Course AFTER INSERT ON Course FOR EACH ROW
BEGIN
   -- code should be more sophisticated.
   INSERT INTO courseUpdate(`type`, courseId, rubric, number, title, credits)
   VALUES('I', new.courseId, new.rubric, new.number, new.title, new.credits);
END $$

DELIMITER ;

SELECT * FROM Course;

UPDATE Course
SET number = 2315
WHERE courseId = 2000;

INSERT INTO Course(courseId, rubric, number, title, credits) VALUES
    (4000,'CSCI',3532,'Advanced Data Structures',3);
  
UPDATE Course
SET number = 3341
WHERE courseId = 4000;

SELECT * FROM course;
SELECT * FROM courseUpdate;

-- Clean up.
DROP TRIGGER update_Course;
DROP TRIGGER insert_Course;

DROP TABLE courseUpdate;

DELETE FROM Course
WHERE CourseId = 4000;

UPDATE Course
SET number = 3333
WHERE courseId = 2000;

SELECT * FROM Course;
 

5. System Catalog

• Named collections of meta-data in SQL DBMS.
• Important for DB administrators and developers to know.
• Usually stored in relations.
• Common terms with similar meaning: data dictionary, catalog, etc.
• Three major databases that are included with MySQL: mysql, information_schema and performance_schema.

Example:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| swim              |
| toytu              |
| world              |
| yue_exp            |
+--------------------+
7 rows in set (0.00 sec)
> 

5.1 mysql database

• MySQL database stores information about the system. It is the system schema.
• See: https://dev.mysql.com/doc/refman/8.1/en/system-schema.html.
• However, many of these system tables cannot be accessed directly. They should be access through other means (such as information_schema, see below)

Example:

SELECT DISTINCT u.user, u.password
FROM mysql.user AS u
WHERE u.host = 'localhost'; 

5.2 information_schema database

• Store information about all databases in MySQL.
• Contents are system views using the MEMORY storage engine and they cannot be updated.
• Triggers cannot be defined on tables in information_schema.

Example:

mysql> SELECT table_name, table_type, row_format, table_rows, avg_row_length
    -> FROM information_schema.tables
    -> WHERE table_schema = 'information_schema'
    -> ORDER BY table_name DESC;
+---------------------------------------+-------------+------------+------------+----------------+
| table_name                            | table_type  | row_format | table_rows | avg_row_length |
+---------------------------------------+-------------+------------+------------+----------------+
| VIEWS                                 | SYSTEM VIEW | Dynamic    |       NULL |              0 |
| USER_PRIVILEGES                       | SYSTEM VIEW | Fixed      |       NULL |           1986 |
| TRIGGERS                              | SYSTEM VIEW | Dynamic    |       NULL |              0 |
| TABLE_PRIVILEGES                      | SYSTEM VIEW | Fixed      |       NULL |           2372 |
| TABLE_CONSTRAINTS                     | SYSTEM VIEW | Fixed      |       NULL |           2504 |
| TABLESPACES                           | SYSTEM VIEW | Fixed      |       NULL |           6951 |
| TABLES                                | SYSTEM VIEW | Fixed      |       NULL |           9450 |
| STATISTICS                            | SYSTEM VIEW | Fixed      |       NULL |           5753 |
| SESSION_VARIABLES                     | SYSTEM VIEW | Fixed      |       NULL |           3268 |
| SESSION_STATUS                        | SYSTEM VIEW | Fixed      |       NULL |           3268 |
| SCHEMA_PRIVILEGES                     | SYSTEM VIEW | Fixed      |       NULL |           2179 |
| SCHEMATA                              | SYSTEM VIEW | Fixed      |       NULL |           3464 |
| ROUTINES                              | SYSTEM VIEW | Dynamic    |       NULL |              0 |
| REFERENTIAL_CONSTRAINTS               | SYSTEM VIEW | Fixed      |       NULL |           4814 |
| PROFILING                             | SYSTEM VIEW | Fixed      |       NULL |            308 |
| PROCESSLIST                           | SYSTEM VIEW | Dynamic    |       NULL |              0 |
| PLUGINS                               | SYSTEM VIEW | Dynamic    |       NULL |              0 |
| PARTITIONS                            | SYSTEM VIEW | Dynamic    |       NULL |              0 |
| PARAMETERS                            | SYSTEM VIEW | Dynamic    |       NULL |              0 |
| KEY_COLUMN_USAGE                      | SYSTEM VIEW | Fixed      |       NULL |           4637 |
| INNODB_TRX                            | SYSTEM VIEW | Fixed      |       NULL |           4534 |
| INNODB_LOCK_WAITS                     | SYSTEM VIEW | Fixed      |       NULL |            599 |
| INNODB_LOCKS                          | SYSTEM VIEW | Fixed      |       NULL |          31244 |
| INNODB_CMP_RESET                      | SYSTEM VIEW | Fixed      |       NULL |             25 |
| INNODB_CMPMEM_RESET                   | SYSTEM VIEW | Fixed      |       NULL |             29 |
| INNODB_CMPMEM                         | SYSTEM VIEW | Fixed      |       NULL |             29 |
| INNODB_CMP                            | SYSTEM VIEW | Fixed      |       NULL |             25 |
| GLOBAL_VARIABLES                      | SYSTEM VIEW | Fixed      |       NULL |           3268 |
| GLOBAL_STATUS                         | SYSTEM VIEW | Fixed      |       NULL |           3268 |
| FILES                                 | SYSTEM VIEW | Fixed      |       NULL |           2677 |
| EVENTS                                | SYSTEM VIEW | Dynamic    |       NULL |              0 |
| ENGINES                               | SYSTEM VIEW | Fixed      |       NULL |            490 |
| COLUMN_PRIVILEGES                     | SYSTEM VIEW | Fixed      |       NULL |           2565 |
| COLUMNS                               | SYSTEM VIEW | Dynamic    |       NULL |              0 |
| COLLATION_CHARACTER_SET_APPLICABILITY | SYSTEM VIEW | Fixed      |       NULL |            195 |
| COLLATIONS                            | SYSTEM VIEW | Fixed      |       NULL |            231 |
| CHARACTER_SETS                        | SYSTEM VIEW | Fixed      |       NULL |            384 |
+---------------------------------------+-------------+------------+------------+----------------+
37 rows in set (0.08 sec)
 

• It is more flexible than the SHOW command.

Example:

Execute the following code and ensure that you understand the results.

-- System Catalog
-- Getting selected columns from information_schema
SELECT table_name, table_type, row_format, table_rows, avg_row_length
FROM information_schema.tables
WHERE table_schema = 'information_schema'
ORDER BY table_name DESC;

-- databases and tables
SELECT t.TABLE_SCHEMA AS `schema`, COUNT(t.TABLE_NAME) AS num_tables
FROM information_schema.tables AS t
GROUP BY `schema`
ORDER BY num_tables DESC;

SELECT t.TABLE_SCHEMA AS `schema`, t.ENGINE, COUNT(t.TABLE_NAME) AS num_tables
FROM information_schema.tables t
GROUP BY `schema`, t.ENGINE
ORDER BY `schema`, num_tables DESC;

Example:

DROP SCHEMA IF EXISTS dbtool;
CREATE SCHEMA dbtool;
USE dbtool;

-- Return the number of columns of a table in a schema (database) in the output parameter column_count
DELIMITER //
CREATE OR REPLACE PROCEDURE count_columns(
    IN schema_name VARCHAR(64),
    IN table_name VARCHAR(64),
    OUT column_count INT
)
BEGIN
    SET @__cc_query = CONCAT('SELECT COUNT(*) INTO @__cc_column_count FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?');
    PREPARE stmt FROM @__cc_query;
    SET @__cc_schema_name = schema_name;
    SET @__cc_table_name = table_name;
  
    EXECUTE stmt USING @__cc_schema_name, @__cc_table_name;
    SET column_count = @__cc_column_count;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

CALL count_columns('toyu', 'student', @column_count);
SELECT @column_count;
CALL count_columns('swim', 'swimmer', @column_count);
SELECT @column_count;

5.3 performance_schema database

• contain performance related server data.