MySql single table & constraint & Transaction

Posted by dennismonsewicz on Wed, 01 Dec 2021 18:08:24 +0100

MySql single table & constraint & Transaction

1. DQL operation sheet

1.1 create database and copy tables

1) Create a new db2 database

CREATE DATABASE db2 CHARACTER SET utf8;

2) Copy the emp table from the db1 database to the current db2 database

1.2 sorting

Through the ORDER BY clause, you can sort the query results (sorting is only a display effect and will not affect the real data)

Grammatical structure

SELECT Field name FROM Table name [WHERE field = value] ORDER BY Field name [ASC / DESC]

ASC indicates ascending sort (default)

DESC means descending sort

1.2.1 sorting method

1) Single column sorting

Sorting by only one field is single column sorting
Demand 1:
Use the salary field to sort the emp table data (ascending / descending)

-- Default ascending sort ASC
SELECT * FROM emp ORDER BY salary;
-- Descending sort
SELECT * FROM emp ORDER BY salary DESC;

2) Combinatorial sorting

Sort multiple fields at the same time. If the first field is the same, sort according to the second field, and so on
Demand 2:
On the basis of salary sorting, id is used for sorting. If the salary is the same, id is used for descending sorting

-- Combinatorial sorting
SELECT * FROM emp ORDER BY salary DESC, eid DESC;

1.3 aggregate function

The previous queries are horizontal queries, which are judged according to the conditions line by line, while the queries using aggregate functions are vertical
Query, which calculates the value of a column and then returns a single value (in addition, the aggregate function will ignore null values.);
Grammatical structure

SELECT Aggregate function(Field name) FROM Table name;

Let's learn five aggregate functions

Demand 1:

#1 Query the total number of employees
#2 View the average of total salary, maximum salary, minimum salary and salary of employees
#3 Query the number of employees whose salary is greater than 4000
#4 Query department is'Teaching Department'Number of all employees
#5 Query department is'Marketing Department'Average salary of all employees

SQL implementation

#1 Query the total number of employees
-- Number of records in the statistical table count()
SELECT COUNT(eid) FROM emp; -- Use a field
SELECT COUNT(*) FROM emp; -- use *
SELECT COUNT(1) FROM emp; -- Use 1,And * Same effect
-- The following one SQL The total number of entries obtained is not accurate,because count Function ignored null value
-- So be careful not to use with null Statistics for columns
SELECT COUNT(dept_name) FROM emp;
#2 View the average of total salary, maximum salary, minimum salary and salary of employees
-- sum Function summation, max Function maximization, min Function minimization, avg Function averaging
SELECT
SUM(salary) AS 'Total salary',
MAX(salary) AS 'ceiling on wages',
MIN(salary) AS 'minimum wages',
AVG(salary) AS 'average wages'
FROM emp;
#3 Query the number of employees whose salary is greater than 4000
SELECT COUNT(*) FROM emp WHERE salary > 4000;
#4 Query department is'Teaching Department'Number of all employees
SELECT COUNT(*) FROM emp WHERE dept_name = 'Teaching Department';
#5 Query department is'Marketing Department'Average salary of all employees
SELECT
AVG(salary) AS 'Average salary of marketing department'
FROM emp
WHERE dept_name = 'Marketing Department';

1.4 grouping

Grouping query refers to grouping the queried information using the GROUP BY statement, with the same data as a group

Syntax format

SELECT Grouping field/Aggregate function FROM Table name GROUP BY Grouping field [HAVING condition];

Requirement 1: grouping by gender field

-- Group operations by gender
SELECT * FROM emp GROUP BY sex; -- Note that this is meaningless

Analysis: GROUP BY grouping process

be careful:

When grouping, you can query the fields to be grouped, Or use aggregate functions for statistical operations.
* Querying other fields is meaningless

Demand: group by gender field to find the average salary of each group

SELECT sex, AVG(salary) FROM emp GROUP BY sex;

Demand 2:

#1.Query all department information
#2.Query the average salary of each department
#3.Query the average salary of each department, Department name cannot be blank null

SQL implementation

#1. How many departments are there
SELECT dept_name AS 'Department name' FROM emp GROUP BY dept_name;
#2.Query the average salary of each department
SELECT
dept_name AS 'Department name',
AVG(salary) AS 'Average salary'
FROM emp GROUP BY dept_name;
Characteristics of filtering mode
where
where Filter before grouping
where Aggregate function cannot be written later
having
having Filtering after grouping
having Aggregate functions can be written later
 Demand 3:
