-
Notifications
You must be signed in to change notification settings - Fork 4
SQL syntax
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 can be used on any place where values allowed. You can use the follow syntax:
| 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 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.
CREATE DATABASE database_name
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.
CREATE DATABASE c:\MyDatabase CREATE DATABASE ../MyDatabase CREATE DATABASE '../My Database'
Delete a database.
DELETE DATABASE database_name
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.
Change the current database context. This is equals to the JDBC API method connection.setCatalog(x).
USE database_name
database_name
The name of the new database. The name is identical to the name of CREATE DATABASE.
Create a new Table.
CREATE TABLE table_name ( <column_def> [,...n] )
<column_def> ::= { column_name data type } [ DEFAULT constant_expression ] [ IDENTITY ] [ NULL | NOT NULL ]
table_name
The name of the new table.
Delete a table definition and all data.
DROP`` TABLE table_name
table_name
The name of the table.
This clause request data from the database. The selection can include one or more columns or rows.
SELECT <column_def> [,...n] FROM <from list> [WHERE <where expression>] [[GROUP BY <group list> [HAVING <having expression>]] [ORDER BY <order list>]
Add a row to a table or view.
INSERT INTO <tablename> [(col1,coll2[,...n])] VALUES(val1,val2[,...n])