Mysql Basics 2

View#

A view is a ==virtual table== whose content is defined by a query. Like a real table, a view contains a set of named columns and row data. However, a view does not exist in the database as a stored set of data values. The row and column data come from the tables referenced by the query that defines the view and are generated dynamically when the view is referenced. The role of a view is similar to filtering for the underlying tables it references. The filtering that defines a view can come from one or more tables in the current or other databases, or other views. There are no restrictions on querying through views, and there are few restrictions on modifying data through them. ==A view is an SQL statement of a query stored in the database==, primarily for two reasons: security, as views can hide some data.

Create View#

CREATE VIEW view_name AS SQL_statement
Example:
CREATE VIEW v1 AS SELECT nid, name FROM tab1 WHERE nid > 4

Drop View#

DROP VIEW view_name

Modify View#

ALTER VIEW view_name AS SQL_statement
Example:
ALTER VIEW v1 AS
SELECT A.nid, B.NAME FROM tab1
LEFT JOIN B ON A.id = B.nid
LEFT JOIN C on A.id = C.nid
WHERE tab1.id > 2

Use View#

When using a view, treat it as a table. Since a view is a virtual table, you cannot perform create, update, and delete operations on it as you would on a real table, ==it can only be used for querying==.

SELECT * FROM v1

Stored Procedure#

Why Use Stored Procedures#

We all know that applications are divided into two types: web-based and desktop-based, both of which interact with databases to complete data access tasks. Suppose there is an application that includes both types, and now we need to modify one of the SQL query statements. We may need to modify the corresponding SQL query statements in both cases, which can become problematic when our application is large and complex, making it hard to maintain! Additionally, placing SQL query statements in our web or desktop applications can easily expose them to SQL injection attacks. Stored procedures can help us solve these problems.

Create Stored Procedure#

There are mainly two types of stored procedures: those with parameters and those without parameters.

Example without parameters

// Create stored procedure
delimiter   // Custom statement terminator, as multiple SQL statements will be executed here, it needs to be customized to prevent errors
CREATE PROCEDURE p1()
BEGIN
    SELECT * FROM tab1;
END
delimiter； // End of custom local terminator
// Execute stored procedure
CALL p1()

Example with parameters

// Create stored procedure
delimiter\\
CREATE PROCEDURE p1(
    IN i1 INT,  // Input parameter i1
    IN i2 INT,  // Input parameter i2
    INOUT i3 INT,   // Can take input and return a value
    OUT r1 INT  // Returns a value
)
BEGIN
    DECLARE temp1 INT;
    DECLARE temp2 INT DEFAULT 0;
    SET temp1 = 1;
    SET r1 = i1 + i2 + temp1 + temp2;
    SET i3 = i3 + 100;
END\\
delimiter;

// Execute stored procedure
DECLARE @t1 INT DEFAULT 3;  // Set default value for variable to 3
DECLARE @t2 INT;    // Set variable
CALL p1(1, 2, @t1, @t2);   // Execute stored procedure and pass parameters, t2 is automatically discarded
SELECT @t1, @t2;     // View results from stored procedure

Drop Stored Procedure#

DROP PROCEDURE p1;

Function#

MySQL has many built-in functions, such as those for calculating averages, sums, counts, and various others. First, let’s look at some built-in functions, and then we will discuss user-defined functions. Functions can also take parameters and return values, but functions cannot obtain the results of execution statements, while stored procedures can.

CHAR_LENGTH(str)
        Returns the length of the string str, measured in characters. A multi-byte character counts as one character.
        For a string containing five two-byte characters, LENGTH() returns 10, while CHAR_LENGTH() returns 5.

CONCAT(str1, str2, ...)
        String concatenation
        If any parameter is NULL, the return value is NULL.
CONCAT_WS(separator, str1, str2, ...)
        String concatenation (with custom separator)
        CONCAT_WS() does not ignore any empty strings. (However, it will ignore all NULLs).

CONV(N, from_base, to_base)
        Base conversion
        For example:
            SELECT CONV('a', 16, 2); converts 'a' from hexadecimal to binary string representation.

FORMAT(X, D)
        Formats the number X as '#,###,###.##', rounding to D decimal places, and returns the result as a string. If D is 0, the result is returned without a decimal point or decimal part.
        For example:
            SELECT FORMAT(12332.1, 4); results in: '12,332.1000'
INSERT(str, pos, len, newstr)
        Inserts a string at a specified position in str
            pos: the position to replace
            len: the length to replace
            newstr: the new string
        Special cases:
            If pos exceeds the original string length, the original string is returned.
            If len exceeds the original string length, the new string completely replaces the original.
INSTR(str, substr)
        Returns the position of the first occurrence of substring in string str.

LEFT(str, len)
        Returns a substring of str starting from the beginning of length len.

LOWER(str)
        Converts to lowercase.

UPPER(str)
        Converts to uppercase.

LTRIM(str)
        Returns string str with leading space characters removed.
RTRIM(str)
        Returns string str with trailing space characters removed.
SUBSTRING(str, pos, len)
        Gets a substring.

LOCATE(substr, str, pos)
        Gets the index position of the substring.

