iOS之sqlite的使用

iOS中的数据存储方式:

1.Plist（NSArray\NSDictionary）    JSON

2.Preference（偏好设置\NSUserDefaults）

3.NSCoding（NSKeyedArchiver\NSkeyedUnarchiver）归档

4.NSTemporary  临时文件存储

5.SQLite3

6.Core Data

==================================

数据库分两大类:关系型数据库(主流)   ,对象型数据库(例如coreData);

sqlite是一种轻型的嵌入式数据库(数据库名是以.sqlite或.db结尾)

1.sqlite数据库就是一个本地存储的文件;一般存储在沙盒路径中;

2.数据库内有多张数据表,每一张表中,存放一条或多条数据(每一条数据有字段和对应的值),数据库中标示唯一的记录(每一行数据就是一条记录)用主键,,主键不能重复,主键需要有数据库自动生成(前提是:主键设置自增长;

3.数据库中的单条数据(一行记录)对应开发中的一个对象

4,数据库中的一列对应开发中的一个属性

5,数据库中的一张表对应开发中的模型数据

==================================

navicate--------数据库管理工具

navicate的操作步骤:

1.创建一个数据库-----connection ----sqlite------设置connectionName,并且设置数据库名databaseFile

2.双击打开创建好的数据库连接connectionName

3.创建表-------table ------邮件NewTable(注意字段名不要用中文)

4.moreInfo------DDL查看数据库操作语句

5.queries---queryEditor-----用数据库语句操作数据库

==============

SQL 语句的特点:

1.不区分大小写（比如数据库认为 user 和 UsEr 是一样的）

2.数据库中的数据是区分大小写的.

3.每条语句都必须以分号  结尾.

4.SQL 中的常用关键字有

select 、 insert 、 update 、 delete 、 from 、 create 、 where 、 desc 、 order 、 by 、 group 、 table 、 alter 、 view 、 index 等等

5.数据库中不可以使用关键字来命名表、字段

===================

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

integer : 整型值

real : 浮点值

text : 文本字符串

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

• 实际上SQLite是无类型的
• 就算声明为integer类型，还是能存储字符串文本（主键除外）

• 建表时声明啥类型或者不声明类型都可以，也就意味着创表语句可以这么写：
• create table t_student(name, age);

====================

数据库语句:

一.数据定义语句（DDL：DataDefinitionLanguage）

包括 create 和 drop 等操作

在数据库中创建新表或删除表 （ create table 或  droppable ）

1.创建表格式

create table 表名 ( 字段名 1 字段类型 1, 字段名 2 字段类型 2, … ) ;

create table if not exists 表名 ( 字段名 1 字段类型 1, 字段名 2 字段类型 2, … ) ;

示例

create table t_student (id integer , name text , age inetger , score real ) ;

2.删除表

格式

droptable表名 ;

droptableifexists表名;

示例

droptablet_student;

3.增加字段

alert table 表名 add 字段名 字段类型  ;

4.删除字段

alert table 表名 drop column 字段名 ;

5.更改字段名

alert table 表名 rename column 原字段名 to 新字段名;

6.更改字段类型

alert table 表名 alert column  字段名 字段类型;

二.数据操作语句（DML：DataManipulation Language）

包括 insert 、 update 、 delete 等操作

上面的 3 种操作分别用于添加、修改、删除表中的数据

1.1插入数据

格式

insert into 表名 ( 字段 1, 字段 2, …) values ( 字段 1 的值 , 字段 2 的值 , … ) ;

示例

insert into t_student ( name, age) values (‘ wg ’ , 10 ) ;

注意

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

1.2 按照表中的字段顺序插入值

 insert into 表名 values(字段值,...)

2.更改数据

2.1   更改所有字段的值

格式

update  表名 set  字段 1 = 字段 1 的值 , 字段 2 = 字段 2 的值 , … ;

示例

update  t_student  set name = ‘jack’, age = 20 ;

注意

上面的示例会将 t_student 表中 所有记录的 name 都改为 jack ， age 都改为 20

2.2  更改符合条件的字段值

把字段age的值赋值给字段salary的值:update 表名 set salary =age;

2.3  如果字段2 是某个值时,更改字段1 的值: update 表名 set 字段1 =字段1 的值 where 字段2=字段2的值;

2.4 字段名name='lb'时,在原来salary的基础上加500 : update 表名 set salary = salary +500 where name='lb';

3.删除数据

3.1   删除所有记录

格式

delete from 表名 ;

示例

delete from t_student ;

注意

上面的示例会将 t_student 表中 所有记录都删掉

 3.2 删除符合条件的记录

age值大于20 时,删除该条记录:delete from 表名 where age>20;

4.条件语句

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

条件语句的常见格式:

where 字段 = 某个值 ;    // 不能用两个 =

where 字段 is 某个值 ;    // is 相当于 =

where 字段 ! = 某个值 ;

where 字段 is not 某个值 ;    // is not 相当于 !=

where 字段 > 某个值 ;

where 字段 1 = 某个值 and 字段 2 > 某个值 ;   // and 相当于 C 语言中的 &&

w here 字段 1 = 某个值 or 字段 2 = 某个值 ;   //   or 相当于 C 语言中的 ||

条件语句示例

示例

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

update  t_student  set  age = 5  where  age > 10  and  name != ‘jack ’ ;

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

delete from t_student  where age <= 10 or age > 30 ;

猜猜下面语句的作用

update  t_student  set  score = age where name = ‘jack’ ;

将 t_student 表中 名字 等于 jack 的 记录 ， score 字段的值 都改为 age字段的值

三.数据查询语句（DQL：Data QueryLanguage）

可以用于查询获得表中的数据

关键字 select 是 DQL （也是所有 SQL ）用得 最多的操作

其 他 DQL 常用的关键字有 where ， order by ， groupie 和 having

3.1

格式

select  字段 1, 字段 2, … from  表名 ;

select * from  表名 ;    / /  查询所有的字段

示例

select name, age from  t_student ;

select * from  t_student ;

select * from  t_student  where  age > 10 ;   / /   条件查询

3.2   起别名

格式 ( 字段和表都可以起别名 )

select  字段 1 别名 , 字段 2 别名 , … from  表名  别名 ;

select  字段 1  别名 , 字段 2 as 别名 , … from  表名 as 别名 ;

select 别名 . 字段 1 , 别名 . 字段 2, … from  表名 别名 ;

示例

select  name myname , age  maybe  from  t_student ;

给 name 起个叫做 myname 的别名，给 age 起个叫做 myage 的别名

s elect  s . name , s . age  from  t_student s ;

给 t_student 表起个别名叫做 s ，利用 s 来 引用表中的字段

3.3  计算记录的数量

格式

select  count ( 字段 ) from  表名 ;

select  count ( * ) from  表名 ;

示例

select  count (age) from  t_student ;

select  count ( * ) from  t_student  where score > = 60;

3.4排序

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

select * from t_student order by 字段 ;

select * from t_student order by age ;

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

select * from t_student order by age desc ;   / / 降序

select * from t_student order by age asc ;    / / 升序（默认）

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

select * from t_student order by age asc , height desc ;

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

3.5  分页

使用 limit 可以精确地控制查询结果的数量，比如每次只查询 10 条数据

格式

select * from 表名 limit 数值 1, 数值 2 ;

示例

select * from t_student limit 4, 8 ;

可以理解为：跳过最前面 4 条语句，然后取 8 条记录

n limit 常用来做分页查询， 比如每页固定显示 5 条 数据， 那么应该这样取数据

第 1 页： limit 0, 5

第 2 页： limit 5 , 5

第 3 页： limit 1 0 , 5

…

第 n 页： limit 5 * (n-1), 5

猜猜下面语句的作用

select * from t_student limit 7 ;

相当于 select * from t_student limit 0 , 7 ;

表示取最前面的 7 条记录

四.约束

1.简单约束

建表时可以给特定的字段设置一些约束条件，常见的约束有

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

2.主键约束

如果 t_student 表中就 name 和 age 两个字段，而且有些记录 的 name 和 age 字段的值都一样时 ，那么就没法区分这些数据，造成数据库的记录 不唯一，这样就不方便管理数据

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

也就是说，每张表都必须有一个主键，用来标识记录的唯一性

什么是主键

主键（ Primary Key ，简称 PK ）用来唯一地标识某一条记录

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

主键的设计原则:

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

永远也不要 更新主键

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

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

主键的声明:

在创表的时候用 primary key 声明一个主键

create  table  t_student (id integer  primary key , name text , age integer ) ;

integer 类型的 id 作为 t_student 表的主键

主键字段

只要声明为 primary key ，就说明是一个主键字段

主键字段默认就包含了 not null 和 unique 两个约束

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

create table  t_student (id integer  primary key  autoincrement , name text , age integer ) ;

外键约束:

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

外键的一般情况是：一张表的某个字段，引用着另一张表的主键字段

新建一个外键

create table  t_student (id integer  primary key  autoincrement , name text , age integer , class_id  integer , constraint  fk_student_class  foreign key ( class_id ) references  t_class (id));

t_student 表中有一个叫做 fk_t_student_class_id_t_class_id 的外键

这个外键的作用是用 t_student 表中的 class_id 字段引用 t_class 表的 id 字段

多表连接查询:

什么是表连接查询

需要联合多张表才能查到想要的数据

表连接的类型

内连接： inner join 或者 join  （显示的是左右表都有完整字段值的记录）,一张表的每一条记录都和另一张表的所有记录结合,生成m*n条记录;

select *from t_student inner join t_class on classID=t_class.id;

select  t1.name,t1.age,t2.name,t2.age from t_student t1 inner join t_class t2 on t1.classID=t2.id;

左外连接： left outer join （保证左表数据的完整性）

select *from t_class t2 left join t_student t1 on t2.id=t1.classID;

示例

查询 0316iOS 班的所有学生

select s.name,s.age from t_student s, t_class c where s.class_id = c.id and c.name = ‘0316iOS’;

+++++++++++++++++++++++++++++++++++++

OC中使用sqlite

创建或打开数据库

// path是数据库文件的存放路径

sqlite3*db = NULL;

int result = sqlite3_open([pathUTF8String], &db);

代码解析：

s qlite3_open() 将根据文件路径打开数据库，如果不存在，则会创建一个新的数据库 。 如果 result 等于常量 SQLITE_OK ，则表示成功打开数据库

sqlite3 *db： 一个打开的数据库实例

数据库文件的路径必须以 C 字符串 ( 而非 NSString) 传入

关闭数据库 ： sqlite3_close (db);

======

执行创表语句

char*errorMsg = NULL; //用来存储错误信息

char *sql = "create table if not exists t_person(idinteger primary key autoincrement, nametext, ageinteger);";

int result = sqlite3_exec(db, sql,NULL, NULL, &errorMsg);

代码解析：

sqlite3_exec() 可以执行任何 SQL 语句，比如创表、更新、插入和删除操作。但是一般不用它执行查询语句，因为它不会返回查询到的数据

=====

带占位符插入数据

char *sql ="insert into t_person(name, age) values(?, ?);";

sqlite3_stmt*stmt;

if (sqlite3_prepare_v2(db, sql, -1, &stmt, NULL) ==SQLITE_OK) {

    sqlite3_bind_text(stmt, 1,"母鸡", -1, NULL);

    sqlite3_bind_int(stmt, 2, 27);

}

if (sqlite3_step(stmt) !=SQLITE_DONE) {

    NSLog(@"插入数据错误");

}

sqlite3_finalize(stmt);

代码解析：

sqlite3_prepare_v2() 返回值等于 SQLITE_OK ，说明 SQL 语句已经准备成功 ， 没有语法问题

sqlite3_bind_text() ：大部分绑定函数都只有 3 个参数

① 第 1 个参数是 sqlite3_stmt *类型

② 第 2 个参数指占位符的位置， 第一个占位符的位置是 1 ，不是 0

③ 第 3 个参数指占位符要绑定的值

④ 第 4 个参数指在第 3 个参数中所传递数据的长度，对于 C 字符串，可以传递 -1 代替字符串的长度

⑤ 第 5 个参数是一个可选的函数回调，一般用于在语句执行后完成内存清理工作

sqlite_step () ： 执行 SQL 语句 ，返回 SQLITE_DONE 代表成功执行完毕

sqlite_finalize () ：销毁 sqlite3_stmt *对象

sqlite3_step() 返回 SQLITE_ROW 代表遍历到一条新记录

sqlite3_column_*() 用于获取每个字段对应的值， 第 2 个参数是字段的索引，从 0 开始

=========

#import "ViewController.h"

#import <sqlite3.h>

//sqlite数据库文件  对sqlite操作的c语言的框架

@interface ViewController ()

{

    sqlite3 *_db;

}

@end

@implementation ViewController

- (void)viewDidLoad {

    [superviewDidLoad];

    //filename 是要创建的数据库的地址

    NSString *fileName = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask,YES)lastObject]stringByAppendingPathComponent:@"company"];

    //ppdb 数据库连接的句柄

    //1 创建数据库连接,如果文件不存在,创建文件并打开连接,如果文件存在,打开连接

    int result = sqlite3_open(fileName.UTF8String, &_db);

    if (result == SQLITE_OK) {

        NSLog(@"数据库创建成功");

        

        //2 创建表

        

        //sqlite3_exec 执行sql语句,增删改,创建表(非查询的sql语句)

        

        //第1个参数 数据库连接

        //第2个参数 要执行的sql语句,

要转成C语言的--createTable.UTF8String

        //第3个参数 执行的回调的函数

        //第4个参数 给回调函数传递参数

        //第5个参数 错误信息

        NSString *createTable =@"create table if not exists t_student ("

        "id integer primary key,"

        "studentNo text not null UNIQUE,"

        "name text not null,"

        "age integer not null DEFAULT(18),"

        "score real"

        ");";

        

//    也可以写成这样:    NSString *createTable = @"create table if not exists t_student (id integer primary key,studentNo text not null UNIQUE,name text not null,age integer not null DEFAULT(18),score real);";

        char *errmsg = NULL;

        int result = sqlite3_exec(_db, createTable.UTF8String,NULL,NULL, &errmsg);

        if (result == SQLITE_OK) {

            NSLog(@"创建表成功");

        }

    }

}

