Some basic concepts frequently asked in database interview __ Database

Some basic concepts often asked in database interview

1, Super Key, candidate key, primary key, foreign key

Super key: The set of attributes in a relationship that uniquely identifies a tuple is called a hyperlink to a relational pattern. A property can be used as a super key, and multiple attributes can be grouped together as a super key. The Super key contains candidate keys and primary keys.

Candidate Key: Is the minimum key, that is, a super key without redundant elements.

Primary key: A combination of data columns or attributes that are uniquely and fully identified in a database table for storing data objects. A data column can have only one primary key, and the value of a primary key cannot be missing, that is, it cannot be null.

FOREIGN key: The primary key of another table that exists in one table is called the foreign key of this table.

2, what is the transaction. What is a lock.

Transactions: is bound together as a logical unit of work, the SQL statements are grouped, and if any one of the statements fails, the entire operation is failed, and later operations are rolled back to the pre-operation state, or there is a node on it. You can use transactions to ensure that they are either executed or not performed. To consider a group statement as a transaction, you need to pass the ACID test, i.e. atomicity, consistency, isolation, and persistence.

Lock: In the DBMS, the lock is the key to the implementation of the transaction, the lock can guarantee the integrity and concurrency of the transaction. Like real-life locks, it allows some data owners to be unable to use certain data or structures for a certain period of time. Of course the locks are divided into levels.

3. Four features and implications of database transactions

Atomicity: All operations in the entire transaction, either complete or incomplete, cannot be stalled in the middle of the process. An error occurs during the execution of a transaction and is rolled back (Rollback) to the state before the transaction began, as if the transaction had never been performed.

Consistency: Database integrity constraints are not compromised until the transaction is started and after the transaction has ended.

Isolation: Quarantine states perform transactions so that they appear to be the only actions that the system performs within a given time. If there are two transactions running at the same time and performing the same function, the isolation of the transaction will ensure that each transaction is considered only in the system by the transaction. This attribute is sometimes referred to as serialization, in order to prevent confusion between transaction operations, the request must be serialized or serializable so that only one request at a time is used for the same data.

Persistence: After the transaction is completed, the changes made by the firm to the database are persisted in the database and are not rolled back.

4, what is the view.

A view is a virtual table with the same functionality as a physical table. You can add, change, check, and manipulate the view, often trying to have a subset of rows or columns of a table or multiple tables. Modifications to the view do not affect the base table. It makes it easier for us to get data than multiple table queries.

The following two scenarios are typically used in view:

(1) Do not want visitors to get the entire table of information, only exposed some of the fields to visitors, so build a virtual table, is the view.

(2) The query data from different tables, and the query people want to query in a unified way, this can also create a view, the query results of multiple tables together, the query only need to get data directly from the view, do not have to consider the data from different tables brought about by the difference.

Note: This view was created in the database rather than in code.

5, the role of triggers.

A trigger is a special stored procedure that is executed primarily by triggering events. It can harden constraints to maintain data integrity and consistency, and can track operations within the database to disallow unauthorized updates and changes. You can cascade operations. For example, a trigger on a table contains a data operation on another table, and that operation causes the table trigger to be triggered.

6, maintain the integrity and consistency of the database, you like to use triggers or write business logic. Why.

Use constraints as much as possible, such as check, primary key, foreign key, non-null field, and so on, to do the most efficient and convenient. The second is the use of triggers, which ensures that no matter what business system accesses the database, it guarantees the integrity and consistency of the data. The final consideration is to write business logic, but this does trouble, programming complex, inefficient.

7, the role of the index. And what is the disadvantage of its advantages.

Database index is a sort of data structure in the database management system to help quickly query and update data in database tables. The implementation of the index usually uses B-tree and its variant plus + tree.

In addition to data, the database system maintains a data structure that satisfies a particular lookup algorithm, which refers to data in some way, so that advanced lookup algorithms can be implemented on these data structures. This data structure is the index.

There is a cost to setting up the index for a table: the first is to increase the storage space of the database, and the second is to spend more time inserting and modifying the data (because the index will also change).

