SelimSql

Database Management System

Main Page User Guide Download Java Code Android Contact

SelimSql Syntax

SelimSql jdbc connection driver supports the following data definition language(DDL), data manipulation language(DML) and query language statements.

Table Of Contents

Connection Driver Class And Url
Data Definition Statements
  -Create Table
  -Drop Table
  -Create Index
  -Drop Index
  -Rename Table
  -Alter Table
  -Create Sequence
  -Drop Sequence
  -Create User
  -Alter User Pass
  -Drop User
  -Grant Database
  -Grant Database Table

Data Manipulation Statements
  -Insert Record
  -Update Record
  -Delete Record

Select Statement
Sql Functions


Connection Driver Class And Url

Jdbc Driver Class selimsql.jdbc.DbDriver  
Jdbc Url jdbc:selimsql:<DbTypeOrOperation>:<DbPathName> DbTypeOrOperation: -file: Connect to a file based database. -//ServerIp:ServerPort(9933): Connect to host based database. -fileNewDatabase: Create a new database. -fileDropDatabase: Drop the database. DbPathName: Database location and name.
Example for connect: jdbc:selimsql:file:D:/SSqlData/Stok jdbc:selimsql://192.168.2.3:9933/MyDb
Example for new database: jdbc:selimsql:fileNewDatabase:D:/SSqlData/MyDb


Data Definition Statements

-Create Table

 CREATE TABLE <TableName> ( <ColumnDefinition> [, ...])

 ColumnDefinition:
 <ColumnName> <DataType> [(<ColumnSize>[, <Precision>]) [NOT NULL]]

DataType Java Equivalent Type
VARCHAR | CHAR | LONGCHAR java.lang.String
BIGINT java.lang.Long
INTEGER java.lang.Integer
SMALLINT java.lang.Short
BYTE java.lang.Byte
DECIMAL | NUMBER | MONEY java.lang.Double | java.math.BigDecimal
DATE java.sql.Date
TIMESTAMP java.sql.Timestamp
BLOB java.lang.byte[]
Example: CREATE TABLE Product (Id INTEGER NOT NULL, Name VARCHAR(20) NOT NULL, Description VARCHAR(50), Price DECIMAL(15, 2) NOT NULL, ProductDate DATE NOT NULL, Status CHAR NOT NULL, Comment LONGTEXT, Photo BLOB)

-Drop Table

DROP TABLE <TableName>

-Create Index

CREATE [UNIQUE] INDEX <IndexName> ON <TableName>(<ColumnName> [, ...])

Example1:
CREATE UNIQUE INDEX ProductPk ON Product(ProductId)

Example2:
CREATE INDEX ProductDateIdx ON Product(ProductDate, Status)

-Drop Index

DROP INDEX <IndexName>

-Rename Table

RENAME <TableName> TO <NewTableName>

-Alter Table

For now, altering the table structure is not supported.
You can follow alternative method to alter table:
a) Create a new table and index like this:
CREATE TABLE YourNewTableName
(Id INTEGER NOT NULL,
 Column2 ,
 Column3 ,
 ...
 )

CREATE UNIQUE INDEX YourNewTableNamePk ON YourNewTableName(Id)

b) Move records from your old table:
insert into YourNewTable
select id, column2, column3, ... , 'NewData' as NewColumn from YourOldTable

c) Delete old table:
Drop table YourOldTable

d) Rename new table as old table:
Rename YourNewTable to YourOldTable


-Create Sequence

Create SEQUENCE <SEQ_NAME> [STARTVALUE 1] [MINVALUE 1] [MAXVALUE 9999999999999999999] [INCREMENT 1] [CYCLE]

Example:
Create SEQUENCE Seq_Order_Id;

Look at other Sequence Functions


-Drop Sequence

Drop SEQUENCE <SEQ_NAME>

Example:
Drop SEQUENCE Seq_Order_Id;


-Create User

CREATE USER <UserName> BY PASSWORD <UserPassword>

-Alter User Pass

ALTER USER <UserName> BY PASSWORD <UserPassword>

-Drop User

DROP USER <UserName>

-Database Grants

GRANT grant1[,grant2,..] TO <UserName>

