SQLite sql命令

准备将它加入到TopicLive支持的数据库类型中.因此将一些资料放在这里备查.
主页:SQLite

ATTACH DATABASE

sql-statement ::= ATTACH [DATABASE] database-filename AS database-name

BEGIN TRANSACTION

sql-statement ::= BEGIN [ DEFERRED | IMMEDIATE | EXCLUSIVE ] [TRANSACTION [name]]
sql-statement ::= END [TRANSACTION [name]]

sql-statement ::=

COMMIT [TRANSACTION [name]]
sql-statement ::= ROLLBACK [TRANSACTION [name]]

comment

comment ::= SQL-comment | C-comment
SQL-comment ::= single-line
C-comment ::= /* multiple-lines [*/]

COPY

sql-statement ::= COPY [ OR conflict-algorithm ] [database-name .] table-name FROM filename

[ USING DELIMITERS delim ]

CREATE INDEX

sql-statement ::= CREATE [UNIQUE] INDEX index-name
ON
[database-name .] table-name ( column-name [, column-name]* )

[ ON CONFLICT conflict-algorithm ]

column-name ::= name [ COLLATE collation-name] [ ASC | DESC ]

CREATE TABLE

sql-command ::= CREATE [TEMP | TEMPORARY] TABLE table-name (

    column-def [, column-def]*
    
[, constraint]*
)

sql-command ::= CREATE [TEMP | TEMPORARY] TABLE [database-name.] table-name AS select-statement
column-def ::= name [type] [[CONSTRAINT name] column-constraint]*
type ::= typename |
typename ( number ) |

typename ( number , number )

column-constraint ::= NOT NULL [ conflict-clause ] |

PRIMARY KEY [sort-order] [ conflict-clause ] |
UNIQUE
[ conflict-clause ] |

CHECK ( expr ) [ conflict-clause ] |
DEFAULT
value |

COLLATE collation-name

constraint ::= PRIMARY KEY ( column-list ) [ conflict-clause ] |

UNIQUE ( column-list ) [ conflict-clause ] |
CHECK (
expr ) [ conflict-clause ]

conflict-clause ::= ON CONFLICT conflict-algorithm

CREATE TRIGGER

sql-statement ::= CREATE [TEMP | TEMPORARY] TRIGGER trigger-name [ BEFORE | AFTER ]

database-event ON [database-name .] table-name
trigger-action

sql-statement ::= CREATE [TEMP | TEMPORARY] TRIGGER trigger-name INSTEAD OF
database-event ON [database-name .] view-name

trigger-action

database-event ::= DELETE |
INSERT
|
UPDATE
|

UPDATE OF column-list

trigger-action ::= [ FOR EACH ROW | FOR EACH STATEMENT ] [ WHEN expression ]

BEGIN
    
trigger-step ; [ trigger-step ; ]*
END

trigger-step ::= update-statement | insert-statement |
delete-statement | select-statement

CREATE VIEW

sql-command ::= CREATE [TEMP | TEMPORARY] VIEW [database-name.] view-name AS select-statement

DELETE

sql-statement ::= DELETE FROM [database-name .] table-name [WHERE expr]

DETACH DATABASE

sql-command ::= DETACH [DATABASE] database-name

DROP INDEX

sql-command ::= DROP INDEX [database-name .] index-name

DROP TABLE

sql-command ::= DROP TABLE [database-name.] table-name

DROP TRIGGER

sql-statement ::= DROP TRIGGER [database-name .] trigger-name

DROP VIEW

sql-command ::= DROP VIEW view-name

EXPLAIN

sql-statement ::= EXPLAIN sql-statement

expression

expr ::= expr binary-op expr |

expr like-op expr |
unary-op expr |
(
expr ) |

column-name |
table-name . column-name |
database-name . table-name . column-name |

literal-value |
function-name ( expr-list | * ) |

expr ISNULL |
expr NOTNULL |
expr [NOT] BETWEEN expr AND expr |

expr [NOT] IN ( value-list ) |
expr [NOT] IN ( select-statement ) |

expr [NOT] IN [database-name .] table-name |

( select-statement ) |
CASE
[expr] ( WHEN expr THEN expr )+ [ELSE expr] END

like-op ::= LIKE | GLOB | NOT LIKE | NOT GLOB

INSERT

sql-statement ::= INSERT [OR conflict-algorithm] INTO [database-name .] table-name [(column-list)] VALUES(value-list) |

INSERT [OR conflict-algorithm] INTO [database-name .] table-name [(column-list)] select-statement

ON CONFLICT clause

conflict-clause ::= ON CONFLICT conflict-algorithm
conflict-algorithm ::= ROLLBACK | ABORT | FAIL | IGNORE | REPLACE

REPLACE

sql-statement ::= REPLACE INTO [database-name .] table-name [( column-list )] VALUES ( value-list ) |

REPLACE INTO [database-name .] table-name [( column-list )] select-statement

SELECT

sql-statement ::=

SELECT [ALL | DISTINCT] result [FROM table-list]

[WHERE expr]
[GROUP BY expr-list]
[HAVING expr]
[compound-op select]*

[ORDER BY sort-expr-list]
[LIMIT integer [( OFFSET | , ) integer]]

result ::= result-column [, result-column]*
result-column ::= * | table-name . * | expr [ [AS] string ]
table-list ::= table [join-op table join-args]*
table ::= table-name [AS alias] |
(
select ) [AS alias]
join-op ::= , | [NATURAL] [LEFT | RIGHT | FULL] [OUTER | INNER | CROSS] JOIN
join-args ::= [ON expr] [USING ( id-list )]
sort-expr-list ::= expr [sort-order] [, expr [sort-order]]*
sort-order ::= [ COLLATE collation-name ] [ ASC | DESC ]
compound_op ::= UNION | UNION ALL | INTERSECT | EXCEPT

UPDATE

sql-statement ::= UPDATE [ OR conflict-algorithm ] [database-name .] table-name

SET assignment [, assignment]*
[WHERE expr]

assignment ::= column-name = expr

VACUUM

sql-statement ::= VACUUM [index-or-table-name]

发表评论