- (void)touchesBegan:(NSSet<UITouch *> *)touches withEvent:(UIEvent *)event {

    [self select];

    

}

//1 插入数据

- (BOOL)insert {

    NSString *insertSQL =@"insert into t_student (studentNo,name) values('s003','pp')";

    char *errmsg = NULL;

    sqlite3_exec(_db, insertSQL.UTF8String,NULL,NULL, &errmsg);

    if (errmsg == NULL) {

        return YES;

    }else{

        NSLog(@"%s",errmsg);

        

        return NO;

    }

}

//2 更新数据

- (BOOL)update {

    NSString *updateSQL =@"update t_student set name='cc' where studentNo='s002'";

    

    char *errmsg = NULL;

    sqlite3_exec(_db, updateSQL.UTF8String,NULL,NULL, &errmsg);

    if (errmsg == NULL) {

        return YES;

    }else{

        NSLog(@"%s",errmsg);

        return NO;

    }

}

//3 删除

- (BOOL)delete {

    NSString *deleteSQL =@"delete from t_student where studentNo='s003'";

    

    int result = sqlite3_exec(_db, deleteSQL.UTF8String,NULL,NULL,NULL);

    

    NSLog(@"result  == %d",result);

    if (result == 0) {

        return YES;

    }else{

        return NO;

    }

}

