Simple summary of the database

Primary key: A combination of data columns or properties in a database table that uniquely and fully identifies the stored data object. A data column can have only one primary key,
And the value of the primary key cannot be missing, that is, it cannot be a null value (NULL).
FOREIGN key: The foreign key of this table is called by the primary key of another table that exists in one table.

Transaction: A separate execution unit in a database, usually caused by a program executed by a user. When the data in the database is changed successfully, the
The data changed in the transaction is committed and no longer changed. Otherwise, the transaction is canceled or rolled back, and the change is not valid.
Four properties: ACID, atomicity, consistency, isolation, durability
Atomicity: A transaction is an indivisible whole, and when data is modified, it is either fully executed or not executed, that is, the completion of the transaction part is not allowed
Consistency: After a transaction executes, the database data must remain in a consistent state. For example, bank transfer, the total amount of two accounts before and after the transfer is guaranteed
Not change.
Isolation: When two or more transactions are executed concurrently, multiple transaction executions need to be separated to ensure the security of the data.

Transaction Lock: A database lock is a transaction t that makes a request to the system and locks it before it operates on a data object, such as a table, record, and so on. The
Lock transaction T has some control over the data object, and the other transaction cannot update the data
object until the transaction T releases its lock. The
Database lock is an important technique for implementing concurrency control, but locks can bring additional overhead to the system. Therefore, we need to pay attention to the choice of block granularity
must consider both the cost and concurrency factors, trade-offs to achieve optimal results.
Shared Locks: Multiple transactions can block a shared page, no transaction can modify the page, usually the page is read, and the S lock is immediately released.
When executing a SELECT statement, you need to add a shared lock to the operand (table or some records), but before locking, you need to check if
has an exclusive lock, and if not, you can add a shared lock (you can add n shared locks on an object). A shared lock is usually released after the
line is finished with a SELECT statement
Exclusive lock: Only one transaction is allowed to block this page, and any other transaction must wait until the X lock is released to access the page, and the X lock continues until the transaction knot
Bundle is released. When executing an INSERT, UPDATE, DELETE statement, you need to add an exclusive lock to the object of the operation (I feel that the
line insert should be an exclusive lock at the table level), before the exclusive lock must be confirmed that there is no other lock on the object, once the
plus an exclusive lock, You can no longer add any other locks to this object.
Update Lock: Used to book an X lock on this page, which allows other transactions to read, but does not allow U-lock or X-Lock, and when the page being read is to be more
new, it is promoted to an X lock, and the U Lock is not released until the end of the transaction.
Useful: If two transactions share a lock on an object at the same time, when they all want to modify the object, the database supports automatic lock escalation in a transaction, so both transactions want to upgrade their locks, but because of this object on the other side of the transaction plus a total of
sharing lock. So you can't upgrade. So two transactions are waiting for the other party to release the shared lock and enter a deadlock state. The
update lock is to solve this problem, that is, when performing a query operation is not a shared lock but update the lock (only one update lock and N shared locks on an object
), and then upgrade the update lock to an exclusive lock when the update is to be updated

Problems when the database does not have isolation levels:
Update lost: Two threads to change the data while getting the original value, a updated after writeback, b after the update is also written back, when a updates the internal
It's lost.
Dirty reads: When a transaction is accessing the data and the data has been modified, and this modification has not yet been committed to the database, another
The external transaction also accesses this data, and then uses the data
Non-repeatable reads: A transaction repeats two reads of the same row of data, but it gets different results. Which is the Phantom read, the transaction T1 reads a
Data, the transaction T2 modifies it, and when the transaction T1 reads the data again, it gets a different value than the previous one.

Transaction isolation level of the database:
(1) READ UNCOMMITTED: Allow dirty reads, but do not allow updates to be lost. If a transaction has started writing data, the other transaction does not allow simultaneous
Writes, but allows other transactions to read this row of data
(2) Read commit: Allow non-repeatable reads, but dirty reads are not allowed. A transaction that reads data allows other transactions to continue accessing the row's data, but does not mention
The transaction will prohibit other transactions from accessing the row.
(3) Repeatable READ: Prevents non-repeatable reads and dirty reads. Transactions that read data will prohibit write transactions (but allow read transactions), and write transactions prohibit
Any other transaction.
(4) Serialization: Provides strict transaction isolation. It requires the transaction to serialize execution, and the transaction can be executed one after the other, but not concurrently.

Connection: divided into internal and external connections
Inner joins: The returned result set selects all matching data from two tables, discarding unmatched data
Example: Select Tlb_a.sid, Tlb_b.name
From tlb_a INNER JOIN Tlb_b
On tlb_a.id = Tlb_b.id
Left OUTER join: The result set contains all the rows of the left table, and if a row in the left table does not have a matching row in the right table, the right table in the result set corresponds to the null
Example: Select Tlb_a.sid, Tlb_b.name
From Tlb_a left join Tlb_b
On tlb_a.id = Tlb_b.id
Right connection ibid.

