SQL Server Database--"top keyword, order by sort, distinct deduplication, SQL aggregate function, fuzzy query, wildcard, NULL processing ....

Top keyword: write in front of the field after select

For example, you want to display the first 5 records of a query, as follows:

Select Top 5 * from Student

In general, top is the same as the order by

Order by uses the order by after the name of the table to fill in the fields you want to sort by, in ascending order (from small to large)

Percent: Percent%=percent

For example, to display 5% of data processing, cannot write top 5%, but top 5 percent

Tip: If the top 5 percent out of the number is 3.1 data, is to take four data, instead of 3

To remove duplicate records using distinct:

Note: Duplicate records are not related to the original data of the table, it only focuses on the result set after querying through the query statement, if the value of each column of the virtual result set is the same, then the duplicate record, if there is a value different, then a separate record

--sql Aggregation Function--

Count (); The number of records that satisfy the condition, regardless of the value

Note: The parameter is arbitrarily passed in the count () bracket because it is independent of the value and is only related to the number of records, typically passing in a 0 or * number. such as COUNT (*)

Max (); In the maximum bracket, the field name passed in as Max (age)

Min (); Find minimum value

sum (); The name of the field to pass in the sum bracket

AVG (); The name of the field in the average bracket that is passed in the required mean

Note: Sum/avg cannot be calculated for dates and names

--Fuzzy query--

Note: Select field List from table list where condition order by sort field

All of the following are used as conditions, in the back of where

1.between...and equivalent to >=n,<=m priority to use Between...and, because of the priority, high efficiency

It is used to describe numeric or date values and is useless for strings

2.in equivalent to And,and,and can actually be in (three) to choose one

3.not in (no) within this range

The above Between...and,in,not in is used after the field behind the where

Wildcard characters:

1% represents any arbitrary character

2 _: It represents a placeholder for a character, equivalent to. Represents a specific character

3 []: equivalent to specifying a specific range or a specific range of values. This usage is the same as the regular expression

4 [^]: take Reverse

Like: A. Same: If you want to use wildcard characters, you must mate with the fuzzy query keyword

Null handling:null is not known, and is not the same as in C # without assigning an address so it is not possible to use an equals sign (=) When judging whether it is a null value, but is to use is, for example: SELECT * from Student where name is null

Determine if the Name field in the student table is null

ISNULL () method : Used after the Select field, in order to determine whether the query results (this field) is NULL, if the null value is replaced with the specified content as follows:

Select *,isnull (name, ' No Name written ') from Student

Data sort: ORDER by

Sort By default is ascending sort

Ascending: ASC

Descending: Desc

If the sort has more than one field, the first field is sorted first, the same record is sorted by the second field

Example: SELECT * from Student ORDER BY sex,name Desc

Group Statistics : First group and then statistics

GROUP BY: Categorize the specified fields with the back of the table and then group by followed by the fields to be grouped

Such as:

-The total number of girls and boys received

Select Sex,count (*) from Student GROUP by sex

Order of Query statements:

Select from where the group by has an order by

Note: Where is the filter for the source data. It can only use columns that are referred to in the table following the from

An aggregate function cannot be used after a where condition, and an error will be made if used

Having

If you are filtering the result set after grouping, then you need to have a having, because the where condition cannot let go of the aggregate function

Such as:

Select Classid,count (*) from Student GROUP by CLASSID have Count (*) >2

Classification of SQL

1, DDL (data definition language, build table, build library and other languages)

2.DML (Data Manipulation language mutipulation) Add delete modify and query also called cuid: Database additions and deletions 3.DCL (Database control Language)

Type conversions

1.--cast (source data as target type)--

2.--convert (destination type, source data, format (date value format))

Select GETDATE ()--Get current system time select GETDATE ()

Union result set Union (rarely used)

Union All: Shows whether the result is repeated or not

Union: Duplicate data is not displayed

Union's restrictions on merging multiple result sets:

1. To have the same number of columns

2. The corresponding column type is consistent, the type can be cast () cast or convert

SQL Server Database--"top keyword, order by sort, distinct deduplication, SQL aggregate function, fuzzy query, wildcard, NULL processing ....