SmallSQL Database SQL Syntax Reference
Introduction
This documentation descript the implemented SQL Syntax of the SmallSQL database. The SQL Syntax of the SmallSQL database is a subset from ANSI SQL-92 and ANSI SQL-99. This reference is not a book to learning the SQL language.
- SQL Constant Expressions
- SQL Data types
- SQL Operators
- SQL Functions
- CREATE DATABASE
- DROP DATABASE
- USE
- CREATE TABLE
- DROP TABLE
- SELECT statement
- INSERT statement
- DELETE statement
Constant Expressions
Constant expressions can be used on any place where values allowed. You can use the follow syntax:
data type |
samples |
description |
| String | 'any string value' 'O'' Brien' |
Strings a quoted with a single quote. If string include a quote then you need to duplicate the quote |
| Binary | 0xAB 0x12ad 0x (empty binary) |
Binaries have the prefix 0x. Every byte is displayed with 2 hexadecimal digit. You can use lower and upper letter. |
| Bit | true false |
|
| Timestamp | {ts '2003-07-16 12:30:15.000'} | The format is yyyy-mm-dd hh:mm:ss.nnn. |
| Time | {t '12:30:15'} | The format is hh:mm:ss. |
| Date | {d '2003-07-16'} | The format is yyyy-mm-dd. |
| Integer | 1234 67 |
Integer are numbers with digits only without dicimal point. |
| Decimal | 12.34 | Decimal are numbers with a decimal point. |
| Double | 1.234E1 | Double are numbers with a exponent. |
| Money | $12 $12.34 |
Money have the currency symol as the prefix. |
| uniqueidentifier | '618859EE-7B89-C122-2ED3-12AA54B6FF22' |
A guid can write as quoted String or as binary with 16 byte. |
| any | NULL | A NULL value. |
CREATE DATABASE
Create a new database. A database in SmallSQL is saving as a directory with a master control file. If you want create the first database then you can connect to no database to execute this command.
Syntax
CREATE DATABASE database_name
Parameters
database_name
The name of the database. The name is identical to the name of the directory. The directory is created as sub directory to the current working directory. You can also specify a absolute or relative path. If the directory name include spaces then you need to quote the identifier.
Samples
CREATE DATABASE c:\MyDatabase CREATE DATABASE ../MyDatabase CREATE DATABASE '../My Database'
DROP DATABASE
Delete a database.
Syntax
DELETE DATABASE database_name
Parameters
database_name
The name of the database. SmallSQL is verify if the directory and the master file exists. Then it delete the directory with all files in it. The directory is search relative to the current directory. You can also specify a absolute path.
USE
Change the current database context. This is equals to the JDBC API method connection.setCatalog(x).
Syntax
USE database_name
Parameters
database_name
The name of the new database. The name is identical to the name of CREATE DATABASE.
CREATE TABLE
Create a new Table.
Syntax
CREATE TABLE table_name (
<column_def> [,...n]
)
<column_def> ::= { column_name data type } [ DEFAULT
constant_expression ] [ IDENTITY ] [ NULL | NOT NULL ]
Parameters
table_name
The name of the new table.
DROP TABLE
Delete a table definition and all data.
Syntax
DROP TABLE table_name
Parameters
table_name
The name of the table.
SELECT
This clause request data from the database. The selection can include one or more columns or rows.
Syntax
SELECT <column_def> [,...n]
FROM <from list>
[WHERE <where expression>]
[[GROUP BY <group list>
[HAVING <having expression>]]
[ORDER BY <order list>]
Parameters
INSERT
Add a row to a table or view.
Syntax
INSERT INTO <tablename> [(col1,coll2[,...n])] VALUES(val1,val2[,...n])
Parameters
DELETE
Remove rows from a table.
Syntax
DELETE
<tablename> WHERE <where expression>
Parameters