Programmer interview written book study Record (iii) (database-related knowledge)

The difference between a relational database system and a file database system is as follows:

(a) The main feature of a relational database system is the structure of the data, which is unstructured in the data database system.

(b) In a relational database system, the logical structure that the user sees is a two-dimensional table, and the basic element in a file database system is a file.

(c) File database system can realize multimedia file management, support C/s working mode.

Acid, which is an abbreviation for the four basic elements that the database transaction performs correctly. Contains: atomicity (atomicity), consistency (consistency), isolation (isolation), persistence (durability).

Data query: Select SELECT * FROM table WHERE condition statement

Data manipulation: INSERT into table (field name 1, field Name 2 ...) ) VALUES (value 1, value 2 ...) ）

Update table SET field name = field value WHERE Condition statement

Delete from table where conditional expression

Data definition: CREATE TABLE tablename (field 1, Field 2 ...) ）

DROP TABLE TableName

Data Control: Grant < system permissions >|< roles >[,< system privileges >|< roles >]...to < user name >|< role >|public[,< username >| < role; [with admin option]

Revoke < system permissions >|< roles >[,< system privileges >|< roles >]...from < user name >|< role >|public[,< username >|< a character;

Differences between internal and external connections

stored procedure, the SQL statement executes the time to compile first, and then be executed. In large database systems, in order to improve efficiency, the SQL statement set for specific functions is compiled and optimized and stored in the database server, and the user invokes execution by specifying the name of the stored procedure.

In the database, the lock is mainly a kind of protection mechanism to read/write the heap data, and from the point of view of database system, the lock can be divided into shared lock and mutual exclusion lock. Shared locks are referred to as s, also called Read locks. A mutex is called an X-lock, also known as an exclusive lock, which is used for data modification operations.

Common paradigms are 1nf,2nf,3nf,bcnf, and 4NF.

A view is a logical window of data that is picked up from a base table in a database, unlike a base table. It is a virtual table, in the database, only the definition of the view is stored, not the data items that the view contains, these items are still stored in the original basic table structure.

A trigger is a special type of stored procedure that is triggered by an event rather than a program call or a manual startup. These SQL statements are automatically completed when the database has special operations that are performed by the events in the database. The use of triggers can be used to ensure the validity and completeness of data, and to accomplish more complex data constraints than constraints.

A pre-trigger occurs before an event to verify some conditions or perform some preparatory work; an afterthought occurs after the event, doing the finishing touches to ensure the integrity of the transaction. The pre-trigger can get the previous and new field values. Statement-level triggers can be executed before or after the statement executes, and row-level firings are triggered once for each row affected by the trigger.

An index is a mechanism for increasing the speed of a database query, which is an object created in ascending or descending order on a table or view of a database based on the value of a key field. When the user queries the index field, it can quickly perform the retrieval operation, with the help of the index, you can quickly find the data you need without scanning the entire table when you execute the query. An index is a structure on a disk that is associated with a table or view, a structure that sorts the column values in a table to speed up the retrieval of rows from a table or view, and to perform queries without having to scan the entire table for faster access to information in the database.

The index structure of the general B-tree, plus + trees, of course, can also make hash tables. Creating an index can greatly improve the performance of your system:

(a) by creating a unique index, you can guarantee the uniqueness of each row of data in a database table.

(b) By indexing, the speed of data retrieval can be greatly accelerated.

(c) The index can accelerate the connection between tables and tables, thus effectively achieving the referential integrity of the data.

(d) When using grouping and sorting clauses for data retrieval, you can significantly reduce the time for grouping and sorting in queries.

(e) By using an index, you can improve the performance of your system by using an optimized hidden device during the query process.

B + trees are most commonly used, and performance is not bad, both for range queries and single-valued queries. In particular, the scope of the query, must use the B + tree in order to be able to.

Hash if only for the single-valued query speed than the B + tree faster.

The use of bitmaps is very limited, only a few cases can be used, it is important to determine the real appropriate use of this index adoption (the value of the type is very small and need to compound query), or build a lot of bitmaps have no meaning.

A cursor is a data buffer opened by the system for the user, which holds the results of the SQL statement execution. Each cursor area has a name that allows the user to retrieve the record from the cursor one at a-and assign it to the main variable for further processing by the main language.

Programmer interview written book study Record (iii) (database-related knowledge)