MySQL (ii)

MYSQL (ii)

The previous article is about the basic MySQL operation, this one will be a little difficult to understand, this section of the main content of MySQL view, stored procedures, functions, transactions, triggers, and dynamic execution of SQL

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 SQL statement CREATE VIEW v1 as Selet nid, name from Tab1 WHERE nid > 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 = C.nidwhere tab1.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.

SELECT * 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:

--Create a stored procedure delimiter//        --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    select * from Tab1; End//delimiter;         --Custom local end symbol end-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 Stored procedure delimiter \create procedure P1 (    in I1 int,                        --Incoming parameter I1 in    i2 int,--                        incoming parameter i2    inout i3 int,                     - -that is, pass in and get the return value out    R1 int                        --To get the return value) BEGIN    DECLARE Temp1 int;    DECLARE Temp2 int 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 the default value of the variable to 3DECLARE @t2 INT;                     --  Set the variable call P1 (1, 2, @t1, @t2);            --  executes the stored procedure and passes in the parameter, T2 automatically cancels the select @t1, @t2;                      --  view stored procedure output results

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 ', (a)) # query result of stored procedure SELC = Cursor.fetchall () print (SELC) # Get stored procedure return Effect_row = Cursor.execute (' select @_p1_0,@_p1_1,@_p1_2 ') # The return value of the Koji stored procedure ret = Cursor.fetchone () print (ret) # commits, otherwise 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 (    i1 int,    i2 int) returns intbegin    declare num int;    Set num = I1 + i2;    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 (11,nid) in the query, name from TB2;

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 handler 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;                   --The SQL statements are placed inside this    insert into TB2 (name) VALUES (' seven ');  COMMIT;    --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 = Zhang Yan forest into the TAB2 to insert the data into the TAB1, of course, in determining whether the name inserted into TAB1 is equal to Aylindelimiter//create TRIGGER tri_before_insert_tb1 Before INSERT on tb1 for each rowbeginif NEW. Name = = ' Aylin ' then    INSERT into TB2 (name) VALUES    (' Zhang Yan Forest ') 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        inser T 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!!

-this article reprinted-reprinted from: http://www.cnblogs.com/aylin/p/5746501.html

MySQL (ii)