[MySQL]Views, stored procedures, triggers

Article directory

• 1. View
• • 1.1 Basic operations of views
  • 1.2 Inspection options for views
  • 1.3 View update
• 2. Stored procedures
• • 2.1 Basic syntax of stored procedures
  • 2.2 Variables
  • • 2.2.1 System variables
    • 2.2.2 User-defined variables
    • 2.2.3 Local variables
  • 2.3 Keywords
  • • 2.3.1 Judgment
    • 2.3.2 Loop
  • 2.4 Cursor
  • 2.5 Stored functions
• 3. Trigger

1. View

A view is a virtual table. The data in the view does not actually exist in the database. The row and column data come from the tables used in the query of the custom view and are dynamically generated when the view is used.
In layman’s terms, the view only saves the SQL logic of the query and does not save the query results. So when we create a view, the main work falls on creating this SQL query statement.

effect:

• Simple
  Views not only simplify users’ understanding of data, but also their operations. Frequently used queries can be defined as views, so that users do not have to specify all the conditions for subsequent operations every time.
• Safety
  The database can authorize, but cannot authorize to specific rows and columns of the database. Through views, users can only query and modify the data they can see.
• Data independence
  Views can help users shield the impact of changes in the real table structure

1.1 Basic operations of views

#Create
create [or replace] view view name [(column name list)] as select statement [with [cascaded | local] check option];

#Inquire
show create view view name; #View the create view statement:
select * from view name...; #View view data:

#Modification (two options)
create [or replace] view view name [(column name list)] as select statement [with [cascaded | local] check option];
alter view view name [(column name list)] as select statement [with [cascaded | local] check option];

#delete
drop view [if exists] view name [, view name]..

e.g.

#Create view
create or replace view stu_v_1 as select id,name from student where id <= 10;

#Query view
show create view stu_v_1;
select * from stu_v_1;
select * from stu_v_1 where id<3;
 
#Modify view
create or replace view stu_v_1 as select id,name,num from student where id <= 10;
alter view stu_v_1 as select id,name,num from student where id <=10;

#Delete view
drop view if exists stu_v_1;

1.2 View inspection options

When you create a view using the with check option clause, MySOL checks each row that is being changed, such as insert, update, delete, through the view to make it conform to the view’s definition.
MySQL allows creating a view based on another view, and it also checks the rules in the dependent view for consistency. To determine the scope of the check, mysql provides two options:
cascaded and local, default value is CASCADED

create view v1 as select id,name from student where id <= 20 with cascaded check option;
create view v1 as select id,name from student where id <= 20 with local check option;

Cascaded has upward transitivity, that is, a2 is created based on a1, a1 does not have a check option, and a2 does, then a1 will also have this attribute.
local does not
When checking, it will recursively check whether there is a check option and obey the conditions.

1.3 View update

For a view to be updateable, there must be a one-to-one relationship between the rows in the view and the rows in the underlying table. A view is not updateable if it contains any of the following:

1. Aggregation function or window function: SUM(), MIN(), MAX(), COUNT(), etc.
2. distinct
3. group by
4. having
5. union or union all

e.g.

#Create a view and use aggregate functions
create view stu_v_count as select count(*) from student;
insert into stu_v_count vaLues(10); #Error report

#1. In order to ensure the security of the database table, when developers operate the tb_user table, they can only see the basic fields of the user and block the two fields of mobile phone number and email address.
create view tb_user_view as select id,name,profession,age,gender,status,createtime from tb_user;
select from tb_user_view;

#2. Query the courses selected by each student (joint query of three tables). This function is used in many businesses. In order to simplify the operation, define a view.
create view tb-stu_course_view as select S.name stu_name,S.no,c.name cou_name from student S,student_course sc,course c where S.id = Sc.studentid and sc.courseid = c.id;

2. Stored procedure

A stored procedure is a collection of SQL statements that have been compiled in advance and stored in the database. Calling a stored procedure can simplify a lot of work for application developers, reduce the transmission of data between the database and the application server, and is helpful for improving the efficiency of data processing. Benefits.
The concept of stored procedures is very simple. It is code encapsulation and reuse at the SQL language level of the database.

Features:

• Encapsulation, reuse
• Can accept parameters and return data
• Reduce network interaction and improve efficiency

2.1 Basic syntax of stored procedures

#Create
create procedure name of the stored procedure ([parameter list])
begin
--SQL statement
end;

#transfer
call stored procedure name([parameter]);

