Five kinds of functions, sub-queries and paged query ideas

Aggregation functions:
"Plus the column name is easy to differentiate when you put multiple together!" 】

AVG Score: AVG ()
Write format: Select AVG (field name) as ' function Number field name ' from table name

Maximum value: Max ()
Write format: Select max (field name) as ' function Number field name ' from table name

Minimum value: Min ()
Write format: select min (field name) as ' function Number field name ' from table name

Sum: Sum ()
Write format: Select sum (field name) as ' function Number field name ' from table name

Number of data bars: count (*)
Write format: Select field name, COUNT (*) from table name

Use: Ask how many of each series, and what the average price they are

Ps:
-------------------------------------------------------------
Mathematical functions:

Upper limit: Ceiling ()
Write format: Select ceiling (field name) from table name

Lower limit: Floor ()
Write format: Select floor (field name) from table name

Absolute value: ABS ()
Write format: Select ABS (Value)

Pi ()
Writing format:

Rounding: ROUND ()
Write format: Select round (the value to be rounded, the number of digits to save)

Open root: SQRT ()
Write format: Select sqrt (value to open root)

Square Root: Square ()
Write format: Select square (value to open square)

-------------------------------------------------------------
Conversion functions:

Cast (column as type)
Write format: The value of the select ' String type ' + CONVERT (field name as) from the data type table name to be converted to

Convert (type, column)
Write format: The value of the select ' String type ' + CONVERT (the data type to convert to, field name) from table name

-------------------------------------------------------------
String functions:

Convert uppercase: Upper ()
Write format: Select upper (Value or field name)

Convert lowercase: lower ()
Write format: Select lower (value or field name)

Go space: Trim ()
Writing format: Select LTrim (To go to the contents of the space)--go left space
Select RTrim (To go to the contents of the space)--go to the right space

Left-hand intercept: Ieft (value, length)
Write format: Select Left (what to intercept, length to intercept)
Select Right (what to intercept, the length to intercept)

Length: Len ()
Writing format:

Replace with: replace (value, content, replace content)
Writing format: Select Repiace (that is, all the original content that will be replaced, the original content to be replaced, the new content to be replaced)

Flip: Reverse ()
Writing format: Select reverse (what to flip)

String conversions: str (value, reserved digits, number of decimal digits)
Write format: Select str (number, number of digits reserved, number of decimal digits to keep)
"The decimal place also occupies one!" 】

String intercept: substring (value, index, number of bits)
Writing format: Select substring (the content to be intercepted, the starting index, the length of the Intercept)
Note: The index of the string intercept starts at 1! 】
Ps:
-------------------------------------------------------------
Time-Date function:

Get current time: GetDate ()
Writing format: Select GETDATE ()

Acquired date: Year () month () Day ()
Write format: Select year (field name), Month (field name) from table name

Determine if the date is correct: IsDate ()
Write format: Select IsDate (Value of datetime type)

Add Time: DateAdd (add type, quantity, value)
Writing format: Select DATEADD (add type, quantity, cardinality data to be added)

Return weeks: Datename (weekday, value)
Write format: Select Datename (Value of weekday,datetime data type)

Returns the day of the Month: Datename (days, value)
Write format: Select Datename (Value of day,datetime data type)

Returns the day of the Year: Datename (dayofyear, value)
Write format: Select Datename (Value of dayofyear,datetime data type)
Ps:
-------------------------------------------------------------
Sub-query:
Any
Select *from field name where the field name >/</>=/<= any
(select field name from table name where the field name In/not in (value, value, 、、、, value))

All
Select *from field name where the field name >/</>=/<= all
(select field name from table name where the field name In/not in (value, value, 、、、, value))

Inch
Select *from field name where the field name in (value, value, 、、、、, value)

Not in
Select *from field name where the field name is not in (value, value, 、、、、, value)

Note In a subquery, when the statement is a parameter, the queried data can be multiple rows, but must be the same column! 】

Inquire about the price of vehicles with a higher price than any of the BMW cars
Select *from Car where price > any
(select price from car where name like '% BMW% ')
or writing:
Select *from car where price > (select min (price) from car where name like '% BMW% ')

Check prices for vehicles with higher prices than BMW's All cars information
Select *from car where price > (select Max (price) from car where name like '% BMW% ')

Boss: higher than BMW's lowest price is not the BMW's those cars
Select *from Car where price>
(select min (price) from car where name like '% BMW% ')
and code not in
(select code from car where name like '% BMW% ')

Paged Query Ideas
Select top constant value *from table name where field name not in (SELECT top Variable formula field name from table name)

Five kinds of functions, sub-queries and paged query ideas