Common Database interview questions and database questions

Common Database interview questions and database questions

Paradigm 1: Each column in a database table is a basic data item that cannot be further divided. values or duplicate attributes are not allowed. For example, if a table contains a phone number field, the value has a mobile phone number, there is another seat number, which does not match.
Summary: fields cannot be further divided.

Second paradigm: built on the basis of the first paradigm, that is, to satisfy the second paradigm, We must satisfy the first paradigm first, the second paradigm requires that each instance or row in the database table must be uniquely distinguished. Generally, a column must be added to the table to store the unique identifier of each instance.
Summary: it cannot be partially dependent. That is to say, when a table has a composite primary key, other non-primary key fields must be completely dependent on the primary key.

Third paradigm: the third paradigm is to conform to the second paradigm. The third paradigm is that a data table cannot contain non-primary key fields in other data tables, such as a, B, and c fields in a table, if a-> B, B-> c, c and a are dependent on each other, which is inconsistent with each other.
Summary: dependencies cannot be transmitted. In other words, fields other than the primary key must depend entirely on the primary key, rather than other fields.

SQL optimization (in fact, INDEX OPTIMIZATION, because it is impossible for us to modify the data structure, it seems that we can only use indexes to improve the database query efficiency)

1. Create an index: indexes are especially important for most queries. This can effectively avoid full table scans during each query. If the table contains a large amount of data, however, if there are few data that meets the conditions, no index will cause performance degradation.

2. Composite Index
For example, there is a statement like this: select * from users where area = 'beijing' and age = 22; if we create a single index on area and age respectively, because mysql queries can only use one index at a time, the full table scan improves the efficiency even if no index is used, however, creating a composite index on the "area" and "age" columns will increase the efficiency.

3. The index does not contain columns with NULL values,

4. Use a short index to index string columns. If possible, specify a prefix length. For example, if a CHAR (255) Column exists and multiple values are unique within the first 10 or 20 characters, do not index the entire column.

5. Do not perform operations on the column. Performing operations on the column will cause the index to become invalid and perform a full scan.

6. If you do NOT use the not in and <> operations, the index will become invalid and you will perform a full scan.

A transaction is a mechanism and a sequence of operations. It contains a set of database operation commands. These commands are either executed in full or not executed in full.

Four attributes of a transaction:

Atomicity: a transaction is a complete operation.
2. Consistency: when the transaction is completed, the data must be consistent.
3 Isolation: all concurrent transactions that modify data are isolated from each other.
4 Durability: after a transaction is completed, its impact on the system is permanent.

3. Create a transaction
Statement for managing transactions in the T-SQL:
1. Start transaction: begin transaction
2. Submit the transaction: committransaction
3. rollback transaction: rollback transaction

Transaction category:
1. Explicit transaction: Use begin transaction to specify the start of the transaction.
2. Implicit Transaction: Open the implicit transaction: set implicit_transactions on. When you operate in implicit transaction mode, SQL Servler automatically starts a new transaction after committing or rolling back the transaction. The start of the transaction cannot be described. You only need to commit or roll back the transaction.
3 Automatic commit transaction: the default mode of SQLServer, which treats each individual T-SQL statement as a transaction. If the task is successfully executed, the task is automatically submitted. Otherwise, the task is rolled back.

For example, Michael transferred 800 yuan to the Li Si account.
Use stuDB
Go
-Create an account table bank-
If exists (select * from sysobjects where name = 'bank ')
Drop table bank
Create table bank
(
CustomerName char (10),-customer name
CurrentMoney money-Current Balance
)
Go
/*//-Add constraints. The account cannot be less than RMB -*/
Alter table bank add
Constraint CK_currentMoneycheck (currentMoney> = 1)
/*//-Insert test data -*/
Insert into bank (customerName, currentMoney)
Select 'zhang san', 1000 union
Select 'Li si', 1

Select * from bank
Go

