Shulou(Shulou.com)05/31 Report--

This article shares with you the content of an example analysis of TCL transaction control statements in MySQL. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

I. introduction and use of transactions

You can view the storage engines supported by mysql through show engines;, where innodb supports transactions, while myisam,memory and others do not.

Transaction: a sql statement or set of sql statements that forms a sql unit that executes either all or none of them.

The transaction has four attributes of ACID

Atomicity transactions are an indivisible unit of work (Consistency) transactions must transform the database from one consistency state to another consistency state isolation (Isolation) transactions cannot be interfered with by other transactions, and concurrent transactions cannot interfere with each other (Durability) once committed, changes to the data in the database are permanent.

Implicit transactions: transactions have no obvious opening and ending marks, such as insert, delete, update statements.

Explicit transaction: the transaction has obvious opening and ending marks, and the premise is that the autocommit feature must be set to disable.

After the DELETE statement is rolled back, it can be restored; the TRUNCATE statement cannot be restored after the rollback.

[demonstrate steps for using transactions] DROP TABLE IF EXISTS account;CREATE TABLE account (id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR (20), balance DOUBLE); INSERT INTO account (username,balance) VALUES ('Zhang Wuji', 1000), ('Zhao Min', 1000); # step 1: turn off autocommit SET autocommit=0;START TRANSACTION;# can be omitted # step 2: write a set of transactions UPDATE account SET balance=balance+500 WHERE username=' Zhang Wuji' UPDATE account SET balance=balance-500 WHERE username=' Zhao Min'; # step 3: end the transaction # commit;# commit ROLLBACK; # rollback SELECT * FROM account; [demonstrate the use of savepoint] SET autocommit=0;START TRANSACTION;DELETE FROM account WHERE id=1;SAVEPOINT Abot # set SavePoint DELETE FROM account WHERE id=2;ROLLBACK TO Aten # Roll back to SavePoint

Second, the problem of transaction concurrency and its solution

For multiple transactions running at the same time, when these transactions access the same data in the database, if the necessary isolation mechanism is not adopted, it will lead to a variety of concurrency problems.

Dirty reading: a transaction reads data that is updated by something else but not committed.

Non-repeatable read: a transaction reads multiple times and the result is different.

Illusion: one transaction reads data inserted by other transactions but not committed.

The solution to the transaction concurrency problem is to avoid the concurrency problem by setting the isolation level of the transaction.

Each time you start a mysql program, you get a separate database connection, and each database connection has a global variable @ @ tx_isolation, indicating the current transaction isolation level.

View the current isolation level: select @ @ tx_isolation

Transaction isolation level dirty read not repeatable phantom read read uncommitted read unresolved read committed read committed (Oracle default) resolve √ unresolved repeatable read repeatability (Mysql default) resolve √ resolve √ unresolved serializable serialization resolve √

Sets the isolation level of the current mysql connection: set transaction isolation level read committed

Set the global isolation level of the database system: set global transaction isolation level read committed

III. View

The meaning of the view: after the mysql5.1 version of the new features, a virtual existence of table, row and column data to customize the view of the query used in the table, and is dynamically generated when using the view, only save the sql logic, do not save the query results.

Application scenarios:

The same query results are used in multiple places.

The sql statement used for this query result is more complex.

Comparison of views and tables physical space occupied by keywords for creating syntax using view create view only saves sql logic can be added, deleted, changed and queried, but generally only supports query table create table saves data to support addition, deletion, modification and query

View creation syntax:

Create view View name

As (query statement)

View modification syntax:

Method 1:

Create or replace view View name

As (new query statement)

Method 2:

Alter view View name

As (new query statement);'

View deletion syntax:

DROP VIEW view name, view name...

Update of the view:

The updatability of the view is related to the definition of the query in the view, and the following types of views cannot be updated.

① sql statements containing the following keywords: grouping function, distinct, group by, having, union, or union all

② constant view

Include subqueries in ③ Select

④ join

