Python development "18th article": MySQL (ii)

View

A view is a virtual table (not a real one), which is essentially "getting a dynamic dataset from an SQL statement and naming it", which allows the user to get a result set using only "name" and use it as a table.

Select    *from    (        Select            nid,            NAME        from            tb1        WHERE            nid > 2    ) as Awhere    A. NAME > ' Alex ';

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

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

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.

1. Create basic syntax

# 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

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;

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;

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

Stored Procedures

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.

1. Create a stored procedure

--Create Stored procedure delimiter//create procedure P1 () BEGIN    select * from T1; End//delimiter;--Execute Stored procedure call P1 ()

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

--Create Stored procedure delimiter \create procedure P1 (in    i1 int, in    i2 int,    inout i3 int, out    R1 int) 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 set @t1 =4;set @t2 = 0; Call P1 (1, 2, @t1, @t2); SELECT @t1, @t2;

                    Delimiter//                    CREATE PROCEDURE P1 ()                    begin                        Select * FROM v1;                    End//                    delimiter;

                    Delimiter//                    CREATE PROCEDURE P2 (in                        N1 int,                        inout n3 int., out                        n2 int,                    )                    begin                        Declare TEMP1 int;                        DECLARE TEMP2 int default 0;                        SELECT * FROM v1;                        Set n2 = n1 +;                        Set n3 = n3 + n1 +;                    End//                    delimiter;

                    Delimiter \ Create PROCEDURE p1 (out P_return_code t Inyint) BEGIN DECLARE exit handler for Sqlexcepti                              On 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;

                    Delimiter//                    CREATE PROCEDURE P3 ()                    begin                         declare SSID int;--Custom Variable 1                          declare ssname varchar (50);--Custom variable 2                          DECLARE done INT DEFAULT FALSE;                        DECLARE my_cursor cursor FOR select sid,sname from student;                        DECLARE CONTINUE HANDLER for don't FOUND SET done = TRUE;                                                Open my_cursor;                            Xxoo:loop                                fetch my_cursor into ssid,ssname;                                If do then                                     leave Xxoo;                                END IF;                                Insert into teacher (Tname) values (ssname);                            End Loop Xxoo;                        Close my_cursor;                    End  //                    delimter;

                    Delimiter \                    CREATE PROCEDURE P4 (in                        nid int                    )                    BEGIN                        PREPARE prod from ' select * from student where Si d >? ';                        EXECUTE prod USING @nid;                        deallocate prepare prod;                     End\                    delimiter;

2. Delete stored Procedures

drop procedure Proc_name;

3. Execute 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)

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

Function

A number of built-in functions are available in MySQL, such as:

    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: Chinese punch here OR the official bash here

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 (' 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.

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;

123	set@i =0;call p1(@i);select @i;

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.

1234567

30        1040   515 3566161119213955100

　　

Common indexes in MySQL are:

• Normal index
• Unique index
• Primary key Index
• Combined index

1. General Index

The normal index has only one function: Accelerated query

CREATE TABLE in1 (    nid int not NULL Auto_increment primary key,    name varchar (+) NOT NULL,    email varchar (+) No T null,    extra text,    index Ix_name (name))

CREATE INDEX index_name on table_name (column_name)

Drop index_name on table_name;

Show index from TABLE_NAME;

Note: If you are creating an index with a blob and TEXT type, you must specify length.

Create INDEX Ix_extra on in1 (extra (32));

2. Unique index

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

CREATE TABLE in1 (    nid int not NULL Auto_increment primary key,    name varchar (+) NOT NULL,    email varchar (+) No T null,    extra text,    unique ix_name (name))

Create unique index index name on table name (column name)

Drop unique index index name on table name

3. Primary KEY index

Primary key has two functions: Accelerated Query and UNIQUE constraint (non-nullable)

CREATE TABLE in1 (    nid int not NULL Auto_increment primary key,    name varchar (+) NOT NULL,    email varchar (+) No T null,    extra text,    index Ix_name (name)) orcreate table in1 (    nid int not null auto_increment,    name VA Rchar (+) not NULL,    email varchar (+) NOT NULL,    extra text,    primary key (NI1),    index Ix_name (name))

ALTER TABLE name add primary key (column name);

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

4. 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 TABLE in3 (    nid int not NULL Auto_increment primary key,    name varchar (+) NOT NULL,    email varchar (+) No T null,    extra text)

Create INDEX Ix_name_email on in3 (Name,email);

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.

Other

1. Conditional statements

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

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;

Delimiter \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;

BEGIN        declare i int default 0;    Loop_label:loop                set i=i+1;        If I<8 then            iterate Loop_label;        End If;        If i>=10 then            leave Loop_label;        End If;        Select I;    End Loop Loop_label; END

3. Dynamic execution of SQL statements

Delimiter \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;

Python development "18th article": MySQL (ii)