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等操作

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

1.创建表格式

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

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

示例

createtablet_student(idinteger,nametext,ageinetger,scorereal);

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的值, …) ;

示例

insertintot_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 字段 isnot某个值 ;  //isnot 相当于!=

where字段 >某个值 ;

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

where字段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<= 10or age>30;

猜猜下面语句的作用

update t_student set score= agewhere name=‘jack’;

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

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

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

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

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

3.1

格式

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

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

示例

select name, agefrom t_student;

select *from t_student;

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

3.2   起别名

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

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

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

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

示例

select namemyname, age maybe from t_student;

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

select 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 * fromt_studentorderby字段;

select * fromt_studentorderbyage;

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

select * fromt_studentorder byagedesc; //降序

select * fromt_studentorder byageasc;  //升序（默认）

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

select * fromt_studentorderbyageasc,heightdesc;

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

3.5  分页

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

格式

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

示例

select * fromt_studentlimit4,8;

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

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

第1页：limit 0,5

第2页：limit5,5

第3页：limit10,5

…

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

猜猜下面语句的作用

select* fromt_studentlimit 7;

相当于select *fromt_studentlimit0,7;

表示取最前面的7条记录

四.约束

1.简单约束

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

not null：规定字段的值不能为null

unique：规定字段的值必须唯一

default：指定字段的默认值

（建议：尽量给字段设定严格的约束，以保证数据的规范性）

示例

create table t_student (idinteger, nametext not null unique,ageinteger not null default 1);

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

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

2.主键约束

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

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

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

什么是主键

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

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

主键的设计原则:

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

永远也不要更新主键

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

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

主键的声明:

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

create table t_student(idinteger primary key,nametext,ageinteger);

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

主键字段

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

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

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

create table t_student (idinteger primarykey autoincrement,nametext,ageinteger);

外键约束:

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

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

新建一个外键

create table t_student (idinteger primary key autoincrement,nametext,ageinteger,class_id integer,constraint fk_student_class foreignkey(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 fromt_student s,t_class c wheres.class_id =c.id andc.name = ‘0316iOS’;

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

OC中使用sqlite

创建或打开数据库

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

sqlite3*db = NULL;

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

代码解析：

sqlite3_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