iOS開(kāi)發(fā)中常用的數(shù)據(jù)存儲(chǔ)方式主要有以下幾種:
1,XML屬性列表;2,Preference;3,歸檔;4,sqlite3;5,CoreData.
因?yàn)榻趧倱Q公司,公司項(xiàng)目的數(shù)據(jù)存儲(chǔ)主要由sqlite完成,項(xiàng)目主管也提前打了招呼,因?yàn)轫?xiàng)目中的數(shù)據(jù)存儲(chǔ)也會(huì)比較復(fù)雜,需要復(fù)習(xí)sqlite的知識(shí).所以本文主要復(fù)習(xí)sqlite的基本操作.
一:數(shù)據(jù)庫(kù)管理
test.db //創(chuàng)建數(shù)據(jù)庫(kù),創(chuàng)建表的時(shí)候才會(huì)真的創(chuàng)建數(shù)據(jù)庫(kù)
.schema //獲得數(shù)據(jù)庫(kù)的Schema信息
.output file.sql ; .dump; .output stdout //導(dǎo)出數(shù)據(jù)
.read file.sql //用來(lái)導(dǎo)入.dump命令創(chuàng)建的文件
test2.db < file.sql //備份數(shù)據(jù)庫(kù)
create table foods (id integer primary key,type_id integer,name test) //創(chuàng)建表
alter table foods add column email text not null default '' //修改表,添加列
二:數(shù)據(jù)庫(kù)查詢(xún)
過(guò)濾:
? ? ? ?select * from foods where name = 'egg' and type_id = 9; //支持邏輯操作符 AND,OR,NOT,IN
? ? ? ?select * from foods where name like '%egg%'; // %是貪婪匹配符
限定和排序
? ? ? ?select * from foods order by id limit 1 offset 1 //limit限制數(shù)量,offset 指定偏移的記錄數(shù)
? ? ? ?select * from foods where name like 'B%' order by type_id desc, name limit 10; //desc(降序),asc(默認(rèn)的升序)
函數(shù)和聚合
? ? ?函數(shù)包括upper(),lower(),length(),abs()等數(shù)學(xué)函數(shù),也包括聚合函數(shù):sum(),avg(),count(),min(),max().
? ? ?select count(*) from foods where type_id=1; //計(jì)算type_id = 1 的行的數(shù)量
分組(grouping)
? ? ?select type_id from foods group by type_id;
去掉重復(fù)
? ? select distinct type_id from foods;
多表連接,分為內(nèi)連接,交叉連接,外連接(外連接又分為左連接,右連接,全外連接),以及自然連接.
? ?select foods.name, food_types.name from foods, food_types where ? ? ? ?foods.type_id=food_types.id ;
? ? Select * From foods inner join food_types on foods.type_id = food_types.id; //內(nèi)連接
? select * from foods, food_types; //交叉連接
? ?select *from foods left outer join foods_episodes on ?foods.id=foods_episodes.food_id; //左外連接
子查詢(xún),子查詢(xún)是指select語(yǔ)句中又嵌套select語(yǔ)句
select count(*) from foods where type_id in (select id from food_types where name='Bakery' or name='Cereal');
