Summary of interview questions in the database

Question 1. What is the difference between a relational database and a file database?

A relational database is a collection of data items organized as tables, which can be accessed or re-convened in different access methods without the need to reorganize the data tables. The basic element of the relational type is the table, and the basic element of the file type is the file; the data of the relational database is structured (that is, it is accessed through a certain data structure, and the space overhead of managing the data is large, but it is more time-saving , Very fast), but the file database does not structure the data management, so the time cost is relatively high, and the space management is relatively small; in the relational database, the logical structure that the user sees is a two-dimensional table, while the file database In the system, the basic element is the file.

Question 2. What is the difference between inner connection and outer connection?

Question 3. What is a transaction?

During database processing, a series of actions (a single execution unit in the database) to be completed by an operation is called a transaction. For example, if a bank transfer is 100 yuan, first subtract 100 yuan from A account, and then add 100 yuan to B account. This process cannot be disturbed, otherwise there is a risk of not satisfying the entire constraint. This entire process is called a transaction.

The 4 attributes of the transaction (ACID):
A: Atomicity, the smallest unit that cannot be separated from the transaction, that is, your operation must be independent and the smallest unit of execution. Either execute all or none at all.
C: Consistency. Before and after your operation is executed, the constraints satisfied by the database data are unchanged, and the data is consistent.
I: Isolation, that is, during the operation process, the transactions always do not affect each other, even if they are operated concurrently, each transaction does not affect each other. Regarding isolation, there are corresponding database isolation levels from high to low, respectively serialization (this level is absolutely safe, but the concurrency performance is not high), repeatable read (concurrency performance is higher than serialization), read has been submitted , Read uncommitted
D: Persistence, after the operation, the changes to the data are permanent and will not change.

Once the changes are submitted using commit, the changed data will be written to the hard disk, and the changes will become permanent. If you want to roll back the data, use the rollback command. All changes will be undone and return to the state it was in before the transaction started.
What is changed is the data in the memory, as long as it is not written, the changes can be rolled back.

Question 4: What is a stored procedure? What is the difference and connection between it and function?

SQL statements need to be compiled and then run during execution. In the process of execution, some statements with specific functions are listed separately and stored in the database server because they are commonly used. The user only needs to call the stored procedure name. Call execution. This compiled and optimized SQL for a specific function is called a stored procedure.

Create a stored procedure and delete the process by yourself

Stored procedures and functions are essentially the same, but there are differences. Stored procedures may be a combination of multiple functions, and the functions implemented are more complex, while functions are generally more specific; stored procedures are generally completed by a single process , And the function can be called as a part of the query statement; the function cannot directly manipulate the entity table, only the built-in table; the stored procedure has been compiled on the server, and the execution speed will be faster.

Question 5: What is the primary key and what is the foreign key?

The primary key is a sign that can uniquely mark an object (record) in the database. It is not necessarily a 1-column attribute (keyword), but can also be multiple columns, and the primary key cannot be empty. Generally, primary keys are meaningless keys and are only used as distinguishing signs. The primary key should remain unchanged, that is, as the database data increases, the primary key remains the same; in principle, the primary key should be generated by the computer itself, rather than specified by the user.

A foreign key represents the connection between two relationships. For example, two relational databases A and B have the keyword x, but x is the primary key in A, but not the primary key in B, then x is the foreign key of B

Question 6. What is a deadlock?

Deadlock is due to mutually exclusive ownership of certain resources. When process 1 is waiting for process 2 to release the resources it owns, while process 2 is also waiting for process 1 to release the resources it owns, causing infinite waiting for each other. Of course, deadlock also occurs in multiple processes, not just two processes.

Four conditions are required to form a deadlock:
1. The resource must be mutually exclusive, and only one process can use it at a time
2. The possession is not preemptable, and the resource will not be forced to transfer due to other factors such as priority before it is used
3. Occupy and wait. Before waiting for enough resources, the process will wait until enough resources are obtained, and keep occupying resources
. 4. Waiting in a loop, a waiting relationship is formed end to end between several processes.

To break the deadlock, we must start from these 4 conditions

Question 6: What is a shared lock and what is an exclusive lock?
Shared locks (S locks) are locks during read operations (involving select operations and not updating data). Because no write operations are involved, multiple threads can read at the same time, so shared locks can be added at the same time.
An exclusive lock is a write operation lock (update, insert, delete, etc.). Since only one process can write at the same time, the concurrent performance of write operations is slightly worse. The shared lock must be removed before the exclusive lock is added. Therefore, if there is a shared lock, you cannot add an exclusive lock (X lock)

Pay attention to the lock related matters: the lock must be added before the operation, and the lock must be released after the operation

Question 7: What is the difference between the 1234 paradigm?

Paradigm refers to the guidelines that need to be followed when designing the database system, so that the designed database will be relatively convenient to use. And it will not cause data redundancy and update abnormality when storing data.

The first paradigm: the atomicity of attributes. A key can only correspond to one data, not multiple data. That is, each column of the database table is an indivisible basic data item. The first normal form requires that each column cannot be further subdivided.

Second paradigm: On the basis of satisfying the first paradigm, non-primary attributes are required to be completely dependent on the candidate key (that is, used to uniquely identify an instance in the database table, the candidate key may have multiple attributes). Generally, the candidate key is used separately One or more columns to mark, you can see that the definition of the candidate key is very similar to the primary key, but unlike the primary key, the candidate key is the only sign in the table, and the primary key is the only sign of the entire database. The non-primary key cannot derive the candidate key, and the non-primary key completely depends on the candidate key. That is, no matter how many attributes the candidate key has, it must be uniquely calibrated by all the attributes of the candidate key. If the candidate key has multiple attributes, but the candidate key is used When a certain attribute can uniquely calibrate a non-primary key attribute, this does not satisfy the second paradigm. At this time, the non-primary key attribute and the candidate key attribute should be made into a separate table. It is generally required that each record (that is, each row, or each instance) can be uniquely distinguished.

Simply put, each of our records must be uniquely distinguished, and non-primary attributes must be completely dependent on the primary attributes.

Insert picture description here

The third paradigm: Use any non-primary attributes to infer any non-primary attributes, and there is no transitive dependency between them (any other non-primary attributes cannot be inferred from the relationship between the non-primary attributes and candidate keys).

Question 8: What is a view?

A view is a logical window where the data in the basic tables in the database is pieced together, and it is something in a logical sense. It is a virtual table, not a physical table. In the database, only the definition of the view is stored, and the data of the view is not stored. These tables are still stored in the previous basic table.

What does the view do?
It can simplify the steps and statements of the data to be queried. If the data you need comes from multiple basic tables, and part of it comes from other views, writing query statements at this time is very complicated; the efficiency of the query cannot be improved, because the view is only a logical implementation. The implementation is still based on the previous things, which is easy to manage; you only need to focus on the data you want, without worrying about the overall data; by introducing views, the data has a certain degree of security, because of the search conditions and the operations between tables All are invisible.