Database Grants:
ALL, NONE, BUILDTABLE, DROPTABLE, RENAMETABLE, BUILDUSER, DROPUSER, ALTERUSER

Example:
GRANT ALL TO admin

-Database Table Grants

GRANT grant1[,grant2,..] ON <TableName> TO <UserName>

Database Table Grants:
ALL, NONE, ALTERTABLE, BUILDINDEX, DROPINDEX, SELECT, INSERT, UPDATE, DELETE

Example:
GRANT Select,Update,Insert ON Customer TO admin



Data Manipulation Statements


-Insert Record

INSERT INTO <Table>[(<Column> [,...] )]
{VALUES(<Expression> [,...]) | <SelectStatement>};

Example1:
INSERT INTO Product(ProductId, Name, Description, Price, ProductDate, Status)
VALUES(1, 'Product1', null, 12.35, Today(), 'A');

Example2:
INSERT INTO Product
VALUES(2, 'Product2', null, 23.68, Today(-1), 'P');

Example3:
INSERT INTO Product
select * from ProductOther where ProductId <= 10;

-Update Record

UPDATE <Table> SET <Column> = <Expression> [, ...] [WHERE <Expression>];

Example:
UPDATE Product
SET Description = Name + ' Desc.',
    Price = Price * 0.95
WHERE Description IS NULL

-Delete Record

DELETE FROM <Table> [WHERE <Expression>]

Example:
DELETE FROM Product
WHERE Status = 'P'


Select Statement

SELECT { <SelectExpression> | <Table>.* | * } [, ...]
FROM <TableList>
[WHERE <Expression>]
[GROUP BY <Expression> [, ...]]
[ORDER BY <OrderExpression> [, ...]]

Expression:
<Condition> [{ OR | AND } <Condition>]

Condition:
{ <Value> [|| <Value>]
 | <Value> { = | < | <= | > | >= | <> | != } <Value>
 | <Value> IS [NOT] NULL
 | <Value> [NOT] LIKE <Value> }

