Distance horse is not snow, for the dark incense come, in this cold and warm season we learned "fuzzy Query and Aggregation function"

Every year the snow, often inserted plum blossom drunk. Creases to the plum blossom without kindness, win full clothes clear tears.
This year ends, the rustling of the temples of Sheng Hua. Look at the wind in the evening, it should be ugly plum


In this chapter we mainly study:

1. Fuzzy query using like , between, in

2. Using aggregate functions to count and summarize query information

In the fuzzy query, we want to learn the content:

10.1.1 wildcard characters

10.1.2 using like for fuzzy queries

10.1.3 using between to query within a range

10.1.4 using in to query within enumeration values

Wildcard characters

In a nutshell, a wildcard is a class of characters that can replace one or more real characters and appear as alternate characters when looking for information. Wildcard characters in T-SQL must

Use with the LIKE keyword to complete special constraints or requirements.

                                                                        Wildcard table

with   characters	Solutions     release	Sample        Example
-	A character	A like ' c_ ', then eligible a such as CS, CD, etc.
%	string of any length	b like ' co% ', then qualifying b such as CONST, COKE, etc.
[]	One character in the range specified in parentheses	C like ' 9w0[1-2] ', then eligible C such as 9W01 or 9w02
[^]	Not within the range specified in the parentheses Meaning a character	d like ' 9w0[^1-2] ', then qualifying d such as 9w03 or 9w07, etc.

Use like for fuzzy queries

The LIKE operator is used to match a string or part of a string, because the operator is used only for strings, and is only used in conjunction with character data types such as char or varchar. You can still use the LIKE keyword for matching lookups when updating, deleting, or querying data:

* Student Address  like ' Beijing '

Use between to query within a range

Using the keyword between to find a set of unknown values between two known values, to implement such a lookup, you must know the initial and final values of the lookup, and the initial value is less than the final values, and the initial and final values are separated by the and keyword. As follows:

--Find students in classbetween 1-3 Select from Studentwhere13

Use in to query within enumeration values

The value of the query is one of the specified values and can be queried using the In keyword with an enumeration value. Place the enumeration values in parentheses and separate them with commas. Gca

-- The query address is a student of Beijing, Shanghai, and Xiamen select from studentwherein (' Beijing ',' Shanghai ',' xiamen ')

In learning to use aggregate functions to count and summarize query information, we have learned about four kinds of functions:

10.2.1 SUM () function

10.2.2 AVG () function

10.2.3 MAX () function and MIN () function

10.2.4 COUNT () function

SUM () function

The SUM () function can only be used for columns of numeric types and cannot summarize other data types such as characters, dates, and so on. Gca

-- Query the total grade of the class select as from result

AVG () function

The AVG () function can also be used only for columns of numeric types. For example:

-- Query the average of the class for the  average score from result of the class students 

MAX () function and MIN () function

The max () function is the maximum value in the return value expression, and the Min () function is the smallest value in the return value expression, and both functions ignore any null values, and they can all be used for numeric, string, and date/time type columns. For character sequences, the max () function looks for the maximum value of the sort sequence. The min () function, in the same vein, returns the minimum value of the sort sequence. For example:

- class average, highest and lowest select  as as highest score, min (studentresult) as Min. from  Result

COUNT () function

The count () function returns the supplied group or Recordset count, and the count () function can be used to drop columns of any type other than text, image, ntext. Alternatively, you can use an asterisk (*) as an expression for count, and you can use the asterisk to calculate all the rows without specifying a particular column, including empty-worthy rows when counting all the rows. For example:

-- total number of records select as Total records  from result

Well, the talk of all speak a bit, if there is nothing to understand or do not understand can contact me Oh! (Contact: QQ 1759265946) If I think this article is still OK, then please mercy, order a praise!

Distance horse is not snow, for the dark incense come, in this cold and warm season we learned "fuzzy Query and Aggregation function"