数据库-SQLite

一、iOS应用数据存取的常用方式

XML属性列表 ——PList

NSKeyedArchiver归档

Preference（偏好设置）

SQLite3

Core Data（以面向对象的方式操作数据库SQLite）

二、数据库简介（文件）

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库

数据库的种类：

关系型数据库（主流）

对象型数据库

文档型数据库（Lotus Notes）

主流数据库

PC端：DB2、Oracle、Sybase、MySQL、SQLServer

嵌入式\客户端：SQLite

三、数据库的基本概念

数据库的存储结构跟excel类似

表（table）

记录（row），每行都有很多个字段

字段（column）

SQL语句

SQL（structured query language）：结构化查询语言，一种对关系数据库中的数据进行定义和操作的句法

要操作和访问数据库中存储的数据，必须编写SQL语句

SQL语句是不区分大小写的（即大小写一样）

SQL语句都以分号;结尾

四、SQL语句

数据定义语言（DDL：Data Definition Language）

其语句包括动词CREATE和DROP

在数据库中创建新表或删除表（CREAT TABLE或DROP TABLE）

数据操作语言（DML：Data Manipulation Language）

其语句包括INSERT，UPDATE和DELETE，它们分别用于添加，修改和删除表中的行

也称为动作查询语言

数据查询语言（DQL：Data Query Language）

也称为“数据检索语句”，用以从表中获得数据

保留字SELECT是DQL（也是所有SQL）用得最多的动词，其他DQL常用的保留字有WHERE，ORDER BY，GROUP BY和HAVING

五、DDL语句

创表

CREATE TABLE表名(字段名1字段类型1,字段名2字段类型2,…);

CREATE TABLE IF NOT EXISTS表名(字段名1字段类型1,字段名2字段类型2,…);

示例：

CREATE TABLE IF NOT EXISTS T_Person (id INTEGER, name TEXT, gender INTEGER, age INTEGER, height REAL);

删表

DROP TABLE表名;

示例：

DROP TABLE T_Person;

六、字段类型

SQLite将数据值的存储划分为以下几种存储类型：

NULL:表示该值为NULL值

INTEGER:无符号整型值

REAL:浮点值

TEXT:文本字符串

BLOB:二进制数据（比如文件）

实际上SQLite是无类型的，建表时声明或不声明类型都可以，也就意味着创表语句可以这么写：

create table t_student(name, age);

注意：为了保持良好的编程规范，方便程序员之间的交流，编写建表语句的时候最好加上每个字段的具体类型

七、DML语句

插入数据（INSERT）

INSERT INTO表名(字段1,字段2,…) VALUES (字段1的值,字段2的值,…);

示例：

INSERT INTO T_Person (name, age) VALUES ('张三', 18);

注意：数据库中的字符串内容应该用单引号’括住

更新数据（UPDATE）

UPDATE表名SET字段1 =字段1的值,字段2 =字段2的值,…;

示例：

UPDATE T_Person SET name = '李四', age = 20;

注意：这里会将T_Person表中所有记录的name都改为李四，age都改为20

删除数据（DELETE）

DELETE FROM表名;

示例：

DELETE FROM T_Person;

注意：这里会将T_Person表中所有记录都删掉

八、条件语句

如果只想更新或者删除某些固定的记录，就需要在DML语句后加上一些条件

条件语句的格式如下：

WHERE字段=某个值AND字段>某个值;

WHERE字段=某个值OR字段=某个值;

示例

将年龄大于10并且姓名不等于jack的记录年龄都改为5

UPDATE T_Person SET age = 5 WHERE age > 10 AND name !=‘张三’;

删除年龄小于等于10或者年龄大于30的记录

DELETE FROM T_Person WHERE age < 10 OR age > 30;

九、DQL语句

DQL语句就是查询语句，格式如下：

SELECT字段1,字段2,…FROM表名;

SELECT name, age FROM T_Person;

如果想查询所有的字段可以用：

SELECT * FROM表名;

SELECT * FROM T_Person;

也可以添加条件语句

SELECT * FROM T_Person WHERE age > 10;

计算记录的数量可以用count(字段)或者count(*)

SELECT count(*) FROM T_Person;

SELECT count(*) FROM T_Person WHERE height < 1.68;

十、排序

查询出来的结果可以用order by进行排序

SELECT * FROM T_Person ORDER BY字段;

SELECT * FROM T_Person ORDER BY age;

默认是按照升序排序（由小到大），也可以变为降序（由大到小）

SELECT * FROM T_Person ORDER BY age DESC; //降序

SELECT * FROM T_Person ORDER BY age ASC;  //升序（默认）

也可以用多个字段进行排序

