Mysql (ii)

View

A view is a virtual table that is used only when the user uses the view to get the dataset based on the SQL statement.

A view can be used directly as a data table, but not as a table of real significance.

Views can only be used at query time, and the actual table cannot be added and manipulated.

1. Create a View

Format:

CREATE VIEW  as SQL statements

2. Modify the View

Format:

ALTER VIEW  as SQL statements

3. Delete a view

Format:

DROP  View Name

4. Working with views

SELECT *  from view name

Stored Procedures

A stored procedure is equivalent to a function in a program language, can pass in a parameter, or can have a return value. When a user actively invokes a stored procedure, the SQL statement defined in the stored procedure is executed logically.

1. Create a stored procedure

There are three types of parameters that a stored procedure can receive:

in: for incoming parameters

out: for return value

inout: Both parameters can be passed in and return a value

No parameter stored procedure:

1 --create a stored procedure2 3Delimiter// # delimiter for modifying SQL statement default Terminator 4 Create procedureP1 ()5 BEGIN6     Select *  fromT1;7 END//8 delimiter; # Change the SQL statement terminator back to the default 9 Ten  One  A --Executing stored procedures -  -Call P1 ()# Call a stored procedure

Stored procedure with parameters:

1 --create a stored procedure2 delimiter \3 Create procedureP1 (4     inchI1int, # I1,I2 is used to receive incoming arguments 5     inchI2int,6InOut i3int,7Out R1Int # r1 pass in any value, default is null 8 )9 BEGINTen     DECLARETemp1int; # Declare declaring variable types One     DECLARETemp2int default 0; # Declare the variable type and give the default value  A      -     SetTemp1= 1; -  the     SetR1=I1+I2+Temp1+Temp2; -      -     Seti3=i3+  -; -  + End\ - delimiter; +  A --Executing stored procedures at Set @t1 =4; - Set @t2 = 0; -Call P1 (1,2,@t1,@t2);  - SELECT @t1,@t2;

2. Delete a stored procedure

DROP PROCEDURE Stored procedure name

3. Execute the Stored procedure

1 --No Parameters2 Call proc_name ()3 4 --with parameters, full in5Call Proc_name (1,2)6 7 --There are parameters, there are in,out,inout8 Set @t1=0;9 Set @t2=3;TenCall Proc_name (1,2,@t1,@t2)

4.pymysql Executing stored procedures

ImportPymysqlconn= Pymysql.connect (host='127.0.0.1', port=3306, user='Root', passwd='123', db='T1', charset='UTF8') Cursor= Conn.cursor (cursor=pymysql.cursors.DictCursor)#Executing stored proceduresCursor.callproc ('P1', args= (1, 22, 3, 4)) RET=Cursor.fetchall () # Get data set Print(ret)#gets the parameters of the store after executionCursor.execute ("Select @_p1_0,@_p1_1,@_p1_2,@_p1_3") Result=Cursor.fetchall () # Gets the value of the parameter that executes the stored procedure conn.commit () cursor.close () conn.close ()Print(Result)

Trigger

Before/After a table is modified, you can use a trigger to do so if you want to start other behaviors.

1. Create a Trigger

Format:

Trigger before insertion and after insertion:

CREATE TRIGGER before INSERT  on  for Each ROW BEGIN     ... END

CREATE TRIGGER  After INSERT  on  for Each ROW BEGIN     ... END

Delete and modify trigger the same as above, for example: Delete on ... , Update on:

For example:

New represents the data row that is about to be inserted, and old represents the data row that is about to be deleted.

Delimiter//CREATE TRIGGERTRI_AFTER_INSERT_TB1 afterINSERT  onTb1 forEach ROWBEGIN    IFNEW. Num= 666  Then        INSERT  intoTB2 (NAME)VALUES            ('666'),            ('666') ; ELSEIF NEW. Num= 555  Then        INSERT  intoTB2 (NAME)VALUES            ('555'),            ('555') ; END IF;END//delimiter;

2. Delete a trigger

DROP trigger name;

3. Using triggers

Triggers cannot be called directly by the user, but are known to be passively triggered by the "Add/delete/change" operation on the table. When you delete an entire table, each time you delete a single piece of data, it fires once.

Insert  into Values (666)

Execute SQL statements Dynamically

Mysql (ii)