Python Learning _day48_mysql Other supplements

First, the 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 you can use as a table by simply using "name" to get a result set. Using views we can take out the temporary table in the query process, and use the view to implement, so that later on to manipulate the temporary table data without rewriting the complex SQL, directly to the view to find, but the view has obvious efficiency problems, and the view is stored in the database, If the SQL used in our program relies too much on the views in the database, which is strongly coupled, it means that the extension of SQL is extremely inconvenient and therefore not recommended.

1. Create a View

Create  view name as SQL statement

Note that this is the statement that created the view, but the SQL statement in the statement cannot contain subqueries. Examples are as follows:

You can save the result of the SQL query statement as a virtual table in view by creating a view, and you can avoid the duplicate SQL statement the next time you use it again:

2. Changes to the view

As described in the view concept, the view is a virtual table of SQL statement query results, its real data is the actual table involved in the source SQL statement, when the SQL statement is associated with only one table, change the data in the view, the data in the associated table will also change, obviously this is unreasonable, When you associate multiple tables in an SQL statement, you cannot implement changes to the data in the view at all. In summary, you cannot change the contents of a view by using a statement such as update or insert. If you want to really implement the changes to the view, you are actually changing the SQL statements in the original views, the following syntax is changed:

3. Delete View

Drop View name      # Example: Drop View Course_teacher

Summary: The use of the view will not need to rewrite the subquery every time the SQL, but this is not high efficiency, it is not as efficient as we write sub-query, the second view is stored in the database, if the SQL in our program is too dependent on the view stored in the database, it means that Once SQL needs to be modified and involves parts of the view, it must be modified in the database.

Second, Trigger

Use triggers to customize the behavior of the user in the "Add, delete, change" operation of the table, note: No query

1. Create TRIGGER syntax

#before insertingCREATE TRIGGER tri_before_insert_tb1 before insert on TB1 for each rowbegin ... END#after insertingCREATE TRIGGER tri_after_insert_tb1 after insert on TB1 for each rowbegin ... END#before deletingCREATE TRIGGER tri_before_delete_tb1 before delete on tb1 for each rowbegin ... END#after deletionCREATE TRIGGER tri_after_delete_tb1 after delete on tb1 for each rowbegin ... END#before updateCREATE TRIGGER tri_before_update_tb1 before update on TB1 for each rowbegin ... END#after the updateCREATE TRIGGER tri_after_update_tb1 after update on TB1 for each rowbegin ... END

2. Trigger instance

#Prepare tableCREATE TABLE cmd (id INT PRIMARY KEY auto_increment, USER CHAR (32), Priv CHAR (10), cmd CHAR (64), Sub_time datetime,#Time of submissionSuccess Enum ('Yes','No')#0 represents execution failure); CREATE TABLE errlog (id INT PRIMARY KEY auto_increment, Err_cmd CHAR (64), Err_time datetime);#Create a triggerDelimiter//CREATE TRIGGER tri_after_insert_cmd after insert in cmd for each rowbegin IF new.success='No'Then#equivalence judgment has only one equal signINSERT into ErrLog (Err_cmd, Err_time) VALUES (New.cmd, new.sub_time);#You must add a semicolonEND IF;#You must add a semicolonend//delimiter;#inserting records into table cmd, triggering triggers, deciding whether to insert error logs based on if conditionsINSERT into cmd (USER, Priv, CMD, sub_time, Success) VALUES ('Egon','0755','ls-l/etc', now (),'Yes'),    ('Egon','0755','cat/etc/passwd', now (),'No'),    ('Egon','0755','useradd xxx', now (),'No'),    ('Egon','0755','PS aux', now (),'Yes');#Query error log, found that there are twoMysql> SELECT * fromErrLog;+----+-----------------+---------------------+| ID | Err_cmd |  Err_time |+----+-----------------+---------------------+| 1 | cat/etc/passwd |  2017-09-14 22:18:48 | | 2 | Useradd XXX | 2017-09-14 22:18:48 |+----+-----------------+---------------------+rowsinchSet (0.00 sec)

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

3, the deletion of triggers

Triggers cannot be called directly by the user, but are caused passively by the "Add/delete/change" operation of the table. The syntax for deleting triggers is as follows:

Drop trigger trigger name;  # Example: Drop trigger Tri_after_insert_cmd;

