SQLite教程（三）：資料表和視圖簡介，sqlite視圖

SQLite教程（三）：資料表和視圖簡介，sqlite視圖

一、建立資料表:

    該命令的文法規則和使用方式與大多數關係型資料庫基本相同，因此我們還是以樣本的方式來示範SQLite中建立表的各種規則。但是對於一些SQLite特有的規則，我們會給予額外的說明。註：以下所有樣本均是在sqlite內建命令列工具中完成的。

    1). 最簡單的資料表：
 複製代碼 代碼如下:
    sqlite> CREATE TABLE testtable (first_col integer);
 
    這裡需要說明的是，對於自訂資料表表名，如testtable，不能以sqlite_開頭，因為以該首碼定義的表名都用於sqlite內部。

    2). 建立帶有預設值的資料表：
 複製代碼 代碼如下:
    sqlite> CREATE TABLE testtable (first_col integer DEFAULT 0, second_col varchar DEFAULT 'hello');

    3). 在指定資料庫建立表：
 複製代碼 代碼如下:
    sqlite> ATTACH DATABASE 'd:/mydb.db' AS mydb;
    sqlite> CREATE TABLE mydb.testtable (first_col integer);
 
    這裡先通過ATTACH DATABASE命令將一個已經存在的資料庫檔案attach到當前的串連中，之後再通過指定資料庫名的方式在目標資料庫中建立資料表，如mydb.testtable。關於該規則還需要給出一些額外的說明，如果我們在建立資料表時沒有指定資料庫名，那麼將會在當前串連的main資料庫中建立該表，在一個串連中只能有一個main資料庫。如果需要建立暫存資料表，就無需指定資料庫名，見如下樣本：
    --建立兩個表，一個暫存資料表和普通表。
 複製代碼 代碼如下:
    sqlite> CREATE TEMP TABLE temptable(first_col integer);
    sqlite> CREATE TABLE testtable (first_col integer);   
 
    --將當前串連中的快取資料匯出到本地檔案，同時退出當前串連。
 複製代碼 代碼如下:
    sqlite> .backup d:/mydb.db
    sqlite> .exit
 
    --重建立立sqlite的串連，並將剛剛匯出的資料庫作為主庫重新匯入。
 複製代碼 代碼如下:
    sqlite> .restore d:/mydb.db
 
    --查看該資料庫中的表資訊，通過結果可以看出暫存資料表並沒有被持久化到資料庫檔案中。
 複製代碼 代碼如下:
    sqlite> .tables
    testtable   

    4). "IF NOT EXISTS"從句：
    如果當前建立的資料表名已經存在，即與已經存在的表名、視圖名和索引名衝突，那麼本次建立操作將失敗並報錯。然而如果在建立表時加上"IF NOT EXISTS"從句，那麼本次建立操作將不會有任何影響，即不會有錯誤拋出，除非當前的表名和某一索引名衝突。
 複製代碼 代碼如下:
    sqlite> CREATE TABLE testtable (first_col integer);
    Error: table testtable already exists
    sqlite> CREATE TABLE IF NOT EXISTS testtable (first_col integer);

    5). CREATE TABLE ... AS SELECT：
    通過該方式建立的資料表將與SELECT查詢返回的結果集具有相同的Schema資訊，但是不包含預設值和主鍵等約束資訊。然而新建立的表將會包含結果集返回的所有資料。
 複製代碼 代碼如下:
    sqlite> CREATE TABLE testtable2 AS SELECT * FROM testtable;   
    sqlite> .schema testtable2
    CREATE TABLE testtable2(first_col INT);
 
    .schema命令是sqlite3命令列工具的內建命令，用於顯示當前資料表的CREATE TABLE語句。   

    6). 主鍵約束：
 複製代碼 代碼如下:
    --直接在欄位的定義上指定主鍵。
    sqlite> CREATE TABLE testtable (first_col integer PRIMARY KEY ASC);
    --在所有欄位已經定義完畢後，再定義表的數約束，這裡定義的是基於first_col和second_col的聯合主鍵。
    sqlite> CREATE TABLE testtable2 (
       ...>     first_col integer,
       ...>     second_col integer,
       ...>     PRIMARY KEY (first_col,second_col)
       ...> );

    和其他關係型資料庫一樣，主鍵必須是唯一的。

    7). 唯一性限制式：
 複製代碼 代碼如下:
    --直接在欄位的定義上指定唯一性限制式。
    sqlite> CREATE TABLE testtable (first_col integer UNIQUE);
    --在所有欄位已經定義完畢後，在定義表的唯一性限制式，這裡定義的是基於兩個列的唯一性限制式。
    sqlite> CREATE TABLE testtable2 (
       ...>     first_col integer,
       ...>     second_col integer,
       ...>     UNIQUE (first_col,second_col)
       ...> );   
    在SQLite中，NULL值被視為和其他任何值都是不同的，這樣包括和其他的NULL值，如下例：
    sqlite> DELETE FROM testtable;
    sqlite> SELECT count(*) FROM testtable;
    count(*)
    ----------
    0
    sqlite> INSERT INTO testtable VALUES(NULL);
    sqlite> INSERT INTO testtable VALUES(NULL);
    sqlite> SELECT count(*) FROM testtable;
    count(*)
    ----------
    2  
 
    由此可見，兩次插入的NULL值均插入成功。

    8). 為空白(NOT NULL)約束：
 複製代碼 代碼如下:
    sqlite> CREATE TABLE testtable(first_col integer NOT NULL);
    sqlite> INSERT INTO testtable VALUES(NULL);
    Error: testtable.first_col may not be NULL
 
    從輸出結果可以看出，first_col已經被定義了非空約束，因此不能在插入NULL值了。   

    9). 檢查性約束：
 複製代碼 代碼如下:
    sqlite> CREATE TABLE testtable (first_col integer CHECK (first_col < 5));
    sqlite> INSERT INTO testtable VALUES(4);
    sqlite> INSERT INTO testtable VALUES(20); -- 20違反了欄位first_col的檢查性約束(first_col < 5)
    Error: constraint failed
    --和之前的其它約束一樣，檢查性約束也是可以基於表中的多個列來定義的。   
    sqlite> CREATE TABLE testtable2 (
       ...>     first_col integer,
       ...>     second_col integer,
       ...>     CHECK (first_col > 0 AND second_col < 0)
       ...> );

