Detailed reference documentation for NanoDB's SQL dialect.
- Data Types
- SQL Syntax
- SQL Statements
- Data Definition Language
- Data Manipulation Language
BOOLEAN: logical truth values, i.e. true and false.FLOAT: 64-bit signed floating-point numbers.INTEGER: 64-bit signed integer numbers.TEXT: UTF-8 encoded strings.NULL: unknown value (three-valued logic).
Identifiers and keywords must begin with a letter (a-z) or an underscore (_). Subsequent characters in an identifier or keyword can be letters, digits (0-9) or underscores. For example:
abc
local_x
_xyz9
Numeric constants are accepted in these general forms:
digits
digits.[digits]
where digits is one or more decimal digits (0-9).
These are some examples of valid numeric constants:
42
3.5
A string constant in SQL is an arbitrary sequence of characters bounded by single quotes ('), for
example 'This is a string'.
TRUE: the boolean true valueFALSE: the boolean false value
Unary operators:
+(prefix): identity, e.g. +1 yields 1-(prefix): negation, e.g. -2 yields -2
Binary operators:
+: addition, e.g. 1 + 2 = 3-: subtraction, e.g. 3 - 2 = 1*: multiplication, e.g. 3 * 2 = 6/: division, e.g. 6 / 2 = 3^: exponentiation, e.g. 2 ^ 4 = 16%: modulo, e.g. 8 % 3 = 2
Comparison operators:
=: equal!=: not equal>: greater than<: less than>=: greater or equal<=: less or equal
Logical operators:
AND: conjunctionOR: disjunction
| Precedence | Operator | Associativity |
|---|---|---|
| 7 | +, - (unary plus/minus) |
Right |
| 6 | ^ |
Right |
| 5 | *, /, % |
Left |
| 4 | +, - |
Left |
| 3 | =, !=, >, >=, <, <= |
Left |
| 2 | AND |
Left |
| 1 | OR |
Left |
CREATE DATABASE name
CREATE DATABASE will create a new database.
CREATE DATABASE films;
DROP DATABASE name
DROP DATABASE drops a database.
DROP DATABASE films;
CREATE TABLE table_name (
[ column_name data_type [ column_constraint [ ... ] ]
[, ... ]
)
where column_constraint is:
- NOT NULL
- NULL
- DEFAULT expr
- PRIMARY KEY
CREATE TABLE will create a new empty table.
CREATE TABLE films (
id INTEGER PRIMARY KEY,
code TEXT NOT NULL,
title TEXT NOT NULL,
is_active BOOLEAN DEFAULT FALSE,
);
DROP TABLE table_name
DROP TABLE removes tables from the database.
DROP TABLE films;
SELECT [ * | expression [ [ AS ] output_name [, ...] ] ]
[ FROM table_name [, ...] ]
[ WHERE predicate ]
[ ORDER BY order_expr [ ASC | DESC ] [, ...] ]
[ LIMIT count ]
[ OFFSET start ]
SELECT retrieves rows from zero or more tables.
SELECT id, title FROM films LIMIT 2;
id | title
----+----------------
1 | Meet Joe Black
2 | Seven
INSERT INTO table_name [ ( column_name [, ... ] ) ] VALUES ( expression [, ... ] ) [, ... ]
INSERT inserts new rows into a table.
INSERT INTO films (id, code, title, is_active) VALUES (1, 'UA502', 'Bananas', true);
UPDATE table_name SET column_name = expression [, ... ] [ WHERE predicate ]
UPDATE changes the values of the specified columns in all rows that satisfy the condition. Only the columns to be modified need be mentioned in the SET clause; columns not explicitly modified retain their previous values.
UPDATE films SET code = 'UW500' WHERE id = 42;
DELETE FROM table_name [ WHERE predicate ]
DELETE deletes rows that satisfy the WHERE clause from the specified table. If the WHERE clause is absent, the effect is to delete all rows in the table.
DELETE FROM films WHERE id = 10;