Usage of SQLite relational database _ MySQL

Database used by SQLite relational Database: the data warehouse stores a table, especially Excel and Numbers, which stores data in the form of tables. you can create multiple tables. Common databases: sqlite, MySQL, SQLServer, and SQLite relational databases

Database: a data warehouse that stores a table, especially Excel and Numbers, which stores data in tables. you can create multiple tables.

Common databases: sqlite, MySQL, SQLServer, Oracle, and Access.

Databases are mainly used because all data needs to be read at a time for reading, writing, and archiving files, which consumes a large amount of memory. second, database data efficiency is high, which is reflected in addition, deletion, modification, and query.

Steps for database data storage

1. create a database

2. create a new table)

3. add multiple fields (column, column, attribute)

4. add multi-row Records (row stores the values of multiple fields in each row)

Database operation statement (add, delete, modify, and Query), that is, SQL (Structured Query Language)

SQL statements are case-insensitive and must contain "" or''

Common syntax: (primary key: it is the unique identifier of a data record. a table can only have one primary key and the primary key cannot be repeated. Generally, the primary key name is set to "id" and no value is required, auto-increment; * indicates all fields; where is a condition)

1 Table operations

(1) create a table: creat table name (whether the field name field data type is a primary key, field name field data type, field name field data type ...)

(2) modify the TABLE name: alter table old TABLE name rename to new TABLE name

(3) delete a TABLE: drop table name

(4) ADD a COLUMN to the TABLE: alter table name add column name data type qualifier

2 Table data operations

(1) query: select field name (or *) from table name where field name = value

(2) Add: insert into table name (Field 1, field 2...) values (value 1, value 2 ...)

(3) change: update table name set field = value where field = value

(4) delete: delete from table name where field = value

SQLite is a lightweight embedded database, which occupies very low resources. in embedded devices, it may only need several hundred KB of memory. It processes faster than Mysql and PostgreSQL, two famous databases.

SQLite Glossary

2 Important struct

1 sqlite3 * pdb (database handle, similar to FILE handle)

2 sqlite3_stmt * stmt (this is equivalent to the Command object of ODBC, used to save compiled SQL statements)

Five main functions

1 sqlite3_open () (open the database)

2 sqlite3_exec () (execute a non-query SQL statement)

3 sqlite3_prepare () (this function is used to prepare an SQL statement, execute a select statement, or use parameterbind (encapsulate sqlite3_exec ))

4 Sqlite3_step () (use this function to move in the record set after sqlite3_prepare is called)

5 Sqlite3_close () (close database files)

SQLite storage class

1 NULL (the value is a NULL value)

2 INTEGER (the value is a signed INTEGER, which is stored in 1, 2, 3, 4, 6, or 8 bytes according to the value size)

3 REAL (The value is a floating point value stored as an 8-byte IEEE floating point number)

