1. View: view

1. View It is a temporary table in memory
2. The creation of the view depends on the select statement, so it is the table of the result parameter of the select statement operation
3. View supports adding, deleting, checking and modifying data
4. View does not allow to modify the fields of the view table
5. View Not only support creation, but also update and delete

# Data dependency: single table emp# Syntax# Create view mysql>: create view view name [( Aliases)] as select statement; eg>: create view v1 as select dep, max(salary) from emp group by dep;# Create or replace view mysql>: create or replace view view name[(alias)] as select Statement;eg>: create or replace view v1(dep_name, max_salary) as select dep, max(salary) from emp group by dep;# modify view mysql>: alter view view name[(aliases)] as select statement;eg >: alter view v1(name, salary) as select dep, max(salary) from emp group by dep;# delete view mysql>: drop view view name eg> : drop view v1;

Add, delete, modify, and check view

The addition, deletion, and modification of a view can be directly mapped to the real table (essentially Operate the real table)

# Operation view equals operation real table# Increase, increase data insert into v1(name,salary) values('yangsir', 1.11) ; # Delete, delete view record delete from v1 where id=1;# Change, modify view data update v1 set salary=salary+1 where id=1;# Check, view view data select * from v1;# Summary: Operation view , Will affect the real table, and vice versa, it will also affect select * from emp;

Summary: Operating the view will affect the real table, and vice versa it will also affect the view

Second, transaction

• Transaction: Usually some businesses require multiple SQLs to participate, and the participating SQLs will form a parameter to execute the whole, the whole We call it a transaction
• In short: Transaction-protects multiple executed sql statements

Four characteristics of transactions

1. Atomicity: A transaction is a group of indivisible units that either succeed or fail at the same time
2. Consistency: The data integrity before and after the transaction should be consistent (database integrity: if the database at a certain point in time, all data meets all constraints, it is called the database The state of completeness)
3. Isolation: Isolation of things means that when multiple users access data concurrently, one user’s things cannot be interfered by other users’ transactions. Data between multiple concurrent transactions must be isolated from each other
4. Persistence: Persistence means that once a thing is submitted, its changes to the data are permanent. Failure of the database should not have any impact on it

# Syntax begin; # Open transaction update emp set sa lary=salary+1 where id=2;update emp set salary=salary-1 where id=3;commit; # Confirm correct, submit the transaction rollback; # Confirm incorrect, roll back

Three. Index

Index is the key-key

Index can greatly speed up query speed

1) The key is added to the field of the database table 2) After creating the key for the table, the table will not only form the parameter table structure, table data, but also the B+ structure of the key. Figure 3) The key structure diagram needs to be maintained , When data is added, deleted, or modified, as long as the keyed fields are affected, the structure diagram must be maintained once. Therefore, the efficiency of addition, deletion, and modification will be reduced after creating the key. 4) The key can greatly accelerate the query speed (In development requirements, almost all businesses are related to search) 5) Ways to establish keys: primary key, foreign key, unique key, index (ordinary index, speed up the query speed of ordinary data)

Table of Contents

• 1. View: view
  • Addition, deletion, and modification of view
• 2. Transaction
  • Four characteristics of transaction
• 3. Index