SQL commands usage

Types

• INT
• FLOAT
• STRING (aliases: VARCHAR, TEXT)
• NULL

Operators

• =, !=, <, >, <=, >=

String literals

Both single and double quotes are accepted:

'hello world'
"hello world"

Multi-statement queries

Statements can be chained in a single request using ; as separator:

INSERT INTO users VALUES (1, 'Alice'); INSERT INTO users VALUES (2, 'Bob');

Table definitions

Create a table:

CREATE TABLE `table_name` (
    `col_1_name` [TYPE] [PRIMARY KEY],
    `col_2_name` [TYPE] [REFERENCES `other_table`(`other_col`)],
    ...
)

A column marked REFERENCES declares a foreign-key relationship used for JOIN queries.

Delete a table:

DROP TABLE `table_name`

List all tables stored:

LIST TABLE

Update a table schema:

Not implemented yet.

Table content manipulation

Insert data into a table:

Parentheses around the value list are optional.

INSERT INTO `table_name` VALUES (`value1`, `value2`, ...)
INSERT INTO `table_name` VALUES  `value1`, `value2`, ...

Select data from a table:

SELECT `col_1_name`, `col_2_name`, ...
FROM `table_name`
WHERE `col_x_name` [OPERATOR] `value`

Use * to select all columns:

SELECT * FROM `table_name`

Join two tables:

SELECT `t1`.`col_a`, `t2`.`col_b`
FROM `table_1` JOIN `table_2` ON `table_1`.`col` = `table_2`.`col`
WHERE `t1`.`col_x` [OPERATOR] `value`

Column references in ON and WHERE clauses accept the table.column dotted notation.

Update data of a table:

Not implemented yet.

Delete data from a table:

DELETE FROM `table_name`
WHERE `col_x_name` [OPERATOR] `value`

Example

The file examples/simple-test.sql demonstrates table creation with foreign keys and a join query:

LIST TABLE;
 
CREATE TABLE table_1 (id INT PRIMARY KEY, name STRING);
 
CREATE TABLE table_2 (id INT PRIMARY KEY, t1Id INT REFERENCES table_1(id), addr STRING);
 
LIST TABLE;
 
INSERT INTO table_1 VALUES (1, "name1");
INSERT INTO table_1 VALUES (2, "name2");
 
INSERT INTO table_2 VALUES (1, 1, "addr1_of_name1");
INSERT INTO table_2 VALUES (2, 1, "addr2_of_name1");
INSERT INTO table_2 VALUES (3, 2, "addr1_of_name2");
 
SELECT * FROM table_1;
SELECT * FROM table_2;
 
SELECT table_1.name, table_2.addr
FROM table_1 JOIN table_2 ON table_1.id = table_2.t1Id;

This file can be executed directly from the CLI using:

!file examples/simple-test.sql

CLI commands

Command	Description
!help	Print the command reference
!file <path.sql>	Execute all queries from a SQL file
exit / quit	Close the connection