SQL interview to learn new content

1. The point of preservation of things

　　MySQL allows us to partially rollback a transaction by invoking the SavePoint statement in a transaction to set some named tokens. If you want to roll back to that Mark Point location, you need to use the ROLLBACK statement to specify which save point.

Mysql>TRUNCATE TABLE D; Query OK,0Rows Affected (0.02sec) MySQL>start transaction; Query OK,0Rows Affected (0.00sec) MySQL> INSERT into D values (1); Query OK,1Row affected (0.01sec) MySQL>savepoint My_savepoint; Query OK,0Rows Affected (0.00sec) MySQL> INSERT into D values (2); Query OK,1Row affected (0.00sec) MySQL>rollback to SavePoint my_savepoint; Query OK,0Rows Affected (0.00sec) MySQL> INSERT into D values (3); Query OK,1Row affected (0.00sec) MySQL>commit; Query OK,0Rows Affected (0.00sec) MySQL>Select* fromD;+------+| Name |+------+|1||3|+------+2Rowsinch Set(0.00Sec

2.SQL Server Sort function

　Row_number:

Displays the position of each record in the group according to the group, for example: if there are two first names, the first names are ranked as 1, 2 in a group, and the next group is still sorted starting from 1, and so on.

RANK:

The rank of each record in the result set, but may not be contiguous, for example: if there are two first names within the same group, the next position in the group jumps directly to the third place.

Dense_rank:

The function is similar to rank, but the number of rankings is continuous, for example: if there are two first names in the same group, the next rank in the group is second.

NTILE:

Partitions the result set according to the specified number of groupings, and records its location in the group. After sorting the result set's data, dividing the result set into n groups according to the specified number, and giving each group a group number, it is easy to group the total number of records of the result set by N, and if there are more than M, the first M group will have a record number, so not all groups have the same record count. However, there is at most one record for a multi-logged group.

3.SQL Server Other functions

Pivot: Row to Column function

Over: Open Window function

Partition by: Analytic functions

Reference articles

1. use savepoints in SQL Server transactions to save the submission point

2.Introduction to SQL four ranking functions (Row_number, Rank, Dense_rank, NTILE)

3.SQL Server 2012 Open Window function

Use of partition keywords in 4.SQL

5. Row to column: SQL SERVER pivot and usage explanation

SQL interview to learn new content