Informatics Practices for Class XI (CBSE)/SQL Functions and Grouping

Syntax
Function names are case insensitive. You can write them as you prefer:

SELECT database -- ok SELECT DataBase -- ok SELECT DATABASE -- ok

If the IGNORE_SPACE SQL_MODE is not set, you can not put a space between the function name and the first parenthesis. It would return a 1064 error. IGNORE_SPACE is usually 0. The reason is that the parser is faster if that flag is disabled. So:

SELECT DATABASE -- usually not accepted SELECT DATABASE -- always works fine

However, this restriction only applies to the native MySQL functions. UDFs and stored functions may be written with a space after the name.

You can't use a value calculated in the SELECT clause as a constraint in the WHERE clause (its a chicken & egg problem); the WHERE clause is what determines the values in the SELECT clause. What you want is the HAVING clause which is applied *after* all matching rows have been found.

General functions
Type-independent functions.

BENCHMARK(times, espression)

Executes espression n times and returns how time it spent. Useful to find bottlenecks in SQL expressions.

BENCHMARK(10000, CAST(666 AS TEXT))

CAST(value AS type)

Returns value converted in the specified type.

CHARSET(string)

Returns the CHARACTER SET used by string.

COALESCE(value, ...)

Returns the first argument which is not NULL. If all arguments are NULL, returns NULL. There must be at least one argument.

COERCIBILITY(string)

COLLATION(string)

Returns the COLLATION used by the string.

CONNECTION_ID

Returns the id of the current thread.

CONVERT(value, type)

Returns value converted to the specified type.

SELECT CONVERT ('666', UNSIGNED INTEGER)

CONVERT(string USING charset)

Converts the passed string to the specified CHARACTER SET.

SELECT CONVERT ('This is a text' USING utf8)

CURRENT_USER

Returns the username and the hostname used in the current connection.

SELECT CURRENT_USER SELECT CURRENT_USER -- it's correct

DATABASE

Returns the current database's name, set with the USE command.

SELECT DATABASE

FOUND_ROWS

After a SELECT with a LIMIT clause and the SQL_CALC_FOUND_ROWS keyword, you can run another SELECT with the FOUND_ROWS function. It returns the number of rows found by the previous query if it had no LIMIT clause.

SELECT SQL_CALC_FOUND_ROWS * FROM stats ORDER BY id LIMIT 10 OFFSET 50 SELECT FOUND_ROWS AS n

GREATEST(value1, value2, ...)

Returns the greatest argument passed.

IF(val1, val2, val3)

If val1 is TRUE, returns val2. If val1 is FALSE or NULL, returns val3.

IFNULL(val1, val2)

If val1 is NULL, returns val2; else, returns val1.

ISNULL(value)

If the value passed is NULL returns 1, else returns 0.

INTERVAL(val1, val2, val3, ...)

NULLIF(val1, val2)

If val1 = val2, returns NULL; else, returns val1.

LEAST(value1, value2, ...)

Returns the minimum argument passed.

Date and time
SELECT * FROM mytable WHERE datetimecol >= (CURDATE - INTERVAL 1 YEAR) AND datetimecol < (CURDATE - INTERVAL 1 YEAR) INTERVAL 1 DAY;

SELECT IF(DAYOFMONTH(CURDATE) <= 15, DATE_FORMAT(CURDATE, '%Y-%m-15'),  DATE_FORMAT(CURDATE + INTERVAL 1 MONTH, '%Y-%m-15')) AS next15 FROM table;

SELECT YEAR('2002-05-10'), MONTH('2002-05-10'), DAYOFMONTH('2002-05-10')

SELECT PurchaseDate FROM table WHERE YEAR(PurchaseDate) <= YEAR(CURDATE)

SELECT columns FROM table WHERE start_time >= '2004-06-01 10:00:00' AND end_time <= '2004-06-03 18:00:00'

SELECT * FROM t1 WHERE DATE_FORMAT(datetime_column, '%T') BETWEEN 'HH:MM:SS' AND 'HH:MM:SS'

SELECT Start_time, End_time FROM Table WHERE Start_time >= NOW - INTERVAL 4 HOUR SELECT NOW + INTERVAL 60 SECOND

SELECT UNIX_TIMESTAMP('2007-05-01'); -- 1177970400 SELECT FROM_UNIXTIME(1177970400); -- 2007-05-01 00:00:00

COUNT(field)
If * is given, instead of the name of a field, COUNT returns the number of rows found by the query. It's commonly used to get the number of rows in a table.

SELECT COUNT(*) FROM `antiques`

If the DISTINCT keyword is used, identical rows are counted only once.

SELECT COUNT(DISTINCT *) FROM `antiques`

If a field name is given, returns the number of non-NULL values.

SELECT COUNT(`cost`) FROM `antiques`

If a field name is given and the DISTINCT keyword is given, returns the number of non-NULL values, and identical values are counted only once.

SELECT COUNT(DISTINCT `cost`) FROM `antiques`

You can count non-NULL values for an expression:

SELECT COUNT(`longitude` + `latitude`) FROM `cities`

This returns the number of rows where longitude and latitude are both non-NULL.

MAX(field)
MAX can be used to get the maximum value for an expression in the rows matching to a query. If no row matches the query, returns NULL.

SELECT MAX(`cost`) FROM `antiques` SELECT MAX(LENGTH(CONCAT(`first_name`, ' ', `last_name`))) FROM `subscribers`

MIN(field)
MAX can be used to get the minimum value for an expression in the rows matching to a query. If no row matches the query, returns NULL.

SELECT MIN(`cost`) FROM `antiques`

AVG(field)
MAX can be used to get the average value for an expression in the rows matching to a query. If no row matches the query, returns NULL.

SELECT AVG(`cost`) FROM `antiques`

SUM(field)
SUM can be used to get the sum of the values for an expression in the rows matching to a query. If no row matches the query, returns NULL.

If SUM(SELECTED expr) is used, identical values are added only once. SUM(DISTINCT) has been added in MySQL 5.1.

SELECT SUM(`cost`) FROM `antiques`

Aggregate bit functions
General syntax:

FUNCTION_NAME(expression)

These functions calculate expression for each row of the resultset and permorm the calculation between all the expressions. These are bitwise functions. The precision used is 64 bit.

AND

SELECT BIT_AND(ip) FROM log

OR

SELECT BIT_OR(ip) FROM log

(returns 0 if there are no rows)

XOR

SELECT BIT_XOR(ip) FROM log

(returns 0 if there are no rows)