analysis:
1) After grouping,Filter data,Use keywords having
2) In grouping operations having The sub statement is used to filter data after grouping. Its function is similar to where Conditions.
SQL realization:
where And having Differences between
1.5 limit keyword
limit Role of keywords
limit It means restriction,Used to limit the number of rows of query results returned (Can pass limit Specify how many rows of data to query)
limit Grammar is MySql Dialect of,Used to complete paging
 Grammatical structure
 Parameter description
#3.Query the average salary of each department, Department name cannot be blank null
SELECT
dept_name AS 'Department name',
AVG(salary) AS 'Average salary'
FROM emp WHERE dept_name IS NOT NULL GROUP BY dept_name;

Demand 3:

# Query departments with average salary greater than 6000

analysis:
1) after grouping, filter the data and use the keyword having
2) the having sub statement in grouping operation is used to filter data after grouping, and its function is similar to the where condition.
SQL implementation:

# Query departments with average salary greater than 6000
-- You need to filter again after grouping,use having
SELECT
dept_name ,
AVG(salary)
FROM emp WHERE dept_name IS NOT NULL GROUP BY dept_name HAVING AVG(salary) >
6000 ;

The difference between where and having

1.5 limit keyword

Function of limit keyword

Limit means limit. It is used to limit the number of rows of returned query results (you can specify how many rows of data to query through limit)
limit syntax is a dialect of MySql, which is used to complete paging

Grammatical structure

SELECT Field 1,Field 2... FROM Table name LIMIT offset , length;

Parameter description

Demand 1:

# Query the first 5 data in emp table
# Query emp table from item 4 to Item 6

SQL implementation

# Query the first 5 data in emp table
-- Starting value of parameter 1,The default is 0 , Parameter 2 number of entries to query
SELECT * FROM emp LIMIT 5;
SELECT * FROM emp LIMIT 0 , 5;
# Query emp table from item 4 to Item 6
-- The starting value starts from 0 by default.
SELECT * FROM emp LIMIT 3 , 6;

Requirement 2: paging operation displays 3 pieces of data per page
SQL implementation

-- Paging operation displays 3 pieces of data per page
SELECT * FROM emp LIMIT 0,3; -- Page 1
SELECT * FROM emp LIMIT 3,3; -- Page 2-1=1 1*3=3
SELECT * FROM emp LIMIT 6,3; -- Page 3
-- Paging formula start index = (Current page - 1) * Number of entries per page
-- limit yes MySql Dialects in

2. SQL constraints

1) Function of constraint: further restrict the data in the table, so as to ensure the correctness, effectiveness and integrity of the data
Incorrect data will not be inserted into the table
2) Common constraints

2.1 primary key constraints

2.1.1 add primary key constraint

Syntax format

Field name field type primary key

1) Requirement: create a table with a primary key

# Method 1: create a table with a primary key
CREATE TABLE emp2(
-- Set primary key unique non empty
eid INT PRIMARY KEY,
ename VARCHAR(20),
sex CHAR(1)
);
-- Delete table
DROP TABLE emp2;
-- Method 2: create a table with a primary key
CREATE TABLE emp2(
eid INT ,
ename VARCHAR(20),
sex CHAR(1),
-- Specify the primary key as eid field
PRIMARY KEY(eid)
);
-- Method 3 creates a table with a primary key
CREATE TABLE emp2(
eid INT ,
ename VARCHAR(20),
sex CHAR(1)
)
-- No primary key is specified during creation,Then pass DDL Statement
ALTER TABLE emp2 ADD PRIMARY KEY(eid);

DESC view table structure

-- View table details
DESC emp2;

2) Test the uniqueness and non emptiness of the primary key

# Insert a piece of data normally
INSERT INTO emp2 VALUES(1,'Song Jiang','male');
# Insert a piece of data. The primary key is empty
-- Column 'eid' cannot be null Primary key cannot be empty
INSERT INTO emp2 VALUES(NULL,'Li Kui','male');
# Insert a piece of data. The primary key is 1
-- Duplicate entry '1' for key 'PRIMARY' Primary key cannot be duplicate
INSERT INTO emp2 VALUES(1,'Sun erniang','female');

3) Which fields can be used as primary keys?

