Linux using Sqlite3

Source: Internet
Author: User
Tags local time one table sql error

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.