Summary of common face questions in database

Note: This article is only for learning Exchange, reprint please indicate the source, welcome reprint !

Summary of common face questions in database

1. What are the table connection methods for SQL?

Connections in SQL are divided by result set: Inner connection, outer join, cross Connect

Inner connection : Inner join on, both tables satisfy the combination. The inner connection is divided into equivalent connection , unequal connection , natural connection .

equivalent joins : The same columns in both tables appear in the result set.

Natural joins : columns of the exact same list in both tables are merged into the same listed now result set.

Outer connection : Divided into left (outer) connection , right (outside) connection , full connection

Left (outer) join: a outer join B, based on table A, all data of table A, the combination of B table, not null.

Right (outside) connection: a outer join B, based on table B, all data of table B, the combination of a table, no bit null.

Fully connected: a full (outer) Join two tables are the same combination, a table has, B table does not have data (shown as null), the same B table has, a table does not display null.

Cross joins: the product of Descartes.

2. three paradigms

1NF: The fields in the table are single attributes and no longer can be divided.

2NF: On a 1NF basis, all non-primary properties in a table must be completely dependent on any set of candidate keys and cannot rely on only one of the attributes in the candidate key.

3NF: On a 2NF basis, all properties in the table do not depend on other non-primary properties.

Simply put : 1NF means that each attribute is indivisible, 2NF means that the non-primary attribute does not have a partial dependency on the primary key, and 3NF indicates that there is no dependency passing on the primary key for the non-primary attribute.

3. operation of the table

table creation : CREATE TABLE table name (column name 1 type constraint, column 2 type constraint ...)

deletion of Tables : drop table Table name

table Changes (structural changes, not record updates): ALTER TABLE table name add|drop column name | constraint name

Insert record : INSERT into table name ... values ...

Update record : The Update table name set column name = value WHERE condition

Delete Record : Delete from table name where condition

4. completeness of data

Data integrity refers to the consistency and accuracy of the data stored in the database.

Integrity Classification :

(1) Entity integrity: The primary key value must be unique and not NULL. (PRIMARY KEY constraint)

(2) Referential integrity (also called referential integrity): The foreign key is either empty or references a record that exists in the primary table. (Foreign KEY constraint).

(3) User-defined integrity: For constraints in a particular relational database.

5. query optimization for SQL

(1) from the angle of the table connection optimization : As far as possible to use the inner connection, because the internal connection is the two tables are satisfied with the combination of rows, the outer connection is the whole of one of the tables as a benchmark.

(2) try to use a stored procedure instead of a temporary write SQL statement : Because the stored procedure is a collection of precompiled SQL statements, this reduces compilation time.

(3) optimization from the index : For those commonly used query field resume index, so that the query value index Scan, do not read the data block.

(4) There are some common select optimization techniques:

(5) A. Query only those fields that need access, instead of select*

B Move the where statement that filters more records forward: In an SQL statement, if a Where condition filters more database records, the more accurate the location, the more the Where condition should be moved forward.

6. the role of the index, the difference between a clustered index and a nonclustered index

An index is a database object that uses an index, which can be used to find the target data in a database program without having to scan the entire data to improve the lookup efficiency. The bottom of the index is a B-tree.

Clustered index: Sorts rows of data in a table based on the key of the record.

Nonclustered indexes: Separate from the record structure, nonclustered so contains the key, and each key-value entry has a pointer to the simply data row.

The difference between a clustered index and a nonclustered index:

(1) The physical storage of a clustered index is sorted by index, and non-clustered physical storage is not sorted by index.

(2) When the clustered index is inserted, the data is updated more slowly than the nonclustered index, and the single query speed is faster.

(3) The leaf node of the clustered index holds the data item of the time, while the leaf node of the non-clustered node holds a pointer to the data item.

(4) A table can have only one clustered index (because there is only one sort method), but there may be multiple nonclustered indexes.

7. The difference between a stored procedure and a function

(1) The function has a return value, and the stored procedure has no return value.

(2) because the stored procedure does not return a value, the execution result of the stored procedure cannot be assigned to the variable, the function has a return value type, and when the function is called, the execution result of the function can be assigned to the variable. In other words, a function can be used in a SELECT statement, while a stored procedure cannot.

Summary of common face questions in database