sqlite學習筆記

標籤：

1.java中的資料類型與sqlite中的對應關係

    Boolean 對應 INTEGER(SQLite 並沒有單獨的布爾儲存類型，而是將布爾值儲存為整數 0 (false) 和 1 (true)。)    Byte 對應 INTEGER      Short 對應 INTEGER      Integer 對應 INTEGER    Long 對應 INTEGER    Float 對應 REAL      Double 對應 REAL    String 對應 TEXT    Byte[] 對應 BLOB    Date 對應 INTEGER/TEXT/REAL    [更詳細請參考](http://www.oschina.net/translate/data-types-in-sqlite-version-3?cmp)

2.sqlite 資料庫匯入匯出：.dump 命令

• $sqlite3 testDB.db .dump > testDB.sql
  ==>將 testDB.db 資料庫的內容到轉儲到 ASCII 文字檔 testDB.sql 中

• $sqlite3 testDB.db < testDB.sql
  ==>將testDB.sql中的資料恢複(插入)到資料庫testDB.db中

3.sqlite建立表：

sqlite> CREATE TABLE COMPANY(   ID INT PRIMARY KEY     NOT NULL,   NAME           TEXT    NOT NULL,   AGE            INT     NOT NULL,   ADDRESS        CHAR(50),   SALARY         REAL);

4.SQLIte 命令中的 .tables 命令來驗證表是否已成功建立

  sqlite>.tables  COMPANY   DEPARTMENT

5.SQLite 刪除表

sqlite>DROP TABLE COMPANY;--刪除表sqlite>.tables；查看刪除後的表

6.SQLite Insert 語句

建立表：

sqlite> CREATE TABLE COMPANY(   ID INT PRIMARY KEY     NOT NULL,   NAME           TEXT    NOT NULL,   AGE            INT     NOT NULL,   ADDRESS        CHAR(50),   SALARY         REAL);

插入資料

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)VALUES (1, ‘Paul‘, 32, ‘California‘, 20000.00 );

也可對應所有欄位全部插入：

INSERT INTO COMPANY VALUES (7, ‘James‘, 24, ‘Houston‘, 10000.00 );

也可使用一個表來填充另一個表

INSERT INTO first_table_name [(column1, column2, ... columnN)] SELECT column1, column2, ...columnN FROM second_table_name[WHERE condition];

7.SQLite 邏輯運算子

• AND
  
  • AND 運算子允許在一個 SQL 陳述式的 WHERE 子句中的多個條件的存在。
  • sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;
• BETWEEN
  
  • BETWEEN 運算子用於在給定最小值和最大值範圍內的一系列值中搜尋值。
  • qlite> SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;
• EXISTS
  
  • EXISTS 運算子用於在滿足一定條件的指定表中搜尋行的存在。
  • sqlite> SELECT AGE FROM COMPANY WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
  • sqlite> SELECT * FROM COMPANY WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
• IN
  
  • IN 運算子用於把某個值與一系列指定列表的值進行比較。
  • sqlite> SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );
• NOT IN
  
  • NOT IN 運算子的對立面，用於把某個值與不在一系列指定列表的值進行比較。
  • sqlite> SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 );
• LIKE
  
  • LIKE 運算子用於把某個值與使用萬用字元運算子的相似值進行比較。
  • sqlite> SELECT * FROM COMPANY WHERE NAME LIKE ‘Ki%’;
  • ‘Ki%’ 表示name是以ki開頭的（也可’ki*’,後面類似），’%ki’表示name是以ki結尾的，’%ki%’表示name中間包含ki
• GLOB
  
  • GLOB 運算子用於把某個值與使用萬用字元運算子的相似值進行比較。GLOB 與 LIKE 不同之處在於，它是大小寫敏感的。
  • sqlite> SELECT * FROM COMPANY WHERE NAME GLOB ‘Ki*’;
• NOT
  
  • NOT 運算子是所用的邏輯運算子的對立面。比如 NOT EXISTS、NOT BETWEEN、NOT IN，等等。它是否定運算子。
• OR
  
  • OR 運算子用於結合一個 SQL 陳述式的 WHERE 子句中的多個條件。
  • sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;
• IS NULL
  
  • NULL 運算子用於把某個值與 NULL 值進行比較。
