MySQL transactions and views, MySQL transaction views

MySQL transactions and views, MySQL transaction views

　　　　　　　　　　　　　　　　Transactions

1. Concept

A set of one or more SQL statements!
Transactions: a collection of operations. They are born together. Either the execution is successful or the execution fails.
2. Transaction Features ACID
A: The Atomicity is complete and inseparable.

Atomicity: the operations in a transaction are either executed or not executed!

C: After a consistent transaction is executed, the data status is consistent ()

Consistency: the transaction must ensure that the database changes from one consistent state to another consistent state!

I: ISOLation Two transactions are not affected by each other

Isolation: Every transaction does not interfere with each other! Even concurrent execution does not interfere!

D: After a permanent transaction is executed, the impact on the data is permanent.

Durability: Once a transaction is changed, the impact on data in the database is permanent!

3. Keywords of transaction operations
Set autocommit = 0
Start transaction/begin
Commit transaction commit
Roll back the transaction rollback
Set autocommit = 1
4. Example of transaction ++ stored procedure transfer

DELIMITER $
Create procedure usp_transfer ()
BEGIN
# The default error count is 0.
DECLARE t_error integer default 0;
# Continue hanlder execution and assign the t_error value to 1 again
Declare continue handler for sqlexception set t_error = 1;
SET autocommit = 0;
Start transaction;
UPDATE bank SET balance = balance-100 WHERE cid = 1;
UPDATE bank SET balance = balance + 100 WHERE cid = 2;
IF t_error> 0 THEN
ROLLBACK;
ELSE
COMMIT;
End if;
SET autocommit = 1;
END $
DELIMITER;
 
CALL test_sp800 ()

 

The simplest syntax for defining a stored procedure is
Delimiter $
Create procedure usp_stulist ()
Begin
Select * from student
End $
Delimiter;

 

Simulate bank transfer!

-- Create a database
Create database myBank;
-- Switch the specified database
USE myBank;
-- Create a table
Create table if not exists bank (
CustomerName VARCHAR (10) not null comment 'username ',
CurrentMoney DECIMAL (10, 2) not null comment 'account balance'
);
-- Insert data
Insert into bank VALUES ('black Black', 50000), ('White ', 500000 );

-- Transfer money to Tom 10000
-- Modify two data items
-- 01. Small black-10000
-- 02. xiaobai+ 10000

UPDATE bank SET currentMoney = currentMoney-10000
WHERE customerName = ' ';
-- Intentionally write the error field name to make 02 report an error
UPDATE bank SET currentMoneysss = currentMoney + 10000
WHERE customerName = 'xiaobai ';
 

-- Start transaction or BEGIN
-- 01.02 is a transaction
BEGIN
UPDATE bank SET currentMoney = currentMoney-10000
WHERE customerName = ' ';
UPDATE bank SET currentMoneysss = currentMoney + 10000
WHERE customerName = 'xiaobai ';
-- Transaction ROLLBACK
-- COMMIT transaction COMMIT

UPDATE bank SET currentcurrency = 500000
WHERE customerName = ' ';
-- Proves that mysql commits transactions by default!

SET autocommit = 0 (disable automatic transaction commit) | 1 (enable automatic transaction commit)

-- Disable automatic transaction commit
SET autocommit = 0;

BEGIN; -- start the transaction
UPDATE bank SET currentMoney = currentMoney-10000
WHERE customerName = ' ';
UPDATE bank SET currentMoney = currentMoney + 10000
WHERE customerName = 'xiaobai ';
COMMIT; -- manually submit a transaction
Updated bank SET currentMoney = 10000;
ROLLBACK; -- transaction ROLLBACK

SET autocommit = 1; -- Restore automatic submission

1. Concept

A set of one or more SQL statements!
Transactions: a collection of operations. They are born together. Either the execution is successful or the execution fails.
2. Transaction Features ACID
A: The Atomicity is complete and inseparable.

Atomicity: the operations in a transaction are either executed or not executed!

C: After a consistent transaction is executed, the data status is consistent ()

Consistency: the transaction must ensure that the database changes from one consistent state to another consistent state!

I: ISOLation Two transactions are not affected by each other

Isolation: Every transaction does not interfere with each other! Even concurrent execution does not interfere!

D: After a permanent transaction is executed, the impact on the data is permanent.

Durability: Once a transaction is changed, the impact on data in the database is permanent!

3. Keywords of transaction operations
Set autocommit = 1
Start transaction/begin
Commit transaction commit
Roll back the transaction rollback
Set autocommit = 1
4. Example of transaction ++ stored procedure transfer

