mysql-views, triggers, transactions, stored procedures, process control

Directory

  1. view
  2. trigger
  3. affairs
  4. stored procedure
  5. process control

1. View

A view is a virtual table composed of query results. Just like a real table, it has named column and row data.

Emphasis: The view is permanently stored, but the view stores not data, just a SQL statement

View features:

  1. The columns of the view can come from different tables, which is a new relationship established in the abstract and logical sense of the table.
  2. A view is a table (virtual table) generated from a basic table (real table).
  3. The creation and deletion of views does not affect the basic tables.
  4. Updates to the view content (additions, deletions, and modifications) directly affect the underlying table.
  5. Adding and deleting data is not allowed when the view is from multiple base tables.

advantage:

  1. Query can be simplified (multi-table query is converted into direct query through view)
  2. Permission control can be performed (enclose table permissions and develop corresponding view permissions)

(1), Create a view

create view view name as sql query statement Example: CREATE view test_view as SELECT * from test;

(2), query view

select * from view name [where condition]

(3). Modify view

alter view view name AS SQL statement; Example: ALTER view test_view as SELECT * from test_view WHERE salary>10000

(4), delete view

drop view view name;
Example: drop view test_view

2. Trigger

Triggers can monitor users’ operations of adding, deleting, and modifying tables, and trigger certain operations (without checking). They are automatically executed and cannot be called directly.

Four elements of trigger syntax:

1. Surveillance location (table)

2. Monitor events (insert/update/delete)

3. Trigger time (before/after)

4. Trigger events (insert/update/delete)

(1), Create trigger

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

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

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

# After deletion
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

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

grammar 

#Preparation table
CREATE TABLE cmd (
    id INT PRIMARY KEY auto_increment,
    USER CHAR (32),
    priv CHAR (10),
    cmd CHAR (64),
    sub_time datetime, #Submission time
    success enum ('yes', 'no') #0 represents execution failure
);

CREATE TABLE errlog (
    id INT PRIMARY KEY auto_increment,
    err_cmd CHAR (64),
    err_time datetime
);

#Create trigger
delimiter //
CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROW
BEGIN
    IF NEW.success = 'no' THEN #Equality judgment only has one equal sign
            INSERT INTO errlog(err_cmd, err_time) VALUES(NEW.cmd, NEW.sub_time); #Must add semicolon
      END IF; #must add semicolon
END//
delimiter;


#Insert records into table cmd, trigger triggers, and decide whether to insert error logs based on IF conditions
INSERT INTO cmd (
    USER,
    private,
    cmd,
    sub_time,
    success
)
VALUES
    ('A','0755','ls -l /etc',NOW(),'yes'),
    ('A','0755','cat /etc/passwd',NOW(),'no'),
    ('A','0755','useradd xxx',NOW(),'no'),
    ('A','0755','ps aux',NOW(),'yes');


#Query the error log and found two
mysql> select * from errlog;
 + ---- + ------------------ + -------------------------- +
| id | err_cmd | err_time |
 + ---- + ------------------ + -------------------------- +
| 1 | cat /etc/passwd | 2018-09-18 20:18:48 |
| 2 | useradd xxx | 2018-09-18 20:18:48 |
 + ---- + ------------------ + -------------------------- +
2 rows in set (0.00 sec)

Case

Emphasis: NEW represents the data row to be inserted, and OLD represents the data row to be deleted.

(2), view trigger

show triggers

(3), delete trigger

drop trigger trigger name

3. Affairs

Transactions are used to treat multiple SQL operations of certain operations as atomic operations, which means that a transaction is a set of SQL statements.

Once an error occurs in one of them, it can be rolled back to the original state to ensure database data integrity. The statements within the transaction will either all execute successfully or all fail.

(1) Characteristics of transactions

Transactions have the following four characteristics (ACID)

1. Atomicity: A transaction is a whole and indivisible. The SQL operations included in it must either all succeed or all fail and be rolled back. It is impossible to execute only part of the operations.

2. Consistency: After the transaction is executed, all data is complete (foreign key constraints, non-null constraints).

3. Persistence: Once the transaction is committed, the data is permanently stored in the database

4. Isolation: Transactions are isolated from each other, and the execution of one transaction does not affect the execution of other transactions.

The SQL standard defines four types of isolation levels, including some specific rules to limit which changes inside and outside the transaction are visible and which are invisible. Lower isolation levels generally support higher concurrency and have lower system overhead.

