1）系統(tǒng)命令 以 ‘.’ 開頭
.help 幫助手冊
.exit 退出
.table 查看當前數(shù)據(jù)庫的有的表格名
.databases
.schema 查看表的結(jié)構(gòu)屬性
2）sql語句 以 ';'結(jié)尾
1. 創(chuàng)建表格
create table <table-name> (id integer, age integer, name char, score float);
2. 插入數(shù)據(jù)
insert into <table-name> values(1001, 15, zhangsan, 89);// 標準插入
insert into <table-name> values (id, age, name) values(1002, 18, lisi);//按列名插入
3. 查看數(shù)據(jù)庫記錄
select * from <table-name>; //查看全部
select * from <table-name> were age = 15;//按列名的信息 age=15 的查看
select * from <table-name> were age = 15 and name = lisi;//按多個列名的信息查看
select * from <table-name> were age = 15 or name = lisi;//age = 15 或 name = lisi滿足一個條件就輸出
select name, id from <table-name>;//指定字段查詢
select * from <table-name> where score >= 90 and score <= 100;//查詢score[90,100] 之間的信息
4. 刪除信息
delete from <table-name> where id = 1005 and name = 'zhangsan';//刪除id=1005和name=‘張三’的信息（同時滿足）
delete from <table-name> where id = 1005 or name = 'zhangsan';//刪除id=1005或name=‘張三’的信息（有一個條件滿足就執(zhí)行）
5. 更新數(shù)據(jù)
update <table-name> set name = 'wangwu' where id = 1002;//將id是1002的行信息中的name全部改成 'wangwu'；
6. 增加一列
alter table <table-name> add column score float;//在<table-name>表名后面添加一列名: score屬性為：float
7. 刪除一列 （sqlite3 不支持直接刪除一列）
1-- 創(chuàng)建一張新表
create table <new-table-name> as select id, name, score from <table-name>;//從<table-name>表名中選擇指定的列（id，name，score）為基礎(chǔ)創(chuàng)建<new-table-name>新的表
2-- 刪除原有的表
drop table <table-name>;
3-- 將新表的名字改成原有的舊表的名字
alter table <new-table-name> rename to <table-name>;//將<table-name>新表明改成<table-name>舊表明
3）sqlite3 數(shù)據(jù)庫 C語言 API
1. 打開數(shù)據(jù)庫
int sqlite3_open (
const char *filename, /* Database filename (UTF-8) */
sqlite3 **ppDb /* OUT: SQLite db handle */
);
功能：打開數(shù)據(jù)庫
參數(shù)：filename 數(shù)據(jù)庫名稱
ppdb 數(shù)據(jù)庫句柄
返回值：成功為0 SQLITE_OK ,出錯 錯誤碼
2. 關(guān)閉數(shù)據(jù)庫
int sqlite3_close(sqlite3* db);
功能：關(guān)閉數(shù)據(jù)庫
參數(shù)：
返回值：成功為0 SQLITE_OK ,出錯 錯誤碼
3. 錯誤信息
const char *sqlite3_errmsg(sqlite3*db);
功能：得到錯誤信息的描述
4. 執(zhí)行一條sql語句
int sqlite3_exec(
sqlite3* db, /* An open database */
const char *sql, /* SQL to be evaluated */
int (*callback)(void* arg,int,char**,char**), /* Callback function */
void * arg, /* 1st argument to callback */
char **errmsg /* Error msg written here */
);
功能：執(zhí)行一條sql語句
參數(shù)：db 數(shù)據(jù)庫句柄
sql sql語句
callback 回調(diào)函數(shù)，只有在查詢時，才傳參
arg 為回調(diào)函數(shù)傳遞參數(shù)
errmsg 錯誤消息
返回值：成功 SQLITE_OK
5. 查詢回調(diào)函數(shù)
查詢回調(diào)函數(shù)：
int (*callback)(void* arg,int ncolumns ,char** f_value,char** f_name), /* Callback function */
功能：查詢語句執(zhí)行之后，會回調(diào)此函數(shù)
參數(shù)：arg 接收sqlite3_exec 傳遞來的參數(shù)
ncolumns 列數(shù)
f_value 列的值得地址
f_name 列的名稱
返回值：0，
6. 查詢函數(shù)
int sqlite3_get_table(
sqlite3 *db, /* An open database */
const char *zSql, /* SQL to be evaluated */
char ***pazResult, /* Results of the query */
int *pnRow, /* Number of result rows written here */
int *pnColumn, /* Number of result columns written here */
char **pzErrmsg /* Error msg written here */
);
功能：不需要回調(diào)函數(shù)的查詢函數(shù)
參數(shù)：
db 數(shù)據(jù)庫句柄
zSql 存放sqlite3的語句
pazResult 存放表中的數(shù)據(jù)
pnRow 行
pnColumn 列
pzErrmsg 錯誤信息
小知識：如果結(jié)構(gòu)體中定義的是一級指針，那么你要定義變量取地址的形式，如果是二級指針函數(shù)，你要定義一級指針取地址的形式
學生信息實例
student.c文章來源：http://www.zghlxwxcb.cn/news/detail-579101.html
#include <stdio.h>
#include <sqlite3.h>
#include <string.h>
#include <stdlib.h>
#define N 128
#define DATABASE "student.db"
int do_insert(sqlite3 *db);
int do_query(sqlite3 *db);
int do_query_1(sqlite3 *db);
int do_delete(sqlite3 *db);
int do_update(sqlite3 *db);
int main (int argc, char *argv[]) {
sqlite3 *db;
char *errmsg;
int n;
//打開SQlite數(shù)據(jù)庫文件.db
if (sqlite3_open(DATABASE, &db) != SQLITE_OK)
printf("%s\n", errmsg);
else
printf("open DATABASE success.\n");
//創(chuàng)建一張數(shù)據(jù)庫的表格 stu
if (sqlite3_exec(db, "create table stu(id integer, name char, sex char, score integer);",
NULL, NULL, &errmsg) != SQLITE_OK) {
printf("%s\n", errmsg);
} else {
printf("Create of open table success.\n");
}
while (1) {
printf("************************************************\n");
printf("1: insert 2:query 3: delete 4: update 5: quit\n");
printf("************************************************\n");
printf("Please select:");
scanf("%d", &n);
getchar();
switch(n) {
case 1:
do_insert(db);//插入信息
break;
case 2:
//do_query(db);//查詢
do_query_1(db);
break;
case 3:
do_delete(db);//刪除
break;
case 4:
do_update(db);//更新
break;
case 5:
printf("main exit.\n");
sqlite3_close(db);
exit(0);
break;
default:
printf("Invalid data n.\n");
}
}
return 0;
}
int do_insert(sqlite3 *db)
{
int id;
char name[32] = {};
char sex;
int score;
char sql[N] = {};
char *errmsg;
printf("Input id: ");
scanf("%d", &id);
printf("Input name: ");
scanf("%s", name);
getchar();//回收掉垃圾字符
printf("Input sex: ");
scanf("%c", &sex);
printf("Input score: ");
scanf("%d", &score);
sprintf(sql, "insert into stu values(%d, '%s','%c', %d )", id, name, sex, score );
if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
printf("%s\n", errmsg);
else
printf("Insert done.\n");
return 0;
}
int callback(int* arg,int f_num ,char** f_value,char** f_name) {
int i;
for (i = 0; i < f_num; i++) {
printf("%-8s", f_value[i]);
}
putchar(10);
return 0;
}
int do_query(sqlite3 *db)
{
char sql[N] = {};
char *errmsg;
sprintf(sql, "select * from stu");
if(sqlite3_exec(db, sql, callback, NULL, &errmsg) != SQLITE_OK)
printf("%s\n", errmsg);
else
printf("Query done.\n");
return 0;
}
int do_query_1(sqlite3 *db)
{
char *errmsg;
char ** resultp;
int nrow;
int ncolumn;
if(sqlite3_get_table(db, "select * from stu", &resultp, &nrow, &ncolumn, &errmsg) != SQLITE_OK)
printf("%s\n", errmsg);
else
printf("query done.\n");
int i = 0;
int j = 0;
int index = ncolumn;
for(j = 0; j < ncolumn; j++)
{
printf("%-10s", resultp[j]);
}
putchar(10);
for(i = 0; i < nrow; i++)
{
for(j = 0; j < ncolumn; j++)
{
printf("%-10s ", resultp[index++]);
}
putchar(10);
}
return 0;
}
int do_delete(sqlite3 *db)
{
char sql[N] = {};
char *errmsg;
int id;
printf("Invalid id:");
scanf("%d", &id);
getchar();
sprintf(sql, "delete from stu where id = %d;", id);
if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
printf("%s\n", errmsg);
else
printf("Delete done.\n");
return 0;
}
int do_update(sqlite3 *db)
{
char *errmsg;
char sql[N] = {};
char name[32] = "zhangsan";
int id;
printf("Input id:");
scanf("%d", &id);
sprintf(sql, "update stu set name = '%s' where id = %d;",name, id);
if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
printf("%s\n", errmsg);
else
printf("Delete done.\n");
return 0;
}
輸出結(jié)果：
文章來源地址http://www.zghlxwxcb.cn/news/detail-579101.html
到了這里，關(guān)于SQlite3數(shù)據(jù)庫相關(guān)相關(guān)命令的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
