SQLite 終端相關命令，sqlite終端命令

SQLite 終端相關命令，sqlite終端命令
  

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

USERsqlite> drop table USER;//刪除表

sqlite> create table if not exists USER(id,name,score);

sqlite> .table

USERsqlite> 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|陳喬恩|90sqlite> 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|陳喬恩|89sqlite> select name,score from USER;//查詢 name,score

蒲亭|90

神鵰|100

楊過|150

王麗坤|90

陳喬恩|89sqlite> select * from USER limit3;//查詢前三項

1|蒲亭|90

3|神鵰|100

4|楊過|150

5|王麗坤|90

2|陳喬恩|89sqlite> 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 integer primary 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|陳喬恩|黯然銷魂掌|89sqlite> 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