Table of Contents [+/-]
GROUP BY Clauses [+/-]
Expressions can be used at several points in SQL statements, such as
in the ORDER BY or HAVING
clauses of SELECT statements, in the
WHERE clause of a
SELECT,
DELETE, or
UPDATE statement, or in
SET
statements. Expressions can be written using literal values, column
values, NULL, built-in functions, stored
functions, user-defined functions, and operators. This chapter
describes the functions and operators that are permitted for writing
expressions in MySQL. Instructions for writing stored functions and
user-defined functions are given in
Section 19.2, “Using Stored Routines (Procedures and Functions)”, and
Section 23.3, “Adding New Functions to MySQL”. See
Section 9.2.4, “Function Name Parsing and Resolution”, for the rules describing how
the server interprets references to different kinds of functions.
An expression that contains NULL always produces
a NULL value unless otherwise indicated in the
documentation for a particular function or operator.
By default, there must be no whitespace between a function name and the parenthesis following it. This helps the MySQL parser distinguish between function calls and references to tables or columns that happen to have the same name as a function. However, spaces around function arguments are permitted.
You can tell the MySQL server to accept spaces after function names
by starting it with the
--sql-mode=IGNORE_SPACE option. (See
Section 5.1.7, “Server SQL Modes”.) Individual client programs can
request this behavior by using the
CLIENT_IGNORE_SPACE option for
mysql_real_connect(). In either
case, all function names become reserved words.
For the sake of brevity, most examples in this chapter display the output from the mysql program in abbreviated form. Rather than showing examples in this format:
mysql> SELECT MOD(29,9);
+-----------+
| mod(29,9) |
+-----------+
| 2 |
+-----------+
1 rows in set (0.00 sec)
This format is used instead:
mysql> SELECT MOD(29,9);
-> 2
© 2012, Oracle Corporation and/or its affiliates
User Comments
In the absence of a "delta" function to find the change in value for a column from one row to the next, I found this solution using a mysql variable:
4 rows in set (0.00 sec)mysql> create temporary table t (idx int, val int);
Query OK, 0 rows affected (0.08 sec)
mysql> insert into t values(1, 100), (2, 120), (3, 95), (4, 200);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> set @v = 0; select val - @v delta, @v := val val from t order by idx;
Query OK, 0 rows affected (0.00 sec)
Don't swap the argument order on the select - the assignment must come last. And don't forget to prime your variable before the select or it won't have the right type (ie. numeric in this case.)
This is particularly useful with unix timestamps to work out the elapsed seconds between records.
You can extract multiple column values in a single column
eg:
SELECT IF(First_name LIKE '%mathew%',First_name,Last_name) FROM user_tbl
It will work perfectly and allows us to choose a dynamic column.
Bye Bye
Add your own comment.