Database Series Learning (v)-Retrieval of data

1. Execute SQL script for this study

--Create student TablesCreate TableT_student (--identity indicates that the primary key is growing from 1, and 1 is added each timeSidint Primary Key Identity(1,1), SNamenvarchar(Ten), Sgendervarchar(2)default('male'), SAgeint)--Inserting DataInsert  intoT_studentValues(' Harry','female', -)Insert  intoT_studentValues('Harry','male', +)Insert  intoT_studentValues('Zhao Liu','male', -)Insert  intoT_studentValues('Kim','male', -)Insert  intoT_studentValues('Lily','female', -)Insert  intoT_studentValues('Jerry','female', -)

2. Group BY for Data grouping

(1) Simple GROUP BY

Let's look at an example of what group by does for us.

(2) Group BY with a WHERE clause

Note If there is a WHERE clause in SQL, the group by must be placed after the where statement

(3) Group BY specifies multiple columns

(4) Data grouping and aggregation functions

(5) Filtering the grouped results having a statement

3. Restricting result set functions

(1) Using Top filter

(2) Using the open Window function: Row_number () over (collation)

3. Suppress duplication of data

4. Calculated fields

(1) Constant field

(2) Calculation between fields

5. Data processing functions

(1) Concatenation of strings

6. Joint queries

(1) Union: Guarantee the uniqueness of the data after union

(2) UNION ALL: retain all data after union

Database Series Learning (v)-Retrieval of data