SQLite 入門教程(四)增刪改查,有講究

SQLite 入門教程(四)增刪改查,有講究

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
一般稍微瞭解點程式設計知識的應該都沒問題。

 

 

 

發表迴響

你的電子郵件位址並不會被公開。 必要欄位標記為 *