SQLite 入门教程增删改查

小灰灰 2022-08-11 09:58 361阅读 0赞

一、插入数据 INSERT INTO 表（列…） VALUES（值…）

根据前面几篇的内容，我们可以很轻送的创建一个数据表，并向其中插入一些数据，不多说，看例子：

  1. myqiao@ubuntu:~/My Documents/db$ sqlite3 test.db
  2. -- Loading resources from /home/myqiao/.sqliterc
  3. SQLite version 3.7.4
  4. Enter ".help" for instructions
  5. Enter SQL statements terminated with a ";"
  6. sqlite> .tables 
  7. sqlite>  
  8. sqlite> CREATE TABLE Teachers(
  9.    ...> Id integer PRIMARY KEY,
  10.    ...> Name text NOT NULL,
  11.    ...> Age integer CHECK(Age>22),
  12.    ...> Country text DEFAULT 'USA');
  13. sqlite> .tables
  14. Teachers
  15. sqlite> 
  16. sqlite> INSERT INTO Teachers VALUES(1,'Alice',25,'CHN');
  17. sqlite> INSERT INTO Teachers VALUES(2,'Bob',25,'BRA');
  18. sqlite> INSERT INTO Teachers(Id,Name,Age,Country) VALUES(3,'Charls',33,'USA');
  19. sqlite> INSERT INTO Teachers(Name,Age) VALUES('Jhon',43);
  20. sqlite> SELECT * FROM Teachers;
  21. Id    Name             Age              Country        
  22. ----  ---------------  ---------------  ---------------
  23. 1     Alice            25               CHN            
  24. 2     Bob              25               BRA            
  25. 3     Charls           33               USA            
  26. 4     Jhon             43               USA            
  27. sqlite>

很简单，创建了一个 Teachers 表并向其中添加了四条数据，设定了一些约束，其中有自动增加的主键、默认值等等。

二、修改数据 UPDATE 表 SET 列 = ‘新值’ 【WHERE 条件语句】

UPDATE 语句用来更新表中的某个列，如果不设定条件，则所有记录的这一列都被更新； 如果设定了条件，则符合条件的记录的这一列被更新， WHERE 子句被用来设定条件，如下例：

  1. sqlite>  
  2. sqlite> SELECT * FROM Teachers;
  3. Id    Name             Age              Country        
  4. ----  ---------------  ---------------  ---------------
  5. 1     Alice            25               CHN            
  6. 2     Bob              25               BRA            
  7. 3     Charls           33               USA            
  8. 4     Jhon             43               USA            
  9. sqlite> 
  10. sqlite> 
  11. sqlite> UPDATE Teachers SET Country='China';
  12. sqlite> SELECT * FROM Teachers;
  13. Id    Name             Age              Country        
  14. ----  ---------------  ---------------  ---------------
  15. 1     Alice            25               China          
  16. 2     Bob              25               China          
  17. 3     Charls           33               China          
  18. 4     Jhon             43               China   
  19. sqlite> 
  20. sqlite>       
  21. sqlite> UPDATE Teachers SET Country='America' WHERE Id=3;
  22. sqlite> SELECT * FROM Teachers;
  23. Id    Name             Age              Country        
  24. ----  ---------------  ---------------  ---------------
  25. 1     Alice            25               China          
  26. 2     Bob              25               China          
  27. 3     Charls           33               America        
  28. 4     Jhon             43               China          
  29. sqlite> 
  30. sqlite> 
  31. sqlite> UPDATE Teachers SET Country='India' WHERE Age<30;
  32. sqlite> SELECT * FROM Teachers;
  33. Id    Name             Age              Country        
  34. ----  ---------------  ---------------  ---------------
  35. 1     Alice            25               India          
  36. 2     Bob              25               India          
  37. 3     Charls           33               America        
  38. 4     Jhon             43               China     
  39. sqlite>

三、删除数据 DELETE FROM 表 【WHERE 条件语句】

