MySQL (2), mysql

MySQL (2), mysql
MYSQL (2)

The previous article talked about the basic operations of mysql, which is hard to understand. This section mainly covers mysql views, stored procedures, functions, transactions, triggers, and dynamic SQL Execution.

View

A view is a virtual table whose content is defined by the query. Like a real table, a view contains a series of columns and row data with names. However, a view does not exist in the database as a stored data value set. Rows and columns are used to define tables referenced by View queries and dynamically generate tables when views are referenced. For the referenced basic table, the view function is similar to filtering. The filtering of the definition view can be from one or more tables of the current or other databases, or other views. There are no restrictions on query through views, and there are few restrictions on data modification through them. A view is an SQL statement stored in a database. It can hide some data for two reasons: security.

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 a view

-- Format: drop view name drop view v1

3. Modify the view

-- Format: alter view name as SQL statement ALTER VIEW v1 ASSELET. nid, B. name from tab1LEFT join B ON. id = B. nidLEFT join c on. id = C. nidWHERE tab1.id> 2

In this case, the create statement is changed to alter, and the intermediate statement is changed.

4. Use View

When using a view, you can operate on it as a table. Because the view is a virtual table, you cannot use it to create, update, or delete a real table. You can only use it for queries.

select * from v1

Stored procedure

1. Why should we use stored procedures?

We all know that there are two types of applications: web-based and desktop-based. They interact with databases to access data. Assume that an application contains these two types of SQL statements. To modify one of the query SQL statements, you may need to modify the corresponding query SQL statements, this problem occurs when our applications are very huge and complex, so it is not easy to maintain! In addition, placing SQL query statements in our web programs or desktops can be easily damaged by SQL injection. The storage routine can help us solve these problems.

2. Create a stored procedure

There are two main types of stored procedures: Parameter-based and parameter-free.

Case study without parameters:

-- Create a stored procedure delimiter // -- the end symbol of the custom statement. Because many SQL statements need to be executed here, you have to customize the statement to prevent the create procedure p1 () error () BEGIN select * from tab1; END // delimiter; -- END of the custom local area Terminator -- run the Stored Procedure call p1 ()

There are three main classes in the case of Parameter

• In is only used for passing in parameters.
• Out is only used for return values.
• Inout can be passed in and can be used as the return value.

-- Create Stored procedure delimiter \ create procedure p1 (in i1 int, -- input parameter i1 in i2 int, -- input parameter i2 inout i3 int, -- pass in and get the returned value out r1 int -- get the returned 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 the Stored Procedure DECLARE @ t1 INT default 3; -- set the default value of the variable to 3 DECLARE @ t2 INT; -- set the variable CALL p1 (1, 2, @ t1, @ t2); -- execute the stored procedure and input parameters, t2 automatically cancels SELECT @ t1, @ t2; -- view the output results of the stored procedure

2. delete a stored procedure

drop procedure p1;

3. python uses the pymysql module to call the stored procedure, because we learned this for language calling.

#! /Usr/bin/env python #-*-coding: UTF-8-*-import pymysqlconn = pymysql. connect (host = '2017. 0.0.1 ', port = 3306, user = 'root', passwd = '', db = 'day39b _') cursor = conn. cursor (cursor = pymysql. cursors. dictCursor) # Run the Stored Procedure row = cursor. callproc ('p1', (, 3) # selc = cursor. fetchall () print (selc) # obtain the stored procedure and return export t_row = cursor.exe cute ('select @ _ p1_0, @ _ p1_1, @ _ p1_2 ') # The returned value of the Qu Stored Procedure ret = cursor. fetchone () print (ret) # submit, otherwise the new or modified data conn cannot be saved. commit () # Close the cursor. close () # close the connection conn. close ()

Function

There are many built-in functions in mysql. For example, we often use the average value, sum, and number. There are various types of built-in functions. First, let's talk about the built-in functions,A function can also pass parameters or receive returned values. However, a function cannot obtain the result of executing a statement. A stored procedure is acceptable.

Built-in functions

For more information, see Chinese Document http://doc.mysql.cn/mysql5/refman-5.1-zh.html-chapter/functions.html#encryption-functions

1. Create a UDF

delimiter \\create function f1(    i1 int,    i2 int)returns intBEGIN    declare num int;    set num = i1 + i2;    return(num);END \\delimiter ;

2. delete a function

drop function f1;

3. Execute Functions

# Obtain the returned value declare @ I VARCHAR (32); select UPPER ('Alex ') into @ I; SELECT @ I; # Use select f1 (11, nid) in the query ), name from tb2;

Transactions

A transaction is used to take multiple SQL statements of some operations as atomic operations. Once an error occurs, the transaction can be rolled back to the original state to ensure database data integrity. For example, when money is transferred between two bank cards and Party A's money is transferred out, the optical cable suddenly breaks down. Party B has not received the money and where the money is going, to prevent this situation, the transaction comes out, and the transaction can prevent such a thing from happening.

Application 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; -- insert into tb2 (name) values ('seven'); COMMIT; -- SUCCESS set p_return_code = 0; END \ delimiter;

Execute the stored procedure:

DECLARE @i TINYINT;call p1(@i);select @i;

TRIGGER

In simple words, a trigger triggers an addition, deletion, modification, and query before or after executing this statement. The trigger is used to customize the actions before and after the user adds, deletes, or modifies the rows of the table.

1. Basic syntax

# Create trigger tri_before_insert_tb1 before insert on tb1 for each rowbegin... END # create trigger tri_after_insert_tb1 after insert on tb1 for each rowbegin... END # create trigger tri_before_delete_tb1 before delete on tb1 for each rowbegin... END # create trigger tri_after_delete_tb1 after delete on tb1 for each rowbegin... END # create trigger tri_before_update_tb1 before update on tb1 for each rowbegin... END # create trigger tri_after_update_tb1 after update on tb1 for each rowbegin... END

Before Example 1 is inserted:

-- Insert a name = Zhang Yilin in tab1 to tab2, of course, it is to determine whether the name inserted to 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 yanlin') ENDEND // delimiter;

After Example 2 is inserted:

Delimiter // create trigger tri_after_insert_tb1 after insert on tb1 for each rowbegin if new. num = 666 then insert into tb2 (NAME) VALUES ('zhang yanlin'), ('handsome '); elseif new. num = 555 then insert into tb2 (NAME) VALUES ('aylin'), ('very handsome '); end if; END // delimiter;

The same principle applies to deletion, modification, and query.

Special: NEW indicates the row to be inserted, and OLD indicates the row to be deleted.

2. delete a trigger

DROP TRIGGER tri_after_insert_tb1;

3. Use a trigger

The trigger cannot be called directly by the user, but it is caused by the passive addition, deletion, and modification operations on the table.

Insert into tb1 (name) values ('zhang yanlin ')

In the next chapter, I will update the database index for you. There are many things in this regard, so the landlord decided to write a new blog and remember to like it !!

-This article Reprinted from: http://www.cnblogs.com/aylin/p/5746501.html