In the previous article, we gave an overview of database knowledge:< Database overview >, this paper introduces some basic and commonly used SQLite database knowledge.

SQLite Foundation

SQL basic syntax

• insert

  INSERT INTO Table name (Column name 1,...) 
  VALUES (Column 1 value,...);
  

• modify

  UPDATE Table name
  SET Column name 1= Column 1 value, ....
  WHERE [Conditional expression];
  

• delete

  DELETE FROM Table name
  WHERE [Conditional expression];
  

• query

  SELECT Column name 1, .... FROM Table name;
  WHERE [Conditional expression];
  

• Create table

  CREATE TABLE Library name.Table name(
    Column 1 name type characteristics (primary key, unique, non empty, self increment, comment),
    ...
  );
  

• Delete table

  DROP TABLE Table name;
  

SQLite interface function

// Open file: if the file exists, try to open it; If the file does not exist, it is created
// Note: this file in sqlite is a library
// Multi thread access and multi instance access are not supported
SQLITE_API int sqlite3_open(
  const char*filename,   /* Database filename (UTF-8)*/
  sqlite3**ppDb          /* OUT: SQLite db handle*/
);
SQLITE_API int sqlite3_open16(
  const void*filename,   /* Database filename (UTF-16)*/
  sqlite3**ppDb          /* OUT: SQLite db handle*/
);

// Close the file and be sure to close it after opening
SQLITE_API int sqlite3_close(sqlite3*);

// Perform operations
SQLITE_API int sqlite3_exec(
  sqlite3*,                                  /* An open database*/
  const char*sql,                           /* SQL to be evaluated*/
  int (*callback)(void*,int,char**,char**),  /* Callback function*/
  void*,                                    /* 1st argument to callback*/
  char**errmsg                              /* Error msg written here*/
);

SQLITE_API int sqlite3_prepare(
  sqlite3*db,            /* Database handle*/
  const char*zSql,       /* SQL statement, UTF-8 encoded*/
  int nByte,              /* Maximum length of zSql in bytes.*/
  sqlite3_stmt**ppStmt,  /* OUT: Statement handle*/
  const char**pzTail     /* OUT: Pointer to unused portion of zSql*/
);

SQLITE_API int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*));
SQLITE_API int sqlite3_bind_double(sqlite3_stmt*, int, double);
SQLITE_API int sqlite3_bind_int(sqlite3_stmt*, int, int);
SQLITE_API int sqlite3_bind_int64(sqlite3_stmt*, int, sqlite3_int64);
SQLITE_API int sqlite3_bind_null(sqlite3_stmt*, int);
SQLITE_API int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, void(*)(void*));
SQLITE_API int sqlite3_bind_text16(sqlite3_stmt*, int, const void*, int, void(*)(void*));
SQLITE_API int sqlite3_bind_value(sqlite3_stmt*, int, const sqlite3_value*);
SQLITE_API int sqlite3_bind_zeroblob(sqlite3_stmt*, int, int n);

SQLITE_API int sqlite3_step(sqlite3_stmt*);
SQLITE_API int sqlite3_finalize(sqlite3_stmt*pStmt);

SQLITE_API const void*sqlite3_column_blob(sqlite3_stmt*, int iCol);
SQLITE_API int sqlite3_column_bytes(sqlite3_stmt*, int iCol);
SQLITE_API int sqlite3_column_bytes16(sqlite3_stmt*, int iCol);
SQLITE_API double sqlite3_column_double(sqlite3_stmt*, int iCol);
SQLITE_API int sqlite3_column_int(sqlite3_stmt*, int iCol);
SQLITE_API sqlite3_int64 sqlite3_column_int64(sqlite3_stmt*, int iCol);
SQLITE_API const unsigned char*sqlite3_column_text(sqlite3_stmt*, int iCol);
SQLITE_API const void*sqlite3_column_text16(sqlite3_stmt*, int iCol);
SQLITE_API int sqlite3_column_type(sqlite3_stmt*, int iCol);
SQLITE_API sqlite3_value*sqlite3_column_value(sqlite3_stmt*, int iCol);

