MySQL Summary Chapter 2 (17.6.27)

First, the View

1. What is a view, function;

A: Get a select result, and generate a new virtual table, and then you can continue to work on this new table;

Note: When the old table data is updated, the virtual table data is updated as well;

　　

2. Create

DROP VIEW IF EXISTS V1; Represents a V1 virtual table deletion if it exists.

V1: Custom table name

Execute SELECT statement in parentheses

　　

DROP viewif EXISTS v1; CREATE VIEW v1 as (SELECT * from Tb1 WHERE nid < 3)

3. Working with views

When we create a V1 view, we can work on the V1, because V1 is a virtual table, so the contents of the table can not be deleted or modified, can only query.

SELECT * FROM v1 where name = ' Mary '

4. Deleting a virtual table

Drop View V1

5. Modifications

The amendment is too troublesome to delete the rewrite;

Second, the stored procedure

1. Introduction:

The complex SQL statement to package packaging, call convenient (is a set of SQL statements, you can implement some more complex logic functions);

2. Create:

DROP PROCEDURE  IF EXISTS proc_p1;    #如果存在删除该存储过程delimiter \ \                  #更改结束符 CREATE PROCEDURE proc_p1 (in M1 int,out m2 int, INOUT m3 int in out              inout< c6/> himself very confused ) BEGIN                                #函数体 a   variety of judging conditions to enter DECLARE N1 int;D eclare n2 int DEFAULT, IF m1 = 1 then SET n1 = m2 ; ELSEIF m1 = 2 then SET N1 =m3; ELSE SET n1 = 0; END IF; SELECT N1; end\\                                  #结束记着要加 \ delimiter;                            #恢复结束符

3. Enquiry

A.navicat for MySQL

Set @m =;    #设定参数set @n = 10;call proc_p1 (1,@m,@n);   #执行存储过程SELECT @m      #查询参数

B.python not clear .

Import pymysql# Create Connection conn = Pymysql.connect (host= ' 127.0.0.1 ', port=3306,user= ' root ', password= ' Www123 ... ', db= "6.27db" ) #创建游标cursor = Conn.cursor (cursor=pymysql.cursors.dictcursor) cursor.callproc (' Proc_p1 ', ()) ret = Cursor.execute ("select @_proc_p1_0,@_proc_p1_1,@_proc_p1_2") result = Cursor.fetchone () conn.commit () Cursor.close () Conn.close () print (Ret,result,)

Third, Trigger

1. Introduction:

Before and after the operation of a table (increment, delete, change), the other table will also change;

2. Create

Delimiter $    #设置终止符号drop trigger if EXISTS tri_brfore_inset_tb1 $    #判断是否已经存在 "" "# Set the pre-insertion mechanism  1.tri_brfore_inset _TB1   take a name; the 2.before description    is before or after the operation, the 3.insert     description is inserted or deleted, obviously this example is inserted; 4. No tb1   indicates that only tb1 change will occur; 5.began     This means that if tb1 changes, the other table is changed and what is changed; CREATE TRIGGER tri_brfore_inset_tb1 before INSERT on tb1 for each ROW  BEGIN INSERT into TB2 (favorate) VALUES (new.name); End $                                        #对应的是begin    $ means the delimiter is terminated    ;      #恢复了终止符号为;                              

3. Other

A.nwe can specify that the follower variable value is the latest value inserted by the owner;

B.old can specify that the follower variable value is the value that the owner just deleted;

　　

Iv. functions

Create a function

Delimiter \ \      #. Drop function if exists f1\\   #:  Create  function f1 (n int, m int) returns INT   #创建函数 set parameter, and the type of return value begindeclare num int; set num = n + m; return num ;              #指定返回值enddelimiter;

Execute function

Select F1

Five, built-in functions

。。。

VI. Things

1. What is

A: The function is the same as the try function in Python and is not executed when an error occurs.

2. Create

delimiter \Create procedureP1 (out P_return_codetinyint)begin   Declare ExitHandler forSqlExceptionbegin     --Error    SetP_return_code= 1; rollback; End; Declare ExitHandler forsqlwarningbegin     --Warning    SetP_return_code= 2; rollback; End; # StartTransaction; UpdateTb1SetTelephone=Telephone+ 5 where' Name '= 'Coob1'; UpdateTb2SetFavorate=Favorate- 5 whereNid< -; Commit; --Success  SetP_return_code= 0; End\delimiter;

Example

3. In the Py_mysql pymysql module has been the default execution error, when the SQL statement errors, automatic error.

VII. MySQL Dynamic statements

MySQL Summary Chapter 2 (17.6.27)