[MySQL basics] IV. constraints, multi table queries, transactions

reference material

https://www.bilibili.com/video/BV1Kr4y1i7ru?p=32&spm_id_from=pageDriver

1. Constraints

1.1 general

Concept: constraints are rules that act on fields in a table and are used to limit the data stored in the table.
Objective: to ensure the correctness, validity and integrity of the data in the database.
classification

Note: constraints are applied to fields in the table. You can add constraints when creating / modifying tables

1.2 constraint demonstration

Complete the creation of table structure according to requirements

CREATE TABLE tb_user
(
    id     int AUTO_INCREMENT PRIMARY KEY COMMENT 'ID Unique identification',
    name   varchar(10) NOT NULL UNIQUE COMMENT 'full name',
    age    int check (age > 0 && age <= 120) COMMENT 'Age',
    status char(1) default '1' COMMENT 'state',
    gender char(1) COMMENT 'Gender'
);

1.3 foreign key constraints

Foreign keys are used to establish a connection between the data of two tables, so as to ensure the consistency and integrity of the data.

Note: at present, the above two tables do not establish foreign key Association at the database level, so the consistency and integrity of data cannot be guaranteed.

Add foreign key
Add when creating table

CREATE TABLE Table name(
Field name data type
[CONSTRAINT] Foreign key name FOREIGN KEY (External key field name) REFERENCES Main table(Main table column name)
);

Add when the table already exists

ALTER TABLE Table name ADD CONSTRAINT Foreign key name FOREIGN KEY (Foreign key field name) REFERENCES Main table(Main table column name);

Delete foreign key

ALTER TABLE Table name DROP FOREIGN KEY Foreign key name;

Delete / update behavior

ALTER TABLE surface ADD CONSTRAINT Foreign key name FOREIGN KEY (Foreign key field) REFERENCES Main table name(Main table field name) ON UPDATE CASCADE ON DELETE CASCADE;

1.4 practice

Create table create table

create table dept(
    id   int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment 'Department name'
)comment 'Department table';

create table employee(
    id  int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment 'full name',
    age  int comment 'Age',
    job varchar(20) comment 'position',
    salary int comment 'salary',
    entrydate date comment 'Entry time',
    managerid int comment 'Direct leader ID',
    dept_id int comment 'department ID'
)comment 'Employee table';

Add data