二、表的修改：

    SQLite對ALTER TABLE命令支援的非常有限，僅僅是修改表名和添加新欄位。其它的功能，如重新命名欄位、刪除欄位和添加刪除約束等均為提供支援。

    1). 修改表名：

    需要先說明的是，SQLite中表名的修改只能在同一個資料庫中，不能將其移動到Attached資料庫中。再有就是一旦表名被修改後，該表已存在的索引將不會受到影響，然而依賴該表的視圖和觸發器將不得不重新修改其定義。
 複製代碼 代碼如下:
    sqlite> CREATE TABLE testtable (first_col integer);
    sqlite> ALTER TABLE testtable RENAME TO testtable2;
    sqlite> .tables
    testtable2   
 
    通過.tables命令的輸出可以看出，表testtable已經被修改為testtable2。   

    2). 新增欄位：
 複製代碼 代碼如下:
    sqlite> CREATE TABLE testtable (first_col integer);
    sqlite> ALTER TABLE testtable ADD COLUMN second_col integer;
    sqlite> .schema testtable
    CREATE TABLE "testtable" (first_col integer, second_col integer);
 
    通過.schema命令的輸出可以看出，表testtable的定義中已經包含了新增欄位。   
    關於ALTER TABLE最後需要說明的是，在SQLite中該命令的執行時間是不會受到當前表行數的影響，也就是說，修改有一千萬行資料的表和修改只有一條資料的表所需的時間幾乎是相等的。
   
三、表的刪除：

    在SQLite中如果某個表被刪除了，那麼與之相關的索引和觸發器也會被隨之刪除。在很多其他的關係型資料庫中是不可以這樣的，如果必須要刪除相關對象，只能在刪除表語句中加入WITH CASCADE從句。見如下樣本：
 複製代碼 代碼如下:
    sqlite> CREATE TABLE testtable (first_col integer);
    sqlite> DROP TABLE testtable;
    sqlite> DROP TABLE testtable;
    Error: no such table: testtable
    sqlite> DROP TABLE IF EXISTS testtable; 
 
    從上面的樣本中可以看出，如果刪除的表不存在，SQLite將會報錯並輸出錯誤資訊。如果希望在執行時不拋出異常，我們可以添加IF EXISTS從句，該從句的語義和CREATE TABLE中的完全相同。
   
四、建立視圖：

    我們這裡只是給出簡單的SQL命令樣本，具體的含義和技術細節可以參照上面的建立資料表部分，如臨時視圖、"IF NOT EXISTS"從句等。
    1). 最簡單的視圖：
 複製代碼 代碼如下:
    sqlite> CREATE VIEW testview AS SELECT * FROM testtable WHERE first_col > 100;   
   
    2). 建立臨時視圖：
 複製代碼 代碼如下:
    sqlite> CREATE TEMP VIEW tempview AS SELECT * FROM testtable WHERE first_col > 100;
   
    3). "IF NOT EXISTS"從句：
 複製代碼 代碼如下:
    sqlite> CREATE VIEW testview AS SELECT * FROM testtable WHERE first_col > 100;
    Error: table testview already exists
    sqlite> CREATE VIEW IF NOT EXISTS testview AS SELECT * FROM testtable WHERE first_col > 100;
   
五、刪除視圖：

    該操作的文法和刪除表基本相同，因此這裡只是給出樣本：
 複製代碼 代碼如下:
    sqlite> DROP VIEW testview;
    sqlite> DROP VIEW testview;
    Error: no such view: testview
    sqlite> DROP VIEW IF EXISTS testview;