sqLite 实例

insert into payIncome(time,inout,money,type) values("2014-05-12","支出",25.5,"衣服");

insert into payIncome(time,inout,money,type) values("2014-05-12","支出",35.5,"饮食");

insert into payIncome(time,inout,money,type) values("2014-05-12","收入",135.5,"打工");

insert into payIncome(time,inout,money,type) values("2014-05-12","收入",245.5,"工资");

insert into payIncome(time,inout,money,type) values("2013-05-12","收入",3245.5,"奖金");

insert into payIncome(time,inout,money,type) values("2014-04-12","支出",500,"学费");

.mode column

.mode list

.mode line

select * from payIncome;

ID         time        inout       money       type        content

---------- ----------  ----------  ---------- ----------  ----------

1          2014-05-12  支出        25.5        衣服

2          2014-05-12  支出        35.5        饮食

3          2014-05-12  收入        135.5       打工

4          2014-05-12  收入        245.5       工资

5          2013-05-12  收入        3245.5      奖金

6          2014-04-12  支出        500         学费

 

select inout,type,sum(money) as money frompayIncome where time like "2014-05%"

group by inout,type having sum(money) >100  order by sum(money) desc;

inout      type        money

---------- ----------  ----------

收入        工资        245.5

收入        打工        135.5

 

select inout,type,sum(money) as money frompayIncome group by inout,type having

sum(money) > 100  order by sum(money) desc;

inout      type        money

---------- ----------  ----------

收入        奖金        3245.5

支出        学费        500

收入        工资        245.5

收入        打工        135.5

 

select inout,type,sum(money) as money frompayIncome group by type,inout having

sum(money) > 100  order by sum(money) desc;

inout      type        money

---------- ----------  ----------

收入        奖金        3245.5

支出        学费        500

收入        工资        245.5

收入        打工        135.5

 

select inout,type,sum(money) as money frompayIncome group by type,inout order b

y sum(money) desc;

inout      type        money

---------- ----------  ----------

收入        奖金        3245.5

支出        学费        500

收入        工资        245.5

收入        打工        135.5

支出        饮食        35.5

支出        衣服        25.5

 

sqlite> select inout,type,sum(money) asmoney from payIncome group by type,inout

 order by sum(money) desc,inout;

select inout,type,sum(money) as money frompayIncome group by type,inout order b

y sum(money) desc,inout;

inout      type        money

---------- ----------  ----------

收入        奖金        3245.5

支出        学费        500

收入        工资        245.5

收入        打工        135.5

支出        饮食        35.5

支出        衣服        25.5

 

select inout,type,sum(money) as money frompayIncome group by type,inout order b

y sum(money) desc,inout desc;

inout      type        money

---------- ----------  ----------

收入        奖金        3245.5

支出        学费        500

收入        工资        245.5

收入        打工        135.5

支出        饮食        35.5

支出        衣服        25.5

 

delete from payIncome where money=500 andtype="学费" andinout="收入";

select inout,type,sum(money) as money frompayIncome group by type,inout order b

y sum(money) desc;

inout      type        money

---------- ----------  ----------

收入        奖金        3245.5

支出        学费        500

收入        工资        245.5

收入        打工        135.5

支出        饮食        35.5

支出        衣服        25.5

delete from payIncome where money=500 andtype="学费";

select inout,type,sum(money) as money frompayIncome group by type,inout order b

y sum(money) desc;

inout      type        money

---------- ----------  ----------

收入        奖金        3245.5

收入        工资        245.5

收入        打工        135.5

支出        饮食        35.5

支出        衣服        25.5

 

update payIncome set type="理发"  where money=25.5 andinout="收入";

select inout,type,sum(money) as money frompayIncome group by type,inout order b

y sum(money) desc;

inout      type        money

---------- ----------  ----------

收入        奖金        3245.5

收入        工资        245.5

收入        打工        135.5

支出        饮食        35.5

支出        衣服        25.5

 

update payIncome set type="理发"  where money=25.5 andinout="支出";

select inout,type,sum(money) as money frompayIncome group by type,inout order b

y sum(money) desc;

inout      type        money

---------- ----------  ----------

收入        奖金        3245.5

收入        工资        245.5

收入        打工        135.5

支出        饮食        35.5

支出        理发        25.5

 

create table user(id integer primary keyautoincrement,name varchar(10),pwd varc

har(10));

sqlite> .tables

.tables

android_metadata  payIncome user   incomeItem        payItem

 

sqlite> drop table user;

drop table user;

sqlite> .tables

.tables

android_metadata  incomeItem        payIncome         payItem

sqlite>