SQL Reference — MilanSQL

DDL

Data Definition Language

CREATE TABLE

Creates a new table with specified columns, types, constraints, and optional table inheritance.

Syntax

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name (
  col_name data_type [NOT NULL] [DEFAULT expr] [PRIMARY KEY] [UNIQUE] [AUTO_INCREMENT],
  ...,
  [CONSTRAINT name] [PRIMARY KEY (cols)] | [FOREIGN KEY (col) REFERENCES tbl(col)] | [CHECK (expr)]
) [INHERITS (parent_table)];

Example

SQL

CREATE TABLE employees (
  id      INT  PRIMARY KEY AUTO_INCREMENT,
  name    TEXT NOT NULL,
  dept    TEXT DEFAULT 'General',
  salary  REAL CHECK (salary > 0),
  manager INT  REFERENCES employees(id)
);

CREATE COLUMN TABLE

Creates a column-store table optimized for OLAP aggregation queries. Columnar layout enables faster SUM/AVG/COUNT/MIN/MAX scans.

Syntax

CREATE COLUMN TABLE table_name (
col_name data_type [NOT NULL] [DEFAULT expr],
...
);

Example

SQL

CREATE COLUMN TABLE sales_facts (
  id      INT  PRIMARY KEY,
  amount  REAL,
  region  TEXT,
  sale_dt DATE
);
-- Fast columnar aggregation:
SELECT region, SUM(amount) FROM sales_facts GROUP BY region;

CREATE INDEX

Creates a B-Tree, composite, full-text, or spatial index to accelerate lookups and joins.

Syntax

CREATE [UNIQUE] [FULLTEXT | SPATIAL] INDEX index_name
ON table_name (col1 [ASC|DESC], col2, ...);

Example

SQL

CREATE INDEX idx_emp_dept ON employees(dept);
CREATE UNIQUE INDEX idx_emp_email ON employees(email);
CREATE FULLTEXT INDEX idx_ft_desc ON products(description);
CREATE INDEX idx_composite ON orders(customer_id, order_date DESC);

CREATE VIEW / CREATE MATERIALIZED VIEW

Creates a named virtual table from a SELECT query. Materialized views persist the result for faster access.

Syntax

CREATE [OR REPLACE] VIEW view_name [(col_list)] AS select_stmt;
CREATE MATERIALIZED VIEW view_name AS select_stmt;
REFRESH MATERIALIZED VIEW view_name;

Example

SQL

CREATE VIEW high_earners AS
  SELECT name, salary FROM employees WHERE salary > 80000;

CREATE MATERIALIZED VIEW monthly_totals AS
  SELECT DATE_TRUNC('month', sale_dt) AS month,
         SUM(amount) AS total
  FROM sales_facts GROUP BY month;

CREATE PROCEDURE / CREATE FUNCTION

Creates a stored procedure or user-defined function with procedural logic, cursors, loops, and conditionals.

Syntax

CREATE [OR REPLACE] PROCEDURE proc_name([param type, ...])
BEGIN
statements;
END;

CREATE [OR REPLACE] FUNCTION func_name([param type]) RETURNS type
BEGIN
RETURN expr;
END;

Example

SQL

CREATE PROCEDURE give_raise(dept_name TEXT, pct REAL)
BEGIN
  UPDATE employees
  SET salary = salary * (1 + pct / 100)
  WHERE dept = dept_name;
END;

CALL give_raise('Engineering', 10);

DROP TABLE / DROP INDEX / DROP VIEW

Removes a table, index, or view and all associated data. Use IF EXISTS to avoid errors when the object does not exist.

Syntax

DROP TABLE [IF EXISTS] table_name [CASCADE];
DROP INDEX [IF EXISTS] index_name ON table_name;
DROP VIEW [IF EXISTS] view_name;
DROP PROCEDURE [IF EXISTS] proc_name;

Example

SQL

DROP TABLE IF EXISTS temp_staging;
DROP INDEX idx_emp_dept ON employees;
DROP VIEW IF EXISTS high_earners;

ALTER TABLE

Modifies an existing table — add/drop/rename columns, add constraints, enable CDC or partitioning.

Syntax

Example

SQL

ALTER TABLE employees ADD COLUMN hire_date DATE;
ALTER TABLE employees DROP COLUMN old_field;
ALTER TABLE orders ENABLE CDC;
ALTER TABLE employees ENABLE ROW LEVEL SECURITY;

TRUNCATE

Removes all rows from a table quickly without logging individual row deletions. Faster than DELETE with no WHERE clause.

Syntax

