結構定義

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

VIEW

創建一個視圖(虛擬表),該表以另一種方式表示一個或多個表中的數據。

語法:

sql-command ::=

CREATE [ TEMP | TEMPORARY ]

VIEW [database-name . ] view-name AS select-statement


例子:
CREATE VIEW master_view AS
SELECT * FROM sqlite_master

WHERE type='view';

說明:創建一個名為master_view的視圖,

其中包括sqlite_master這個表中的所有視圖表。

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 TRIGGER update_customer_address

UPDATE OF address ON customers
BEGIN
UPDATE orders SET address = new.address WHERE customer_name = old.name;
END;

說明:創建了一個名為update_customer_address的觸發器,

當用戶更新customers表中的address字段時,

將觸發並更新orders表中的address字段為新的值。

比如執行如下一條語句:

UPDATE customers SET address = '1 Main St.'

WHERE name = 'Jack Jones';

數據庫將自動執行如下語句:

UPDATE orders SET address = '1 Main St.'

WHERE customer_name = 'Jack Jones' ;

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 INDEX idx_email ON customers (email); 說明:為customers表中的email創建一個名為idx_email的字段。

結構刪除

DROP

TABLE

刪除表定義及該表的所有索引。

語法:

sql-command ::=

DROP TABLE [database-name . ] table-name

例子:
DROP TABLE customers;

DROP

VIEW

刪除一個視圖。

語法:

sql-command ::=

DROP VIEW view-name

例子:
DROP VIEW master_view;

DROP

TRIGGER

刪除一個觸發器。

語法:

sql-statement ::=

DROP TRIGGER [database-name . ] trigger-name

例子:
DROP TRIGGER update_customer_address;

DROP

INDEX

刪除一個索引。

語法:

sql-command ::=

DROP INDEX [database-name . ] index-name

例子:
DROP INDEX idx_email;

數據操作

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

UPDATE

更新表中的現有數據。

語法:

sql-statement ::=

UPDATE [ OR conflict-algorithm ] [database-name . ] table-name SET assignment [ ,assignment
]*
[ WHERE expr]

assignment ::=

column-name = expr

DELETE

從表中刪除行。

語法:

sql-statement ::=

DELETE FROM [database-name . ] table-name [ WHERE expr ]

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

REPLACE

類似INSERT

語法:

sql-statement ::=

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

事務處理

BEGIN TRANSACTION

標記一個事務的起始點。

語法:

sql-statement ::=

BEGIN [ TRANSACTION [name]]

END TRANSACTION

標記一個事務的終止。

語法:

sql-statement ::=

END [ TRANSACTION [name]]

COMMIT TRANSACTION

標誌一個事務的結束。

語法:

sql-statement ::=

COMMIT [ TRANSACTION [name]]

ROLLBACK TRANSACTION

將事務回滾到事務的起點。

語法:

sql-statement ::=

ROLLBACK [ TRANSACTION [name]]

其他操作

COPY

主要用於導入大量的數據。

語法:

sql-statement ::=

COPY [ OR conflict-algorithm ] [database-name . ] table-name FROM filename [ USING DELIMITERS delim ]

例子:
COPY customers FROM customers.csv;

EXPLAIN

語法:

sql-statement ::=

EXPLAIN sql-statement

PRAGMA

語法:

sql-statement ::=

PRAGMA name [ = value] | PRAGMA function ( arg )

VACUUM

語法:

sql-statement ::=

VACUUM [index-or-table-name]

ATTACH DATABASE

附加一個數據庫到當前的數據庫連接。

語法:

sql-statement ::=

ATTACH [ DATABASE ] database-filename AS database-name

DETTACH DATABASE

從當前的數據庫分離一個使用ATTACH DATABASE附加的數據庫。

語法:

sql-command ::=

DETACH [ DATABASE ] database-name

SQLite內建函數表

算術函數

abs(X)

返回給定數字表達式的絕對值。

max(X,Y[,...])

返回表達式的最大值。

min(X,Y[,...])

返回表達式的最小值。

random(*)

返回隨機數。

round(X[,Y])

返回數字表達式並四捨五入為指定的長度或精度。

字符處理函數

length(X)

返回給定字符串表達式的字符個數。

lower(X)

將大寫字符數據轉換為小寫字符數據後返回字符表達式。

upper(X)

返回將小寫字符數據轉換為大寫的字符表達式。

substr(X,Y,Z)

返回表達式的一部分。

randstr()

 

quote(A)

 

like(A,B)

確定給定的字符串是否與指定的模式匹配。

glob(A,B)

 

條件判斷函數

coalesce(X,Y[,...])

 

ifnull(X,Y)

 

nullif(X,Y)

 

集合函數

avg(X)

返回組中值的平均值。

count(X)

返回組中項目的數量。

max(X)

返回組中值的最大值。

min(X)

返回組中值的最小值。

sum(X)

返回表達式中所有值的和。

其他函數

typeof(X)

返回數據的類型。

last_insert_rowid()

返回最後插入的數據的ID。

sqlite_version(*)

返回SQLite的版本。

change_count()

返回受上一語句影響的行數。

last_statement_change_count()

 

 

arrow
arrow
    文章標籤
    SQLite
    全站熱搜

    Jimmy 發表在 痞客邦 留言(0) 人氣()