MySQL Series (iii) Views, triggers, stored procedures, functions, transactions, indexes, statements that you do not know

Transferred from: http://www.cnblogs.com/suoning/p/5744849.html

The contents of this chapter:

• View, add/delete/change/Search
• Trigger, Add/delete/change/check
• Stored procedures, add/delete/change/check
• Stored procedure three kinds of parameters, pymysql how to use?
• function, increment/delete/change/check/return value
• Built-in functions
• Transaction
• Index!

First, the View

A view is a virtual table (non-real) that consists of a query command result, which is essentially "getting a dynamic dataset from a SQL statement and naming it", using the "name" to get the result collection and using it as a table.

1. Create a View

--Format: CREATE VIEW name  as SQL statement CREATE VIEW v1 as Selet nid,     namefrom    awhere    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. namefrom    Aleft Join B on a.id = B.nidleft Join C on a.ID = C.nidwhere    a.id > 2AND C.nid < 5

4. Using views

Views are used as normal tables, and because views are virtual tables, they cannot be created, updated, or deleted from their real tables, only for queries.

SELECT * FROM v1

Second, Trigger

A trigger is used before and after the "Add/delete/change" action on a table, and triggers are available to customize the behavior of the user before or after the "Add/delete/change" row of the table, if you want to trigger the action before or after the change or deletion.

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

2. Create a Trigger

The basic syntax for creating triggers is the following code:

But one thing to note is that the keyword new in the trigger represents the data row that is about to be inserted, and the old represents the data row that is about to be deleted.

# Insert pre-trigger delimiter//create TRIGGER tri_before_insert_tb1 before insert on TB1 for each rowbeginif NEW. Name = = ' Nick ' then    INSERT into TB2 (name) VALUES    (' AA ') Endend//delimiter;

# post-insert trigger delimiter//create TRIGGER tri_after_insert_tb1 after insert on TB1 for each rowbegin    IF NEW. num = 666 then
   insert into TB2 (NAME)        VALUES            (' 666 '),            (' 666 ');    ELSEIF NEW. num = 555 Then        inserts into TB2 (NAME)        VALUES            (' 555 '),            (' 555 ');    END IF; End//delimiter;

3. Delete Trigger

DROP TRIGGER tri_after_insert_tb1;

4. Using triggers

Triggers are executed passively when the table is added, deleted, and changed.

INSERT into TB1 (num) VALUES (666)

Third, stored procedures

A stored procedure is a collection of SQL statements, similar to a function that requires active invocation.

1. Create a stored procedure

# no parameter stored procedure # Create stored procedure delimiter//create procedure P1 () BEGIN    select * from T1; end//delimiter; # Execute stored procedure call P1 ()

All say a similar function, it must be able to receive parameters, and there are 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

# have parameter stored procedure # Create stored procedure delimiter \ \    # End semicolon to \create procedure P1 (in I1 int, i2 int,    inout i3 int., out    R1 int) BEGIN    DECLARE Temp1 int;    # Create Declaration local variable    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;declare @t2 int; Call P1 (1, 2, @t1, @t2); SELECT @t1, @t2;

2. Delete stored Procedures

drop procedure Proc_name;

3. Execute Stored Procedure

Executes the parentheses for the function name;

Declare represents creating a local variable

# parameterless Call Proc_name () # has parameters, full incall proc_name # with parameters, In,out,inoutdeclare @t1 int;declare @t2 INT default 3;call proc_na Me (@t1, @t2)

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

It's time to talk about real functions, unlike stored procedures, which have return values.

1. Custom Functions

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 Func_name;

3. Execution function

# Get return value declare @i VARCHAR (+), select UPPER (' Nick ') into @i; Select @i;# uses select F1 (11,nid) in the query, name from TB2;

4. Built-in functions

Of course, MySQL is no exception, with a very useful built-in function

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 '

More functions: Punch here OR bash here

V. Business

Transactions are used to operate multiple SQL statements for certain operations as atomic operations, and to roll back to their original state if an error occurs, guaranteeing database data integrity.

Delimiter for changing the end symbol, default ";"

