SQL Server's T-SQL Basic statement (2)

Then continue to use the above example to summarize the knowledge points.

Filtering with wildcard characters

Like operator //used to select data that is similar or partial to the condition

Select Name

From person

Where name like ' Chen ';

Result: Chen

Percent percent (%) wildcard character //percent symbol indicates any number of occurrences of any character (which can be a string).

Select Name

From person

Where name like '%en ';

Result: Chen

Note: '%en ' denotes any data ending in en, ' ch% ' represents any data that begins with CH, '%he% ' means data that matches any location containing he, and ' c%n ' represents data that begins with C and ends with N.

Note: With wildcard%, case sensitive, for example, '%en ' is not the same as '%en ' matching data.

the underscore (_) wildcard //is the same as the% wildcard effect, except that it matches a single character.

such as where name like ' _hen ';

square brackets [] match //used to specify a character set, which must match a character at the specified position.

For example, find the data whose name begins with C and L

The where name like ' [cl]% ';//[cl] means matching any name that begins with the letter in square brackets.

Stitching Fields

General use + and | | to make stitching;

Like what:

Select name + ' (' +age+ ') '

From person

The result: Chen (24)

Li (24)

Zhang (23)

Same: select name | | ' (' | | age| | ') '

As keyword: using aliases

Select name + ' (' +age+ ') ' as nameage///actually nameage does not exist, he is just a value. Used instead of the result substitution of Chen (24).

Perform arithmetic calculations

Operator	Description
+	Add
-	Reducing
*	By
/	Except

Like what:

Select 2*3; will return 6

Similarly: Select Trim (' abc '); return ABC; Select now (); Returns the current date and time.

Data processing Functions

Text-handling functions:

Function	Description
Left ()	Returns the character to the left of a string
LENGTH ()	return string length
LOWER ()	Convert a string to lowercase
LTRIM ()	Remove the space to the left of the string
Right ()	Returns the character to the right of a string
RTRIM ()	Remove the space to the right of the string
UPPER ()	Convert a string to uppercase

For example: Select Name,upper (name) as Upname

...

Result: Chen Chen

Date and time processing functions

The DATEPART () function has two parameters, one is the returned component, and the date from which the component is returned.

For example: Retrieving all orders for 2012 years

Select Order_num

From Orders

where DATEPART (yy,order_date) =2012;//returns the year from the Order_date column. Filter out this year

Numeric processing functions

Function	Description
ABS ()	Returns the absolute value of a number
COS ()	Returns the cosine of an angle
EXP ()	Returns the exponential value of a number
PI ()	return pi
SIN ()	Returns the sine of an angle
SQRT ()	Return a square root
TAN ()	Returns an angle sine

Usage is the same as a text-handling function.

Aggregation functions

Function	Description
AVG ()	Returns the average of a column
COUNT ()	Returns the number of rows in a column
MAX ()	Returns the maximum value of a column
MIN ()	Returns the minimum value of a column
SUM ()	Returns the sum of a column value

For example: select Max (age)

from person;

Results: 24

————————

Select COUNT (*)

from person;

Results: 3

SQL Server's T-SQL Basic statement (2)