Introduction to transactions and views in MySQL system

• 1. Transaction

• 1. Submit

• 2.Rollback

• 3. ACID properties

• 2. View

• 1. Create a view

• 2. Delete the view

• 3. Update the view

• 4. Use the view

• 3. Subquery

• 1. Use subquery

• 2. Scalar subquery

• 3. Correlated subquery

1. Transaction

In RDBMS, transaction is The unit to update the data in the table. Simply put, a transaction is a collection of update processes that need to be performed in the same processing unit.

事务开始语句START TRANSACTION;

      DML语句①;
      DML语句②;
      DML语句③;
         .
         .
         .
事务结束语句（COMMIT或者ROLLBACK）;

1. Commit

COMMIT is the end instruction for all update processing included in the commit transaction, which is equivalent to overwriting and saving in file processing. Once committed, it cannot be restored to the state before the transaction started.

2. Rollback

ROLLBACK is the end instruction to cancel all update processing included in the transaction, which is equivalent to giving up saving in file processing. Once rolled back, the database is restored to the state it was in before the transaction began.

3. ACID characteristics

DBMS transactions all follow four characteristics. The first letters of these four characteristics are collectively called ACID characteristics.

• Atomicity Atomicity means that at the end of a transaction, the update processing contained in it is either all executed or not executed at all.

• Consistency Consistency means that the processing included in the transaction must satisfy the constraints set by the database in advance, such as primary key constraints or NOT NULL constraints. For example, a column with a NOT NULL constraint cannot be updated to NULL. If you try to insert a record that violates the primary key constraint, an error will occur and cannot be executed. For transactions, these illegal SQL will be rolled back.

• Isolation Isolation refers to the feature that ensures that different transactions do not interfere with each other. This feature ensures that transactions are not nested within each other. In addition, changes made within a transaction are not visible to other transactions until the transaction ends. Therefore, even if a transaction adds records to the table, other transactions will not see the newly added records until they are committed.

• Durability Durability refers to the feature of the DBMS that ensures that the data state at that point in time will be saved after the transaction (whether committed or rolled back) ends. Even if data is lost due to system failure, the database must be recovered by some means.

2. View

The essential difference between a view and a table is "whether the actual data is saved."

The table stores actual data, while the view stores the SELECT statement used to retrieve data from the table.

We should make the frequently used SELECT statements into views.

1. Create a view

--格式：CREATE VIEW 视图名称(<视图列名1>, <视图列名2>, ……) AS <SELECT语句>

CREATE VIEW v1 (product_name, name_cnt)
AS 
SELECT product_name, count(*)
FROM
    表名/视图名
GROUP BY product_name

Note:

• Avoid creating a view based on a view. For most DBMS, multiple views reduce SQL performance.

• The ORDER BY clause cannot be used when defining a view

2. Delete the view

--格式：DROP VIEW 视图名称

DROP VIEW v1

3. Update the view

-- 格式：ALTER VIEW 视图名称 AS <SELECT语句>

-- 格式：INSERT INTO 视图名称 VALUES(...)

Note:

• The view and table need to be updated at the same time, so the view obtained through summary (aggregation combined with connected table) cannot be updated by INSERT.

Remember, updating a view ultimately means updating the table corresponding to the view.

4. Using views

When using views, just treat them as tables. Since views are virtual tables, they cannot be used to create or update real tables. and delete operations can only be used for queries.

select * from v1

3. Subquery

1. Using subquery

A subquery is a one-time view (SELECT statement). Unlike views, subqueries disappear after the SELECT statement is executed.

-- 根据商品种类统计商品数量的视图
CREATE VIEW ProductSum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
  FROM Product
 GROUP BY product_type;
 
 -- 确认创建好的视图
SELECT product_type, cnt_product
  FROM ProductSum;
  
--子查询
SELECT product_type, cnt_product
  FROM （SELECT product_type, COUNT(*)
           FROM Product
    GROUP BY product_type）AS ProductSum;

2. Scalar subquery

The scalar subquery has a special restriction, that is, it must and can only return the results of 1 row and 1 column, that is, return the results of a certain row in the table The value of a certain column.

SELECT product_id, product_name, sale_price
  FROM Product
 WHERE sale_price > (SELECT AVG(sale_price)
                       FROM Product);

3. Correlated subquery

When comparing within subdivided groups, you need to use correlated subqueries.

--子查询中添加的 WHERE 子句的条件
--该条件的意思是，在同一商品种类中对各商品的销售单价和平均单价进行比较。
SELECT product_id, product_name, sale_price
  FROM Product AS P1
 WHERE sale_price > (SELECT AVG(sale_price)
                          FROM Product AS P2
                          WHERE P1.product_type = P2.product_type
                         GROUP BY product_type);

The above is the detailed content of Introduction to transactions and views in MySQL system. For more information, please follow other related articles on the PHP Chinese website!