http://dev.mysql.com/doc/refman/5.0/en/expressions.html
http://dev.mysql.com/doc/refman/5.0/en/functions.html

Chapter 12. Functions and Operators

Expressions can be used at several points in SQL statements, such as in the ORDER BY or HAVING clauses of SELECTstatements, in the WHERE clause of a SELECT, DELETE, orUPDATE 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 18.2, “Using Stored Routines (Procedures and Functions)”, and Section 21.2, “Adding New Functions to MySQL”. See Section 9.2.3, “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 NULLvalue unless otherwise indicated in the documentation for a particular function or operator.

Note

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.

EXPRESSIONS SYNTAX:

expr:
    expr OR expr
  | expr || expr
  | expr XOR expr
  | expr AND expr
  | expr && expr
  | NOT expr
  | ! expr
  | boolean_primary IS [NOT] {TRUE | FALSE | UNKNOWN}
  | boolean_primary

boolean_primary:
    boolean_primary IS [NOT] NULL
  | boolean_primary <=> predicate
  | boolean_primary comparison_operator predicate
  | boolean_primary comparison_operator {ALL | ANY} (subquery)
  | predicate

comparison_operator: = | >= | > | <= | < | <> | !=

predicate:
    bit_expr [NOT] IN (subquery)
  | bit_expr [NOT] IN (expr [, expr] ...)
  | bit_expr [NOT] BETWEEN bit_expr AND predicate
  | bit_expr SOUNDS LIKE bit_expr
  | bit_expr [NOT] LIKE simple_expr [ESCAPE simple_expr]
  | bit_expr [NOT] REGEXP bit_expr
  | bit_expr

bit_expr:
    bit_expr | bit_expr
  | bit_expr & bit_expr
  | bit_expr << bit_expr
  | bit_expr >> bit_expr
  | bit_expr + bit_expr
  | bit_expr - bit_expr
  | bit_expr * bit_expr
  | bit_expr / bit_expr
  | bit_expr DIV bit_expr
  | bit_expr MOD bit_expr
  | bit_expr % bit_expr
  | bit_expr ^ bit_expr
  | bit_expr + interval_expr
  | bit_expr - interval_expr
  | simple_expr

simple_expr:
    literal
  | identifier
  | function_call
  | simple_expr COLLATE collation_name
  | param_marker
  | variable
  | simple_expr || simple_expr
  | + simple_expr
  | - simple_expr
  | ~ simple_expr
  | ! simple_expr
  | BINARY simple_expr
  | (expr [, expr] ...)
  | ROW (expr, expr [, expr] ...)
  | (subquery)
  | EXISTS (subquery)
  | {identifier expr}
  | match_expr
  | case_expr
  | interval_expr

Notes:

For literal value syntax, see Section 9.1, “Literal Values”.

For identifier syntax, see Section 9.2, “Schema Object Names”.

Variables can be user variables, system variables, or stored program local variables or parameters:

User variables: Section 9.4, “User-Defined Variables”
System variables: Section 5.1.5, “Using System Variables”
Local variables: Section 13.6.4.1, “Local Variable DECLARE Syntax”
Parameters: Section 13.1.9, “CREATE PROCEDURE and CREATE FUNCTION Syntax”

param_marker is '?' as used in prepared statements for placeholders. See Section 13.5.1, “PREPARESyntax”.

(subquery) indicates a subquery that returns a single value; that is, a scalar subquery. SeeSection 13.2.9.1, “The Subquery as Scalar Operand”.

{identifier expr} is ODBC escape syntax and is accepted for ODBC compatibility. The value isexpr. The curly braces in the syntax should be written literally; they are not metasyntax as used elsewhere in syntax descriptions.

match_expr indicates a MATCH expression. See Section 12.9, “Full-Text Search Functions”.

case_expr indicates a CASE expression. See Section 12.4, “Control Flow Functions”.

interval_expr represents a time interval. The syntax is INTERVAL expr unit, where unit is a specifier such as HOUR, DAY, or WEEK. For the full list of unit specifiers, see the description of theDATE_ADD() function in Section 12.7, “Date and Time Functions”.

The meaning of some operators depends on the SQL mode:

By default, || is a logical OR operator. With PIPES_AS_CONCAT enabled, || is string concatenation, with a precedence between ^ and the unary operators.
By default, ! has a higher precedence than NOT as of MySQL 5.0.2. For earlier versions, or from 5.0.2 on with HIGH_NOT_PRECEDENCE enabled, ! and NOT have the same precedence.

See Section 5.1.7, “Server SQL Modes”.

Code StackCats Edoc

Friday, October 12, 2012

MySQL COMMANDS and EXPRESSIONS

Chapter 12. Functions and Operators

No comments:

Post a Comment

Contributors