This section focuses on mysql views, stored procedures, functions, transactions, triggers, and dynamic execution of SQL

View A view is a virtual table whose contents are defined by a query. Like a real table, a view contains a series of named column and row data. However, the view does not exist in the database as a stored set of data values. The row and column data comes from the tables referenced by the query that defines the view and is generated dynamically when the view is referenced. For the underlying tables referenced in it, the view acts like a filter. Filters for defining views can come from one or more tables of the current or other database, or from other views. There are no restrictions on querying through views, and there are few restrictions on data modification through them. A view is an SQL statement of a query stored in a database for two main reasons: a view can hide some data for security reasons.

1. Create a view

-- Format: CREATE VIEW VIEW name AS SQL statement CREATE VIEW v1 AS SELET nid, name FROM tab1 WHERE nID > 4Copy the code

2. Delete the view

-- Format: DROP VIEW VIEW name DROP VIEW v1Copy the code

3. Modify the view

- the format: ALTER VIEW V1 AS SELET A.id,B. NAME FROM tab1 LEFT JOIN B ON A.id = B.id LEFT JOIN C ON A.id = C.nid WHERE tab1.id > 2Copy the code

So we just changed create to ALTER, and we changed the statement in the middle.

4. Use views

You can use a view as a table. Because a view is a virtual table, it cannot be used to create, update, or delete real tables, but can only be used for query.

select * from v1
Copy the code

1. Why do we use stored procedures?

We all know that there are two kinds of applications, one is based on the Web, the other is based on the desktop, they both interact with the database to complete the data access work. Let’s say we have an application that contains both, and we want to modify one of the query SQL statements. We might want to modify the corresponding query SQL statements in both of them at the same time. In addition, SQL queries placed in our Web applications or desktops are vulnerable to SQL injection. And storage routines can help us solve these problems.

Create a stored procedure

There are two main types of creating stored procedures, one with parameters, one with no parameters, first with no parameters and then with no parameters

Case without parameters:

Create PROCEDURE p1() BEGIN SELECT * from tab1; END// delimiter ; -- Execute the stored procedure call P1 ()Copy the code

The case with parameters section has three main classes

In is only used for passing in parameters and out is only used for returning values. Inout can be either passed in or used as a return value

Delimiter \ create PROCEDURE P1 (in i1 int, -- pass i1 in i2 int, -- pass i2 inout i3 int, Out R1 int -- return 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 ; DECLARE @t1 INT default 3; -- DECLARE @t2 INT; -- DECLARE @t2 INT; CALL p1 (1, 2,@t1, @t2); SELECT @t1,@t2; SELECT @t1,@t2; View the stored procedure outputCopy the code

2. Delete the stored procedure

drop procedure p1;
Copy the code

3. Python uses the Pymysql module to call stored procedures, because we learned this for language calls

#! /usr/bin/env python # -* -coding: utF-8 -* -import pymysql conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', Cursor = = 'day39b_' db) conn. Cursor (cursor = pymysql. Your cursors. DictCursor) # execute the stored procedure row = cursor. Callproc (p1, (1, 2, 3)) # Fetchall () print(selc) # effect_row = cursor.execute('select @_p1_0,@_p1_1,@_p1_2') # Fetchone () print(ret) # submit, Conn.mit () # close cursor. Close () # close connection conn.close()Copy the code

Function is the function in mysql has many built-in functions, for example, we often use averaging, sum, the number of of all kinds, built-in functions, please send you to the department and then there’s a custom function, function also can pass parameters, also can receive the return value, but the function can’t enforced statement results, can be stored procedures.

Built-in functionCopy the code

For more information, please refer to doc.mysql.cn/mysql5/refm…

1. Create custom functions

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

2. Delete functions

drop function f1;
Copy the code

3. Execute the function

Declare @i VARCHAR(32); declare @i VARCHAR(32); select UPPER('alex') into @i; SELECT @i; Select f1(11,nid),name from tb2;Copy the code

Transaction transactions are used to treat multiple SQL for certain operations as atomic operations that can be rolled back to their original state if any one of them fails, thereby ensuring database data integrity. For example: when the transfer between two bank cards, party A money transfer, suddenly the cable is broken, Party B has not received the money, where the money went, in order to prevent this situation, the transaction will come out, the transaction can prevent this kind of thing.

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 ;Copy the code

Execute the stored procedure:

DECLARE @i TINYINT;
call p1(@i);
select @i;
Copy the code

When you add, delete, or modify a row in a table before or after the statement is executed, a TRIGGER is used to customize the user’s behavior before and after adding, deleting, or modifying rows in a table.

1. Basic grammar

CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON TB1 FOR EACH ROW BEGIN... END # CREATE TRIGGER tri_after_insert_tB1 AFTER INSERT ON TB1 FOR EACH ROW BEGIN... END # CREATE TRIGGER tri_before_delete_tB1 BEFORE DELETE ON TB1 FOR EACH ROW BEGIN... CREATE TRIGGER tri_after_delete_tB1 AFTER DELETE ON TB1 FOR EACH ROW BEGIN... END # CREATE TRIGGER tri_before_update_tB1 BEFORE UPDATE ON TB1 FOR EACH ROW BEGIN... CREATE TRIGGER tri_after_update_tB1 AFTER UPDATE ON TB1 FOR EACH ROW BEGIN... ENDCopy the code

Example 1 Before insertion:

Select * from tab2 where name = zhangyanlin; INSERT name into TAB1 = aylin delimiter // CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON TB1 FOR EACH ROW BEGIN IF New. NAME == 'aylin' THEN INSERT INTO TB2 (NAME) VALUES (' tB2 ') END END// delimiter;Copy the code

Example 2:

delimiter // CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW BEGIN IF NEW. num = 666 THEN INSERT INTO TB2 (NAME) VALUES (' tb2 '), (' tB2 '); ELSEIF new. num = 555 THEN INSERT INTO Tb2 (NAME) VALUES ('aylin'), ('aylin'); END IF; END// delimiter ;Copy the code

The same delete, change, check is the same reason

In particular: NEW denotes the row to be inserted and OLD denotes the row to be deleted.

Delete trigger

DROP TRIGGER tri_after_insert_tb1;
Copy the code

3. Use triggers

Triggers cannot be called directly by the user but are passively triggered by an add/delete/alter operation on a table.

Insert into VALUES (name)Copy the code

Mysql, Netty, Spring, thread, Spring Cloud, JVM, source code, algorithm, etc., also have a detailed learning plan map, interview questions, etc., need to obtain these contents of the friend please add Q: sample: 909038429/./* Welcome to Java chat