Shulou(Shulou.com)05/31 Report--

This article mainly explains "what are the interview questions for the basic concepts of the database". The content of the explanation in the article is simple and clear, and it is easy to learn and understand. let's study and learn what are the interview questions for the basic concepts of the database.

1. Superkeys, candidate keys, primary keys, foreign keys

Superkey: a set of attributes that uniquely identifies a tuple in a relationship is called a hyperkey in a relational schema. An attribute can be used as a superkey, or multiple attributes can be combined as a superkey. The superkey contains a candidate key and a primary key.

Candidate key: is the minimum superkey, that is, a superkey with no redundant elements.

Primary key: a combination of data columns or attributes in a database table that uniquely and fully identify 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 null (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 a transaction? What is a lock?

Transaction: a grouping of SQL statements bound together as a logical unit of work. If any statement operation fails, the entire operation fails, and later the operation is rolled back to the pre-operation state, or there is a node on it. Transactions can be used to ensure that either they are executed or not. To consider grouped statements as transactions, you need to pass ACID tests, that is, atomicity, consistency, isolation, and persistence.

Locks: in all DBMS, locks are the key to implementing transactions, and locks can ensure the integrity and concurrency of transactions. Like locks in real life, it can prevent the owner of some data from using certain data or data structures for a certain period of time. Of course, there are different levels of locks.

3. Four characteristics and meanings of database transactions.

Atomicity: all operations in the whole transaction are either completed or not completed, and cannot be stuck in the middle. An error occurs during the execution of a transaction and is Rollback back to its state before the transaction starts, as if the transaction had never been executed.

Consistency: before the transaction starts and after the transaction ends, the integrity constraints of the database are not broken.

Isolation: transactions are executed in an isolated state so that they appear to be the only operations performed by the system at a given time. If there are two transactions that run at the same time and perform the same function, the isolation of the transaction ensures that each transaction is thought to be the only transaction in the system that is using the system. This attribute is sometimes called serialization, and to prevent confusion between transaction operations, requests must be serialized or serialized so that there is only one request for the same data at a time.

Persistence: after the transaction completes, the changes made by the transaction to the database are persisted in the database and are not rolled back.

4. What is a view?

A view is a virtual table with the same function as a physical table. The view can be added, modified, checked, manipulated, and attempted to be a subset of rows or columns that usually have one or more tables. Changes to the view do not affect the underlying table. It makes it easier for us to get data than multi-table queries.

Views are generally used in the following two scenarios:

(1) do not want the visitor to get the information of the whole table, only expose some fields to the visitor, so build a virtual table, that is, the view.

(2) the queried data comes from different tables, and the querier wants to query in a unified way, so that a view can be established to combine the query results of multiple tables, and the querier only needs to obtain the data directly from the view. there is no need to consider the differences caused by the data coming from different tables.

Note: this view is created in the database, not in code.

5. What is the function of the trigger?

Trigger is a special stored procedure, which is mainly triggered by events and executed. It can strengthen constraints to maintain data integrity and consistency, and can track operations within the database so as not to allow unauthorized updates and changes. It can be cascaded. For example, a trigger on a table contains a data operation on another table, which in turn causes the table trigger to be triggered.

6. to maintain the integrity and consistency of the database, do you prefer to use triggers or write your own business logic? Why?

It is most efficient and convenient to use constraints such as check, primary key, foreign key, non-empty fields, etc., whenever possible. The second is the use of triggers, this method can ensure that no matter what business system accesses the database can ensure the integrity and consistency of the data. The last consideration is self-writing business logic, but it is troublesome, complex and inefficient.

7. What is the function of the index? And what are its advantages and disadvantages?

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

In addition to the data, the database system also maintains data structures that meet specific search algorithms, which refer to (point to) the data in some way, so that advanced search algorithms can be implemented on these data structures. This kind of data structure is the index.

There is a price to pay for setting an index for a table: one is to increase the storage space of the database, and the other is to spend more time inserting and modifying data (because the index has to change with it).

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

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

Second, it can greatly accelerate the speed of data retrieval, which is also the main reason for creating an index.

Third, the connection between the meter and the table can be accelerated, especially in achieving the referential integrity of the data.

Fourth, when using grouping and sorting clauses for data retrieval, the time of grouping and sorting in the query can also be significantly reduced.

Fifth, through the use of index, we can use the optimization hidden device in the process of query to improve the performance of the system.

One might ask: adding an index has so many advantages, why not create an index on each column in the table? Because, adding an index also has many disadvantages:

First, it takes time to create and maintain an index, which increases 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 amount of physical space, if you want to establish a clustered index, then the space needed will be more.

Third, when the data in the table is added, deleted and modified, the index should also be maintained dynamically, which reduces the speed of data maintenance.

The index is built on top of some columns in the database table. When creating an index, you should consider on which columns you can create an index and on which columns you cannot.

In general, indexes should be created on these columns:

(1) you can speed up the search on the columns that often need to be searched.

(2) on the column as the primary key, force the uniqueness of the column and organize the arrangement structure of the data in the table.

(3) on the columns that are often used in joins, these columns are mainly foreign keys, which can speed up the join.

(4) create an index on a column that often needs to search by range, because the index is sorted and its specified range is continuous.

(5) create an index on columns that often need to be sorted, because the index is already sorted, so that the query can make use of the sorting of the index to speed up the sorting query time.

(6) create an index on the columns that are often used in the WHERE clause to speed up the judgment of conditions.

Similarly, indexes should not be created for some columns:

First, indexes should not be created for columns that are rarely used or referenced in queries. This is because, since these columns are rarely used, indexing or no indexing does not improve query speed. On the contrary, due to the increase of the index, the maintenance speed of the system is reduced and the space requirement is increased.

Second, the index should not be added to columns that have only a few data values. This is because, because these columns have very few values, such as the gender column of the personnel table, the data rows of the result set account for a large proportion of the data rows in the table in the results of the query, that is, a large proportion of the data rows that need to be searched in the table. Increasing the index does not significantly speed up the retrieval speed.

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

Fourth, indexes should not be created when modification performance is much greater than retrieval performance. This is because modification performance and retrieval performance contradict each other. When the index is added, the retrieval performance is improved, but the 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 modification performance is much greater than retrieval performance.

8. The difference between drop,delete and truncate

Drop deletes the table directly.

Truncate deletes the data from the table, and the self-growing id starts at 1 when you insert it.

Delete deletes the data in the table by adding the where sentence.

(1) the process of DELETE statement performing deletion is to delete a row from the table each time, and at the same time save the deletion of that row in the log as a transaction record for rollback operation. On the other hand, TRUNCATE TABLE deletes all the data from the table at once and does not log the individual delete operation records, and the delete row cannot be recovered. And table-related delete triggers are not activated during deletion. The execution speed is fast.

(2) the space occupied by tables and indexes. When a table is TRUNCATE, the space occupied by the table and index is restored to its original size, while the DELETE operation does not reduce the space occupied by the table or index. The drop statement frees up all the space occupied by the table.

Generally speaking, drop > truncate > delete

(4) the scope of application. TRUNCATE can only be for TABLE;DELETE. It can be table and view.

(5) TRUNCATE and DELETE delete only data, while DROP deletes the entire table (structure and data).

(6) truncate and delete without where: only delete data, not delete table structure (definition) drop statement will delete table structure dependent constraints (constrain), trigger (trigger) index (index); stored procedures / functions that depend on the table will be retained, but its state will be changed to: invalid.

(7) the delete statement is DML (data maintain Language), and this operation will be put into the rollback segment and will not take effect until the transaction is committed. If there is a corresponding tigger, it will be triggered during execution.

(8) truncate and drop are DLL (data define language), and the operation takes effect immediately. The original data is not put into the rollback segment and cannot be rolled back.

(9) use drop and truncate carefully in the absence of backup. To delete some data rows, use delete and note that the scope of influence is constrained in combination with where. The rollback segment should be large enough. To delete a table use drop; if you want to keep the table and delete the data in the table, if it has nothing to do with the transaction, you can do it with truncate. If it is related to business, or if the teacher wants to trigger trigger, use delete.

(10) Truncate table table names are fast and efficient because:

Truncate table is functionally the same as a DELETE statement without a WHERE clause: both delete all rows in the table. However, TRUNCATE TABLE is faster than DELETE and uses fewer system and transaction log resources. The DELETE statement deletes one row at a time and records one entry for each row deleted in the transaction log. TRUNCATE TABLE deletes the data by releasing the data pages used to store the table data, and only records the release of the pages in the transaction log.

(11) TRUNCATE TABLE deletes all rows in the table, but the table structure and its columns, constraints, indexes, and so on remain unchanged. The count value used for the new row identity is reset to the seed of the column. If you want to keep the identity count value, use DELETE instead. Use the DROP TABLE statement if you want to delete the table definition and its data.

(12) instead of using TRUNCATE TABLE for tables referenced by FOREIGN KEY constraints, you should use a DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not logged, it cannot activate the trigger.

9. SQL common commands:

CREATE TABLE Student (ID NUMBER PRIMARY KEY, NAME VARCHAR2 (50) NOT NULL); / / Table CREATE VIEW view_name AS Select * FROM Table_name;// View Create UNIQUE INDEX index_name ON TableName (col_name); / / Index INSERT INTO tablename {column1,column2, … } values (exp1,exp2, …) ; / / insert INSERT INTO Viewname {column1,column2, … } values (exp1,exp2, …) ; / / insert view actually affects table UPDATE tablename SET name='zang 3 'condition;// update data DELETE FROM Tablename WHERE condition;// delete GRANT (Select,delete, …) ON (object) TO USER_NAME [WITH GRANT OPTION]; / / Authorization REVOKE (permission list) ON (object) FROM USER_NAME [WITH REVOKE OPTION] / / revoke authority

List the names of the staff and their leaders:

Select E.NAME, S.NAME FROM EMPLOYEE E S WHERE E.SUPERName=S.Name thank you for your reading, the above is the content of "what are the basic concepts of database interview questions". After the study of this article, I believe you have a deeper understanding of what the basic database concepts interview questions have, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.