国产一级a片免费看高清,亚洲熟女中文字幕在线视频,黄三级高清在线播放,免费黄色视频在线看

序言：

    嵌入式數(shù)據(jù)庫Sqlite的基本sql使用匯總，使用測試起來，與關(guān)系型數(shù)據(jù)庫mysql在語法上有很多的相似之處，先準(zhǔn)備測試數(shù)據(jù)：

CREATE TABLE COMPANY(ID INT NOT NULL, NAME VARCHAR(20),AGE INT,ADDRESS VARCHAR(20),SALARY DECIMAL(7,2));

INSERT INTO "COMPANY" VALUES(1,'Paul',32,'California',20000);

INSERT INTO "COMPANY" VALUES(2,'Allen',25,'Texas',15000);

INSERT INTO "COMPANY" VALUES(3,'Teddy',23,'Norway',20000);

INSERT INTO "COMPANY" VALUES(4,'Mark',25,'Rich-Mond',65000);

INSERT INTO "COMPANY" VALUES(5,'David',27,'Texas',85000);

INSERT INTO "COMPANY" VALUES(6,'Kim',22,'South-Hall',45000);

INSERT INTO "COMPANY" VALUES(7,'James',24,NULL,10000);

INSERT INTO "COMPANY" VALUES(8,'Xiaoteng',29,NULL,NULL);

1，分組統(tǒng)計排序

GROUP BY 進(jìn)行分組統(tǒng)計數(shù)據(jù)，命令如下：

sqlite> SELECT NAME, SUM(SALARY) SALARY_SUM, COUNT(1) COUNT_NUM FROM COMPANY GROUP BY NAME;

 

ORDER BY 進(jìn)行排序，命令如下：

sqlite> SELECT NAME, SUM(SALARY) SALARY_SUM, COUNT(1) COUNT_NUM FROM COMPANY GROUP BY NAME ORDER BY SALARY_SUM ASC;

 

HAVING 字句過濾數(shù)據(jù)記錄，命令如下：

SELECT c.*,COUNT(1) COUNT_NUM FROM COMPANY c GROUP BY c.NAME HAVING (COUNT_NUM) > 1 ORDER BY COUNT_NUM ;

PS：在一個查詢中，HAVING 子句必須放在 GROUP BY 子句之后，必須放在 ORDER BY 子句之前。下面是包含 HAVING 子句的 SELECT 語句的語法：

 

2，Limit分頁統(tǒng)計語句

SQLite 的 LIMIT 子句用于限制由 SELECT 語句返回的數(shù)據(jù)數(shù)量。

第一頁取值SQL： SELECT * FROM COMPANY ORDER BY ID LIMIT 0,3;  也可以從一個特定的偏移開始提取記錄，從第四位開始提取 3 個記錄，使用OFFSET關(guān)鍵字，SELECT * FROM COMPANY ORDER BY ID LIMIT 3 OFFSET 0；PS：首頁從0開始取值。

第二頁取值SQL： SELECT * FROM COMPANY ORDER BY ID LIMIT 3,3;  也可以從一個特定的偏移開始提取記錄，從第四位開始提取 3 個記錄，使用OFFSET關(guān)鍵字，SELECT * FROM COMPANY ORDER BY ID LIMIT 3 OFFSET 3；如下圖所示：

 

3，Glob匹配字句

SQLite 的 GLOB 運(yùn)算符是用來匹配通配符指定模式的文本值。如果搜索表達(dá)式與模式表達(dá)式匹配，GLOB 運(yùn)算符將返回真（true），也就是 1。與 LIKE 運(yùn)算符不同的是，GLOB 是大小寫敏感的，對于下面的通配符，它遵循 UNIX 的語法。

星號 （*）

問號 （?）

星號（*）代表零個、一個或多個數(shù)字或字符。問號（?）代表一個單一的數(shù)字或字符。這些符號可以被組合使用。

下面一些實例演示了 帶有 '*' 和 '?' 運(yùn)算符的 GLOB 子句不同的地方：

 

下面是一個實例，它顯示 COMPANY 表中 AGE 以 2 開頭的所有記錄，如下所示：

 

 

下面是一個實例，它顯示 COMPANY 表中 ADDRESS 文本里包含一個連字符（-）的所有記錄：

 

4，Distinct關(guān)鍵字過濾重復(fù)記錄

SQLite 的 DISTINCT 關(guān)鍵字與 SELECT 語句一起使用，來消除所有重復(fù)的記錄，并只獲取唯一一次記錄。

有可能出現(xiàn)一種情況，在一個表中有多個重復(fù)的記錄。當(dāng)提取這樣的記錄時，DISTINCT 關(guān)鍵字就顯得特別有意義，它只獲取唯一一次記錄，而不是獲取重復(fù)記錄。

 

 