#Check
select * from information_scheme.routines where routine_scheme = 'xxx'; #Query the stored procedures and status information of the specified database
show create procedure stored procedure name; #Query the definition of a stored procedure
#eg
select * from information_schema.routines where routine_schema = '9tse';

#delete
drop procedure [if exists] The name of the stored procedure;

If you execute SQL to create a stored procedure on the command line, you need the keyword delimiter to specify the end character of the SQL statement.

delimiter $$
create procedure test()
begin
select count(*) from student;
end$$;

2.2 Variables

2.2.1 System variables

System variables are provided by the MySQL server and are not user-defined. They belong to the server level and are divided into global variables (global) and session variables (session).

#View system variables
show [session | global] variables; --View all system variables
show [session | global] variables like '....'; --You can find variables through like fuzzy matching
select @@[session | global] system variable name; --View the value of the specified variable
#Set system variables
set [session | global] system variable name = value;
set @@[session | global] system variable name = value;

PS

• If session/global is not specified, defaults to session, session variable
• After the MySQL server is restarted, the global parameters set will become invalid. If you want to avoid losing the calibration, you can configure it in /etc/my.cnf

2.2.2 User-defined variables

User-defined variables are variables defined by users according to their needs. User variables do not need to be declared in advance. When using them, just use @variable name directly. Its scope is the current connection.

#Assignment
set @var_name = expr;
set @var_name := expr;
select @var_name := expr;
select field name into @var_name from table name;

#use
select @var_name;

PS

• User-defined variables do not need to be declared or initialized, but the value obtained is NULL.
• It is recommended to use := to avoid confusion with = (judgment of equality)

set @mygender :='male';
select @mycolor := 'red';
select count(*) into @mycount from tb_user;

2.2.3 Local variables

Local variables are variables that are defined according to and are valid locally. Before accessing, they need to be declared with DECLARE. Can be used as local variables and input parameters within stored procedures. The scope of local variables is the begin and end blocks declared within them

#Statement
declare variable name variable type [default ...];
The variable type is the database field type: INT, BIGINT, CHAR, VARCHAR, DATE, TIME, etc.

#assignment
SET variable name = value;
SET variable name:=value
SELECT field name INTO variable name FROM table name...;

2.3 Keywords

Parameters of stored procedures

create procedure stored procedure name ([ in/out/inout parameter name parameter type])
begin
...
end;

2.3.1 Judgment

if

#Syntax
if condition 1 then
...
elseif condition 2 then --optional
...
else --optional
...
end if;

case

case case_value
        when search_condition1 then statement_List1
        [when search_condition2 then statement_list2] ..
        [else statement_list]
end case;

e.g.

create procedure p6(in month int)
begin
      declare result varchar(10);
      case
            when month >= 1 and month <= 3 then
               set result := 'Q1';
            when month >= 4 and month <= 6 then
               set result := 'Q2';
            when month >= 7 and month1<=9 then
               set result := 'Q3';
            when month >= 10 and month <= 12 then
               set result := 'Q4';
            else
               set result := 'Illegal parameter';
      end case
      select concat('The month you entered is:',month,'The quarter it belongs to is:',resut);
end;

2.3.2 Loop

while
The while loop is a conditional loop control statement. After the conditions are met, the SQL statement in the loop body is executed.
The specific syntax is:

# Determine the condition first. If the condition is true, the logic will be executed. Otherwise, the logic will not be executed.
while condition do
     SQL logic.
end while;

e.g.

create procedure p(in n int)
begin
declare total int default 0;
while n>0 do
set total := total + n;
set n := n-1;
end while;
select total;
end;

call p(100);

repeat
Repeat is a conditional loop control statement that exits the loop when the condition is met. The specific syntax is:

#Execute the logic once, and then determine whether the logic is satisfied. If so, exit. If not satisfied, continue to the next cycle
repeat
      SQL..
      until condition
end repeat;

e.g.

