s1/c# Language and Database Technology Foundation/10-fuzzy Query and aggregation function

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 be used with the LIKE keyword to accomplish special constraints or requirements.

Wildcard characters

Wildcard characters	Explain	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
[^]	Any character that is not in the range specified in parentheses	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, it is only used in conjunction with character data types such as char or varchar.

When updating, deleting, or querying data, you can still use the LIKE keyword for matching lookups, for example, to find student information for the surname Zhang:

SELECT * from Students WHERE Sname like ' sheet% '

Or inquire about the student's information in the address containing the words "Beijing":

SELECT * from Students WHERE saddress like '% Beijing '

Use between to query within a range

Use the keyword between to find a set of unknown values between two known values. To achieve this, you must know the initial and final values of the lookup, and the initial and final values are separated by the and keyword. For example, the information for a query score between 60 (inclusive) and 80 (inclusive) is as follows:

SELECT * FROM score WHERE score between and 80

If the following form is written:

SELECT * FROM score WHERE score between and 60

Tip: Use not to perform a "reverse" operation on a restriction condition.

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. For example, check the names of students in Beijing, Guangzhou or Shanghai:

SELECT Sname as student name from Students WHERE saddress

In (' Beijing ', ' Guangdong ', ' Shanghai ') ORDER by saddress

You can also use the In keyword together with the NOT keyword to get all rows that do not match the enumerated values.

Tip: The enumeration value type must have the same data type as the matching column.

Aggregate functions in T-SQL

Another type of intrinsic function of T-SQL--"aggregate function". Aggregation functions can be evaluated based on a column, merging multiple values into a single value, which evaluates a set of values and returns the computed value.

1. SUM () function

The sum () function returns the sum of all the values in the expression, and the null value is ignored. 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. For example, to query the total score for a student number 23, you can use the following query.

SELECT SUM (Score) as number 23 students total from score WHERE StudentID = 23

Note: This query returns only one numeric value, so it cannot be queried directly with columns that might return multiple rows, for example:

SELECT SUM (score) as student score 23, CourseID as account number from score WHERE StudentID = 23

An error message is reported. However, you can use multiple aggregate functions at the same time in a single query.

2. AVG () function

The AVG () function returns the average of all the values in the expression, and the null value is ignored. The AVG () function can also be used only for columns of numeric types.

3, MAX () function and min () function

The max () function returns the maximum value in an expression, and the Min () function returns the minimum value in the expression, which also ignores any null values, and they can be used for numeric, character, 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, the query for the average score, highest score, and lowest score is as follows.

SELECT avg (score) as average score, MAX (score) as highest score, Min (score) as lowest score from score WHERE score >= 60

4. COUNT () function

The count () function returns the count in the provided group or Recordset. The COUNT () function can be used to remove 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, and to include rows with null values when all rows are counted.

For example, the statement that queries the total number of records is as follows.

SELECT COUNT (*) as Total records from score

For example, the statement that queries the total number of single-column (score) records is as follows.

SELECT count (score) as fraction record from score

s1/c# Language and Database Technology Foundation/10-fuzzy Query and aggregation function