1, Business

Characteristics of transactions

1. Atomicity: a transaction must be an atomic unit of work. All statements in a transaction must be done or none of them.
2. Consistency: keep the data logically "reasonable". When A transfers to B, A must pay less and B must pay more
3. Isolation: if multiple transactions are executed concurrently, each transaction is independent
4. Durability: a successful transaction is a hard disk data change (not just memory level) for data

In MySQL, if a table needs to use transactions, you need to set the table engine as innodb engine

1. Basic operation of transaction

-- Start of transaction:
begin;
start transaction;
-- Transaction commit: (before commit, the data will only be saved in memory and will not cause modification of hard disk data)
commit;
-- Rollback of transaction:
rollback;

Why can general sql (non transaction) take effect directly (affecting hard disk data) after one line?
Because of this setting. autocommit=1
It is equivalent to that the database will add a commit for us by default every time we execute sql; Command, auto submit

If the auto submit setting is turned off, the operation will only affect the memory, not the hard disk.

2. Enable autocommit (temporary effect):

OFF (0): OFF
ON (1): indicates ON

-- ordinary sql Instant update
show variables like 'autocommit';

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
mysql> set autocommit=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |

3. Enable autocommit (permanent):

-- To modify a profile:
vi /etc/my.cnf 
-- stay[mysqld]Add below:
autocommit=1 
-- Remember to restart the service before it takes effect

2, View

1. What is a view

• A view is a virtual table and a logical table. It does not contain data. Stored in the data dictionary as a select statement.
• Through the view, you can display part of the data of the base table (the table used to create the view is called the base table). In other words, the data of the view comes from the base table

2. Advantages of view

• Simple: users using the view do not need to care about the structure, association conditions and filter conditions of the corresponding table. For users, it is already the result set of filtered composite conditions.
• Security: users using the view can only access the result set they are allowed to query. The permission management of the table cannot be limited to a row or a column, but it can be simply realized through the view.
• Data independence: once the view structure is determined, the impact of table structure changes on users can be shielded. Adding columns to the source table has no impact on the view; If the column name of the source table is modified, it can be solved by modifying the view without affecting visitors.
• No space: views are logical tables that do not occupy memory space. In short, most of the views are used to ensure data security and improve query efficiency.

3. View creation and modification

-- The basic syntax for creating is:
create view <View name> as select sentence;
create view <View name> (field) as select sentence;
create or replace view <View name>; --Replace if present

The modified syntax is:
alter view <View name> as select sentence;

View deletion syntax:
drop view <View name> ;

• Disadvantages of view

• Poor performance: sql must convert the view query into the query of the basic table. If the view is defined by a complex multi table query, even a simple query of the view will take some time to turn it into a complex combination.
• Modification limitation: when users try to modify some information of the view, the database must convert it into modification of some information of the basic table. This is very convenient for simple attempts, but it may not be modifiable for more complex views. Such as associated foreign keys and so on.

3, Trigger

1. What is a trigger?

A trigger is to trigger another operation when monitoring a situation

2. View trigger information

show create trigger Trigger Name \G 
-- \G You can format the output 

3. Create trigger

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

-- explain
after/before:It can be set before or after the event
insert/update/delete:They can be executed in insert,update or delete Triggered during
for each row:Perform the action every other line

4. Delete trigger

drop trigger Trigger Name ;

• demonstration

Create an employee lateness table:
 create table work_time_delay(
            empno int not null comment 'Employee number',
            ename varchar(50) comment 'Employee name',
            status int comment 'state'
            );

-- Tips: custom end symbol delimiter
-- Put each sql Replace the ending symbol with//
delimiter // End symbol of custom statement

-- Put each sql Replace the ending symbol with//
mysql> delimiter //

-- Create a trigger
-- When detected work_time_delay There are in the table insert During the operation, 14 lines of update operation will be performed
-- 14 Line program where After, new Means work_time_delay New data in the table, new.empno Refers to the column information of the new data
mysql> create trigger trig_work after insert on work_time_delay
    -> for each row
    -> begin
    -> update employee set sal=sal-100 where employee.empno=new.empno;
    -> end
    -> //
Query OK, 0 rows affected (0.01 sec)

new: Refers to the occurrence of an event before perhaps after New data saved

4, Stored procedure

1. What is a stored procedure

It is equivalent to encapsulating complex operations into a "process". It is similar to writing a function function.

2. Advantages and disadvantages of stored procedures

• advantage:
  • Complex operation and simple call
  • Fast speed
• Disadvantages:
  • Complex packaging
  • No flexibility

3. Create stored procedure

create procedure name (parameter....)
        begin
         Process body;
         Process body;
         end
         
Parameters: in|out|inout Parameter name type (length)
        in: Indicates that the caller passes in a value to the procedure (the passed in value can be literal or variable)
        out: Indicates that the procedure passes out a value to the caller(Multiple values can be returned)(Outgoing value can only be variable)
        inout: It means that the caller passes in a value to the procedure and the procedure passes out a value to the caller (the value can only be a variable)
        
Declare variables: declare Variable name type(length) default Default value;
Assign values to variables: set @Variable name=value;

• Call stored procedure command

call name(@Variable name);

• Delete stored procedure command

drop procedure name;

• Viewing stored procedure information

show create procedure name\G;

4. Case

-- Create a stored procedure
create procedure  name(in n int)
    begin
    select * from employee limit n;
    end
-- Set a variable
set @n=5
-- Call name is name Stored procedure for
call name(@n)

mysql>         create procedure  name()
    ->             begin
    ->             declare n int default 6;
    ->             select * from employee limit n;
    ->             end
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> call name();