[2020Python cultivation] MySQL view, trigger, transaction, stored procedure and function

[contents] (the rest are knowledge)

A view

Two triggers

Three Affairs (mastery)

Four stored procedures

Five functions

Vi. process control

7, Index theory

 

1, View

1. What is a view

A view is a virtual table (non real existence). Its essence is to get a dynamic data set according to SQL statements and name it. When users use it, they only need to use the name to get the result set, which can be used as a table.

That is to say, view is to get a virtual table through query, and then save it. It can be used directly next time. Actually, view is also a table

2. Why use views

With view, we can extract the temporary table in the query process and implement it with view, so that when we want to operate the data of the temporary table in the future, we don't need to rewrite the complex sql, and we can directly search in the view.

But views have obvious efficiency problems, and views are stored in the database. If the sql used in our program excessively depends on the views in the database, that is, strong coupling, it means that it is very inconvenient to extend sql, so it is not recommended to use

3. How to use views

(1) Application example of temporary table:

#Two related tables
mysql> select * from course;
+-----+--------+------------+
| cid | cname  | teacher_id |
+-----+--------+------------+
|   1 | Biology   |          1 |
|   2 | Physics   |          2 |
|   3 | Sports   |          3 |
|   4 | Fine Arts   |          2 |
+-----+--------+------------+
4 rows in set (0.00 sec)

mysql> select * from teacher;
+-----+-----------------+
| tid | tname           |
+-----+-----------------+
|   1 | Mr. Zhang Lei        |
|   2 | Mr. Li Ping        |
|   3 | Miss Liu Haiyan      |
|   4 | Miss Zhu Yunhai      |
|   5 | Miss Li Jie        |
+-----+-----------------+
5 rows in set (0.00 sec)

#Check the course name of Mr. Li Ping
mysql> select cname from course where teacher_id = (select tid from teacher where tname='Mr. Li Ping');
+--------+
| cname  |
+--------+
| Physics   |
| Fine Arts   |
+--------+
2 rows in set (0.00 sec)

#Subquery the temporary table as teacher_id Etc
select tid from teacher where tname='Mr. Li Ping'

(2) CREATE VIEW - CREATE VIEW view name AS SQL statement