5，字符串連接操作

問題地址：

sqlite> CREATE TABLE t1(id int, name varchar(60));

sqlite> INSERT INTO "t1" VALUES(4,'1@test.cn');

sqlite> select * from t1;

id          name     

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

4           1@test.cn

sqlite> update t1 set name=(id/2)||substr(name,instr(name,'@'),length(name)-instr(name,'@')+1)  where id=4;

sqlite> select * from t1;

id          name     

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

4           2@test.cn

sqlite>

 

6，對Null值的處理

往表里面錄入Null值

sqlite> INSERT INTO COMPANY(ID,NAME,AGE,ADDRESS,SALARY) VALUES(8,'Xiaoteng',29,NULL,18000);

sqlite>

修改某個字段為null值

sqlite> UPDATE COMPANY SET SALARY = NULL WHERE ID=8;

sqlite>

查詢?yōu)?/span>null的記錄

sqlite> SELECT * FROM COMPANY WHERE ADDRESS IS NULL;

ID          NAME        AGE         ADDRESS     SALARY   

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

7           James       24                      10000    

8           Xiaoteng    29                               

sqlite>

查詢不為null的記錄

sqlite> SELECT * FROM COMPANY WHERE ADDRESS IS NOT NULL;

ID          NAME        AGE         ADDRESS     SALARY   

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

1           Paul        32          California  20000    

2           Allen       25          Texas       15000    

3           Teddy       23          Norway      20000    

4           Mark        25          Rich-Mond   65000    

5           David       27          Texas       85000    

6           Kim         22          South-Hall  45000    

sqlite>

7，子查詢

SELECt中的基本語法如下：

SELECT column_name [, column_name ]

FROM   table1 [, table2 ]

WHERE  column_name OPERATOR

      (SELECT column_name [, column_name ]

      FROM table1 [, table2 ]

      [WHERE])

實例如下：

sqlite> SELECT * FROM COMPANY WHERE ID IN (SELECT ID FROM COMPANY WHERE SALARY > 45000);

 

INSERT語句中的子查詢使用，基本語法：