⑤ from a view that cannot be updated

The subquery of the ⑥ where clause references the table in the from clause

Advantages of views:

Reuse SQL statements.

Simplify complex sql operations without knowing the query details.

Protect data and provide security.

[view creation] # 1. Query the employee name, department name and job name containing a character in the mailbox CREATE VIEW myv1 # encapsulate the connection of the three tables AS (SELECT Last_name,department_name,job_title FROM employees e JOIN departments d ON e.department_id=d.department_id JOIN jobs j ON j.job_id=e.job_id); SELECT * FROM myv1 WHERE Last_name LIKE'% a% employee # 2. Query the average wage grade of each department CREATE VIEW myv2 # average wage of each department and departmental idAS (SELECT AVG (Salary) ag,department_id FROM employees GROUP BY department_id); SELECT myv2.ag,grade_levelFROM myv2JOIN job_grades jON myv2.ag BETWEEN j.lowest_sal AND j.highestrated salinity # 3. Query the sector with the lowest average wage id and average wage SELECT * FROM myv2 ORDER BY ag LIMIT 1 cross # 4. Query the name of the department with the lowest average wage and the salary CREATE VIEW myv3 AS (SELECT * FROM myv2 ORDER BY ag LIMIT 1); SELECT department_name,agFROM departmentsJOIN myv3ON myv3.department_id=departments.department_id -[View modification] # method 1: CREATE OR REPLACE VIEW myv3AS (SELECT AVG (Salary)) Job_id FROM employees GROUP BY job_id) # method 2: ALTER VIEW myv3AS (SELECT * FROM employees); SELECT * FROM myv3;- [deletion of view] DESC myv1;# View View SHOW CREATE VIEW myv1 # View View DROP VIEW myv1,myv2,myv3 # Delete view-[View update] CREATE OR REPLACE VIEW myv4AS (SELECT Last_name,email FROM employees); # 1. Insert INSERT INTO myv4 VALUES ('Huahua', 'huahua@163.com'); SELECT * FROM myv4;SELECT * FROM employees;# 2. Modify UPDATE myv4 SET Last_name='Hudie' WHERE Last_name=' Huahua'; # 3. Delete DELETE FROM myv4 WHERE Last_name='Hudie'

After learning the view of MySQL, try to complete the following exercises

Answer:

I.

CREATE OR REPLACE VIEW emp_v1

AS (

SELECT Last_name,Salary,email

FROM employees

WHERE phone_number LIKE '011%'

);

SELECT * FROM emp_v1

II.

ALTER VIEW emp_v1

AS (

SELECT Last_name,Salary,email

FROM employees

WHERE phone_number LIKE '011%' AND email LIKE'% e%'

);

3.

CREATE OR REPLACE VIEW emp_v2 # departments whose maximum wage is higher than 12000 id, department maximum wage

AS (

SELECT MAX (Salary) mx,department_id

FROM employees

GROUP BY department_id

HAVING MAX (Salary) > 12000

);

SELECT d.paper.m.mx

FROM departments d

JOIN emp_v2 m

ON m.department_id=d.department_id

IV.

CREATE TABLE Book (

Bid INT PRIMARY KEY

Bname VARCHAR (20) UNIQUE NOT NULL

Price FLOAT DEFAULT 10

BtypeId INT

FOREIGN KEY (byteId) REFERENCES bookType (id)

);

5.

SET autocommit=0

INSERT INTO book (bid,bname,price.btypeId) VALUES (1Jing 'big defeat', 100jue 1)

COMMIT

VI.

CREATE VIEW myv1

AS (

SELECT bname,NAME

FROM book b

JOIN bookType t

ON b.btypeid=t.id

WHERE price > 100

);

7.

CREATE OR REPLACE VIEW myv1 (

SELECT bname,price

FROM book

WHERE price BETWEEN 90 AND 120

);

8.

DROP VIEW myv1

Thank you for reading! This is the end of the article on "sample analysis of TCL transaction control statements in MySQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.