<> Characteristics of relational database
Save data in different tables , Then put these tables into the database , Instead of putting all the data in the database
<> Advantages of relational database
（1） Increased Mysql Read speed of
（2） Improve flexibility and manageability
（3） Access and management Mysql The most commonly used standardized language for databases is SQL Structured query statement , Allows data to be persisted and stored on the hard disk
（4） Complex query ： Can use SQL Statement is convenient for complex data query between one table and multiple tables
（5） Transaction support ： So that the data access requirements with high security performance can be realized
<> Relationship chart
data → surface → database
<> Design principles of relational database
follow ER Model and three paradigms
ER Model
entity — contact ---- attribute
entity ： Abstraction in real life , The information transmission between entities is realized by connection
attribute ： A business card shared by entities and contacts
Three paradigms
First paradigm ： Each column cannot be split
Second paradigm ： Non primary key columns depend entirely on primary keys , It cannot be a part that depends on the primary key
Third paradigm ： Non primary key columns depend only on primary keys , Independent of other non primary keys
<>drop,truncate,delete difference
drop： Delete table directly （ Structure and data ）
truncat： Delete data in table , Self growth on reinsertion id from 1 start
delete： Delete data in table , Can add where clause , Delete a row from the table one at a time
<> Indexes
Is a data structure , It can help us find data quickly .
The data structure of the index is related to the implementation of the specific storage engine , stay MySQL The indexes used more in are Hash Indexes ,B+ Tree index, etc , Frequently used InnoDB The default index implementation for the storage engine is B+ Tree index
<> affair
Is a series of operations , To meet ACID characteristic . The most common understanding is ： All operations in the transaction are either successful , Or all fail
ACID
A=Atomicity： Atomicity , Or all succeed , Or all fail , It is impossible to perform only part of the operation
C=Consistency： system ( database ) Always move from one consistent state to another , There will be no intermediate state
I=Isolation： Isolation . Generally speaking , Before a transaction is fully committed , Is invisible to other transactions , There are exceptions
D=Durability： persistence . Once the transaction is committed , Then it will always be like this , Even a system crash will not affect the outcome of the transaction
Concurrency of transactions
（1） Dirty reading ： affair A Read transaction B Updated data , then B Rollback operation , that A The read data is dirty data
（2） Non repeatable reading ： affair A Read the same data multiple times , affair B In transaction A During multiple reads , The data were updated and submitted , Cause transaction A When the same data is read multiple times , The results are inconsistent
（3） Unreal reading ： system administrator A Change the scores of all students in the database from specific scores to ABCDE Grade , But the system administrator B At this time, a specific score record was inserted , As a system administrator A After the change, I found that there was another record that had not been changed , It's like an illusion , This is called Unreal reading
<> Transaction isolation level
Transaction isolation level Dirty reading Non repeatable reading Unreal reading
Read uncommitted （read-uncommitted） yes yes yes
Non repeatable reading （read-committed） no yes yes
Repeatable reading （repeatable-read） no no yes
Serialization （serializable） no no no
<> Why is the field required to be defined as not null
null Values take up more bytes , And it will cause a lot of inconsistencies with expectations in the procedure
<> lock
In terms of lock category , There are shared locks and exclusive locks
Shared lock ： Read lock . When the user wants to read data , Apply a shared lock to the data , Multiple shared locks can be added at the same time
Exclusive lock ： Write lock . When the user wants to write data , Exclusive lock on data , Only one exclusive lock can be added , And other exclusive locks , Shared locks are mutually exclusive
The granularity of locks depends on the specific storage engine ,InnoDB Row level locking is implemented , Page level lock , Table lock
Locking overhead ： Row level lock > Page level lock > Table lock
Concurrency capability ： Row level lock > Page level lock > Table lock
Non repeatable reading focuses on modification , Unreal reading focuses on adding or deleting . To solve the problem of non repeatable reading, you only need to lock the rows that meet the conditions , To solve the problem of unreal reading, you need to lock the table
Technology