数据库查询语句

CREATE table IF NOT EXISTS t_student(id integer,name text,age integer,score real);

drop table if exists t_student;

update t_student set name = 'jack' , age = 19 ;

delete from t_student;

删除

delete from t_student where age <= 10 or age > 30 ;

创建

CREATE table IF NOT EXISTS t_student(id integer PRIMARY key AUTOINCREMENT,name text,age integer,score real);

插入

insert into t_student (id ,name,age,score) values (10,'mj',10,100);

修改

update t_student set age = 5 where age > 10 and score <= 60;

查询

select name ,age from t_student where age ==5 and score <60;

别名

select name sanme,age sage from t_student where age ==5 and score <60;

select s.name ,s.age from t_student s where age ==5 and score <60;

算个数

select count(*) from t_student s where age ==5 and score <60;

升序

select name ,age from t_student where age ==5 and score <60 order by score asc,age asc;

降序

select name ,age from t_student where age ==5 and score <60 order by score desc,age desc;

分页控制

select * from t_student linmit 4,8;

约束

drop table if EXISTS t_student;

create table if not EXISTS t_student(id integer primary key AUTOINCREMENT, name text not null unique,age null unique DEFAULT 1,score real);

外键：

create table t_student (id integer primary key autoincrement, name text, age integer, class_id integer, constraint fk_t_student_class_id_t_class_id foreign key (class_id) (id)) ; 

多表查询,表连接查询：

select * from t_student s,t_class c where s.class_id = c.id ,and c.name = '0316IOS';

模糊查询:

+ (NSArray *)studentsWithCondition:(NSString *)condition

{

    // 0.定义数组

    NSMutableArray *students = nil;

    

    // 1.定义sql语句

    constchar *sql = "select id, name, age from t_student where name like ?;";

    

    // 2.定义一个stmt存放结果集

    sqlite3_stmt *stmt = NULL;

    

    // 3.检测SQL语句的合法性

    int result = sqlite3_prepare_v2(_db, sql, -1, &stmt, NULL);

    if (result == SQLITE_OK) {

        NSLog(@"查询语句是合法的");

        students = [NSMutableArrayarray];

        

        // 填补占位符的内容

        NSString *newCondition = [NSStringstringWithFormat:@"%%%@%%", condition];

//        NSLog(@"%@", newCondition);

        sqlite3_bind_text(stmt, 1, newCondition.UTF8String, -1, NULL);

        

        // 4.执行SQL语句，从结果集中取出数据

        while (sqlite3_step(stmt) == SQLITE_ROW) { // 真的查询到一行数据

            // 获得这行对应的数据

            

            IWStudent *student = [[IWStudentalloc] init];

            

            // 获得第0列的id

            student.ID = sqlite3_column_int(stmt, 0);

            

            // 获得第1列的name

            constunsignedchar *sname = sqlite3_column_text(stmt, 1);

            student.name = [NSStringstringWithUTF8String:(constchar *)sname];

            

            // 获得第2列的age

            student.age = sqlite3_column_int(stmt, 2);

            

            // 添加到数组

            [students addObject:student];

        }

    } else {

        NSLog(@"查询语句非合法");

    }

    

    return students;

}