IOS 数据库管理系统(SQLite)

转自：http://www.2cto.com/kf/201411/354688.html

嵌入式数据库

SQLite嵌入式数据库优点

1.支持事件,不需要配置,不需要安装,不需要管理员

2.支持发部分SQL92

3.完整的数据库保存在磁盘上面一个文件,同一个数据库文件可以在不同机器上面使用,最大支持数据库到2T

4.整个系统少于3万行,少于250KB的内存占用

Linux系统级的SQLite技术实现框架libsqlite3.0.dylib

开始使用SQLite  

1.引入头文件

2.打开数据库

3.执行SQL命令

4.关闭数据库

一下代码展示整个数据库的 打开,关闭,增加,删除,查找,修改,

创建一个单例

?

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

# import <foundation foundation.h > @class Student; @interface DataBaseHandle : NSObject //创建一个单例 +(DataBaseHandle *)shareDB;   //获取Documents路径 - (NSString *)documentsPath;   //打开数据库 - ( void )openDB;   //关闭数据库 - ( void )closeDB;   //创建表 - ( void )createTable;   //插入信息 - ( void )insertStudent:(Student *)stu;   //修改信息 - ( void )updateMessage;   //删除信息 - ( void )deleteMessage;   //查找全部 - ( void )selectAllStudent;   //条件查找 - ( void )selectWithSex:(NSString *)sex;     @end

?

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 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223

# import "DataBaseHandle.h" # import <sqlite3.h> # import "Student.h" //全局单利对象 static DataBaseHandle *shareModle = nil; @implementation DataBaseHandle #pragma mark 实现单利 + (DataBaseHandle *)shareDB {      if (nil == shareModle)      {          shareModle = [[DataBaseHandle alloc] init];      }      return shareModle; }   #pragma  mark 获取Documents路径 - (NSString *)documentsPath {      NSString *documentPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[ 0 ];      return documentPath; }   //在操作数据之前引入框架(sql...3.0) //声明一个数据库的对象 static sqlite3 *db = nil; #pragma mark 打开数据库 - ( void )openDB {      //判断数据库是不是为空      if (nil == db)      {          //与documents路径拼接数据库存放路径(获得存放路径)          NSString *dbPath = [[self documentsPath] stringByAppendingString:@ "/Student.sqlite" ];          //根据路径打开数据库,如该路径下没有数据库,就自动创建一个数据库          //打开数据库(c语言中的语法)          int result = sqlite3_open(dbPath.UTF8String, &db);          //判断是否打开数据库成功          if (result == SQLITE_OK)          {              NSLog(@ "数据库打开成功" );          }          else          {              NSLog(@ "数据库打开失败" );          }      } }     #pragma mark 关闭数据库   - ( void )closeDB {      int result = sqlite3_close(db);      if (result == SQLITE_OK)      {          //如果关闭成功,置为nill          db = nil;          NSLog(@ "数据库关闭成功" );      }      else      {          NSLog(@ "数据库关闭失败" );      } }     #pragma mark 创建表 - ( void )createTable {      //要创建表,就要使用SQL语句.      //SQL语句在这是以字符串的形式存在的      NSString *createString = @ "CREATE  TABLE  IF NOT EXISTS student (sid INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , sname TEXT, ssex TEXT, sage TEXT)" ;      //执行SQL语句     int result = sqlite3_exec(db, createString.UTF8String, NULL, NULL, NULL);            if (result == SQLITE_OK)      {          NSLog(@ "创建表成功" );      }      else      {          NSLog(@ "创建失败" );      }       }     #pragma mark 插入信息 - ( void )insertStudent:(Student *)stu {      //准备插入语句      NSString *insertString = [NSString stringWithFormat:@ "INSERT INTO student (sname,ssex,sage) VALUES ('%@','%@','%@')" ,stu.name,stu.sex,stu.age];      //执行语句      int result = sqlite3_exec(db, insertString.UTF8String, NULL, NULL, NULL);      if (result == SQLITE_OK)      {          NSLog(@ "插入成功" );      }      else      {          NSLog(@ "插入失败" );      }                   }   #pragma mark 修改数据 - ( void )updateMessage {      //准备修改语句      NSString *undataString = @ "UPDATE student SET ssex = '女' ,sname = '小梦' WHERE  sid = 10" ;      int result = sqlite3_exec(db, undataString.UTF8String, NULL, NULL, NULL);      if (result == SQLITE_OK)      {          NSLog(@ "修改成功" );      }      else      {          NSLog(@ "修改失败" );      }         }   #pragma  mark 删除数据 - ( void )deleteMessage {      //准备删除语句      NSString *deleteString = @ "DELETE  FROM student WHERE sid = 1" ;      int result = sqlite3_exec(db, deleteString.UTF8String, NULL, NULL, NULL);      if (result == SQLITE_OK)      {          NSLog(@ "delete OK" );      }      else      {          NSLog(@ "delete _NO" );      }       }   #pragma mark 查找全部 - ( void )selectAllStudent {      //准备查找语句      NSString *selectAll = @ "SELECT *FROM student" ;      //创建伴随指针      sqlite3_stmt *stmt = nil;            //预备执行      int result = sqlite3_prepare(db, selectAll.UTF8String, - 1 , &stmt, NULL);      if (result == SQLITE_OK)      {          //在没有查找完成之前一直循环执行          while (sqlite3_step(stmt) == SQLITE_ROW)          {              //取出sid(第0列)              NSInteger sid = sqlite3_column_int(stmt, 0 );              //取出sname(第1列)              NSString *sname = [NSString stringWithUTF8String:( const char *)sqlite3_column_text(stmt, 1 )];              //取出ssex(第2列)              NSString *ssex = [NSString stringWithUTF8String:( const char *)sqlite3_column_text(stmt, 2 )];              //取出sage(第3列)              NSString *sage = [NSString stringWithUTF8String:( const char *)sqlite3_column_text(stmt, 3 )];              NSLog(@ "sid:%d sname:%@ ssex:%@ sage:%@" ,sid,sname,ssex,sage);                              }          sqlite3_finalize(stmt);      }      else      {          //如果查找失败,结束伴随指针          sqlite3_finalize(stmt);          NSLog(@ "查找失败" );      }       } #pragma mark 条件查找 - ( void )selectWithSex:(NSString *)sex {      //准备查找语句      NSString *selecstString = @ "SELECT * FROM student WHERE ssex = ?" ;      //创建伴随指针      sqlite3_stmt *stmt = nil;      //预执行       int result = sqlite3_prepare(db, selecstString.UTF8String, - 1 , &stmt, NULL);              if (result == SQLITE_OK)      {          //绑定?的值          //"1"代表第一个问号"?"和哪个参数绑定          sqlite3_bind_text(stmt, 1 , sex.UTF8String, - 1 , NULL);          while (sqlite3_step(stmt) == SQLITE_ROW)          {                NSInteger sid = sqlite3_column_int(stmt, 0 );              NSString *sname = [NSString stringWithUTF8String:( const char *)sqlite3_column_text(stmt, 1 )];              NSString *ssex = [NSString stringWithUTF8String:( const char *)sqlite3_column_text(stmt, 2 )];              NSString *sage = [NSString stringWithUTF8String:( const char *)sqlite3_column_text(stmt, 3 )];              NSLog(@ "sid: %d sname: %@ ssex: %@ sage: %@" ,sid,sname,ssex,sage);                        }                          }      else      {          //关闭指针          sqlite3_finalize(stmt);          NSLog(@ "查询失败" );      } }         @end

创建一个Student类

?

1 2 3 4 5 6 7

# import <foundation foundation.h >   @interface Student : NSObject @property (nonatomic,strong)NSString *name; @property (nonatomic,strong)NSString *age; @property (nonatomic,strong)NSString *sex; @end

在 ViewController.m中 ViewDidLoad 调用方法

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

//打印路径      NSLog(@ "%@" ,[[DataBaseHandle shareDB] documentsPath]);      //打开数据库      [[DataBaseHandle shareDB] openDB];      //创建表      [[DataBaseHandle shareDB] createTable];            //插入信息 //    Student *stu = [[Student alloc] init]; //    stu.name = @"小露"; //    stu.age = @"29"; //    stu.sex = @"女"; //    [[DataBaseHandle shareDB] insertStudent:stu];      //修改 //    [[DataBaseHandle shareDB] updateMessage];      //删除 //    [[DataBaseHandle shareDB] deleteMessage];      //查找全部信息 //    [[DataBaseHandle shareDB] selectAllStudent];      //根据条件查找信息      [[DataBaseHandle shareDB] selectWithSex:@ "女" ];