# stored procedures that support 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;

# Execute stored procedure declare @i tinyint;call P1 (@i); select @i;

Vi. index 1, index overview

Index is the index directory of the table, look up the directory before looking at the index location, so as to quickly locate the query data;

Can be understood as the index in the Xinhua dictionary;

The index is saved in the extra file.

2. Type of index

General index types and functions:

• Normal index: Accelerated query only
• Unique index: Accelerated query + column value unique (can have null)
• Primary key index: Acceleration query + column Value unique + only one in table (cannot have null)
• Composite index: Multi-column values make up an index that is specifically used to combine searches that are more efficient than index merges
• Full-Text Indexing: Word segmentation for text content, search

Index Merge: Query search using multiple single-column index combinations
Overwrite index: The data column of select is only available from the index and does not have to read the data row, in other words the query column is overwritten by the index being built

A, normal index

# Creating table + index CREATE TABLE in1 (    nid int not NULL Auto_increment primary key,    name varchar (+) NOT NULL,    email VARC Har (+) not NULL,    extra text,    index Ix_name (name))

# CREATE INDEX index_name on table_name (column_name)

# Delete Index drop index_name on table_name;

# View index show index from TABLE_NAME;

#注意: If you are creating an index with a blob and TEXT type, you must specify length. Create INDEX Ix_extra on in1 (extra (32));

B, unique index

# Creating table + Unique index CREATE TABLE in1 (    nid int not NULL Auto_increment primary key,    name varchar (+) NOT NULL,    email VA Rchar (+) not NULL,    extra text,    unique ix_name (name))

# Creating a unique index create unique index name on table name (column name)

# Delete unique index drop unique index name on table name

C, primary key index

# CREATE TABLE + Create primary key created table in1 (    nid int not NULL Auto_increment primary key,    name varchar (+) NOT NULL,    email VA Rchar (+) not NULL,    extra text,    index Ix_name (name)) orcreate table in1 (    nid int not null AUTO_INCREMENT,
   name varchar (+) NOT NULL,    email varchar (+) NOT NULL,    extra text,    primary key (NI1),    index Ix_ Name)

# Create PRIMARY key ALTER TABLE name add primary key (column name);

# Delete primary key ALTER TABLE name drop primary key;alter table name  modify  column name int, drop primary key;

D, combined index

Composite indexes are multiple columns combined into a single draw query

Scenario: Multiple columns are used frequently to query, such as: where name = ' Nick ' and age = 18.

# CREATE TABLE Mess (    nid int not NULL Auto_increment primary key,    name varchar (+) NOT NULL, age    int. not Nu LL) # Create a composite index creation index ix_name_age on Mess (Name,age);

After creating a composite index, the query must be aware of:

• Name and email--> Use index, name must be put in front
• Name--> Use index
• Email--> do not use index

Note: When searching multiple conditions at the same time, the performance of the composite index is more efficient than multiple single index merges.

3. Related commands

# view index    show index from  table name # View execution Time    set profiling = 1;  # Open Profiling    SQL              ... # Execute SQL statement    show profiles;      # View Results

4. How to use index correctly

# like '%xx ', avoid%_ writing at the beginning of the    select * from TB1 where the name like '%n '; # using a function    select * from tb1 where reverse (name) = ' Nick ' ; # or    select * from tb1 where nid = 1 or email = ' [email protected] ';    Note: When there are non-indexed columns in the OR condition fail, the index # type is inconsistent    if the column is a string type, the incoming condition must be quoted.    SELECT * from tb1 WHERE name = 999;#! =, not equal to the    select * from TB1 where name! = ' Nick '    Note: If it is a primary key, the index        Select * FROM TB1 where nid! = 123#;, greater than    select * from TB1 where name > ' Nick '    Note: If the primary key or index is an integer type, the index is still gone        Selec T * from tb1 where nid > 123        SELECT * from tb1 where num > 123# ORDER by    Select e-mail from tb1 order by NA Me desc;    When sorting by index, the selected map does not go through the index if it is not an index:    if the primary key is sorted, then the index:        select * from tb1 ORDER by nid desc; # Combined index the leftmost prefix    if the combined index is: ( Name,email), query using:    name and email       --use index    name                 --use index    email                --Do not use index

