Navigation
This version of the documentation is archived and no longer supported.
Supported SQL Functions and Operators
MongoDB Connector for BI Version 2.2 is compatible with SQL-99 SELECT statements.
The following tables list the various MySQL functions, constructors, and
operators supported in MongoDB Connector for BI 2.2.
Supported MySQL functions, constructors, and operators
Comparison Functions and Operators
Function/Operator |
Description |
BETWEEN ... AND ... |
Check whether a value is within a range of values |
COALESCE() |
Return the first non-NULL argument |
= |
Equal operator |
<=> |
NULL-safe equal to operator |
> |
Greater than operator |
>= |
Greater than or equal operator |
GREATEST() |
Return the largest argument |
IN() |
Check whether a value is within a set of values |
INTERVAL(N,N1,N2,...) |
Return the 0-based index position of last number in the list to be
< N . Return -1 if N is NULL
N1 , N2 , etc. must be given in ascending order.
Example
Returns 3 because 12 is the last number in the list
less than 26 .
|
IS |
Test a value against a boolean |
IS NOT |
Test a value against a boolean |
IS NOT NULL |
NOT NULL value test |
IS NULL |
NULL value test |
ISNULL() |
Test whether the argument is NULL |
LEAST() |
Return the smallest argument |
< |
Less than operator |
<= |
Less than or equal operator |
LIKE |
Simple pattern matching |
NOT BETWEEN ... AND ... |
Check whether a value is not within a range of values |
!= , <> |
Not equal operator |
NOT IN() |
Check whether a value is not within a set of values |
Logical Operators
Function/Operator |
Description |
AND , && |
Logical AND |
NOT , ! |
Negates value |
|| , OR |
Logical OR |
XOR |
Logical XOR |
JOIN Expressions
Expression |
Description |
JOIN |
Select records that have matching values in multiple tables. |
INNER JOIN |
Semantically equivalent to JOIN . |
LEFT JOIN |
Return all records from the left table, and the matched records
from the right table. |
RIGHT JOIN |
Return all records from the right table, and the matched records
from the left table. |
NATURAL JOIN |
Return only records which do not appear in both tables. |
USING |
Return all records where the given columns appear in both tables. |
Control Flow Functions and Operators
Function/Operator |
Description |
CASE |
Case operator |
IF() |
If/else construct |
IFNULL() |
Null if/else construct |
NULLIF() |
Return NULL if expr1 = expr2 |
String Functions
Function/Operator |
Description |
ASCII() |
Return numeric value of left-most character |
CHAR_LENGTH() |
Return number of characters in argument |
CHARACTER_LENGTH() |
Synonym for CHAR_LENGTH() |
CONCAT() |
Return concatenated string |
CONCAT_WS() |
Return concatenate with separator |
ELT() |
Return string at index number |
INSERT() |
Insert a substring at the specified position up to the specified
number of characters |
INSTR() |
Return the index of the first occurrence of substring |
LCASE() |
Synonym for LOWER() |
LEFT() |
Return the leftmost number of characters as specified |
LENGTH() |
Return the length of a string in bytes |
LOCATE() |
Return the position of the first occurrence of substring |
LOWER() |
Return the argument in lowercase |
LTRIM() |
Remove leading spaces |
MID() |
Return a substring starting from the specified position |
NOT LIKE |
Negation of simple pattern matching |
NOT REGEXP |
Negation of REGEXP |
REGEXP |
Pattern matching using regular expressions |
REPLACE() |
Replace occurrences of a specified string |
RIGHT() |
Return the specified rightmost number of characters |
RTRIM() |
Remove trailing spaces |
SPACE() |
Return a string of the specified number of spaces |
SUBSTR() |
Return the substring as specified |
SUBSTRING() |
Return the substring as specified |
SUBSTRING_INDEX() |
Return a substring from a string before the specified number of
occurrences of the delimiter |
TRIM() |
Remove leading and trailing spaces |
UCASE() |
Synonym for UPPER() |
UPPER() |
Convert to uppercase |
Numeric Functions and Operators
Function/Operator |
Description |
ABS() |
Return the absolute value |
ACOS() |
Return the arc cosine |
ASIN() |
Return the arc sine |
ATAN() |
Return the arc tangent |
ATAN2(), ATAN() |
Return the arc tangent of the two arguments |
CEIL() |
Return the smallest integer value not less than the argument |
CEILING() |
Return the smallest integer value not less than the argument |
COS() |
Return the cosine |
COT() |
Return the cotangent |
DEGREES() |
Convert radians to degrees |
DIV |
Integer division |
/ |
Division operator |
EXP() |
Raise to the power of |
FLOOR() |
Return the largest integer value not greater than the argument |
LN() |
Return the natural logarithm of the argument |
LOG() |
Return the natural logarithm of the first argument |
LOG10() |
Return the base-10 logarithm of the argument |
LOG2() |
Return the base-2 logarithm of the argument |
- |
Minus operator |
MOD() |
Return the remainder |
%, MOD |
Modulo operator |
PI() |
Return the value of pi |
+ |
Addition operator |
POW() |
Return the argument raised to the specified power |
POWER() |
Return the argument raised to the specified power |
RADIANS() |
Return argument converted to radians |
ROUND() |
Round the argument |
SIGN() |
Return the sign of the argument |
SIN() |
Return the sine of the argument |
SQRT() |
Return the square root of the argument |
TAN() |
Return the tangent of the argument |
* |
Multiplication operator |
TRUNCATE() |
Truncate to specified number of decimal places |
- |
Change the sign of the argument |
Date and Time Functions
Function/Operator |
Description |
ADDDATE() |
Add time values (intervals) to a date value |
CURDATE() |
Return the current date |
CURRENT_DATE(), CURRENT_DATE |
Synonyms for CURDATE() |
CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP |
Synonyms for NOW() |
DATE() |
Extract the date part of a date or datetime expression |
DATEDIFF() |
Returns the difference, in days, between two specified dates. |
DATE_ADD() |
Add time values (intervals) to a date value |
DATE_FORMAT() |
Format date as specified |
DATE_SUB() |
Subtract a time value (interval) from a date |
DAY() |
Synonym for DAYOFMONTH() |
DAYNAME() |
Return the name of the weekday |
DAYOFMONTH() |
Return the day of the month (0-31) |
DAYOFWEEK() |
Return the weekday index of the argument |
DAYOFYEAR() |
Return the day of the year (1-366) |
EXTRACT() |
Extract part of a date |
FROM_DAYS() |
Convert a day number to a date |
HOUR() |
Extract the hour |
LAST_DAY |
Return the last day of the month for the argument |
MAKEDATE() |
Create a date from the year and day of year |
MICROSECOND() |
Return the microseconds from the input time or datetime expression
as a number between 0 and 999999 inclusive. |
MINUTE() |
Return the minute from the argument |
MONTH() |
Return the month from the date passed |
MONTHNAME() |
Return the name of the month |
NOW() |
Return the current date and time |
QUARTER() |
Return the quarter from a date argument |
SECOND() |
Return the second (0-59) |
STR_TO_DATE() |
Convert a string to a date |
SUBDATE() |
Synonym for DATE_SUB() when invoked with three arguments |
TIME_TO_SEC() |
Return the argument converted to seconds |
TIMEDIFF() |
Subtract time |
TIMESTAMP() |
With a single argument, this function returns the date or datetime |
TIMESTAMPADD() |
Add an interval to a datetime expression |
TIMESTAMPDIFF() |
Subtract an interval from a datetime expression |
TO_DAYS() |
Return the date argument converted to days |
UTC_TIMESTAMP() |
Return the current UTC date and time |
WEEK() |
Return the week number |
WEEKDAY() |
Return the weekday index |
WEEKOFYEAR() |
Return the calendar week of the date (1-53) |
YEAR() |
Return the year |
YEARWEEK() |
Return the year and week |
Cast Functions and Operators
Function |
Description |
CAST() |
Cast a value as a certain type |
CONVERT() |
Convert a value as a certain type |
Subquery Constructors
Function |
Description |
ROW() |
Returns one row of values rather than one column of values |
GROUP BY
(Aggregate) Functions
Function |
Description |
AVG() |
Return the average value of the argument |
COUNT() |
Return a count of the number of rows returned |
COUNT(DISTINCT) |
Return the count of a number of different values |
MAX() |
Return the maximum value |
MIN() |
Return the minimum value |
STD() |
Return the population standard deviation |
STDDEV() |
Return the population standard deviation |
STDDEV_POP() |
Return the population standard deviation |
STDDEV_SAMP() |
Return the sample standard deviation |
SUM() |
Return the sum |
Utility Statements
Statement |
Description |
USE <databaseName> |
Choose the database to use as the current database for subsequent statements |
Database Administration Statements
Statement |
Description |
SHOW CREATE TABLE <tableName> |
Shows the structure of the <tableName> table. The structure
includes comments that describe the mapping from the SQL table and
columns to MongoDB collection and fields. |
SHOW [GLOBAL | SESSION] STATUS [LIKE 'pattern' | WHERE expr] |
Shows mongosqld server metrics. The structure contains
non-empty values for at least the following fields:
Bytes_received
Bytes_sent
Connections
Queries
Threads_connected
Threads_created
Uptime
By default the SHOW STATUS statement will only show statistics
for the current connection, corresponding to the SESSION
qualifier. If you specify the GLOBAL qualifier,
SHOW STATUS will instead show aggregate statistics for the
entire server.
|
ALTER TABLE <tableName> [alter_specification [, alter_specification] ...] |
Use to rename tables and add, update or remove columns in a
table. |
RENAME TABLE <tableName> TO <newTableName> [, <tableName2> TO <newTableName2> ] ... |
Use to rename one or more tables. |