IOS SQLite3 Database Operations

There are four types of data persistence in iOS: Attribute lists, object archiving, SQLite3, and core Data,sqlite3 database operations are a must-have or missing technology.

SQLite3 Introduction SQLite3 database is the mobile side (IOS, Android, embedded) identified on the relational database, and MySQL, Oracle and other databases, with a lightweight advantage, which resulted in small size, rapid, simple function is still strong and other advantages. SQLite3 Statement Features

• Case insensitive

• At the end of each sentence;

SQLite field type

• Integer: Integer type

• Real: floating-point value

• Text: literal string

• BLOB: Binary type

  Note: In strict sense, the SQLite character type is no type, that is, the integer type can also store other types. However, for programming specifications, it is recommended to strictly observe each type

Type of SQLITE3 Statement DDL statement (Create, drop) CREATE table

create 表名（字段名1 字段类型，字段名2 字段类型，字段名3 字段类型，....）；create table t_Student(id integer name text,age integer,addr text);

Expansion: PRIMARY KEY constraint

To facilitate accurate data recording for each group of data, it is necessary to add a primary key constraint to identify the uniqueness of the records in each set of data.

create table t_Student(id integer primary key autoincrement name text,age integer,addr text);autoincrement 自动增长

Simple constraints

• Not NULL: The value of the specified field cannot be null

• Unique: The value of the specified field must be unique

• Default: Specify a value for the field

Delete Table

drop table 表名;drop table t_Student;

DML statement (insert update Delete)

insert into table name (field 1 , field 2 , field 3 , ) VALUES (field 1 , field 2  value, field 3 , ); Insert into t_student (Name,age,addree) VALUES (,  788 , );  

update 表名 set 字段1= 字段1的值 where 条件;update t_Student set age = 1000 where name=‘唐伯虎‘;

delete from 表名 where 条件;delete from t_Student where age < 100;

DQL statement (SELECT)

 Select field 1, field 2, ... from table name;Select * from table name;   Select *  from t_student; Select name,age,addree  from T_student; Select name  from T_student where age<; Select count(field) from table name;Select Count(*) from T_student; Select Count (age) from T_student;

Other corner knowledge points

• When you create a table, the if not exists is created to prevent the table from being duplicated, the table is not created when it exists, and if it does not exist, lazy loading of similar variables

• The where field in a conditional sentence can be used with >, <, =, is,! =, is not, or, and, and they are expressed in the same meaning as the English math class of the school age.

• As can alias a table and use point syntax to access fields within a table

Let's create an information system. Technical Preparation

sqlite3          *db, 数据库句柄，跟文件句柄FILE很类似sqlite3_stmt      *stmt, 这个相当于ODBC的Command对象，用于保存编译好的SQL语句sqlite3_open(),   打开数据库，没有数据库时创建。sqlite3_exec(),   执行非查询的sql语句sqlite3_bind_text()，设置占位符的内容sqlite3_prepare_v2(),检测查询语句合法性Sqlite3_step(), 在调用sqlite3_prepare后，使用这个函数在记录集中移动。Sqlite3_close(), 关闭数据库文件sqlite3_column_text(), 取text类型的数据。sqlite3_column_blob（），取blob类型的数据sqlite3_column_int(), 取int类型的数据

1. Create a project, add a Sqlite3 class library package
   
2. 
   To add several controls in storyboard
   
3.

//Create a database in a sandbox    NSString*filename = [[Nssearchpathfordirectoriesindomains (NSDocumentDirectory, Nsuserdomainmask,YES) Lastobject] stringbyappendingpathcomponent:@"Student.sqlite"];intResult =sqlite3_open (filename. Utf8string, &db);if(RESULT==SQLITE_OK) {NSLog(@"successfully created the database and opened");//Create a sheet        Const Char*sql="CREATE table if not exists t_student (ID integer primary key autoincrement,name text,age integer,addr text);";Char*errormesg=NULL;intResult=sqlite3_exec (Db,sql,NULL,NULL, &ERRORMESG);if(RESULT==SQLITE_OK) {NSLog(@"CREATE table successfully"); }Else{NSLog(@"CREATE table failed%s", ERRORMESG); }        }Else{NSLog(@"Failed to open database"); }

1. Add data

 NSString*sql=[NSStringstringwithformat:@"INSERT into t_student (NAME,AGE,ADDR) VALUES (%@,%@, '%@ ');", _NAMETF. Text, _AGETF. Text, _ADDREETF. Text];Char*errormesg=NULL;intResult=sqlite3_exec (Db,sql. Utf8string,NULL,NULL, &ERRORMESG);if(RESULT==SQLITE_OK) {NSLog(@"Add Success"); }Else{NSLog(@"Add failed%s", ERRORMESG); }

1. Querying data

Const Char*sql ="SELECT * from T_student;";//2. Define a stmt to hold the result setSqlite3_stmt *stmt =NULL;//3. Detecting the legality of SQL statements    intresult = SQLITE3_PREPARE_V2 (db, SQL,-1, &stmt,NULL);if(Result = = SQLITE_OK) {NSLog(@"query statement is legal"); Sqlite3_bind_text (stmt,1,"Student",-1,NULL); while(Sqlite3_step (stmt) ==sqlite_row) {intSid=sqlite3_column_int (stmt,0);Const unsigned Char*sname=sqlite3_column_text (stmt,1);intSage=sqlite3_column_int (stmt,2);NSLog(@"%d,%s,%d", sid,sname,sage); }    }Else{NSLog(@"query statement is not valid"); }

Demo Address: Click to go to download

SQLite3 Visualization Tool

• Navicat Premium

Navicat Premium Internal integration of SQLite3 can be very friendly to the creation of data, add, modify, delete and other operations.
The following is a description of the usage of Navicat premium (since CSDN upload attachment cannot exceed 60M need Navicat premium software can leave a message below and send it privately to you)
1. Open Navicat Premium Software
2. Click on the top left corner to select the SQLite option, fill in the database name selection data type, database storage address, such as:

Click OK.
3. Click Tables under Main, right-click to select New table to create a file

Attention:

1. The ID is set to key (unique) and the auto increment self-growth button is checked

2. Age sets the default value to 20 and initializes the default value if you do not enter a number

4. Click the button and set the table name to T_student
5. Double-click T_student to + Add a few sets of data

It can be observed that we do not assign a value to ID and age with a default value
In addition, we can create tables and add data and query data with database statements

INSERT into t_Student(name,age,addree)VALUES(‘乔布斯‘,64,‘苹果公司‘)

IOS SQLite3 Database Operations