DELIMITER $
Create procedure usp_transfer ()
BEGIN
# The default error count is 0.
DECLARE t_error integer default 0;
# Continue hanlder execution and assign the t_error value to 1 again
Declare continue handler for sqlexception set t_error = 1;
SET autocommit = 0;
Start transaction;
UPDATE bank SET balance = balance-100 WHERE cid = 1;
UPDATE bank SET balance = balance + 100 WHERE cid = 2;
IF t_error> 0 THEN
ROLLBACK;
ELSE
COMMIT;
End if;
SET autocommit = 1;
END $
DELIMITER;
 
CALL test_sp800 ()

 

The simplest syntax for defining a stored procedure is
Delimiter $
Create procedure usp_stulist ()
Begin
Select * from student
End $
Delimiter;

 

Simulate bank transfer!

-- Create a database
Create database myBank;
-- Switch the specified database
USE myBank;
-- Create a table
Create table if not exists bank (
CustomerName VARCHAR (10) not null comment 'username ',
CurrentMoney DECIMAL (10, 2) not null comment 'account balance'
);
-- Insert data
Insert into bank VALUES ('black Black', 50000), ('White ', 500000 );

-- Transfer money to Tom 10000
-- Modify two data items
-- 01. Small black-10000
-- 02. xiaobai+ 10000

UPDATE bank SET currentMoney = currentMoney-10000
WHERE customerName = ' ';
-- Intentionally write the error field name to make 02 report an error
UPDATE bank SET currentMoneysss = currentMoney + 10000
WHERE customerName = 'xiaobai ';
 

-- Start transaction or BEGIN
-- 01.02 is a transaction
BEGIN
UPDATE bank SET currentMoney = currentMoney-10000
WHERE customerName = ' ';
UPDATE bank SET currentMoneysss = currentMoney + 10000
WHERE customerName = 'xiaobai ';
-- Transaction ROLLBACK
-- COMMIT transaction COMMIT

UPDATE bank SET currentcurrency = 500000
WHERE customerName = ' ';
-- Proves that mysql commits transactions by default!

SET autocommit = 0 (disable automatic transaction commit) | 1 (enable automatic transaction commit)

-- Disable automatic transaction commit
SET autocommit = 0;

BEGIN; -- start the transaction
UPDATE bank SET currentMoney = currentMoney-10000
WHERE customerName = ' ';
UPDATE bank SET currentMoney = currentMoney + 10000
WHERE customerName = 'xiaobai ';
COMMIT; -- manually submit a transaction
Updated bank SET currentMoney = 10000;
ROLLBACK; -- transaction ROLLBACK

SET autocommit = 1; -- Restore automatic submission

　　　　　　　　　　　　View

1. View: a virtual table, essentially a set of SQL statements.

-- A view is a virtual table.
01. Partial data of a table or comprehensive data of multiple tables!
02. The structure and data are based on queries to real tables!
03. The data stored in the view is actually a reference to the real table!
Adding and deleting data in the view will affect the real table!
04. You can create N views for a real table!
05. If the view is associated with multiple tables, addition or deletion is not allowed! You can add, delete, and modify a single table.
06. query is generally used for views!

2. Define the view Syntax:
Create view VW_stulist (vw_view function)
As
SQL statement
Tip: when multiple tables have the same name, only one column is allowed in the virtual table of view. Manually specify which table the column belongs

3. View usage is the same as basic table retrieval.
Select * from VW_stulist

4. delete a view
Drop view name
Drop table
Drop database
Drop index
Drop constraint
In the final analysis, drop deletion is a structure delete operation to delete data.

 

5. view all views of all databases

5. 1. Switch to the system database
Use information_schema

5.2.select * from views \ G;

6. view all views of the current database
Show tables status where comment = 'view'

7. View considerations
1) views can query multi-table data
2) views can be nested
3) update, insert, and delete cannot be encapsulated into view content. data can be operated through the graphical interface (operations on View data affect the basic table ).
4) When deleting View data, there are limits. When the data results come from multiple tables, they cannot be deleted.

 

-- Create a view to obtain only the Student name and score
Create view view_student_result
AS
SELECT s. studentNo student ID, studentName Student name,
StudentResult
FROM student s, result r
WHERE s. 'studentno' = r. 'studentno'
-- Query the content in the view
SELECT * FROM view_student_result
-- Query all views of the mysql database
SELECT * FROM information_schema.views;
-- Delete View
Drop view view_student_result;

-- Create a table view Student name, address, and mobile phone number
Create view view_student
AS
SELECT studentName Student name, address, phone number
FROM student

-- Query view

SELECT * FROM view_student