A SQL statement is a sequence of tokens ending with a semicolon (;). Tokens are separated by spaces, tabs, or newlines, and can be keywords, identifiers, constants, or special characters.
Identifiers
An identifier names a data object such as a database, table, or column. Lindorm SQL uses backticks (` ``) to quote identifiers.
| Identifier type | Rules | Example |
|---|---|---|
| Unquoted | Must start with a letter; can contain only letters, digits, and underscores (_) | employee_name |
| Quoted | Enclosed in backticks; can contain any characters | ` Employee Name ` |
To separate data objects at different levels, use a period (.). For example, db.tbl refers to the table tbl in the database db.
Valid vs. invalid identifiers
| Valid | Invalid | Reason |
|---|---|---|
orders, user_id, tbl1 | 1table, my-table, user name | Unquoted identifiers must start with a letter and contain only letters, digits, and underscores |
` 1table , my-table , user name ` | — | Quoted identifiers accept any characters |
Important
Beyond these lexical rules, identifiers must also comply with the constraints of the specific Lindorm engine you are using:
LindormTable: see General limits
LindormTSDB: see Limits
Constants
Lindorm SQL supports two types of constants: string constants and numeric constants.
String constants
A string constant is a sequence of characters enclosed in single quotation marks ('). Example: 'This is a string'.
Escape characters (supported in SQL 2.8.4.8 and later)
Starting from SQL 2.8.4.8, the SQL parser interprets backslash (\) as an escape character based on the character that follows it.
| Escape sequence | Character |
|---|---|
\0 | ASCII NUL (X'00') |
\' | Single quotation mark (') |
\" | Double quotation mark (") |
\\ | Backslash (\) |
\Z | ASCII 26 (Control-Z) |
\r | Carriage return |
\n | Line feed |
To treat backslashes as ordinary characters rather than escape sequences, set the SQL_MODE parameter to NO_BACKSLASH_ESCAPES. For details, see Session variables.
Note
To check which SQL version you are running, see SQL versions.
Escape sequences apply only to string constants in SQL statements. Parameters in prepared statements are not escaped.
Numeric constants
| Format | Description | Example |
|---|---|---|
digits | One or more decimal digits (0–9) | 42 |
digitse[+-]digits | Contains an exponent mark (e) | 4e3 |
digits.[digits][e[+-]digits] | Contains a decimal point (.) and an optional exponent | 5.3e2 |
[digits].digits[e[+-]digits] | Decimal point at the start | — |
Important
A numeric constant cannot contain spaces or characters outside the formats above.
If a numeric constant contains a decimal point (
.), at least one digit must appear before it.If a numeric constant contains an exponent mark (
e), at least one digit must appear before it.
Special characters
| Character | Usage |
|---|---|
() parentheses | Group expressions; prioritize evaluation order; required by certain SQL syntax |
, comma | Separate elements in lists |
; semicolon | End a SQL statement; also valid inside string constants |
: colon | Separate column family names from column names in identifiers |
* asterisk | Refer to all fields in a table, or denote a combination of values |
. period | Decimal point in numeric constants; separator between hierarchical data objects |
Keywords
SQL keywords are classified as reserved or non-reserved:
Reserved keywords are true keywords. To use them as identifiers, enclose them in backticks.
Non-reserved keywords have special meaning only in specific contexts and can be used as identifiers elsewhere.
Example: reserved keyword as an identifier
-- Using a reserved keyword without backticks causes an error
CREATE TABLE select (id INT);
-- ERROR: "select" is a reserved keyword
-- Enclose the keyword in backticks to use it as an identifier
CREATE TABLE `select` (id INT);
-- OKQuery keywords dynamically
The keyword list may not reflect the latest updates. For LindormTable 2.6.3 and later, query the INFORMATION_SCHEMA.KEYWORDS system view to get the current keyword list.
To check whether a specific word is a reserved keyword:
SELECT * FROM information_schema.keywords WHERE WORD = UPPER('<word>');If the result set is not empty and the RESERVED field is 1, the word is a reserved keyword.
Example: Check whether CASCADED is reserved:
SELECT * FROM information_schema.keywords WHERE WORD = UPPER('CASCADED');Reserved keywords
| Initial | Reserved keywords |
|---|---|
| A | ABS, ALL, ALLOCATE, ALLOW, ALTER, AND, ANY, ARE, ARRAY, ARRAY_MAX_CARDINALITY, AS, ASENSITIVE, ASYMMETRIC, AT, ATOMIC, AUTHORIZATION, AVG |
| B | BEGIN, BEGIN_FRAME, BEGIN_PARTITION, BETWEEN, BIGINT, BINARY, BIT, BLOB, BOOLEAN, BOTH, BY |
| C | CALL, CALLED, CARDINALITY, CASCADED, CASE, CAST, CEIL, CEILING, CHAR, CHARACTER, CHARACTER_LENGTH, CHAR_LENGTH, CHECK, CLASSIFIER, CLOB, CLOSE, COALESCE, COLLATE, COLLECT, COLUMN, COMMIT, CONDITION, CONNECT, CONSTRAINT, CONTAINS, CONVERT, CORR, CORRESPONDING, COUNT, COVAR_POP, COVAR_SAMP, CREATE, CROSS, CUBE, CUME_DIST, CURRENT, CURRENT_CATALOG, CURRENT_DATE, CURRENT_DEFAULT_TRANSFORM_GROUP, CURRENT_PATH, CURRENT_ROLE, CURRENT_ROW, CURRENT_SCHEMA, CURRENT_TIME, CURRENT_TIMESTAMP, CURRENT_TRANSFORM_GROUP_FOR_TYPE, CURRENT_USER, CURSOR, CYCLE |
| D | DATE, DATETIME, DAY, DEALLOCATE, DEC, DECIMAL, DECLARE, DEFAULT, DEFINE, DELETE, DENSE_RANK, DEREF, DESCRIBE, DETERMINISTIC, DISALLOW, DISCONNECT, DISTINCT, DOUBLE, DROP, DYNAMIC |
| E | EACH, ELEMENT, ELSE, EMPTY, END, END-EXEC, END_FRAME, END_PARTITION, EQUALS, ESCAPE, EVERY, EXCEPT, EXEC, EXECUTE, EXISTS, EXP, EXPLAIN, EXTEND, EXTERNAL, EXTRACT |
| F | FALSE, FETCH, FILTER, FIRST_VALUE, FLOAT, FLOOR, FOR, FOREIGN, FRAME_ROW, FREE, FRIDAY, FROM, FULL, FUNCTION, FUSION |
| G | GEOMETRYCOLLECTION, GET, GLOBAL, GRANT, GROUP, GROUPING, GROUPS |
| H | HAVING, HOLD, HOUR |
| I | IDENTITY, IMPORT, IN, INDICATOR, INITIAL, INNER, INOUT, INSENSITIVE, INSERT, INT, INTEGER, INTERSECT, INTERSECTION, INTERVAL, INTO, IS |
| J | JOIN, JSON_ARRAY, JSON_ARRAYAGG, JSON_EXISTS, JSON_OBJECT, JSON_OBJECTAGG, JSON_QUERY, JSON_VALUE |
| L | LAG, LANGUAGE, LARGE, LAST_VALUE, LATERAL, LEAD, LEADING, LEFT, LIKE, LIKE_REGEX, LIMIT, LINESTRING, LN, LOCAL, LOCALTIME, LOCALTIMESTAMP, LOWER |
| M | MATCH, MATCHES, MATCH_NUMBER, MATCH_RECOGNIZE, MAX, MEASURES, MEMBER, MERGE, METHOD, MIN, MINUS, MINUTE, MOD, MODIFIES, MODULE, MONDAY, MONTH, MULTILINESTRING, MULTIPOINT, MULTIPOLYGON, MULTISET |
| N | NATIONAL, NATURAL, NCHAR, NCLOB, NEW, NEXT, NO, NONE, NORMALIZE, NOT, NTH_VALUE, NTILE, NULL, NULLIF, NUMERIC |
| O | OCCURRENCES_REGEX, OCTET_LENGTH, OF, OFFSET, OLD, OMIT, ON, ONE, ONLY, OPEN, OR, ORDER, ORDINAL, OUT, OUTER, OVER, OVERLAPS, OVERLAY |
| P | PARAMETER, PARTITION, PATTERN, PER, PERCENT, PERCENT_RANK, PERIOD, PERMUTE, PORTION, POSITION, POSITION_REGEX, POINT, POLYGON, POWER, PRECEDES, PRECISION, PREPARE, PREV, PRIMARY, PROCEDURE |
| Q | QUALIFY |
| R | RANGE, RANK, READS, REAL, RECURSIVE, REF, REFERENCES, REFERENCING, REGR_AVGX, REGR_AVGY, REGR_COUNT, REGR_INTERCEPT, REGR_R2, REGR_SLOPE, REGR_SXX, REGR_SXY, REGR_SYY, RELEASE, RESET, RESULT, RETURN, RETURNS, REVOKE, RIGHT, ROLLBACK, ROLLUP, ROW, ROWS, ROW_NUMBER, RUNNING |
| S | SAFE_CAST, SAFE_OFFSET, SAFE_ORDINAL, SATURDAY, SAVEPOINT, SCOPE, SCROLL, SEARCH, SECOND, SEEK, SELECT, SENSITIVE, SESSION_USER, SET, SHOW, SIMILAR, SKIP, SMALLINT, SOME, SPECIFIC, SPECIFICTYPE, SQL, SQLEXCEPTION, SQLSTATE, SQLWARNING, SQRT, START, STATIC, STDDEV_POP, STDDEV_SAMP, STREAM, SUBMULTISET, SUBSET, SUBSTRING, SUBSTRING_REGEX, SUCCEEDS, SUM, SUNDAY, SYMMETRIC, SYSTEM, SYSTEM_TIME, SYSTEM_USER |
| T | TABLE, TABLESAMPLE, THEN, THURSDAY, TIME, TIMESTAMP, TIMEZONE_HOUR, TIMEZONE_MINUTE, TINYINT, TO, TRAILING, TRANSLATE, TRANSLATE_REGEX, TRANSLATION, TREAT, TRIGGER, TRIM, TRIM_ARRAY, TRUE, TRUNCATE, TRY_CAST, TUESDAY |
| U | UESCAPE, UNION, UNIQUE, UNKNOWN, UNNEST, UPDATE, UPPER, UPSERT, USER, USING |
| V | VALUE, VALUES, VALUE_OF, VARBINARY, VARCHAR, VARYING, VAR_POP, VAR_SAMP, VERSIONING, WEDNESDAY |
| W | WHEN, WHENEVER, WHERE, WIDTH_BUCKET, WINDOW, WITH, WITHIN, WITHOUT |
| Y | YEAR |
Non-reserved keywords
| Initial | Non-reserved keywords |
|---|---|
| A | A, ABSENT, ABSOLUTE, ACTION, ADA, ADD, ADMIN, AFTER, AGGREGATORS, ALWAYS, APPLY, ARRAY_AGG, ARRAY_CONCAT_AGG, ASC, ASSERTION, ASSIGNMENT, ATTR, ATTRIBUTE, ATTRIBUTES, AUTO_INCREMENT |
| B | BEFORE, BERNOULLI, BREADTH |
| C | C, CASCADE, CATALOG, CATALOG_NAME, CENTURY, CHAIN, CHARACTERISTICS, CHARACTERS, CHARACTER_SET_CATALOG, CHARACTER_SET_NAME, CHARACTER_SET_SCHEMA, CLASS_ORIGIN, COBOL, COLLATION, COLLATION_CATALOG, COLLATION_NAME, COLLATION_SCHEMA, COLUMN_FAMILY, COLUMN_NAME, COLUMNS, COMMAND_FUNCTION, COMMAND_FUNCTION_CODE, COMMENT, COMMITTED, CONDITIONAL, COMPACT, CONDITION_NUMBER, CONNECTION, CONNECTION_NAME, CONSTRAINTS, CONSTRAINT_CATALOG, CONSTRAINT_NAME, CONSTRAINT_SCHEMA, CONSTRUCTOR, CONTINUE, CONTINUOUS, CURSOR_NAME |
| D | DATA, DATABASE, DATETIME_DIFF, DATETIME_INTERVAL_CODE, DATETIME_INTERVAL_PRECISION, DATETIME_TRUNC, DATE_DIFF, DATE_TRUNC, DAYOFWEEK, DAYOFYEAR, DAYS, DECADE, DEFAULTS, DEFERRABLE, DEFERRED, DEFINED, DEFINER, DEGREE, DEPTH, DERIVED, DESC, DESCRIPTION, DESCRIPTOR, DIAGNOSTICS, DISPATCH, DOMAIN, DOT, DOW, DOY, DYNAMIC_FUNCTION, DYNAMIC_FUNCTION_CODE |
| E | ENCODING, EPOCH, ERROR, EXCEPTION, EXCLUDE, EXCLUDING |
| F | FINAL, FIRST, FOLLOWING, FORMAT, FORTRAN, FOUND, FRAC_SECOND |
| G | G, GENERAL, GENERATED, GEOMETRY, GO, GOTO, GRANTED, GROUP_CONCAT |
| H | HBOOLEAN, HDOUBLE, HIERARCHY, HINTEGER, HLONG, HOP, HOURS, HSHORT, HSTRING |
| I | IGNORE, IF, ILIKE, IMMEDIATE, IMMEDIATELY, IMPLEMENTATION, INCLUDE, INCLUDING, INCREMENT, INITIALLY, INPUT, INSTANCE, INSTANTIABLE, INVOKER, ISODOW, ISOLATION, ISOYEAR |
| J | JAVA, JSON |
| K | K, KEY, KEY_MEMBER, KEY_TYPE |
| L | LABEL, LAST, LENGTH, LEVEL, LIBRARY, LOCATOR |
| M | M, MAP, MATCHED, MAXVALUE, MESSAGE_LENGTH, MESSAGE_OCTET_LENGTH, MESSAGE_TEXT, MICROSECOND, MILLENNIUM, MILLISECOND, MINUTES, MINVALUE, MONTHS, MORE, MUMPS |
| N | NAME, NAMES, NAMESPACE, NANOSECOND, NESTING, NORMALIZED, NULLABLE, NULLS, NUMBER |
| O | OBJECT, OCTETS, OPTION, OPTIONS, ORDERING, ORDINALITY, OTHERS, OUTPUT, OVERRIDING |
| P | PAD, PARAMETER_MODE, PARAMETER_NAME, PARAMETER_ORDINAL_POSITION, PARAMETER_SPECIFIC_CATALOG, PARAMETER_SPECIFIC_NAME, PARAMETER_SPECIFIC_SCHEMA, PARTIAL, PARTITIONS, PASCAL, PASSING, PASSTHROUGH, PASSWORD, PAST, PATH, PERCENTILE_CONT, PERCENTILE_DISC, PIVOT, PLACING, PLAN, PLI, PRECEDING, PREDOWNSAMPLE, PREDOWNSAMPLES, PRESERVE, PRIOR, PRIVILEGES, PUBLIC |
| Q | QUARTER, QUARTERS, QUERY, QUERIES |
| R | READ, RELATIVE, REPEATABLE, REPLACE, RESPECT, RESTART, RESTRICT, RETURNED_CARDINALITY, RETURNED_LENGTH, RETURNED_OCTET_LENGTH, RETURNED_SQLSTATE, RETURNING, RLIKE, ROLE, ROUTINE, ROUTINE_CATALOG, ROUTINE_NAME, ROUTINE_SCHEMA, ROW, ROW_COUNT |
| S | SCALAR, SCALE, SCHEMA, SCHEMA_NAME, SCOPE_CATALOGS, SCOPE_NAME, SCOPE_SCHEMA, SEARCH, SECONDS, SECTION, SECURITY, SELF, SEPARATOR, SEQUENCE, SERIALIZABLE, SERVER, SERVER_NAME, SESSION, SETS, SIMPLE, SIZE, SOURCE, SPACE, SPECIFIC_NAME, SQL_BIGINT, SQL_BINARY, SQL_BIT, SQL_BLOB, SQL_BOOLEAN, SQL_CHAR, SQL_CLOB, SQL_DATE, SQL_DECIMAL, SQL_DOUBLE, SQL_FLOAT, SQL_INTEGER, SQL_INTERVAL_DAY, SQL_INTERVAL_DAY_TO_HOUR, SQL_INTERVAL_DAY_TO_MINUTE, SQL_INTERVAL_DAY_TO_SECOND, SQL_INTERVAL_HOUR, SQL_INTERVAL_HOUR_TO_MINUTE, SQL_INTERVAL_HOUR_TO_SECOND, SQL_INTERVAL_MINUTE, SQL_INTERVAL_MINUTE_TO_SECOND, SQL_INTERVAL_MONTH, SQL_INTERVAL_SECOND, SQL_INTERVAL_YEAR, SQL_INTERVAL_YEAR_TO_MONTH, SQL_LONGVARBINARY, SQL_LONGVARCHAR, SQL_LONGVARNCHAR, SQL_NCHAR, SQL_NCLOB, SQL_NUMERIC, SQL_NVARCHAR, SQL_REAL, SQL_SMALLINT, SQL_TIME, SQL_TIMESTAMP, SQL_TINYINT, SQL_TSI_DAY, SQL_TSI_FRAC_SECOND, SQL_TSI_HOUR, SQL_TSI_MICROSECOND, SQL_TSI_MINUTE, SQL_TSI_MONTH, SQL_TSI_QUARTER, SQL_TSI_SECOND, SQL_TSI_WEEK, SQL_TSI_YEAR, SQL_VARBINARY, SQL_VARCHAR, STATE, STATEMENT, STRING_AGG, STRUCTURE, STYLE, SUBCLASS_ORIGIN, SUBSTITUTE |
| T | TABLES, TABLE_NAME, TAG, TEMPORARY, TIES, TIMESTAMPADD, TIMESTAMPDIFF, TIMESTAMP_DIFF, TIMESTAMP_TRUNC, TIME_DIFF, TIME_TRUNC, TOP_LEVEL_COUNT, TRANSACTION, TRANSACTIONS_ACTIVE, TRANSACTIONS_COMMITTED, TRANSACTIONS_ROLLED_BACK, TRANSFORM, TRANSFORMS, TRASH, TRIGGER_CATALOG, TRIGGER_NAME, TRIGGER_SCHEMA, TTL, TUMBLE, TYPE |
| U | UNBOUNDED, UNCOMMITTED, UNCONDITIONAL, UNDER, UNMAP, UNNAMED, UNPIVOT, USAGE, USE, USERS, USER_DEFINED_TYPE_CATALOG, USER_DEFINED_TYPE_CODE, USER_DEFINED_TYPE_NAME, USER_DEFINED_TYPE_SCHEMA, UTF16, UTF32, UTF8 |
| V | VARIABLES, VERSION, VIEW, WEEK, WEEKS |
| W | WILDCARD, WORK, WRAPPER, WRITE |
| X | XML |
| Y | YEARS |
| Z | ZONE |