Classic database problems, classic databases

Classic database problems, classic databases

1. What are the advantages of stored procedures? Advantages of stored procedures: 1. Improved performance 2. Reduced network congestion 3. better consistency 4. Improved security mechanisms

2. What is a trigger? Which types of triggers are available? What are the advantages of triggers?

A trigger is a code block consisting of a T-SQL statement set that is activated in response to certain actions

Insert, delete, and update triggers

It prevents data from being incorrect, unauthorized, and inconsistent changes.

3. What are the common constraints? What do they mean? How to use it?

1) entity integrity: the primary key ensures entity integrity. A table has only one primary key, but a primary key can contain multiple fields. The primary key field cannot be blank.

2) integrity of reference: The foreign key ensures the integrity of reference. A table can have multiple foreign keys.

3) User-Defined Integrity: CHECK ensures domain integrity. A table can have multiple CHECK constraints.

4. transactions are a series of database operations and basic logical units of database applications.

Transaction nature: ACID properties
Atomicity: All the operations of a transaction are either correctly reflected or completely not reflected in the database;

Consistency: data is consistent before and after execution. For example, the total amount of money is consistent before and after the transfer system is executed;

Isolation: Although multiple transactions may be executed concurrently, the execution result is the same as that of a serial execution. Therefore, every transaction cannot feel that other transactions are being executed concurrently in the system, isolation is the main task of concurrency control;

Durability: after a transaction is successfully executed, its changes to the database must be permanent. Even if a system failure occurs, Durability is the main task of restoring the system.

5. Inner join and outer join
The internal connection ensures that all rows in the two tables must meet the connection conditions, while the external connection does not. In the outer join, some columns that are not qualified are displayed. That is to say, only the rows of one table are restricted, but the rows of the other table are not restricted. Left join, right join, and full join

6. View

A view is a specific subset of database data. You can prohibit all users from accessing database tables, but require users to operate data only through views. This method can protect users and applications from modifying some databases. 2. The view is abstract. When used, the view extracts data from the table to form a virtual table. The view does not have its own data. When you operate the view, the database converts the corresponding operation to the operation of the corresponding table. The view is only a pre-defined SQL operation, it is a table created on top of a basic table. Its structure (that is, the defined column) and content (that is, all data rows) come from the basic table, it exists based on the basic table. A view can correspond to one basic table or multiple basic tables.

7. Index: References

Advantages:

First, it can greatly speed up data retrieval, which is also the main reason for creating an index.

Second, you can create a unique index to ensure the uniqueness of each row of data in the database table.

Disadvantages:

First, it takes time to create and maintain indexes. This time increases with the increase of data volume.
Second, indexes occupy physical space. In addition to data tables, each index occupies a certain amount of physical space. To create a clustered index, the required space is larger.
Third, when adding, deleting, and modifying data in the table, the index must also be dynamically maintained, which reduces the Data Maintenance speed.

Indexes are created on certain columns in the database table. Therefore, when creating an index, you should carefully consider which columns can create an index and which Columns cannot create an index. In general, you should create an index on these columns, for example:

You can speed up the search on columns that frequently need to be searched;
In a column that acts as a primary key, the uniqueness of the column and the data arrangement structure in the organization table are enforced;
These columns are usually used in connection columns. These columns are mainly foreign keys, which can speed up the connection;
Create an index on a column that often needs to be searched by range. The specified range is continuous because the index has been sorted;
Create an index on the columns that frequently need to be sorted. Because the index has been sorted, you can use the index sorting to speed up the sorting query time;
Create an index on the columns in the WHERE clause frequently to speed up condition judgment.

Similarly, indexes should not be created for some columns. In general, these columns that should not be indexed have the following features:

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, there is an index or no index, and the query speed cannot be improved. On the contrary, the addition of indexes reduces the system maintenance speed and space requirements.
Second, indexes should not be added for those columns with few data values. This is because these columns have very few values, such as gender columns in the personnel table. In the query results, the data rows in the result set account for a large proportion of the data rows in the table, that is, the proportion of data rows to be searched in the table is large. Adding indexes does not significantly accelerate the search speed.
Third, indexes should not be added for columns defined as text, image, and bit data types. This is because the data volume of these columns is either large or small.
Fourth, when the modification performance is much higher than the retrieval performance, you should not create a cable reference. This is because the modification performance and retrieval performance are inconsistent. When an index is added, the search 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, when the modification performance is much higher than the retrieval performance, you should not create an index.

Several Indexing Methods

1. ordered index (not needed) 2. B + tree index (most commonly used, such as InnoDB of mysql) 3. hash index (not used much)

8. Reference to the four isolation levels defined in the SQL standard (both transaction-related)

The isolation level can be reflected through some phenomena. These phenomena include:

1. lost update: if the system allows two transactions to update the same data at the same time, the update is lost.

2. dirty read: dirty reads are generated when a transaction reads modifications that have not been committed by another transaction.

3. nonrepeatableread: The same query is performed multiple times in the same transaction. Because of the modifications or deletions made by other commit firms, different result sets are returned each time, in this case, non-repeated read occurs.

4. phantom read: The same query is performed multiple times in the same transaction. Because of the insert operations performed by other commit firms, different result sets are returned each time, and a phantom read occurs.