SQLite installation

SQLite official website: https://www.sqlite.org/index.html

SQLite official website download address: https://www.sqlite.org/download.html

There are two ways to use SQLite to operate the database in c + + projects. Next, we will talk about two ways:

Source file operation database

In C + + project, SQLite database can be operated by calling SQLite source file code. Realize the basic functions, including creating database, opening and connecting database, building table, adding data and closing database.

1. Download the sqlite source file from the official website, create the sqlite folder, and put the extracted file into the sqlite folder:
   

2. Use the following files, especially SQLite3 c sqlite3. H these two documents.
   

3. Create a new VC + + solution and new projects with vs2019.

4. Put the sqlite folder, header file and source file into the project.

5. Set the project as a multi byte character set to avoid the problems caused by the character set.

   1. The multi byte character set uses sqlite3_open
   2. The wide character set uses sqlite3_open16
      

6. C/C + + mixed programming, compilation header file error, SQLite3 C set not to use precompiled headers, or compile them into dynamic libraries for loading
   

7. Reference header files, create sqlite3 objects, and use them in subsequent database operations

8. Create a new database. At this time, you can see the generated database file under the project directory. Note: opening (connecting) the database is the same function as creating a new one. Whether it exists will be judged internally. If it does not exist, it will be created and opened again. If it exists, it will be opened directly.

   #include "sqlite3.h"
   #include <iostream>
   
   int callback(void*, int argc, char* argv[], char* names[])
   {
      for (int i = 0; i < argc; i++)
       {
           std::cout << names[i] << "= " << argv[i] << std::endl;
      }
   	return 0; 
   }
   
   int main()
   {
       sqlite3* pdb = NULL;
       char* errMsg = NULL;
       int ret = sqlite3_open("edyun. db", &pdb);
       if (ret){
           std::cout << sqlite3_errmsg(pdb) << std::endl;
           return -1;
       else {
           std::cout << "open edyun.db success! \r\n";
       }
       
       const char* sql =
           "CREATE TABLE EDoYun(" \
           "ID	    INT PRIMARY KEY NOT NULL,"\
           "NAME   TEXT            NOT NULL);";
   	do {
   		ret = sqlite3_exec(pdb, sql, NULL, NULL, &errMsg);
       	if (ret != SQLITE_OK){
          		std::cout<< errMsg << " return " << ret << std::endl;
           	sqlite3_free(errMsg);
           	break;
       	} else {
   			std::cout<< "create table EDoYun success!" << std::endl;
           	break;
       	}
           sql = "INSERT INTO EDoYun (ID,NAME)VALUES(1,\"jueding\");";
           ret = sqlite3_exec(pdb, sq, NULL, NULL, &errMsg);
           if (ret != SQLITE_OK){
               std::cout<< errMsg << " return " << ret << std::endl;
               sqlite3_free(errMsg);
               break;
           } else {
               std::cout << "insert table EDoYun success!" << std::endl;
           }
   	
           sql = "SELECT * FROM EDoYun;";
           ret = sqlite3_exec(pdb, sql, callback, NULL, &errMsg);
           if (ret != SQLITE_OK){
               std::cout<< errMsg << " return " << ret << std::endl;
               sqlite3_free(errMsg);
               break;
           } else {
       		std:: cout<< "insert table EDoYun success!" << std::endl;
       	}
           sql = "DROP TABLE EDoYun;" ;
   		ret = sqlite3_exec(pdb, sql, NULL, NULL, &errMsg);
           if (ret != SQLITE_OK){
   			std::cout << errMsg << " return " << ret << std::endl;
               sqlite3_free(errMsg);
   			break;
           } else {
   			std::cout << " drop table EDoYun success!" << std::endl;
           }
       } while (false);
           
   	sqlite3_close(pdb);
   }
   
   

Dynamic library operation database

By calling sqlite dynamic library, the operation of sqlite database and basic functions are realized, including the functions of creating database, opening and connecting database, building table, adding data and closing database. The demo project created by vs2019 can be executed in the release folder exe file demo