SQLite

1.SQLite语句:

--建表
--create table if not exists student(id integer not null primary key,name text,age integer)
--插入
--insert into student (id,name,age)values(null,'刘山',28)
--删表
--drop table student
--删除
--delete from student where id=4
--修改
--update student set name = '王五' where id = 3
--显示所有
--select *from student
--查找
--select *from student where id=3
--显示数据记录
--select count (*)from student
--降序显示
--select *from student order by age desc
--升序显示
--select *from student order by age asc
--列总和
--select sum (age) from student

2.示例:

 基于c：

#import "ViewController.h"
#import <sqlite3.h>

@interface ViewController ()

@end

@implementation ViewController

- (void)viewDidLoad {
    [super viewDidLoad];
    /**
     *  创建沙盒路径
     */
    NSString * Document = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)lastObject];
    NSString * path = [Document stringByAppendingPathComponent:@"tc.sqlite"];
    NSLog(@"%@",path);
    /**
     *  创建sqlite指针并使其指向沙盒中的sqlite文件（建立关联）
     */
    sqlite3 * db;
    sqlite3_open([path UTF8String], &db);
    /**
     *  创建sqlite语句
     */
    char * create = "create table if not exists tc (id integer not null primary key,name text,age integer,sex text)";
    char * insert = "insert into tc (id,name,age,sex)values(null,'张三',21,'男')";
    char * delete = "delete from tc where name = '王五'";
    char * update = "update tc set name='小明' where id = 2";
    char * select = "select *from tc";
    char * clear = "delete from tc";
    /**
     *  执行sqlite语句
     */
  
    sqlite3_exec(db, clear, NULL, NULL, NULL);
    sqlite3_exec(db, create, NULL, NULL, NULL);
    sqlite3_exec(db, insert, NULL, NULL, NULL);
    sqlite3_exec(db, delete, NULL, NULL, NULL);
    sqlite3_exec(db, update, NULL, NULL, NULL);
    
    /**
     *   参数:1.数据库对象,2.sql语句,3.负责sql语句,4.描述文件.5.剩余sql语句
     */
    sqlite3_stmt * stmt;
    sqlite3_prepare(db, select, -1, &stmt, NULL);
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        NSInteger ID = sqlite3_column_int(stmt, 0);
        NSString * Name = [NSString stringWithUTF8String:(char *)sqlite3_column_text(stmt, 1)];
        NSInteger Age = sqlite3_column_int(stmt, 2);
        NSString * Sex = [NSString stringWithUTF8String:(char *)sqlite3_column_text(stmt, 3)];
        NSLog(@"ID:%li 姓名:%@ 年龄:%li 性别:%@",ID,Name,Age,Sex);
    }
}

@end

基于OC（fmdb第三方封装）:

#import "ViewController.h"
#import "FMDB.h"

@interface ViewController ()

@end

@implementation ViewController

- (void)viewDidLoad {
    [super viewDidLoad];

#pragma mark - 创建沙盒路径
    NSString * Document = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)lastObject];
    NSString * path = [Document stringByAppendingPathComponent:@"student.sqlite"];
    NSLog(@"%@",path);

#pragma mark - 实例化数据库并和沙盒路径进行关联
    FMDatabase * db = [FMDatabase databaseWithPath:path];
 
#pragma mark - 打开数据块库
    [db open];
    
    BOOL b;

#pragma mark - 建表
 
    b = [db executeUpdate:@"create table if not exists stu (id integer not null primary key,name text,score text)"];
    if (!b) {
        NSLog(@"建表失败");
    }


#pragma mark - 插入数据

    b = [db executeUpdate:@"insert into stu (id,name,score)values(?,?,?)",NULL,@"王五",@"68"];
    if (!b) {
        NSLog(@"插入数据失败");
    }

    
#pragma mark - 修改数据

    b = [db executeUpdate:@"update stu set name = ? where id = ?",@"小明",@2];
    if (!b) {
        NSLog(@"修改数据失败");
    }

    
#pragma mark - 删除数据

    b = [db executeUpdate:@"delete from stu where id = ?",@1];
    if (!b) {
        NSLog(@"删除数据失败");
    }

    
#pragma mark - 查询数据

    FMResultSet * result = [db executeQuery:@"select *from stu where id>2"];
    while ([result next]) {
        NSString * name = [result stringForColumnIndex:1];
        NSString * score = [result stringForColumnIndex:2];
        NSLog(@"姓名:%@  成绩:%@",name,score);
    }

    
#pragma mark - 获取数据记录

    FMResultSet * result = [db executeQuery:@"select count(*) as 'n' from stu"];
    [result next];
    NSInteger n = [result intForColumn:@"n"];
    NSLog(@"%li",n);

}

@end