如果设定 WHERE 条件子句，则删除符合条件的数据记录；如果没有设定条件语句，则删除所有记录

  1. sqlite> 
  2. sqlite> SELECT * FROM Teachers;
  3. Id    Name             Age              Country        
  4. ----  ---------------  ---------------  ---------------
  5. 1     Alice            25               India          
  6. 2     Bob              25               India          
  7. 3     Charls           33               America        
  8. 4     Jhon             43               China     
  9. sqlite> 
  10. sqlite> 
  11. sqlite> DELETE FROM Teachers WHERE Age>30;
  12. sqlite> SELECT * FROM Teachers;
  13. Id    Name             Age              Country        
  14. ----  ---------------  ---------------  ---------------
  15. 1     Alice            25               India          
  16. 2     Bob              25               India  
  17. sqlite> 
  18. sqlite>       
  19. sqlite> DELETE FROM Teachers;
  20. sqlite> SELECT * FROM Teachers;
  21. 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，将数据导入到数据库中

  1. sqlite>  
  2. sqlite> .tables
  3. Friends
  4. sqlite> .read data.sql
  5. sqlite> .tables
  6. Cars      Orders    Teachers
  7. sqlite>

可以看到，Cars 表和 Orders 表已经导入到数据库中，现在可以查询了

  1. sqlite>  
  2. sqlite> SELECT * FROM Cars;
  3. Id    Name             Cost           
  4. ----  ---------------  ---------------
  5. 1     Audi             52642          
  6. 2     Mercedes         57127          
  7. 3     Skoda            9000           
  8. 4     Volvo            29000          
  9. 5     Bentley          350000         
  10. 6     Citroen          21000          
  11. 7     Hummer           41400          
  12. 8     Volkswagen       21600 
  13. sqlite> SELECT * FROM Orders;
  14. Id    OrderPrice       Customer       
  15. ----  ---------------  ---------------
  16. 1     1200             Williamson     
  17. 2     200              Robertson      
  18. 3     40               Robertson      
  19. 4     1640             Smith          
  20. 5     100              Robertson      
  21. 6     50               Williamson     
  22. 7     150              Smith          
  23. 8     250              Smith          
  24. 9     840              Brown          
  25. 10    440              Black          
  26. 11    20               Brown 
  27. sqlite>

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

有时候数据库中的数据太多，全部返回可不行，可以限制返回的数量，还可以设定返回的起始位置，如下：

  1. sqlite>  
  2. sqlite> SELECT * FROM Cars LIMIT 4;
  3. Id    Name             Cost           
  4. ----  ---------------  ---------------
  5. 1     Audi             52642          
  6. 2     Mercedes         57127          
  7. 3     Skoda            9000           
  8. 4     Volvo            29000          
  9. sqlite> 
  10. sqlite> SELECT * FROM Cars LIMIT 4 OFFSET 2;
  11. Id    Name             Cost           
  12. ----  ---------------  ---------------
  13. 3     Skoda            9000           
  14. 4     Volvo            29000          
  15. 5     Bentley          350000         
  16. 6     Citroen          21000           
  17. sqlite>

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

我们可以给返回数据集中的某些列起一个比较直观的名字，比如把 Cost 改为”Price Of Car”

  1. sqlite>  
  2. sqlite> SELECT Name , Cost AS 'Price Of Car' FROM Cars;
  3. Name  Price Of Car   
  4. ----  ---------------
  5. Audi  52642          
  6. Merc  57127          
  7. Skod  9000           
  8. Volv  29000          
  9. Bent  350000         
  10. Citr  21000          
  11. Humm  41400          
  12. Volk  21600            
  13. 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 之间的车，都是好车啊。

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

IN （集合）

