Analysis of SQLite database (Basic teaching)

SQLite uses dynamic data types that can be used across platforms, unlike CoreData, which is specific to Apple: introduce the simple use in Xcode:

Connect to the database first

Then introduce the header file #import <sqlite3.h>

Before the code starts, let's introduce the SQ statement:

1. Create a table
Grammar:
CREATE TABLE table name (field 1 type constraint 1 constraint 2, field 2 type constraint 1 constraint 2);
CREATE table if not EXISTS table name (field 1 type constraint 1 constraint 2, field 2 type constraint 1 constraint 2);
Case:
Requirements: Create a student table, the field in the table has a number, name, age, study number constraints: Primary key, self-increment, can not be empty; the name defaults to ' anonymous '; Age: Greater than 16 years old;
CREATE table student (s_id integer PRIMARY key autoincrement not NULL, s_name text default ' anonymous ', s_age integer check (s_ag e > 16));
2. Inserting data
Grammar:
Insert into table name (Field 1, Field 2, Field 3) VALUES (Field 1 value, field 2 value, field 3 value);
Example: Inserting student's name, age
INSERT into student (S_name, S_age) VALUES (' Alex ', 30);
INSERT into student (S_name, S_age) VALUES (' Little plums ', 40);
3. Updating data
Grammar:
Update table name SET field Name 1 = Modify value 1, field Name 2 = Modify value 2 where condition;
Update student Set s_age = where s_age = 30;
4. Delete data
Grammar:
Delete from table name where condition;
Requirements: Delete students aged 10
Delete from student where s_age = 10;
5. Querying data
Grammar:
Select the field to find from table name where condition;
Select *from Student where s_name = ' bei ye ';//Query All information
Select s_id, s_age from student where s_name = ' bei ye ';

Here's the code to start:

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

@interface Databasehandle ()
Storage path of the database
@property (nonatomic, copy) NSString *dbpath;
@end

static Databasehandle *database = nil;
@implementation Databasehandle
Single case
+ (Databasehandle *) Sharedatabasehandle {

if (dataBase = = nil) {
DataBase = [[Databasehandle alloc] init];
}
return dataBase;
}
Lazy loading requires assigning a value to the database path
-(NSString *) DbPath {
if (_dbpath = = nil) {
Requirements: The path is stored in the Documents folder, the database file is person.sqlite;
NSString *documentpath = [Nssearchpathfordirectoriesindomains (nsdocumentdirectory, NSUserDomainMask, YES) OBJECTATINDEX:0];
_dbpath = [Documentpath stringbyappendingpathcomponent:@ "Person.sqlite"];
}
return _dbpath;
}
Open a database; a database is used in many places, so a static variable of a database is initialized
static Sqlite3 *db = nil;
-(void) OpenDatabase {
Open the database and use int to receive open results
First parameter: Represents the storage path for the database
Second parameter: Level two pointer, the address of the database
int result = Sqlite3_open ([Self.dbpath utf8string], &db);
Result is an enumeration value, and there are a number of cases
if (result = = SQLITE_OK) {
NSLog (@ "Database open successfully");
} else {
NSLog (@ "Database open failed");
}
}

-(void) Updatewithuid: (Nsinteger) UID {
NSString *updatestr = @ "Update person set name = ' Goku ' where uid =?";
sqlite3_stmt *stmt = nil;
int result = Sqlite3_prepare (db, Updatestr.utf8string,-1, &stmt, NULL);
if (result = = SQLITE_OK) {

Sqlite3_bind_int64 (stmt, 1, UID);
if (Sqlite3_step (stmt) = = Sqlite_done) {
NSLog (@ "Update data success");
} else {
NSLog (@ "Failed to update data");
}
} else {
NSLog (@ "result =%d", result);
}
Sqlite3_finalize (stmt);
}

-(void) Deletewithuid: (Nsinteger) UID {

NSString *deletestr = [NSString stringwithformat:@ "Delete from the person where uid =%ld", UID];

int result = SQLITE3_EXEC (db, deletestr.utf8string, NULL, NULL, NULL);
if (result = = SQLITE_OK) {
NSLog (@ "Delete succeeded");
} else {
NSLog (@ "Delete failed");
}
}

These are just simple examples.

Note: The string in OC do not forget to add the @ database%@ write '%@ ' do not forget to add single quotation marks (string plus single quote, integer not added)
[NSString stringwithformat:@ "Delete from person where name = '%@ '", name1] with @ "Delete from person where name = ' Name1"
The difference is if name1 = @ "Liubei"; statement 1 means that the Name property ==liubei satisfies the condition; Statement 2 means that the property of name is ==name1 when the condition is met;

Analysis of SQLite database (Basic teaching)