• IS NOT
  
  • IS NOT 運算子與 != 相似。
  • sqlite> SELECT * FROM COMPANY WHERE AGE IS NOT NULL;
• IS
  
  • IS 運算子與 = 相似。
• ||
  
  • 串連兩個不同的字串，得到一個新的字串。
• UNIQUE
  
  • UNIQUE 運算子搜尋指定表中的每一行，確保唯一性（無重複）。

8.SQLite Update 語句

更新 ID 為 6 的客戶地址sqlite> UPDATE COMPANY SET ADDRESS = ‘Texas‘ WHERE ID = 6;修改 COMPANY 表中 ADDRESS 和 SALARY 列的所有值sqlite> UPDATE COMPANY SET ADDRESS = ‘Texas‘, SALARY = 20000.00;

9.SQLite Delete 語句

刪除 ID 為 7 的客戶sqlite> DELETE FROM COMPANY WHERE ID = 7;從 COMPANY 表中刪除所有記錄sqlite> DELETE FROM COMPANY;

10.SQLite Like 子句

SQLite 的 LIKE 運算子是用來匹配萬用字元指定模式的文本值。如果搜尋運算式與模式運算式匹配，LIKE 運算子將返回真（true），也就是 1。這裡有兩個萬用字元與 LIKE 運算子一起使用：百分比符號 （%）底線 （_）百分比符號（%）代表零個、一個或多個數字或字元。底線（_）代表一個單一的數字或字元。這些符號可以被組合使用。SELECT FROM table_nameWHERE column LIKE ‘XXXX%‘or SELECT FROM table_nameWHERE column LIKE ‘%XXXX%‘orSELECT FROM table_nameWHERE column LIKE ‘XXXX_‘orSELECT FROM table_nameWHERE column LIKE ‘_XXXX‘orSELECT FROM table_nameWHERE column LIKE ‘_XXXX_‘WHERE SALARY LIKE ‘200%‘    尋找以 200 開頭的任意值WHERE SALARY LIKE ‘%200%‘   尋找任意位置包含 200 的任意值WHERE SALARY LIKE ‘_00%‘    尋找第二位和第三位為 00 的任意值WHERE SALARY LIKE ‘2_%_%‘   尋找以 2 開頭，且長度至少為 3 個字元的任意值WHERE SALARY LIKE ‘%2‘      尋找以 2 結尾的任意值WHERE SALARY LIKE ‘_2%3‘    尋找第二位為 2，且以 3 結尾的任意值WHERE SALARY LIKE ‘2___3‘   尋找長度為 5 位元，且以 2 開頭以 3 結尾的任意值注意區分：_底線和，-減號：顯示 COMPANY 表中 ADDRESS 文本裡包含一個連字號（-）的所有記錄：sqlite> SELECT * FROM COMPANY WHERE ADDRESS  LIKE ‘%-%‘;

11.SQLite GLOB 子句

SQLite 的 GLOB 運算子是用來匹配萬用字元指定模式的文本值。如果搜尋運算式與模式運算式匹配，GLOB 運算子將返回真（true），也就是 1。與 LIKE 運算子不同的是，GLOB 是大小寫敏感的，對於下面的萬用字元，它遵循 UNIX 的文法。星號 （*）問號 （?）星號（*）代表零個、一個或多個數字或字元。問號（?）代表一個單一的數字或字元。這些符號可以被組合使用SELECT FROM table_nameWHERE column GLOB ‘XXXX*‘or SELECT FROM table_nameWHERE column GLOB ‘*XXXX*‘orSELECT FROM table_nameWHERE column GLOB ‘XXXX?‘orSELECT FROM table_nameWHERE column GLOB ‘?XXXX‘orSELECT FROM table_nameWHERE column GLOB ‘?XXXX?‘orSELECT FROM table_nameWHERE column GLOB ‘????‘WHERE SALARY GLOB ‘200*‘    尋找以 200 開頭的任意值WHERE SALARY GLOB ‘*200*‘   尋找任意位置包含 200 的任意值WHERE SALARY GLOB ‘?00*‘    尋找第二位和第三位為 00 的任意值WHERE SALARY GLOB ‘2??‘     尋找以 2 開頭，且長度至少為 3 個字元的任意值WHERE SALARY GLOB ‘*2‘      尋找以 2 結尾的任意值WHERE SALARY GLOB ‘?2*3‘    尋找第二位為 2，且以 3 結尾的任意值WHERE SALARY GLOB ‘2???3‘   尋找長度為 5 位元，且以 2 開頭以 3 結尾的任意值

