學習SQLite之路（二）

下面就是真正關于資料庫的一些知識了：

20160614更新

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

1. SQLite建立表：

基本文法：

CREATE TABLE database_name.table_name(

column1 datatype PRIMARY KEY(one or more columns),

column2 datatype,

column3 datatype,

.....

columnN datatype,

);

執行個體： create_company.txt

drop table company; -- 删除表，我這是為了可能會運作多次這個txt，就先删除一下，其實是多餘的

create table company(

ID INT PRIMARY KEY NOT NULL, -- ID 是列名， INT是資料類型，後面的是一些限制（主鍵，非空等）

NAME TEXT NOT NULL,

AGE INT NOT NULL,

ADDRESS CHAR(50),

SALARY REAL

建立完以後可以用 .table 來檢視表：

也可以 .schema 指令得到表的完整資訊：

2. SQLite删除表：

基本文法：可以選擇指定帶有表名的資料庫名稱

DROP TABLE database_name.table_name;

注意：表被删除，表裡面的所有資訊也被删除了

執行個體：

3. SQLite insert語句：用于向表中插入資料

文法（1）：

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)]  
VALUES (value1, value2, value3,...valueN);   /* 要為表中的指定列添加值 */

文法（2）：

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);  /* 要為表中的所有列添加值 */

執行個體：我這裡寫入一個insert_company.txt

insert into company values(1, 'Paul', 32, 'USA', 20000.0); -- 為所有列添加值

insert into company (ID, NAME, AGE, ADDRESS,SALARY) VALUES(2, 'Mark',33,'JAP',1500.00); -- 添加指定列

insert into company (ID, NAME, AGE, ADDRESS,SALARY) VALUES(3, 'Allen',35,'SPA',3000.00);

insert into company (ID, NAME, AGE, ADDRESS,SALARY) VALUES(4, 'Jack',32,'JAP',2100.00);

insert into company (ID, NAME, AGE, ADDRESS,SALARY) VALUES(5, 'David',25,'END',4500.00);

insert into company (ID, NAME, AGE, ADDRESS,SALARY) VALUES(6, 'Kim',22,'AUS',3500.00);

.read select_company.txt -- 這個先不管

4. SQLite select語句：從 SQLite 資料庫表中擷取資料，以結果表的形式傳回資料。這些結果表也被稱為結果集。

文法：

SELECT column1, column2, columnN FROM table_name;

columnN表示哪幾個列，也可以用 * 表示全部

執行個體： select_company.txt

内容： select * from company;

（2）設定輸出列的寬度：

sqlite>.width 10, 20, 10, 0

第一列寬度為10，第二列為20，第三列為10，第四列為預設（0表示預設）

（3）Schema資訊：系統裡面有個表叫 sqlite_master ，這裡有所有表的資訊。

schema_info.txt

select tbl_name from sqlite_master where type = 'table'; -- 列出所有在資料庫中建立的表

select sql from sqlite_master where type = 'table' and tbl_name='company'; -- 列出表company的資訊

效果：

5. SQLite 運算符：運算符是一個保留字或字元，主要用于 SQLite 語句的 WHERE 子句中執行操作，如比較和算術運算

主要有：算數運算符比較運算符邏輯運算符位運算符

（1）算數運算符：

+ - * /

（2）比較運算符：

假設變量 a=10，變量 b=20，則：

運算符描述

運算符運算符	描述	執行個體
==	檢查兩個操作數的值是否相等，如果相等則條件為真。	(a == b) 不為真。
=		(a = b) 不為真。
!=	檢查兩個操作數的值是否相等，如果不相等則條件為真。	(a != b) 為真。
<>		(a <> b) 為真。
>	檢查左操作數的值是否大于右操作數的值，如果是則條件為真。	(a > b) 不為真。
<	檢查左操作數的值是否小于右操作數的值，如果是則條件為真。	(a < b) 為真。
>=	檢查左操作數的值是否大于等于右操作數的值，如果是則條件為真。	(a >= b) 不為真。
<=	檢查左操作數的值是否小于等于右操作數的值，如果是則條件為真。	(a <= b) 為真。
!<	檢查左操作數的值是否不小于右操作數的值，如果是則條件為真。	(a !< b) 為假。
!>	檢查左操作數的值是否不大于右操作數的值，如果是則條件為真。	(a !> b) 為真。

（3）邏輯運算符：

運算符描述

