This chapter focuses on views 、 trigger 、 Business 、 stored procedure 、 The understanding of functions and how to use these functions , There are also problems encountered in the use of how to avoid , Let's have a look !

View

1、 What is a view

View is to get a virtual table through query , And then save it , Next time you can use it directly

2、 Why use views

If you want to use a virtual table frequently , You don't have to query repeatedly

3、 How to use views

createviewteacher2courseasselect*fromteacherinnerjoincourseonteacher.tid=course.teacher_id

emphasize

1、 In the hard disk , Views have only table structure files , No table data file

2、 Views are often used for queries , Try not to modify the data of the view

Delete view

dropviewteacher2course;

remember : Views are not used in the development process , Because the view is mysql The function of , If views are widely used in projects , It means that when you want to expand a function later, the function just needs to modify the view , It means you need to be in mysql Here, change the view , Then go to the application and modify the corresponding sql sentence , This involves cross departmental communication , So you don't usually use views , It's through revision sql Statement to extend function

trigger

In order to satisfy the requirement of adding data to a table 、 Delete 、 In the case of , The function of automatic triggering is called trigger

Why use triggers

Triggers are designed for adding data to a table insert、 Delete delete、 Change update act , Once this kind of behavior is carried out

It triggers the execution of the trigger , That is to automatically run another section sql Code

Create trigger Syntax

For insertion

createtriggertri_after_insert_t1afterinserton Table name foreachrow

begin

sql Code

end

createtriggertri_before_insert_t2before

inserton Table name foreachrow

begin

sql Code

end

For deletion

createtriggertri_after_delete_t1afterdeleteon Table name foreachrow

begin

sql Code

end

createtriggertri_before_delete_t2beforedeleteon Table name foreachrow

begin

sql Code

end

For modification

createtriggertri_after_update_t1afterupdateon Table name foreachrow

begin

sql Code

end

createtriggertri_before_update_t2beforeupdateon Table name foreachrow

begin

sql Code

end

Case study

createtablecmd(

idintprimarykeyauto_increment,

userchar(32),

privchar(10),

cmdchar(64),

sub_time datetime,# Submission time

successenum(‘yes’,‘no’)

);

CREATETABLEerrlog (

idINTPRIMARYKEYauto_increment,

err_cmdCHAR(64),

err_time datetime


);

delimiter KaTeX parse error: Expected 'EOF', got '#' at position 1: #̲ take mysql The default terminator is ;…

createtriggertri_before_insert_cmdbeforeinsertoncmdforeachrow

begin

ifNEW.success ='no’then# New records will be mysql Encapsulated into NEW object

insertintoerrlog(err_cmd,err_time)values(NEW.cmd,NEW.sub_time);

endif;

end$$

delimiter ;# Remember to change back when it's over , Otherwise, the ending signs are $$ 了

To the table cmd Insert record in , Trigger trigger , according to if The condition of whether to insert the error log

insertintocmd(

user,

priv,
cmd,
sub_time,


success

)

values

(‘egon’,‘0755’,‘ls -l /etc’,NOW(),‘yes’),

(‘egon’,‘0755’,‘cat /etc/passwd’,NOW(),‘no’),

(‘egon’,‘0755’,‘useradd xxx’,NOW(),‘no’),

(‘egon’,‘0755’,‘ps aux’,NOW(),‘yes’);

Inquire about errlog Table record

select*fromerrlog;

Delete trigger

droptriggertri_after_insert_cmd;

Business

What is business

Opening a transaction can contain some sql sentence , these sql Sentences either succeed at the same time or none of them , Call it the atomicity of transactions

The role of affairs

Ensure the data security of data operation

A transaction has four properties

Atomicity 、 Uniformity 、 Isolation, 、 persistence , These four attributes are often called ACID characteristic

Atomicity (atomicity): A transaction is an indivisible unit of work , All operations included in a transaction are either done , Either not

Uniformity (consistency): A transaction must change a database from one consistent state to another , Consistency is closely related to atomicity

Isolation, (isolation): 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 , Transactions that execute concurrently cannot interfere with each other

