Sqllite Basic Operation

1. Data type

• ● SQLite divide the data into the following categories? Several storage types:

• ● integer: integer value

• ● Real: floating point value

• ● Text: ? text string

• ● blob: Binary data (? files, for example)

• ● actually SQLite Yes, no type.

• ● even if it is declared integer type, or can I store a string? text (except primary key)

• ● It is possible to declare what type or not to declare a type when building a table, which means that the statement can be written like this:

• CREATE TABLE t_student (name, age);

#import "ViewController.h"

#import <sqlite3.h>

@interface viewcontroller ()

//1. Create Database ( save path )

@property (nonatomic,assign)sqlite3 *db;

@end

@implementation Viewcontroller

-(ibaction) InsertData: (UIButton *) Sender {

//3. Increase Data ( random data )

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

nsstring *strname = [nsstring stringwithformat:@ "8mingyeuxin-%d", I];

nsstring *sqlstr = [nsstring stringwithformat:@ "I Nsert into T_student (name, score) VALUES ('%@ ',%.02f) ", StrName,arc4random_uniform( + )/10.0];

// Execution

int success = sqlite3_exec(_db, sqlstr. Utf8string, null, null, null);

if (Success = = Sqlite_ok) {

NSLog (@ " add success !");

}Else{

NSLog (@ " add failed !");

}

}

}

-(ibaction) DeleteData: (UIButton *) Sender {

//4. Delete Data (70-80 score )

nsstring *sqlstr = @ "DELETE from t_student WHERE score > 80.0";

// Execution

int success = sqlite3_exec(_db, sqlstr. Utf8string, null, null, null);

if (Success = = Sqlite_ok) {

NSLog (@ " Delete succeeded !");

}Else{

NSLog (@ " Delete failed !");

}

}

-(ibaction) UpdateData: (UIButton *) Sender {

//5. Modify Data ( the modified fraction is less than 60.0 60.0)

nsstring *sqlstr = @ "UPDATE t_student SET score = 60.0 WHERE score < 60.0";

// Execution

int success = sqlite3_exec(_db, sqlstr. Utf8string, null, null, null);

if (Success = = Sqlite_ok) {

NSLog (@ " modified successfully !");

}Else{

NSLog (@ " Modify failed !");

}

}

-(ibaction) Selectdata: (UIButton *) Sender {

//6. Querying Data (Score >= 60)

//nsstring *sqlstr = @ "SELECT * from T_student WHERE score > 60.0 ORDER by Score DESC;";

// query data (with 8 digits in name)

nsstring *sqlstr = @ "SELECT * from T_student WHERE name is like '%ming% '";

// after query , put the result on the stmt object

// Save all result sets

sqlite3_stmt *stmt = nil;

sqlite3_prepare_v2(_db, sqlstr. Utf8string,-1, &stmt, NULL);

// get to all results each step of the query to a record

while (sqlite3_step(stmt) = = Sqlite_row) {

// Take out a record

//Name TEXT column

Const unsigned Char * name = Sqlite3_column_text(stmt, 1);

nsstring *strname = [nsstring stringwithcstring:(cons T char *) name encoding:nsutf8stringencoding];

//score REAL

Double score = sqlite3_column_double(stmt, 2);

NSLog (@ "name =%@ score =%f", Strname,score);

}

}

-(void) Viewdidload {

[super viewdidload];

// Open database if not, create

nsstring *path = [[nssearchpathfordirectoriesindomains(nsdocumentdirectory , Nsuserdomainmask, YES)lastobject]stringbyappendingpathcomponent: @ "Data.sqlite"];

NSLog (@ "%@", Path);

int success = sqlite3_open(path. Utf8string, &_db);

if (Success = = Sqlite_ok) {

NSLog (@ " CREATE database successfully !");

//2. Create a table ( specify fields , requirements : save student information ID name score)

// using SQL statements to create

// write SQL statements

nsstring *str = @ "CREATE TABLE IF not EXISTS t_student (id INTEGER PRIMARY KEY autoincr Ement, name TEXT not NULL, score REAL NOT null) ";

// execute SQL statement

int success_t = sqlite3_exec(_db, str.utf8string, NULL, Null, null);

if (success_t = = Sqlite_ok) {

NSLog (@ " CREATE table successfully !");

}Else{

NSLog (@ " failed to create TABLE !");

}

}Else{

NSLog (@ " failed to create DATABASE !");

}

// Close Database

sqlite3_close(_db);

}

@end

Sqllite Basic Operation