SQLite 入门教程四  增删改查有讲究-木庄网络博客

本文整理自网络，侵删。

一、插入数据 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>

阅读剩余部分