12.SQLite Limit 子句

ID          NAME        AGE         ADDRESS     SALARY----------  ----------  ----------  ----------  ----------1           Paul        32          California  20000.02           Allen       25          Texas       15000.03           Teddy       23          Norway      20000.04           Mark        25          Rich-Mond   65000.05           David       27          Texas       85000.06           Kim         22          South-Hall  45000.07           James       24          Houston     10000.0從表中提取的行數：sqlite> SELECT * FROM COMPANY LIMIT 6;從跳過2行提取3條記錄：sqlite> SELECT * FROM COMPANY LIMIT 3 OFFSET 2;

13.SQLite Order By 子句

ID          NAME        AGE         ADDRESS     SALARY----------  ----------  ----------  ----------  ----------1           Paul        32          California  20000.02           Allen       25          Texas       15000.03           Teddy       23          Norway      20000.04           Mark        25          Rich-Mond   65000.05           David       27          Texas       85000.06           Kim         22          South-Hall  45000.07           James       24          Houston     10000.0安裝SALARY升序排列：sqlite> SELECT * FROM COMPANY ORDER BY SALARY ASC;結果：ID          NAME        AGE         ADDRESS     SALARY----------  ----------  ----------  ----------  ----------7           James       24          Houston     10000.02           Allen       25          Texas       15000.01           Paul        32          California  20000.03           Teddy       23          Norway      20000.06           Kim         22          South-Hall  45000.04           Mark        25          Rich-Mond   65000.05           David       27          Texas       85000.0=========================================================將結果按 NAME 和 SALARY 升序排序：（順序從左至右，先按NAME排序，得到結果集在按SALARY排序（不影響第一次排序的基礎上排序））如：有Allen1和Allen2，第二次排序只是對此兩者的SALARY進行排序sqlite> SELECT * FROM COMPANY ORDER BY NAME, SALARY ASC;結果：ID          NAME        AGE         ADDRESS     SALARY----------  ----------  ----------  ----------  ----------2           Allen       25          Texas       15000.05           David       27          Texas       85000.07           James       24          Houston     10000.06           Kim         22          South-Hall  45000.04           Mark        25          Rich-Mond   65000.01           Paul        32          California  20000.03           Teddy       23          Norway      20000.0按 NAME 降序排序：sqlite> SELECT * FROM COMPANY ORDER BY NAME DESC;ID          NAME        AGE         ADDRESS     SALARY----------  ----------  ----------  ----------  ----------3           Teddy       23          Norway      20000.01           Paul        32          California  20000.04           Mark        25          Rich-Mond   65000.06           Kim         22          South-Hall  45000.07           James       24          Houston     10000.05           David       27          Texas       85000.02           Allen       25          Texas       15000.0

14.SQLite Group By

下面給出了 GROUP BY 子句的基本文法。GROUP BY 子句必須放在 WHERE 子句中的條件之後，必須放在 ORDER BY 子句之前。如：SELECT column-listFROM table_nameWHERE [ conditions ]GROUP BY column1, column2....columnNORDER BY column1, column2....columnN資料表如下：  ID          NAME        AGE         ADDRESS     SALARY----------  ----------  ----------  ----------  ----------1           Paul        32          California  20000.02           Allen       25          Texas       15000.03           Teddy       23          Norway      20000.04           Mark        25          Rich-Mond   65000.05           David       27          Texas       85000.06           Kim         22          South-Hall  45000.07           James       24          Houston     10000.0如果您想瞭解每個客戶的工資總額，則可使用 GROUP BY 查詢，如下所示：sqlite> SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME;這將產生以下結果：NAME        SUM(SALARY)----------  -----------Allen       15000.0David       85000.0James       10000.0Kim         45000.0Mark        65000.0Paul        20000.0Teddy       20000.0我們的表具有重複名稱的記錄，如下所示：ID          NAME        AGE         ADDRESS     SALARY----------  ----------  ----------  ----------  ----------1           Paul        32          California  20000.02           Allen       25          Texas       15000.03           Teddy       23          Norway      20000.04           Mark        25          Rich-Mond   65000.05           David       27          Texas       85000.06           Kim         22          South-Hall  45000.07           James       24          Houston     10000.08           Paul        24          Houston     20000.09           James       44          Norway      5000.010          James       45          Texas       5000.0讓我們用同樣的 GROUP BY 語句來對所有記錄按 NAME 列進行分組，如下所示：sqlite> SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME;這將產生以下結果：NAME        SUM(SALARY)----------  -----------Allen       15000David       85000James       20000Kim         45000Mark        65000Paul        40000Teddy       20000

