Zi 字媒體

SQLite 入門教程（四）增刪改查，有講究 – jashliao部落格

2021/12/25

jashliaoeuwordpress

SQLite 入門教程（四）增刪改查，有講究

資料來源:http://www.cnblogs.com/myqiao/archive/2011/07/13/2105800.html

增刪改查操作，其中增刪改操作被稱為資料操作語言 DML，相對來說簡單一點。查操作相對來說複雜一點，涉及到很多子句，所以這篇先講增刪改操作，以例子為主，後面再講查操作。

一、插入資料 INSERT INTO 表（列…） VALUES（值…）

根據前面幾篇的內容，我們可以很輕送的創建一個資料表，並向其中插入一些資料，不多說，看例子：

myqiao@ubuntu:~/My Documents/db$ sqlite3 test.db
— Loading resources from /home/myqiao/.sqliterc
SQLite version 3.7.4
Enter “.help” for instructions
Enter SQL statements terminated with a “;”
sqlite> .tables
sqlite>
sqlite> CREATE TABLE Teachers(
…> Id integer PRIMARY KEY,
…> Name text NOT NULL,
…> Age integer CHECK(Age>22),
…> Country text DEFAULT ‘USA’);
sqlite> .tables
Teachers
sqlite>
sqlite> INSERT INTO Teachers VALUES(1,’Alice’,25,’CHN’);
sqlite> INSERT INTO Teachers VALUES(2,’Bob’,25,’BRA’);
sqlite> INSERT INTO Teachers(Id,Name,Age,Country) VALUES(3,’Charls’,33,’USA’);
sqlite> INSERT INTO Teachers(Name,Age) VALUES(‘Jhon’,43);
sqlite> SELECT * FROM Teachers;
Id Name Age Country
—- ————— ————— —————
1 Alice 25 CHN
2 Bob 25 BRA
3 Charls 33 USA
4 Jhon 43 USA
sqlite>

很簡單，創建了一個 Teachers 表並向其中添加了四條資料，設定了一些約束，其中有自動增加的主鍵、預設值等等。

二、修改資料 UPDATE 表 SET 列 = ‘新值’ 【WHERE 條件陳述式】

UPDATE 語句用來更新表中的某個列，如果不設定條件，則所有記錄的這一列都被更新；如果設定了條件，則符合條件的記錄的這一列被更新， WHERE 子句被用來設定條件，如下例：

sqlite>
sqlite> SELECT * FROM Teachers;
Id Name Age Country
—- ————— ————— —————
1 Alice 25 CHN
2 Bob 25 BRA
3 Charls 33 USA
4 Jhon 43 USA
sqlite>
sqlite>
sqlite> UPDATE Teachers SET Country=’China’;
sqlite> SELECT * FROM Teachers;
Id Name Age Country
—- ————— ————— —————
1 Alice 25 China
2 Bob 25 China
3 Charls 33 China
4 Jhon 43 China
sqlite>
sqlite>
sqlite> UPDATE Teachers SET Country=’America’ WHERE Id=3;
sqlite> SELECT * FROM Teachers;
Id Name Age Country
—- ————— ————— —————
1 Alice 25 China
2 Bob 25 China
3 Charls 33 America
4 Jhon 43 China
sqlite>
sqlite>
sqlite> UPDATE Teachers SET Country=’India’ WHERE Age<30;
sqlite> SELECT * FROM Teachers;
Id Name Age Country
—- ————— ————— —————
1 Alice 25 India
2 Bob 25 India
3 Charls 33 America
4 Jhon 43 China
sqlite>

三、刪除資料 DELETE FROM 表【WHERE 條件陳述式】

如果設定 WHERE 條件子句，則刪除符合條件的資料記錄；如果沒有設定條件陳述式，則刪除所有記錄

sqlite>
sqlite> SELECT * FROM Teachers;
Id Name Age Country
—- ————— ————— —————
1 Alice 25 India
2 Bob 25 India
3 Charls 33 America
4 Jhon 43 China
sqlite>
sqlite>
sqlite> DELETE FROM Teachers WHERE Age>30;
sqlite> SELECT * FROM Teachers;
Id Name Age Country
—- ————— ————— —————
1 Alice 25 India
2 Bob 25 India
sqlite>
sqlite>
sqlite> DELETE FROM Teachers;
sqlite> SELECT * FROM Teachers;
sqlite>