Primary keys are usually designed for business. Each table is designed with a primary key id
The primary key is used for databases and programs and has nothing to do with the final customer. Therefore, the primary key is meaningless and has nothing to do with it. As long as it can be guaranteed that it is not repeated, for example, the ID card can be used as the primary key

2.1.2 delete primary key constraint

Delete the primary key constraint in the table (understand)

-- use DDL Statement to delete the primary key in a table
ALTER TABLE emp2 DROP PRIMARY KEY;
DESC emp2;

2.1.3 auto increment of primary key

Note: if the primary key is added by ourselves, it is likely to be repeated. We usually want the database to automatically generate the primary key field every time a new record is inserted
The value of the

keyword:
AUTO_INCREMENT Indicates automatic growth(The field type must be an integer type)

1) Create a table with self incrementing primary key

-- Create a table with self incrementing primary key
CREATE TABLE emp2(
-- keyword AUTO_INCREMENT,The primary key type must be an integer type
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
sex CHAR(1)
);

2) Add auto increment of data observation primary key

INSERT INTO emp2(ename,sex) VALUES('Zhang San','male');
INSERT INTO emp2(ename,sex) VALUES('Li Si','male');
INSERT INTO emp2 VALUES(NULL,'Cuihua','female');
INSERT INTO emp2 VALUES(NULL,'Yanqiu','female');

2.1.4 modify the self incrementing starting value of the primary key

Auto by default_ The starting value of increment is 1. If you want to modify the starting value, please use the following method

-- Create a table with self incrementing primary key,Custom Auto increment
CREATE TABLE emp2(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
sex CHAR(1)
)AUTO_INCREMENT=100;
-- insert data,Observe the starting value of the primary key
INSERT INTO emp2(ename,sex) VALUES('Zhang million','male');
INSERT INTO emp2(ename,sex) VALUES('Yanqiu','female');

2.1.5 impact of delete and TRUNCATE on self growth

There are two ways to delete all data in a table

Test 1: delete deletes all data in the table

-- At present, the final primary key value is 101
SELECT * FROM emp2;
-- delete Delete data in table,It has no effect on self increment
DELETE FROM emp2;
-- Insert data view primary key
INSERT INTO emp2(ename,sex) VALUES('Zhang million','male');
INSERT INTO emp2(ename,sex) VALUES('Yanqiu','female');

Test 2: truncate delete data in the table

-- use truncate Delete all data in the table,
TRUNCATE TABLE emp2;
-- Insert data view primary key
INSERT INTO emp2(ename,sex) VALUES('Zhang million','male');
INSERT INTO emp2(ename,sex) VALUES('Yanqiu','female');

2.2 non null constraints

Characteristics of non empty constraints: a column cannot be empty
Syntax format

Field name field type not null

Requirement 1: add a non empty constraint to the ename field

# Non NULL constraint
CREATE TABLE emp2(
eid INT PRIMARY KEY AUTO_INCREMENT,
-- Add a non empty constraint, ename Field cannot be empty
ename VARCHAR(20) NOT NULL,
sex CHAR(1)
);

2.3 unique constraints

Unique constraint features: the value of a column in the table cannot be repeated (no unique judgment is made for null)
Syntax format

Field name field type unique

1) Add unique constraint

#Create the emp3 table to add unique constraints to the ename field
CREATE TABLE emp3(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20) UNIQUE,
sex CHAR(1)
);

2) Test unique constraints

-- Add a piece of data to the test unique constraint
INSERT INTO emp3 (ename,sex) VALUES('Zhang million','male');
-- Add a ename Duplicate data
-- Duplicate entry 'Zhang million' for key 'ename' ename Cannot repeat
INSERT INTO emp3 (ename,sex) VALUES('Zhang million','female');

2.4 foreign key constraints

FOREIGN KEY represents the FOREIGN KEY constraint, which will be learned in multiple tables.

2.5 default values

The default value constraint is used to specify the default value of a column

Syntax format

Field name field type DEFAULT Default value

1) Create emp4 table, and the gender field defaults to female

-- Create a table with default values
CREATE TABLE emp4(
　　eid INT PRIMARY KEY AUTO_INCREMENT,
　　-- by ename Add default values to fields
　　ename VARCHAR(20) DEFAULT 'awesome',
　　sex CHAR(1)
);

2) Test add data using default values

-- Add data using default values
INSERT INTO emp4(ename,sex) VALUES(DEFAULT,'male');
INSERT INTO emp4(sex) VALUES('female');
-- Do not use default values
INSERT INTO emp4(ename,sex) VALUES('Yanqiu','female');