SQLite Having 子句

下面是 HAVING 子句在 SELECT 查詢中的位置SELECTFROMWHEREGROUP BYHAVINGORDER BYLIMIT OFFSET 在一個查詢中，HAVING 子句必須放在 GROUP BY 子句之後，必須放在 ORDER BY 子句之前。下面是包含 HAVING 子句的 SELECT 語句的文法：SELECT column1, column2FROM table1, table2WHERE [ conditions ]GROUP BY column1, column2HAVING [ conditions ]ORDER BY column1, column2COMPANY 表有以下記錄：ID          NAME        AGE         ADDRESS     SALARY----------  ----------  ----------  ----------  ----------1           Paul        32          California  20000.02           Allen       25          Texas       15000.03           Teddy       23          Norway      20000.04           Mark        25          Rich-Mond   65000.05           David       27          Texas       85000.06           Kim         22          South-Hall  45000.07           James       24          Houston     10000.08           Paul        24          Houston     20000.09           James       44          Norway      5000.010          James       45          Texas       5000.0下面是一個執行個體，它將顯示名稱計數小於 2 的所有記錄：sqlite > SELECT * FROM COMPANY GROUP BY name HAVING count(name) < 2;這將產生以下結果：ID          NAME        AGE         ADDRESS     SALARY----------  ----------  ----------  ----------  ----------2           Allen       25          Texas       150005           David       27          Texas       850006           Kim         22          South-Hall  450004           Mark        25          Rich-Mond   650003           Teddy       23          Norway      20000下面是一個執行個體，它將顯示名稱計數大於 2 的所有記錄：sqlite > SELECT * FROM COMPANY GROUP BY name HAVING count(name) > 2;這將產生以下結果：ID          NAME        AGE         ADDRESS     SALARY----------  ----------  ----------  ----------  ----------10          James       45          Texas       5000

SQLite Distinct 關鍵字

SQLite 的 DISTINCT 關鍵字與 SELECT 語句一起使用，來消除所有重複的記錄，並只擷取唯一一次記錄。有可能出現一種情況，在一個表中有多個重複的記錄。當提取這樣的記錄時，DISTINCT 關鍵字就顯得特別有意義，它只擷取唯一一次記錄，而不是擷取重複記錄。文法用於消除重複記錄的 DISTINCT 關鍵字的基本文法如下：SELECT DISTINCT column1, column2,.....columnN FROM table_nameWHERE [condition]執行個體假設 COMPANY 表有以下記錄：ID          NAME        AGE         ADDRESS     SALARY----------  ----------  ----------  ----------  ----------1           Paul        32          California  20000.02           Allen       25          Texas       15000.03           Teddy       23          Norway      20000.04           Mark        25          Rich-Mond   65000.05           David       27          Texas       85000.06           Kim         22          South-Hall  45000.07           James       24          Houston     10000.08           Paul        24          Houston     20000.09           James       44          Norway      5000.010          James       45          Texas       5000.0首先，讓我們來看看下面的 SELECT 查詢，它將返回重複的工資記錄：sqlite> SELECT name FROM COMPANY;這將產生以下結果：NAME----------PaulAllenTeddyMarkDavidKimJamesPaulJamesJames現在，讓我們在上述的 SELECT 查詢中使用 DISTINCT 關鍵字：sqlite> SELECT DISTINCT name FROM COMPANY;這將產生以下結果，沒有任何重複的條目：NAME----------PaulAllenTeddyMarkDavidKimJames

參考自：http://www.runoob.com/sqlite/sqlite-operators.html

sqlite學習筆記