MySQL view, stored procedure, variable, function, transaction

1. View

Meaning: virtual table, but it is the same as that of ordinary table
MySQL5. After 1, the characteristics appear through the data of dynamic production

Syntax:
 		create  view  View name
 		as
 		Query statement   

		Create syntax			Does it actually occupy physical space		use
 surface		create	table		Data maintained					Add, delete, modify and check
 view	create  view		Only saved SQL logic				Mainly for query (data can be modified, data cannot be added or deleted)

Delete view
	 	Syntax: drop  view   View name
 		drop  view  v2;

Modify the contents of the view:
Mode 1:
	create  or  replace  view  View name
	as
 	Query statement

Mode 2:
	alter  view  View name
	as
	Query statement

Query table structure
	desc  employess;
Query view structure
	desc  v1;

be careful:
An attempt was made not to allow updates
Including grouping function, group by, having and de duplication

2. Stored procedure

stored procedure
Similar to methods in Java
Meaning: a set of pre changed SQL statements, which can be used to understand batch processing statements
Benefits:
1. Improve SQL reusability
2. Simplify operation
3. The compilation times are reduced and the connection times with the database server are reduced, which improves the efficiency

Create syntax:
	create  procedure  Stored procedure name([parameter list])
	begin
	 	Stored procedure body (a group) SQL Statement)
	end 
	 
	use[ ]It means you can not write
	
be careful:
	1. Parameter list: parameter model     Parameter name     Parameter type
		
	   Parametric model:
		 in:This parameter can be used as input, that is, it needs to be inserted when calling the stored procedure
		 out: This parameter can be used as output, that is, it can be used as return value
		 inout: This parameter can be used as input or output, that is, it needs to be passed in or returned
	2. If there is only one statement in the stored procedure, begin and end It can be omitted
	3. Each in the stored procedure body SQL A semicolon is required at the end of the statement
				delimiter  Reset (end tag)

Case: sum two numbers (two int -- > in and one out type parameter)

create procedure pro1_sum(in a int,in b int,out c int)
begin
	-- c Is the output result   set:Assign values to variables
	set c = a + b;
end;

Use of stored procedures
	Call stored procedure:
		call  Stored procedure name()；
	To call a stored procedure with parameters:
		call  Stored procedure name(parameter)；
 
 be careful:
 	If in the parameter in Type. The parameter can be numeric or variable
 	about out or inout Type, parameter must be variable
 	MySQL Variables in must be@start
 	For example: call pro_sum(@Variable name,@Variable name)

Case:

@result Is a variable used to receive the return value in the stored procedure
call pro1_sum(10,20,@result);
selecct @result;


call pro1_sum(@a,@b,@c);
select @c;

Delete stored procedure

drop procedure pro1_sum;

Case: query the average salary of employee table

nothing in  nothing out
create procedure pro2_avg()
begin
	select round(avg(salary),2) as 'average wage' from employees;
end;

#Call stored procedure
call pro2_avg();

Case: pass an employee number and output the employee information

have in  nothing out
create  procedure  pro3(in empId int)
begin
	select * from employees where employee_id = empId;
end;

#Call stored procedure
call pro3(123);
#Delete stored procedure
drop procedure pro3;

Case: query the total salary of the company

nothing in have out
create procedure pro4(out totalMoney double)
begin
	Assign values to variables set 
	select value into Variable name
	select sum(salary) into totalMoney from employees;
end;

#Call stored procedure
call pro4(@temp);
select @temp;

Case: paging stored procedure

	8 Parameters (6 inputs, 2 outputs)
	limit (Current page number-1)*Entries per page
	
	6 Input:
		Table name				tableName
		show field name 	fieldList
		sort				orderStr
		query criteria		whereStr
		Entries per page	pageSize
		Current page number		pageNum
	2 Outputs
		Total records	totalSize
		PageCount 		totalPage

