1, View

1. What is a view

The view is to get a virtual table through query, then save it and use it directly next time

2. Why use views

If you want to use a virtual table frequently, you can avoid repeated queries

3. How to use views

create view teacher2course as 
select * from teacher inner join course on teacher.tid = course.teacher_id;

be careful:

1. In the hard disk, the view only has a table structure file and no table data file

2. Views are usually used for queries. Try not to modify the data in the view

# Delete view
drop view teacher2course;

4. Thinking: will views be used in the development process

Generally not! (extra table)

View is the function of mysql. If you use a large number of views in your project, it means that when you want to expand a function later, this function happens to need to modify the view, which means that you need to modify the view on the mysql side first, and then modify the corresponding sql statement in the application, which involves cross departmental communication, Therefore, the view is usually not used, but the function is extended by modifying the sql statement

2, Trigger

1. What is a trigger

The function of automatic trigger is called trigger when the data of a table is added, deleted and modified

2. Why use triggers

The trigger is specifically used to add insert, delete and update to a table data. Once this kind of behavior is executed
It will trigger the execution of the trigger, that is, automatically run another piece of sql code

3. Create trigger syntax

"""Grammatical structure
create trigger The name of the trigger before/after insert/update/delete on Table name for each row
begin
    sql sentence
end
"""

# For insertion
create trigger tri_after_insert_t1 after insert on Table name for each row
begin
    sql code...
end 
create trigger tri_after_insert_t2 before insert on Table name for each row
begin
    sql code...
end

# For deletion
create trigger tri_after_delete_t1 after delete on Table name for each row
begin
    sql code...
end
create trigger tri_after_delete_t2 before delete on Table name for each row
begin
    sql code...
end

# For modification
create trigger tri_after_update_t1 after update on Table name for each row
begin
    sql code...
end
create trigger tri_after_update_t2 before update on Table name for each row
begin
    sql code...
end

"""
Attention should be paid to writing sql The code terminator is; A semicolon is also required at the end of the entire trigger;
There will be a syntax conflict and we need to modify the closing symbol temporarily
delimiter $$
delimiter ; 
This syntax is only valid in the current window  
"""

# 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
);

delimiter $$  # take mysql The default terminator is;change into $$
create trigger tri_after_insert_cmd after insert on cmd for each row
begin
    if NEW.success = 'no' then  # New records will be MySQL Package into NEW object
        insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
    end if;
end $$
delimiter ;  # Remember to change it back after it is finished, otherwise the following terminators will be all $$Yes