REPEAT(str, count)
        Returns a string composed of the repeated string str, with the number of str equal to count.
        If count <= 0, it returns an empty string.
        If str or count is NULL, it returns NULL.
REPLACE(str, from_str, to_str)
        Returns string str with all occurrences of from_str replaced by to_str.
REVERSE(str)
        Returns string str in reverse order.
RIGHT(str, len)
        Returns a substring of str starting from the end of length len.

SPACE(N)
        Returns a string composed of N spaces.

SUBSTRING(str, pos), SUBSTRING(str FROM pos), SUBSTRING(str, pos, len), SUBSTRING(str FROM pos FOR len)
        The format without the len parameter returns a substring from string str starting at position pos. The format with the len parameter returns a substring of the same length as len starting at position pos. The format using FROM is standard SQL syntax. A negative value can also be used for pos. If so, the substring position starts from the end of the string at pos characters, rather than from the beginning. Negative values can be used in the following function formats.

        mysql> SELECT SUBSTRING('Quadratically', 5);
            -> 'ratically'

        mysql> SELECT SUBSTRING('foobarbar' FROM 4);
            -> 'barbar'

        mysql> SELECT SUBSTRING('Quadratically', 5, 6);
            -> 'ratica'

        mysql> SELECT SUBSTRING('Sakila', -3);
            -> 'ila'

        mysql> SELECT SUBSTRING('Sakila', -5, 3);
            -> 'aki'

        mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
            -> 'ki'

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str)
       Returns string str with all prefixes and/or suffixes of remstr removed. If none of the classifiers BOTH, LEADING, or TRAILING are given, BOTH is assumed. remstr is optional; if not specified, spaces are removed.

        mysql> SELECT TRIM('  bar   ');
                -> 'bar'

        mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
                -> 'barxxx'

        mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
                -> 'bar'

        mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
                -> 'barx'

Create User-Defined Function#

delimiter\\
CREATE FUNCTION f1(
    i1 INT,
    i2 INT)
RETURNS INT
BEGIN
    DECLARE num INT;
    SET num = i1 + i2;
    RETURN(num);
END\\
delimiter;

Drop Function#

DROP FUNCTION f1;

Execute Function#

// Get return value
DECLARE @i VARCHAR(32);
SELECT UPPER('alex') INTO @i;
SELECT @i;

// Use in query
SELECT f1(11, nid), name FROM tb2;

Transaction#

Transactions are used to treat multiple SQL operations as atomic operations. If any one of them encounters an error, it can roll back to the original state, thus ensuring the integrity of the database data. For example: when transferring money between two bank cards, if Party A has sent the money, but suddenly the fiber optic cable breaks and Party B has not yet received the money, where did the money go? To prevent such situations, transactions are introduced to avoid these occurrences.

Example of Using Transactions#

delimiter \\
CREATE PROCEDURE p1(
    OUT p_return_code TINYINT
)
BEGIN 
  DECLARE EXIT HANDLER FOR SQLEXCEPTION 
  BEGIN 
    -- ERROR 
    SET p_return_code = 1; 
    ROLLBACK; 
  END; 

  DECLARE EXIT HANDLER FOR SQLWARNING 
  BEGIN 
    -- WARNING 
    SET p_return_code = 2; 
    ROLLBACK; 
  END; 

  START TRANSACTION; 
    DELETE FROM tb1;                   -- SQL statements are placed here
    INSERT INTO tb2(name) VALUES('seven');
  COMMIT; 

  -- SUCCESS 
  SET p_return_code = 0; 

  END\\
delimiter ;

Execute Stored Procedure#

DECLARE @i TINYINT;
CALL p1(@i);
SELECT @i;

Trigger#

A trigger, simply put, is an action that is triggered before or after executing an insert, delete, or update statement. Triggers are used to customize user behavior before and after performing operations on table rows.

Basic Syntax#

# Before Insert
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# After Insert
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# Before Delete
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# After Delete
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# Before Update
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# After Update
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END

Example 1 Before Insert

-- Before inserting data into tab1, insert a record into tab2 with name = Zhang Yanlin, only if the name being inserted into tab1 is 'aylin'
delimiter //
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN

IF NEW.NAME = 'aylin' THEN
    INSERT INTO tb2 (NAME)
VALUES
    ('Zhang San')
END
END//
delimiter ;

Example 2 After Insert

delimiter //
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
    IF NEW.num = 666 THEN
        INSERT INTO tb2 (NAME)
        VALUES
            ('Zhang San'),
            ('Very Handsome') ;
    ELSEIF NEW.num = 555 THEN
        INSERT INTO tb2 (NAME)
        VALUES
            ('aylin'),
            ('Very Handsome') ;
    END IF;
END//
delimiter ;

The same logic applies to delete, update, and select operations.

Special note: NEW refers to the data row that is about to be inserted, and OLD refers to the data row that is about to be deleted.

Drop Trigger#

DROP TRIGGER tri_after_insert_tb1;

Use Trigger#

Triggers cannot be called directly by users but are triggered passively by operations of [insert/delete/update] on the table.

INSERT INTO tb1(name) VALUES('Zhang San')