Iii. Business

Transactions are used to manipulate multiple SQL for some operations as atomic, and once an error occurs, it can be rolled back to its original state, guaranteeing database data integrity.

CREATE TABLE user (ID int primary key auto_increment,name char (32), balance int), insert into user (Name,balance) VALUES ('WSB', 1000),('Egon', 1000),('YSB', 1000);#atomic operation, no exception in executionstart transaction;update user set balance=900 where name='WSB'; Update user set balance=1010 where name='Egon'; Update user set balance=1090 where name='YSB';commit;#exception occurred, rollback to initial statestart transaction;update user set balance=800 where name='WSB'; Update user set balance=1020 where name='Egon'; Uppdate user set balance=1180 where name='YSB';rollback;
Commit

Iv. Stored Procedures

The stored procedure contains a series of executable SQL statements that are stored in MySQL, and can be executed by invoking its name in a heap of SQL within it. Advantages of using Stored procedures: (1) to replace the SQL statements written by the program, the implementation of the program and SQL decoupling, (2) based on the network transmission, the data of the alias is small, and the direct transfer of SQL data is large. The disadvantage is that the programmer is not easy to extend functionality.

1. How to use the database and program

# Way One:     mysql: Stored procedure    Program: Call stored procedure # Way two:    MySQL:    program: Pure SQL statement # Way Three:     MySQL:    Programs: Classes and objects, i.e. ORM (essentially or purely SQL statements)

2. Creating a parameter-free stored procedure

# Create a non-parametric stored procedure delimiter $$                             # symbol is not limited to this, but need to be consistent before and after create procedure P1 () Begin    1;      while (i<10)        does from  course;        Set I=i+1;      while ; end $ $delimiter; # calling the stored procedure call P1 ();

3. Create a parameter stored procedure

In the case of parameters, the type of the parameter must be defined in the definition stored procedure, consisting of three types, as explained below:

#           in only used for incoming parameters with #out         only for return values with #inout       can both be passed in and can be used as return values

Application Examples:

#creating a parameter stored proceduredelimiter $ $create procedure P2 (inchX char (5), out y int) begin SELECT CID fromCourse where cname=x; Set Y=1; end $ $delimiter;#mysql中调用:Set @x='Biological'; set @y=0;call P2 (@x,@y);#View return ValuesSelect @y;#pymysql中的调用:ImportPymysqlconn=Pymysql.connect (Host='localhost', Port=3306, the user='Root', Password="', Database='day48', CharSet='UTF8') cur=conn.cursor ()#get the execution result of the stored procedure SQL statementCur.callproc ('P2',('Biological', 0))Print(Cur.fetchall ())
#get the stored procedure return value" "pymysql Default will change the parameters to the following form of name, respectively, the name of the mob and 0: @_p2_0, @_p2_1;" "Cur.execute ('Select @_p2_1;')Print(Cur.fetchone ()) Cur.close () Conn.close ( )

Instance with transaction stored procedure:

#IntroductionDelimiter//CREATE PROCEDURE P4 (out status int) BEGIN1declares that the {set status is executed if an exception occurs.= 1;                Rollback } Start Transaction--from fought account minus--Fang Shaowei account plus--Zhanggen account plus ten commits; End Set Status= 2; END//delimiter;#ImplementDelimiter//Create PROCEDURE P5 (out P_return_code tinyint) BEGIN DECLARE exit Handler forSqlException BEGIN--ERROR Set P_return_code= 1;     Rollback     END; DECLARE Exit Handler forsqlwarning BEGIN--WARNING Set P_return_code= 2;     Rollback     END;         START TRANSACTION; DELETE fromTB1;#Execution failedINSERT into Blog (name,sub_time) VALUES ('yyy', now ());     COMMIT; --SUCCESS Set P_return_code= 0;#0 on behalf of successful executionEND//delimiter;#calling a stored procedure in MySQLSet @res =123; call P5 (@res); select @res;#calling stored procedures in Python based on PymysqlCursor.callproc ('P5', (123,))Print(Cursor.fetchall ())#query Query results for selectCursor.execute ('Select @_p5_0;')Print(Cursor.fetchall ())

4. Delete stored Procedures

drop procedure Proc_name;

Python Learning _day48_mysql Other supplements