create procedure p(in 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;

call p(10);

loop
LOOP implements a simple loop. If you do not add conditions for exiting the loop in the SQL logic, you can use it to implement a simple infinite loop.
LOOP can be used with the following two statements:
LEAVE: Used with loops to exit the loop.
TERATE: must be used in a loop. Its function is to skip the remaining statements of the current loop and directly enter the next loop.

[begin_label:] loop
      SQL logic...
end loop [end_label];

leave label --Exit the loop body of the specified label
iterate label --enter directly into the next loop

e.g.

#1
create procedure p(in n int)
begin
declare total int default 0;
sum: loop
if n<=0 then
leave sum;
end if;
set total := total + n;
set n:=n-1;
end loop sum;
select total;
end;

call p(90);


#2
create procedure p2(in n int)
begin
declare total int default 0;
sum:loop
if n<=0 then
leave sum;
end if;
if n%2 == 1 then
set n := n-1;
iterate sum;
end if;
set total := total + n;
set n := n-1;
end loop sum;
select total;
end;

2.4 Cursor

Cursor (CURSOR) is a data type used to store query result sets. Cursors can be used in stored procedures and functions to process the result set in a loop.
The use of cursors includes cursor declaration, OPEN, FETCH and CLOSE, and their syntax is as follows.

#Declare cursor
declare cursor name cursor for query statement;
 
#Open cursor
open cursor name;
 
#Get cursor records
fetch cursor name into variable[,variable];
 
#Close cursor
close cursor name;

Condition handler
Can be used to define corresponding processing steps when problems are encountered during the execution of the process control structure.

declare hander_action handler for condition_value [,condition_value] ... statement;

handler_action
continue: continue executing the current program
exit: execute the current program
condition_value
sqlstate sqlstate_value: status code, such as 02000
sqlwarning: abbreviation for all sqlstate codes starting with 01
not found: abbreviation for all sqlstate codes starting with 02
sqexception: abbreviation for all sqlstate codes that are not caught by sqlwarning or notfound

e.g.

create procedure p(in uage int)
begin
declare uname varchar(100);
declare upro varchar(100);
declare u_cursor cursor for select name,profession from tb_user where age <= uage;
declare exit handler for sqlstate '02000' close u_cursor;
#declare exit handler for not found close u_cursor;
\t
drop table if exists tb_user_pro;
create table if not exists tb_user_pro(
id int primary key auto_increment,
name varchar(100),
profession varchar(100)
);
\t
open u_cursor;
while true do
fetch u_cursor into uname,upro;
insert into tb_user_pro values(null,uname,upro);
end while;
close u_cursor;
end;

2.5 Storage function

Stored functions are not widely used in practice and can be replaced by stored procedures.
The parameters can only be of type in

create function stores function name ([parameter list])
return type [characteristic...]
begin
SQL
return ...;
end;

characteristic description:
deterministic: the same input parameters always produce the same result
no sql: does not contain SQL statements
reads sql data: Contains statements for reading data, but does not include statements for writing data

e.g.

create function fun(n int)
return int deterministic
begin
declare total int default 0;
while n>0 do
set total := total + n;
set n := n-1;
end while;
return total;
end;

select fun(50);

3. Trigger

A trigger is a database object related to a table, which refers to triggering and executing a set of SQL statements defined in the trigger before or after insert/update/delete. This feature of triggers can assist applications in ensuring data integrity, logging, data verification and other operations on the database side.
Use the aliases old and new to refer to the changed record content in the trigger, which is similar to other databases.
Currently, triggers only support row-level triggering and do not support statement-level triggering.

Type	Meaning
in (default)	This type of parameter is used as input, that is, the value needs to be passed in when calling
out	This type of parameter is used as output, that is, this parameter can As a return value
inout	can be used as an input parameter or an output parameter

Trigger type	new and old
insert type trigger	new represents data that will be or has been added
update type trigger	old represents data before modification, new represents Data that will be or has been modified
delete trigger	. old represents data that will be or has been deleted

#Create
create tigger trigger_name
before/after insert/update/delete
on tb_name for each row --row-level trigger
begin
trigger_stmt;
end;

#Check
show triggers;

#delete
drop trigger [schema_name.]trigger_name; --If schema_name is not specified, it defaults to the current database

e.g.

#Requirements: Record the data change log (user_ogs) of the user table through the server, including add, modify, and delete;
#Preparation work: oral log table userlogs
create table user_logs(
    id int(11) not null auto_increment,
    operation varchar(20) not null comment'operation type, insert/update/delete',
    operate_time datetime not nul comment'operate time',
    operate_id int(11) not null comment'operation ID',
    operate_params varchar(500) comment'operating parameters',
    primary key('id')
)engine=innodb default charset=utf8;

create trigger tb_user_insert_trigger
after insert on tb_user for each row
begin
insert into user_logs(id,operation,operate_time,operate_id,operate_params)values
(null,'insert',now(),new.id,concat('insert msg : ' ,new.id,...)
end;