INSERT INTO dept (id, name) VALUES (1, 'R & D department'), (2, 'Marketing Department'),(3, 'Finance Department'), (4, 'Sales Department'), (5, 'General manager's Office'), (6, 'Ministry of Personnel');
INSERT INTO employee (id, name, age, job,salary, entrydate, managerid, dept_id) VALUES
            (1, 'Jin Yong', 66, 'CEO',20000, '2000-01-01', null,5),

            (2, 'zhang wuji', 20, 'project manager',12500, '2005-12-05', 1,1),
            (3, 'Yang Xiao', 33, 'development', 8400,'2000-11-03', 2,1),
            (4, 'Xiangr ', 48, 'development',11000, '2002-02-05', 2,1),
            (5, 'chang yuchun', 43, 'development',10500, '2004-09-07', 3,1),
            (6, 'Xiao Zhao', 19, 'Programmer encouragement Division',6600, '2004-10-12', 2,1),

            (7, 'Extinction', 60, 'Chief financial officer',8500, '2002-09-12', 1,3),
            (8, 'Zhou Zhiruo', 19, 'accounting',48000, '2006-06-02', 7,3),
            (9, 'Ding Minjun', 23, 'cashier',5250, '2009-05-13', 7,3),

            (10, 'Zhao Min', 20, 'Marketing Director',12500, '2004-10-12', 1,2),
            (11, 'Deer stick guest', 56, 'staff member',3750, '2006-10-03', 10,2),
            (12, 'Crane pen Weng', 19, 'staff member',3750, '2007-05-09', 10,2),
            (13, 'Fang Dongbai', 19, 'staff member',5500, '2009-02-12', 10,2),

            (14, 'Zhang Sanfeng', 88, 'Sales Director',14000, '2004-10-12', 1,4),
            (15, 'Yu Lianzhou', 38, 'sale',4600, '2004-10-12', 14,4),
            (16, 'Song Yuanqiao', 40, 'sale',4600, '2004-10-12', 14,4),
            (17, 'rival of Zhu Yuanzhang', 42, null,2000, '2011-10-12', 1,null);

Set foreign keys

alter table employee add constraint fk_employee_dept_id foreign key (dept_id) references dept(id);

Delete Behavior Check (cascade example)

alter table employee add constraint fk_employee_dept_id foreign key (dept_id) references dept(id) on update  cascade on delete cascade ;

2. Multi table query

2.1 multi table relationship

summary
During the project development, when designing the database table structure, the table structure will be analyzed and designed according to the business requirements and the relationship between business modules. Due to the correlation between businesses, there are also various connections between each table structure, which are basically divided into three types:
- One to many (many to one)
- Many to many
- one-on-one
One to many (many to one)
Case: relationship between department and employee
Relationship: One Department corresponds to multiple employees, and one employee corresponds to one department
Implementation: establish a foreign key in one of the multiple parties and point to the primary key of one party. Exercise 1.4 That is to show this relationship.

Many to many
Case: the relationship between students and courses
Relationship: one student can take multiple courses, and one course can also be selected by multiple students
Implementation: create the third intermediate table. The intermediate table contains at least two foreign keys, which are respectively associated with the primary keys of the two parties

-- Many to many ----------------
create table student(
    id int auto_increment primary key comment 'Primary key ID',
    name varchar(10) comment 'full name',
    no varchar(10) comment 'Student number'
) comment 'Student list';
insert into student values (null, 'Daisy', '2000100101'),(null, 'Thankson ', '2000100102'),(null, 'Yin Tianzheng', '2000100103'),(null, 'Xiangr ', '2000100104');


create table course(
    id int auto_increment primary key comment 'Primary key ID',
    name varchar(10) comment 'Course name'
) comment 'Class Schedule Card';
insert into course values (null, 'Java'), (null, 'PHP'), (null , 'MySQL') , (null, 'Hadoop');


create table student_course(
    id int auto_increment comment 'Primary key' primary key,
    studentid int not null comment 'student ID',
    courseid  int not null comment 'curriculum ID',
    constraint fk_courseid foreign key (courseid) references course (id),
    constraint fk_studentid foreign key (studentid) references student (id)
)comment 'Student intermediate schedule';

insert into student_course values (null,1,1),(null,1,2),(null,1,3),(null,2,2),(null,2,3),(null,3,4);

one-on-one
Case: relationship between user and user details
Relationship: one-to-one relationship, which is mostly used for single table splitting. The basic fields of one table are placed in one table and other detail fields are placed in another table to improve operation efficiency
Implementation: add a foreign key to any party, associate the primary key of the other party, and set the foreign key as unique

-- --------------------------------- one-on-one ---------------------------
create table tb_user(
    id int auto_increment primary key comment 'Primary key ID',
    name varchar(10) comment 'full name',
    age int comment 'Age',
    gender char(1) comment '1: male , 2: female',
    phone char(11) comment 'cell-phone number'
) comment 'User basic information table';

create table tb_user_edu(
    id int auto_increment primary key comment 'Primary key ID',
    degree varchar(20) comment 'education',
    major varchar(50) comment 'major',
    primaryschool varchar(50) comment 'primary school',
    middleschool varchar(50) comment 'middle school',
    university varchar(50) comment 'university',
    userid int unique comment 'user ID',
    constraint fk_userid foreign key (userid) references tb_user(id)
) comment 'User information form';


insert into tb_user(id, name, age, gender, phone) values
        (null,'Huang Bo',45,'1','18800001111'),
        (null,'Bingbing',35,'2','18800002222'),
        (null,'Code cloud',55,'1','18800008888'),
        (null,'Robin Li',50,'1','18800009999');

insert into tb_user_edu(id, degree, major, primaryschool, middleschool, university, userid) values
        (null,'undergraduate','dance','Jing'an District No.1 Primary School','Jing'an District No.1 Middle School','Beijing Dance Academy',1),
        (null,'master','perform','Chaoyang first primary school','Chaoyang District No.1 Middle School','Beijing Film Academy',2),
        (null,'undergraduate','English','Hangzhou No.1 Primary School','Hangzhou No.1 Middle School','Hangzhou Normal University ',3),
        (null,'undergraduate','applied mathematics','Yangquan No.1 Primary School','Yangquan No.1 Middle School','Tsinghua University',4);

2.2 overview of multi table query

Data preparation

-- ------------------------------------> multi-table query <--------------------------------------------
-- Prepare data
create table dept(
    id   int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment 'Department name'
)comment 'Department table';

create table emp(
    id  int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment 'full name',
    age  int comment 'Age',
    job varchar(20) comment 'position',
    salary int comment 'salary',
    entrydate date comment 'Entry time',
    managerid int comment 'Direct leader ID',
    dept_id int comment 'department ID'
)comment 'Employee table';

-- Add foreign key
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

INSERT INTO dept (id, name) VALUES (1, 'R & D department'), (2, 'Marketing Department'),(3, 'Finance Department'), (4, 'Sales Department'), (5, 'General manager's Office'), (6, 'Ministry of Personnel');
INSERT INTO emp (id, name, age, job,salary, entrydate, managerid, dept_id) VALUES
            (1, 'Jin Yong', 66, 'CEO',20000, '2000-01-01', null,5),

            (2, 'zhang wuji', 20, 'project manager',12500, '2005-12-05', 1,1),
            (3, 'Yang Xiao', 33, 'development', 8400,'2000-11-03', 2,1),
            (4, 'Xiangr ', 48, 'development',11000, '2002-02-05', 2,1),
            (5, 'chang yuchun', 43, 'development',10500, '2004-09-07', 3,1),
            (6, 'Xiao Zhao', 19, 'Programmer encouragement Division',6600, '2004-10-12', 2,1),

            (7, 'Extinction', 60, 'Chief financial officer',8500, '2002-09-12', 1,3),
            (8, 'Zhou Zhiruo', 19, 'accounting',48000, '2006-06-02', 7,3),
            (9, 'Ding Minjun', 23, 'cashier',5250, '2009-05-13', 7,3),

            (10, 'Zhao Min', 20, 'Marketing Director',12500, '2004-10-12', 1,2),
            (11, 'Deer stick guest', 56, 'staff member',3750, '2006-10-03', 10,2),
            (12, 'Crane pen Weng', 19, 'staff member',3750, '2007-05-09', 10,2),
            (13, 'Fang Dongbai', 19, 'staff member',5500, '2009-02-12', 10,2),

            (14, 'Zhang Sanfeng', 88, 'Sales Director',14000, '2004-10-12', 1,4),
            (15, 'Yu Lianzhou', 38, 'sale',4600, '2004-10-12', 14,4),
            (16, 'Song Yuanqiao', 40, 'sale',4600, '2004-10-12', 14,4),
            (17, 'rival of Zhu Yuanzhang', 42, null,2000, '2011-10-12', 1,null);

Overview: refers to querying data from multiple tables
Cartesian product: Cartesian product refers to all combinations of two sets A and B in mathematics. (invalid Cartesian product needs to be eliminated in multi table query)
```
-- multi-table query -- Cartesian product
select * from emp , dept;
-- Eliminate invalid Cartesian product
select * from emp , dept where emp.dept_id = dept.id;
```
Multi table query classification
- join query
  Internal connection: it is equivalent to querying the data of the intersection of A and B
  External connection:
  -Left outer connection: query all data in the left table and some data at the intersection of two tables
  -Right outer connection: query all data in the right table and some data at the intersection of two tables
  Self connection: the connection query between the current table and itself. The self connection must use the table alias
- Subquery

2.3 internal connection

The inner join query is the part of the intersection of two tables

Implicit inner connection

SELECT Field list FROM Table 1,Table 2 WHERE condition;

Explicit inner join

SELECT Field list FROM Table 1[ININER] JOIN Table 2 ON Connection conditions...;

INNER can be omitted.

practice

Query the name of each employee and the name of the associated Department (implicit connection Implementation)

select emp.name , dept.name from emp , dept where emp.dept_id = dept.id ;
-- Alias
select e.name,d.name from emp e , dept d where e.dept_id = d.id;

Query the name of each employee and the name of the associated Department (explicit internal connection Implementation)

select e.name, d.name from emp e inner join dept d  on e.dept_id = d.id;

select e.name, d.name from emp e join dept d  on e.dept_id = d.id;

2.4 external connection

Left outer connection
It is equivalent to querying all data of Table 1 (left table), including the data of the intersection of Table 1 and table 2
```
SELECT Field list FROM Table 1 LEFT [OUTER] JOIN Table 2 ON condition
```
Right outer connection
It is equivalent to querying all data of Table 2 (right table), including the data of the intersection of Table 1 and table 2
```
SELECT Field list FROM Table 1 RIGHT [OUTER] JOIN Table 2 ON condition
```

OUTER can be omitted.

practice

-- External connection demonstration
-- 1. query emp All data in the table, And corresponding department information(Left outer connection)
-- Table structure: emp, dept
-- Connection conditions: emp.dept_id = dept.id

select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;

select e.*, d.name from emp e left join dept d on e.dept_id = d.id;


-- 2. query dept All data in the table, And corresponding employee information(Right outer connection)

select d.*, e.* from emp e right outer join dept d on e.dept_id = d.id;

select d.*, e.* from dept d left outer join emp e on e.dept_id = d.id;

2.5 self connection

Self connection query can be internal connection query or external connection query.

SELECT Field list FROM surface A alias A JOIN surface A alias B ON condition;

practice

-- Self connection
-- 1. Query the names of employees and their leaders
-- Table structure: emp

select  e1.name, e2.name from emp e1 join emp e2 on e1.managerid=e2.id;


-- 2. Query all employees emp And the names of their leaders emp , If the employee has no leader, You also need to find out
-- Table structure: emp a , emp b

select e1.name, e2.name from emp e1 left join emp e2 on e1.managerid=e2.id;

Union query - Union, union all
For union query, the results of multiple queries are combined to form a new query result set.
```
SELECT Field list FROM surface A..
UNION [ALL]
SELECT Field list FROM surface B...;
```
The number of columns and field types of multiple tables in a joint query must be consistent. union all will directly merge all the data together, and union will de duplicate the merged data.

practice

-- union all , union
-- 1. Employees whose salary is less than 5000 , And employees older than 50.

-- Add all All satisfied items will be returned, which may be repeated
select e1.* from emp e1 where e1.salary<5000
union all
select  e1.* from emp e1 where e1.age>50;
-- No all All satisfied will be returned, but will not be repeated
select e1.* from emp e1 where e1.salary<5000
union
select  e1.* from emp e1 where e1.age>50;
-- Consistent with the following effects
select * from emp where age>50||salary<5000;

2.6 sub query

Concept: nested SELECT statements in SQL statements are called nested queries, also known as sub queries.
```
SELECT * FROM t1 WHERE column1= (SELECT column1 FROM t2);
```
The statement outside the subquery can be any one of INSERT / UPDATE / DELETE / SELECT.
According to different sub query results, it can be divided into:
- Scalar subquery (subquery result is a single value)
- Column subquery (subquery result is one column)
- Row subquery (the subquery result is one row)
- Table sub query (the sub query result is multi row and multi column)
According to the sub query location, it can be divided into: after WHERE, after FROM and after SELECT.

2.6.1 scalar quantum query

The result returned by the sub query is a single value (number, string, date, etc.). In its simplest form, this sub query becomes a scalar sub query.
Common operators: = > > =<<=

practice

-- scalar subquery 
-- 1. query "Sales Department" All employee information
-- a. query "Sales Department" department ID
select id from dept where name = 'Sales Department';

-- b. According to the Sales Department ID, Query employee information
select * from emp where dept_id = (select id from dept where name = 'Sales Department');



-- 2. Query in "Fang Dongbai" Employee information after employment
-- a. Query the employment date of Fang Dongbai
select entrydate from emp where name = 'Fang Dongbai';

-- b. Query the information of employees who are employed after the specified employment date
select * from emp where entrydate > (select entrydate from emp where name = 'Fang Dongbai');

2.6.2 column sub query

The result returned by a sub query is a column (which can be multiple rows). This sub query is called a column sub query.
Common operators: IN, NOT IN, ANY, SOME, ALL

practice

-- Column subquery
-- 1. query "Sales Department" and "Marketing Department" All employee information
-- a. query "Sales Department" and "Marketing Department" Department of ID
select id from dept where name = 'Sales Department' or name = 'Marketing Department';

-- b. According to Department ID, Query employee information
select * from emp where dept_id in (select id from dept where name = 'Sales Department' or name = 'Marketing Department');


-- 2. Query the information of employees whose salary is higher than that of everyone in the finance department
-- a. Query the salary of all personnel in the finance department
select id from dept where name = 'Finance Department';

select salary from emp where dept_id = (select id from dept where name = 'Finance Department');

-- b. Employee information with higher salary than everyone in the finance department
select * from emp where salary > all ( select salary from emp where dept_id = (select id from dept where name = 'Finance Department') );


-- 3. Query the information of employees whose salary is higher than that of any one in the R & D department
-- a. Query the salary of the owner of the R & D department
select salary from emp where dept_id = (select id from dept where name = 'R & D department');

-- b. Employee information with higher salary than any one in the R & D department
select * from emp where salary > some ( select salary from emp where dept_id = (select id from dept where name = 'R & D department') );

2.6.3 line sub query

The result returned by a sub query is a row (which can be multiple columns). This sub query is called a row sub query.

Common operators: =, < >, IN, NOT IN

-- Row subquery
-- 1. Query and "zhang wuji" The salary and the employee information of the direct leader are the same ;
-- a. query "zhang wuji" Salary and direct leadership
select salary, managerid from emp where name = 'zhang wuji';

-- b. Query and "zhang wuji" The salary and the employee information of the direct leader are the same ;
select * from emp where (salary,managerid) = (select salary, managerid from emp where name = 'zhang wuji');

2.6.4 table sub query

The result returned by the sub query is multi row and multi column. This sub query is called table sub query.
Common operator: IN

practice

-- Table subquery
-- 1. Query and "Deer stick guest" , "Song Yuanqiao" Employee information with the same position and salary
-- a. query "Deer stick guest" , "Song Yuanqiao" Position and salary
select job, salary from emp where name = 'Deer stick guest' or name = 'Song Yuanqiao';

-- b. Query and "Deer stick guest" , "Song Yuanqiao" Employee information with the same position and salary
select * from emp where (job,salary) in ( select job, salary from emp where name = 'Deer stick guest' or name = 'Song Yuanqiao' );


-- 2. Query entry date is "2006-01-01" Employee information after , And its department information
-- a. The entry date is "2006-01-01" Employee information after
select * from emp where entrydate > '2006-01-01';

-- b. Query these employees, Corresponding department information;
select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id ;

2.7 multi table query cases

Create the following table

create table salgrade(
    grade int,
    losal int,
    hisal int
) comment 'Salary scale';

insert into salgrade values (1,0,3000);
insert into salgrade values (2,3001,5000);
insert into salgrade values (3,5001,8000);
insert into salgrade values (4,8001,10000);
insert into salgrade values (5,10001,15000);
insert into salgrade values (6,15001,20000);
insert into salgrade values (7,20001,25000);
insert into salgrade values (8,25001,30000);

According to the requirements, complete the preparation of SQL statements

-- 1. Query employee's name, age, position and department information (implicit connection)
-- surface: emp , dept
-- Connection conditions: emp.dept_id = dept.id

select e.name , e.age , e.job , d.name from emp e , dept d where e.dept_id = d.id;


-- 2. Query the name, age, position and department information of employees younger than 30 years old (explicit internal connection)
-- surface: emp , dept
-- Connection conditions: emp.dept_id = dept.id

select e.name , e.age , e.job , d.name from emp e inner join dept d on e.dept_id = d.id where e.age < 30;


-- 3. Query departments with employees ID,Department name
-- surface: emp , dept
-- Connection conditions: emp.dept_id = dept.id

select distinct d.id , d.name from emp e , dept d where e.dept_id = d.id;



-- 4. Query all employees older than 40, And the Department to which it belongs; If the employee is not assigned a department, It also needs to be displayed
-- surface: emp , dept
-- Connection conditions: emp.dept_id = dept.id
-- External connection

select e.*, d.name from emp e left join dept d on e.dept_id = d.id where e.age > 40 ;


-- 5. Query the salary grade of all employees
-- surface: emp , salgrade
-- Connection conditions : emp.salary >= salgrade.losal and emp.salary <= salgrade.hisal

select e.* , s.grade , s.losal, s.hisal from emp e , salgrade s where e.salary >= s.losal and e.salary <= s.hisal;

select e.* , s.grade , s.losal, s.hisal from emp e , salgrade s where e.salary between s.losal and s.hisal;


-- 6. query "R & D department" Information and salary grade of all employees
-- surface: emp , salgrade , dept
-- Connection conditions : emp.salary between salgrade.losal and salgrade.hisal , emp.dept_id = dept.id
-- query criteria : dept.name = 'R & D department'

select e.* , s.grade from emp e , dept d , salgrade s where e.dept_id = d.id and ( e.salary between s.losal and s.hisal ) and d.name = 'R & D department';



-- 7. query "R & D department" Average salary of employees
-- surface: emp , dept
-- Connection conditions :  emp.dept_id = dept.id

select avg(e.salary) from emp e, dept d where e.dept_id = d.id and d.name = 'R & D department';



-- 8. Query salary ratio "Extinction" High employee information.
-- a. query "Extinction" Salary
select salary from emp where name = 'Extinction';

-- b. Query employee data with higher salary than her
select * from emp where salary > ( select salary from emp where name = 'Extinction' );


-- 9. Query employee information with higher than average salary
-- a. Query the average salary of employees
select avg(salary) from emp;

-- b. Query employee information with higher than average salary
select * from emp where salary > ( select avg(salary) from emp );





-- 10. Query the information of employees whose salary is lower than the average salary of the Department

-- a. Query average salary of specified Department 1
select avg(e1.salary) from emp e1 where e1.dept_id = 1;
select avg(e1.salary) from emp e1 where e1.dept_id = 2;

-- b. Query the information of employees whose salary is lower than the average salary of the Department
select * from emp e2 where e2.salary < ( select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id );


-- 11. Query all department information, And count the number of employees in the Department
select d.id, d.name , ( select count(*) from emp e where e.dept_id = d.id ) 'Number of people' from dept d;

select count(*) from emp where dept_id = 1;


-- 12. Query the course selection of all students, Show student name, Student number, Course name
-- surface: student , course , student_course
-- Connection conditions: student.id = student_course.studentid , course.id = student_course.courseid

select s.name , s.no , c.name from student s , student_course sc , course c where s.id = sc.studentid and sc.courseid = c.id ;

2.8 summary

3. Services

3.1 introduction

Transaction is a set of operations. It is an inseparable work unit. Transaction will submit or revoke operation requests to the system as a whole, that is, these operations will either succeed or fail at the same time.
By default, MySQL transactions are automatically committed, that is, when a DML statement is executed, MySQL will immediately and implicitly commit transactions.

Data preparation

-- ---------------------------- Transaction operation ----------------------------
-- Data preparation
create table account(
    id int auto_increment primary key comment 'Primary key ID',
    name varchar(10) comment 'full name',
    money int comment 'balance'
) comment 'Account table';
insert into account(id, name, money) VALUES (null,'Zhang San',2000),(null,'Li Si',2000);

3.2 transaction operation

View / set transaction submission method

SELECT @@autocommit;
SET @@autocommit =0;

Commit transaction
```
COMMIT;
```
Rollback transaction
```
ROLLBACK;
```
Open transaction
```
START TRANSACTION or BEGIN;
```
Commit transaction
```
COMMIT;
```
Rollback transaction
```
ROLLBACK;
```

practice

-- Recover data
update account set money = 2000 where name = 'Zhang San' or name = 'Li Si';


select @@autocommit;

set @@autocommit = 0; -- Set to manual submission

-- Transfer operation (Zhang San transfers 1000 to Li Si)
-- 1. Query Zhang San's account balance
select * from account where name = 'Zhang San';

-- 2. Balance of Zhang San's account-1000
update account set money = money - 1000 where name = 'Zhang San';

Program execution error ...

-- 3. Transfer the balance of Li Si's account+1000
update account set money = money + 1000 where name = 'Li Si';


-- Commit transaction
commit;

-- Rollback transaction
rollback ;



-- Mode 2
-- Transfer operation (Zhang San transfers 1000 to Li Si)
start transaction ;

-- 1. Query Zhang San's account balance
select * from account where name = 'Zhang San';

-- 2. Balance of Zhang San's account-1000
update account set money = money - 1000 where name = 'Zhang San';

Program execution error ...

-- 3. Transfer the balance of Li Si's account+1000
update account set money = money + 1000 where name = 'Li Si';


-- Commit transaction
commit;

-- Rollback transaction
rollback;

3.3 four characteristics of transaction

Atomicity: a transaction is the smallest and indivisible unit of operation, either all successful or all failed.
Consistency: when a transaction is completed, all data must be kept in a consistent state.
Isolation: the isolation mechanism provided by the database system ensures that transactions run in an independent environment that is not affected by external concurrent operations.
Durability: once a transaction is committed or rolled back, its changes to the data in the database are permanent.

3.4 concurrent transactions

3.5 transaction isolation level

From top to bottom, the isolation level is higher and higher, the data is safer, but the performance is worse.
Please refer to the dark horse programmer for details video.

-View transaction isolation level
SELECT @@TRANSACTION ISOLATION;
-Set transaction isolation level
SET [SESSION| GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE};

-- View transaction isolation level
select @@transaction_isolation;

-- Set transaction isolation level
set session transaction isolation level serializable ;

3.6 summary

Keywords: Database MySQL

Added by FillePille on Wed, 23 Feb 2022 06:19:33 +0200

Programming VIP

[MySQL basics] IV. constraints, multi table queries, transactions

reference material

1. Constraints

1.1 general

1.2 constraint demonstration

1.3 foreign key constraints

1.4 practice

2. Multi table query

2.1 multi table relationship

2.2 overview of multi table query

2.3 internal connection

2.4 external connection

2.5 self connection

2.6 sub query

2.6.1 scalar quantum query

2.6.2 column sub query

2.6.3 line sub query

2.6.4 table sub query

2.7 multi table query cases

2.8 summary

3. Services

3.1 introduction

3.2 transaction operation

3.3 four characteristics of transaction

3.4 concurrent transactions

3.5 transaction isolation level

3.6 summary

Popular Keywords