Database site review

1. Database Theory

1nf: The first paradigm. If the value ranges of all properties of the relational mode R are non-decomposed values, r belongs to the first paradigm.

The pattern of the first paradigm requires that attribute values cannot be split into a smaller part, that is, attribute items cannot be combined with attribute items or composed of group attributes.

2nf: Second paradigm. If the relational mode r is the first paradigm, and every non-primary attribute function in R depends on a candidate key of R, R is called the second paradigm.

If each relational mode in a database mode is of the second paradigm, the database mode is called the second paradigm.

3nf: The third paradigm. If the relational mode r is the second paradigm, no candidate keys dependent on R are transmitted for each non-primary attribute, then R is the third paradigm. If each relational mode in a database mode

The 3nf database model is the third paradigm.

Bcnf: BC paradigm. If the relational mode r is the first paradigm and each attribute does not pass a candidate key dependent on R, R is called the bcnf mode.

4nf: fourth paradigm. Set R to a relational mode, and D to a set of multi-value dependencies on R. If a non-trivial multi-value dependency X is established in D, "-" Y x must be the super key of R, and R is called the fourth paradigm.

Relationship R (S, D, m)

2 what is the difference between stored procedures and functions?

A stored procedure is a collection of user-defined SQL statements that involve tasks of a specific table or other objects. You can call the stored procedure.

A function is usually a database-defined method. It accepts a function and returns a value of some type, and does not involve a specific user table.

3. What is a database transaction?

Database transactions refer to a series of operations performed by multiple logical units of work. These operations are either full or not done, and are an inseparable wage unit.

The start and end of a transaction can be controlled by the user display. If transactions are not explicitly defined, DBMS automatically divides transactions according to the default rules.

Transactions are characterized by atomicity, consistency, independence, and durability.

4. What is the role of a cursor? How can I know that the cursor has reached the end?

A cursor is used to locate rows in a structure set. You can determine whether the global variable @ fetch_status is the last. Generally, if this variable is not equal to 0, it indicates an error or an error is reached.

5. Trigger is divided into trigger beforehand and trigger afterwards. What is the difference between the two triggers? What is the difference between statement-level triggering and row-level triggering?

Beforehand --- run before the trigger event occurs

Post -- run after the trigger event

Statement-level -- execution before or after statement execution

Row-level trigger -- trigger each affected row

6. Differences between clustered indexes and non-clustered Indexes

Clustered indexes are interpreted as the physical storage order of data, while non-clustered indexes are interpreted as unrelated to the physical sorting order of data. Because of this, one table can have at most one

Clustered index.

In SQL Server, the index is described by the data structure of the binary tree. We can understand the clustered index as follows: the leaf node of the index is the data node. Leaf nodes without clustered indexes are still index nodes,

There is a pointer pointing to the corresponding data block.

The clustered index determines the physical sequence of data in the table. The clustered index is similar to a telephone book. The latter lists data by name. Because the clustered index specifies the physical storage order of data in the table, a table can only contain one clustered index,

However, this index can contain multiple columns, just as the phone book is organized by the last name and name.

Non-clustered indexes are used. Non-clustered indexes are similar to those in textbooks. The data is stored in one place, and the index is stored in another place. The index has a pointer to the data storage location. Index-based projects

Key-value clustered index, these rows cannot be guaranteed to have any specific order

Aggregation function or build-in Function)

Function Name	Function
Count	Count tuples
Total	Sum
Max	Maximum Value
Min	Minimum value
AVG	Average Value

The relational model can be formally expressed: R ( U , D , Dom , F ) RLink name USet of attribute names that constitute the link DAttribute Group UThe domain from which the attribute is located Set of Dom attributes to the domain FSet of data dependencies between attributes

Three types of integrity constraints Entity integrity Integrity of reference User-Defined integrity

Entity integrity and referential integrity: Integrity constraints that must be met by the relational model Two Links Immutability, Should be automatically supported by the relational system User-Defined integrity: The constraints that need to be observed in the application field reflect the semantic constraints in the specific field

Set FIs the basic relationship RBut not a link. R. If F has a basic relationship SCorresponding to the primary code KS, F is the basic relationship. RExternal code Basic Relationship R nameReferencing Relation) Basic Relationship S nameReferenced Relation) Or target Relation)
Rule 2.2 reference integrity rules If the attribute (or attribute group) FIs the basic relationship RIt has a basic relationship SCorresponding to the primary code KS (Basic Relationship RAnd SIs not necessarily a different relationship ), RIn FThe value on must be: Or take the null value ( FEach attribute value of is null) Or equal SPrimary code value of a tuples in

Decimal (M, d );
M --- indicates the sum of the digits before and after the decimal point, and D indicates the number of digits after the decimal point .......

If D = 0, it is an integer... M.

Identity indicates that this column is an identity column. If you set identity, the value of this column is automatically added when data is inserted according to the initial value and increment.

Numeric () indicates that the field is numeric and the length is two digits after 10 decimal places.

Data Type	Description
Char (N)	A fixed-length string with a length of N
Varchar (N)	A variable-length string with a maximum length of N
Int	Long Integer (integer can also be written)
Smallint	Short integer
Numeric (p, d)	Number of points, which is composed of p-digit numbers (excluding symbols and decimal points), followed by D-digit digits
Real	Depends on the floating point number of the machine's precision
Double Precision	Double-precision floating-point numbers depending on machine precision
Float (N)	Floating Point Number, with a precision of at least N digits
Date	Date, including year, month, day, format: YYYY-MM-DD
Time	Time, including the hour, minute, and second of the day. Format: hh: mm: SS

V statement format Select [All | distinct] <target column expression> [, <Target column expression>]… From <Table name or view Name> [, <Table name or view Name>]… [Where <conditional expression>] [Group by <column name 1> [
Having <conditional expression>] [Order by <column name 2> [ASC | DESC];

Select * From tab where tname like '\ _ %' escape '\';

Tname tabtype clusterid
-----------------------------------------------
_ Test table

Escape converts a wildcard _ to a common character

Predicate:Is null or is not null "is" cannot be replaced by "="

Order by clause Can be sorted by one or more attribute Columns Ascending: ASC; descending: DESC; default value: ascending When the sorting column contains a null value ASC: The last display of tuples whose sorting column is null Desc: The first display of tuples whose sorting column is null

Aggregate functions: Count Count ([distinct | All] *) Count ([distinct | All] <Column Name>) Sum Sum ([distinct | All] <Column Name>) Calculate the average value AVG ([distinct | All] <Column Name>) Maximum and minimum values Max ([distinct | All] <Column Name>) Min ([distinct | All] <Column Name>)