Five functions, subqueries, and paging queries

aggregate functions : mainly action columns

AVG Score: AVG ()
Format: SELECT AVG (column name) as ' custom column name ' from table name--Calculates the average of a column separately

Maximum value: Max ()
Format: SELECT MAX (column name) as ' custom column name ' from table name

Minimum value: Min ()
Format: select min (column name) as ' custom column name ' from table name

Sum: Sum ()
Format: Select sum (column name) as ' custom column name ' from table name

Number of data bars: count (*)
Format: SELECT COUNT (*) from table name

Use: Ask how many of each series, and what the average price they are
Format: Select column name, COUNT (*) as ' custom column name ', AVG ' custom column name ' from table name Group BY column name--column name is one, how many per series

-------------------------------------------------------------
Mathematical Functions :

Upper limit: Ceiling ()
Format: Select ceiling (column name) from table name--After the decimal point is greater than 0, enter a

Lower limit: Floor ()
Format: Select floor (column name) from

Absolute value: ABS ()
Format: Select ABS ( -5)--select map The results to the result box, ABS turns negative values into positive values
PRINT-5--Map the results to a message box

Pi ()

Rounding: ROUND ()
Ps:select Roung (Pi (), 2)--2 retain several decimal places. PI brackets do not need to put things, where is pi

Open root: SQRT ()
Format: Select sqrt (value to open root)

Square Root: Square ()
Format: Select square (value to open square)

-------------------------------------------------------------
Conversion Functions :

Cast (column as type) Ps:select ' fuel consumption ' +cast (oil as nvarchar (a)) from car

Convert (Type, column) ps:select column name +convert (nvarchar (), oil) from car

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

Convert uppercase: Upper ()
Select Upper () from

Convert lowercase: lower ()

Go to Space: trim ()--the middle of the space to not fall
Select LTrim ()--Remove the left space
Select RTrim ()--Remove the right space

Left-hand intercept: Ieft (value, length)
Select Left (' ABCDE ', 3)--intercept result ABC
Right intercept: (value, Length)
Select Right (' ABCDE ', 3)--interception result CDE
String truncation (value, index, length)
Select substring (' ABCDE ', 2,2)--intercept result BC, index starting from 1

Length: Len ()
Select Len (' ABCDE ')--Results 5

Replace with: replace (value, content, replace content)
Select replace (' aaaabbaaaa ', ' BB ', ' hehe ')--results AAAAHEHEAAAA

Flip: Reverse ()
Select reverse (' ABCDE ')--EDCBA

String conversions: str (value, reserved digits, number of decimal digits)
Select STR (3.14,2,1)-Result 3. 2 altogether two digits, the decimal point counts as one
-------------------------------------------------------------
Time-Date function:

Get current time: GetDate ()
Select GETDATE ()--Get the current time

Acquired date: Year () month () Day ()
Select year (column name), month (column name), Day (column name) from table name

Determine if the date is correct: IsDate ()
Select IsDate (datetime type)--bool type, result 1 or 0

Add Time: DateAdd (add type, quantity, value)
Ps:select DateAdd (Day,2,getdate ())

Return weeks: Datename (weekday, value)
Select Datename (Weekday,getdate ())
Combined use: Select Datename (Weekday,dateadd (Day,2,getdate ()))

Return: Datename (day, value): Select Datename (Day,getdate ())--return the day ordinal of the month
Datename (DayOfYear, value): Select Datename (Dayofyear,getdate ()) returns the day ordinal of the year

-------------------------------------------------------------
Sub-query:
Use the SQL statement as a value:
Select the child column name to query from the child table name where to query for the child primary key Column name = (Select the parent field name to query from the parent field name of the table where the parent primary key column = corresponding value)

In
Select *from table name where column name in (value, value)--when the value of that column is the value of which is the row
Not in:
Select *from table name where column name not in (value, value)--when the value of that column is not inside the value
Any
Select *from table name where column name >any (select column name from table name where column name in (value, value))--any any one, greater than any one of the values in the parentheses matches the condition
All
Select *from table name where column name >all (select column name from table name where column name in (value, value))--all all values. is larger than all values to meet the criteria

Ps:select *from Car where powers >any (select Powers (HP) from table name where oil (fuel consumption) in (value, value))--calculate horsepower to be greater than fuel consumption is these cars

Note: When a subquery, a statement as a parameter, the queried data can be multiple rows, but must be a column

Inquire about the price of vehicles with a higher price than any of the BMW cars
Select *from Car where price > any (select prices from car where name like '% BMW% '--BMW's full price)
Or: Select *from car where price > (select min (prices) from car where name like '% BMW% '--BMW's lowest rate)

Check prices for vehicles with higher prices than BMW's All cars information
Select *from Car where price > All (select Prices from car where name like '% BMW% '--BMW's full price)
Or: Select *from car where price > (select MAX (prices) from car where name like '% BMW% '--BMW's highest rate)

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 5 (first 5) *from car where IDs not in (select top pages multiplied by 5 IDs from car)

Five functions, subqueries, and paging queries