对应列的值必须在集合中。下面的语句查找奥迪和悍马的价格。

  1. sqlite>  
  2. sqlite> SELECT * FROM Cars WHERE Name IN ('Audi','Hummer');
  3. Id    Name             Cost           
  4. ----  ---------------  ---------------
  5. 1     Audi             52642          
  6. 7     Hummer           41400         
  7. sqlite>

八、 排序 ORDER BY 列 ASC (DESC)

指定某个列进行排序，ASC 为升序，DESC 为降序。下面的语句查询汽车品牌和价格，并以价格排序

  1. sqlite>  
  2. sqlite> SELECT Name, Cost FROM Cars ORDER BY Cost DESC;
  3. Name  Cost           
  4. ----  ---------------
  5. Bent  350000         
  6. Merc  57127          
  7. Audi  52642          
  8. Humm  41400          
  9. Volv  29000          
  10. Volk  21600          
  11. Citr  21000          
  12. Skod  9000       
  13. sqlite>

九、 区分 DISTINCT 列

有一些字段的值可能会出现重复，比如订单表中，一个客户可能会有好几份订单，因此客户的名字会重复出现。

到底有哪些客户下了订单呢？下面的语句将客户名字区分出来。

  1. sqlite>  
  2. sqlite> Select * FROM Orders;
  3. Id    OrderPrice       Customer       
  4. ----  ---------------  ---------------
  5. 1     1200             Williamson     
  6. 2     200              Robertson      
  7. 3     40               Robertson      
  8. 4     1640             Smith          
  9. 5     100              Robertson      
  10. 6     50               Williamson     
  11. 7     150              Smith          
  12. 8     250              Smith          
  13. 9     840              Brown          
  14. 10    440              Black          
  15. 11    20               Brown        
  16. sqlite> 
  17. sqlite> SELECT DISTINCT Customer FROM ORDERS;
  18. Customer       
  19. ---------------
  20. Black          
  21. Brown          
  22. Robertson      
  23. Smith          
  24. Williamson       
  25. sqlite>

十、 分组 GROUP BY 列

分组和前面的区分有一点类似。区分仅仅是为了去掉重复项，而分组是为了对各类不同项进行统计计算。

比如上面的例子，我们区分出 5 个客户，这 5 个客户一共下了 11 个订单，说明很多客户都下了不止一个订单。

下面的语句统计每个客户在订单上总共花费了多少钱。

  1. sqlite>  
  2. sqlite> SELECT sum(OrderPrice) AS Total, Customer FROM Orders GROUP BY Customer;
  3. Total            Customer       
  4. ---------------  ---------------
  5. 440              Black          
  6. 860              Brown          
  7. 340              Robertson      
  8. 2040             Smith          
  9. 1250             Williamson      
  10. sqlite>

这里 Sum 是 SQLite 内置的统计函数，在这个例子中用来求每个顾客的订单价格的和。

统计结果也可以设定返回条件，但是不能用 WHERE 子句，而是用 HAVING 子句，如下例，返回订单总额大于 1000 的顾客。

  1. sqlite>  
  2. sqlite> SELECT sum(OrderPrice) AS Total, Customer FROM Orders 
  3.    ...>         GROUP BY Customer HAVING sum(OrderPrice)>1000;
  4. Total            Customer       
  5. ---------------  ---------------
  6. 2040             Smith          
  7. 1250             Williamson     
  8. sqlite>

十一、 逻辑运算符

有的查询涉及的条件语句很复杂，是有好几个条件语句经过逻辑运算得来的，一共有三种逻辑运算符：

  • AND
  • OR
  • NOT

一般稍微了解点编程知识的应该都没问题。

发表评论

表情：
评论列表 （有 0 条评论，361人围观）

还没有评论，来说两句吧...

相关阅读

    相关 SQLite 入门教程增删改查

    一、插入数据 INSERT INTO 表（列...） VALUES（值...） 根据前面几篇的内容，我们可以很轻送的创建一个数据表，并向其中插入一些数据，不多说，看例子：

    小灰灰小灰灰/ 0 赞/ 362 阅读