INSERT INTO table_name [ (column1 [, column2 ]) ]

           SELECT [ *|column1 [, column2 ]

           FROM table1 [, table2 ]

           [ WHERE VALUE OPERATOR ]

實例如下：

sqlite> INSERT INTO COMPANY_BKP

SELECT * FROM COMPANY

     WHERE ID IN (SELECT ID

                  FROM COMPANY) ;

UPDATE語句中的子查詢使用，基本語法如下：

UPDATE table

SET column_name = new_value

[ WHERE OPERATOR [ VALUE ]

   (SELECT COLUMN_NAME

   FROM TABLE_NAME)

   [ WHERE) ]

實例如下：

 sqlite> UPDATE COMPANY

     SET SALARY = SALARY * 0.50

     WHERE AGE IN (SELECT AGE FROM COMPANY_BKP

                   WHERE AGE >= 27 );

DELETE語句中的子查詢使用，語法如下：

DELETE FROM TABLE_NAME

[ WHERE OPERATOR [ VALUE ]

   (SELECT COLUMN_NAME

   FROM TABLE_NAME)

   [ WHERE) ]

實例如下：

sqlite> DELETE FROM COMPANY

     WHERE AGE IN (SELECT AGE FROM COMPANY_BKP

                   WHERE AGE > 27 );

 

8，EXPLAIN分析

沒有建立索引之前，分析都是表掃描：

sqlite> EXPLAIN SELECT *  FROM COMPANY  WHERE Salary < 20000;

addr        opcode      p1          p2          p3          p4          p5          comment  

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

0           Trace       0           0           0                       00                   

1           Integer     20000       1           0                       00                   

2           Goto        0           16          0                       00                   

3           OpenRead    0           2           0           5           00                   

4           Rewind      0           14          0                       00                   

5           Column      0           4           2                       00                   

6           Ge          1           13          2           collseq(BI  6b                   

7           Column      0           0           4                       00                   

8           Column      0           1           5                       00                   

9           Column      0           2           6                       00                   

10          Column      0           3           7                       00                   

11          Column      0           4           8                       00                   

12          ResultRow   4           5           0                       00                   

13          Next        0           5           0                       01                   

14          Close       0           0           0                       00                   

15          Halt        0           0           0                       00                   

16          Transactio  0           0           0                       00                   

17          VerifyCook  0           1           0                       00                   

18          TableLock   0           2           0           COMPANY     00                    

19          Goto        0           3           0                       00                   

sqlite> EXPLAIN QUERY PLAN SELECT * FROM COMPANY WHERE Salary < 20000;

order       from        detail      

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

0           0           TABLE COMPANY

sqlite>

 

建立索引，再進(jìn)行EXPLAIN分析查看結(jié)果，走了idx_sal索引掃描：

sqlite> CREATE INDEX idx_sal ON COMPANY(SALARY);

sqlite> EXPLAIN SELECT *  FROM COMPANY  WHERE Salary < 20000;

addr        opcode      p1          p2          p3          p4          p5          comment  

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

0           Trace       0           0           0                       00                   

1           Integer     20000       1           0                       00                   

2           Goto        0           25          0                       00                   

3           OpenRead    0           2           0           5           00                   

4           OpenRead    1           3           0           keyinfo(1,  00                   

5           Affinity    2           0           0           cb          00                   

6           Rewind      1           22          2           0           00                   

7           SCopy       1           2           0                       00                   

8           IsNull      2           22          0                       00                   

9           Affinity    2           1           0           cb          00                   

10          IdxGE       1           22          2           1           00                   

11          Column      1           0           3                       00                   

12          IsNull      3           21          0                       00                   

13          IdxRowid    1           3           0                       00                   

14          Seek        0           3           0                       00                   

15          Column      0           0           4                       00                   

16          Column      0           1           5                       00                   

17          Column      0           2           6                       00                    

18          Column      0           3           7                       00                   

19          Column      1           0           8                       00                   

20          ResultRow   4           5           0                       00                   

21          Next        1           10          0                       00                   

22          Close       0           0           0                       00                   

23          Close       1           0           0                       00                   

24          Halt        0           0           0                       00                   

25          Transactio  0           0           0                       00                    

26          VerifyCook  0           2           0                       00                   

27          TableLock   0           2           0           COMPANY     00                   

28          Goto        0           3           0                       00                   

sqlite> EXPLAIN QUERY PLAN SELECT * FROM COMPANY WHERE Salary < 20000;

order       from        detail                         

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

0           0           TABLE COMPANY WITH INDEX idx_sal

sqlite>


9，刪除重復(fù)數(shù)據(jù)但是一條記錄

錄入測試數(shù)據(jù)

sqlite> .dump

PRAGMA foreign_keys=OFF;

BEGIN TRANSACTION;

CREATE TABLE COMPANY(ID INT NOT NULL, NAME VARCHAR(20),AGE INT,ADDRESS VARCHAR(20),SALARY DECIMAL(7,2));

INSERT INTO "COMPANY" VALUES(2,'Allen',25,'Texas',15000);

INSERT INTO "COMPANY" VALUES(3,'Teddy',23,'Norway',20000);

INSERT INTO "COMPANY" VALUES(4,'Mark',25,'Rich-Mond',65000);

INSERT INTO "COMPANY" VALUES(5,'David',27,'Texas',85000);

INSERT INTO "COMPANY" VALUES(6,'Kim',22,'South-Hall',45000);

INSERT INTO "COMPANY" VALUES(7,'James',24,'Houston',10000);

INSERT INTO "COMPANY" VALUES(7,'James',28,'Houston',20000);

INSERT INTO "COMPANY" VALUES(4,'Mark',29,'Rich-Mond',95000);

COMMIT;

sqlite>

 

查看重復(fù)記錄數(shù)

sqlite> select * from company order by name;

ID          NAME        AGE         ADDRESS     SALARY     

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

2           Allen       25          Texas       15000      

5           David       27          Texas       85000      

7           James       24          Houston     10000      

7           James       28          Houston     20000      

6           Kim         22          South-Hall  45000      

4           Mark        25          Rich-Mond   65000      

4           Mark        29          Rich-Mond   95000      

3           Teddy       23          Norway      20000      

sqlite>

 

通過rowid來刪除重復(fù)記錄

sqlite> DELETE FROM COMPANY WHERE rowid NOT IN(SELECT MAX(rowid) rowid FROM COMPANY GROUP BY NAME);

sqlite>

 

再查看最新的數(shù)據(jù)記錄，已經(jīng)刪除了重復(fù)NAME的記錄

sqlite> select * from company;

ID          NAME        AGE         ADDRESS     SALARY     

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

2           Allen       25          Texas       15000      

3           Teddy       23          Norway      20000      

5           David       27          Texas       85000      

6           Kim         22          South-Hall  45000      

7           James       28          Houston     20000      

4           Mark        29          Rich-Mond   95000      

sqlite>

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

<版權(quán)所有，文章允許轉(zhuǎn)載，但必須以鏈接方式注明源地址，否則追究法律責(zé)任!>
原博客地址：http://blog.itpub.net/26230597/abstract/1/
原作者：黃杉 (mchdba)

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

本站僅提供存儲服務(wù)，所有內(nèi)容均由用戶發(fā)布，如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請點(diǎn)擊舉報。