本文介绍了iOS 原生sqlite3的使用方法，分享给大家，具体如下：
SQLite？
- SQLit是一个开源、轻型嵌入式关系数据库，诞生于2000年5月
- 占用资源非常的低，在嵌入式设备中，可能只需要几百K的内存就够了
- 能够支持Windows/Linux/Unix等等主流的操作系统
- 比起Mysql、PostgreSQL这两款开源的世界著名数据库管理系统来讲，它的处理速度比他们都快
SQL语句常用操作
增、删、改、查,CRUD,Create[新建], Retrieve[检索], Update[更新], Delete[删除]。
SQL语法写法特点
1、不区分大小写（CREATE = create）
2、每条语句以分号（;）结尾
3、关键字建议大写
SQL语句常用关键字
select、insert、update、delete、from、create、where、desc、order、by、group、table、alter、view、index等
- 数据定义语句（DDL）：包括create和drop等操作，在数据库中创建新表或删除表（create table或 drop table）
- 数据操作语句（DML）：包括insert、update、delete等操作，分别用于添加、修改、删除表中的数据
- 数据查询语句（DQL）：可以用于查询获得表中的数据，关键字select是DQL（也是所有SQL）用得最多的操作，其他DQL常用的关键字有where，order by，group by和having
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
|
// ViewController.m// sqlite3//// Created by 周玉 on 2017/12/13.// Copyright © 2017年 guidekj. All rights reserved.//#import "ViewController.h"#import <sqlite3.h>#define KUIScreenWidth [UIScreen mainScreen].bounds.size.width#define KUIScreenHeight [UIScreen mainScreen].bounds.size.height#define FILE_NAME @"saas.sqlite"static sqlite3 *db = nil;@interface ViewController ()@end@implementation ViewController- (void)viewDidLoad { [super viewDidLoad]; self.title = @"sqlite3"; //DDB// [self openDB];// [self closeDB]; //DDL// [self createTable];// [self dropTable]; //DML// [self insertData];// [self updateData];// [self deleteData]; //DQL [self queryData];}#pragma mark 查询- (void)queryData{ sqlite3 *newDB = [self openDB]; sqlite3_stmt *statement = nil; NSString *sqlStr = @"SELECT * FROM SAAS_PERSON"; int result = sqlite3_prepare_v2(newDB, sqlStr.UTF8String, -1, &statement, NULL); if (result == SQLITE_OK) { //遍历查询结果 if (!(sqlite3_step(statement) == SQLITE_DONE)) { while (sqlite3_step(statement) == SQLITE_ROW) { int ID = sqlite3_column_int(statement, 0); const unsigned char *name = sqlite3_column_text(statement, 1); const unsigned char *sex = sqlite3_column_text(statement, 2); int age = sqlite3_column_int(statement, 3); const unsigned char *description = sqlite3_column_text(statement, 4); NSLog(@"ID = %d , name = %@ , sex = %@ , age = %d , description = %@",ID,[NSString stringWithUTF8String:(const char *)name],[NSString stringWithUTF8String:(const char *)sex],age,[NSString stringWithUTF8String:(const char *)description]); } } else { NSLog(@"查询语句完成"); } } else { NSLog(@"查询语句不合法"); } sqlite3_finalize(statement); [self closeDB];}#pragma mark 删除数据记录- (void)deleteData{ sqlite3 *newDB = [self openDB]; sqlite3_stmt *statement = nil; NSString *sqlStr = @"DELETE FROM SAAS_PERSON WHERE NAME = '王鹏飞'"; int result = sqlite3_prepare_v2(newDB, sqlStr.UTF8String, -1, &statement, NULL); if (result == SQLITE_OK) { if (sqlite3_step(statement) == SQLITE_DONE) { NSLog(@"删除操作完成"); } } else { NSLog(@"删除操作不合法"); } sqlite3_finalize(statement); [self closeDB];}#pragma mark 更新数据记录- (void)updateData{ sqlite3 *newDB = [self openDB]; sqlite3_stmt *statement = nil; NSString *sqlStr = @"UPDATE SAAS_PERSON SET DESCRIPTION = '喜欢运动,旅游' WHERE NAME = '周玉'"; int result = sqlite3_prepare_v2(newDB, sqlStr.UTF8String, -1, &statement, NULL); if (result == SQLITE_OK) { if (sqlite3_step(statement) == SQLITE_DONE) { NSLog(@"更新信息完成"); } } else { //[logging] no such column: DESCRIPT (KEY拼写错误) --- 更新信息不合法 NSLog(@"更新信息不合法"); } sqlite3_finalize(statement); [self closeDB];}#pragma mark 新增数据记录- (void)insertData{ sqlite3 *newDB = [self openDB]; sqlite3_stmt *statement = nil;// NSString *sqlStr = @"INSERT INTO SAAS_PERSON (NAME , SEX , AGE , DESCRIPTION) VALUES('周玉','男',28,'开朗乐观')";// NSString *sqlStr = @"INSERT INTO SAAS_PERSON (NAME , SEX , AGE , DESCRIPTION) VALUES('王鹏飞','女',27,'开朗乐观')";// NSString *sqlStr = @"INSERT INTO SAAS_PERSON (NAME , SEX , AGE , DESCRIPTION) VALUES('李梅','女',20,'年轻可爱��')"; NSString *sqlStr = @"INSERT INTO SAAS_PERSON (NAME , SEX , AGE , DESCRIPTION) VALUES('sumit','男',15,'小朋友的年纪')"; //检验合法性 int result = sqlite3_prepare_v2(newDB, sqlStr.UTF8String, -1, &statement, NULL); if (result == SQLITE_OK) { //判断语句执行完毕 if (sqlite3_step(statement) == SQLITE_DONE) { NSLog(@"插入的信息完成"); } } else { NSLog(@"插入的信息不合法"); } sqlite3_finalize(statement); [self closeDB];}#pragma mark 创建表- (void)createTable{ sqlite3 *newDB = [self openDB]; // char *sql = "CREATE TABLE IF NOT EXISTS t_person (ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, NAME TEXT , SEX TEXT,AGE INTEGER,DESCRIPTION TEXT);"; NSString *sqlStr = @"CREATE TABLE IF NOT EXISTS SAAS_PERSON (ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, NAME TEXT , SEX TEXT,AGE INTEGER,DESCRIPTION TEXT);"; char *error = NULL; int result = sqlite3_exec(newDB, sqlStr.UTF8String, NULL, NULL, &error); if (result == SQLITE_OK) { NSLog(@"创建表成功"); } else { NSLog(@"创建表失败 = %s",error); } [self closeDB];}#pragma mark 删除表- (void)dropTable{ sqlite3 *newDB = [self openDB]; NSString *sqlStr = @"DROP TABLE t_person"; char *error = NULL; int result = sqlite3_exec(newDB, sqlStr.UTF8String, NULL, NULL, &error); if (result == SQLITE_OK) { NSLog(@"删除表成功"); } else { NSLog(@"删除表失败 = %s",error); } [self closeDB];}#pragma mark 打开或者创建数据库- (sqlite3 *)openDB { if (!db) { //1.获取document文件夹的路径 //参数1:文件夹的名字 参数2:查找域 参数3:是否使用绝对路径 NSString *documentPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0]; //获取数据库文件的路径 NSString *dbPath = [documentPath stringByAppendingPathComponent:@"saas.sqlite"]; NSLog(@"%@",dbPath); //判断document中是否有sqlite文件 int result = sqlite3_open([dbPath UTF8String], &db); if (result == SQLITE_OK) { NSLog(@"打开数据库"); }else{ [self closeDB]; NSLog(@"打开数据库失败"); } } return db;}#pragma mark 关闭数据库- (void)closeDB{ int result = sqlite3_close(db); if (result == SQLITE_OK) { NSLog(@"数据库关闭成功"); db = nil; } else { NSLog(@"数据库关闭失败"); }}@end |
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持服务器之家。
原文链接：http://www.jianshu.com/p/bf3ae0a2508c