AND	AND 運算符允許在一個 SQL 語句的 WHERE 子句中的多個條件的存在。
BETWEEN	BETWEEN 運算符用于在給定最小值和最大值範圍内的一系列值中搜尋值。
EXISTS	EXISTS 運算符用于在滿足一定條件的指定表中搜尋行的存在。
IN	IN 運算符用于把某個值與一系列指定清單的值進行比較。
NOT IN	IN 運算符的對立面，用于把某個值與不在一系列指定清單的值進行比較。
LIKE	LIKE 運算符用于把某個值與使用通配符運算符的相似值進行比較。
GLOB	GLOB 運算符用于把某個值與使用通配符運算符的相似值進行比較。GLOB 與 LIKE 不同之處在于，它是大小寫敏感的。
NOT	NOT 運算符是所用的邏輯運算符的對立面。比如 NOT EXISTS、NOT BETWEEN、NOT IN，等等。它是否定運算符。
OR	OR 運算符用于結合一個 SQL 語句的 WHERE 子句中的多個條件。
IS NULL	NULL 運算符用于把某個值與 NULL 值進行比較。
IS	IS 運算符與 = 相似。
IS NOT	IS NOT 運算符與 != 相似。
\|\|	連接配接兩個不同的字元串，得到一個新的字元串。
UNIQUE	UNIQUE 運算符搜尋指定表中的每一行，確定唯一性（無重複）。

（4）位運算符：

如果 A = 60，且 B = 13，則：

運算符描述

&	如果同時存在于兩個操作數中，二進制 AND 運算符複制一位到結果中。	(A & B) 将得到 12，即為 0000 1100
\|	如果存在于任一操作數中，二進制 OR 運算符複制一位到結果中。	(A \| B) 将得到 61，即為 0011 1101
~	二進制補碼運算符是一進制運算符，具有"翻轉"位效應。	(~A ) 将得到 -61，即為 1100 0011，2 的補碼形式，帶符号的二進制數。
<<	二進制左移運算符。左操作數的值向左移動右操作數指定的位數。	A << 2 将得到 240，即為 1111 0000
>>	二進制右移運算符。左操作數的值向右移動右操作數指定的位數。	A >> 2 将得到 15，即為 0000 1111

以上四種運算符用在where子句中比較多，以後再分析；

20160615 更新

1.SQLite表達式：

表達式是一個或多個值、運算符和計算值的SQL函數的組合。

SQL 表達式與公式類似，都寫在查詢語言中。您還可以使用特定的資料集來查詢資料庫。

（1）布爾表達式：如： *** where ID = 1;

（2）數值表達式：如： *** where SALARY > 4000;

（3）日期表達式：如：sqlite> SELECT CURRENT_TIMESTAMP;

2. SQLite AND OR運算符：AND 和 OR 運算符用于編譯多個條件來縮小在 SQLite 語句中所選的資料。這兩個運算符被稱為連接配接運算符。

（1）AND 運算符：就是且，要同時滿足所有條件。

基本文法：且每個條件必須為真

SELECT column1, column2, columnN 
FROM table_name
WHERE [condition1] AND [condition2]...AND [conditionN];

（2）OR運算符：就是或，滿足一個條件就可以

SELECT column1, column2, columnN 
FROM table_name
WHERE [condition1] OR [condition2]...OR [conditionN]

（3）執行個體見下面：

3. SQLite where 子句：WHERE 子句用于指定從一個表或多個表中擷取資料的條件。

WHERE 子句不僅可用在 SELECT 語句中，它也可用在 UPDATE、DELETE 語句中，等等

（1）基本文法：

SELECT column1, column2, columnN 
FROM table_name
WHERE [condition]

先建立并插入幾條資料：

（2）執行個體： where_company.txt：

-- AGE大于20 且 SALARY大于5000

select * from company where AGE>25 AND SALARY >4500;

-- AGE大于20 或 SALARY大于5000

select * from company where AGE>25 OR SALARY >4500;

-- AGE不為NULL

select * from company where AGE IS NOT NULL;

-- NAME以 ki 開頭的，Ki之後的字元不做限制

select * from company where NAME LIKE 'Ki%';

select * from company where NAME GLOB 'Ki*';

-- AGE 為22或25的所有記錄

select * from company where AGE IN(25, 22);

-- AGE 不為22或25的所有記錄

select * from company where AGE NOT IN(25, 22);

-- AGE 為25到35之間的所有記錄

select * from company where AGE BETWEEN 25 AND 35;

-- 在工資大于3000的人中找到年齡在 25到35之間的

select * from (select * from company where SALARY > 3000) where AGE BETWEEN 25 AND 35;

結果太長了，這裡省略。

4. SQLite update子句： UPDATE 查詢用于修改表中已有的記錄。可指定條件更新。

UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

（2）執行個體： update_company.txt

-- 更新ID為6的人，工資增加1000。隻更新滿足條件的記錄

update company set SALARY = SALARY + 1000 where ID = 6

-- 修改表中ADDRESS和SALARY的所有值，則不需要使用where子句

update company set ADDRESS = 'Texas',SALARY = 10000.0;

5.SQLite delete子句：查詢用于删除表中已有的記錄。可以使用帶有 WHERE 子句的 DELETE 查詢來删除標明行，否則所有的記錄都會被删除。

DELETE FROM table_name
WHERE [condition];

（2）執行個體： delete_company.txt

-- 删除ID為5的記錄

delete from company where ID = 5;

-- 删除所有記錄

delete from company;

