A brief discussion on MySql views, triggers and stored procedures

view

What is a view? What is the role of a view?

A view is a virtual table, a logical table that does not contain data itself. Saved in the data dictionary as a select statement .

Through the view, part of the data of the base table (the table used to create the view is called the base table) can be displayed. To put it simply, the data of the view comes from the base table.

Advantages of views:

• Simple: Users who use views do not need to care about the structure, join conditions, and filter conditions of the corresponding tables. For users, the result set is already filtered by the composite conditions.
• Security: Users who use views can only access the result sets they are allowed to query. Permission management for tables cannot be restricted to a certain row or column, but this can be easily achieved through views.
• Data independence: Once the structure of the view is determined, the impact of table structure changes on users can be shielded. Adding columns to the source table has no effect on the view. If the source table changes the column name, it can be solved by modifying the view without affecting visitors.
• No space is occupied: Views are logical tables and do not occupy memory space.

In short, most of the time, views are used to ensure data security and improve query efficiency.

Disadvantages of views:

• Poor performance: SQL Server must convert view queries into queries on base tables. If the view is defined by a complex multi-table query, then even a simple query on the view will be converted into a complex combination by SQL Server, which takes a certain amount of time.
• Modification restrictions: When a user tries to modify certain information in a view, the database must convert it into a modification of certain information in the base table. This is very convenient for simple views, but for more complex views, it may not be modifiable.
  

Creating and modifying views

--The basic syntax for creating is:
create view <view name> as select statement;
create view <view name> (field) as select statement;
create or replace view <view name>;

--The modified syntax is:
alter view <view name> as select statement;

--View deletion syntax:
drop view <view name>;

trigger

What is a trigger?

A trigger monitors a certain situation and triggers an action.

Syntax for creating and deleting triggers:

--Syntax for creating a trigger:
create trigger trigger name after/before insert/update/delete on table name for each row
begin
sql statement;
end

--after/before: can be set before or after the event occurs --insert/update/delete: they can be triggered during the execution of insert, update or delete --for each row: perform an action every other row --delete trigger syntax:
drop trigger trigger name;

Demo

--Create an employee lateness table:
create table work_time_delay(
empno int not null comment 'employee number',
ename varchar(50) comment 'Employee name',
status int comment 'status'
);

--delimiter // Custom statement end symbol​
delimiter //

create trigger trig_work after insert on work_time_delay
for each row
begin
 update employee set sal=sal-100 where empno=new.empno;
end
//

--new: refers to the new data saved before or after the event occurs

Stored Procedures

What is a stored procedure?

A stored procedure encapsulates a complex series of operations into a process. Similar to shell, python scripts, etc.

Advantages of stored procedures:

• Complex operations, simple calls
• Fast speed
  

Disadvantages of stored procedures:

• Complex packaging
• No flexibility
  

Create stored procedure syntax

--Create stored procedure syntax:
create procedure name(parameters....)
begin
 procedure body;
 procedure body;
end
--parameter:
-- in|out|inout parameter name type (length)
-- in: indicates that the caller passes a value to the procedure (the passed value can be a literal or a variable)
-- out: indicates that the procedure passes a value to the caller (multiple values ​​can be returned) (the output value can only be a variable)
-- inout: indicates that the caller passes a value to the procedure, and the procedure passes a value to the caller (the value can only be a variable)

--Declare variables: declare variable name type (length) default default value;
--Assign a value to a variable: set @variable name = value;
--Call storage command: call name(@variable name);
--Delete stored procedure command: drop procedure name;
--View the created stored procedure command: show create procedure name\G;

Demo

--Create a simple stored procedure:
delimiter //
create procedure name(in n int)
begin
 select * from employee limit n;
end
//

--Call stored procedure set @n=5;
//

call name(@n);
create procedure name()
begin
 declare n int default 6;
 select * from employee limit n;
end
//

--Call the stored procedure call name();
//

The above is a brief discussion of the details of MySql views, triggers, and stored procedures. For more information about MySql views, triggers, and stored procedures, please pay attention to other related articles on 123WORDPRESS.COM!