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;
}