MySQL Learning---views/triggers/stored procedures/functions/indexes 180101

View

View: A view is a virtual table (non-real) that dynamically acquires data and can only do query operations

Essence: " get a dynamic dataset and name it based on the SQL statement", which you can use only by using "name" to get the result set, and use it as a table. because the view is a virtual table, you cannot use it to create, update, and delete real tables . The pymysql is a supported view.

Can only be used for queries.

Creating views: Create View Stu as select * from student;  # here is just a correspondence relationship, view is virtual table, dynamic fetch data select * from Stu;          # This just simplifies the operation, actually executes the SELECT * FROM Student view: Show TABLES      # displays table and view view information Delete view: Drop view Stu; Modify view: Alter views Stu As SELECT * FROM student where gender = ' male '; The pymysql is a supported view

Trigger

If you want to trigger a particular behavior before and after the "Add/delete/change" operation on a table, you can use a trigger

triggers are used to customize the behavior of the user before and after the "Add/delete/change" row of a table

Triggers cannot be called directly by the user, but are known to be passively triggered by the "Add/delete/change" operation on the table.

In particular:

New represents the data row that will be inserted, old means the data row to be deleted

Multi-line operation, each row will have a polling operation

scope of the trigger: INSERT, DELETE, UPDATE

timing OF Triggers: Before, after

Create a trigger: Special: New represents the data row that will be inserted, old means the data row to be deleted

CREATE TRIGGER tri_before_insert_tb1 before insert on student for each rowbeginif New.gender = = ' M ' then      # NEW = Stud Entinsert into Male_stu (sex) VALUES (' M '); # The input gender is m, insert Male_stu          ELSE insert into feamle_stu (sex) VALUES (' W '); Elseifendinsert into student (gender, class_id, sname) VALUES (' W ', 1, ' hahaha ') Note: The update operation requires 2 values, a new value passed in, an old value

Delete Trigger

Drop TRIGGER TRI_BEFORE_INSERT_TB1

Stored Procedures

A stored procedure is a collection of SQL statements [which can be censored in a function], and when the stored procedure is actively invoked, the internal SQL statements are executed logically, and the internal can have a for statement.

Note: Executing a stored procedure is sure to execute the SQL statement inside, and only one result set will be returned, and all union query operations with multiple tables are best combined to be returned as a result set.

Stored Procedures

A. can write complex logic

B. Parameters: In Out inout

C. Result set: Select ...

# Create a stored procedure with no parameters, like the creation of a function

CREATE PROCEDURE p1 () BEGIN   select * from student; END

# stored Procedure Calls

Call P1 ()          # Use the call Store name to execute the stored procedure and display the results

To delete a stored procedure:

drop procedure P1;

# Create a stored procedure with parameters to execute a custom variable and get the SQL set [result set can only have one, but may stitch result set]

# for stored procedures, you can receive parameters with three types of parameters:
                 in         only for incoming parameters with
              out         only for return values, External calls can get to content directly after internal direct assignment [MySQL automatically builds link relationships]
           inout         can be passed in and can be used as a return value

CREATE PROCEDURE P3 (in I1 int,in i2 int,inout i3 int, out R1 int) BEGIN    DECLARE Temp1 int;           # DECLARE declares the variable, and the stored procedure must use    DECLARE temp2 int default 0; # declares the default variable value        set temp1 = 1;    Set r1 = i1 + i2 + temp1 + temp2;        Set i3 = i3 +;           # function One: Custom function Operation   SELECT * from student;        # function Two: query and return the result set, and only one return at a time, but can be stitching result set end;                             # Note that the number is used to execute the result, no number is executed in the execution of the store-execute the stored procedure: set @t1 =4 with the call Store name;                      # must be set with the @ symbol @t2 = 0; Call P3 (1, 2, @t1, @t2);        # executes the store and automatically returns the result of select * Combined with select @t1, @t2;                  # executes this row alone, returning only the custom function results

MySQL Learning---Executing stored procedures using Python

Transaction: InnoDB Support 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 \     createPROCEDUREp1 (    outp_return_codetinyint) begindeclareexithandlerfor SqlException    begin--Errorsetp_return_code = 1;rollback; End;declareexithandlerfor sqlwarning    begin--warningsetp_return_code = 2;rollback; END; Starttransaction;deletefrom TB1;INSERTINTOTB2 (name) VALUES (' seven '); commit;--successsetp_return_code = 0; End\     delimiter; a stored procedure that supports transactions

function

functions: built-in functions + custom functions

Built-in functions:

SELECT 1;select char_length (' Hello World ');  #11, returns the character length of select CONCAT (' Hello ', ' world ', ' 2017 '); # Hello World 2017, character stitching Select Concat_ws (' _ ', ' Hello ', ' world ', ' 2017 ');  #hello _world _2017, added delimiter character stitching select CONV (' 8 ', 10, 2);  #1000, the binary conversion 10 binary conversion 2 binary Select FORMAT (123456.2,2); #123, 456.20 retain 2-bit select LOWER (' HELLO ') after the decimal point;  # Uppercase to lowercase select UPPER (' Hello '); # Lowercase to uppercase select INSERT (' Hello ', 0,2, ' YY '); # does not change, so the description character substitution is the select INSERT starting from the first position (' Hello ', ', ' YY '); # Yyllo, starting from the first position # Special: # If the Pos exceeds the original string length, return the original string # If Len exceeds the original string length, the new string is completely replaced by the Select INSTR (' Hello ', ' e ');   # 2, returns the index position where E appears select left (' Hello ', 3);   #hel, gets the first 3 characters of select right (' Hello ', 3); #llo, remove 3 values from the right select SUBSTRING (' Hello ', 1, 3); #hel, the default starts from the first position by taking select TRIM (' ' Hello ') # Hello, removing the left and right spaces select LTRIM (' Hello World '); #helloworld, its leading whitespace character is deleted. SELECT RTRIM (' Hello World ');  #hello World, the trailing space character is deleted from select LOCATE (' ll ', ' hello '); # 3, returns the position where the string is located select REPEAT (' H ', 5) #hhhhh, repeating the preceding character n times select replace (' Hello ', ' ll ', ' yy ') #heyyo, replace the character select revers E' Hello ') #olleh, character reversal Select space (2) # returns 2 spaces 

Custom Function: function only supports passing parameters, returns a result, does not allow write SQL, does not support return result set

Creating functions: Create function F1 (i1 int,i2 int) # pass 2 parameters returns int   # return result, similar to Java publist int F1 (int i1, int i2) BEGIN         # function inside The function content does not allow SQL to be written, declare num int is not allowed to get the result set    ;    Set num = I1 + i2;    # declare a int;      # function to use SELECT INTO can also be used to achieve the operation of the assignment    # Select Nid into a from student where name = ' HHH ';  # assigns the NID value to a    return (num);   # returns the result end; Execute function: SELECT F1 (2,3)  # 5 Delete function: Drop functions F1;

The difference between a function and a stored procedure:

"More References" http://www.cnblogs.com/wupeiqi/articles/5713323.html--view

"More References" http://www.cnblogs.com/wupeiqi/articles/5716963.html--index

"More References" http://www.cnblogs.com/wupeiqi/articles/5716963.html---Index supplement

MySQL Learning---views/triggers/stored procedures/functions/indexes 180101