First, the basic terminology
DML (Data Manipulation language):
Languages such as SELECT, UPDATE, INSERT, DELETE, which are primarily used to manipulate data in the database
DDL (data definition Language):
The main commands are create, alter, DROP, etc., the DDL is mainly used in the initialization of defining or altering tables (table), data types, links between tables, and constraints, etc., mostly when establishing tables.
DCL (Data Control Language):
Database control functions. is a statement that is used to set or change permissions for a database user or role, including (Grant,deny,revoke, etc.) statements. By default, only people such as Sysadmin,dbcreator,db_owner or db_securityadmin have the power to perform DCL
Ii. the three paradigms of the database
relational database design table in order to reduce redundancy, enhance the effectiveness of data and storage efficiency, need to follow certain specifications, these specifications according to the strict degree of distinction can be divided into the first paradigm (1NF), the second paradigm (2NF), the third normal form (3NF), BC Paradigm (BCNF), the fourth paradigm (4NF), Model Five (5NF) and so on.
Database paradigm is an indispensable theoretical basis in database design, not understanding the database paradigm can not design a funny and elegant database, and even rely on chaos, operation often error. In practical applications, the usual paradigms are the first paradigm (1NF), the second paradigm (2NF) and the third Normal (3NF), and the latter three paradigms are less, because proper redundancy can increase the efficiency of database query.
First normal form (1NF): attribute is not divided
The first paradigm requires that all fields in a database table are atomic values that are not divided. A database that does not meet the first paradigm, not a relational database!
Second Normal form (2NF): The non-principal attribute is completely dependent on the code
The second paradigm, which is based on the first paradigm, requires that the database have a primary key, and that other non-primary properties depend entirely on the primary key, and not only on the part of the primary key. That is, if a non-primary property depends only on one or a subset of the attributes in the Federated primary key, it does not conform to the second normal. The second paradigm implicitly requires a database to hold only one type of data, and try not to save multiple data to a single table.
Third paradigm (3NF): No transitive dependencies exist between non-primary attributes
The third paradigm is a further step on the basis of the second paradigm, which requires that a property not be dependent on a non-primary attribute in the table. The third paradigm needs to ensure that each column of data in a data table is directly related to the primary key, not indirectly.
Third, the business:
A transaction (Transaction) is the basic unit of concurrency control. The so-called transaction, a collection of operations, that either does not execute, or executes all, and does not allow the execution of a part of it, which controls a series of operations as a whole. such as bank transfer, the simplification of the need for at least two steps, 1: to deduct 100 yuan from a account, 2: to the B account increase of 100 yuan, these two operations must be controlled as a whole, if the account receivable from a successful, to the B account to increase the amount of failure, the transaction must be rolled back (Roolback), That is, the debit of the A account is returned to the a account.
A transaction has four basic characteristics (ACID):
Atomic (atomicity):
The operations contained in a transaction are considered to be a logical unit in which the operations of the logical unit either succeed or fail altogether.
Consistency (consistency):
Only legitimate data can be written to the database, or the transaction should roll back to its original state.
Isolation (Isolation):
Transactions allow multiple users to concurrently access the same data without destroying the correctness and integrity of the data. At the same time, the modification of parallel transactions must be independent of the modifications of other parallel transactions. This indicates that the transaction must be independent and should not be in any way or affect other transactions.
Durability (persistent):
After the transaction ends, the result of the transaction must be cured. After the transaction is complete, its effect on the system is permanent, and the modification will persist even if a system failure occurs, and the database is actually modified.
If multiple transactions affect the same or the same data at the same time, it can result in data clutter or failure of the transaction execution, so the concurrency of the transaction needs to be controlled. The isolation level is the level of concurrency control over transactions. The SQL-92 standard defines four isolation levels, serialization (SERIALIZABLE), repeatable read (repeatable read), Read committed (reads commited), read UNCOMMITTED (read uncommited):
√ Indicates a possible occurrence, and x indicates that it will not occur
|
Dirty Read |
Non-REPEATABLE READ |
Phantom reading |
Read Uncommitted |
√ |
√ |
√ |
Read Committed |
x |
√ |
√ |
Repeatable Read |
x |
x |
√ |
Serializable
|
X |
X |
X |
If there are two transactions A and B are all operating on the same database:
1, if the database isolation level is set to read UNCOMMITTED, transaction a first operations the database, modified a data, but did not commit the transaction, at this time, the transaction B read the database, will read to transaction a uncommitted dirty data, that is dirty read ;
2, if the database isolation level is set to read Committed, transaction a first operation of the database, read the data data_a, and then transaction b in the database to modify the corresponding data to Data_b and commit the transaction, when transaction a again read the same data, the data becomes data_b, that is Non-repeatable reading ;
3, if the database isolation level is set to repeatable read, transaction a first reads some data under a condition, then transaction B inserts a piece of data and commits, and this data exactly satisfies the select condition of transaction A, at which time transaction a then goes to the database to query the data under that condition. Found more than the previous one, as if the illusion of general, called Phantom reading ;
4. If the database isolation level is set to serializable, all transactions are performed in line one, and only the last transaction is completed before the next transaction is started, so dirty reads, non-repeatable reads, and Phantom reads are unlikely to occur.
The difference between non-repeatable reading and phantom reading is that non-repeatable reading is due to inconsistent data caused by the modification of data, and phantom reading because data inconsistency is caused by inserting data. The default isolation level for SQL Server and Oracle is the read Committed,mysql database, the default isolation level is repeatable read Iv. Database Lock
The locking mechanism of the database system itself:
1. Share lock (Shared lock) The S lock allows multiple transactions to read the same data at the same time, but does not allow the data to be modified (in conflict with the X lock).
2, UPDATE lock Update is also known as the change lock, the transaction in the update of an item of data need to add U lock, read the data item, at this time allow other transactions to add S lock on the item, after the transaction is read and modified, the U lock is promoted to x lock, and then write the modified data.
3, Exclusive lock (Exclusive Lock): exclusive Lock is an exclusive lock, generally used to modify data, select...for update
4. Intent Lock (Intent Lock): Intent Lock is a table-level lock that indicates the intention to acquire a shared or exclusive lock at the bottom of the resource. For example, when a page in the table is read, the transaction requests a shared intent lock at the table level before the page share lock (s lock) is requested. This prevents other transactions from subsequently acquiring an exclusive lock (x Lock) on the table, modifying the entire table. Intent locks can improve performance because the database engine examines intent locks only at the table level, determines whether transactions can safely get locks on the table, and does not need to check each row in the table or lock on each page to determine whether the transaction can lock the entire table. There are two uses for intent locks: to prevent other transactions from modifying higher-level resources in such a way that they invalidate the lower-level locks, and to improve the efficiency of the database engine to detect lock collisions at a high level of granularity.
Intent shared Lock (IS): Indicates that a transaction is ready to join a shared lock on a data row, that is, a data row must be acquired before a shared lock
Intent exclusive Lock (IX): Similar to the above, indicates that the transaction is prepared to add an exclusive lock to the data row, stating that the transaction must obtain an IX lock on the table before a data row is added to the exclusive lock.
the compatibility of various locks is as follows:
  |