4 TEXT (the value is a TEXT string that is stored using database encoding (UTF-8, UTF-16BE, or UTF-16LE)

5 BLOB (the value is a blob data, which is fully stored according to its input)

Commonly used SQLite statements

1. open the sqlite3_open function of the database.

2. preprocessing SQL statement sqlite3_prepare_v2 function

3. bind the sqlite3_bind_text function

4. execute the SQL statement sqlite3_step function (Status: * SQLITE_BUSY-this function is used when the database is locked, * SQLITE_DONE-successful execution, * SQLITE_ROW-returns a row of results, * SQLITE_ERROR-running error, and * SQLITE_MISUSE-error)

5. functions such as sqlite3_column_text, sqlite3_column_blob, and sqlite3_column_int are extracted.

6. release the statement object resource sqlite3_finalize function

7. disable the sqlite3_close function of the database.

Use of SQLite

1. When Using SQLite in iOS, you must first add the library file libsqlite3.tbd.

2. import the main header file # import

3. database operations (Note: Set the database name and path)

Sample code:

Set Database path

-(Void) setSQLitePath

{

If (self. filePath = nil)

{

// Under the document directory

NSArray * documentArray = NSSearchPathForDirectoriesInDomains (NSDocumentDirectory, NSUserDomainMask, YES );

NSString * document = [documentArray objectAtIndex: 0];

_ FilePath = [document stringByAppendingPathComponent: SQLiteFile];

}

NSLog (@ "filePath % @", _ filePath );

}

Open the database and create a table

-(Void) New

{

NSString * SQL = @ "CREATE TABLE IF NOT EXISTS STUDENT (NAME TEXTPRIMARY KEY, ADDRESS TEXT, PHONE TEXT )";

If (SQL & 0! = SQL. length)

{

[Self setSQLitePath];

If ([[NSFileManager defaultManager] fileExistsAtPath: self. filePath])

{

// Open the database

Sqlite3 * dataBase; // sqlite3

Const char * fileName = [self. filePath UTF8String]; // [xxx UTF8String] converts NSString to C string, because SQLite3 is written in portable C (instead of Objective-C, it does not know what NSString is.

Int openStatus = sqlite3_open (fileName, & dataBase );

If (openStatus! = SQLITE_ OK)

{

// The database fails to be opened and the database is closed.

Sqlite3_close (dataBase );

NSAssert (0, @ "failed to open database ");

NSLog (@ "failed to open database ");

}

NSLog (@ "database opened successfully ");

// Create a table

Char * errorMsg;

Const char * execSql = [SQL UTF8String];

Int execStatus = sqlite3_exec (dataBase, execSql, NULL, NULL, & errorMsg );

If (execStatus! = SQLITE_ OK)

{

// An error occurred while creating the table. disable the database.

Sqlite3_close (dataBase );

NSAssert1 (0, @ "failed to create table: % s", errorMsg );

}

NSLog (@ "table created successfully ");

}

}

}

Insert data

-(Void) Insert

{

//? Indicates an undefined value.

NSString * SQL = @ "INSERT OR REPLACE INTO STUDENT (NAME, ADDRESS, PHONE) VALUES (?,?,?) ";

If (SQL & 0! = SQL. length)

{

[Self setSQLitePath];

If ([[NSFileManager defaultManager] fileExistsAtPath: self. filePath])

{

// Open the database

Sqlite3 * dataBase; // sqlite3

Const char * fileName = [self. filePath UTF8String];

Int openStatus = sqlite3_open (fileName, & dataBase );

If (openStatus! = SQLITE_ OK)

{

// The database fails to be opened and the database is closed.

Sqlite3_close (dataBase );

NSAssert (0, @ "failed to open database ");

NSLog (@ "failed to open database ");

}

NSLog (@ "database opened successfully ");

Const char * execSql = [SQL UTF8String];

Sqlite3_stmt * statment;

Int execStatus = sqlite3_prepare_v2 (dataBase, execSql,-1, & statment, nil); // The interface resolves an SQL statement to the statement structure. using this interface to access the database is currently a better method.

If (execStatus = SQLITE_ OK)

{

NSLog (@ "update table inserted successfully ");

// Start to bind the parameter

// The numbers 1, 2, and 3 represent the question mark above. here, the three values are bound to three bound variables.

Sqlite3_bind_text (statment, 1, [@ "zhangshaoyu" UTF8String],-1, NULL );

Sqlite3_bind_text (statment, 2, [@ "meizhou" UTF8String],-1, NULL );

Sqlite3_bind_text (statment, 3, [@ "13800138000" UTF8String],-1, NULL );

// Execute SQL statement execution insert

If (sqlite3_step (statment )! = SQLITE_DONE)

{

NSAssert (NO, @ "failed to insert update table. ");

}

Else

{

NSLog (@ "update table inserted successfully ");

}

}

Else

{

NSLog (@ "failed to insert update table ");

}

// Release the sqlite3_stmt object resource

Sqlite3_finalize (statment );

// Close the database

Sqlite3_close (dataBase );

}

}

}

Modify and update data

-(Void) Update

{

NSString * SQL = @ "UPDATE STUDENT SET ADDRESS =? Where NAME =? ";

If (SQL & 0! = SQL. length)

{

[Self setSQLitePath];

If ([[NSFileManager defaultManager] fileExistsAtPath: self. filePath])

{

// Open the database

Sqlite3 * dataBase; // sqlite3

Const char * fileName = [self. filePath UTF8String];

Int openStatus = sqlite3_open (fileName, & dataBase );

If (openStatus! = SQLITE_ OK)

{

// The database fails to be opened and the database is closed.

Sqlite3_close (dataBase );

NSAssert (0, @ "failed to open database ");

NSLog (@ "failed to open database ");

}

NSLog (@ "database opened successfully ");

Const char * execSql = [SQL UTF8String];

Sqlite3_stmt * statment;

Int execStatus = sqlite3_prepare_v2 (dataBase, execSql,-1, & statment, nil );

If (execStatus = SQLITE_ OK)

{

NSLog (@ "table updated successfully ");

// Bind text-type database data

// The numbers 1, 2, and 3 represent the question marks. There is only one question mark. this is a relatively simple database operation, and the real project will be much more complicated than this.

Sqlite3_bind_text (statment, 1, [@ "meizhouWUHUA" UTF8String],-1, NULL );

Sqlite3_bind_text (statment, 2, [@ "zhangshaoyu" UTF8String],-1, NULL );

// Execute insert

If (sqlite3_step (statment )! = SQLITE_DONE)

{

NSAssert (NO, @ "failed to update the table. ");

}

Else

{

NSLog (@ "table updated successfully ");

}

}

Else

{

NSLog (@ "failed to update table ");

}

// Release the sqlite3_stmt object resource

Sqlite3_finalize (statment );

// Close the database

Sqlite3_close (dataBase );

}

}

}

Search for data

-(Void) Select

{

NSString * SQL = @ "SELECT * FROM STUDENT ";

If (SQL & 0! = SQL. length)

{

[Self setSQLitePath];

If ([[NSFileManager defaultManager] fileExistsAtPath: self. filePath])

{

// Open the database

Sqlite3 * dataBase; // sqlite3

Const char * fileName = [self. filePath UTF8String];

Int openStatus = sqlite3_open (fileName, & dataBase );

If (openStatus! = SQLITE_ OK)

{

// The database fails to be opened and the database is closed.

Sqlite3_close (dataBase );

NSAssert (0, @ "failed to open database ");

NSLog (@ "failed to open database ");

}

NSLog (@ "database opened successfully ");

Const char * execSql = [SQL UTF8String];

Sqlite3_stmt * statment;

Int execStatus = sqlite3_prepare_v2 (dataBase, execSql,-1, & statment, nil );

If (execStatus = SQLITE_ OK)

{

NSLog (@ "query successful ");

// The query is successful and the traversal operation is performed.

// In the query result set, all records are traversed one by one. the numbers here correspond to the column values. Note that the column values here are different from the column values bound to sqlite3_bind_text! Be sure to separate the columns. otherwise, crash will occur. only the column numbers in this position are different. Note that!

While (sqlite3_step (statment) = SQLITE_ROW)

{

Const char * NAME = (char *) sqlite3_column_text (statment, 0 );

If (NAME! = NULL)

{

NSString * name = [[NSString alloc] initwithuf8string: NAME];

NSLog (@ "NAME % @", name );

}

Char * ADDRESS = (char *) sqlite3_column_text (statment, 1 );

If (ADDRESS! = NULL)

{

NSString * address = [[NSString alloc] initwithuf8string: ADDRESS];

NSLog (@ "ADDRESS % @", address );

}

Char * PHONE = (char *) sqlite3_column_text (statment, 2 );

If (PHONE! = NULL)

{

NSString * phone = [[NSString alloc] initwithuf8string: PHONE];

NSLog (@ "PHONE % @", phone );

}

}

}

Else

{

NSLog (@ "query failed ");

}

// Release the sqlite3_stmt object resource

Sqlite3_finalize (statment );

// Close the database

Sqlite3_close (dataBase );

}

}

}

Delete data

-(Void) Delete

{

// NSString * SQL = @ "DELETE FROM STUDENT"; // method 1

NSString * SQL = @ "DELETE FROM STUDENT where NAME =? "; // Method 2

If (SQL & 0! = SQL. length)

{

[Self setSQLitePath];

If ([[NSFileManager defaultManager] fileExistsAtPath: self. filePath])

{

// Open the database

Sqlite3 * dataBase; // sqlite3

Const char * fileName = [self. filePath UTF8String];

Int openStatus = sqlite3_open (fileName, & dataBase );

If (openStatus! = SQLITE_ OK)

{

// The database fails to be opened and the database is closed.

Sqlite3_close (dataBase );

NSAssert (0, @ "failed to open database ");

NSLog (@ "failed to open database ");

}

NSLog (@ "database opened successfully ");

Const char * execSql = [SQL UTF8String];

Sqlite3_stmt * statment;

Int execStatus = sqlite3_prepare_v2 (dataBase, execSql,-1, & statment, nil );

If (execStatus = SQLITE_ OK)

{

// Bind text-type database data

// The numbers 1, 2, and 3 represent the question marks. There is only one question mark. this is a relatively simple database operation, and the real project will be much more complicated than this.

Sqlite3_bind_text (statment, 1, [@ "zhangshaoyu" UTF8String],-1, NULL );

// Execute delete

If (sqlite3_step (statment )! = SQLITE_DONE)

{

NSAssert (NO, @ "failed to delete data. ");

NSLog (@ "failed to delete data ");

}

Else

{

NSLog (@ "data deleted ");

}

}

Else

{

NSLog (@ "failed to delete data ");

}

// Release the sqlite3_stmt object resource

Sqlite3_finalize (statment );

// Close the database

Sqlite3_close (dataBase );

}

}

}

Delete table

-(Void) DeleteTable

{

NSString * SQL = @ "DROP TABLE STUDENT ";

If (SQL & 0! = SQL. length)

{

[Self setSQLitePath];

If ([[NSFileManager defaultManager] fileExistsAtPath: self. filePath])

{

// Open the database

Sqlite3 * dataBase; // sqlite3

Const char * fileName = [self. filePath UTF8String];

Int openStatus = sqlite3_open (fileName, & dataBase );

If (openStatus! = SQLITE_ OK)

{

// The database fails to be opened and the database is closed.

Sqlite3_close (dataBase );

NSAssert (0, @ "failed to open database ");

NSLog (@ "failed to open database ");

}

NSLog (@ "database opened successfully ");

Const char * execSql = [SQL UTF8String];

Sqlite3_stmt * statment;

Int execStatus = sqlite3_prepare_v2 (dataBase, execSql,-1, & statment, nil );

If (execStatus = SQLITE_ OK)

{

// Execute delete

If (sqlite3_step (statment )! = SQLITE_DONE)

{

NSAssert (NO, @ "failed to delete the table. ");

NSLog (@ "failed to delete table ");

}

Else

{

NSLog (@ "table deleted ");

}

}

Else

{

NSLog (@ "failed to delete table ");

}

// Release the sqlite3_stmt object resource

Sqlite3_finalize (statment );

// Close the database

Sqlite3_close (dataBase );

}

}

}

View data

Note:

1. because sqlite3 is written in c language rather than pure object-c, we cannot use NSString for strings. because it is not recognized, we can only use strings in c language, char *. Fortunately, Nsstring provides the conversion method, that is, UTF8String. For example:

NSString * SQL = @ "DELETE FROM STUDENT where NAME =? ";

Constchar * execSql = [SQL UTF8String];

2. what are the uncertain values in SQL statements? Symbol. For example:

NSString * SQL = @ "DELETE FROM STUDENT where NAME =? ";

3. extract the number in the data query function to indicate the column number in the SQL statement (0 ~ N ). For example:

Column 1st: constchar * NAME = (char *) sqlite3_column_text (statment, 0 );

4. when binding data, the number in the function indicates the number of values in the SQL statement. For example:

NSString * SQL = @ "INSERT OR REPLACE INTO STUDENT (NAME, ADDRESS, PHONE) VALUES (?,?,?) ";

It indicates 1st values: sqlite3_bind_text (statment, 1, [@ "zhangshaoyu" UTF8String],-1, NULL );

It indicates 2nd values: sqlite3_bind_text (statment, 2, [@ "meizhou" UTF8String],-1, NULL );

3rd values: sqlite3_bind_text (statment, 3, [@ "13510213244" UTF8String],-1, NULL );

5. when creating a table, you must set a primary key. For example:

NSString * SQL = @ "CREATE TABLE IF NOT EXISTS STUDENT (NAME TEXT PRIMARY KEY, ADDRESSTEXT, PHONE TEXT )";