TRUNCATE [TABLE] table_name;

Example

SQL

TRUNCATE TABLE staging_data;
TRUNCATE temp_logs;

DML

Data Manipulation Language

SELECT

Retrieves rows from one or more tables. Supports WHERE, ORDER BY, LIMIT, GROUP BY, HAVING, all JOIN types, window functions, CTEs, UNION/INTERSECT/EXCEPT, and subqueries.

Full Syntax

[WITH [RECURSIVE] cte_name AS (select), ...]
SELECT [DISTINCT] expr [AS alias], ...
FROM table [AS alias]
[INNER|LEFT|RIGHT|FULL [OUTER]] JOIN table2 ON condition
[WHERE condition]
[GROUP BY col, ...]
[HAVING condition]
[WINDOW w AS (PARTITION BY col ORDER BY col)]
[ORDER BY col [ASC|DESC], ...]
[LIMIT n] [OFFSET n]
[FOR UPDATE]
[UNION | INTERSECT | EXCEPT [ALL] select]

Window Functions

SQL

SELECT name, dept, salary,
  RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rank,
  SUM(salary) OVER (PARTITION BY dept) AS dept_total,
  ROW_NUMBER() OVER () AS row_num,
  LAG(salary, 1) OVER (ORDER BY hire_date) AS prev_salary
FROM employees;

CTEs (Common Table Expressions)

SQL

WITH dept_avg AS (
  SELECT dept, AVG(salary) AS avg_sal
  FROM employees GROUP BY dept
),
RECURSIVE org_tree(id, name, depth) AS (
  SELECT id, name, 0 FROM employees WHERE manager IS NULL
  UNION ALL
  SELECT e.id, e.name, t.depth + 1
  FROM employees e JOIN org_tree t ON e.manager = t.id
)
SELECT * FROM org_tree ORDER BY depth;

UNION / INTERSECT / EXCEPT

SQL

SELECT id FROM customers
UNION ALL
SELECT id FROM suppliers;

SELECT name FROM employees
INTERSECT
SELECT name FROM contractors;

SELECT name FROM employees
EXCEPT
SELECT name FROM terminated;

INSERT INTO

Inserts one or more rows into a table. Supports VALUES, SELECT subquery, and ON DUPLICATE KEY UPDATE for upsert behavior.

Syntax

INSERT INTO table [(col1, col2, ...)]
VALUES (val1, val2, ...) [, (...), ...]
| SELECT ...
[ON DUPLICATE KEY UPDATE col = val, ...];

Example

SQL

INSERT INTO employees (name, dept, salary)
VALUES ('Alice', 'Engineering', 95000),
       ('Bob',   'Marketing',   72000);

-- Insert from SELECT
INSERT INTO archive SELECT * FROM employees WHERE hire_date < '2020-01-01';

-- Upsert
INSERT INTO counters (key, val) VALUES ('hits', 1)
ON DUPLICATE KEY UPDATE val = val + 1;

UPDATE

Modifies existing rows in a table, optionally joining other tables for correlated updates.

Syntax

UPDATE table [AS alias]
SET col1 = expr1, col2 = expr2, ...
[FROM other_table]
[WHERE condition];

Example

SQL

UPDATE employees
SET salary = salary * 1.1,
    dept   = 'Senior Engineering'
WHERE dept = 'Engineering' AND salary > 90000;

DELETE

Removes rows from a table matching a WHERE condition. Without WHERE, all rows are deleted.

Syntax

DELETE FROM table [WHERE condition];

Example

SQL

DELETE FROM employees WHERE id = 42;
DELETE FROM sessions WHERE created_at < '2024-01-01';

COPY FROM / COPY TO

Bulk-loads data from a CSV/TSV file into a table, or exports table rows to a file. The fastest way to import/export large datasets.

Syntax

COPY table [(col1, ...)] FROM 'filepath' [WITH (FORMAT CSV, HEADER, DELIMITER ',')];
COPY table TO 'filepath' [WITH (FORMAT CSV, HEADER)];

Example

SQL

COPY employees FROM '/data/employees.csv'
  WITH (FORMAT CSV, HEADER, DELIMITER ',');

COPY employees TO '/backup/employees_export.csv'
  WITH (FORMAT CSV, HEADER);

Transactions

Transaction Control

BEGIN / COMMIT / ROLLBACK

Controls transaction boundaries. MilanSQL uses MVCC (Multi-Version Concurrency Control) with WAL for ACID guarantees.

Syntax

BEGIN [TRANSACTION] [ISOLATION LEVEL {READ COMMITTED | REPEATABLE READ | SERIALIZABLE}];
COMMIT;
ROLLBACK;

