Sqlite使用命令个别大全

有表两个boss 和emp表

emp表

name     id      gongzi       bumendaima

zhang     1        9000              10

li              2        9000               20

zhao       3        1999               20

yun         4        1822               30

hai          5        2000               20

long        6       1299               10

hou         7        6890              30

jie            8       9088               20

boss表

name                    id                bumen                      daima

yun                         1         Factory Marketing            30         long                        2         Money Manage                10hai                          3         Person Board                   20             首先建立表create table emp(name varchar(10),id integer primary key autoincrement,gongzi int,bumendaima int);create tabel boss(name varchar(10),id int,bumen varchar(20),daima int);              查看表结构 .schema  table名字规格化显示表.mode tabs               搜索个别列是空的:

select * from emp where name is null;

select * from emp where name is not null;

相当智能

删除个别：

delete from emp where name=hou;

更改个别：

update emp set bumendaima=20 where name=hou;

增加一列：

alter table emp add  age int;

alter table emp add age text default '8088';

以部门代码为关键字分组：

select id ,bumendaima ,count(*),sum(gongzi)  from emp group by bumendaima;//count统计人数,sum计算工资和

两个表进行内连接查询显示：

select  id,name,bumen from boss,emp where boss.daima=emp.bumendaima;

select * from boss,emp where boss.daima=emp.bumendaima;

select * from boss inner join emp on boss.daima=emp.bumendaima;
查询表中id为1,3，5的信息

select * from emp where id int(1,3,5);

查询emp中1到5的字段信息

select * from emp where id between 1 and 5;

查询id=5的工资排名

select count(*)+1 from emp where gongzi>(select gongzi from emp where id=5);

太多了改天写