SELECT * FROM T_Person ORDER BY age ASC, height DESC;

先按照年龄排序（升序），年龄相等就按照身高排序（降序）

十一、限制查询数量

SELECT * FROM表名LIMIT数值1,数值2;

SELECT * FROM T_Person LIMIT 4, 8;

上面的语句，可以这么理解：

跳过最前面4条语句，然后取8条记录

LIMIT常用来做分页查询，比如每页固定显示6条数据，那么每一页应该这样取数据：

第1页：LIMIT 0, 6

第2页：LIMIT 6, 6

第3页：LIMIT 12, 6

…

第n页：LIMIT 6*(n-1), 6

SELECT * FROM T_Person LIMIT 5;

表示取最前面的5条记录

十二、简单约束

建表的时候可以给特定的字段 设置 一些约束条件

用NOT NULL指定字段的值不能为null

用UNIQUE指定字段的值必须唯一

用DEFAULT指定字段的默认值

CREATE TABLE T_Student (id INTEGER, name TEXT NOT NULL UNIQUE, age INTEGER NOT NULL DEFAULT 1);

name字段不能为NULL，并且唯一

age字段不能为NULL，并且默认为1

十三、主键约束(PK)

当T_Person中有些记录的name属性和age属性都一样时，将无法区分这些数据，造成数据库的记录不唯一，这样不方便管理数据

良好的数据库编程规范应该是要保证每条记录的唯一性，为此，增加了主键约束

主键是用来唯一地标识某一条记录的，可以是一个字段或多个字段

比如T_Person可以增加一个id字段作为主键，相当于人的身份证

主键的设计原则：

主键应当是对用户没有意义的

永远也不要更新主键，让数据库自动维护

主键不应包含动态变化的数据

主键应当由计算机自动生成

主键目的：唯一标示一条记录

在需要时，让数据库告诉我们主键是多少

主键通常采用自动增加的整数来定义！

SELECT * FROM T_Student LEFT JOIN T_BOOK

LEFT JOIN就是以左边的数据为主，右边的数据为辅助，会将左边的数据全部显示出来，右边没有数据的显示为NULL，左边为主的关系！

JOIN只查询两个表之间共有的数据！在开发中JION用得少，而且容易“丢（有些右边没有数据的记录不会显示）”数据，JOIN是平等关系

十四、主键约束

在创表的时候用PRIMARY KEY声明一个主键

CREATE TABLE T_Student (id integer PRIMARY KEY,name text,age integer);

只要声明为PRIMARY KEY，就说明是一个主键字段，主键字段默认就包含了NOT NULL和UNIQUE两个约束

如果想要让主键自动增长（integer类型），应该增加AUTOINCREMENT

CREATE TABLE T_Student (id integer PRIMARY KEY AUTOINCREMENT, name text, age integer);

十五、外键约束(FK)

外键约束可以用来建立表与表之间的联系

新建一个外键

CREATE TABLE T_Student (

id INTEGER PRIMARY KEY AUTOINCREMENT,

name TEXT NOT NULL,

age INTEGER NOT NULL DEFAULT 1,

book_id INTEGER,

FOREIGN KEY (book_id) REFERENCES T_Book (id) ON DELETE SET NULL,

UNIQUE ("name" ASC)

)

T_Student表中有一个外键book_id引用着T_Book的主键id

十六、表连接查询

查询拥有《如来神掌》这本书的所有学生

SELECT s.name, s.age, b.name bookName

FROM T_Student s

JOIN T_Book b ON b.id = s.book_id;

WHERE b.name =‘如来神掌';

JOIN和ON用来建立两个表之间的关联关系

十七、SQLite3

SQLite3是一款开源的嵌入式关系型数据库，可移植性好、易使用、内存开销小

SQLite3是无类型的，意味着可以保存任何类型的数据到任意表的任意字段中。比如下列的创表语句是合法的：

CREATE TABLE T_Person(name, age);

为了保证可读性，建议还是把字段类型加上：

CREATE TABLE T_Person(name text, age integer);

SQLite3常用的5种数据类型：text、integer、float、boolean、blob（大量的文本或者图像）

在iOS中要使用SQLite3，需要添加库文件：libsqlite3.dylib并导入主头文件，这是一个C语言的库

十八、应用程序中SQLite3相关的操作

创建数据库（sqlite3_opendb）

单步执行操作（sqlite3_exec）

创建数据表

数据操作

插入数据

更新数据

删除数据

查询操作

sqlite3_prepare_v2检查sql的合法性

sqlite3_step逐行获取查询结果

sqlite3_coloum_xxx获取对应类型的内容

sqlite3_finalize释放stmt