#Syntax: CREATE VIEW view name as SQL statement
 Create view teacher [view as select TID from teacher where tname = 'Teacher Li Ping';

#So the sql for querying the course name taught by Mr. Li Ping can be rewritten as
mysql> select cname from course where teacher_id = (select tid from teacher_view);
+--------+
| cname  |
+--------+
|Physics|
|Art|
+--------+
2 rows in set (0.00 sec)

#!!! Attention attention attention:
#1. After using views, you don't need to rewrite the sql of subqueries every time, but it's not as efficient as we write subqueries

#And there is a fatal problem:
The view is stored in the database. If the sql in our program depends too much on the view stored in the database, it means that once the sql needs to be modified and involves the view, it must be modified in the database,
Generally, there is a special DBA responsible for the database in the company. If you want to complete the modification, you must pay a lot of communication costs. DBA may help you complete the modification, which is extremely inconvenient

(3) Using views

#Modify the view and the original table
mysql> select * from course;
+-----+--------+------------+
| cid | cname  | teacher_id |
+-----+--------+------------+
|   1 | Biology   |          1 |
|   2 | Physics   |          2 |
|   3 | Sports   |          3 |
|   4 | Fine Arts   |          2 |
+-----+--------+------------+
4 rows in set (0.00 sec)

mysql> create view course_view as select * from course; #Create table course View of
Query OK, 0 rows affected (0.52 sec)

mysql> select * from course_view;
+-----+--------+------------+
| cid | cname  | teacher_id |
+-----+--------+------------+
|   1 | Biology   |          1 |
|   2 | Physics   |          2 |
|   3 | Sports   |          3 |
|   4 | Fine Arts   |          2 |
+-----+--------+------------+
4 rows in set (0.00 sec)

mysql> update course_view set cname='xxx'; #Update data in view
Query OK, 4 rows affected (0.04 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> insert into course_view values(5,'yyy',2); #Insert data into view
Query OK, 1 row affected (0.03 sec)

mysql> select * from course; #It was found that the records of the original table were also modified
+-----+-------+------------+
| cid | cname | teacher_id |
+-----+-------+------------+
|   1 | xxx   |          1 |
|   2 | xxx   |          2 |
|   3 | xxx   |          3 |
|   4 | xxx   |          2 |
|   5 | yyy   |          2 |
+-----+-------+------------+
5 rows in set (0.00 sec)

Note: we should not modify the records in the view, and it is impossible to modify the records in the view at all when multiple tables are involved.

(4) Modify view

 Modify view

//Syntax: ALTER VIEW view view name AS SQL statement
mysql> alter view teacher_view as select * from course where cid>3;
Query OK, 0 rows affected (0.04 sec)

mysql> select * from teacher_view;
+-----+-------+------------+
| cid | cname | teacher_id |
+-----+-------+------------+
|   4 | xxx   |          2 |
|   5 | yyy   |          2 |
+-----+-------+------------+
2 rows in set (0.00 sec)

 

(5) Delete view

Syntax: DROP VIEW view name

DROP VIEW teacher_view

 

2, Trigger

1. What is trigger

Use the trigger to customize the user's behavior before and after adding, deleting and modifying the table. Note: no query

Using triggers can help us achieve monitoring, logging

Trigger can be triggered automatically in six cases: before adding, after adding, before deleting, after modifying, before modifying

2. Basic grammatical structure

create trigger Trigger name  before/after insert/update/delete on Table name
for each row
begin
    sql Sentence
end

# We usually need to know the meaning of the trigger name


# For increase
create trigger tri_before_insert_t1  before insert on t1
for each row
begin
    sql Sentence
end
create trigger tri_after_insert_t1  after insert on t1
for each row
begin
    sql Sentence
end
"""Consistent writing format for deletion and modification"""

ps:modify MySQL The default statement terminator only applies to the current window
    delimiter $$  Set the default end symbol by;Change to $$
    delimiter ;
    
# case
CREATE TABLE cmd (
    id INT PRIMARY KEY auto_increment,
    USER CHAR (32),
    priv CHAR (10),
    cmd CHAR (64),
    sub_time datetime, #Submission time
    success enum ('yes', 'no') #0 Delegate execution failed
);

CREATE TABLE errlog (
    id INT PRIMARY KEY auto_increment,
    err_cmd CHAR (64),
    err_time datetime
);
"""
//When the record succes s field in the cmd table is no, trigger the execution of the trigger to insert data into the errlog table
NEW It refers to data objects
"""
delimiter $$
create trigger tri_after_insert_cmd after insert on cmd 
for each row
begin
    if NEW.success = 'no' then
        insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
    end if;
end $$
delimiter ;

# towards cmd Table insert data
INSERT INTO cmd (
    USER,
    priv,
    cmd,
    sub_time,
    success
)
VALUES
    ('jason','0755','ls -l /etc',NOW(),'yes'),
    ('jason','0755','cat /etc/passwd',NOW(),'no'),
    ('jason','0755','useradd xxx',NOW(),'no'),
    ('jason','0755','ps aux',NOW(),'yes');

# Delete trigger 
drop trigger tri_after_insert_cmd;
#Cases 
# Before insertion
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# After insertion
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# Before deleting
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# After deletion
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# Before update
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# After update
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END
Basic syntax of six triggering situations

Use trigger:

The trigger cannot be called directly by the user, but is passively triggered by the [add / delete / modify] operation on the table.

Delete trigger:

drop trigger tri_after_insert_cmd;

3, Transactions

1. What is business

Transactions are used to treat multiple SQL of certain operations as atomic operations

(opening a transaction can contain multiple sql statements. These sql statements are either successful at the same time, or none of them wants to succeed, which is called transaction atomicity),

Once an error occurs, it can be rolled back to the original state to ensure the integrity of database data.

2. Role of transaction

[ensure the safety of data operation]

Example: repayment

There are multiple operations for payer and payee;

When operating multiple pieces of data, some unsuccessful operations may occur

[four characteristics of transaction ACID]

A: atomicity

A transaction is an indivisible unit. Many operations contained in a transaction either succeed or fail at the same time

C: consistency

The transaction must change the database from one consistent state to another

Consistency is closely related to atomicity

1: I solation

The execution of one transaction cannot be interfered by other transactions

(that is, the operations and data used within a transaction are isolated from other concurrent transactions, and the concurrent transactions do not interfere with each other.)

D: durability

It's also called permanence. Once a transaction is successfully committed and executed, its modification to the data in the database should be permanent,

The next operation or failure should not have any impact on it

3. How to use transactions

#Transaction related keywords
#1 open transaction
start transaction;

#2 rollback (return to the state before transaction execution)
rollback;

#3 confirmation (after confirmation, it cannot be rolled back)
commit;

"""Analog transfer function"""
create table user(
    id int primary key auto_increment,
    name char(16),
    balance int
);
insert into user(name,balance) values
('jason',1000),
('egon',1000),
('tank',1000);


# 1 Start transaction first
start transaction;
# 2 Multiple strips sql Sentence
update user set balance=900 where name='jason';
update user set balance=1010 where name='egon';
update user set balance=1090 where name='tank';

summary
When you want multiple sql statements to be consistent, you either succeed at the same time or fail at the same time
You should consider using business

 

4, Stored procedure

1. What is stored procedure

Stored procedures are similar to custom functions in python

It contains a series of sql statements that can be executed. The stored procedures are stored in the MySQL server,

You can trigger the execution of internal sql statements directly by calling stored procedures

2. Basic use

create procedure Name of stored procedure(Parameter 1,Parameter 2,...)
begin
    sql Code
end
# call
call Name of stored procedure();

 

3. Three development models

The first is basically not used. Generally, it's the third type. If you have efficiency problems, you can use handwritten sql

[first]

Application: programmers write code development

MySQL: write the stored procedure in advance for the application to call

Benefit: development efficiency improves execution efficiency

Disadvantages: the subsequent stored procedures are not scalable considering the problems of elements and cross department communication

[Second]

Application program: the programmer writes the code to develop and designs the database operation by himself

Advantages: high scalability

Disadvantages:
Reduced development efficiency
It is too tedious to write sql statements, and sql optimization needs to be considered in the future

[Third]

Application program: write only program code, not sql statement, directly call operation based on the python framework of MySQL written by others -- for example, ORM framework

Advantages: development efficiency is higher than the above two situations

Disadvantage: poor scalability of statements may lead to inefficiency

4. Specific demonstration of stored procedure

delimiter $$
create procedure p1(
    in m int,  # Just in and out  m Can't go back out
    in n int,
    out res int  # This parameter can be returned
)
begin
    select tname from teacher where tid>m and tid<n;
    set res=666;  # take res Variable modification is used to identify that the current stored procedure code is actually executed
end $$
delimiter ;

# For formal parameters res You can't transfer data directly. You should transfer a variable name
# Defining variables
set @ret = 10;
# View the value of the variable
select @ret;

 

5. How to call stored procedure in pymysql module

import pymysql


conn = pymysql.connect(
    host = '127.0.0.1',
    port = 3306,
    user = 'root',
    passwd = '123456',
    db = 'day48',
    charset = 'utf8',
    autocommit = True
)
cursor = conn.cursor(pymysql.cursors.DictCursor)
# Call stored procedure
cursor.callproc('p1',(1,5,10))
"""
@_p1_0=1
@_p1_1=5
@_p1_2=10
"""
# print(cursor.fetchall())
cursor.execute('select @_p1_2;')
print(cursor.fetchall())

 

5, Functions

It is different from a stored procedure. A stored procedure is a custom function. Functions are similar to built-in functions

('jason','0755','ls -l /etc',NOW(),'yes')

CREATE TABLE blog (
    id INT PRIMARY KEY auto_increment,
    NAME CHAR (32),
    sub_time datetime
);

INSERT INTO blog (NAME, sub_time)
VALUES
    ('Part 1','2015-03-01 11:31:21'),
    ('Part 2','2015-03-11 16:31:21'),
    ('Chapter 3','2016-07-01 10:21:31'),
    ('Chapter 4','2016-07-22 09:23:21'),
    ('Chapter 5','2016-07-23 10:11:11'),
    ('Chapter 6','2016-07-25 11:21:31'),
    ('Chapter 7','2017-03-01 15:33:21'),
    ('Chapter 8','2017-03-01 17:32:21'),
    ('Chapter 9','2017-03-01 18:31:21');

select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');

 

6, Process control

# if judge
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 ;
# while loop
delimiter //
CREATE PROCEDURE proc_while ()
BEGIN
    DECLARE num INT ;
    SET num = 0 ;
    WHILE num < 10 DO
        SELECT
            num ;
        SET num = num + 1 ;
    END WHILE ;

 

7, Index theory

1. Index

ps: data exists on the hard disk, so it is inevitable for IO operation to query data

Index: a data structure, similar to a book's catalog. It means that in the future, when querying data, you should find the directory first and then the data, instead of turning pages one by one, so as to improve the query speed and reduce the IO operation

Index, also known as "key" in MySQL, is a data structure used by storage engine to quickly find records

Note that the foreign key is not used to speed up the query, which is not in our research scope

The first two types of keys have their own constraints in addition to increasing the query speed. The last type of index key has no constraints, just to help you query data quickly

essence

Filter out the final results by constantly narrowing the desired data range, and at the same time, turn random events (page by page)

Become a sequential event (find directory and data first)

That is to say, with index mechanism, we can always find data in a fixed way

There can be multiple indexes (multiple directories) in a table

Indexes can help you speed up your queries, but they also have disadvantages.

1. When there is a large amount of data in the table, the speed of index creation will be very slow
2. After the index is created, the query performance of the table will be greatly improved, but the write performance will also be greatly reduced
"""
Index should not be created at will!!!

2. b + tree

Only the leaf node stores the real data, and the other nodes store the virtual data only to guide the way
The higher the level of the tree, the more steps it takes to query the data (the higher the level of the tree, the more steps it takes to query the data)

Block storage is limited
Why is it recommended that you use the id field as an index
Less space a disk block can store more data
So long, it reduces the height of the tree and thus reduces the number of queries

3. Clustered index (primary key)

A clustered index is a primary key
Innodb only has two files that directly store the primary key in the idb table
MyIsam, three files, one file for index

 

4. Secondary index (unique,index)

When querying data, it is not possible to use the primary key all the time. It is also possible to use other fields such as name and password

Then you can't use clustered index at this time. At this time, you can set auxiliary index (also a b + tree) for other fields according to the situation

The leaf node stores the primary key value corresponding to the data
First get the primary key value of the data according to the secondary index
After that, you still need to query the data in the clustered index of the primary key

 

5. Override index

The leaf node of the secondary index has obtained the required data

# to name Set secondary index
select name from user where name='jason';
# Non overlay index
select age from user where name='jason';

6. Code to test whether the index is valid 

**Get ready**

```mysql
#1. Preparation form
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);

#2. Create a stored procedure to insert records in batches
delimiter $$ #Declare that the end symbol of the stored procedure is $$
create procedure auto_insert1()
BEGIN
    declare i int default 1;
    while(i<3000000)do
        insert into s1 values(i,'jason','male',concat('jason',i,'@oldboy'));
        set i=i+1;
    end while;
END$$ #$$End
delimiter ; #Redeclare semicolon as end symbol

#3. View stored procedures
show create procedure auto_insert1\G 

#4. Call stored procedure
call auto_insert1();
```

``` mysql 
# Table without any index
select * from s1 where id=30000;
# Avoid time loss caused by printing
select count(id) from s1 where id = 30000;
select count(id) from s1 where id = 1;

# to id Make a primary key
alter table s1 add primary key(id);  # Very slow

select count(id) from s1 where id = 1;  # The speed is an order of magnitude lower than before the index is not built
select count(id) from s1 where name = 'jason'  # Still slow


"""
//Scope issues
"""
# It's not that the index is added. The query speed will be faster according to this field in the future   
select count(id) from s1 where id > 1;  # Speed compared to id = 1 A lot slower
select count(id) from s1 where id >1 and id < 3;
select count(id) from s1 where id > 1 and id < 10000;
select count(id) from s1 where id != 3;

alter table s1 drop primary key;  # Delete the primary key and study it separately name field
select count(id) from s1 where name = 'jason';  # It's slow again

create index idx_name on s1(name);  # to s1 Table name Field create index
select count(id) from s1 where name = 'jason'  # Still very slow!!!
"""
//Let's look at the principle of b + tree. The data needs to be highly differentiated, but our table is all jason, which can't be distinguished at all
//So this tree is actually a "stick"
"""
select count(id) from s1 where name = 'xxx';  
# This will be very fast. I'm just a stick. The first one doesn't match. I don't need to go down any more
select count(id) from s1 where name like 'xxx';
select count(id) from s1 where name like 'xxx%';
select count(id) from s1 where name like '%xxx';  # Slow leftmost matching feature

# Fields with low discrimination cannot be indexed
drop index idx_name on s1;

# to id Common index of fields
create index idx_id on s1(id);
select count(id) from s1 where id = 3;  # Soon
select count(id) from s1 where id*12 = 3;  # Slow index fields must not participate in the calculation

drop index idx_id on s1;
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';
# For the above continuous multiple and Operation of, mysql The index field with high differentiation will be found from left to right, and the overall range will be reduced before other conditions are compared
create index idx_name on s1(name);
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';  # No acceleration

drop index idx_name on s1;
# to name，gender It is not difficult to speed up the query speed by adding indexes to the fields with low differentiation

create index idx_id on s1(id);
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';  # Come on, let's go first id It's been said that fast data locking has become one
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # Slow based on id There is still a lot of data found, and then we have to compare other fields

drop index idx_id on s1

create index idx_email on s1(email);
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # Go through email One sword at a time 
```

#### Joint index

```mysql
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  
# If the above four fields are highly differentiated, the query can be accelerated for anyone who builds them
# to email Add but not use email field
select count(id) from s1 where name='jason' and gender = 'male' and id > 3; 
# to name Add but not use name field
select count(id) from s1 where gender = 'male' and id > 3; 
# to gender Add but not use gender field
select count(id) from s1 where id > 3; 

# The problem is that all fields are indexed but not used. It will take four more times to build
create index idx_all on s1(email,name,gender,id);  # The leftmost matching principle, put it to the left with high discrimination
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # Speed up
```

//Conclusion: for the above operations, you can knock if you are interested, or you can knock if you are not interested. You should have fun. Just master the theory

//Slow query log

//Set a time to detect all sql statements beyond that time, and then optimize them accordingly
Interested in health

 

 

Reference reading:

https://zhuanlan.zhihu.com/p/120227954

Keywords: MySQL SQL Stored Procedure Database

Added by csplrj on Fri, 08 May 2020 11:46:25 +0300

Popular Keywords

©2024 Programming VIP