The Txt-Db-Api does not support all SQL keywords/statements nor the SQL Standard.
On this Page the sql syntax for the txt-db-api and its limitations are listed.
Everything in [] is optional, "..." means that any
more elements can follow,
| means OR (AND|OR => you can put AND or OR at this place).
As in Standard SQL: Do always enclose values for str (String) fields in quotes (' ' or " ") !
SELECT [DISTINCT]
[table_name.]field1 [AS alias_name] [, [table_name].field2 [AS alias_name] , ...]
FROM tablex [AS table_alias] [, tabley [AS table_alias], ... ]
[WHERE field=value AND|OR field2=field3 AND|OR ...] [GROUP BY column_name, ...] [ORDER BY column_name [ASC | DESC] ,...] [LIMIT [offset,]rows [;]
- look at the syntax...
- LIKE (use % as wildcard)
- * instead of field names
- table.*, combinations are also allowed, for example: SELECT table1.*,
people.Name, table3.* ...
- In the WHERE part, the following operators are supported: =,>,<,>=,<=,
!=, <>
- In the WHERE part, you can use field=value or field=field conditions (to
query tables linked, e.g. "person.typ_id = types.id",
for field=field conditions all the above listed operators can
be used)
- In the WHERE part you can use countless AND's and OR's
- In the WHERE part brackets can be used, and also be nested
- LIMIT: only rows Rows are returned, starting with offset (The
first Row has offset 0!)
- DISTINCT: only unique rows are returned
- GROUP BY: the result will be grouped by this columns
- Aliases for Tables
- When using DISTINCT and GROUP BY always the first value is taken
Example: "SELECT time, ip FROM log GROUP BY time"
time | ip 10348395 | 192.168.0.1 10348395 | 192.168.0.3 |
>
|
time | ip 10348395 | 192.168.0.1 |
- All functions like MAX(), MIN() etc.
- Appending of Fields like SELECT name & " " & prename
AS whole_name or
SELECT name + prename AS whole_name
- Everything not found under Syntax or supported !
SELECT prename, person.name, types.typ AS person_typ FROM person, types
WHERE person.typ_id = types.id AND (person.Nr>=10 OR person.prename='Hans')
ORDER BY prename
ATTENTION: Before and after each OR or AND must be a Space ("
"). This is
requied for the parser, that can distinguish between field names containing
OR or AND as part of it, or real OR or AND's. The same applies for LIKE.
Examples:
Wrong: Name='Test'OR Nr>20
Right: Name='Test' OR Nr>20
ATTENTION: Use LIKE only if it's really necessary. Normal String
compares are much
faster then LIKE. For example, if you want to query all records where the
name is exactly
'tom', do it like this: SELECT * FROM table WHERE name='tom'
AND NOT: SELECT * FROM table WHERE name LIKE 'tom'
AND ALSO NOT: SELECT * FROM table WHERE name LIKE '%tom%'
ATTENTION: At the moment you can only use fields with ORDER BY which
are also listed after
SELECT!
INSERT [INTO] table [(column1, column2, column_n, ...)]
VALUES (value [,value2, value_n, ...]) [;] oder
INSERT [INTO] table
SET column=value [,column2=valuet2, column_n=value_n, ...] [;]
- Look at syntax
INSERT INTO person(prename, name) VALUES ('hans','meier');
DELETE FROM table [WHERE <same as in SELECT>] [;]
- Look at Syntax
- Deleting the whole Table (drop the WHERE part, for example: "DELETE
FROM tableXY;")
- LIMIT
- Same restrictions for the WHERE part as by SELECT
- Everything not found in the Syntax above..
DELETE FROM person WHERE prename='Hans';
UPDATE table SET column=value [,column2=value2, column_n=value_n, ...]
[WHERE <same as in SELECT>] [;]
- Look at Syntax..
- Update the whole Table (drop the WHERE part)
- LIMIT
- ORDER BY
- Same restrictions for the WHERE part as by SELECT
- Everything not found in the Syntax above..
UPDATE person SET prename='Hans2000' WHERE prename='Hans';
CREATE TABLE (column_name type [DEFAULT 'defaultValue'] [, column_name2 type2 [DEFAULT 'defaultValue'] , ... ]) [;]
- Look at Syntax
- DEFAULT (Enclose the Default-Value in ' ')
- ONLY the following Synatx: (column_name type , column_name type ,
....), and the Keyword DEFAULT, and thats all !
- If you specify an Default-Value for an inc column, the first records
inc values will be set to DefaultValue+1,
and for all other Records the Default-Value is ignored.
- Everything not found under Syntax !!
- SQL Standard types, type can only be inc (Autoincrement), int (Number)
or str (String,Text).
CREATE TABLE people (id inc, prename str DEFAULT 'john', name str);
DROP TABLE table1 [, table2, ...][;]
- Look at Syntax
- Drop 1 or more tables in one statement
- Everything not found under Syntax !!
DTOP TABLE people;
LIST TABLES [WHERE ...] [GROUP BY spalten_name, ...] [ORDER BY spalten_name [ASC | DESC] ,...] [LIMIT [offset,]anzahl] [;]
- Look at Syntax
- Returns a ResultSet Object containing one Column with the name "table",
which contains all Table names of the Database as String.
- For the WHERE, GROUP BY, ORDER BY and LIMIT syntax see SELECT
- Everything not found under Syntax !!
LIST TABLES;
CREATE DATABASE database_name [;]
- Look at Syntax
- Only one Database can be created in one Statement.
- This Statement should only be executed on a ROOT_DATABASE Database!
You can get an instance of it with a $db=new Database(ROOT_DATABASE);
call.
ROOT_DATABASE is a defined constant.
- Everything not found under Syntax !!
CREATE DATABASE myDatabase;
DROP DATABASE database_name [;]
- Look at Syntax
- Only one Database can be deleted in one Statement.
- This Statement should only be executed on a ROOT_DATABASE Database!
You can get an instance of it with a $db=new Database(ROOT_DATABASE);
call.
ROOT_DATABASE is a defined constant.
- Everything not found under Syntax !!
DROP DATABASE myDatabase;