/*//-Use transactions -*/
Use stuDB
Go
-Restore original data
-Update bank set currentMoney = currentMoney-1000 where customerName = 'lil'
Set nocount on-No affected rows are displayed
Print 'view the balance before the transfer transaction'
Select * from bank
Go

/*//-Start transaction -*/
Begin transaction
Declare @ errorSum int-defines the variable, used to accumulate errors during transaction execution
/*//-Transfer -*/
Update bank set currentMoney = currentMoney-800 where customerName = 'zhang san'
Set @ errorSum = @ errorSum + @ error-Indicates whether an error exists.
Update bank set currentMoney = currentMoney + 800 where customerName = 'Li si'
Set @ errorSum = @ errorSum + @ error-Indicates whether an error exists.

Print 'view the balance in the transfer transaction'
Select * from bank

/*//-Determine whether the transaction is committed or rolled back based on whether an error exists -*/
If @ errorSum> 0
Begin
Print 'transaction failed, rollback transaction .'
Rollback transaction
End
Else
Begin
Print 'transaction successful, submit transaction, write to hard disk, save permanently! '
Commit transaction
End
Go

Print 'view the balance after transfer'
Select * from bank
Go

Index definition: SQL Server allows you to create indexes in a table and specify pre-sorting by a column, which greatly improves the query speed (similar to searching by pinyin or text in a Chinese Dictionary ).

Index function: index can greatly improve the database retrieval speed and database performance.

Index type:

1: unique index: the unique index does not allow the two rows to have the same index value (in fact, this column of the unique index, each value is unique );

For example, if the student ID number (stuID) column in The stuInfo table creates a unique index, the ID number of all students cannot be the same.

2: Primary Key Index: when defining a table's primary key, the primary key index is automatically created (in fact, the primary key index is a special case of the unique index). The primary key index requires that each value be unique and not empty.

Primary key indexes are special types of unique indexes. The primary key index requires that each value in the primary key be unique. When a primary key index is used in a query, it also allows quick access to data.

3: clustered index: the physical order of each row in the table is the same as that of the key value. Each table can only have one.

In a clustered index, the physical order of each row in the table is the same as that of the key-value Logic (index. A table can contain only one clustered index. For example, by default, the Chinese character (Word) is sorted by pinyin to orchestrate the page number of each page in the dictionary. Pinyin letters a, B, c, d ...... X, y, and z are the logical order of the index, and the page numbers are 1, 2, 3 ...... Physical order. By default, the index order and logical order of a dictionary are the same. That is, the page numbers of words (words) with a higher pinyin order are also large. For example, the page number of the word (Word) corresponding to the pinyin "ha" is later than that of the word (Word) corresponding to the pinyin "ba.

4: Non-clustered index specifies the logical sequence of the table. The data is stored in one location and the index is stored in another location. The index contains a pointer to the data storage location.

If it is not a clustered index, the physical sequence of each row in the table does not match the logical sequence of the key value. Clustered indexes provide faster data access speeds than non-clustered indexes. For example, an index sorted by strokes is a non-clustered index. The number of pages corresponding to the words (words) in the "1" painting may be different from the words (words) in the "3" painting) the corresponding page number is large (back ).

Tip: in SQL Server, only one clustered index and multiple non-clustered indexes can be created for a table. If a column is set as the primary key, the column is a clustered index by default.

Advantages and disadvantages of indexes:

Advantage: faster access;

Strengthen the uniqueness of rows

Disadvantages: the storage of indexed tables in the database requires more space;

Principles for creating indexes:

Indexes can be used in the following cases:

This column is frequently used for search;

This column is used to sort data;

Avoid using indexes in the following situations:

The column only contains several different values;

The table contains only a few rows. Creating an index for a small table may be less cost-effective because SQLServer takes longer time to search for data in the index than to search for data row by row in the table.

Differences between hashtable and hashset:

The two are inherited from different interfaces. The two are completely different.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.