Value:
[+ | -] { <Term> [{ + | - | * | / | || } <Term>]
 | ( <Condition> )
 | <function>([<parameter>] [,...])

Term:
{ 'string' | number | floatingpoint
 | [<Table>.]<Column> | TRUE | FALSE | NULL }


Example1:
select * from Product
where ProductId <= 2;

ProductId Name Description Price ProductDate Status
======= ====== ============ ====== ============ ====
1 Product1 Product1 Desc. 11,73 07.11.2011 A
2 Product2 Product2 Desc. 22,50 08.11.2011 P
Example2: SELECT ProductDate, count(ProductId) AS Number FROM Product GROUP BY ProductDate ORDER BY 1 DESC; Example3: select o.OrderId, o.OrderDate, p.Name as Product, o.OrderNumber, (o.OrderNumber * o.UnitPrice) as Price, (c.Name + ' ' + c.Surname) as Customer from Order o, Customer c, Product p where o.OrderDate >= DateFromStr('01.06.2010','dd.MM.yyyy') and o.OrderDate <= DateFromStr('27.10.2011','dd.MM.yyyy') and c.CustomerId = o.CustomerId and p.ProductId = o.ProductId order by 1, 2;


Sql Functions

Function Name Function Parameters Decription
Aggregate Functions
Count Scanned column or object of count Aggregate function. Example: Count(Id)
Sum Scanned column or object of sum Aggregate function. Example: Sum(Price)
Min Scanned column or object of min Aggregate function. Example: Min(OrderDate)
Max Scanned column or object of max Aggregate function. Example: Max(Number)
Datetime Functions
Today, CurDate Prm1 optional.
Date or DifDay
Current or different date.
Example1: Today() Example2: CurDate(); --Today Example3: CurDate(-1); --Yesterday
CurDateTime Prm1 optional.
Date or DifDay
Current or different datetime.
Example1: CurDateTime() Example2: CurDateTime(-1); --Yesterday time Example3: CurDateTime(ATimeStamp); --ATimeStamp time by no milisecond
Now, CurDateTimeStamp Prm1 optional.
DifDay
Current or different datetime stamp.
Example1: Now() Example2: CurDateTimeStamp(); --Now Example3: CurDateTimeStamp(-5/24); --5 hour before
Year, Month, Day,
Hour, Minute, Second
- Current Year, Month, Day, Hour, Minute, Second Example1: Year() Example2: Hour()
WeekDay Prm1:Number,
Prm2:Mod
Day of Week. 1:Monday, 2:Tuesday,.. 7:Sunday Example: WeekDay()
DateFromStr Prm1:DateStr,
Prm2:DateFormat optional
Date from string
Example: DateFromStr('01.06.2014 10:48:23','dd.MM.yyyy HH:mm:ss')
DateDifMonth Prm:DifMonth Different month date. Example: DateDifMonth(-3)
StrFromDate Prm1:Date,
Prm2:DateFormat optional
String from date
Example: StrFromDate(ProductDate,'dd/MM/yyyy'), StrFromDate(Now(-7),'yyyyMMdd HH:mm:ss')
StrFromNumber Prm1:Number,
Prm2:Format is optional
String from number
Example: StrFromNumber(23419.2865, '#0.00'); --Out: 23419.29 StrFromNumber(23419.2865, '#,##0.00'); --Out: 23,419.29
Text Functions
StrLen Prm:Text Length of text. Example: StrLen(Name)
Trim Prm:Text Trim the text. Example: Trim(Description)
SubStr Prm1:Text,
Prm2:StartPos,
Prm3:Length optional
Sub string from text.
Example: SubStr(DESCRIPTION, 1, 5)
Upper Prm:Text Upper the text. Example: Upper(Surname)
Lower Prm:Text Lower the text. Example: Lower(Address)
InStr Prm1:stSource,
Prm2:stSource, stLook
InStr search the stLook in stSource
InStrSet Prm1:stSourceSet,
Prm2:stLook
InStrSet search the stLook in stSourceSet
Example: InStrSet('1,3,5,7-9,11', '8')
IsNull Prm1:Object,
Prm2:NewValue
If Object is null, use NewValue. Example: IsNull(Status, 1)
IfValue Prm1:leftValue,
Prm2:condition,
Prm3:rightValue,
Prm4:thenValue,
Prm5:elseValue
IfValue check the condition, get thenValue or elseValue
Example: IfValue(OperationType, '=', '+', 'INCOME', 'EXPENSE')
CaseValue Prm1:Value,
Prm2:EqualValue1,
Prm3:ThenValue1,
[EqualValue2,ThenValue2,..]
PrmN:OtherValue
CaseValue check the equal values and get the then values or other value.
Example: CaseValue(MaleFemale, 'M', 'Male', 'F', 'Female', 'Unknown')
Various Functions
Mod Prm1:Number,
Prm2:Mod
Mod of the number. Example: Mod(Id, 3)
Round Prm1:Number,
Prm2:Precision is optional
Round the number.
Example: Round(413.2865); --413.29
Floor Prm1:Number,
Prm2:Precision is optional
Floor the number.
Example: Floor(413.2865, 3); --413.286
BinaryFromFile Prm:Url or FileName. Note: FileName is valid for local database. Read the data bytes from file or url.
Example: update Product set Photo =
BinaryFromFile('http://www.mysite.com/Note1.png') where id = 1
Sequence Functions
SequenceNextValue Prm: SequenceName Next Value of SequenceName. Example1: Select SequenceNextValue(SequenceName) From _DB_Sequence Where SequenceName = 'SEQ_ORDER_ID'; Example2: Insert Into Order(Id, Desc) Values(SequenceNextValue('SEQ_ORDER_ID'), 'Desc1');
SequenceCurrentValue Prm: SequenceName Current Value of SequenceName. Example: Select SequenceCurrentValue(SequenceName) From _DB_Sequence Where SequenceName = 'SEQ_ORDER_ID'
DateTime Generated Number Functions
CurDateNumber - Current date number. Example: CurDateNumber(); --20150930
CurDateTimeNumber - Current date time number. Example: CurDateTimeNumber(); --20150930115213
CurTimeNumber - Current time number. Example: CurTimeNumber(); --115306
CurDateTimeStampNumber - Current date time stamp number. Example: CurDateTimeStampNumber(); --20151001141304673
CurTimeStampNumber - Current time stamp number. Example: CurTimeStampNumber(); --140330215

Copyright © 2009 - 2017 SelimSql