Database paradigm:
Redundancy: When you design a database, a field belongs to one table, but it appears in another or more tables, and is exactly the same as it does in its
To the meaning of the owning table, then this field is a redundant field
Http://www.blueidea.com/tech/program/2011/8311.asp
Scenario One: Only nickname in the name table, so when modifying nickname, you only need to change the table
Scenario Two: To find nickname by ID, you need to use join, which is time consuming when there is a lot of data

All relational databases meet the first normal form
First paradigm: attributes are not divided. Each column of a database table is an indivisible base data item and cannot have multiple values in the same column, that is, in the entity
A property cannot have more than one value or cannot have duplicate properties. If the contact field is in the table, you cannot have both a landline number and
Mobile phone number
Second normal form: 1NF compliant, and the non-master attribute is completely dependent on the code
Main attribute: An attribute is the primary attribute as long as it appears in any candidate code.
Non-primary attribute: In contrast to the above, there is no candidate code, this property is a non-primary attribute.
You cannot have a non-primary attribute part dependent on a code, such as a table where the main code is the course and the student, but the course can determine the textbook.
Three types of exceptions:
What's wrong with it? You can think about:
1, the headmaster to add a new course called "Calculus", the textbook is "College Mathematics", How to do? Students have not yet chosen a class, and the student is the master
property, the main attribute cannot be empty, how is the course recorded, and where is the textbook written? ...... Are you depressed? (Insert exception)
2, the next semester no students to learn the first year of Chinese (on), the first grade language (the next) went, then the table will not exist in the first grade language
(above), there is no "primary language 1". At this time, the headmaster asked: first grade Chinese (on) What teaching materials AH? ...... Depressed.
, huh? (Delete exception)
3, the headmaster said: First grade language (on) for the teaching materials, replaced by the "University of Chinese." There are 10,000 students who have chosen such a class and have changed so much.
Ah! It's exhausting. Are you depressed? (Modify exception)

Third paradigm: If no non-critical fields exist in the data table the transfer function dependency on either of the candidate key fields conforms to the third normal form

There is a pass dependent a->b->c, where the primary key A can determine a non-critical field B, and B can determine C, which means C depends on
Depends on a non-critical field B. Therefore, the third paradigm can be described as: there is no non-key field in the table that can determine other non-keywords
The problem is in the "teacher" and "teacher title" here. A teacher must be able to determine a teacher's title.
What's the problem? Think about:
1, the teacher upgraded, changed the professor, to change the database, the table has n, changed n times ... (Modify exception)
2, no candidate this teacher's class, the teacher's title has not been recorded ... (Delete exception)
3, a new teacher, not assigned to teach what class, his title to remember? ...... (Insert exception)

BC Paradigm: 3NF compliant, and the primary attribute is not dependent on the primary attribute

View: A logical window of data selected from a database, which is a virtual table. In the database, only the definition of the view is stored, not
Data items that are included
Benefits: (1) Use complex SQL statements when querying
(2) The complex operations and connections between the tables can be hidden from the user
Example:
Create View del as
Select employee number, name, department name, responsible from WORK1, department
where WORK1. Department Number = Department. Department number

Trigger: A trigger is a database object related to a table operation that is invoked when a specified event occurs on the table on which the trigger is located, that is, the table
The execution of a trigger on an action event touch publication
Syntax: CREATE TRIGGER trigger_name
Trigger_time
Trigger_event on Tbl_name
For each ROW
Trigger_stmt
Trigger_name: Identifies the trigger name, which is specified by the user;
Trigger_time: Identification trigger time, value is before or after;
Trigger_event: Identifies the triggering event, with a value of INSERT, UPDATE, or DELETE;
Tbl_name: Identifies the name of the table on which the trigger is established, that is, the table on which the trigger is established;
TRIGGER_STMT: A trigger program body, which can be an SQL statement, or multiple statements containing the BEGIN and END.

Where statement_list represents a list of one or more statements, each statement in the list must end with a semicolon (;).
In MySQL, the semicolon is the end-of-statement identifier, and a semicolon indicates that the segment statement has ended and MySQL can start executing. So
The interpreter encounters a semicolon in the statement_list and then begins execution, and then reports an error because no match to begin is found
END.
This will use the DELIMITER command (DELIMITER is the delimiter, the delimiter meaning), it is a command, do not need statement knot
Bundle identification, the syntax is:
DELIMITER New_delemiter
New_delemiter can be set to 1 or more length symbols, the default is a semicolon (;), we can modify it to other symbols, such as $:
DELIMITER $

Http://www.cnblogs.com/CraryPrimitiveMan/p/4206942.html

Stored procedures and functions: SQL statements are compiled and executed before they are executed. In order to improve efficiency, the statement set compilation optimization for specific functions is done
is placed in the database server. Using stored procedures, you can pass parameters and use Process Control statements
Differences from functions: (1) functions can be part of a query and stored procedures are separate parts
(2) The stored procedure is compiled at the time of creation, faster than the function