(2). Transaction isolation level

1.READ UNCOMMITED: All transactions can see the execution results of other uncommitted transactions. Rarely used in real applications as its performance is not much better than other levels

2.READ COMMITED: The default level for most databases, excluding mysql. Any modifications made from the start of a transaction to the time it is committed are not visible to other transactions.

3.REPEATABLE READ: mysql default level, which solves the problem of dirty reading. This level ensures that the results of reading the same record multiple times in the same transaction are consistent. Unable Solve the phantom reading problem

4.SERIALIZABLE (serializable): It is the highest isolation level, forcing transactions to be ordered so that they cannot conflict with each other, thereby solving the phantom read problem

Dirty read: When one transaction reads uncommitted data from another transaction, it must be ensured that all updates have been completed before querying.

Non-repeatable read: Data is inconsistent between two queries of a transaction. This may be due to the insertion of original data updated by a transaction between the two queries.

Phantom reading: refers to when a transaction is reading records in a certain range, and another transaction inserts a new record in the range. When the previous transaction reads the records in the range again, it will occur. Phantom Row.

(3) Transaction operations

start transaction; start a transaction
commit commit something
rollback rollback transaction

Note: mysql enables automatic transaction submission by default, pymysql does not automatically submit transactions by default, and requires manual commit

4. Stored procedures

A stored procedure contains a collection of executable SQL statements, similar to functions (methods).

Advantages of using stored procedures:

#1. Used to replace SQL statements written by programs to achieve decoupling of programs and sql.

#2. Based on network transmission, the amount of data transferred by alias is small, but the amount of data transferred directly to SQL is large

Disadvantages: Inconvenient to expand

(1). Use stored procedures

Creation syntax:
    create procedure name of the procedure ({<!-- -->in,out,inout} data type parameter name)
    begin
        Specific sql code
    end
    The function of the parameter needs to be specified before the parameter.<br>
    in indicates that the parameter is used to pass in data
    out is used to return data
    inout can be passed in or returned

    The parameter type is the data type in mysql<br><br>Call syntax:<br>call stored procedure()<br>

 Case: Create a stored procedure to add two integers
            create procedure add_p (in a int,in b int)
            begin
                select a + b;
            end

            //
      call call add_p(1,2)

         Case: Create a stored procedure that adds two integers and saves the result in a variable.
        define a variable
        set @su = 100;
            create procedure add_p2 (in a int,in b int,out su int)
            begin
                set su = a + b;
            end

            //
    
    Define variables set @su = 100;
    Calling process call add_p2(10,20,@su);

          Note that in a stored procedure, you need to use a semicolon to end a line, but the semicolon has a special meaning.
          You have to change the original terminator to other symbols
            delimiter // Replace the end character with //
            delimiter;

case 

In a stored procedure, you need to use a semicolon to end a line, but the semicolon has a special meaning.
You have to change the original terminator to other symbols
      delimiter // Replace the end character with //
      delimiter;

 create procedure show_p (in a int)
    begin
    if a = 1 then
        select "一";
    elseif a = 2 then
        select "二";
    else
        select "other";
    end if;
    end //

Use stored procedure to complete Enter a number 1 or 2 Display one or two

(2). Delete stored procedures

drop procedure proc_name;

5. Process control

(1), conditional statements

delimiter //
CREATE PROCEDURE proc_if ()
BEGIN
    
    declare i int default 0;
    if i = 1 THEN
        SELECT 1;
    ELSEIF i = 2 THEN
        SELECT 2;
    ELSE
        SELECT 7;
    END IF;

END //
delimiter ;

if

(2), loop statement

delimiter //
CREATE PROCEDURE proc_while ()
BEGIN

    DECLARE num INT;
    SET num = 0;
    WHILE num < 10 DO
        SELECT
            num;
        SET num = num + 1;
    END WHILE ;

END //
delimiter ;

while

delimiter //
CREATE PROCEDURE proc_repeat ()
BEGIN

    DECLARE i INT ;
    SET i = 0;
    repeat
        select i;
        set i = i + 1;
        until i >= 5
    end repeat;

END //
delimiter ;

repeat

BEGIN
    
    declare i int default 0;
    loop_label: loop
        
        set i=i + 1;
        if i<8 then
            iterate loop_label;
        end if;
        if i>=10 then
            leave loop_label;
        end if;
        select i;
    end loop loop_label;

END

loop