3. Database transactions

3.1 what is a transaction

A transaction is a whole, which is composed of one or more SQL statements. These SQL statements either execute successfully or fail, as long as there is one
If an SQL exception occurs, the whole operation will be rolled back and the whole business execution fails

such as: Bank transfer business,Zhang San transferred 500 yuan to Li Si , You must operate the database at least twice, Zhang San -500, Li Si + 500,In this
 There is a problem at any step between,The entire operation must be rolled back, Only in this way can we ensure that users and banks have no losses.

RollBACK
That is, if a fault occurs during the operation of the transaction and the transaction cannot continue to execute, the system cancels all completed operations on the database in the transaction and rolls back to the state at the beginning of the transaction. (implemented before submission)

3.2 simulated transfer operation

1) Create account table

-- Create account table
CREATE TABLE account(
-- Primary key
　　id INT PRIMARY KEY AUTO_INCREMENT,
-- full name
　　NAME VARCHAR(10),
-- balance
　　money DOUBLE
);
-- Add two users
INSERT INTO account (NAME, money) VALUES ('tom', 1000), ('jack', 1000);

2) Simulate tom to transfer 500 yuan to jack. For a transfer, at least the following two statements should be executed:

-- tom account -500 element
UPDATE account SET money = money - 500 WHERE NAME = 'tom';
-- jack account + 500 element
UPDATE account SET money = money + 500 WHERE NAME = 'jack';

Note:

Suppose when tom Account number -500 element,The server crashed. jack Your account number doesn't+500 Yuan, there is a problem with the data.
We want to ensure the integrity of the whole transaction,Or both, Or they all fail. At this time, we need to learn how to operate transactions.

3.3 MySql transaction operation

There are two ways to perform transaction operations in MYSQL:
- Manually commit transactions
- Auto commit transaction

3.3.1 manually commit transactions

3.3.1.1 syntax format

START TRANSACTION
- This statement explicitly marks the starting point of a transaction.
COMMIT
- It means to commit a transaction, that is, to commit all operations of the transaction. Specifically, it means to write all updates to the database in the transaction to the physical database on the disk, and the transaction ends normally.
ROLLBACK
- It means revoking a transaction, that is, if a fault occurs during the operation of the transaction and the transaction cannot be continued, the system revokes all completed operations on the database in the transaction and rolls back to the state at the beginning of the transaction

3.3.1.2 manual transaction submission process

Successful execution: start transaction - > execute multiple SQL statements - > commit transaction successfully
Execution failure: start transaction - > execute multiple SQL statements - > rollback of transaction

3.3.1.3 demonstration of successful cases

Simulated Zhang San gave Li sizhuan 500 yuan

1) Command line login database

2) Using db2 databases

USE db2;

3) Execute the following SQL

3.1 start transaction
- ```
start transaction;
```

3.2 tom account - 500

update account set money = money - 500 where name = 'tom'

3.3 jack account + 500

update account set money = money + 500 where name = 'jack';

4) At this point, we use sqlYog to view the table and find that the data has not changed

5) Execute the commit transaction on the console

commit;

6) Using sqlYog again, it is found that the data changes after the transaction is committed

3.3.1.4 transaction rollback demonstration

If an error occurs in the execution of an sql statement in a transaction, and we do not have a manual commit, the whole transaction will be rolled back automatically

1) Command line start transaction

start transaction;

2) Insert two pieces of data

INSERT INTO account VALUES(NULL,'Zhang million',3000);
INSERT INTO account VALUES(NULL,'Have money',3500);

3) Close the window directly without committing the transaction, rollback occurs, and the data has not changed

Note:

If there is no problem with the SQL statement in the transaction, commit commits the transaction and changes the data of the database data. If there is a problem with the SQL statement in the transaction, rollback rolls back the transaction and returns to the state when the transaction was started.

3.3.2 auto commit transactions

MySQL defaults that each DML (addition, deletion and modification) statement is a separate transaction. Each statement will automatically start a transaction and execute the statement
After the line is completed, the transaction is automatically submitted. MySQL starts to automatically submit the transaction by default

MySQL automatically commits transactions by default

3.3.2.1 presentation of auto commit transaction

1) Add tom account amount + 500 yuan

2) Using SQLYog to view the database: it is found that the data has changed

3.3.2.2 cancel automatic submission