6. SQLite like子句：LIKE 運算符是用來比對通配符指定模式的文本值（大小寫不敏感）

需要兩個通配符一起使用：也可以組合使用

% ：代表零個、一個或多個數字或字元

_：下劃線（_）代表一個單一的數字或字元

（1）說明：

語句 a 描述

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 結尾的任意值

（2）執行個體： like_company.txt

select * from company where SALARY like '2%'; -- 2 開頭的SALARY

select * from company where SALARY like '210_.0'; -- 自己體會

select * from company where NAME like 'k%'; -- 可以看出大小寫不敏感

7. SQLite GLOB子句：GLOB 運算符是用來比對通配符指定模式的文本值（大小寫敏感）

先說兩個通配符：

% ：星号（*）代表零個、一個或多個數字或字元

? : 問号（?）代表一個單一的數字或字元

（1）說明：

　　語句　　　　　　　　　　　　　　　　　　　　描述

WHERE SALARY GLOB '200*'
WHERE SALARY GLOB '200'
WHERE SALARY GLOB '?00*'
WHERE SALARY GLOB '2??'
WHERE SALARY GLOB '*2'
WHERE SALARY GLOB '?2*3'
WHERE SALARY GLOB '2???3'

（2）執行個體： glob_company.txt

select * from company where SALARY glob '2*'; -- 2 開頭的SALARY

select * from company where SALARY glob '210?.0'; -- 自己體會

select * from company where NAME glob 'k*'; -- 可以看出大小寫敏感

select * from company where NAME glob 'K*'; -- 可以看出大小寫敏感

8. SQLite Limit子句：LIMIT 子句用于限制由 SELECT 語句傳回的資料數量

（1）基本文法1：

SELECT column1, column2, columnN 
FROM table_name
LIMIT [no of rows]

下面是 LIMIT 子句與 OFFSET 子句一起使用時的文法：

SELECT column1, column2, columnN 
FROM table_name
LIMIT [no of rows] OFFSET [row num]

（2）執行個體： limit_company.txt

select * from company limit 3; -- 隻顯示3行

select * from company limit 3 offset 2; -- 從第3行開始顯示3行

9. SQLite order by子句：用來基于一個或多個列按升序或降序順序排列資料。

SELECT column-list 
FROM table_name 
[WHERE condition] 
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

ASC ：表示升序（預設就是這個，可以不寫）

DESC：表示降序

（2）執行個體： orderby_company.txt

select * from company order by SALARY; -- 升序，預設asc可以不寫

select * from company order by SALARY ASC;

select * from company order by SALARY DESC; -- 降序

10. SQLite group by 子句：SQLite 的 GROUP BY 子句用于與 SELECT 語句一起使用，來對相同的資料進行分組。

在 SELECT 語句中，GROUP BY 子句放在 WHERE 子句之後，放在 ORDER BY 子句之前。

（1）基本文法：

SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN    -- where 之後      order by  之前
ORDER BY column1, column2....columnN

（2）執行個體： groupby_company.txt

先插入3條資料：

insert into company values(8, 'Paul', 24, 'USA', 5000);

insert into company values(9, 'James', 24, 'CNA', 4000);

insert into company values(10, 'James', 25, 'USA', 6000);

再進行分組

insert into company values(8, 'Paul', 24, 'USA', 5000);

insert into company values(9, 'James', 24, 'CNA', 4000);

insert into company values(10, 'James', 25, 'USA', 6000);

-- 按name進行分組，SUM( *** ) 表示對裡面的東西求和

select NAME, SUM(SALARY) from company group by NAME order by NAME;

select NAME, SUM(SALARY) from company group by NAME order by NAME DESC; -- 降序

11. SQLite having 子句：HAVING 子句允許指定條件來過濾将出現在最終結果中的分組結果。

WHERE 子句在所選列上設定條件，而 HAVING 子句則在由 GROUP BY 子句建立的分組上設定條件。

（1）重要知識：HAVING 子句在 SELECT 查詢中的位置：

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

（2）基本文法：HAVING 子句必須放在 GROUP BY 子句之後，必須放在 ORDER BY 子句之前

SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2

（3）執行個體：having_company.txt

-- 顯示名稱計數小于 2 的所有記錄：

select * from company group by name having count(NAME) < 2;

-- 顯示名稱計數等于 2 的所有記錄：

select * from company group by name having count(NAME) >= 2;

12. SQLite distinct關鍵字：與 SELECT 語句一起使用，來消除所有重複的記錄，并隻擷取唯一一次記錄。

隻擷取唯一一次記錄，而不是擷取重複記錄

（1）文法：在select的後面：

SELECT DISTINCT column1, column2,.....columnN 
FROM table_name
WHERE [condition]

（2）執行個體：distinct_company.txt

select NAME, SALARY from company;

select distinct NAME, SALARY from company; -- 相同NAME隻出現一次

學習SQLite之路（二）

繼續閱讀

set define off關閉替代變量功能

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述