toc

Transaction Actualities, Views, Triggers, and Stored Procedures at the Core of mysql

A detailed analysis of mysql essential core knowledge transactions

What is a transaction?

• A database transaction is usually an operation that reads or writes a database. The first is to provide a way for database operations to recover from failure to normal state, while providing a way for the database to maintain consistency even in abnormal state. The second is that when multiple applications access the database concurrently, they can provide a way to isolate one another to prevent interference with each other's operations.

Transaction characteristics (ACID)

• Atomicity: A transaction must be an atomic unit of work, and all statements in a transaction should be done: either do it all or do nothing at all;
• Consistency: Keep the data logically "reasonable", such as: Xiao Ming gives Xiao Hong 10,000 yuan, which will reduce Xiao Ming's account by 10,000 yuan and increase Xiao Hong's account by 10,000 yuan.
• Isolation: If multiple transactions execute simultaneously, but each transaction executes independently.
• Durability: When a transaction is successfully executed, it should be an explicit hard disk data change (not just in memory) to the data.

Be careful

• To use transactions, the engine of the table is the innodb engine

Transaction Reality of mysql Essential Core Knowledge

Opening and committing transactions:

• Opening of transaction: begin; start transaction;
• Submission of a transaction: commit;
• Rollback of transaction: rollback;

Create an account table to simulate transfers
create table account (
	id tinyint(5) zerofill auto_increment not null comment 'id number',
	name varchar(20) default null comment 'Customer Name',
	money decimal(10,2) not null comment 'Account amount',
	primary key (id)
)engine=innodb charset=utf8;

Turn on autocommit (temporary effect):

• OFF (0): Off ON (1): On

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>
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON	|

Turn on autocommit (permanent):

• Modify the configuration file: vi/etc/my. CNF is added below [mysqld]: autocommit=1 Remember to restart the service for it to take effect

Application of mysql core knowledge view

What is a view? What does a view do?

• A view is a virtual table, a logical table, and it does not contain data. Save as a select statement in the data dictionary. Views allow you to present a portion of the data from a base table (the table used to create the view is called the base table), stating that the data for the view comes from the base table.

The advantages of views are:

• Simple: Users of views do not need to care about the structure, association, and filtering conditions of the corresponding tables behind them. They are already the result set of the filtered composite conditions.
• Security: Users of views can only access the result set of the query they are allowed to query. Permission management on tables cannot be restricted to a row or a column, but it can be easily achieved through views.
• Data independence: Once the structure of the view is determined, changes in the table structure can be shielded from the user's impact. Adding columns to the source table has no effect on the view. If the source table modifies the column name, it can be resolved by modifying the view without affecting the visitor.
• Space Free: Views are logical tables that don't take up memory space
• In summary, most of the cases where views are used are to ensure data security and improve query efficiency.

View creation and modification

The basic syntax you create is:

	create view <View Name> as select Sentence;
	create view <View Name> (field) as select Sentence;
	create or replace view <View Name>;

The modified grammar is:

alter view <View Name> as select Sentence;

Deleted Syntax

drop view <View Name> ;

Disadvantages of Views

• Poor performance: sql server must transform a view query into a query on a basic table. If the view is defined by a complex multi-table query, it will take some time for sql server to turn it into a complex combination even if it is a simple query on a view.
• Modification restrictions: When a user tries to modify some of the information he or she is trying to modify, the database must convert it into a modification of some of the information in the basic table. This is convenient for a simple attempt, but may not be modifiable for a more complex attempt.

Trigger introduction to mysql

What is a trigger

• A trigger is to monitor a situation and trigger an action

Syntax for creating triggers:

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

after/before:Can be set before or after an event occurs
insert/update/delete:They can be executed insert,update or delete Trigger in process
for each row:Perform actions every other line

Deleted Syntax

drop trigger Trigger Name;

Demonstration

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

delimiter // End symbol for custom statement
mysql> delimiter //
mysql>
mysql> create trigger trig_work after insert on work_time_delay
	-> for each row
	-> begin
	-> update employee set sal=sal-100 where empno=new.empno;
	-> end
	-> //
Query OK, 0 rows affected (0.01 sec)
new: Refers to the occurrence of an event before perhaps after Saved new data

Introduction to mysql's stored procedures

What is a stored procedure?

• Stored procedures encapsulate a complex series of operations into a single process. Similar to shell, python script, etc.

Advantages and disadvantages of stored procedures

• The advantages are:
  • Complex operation, simple invocation
  • Fast
• The drawbacks are:
  • Complex encapsulation
  • No flexibility

Create stored procedure syntax:

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 value can be literal or a variable)
		out: Represents a procedure passing a value to the caller(Multiple values can be returned)(Outgoing value can only be a variable)
		inout: Represents both the value passed by the caller to the procedure and the value passed by the procedure to the caller (values can only be variables)

• Declare variables: declare variable name type (length) default default value;
• Assign values to variables: set @variable name=value;
• Call storage command: call name (@variable name);
• Delete stored procedure command: drop procedure name;
• View the created stored procedure commands:
  • show create procedure name\G;

Create a simple stored procedure:
mysql> delimiter //
mysql> create procedure name(in n int)
	-> begin
	-> select * from employee limit n;
	-> end
	-> //
Query OK, 0 rows affected (0.00 sec)
mysql> set @n=5;
	-> //
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> 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();