NUMERIC FUNCTIONS
To get the remainder of a division:
SELECT MOD(13, 2); -- result: 1
To round a number to its nearest integer:
SELECT ROUND(1234.56789); -- result: 1235
To round a number to three decimal places:
SELECT ROUND(1234.56789, 3); -- result: 1234.568
To round a number up:
SELECT CEIL(13.1); -- result: 14
SELECT CEIL(-13.9); -- result: -13
The CEIL(x) function returns the smallest integer not less than x. To round the number down:
SELECT FLOOR(13.8); -- result: 13
SELECT FLOOR(-13.2); -- result: -14
The FLOOR(x) function returns the greatest integer not greater than x. To round towards 0 irrespective of the sign of a number:
SELECT TRUNCATE(13.56, 0); -- result: 13 SELECT TRUNCATE(-13.56, 1); -- result: -13.5
To get the absolute value of a number:
SELECT ABS(-12); -- result: 12
To get the square root of a number:
SELECT SQRT(9); -- result: 3
USEFUL NULL FUNCTIONS
To fetch the names of the cities whose rating values are not missing:
SELECT name FROM city WHERE rating IS NOT NULL;
COALESCE(x, y, ...)
To replace NULL in a query with something meaningful:
SELECT domain,
COALESCE(domain, 'domain missing')
FROM contacts;
The COALESCE() function takes any number of arguments and returns the value of the first argument that is not NULL.
NULLIF(x, y)
To save yourself from division by 0 errors:
SELECT last_month, this_month,
this_month * 100.0
/ NULLIF(last_month, 0) AS better_by_percent
FROM video_views;
The NULLIF(x, y) function returns NULL if x equals y, else it returns the value of x value.
DATE AND TIME
There are 5 main time-related types in MySQL:
DATE TIME DATETIME TIMESTAMP YEAR
DATE – stores the year, month, and day in the YYYY-MM-DD format.
TIME – stores the hours, minutes, and seconds in the HH:MM:SS format.
DATETIME – stores the date and time in the YYYY-MM-DD HH:MM:SS format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
TIMESTAMP – stores the date and time. The range is '197001-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.
MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval.
YEAR – stores the year in the YYYY format.
NTERVALS
An interval is the duration between two points in time. To define an interval: INTERVAL 1 DAY This syntax consists of the INTERVAL keyword, a value, and a time part keyword (YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MICROSECOND).
You may combine di erent INTERVALs using the + or operator:
INTERVAL 1 YEAR + INTERVAL 3 MONTH
You may also use the standard SQL syntax:
INTERVAL '1-3' YEAR_MONTH
-- 1 year and 3 months
INTERVAL '3-12' HOUR_MINUTE -- 3 hours 12 minutes WHAT TIME IS IT?
To answer this question, use:
CURRENT_TIME or CURTIME – to get the current time.
CURRENT_DATE or CURDATE – to get the current date. NOW() or CURRENT_TIMESTAMP – to get the current timestamp with both of the above.
