Database and SQL language

Paradigm:

1NF: First paradigm. Relational mode r each value in the domain of all attributes is no longer decomposed.

2NF: Second paradigm. If R is the first paradigm, and every non-primary property in R is dependent on a candidate key of R. (Transitive dependency)

3NF: the third paradigm. If R is the second normal, and each non-primary attribute does not pass a candidate key that relies on R.

BCNF:BC paradigm. If R is the first paradigm, and each attribute does not pass a candidate key that relies on R.

4NF: The fourth paradigm. D is a multivalued dependency set on R, and if a non-trivial multivalued dependent x→→y is established in D, the x must be the super-key of R, then the fourth normal form.

database Transactions : Refers to a series of operations performed as a single logical unit of work.

Transactions have the characteristics of atomicity, consistency, independence, and persistence.

Atomicity: A transaction is either fully executed or not executed.

Consistency: The operation of a transaction does not alter the consistency of the data in the database.

Independence: Transactions do not interleave.

Persistence: After a transaction runs successfully, updates to the system are permanent.

cursor : The row that is used to locate the result set. By judging the global variable @ @FETCH_STATUS can determine whether the last, if not equal to 0 indicates an error or to the end.

External mode/Logic mode image function: Ensure the logical independence of data;

Mode/Internal mode image function: Ensure the physical independence of the data.

SQL language: Structured Query Language, standard language for relational databases.

Query: Select (Query block: Select From where Statement

Definition: Create, DROP, alter

Manipulation: Insert, UPDATE, delete

Control: Grant (Authorization), REVOKE (revoke authorization)

Example: 1, find the table PPP inside NUM Minimum number:

SELECT * FROM PPP where num <=all (select num from PPP)

Select top 1 num from PPP ORDER by num

SELECT * from PPP where num = (select Min (num) from PPP)

2. Copy table (copy structure only, source table A, new table B)

SELECT * to B from A where 1=0

3. Create a temporary table

CREATE Table #Temp (Field 1 type, field 2 type ... ）

4, the symbol used to connect the string is +

5, surname Zhang: Like ' Zhang% ';

"_" means any character;

6, according to the results of descending order, if the same results in ascending order by the school number

Order by grade DESC, XH ASC

7. Total number of enquiries

Select COUNT (*) from student

8, statistical average of more than 80 of students, list number and average scores

Select Xh,avg (Grade)

From SC

GROUP BY XH

Having avg (grade) >=80

Database and SQL language