1: Using Sqlite3 in C requires calling function interface operations: Sqlite3*db;intStatus=sqlite_open ("dbname", &db);//Open or create a database intStatus=sqlite3_exec (Db,yuju,huitiaohanshu,0, Cuowuzhizhen);//all operations of the database have to be performed through this functionSqlite3_close (DB);//Close Database resources When you are finished using2: Sqlite3 statement: Build tables: Create TABLE pic ([picid] integer PRIMARY KEY autoincrement, [inserttime] TimeStamp not NULL DEFAULT ( DateTime (' Now','localtime')), [url] varchar ( -)); //created a three field (Picid,url,inserttime) and the insertion time here is automatically inserted into the current local timeconstraint: notNULL: Unique: Single PRIMARY key: Primary key FOREIGN key: foreign key (creates a connection between the table and the parent table) Check: Condition check for the entry of the itemdefault: Default Value data type: (similar match, will automatically look for a more appropriate specific data type to match) integer:intInteger int2 int8 (unsigned bigint) (Bigint) Text:character ( -) varchar (255) Text Clob .... none:real:realDouble float.. Numeric:boolean data datetime CREATE TABLE teacher (ID integer primary key auto increment); CREATE table stu (ID integer primary key autoincrement, name varchar ( -) Check (length (name) >3), tel varchar ( One) notNULL default '13631629322', cls integer notNULL, Unique (Name,tel),//set name and tel combination uniqueForeign KEY (CLS) references teacher (ID));//binding IDs in two tablesinsert: INSERT INTO pic ([Picid],[url]) VALUES (NULL,'%s'); //parameters that can be followed by the default table when each field is to be insertedINSERT INTO STU1Select* fromStu; //Import all the contents of one table into anotherEnquiry:Select* frompic; SelectName fromStuwhereId=0; SelectId fromStu Order by ID;//sort output by ID Select* fromStuwhereName like"t%";//find data in Stu that begins with T Select* fromStu GROUP by ID have id>2;//querying id>2 data and grouping by ID Select* fromStu Limit1Offset2;//output The following data starting at index 2//The query in C is generally used as a callback, a function of what should be done when the query data is executed in the execution of the SQL statement
Example:
#include <stdio.h>#include<time.h>#include<sqlite3.h>#include<string.h>//callback function declaration for a queryintSelect_callback (void* Data,intCol_count,Char* * Col_values,Char**col_name);intMainintargcChar*argv[]) { Const Char* SSQL1 ="CREATE TABLE pic ([picid] integer PRIMARY KEY autoincrement, [inserttime] TimeStamp not NULL DEFAULT (DateTime (' Now ', ' l Ocaltime ')), [url] varchar ( ));"; Char* Perrmsg =0; intresult =0; //connecting to a databaseSqlite3 * db =0; intret = Sqlite3_open ("./test9.db", &db); if(Ret! =SQLITE_OK) {fprintf (stderr,"Unable to open database:%s", sqlite3_errmsg (db)); return(1); } printf ("database connection succeeded!\n"); //To perform a built-table SQLSqlite3_exec (db, SSQL1,0,0, &perrmsg); if(ret!=SQLITE_OK) {fprintf (stderr,"SQL Error:%s\n", perrmsg); Sqlite3_free (PERRMSG); return 1; } printf ("Build Table Success! \ n"); //execute insert Record SQL//result = sqlite3_exec (db, "INSERT into pic ([url]) VALUES ('/C ');", 0, 0, &perrmsg); inti; for(i=0;i<5; i++){ if(Sqlite3_exec (DB,"insert INTO pic ([Picid],[url]) VALUES (null, '/C ')",0,0, &perrmsg)! =SQLITE_OK) {fprintf (stderr,"Insert SQL Error:%s\n", perrmsg); Sqlite3_free (PERRMSG); printf ("Insert failed! \ n"); }Else{printf ("Insert data successfully \ n"); } } //Querying data Tablesprintf"start querying database contents \ n"); //sqlite3_exec (DB, "SELECT * from pic;", Select_callback, 0, &perrmsg); if(Sqlite3_exec (DB,"SELECT * from pic;", Select_callback,0, &perrmsg)! =SQLITE_OK) {fprintf (stderr,"Insert SQL Error:%s\n", perrmsg); }Else{printf ("query failed \ n"); } //Close the databasesqlite3_close (DB); DB=0; printf ("database shutdown succeeded!\n"); return 0;}intSelect_callback (void* Data,intCol_count,Char* * Col_values,Char**col_name) { //The function is recalled for each record, and how many times it is recalled inti; for(i=0; i < Col_count; i++) {printf ("%s =%s\n", Col_name[i], col_values[i] = =0?"NULL": Col_values[i]); } return 0;}
Linux using Sqlite3