Example

SQL

BEGIN;
  INSERT INTO orders (item, price) VALUES ('Widget', 9.99);
  UPDATE inventory SET qty = qty - 1 WHERE item = 'Widget';
COMMIT;

BEGIN;
  DELETE FROM orders WHERE id = 99;
ROLLBACK; -- undoes the delete

SAVEPOINT

Creates a named checkpoint within a transaction, enabling partial rollback without aborting the entire transaction.

Syntax

SAVEPOINT savepoint_name;

Example

SQL

BEGIN;
  INSERT INTO orders VALUES (1, 'Item A', 10.00);
  SAVEPOINT after_first_insert;
  INSERT INTO orders VALUES (2, 'Item B', 20.00);
  ROLLBACK TO SAVEPOINT after_first_insert; -- only Item B rolled back
COMMIT; -- Item A is committed

RELEASE SAVEPOINT

Removes a named savepoint, freeing associated resources. The transaction continues normally.

Syntax

RELEASE SAVEPOINT savepoint_name;

Example

SQL

SAVEPOINT sp1;
-- ... do work ...
RELEASE SAVEPOINT sp1; -- sp1 no longer available for rollback

ROLLBACK TO SAVEPOINT

Rolls back all changes made after the named savepoint, without aborting the enclosing transaction.

Syntax

ROLLBACK [TRANSACTION] TO SAVEPOINT savepoint_name;

Example

SQL

BEGIN;
  SAVEPOINT before_risky;
  UPDATE accounts SET balance = balance - 1000 WHERE id = 5;
  -- something went wrong
  ROLLBACK TO SAVEPOINT before_risky;
COMMIT;

Admin

Administration & Introspection

CREATE USER

Creates a new database user with an optional password for authentication.

Syntax

CREATE USER username [IDENTIFIED BY 'password'];

Example

SQL

CREATE USER alice IDENTIFIED BY 'secret123';
CREATE USER readonly_bot;

GRANT

Grants privileges on a table or database to a user or role.

Syntax

GRANT privilege [, ...] ON [TABLE] object TO user;

Example

SQL

GRANT SELECT, INSERT ON employees TO alice;
GRANT ALL ON orders TO admin_user;

REVOKE

Revokes previously granted privileges from a user.

Syntax

REVOKE privilege [, ...] ON [TABLE] object FROM user;

Example

SQL

REVOKE INSERT ON employees FROM alice;
REVOKE ALL ON orders FROM readonly_bot;

SHOW TABLES / SHOW COLUMNS / SHOW INDEXES

Lists tables in the current database, columns of a table, or indexes defined on a table.

Syntax

SHOW TABLES;
SHOW COLUMNS FROM table_name;
SHOW INDEXES FROM table_name;

Example

SQL

SHOW TABLES;
SHOW COLUMNS FROM employees;
SHOW INDEXES FROM orders;

DESCRIBE

Shows the column structure of a table — name, type, nullability, and default values.

Syntax

DESCRIBE table_name;
DESC table_name;

Example

SQL

DESCRIBE employees;
DESC orders;

EXPLAIN

Shows the query execution plan chosen by the optimizer — scan type, join algorithm, index usage, and estimated cost. Use EXPLAIN REWRITTEN to see optimizer rewrites.

Syntax

EXPLAIN select_stmt;
EXPLAIN REWRITTEN select_stmt;

Example

SQL

EXPLAIN SELECT * FROM employees WHERE dept = 'Engineering';
-- Output: Index Scan on idx_emp_dept (cost=0.12)

EXPLAIN REWRITTEN SELECT * FROM employees WHERE 1=1;
-- Rewrite: removed always-true condition WHERE 1=1

ANALYZE TABLE

Updates the optimizer's statistics for a table — row count, column cardinality, and histogram data for better query plans.

Syntax

ANALYZE TABLE table_name;

Example

SQL

ANALYZE TABLE employees;
ANALYZE TABLE sales_facts;

Special

Pub/Sub, Replication & Security

LISTEN / NOTIFY / UNLISTEN

PostgreSQL-compatible asynchronous pub/sub messaging. Sessions LISTEN on a named channel; any session can NOTIFY to broadcast messages to all listeners.

Syntax

LISTEN channel_name;
NOTIFY channel_name [, 'payload'];
UNLISTEN channel_name;

Example

SQL

-- Session A: subscribe
LISTEN order_events;

-- Session B: publish
NOTIFY order_events, '{"order_id": 42, "status": "shipped"}';

