017-mysql question and answer

MySql question and answer

1. The difference between drop and delete truncate?

All three mean to delete, but there are some differences among the three:

Delete and truncate only delete the table's data, not the table's structure.

Drop deletes the entire table

When you want to delete some data, you should delete delete with where statement.

Use truncate when you want to delete all data while preserving the table structure

2. The role of the transaction

Transaction is the basic unit of concurrency control. A transaction is a series of operations that are either performed or not performed.

Transactions have the following four basic characteristics:

A series of operations in Atomic (atomic) transactions either complete or fail

Consistency (consistency) A successful transaction should write data to the database, otherwise it will be rolled back to its original state

Isolation (isolated) concurrent access and modified duli

Durability (persistence) the end of a transaction should be stored according to the transaction processing structure.

Statement of the transaction:

Start things: BEGIN TRANSACTION

Submit things: COMMIT TRANSACTION

Rollback transaction: ROLLBACK TRANSACTION

3. Optimistic lock (shared lock) and pessimistic lock (exclusive lock) in database

Different lock permissions can be set on data according to different types.

* * optimistic | pessimistic lock is mainly used for concurrent access control * *

Pessimistic locks assume that concurrency conflicts will occur, blocking any operations that violate the integrity of the data

Optimistic locks assume that there will be no conflicts, only to check whether the data integrity is violated when submitting the operation.

4. Can the function of the view be changed?

A view is a virtual table, and unlike a table that contains data, a view contains only queries that retrieve data dynamically when used; it does not contain any columns or data. Views can be used to simplify complex sql operations, hide details, and protect data; after views are created, they can be used to modify data in the same way as tables.