Creating an index can greatly improve system performance (benefits):

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

Second, you can greatly speed up the retrieval of data, which is the main reason to create indexes.

Third, you can speed up the connection between tables and tables, especially in terms of realizing the referential integrity of the data.

Finally, when you use grouping and sorting clauses for data retrieval, you can also significantly reduce the time to group and sort in a query.

In the process of querying, the optimization of the hidden device can be used to improve the performance of the system by using the index.

One might ask: why is there so many advantages to adding an index, and why not to create an index for each column in the table?

There are also many downside aspects to adding indexes:

First, it takes time to create indexes and maintain indexes, which increase as the amount of data increases.

Second, the index needs to occupy the physical space, in addition to the data table occupies the data space, each index also occupies a certain physical space, if you want to establish a clustered index, then need more space.

Third, when the data in the table is added, deleted and modified, the index will be maintained dynamically, thus reducing the data maintenance speed.

Indexes are built on top of some columns in a database table. When you create an index, you should consider which columns you can create indexes on, and on which columns you cannot create indexes.

In general, you should create indexes on these columns:

(1) In the frequently need to search the column, can speed up the search speed;

(2) Enforce the uniqueness of the column and the arrangement of the data in the organization table on the column as the primary key;

(3) Frequently used in connected columns, these columns are mainly some foreign keys, can speed up the connection speed;

(4) Create indexes on columns that often need to be searched according to scope, because the index is already sorted and its specified range is contiguous;

(5) Create indexes on columns that often need to be sorted, because indexes are sorted so that the query can use the sort of index to speed up the sorting query time;

(6) Create indexes on columns that are frequently used in the WHERE clause to speed up the judgment of conditions.

Similarly, for some columns, you should not create indexes:

First, you should not create indexes for columns that are rarely used or referenced in queries. This is because, since these columns are rarely used, they are indexed or indexed and do not increase the query speed. On the contrary, because of the addition of indexes, it reduces the maintenance speed of the system and increases the space requirement.

Second, you should not add indexes to columns that have very few data values. This is because, because of the low values of these columns, such as the gender column of the personnel table, the result set's data rows account for a large proportion of the data rows in the table, that is, the data rows that need to be searched in the table are large. Adding indexes does not significantly speed up the retrieval.

Third, columns that are defined as text, image, and bit data types should not be indexed. This is because these columns have either a large amount of data or a very small number of values.

Four, you should not create an index when the modification performance is far greater than the retrieval performance. This is because the modification performance and retrieval performance are contradictory. When indexing is added, retrieval performance is improved, but modification performance is reduced. When the index is reduced, the modification performance is improved and the retrieval performance is reduced. Therefore, indexes should not be created when the modification performance is far greater than the retrieval performance.

8, the difference between drop,delete and truncate

Drop deletes the table directly.

Truncate deletes the data in the table and then inserts it from the growth ID starting at 1.

Delete Deletes the data in the table, and you can add the WHERE clause.

(1) The DELETE statement performs a deletion by deleting a row from the table and saving the row's delete operation as a transaction record in the log for rollback operations. TRUNCATE table deletes all the data from the table once and does not log a separate delete action record to the journal Save, and the delete row is unrecoverable. And the delete trigger associated with the table is not activated during deletion. Faster execution.

(2) Space occupied by tables and indexes. When the table is truncate, the space occupied by the table and index is restored to its original size, and the delete operation does not reduce the amount of space occupied by the table or index. The drop statement frees up all the space occupied by the table.

(3) Generally, drop > truncate > Delete

(4) Application range. TRUNCATE can only be table and view for Table;delete

(5) TRUNCATE and delete delete data only, and drop deletes the entire table (structure and data).

(6) Truncate and no where Delete: delete only data without deleting the structure (definition) of the table the drop statement will delete the constraint (constrain) of the table's structure, the trigger (trigger) index, the stored procedure that depends on the table/ The function will be preserved, but its state will change to: invalid.

The

(7) DELETE statement is DML (data maintain Language), which is placed in the rollback segment and is not effective until the transaction is committed. If there is a corresponding Tigger, the execution will be triggered.