四、查找數據 SELECT 列… FROM 表

為了後面的練習，需要一些樣本資料。首先將下面的 SQL 語句保存到 data.sql 文件中

複製代碼
BEGIN TRANSACTION;
CREATE TABLE Cars(Id integer PRIMARY KEY, Name text, Cost integer);
INSERT INTO Cars VALUES(1,’Audi’,52642);
INSERT INTO Cars VALUES(2,’Mercedes’,57127);
INSERT INTO Cars VALUES(3,’Skoda’,9000);
INSERT INTO Cars VALUES(4,’Volvo’,29000);
INSERT INTO Cars VALUES(5,’Bentley’,350000);
INSERT INTO Cars VALUES(6,’Citroen’,21000);
INSERT INTO Cars VALUES(7,’Hummer’,41400);
INSERT INTO Cars VALUES(8,’Volkswagen’,21600);
COMMIT;

BEGIN TRANSACTION;
CREATE TABLE Orders(Id integer PRIMARY KEY, OrderPrice integer CHECK(OrderPrice>0),
Customer text);
INSERT INTO Orders(OrderPrice, Customer) VALUES(1200, “Williamson”);
INSERT INTO Orders(OrderPrice, Customer) VALUES(200, “Robertson”);
INSERT INTO Orders(OrderPrice, Customer) VALUES(40, “Robertson”);
INSERT INTO Orders(OrderPrice, Customer) VALUES(1640, “Smith”);
INSERT INTO Orders(OrderPrice, Customer) VALUES(100, “Robertson”);
INSERT INTO Orders(OrderPrice, Customer) VALUES(50, “Williamson”);
INSERT INTO Orders(OrderPrice, Customer) VALUES(150, “Smith”);
INSERT INTO Orders(OrderPrice, Customer) VALUES(250, “Smith”);
INSERT INTO Orders(OrderPrice, Customer) VALUES(840, “Brown”);
INSERT INTO Orders(OrderPrice, Customer) VALUES(440, “Black”);
INSERT INTO Orders(OrderPrice, Customer) VALUES(20, “Brown”);
COMMIT;
複製代碼
然後在在終端執行命令 .read data.sql，將資料導入到資料庫中

sqlite>
sqlite> .tables
Friends
sqlite> .read data.sql
sqlite> .tables
Cars Orders Teachers
sqlite>

可以看到，Cars 表和 Orders 表已經導入到資料庫中，現在可以查詢了

sqlite>
sqlite> SELECT * FROM Cars;
Id Name Cost
—- ————— —————
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000
6 Citroen 21000
7 Hummer 41400
8 Volkswagen 21600
sqlite> SELECT * FROM Orders;
Id OrderPrice Customer
—- ————— —————
1 1200 Williamson
2 200 Robertson
3 40 Robertson
4 1640 Smith
5 100 Robertson
6 50 Williamson
7 150 Smith
8 250 Smith
9 840 Brown
10 440 Black
11 20 Brown
sqlite>

五、限制返回數量 SELECT 列… FROM 表 LIMIT 數量 OFFSET 位置

有時候資料庫中的資料太多，全部返回可不行，可以限制返回的數量，還可以設定返回的起始位置，如下：

sqlite>
sqlite> SELECT * FROM Cars LIMIT 4;
Id Name Cost
—- ————— —————
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
sqlite>
sqlite> SELECT * FROM Cars LIMIT 4 OFFSET 2;
Id Name Cost
—- ————— —————
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000
6 Citroen 21000
sqlite>

六、別名 SELECT 列 AS 別名，列 AS 別名 FROM

我們可以給返回資料集中的某些列起一個比較直觀的名字，比如把 Cost 改為”Price Of Car”

sqlite>
sqlite> SELECT Name , Cost AS ‘Price Of Car’ FROM Cars;
Name Price Of Car
—- —————
Audi 52642
Merc 57127
Skod 9000
Volv 29000
Bent 350000
Citr 21000
Humm 41400
Volk 21600
sqlite>

七、條件查詢 SELECT 列 FROM 表【WHERE 條件陳述式】

一般的條件陳述式都是大於、小於、等於之類的，這裡有幾個特別的條件陳述式