MySQL commits transactions automatically by default and manually

1) Log in to mysql and check the autocommit status.

SHOW VARIABLES LIKE 'autocommit';

2) Change autocommit to off;

SET @@autocommit=off;

3) Modify again. It will not take effect until it is submitted

Add the jack account amount to - 500 yuan

-- Select database
use db2;
-- Modify data
update account set money = money - 500 where name = 'jack';
-- Manual submission
commit;

3.4 four characteristics of transaction ACID

3.5 Mysql transaction isolation level (understand)

3.5.1 concurrent data access

A database may have multiple access clients, all of which can access the database concurrently. The same data in the database may be deleted
Multiple transactions are accessed at the same time. If isolation measures are not taken, various problems will be caused and the integrity of data will be damaged

3.5.2 problems caused by concurrent access

The ideal state of transactions during operation: all transactions remain isolated and do not affect each other. Because of concurrent operations, multiple users access the same database at the same time
A data. Problems that may cause concurrent access

3.5.3 four isolation levels

By setting the isolation level, you can prevent the above three concurrency problems. MySQL database has four isolation levels, the lowest level above and the lowest level below
The highest level.

✔ There will be problems
✘ no problem

3.5.4 isolation level related commands

1) View isolation level

select @@tx_isolation;

2) To set the transaction isolation level, you need to exit MySQL and log in again to see the change of the isolation level

set global transaction isolation level Level name;
read uncommitted Read uncommitted
read committed Read committed
repeatable read Repeatable reading
serializable Serialization

For example, change the isolation level to read uncommitted

set global transaction isolation level read uncommitted;

3.6 isolation problem demonstration

3.6.1 dirty reading demonstration

Dirty read: one transaction reads uncommitted data from another transaction

1. Open a window to log in to MySQL and set the global isolation level to the lowest
1) Login is MySQL

2) Using db2 databases

use db2;

3) Set the isolation level to minimum read uncommitted

set global transaction isolation level read uncommitted;

2. Close the window, open A new window A, and query the isolation level again
1) Open new window A

2) Query isolation level

select @@tx_isolation;

3. Open a new window B
1) Login database

2) Select database

use db2;

3) Open transaction

start transaction;

4) Inquiry

select * from account;

1. A window execution
1) Select database

use db2;

2) Open transaction

start transaction;

3) Perform modification

-- tom account -500 element
UPDATE account SET money = money - 500 WHERE NAME = 'tom';
-- jack account + 500 element
UPDATE account SET money = money + 500 WHERE NAME = 'jack';

2. Query data in window B
1) Query account information

select * from account;

3. Window a transfers abnormally and rolls back

rollback;

4. Query the account again in window B

select * from account;

3.6.2 solving dirty reading problems

Dirty reading is very dangerous. For example, Zhang San buys goods from Li Si. Zhang San opens the business, transfers 500 yuan to Li Si's account, and then calls Li Si to say that the money has been transferred. Li Siyi inquired that the money had arrived and delivered it to Zhang San. Zhang San rolls back the transaction after receiving the goods, and Li Si's checks that the money is gone.
Solution
- Raise the global isolation level to read committed

1. Set the global isolation level to read committed in window A

set global transaction isolation level read committed;

2. Reopen window A to check whether the setting is successful

select @@tx_isolation;

3. Open window B. after selecting the database in windows A and B, start the transaction

4. Window a only updates the accounts of two people and does not submit transactions

-- tom account -500 element
UPDATE account SET money = money - 500 WHERE NAME = 'tom';
-- jack account + 500 element
UPDATE account SET money = money + 500 WHERE NAME = 'jack';

5. No uncommitted data is found in window B

mysqlselect * from account;

6. A window commit submit data

commit;

7. View data in window B

select * from account;

3.6.3 non repeatable presentation

Non repeatable reading: in the same transaction, query operations are performed, but the data contents read each time are different

1. Recover the data (change the data back to the initial state)

2. Open two windows A and B, select the database and start the transaction

use db2;
start transaction;

1. After the transaction is opened in window B, perform a data query first

select * from account;

2. After opening the transaction in window A, add the account of user tom + 500, and then submit the transaction

-- Modify data
update account set money = money + 500 where name = 'tom';
-- Commit transaction
commit;

1. Query data again in window B

