ios 怎样打开sqlite数据库

新建Empty Appliation，添加一个HomeViewController，和一个组件libsqlite3.dylib,来支持对sqlite3的连接，关闭，增删改查等 *** 作。

1. HomeViewController.h代码：

#import <UIKit/UIKit.h>

#import "sqlite3.h"

@interface HomeViewController : UIViewController{

sqlite3 *db//声明一个sqlite3数据库

}

- (NSString *)filePath//数据库文件的路径。一般在沙箱的Documents里边 *** 作

@end

2. HomeViewController.m代码：

#import "HomeViewController.h"

@interface HomeViewController ()

@end

@implementation HomeViewController

//该方法用于返回数据库在Documents文件夹中的全路径信息

- (NSString *)filePath{

NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)

NSString *documentsDir = [paths objectAtIndex:0]

return [documentsDir stringByAppendingPathComponent:@"Contacts.sqlite"]

}

//打开数据库的方法

- (void)openDB{

if (sqlite3_open([[self filePath] UTF8String], &db) != SQLITE_OK) {

sqlite3_close(db)

NSAssert(0, @"数据库打开失败。")

}

}

- (void)getAllContacts{

NSString *sql = @"SELECT * FROM members"

sqlite3_stmt *statement

if (sqlite3_prepare_v2(db, [sql UTF8String], -1, &statement, nil) == SQLITE_OK) {

while (sqlite3_step(statement) == SQLITE_ROW) {

char *name = (char *)sqlite3_column_text(statement, 0)

NSString *nameStr = [[NSString alloc] initWithUTF8String:name]

char *email = (char *)sqlite3_column_text(statement, 1)

NSString *emailStr = [[NSString alloc] initWithUTF8String:email]

char *birthday = (char *)sqlite3_column_text(statement, 2)

NSString *birthdayStr = [[NSString alloc] initWithUTF8String:birthday]

NSString *info = [[NSString alloc] initWithFormat:@"%@ - %@ - %@",

nameStr, emailStr, birthdayStr]

NSLog(info)

[nameStr release]

[emailStr release]

[birthdayStr release]

[info release]

}

sqlite3_finalize(statement)

}

}

IOS8以前版本, 如果是模拟器的话, 在这个路径中找到你的应用: /Users/你的用户名/Library/Application Support/iPhone Simulator/5.1/Applications/应用目录 IOS8在以下路径中找到你的应用 /Users/username/Library/Developer/CoreSimulator/Dev...

多表查询

假如有两张表:

表A:

表B:

这里我们是通过相同列名departmentNumber来进行条件查询相关信息:

1.内连接查询:返回的结果只有满足departmentNumber相等的结果

sql语句(有两种方式):

```

-->select a.name, b.name from A as a, B as b where a.departmentNumber = b.departmentNumber

-->select a.name, b.name from A as a  inner join  B as b on a.departmentNumber = b.departmentNumber

```

查询结果:

2.左外链接查询:返回的结果只和左边的表有关(这里假定A是左边的表),右边表如果没有对应值,就为NULL

sql语句:

```

-->select a.name, b.name from A as a  left outer join  B as b on a.departmentNumber= b.departmentNumber

```

返回结果:

3.右外链接查询:返回的结果只和右边的表有关(这里假定B是右表),那么如果左边表是没有对应值,就为NULL

sql语句:

```

-->select a.name, b.name from A as a  right OUTER join  B as b on a.departmentNumber = b.departmentNumber

```

返回结果:

4.完全外链接查询:想获取两个表所有信息,如果两个表内没有对应值的就用NULL表示

sql语句:

```

-->select a.name, b.name from B as b  full OUTER join  A as a on a.departmentNumber = b.departmentNumber

```

返回结果:

注意点:在sqlite中是不支持右外连接和完全外链接的,但是可以通过以下方式来进行连接:

1.sqlite中的右外连接:将左外连接的表换个顺序就可以了

```

-->select a.name, b.name from B as B  left outer join  A as A on a.departmentNumber = b.departmentNumber

```

返回结果:

2.sqlite中的完全外链接:用关键字union将两句连接起来进行合并

```

-->select a.name, b.name from B as b left OUTER A as a on a.departmentNumber = b.departmentNumber

UNION

select a.name, b.name from A as a left outer B as b on a.departmentNumber = b.departmentNumber

```

返回结果:

---