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.

 

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'
0xee598861897b22c12ed312aa54b6ff22
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

 


100% pure Java DBMS
Mirror Site