本文主要演示sqlite开源项目中的数据库方法类基础操作使用说明(包括创建数据表、插入数行、删除行、查询数据表等)。本项目旨在分享知识和经验,欢迎广大网友一起加入到开源项目中一起分享贡献知识和经验。
命令行模式的sqlite操作程序
首先编译命令行模式的sqlite操作程序,源代码如下
#include <stdio.h>
#include <iostream>
#include <sstream>
#include "sqlite/sqlite.hpp"
#include <cstring>
#include "./Wtime.h"
#include <sys/time.h>
#include <unistd.h>
#include "./main.hpp"
typedef struct {
bool output;
bool timing;
} dbCfg_t;
/********************************
* std::cin.getline()属于字符串流
* 用法:char str[100];std::cin.getline(str, 100);
*
* std::getline()属于std::string流
* 用法:std::string str;std::getline(std::cin, str);
********************************/
int test_exec(int argc, char* argv[])
{
class sql::sqlite* handle;
struct timeval start,end;
dbCfg_t cfg;
std::string sqlCmd;
if (argc>2) {
handle = new sql::sqlite(argv[2]);
if (handle == nullptr) {
std::cout << "数据库[" << argv[2] << "]打开失败!" << std::endl;
return -1;
}
} else {
std::cout << "参数错误:输入正确的参数,如:./app 6 database.db" << std::endl;
return -1;
}
//memset (&cfg, 0, sizeof (TdbCfg));
cfg.timing = true;
cfg.output = true;
while (1) {
std::cout << ">";
//std::cin >> sqlCmd;
std::getline (std::cin, sqlCmd);
if (sqlCmd == "help") {
std::cout << "[帮助]" << std::endl;
std::cout << "output disable:关闭查询输出" << std::endl;
std::cout << "output enable:打开查询输出" << std::endl;
std::cout << "timing disable:关闭计时器" << std::endl;
std::cout << "timing enable:打开计数器" << std::endl;
std::cout << "exit:退出" << std::endl;
} else if (sqlCmd == "output disable") {
cfg.output = false;
} else if (sqlCmd == "output enable") {
cfg.output = true;
} else if (sqlCmd == "timing disable") {
cfg.timing = false;
} else if (sqlCmd == "timing enable") {
cfg.timing = true;
} else if (sqlCmd == "exit") {
delete handle;
return 0;
} else {
gettimeofday (&start, NULL);
cfg.output?handle->exec (sqlCmd.c_str(), test_callback):handle->exec (sqlCmd.c_str());
//sleep (1);
gettimeofday (&end, NULL);
if (cfg.timing) {
std::cout << "耗时:" << (end.tv_sec-start.tv_sec)*1000 + (end.tv_usec-start.tv_usec)/1000 << " ms,";
std::cout << (end.tv_usec-start.tv_usec)%1000 << " us" << std::endl;
}
}
sqlCmd.clear ();
}
}
编译方法
make type=1
这里我们把编译好的命令行程序重命名为cmdexec,以备后续使用
mv exec cmdexec
创建数据表
接下来是创建数据表的测试代码
#include "./samples_create_table.hpp"
#include "sqlite/sqlite.h"
int samples_create_table (int argc, char** argv)
{
if (argc < 2) {
printf ("请输入一个参数以指定数据库文件名!\n");
return -1;
}
sql::sqlite db(argv[1]);
db.exec ("create table if not exists employee("
"ID INTEGER PRIMARY KEY AUTOINCREMENT, "
"NAME TEXT NOT NULL UNIQUE, "
"AGE INT NOT NULL, "
"ADDRESS CHAR(50), "
"SALARY REAL"
");");
return 0;
}
上面的sql语句采用的是未包装的全拼命令,也可以使用接口定义好的宏定义来节省一些重复操作,代码如下:
#include "./samples_create_table.hpp"
#include "sqlite/sqlite.h"
int samples_create_table (int argc, char** argv)
{
if (argc < 2) {
printf ("请输入一个参数以指定数据库文件名!\n");
return -1;
}
sql::sqlite db(argv[1]);
db.exec (SQLCMD_CREATE_TABLE ("employee",
"ID INTEGER PRIMARY KEY AUTOINCREMENT, "
"NAME TEXT NOT NULL UNIQUE, "
"AGE INT NOT NULL, "
"ADDRESS CHAR(50), "
"SALARY REAL"
));
return 0;
}
数据库语句简化宏定义
项目中定义好的宏定义在inc/sqlite/sqlCmd.h文件中,其内容如下:
#ifndef __SELDOM_SQLITE_SQLCMD_H
#define __SELDOM_SQLITE_SQLCMD_H
//SQL 语句 仅供参考
//创建表
#define SQLCMD_CREATE_TABLE(TABLE_NAME,MEMBER) "CREATE TABLE IF NOT EXISTS " TABLE_NAME " (" MEMBER ");"
//删除表
//#define SQLCMD_DROP_TABLE(TABLE_NAME) "DROP IF EXISTS TABLE " TABLE_NAME ";"
#define SQLCMD_DROP_TABLE(TABLE_NAME) "DROP TABLE " TABLE_NAME ";"
//检查表是否存在
#define SQLCMD_CHECK_TABLE(TABLE_NAME) "SELECT COUNT(*) AS Cnt FROM sqlite_master where type='table' and name='" TABLE_NAME "';"
//查询记录数
#define SQLCMD_RECORDCNT(TABLE_NAME) "SELECT COUNT(*) AS Cnt FROM " TABLE_NAME
#define SQLCMD_RECORDCNTEX(TABLE_NAME,DONDITION) "SELECT COUNT(*) AS Cnt FROM " TABLE_NAME " WHERE " DONDITION
//将表中的rowid自增序列归零,下一次插入记录的行号从当前设置的0+1开始,注意:seq的值不能小于当前最大的rowid
#define SQLCMD_CLEAR_TABLE_ROWID(TABLE_NAME) "UPDATE sqlite_sequence SET seq=0 WHERE name='" TABLE_NAME "';"
//查询表字段等信息
/***************
* 判断 pk 列是否为1,name即为主键列名
***************/
#define SQLCMD_TABLEINFO(TABLE_NAME) "PRAGMA table_info(" TABLE_NAME ");"
//重命名表
#define SQLCMD_RENAME_TABLE(OLD_NAME,NEW_NAME) "ALTER TABLE " OLD_NAME " RENAME TO " NEW_NAME
// 增加表中字段
#define SQLCMD_ADD_COLUMN(TABLE_NAME,TABLE_FIELD) "ALTER TABLE " TABLE_NAME " ADD COLUMN " TABLE_FIELD
// 创建索引
#define SQLCMD_CREATE_IDX(TABLE_NAME,IDX_NAME,IDX_PARAM) "CREATE INDEX IF NOT EXISTS " IDX_NAME " ON " TABLE_NAME " (" IDX_PARAM ") ;"
//#define SQLCMD_CREATE_IDX(TABLE_NAME,IDX_NAME,IDX_PARAM) "CREATE INDEX " IDX_NAME " ON " TABLE_NAME " (" IDX_PARAM ") ;"
// 查询索引是否存在
#define SQLCMD_IDX_EXIST(IDX_NAME) "SELECT COUNT(*) AS Cnt FROM sqlite_master WHERE type='index' AND name='" IDX_NAME "'"
// 删除索引
#define SQLCMD_DROP_IDX(IDX_NAME) "DROP INDEX " IDX_NAME
#endif
编译示例程序
make type=4
运行示例程序并检查创建结果
在这里插入图片描述
插入记录
数据表创建成功,接下来插入一些数据;测试代码如下:
#include "./samples_insert.hpp"
#include "sqlite/sqlite.h"
int samples_insert (int argc, char** argv)
{
if (argc < 2) {
printf ("请输入一个参数以指定数据库文件名!\n");
return -1;
}
sql::sqlite db(argv[1]);
db.exec ("INSERT INTO employee(ID,NAME,AGE,ADDRESS,SALARY) VALUES(1, '张三', 18, '珠海', 2500.0);");
db.exec ("INSERT INTO employee(ID,NAME,AGE,ADDRESS,SALARY) VALUES(NULL, '李四', 21, '广州', 6800.0);");
db.exec ("INSERT INTO employee(ID,NAME,AGE,ADDRESS,SALARY) VALUES(NULL, '啊五', 32, '深圳', 16500.0);");
db.exec ("INSERT INTO employee(ID,NAME,AGE,ADDRESS,SALARY) VALUES(NULL, '小哀', 23, '珠海', 13000.0);");
db.exec ("INSERT INTO employee(ID,NAME,AGE,ADDRESS,SALARY) VALUES(NULL, '刘五', 18, '深圳', 4500.0);");
db.exec ("INSERT INTO employee(ID,NAME,AGE,ADDRESS,SALARY) VALUES(NULL, '柯南', 43, '广州', 20500.0);");
db.exec ("INSERT INTO employee(ID,NAME,AGE,ADDRESS,SALARY) VALUES(NULL, '苏周', 51, '广州', 22500.0);");
db.exec ("INSERT INTO employee(ID,NAME,AGE,ADDRESS,SALARY) VALUES(NULL, '啊二', 21, '珠海', 12500.0);");
return 0;
}
编译并运行检查
linux@ubuntu:~/project/sqlite3$ ./exec test.db
linux@ubuntu:~/project/sqlite3$ ls
build cmdexec exec executable inc lib Makefile mkfile README.md samples src test.db
linux@ubuntu:~/project/sqlite3$ ./cmdexec 1 test.db
wsqlite version:V0.0.0.6
>select * from employee;
ID = 1
NAME = 张三
AGE = 18
ADDRESS = 珠海
SALARY = 2500.0
ID = 2
NAME = 李四
AGE = 21
ADDRESS = 广州
SALARY = 6800.0
ID = 3
NAME = 啊五
AGE = 32
ADDRESS = 深圳
SALARY = 16500.0
ID = 4
NAME = 小哀
AGE = 23
ADDRESS = 珠海
SALARY = 13000.0
ID = 5
NAME = 刘五
AGE = 18
ADDRESS = 深圳
SALARY = 4500.0
ID = 6
NAME = 柯南
AGE = 43
ADDRESS = 广州
SALARY = 20500.0
ID = 7
NAME = 苏周
AGE = 51
ADDRESS = 广州
SALARY = 22500.0
ID = 8
NAME = 啊二
AGE = 21
ADDRESS = 珠海
SALARY = 12500.0
耗时:0 ms,377 us
>exit
linux@ubuntu:~/project/sqlite3$
插入同名记录
插入成功,接下来是测试插入重名记录;
插入失败,说明name的唯一性限制正常起作用。
查询记录
接下来测试查询接口。
#include "./samples_query.hpp"
#include "sqlite/sqlite.h"
int samples_query (int argc, char** argv)
{
if (argc < 2) {
printf ("请输入一个参数以指定数据库文件名!\n");
return -1;
}
sql::sqlite db(argv[1]);
sql::query* query = NULL;
query = db.query ("select * from employee;");
if (query == NULL) {
printf ("查询失败或未查询到数据!\n");
return -1;
}
if (query->size () <= 0) {
delete query;
printf ("未查询到数据!\n");
return -1;
}
printf ("总共查询到%d条记录,记录如下:\n", query->size ());
query->begin ();
do {
printf ("ID:%ld, name:%s, age:%ld, address:%s, salary:%f\n"
, query->get_number ("ID", 0)
, query->get_string ("name", "")
, query->get_number ("age", 0)
, query->get_string ("address", "")
, query->get_float ("salary", 0)
);
} while (query->next () > 0);
delete query;
return 0;
}
linux@ubuntu:~/project/sqlite3$ make type=4
-e g++ samples/samples_query.cpp
-e ---------------------------[create exec]-[g++]----------------------------------
-e build/samples/samples_query.obj
build/samples/samples_insert.obj
build/samples/samples_create_table.obj
build/samples/main.obj
linux@ubuntu:~/project/sqlite3$ ./exec test.db
总共查询到8条记录,记录如下:
ID:1, name:张三, age:18, address:珠海, salary:2500.000000
ID:2, name:李四, age:21, address:广州, salary:6800.000000
ID:3, name:啊五, age:32, address:深圳, salary:16500.000000
ID:4, name:小哀, age:23, address:珠海, salary:13000.000000
ID:5, name:刘五, age:18, address:深圳, salary:4500.000000
ID:6, name:柯南, age:43, address:广州, salary:20500.000000
ID:7, name:苏周, age:51, address:广州, salary:22500.000000
ID:8, name:啊二, age:21, address:珠海, salary:12500.000000