SQLite ALL
Last login: Fri Dec 5 09:52:08 on ttys002
BeSilent:~ qianfeng$ sqlite3 data.db
SQLite version 3.7.13 2012-07-17 17:46:21
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table USER(id,name,score);//创建表
sqlite> .table
USER
sqlite> create table if not exists USER(id,name,score);//创建表
sqlite> .table
USER
sqlite> drop table USER;//删除表
sqlite> create table if not exists USER(id,name,score);
sqlite> .table
USER
sqlite> insert into USER values(1,"普京",90);//插入
sqlite> insert into USER values(2,"奥巴马",50);
sqlite> select *from USER
...> ;
1|普京|90
2|奥巴马|50
sqlite> insert into USER values(3,"神雕",100);
sqlite> insert into USER values(4,"杨过",150);
sqlite> insert into USER values(5,"王丽坤",90);
sqlite> select * from USER;//查询
1|普京|90
2|奥巴马|50
3|神雕|100
4|杨过|150
5|王丽坤|90
sqlite> insert into USER values(6,"陈乔恩",90);
sqlite> select * from USER;
1|普京|90
2|奥巴马|50
3|神雕|100
4|杨过|150
5|王丽坤|90
6|陈乔恩|90
sqlite> delete from USER where id=2;//删除元素
sqlite> select * from USER;
1|普京|90
3|神雕|100
4|杨过|150
5|王丽坤|90
6|陈乔恩|90
sqlite> update USER set id=2 where id=6;//修改
sqlite> select *from USER;
1|普京|90
3|神雕|100
4|杨过|150
5|王丽坤|90
2|陈乔恩|90
sqlite> update USER set score= 89 where id=2;//修改
sqlite> select *from USER;
1|普京|90
3|神雕|100
4|杨过|150
5|王丽坤|90
2|陈乔恩|89
sqlite> select name,score from USER;//查询name,score
普京|90
神雕|100
杨过|150
王丽坤|90
陈乔恩|89
sqlite> select * from USER limit3;//查询前三项
1|普京|90
3|神雕|100
4|杨过|150
5|王丽坤|90
2|陈乔恩|89
sqlite> select * from USER order by id;//通过id 升序查询
1|普京|90
2|陈乔恩|89
3|神雕|100
4|杨过|150
5|王丽坤|90
sqlite> select *from USER;
1|普京|90
3|神雕|100
4|杨过|150
5|王丽坤|90
2|陈乔恩|89
sqlite> select * from USER order by score desc;//降序查询
4|杨过|150
3|神雕|100
1|普京|90
5|王丽坤|90
2|陈乔恩|89
sqlite> select * from USER order by name limit 3;
1|普京|90
4|杨过|150
5|王丽坤|90
sqlite> select count(*)from USER;//查询count(*)
5
sqlite> select sum(score) from USER;//查询sum(score)
519
sqlite> select avg(score) from USER;//查询avg(score)
103.8
sqlite> create table if not exists WUGONG(wid integerPRimary key autoincrement,name,uid);//wid integer primary key auto increment,多表，主键，外键，唯一键
sqlite> insert into WUGONG(name,uid) values("葵花",5);
sqlite> insert into WUGONG(name,uid) values("独孤九剑",4);
sqlite> insert into WUGONG(name,uid) values("降龙十八掌",3);
sqlite> insert into WUGONG(name,uid) values("黯然销魂掌",2);
sqlite> insert into WUGONG(name,uid) values("一阳指",1);
sqlite> select *from USER;
1|普京|90
3|神雕|100
4|杨过|150
5|王丽坤|90
2|陈乔恩|89
sqlite> select USER.id,USER.name,WUGONG.name,USER.score from USER,WUGONG where USER.id=WUGONG.uid;//多表查询
1|普京|一阳指|90
3|神雕|降龙十八掌|100
4|杨过|独孤九剑|150
5|王丽坤|葵花|90
2|陈乔恩|黯然销魂掌|89
sqlite> select USER.id,USER.name,WUGONG.name,USER.score from USER,WUGONG where USER.id=WUGONG.uid order by USER.score;//多表查询按成绩升序（asc）
2|陈乔恩|黯然销魂掌|89
1|普京|一阳指|90
5|王丽坤|葵花|90
3|神雕|降龙十八掌|100
4|杨过|独孤九剑|150