persistence (durability): Persistence is also called permanence , Once a transaction is committed , Its changes to the data in the database should be permanent , Subsequent operations or failures should not have any impact on it

How to use

createtableuser(

idintprimarykeyauto_increment,

namechar(32),

balanceint

);

insertintouser(name,balance)values

(‘wsb’,1000),

(‘lucas’,1000),

(‘mjj’,1000);

Start transaction operation before modifying data

starttransaction;

Modify the operating

updateusersetbalance=900wherename=‘wsb’;# Purchase payment 100

updateusersetbalance=1010wherename=‘egon’;# The agency took away 10 element

updateusersetbalance=1090wherename=‘ysb’;# The seller gets 90 element

Roll back to the previous state

rollback;

After opening the transaction , As long as there is no implementation commit operation , The data doesn't actually go to the hard disk

commit;# After submission , Automatically close transactions

“”“ Whether the transaction detection operation is complete , Incomplete active rollback to previous state , If it's complete, it should be executed commit operation ”""

Standing on the python The angle of the code , Pseudo code logic that should be implemented

try:

update user set balance=900 where name='wsb'; # Purchase payment 100 element
update user set balance=1010 where name='egon'; # The agency took away 10 element
update user set balance=1090 where name='ysb'; # The seller gets 90 element 


except abnormal :

rollback;


else:

commit;


stored procedure

Stored procedures contain a series of executable sql sentence , Stored procedures are stored in mysql in , By calling its name, you can execute its internal heap sql

Stored procedures can only be used under which library when they are created

Three development models

The first one is

“”"

Applications : Just develop the logic of the application

mysql: Write the stored procedure , For the application to call

advantage : Development efficiency , High efficiency of execution

shortcoming : Considering human factors 、 Cross departmental communication and other issues , Will lead to poor scalability

“”"

The second kind

“”"

Applications : In addition to the logic of developing applications , You also need to write native sql

advantage : Than the way 1, High expansibility ( Nontechnical )

shortcoming :

1、 Development efficiency , The execution efficiency is not as good as the way 1

2、 Write native sql Too complicated , And it needs to be taken into account sql Optimization of statements

“”"

The third kind of

“”"

Applications : The logic of developing applications , No need to write native sql, Process data based on a framework written by others ,ORM

advantage : No need to write pure life sql, This means that the development efficiency is higher than the way 2 high , At the same time compatible way 2 The benefits of high scalability

shortcoming : The way of execution efficiency 2 It can't be compared with

“”"

Create stored procedure

delimiter $$

createprocedurep1(

inmint,# in Indicates that this parameter must only be passed in and cannot be returned

innint,

outresint# out Indicates that this parameter can be returned , One more inout Follow out No difference , It can be either in or out

)

begin

selecttnamefromteacherwheretid > mandtid < n;

setres=0;

end$$

delimiter ;

How to use stored procedures

1、 Directly in mysql Call in

set@res=10# res The value of is used to determine whether the stored procedure is executed successfully , So we need to define a variable first @res Storage 10

callp1(2,4,10);# Report errors

callp1(2,4,@res);

View results

select@res;# Successful implementation ,@res Variable value changed

2、 stay python Call in program

pymysql link mysql

Generated cursor cursor.callproc(‘p1’,(2,4,10))# internals :@_p1_0=2,@_p1_1=4,@_p1_2=10;

cursor.execute(‘select @_p1_2;’)

3、 Examples of stored procedures and transactions ( understand )

delimiter //

createPROCEDUREp5(

outp_return_codetinyint)

begin

declareexithandlerforsqlexception# Statement

begin

– ERROR

setp_return_code =1;

rollback;

end;

declareexithandlerforsqlwarning

begin

– WARNING

setp_return_code =2;

rollback;

end;

starttransaction;

updateusersetbalance=900whereid=1;

updateusersetbalance=1010whereid=2;

updateusersetbalance=1090whereid=3;

commit;

– success

setp_return_code=0;# 0 On behalf of successful execution

end//

delimiter ;

function