The results of the two queries are different. Which one is correct?
- I don't know which one will prevail. Many people think that this situation is right. There is no need to be confused. Of course, the latter shall prevail.
- We can consider such a situation:
  - For example, the bank program needs to output the query results to the computer screen and send text messages to customers respectively. As a result, the two queries for different output destinations in a transaction are inconsistent, resulting in inconsistent results in the file and screen, and the bank staff do not know which shall prevail

3.6.4 solve the problem of non repeatable reading

Raise the global isolation level to repeatable read

1. Recover data

UPDATE account SET money = 1000

2. Open window A and set the isolation level to repeatable read

-- View transaction isolation level
select @@tx_isolation;
-- Set the transaction isolation level to repeatable read
set global transaction isolation level repeatable read;

3. Re open windows a and B, select the database, and start the transaction at the same time

4. The transaction in window B is queried for the first time

select * from account;

5. Window a updates the data and then commits the transaction

-- Modify data
update account set money = money + 500 where name = 'tom';
-- Commit transaction
commit;

6. Query again in window B

select * from account;

In order to ensure the consistency of multiple query data in the same transaction, the repeatable read isolation level must be used

3.6.5 unreal reading demonstration

Phantom read: select whether a record exists. If it does not exist, it is ready to insert the record. However, when inserting, it is found that the record already exists and cannot be inserted. At this time, phantom read occurs.

1. Open a / b window and select database to start transaction

2. Window a performs a query operation first

-- Suppose you want to add another one id Data for 3,Before adding, judge whether it exists
select * from account where id = 3;

3. Insert a data submission transaction in window B

INSERT INTO account VALUES(3,'lucy',1000);
commit;

4. When window a performs the insertion operation, it is found that an error is reported and unreal reading occurs

Hell, the results I just read should support my operation. Why not now

3.6.6 solving unreal reading problems

Set the transaction isolation level to the highest SERIALIZABLE to block unreal reads
If a transaction uses SERIALIZABLE - SERIALIZABLE isolation level, before the transaction is committed, other threads can only wait until the current operation is completed. This will be very time-consuming and affect the performance of the database. The database will not use this isolation level

1. Recover data

DELETE FROM account WHERE id = 3;

2. Open window A to raise the data isolation level to the highest level

set global transaction isolation level SERIALIZABLE;

3. Open a / b window and select database to start transaction

4. Window a performs a query operation first

SELECT * FROM account WHERE id = 3;

5. Insert a piece of data in window B

INSERT INTO account VALUES(3,'lucy',1000);

6. Window a executes the insert operation, and the commit transaction data is inserted successfully

INSERT INTO account VALUES(3,'lucy',1000);
commit;

7. Window B executes the transaction after window A commits the transaction, but the primary key conflicts and an error occurs

Summary:
- serializable serialization can completely solve the unreal reading, but transactions can only be queued for execution, which seriously affects the efficiency. The database will not use this isolation level

Topics: MySQL

Programmer Think

MySql single table & constraint & Transaction

MySql single table & constraint & Transaction

1. DQL operation sheet

1.1 create database and copy tables

1.2 sorting

1.2.1 sorting method

1.3 aggregate function

1.4 grouping

1.5 limit keyword

2. SQL constraints

2.1 primary key constraints

2.1.1 add primary key constraint

2.1.2 delete primary key constraint

2.1.3 auto increment of primary key

2.1.4 modify the self incrementing starting value of the primary key

2.1.5 impact of delete and TRUNCATE on self growth

2.2 non null constraints

2.3 unique constraints

2.4 foreign key constraints

2.5 default values

3. Database transactions

3.1 what is a transaction

3.2 simulated transfer operation

3.3 MySql transaction operation

3.3.1 manually commit transactions

3.3.1.1 syntax format

3.3.1.2 manual transaction submission process

3.3.1.3 demonstration of successful cases

3.3.1.4 transaction rollback demonstration

3.3.2 auto commit transactions

3.3.2.1 presentation of auto commit transaction

3.3.2.2 cancel automatic submission

3.4 four characteristics of transaction ACID

3.5 Mysql transaction isolation level (understand)

3.5.1 concurrent data access

3.5.2 problems caused by concurrent access

3.5.3 four isolation levels

3.5.4 isolation level related commands

3.6 isolation problem demonstration

3.6.1 dirty reading demonstration

3.6.2 solving dirty reading problems

3.6.3 non repeatable presentation

3.6.4 solve the problem of non repeatable reading

3.6.5 unreal reading demonstration

3.6.6 solving unreal reading problems

Hot Topics