Sqlite learning notes 11: Using sqlite to delete records in C language, sqlite learning notes

Sqlite learning notes 11: Using sqlite to delete records in C language, sqlite learning notes

The last section describes how to delete data.

All the previous code is inherited here and passed the test on Ubuntu14.04 and Mac10.9.

# Include <stdio. h> # include <stdlib. h> # include "sqlite/sqlite3.h" # define DB_NANE "sqlite/test. db "sqlite3 * db = NULL; char * SQL = NULL; char * zErrMsg = NULL; const char * data =" Callback function called "; int ret = 0; typedef enum {false, true} bool;/* typedef int (* sqlite3_callback) (void *, Data provided in the 4th argument of sqlite3_exec () int, the number of columns in rowchar **, An array of strings repre Senting fields in the rowchar ** An array of strings representing column names); */static int callback (void * NotUsed, int argc, char ** argv, char ** azColName) {int I = 0; for (I = 0; I <argc; I ++) {printf ("% s = % s \ n", azColName [I], argv [I]? Argv [I]: "NULL");} printf ("\ n"); return 0;} bool connectDB () {ret = sqlite3_open (DB_NANE, & db ); if (ret! = SQLITE_ OK) {fprintf (stderr, "Error open database: % s \ n", sqlite3_errmsg (db); sqlite3_free (zErrMsg); return false;} fprintf (stdout, "Successfully opened database \ n"); return true;} bool createTable () {/* Create SQL statement */SQL = "CREATE TABLE COMPANY (" \ "ID INT PRIMARY KEY NOT NULL," \ "NAME TEXT NOT NULL, "\" age int not null, "\" address char (50), "\" salary real); ";/* Execute SQL state Ment */ret = sqlite3_exec (db, SQL, callback, 0, & zErrMsg); if (ret! = SQLITE_ OK) {fprintf (stderr, "Error SQL: % s \ n", zErrMsg); sqlite3_free (zErrMsg); return false;} fprintf (stdout, "Successfully table created \ n"); return true;} bool insertRecords () {/* Create SQL statement */SQL = "INSERT INTO COMPANY (ID, NAME, AGE, ADDRESS, SALARY) "\" VALUES (1, 'Paul ', 32, 'california', 20000.00); "\" insert into company (ID, NAME, AGE, ADDRESS, SALARY) "\" VALUES (2, 'allen ', 25, 'Texas ', 15000.00); "\" insert into company (ID, NAME, AGE, ADDRESS, SALARY) "\" VALUES (3, 'Teddy', 23, 'norway ', 20000.00); "\" insert into company (ID, NAME, AGE, ADDRESS, SALARY) "\" VALUES (4, 'mark', 25, 'Rich-Mond ', 65000.00); ";/* Execute SQL statement */ret = sqlite3_exec (db, SQL, callback, 0, & zErrMsg); if (ret! = SQLITE_ OK) {fprintf (stderr, "SQL error: % s \ n", zErrMsg); sqlite3_free (zErrMsg); return false;} fprintf (stdout, "successfully records created \ n"); return true;} bool selectFromTable () {/* Create SQL statement */SQL = "SELECT * from COMPANY "; /* Execute SQL statement */ret = sqlite3_exec (db, SQL, callback, (void *) data, & zErrMsg); if (ret! = SQLITE_ OK) {fprintf (stderr, "Error SQL: % s \ n", zErrMsg); sqlite3_free (zErrMsg); return false;} fprintf (stdout, "successfully operation done \ n"); return true;} bool updateTable () {/* Create merged SQL statement */SQL = "UPDATE COMPANY set SALARY = 25000.00 where ID = 1; "\" SELECT * from COMPANY ";/* Execute SQL statement */ret = sqlite3_exec (db, SQL, callback, (void *) data, & zErrMsg); if (ret! = SQLITE_ OK) {fprintf (stderr, "SQL error: % s \ n", zErrMsg); sqlite3_free (zErrMsg); return false;} fprintf (stdout, "Successfully operation done \ n"); return true;} bool deleteTable () /* newly added */{/* Create merged SQL statement */SQL = "DELETE from COMPANY where ID = 2;" \ "SELECT * from COMPANY "; /* Execute SQL statement */ret = sqlite3_exec (db, SQL, callback, (void *) data, & zErrMsg); if (ret! = SQLITE_ OK) {fprintf (stderr, "Error SQL: % s \ n", zErrMsg); sqlite3_free (zErrMsg); return false;} fprintf (stdout, "Successfully operation done \ n"); return true;} bool closeDB () {int ret = 0; ret = sqlite3_close (db); if (ret = SQLITE_BUSY) {return false;} return true;} int main (int argc, char * argv []) {connectDB ();/* createTable (); * // * insertRecords (); */selectFromTable ();/* updateTable (); */deleteTable (); selectFromTable (); closeDB (); return 0 ;}

Sqlite database deletion. Duplicate data in the table is ensured to be unique.

$ Sqlite3
SQLite version 3.7.13 2012-06-11 02:05:22
Enter ". help" for instructions
Enter SQL statements terminated with ";"
Sqlite> create table tb_test (id int, val char (32 ));
Sqlite> insert into tb_test values (1, 'adb ');
Sqlite> insert into tb_test values (2, 'def ');
Sqlite> insert into tb_test values (2, 'ghi ');
Sqlite> insert into tb_test values (1, 'jkl ');
Sqlite> insert into tb_test values (3, 'mnu ');
Sqlite> select * from tb_test;
1 | adb
2 | def
2 | ghi
1 | jkl
3 | mnu
Sqlite> select * from tb_test
...> Where not exists (select 1
...> From tb_test B
...> Where B. id = a. id
...> And a. rowid> B. rowid)
...>;
1 | adb
2 | def
3 | mnu
Sqlite>. q
$

How does sqlite3 query all tables in the database using C language?

SELECT name FROM sqlite_master

WHERE type = 'table'
Order by name;
Use this query statement in C Language