SQLite快速上手（c语言代码调用）

1、下载：https://www.sqlite.org/download.html

 

2、安装：

$ tar xvzf sqlite-autoconf-3071502.tar.gz $ cd sqlite-autoconf-3071502 $ ./configure --prefix=/usr/local $ make $ make install

3、创建数据库：

$sqlite3 DatabaseName.db

创建成功之后可以使用.databases来检查他是否在数据库列表中

 

sqlite>.databases seq name file --- --------------- ---------------------- 0 main /home/sqlite/DatabaseName.db

4、导入导出

可以使用.dump命令导入导出数据库

 

$sqlite3 testDB.db .dump > testDB.sql $sqlite3 testDB.db < testDB.sql

5、创建表

sqlite> CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );

查看是否创建成功

sqlite> .table COMPANY DEPARTMENT

6、删除表

sqlite> DROP TABLE COMPANY;

查看删除结果

sqlite> .table DEPARTMENT

7、insert语句

sqlite> CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );

 

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 ); 或者 INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 );

8、显示表中信息

sqlite>.header on sqlite>.mode column sqlite> SELECT * FROM COMPANY; ID NAME AGE ADDRESS SALARY -- ----- --- ---------- ------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0

 

如果只想获取 COMPANY 表中指定的字段，则使用下面的查询：

sqlite> SELECT ID, NAME, SALARY FROM COMPANY; ID NAME SALARY ---------- ---------- ---------- 1 Paul 20000.0 2 Allen 15000.0 3 Teddy 20000.0 4 Mark 65000.0 5 David 85000.0 6 Kim 45000.0 7 James 10000.0

 

设置输出宽度：

 

sqlite>.width 10, 20, 10

 9、其他语法及高级教程详情：https://www.runoob.com/sqlite/sqlite-tutorial.html

10、C语言实现和操作

 

#include <stdio.h> #include <stdlib.h> #include <sqlite3.h> static int callback(void *data, int argc, char **argv, char **azColName){ int i; fprintf(stderr, "%s: ", (const char*)data); for(i=0; i<argc; i++){ printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL"); } printf("\n"); return 0; } int main(int argc, char* argv[]) { sqlite3 *db; char *zErrMsg = 0; int rc; char *sql; const char* data = "Callback function called"; /* Open database */ rc = sqlite3_open("DatabaseName.db", &db); if( rc ){ fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db)); exit(0); }else{ fprintf(stderr, "Opened database successfully\n"); } /* Create SQL statement */ sql = "DELETE from COMPANY where ID=2; " \ "SELECT * from COMPANY"; /* Execute SQL statement */ rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg); if( rc != SQLITE_OK ){ fprintf(stderr, "SQL error: %s\n", zErrMsg); sqlite3_free(zErrMsg); }else{ fprintf(stdout, "Operation done successfully\n"); } sqlite3_close(db); return 0; }