LIKE
LIKE 用萬用字元匹配字串
底線 _ 匹配一個字串
百分號 % 匹配多個字串
LIKE 匹配字串時不區分大小寫
sqlite>
sqlite> SELECT * FROM Cars WHERE Name Like ‘____’;
Id Name Cost
—- ————— —————
1 Audi 52642
sqlite>
sqlite> SELECT * FROM Cars WHERE Name Like ‘%en’;
Id Name Cost
—- ————— —————
6 Citroen 21000
8 Volkswagen 21600
sqlite>
sqlite> SELECT * FROM Cars WHERE Name Like ‘%EN’;
Id Name Cost
—- ————— —————
6 Citroen 21000
8 Volkswagen 21600
sqlite>

GLOB
GLOB 用萬用字元匹配字串
底線 ? 匹配一個字串
百分號 * 匹配多個字串
LIKE 匹配字串時,區分大小寫
BETWEEN 值1 AND 值2
返回兩個值之間的資料集合。下面的語句查詢價格在 20000 到 55000 之間的車，都是好車啊。

sqlite>
sqlite> SELECT * FROM Cars WHERE Cost BETWEEN 20000 AND 55000;
Id Name Cost
—- ————— —————
1 Audi 52642
4 Volvo 29000
6 Citroen 21000
7 Hummer 41400
8 Volkswagen 21600
sqlite>

IN （集合）
對應列的值必須在集合中。下面的語句查找奧迪和悍馬的價格。

sqlite>
sqlite> SELECT * FROM Cars WHERE Name IN (‘Audi’,’Hummer’);
Id Name Cost
—- ————— —————
1 Audi 52642
7 Hummer 41400
sqlite>

八、排序 ORDER BY 列 ASC (DESC)

指定某個列進行排序，ASC 為昇冪，DESC 為降冪。下面的語句查詢汽車品牌和價格，並以價格排序

sqlite>
sqlite> SELECT Name, Cost FROM Cars ORDER BY Cost DESC;
Name Cost
—- —————
Bent 350000
Merc 57127
Audi 52642
Humm 41400
Volv 29000
Volk 21600
Citr 21000
Skod 9000
sqlite>

九、區分 DISTINCT 列

有一些欄位的值可能會出現重複，比如訂單表中，一個客戶可能會有好幾份訂單，因此客戶的名字會重複出現。

到底有哪些客戶下了訂單呢？下面的語句將客戶名字區分出來。

sqlite>
sqlite> Select * FROM Orders;
Id OrderPrice Customer
—- ————— —————
1 1200 Williamson
2 200 Robertson
3 40 Robertson
4 1640 Smith
5 100 Robertson
6 50 Williamson
7 150 Smith
8 250 Smith
9 840 Brown
10 440 Black
11 20 Brown
sqlite>
sqlite> SELECT DISTINCT Customer FROM ORDERS;
Customer
—————
Black
Brown
Robertson
Smith
Williamson
sqlite>

十、分組 GROUP BY 列

分組和前面的區分有一點類似。區分僅僅是為了去掉重複項，而分組是為了對各類不同項進行統計計算。

比如上面的例子，我們區分出 5 個客戶，這 5 個客戶一共下了 11 個訂單，說明很多客戶都下了不止一個訂單。

下面的語句統計每個客戶在訂單上總共花費了多少錢。

sqlite>
sqlite> SELECT sum(OrderPrice) AS Total, Customer FROM Orders GROUP BY Customer;
Total Customer
————— —————
440 Black
860 Brown
340 Robertson
2040 Smith
1250 Williamson
sqlite>

這裡 Sum 是 SQLite 內置的統計函數，在這個例子中用來求每個顧客的訂單價格的和。

統計結果也可以設定返回條件，但是不能用 WHERE 子句，而是用 HAVING 子句，如下例，返回訂單總額大於 1000 的顧客。

sqlite>
sqlite> SELECT sum(OrderPrice) AS Total, Customer FROM Orders
…> GROUP BY Customer HAVING sum(OrderPrice)>1000;
Total Customer
————— —————
2040 Smith
1250 Williamson
sqlite>

十一、邏輯運算子

有的查詢涉及的條件陳述式很複雜，是有好幾個條件陳述式經過邏輯運算得來的，一共有三種邏輯運算子：

AND
OR
NOT
一般稍微瞭解點程式設計知識的應該都沒問題。