MySQL (ii)--Transactions and views

• First, the business
  • 1. Submit
  • 2. Rollback
  • 3. Acid characteristics
• Second, the View
  • 1. Create a View
  • 2. Delete View
  • 3. Update view
  • 4. Using views
• Third, sub-query
  • 1. Using sub-query
  • 2. Standard Quantum Query
  • 3. Correlated sub-query

First, the business

In an RDBMS, a transaction is the unit in which data in a table is updated. Simply put, a transaction is a collection of a series of update processing that needs to be performed in the same processing unit.

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

1. Submit

Commit is the end instruction for all update processing included in the commit transaction, equivalent to the overwrite save in file processing. Once committed, it cannot be restored to the state before the transaction started.

2. Rollback

ROLLBACK is the end instruction that cancels all update processing that the transaction contains, which is equivalent to discarding the save in file processing. Once rolled back, the database reverts to the state it was in before the transaction started.

3. Acid characteristics

The DBMS's transactions follow four characteristics, combining the first letters of these four attributes collectively known as ACID properties.

• Atomic (atomicity) atomicity means that when a transaction ends, the update processing contained in it is either all executed or not executed at all.
• Consistency (consistency) consistency refers to the constraints that a transaction contains in order to satisfy a database's advance settings, such as a primary key constraint or a not NULL constraint. For example, a column that has a NOT NULL constraint set cannot be updated to NULL, and attempting to insert a record that violates a PRIMARY key constraint can be an error and cannot be executed. For transactions, these non-legal SQL will be rolled back.
• Isolation (Isolation) isolation refers to the characteristics that guarantee the non-interference between different transactions. This feature guarantees that transactions do not nest with each other. In addition, changes made in a transaction are not visible to other transactions until the end of the transaction. Therefore, even if a transaction adds a record to a table, other transactions do not see the newly added record until the commit is committed.
• Persistence (durability) persistence refers to the ability of a DBMS to guarantee that the data state of a point in time is preserved when the transaction (whether committed or rolled back) ends. Even if data is lost due to a system failure, the database must be able to recover by some means.

Second, the View

The nature of views and tables differs from "whether the actual data is saved."

The actual data is stored in the table, and the view holds the SELECT statement that is used to fetch the data from the table.

We should make a view of frequently used SELECT statements.

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

Attention:

• Avoid creating views on the basis of a view. For most DBMS, more attention to graphs can degrade SQL performance.
• Cannot use ORDER by clause when defining views

2. Delete View

--格式：DROP VIEW 视图名称DROP VIEW v1

3. Update view

-- 格式：ALTER VIEW 视图名称 AS <SELECT语句>-- 格式：INSERT INTO 视图名称 VALUES(...)

Attention:

• Views and tables need to be updated at the same time, so the view resulting from summary (aggregation combined with a table) cannot be updated with insert.

Remember that the update to the view is ultimately the update to the table that corresponds to the view.

4. Using views

When you use a view, you manipulate it as a table, and because the view is a virtual table, you cannot use it to create, update, and delete real tables, only for queries.

select * from v1

Third, sub-query 1, the use of sub-query

A subquery is a one-time view (SELECT statement). Unlike views, a subquery disappears after the SELECT statement finishes executing.

-- 根据商品种类统计商品数量的视图CREATE VIEW ProductSum (product_type, cnt_product)ASSELECT 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. Standard Quantum Query

The scalar subquery has a special restriction that must and can only return the result of 1 rows and 1 columns, that is, the value of a column that returns a row in the table.

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

3. Correlated sub-query

When you compare within a segmented group, you need to use the associated subquery.

--子查询中添加的 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);

MySQL (ii)--Transactions and views