5. Precautions

# Avoid using the Select *# count (1) or count (column) instead of Count (*) # to create a table whenever possible char instead of varchar# table field order fixed-length field precedence # Composite index instead of multiple single-column indexes (when multiple conditional queries are used frequently) # Use short cables as much as possible Citation # Use Join to replace subquery (sub-queries) # Note that conditional types need to be consistent in the case of a hyphen # index hash value (less repetition) not suitable for index, example: gender inappropriate

6. Implementation plan

explain + 查询SQL Used to display SQL execution information parameters that can be optimized for SQL based on reference information

Mysql> Explain select * from suoning;+----+-------------+---------+------+---------------+------+---------+----- -+------+-------+| ID | Select_type | Table   | type | possible_keys | key  | key_len | ref  | rows | Extra |+----+-------------+---------+------+---------------+------+---------+------+------+-------+|  1 | Simple      | suoning | All  | NULL          | NULL | NULL    | NULL |    4 |       | +----+-------------+---------+------+---------------+------+---------+------+------+-------+1 row in Set (1.67 sec)

ID Query order identifies such as:mysql> explain select * FROM (select Nid,name from tb1 where Nid <) as B; +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+            | ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows |            Extra |  +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+            | 1 | PRIMARY | <derived2> | All | NULL | NULL | NULL |    NULL | 9 |            NULL |  | 2 | DERIVED | tb1 | Range | PRIMARY | PRIMARY | 8 |    NULL | 9 |            Using where | +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+ Special: if using Union connection gas value may be null Select_type query type simple query PRIMARY outermost query SU   Bquery Map to subquery DERIVED      The Subquery Union union result uses the result of the Union ... table name being accessed by type            How to access the query, performance: All < index < range < Index_merge < Ref_or_null < ref < Eq_ref < System/const                            All full table scan, for data table from start to finish select * from TB1;                                   Special: If there is a limit restriction, then it will not continue to scan down after the select * from tb1 where email = ' [email protected] '                                   SELECT * from tb1 where email = ' [email protected] ' limit 1;            Although both statements perform a full-table scan, and the second sentence uses limit, the scan is no longer resumed when one is found.            Index full index Scan, find the index from start to finish select Nid from Tb1;                            Range Search for indexed columns select * FROM TB1 where name < ' Alex '; Ps:between and in >  >= < <= operations Note:! = and > Symbols index_merge merge indexes, search using multiple single-column indexes            SELECT * from tb1 where name = ' Alex ' or Nid in (11,22,33);            REF finds one or more values based on the index select * from TB1 WHERE name = ' seven '; Eq_ref Connect using primary KEY or unique type select Tb2.nid,tb1.name from TB2 left join TB1 on t            B2.nid = Tb1.nid;                            The const constant table has a maximum of one matching row, because only one row, in which the column values in this row can be considered constants by the remainder of the optimizer, the const table is fast because they are read only once.            Select Nid from tb1 where nid = 2; System systems tables have only one row (= system table).                            This is a special case of the const join type.    SELECT * FROM (select Nid from tb1 where nid = 1) as A; Possible_keys possible index key real use Key_len MySQL use index byte length rows mysql estimate number of rows to read in order to find the desired rows-- ----Just a pre-valuation extra This column contains the details of the MySQL resolution query "Using IndeX "This value indicates that MySQL will use the overwrite index to avoid accessing the table.        Do not confuse the overlay index with the index access type.  "Using where" this means that the MySQL server will filter after the storage engine retrieves rows, and many of the where conditions involve the columns in the index, and when (and if) it reads the index, it can be inspected by the storage engine, so not all queries with a WHERE clause will show "Using where ".        Sometimes the presence of a "Using where" is a hint: A query can benefit from a different index.        "Using temporary" this means that MySQL uses a temporary table when sorting the results of the query. "Using Filesort" This means that MySQL uses an external index to sort the results instead of reading rows from the table in the index order.        MySQL has two kinds of file sorting algorithms, both of which can be done in memory or on disk, explain will not tell you which sort of file MySQL will use, and will not tell you whether the sort will be done in memory or on disk. "Range checked for each record (index map:n)" This means that there is no good index, the new index will be recalculated on each row of the join, N is the bitmap that is displayed in the Possible_keys column, and is redundant. With