#To the table cmd Insert a record in and trigger the trigger according to IF The condition determines whether to insert the error log
INSERT INTO cmd (
    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');

# query errlog Table record
select * from errlog;
# Delete trigger 
drop trigger tri_after_insert_cmd;

3, Business

1. What is a transaction

Starting a transaction can contain some sql statements, which either succeed at the same time
Or none of them will succeed, ensuring the security of the data

2. The function of things

It ensures the data security of data operation

For example: use the card of Bank of communications to operate the ATM machine of CCB to transfer money to the account of industry and commerce

 

Transactions should have four attributes: atomicity, consistency, isolation and persistence. These four attributes are commonly referred to as ACID properties.

atomicity. A transaction is an inseparable work unit, and all operations included in the transaction are either done or not done.

Consistency. The transaction must be to change the database from one consistency state to another. Consistency and atomicity are closely related.

isolation. The execution of one transaction cannot be disturbed by other transactions. That is, the operations and data used within a transaction are isolated from other concurrent transactions, and the concurrent transactions cannot interfere with each other.

Persistence. Persistence, also known as permanence, means that once a transaction is committed, its changes to the data in the database should be permanent. The following other operations or faults should not have any impact on it.

3. How to use transactions

# Three keywords of transaction

# Start the transaction operation before modifying the data
start transaction;

# Rollback to previous state
rollback;

# After the transaction is started, as long as it is not executed commit In fact, the data is not really refreshed to the hard disk
commit;

create table user(
id int primary key auto_increment,
name char(32),
balance int
);

insert into user(name,balance)
values
('jason',1000),
('egon',1000),
('tank',1000);

# Start the transaction operation before modifying the data
start transaction;

# Modify operation
update user set balance=900 where name='jason'; #Buy pay 100 yuan
update user set balance=1010 where name='egon'; #The intermediary took 10 yuan
update user set balance=1090 where name='tank'; #The seller gets 90 yuan

# Rollback to previous state
rollback;

# After the transaction is started, as long as it is not executed commit In fact, the data is not really refreshed to the hard disk
commit;
"""Start the transaction to check whether the operation is complete. If it is incomplete, actively roll back to the previous state. If it is complete, it should be executed commit operation"""

# Stand python From the perspective of code, the pseudo code logic that should be implemented,
try:
    update user set balance=900 where name='jason'; #Buy pay 100 yuan
    update user set balance=1010 where name='egon'; #The intermediary took 10 yuan
    update user set balance=1090 where name='tank'; #The seller gets 90 yuan
except abnormal:
    rollback;
else:
    commit;

4, Stored procedure

1. What is a stored procedure

The stored procedure contains a series of executable sql statements. The stored procedure is stored in MySQL. By calling its name, you can execute a pile of internal sql, which is similar to the custom functions in python

2. Basic use of stored procedures

delimiter $$
create procedure p1()
begin
    select * from user;
end $$
delimiter ;

# call
call p1()

3. Create stored procedure

# Introduce the characteristics of formal parameters and then write specific functions

delimiter $$
create procedure p2(
    in m int,  # in Indicates that this parameter must only be passed in and cannot be returned
    in n int,  
    out res int  # out Indicates that this parameter can be returned
)
begin
    select tname from teacher where tid > m and tid < n;
    set res=0;  # Used to mark whether the stored procedure is executed
end $$
delimiter ;


# in the light of res You need to define it in advance
set @res=10;  definition
select @res;  see
call p1(1,5,@res)  call
select @res  see

4. How to use stored procedures

# Major premise:Which repository can only be created in the following process!!!

# 1,Directly in mysql Call in
set @res=10  # res The value of is used to judge whether the stored procedure is successfully executed, so you need to define a variable first@res Storage 10
call p1(2,4,10);  # report errors
call p1(2,4,@res);  

# View results
select @res;  # Successful execution,@res The value of the variable has changed

# 2,stay python Call in program
pymysql link mysql
 Generated tour table cursor.callproc('p1',(2,4,10))  # Internal principle:@_p1_0=2,@_p1_1=4,@_p1_2=10;
cursor.excute('select @_p1_2;')


# 3,Examples of using stored procedures and transactions(understand)
delimiter //
create PROCEDURE p5(
    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;
      update user set balance=900 where id =1;
      update user123 set balance=1010 where id = 2;
      update user set balance=1090 where id =3;
  COMMIT;

  -- SUCCESS
  set p_return_code = 0; #0 Represents successful execution


END //
delimiter ;

5, Function

Note the difference between mysql and stored procedures. The built-in functions of mysql can only be used in sql statements!

Reference blog: http://www.cnblogs.com/linhaifeng/articles/7495918.html#_label2

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'),
    ('Part 3','2016-07-01 10:21:31'),
    ('Part 4','2016-07-22 09:23:21'),
    ('Part 5','2016-07-23 10:11:11'),
    ('Part 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 Conditional statement
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 ;

END //
delimiter ;

7, Index

Knowledge review: the data is stored on the hard disk, so the query of data inevitably needs IO operation

An index is a data structure, similar to a book directory. It means that you should look for the directory before looking for the data in the future, rather than turning the page to query the data

Index is also called "key" in MySQL. It is a data structure used by storage engine to quickly find records.

• primary key

• unique key

• index key

Note that the foreign key is not used to speed up the query and is not within the scope of our research. The first two of the above three keys have additional constraints in addition to the effect of speeding up the query (primary key: non empty and unique, unique key: unique), while the index key has no constraint function and will only help you speed up the query

The essence is: filter out the final desired results by constantly narrowing the range of data you want to obtain, and turn random events into sequential events. In other words, with this indexing mechanism, we can always lock the data in the same way.

Impact of index:

• On the premise of a large amount of data in the table, the speed of creating an index will be very slow

• After the index is created, the query performance of the table will be greatly improved, but the write performance will be reduced

 

b + tree

https://images2017.cnblogs.com/blog/1036857/201709/1036857-20170912011123500-158121126.png

Only leaf nodes store real data, while root and branch nodes store only virtual data

The number of queries is determined by the level of the tree. The lower the level, the fewer times

The size of a disk block is certain, which means that the amount of data that can be stored is certain. How to ensure the lowest level of the tree? A disk block stores data items that take up less space

Think about what fields we should index in a table, which can reduce the hierarchical height of the tree > > > primary key id field

Clustered index (primary key)

Clustered index actually refers to the primary key of a table. innodb engine stipulates that a table must have a primary key. Let's review the storage engine first.

How many files (three) are there on the hard disk when myisam is creating a table?

How many files (two) are there on the hard disk when innodb is creating a table? The frm file only stores the table structure and cannot put the index, which means that the index and data of innodb are put in the idb table data file.

Features: complete records of leaf nodes

Unique index

Auxiliary index: when querying data, you can't always use id as the filter condition, or use field information such as name and password. Then you can't take advantage of the accelerated query effect of clustered index at this time. You need to index other fields. These indexes are called auxiliary indexes

Features: the leaf node stores the value of the primary key of the record corresponding to the auxiliary index field (for example, if you create an index according to the name field, the leaf node stores the value corresponding to {Name: the primary key value of the record where name is located})

select name from user where name='jason';

The above statement is called overlay index: all the data we want has been found only in the leaf node of the auxiliary index

select age from user where name='jason';

When the query statement hits the field name, it needs to use the index to find the field name, but it needs to use the key to overwrite the field name