MySQL/Language/Functions

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

SELECTdatabase()-- ok
SELECTDataBase()-- ok
SELECTDATABASE()-- 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:

SELECTDATABASE()-- usually not accepted
SELECTDATABASE()-- 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.

Type-independent functions.

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

SELECTBENCHMARK(10000,'hello');-- Treatment in 0.0010 sec

Returns value converted in the specified type.

SELECTCAST(20130101ASDATE);-- 2013年01月01日

Returns the CHARACTER SET used by string.

SELECTCHARSET(20130101);-- binary
SHOWCHARACTERSET;-- displays all the different installed CHARACTER SET

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

SELECTCOALESCE(NULL,'hello',NULL);-- hello

Returns the coercibility (between 0 to 5):

SELECTCOERCIBILITY('hello');-- 4

Returns the COLLATION used by the string.

SELECTCOLLATION('hello');-- utf8_general_ci

Returns the id of the current thread.

SELECTCONNECTION_ID();-- 31

Returns value converted to the specified type.

SELECTCONVERT('666',UNSIGNEDINTEGER)

Converts the passed string to the specified CHARACTER SET.

SELECTCONVERT('This is a text'USINGutf8)

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

SELECTCURRENT_USER()
SELECTCURRENT_USER-- it's correct

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

SELECTDATABASE()

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.

SELECTSQL_CALC_FOUND_ROWS*FROMstatsORDERBYidLIMIT10OFFSET50
SELECTFOUND_ROWS()ASn

Returns the greatest argument passed.

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

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

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

Returns the location of the first argument which is greater than the first one, beginning by zero in the integers in parameter:

SELECTINTERVAL(10,20,9,8,7);-- 0
SELECTINTERVAL(10,9,20,8,7);-- 1
SELECTINTERVAL(10,9,8,20,7);-- 2
SELECTINTERVAL(10,9,8,7,20);-- 3

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

Returns the last inserted ID in AUTO_INCREMENT from the database, which can avoid a SELECT when inserting two records where the second needs a foreign key to the first.

Returns the minimum argument passed.

Cut a string:

SELECTSUBSTR('Hello World!',7,5);-- World

There are plenty of date related functions.^[2]

SELECT*FROMmytable
WHEREdatetimecol>=(CURDATE()-INTERVAL1YEAR)AND
datetimecol<(CURDATE()-INTERVAL1YEAR)INTERVAL1DAY;
SELECTIF(DAYOFMONTH(CURDATE())<=15,
DATE_FORMAT(CURDATE(),'%Y-%m-15'),
DATE_FORMAT(CURDATE()+INTERVAL1MONTH,'%Y-%m-15'))ASnext15
FROMtable;
SELECTYEAR('2002年05月10日'),MONTH('2002年05月10日'),DAYOFMONTH('2002年05月10日')
SELECTPurchaseDateFROMtableWHEREYEAR(PurchaseDate)<=YEAR(CURDATE())
SELECTcolumnsFROMtable
WHEREstart_time>='2004年06月01日 10:00:00'ANDend_time<='2004年06月03日 18:00:00'
SELECT*FROMt1
WHEREDATE_FORMAT(datetime_column,'%T')BETWEEN'HH:MM:SS'AND'HH:MM:SS'
SELECTStart_time,End_timeFROMTable
WHEREStart_time>=NOW()-INTERVAL4HOUR

SELECTNOW()+INTERVAL60SECOND
SELECTUNIX_TIMESTAMP('2007年05月01日');-- 1177970400
SELECTFROM_UNIXTIME(1177970400);-- 2007年05月01日 00:00:00

Attention: convert('17/02/2016 15:49:03',datetime) or convert('17-02-2016 15:49:03',datetime) gives null, so an insert request replaces it by the same result as now(). This should be convert('2016-02-17 15:49:03',datetime) or convert('2016/02/17 15:49:03',datetime).

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.

SELECTCOUNT(*)FROM`antiques`

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

SELECTCOUNT(DISTINCT*)FROM`antiques`

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

SELECTCOUNT(`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.

SELECTCOUNT(DISTINCT`cost`)FROM`antiques`

You can count non-NULL values for an expression:

SELECTCOUNT(`longitude`+`latitude`)FROM`cities`

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

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.

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

MIN() 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.

SELECTMIN(`cost`)FROM`antiques`

AVG() 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.

SELECTAVG(`cost`)FROM`antiques`

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(DISTINCT expression) is used, identical values are added only once. It has been added in MySQL 5.1.

SELECTSUM(`cost`)FROM`antiques`

GROUP_CONCAT() can be used to concatenate values from all records for a group into a single string separated by a comma by default, or any additional token you like if placed as the optional second parameter.

CREATETEMPORARYTABLEproduct(
idINTEGER,product_typeVARCHAR(10),product_nameVARCHAR(50)
);
INSERTINTOproductVALUES
(1,'mp3','iPod'),
(2,'mp3','Zune'),
(3,'mp3','ZEN'),
(4,'notebook','Acer Eee PC'),
(4,'notebook','Everex CloudBook');
SELECT*FROMproduct;
SELECTproduct_type,group_concat(product_name)
FROMproduct
GROUPBYproduct_type;
SELECTproduct_type,group_concat(' ',product_name)
FROMproduct
GROUPBYproduct_type;

General syntax:

FUNCTION_NAME(''expression'')

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

SELECTBIT_AND(ip)FROMlog

SELECTBIT_OR(ip)FROMlog

(returns 0 if there are no rows)

SELECTBIT_XOR(ip)FROMlog

(returns 0 if there are no rows)

• Book:MySQL