create procedure pro_fenye(
  in tableName varchar(20),
  in fieldList text,
  in orderStr text,
  in whereStr text,
  in pageSize int ,
  in pageNum int,
  out totalSize int,
  out totalPage int
)
BEGIN
  -- Use temporary variables to store splices SQL，Then execute it SQL Statement to get results
  -- Total records  totalSize
  -- select count(*) into @totalSize from Table name where condition
  set @sql = CONCAT_WS(' ','select count(*) into @totalSize from',
                tableName,whereStr);

  prepare count_sql from @sql;-- Ready to execute SQL sentence
  execute count_sql;-- implement SQL sentence
  deallocate prepare count_sql;

  -- Assign values to variables
  set totalSize = @totalSize;
  
  -- Total pages: totalSize and pageSize

  if(pageSize<=0)THEN  
      set pageSize = 5;-- Five pieces of data are displayed by default
  end if;
  set totalPage = ceil(totalSize/pageSize);

  -- Judge whether the page number meets the requirements
  if(pageNum<1)then
    set pageNum = 1;
  elseif(pageNum > totalPage)then
    set pageNum = totalPage;
  end if;

  -- Splicing and paging SQL sentence
  set @fenyeSql = CONCAT_WS(' ','select',fieldList,'from',
            tableName,whereStr,orderStr,'limit',
            (pageNum - 1)*pageSize,',',pageSize);
  -- implement SQL
  prepare fenyeSql from @fenyeSql;
  execute fenyeSql;
  deallocate prepare fenyeSql;-- Cancel execution SQL

  select @fenyeSql from dual;

END;

3. Variables

mysql variable

1. System variables are variables that the system has defined in advance
   It usually has special meaning,
   Set names -- > session variable (only when the session is connected)
   Global variable -- > always in effect

View system variables
show variables;


select  @@The variable name calls the system variable
select  @@hostname;

1. User variable
   In order to distinguish between system variables and user-defined variables, the system stipulates that user-defined variables must use an @ symbol

Definition of variables:
	set  @Variable name  =  Value;
	select  @Variable name  :=  Value;
	select  value  into   @Variable name;

Note: user variables can be used directly without declaration and definition, but the default is NULL value
	 User variable, session level variable, valid only for the current connection

for example:
	declare  @b  int default  100;      declare Yes set default
	set  @a  =  100;
	select  @a := 200;
	select  300  into  @a;


The following is not a query statement, but an assignment statement
select salary,last_name into @sal,@name from employees where employee_id = 100;
select @sal,@name;

1. local variable
   Since local variables are also user-defined, all users can think that local variables are also user variables, but local variables do not need to be used@
   Local variables are generally defined in SQL statement blocks, such as stored procedures
   Definition: use delcare to declare local variables, which can be followed by default to give a default value

Example:
	declare a int;
	declare b int default 100;
	Set variable name = Value;
	set Variable name = value;
	Gets the value of the variable
	select Variable name; 

create  procedure  my01()
begin
	declare a int default 100;
	declare b int;
	set b = 20;
	select a;
	select b;
end;

call my01();

4. Function

Definition: a function stores a series of SQL Statement, calling the function is to execute these statements at one time SQL Statement, all functions can reduce statement repeatability

Differences between stored procedures and functions:
	A function has only one return value. It is not allowed to return a result set
	The stored procedure has no return value

Function creation:	
	create function Function name([parameter list]) returns data type
	begin
		SQL sentence
		return value;
	end; 
	Parameter list: variable name data type

Function call:
	select  Function name(Argument)
	Stored procedure: call Stored procedure name
 Delete function
	drop function Function name;

Case:

Find the sum of two numbers
	create function fun_sum(a double,b double)
	returns double
	begin
		return a+b;
	end;
	
Call function
	select fun_sum(1.2,9.9);	

Case: define a function to find the total number of pages totalPage

totalPage = ceil(Total records/Entries per page)
create function fun_totalpage(totalSize int,pageSize int)
returns int
begin
	return CEIL(totalSize / pageSize);
end;

select fun_totalpage(100,20) as 'PageCount ';

Delete function
drop function fun_totalpage;




Use variables
create function fun_totalpage(totalSize int,pageSize int)
returns int
begin 
	Create a local variable
	declare num int;  No assignment, value is null
	if(totalSize mod pageSize = 0)then
		set num = totalSize div pageSize;   -- to be divisible by
	else
		set num = totalSize div pageSize+1;
	end if;

	return num;
end;

call
select fun_totalPage(16,3);

Case: date formatting

