SQL Basic Series (2)-built-in functions--reprint W3school

1. Date function

Mssql:

SELECT GETDATE ()

Returns the current date and time

SELECT DATEPART (yyyy,orderdate) as OrderYear,

DATEPART (mm,orderdate) as OrderMonth,

DATEPART (dd,orderdate) as Orderday

From Orders1

WHERE orderid=1

Returns a separate part of the date/time

SELECT Orderid,orderdate,dateadd (day,2,orderdate) as Orderpaydate

From Orders1

Add or subtract a specified time interval in a date

SELECT DATEDIFF (Day, ' 2016-06-08 ', ' 2016-06-08 ') as Diffdate

Returns a time between two dates

The CONVERT () function is a general function that converts a date to a new data type.

The CONVERT () function can display date/time data in different formats.

SELECT CONVERT (VARCHAR), GETDATE ())

SELECT CONVERT (VARCHAR), GETDATE (), 110)

SELECT CONVERT (VARCHAR (one), GETDATE (), 106)

SELECT CONVERT (VARCHAR), GETDATE (), 113)

Function	Describe
GETDATE ()	Returns the current date and time
DATEPART ()	Returns a separate part of the date/time
DATEADD ()	Add or subtract a specified time interval in a date
DATEDIFF ()	Returns a time between two dates
CONVERT ()	Display Date/time in a different format

Mysql

Function	Describe
Now ()	Returns the current date and time
Curdate ()	Returns the current date
Curtime ()	Returns the current time
DATE ()	Extract date part of date or date/time expression
EXTRACT ()	Returns a separate part of the date/time Press
Date_add ()	Add a specified time interval to a date
Date_sub ()	Subtract a specified time interval from a date
DATEDIFF ()	Returns the number of days between two dates
Date_format ()	Display Date/time in a different format

2. ISNULL Ifnull

Mssql

SELECT productname,unitprice* (Unitsinstock+isnull (unitsonorder,0))

From Products

Mysql

SELECT productname,unitprice* (Unitsinstock+ifnull (unitsonorder,0))

From Products

SELECT productname,unitprice* (Unitsinstock+coalesce (unitsonorder,0))

From Products

3. Functions

The AVG function returns the average of a numeric column. NULL values are not included in the calculation.

SELECT AVG (Orderprice) as Orderaverage from Orders

When used in the where

SELECT Customer from Orders

WHERE orderprice> (SELECT AVG (Orderprice) from Orders)

Count

SELECT COUNT (column_name) from table_name

The count (column_name) function returns the number of values for the specified column (NULL does not count in)

SELECT COUNT (*) from table_name

COUNT (*) function returns the number of records in a table

SELECT First (column_name) from table_name

SELECT last (column_name) from table_name

The Max function returns the maximum value in a column. NULL values are not included in the calculation.

SELECT MAX (column_name) from table_name

The Min function returns the minimum value in a column. NULL values are not included in the calculation

SELECT MIN (column_name) from table_name

Sum

SELECT SUM (column_name) from table_name

Group

SELECT column_name, aggregate_function (column_name)

From table_name

WHERE column_name operator Value

GROUP by column_name

Having

SELECT column_name, aggregate_function (column_name)

From table_name

WHERE column_name operator Value

GROUP by column_name

Having aggregate_function (column_name) operator value

The where cannot be directly connected to the aggregate function, plus having

SELECT Customer,sum (orderprice) from Orders

WHERE customer= ' Bush ' OR customer= ' Adams '

GROUP by Customer

Having SUM (Orderprice) >1500

The UCASE function converts the value of a field to uppercase

SELECT UCASE (column_name) from table_name

The LCASE function converts the value of a field to lowercase

SELECT LCASE (column_name) from table_name

The MID function is used to extract characters from a text field

SELECT MID (Column_name,start[,length]) from table_name

The LEN function returns the length of a value in a text field

SELECT LEN (city) as lengthofcity from Persons

The ROUND function is used to round a numeric field to a specified number of decimal digits

SELECT ROUND (column_name,decimals) from table_name

The NOW function returns the current date and time

SELECT ProductName, UnitPrice, now () as Perdate from products

The Format function is used for formatting the display of a field

SELECT FORMAT (Column_name,format) from table_name

SQL Basic Series (2)-built-in functions--reprint W3school