Introduction to SQLite in C/C ++

I. Introduction

SQLite is a file-based lightweight database, but it is very powerful and fast. It is definitely enough for small database application development. It is also very convenient to use. The following introduction shows that it is really easy to use.

 

II,Official Website

 

You can download the latest SQLite version from the official website www.sqlite.org of SQLite.

 

 

Iii. Auxiliary Tools

SQLite database management tools include sqlitemanager and sqliteadmin.

Sqlitemanager is only available in English, but has powerful functions. The problem is that it does not support Chinese character display of the ANSI character set. Its help documentation provides a detailed description of SQL statements, which is convenient for those who are not familiar with SQL statements. In addition, many of its operations have automatic SQL statement prompts, which is also useful for those who do not often use databases.

Sqliteadmin has a green Chinese version and has fewer functions than sqlitemanager. This version is sufficient for those familiar with SQL statements.

 

4. C/C ++ preparations before use

You can directly add sqlite3.h and sqlite3.c to your own C/C ++ project to use sqlite3.

 

5. Open and Close the database

SQLite supports UTF-8 and UTF-16, but it does not support C/C ++ProgramThe most common ANSI. Therefore, if the database path contains Chinese characters, you must convert the path to the corresponding character format.

1, open in UTF-8 Mode

// Open the database Sqlite3*DB=Null; Int Result=Sqlite3_open("C: \ ABC. DB",&DB); If(Sqlite_ OK! =Result) { Return; }   // Close the database Sqlite3_close(DB);

 

2. Open in UTF-16

To open it in UTF-16, change the statement for opening the database"Sqlite3_open16(L"C: \ ABC. DB",&DB).

But in general,It is strongly not recommended to open a database in UTF-16 ModeAccording to my tests, I found that if you open the database in this way and create a data table later, if one of them is of the text type. The data content of the data table will force the determination of the type as UTF-16. That is to say, any data you insert will be converted into a UTF-16 and stored in the database. The SQL statement is in UTF-8 format, so when the data you extract is inconsistent with the data you store, it is obviously a disaster for the user.

 

6. Execute SQL statements

The SQL statement can be called.Sqlite3_execFunction. Generally, if you do not need to return data or error messages, the execution method is as follows:

 

Sqlite3_exec(DB,Strsql, 0, 0, 0 );

1. Create a table

Suppose you want to create a database as shown in:

Its SQL statement is:

Create Table[Mytable] ([ID]Integer Primary key not null , [mytext] text null , [mydate] date not null , [mytime] time null , [myfloat] float null )

 

2. insert records

The following is an example SQL statement for inserting a record.

Insert IntoMytable (mytext, mydate, mytime, myfloat)Values('---Go to work! ', '2014-03-23', '9: 00: 00', 2012)

 

3. update records

The following is an example SQL statement for updating several records.

UpdateMytableSetMytext ='Really?', Mytime = '10: 00: 00'WhereId> = 0AndID <= 20

 

4. delete records

The following is an example SQL statement for deleting several records.

Delete FromMytableWhereId> = 3AndID <= 5

 

5. batch operation

If you want to perform a large number of operations, for example, to insert 10000 data records, if you execute SQL statements one by one, it takes a very long time. Transaction-based batch processing can greatly improve the operation speed (I tried it with 1000 records, and the speed was improved by more than 500 times ).

Below is a batch insert of 10000 data recordsCodeExample:

// Insert data entries (batch operation between begin and commit can greatly improve efficiency) Result=Sqlite3_exec(DB,"Begin ;", 0, 0, 0 ); For(Int I= 0;I<1, 10000;I++) { // Insert a data entry Result=Sqlite3_exec(DB, "Insert into mytable (mytext, mydate, mytime, myfloat) values ('--- work far! ', '2014-03-23', '9: 00: 00', 2012 );", 0, 0, 0 ); } Result=Sqlite3_exec(DB,"Commit ;", 0, 0, 0 );

 

VII. query and return results 1. Return in Form

The following is a sample code for getting data in form:

// Query records (return data table) Char**Pazresult; Int Nrow,Ncol; Sqlite3_get_table(DB,"Select * From mytable limit 1000 offset 2000",&Pazresult,&Nrow,&Ncol, 0 );   // Nrow indicates the number of rows // Ncol indicates the number of Columns // All records can be parsed from pazresult, and records are returned as strings // The name of column N, which is stored in pazresult [N] // Data in column M of row N, stored with paszresult [(m + 1) * ncol + M]   // After use, be sure to release the memory allocated for the record Sqlite3_free_table(Pazresult);

The preceding query Code also demonstrates how to query records of a specified number at a specified position. This query method is useful when the record set is very large.

 

2. Return in callback form

The following code shows how to obtain records returned by a query statement.

First, you need to define a callback function as follows (the parameter meaning will be discussed later ):

Int Result(Void*Pcontext,Int Ncol,Char**Azvalue,Char**Azname);

Then, call the SQLite function to execute the query statement. In the callback function column, enter the defined callback function:

Sqlite3_exec(DB,"Select * From mytable limit 10 offset 20",Result, 0, 0 );

When the SQL statement is executed, the preceding callback function is triggered every time a record is returned. The query result is displayed in the implementation of this callback function.

In the result function above, each parameter has the following meanings:

Pcontext	This parameter is calledSqlite3_exec is the input 4th parameter. It is usually used as an environment variable to indicate the subject of the currently executed code. In the sample code, I ignore this parameter, but it is generally not ignored during official use.
Ncol	The number of columns of the record.
Azvalue	The returned data is contained in the variable, which is also a string array. Azvalue [N] is the data in column N.
Azname	Column name. azname [N] is the name of column N.

 

 

8. Access Binary data

to access binary data, sqlite3_bind_blob and other functions. For specific usage, you can view the declaration of these functions. As a lightweight database, I generally don't need it to store binary data, so I will not describe it in detail here.