SQLite常用SQL命令

~~~~~關於SQLite的一些常用的SQL命令~~~~~~~~~~~

 

建立資料庫表：

 

CREATE TABLE IF NOT EXISTS

table-name ( column-def1, column-def2 )

 

table-name：指的是資料庫表的名稱

column-def：指的是表的列定義

其中 column-def又分為： column-name type-name column-constraint

type-name：常用的有 INTEGER / VARCHAR

其中column-constraint：常用的形式有 PRIMARY KEY

--------------------------------------------------------------------

向現有的資料庫表格中插入記錄：

 

INSERT INTO

table-name (column-name1,column-name2) VALUES ( expr1,expr2)

------------------------------------------------------------------------

從現有的資料庫中刪除記錄:

 

DELETE FROM

squalified-table-name WHERE expr

-------------------------------------------------------------------------

 

刪除資料庫的表：

 

DROP TABLE IF EXISTS table-name

 

說明：The DROP TABLE statement removes a table added with the CREATE TABLE statement. The name specified is the table name. The dropped table is completely removed from the database schema and the disk file. The table can not be recovered. All indices and triggers associated with the table are also deleted.

 

------------------------------------------------------------------------------

 

在現有的資料庫表中進行修改記錄：

 

UPDATE qualified-table-name SET column-name = expr WHERE expr

 

 

說明：The UPDATE statement is used to change the value of columns in selected rows of a table. Each assignment in an UPDATE specifies a column name to the left of the equals sign and an arbitrary expression to the right. The expressions may use the values of other columns. All expressions are evaluated before any assignments are made. A WHERE clause can be used to restrict which rows are updated.

 

------------------------------------------------------------------------------

 

從現有的表格中選擇合格記錄：

 

SELECT (DISTINCT/ALL) FROM join-source WHERE expr GROUP-BY ordering-term HAING expr ORDER BY ordering-term