-- Session A: unsubscribe
UNLISTEN order_events;

CREATE PUBLICATION / CREATE SUBSCRIPTION

Sets up logical replication — a publisher exports a stream of row changes, and subscribers consume that stream to replicate data.

Syntax

CREATE PUBLICATION pub_name FOR TABLE table1 [, table2, ...];
CREATE SUBSCRIPTION sub_name
CONNECTION 'connection_string'
PUBLICATION pub_name;

Example

SQL

-- On primary:
CREATE PUBLICATION orders_pub FOR TABLE orders, order_items;

-- On replica:
CREATE SUBSCRIPTION orders_sub
  CONNECTION 'host=primary port=4406'
  PUBLICATION orders_pub;

CREATE EVENT

Schedules a SQL statement to run at a fixed time or on a recurring interval. MySQL-compatible event scheduler.

Syntax

CREATE EVENT event_name
ON SCHEDULE EVERY n {SECOND | MINUTE | HOUR | DAY}
| AT 'timestamp'
DO sql_statement;

Example

SQL

CREATE EVENT cleanup_sessions
  ON SCHEDULE EVERY 1 HOUR
  DO DELETE FROM sessions WHERE expires < NOW();

CREATE EVENT one_time_report
  ON SCHEDULE AT '2026-12-31 23:59:00'
  DO INSERT INTO reports SELECT * FROM year_summary;

ENABLE / DISABLE ROW LEVEL SECURITY

Enables or disables row-level security (RLS) on a table. When RLS is enabled, all access is filtered through policies unless the user is a superuser.

Syntax

ENABLE ROW LEVEL SECURITY ON table_name;
DISABLE ROW LEVEL SECURITY ON table_name;
-- or via ALTER TABLE:
ALTER TABLE table_name ENABLE ROW LEVEL SECURITY;

Example

SQL

ENABLE ROW LEVEL SECURITY ON orders;
DISABLE ROW LEVEL SECURITY ON public_catalog;

CREATE POLICY

Defines a row-level security policy for a table. The USING expression is evaluated per row; only rows where it returns true are visible to the target user.

Syntax

CREATE POLICY policy_name ON table_name
  FOR {SELECT | INSERT | UPDATE | DELETE | ALL}
  TO username
  USING (expr);

Example

SQL

CREATE POLICY own_orders ON orders
  FOR SELECT TO alice
  USING (customer_id = CURRENT_USER_ID());

CREATE POLICY dept_data ON employees
  FOR ALL TO manager
  USING (dept = CURRENT_USER_DEPT());

SET / SHOW

Configuration & Diagnostics

SET QUERY_REWRITE

Enables or disables the adaptive query rewriter, which automatically simplifies redundant conditions and rewrites suboptimal patterns.

Syntax

SET QUERY_REWRITE = {ON | OFF};

Example

SQL

SET QUERY_REWRITE = ON;
-- Now WHERE 1=1 is automatically removed,
-- redundant OR conditions are simplified, etc.

SET PARALLEL_THRESHOLD

Sets the minimum row count at which the parallel query executor kicks in, splitting work across multiple worker threads.

Syntax

SET PARALLEL_THRESHOLD = n;

Example

SQL

SET PARALLEL_THRESHOLD = 10000;
-- Queries touching >10,000 rows use parallel execution

SHOW QUERY STATS

Displays cumulative query execution statistics — total queries, cache hits, average latency, and slow query count.

Syntax

SHOW QUERY STATS;

Example

SQL

SHOW QUERY STATS;
-- total_queries: 1042
-- cache_hits: 387
-- avg_latency_ms: 0.74
-- slow_queries (>100ms): 3

SHOW INDEX SUGGESTIONS

Analyzes the adaptive optimizer's access statistics and recommends indexes based on actual query patterns observed at runtime.

Syntax

SHOW INDEX SUGGESTIONS;

Example

SQL

SHOW INDEX SUGGESTIONS;
-- CREATE INDEX idx_orders_customer ON orders(customer_id)  -- 91% filter rate
-- CREATE INDEX idx_emp_dept ON employees(dept)             -- 78% filter rate

SHOW ENGINE STATUS

Displays the current engine state — active transactions, buffer pool usage, WAL position, replication lag, and background worker status.

Syntax

SHOW ENGINE STATUS;

Example

SQL

SHOW ENGINE STATUS;
-- active_transactions: 2
-- buffer_pool_pages: 1024 / 4096
-- wal_lsn: 0x00041FA0
-- replication_lag_ms: 12
-- event_scheduler: running (3 events)