Notice the difference with stored procedures ,mysql Built in functions can only be found in sql Use in statement !

CREATETABLEblog (

idINTPRIMARYKEYauto_increment,

NAMECHAR(32),

sub_time datetime


);

INSERTINTOblog (NAME, sub_time)

VALUES

(‘ The first 1 piece ’,‘2015-03-01 11:31:21’),

(‘ The first 2 piece ’,‘2015-03-11 16:31:21’),

(‘ The first 3 piece ’,‘2016-07-01 10:21:31’),

(‘ The first 4 piece ’,‘2016-07-22 09:23:21’),

(‘ The first 5 piece ’,‘2016-07-23 10:11:11’),

(‘ The first 6 piece ’,‘2016-07-25 11:21:31’),

(‘ The first 7 piece ’,‘2017-03-01 15:33:21’),

(‘ The first 8 piece ’,‘2017-03-01 17:32:21’),

(‘ The first 9 piece ’,‘2017-03-01 18:31:21’);

selectdate_format(sub_time,’%Y-%m’),count(id)frombloggroupbydate_format(sub_time,’%Y-%m’);

flow control

if Conditional statements

delimiter //

createprocedurepro_if()

begin

declareiintdefault0;

if i = 1 then


select1;

elseif i = 2 then


select2;

else


select7;

endif;

end//

delimiter ;

while loop

delimiter //

createprocedurepro_while()

begin

declarenumint;

setnum=0;

while num < 10do

select

num;

setnum=num+1;

endwhile;

end//

delimiter ;

Indexing and slow query optimization

Knowledge review : Data is stored on the hard disk , It's inevitable that query data needs to be done io operation

Index in mysql It's also called key , It is a data structure used by the storage engine to quickly find records

primary key

unique key

index key

Be careful foreign key It is not used to speed up the query , Not within the scope of our study , The three above key In addition to the effect of accelerating query, the first two have additional constraints (primary key: Non empty and unique ,unique key: only ), and index key It will only speed up your query without any constraints

The essence : By constantly narrowing the scope of data you want to get, you can filter out the final desired results , And turn random events into sequential events , in other words , With this indexing mechanism , We can always lock data in the same way

The impact of indexing :

With a lot of data in the table , Index creation will be slow

After the index is created , The query performance of the table will be greatly improved , But the performance of writing will be reduced

Only leaf nodes store real data , Root and branch nodes only store virtual data

The number of queries depends on the level of the tree , The lower the level, the fewer times

The size of a disk block is certain , That means there is a certain amount of data that can be stored , Keep the tree at the lowest level , A disk can store data that takes up less space

Need to know : Through primary key id Indexing fields can reduce the height of the tree hierarchy

Clustered index (primary key)

A clustered index is actually the primary key of a table ,innodb The engine specifies that a table must have a primary key

myisam When creating a table, there are three files corresponding to the hard disk

innodb When creating a table, there are two files corresponding to the hard disk ,frm The file only stores table structure , It's impossible to index , therefore innodb Both the index and the data are in idb In table data

characteristic : A complete record of leaf nodes

Secondary index (unique,index)

Secondary index : It's impossible to query data with id As a screening condition , May also use name,password Wait for the field information , At this time, we can't take advantage of the accelerated query effect of clustered index . You need to index other fields , These indexes are called secondary indexes

characteristic : The leaf node stores the primary key value of the record corresponding to the auxiliary index field ( such as : according to name Field creation index , So what the leaf node stores is :{name Corresponding value :name The primary key value of the record })

select name from user where name=‘lucas’;

The above statement is called non - covering index , Although the query hit the index field name, But what we need to check is age Field , So you need to use the primary key to find

Test index

Get ready

#1. Preparation form

createtables1(

idint,

namevarchar(20),

genderchar(6),

emailvarchar(50)

);

#2. Create stored procedure , Implement batch insert record

delimiter KaTeX parse error: Expected 'EOF', got '#' at position 1: #̲ Declare that the end symbol of the stored procedure is

createprocedureauto_insert1()

BEGIN

declareiintdefault1;

while(i<3000000)do

