sqlite实践

以下sqlite是对 该blog 内容的实践过程

内容：
- 创建数据库
- 创建表
- 在表中插入字段
- 增加
- 查找
- 更新
- 删除

(ccdk-1.5.2alpha)[root@Router-B sqlite]# sqlite3 foo.db
SQLite version 3.14.2 2016-09-12 18:50:49
Enter ".help" for usage hints.
sqlite> create table film(title,length,year,starring);
sqlite> create index film_title_index on film(title);
sqlite> insert into film values ('Silence of the Lambs, The', 118, 1991, 'Jodie Foster');
sqlite> insert into film values ('Contact', 153, 1997, 'Jodie Foster');insert into film values ('Crouching Tiger, Hidden Dragon', 120, 2000, 'Yun-Fat Chow');insert into film values ('Hours, The', 114, 2002, 'Nicole Kidman');
sqlite> select * from film;
Silence of the Lambs, The|118|1991|Jodie Foster
Contact|153|1997|Jodie Foster
Crouching Tiger, Hidden Dragon|120|2000|Yun-Fat Chow
Hours, The|114|2002|Nicole Kidman
sqlite> select * from film limit 10;
Silence of the Lambs, The|118|1991|Jodie Foster
Contact|153|1997|Jodie Foster
Crouching Tiger, Hidden Dragon|120|2000|Yun-Fat Chow
Hours, The|114|2002|Nicole Kidman
sqlite> select * from film limit 1;
Silence of the Lambs, The|118|1991|Jodie Foster
sqlite> select * from film order by year limit 10;
Silence of the Lambs, The|118|1991|Jodie Foster
Contact|153|1997|Jodie Foster
Crouching Tiger, Hidden Dragon|120|2000|Yun-Fat Chow
Hours, The|114|2002|Nicole Kidman
sqlite> select * from film order by year desc limit 10;
Hours, The|114|2002|Nicole Kidman
Crouching Tiger, Hidden Dragon|120|2000|Yun-Fat Chow
Contact|153|1997|Jodie Foster
Silence of the Lambs, The|118|1991|Jodie Foster
sqlite> select title, year from film order by year desc limit 10;
Hours, The|2002
Crouching Tiger, Hidden Dragon|2000
Contact|1997
Silence of the Lambs, The|1991
sqlite> select * from film where starring='Jodie Foster';
Silence of the Lambs, The|118|1991|Jodie Foster
Contact|153|1997|Jodie Foster
sqlite> select * from film where starring like 'Jodie%';
Silence of the Lambs, The|118|1991|Jodie Foster
Contact|153|1997|Jodie Foster
sqlite> elect title, year from film where starring like 'Jodie%' and year >= 1985 order by year desc limit 10;
Error: near "elect": syntax error
sqlite> select title, year from film where starring like 'Jodie%' and year >= 1985 order by year desc limit 10;
Contact|1997
Silence of the Lambs, The|1991
sqlite> select count(*) from film;
4
sqlite> select * from film;
Silence of the Lambs, The|118|1991|Jodie Foster
Contact|153|1997|Jodie Foster
Crouching Tiger, Hidden Dragon|120|2000|Yun-Fat Chow
Hours, The|114|2002|Nicole Kidman
sqlite> select * from film;
Silence of the Lambs, The|118|1991|Jodie Foster
Contact|153|1997|Jodie Foster
Crouching Tiger, Hidden Dragon|120|2000|Yun-Fat Chow
Hours, The|114|2002|Nicole Kidman
sqlite> 
sqlite> update film set starring='Jodie Foster' where starring='Jodee Foster';
sqlite> select * from film;
Silence of the Lambs, The|118|1991|Jodie Foster
Contact|153|1997|Jodie Foster
Crouching Tiger, Hidden Dragon|120|2000|Yun-Fat Chow
Hours, The|114|2002|Nicole Kidman
sqlite> delete from film where year < 2000;
sqlite> select * from film;
Crouching Tiger, Hidden Dragon|120|2000|Yun-Fat Chow
Hours, The|114|2002|Nicole Kidman
sqlite> 

源地址在 here