Python Day48 MySQL Supplement

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      #例: 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

# Insert before create TRIGGER tri_before_insert_tb1 before insert on TB1 for each rowbegin    ... end# after inserting create TRIGGER tri_after_insert_tb1 after insert on TB1 for each rowbegin    ... end# Delete before create TRIGGER tri_before_delete_tb1 before delete on tb1 for each rowbegin    ... end# Delete Create TRIGGER tri_after_delete_tb1 after delete on tb1 for each rowbegin    ... end# Update before the Create TRIGGER tri_before_update_tb1 before update on TB1 for each rowbegin    ... end# Update after the Create TRIGGER tri_after_update_tb1 after update on TB1 for each rowbegin    ... END

2. Trigger instance

#准备表CREATE TABLE cmd (id INT PRIMARY KEY auto_increment, USER char (+), Priv char (ten), cmd char (+), Su B_time datetime, #提交时间 success enum (' Yes ', ' no ') #0代表执行失败); CREATE TABLE errlog (id INT PRIMARY KEY auto_increment, Err_cmd CHAR (+), Err_time datetime); #创建触发器delimiter//            CREATE TRIGGER tri_after_insert_cmd after insert in cmd for each rowbegin IF new.success = "No" then #等值判断只有一个等号 INSERT into ErrLog (Err_cmd, Err_time) VALUES (New.cmd, new.sub_time); #必须加分号 END IF; #必须加分号END//delimiter; #往表cmd中插入记录 trigger trigger to insert the error log into cmd (USER, Priv, cmd, sub_time, Succe, depending on the IF condition) SS) 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 '), #查询错误日志, found two mysql> select * from errlog;+----+--- --------------+---------------------+| ID | Err_cmd | Err_time |+----+-----------------+---------------------+| 1 | cat/etc/passwd |  2017-09-14 22:18:48 | | 2 | Useradd XXX | 2017-09-14 22:18:48 |+----+-----------------+---------------------+rows in Set (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;  #例: 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 (+), balance int), insert into user (name,balance) VALUES (' WSB ', +), (' Egon ', +), (' YSB ', +), #原子操作, no exception in execution start 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; #出现异常, roll back to the initial state start 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

#方式一:    mysql: Stored procedure    Program: Call stored Procedure # Way two:    MySQL:    program: Pure SQL Statement # Way three:    MySQL:    Program: Class and Object, i.e. ORM (essentially or pure SQL statement)

2. Creating a parameter-free stored procedure

#创建无参存储过程delimiter $$                             #符号不局限于此, but need to be consistent before and after create procedure P1 () Begin    DECLARE i int default 1;    while (i<10) does        select * from course;        Set i=i+1;    End While;end $ $delimiter; #调用存储过程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 #inout       can be passed in and can be used as return values

Application Examples:

#创建有参存储过程delimiter $ $create Procedure P2 (    in x char (5), off    y int) begin    Select CID from course where cname=x;< C3/>set y=1;end $ $delimiter; #mysql中调用: Set @x= ' creature '; set @y=0;call P2 (@x,@y); #查看返回值select @y; #pymysql中的调用: Import Pymysqlconn=pymysql.connect (    host= ' localhost ',    port=3306,    user= ' root ',    password= ',    Database= ' day48 ',    charset= ' UTF8 ') cur=conn.cursor () #拿到存储过程sql语句的执行结果cur. Callproc (' P2 ', (' creature ', 0)) print ( Cur.fetchall ())
#拿到存储过程返回值 ' pymysql default will change the parameters to the following form names, respectively, the names of mobs and 0: @_p2_0, @_p2_1; Cur.execute (' select @_p2_1; ') Print (Cur.fetchone ()) Cur.close () Conn.close ()

Instance with transaction stored procedure:

#介绍delimiter//CREATE PROCEDURE P4 (out status int) BEGIN                    1. Declare that if an exception occurs, execute {set status = 1;                Rollback                    Start transaction--minus 100 from fought account--Fang Shaowei account plus 90                --Zhanggen account plus ten commits;                                            End Set status = 2; END//delimiter; #实现delimiter//create PROCEDURE P5 (out P_return_code tinyint) BEGIN DECLARE exit Handl         Er 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 blog (name,sub_time) VALUES (' yyy ', now ());     COMMIT; --SUCCESS Set P_return_cOde = 0; #0代表执行成功END//delimiter; #在mysql中调用存储过程set @res =123;call P5 (@res); select @res; Call stored procedure Cursor.callproc (' P5 ', (123,)) print (Cursor.fetchall ()) #查询select的查询结果cursor in Python based on Pymysql. Execute (' Select @_p5_0; ') Print (Cursor.fetchall ())

4. Delete stored Procedures

drop procedure Proc_name;

Python Day48 MySQL Supplement