1 view (simplified query operation)

1-1 overview

Features of view:

• The view is the result set returned after the execution of the SELECT statement. It is the encapsulation of the SELECT statement.
• The view is a virtual table (the table obtained by query), and the rows and columns in the table come from the real table.

Advantages of view over normal table

• Simple: users of views only need to pay attention to the view, without paying attention to the structure, association conditions and filtering conditions of the real table behind the view.
• Security: the view can be used to restrict the viewing permission of access users (including developers). The permission restriction of the view on the table can be specific to a row and a column.
• Data independence: there is independence between the view and the table of data source, which is reflected in:
  • The data source table can add or delete records freely
  • The structure of the data source table changes only by updating the SQL statement encapsulated by the view definition.

Points for attention in view usage

Views are usually used to simplify query operations. You can update the table of the view data source by updating the view, but this is generally not recommended.

1-2 creation and modification of view

Preparation table

create database demo_01 default charset=utf8mb4;
use demo_01;
CREATE TABLE `city` (
`city_id` int(11) NOT NULL AUTO_INCREMENT,
`city_name` varchar(50) NOT NULL,
`country_id` int(11) NOT NULL,
PRIMARY KEY (`city_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `country` (
`country_id` int(11) NOT NULL AUTO_INCREMENT,
`country_name` varchar(100) NOT NULL,
PRIMARY KEY (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into `city` (`city_id`, `city_name`, `country_id`) values(1,'Xi'an',1);
insert into `city` (`city_id`, `city_name`, `country_id`) values(2,'NewYork',2);
insert into `city` (`city_id`, `city_name`, `country_id`) values(3,'Beijing',1);
insert into `city` (`city_id`, `city_name`, `country_id`) values(4,'Shanghai',1);
insert into `country` (`country_id`, `country_name`) values(1,'China');
insert into `country` (`country_id`, `country_name`) values(2,'America');
insert into `country` (`country_id`, `country_name`) values(3,'Japan');
insert into `country` (`country_id`, `country_name`) values(4,'UK');

• Note the country in the city table_ ID is the foreign key (the primary key of the external table)

Multi table query: query city information and its associated country information.

 SELECT c.*,t.country_name from city c, country t WHERE c.country_id = t.country_id;

View creation

Objective: encapsulate the above SELECT statement into a view.

CREATE view view_city_country as SELECT c.*,t.country_name from city c, country t WHERE c.country_id = t.country_id;

Summary: create a virtual table view in the figure above_ city_ Country, encapsulating the query statements defined between.

Command format: create view < view name > as < select statement >

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

• [with [cached | local] check option] meaning

Restrictions on update conditions when updating views
LOCAL: It can be updated as long as the conditions of this view are met.
CASCADED: All conditions for all views of this view must be met before they can be updated. Default value.

View changes

Command format: alter view < view name > as < select statement >

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

1-3 view and deletion

see

Starting from MySQL version 5.1, when using the SHOW TABLES command, not only the name of the table but also the name of the view will be displayed without saving
The SHOW VIEWS command displays views separately.

show tables;          
SHOW CREATE VIEW View name \G;      # View the specific definition of the view

Delete (DROP VIEW view name)

DROP VIEW [IF EXISTS] view_name [, view_name] ...[RESTRICT | CASCADE]

2 stored procedures and stored functions

2-1 overview

Definition of stored procedure and stored function:

• A collection of SQL statements previously compiled and stored in a database

Advantages of stored procedures and functions:

• Simplify a lot of work for application developers
• Reduce data transmission between database and application server and improve the efficiency of data processing

Distinction between stored procedure and stored function:

• Functions can be understood as stored procedures with return values

2-2 create, call and query stored procedures

establish

CREATE PROCEDURE procedure_name ([proc_parameter[,...]])
begin
-- SQL sentence
end ;

example

delimiter $
create procedure pro_test1()
    begin
    select 'hello mysql' ;
    end$
delimiter ;

Note: due to the delimiter in MYSQL+ enter means to let the database execute commands. In order to execute multiple SQL statements, you need to temporarily transfer the delimiter from the database; Replace with other characters.

call

call procedure_name() ;

View created storage

-- query db_name All stored procedures in the database
select name from mysql.proc where db='db_name';
-- Query the status information of stored procedure
show procedure status;
-- Query the definition of a stored procedure in the current database
show create procedure Database name.Stored procedure name \G;

Note: in fact, the created stored procedures are also stored in the database in the form of tables, and stored in the proc table in MySQL database in MySQL.

Note: the above command can query all stored procedures created in the database.

Note: as long as you know the name of the stored procedure and its database, you can view the SQL statement set corresponding to the stored procedure through the show command.

A faster way: directly view MySQL through the GUI interface Proc table, you can see all the above information.

delete

DROP PROCEDURE [IF EXISTS] Stored procedure name;

2-2 SQL statement syntax corresponding to stored procedure

2-2-1 assignment of variables (2 ways)

Variable definition: local variables can be declared by DECLARE. The scope of the variable can only be in the BEGIN... END block.

• Manually defined constant (SET)

DELIMITER $
CREATE PROCEDURE pro_test3()
    BEGIN
    DECLARE NAME VARCHAR(20);        
    SET NAME = 'MYSQL';
    SELECT NAME ;
END$
DELIMITER 

• Get variable value from data table (select... Into)

DELIMITER $
CREATE PROCEDURE pro_test5()
BEGIN
    declare countnum int;
    select count(*) into countnum from city;
    select countnum;
END$
DELIMITER ;

The COUNT(*) function returns the number of records in the table

Summary: the statistical results in the data table can be written into variables through into

2-2-2 use of if statement

demand

According to the defined height variable, determine the body type of the current height
180 And above ----------> Tall and tall
170 - 180 ---------> Standard figure
170 following ----------> General figure

Stored procedure creation

delimiter $
create procedure pro_test6()
begin
    declare height int default 175;
    declare description varchar(50);
    if height >= 180 then
        set description = 'good body';
    elseif height >= 170 and height < 180 then
        set description = 'standard body';
    else
        set description = 'common body';
    end if;
    select description ;
end$
delimiter ;

Summary: the default height is 175, which is judged as standard body according to the if statement

2-2-3 transfer of stored procedure parameters

Template

create procedure procedure_name([in/out/inout] Parameter name parameter type)
...
IN : This parameter can be used as input, that is, the value needs to be passed in by the caller , default
OUT: This parameter is used as output, that is, it can be used as return value
INOUT: It can be used as input parameter or output parameter

demand

Write a stored procedure, pass the height as a parameter, and return the determination result through the parameter

code

delimiter $
create procedure pro_test7(IN height INT,OUT description VARCHAR(100))
begin
 if height >= 180 then
        set description = 'good body';
    elseif height >= 170 and height < 180 then
        set description = 'standard body';
    else
        set description = 'common body';
  end if;
end$
delimiter ;

View returned variables

• @description: the "@" symbol is added before the variable name, which is called the user session variable, which represents the whole session process. It is useful
  , this is similar to a global variable.
• @@global.sort_buffer_size: a variable preceded by "@ @" is called a system variable

call pro_test7(168, @description);
select @description;

2-2-4 use of case statement

Template

Mode 1 :
CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE;

Mode II :
CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE;

example:

delimiter $
create procedure pro_test9(month int)
begin
	declare result varchar(20);
    case
        when month >= 1 and month <=3 then
        set result = 'first quarter ';
        when month >= 4 and month <=6 then
        set result = 'Second quarter';
        when month >= 7 and month <=9 then
        set result = 'Third quarter';
        when month >= 10 and month <=12 then
        set result = 'Fourth quarter';
    end case;
    select concat('The month you entered is :', month , ' , The month is : ' , result) as content;
end$
delimiter ;

Execution result:

2-2-5 use of while statement

Template

while search_condition do
    statement_list
end while;

Example (calculate the value of 1 to n)

delimiter $
create procedure pro_test8(n int)
begin
    declare total int default 0;
    declare num int default 1;
    while num<=n do
        set total = total + num;
        set num = num + 1;
    end while;
    select total;
end$
delimiter ;

results of enforcement

2-2-6 use of repeat

Template

REPEAT
    statement_list
    UNTIL search_condition
END REPEAT;

Example: calculate 1 to n

delimiter $
create procedure pro_test10(n int)
    begin
    declare total int default 0;
    repeat
    set total = total + n;
    set n = n - 1;
    until n=0
    end repeat;
    select total ;
    end$
delimiter ;

2-2-7 use of loop and LEAVE

• LOOP can realize a simple dead LOOP. If you want to exit the LOOP, you need to use it with leave

[begin_label:] LOOP
    statement_list
END LOOP [end_label]

example:

delimiter $
CREATE PROCEDURE pro_test11(n int)
BEGIN
    declare total int default 0;
    ins: LOOP
        IF n <= 0 then
            leave ins;
        END IF;
        set total = total + n;
        set n = n - 1;
    END LOOP ins;
    select total;
END$
delimiter ;

Note: the ins above is the start and result mark of the loop.

2-2-8 use of cursor / cursor

definition:

• The data type used to store the query result set. The cursor can be used to cycle the result set in stored procedures and functions
  • Extract one record at a time

The syntax of cursor declaration, OPEN, FETCH and CLOSE is as follows:

DECLARE cursor_name CURSOR FOR select_statement ;        
OPEN cursor_name ;
FETCH cursor_name INTO var_name [, var_name] ...
CLOSE cursor_name ;

Examples of cursor usage:

Data table creation

create table emp(
    id int(11) not null auto_increment ,
    name varchar(50) not null comment 'full name',
    age int(11) comment 'Age',
    salary int(11) comment 'salary',
    primary key(`id`)
)engine=innodb default charset=utf8 ;
insert into emp(id,name,age,salary) values(null,'Golden King ',55,3800),(null,'White browed Eagle
 king',60,4000),(null,'Green winged bat King',38,2800),(null,'Purple Dragon King',42,1800);

Requirement 1: query the data in the emp table and obtain it row by row for display

delimiter $
create procedure pro_test15()
begin
declare e_id int(11);
declare e_name varchar(50);
declare e_age int(11);
declare e_salary int(11);
declare emp_result cursor for select * from emp;
open emp_result;
    fetch emp_result into e_id,e_name,e_age,e_salary;
    select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', Salary is:
    ',e_salary);
    fetch emp_result into e_id,e_name,e_age,e_salary;
    select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', Salary is:
    ',e_salary);
    fetch emp_result into e_id,e_name,e_age,e_salary;
    select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', Salary is:
    ',e_salary);
    fetch emp_result into e_id,e_name,e_age,e_salary;
    select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', Salary is:
    ',e_salary);
    fetch emp_result into e_id,e_name,e_age,e_salary;
    select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', Salary is:
    ',e_salary);
close emp_result;
end$
delimiter ;

Execution result:

Summary:

declare emp_result cursor for select * from emp;

• Encapsulate the results of the SQL statement select query into the defined cursor

open emp_result;
close emp_result;

• The use of cursors requires open and close

 fetch emp_result into e_id,e_name,e_age,e_salary;

• fetch is used to extract a record of the result encapsulated in the cursor and put it into the defined variable
• In the above example, if the number of fetch es > the number of records in the data table, the error prompt No data will occur

Requirement 2: use the circular structure to view all records in the table through the cursor.

• The following example judges whether the record has been read through the handle
• You can also determine the number of records in the table through count()

DELIMITER $
create procedure pro_test15()
begin
    DECLARE id int(11);
    DECLARE name varchar(50);
    DECLARE age int(11);
    DECLARE salary int(11);
    DECLARE has_data int default 1;
    
    DECLARE emp_result CURSOR FOR select * from emp;
    DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0;
    open emp_result;
        repeat
            fetch emp_result into id , name , age , salary;
            select concat('id by',id, ', name by' ,name , ', age by ' ,age , ', Salary is: ',
            salary);
            until has_data = 0
        end repeat;
    close emp_result;
end$
DELIMITER ;

Execution result:

MYSQL determines whether there are records in the data table by providing a handle mechanism.

DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0;  
DECLARE EXIT HANDLER FOR NOT FOUND It defines the handle that cannot find the data. When the data cannot be found, the following statements will be triggered set has_data = 0 Execution.

Note: the declaration of the handle must follow the declaration of the cursor, otherwise an error will be reported.

DECLARE emp_result CURSOR FOR select * from emp;
DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0;

2-2-9 storage function

Stored functions are somewhat similar to stored procedures. Their syntax differences are as follows:

• PROCEDURE and FUNCTION, and the storage FUNCTION needs RETURN
• A stored function can be regarded as a "stored procedure with return value". In fact, a stored procedure can also return data through the OUT keyword in the parameter list.
• In fact, what can be done by storage functions can also be realized by using stored procedures.

CREATE FUNCTION function_name([param type ... ])
RETURNS type
BEGIN
...
END;

Example: define a function to query the number of records in a specific country

delimiter $
create function count_city(countryId int)
returns int
    begin
        declare cnum int ;
        select count(*) into cnum from city where country_id = countryId;
        return cnum;
    end$
delimiter ;

Execution result:

In the figure above, the storage function is called through the select statement to count the number of records with the specified id.

3 trigger

3-1 overview

Definition: the collection of SQL statements triggered and executed before or after insert/update/delete

Application scenario:

• Assist the application in ensuring data integrity, logging, data verification and other operations on the database side

Classification:

• NEW (NEW record) and old (modified old record) are two line record variables
  • Store records that have changed in the trigger

Note: MYSQL database only supports row level triggers, not statement level triggers. Oracle data is supported.

3-2 creation of trigger

Example: record the data change log of the table through the trigger, including adding, modifying and deleting;

Template

create trigger trigger_name
before/after insert/update/delete   # Trigger Type 
on tbl_name                         # Data sheet name
for each row                        # row-level trigger 
    begin
        trigger_stmt ;              # SQL statement collection          
    end;

Step 1: create a log table to save operation records

create table emp_logs(
	id int(11) not null auto_increment,
    operation varchar(20) not null comment 'Operation type, insert/update/delete',
    operate_time datetime not null comment 'Operation time',
    operate_id int(11) not null comment 'Operation table ID',
    operate_params varchar(500) comment 'Operating parameters',
    primary key(`id`)
)engine=innodb default charset=utf8;

Step 2: create an INSERT trigger to record the insertion operation of the data table

DELIMITER $
create trigger emp_logs_insert_trigger
after insert
on emp
for each row
    begin
        insert into emp_logs (id,operation,operate_time,operate_id,operate_params)
        values(null,'insert',now(),new.id,concat('After insertion(id:',new.id,', name:',new.name,',
        age:',new.age,', salary:',new.salary,')'));
    end $
DELIMITER ;

step3: create an update trigger to record the insertion operation of the data table

DELIMITER $
create trigger emp_logs_update_trigger
after update
on emp
for each row
    begin
        insert into emp_logs (id,operation,operate_time,operate_id,operate_params)
        values(null,'update',now(),new.id,concat('Before modification(id:',old.id,', name:',old.name,',
        age:',old.age,', salary:',old.salary,') , After modification(id',new.id, 'name:',new.name,',
        age:',new.age,', salary:',new.salary,')'));
    end $
DELIMITER ;

step4: create a trigger for deleting rows and record the log of deleting data

DELIMITER $

create trigger emp_logs_delete_trigger
after delete
on emp
for each row
    begin
        insert into emp_logs (id,operation,operate_time,operate_id,operate_params)
        values(null,'delete',now(),old.id,concat('Before deletion(id:',old.id,', name:',old.name,',
        age:',old.age,', salary:',old.salary,')'));
    end $
    
DELIMITER ;

Summary:

• The creation of trigger must specify the data table and data operation
• In the SQL statement executed by the trigger, the previous records can be obtained through new or old.

3-3 deleting and viewing triggers

 drop trigger [schema_name.]trigger_name      # shema_name is the name of the database. If it is not specified, it is the current database
 show triggers;                               # View triggers

• In the figure above, you can see the three triggers created before and the SQL statements executed after triggering.

reference material

01 delimiter of MySQL

02 Database Course

03 concept and function of database cursor

20210228