MySQL view, stored procedures, triggers, functions, transactions, dynamic Execution sql

View

A view is a virtual table (not a real one), which is essentially "getting a dynamic dataset from an SQL statement and naming it", which allows the user to get a result set using only "name" and use it as a table.

To create a view:

CREATE VIEW v1 asselect * from student where ID > 100;

The future can be directly through:

SELECT * FROM v1; #直接访问学生ID大于100的信息

To delete a view:

Drop View v1;

To modify a view:

Alter VIEW V1 asselect id,name from student where id<10 and Name=dandan;

Note: When using a view, use it as a table and, because the view is a virtual table, use it to create, update, and delete the real table, only for queries.

Trigger

Before and after an "Add/delete/change" operation on a table you can use triggers when you want to trigger a particular behavior, which is used to customize the behavior of the user before and after the "Add/delete/change" row of the table.

To create a trigger:

Delimiter//create trigger tg_before_insert_student before insert on student for each rowbegin if new.name== ' Alex ' then Insert into Student_back (name) VALUES (' Alex_back '); end//delimiter;

You can also use after insert to indicate that the statements in the trigger are executed after the insert, and there are other actions: Before update, after update, before delete, after delete

Note: New represents the data row that will be inserted, and old indicates the data row to be deleted

Delete trigger: Drop Tgigger tg_before_insert_student

Triggers cannot be called directly by the user, but are known to be passively triggered by the "Add/delete/change" operation on the table.

Stored Procedures

A stored procedure is a collection of SQL statements in which the internal SQL statements are executed logically when the stored procedure is actively invoked.

--Create Stored procedure delimiter//create procedure P1 () BEGIN select * from T1; End//delimiter;--Execute Stored procedure call P1 ()

For stored procedures, parameters can be accepted and have three types of parameters:

In only for incoming parameters

Out for return values

InOut that can be passed in and can be used as return values

Stored Procedures with parameters:

--Create Stored procedure delimiter \create procedure P1 (in I1 int, in I2 int, inout i3 int, out R1 int) 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 set @t1 =4;set @t2 = 0; Call P1 (1, 2, @t1, @t2); SELECT @t1, @t2;

Result set plus return value:

delimiter //                     CREATE PROCEDURE P2 (                         in n1  int,                         inout n3 int,                          out n2 int,                     )                      begin                         declare temp1 int ;                          declare temp2 int default 0;                         select  * from v1;                         set n2 = n1 + 100;                          set n3 = n3 + n1 + 100;                     end  //                    delimiter  ;

Transactional Stored procedure:

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;                              INSERT INTO TB2 (name) VALUES (' seven ');                            commit;                                                       -- SUCCESS                            set p_return_code =  0;                                                      end\                      delimiter ;

cursor:

delimiter //                     CREATE PROCEDURE P3 ()                      begin                           declare ssid int; --  Custom Variable 1                            Declare ssname varchar (; -- ) custom variable 2                            declare done int default false;                         declare my_cursor cursor for select  sid,sname from student;                         DECLARE CONTINUE  handler for not found set done = true;                                                   open my_cursor;                              xxoo: LOOP                    &nbSp;            fetch my_cursor into  ssid,ssname;                                 if  done then                                       leave xxoo;                                  END IF;                                  Insert into teacher(tname)  values (ssname);                             end loop  xxoo;                         close my_cursor;                     end  //                      delimter ;

dynamic Execution sql:

delimiter \                     CREATE PROCEDURE p4  (                         in  nid int                     )                      BEGIN                         prepare prod from   ' select * from student where sid > ? ';                          execute prod&nbsP using  @nid;                         DEALLOCATE prepare prod;                       end\                     delimiter ;

Delete stored procedure: drop procedure Proc_name;

To execute a stored procedure:

--Parametric call Proc_name ()-parameter, full incall proc_name-parameters, In,out,inoutset @t1 =0;set @t2 =3;call proc_name (@t1, @t2)

Function

Functions in MySQL are divided into: built-in functions, custom functions

Built-in function reference:

Http://doc.mysql.cn/mysql5/refman-5.1-zh.html-chapter/functions.html#encryption-functions

Custom functions:

Delimiter \create function F1 (i1 int, i2 int) returns intbegin declare num int;    Set num = I1 + i2; return (NUM); END \delimiter;

MySQL view, stored procedures, triggers, functions, transactions, dynamic Execution sql