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>
五、 限制返回数量 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
一般稍微了解点编程知识的应该都没问题。
上一篇： SQLite3 命令行操作指南