MYSQL (ii)

Views View

A view is a virtual table whose contents are defined by a query. As with a real table, a view contains a series of column and row data with names. However, the view does not exist in the database as a stored set of data values. Row and column data is derived from the table referenced by the query that defines the view, and is generated dynamically when the view is referenced. The view acts like a filter for the underlying table referenced in it. A filter that defines a view can come from one or more tables or other views of the current or other database. There are no restrictions on querying through views, and there are few restrictions on data modification through them. A view is an SQL statement of a query stored in a database, and it is primarily for two reasons: security reasons, and views can hide some data.

1. Create a View

-- format: CREATE view name as  4 

2. Delete View

-- format: Drop view Name drop View V1

3. Modify the View

-- format: ALTER VIEW name as SQL statement alter VIEW v1 asselet a.nid,b. Name from Tab1left join B on a.id = B.nidleft Join C On a.id =2  

Also just changed the create to alter, the middle of the statement replaced.

4. Using views

When you use a view, you manipulate it as a table, and because the view is a virtual table, you cannot use it to create, update, and delete real tables, only for queries.

From v1

Stored Procedure procedure

1. Why should we use stored procedures?

We all know that there are two kinds of applications, one is web-based, the other is desktop-based, and they interact with the database to complete the data access work. Assuming that there is now an application that contains both, now to modify one of the query SQL statements, then we may want to modify their corresponding query SQL statement, when our application is very large and complex problem arises this, difficult to maintain! Also, placing SQL query statements on our web programs or desktops can easily be compromised by SQL injection. And the storage routines just can help us solve these problems.

2. Create a stored procedure

Create a stored procedure there are two main types, one with parameters, one without parameters, and without parameters.

Case with no parameters:

----Custom        statement end symbol, because there are a lot of SQL statements to execute, so you have to customize to prevent errors create PROCEDURE P1 () BEGIN from      TAB1; End//delimiter;--custom local end symbol ends-- execute stored procedure call P1 ()       

With a parameter case this block has three main classes

• In only for incoming parameters
• Out is used only for return values
• InOut can be passed in and can be used as a return value

--Create a stored procedure delimiter \create procedure P1 (In I1int,--Incoming parameter I1In I2int,--Incoming parameter i2 inout i3int,--That is, you can get the return value.Out R1INT--Get return value) BEGIN DECLARE Temp1Int; DECLARE Temp2int default 0; set temp1 = 1 set r1 = i1 + i2 + temp1 + Temp2; set i3 = i3 + 100;end\ delimiter;-- Execute stored procedure declare @t1 INT default 3; -- Set variable default value to 3DECLARE @t2 INT;-- set Variable call P1 (1, 2, @t1, @t2); -- Execute stored procedure and pass in parameters, T2 automatically cancel select @t1, @t2;--View stored procedure output     

2. Delete stored Procedures

drop procedure P1;

3. Python calls the stored procedure with the Pymysql module, because we are learning to call the language

#!/usr/bin/Env python#-*-coding:utf-8-*-Import Pymysqlconn = Pymysql.connect (host=‘127.0.0.1', port=3306, user=‘Root', passwd=", db= "day39b_ " cursor = Conn.cursor (cursor=pymysql.cursors.dictcursor) # Execute stored Procedure row = Cursor.callproc ( ' p1 "( Span style= "COLOR: #800080" >1,2,3 ) # The query result of the stored procedure SELC = Cursor.fetchall () print (SELC) # Get stored procedure returns Effect_row = Cursor.execute ( ' select @_p1_0,@_p1 _1,@_p1_2 ") # The return value of the curve stored procedure ret =  Cursor.fetchone () print (ret) # commits, or cannot save new or modified Data conn.commit () # Close Cursor cursor.close () # Close Connection conn.close ()               

Functional function

In MySQL there are many built-in functions, such as we often use the average, sum, number, a variety of, first give you a department built-in function, and then say the Custom function bar, function can also pass parameters, can also receive the return value, but the function is not able to get the result of executing the statement, Stored procedures can.

built-in functions

For more information, please refer to the Chinese document Http://doc.mysql.cn/mysql5/refman-5.1-zh.html-chapter/functions.html#encryption-functions

1. Custom Create function

Delimiter \create function F1 (int, int. int int        Set num = I1 +return(num); END \delimiter;       

2. Delete function

Drop function F1;

3. Execution function

# Get return value declare @i VARCHAR (+);  Select UPPER ('Alex') into @i; Select @i;# uses select F1 in queries (fromTB2;        

Transaction

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. For example: When the two bank cards to transfer, the party's money to turn out, suddenly the optical cable is broken, party B has not received money, where the money ran, in order to prevent this situation, the business is out, business can prevent this kind of thing to happen.

To apply a transaction instance:

Delimiter \create PROCEDURE p1 (out P_return_code tinyint) BEGIN DECLARE exit HandlerForSqlException BEGIN--ERRORset 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;-- SQL statements are placed inside this insert into TB2 (name) VALUES ( ' seven " SUCCESS set P_return_code = 0; End\delimiter                

To execute a stored procedure:

DECLARE @i tinyint;call p1 (@i); Select @i;

Trigger Trigger

A trigger, in short, is when you trigger an Add or delete change before or after executing this statement, and the trigger is used to customize the behavior of the user before and after the "increment/delete/change" row of the table.

1. Basic grammar

# 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

Case one before inserting:

--Insert a name =//' Aylin ' then insert into tb2 (' Zhang Yan Forest ') in the go to TAB2 to insert data into TAB1 ') endend//delimiter;         

Case two after inserting:

Delimiter//CREATE TRIGGER tri_after_insert_tb1 after insert on TB1 for each rowbegin IF NEW.  num =666 then inserts into TB2 (NAME) VALUES (' Zhang Yan Forest '), (' very handsome '); ELSEIF NEW. num = 555 then inserts into TB2 (NAME) VALUES ('aylin'), (' very handsome '); END IF; END//delimiter;                 

The same deletion, change, check are the same reason

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.

2. Delete Trigger

DROP TRIGGER tri_after_insert_tb1;

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.

Insert into TB1 (name) values (' Zhang Yan Forest ')

The next chapter to update the database index, this aspect of things more, so the landlord decided to write him a new blog, read the Remember points praise yo!!

MYSQL (ii)