//4 查询数据

- (void)select {

    NSString *selectSQL =@"select * from t_student";

    

    

    //第3个参数  sql语句字符串的字节数,系统帮我们计算这个字节数

    //第4个参数  sqlite3_stmt查询之后返回的结果集(结果的集合)

    sqlite3_stmt *stmt;

    int result = sqlite3_prepare_v2(_db, selectSQL.UTF8String, -1, &stmt,NULL);

    if (result == SQLITE_OK) {

        //sqlite3_stmt 并没有存储数据,等到下面的语句执行完才有数据

        //获取结果集中的数据 ,判断当前取得的是否是行的数据

        while (sqlite3_step(stmt) ==SQLITE_ROW) {

            //取出一行数据

            //从stmt改行中获取指定的列的数据,0代表第一列

            int id =sqlite3_column_int(stmt,0);

            const unsigned char *studentNo = sqlite3_column_text(stmt, 1);

            const unsigned char *name = sqlite3_column_text(stmt, 2);

            int age = sqlite3_column_int(stmt, 3);

            

            NSLog(@"id=%d,studentNo=%s,name=%s,age=%d",id,studentNo,name,age);

           

        }

    }

}

@end

＝＝＝＝＝＝＝＝＝终端中使用 sqlite＝＝＝＝＝＝＝＝＝＝＝＝