create function fun_format(d datetime)
returns varchar(20)
begin
	return date_format(d,'%Y year-%m month-%d day');
end;

select fun_format(now());

5. Services

1,What is a transaction?
	A smallest non separable work unit. Usually, a transaction corresponds to a complete business (for example, bank account transfer business)
	A complete business needs batch DML(insert,update,delete)Joint completion of statements

	Transaction only and DML Statement related, or DML Statements have transactions
	Transaction processing can maintain the integrity of the database, which ensures the batch SQL Statement operations are either executed or not executed at all
	 
2,What effect does the transaction want to achieve?
	(1)Reliability: the database should ensure insert or update When an exception is thrown or the database goes down, ensure that the data is consistent,
	(2)Concurrent processing: when multiple requests come (many people operate the data of a table at the same time), one request will affect the data modification operation
	In order to avoid dirty reading, unreal reading and non repeatable reading, it is necessary to isolate the read and write operations between transactions. There are four types of isolation.

Case: transfer operation understanding transaction

The transfer business of a bank is a complete business and the smallest unit is inseparable

Create bank account table
create table tb_account(
	aNo int PRIMARY key auto_increment,
	balance decimal(10,2)
);

insert into tb_account(balance) values(1000);

select * from tb_account;

Transfer operation
1 --->2  Turn 500
update tb_account
set balance = balance - 500
where aNo = 1;
------------problem-----------------------
update tb_account
set balance = balance+500
where aNo = 2;

Above two DML Statements must both succeed or fail.
The smallest unit cannot be subdivided
 When the first article DML After the statement is executed successfully, the data of the first account in the underlying database cannot be modified
 Just record the operation. This record is completed in memory when the second one DML After the statement is executed successfully
 Complete data synchronization with the data of the underlying database file
 If article 2 DML If the statement fails to execute, all historical operation records will be cleared
 In order to complete the above functions, transactions must be used


Use transaction to complete transfer
start transaction;   Start transaction manually

update tb_account
set balance = balance - 500
where aNo = 1;
-----------problem----------------
update tb_account
set balance = balance + 500
where aNo = 2;

commit;  ----Commit the transaction and synchronize it to the database file

Four characteristics of transaction (ACID)

1.Atomicity A: Transaction is the smallest unit and cannot be further divided
		All operations in a transaction, either all successful or all unsuccessful, will not end at a certain stage in the middle
		If an error occurs during the execution of a transaction, it will be rolled back rollback To the state before the start of the transaction, as if it had not been executed
		
2.uniformity C: Transaction requires all DML Statement operations must be guaranteed to succeed or fail at the same time
 Before the transaction starts and after the transaction ends, the integrity of the database is not damaged. For example, the total balance of two users before the transfer is 2000, and after the transfer should be 2000, which cannot be more or less out of thin air

3.Isolation I: affair A And affairs B Isolation between
	Data transportation allows multiple concurrent transactions to read, write and modify their data at the same time. Isolation can prevent data inconsistency caused by cross execution when multiple transactions are executed concurrently.
	Different transaction isolation levels lead to different results
	stay MySQL Only used in innoDB Only the storage engine supports transactions
	Transaction isolation level:
		-- Uncommitted read  read uncommitted
		-- Read committed read committed
		-- Repeatable reading repeatable read
		-- Serially readable serializable		When a user accesses a table, other users cannot access it
		
4.persistence D: It is the guarantee of the transaction and the sign of the end of the transaction (the memory data is persistent to the hard disk)
once commit or rollback，After the transaction is completed, the modification of the data is permanently synchronized to the database file

Transaction related concepts TCL

Submission: commit
 Rollback: rollback

When will the transaction start and end

Start flag:
	Any one DML Statement( insert,update,delete)Execution marks the start of the transaction
 End flag:
	commit Submit: successful completion, all DML Statement operation history and underlying disk data are synchronized
	rollback Rollback: at the end of failure, all DML Clear all records of statement operation
	
	For previous studies DML No transaction related knowledge is used in the statement,
	because DML Statement execution will automatically start the transaction, and then automatically end the transaction after execution

Isolation level of transaction

-- Set transaction isolation level
set session transaction isolation level read uncommitted;
-- Enable manual submission
set autocommit = 0;
-- Open transaction
start transaction;