insertintos1values(i,‘jason’,‘male’,concat(‘jason’,i,’@oldboy’));

seti=i+1;

endwhile;

ENDKaTeX parse error: Expected 'EOF', got '#' at position 1: #̲ end

delimiter ;# To declare The semicolon is the ending sign

#3. View stored procedures

showcreateprocedureauto_insert1\G

#4. Calling stored procedure

callauto_insert1();

If the table does not have any indexes

select*froms1whereid=30000;

Avoid the time loss caused by printing

selectcount(id)froms1whereid=30000;

selectcount(id)froms1whereid=1;

to id Make a primary key

altertables1addprimarykey(id);# slowly

selectcount(id)froms1whereid=1;# The speed is an order of magnitude lower than before indexing

selectcount(id)froms1wherename=‘jason’# It's still very slow

“”"

The question of scope

“”"

It's not indexed , In the future, the query speed will be faster according to this field

selectcount(id)froms1whereid>1;# Speed is compared with id = 1 A lot slower

selectcount(id)froms1whereid>1andid<3;

selectcount(id)froms1whereid>1andid<10000;

selectcount(id)froms1whereid!=3;

altertables1dropprimarykey;# Delete primary key Let's study it alone name Field

selectcount(id)froms1wherename=‘jason’;# It's slow again

createindexidx_nameons1(name);# to s1 Tabular name Field creation index

selectcount(id)froms1wherename=‘jason’# Still very slow !!!

“”"

Look again. b+ The principle of trees , Data needs high differentiation , And our watch is full of jason, It's impossible to distinguish

Then the tree is actually built “ A stick ”

“”"

selectcount(id)froms1wherename=‘xxx’;

This one will be soon , I'm just a stick , The first mismatch doesn't need to go any further

selectcount(id)froms1wherenamelike’xxx’;

selectcount(id)froms1wherenamelike’xxx%’;

selectcount(id)froms1wherenamelike’%xxx’;# slow The leftmost matching feature

Fields with low discrimination cannot be indexed

dropindexidx_nameons1;

to id Create a common index for fields

createindexidx_idons1(id);

selectcount(id)froms1whereid=3;# fast

selectcount(id)froms1whereid*12=3;# slow Index fields must not participate in the calculation

dropindexidx_idons1;

selectcount(id)froms1wherename='jason’andgender ='male’andid=3andemail =‘xxx’;

For the above continuous multiple and The operation of ,mysql From left to right, the index field with high differentiation will be found first , First reduce the overall scope and then compare other conditions

createindexidx_nameons1(name);

selectcount(id)froms1wherename='jason’andgender ='male’andid=3andemail =‘xxx’;# No acceleration

dropindexidx_nameons1;

to name,gender It is not difficult to speed up the query speed by adding indexes to the fields with low differentiation

createindexidx_idons1(id);

selectcount(id)froms1wherename='jason’andgender ='male’andid=3andemail =‘xxx’;# fast Through the first id It has been said that data fast locking has become a

selectcount(id)froms1wherename='jason’andgender ='male’andid>3andemail =‘xxx’;# slow be based on id There's still a lot of data out there , And then compare the other fields

dropindexidx_idons1

createindexidx_emailons1(email);

selectcount(id)froms1wherename='jason’andgender ='male’andid>3andemail =‘xxx’;# fast adopt email Fields are closed

Joint index

selectcount(id)froms1wherename='jason’andgender ='male’andid>3andemail =‘xxx’;

If the above four fields are highly differentiated , It can speed up the query for anyone

to email Plus but not email Field

selectcount(id)froms1wherename='jason’andgender ='male’andid>3;

to name Plus but not name Field

selectcount(id)froms1wheregender ='male’andid>3;

to gender Plus but not gender Field

selectcount(id)froms1whereid>3;

The problem is that all fields are indexed but not used , It's going to take four more builds

createindexidx_allons1(email,name,gender,id);# Left most matching principle , Put it to the left with high discrimination

selectcount(id)froms1wherename='jason’andgender ='male’andid>3andemail =‘xxx’;#