终端中使用sqlite：1----sqlite3 数据库名（sqlite3 foo.db
如果目录下没有foo.db，sqlite3就会建立这个数据库。）

2.----输入数据库语句

1、进入命令行环境：sqlite3

它显示了版本号，并告诉你每一条 SQL 语句必须用分号 ； 结尾 
 
2、命令行帮助：.help
在命令行环境下输入 .help 回车，显示所有可使用的命令以及这些命令的帮助。注意：所有的命令开头都是一个点
 
3、退出命令行环境
.quit 或者 .exit  都可以退出
 
 
四、数据库和表的相关命令
 
1、创建一个新的数据库：sqlite3     文件名
先建立一个 Db 目录，并在 Db 目录中创建一个 test.db 数据库文件，打开控制台窗口，命令如下：
mkdir Db
cd Db
sqlite3 test.db
 
2、打开一个已经存在的数据库：sqlite3      已经存在的文件名
创建一个新数据库和打开一个已经存在的数据库命令是一模一样的，如果文件在当前目录下不存在，则新建；如果存在，则打开。
 
3、导入数据：.read     数据文件
打开记事本，并将下列 SQL 语句复制到记事本中，保存为 test.sql 到上面说到的 Db 目录下，在命令行环境中输入
.read   test.sql
即将所有的数据导入到 test.db 数据库中。
test.db 导入数据
 
 4、列出所有的数据表： .tables