Below are the possible or impossible phenomena of the isolation level and its corresponding

	Dirty Read	NonRepeatable Read	Phantom Read
Read uncommitted	Possible	Possible	Possible
Read committed	Not possible	Possible	Possible
Repeatable read	Not possible	Not possible	Possible
Serializable	Not possible	Not possible	Not possible

ORACLE isolation level ORACLE provides readcommitted and serializable in the SQL92 Standard

The InnoDB Storage engine of mysql supports Repeatable read by default.

In the standard SQL specification, four transaction isolation levels are defined. Different isolation levels process different transactions:

◆ Unauthorized Read (Read Uncommitted): Dirty Read is allowed, but update loss is not allowed. If a transaction has started to write data, the write operation of another transaction is not allowed at the same time, but other transactions are allowed to read this row of data. This isolation level can be achieved through the exclusive write lock.

◆ Authorized Read (Read Committed): Allow repeated Read, but do not allow dirty Read. This can be achieved through "instant shared read lock" and "exclusive write lock. Transactions that read data allow other transactions to continue to access this row of data, but uncommitted write transactions will prohibit other transactions from accessing this row.

◆ Repeatable Read: duplicate reading and dirty reading are prohibited, but phantom data may sometimes appear. This can be achieved through the "shared read lock" and "exclusive write lock. The transaction that reads data will prohibit the write transaction (but allow the read transaction), and the write transaction will prohibit any other transactions.

◆ Serializable: provides strict transaction isolation. It requires the transaction to be serialized. The transaction can only be executed one by one, but cannot be executed concurrently. If transaction serialization is not possible only through the "Row-Level Lock", other mechanisms must be used to ensure that the newly inserted data is not accessed by the transaction that just performs the query operation.

The higher the isolation level, the more data integrity and consistency can be guaranteed, but the greater the impact on concurrency performance, by selecting different isolation levels, you can avoid the various problems mentioned above in transaction processing to different degrees. Therefore, selecting the database isolation level is particularly important. When selecting the database isolation level, pay attention to the following principles:

First, you must exclude "Unauthorized Read" because it is very dangerous to use it between multiple transactions. The rollback or failure of the transaction will affect other concurrent transactions. The rollback of the first transaction will completely clear the operations of other transactions, and even cause the database to be in an inconsistent state. It is very likely that the last modification to the data is submitted by a transaction that has been rolled back to the end, because "unauthorized reading" allows other transactions to read data, finally, the entire error state is spread between other transactions.

Second, most applications do not require "serialization" isolation (generally speaking, reading phantom data is not a problem), and this isolation level is hard to measure. Currently, pessimistic locks are generally used in applications that use serialization isolation, which forces all transactions to be serialized and executed.

The rest is selected between "authorized read" and "Repeatable read. We should first consider repeated reading. If all data is accessed in a unified atomic database transaction, this isolation level eliminates the possibility that one transaction will overwrite data in another concurrent transaction process (the second transaction update loss problem ). This is a very important issue, but the use of repeatable reading is not the only way to solve the problem.

9. clustered and non-clustered Indexes

A clustered index sorts the actual data on a disk by the values of one or more specified columns. Because the index page pointer of the clustered index points to the data page, using the clustered index to search for data is almost always faster than using a non-clustered index. Each table can only create a clustered index. Under the clustered index, data is physically arranged on the data page in order, and duplicate values are also arranged together, therefore, when the include range check (between, <, <=, & gt;,> =) or use groupby or order by queries, once a row with the first key value in the range is found, the row with the subsequent index value is physically contiguous without further searching, avoiding large-scale scanning, this greatly improves the query speed.

 

10. Functions of Log File redo and undo

Before transaction T starts, the log writes the record <T start>. During the execution, T executes any write (X) before the operation, you must write a new record to the log. When T is submitted, the log writes the record <T commit>. In general, the log is written first, and then the record is updated.

Here we will talk about the general restoration method:
(1) scan the log file positively (from start to end), locate the transactions that have been committed before the fault occurs (there are begin transaction and commit records), and redo the logs. At the same time, locate the unfinished transactions (only begin transaction, no commit) at the time of the fault, and record the identifiers to the (undo) queue.
(2) undo all transactions in the undo queue. The undo processing method is to reverse scan the log file and perform a reverse operation on the update operations of each undo transaction to write the "pre-update value" in the log records to the database.

(3) Redo the transactions in the redo log. The redo processing method is to scan logs forward and re-execute log file registration for each redo transaction. Write the "updated value" in the log to the database.

Classic database Problems

Question 1
Select idCard, count (*) from carApplyDetail group by idCard having count (*)> 1

Question 2
Sorry, I didn't think about how to update two tables with one statement, or use two statements, or write a trigger event in the database.
Question 3
Select applydate, count (*) as shul from cardapply group by applydate order by shul desc
Alas, that's it. The first record is the most,
Although the record has more points

What is a classic problem with SQL Server databases ??

The relationship in SQL Server is not directly related to the view.
"Relationship" is generally used for data integrity check. For example, when the F11 field in Table T1 is associated with the F21 field in Table T2, it can constrain the data in the two tables when they store data, you can also set "cascade Delete" to delete a record in the master table and automatically delete related records in the Word Table.

The "relationship" between tables does not have any relationship with the query. You must specify the logical association relationship during the query.