16.1.1 SQL Syntax Dictionary
The SQL Syntax Dictionary provides BNF notation and minimal examples for all SQL statements supported by Machbase.
Supported SQL Statements
| Statement | Category | Description |
|---|---|---|
| CREATE TABLE | DDL | Create LOG/TAG/LOOKUP/VOLATILE/TRANSACTION tables |
| DROP TABLE | DDL | Drop tables |
| ALTER TABLE | DDL | Change schemas: add/drop/modify/rename columns |
| TRUNCATE TABLE | DDL | Delete all table data |
| CREATE INDEX | DDL | Conditional creation and index support by table type |
| DROP INDEX | DDL | Drop indexes |
| CREATE ROLLUP | DDL | Create TAG ROLLUP definitions |
| DROP ROLLUP / ALTER ROLLUP | DDL | Delete and control ROLLUPs |
| CREATE RETENTION | DDL | Create retention policies |
| CREATE VIEW / DROP VIEW | DDL | Create and drop stored views |
| CREATE TABLESPACE | DDL | Create tablespaces |
| INSERT INTO | DML | Insert single or multiple rows |
| INSERT SELECT | DML | Insert query results into another table |
| UPDATE | DML | Modify TRANSACTION/LOOKUP/VOLATILE rows and correct TAG data with restricted predicates |
| DELETE | DML | Delete table data |
| LOAD DATA INFILE | DML | Load CSV files directly |
| SELECT | SELECT | Query data with JOIN, GROUP BY, ORDER BY, and LIMIT |
| WITH / CTE | SELECT | Nonrecursive common table expressions in Standard Edition |
| Named Bind Parameter | Common SQL | Value parameters in :name form |
| CAST | SQL expression | Explicitly convert values to a specified type |
| SAVE DATA INTO | SELECT | Save query results to CSV |
| BACKUP | Operations | Back up databases or tables |
| RESTORE | Operations | Logical database restore and offline restore using machadmin -r |
| MOUNT / UMOUNT DATABASE | Operations | Mount/unmount backup databases |
| CREATE USER / DROP USER / ALTER USER | Users | Create/drop users and change passwords |
| GRANT / REVOKE | Users | Grant and revoke privileges |
| AUTH KEY Management | Users | Register/manage public-key authentication keys |
| ALTER SYSTEM | System | Session control, PVO Cache flush, license installation, and more |
| ALTER SESSION | Session | Configure session parameters |
| PIVOT | Analysis | Convert rows to columns |
| WINDOW FUNCTION (OVER) | Analysis | Window functions and OVER |
| SERIES BY | Analysis | Group consecutive records meeting conditions |
| SEARCH / ESEARCH / REGEXP | Search | Keyword-index-based text search |
| ROLLUP REBUILD | Operations | Recalculate ROLLUP results |
| DATABASE | DDL/session | Create/select/drop logical databases and check status |
| AUTO_INCREMENT | DDL | Generate 64-bit PRIMARY KEY values automatically |
| EXEC procedure / SHOW ROLLUPGAP | Control | Table flush/refresh and ROLLUP control/status |
BNF Conventions
This dictionary uses the following Backus–Naur Form (BNF) conventions.
| Notation | Meaning |
|---|---|
'keyword' | SQL reserved word, case-insensitive |
name | User-defined name |
( A | B ) | Either A or B |
[ ... ] | Optional element |
( ... )* | Zero or more repetitions |
( ... )+ | One or more repetitions |
( ... )? | Zero or one occurrence |
Last updated on