完成上面所有的工作以后，我们就可以列出所有的数据表了
打开一个控制台窗口，输入 sqlite3 回车，这时你就进入了 SQLite 命令行环境

5、显示数据库结构：.schema

6、显示表的结构：.schema    表名
其实就是一些 SQL 语句，他们描述了数据库的结构，

7、导出某个表的数据： .dump    表名

8、设置导出目标： 
.output     文件名  
或者
.output   stdout
先运行 .output cars.sql ，然后再运行 .dump 命令试试看？如果要回复成导出到终端（标准输出），则运行 .output stdout
 

五、数据显示相关命令
 
1、设置分隔符：.separator    分隔符
我们可以首先运行 SELECT * FROM Names； ，可以看到默认的分隔符是 | 
运行.separator : 以后，再 SELECT * FROM Names；，可以看到分隔符已经变成 : 了

2、设置显示模式：.mode    模式
有好几种显示模式，默认的是 list 显示模式，一般我们使用 column 显示模式，还有其他几种显示模式可以 .help 看 mode 相关内容。

4、设置每一列的显示宽度：.width     w1,w2,w3.........
一些内容，默认的宽度显示不下，这个命令就有用了
 
5、设置 NULL 值显示成什么样子： .nullvalue     你想要的NULL值格式

6、列出当前显示格式设置情况：.show