-- DML sentence

-- End transaction
rollback;

1,Read uncommitted read uncommitted
 affair A And affairs B，affair A Uncommitted data, transactions B Can read
 Business here B The data read is "dirty data"
This isolation level is the lowest, mysql The default isolation level of the database is higher than this level

#Dirty reading
 Open two cmd，To simulate transactions A And affairs B

affair A: 
Sign in: mysql -u root -p password
 Set isolation level:
set session transaction isolation level read uncommitted;
View the current isolation level
select @@tx_isolation;
Set manual submission
set autocommit = 0;
Open transaction
start transaction;
Add a piece of data
insert into surface values(value);

affair B
 Sign in: mysql -u root -p password
 Set isolation level:
set session transaction isolation level read uncommitted;
View the current isolation level
select @@tx_isolation;
Data of query table
select * from surface; -- Can see transactions A Added data(Not submitted)


affair A: 
rollback; -- The transaction rolls back and returns to the state before the transaction is started
 affair B: 
select * from surface;

2,Read committed read committed
 affair A And affairs B，affair A Committed data, transactions B To read
 This isolation level is higher than read uncommitted

3,Read repeatable repeatable read [mysql [default isolation level]
affair A And affairs B，affair A Data after submission, transaction B Cannot read, transaction B Is repeatable data

#Unreal reading
 affair A: 
Sign in: mysql -u root -p password
 Set isolation level:
set session transaction isolation level read committed;
View the current isolation level
select @@tx_isolation;

affair B
 Sign in: mysql -u root -p password
 Set isolation level:
set session transaction isolation level read committed;
View the current isolation level
select @@tx_isolation;
View table data
select * from surface -- Only the original data


affair A
 Set manual submission
set autocommit = 0;
Open transaction
start transaction;
Add a piece of data
insert into surface values(value);
Commit transaction
commit;


affair B
 View table data
select * from surface -- Only the original data

4,Serialization  serializable
 affair A And affairs B，affair A When operating the database, the transaction B Can only wait in line
 This isolation level is rarely used, resulting in poor user experience and low throughput
 This level can avoid "unreal reading", and each reading is the real data of the database



Dirty read:
1,In transaction A Transaction during execution A The data has been modified and the transaction B Read transaction A Modified data
2,For some reason, the transaction A The submission was not completed. What happened rollback Operation, transaction B The data read is dirty data
	This phenomenon of reading uncommitted data from another transaction is dirty reading( Dirty Read)
	
Non repeatable:
affair B The data was read twice, during which the transaction occurred A Modified data, resulting in transaction B The data read out twice is inconsistent
 In the same transaction, the data read before and after two times is inconsistent, which is non repeatable

Unreal reading:
affair B Read the data of the same range twice before and after the transaction B Transactions during two reads A Added data, resulting in transaction B The content data is not seen in the last query
 Phantom reading is somewhat similar to non repeatable reading, but phantom reading emphasizes the increase or decrease of sets rather than the update (modification) of a single piece of data

Set isolation level

Set isolation level
set session transaction isolation level Isolation level;
View the current isolation level
select @@tx_isolation

The only storage engine that supports things is InnoDB

Why do you need things

If a user submits an order, this data contains two information: user information and purchased goods information, which need to be stored in the user table and goods table respectively. If things are not used, it may appear that the goods information is inserted successfully, but the user information is not inserted successfully, and then there will be ownerless goods; Users pay, but they don't get goods. If things are adopted, it can be ensured that both user information and commodity information must be inserted successfully before the transaction is considered successful.

Object reservation point

hold tb_01 Table deletion
drop table tb_01;

create table tb_01(
	id int primary key,
	name varchar(20),
	pwd varchar(20)
)

select * from tb_01;

Set manual submission
set autocommit = 0;
Open transaction
start transaction;
Insert a piece of data
insert into tb_01 values(1,'admin','123');
Create a retention point
savepoint s1;

Insert a piece of data
insert into tb_01 values(2,'abc','456');
Query data found two pieces of data
select * from tb_01;

Back to the reservation point
rollback to s1;

Query a piece of data
select * from tb_01;

RollBACK 
rollback;