Week10_day4 (MySQL Advanced)

First, the View

1. What is a view?

The view is a virtual table (non-real), the essence of which is to get a dynamic dataset based on the SQL statement and name it, when the user uses, just use the name to get the result set, and can be used as a table (in fact, the simple point, is a temporary table, put in memory)

　　2. Creation of views
format:CREATE view name as SQL statement

　　　　

　3. Deletion of views

　　　　format:drop view Name

　　4. Changes to the view

　　　　format:ALTER VIEW name as SQL statement

　　　　

　　5, how to use the view?

　　　　format: 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.

　　　　

Second, Trigger

　　1. What is a trigger?

Before and after an "Add/delete/change" operation on a table you can use triggers when you want to trigger a particular behavior, which is used to customize the behavior of the user before and after the "Add/delete/change" row of the table.

　　2. Creation of triggers

# 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

Example:

(i), pre-insertion trigger

Delimiter//create TRIGGER tri_before_insert_tb1 before insert on TB1 for each rowbeginif NEW. name = = ' Alex ' then    INSERT into TB2 (name) VALUES    (' AA ') Endend//delimiter;

(ii), post-insertion trigger

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            (' 666 '),            (' 666 ');    ELSEIF NEW. num = 555 Then        inserts into TB2 (NAME)        VALUES            (' 555 '),            (' 555 ');    END IF; End//delimiter;

　　in particular: New represents the data row that is about to be inserted, and old represents the data row that is about to be deleted.

　　3, the deletion of triggers

　　　　format:drop TRIGGER Trigger name

Drop trigger tri_after_insert_tb1;

　　4, the use of triggers

　　　　NOTE: 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 (num) VALUES (666)

Third, stored procedures

　　1. What is a stored procedure?

A stored procedure is a collection of SQL statements in which the internal SQL statements are executed logically when the stored procedure is actively invoked.

　　2. Creation of stored procedures

(i), parameter-free stored procedures

　　　　　　

So the question is, how to use it?

　　　　　　

(ii) stored procedures with parameters

For stored procedures, you can receive parameters with three types of parameters:

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

　　　　　　Creation of stored procedures with parameters

　　　　　　

　　　　　　Executing stored procedures

　　　　　　

　　3. Deletion of stored procedures

　　　format:drop procedure Stored procedure name

　　　　

　4, the execution of the stored procedure　

--Parametric call Proc_name ()-parameter, full incall proc_name-parameters, In,out,inoutset @t1 =0;set @t2 =3;call proc_name (@t1, @t2)

　　So how do you execute stored procedures in Python?

#!/usr/bin/env python#-*-coding:utf-8-*-import pymysqlconn = pymysql.connect (host= ' 127.0.0.1 ', port=3306, user= ' root ', passwd= ' 123 ', db= ' t1 ') cursor = Conn.cursor (cursor=pymysql.cursors.dictcursor) # Execute Stored procedure cursor.callproc (' P1 ', args= ( 1, 22, 3, 4) # Gets the stored parameter cursor.execute ("select @_p1_0,@_p1_1,@_p1_2,@_p1_3") result = Cursor.fetchall () conn.commit () Cursor.close () Conn.close () print (result)

Iv. functions

　1. Some built-in functions

 The Char_length (str) return value is 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 '

　　2. Custom Functions

　　　　

3. Delete function

　　　　

4. Execution function

　 　　

V. Business

　　1. What is a 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.

　　2. Creation of transactions

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;    Insert into TB2 (name) VALUES (' seven ');  COMMIT;    --SUCCESS   Set p_return_code = 0;    End\delimiter;

Vi. Index

　　1. What is an index?

Index is a data structure in the database that is designed to help users quickly query data. Similar to a directory in a dictionary, you can find the contents of the dictionary based on the directory to locate the data, and then get directly.

                 30 10 40 5 15 35 66 1 6 11 19 21 39 55 100

　　2. What are the common indexes in MySQL?

(i), General index

The normal index has only one function: Accelerated query

　　　　　　

Add an index after the build table is complete

　　　　　　　　

Delete Index

　　　　　　　　

View Index

　　　　Note: When creating an index, if it is a blob and text type, you must specify the length

　　　　

(ii), unique index

Unique index has two functions: Accelerated Query and UNIQUE constraint (can contain null)

Create an index when creating a table

　　　　　　　　

Create a unique index

　　　　　　　　　　　　　　　

To delete a unique index

　　　　　　　　　　　　　　

(iii), PRIMARY key index

Create a primary key when creating a table

　　　　　　　　　

Create a table and then create a primary key first

　　　　　　　　

Delete primary key

　　　　　　　　

　　　　　　　　

(iv), combined index

A composite index is the combination of n columns into one index

The application scenario is: Frequent simultaneous use of n columns for querying, such as: where N1 = ' Alex ' and N2 = 666.

Create an index

　　　　　　　　

　　　　　　

After creating the composite index, query:

Name and email--Using the index

Name--Using the index

Email-Don't use index

　　　　　　

Note: The performance of a composite index is better than multiple single-index merges for simultaneous search of n conditions

Vii. Other

　　1. Conditional statements

　　　　

　　2. Circular statements

(i), while loop

　　　　　　

(ii), repeat cycle

　　　　　　

(iii), loop loop

　　　　　　

　　3. Dynamic execution of SQL statements

　　　　

Week10_day4 (MySQL Advanced)