7. Slow log Query

Logs can record which query statements are slow, do not walk the index, and the user discovers the problem, thereby solving the optimization

A. Configure MySQL to automatically record slow log

Modify the configuration file My.ini, no add

Slow_query_log = OFF                   # Whether to turn on slow logging long_query_time = 2                    # time limit, more than this time, then record slow_query_log_file =/usr/slow.log    #　Log file log_queries_not_using_indexes = OFF    # To use indexed search whether to record # View current configuration information:  show variables like '%query% ' # Modify current configuration:  Set global variable name = value

b, view MySQL slow log

mysqldumpslow -s at -a  /usr/local/var/mysql/MacBook-Pro-3-slow.log

--verbose version--debug debug--help Help-V version-D debug mode-S order sort how to sort B              Y (al, at, AR, C, L, R, T), ' on ' is default Al:average lock time ar:average rows sent At:average Query Time C:count l:lock time R:rows sent T: Query Time-r reverse order, default file is reversed. Reverse the sort order (largest last instead of first)-T NUM shows the top n just show the top n queries-a do not turn the numbers in SQL Change to N and the string to S. Don ' t abstract all numbers to N and strings to ' S '-n NUM abstract numbers with at least N digits within names-g PATT ERN regular match; Grep:only consider stmts that include this string-h HOSTNAME MySQL machine name or ip;hostname of DB Server for *-slow.lo g filename (can be wildcard), default is ' * ', i.e. match all-i name Name of server instance (if using Mys Ql.server startup script)-l total time without subtracting lock time;

Vii. Other

Naturally all things can not be separated from the basic grammatical statements, circular statements and judgment statements

1. Conditional statements

If then\ ElseIf then\ else\ End If

# If conditional statement delimiter \create PROCEDURE proc_if () BEGIN        declare i int default 0;    If i = 1 then        SELECT 1;    ELSEIF i = 2 then        SELECT 2;    ELSE        SELECT 7;    END IF; End\delimiter;

2. Circular statements

Divided into three loop statements: While loop, repeat loop, loop loop

# while loop delimiter \create PROCEDURE proc_while () BEGIN    DECLARE num INT;    SET num = 0;    While num < do        SELECT            num;        SET num = num + 1;    END while; End\delimiter;

# repeat loop, equivalent to do whiledelimiter \create PROCEDURE proc_repeat () BEGIN    DECLARE i INT;    SET i = 0;    Repeat        select I;        Set i = i + 1;        Until I >= 5    end repeat; End\delimiter;

# Loopdelimiter \create PROCEDURE proc_loop () BEGIN        declare i int default 0;    Loop_label:loop        Select I;        Set i=i+1;        If I>=5 then            leave Loop_label;            End If;    End Loop; End\delimiter;

3. Dynamic execution of SQL statements

# Dynamic Execution Sqldelimiter \drop PROCEDURE IF EXISTS proc_sql \create PROCEDURE proc_sql () BEGIN    declare p1 int;    Set p1 = one;    Set @p1 = p1;    PREPARE prod from ' select * from TB2 where nid >? ';    EXECUTE prod USING @p1;    deallocate prepare prod; End\delimiter;

# Way Two delimiter \drop PROCEDURE if EXISTS proc_sql \ # If not present add, otherwise do not operate create PROCEDURE proc_sql (in    strSQL char (128 ), in    Nidd int) BEGIN        set @sqll = strSQL;    Set @p1 = Nidd;    PREPARE prod from @sqll;    EXECUTE prod USING @p1;    deallocate prepare prod; End\delimiter; Call Proc_sql (' select * from suoning2 where ID >? ', ' 2 ');

MySQL Series (iii) Views, triggers, stored procedures, functions, transactions, indexes, statements

that you do not know