MySQL Series (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 functions, and then say the custom function bar, function can also pass parameters, can also receive the return value, but the function can not get the result of executing the statement, stored procedure.

The built-in function char_length (str) returns the length of the string str, and the length of the unit is a character.        A multibyte character counts as a single character.    For one containing five two-byte character sets, the LENGTH () return value is 10, and the return value of Char_length () is 5.        CONCAT (STR1,STR2,...)    string concatenation if any one of the arguments is NULL, the return value is null.        Concat_ws (SEPARATOR,STR1,STR2,...) string concatenation (custom connector) Concat_ws () does not ignore any empty strings.    (All NULL is ignored, however). CONV (n,from_base,to_base) binary conversion For example: SELECT CONV (' A ', 16,2); Represents the conversion of a from 16 to a 2 binary string representation of format (X,D) that formats the number X as ' #,###,###.## ', retains the D-bit after the decimal point in a rounded manner, and returns the result as a string.        If D is 0, the result is returned without a decimal point, or with no fractional part. For example: SELECT FORMAT (12332.1,4);            The result is: ' 12,332.1000 ' Insert (STR,POS,LEN,NEWSTR) inserts the string at the specified position in str pos: to replace the position in the location Len: the length of the replacement        NEWSTR: New String Special: If the Pos exceeds the original string length, the original string is returned if Len exceeds the original string length, then the new string is completely replaced by the INSTR (STR,SUBSTR)    Returns the first occurrence of a substring of string str.    Left (Str,len) returns the substring character of the string Str from the beginning of the Len position.  LOWER (str) to lowercase UPPER (str) to uppercase LTRIM (str) returns the string str, whose boot space character is deleted.  RTRIM (str) returns the string str, and the trailing space character is deleted. SUBSTRING (Str,pos,len) Gets the string subsequence LOCATE (substr,str,pos) Gets the sub-sequence index position REPEAT (str,count) returns a duplicate string s        TR consists of a string of string str with a number equal to count.  If Count<= 0, an empty string is returned.    If STR or count is NULL, NULL is returned.    Replace (STR,FROM_STR,TO_STR) returns the string str and any string from_str that are substituted by the string to_str.    REVERSE (str) returns the string str, in reverse order and character order.    Right (Str,len) starts with the string str, and returns a subsequence space (n), which is a string consisting of n spaces, starting at the back of Len characters. SUBSTRING (Str,pos), SUBSTRING (str from POS) SUBSTRING (Str,pos,len), SUBSTRING (str from POS for len) format without len parameter from The string str returns a substring starting at position pos. The format with the Len parameter returns a substring of the same length as the Len character from the string str, starting at position pos.
Use the from format as standard SQL syntax. You may also use a negative value for the POS. If so, the position of the substring starts at the POS character at the end of the string, not at the beginning of the string.        You can use a negative value for the POS in the following format function. MySQL> SELECT SUBSTRING (' quadratically ', 5);            ' ratically ' mysql> SELECT SUBSTRING (' Foobarbar ' from 4);            ' Barbar ' mysql> SELECT SUBSTRING (' quadratically ', 5,6);            ' Ratica ' mysql> SELECT SUBSTRING (' Sakila ',-3);            ' ila ' mysql> SELECT SUBSTRING (' Sakila ',-5, 3);            ' Aki ' mysql> SELECT SUBSTRING (' Sakila ' FROM-4 for 2); ' Ki ' TRIM ([{BOTH | Leading | TRAILING} [REMSTR] from] str) TRIM (remstr from] str) returns the string str, where all remstr prefixes and/or suffixes have been deleted. If none of the classifier both, leadin, or trailing is given, it is assumed to be both.        REMSTR is optional and can be removed without specifying a space.                mysql> SELECT TRIM (' Bar ');                ' Bar ' mysql> SELECT TRIM (leading ' x ' from ' xxxbarxxx ');                ' Barxxx ' mysql> SELECT TRIM (BOTH ' x ' from ' xxxbarxxx ');                ' Bar ' mysql> SELECT TRIM (TRAILING ' xyz ' from ' barxxyz '); ' Barx '

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!!

MySQL Series (ii)