sql语句与fmdb数据库记录

//创建数据

    NSArray *arrname=@[@"sufei",@"tom",@"kimi",@"peter"];

    NSArray *arrage=@[@"190",@"140",@"329",@"344"];

    NSArray *arrgender=@[@"m",@"m",@"f",@"m"];

    NSArray *arr=@[@"1000",@"1001",@"1002",@"1003"];

    NSString *doc=[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];

    NSString *fileName=[doc stringByAppendingPathComponent:@"teacher.db"];

    FMDatabase *fmdb=[FMDatabase databaseWithPath:fileName];

    //打开数据库建表

    if ([fmdb open]) {

        BOOL result=[fmdb executeUpdate:@"create table if not exists teacher (id integer PRIMARY KEY AUTOINCREMENT,name text,gender text,age integer)"];

        if (result) {

            NSLog(@"success");

        }

        else

        {

            NSLog(@"fail");

        }

    }

    //添加列  不进行删除的话每次运行会报，添加重复列名的错。添加成绩列表

    [fmdb executeUpdate:@"alter table teacher add chengji integer"];

    //插入数据

    for (int i=0; i<4; i++) {

        

        [fmdb executeUpdate:@"INSERT INTO teacher(name,gender ,age ,chengji) VALUES (?,?,?,?);",arrname[i],arrgender[i],arrage[i],arr[i]];

    }

    //修改数据 当年龄为190的。修改成年龄为140

    [fmdb executeUpdate:@"update teacher set age ='140' where age ='190'"];

    //删除数据了一行

   // [fmdb executeUpdate:@"delete from teacher where age='344'"];

    //删除表添加的那一列

   // [fmdb executeUpdate:@"alter table teacher drop column chengji"];

   //改变表中列的数据类型

    //alter table 表名 alter column 列名 列名的新数据类型

   // 1.执行查询语句

    FMResultSet *resultSet = [fmdb executeQuery:@"SELECT * FROM teacher"];

    // 2.遍历结果

    while ([resultSet next]) {

        int ID = [resultSet intForColumn:@"id"];

        NSString *name = [resultSet stringForColumn:@"name"];

        int age = [resultSet intForColumn:@"age"];

        NSString *gender=[resultSet stringForColumn:@"gender"];

        int chengji = [resultSet intForColumn:@"chengji"];

        NSLog(@"id=%d 名字：%@ 年龄：%d 性别：%@ 成绩:%d分", ID, name, age,gender,chengji);

    }

     //删除整个表

    [fmdb executeUpdate:@"drop table teacher"];