4.1 SQLite 存儲類型
SQLite 存儲類型
存儲類型 |
描述 |
NULL
值是一個 NULL 值。
INTEGER
值是一個帶符號的整數，根據值的大小存儲在 1、2、3、4、6 或 8 字節中。
REAL
值是一個浮點值，存儲為 8 字節的 IEEE 浮點數字。
TEXT
值是一個文本字符串，使用數據庫編碼（UTF-8、UTF-16BE 或 UTF-16LE）存儲。
BLOB
值是一個 blob 數據，完全根據它的輸入存儲。
4.2 創建數據庫和創建表
創建了一個 COMPANY 表，ID 作為主鍵，NOT NULL 的約束表示在表中創建紀錄時這些字段不能為 NULL
?
1
2
3
4
5
bixiaopeng@bixiaopeng db$ sqlite3 wireless.db
SQLite version 3.7.13 2012-07-17 17:46:21
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE COMPANY(ID INT PRIMARY KEY NOT NULL,NAME TEXT NOT NULL,AGE INT NOT NULL,ADDRESS CHAR(50),SALARY REAL);
CREATE TABLE 是告訴數據庫系統創建一個新表的關鍵字。CREATE TABLE 語句後跟著表的唯一的名稱或標識。您也可以選擇指定帶有 table_name 的 database_name。
查看表是否創建成功
?
1
2
sqlite> .tables
COMPANY
查看表的完整信息
?
1
2
sqlite> .schema COMPANY
CREATE TABLE COMPANY(ID INT PRIMARY KEY NOT NULL,NAME TEXT NOT NULL,AGE INT NOT NULL,ADDRESS CHAR(50),SALARY REAL);
4.3 插入數據
插入數據，方法一:插入對應的列的值
?
1
2
sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
...> VALUES (1, 'Paul', 32, 'California', 20000.00 );
查詢是否插入成功
?
1
2
sqlite> SELECT * FROM COMPANY;
1|Paul|32|California|20000.0
插入數據，方法二:給所有列插入值
?
1
2
3
4
sqlite> INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 );
sqlite> SELECT * FROM COMPANY;
1|Paul|32|California|20000.0
7|James|24|Houston|10000.0
用第二種方法多插入幾個數據:
?
1
2
3
4
5
6
7
8
9
10
11
12
13
sqlite> INSERT INTO COMPANY VALUES (2, 'Allen', 25, 'Texas', 15000.00 );
sqlite> INSERT INTO COMPANY VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );
sqlite> INSERT INTO COMPANY VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );
sqlite> INSERT INTO COMPANY VALUES (5, 'David', 27, 'Texas', 85000.00 );
sqlite> INSERT INTO COMPANY VALUES (6, 'Kim', 22, 'South-Hall', 45000.00 );
sqlite> SELECT * FROM COMPANY;
1|Paul|32|California|20000.0
7|James|24|Houston|10000.0
2|Allen|25|Texas|15000.0
3|Teddy|23|Norway|20000.0
4|Mark|25|Rich-Mond |65000.0
5|David|27|Texas|85000.0
6|Kim|22|South-Hall|45000.0
4.4 更新數據
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
//先插入一條數據
sqlite> INSERT INTO COMPANY VALUES (8, 'wirelessqa', 28, 'HZ', 20000.00 );
sqlite> SELECT * FROM COMPANY;
ID NAME AGE ADDRESS SALARY
1 Paul 32 California 20000.0
7 James 24 Houston 10000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
8 wirelessqa 28 HZ 20000.0
//更新NAME為wirelessqa的地址為NanJing
sqlite> UPDATE COMPANY SET ADDRESS = 'NanJing' WHERE NAME = 'wirelessqa';
8 wirelessqa 28 NanJing 20000.0
//查看更新後的數據
sqlite> SELECT * FROM COMPANY WHERE NAME = 'wirelessqa';
ID NAME AGE ADDRESS SALARY
8 wirelessqa 28 NanJing 20000.0
4.5 刪除數據
?
1
2
3
4
//刪除ADDRESS為NanJing的這條數據
sqlite> DELETE FROM COMPANY WHERE ADDRESS = 'NanJing';
sqlite> SELECT * FROM COMPANY WHERE ADDRESS = 'NanJing';
sqlite>
4.6 數據查詢
4.6.1. SQLite 算術運算符
運算符: + - * / %
?
1
2
3
4
5
6
7
8
9
10
sqlite> select 4 + 2;
6
sqlite> select 4 - 2;
2
sqlite> select 4 * 2;
8
sqlite> select 4 / 2;
2
sqlite> select 4 % 2;
0
4.6.2. SQLite 算術運算符
運算符 |
描述 |
實例 |
==
檢查兩個操作數的值是否相等，如果相等則條件為真。
(a == b) 不為真。
=
檢查兩個操作數的值是否相等，如果相等則條件為真
(a = b) 不為真。
!=
檢查兩個操作數的值是否相等，如果不相等則條件為真
(a != b) 為真。
<>
檢查兩個操作數的值是否相等，如果不相等則條件為真
(a <> b) 為真。
>
檢查左操作數的值是否大於右操作數的值，如果是則條件為真。
(a > b) 不為真。
<
檢查左操作數的值是否小於右操作數的值，如果是則條件為真
(a < b) 為真。
>=
檢查左操作數的值是否大於等於右操作數的值，如果是則條件為真
(a >= b) 不為真。
<=
檢查左操作數的值是否小於等於右操作數的值，如果是則條件為真
(a <= b) 為真。
看一下表裡現有的數據：
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
sqlite> .headers on
sqlite> .mode tabs
sqlite> SELECT * FROM COMPANY;
ID NAME AGE ADDRESS SALARY
1 Paul 32 California 20000.0
7 James 24 Houston 10000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
sqlite> SELECT * FROM COMPANY WHERE AGE = 32;
ID NAME AGE ADDRESS SALARY
1 Paul 32 California 20000.0
sqlite> SELECT * FROM COMPANY WHERE AGE == 32 ;
ID NAME AGE ADDRESS SALARY
1 Paul 32 California 20000.0
sqlite> SELECT * FROM COMPANY WHERE AGE < 32;
ID NAME AGE ADDRESS SALARY
7 James 24 Houston 10000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
sqlite> SELECT * FROM COMPANY WHERE AGE != 32;
ID NAME AGE ADDRESS SALARY
7 James 24 Houston 10000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
sqlite> SELECT * FROM COMPANY WHERE AGE <= 32;
ID NAME AGE ADDRESS SALARY
1 Paul 32 California 20000.0
7 James 24 Houston 10000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
sqlite> SELECT * FROM COMPANY WHERE AGE >= 32;
ID NAME AGE ADDRESS SALARY
1 Paul 32 California 20000.0
sqlite> SELECT * FROM COMPANY WHERE AGE > 32;
4.6.3. SQLite 邏輯運算符
運算符 |
描述 |
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 運算符搜索指定表中的每一行，確保唯一性（無重復）。
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
//AND 運算符允許在一個 SQL 語句的 WHERE 子句中的多個條件的存在。
sqlite> SELECT * FROM COMPANY WHERE AGE < 25 AND SALARY > 15000.0;
ID NAME AGE ADDRESS SALARY
3 Teddy 23 Norway 20000.0
6 Kim 22 South-Hall 45000.0
//OR 運算符用於結合一個 SQL 語句的 WHERE 子句中的多個條件。
sqlite> SELECT * FROM COMPANY WHERE AGE < 25 OR SALARY > 15000.0;
ID NAME AGE ADDRESS SALARY
1 Paul 32 California 20000.0
7 James 24 Houston 10000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
//BETWEEN 運算符用於在給定最小值和最大值范圍內的一系列值中搜索值。
sqlite> SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 32;
ID NAME AGE ADDRESS SALARY
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
//EXISTS 運算符用於在滿足一定條件的指定表中搜索行的存在。
sqlite> SELECT AGE FROM COMPANY WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
AGE
32
24
25
23
25
27
22
//AGE 不為 NULL 的所有記錄
sqlite> SELECT * FROM COMPANY WHERE AGE IS NOT NULL;
ID NAME AGE ADDRESS SALARY
1 Paul 32 California 20000.0
7 James 24 Houston 10000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
//LIKE 運算符用於把某個值與使用通配符運算符的相似值進行比較。
sqlite> SELECT * FROM COMPANY WHERE NAME LIKE 'Ki%';
ID NAME AGE ADDRESS SALARY
6 Kim 22 South-Hall 45000.0
//GLOB 運算符用於把某個值與使用通配符運算符的相似值進行比較。GLOB 與 LIKE 不同之處在於，它是大小寫敏感的。
sqlite> SELECT * FROM COMPANY WHERE NAME GLOB 'Ki*';
ID NAME AGE ADDRESS SALARY
6 Kim 22 South-Hall 45000.0
//IN 運算符用於把某個值與一系列指定列表的值進行比較。
sqlite> SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );
ID NAME AGE ADDRESS SALARY
2 Allen 25 Texas 15000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
//IN 運算符的對立面，用於把某個值與不在一系列指定列表的值進行比較。
sqlite> SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 );
ID NAME AGE ADDRESS SALARY
1 Paul 32 California 20000.0
7 James 24 Houston 10000.0
3 Teddy 23 Norway 20000.0
6 Kim 22 South-Hall 45000.0
//
sqlite> SELECT * FROM COMPANY WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
ID NAME AGE ADDRESS SALARY
1 Paul 32 California 20000.0
sqlite> SELECT * FROM COMPANY WHERE AGE < (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
ID NAME AGE ADDRESS SALARY
7 James 24 Houston 10000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
6 Kim 22 South-Hall 45000.0
4.6.4 排序、分組、去重、時間
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
//通過內置函數查看一共有多少條數據
sqlite> SELECT COUNT(*) AS "RECORDS" FROM COMPANY;
RECORDS
7
//顯示前4條
sqlite> SELECT * FROM COMPANY LIMIT 4;
ID NAME AGE ADDRESS SALARY
1 Paul 32 California 20000.0
7 James 24 Houston 10000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
//按SALARY降序排序
sqlite> SELECT * FROM COMPANY ORDER BY SALARY ASC;
ID NAME AGE ADDRESS SALARY
7 James 24 Houston 10000.0
2 Allen 25 Texas 15000.0
1 Paul 32 California 20000.0
3 Teddy 23 Norway 20000.0
6 Kim 22 South-Hall 45000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
//按SALARY升序排序
sqlite> SELECT * FROM COMPANY ORDER BY SALARY DESC;
ID NAME AGE ADDRESS SALARY
5 David 27 Texas 85000.0
4 Mark 25 Rich-Mond 65000.0
6 Kim 22 South-Hall 45000.0
1 Paul 32 California 20000.0
3 Teddy 23 Norway 20000.0
2 Allen 25 Texas 15000.0
7 James 24 Houston 10000.0
//按NAME和SALARY升序排序
sqlite> SELECT * FROM COMPANY ORDER BY AGE,SALARY DESC;
ID NAME AGE ADDRESS SALARY
6 Kim 22 South-Hall 45000.0
3 Teddy 23 Norway 20000.0
7 James 24 Houston 10000.0
4 Mark 25 Rich-Mond 65000.0
2 Allen 25 Texas 15000.0
5 David 27 Texas 85000.0
1 Paul 32 California 20000.0
// GROUP BY 子句用於與 SELECT 語句一起使用，來對相同的數據進行分組。
// 查詢某個人的工資總數
sqlite> SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME;
NAME SUM(SALARY)
Allen 15000.0
David 85000.0
James 10000.0
Kim 45000.0
Mark 65000.0
Paul 20000.0
Teddy 20000.0
// GROUP BY 和 ORDER BY一起用
sqlite> SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME DESC;
NAME SUM(SALARY)
Teddy 20000.0
Paul 20000.0
Mark 65000.0
Kim 45000.0
James 10000.0
David 85000.0
Allen 15000.0
//HAVING 子句允許指定條件來過濾將出現在最終結果中的分組結果。
//WHERE 子句在所選列上設置條件，而 HAVING 子句則在由 GROUP BY 子句創建的分組上設置條件。
//在一個查詢中，HAVING 子句必須放在 GROUP BY 子句之後，必須放在 ORDER BY 子句之前
//查詢所有數據
qlite> SELECT * FROM COMPANY;
ID NAME AGE ADDRESS SALARY
1 Paul 32 California 20000.0
7 James 24 Houston 10000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
//查詢AGE，並去重
sqlite> SELECT DISTINCT AGE FROM COMPANY;
AGE
32
24
25
23
27
22
日期 & 時間
//把header關掉了
sqlite> . header off
sqlite> SELECT date('now');
2014-02-27
sqlite> SELECT datetime(1092941466, 'unixepoch');
2004-08-19 18:51:06
sqlite> SELECT TIME('NOW');
07:47:25?
4.6.5. 常用函數
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
//表行數
sqlite> SELECT count(*) FROM COMPANY;
7
//最大值
sqlite> SELECT max(salary) FROM COMPANY;
85000.0
//最小值
sqlite> SELECT min(salary) FROM COMPANY;
10000.0
//平均值
sqlite> SELECT avg(salary) FROM COMPANY;
37142.8571428572
sqlite> SELECT sum(salary) FROM COMPANY;
260000.0
//轉大寫
sqlite> SELECT upper(name) FROM COMPANY;
PAUL
JAMES
ALLEN
TEDDY
MARK
DAVID
KIM
//轉小寫
sqlite> SELECT lower(name) FROM COMPANY;
paul
james
allen
teddy
mark
david
kim
//長度
sqlite> SELECT name, length(name) FROM COMPANY;
Paul 4
James 5
Allen 5
Teddy 5
Mark 4
David 5
Kim 3
sqlite>
4.7 刪除表
?
1
2
sqlite> DROP TABLE COMPANY;
sqlite> .tables
4.8 刪除數據庫
?
1
直接rm 刪除掉db文件就可以了
