Basic Database knowledge and database knowledge

Basic Database knowledge and database knowledge
Transaction ACID atomicity (atomicity)

Each transaction is an inseparable minimum unit. Operations in the transaction are either all successful or all fail to be rolled back.

Consistency)

The result of transaction execution must be that the database changes from one consistent state to another consistent state.

Isolation)

Modifications made by a firm are invisible to other transactions before the final commit.

Durability

After the transaction is committed, the changes are permanently saved to the database. (This is a vague concept, and persistence cannot be fully achieved)

Transaction isolation level

READ UNCOMMITTED)
Transaction modifications will be seen by other transactions even if they are not committed. Other transactions can read uncommitted data. This isDirty read.
READ COMMITTED)
Modifications made before a transaction is committed are invisible to other transactions.
Solved the dirty read problem,Non-repeated read: Two queries in a transaction may have different results. (If the mysql Innodb engine needs to read a row of data, the transaction will hold the shared lock for this row at the beginning. Other transactions that want to update data will request the exclusive lock. They need to wait for the release of the shared lock to obtain the exclusive lock.)
REPEATABLE READ)
The results of reading the same record multiple times in a transaction are the same.
Solved the problem of non-repeated reading,Phantom read: When transaction A reads A record within A certain range, another transaction B inserts A new record in this range. When transaction A reads the record again, phantom rows will appear.
(The gap lock of Innodb engine can prevent the emergence of phantom rows: transaction A will obtain the gap lock between lines to be read, and transaction B must wait for The Gap lock to be released during insertion)
This is the default transaction isolation level of mysql.
SERIALIZABLE)
Enforces the serial execution of transactions. It solves the phantom read problem, but cannot perform concurrent operations, and is prone to deadlocks.

E-R Diagram

The E-R diagram is entity-relationship, which is commonly used in the design of relational databases. E-R graph is divided into three core parts: entity, attribute, relationship. Objects are rectangular, attributes are elliptical, and links are diamond.

DML, DDL, DCL, DQL

DML (Data Manipulation Language), Data Control Language: INSERT, UPDATE, DELETE
DDL (Data Definition Language), Data Definition Language: create table/VIEW/INDEX/SYN/CLUSTER...
DCL (Data Control Language), Database Control Language: ROLLBACK, GRANT. Used to grant or revoke certain privileges for accessing the database and Control
The time and Effect of database operations and transactions, and the monitoring of the database.
DQL (Data Query Language), Data Query Language: SELECT...

Paradigm

Link: https://www.zhihu.com/question/24696#/answer/29189700 :zhihu

The paradigm can be roughly understood as the level of a design standard that the table structure of a data table conforms. The database paradigm is also divided into 1NF, 2NF, 3NF, BCNF, 4NF, and 5NF. Generally, when designing a relational databaseBCNFThat's enough. In line with the design of the high level paradigm, it must be in line with the low level paradigm, for example, in line with the Relationship Model of 2NF, must comply with 1NF.

1NF is defined as: every attribute in the 1NF-compliant relationship cannot be further divided.
Based on 1NF, 2NF eliminates some function dependencies of non-primary attributes on codes.
3NF is based on 2NF, eliminating the dependence of non-primary attributes on code passing functions.
BCNF: removes the code dependency of the primary attribute and transmits the function based on 3NF.

Function dependency

If the value of attribute (or attribute group) X in a table is determined, the value of attribute Y can be determined. Then, the Y function depends on X, write X → Y.

Full function dependency

In a table, if X → Y and any real subset of X (if Attribute Group X contains more than one attribute), x' → Y is not true, so we call Y fully functional dependency on X.
For example, tables that contain fields A, B, and C, where A and B are primary keys. If Function C depends on (A, B) and Function C does not depend on, C is completely dependent on (A, B) because it does not depend on B ).

Some function dependencies

If the Y function depends on X, but Y is not completely dependent on X, then we call Y function dependent on X.
For example, tables that contain fields A, B, and C, where A and B are primary keys. If Function C is dependent on (A, B ), and C functions depend on A or B, so C functions depend on (A, B ).

Pass function dependency

C function depends on B, and B function depends on A, then C transfer function depends on.

Code

Set K to an attribute or attribute group in a table. If all the attributes except K are completely dependent on K (this "completely" should not be omitted ), then we call K as the candidate code. In practice, we can generally understand that, if K is determined, all the attribute values of the table except K are determined accordingly, then K is the code. A table can have more than one code.
(The sensory code is similar to the Unique index (Unique Key) of a table ))

Non-Primary attribute

The attribute contained in any code is the primary attribute.

View comments