Intent to share (IS) |
share (S) |
update (U) |
intent Exclusive (IX) |
exclusive (X) |
Intent to share (IS) |
|
is |
Yes |
Yes |
no |
share (S) |
Yes |
is |
Yes |
no |
no |
update (U) |
Yes |
is |
no |
no |
no |
intent Exclusive (IX) |
|
no |
no |
Yes |
no |
Exclusive (X) |
Whether |
Whether |
Whether |
Whether |
Whether |
5. Mode Lock (Schema Lock): Altert table, using when changing tables structure
6. Batch update lock (Bulk update lock): Database backup Recovery
Lock mechanism at the business level, Hibernate supports the following two types of locks
Pessimistic Lock:
The conservative attitude to the data being modified is that more than one transaction is often modified to modify the same piece of data, so all transactions have to acquire a lock before the data is modified to repel the operation of the other transaction on the data. Pessimistic lock implementation, often rely on the database provided by the lock mechanism (also only the database layer provides a lock mechanism to truly guarantee the exclusivity of data access, otherwise, even in this system to implement the locking mechanism, there is no guarantee that the external system will not modify the data). If the select * form user where Id=#{id} for update, the statement causes the database system to lock the record.
Optimistic Lock:
Optimistic lock the fact that multiple transactions concurrently modify the same record of the database is not a frequent occurrence, and locking the transaction operation is not necessary, that is optimistic about the matter. But also to ensure the consistency of the data, optimistic lock to use a flag to indicate whether there are other transactions in the operation of the data, such a flag bit is based on the data version of the record mechanism is implemented.
Classification of Locks (Oracle)
1, according to the Operation division, can be divided into DML lock, DDL lock
2, according to the granularity of the lock division, can be divided into table-level lock, row-level lock, page-level lock (MySQL)
3, according to the lock level division, can be divided into shared lock, exclusive lock
4, according to the lock mode division, can be divided into automatic lock, display lock
5, according to the use of the way divided, can be divided into optimistic lock, pessimistic lock
V. Other
Views (view)
A database view is a virtual table, a logical table, sometimes for the purpose of distinguishing between views and tables, also called tables as basic tables--base table. The data corresponding to the view is not physically stored and does not occupy the physical space. The complexity of the view mask database, the view separates the complexity of the database design from the user's shielding, also can play the role of decoupling the user program and the actual database table structure.
Stored Procedures
A stored procedure is a precompiled collection of SQL statements and optional control-flow statements, stored as a single name and processed as a unit. Stored procedures are stored in the database and can be executed by the application through a call, and allow the user to declare variables, conditional execution, and other powerful programming features.
The difference between a stored procedure and a function:
1. In general, function implementation of the function is relatively strong, and the function of the stored procedure implementation is a little more complicated.
2: Stored procedures can return parameters, whereas functions can only return values or table objects.
3. Typically, the stored procedure is executed as a separate part, and the function is called as part of the query statement, since the function can return a Table object, so it can appear after the FROM keyword of the query statement.
Trigger
Triggers, like stored procedures, are also a SQL program. The difference is that the stored procedure is called directly by name, and the trigger is equivalent to the event listener, which is automatically called by the database system when a particular event occurs.
Trigger Syntax:
Create Trigger on befor| of Update | Insert | Delete as SQL statements
Under MySQL's InnoDB engine (the engine under which the table is the transaction table), the trigger and the SQL statement that caused the trigger to execute are transactional, and if the trigger execution of the before type fails then the SQL statement does not execute, and if the SQL statement execution fails, the after trigger does not execute, If after trigger execution fails, it causes the SQL statement that the trigger executes to roll back.
Cursor
A cursor is a data buffer, a memory area, that is used to temporarily hold data that is affected by an SQL statement. That is, the affected data is temporarily stored in a virtual table, the virtual table is a cursor.
Orale the type of the mid-target:
1, implicit cursor: An implicit cursor is automatically created when a DML SQL statement is executed in a PL/SQL program, and the name is fixed to SQL. Things in the database can be rolled back, and cursors play a very important role, because the operation of the database we will be temporarily placed in the cursor, as long as we do not commit, we can be based on the content of the cursor rollback, in a sense to facilitate the security of the database.
2, explicit cursors: Used to remove multiple rows of data from a table and to process multiple rows of data on a single line.
Declare -- 1, declaring cursors cursor is Select * from User ; begin -- for inendend;
A summary of relational database knowledge