聊聊SQLite - 基础篇
SQLiteSQLite是一个关系型数据库,是一个嵌入式的数据库,它的核心代码由C编写,直接在宿主应用中运行。 本来想一篇长文章涵盖想写的内容,最后发现想写的东西太多了,就拆分成:基础篇,原理篇和iOS应用篇发出来吧。 MAC OS的命令行字带SQLite。以下示例均在命令行中进行,C API的使用会在之后的文章里讲解: 表
命令行新建一个数据库: sqlite3 person.sqlite3
然后,新建一个表,并且插入两条数据 create table person(id integer primary key,name text not null,phone text not null default 'unknown');
insert into person (id,name,phone) values(10001,'Leo','1234578901');
insert into person (id,name) values(10002,'Lina');
调整命令行显示模式: .mode column
.headers on
然后,我们查询下当前数据库中的数据 select * from person;
结果 id name phone
---------- ---------- ----------
10001 Leo 1234578901
10002 Lina unknown
删除表 drop table table_name
主键
在创建数据库的时候,我们可以设置主键,通过 create table person(id integer primary key,phone text not null default 'unknown');
这里,用户id是数据库的主键,因为它是唯一的。当你尝试插入一行,这行的主键在数据库中已存在的时候,会报错。 insert into person (id,name) values(10001,'Tom');
Error: UNIQUE constraint failed: person.id
insert into person (name) values('Tom');实际插入了这样一行
id name phone
---------- ---------- ----------
10003 Tom unknown
Select在查询之前,我们往数据库里再插入一些数据,方便我们做查询。此时数据库中的数据如下 id name phone
---------- ---------- ----------
10001 Leo 1234578901
10002 Lina unknown
10003 Tom unknown
10004 Lily unknown
10005 Lucy unknown
10006 Jone unknown
10007 Kobe unknown
10008 Jeccy unknown
10009 Smith 1234567890
数据库查询语句,也就是select是SQL中最复杂的命令。select命令的通用形式如下: select [distinct] heading from tables where predicate group by columns having predicate order by columns limit count,offset;
这里的from,where等关键字都是一个单独的子句,每个子句由关键字和跟随参数组成。在理解select语句的时候,可以把整个过程当成一个管道处理
wherewhere子句由 where + 断言组成。 比如: sqlite> select name
...> from person
...> where phone!='unknown' and id>10005;
//结果
name
----------
Smith
断言也支持对字符串进行正则匹配:比如 where name like ‘L%’。表示以L开头的字符串 Orderorder后跟着列名,用来排序。 比如: 查询id>10004的数据,结果按照name降序排列 sqlite> select *
...> from person
...> where id>10004
...> order by name DESC;
结果: id name phone
---------- ---------- -----------
10009 Smith 12345678902
10005 Lucy unknown
10007 Kobe unknown
10006 Jone unknown
10008 Jeccy unknown
limit & offset
比如: 查询id>10004的数据,结果按照name降序排列,取第二个数据。 sqlite> select *
...> from person
...> where id>10004
...> order by Name DESC
...> limit 1 offset 1;
结果 id name phone
---------- ---------- ----------
10005 Lucy unknown
函数 & 聚合SQLite内置和许多函数,比如绝对值abs,大小写转换upper/lower。 比如 sqlite> select upper(name) from person limit 2;
upper(name) -----------
LEO
LINA
还有一类特殊的函数是聚合函数,包括:sum,count,min,max,avg。 比如,查询数据库中行数 sqlite> select count(*) from person; count(*)
----------
9
group & having聚合的主要作用是在分组上面,也就是group关键字。group关键字后跟着列名。举个例子: id name phone
---------- ---------- ----------
10001 Leo 1234578901
10002 Lina unknown
10003 Tom unknown
10004 Lily unknown
10005 Lucy unknown
10006 Jone unknown
10007 Kobe unknown
10008 Jeccy unknown
10009 Smith 1234567890
这是数据,假如按照phone分组,phone一样的行都会被分到一组里,一共分为三组,然后用count就可以统计出每组数据的数量了。 sqlite> select phone,count(*) from person group by phone; phone count(*)
----------- ----------
12345678902 1
1234578901 1
unknown
之前的where是对行进行过滤,而having是对分组后的一组数据进行过滤。比如 sqlite> select phone,count(*) from person group by phone having count(*) > 1;
phone count(*) ---------- ---------- unknown 7
内链接以上的select都是在一个表上进行的操作,而实际应用中,我们经常需要在两个表上操作。 假设我们还有一个teacher的表: create table teacher(id integer primary key,class text not null,course text,person_id integer not null,foreign key(person_id) references person(id));
默认SQLite的外键约束是关闭的,需要手动开启: PRAGMA FOREIGN_KEYS=ON;
然后,我们向teacher表中插入一些数据后, id class course person_id
---------- ---------- ---------- ----------
1 grade1 math 10001
2 grade1 science 10002
3 grade2 chinese 10003
这时候,我想知道所有教一年级的老师的名字和电话,我就需要链接查询两个表。
举例: sqlite> select person.name,teacher.class from person inner join teacher on person.id=teacher.person_id;
name class
---------- ----------
Leo grade1
Lina grade1
Tom grade2
外连接
外连接分为三种:左外连接,右外连接,和全外连接。 以左外连接为例: select person.name,teacher.class from person left outer join teacher on person.id=teacher.person_id;
name class
---------- ----------
Amanda
Jeccy
Jone
Jonese
Kobe
Leo grade1
Lily
Lina grade1
Lucy
Smith
Tom grade2
可以看到,在结果集中,除了内链接的所有结果,还包括了左表(person)的所有行。对应右表无法提供结果以null返回。 右外连接的原理类似,只不过额外的数据是右表。而全外连接则是左右外连接的结合。
索引有如下查询: select * from person where name='Leo'
在实际查询的时候,SQLite会逐行扫描所有的行,然后取匹配
创建一个索引: create index index_name on person(name);
查看索引(命令行): .schema person
CREATE TABLE person(id integer primary key,phone text not null default 'unknown');
CREATE INDEX index_name on person(name);
删除索引 drop index index_name
事务
理解事务有一个很典型的例子:银行把用户A的钱转账到用户B的账户里。这个过程需要两步数据库操作:
假如第一步成功了,而第二步失败了,那么这笔钱就凭空消失了。 当把这两个操作包装到一个事务的时候,第二步失败后,第一步会自动回滚,保证原子性。 使用事务: begin //SQL commit
除了保证操作的原子性,事务还能大幅度的提高数据库的写入速度。
视图
举个简单的例子,在上文我们对两个表进行了join。 select person.name,teacher.class from person left outer join teacher on person.id=teacher.person_id;
假如说我想要操作这个join的结果,每次我都要去写一遍这么长的select语句,这是很繁琐的。 而创建一个视图后: sqlite> create view teacher_detail as
...> select person.name,teacher.class from person left outer join teacher on person.id=teacher.person_id;
我就可以直接按照表的方式操作这个视图了: select * from teacher_detail where name='Leo';
name class
---------- ----------
Leo grade1
其他常用数据库操作
insertinsert命令用作向表中插入数据,通常格式如下: insert into table (column_list) values(value_list)
其中,column_list表示列名(多个以逗号分开),value_list表示值(多个以逗号隔开) 比如,上文我们用到的 insert into person (id,'1234578901');
//id,phone
insert into person values(10012,'Boboka','123112893218');
比如: insert into person values((select max(id) from person)+1,'Clark','1232893218');
updateinsert命令用来更新表中的数据,通常格式如下: update table_name set update_list where predicate
其中,update_list是以逗号分隔的多个赋值语句,where和select中的where一致。 比如: update person set phone='1213124123' where id=10001;
deleteinsert命令用来删除表中的行,格式如下: delete from table_name where perdicate
where的格式也和Select一致 delete from person where person.id=10014;
总结作为SQLite三部曲的开篇,本文回顾了常用的SQLite操作语句,以及一些数据库的基本概念。博客同步在我的Github更新。 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |