开发中经常用到的sql语句：

开发中经常用到的sql语句：
INSERT OR REPLACE INTO PhoneInfo (ID,Logo,Name) VALUES (%d,'%@','%@') //添加
Update MobPhoneInfo ID=%d,Logo='%@',Name='%@' //修改
Delete from PhoneInfo //删除表中所有数据
Delete from PhoneInfo  Where Id=2 //删除表中Id=2的数据

SELECT * FROM PhoneInfo Order By ID desc //按ID从大到小的顺序查询所有数据
SELECT ID FROM PhoneInfo Order By ID desc limit 0,1 //查询最大ID
SELECT * FROM PhoneInfo where Name like '三星' //查询name为三星的所有数据
SELECT * FROM PhoneInfo where NetFlow like '%%上网%%' //查询netflow中包含“上网”的所有数据
SELECT count(Name) num FROM PhoneInfo where Id=2 //查询id=2的数据总数

SELECT * FROM PhoneInfo ORDER BY RANDOM() limit 3 //查询3条随机的数据

打开数据库

sqlite数据库是文件数据库，是保存在文件系统中的。因此需要知道文件保存到哪里，可参见iOS中对文件的操作。比如本文保存到Documents目录下。代码：

NSArray *documentsPaths=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory
                                                   , NSUserDomainMask
                                                   , YES);
NSString *databaseFilePath=[[documentsPaths objectAtIndex:0] stringByAppendingPathComponent:@"mydb"];

if (sqlite3_open([databaseFilePath UTF8String], &database)==SQLITE_OK) {
    NSLog(@"open sqlite db ok.");
}

通过ssh查看Documents目录，发现mydb文件已经创建。sqlite的策略是如果有该文件就打开，如果没有就创建文件，也就是创建数据库。

这里要注意，使用的是c语法，sqlite3_open传入的是database的地址。

关闭数据库

数据库使用完毕后，要关闭，比如退出应用的时候：

- (void)viewDidUnload {
    // Release any retained subviews of the main view.
    // e.g. self.myOutlet = nil;
   sqlite3_close(database);
    self.popoverController = nil;
}

 

建表语句

数据库打开以后，如果没有表，建表：

char *errorMsg;
const char *createSql="create table if not exists persons (id integer primary key autoincrement,name text)";

if (sqlite3_exec(database, createSql, NULL, NULL, &errorMsg)==SQLITE_OK) {
    NSLog(@"create ok.");
}

这里要特别注意errorMsg传的是地址，因为该函数要通过地址引用来写报错字符信息。

向表中插入记录

和建表语句类似：

const char *insertSql="insert into persons (name) values(/"张三/")";
    if (sqlite3_exec(database, insertSql, NULL, NULL, &errorMsg)==SQLITE_OK) {
        NSLog(@"insert ok.");
    }

错误信息的处理

如果在多个地方使用errorMsg，那么每次使用完毕要清空一下字串，比如这样：

if (sqlite3_exec(database, createSql, NULL, NULL, &errorMsg)==SQLITE_OK) {
    NSLog(@"create ok.");
}else {
    NSLog(@"error: %s",errorMsg);
    sqlite3_free(errorMsg);
}

查询结果集

结果集的查询，需要用到statement：

const char *selectSql="select id,name from persons";
sqlite3_stmt *statement;
if (sqlite3_prepare_v2(database, selectSql, -1, &statement, nil)==SQLITE_OK) {
    NSLog(@"select ok.");
}

while (sqlite3_step(statement)==SQLITE_ROW) {
    int _id=sqlite3_column_int(statement, 0);
    char *name=(char *)sqlite3_column_text(statement, 1);
    NSLog(@"row>>id %i, name %s",_id,name);
}

sqlite3_finalize(statement);

不过这里有个问题，看看打印的日志：

乱码。因为直接用的char类型来做的。

解决办法是，用nsstring替代char：

while (sqlite3_step(statement)==SQLITE_ROW) {
    int _id=sqlite3_column_int(statement, 0);
    NSString *name=[[NSString alloc] initWithCString:(char *)sqlite3_column_text(statement, 1) encoding:NSUTF8StringEncoding];
    NSLog(@"row>>id %i, name %@",_id,name);
}

 

char生成nsstring的时候做一次显式的编码。问题解决：

这说明：

• 写入数据库，用char的方式没有问题，写入数据库的编码是对的；
• 从库中取出，可能默认使用ascii解码，造成显示乱码。

使用sqlite c api，要经常参考官方函数文档：http://www.sqlite.org/c3ref/funclist.html

在iOS中使用SQLite我们需要加入libsqlite3.dylib的库，并在引入SQLite的头文件。如下

   #import <sqlite3.h>

 导入库和头文件之后，接下来的操作就是打开一个数据库。这时候需要调用 sqlite3_open 这个函数来对打开一个数据库文件。此函数声明如下：

 int sqlite3_open(

  const char *filename,   /* Database filename (UTF-8) */

  sqlite3 **ppDb          /* OUT: SQLite db handle */

 );

  其中第一个参数就是数据库所存放的路径，如果路径下没有数据库文件则系统会在此路径下创建一个数据库。至于第二个参数则是数据库的句柄引用，但此函数调用成功后此句柄将会保存打开数据库的句柄 , 此句柄在往后的数据库操作中需要用到。因此，可如下方式调用：

NSString *dbPath=[NSString stringWithFormat:@”%@/Documents/demo.db”,NSHomeDirectory()];

sqlite3 *dbHandle;

if (sqlite3_open([path UTF8String], &dbHandle)==SQLITE_OK) {

NSLog(@“打开数据库成功!”);

}

既然有打开数据库，那么就肯定有关闭数据库的操作了，我们要养成良好的习惯，当需要使用数据库的时候就执行 sqlite3_open 来打开数据库，等使用完数据库后就调用 sqlite3_close 函数来对数据库进行关闭。关于 sqlite3_close 函数声明如下：

int sqlite3_close(sqlite3 *);
关闭时传入数据库句柄即可对数据库进行关闭。调用方式如下：

if(sqlite3_close(dbHandle)==SQLITE_OK)

NSLog(@“关闭数据库成功!”);

接下来需要讲述的是如何对数据库进行操作，常用的有两种方式可以操作数据库中的数据和调整结构。

    第一种就是sqlite3_exec函数，这种方法一般使用在不返回数据集的情况，也就是说少用于查询类的操作，同时笔者也尚发现使用此函数如何配合参数使用。如果使用他来创建表结构、更新、插入或者删除操作是一种直观快捷的方法。先来看看此方法的声明：

int sqlite3_exec(

  sqlite3*,                                  /* An open database */

  const char *sql,                           /* SQL to be evaluated */

  int (*callback)(void*,int,char**,char**),  /* Callback function */

  void *,                                    /* 1st argument to callback */

  char **errmsg                              /* Error msg written here */

);

第一个参数就是我们打开数据库的句柄了，第二个参数就是SQL语句，第三个参数为回调方法的函数指针，一旦指定此参数后，当执行语句为查询语句时则在枚举记录集时回对调此方法。第四个参数则为回调的第一个参数引用。关于第三第四个参数还有待研究（如果哪位大虾知道如何使用，不妨告诉小弟，一定感激不尽）。至于第五个参数就是如果方法执行后存在异常，那么这个参数保存的就是错误的描述信息。

    第二种就是使用sqlite3_prepare_v2和sqlite3_step两个函数搭配的进行操作。其中sqlite3_prepare_v2是一个将SQL语句编译为sqlite内部一个结构体(sqlite3_stmt).该结构体中包含了将要执行的的SQL语句的信息。而sqlite3_step则是让转化后的SQL进行下一步的操作。因此通过这两个函数可以很方便的获取到数据库中的数据。本人建议使用此方式取得记录集。下面是这两个函数的声明：

int sqlite3_prepare_v2(

  sqlite3 *db,            /* Database handle */

  const char *zSql,       /* SQL statement, UTF-8 encoded */

  int nByte,              /* Maximum length of zSql in bytes. */

  sqlite3_stmt **ppStmt,  /* OUT: Statement handle */

  const char **pzTail     /* OUT: Pointer to unused portion of zSql */

);

 第一个参数就是打开数据库时的数据库句柄对象。第二个就是SQL语句。第三个参数是用于指定SQL语句最大的长度，如果此参数为负数，则根据第二个参数中的第一个终结符为准作为一条完整的语句。如果为非负数，则以第二个参数的第一个终结符（\000或\u0000）或者指定的数字为准作为一条完整语句。第四个参数则是调用函数后返回的一个结构体，此结构体包含了相关语句的信息。关于第五个参数是用于指向前一条语句结束位置，一旦指定此参数，则参数指向位置的左边语句将不进行编译解析。

sqlite3_step（sqlite3_stmt *）;

传入参数即为准备语句中的结构体对象。

       上面所说的两种方式我将会在下面举例进行说明，但是每个例子不一定两种方式都实现，如果有这方面兴趣的朋友可以自己尝试另外一种方式实现。

例子一：创建数据表

要想让数据库能够存储数据，那就必须得创建一个数据表才能进行数据操作。而数据表是可以包含不同的数据字段，这些字段可以指定不同的数据类型，存储不同的数据。我们建表时可以根据需要进行创建。下面的代码创建了一个叫做persons的数据表，其包含两个字段id和name。其SQL语句为：create table if not exists persons (id integer primary key autoincrement,name);（如果对SQL语句不太熟悉的朋友可以参考相关方面资料）。那么代码如下所示：

char *errorMsg;

if (sqlite3_exec(_database, "create table if not exists persons (id integer primary key autoincrement,name);", NULL, NULL, &errorMsg)!=SQLITE_OK) {

     NSLog(@“操作失败!”);

}

sqlite3_free(errorMsg);

上面要注意的一点是，如果你有传入errorMsg参数，那么你必须在执行完sqlite3_exec后，执行sqlite3_free函数来释放errorMsg。否则就会造成内存泄露。

例子二：插入、更新、删除数据

上面的例子创建了一个数据后，那么我们可以往里面插入数据，我们可以使用insert语句将数据插入表中：代码如下所示：

char *errorMsg;

if (sqlite3_exec(_database, "insert into persons(name) values(‘张三’);", NULL, NULL, &errorMsg)!=SQLITE_OK) {

    NSLog(@“操作失败!”);

}

sqlite3_free(errorMsg);

上面所做的事情就是把一个张三的数据插入了数据表 persons 中。上面的实现是非常方便的，但是并不安全，因为我们的 SQL 语句中要插入的数据是拼合到 SQL 语句中的，这样很容易造成注入问题，因此，我们可以使用下面的方法来实现。

sqlite3_stmt *statement;

if (sqlite3_prepare_v2(_database, [@"insert into persons(name) values(?);" UTF8String], -1, &statement, NULL)!=SQLITE_OK) {

    return;

}

//绑定参数

const char *text=[@”张三” cStringUsingEncoding:NSUTF8StringEncoding];

sqlite3_bind_text(statement, index, text, strlen(text), SQLITE_STATIC);

if (sqlite3_step(statement)!=SQLITE_DONE) {

    sqlite3_finalize(statement);

    return;

}

sqlite3_finalize(statement);

推荐使用参数进行数据查询和操作，这样可以保证读写数据的正确性和提高安全性。对于更新数据和删除数据的调用方式和插入数据一样，只是SQL语句的差异，其中更新数据使用Update语法，而删除表数据则使用Delete语法。

例子三：事务处理

事务在数据库中是一个重要的概念，使用事务可以保证数据的统一和完整性。同时也可以提高效率。拿我们上面创建的persons表来说，假设我要一次插入20个人的名字才算是操作成功，那么，在不使用事务的情况下，如果插入过程中出现异常或者在插入过程中出现一些其他数据库操作的话，就很有可能影响了操作的完整性。所以事务可以很好地解决这样的情况，首先事务是可以把启动事务过程中的所有操作视为事务的过程。等到所有过程执行完毕后，我们可以根据操作是否成功来决定事务是否进行提交或者回滚。提交事务后会一次性把所有数据提交到数据库，如果回滚了事务就会放弃这次的操作，而对原来表的数据不进行更改。

那么，如何启动，提交还有回滚事务呢？SQLite中分别是：BEGIN、COMMIT和ROLLBACK。下面来看一下例子：

       @try{

char *errorMsg;

if (sqlite3_exec(_database, "BEGIN", NULL, NULL, &errorMsg)==SQLITE_OK) {

NSLog(@”启动事务成功”);

sqlite3_free(errorMsg);

       sqlite3_stmt *statement;

if (sqlite3_prepare_v2(_database, [@"insert into persons(name) values(?);" UTF8String], -1, &statement,            NULL)==SQLITE_OK) {

//绑定参数

const char *text=[@”张三” cStringUsingEncoding:NSUTF8StringEncoding];

sqlite3_bind_text(statement, index, text, strlen(text), SQLITE_STATIC);

if (sqlite3_step(statement)!=SQLITE_DONE) {

sqlite3_finalize(statement);

}

}

if (sqlite3_exec(_database, "COMMIT", NULL, NULL, &errorMsg)==SQLITE_OK) {

NSLog(@”提交事务成功”);

}

sqlite3_free(errorMsg);

}else{

sqlite3_free(errorMsg);

}

}

@catch(NSException *e){

char *errorMsg;

if (sqlite3_exec(_database, "ROLLBACK", NULL, NULL, &errorMsg)==SQLITE_OK) {

NSLog(@”回滚事务成功”);

}

sqlite3_free(errorMsg);

}

SQLite3是嵌入在iOS中的关系型数据库，对于存储大规模的数据很有效。SQLite3使得不必将每个对象都加到内存中。

基本操作：

（1）打开或者创建数据库

sqlite3 *database;
int result = sqlite3_open("/path/databaseFile", &database);

如果/path/databaseFile不存在，则创建它，否则打开它。如果result的值是SQLITE_OK，则表明我们的操作成功。

注意上述语句中数据库文件的地址字符串前面没有@字符，它是一个C字符串。将NSString字符串转成C字符串的方法是：

const char *cString = [nsString UTF8String];

（2）关闭数据库

sqlite3_close(database);

（3）创建一个表格

char *errorMsg;
const char *createSQL = "CREATE TABLE IF NOT EXISTS PEOPLE (ID INTEGER PRIMARY KEY AUTOINCREMENT, FIELD_DATA TEXT)";
int result = sqlite3_exec(database, createSQL, NULL, NULL, &errorMsg);

执行之后，如果result的值是SQLITE_OK，则表明执行成功；否则，错误信息存储在errorMsg中。

sqlite3_exec这个方法可以执行那些没有返回结果的操作，例如创建、插入、删除等。

（4）查询操作

NSString *query = @"SELECT ID, FIELD_DATA FROM FIELDS ORDER BY ROW";
sqlite3_stmt *statement;
int result = sqlite3_prepare_v2(database, [query UTF8String], -1, &statement, nil);

如果result的值是SQLITE_OK，则表明准备好statement，接下来执行查询：

while (sqlite3_step(statement) == SQLITE_ROW) {
    int rowNum = sqlite3_column_int(statement, 0);
    char *rowData = (char *)sqlite3_column_text(statement, 1);
    NSString *fieldValue = [[NSString alloc] initWithUTF8String:rowData];
    // Do something with the data here
}
sqlite3_finalize(statement);

使用过其他数据库的话应该很好理解这段语句，这个就是依次将每行的数据存在statement中，然后根据每行的字段取出数据。

（5）使用约束变量

实际操作时经常使用叫做约束变量的东西来构造SQL字符串，从而进行插入、查询或者删除等。

例如，要执行带两个约束变量的插入操作，第一个变量是int类型，第二个是C字符串：

char *sql = "insert into oneTable values (?, ?);";
sqlite3_stmt *stmt;
if (sqlite3_prepare_v2(database, sql, -1, &stmt, nil) == SQLITE_OK) {
    sqlite3_bind_int(stmt, 1, 235);
    sqlite3_bind_text(stmt, 2, "valueString", -1, NULL);
}
if (sqlite3_step(stmt) != SQLITE_DONE)
    NSLog(@"Something is Wrong!");
sqlite3_finalize(stmt);

这里，sqlite3_bind_int(stmt, 1, 235);有三个参数：

第一个是sqlite3_stmt类型的变量，在之前的sqlite3_prepare_v2中使用的。

第二个是所约束变量的标签index。

第三个参数是要加的值。

有一些函数多出两个变量，例如

sqlite3_bind_text(stmt, 2, "valueString", -1, NULL);

这句，第四个参数代表第三个参数中需要传递的长度。对于C字符串来说，-1表示传递全部字符串。

第五个参数是一个回调函数，比如执行后做内存清除工作。

接下来，做个小例子吧！

1、运行Xcode 4.3，新建一个Single View Application，名称为SQLite3 Test：

2、连接SQLite3库：

按照下图中的红色数字的顺序找到加号：

单击这个加号，打开窗口，在搜索栏输入sqlite3：

选择libsqlite3.dylib，单击Add，添加到工程。

3、进行界面设计。打开ViewController.xib，使用Interface Builder设计界面如下：

设置四个文本框的tag分别是1、2、3、4。

4、在ViewController.h中添加属性和方法：

@property (copy, nonatomic) NSString *databaseFilePath;

- (void)applicationWillResignActive:(NSNotification *)notification;

5、打开ViewController.m，向其中添加代码：

5.1 在开头添加代码：

#import "sqlite3.h"
#define kDatabaseName @"database.sqlite3"

5.2 在@implementation之后添加代码：

@synthesize databaseFilePath;

5.3 在viewDidLoad方法中添加代码：

- (void)viewDidLoad
{
    [super viewDidLoad];
    // Do any additional setup after loading the view, typically from a nib.
    //获取数据库文件路径
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    self.databaseFilePath = [documentsDirectory stringByAppendingPathComponent:kDatabaseName];
    //打开或创建数据库
    sqlite3 *database;
    if (sqlite3_open([self.databaseFilePath UTF8String] , &database) != SQLITE_OK) {
        sqlite3_close(database);
        NSAssert(0, @"打开数据库失败！");
    }
    //创建数据库表
    NSString *createSQL = @"CREATE TABLE IF NOT EXISTS FIELDS (TAG INTEGER PRIMARY KEY, FIELD_DATA TEXT);";
    char *errorMsg;
    if (sqlite3_exec(database, [createSQL UTF8String], NULL, NULL, &errorMsg) != SQLITE_OK) {
        sqlite3_close(database);
        NSAssert(0, @"创建数据库表错误: %s", errorMsg);
    }
    //执行查询
    NSString *query = @"SELECT TAG, FIELD_DATA FROM FIELDS ORDER BY TAG";
    sqlite3_stmt *statement;
    if (sqlite3_prepare_v2(database, [query UTF8String], -1, &statement, nil) == SQLITE_OK) {
        //依次读取数据库表格FIELDS中每行的内容，并显示在对应的TextField
        while (sqlite3_step(statement) == SQLITE_ROW) {
            //获得数据
            int tag = sqlite3_column_int(statement, 0);
            char *rowData = (char *)sqlite3_column_text(statement, 1);
            //根据tag获得TextField
            UITextField *textField = (UITextField *)[self.view viewWithTag:tag];
            //设置文本
            textField.text = [[NSString alloc] initWithUTF8String:rowData];
        }
        sqlite3_finalize(statement);
    }
    //关闭数据库
    sqlite3_close(database);
    //当程序进入后台时执行写入数据库操作
    UIApplication *app = [UIApplication sharedApplication];
    [[NSNotificationCenter defaultCenter]
     addObserver:self
     selector:@selector(applicationWillResignActive:)
     name:UIApplicationWillResignActiveNotification
     object:app];
}

5.4 在@end之前实现方法：

//程序进入后台时的操作，实现将当前显示的数据写入数据库
- (void)applicationWillResignActive:(NSNotification *)notification {
    //打开数据库
    sqlite3 *database;
    if (sqlite3_open([self.databaseFilePath UTF8String], &database)
        != SQLITE_OK) {
        sqlite3_close(database);
        NSAssert(0, @"打开数据库失败！");
    }
    //向表格插入四行数据
    for (int i = 1; i <= 4; i++) { 
        //根据tag获得TextField
        UITextField *textField = (UITextField *)[self.view viewWithTag:i];
        //使用约束变量插入数据
        char *update = "INSERT OR REPLACE INTO FIELDS (TAG, FIELD_DATA) VALUES (?, ?);";
        sqlite3_stmt *stmt;
        if (sqlite3_prepare_v2(database, update, -1, &stmt, nil) == SQLITE_OK) {
            sqlite3_bind_int(stmt, 1, i);
            sqlite3_bind_text(stmt, 2, [textField.text UTF8String], -1, NULL);
        }
        char *errorMsg = NULL;
        if (sqlite3_step(stmt) != SQLITE_DONE)
            NSAssert(0, @"更新数据库表FIELDS出错: %s", errorMsg);
        sqlite3_finalize(stmt);
    }
    //关闭数据库
    sqlite3_close(database);
}

6、实现关闭键盘，参考《iOS开发4：关闭键盘》中的第2步。其中，backgroundTap方法如下：

//关闭键盘
- (IBAction)backgroundTap:(id)sender {
    for (int i = 1; i <= 4; i++) {
        UITextField *textField = (UITextField *)[self.view viewWithTag:i];
        [textField resignFirstResponder];
    }
}

7、运行程序：

刚运行时显示如下面左图：

   

在各个文本框输入内容，如上面右图。然后按Home键，这样，就执行了写入数据的操作。

第一次运行程序时，在SandBox的Documents目录下出现数据库文件database.sqlite3：

此时退出程序，再次运行，则显示的就是上次退出时的值。