View
- For complex queries, maintenance is a very troublesome thing to do after multiple use.
- FIX: Define a view
- The essence of view is an encapsulation of queries
- Defining views
Create View as Select students. * from scores Inner Join on Scores.stuid=students.id;
- The purpose of a view is to query
Select * from Stuscore;
Transaction
- When a business logic requires multiple SQL completion, if one of the SQL statements goes wrong, you want the entire operation to be returned
- Transaction can be used to complete the function of return, to ensure the correctness of business logic
- Four major features of transactions (acid, for short)
- Atomicity (atomicity): All operations in a transaction are indivisible in the database, either all completed or not executed
- Consistency (consistency): Several transactions executed in parallel, whose execution results must be consistent with the results executed serially in a sequential order
- Isolation (Isolation): The execution of a transaction is not disturbed by other transactions, and the intermediate result of the transaction execution must be transparent to other transactions
- Persistence (Durability): For any committed transaction, the system must ensure that the transaction's changes to the database are not lost, even if the database fails
- Requirement: The type of the table must be a innodb or BDB type before the transaction can be used on this table
- View creation statements for a table
show create table students;
- Modifying the type of a table
alter table ‘表名‘ engine=innodb;
开启begin;提交commit;回滚rollback;
Example 1
- Step 1: Open two terminals, connect MySQL, use the same database, operate the same table
终端1：select * from students;------------------------终端2：begin;insert into students(sname) values(‘张飞‘);
终端1：select * from students;
终端2：commit;------------------------终端1：select * from students;
Example 2
- Step 1: Open two terminals, connect MySQL, use the same database, operate the same table
终端1：select * from students;------------------------终端2：begin;insert into students(sname) values(‘张飞‘);
终端1：select * from students;
终端2：rollback;------------------------终端1：select * from students;
MySQL (vii)