Index:

A B-tree is a balanced, multi-path lookup tree that is useful in a file system.
Definition: A B-Tree of M-order, or an empty tree, or a M-fork tree that satisfies the following characteristics:
Each node in the ⑴ tree has a maximum of M subtrees trees;
⑵ joghen node is not a leaf node, there are at least two subtrees trees;
All non-terminal nodes outside the ⑶ root node have at least [M/2] subtrees trees;
⑷ all non-terminal nodes contain the following information data:
(N,a0,k1,a1,k2,...,kn,an)
Where: Ki (i=1,2,..., N) is the key code, and Ki<ki+1,
The Ai is a pointer to the subtree node (i=0,1,..., N), and the key code for all nodes in the subtree of the pointer Ai-1 is less than Ki (i=1,2,..., n).
The key codes for all nodes in the subtree of an are greater than KN.
n is the number of key codes.
⑸ all leaf nodes appear at the same level without information (which can be seen as an external node or a failed node, in fact these
Nodes do not exist, pointers to these nodes are empty)

B + Trees are a B-tree deformation tree that is produced as required by the file system. A M-order B + tree and M-order
The difference between trees is:
⑴ n subtrees tree nodes contain n key codes;
⑵ all of the leaf nodes contain information about all key codes, and pointers to the records containing these key codes, and
The leaf nodes themselves are linked by the size of the key yards from a large order.
⑶ all non-terminal nodes can be regarded as the index part, and the nodes contain only the largest (or smallest) key codes in their sub-root nodes.
Usually there are two head pointers on the B + tree, one pointing to the root node and the other to the smallest leaf node of the keyword. Therefore two B + trees can be
Lookup operations: One is to find from the minimum keyword, and the other is to start from the root node and do random lookups.

Why use B-tree (B+tree)
Binary search tree Evolutionary varieties of red and black trees and other data structures can also be used to implement the index, but the file system and database system generally adopted B-/+tree
As the index structure.

In general, the index itself is large and cannot be stored in memory, so the index is often stored as an index file on the disk. Such
, the disk I/O consumption is generated during the index lookup process, and the consumption of I/O accesses is several orders of magnitude relative to memory access, so the evaluation of a
The most important indicator of a data structure as an index is the progressive complexity of the number of disk I/O operations during the lookup process. In other words, the indexed
Structured organization to minimize the number of disk I/O accesses during the lookup process. Why using B-/+tree is also related to the principle of disk access.

Principle of locality and disk pre-reading:
The disk is often not read strictly on-demand, but is read-ahead every time, even if only one byte is required, and the disk starts from this position, sequentially backwards
Reads a certain length of data into memory. The rationale for this is the well-known local principle in computer science: When a data is used,
The data near it is also usually used immediately.
The length of the read-ahead is typically the page, where the disk finds the starting position of the data and sequentially reads a page or pages back into memory.
The database system skillfully utilizes the principle of disk pre-reading, setting a node size equal to one page, so that each node needs only one I/O to
To be fully loaded.
B-tree in a single retrieval requires a maximum of h-1 I/O (root node resident memory), and the red-black tree structure, h is significantly deeper.
In MySQL, there are mainly four types of indexes, namely: B-tree index, Hash Index, fulltext Index and
R-tree Index

What are the Mysql B-Tree indexes:
Normal index
This is the most basic type of index, and it has no limitations such as uniqueness. Normal indexes can be created in the following ways:
(1) Creating index: Create index name on table name (column name 1, column name 2,...);
(2) Modify table: ALTER table name add index index name (column name 1, column name 2,...);
(3) Specify index when creating table: Create table table name ([...], index index name (column name 1, column name 2,...));
Unique index
Represents a unique, not allowed duplicate index, which can be set to unique if the field information is guaranteed not to be repeated, such as a social security number as an index:
(1) Creating index: Create UNIQUE index name on table name (List of columns);
(2) Modify table: ALTER table name add UNIQUE index name (List of columns);
(3) Specify index when creating table: Create table table name ([...], UNIQUE index name (List of columns));
Primary key: PRIMARY key index
The primary key is a unique index, but it must be specified as "PRIMARY key".
(1) The primary key is typically specified when creating the table: "CREATE table table name ([...], PRIMARY KEY (List of columns)"); ”。
(2) However, we can also add the primary key by modifying the table: "ALTER table table name add PRIMARY key (List of columns); ”。
There can be only one primary key per table. (The primary key is equivalent to the aggregate index, which is the fastest index to find)

MySQL Storage engine:
(1) MyISAM: There is a higher insert, query speed, but does not support transactions, data files and index files can be in different directories, 5.5.5 version
Front is the default engine
(2) InnoDB: Supports the ACID properties of transactions, provides MySQL with the ability to commit, rollback, and crash recovery transactions, the new version of the default engine
(3) Memory

Simple summary of the database