命令行操作
简单的说些常用命令
之前我们已经对sqlite源码进行了编译,生成一个.exe文件
打开这个文件可以进入windows下的sqlite命令行操作界面
输入下面两行命令(命令都是以 .XXXX形式 ,语句都以 XXXXX; 形式)
.open 打开demo.db数据库,如果不存在则创建数据库
.databases 查看当前已经打开的数据库信息
.exit 退出界面(这个不好截图)
先打开数据库
create table demo1 (id integer primary key ,name text);创建表demo1,包含两个字段,id和name,id作为主键(SQL语句以;结尾)
.table 查看当前数据库下所有的表
.schema 查看表的结构,主要是看列的信息
drop table demo1; 销毁表demo1(这句也不演示,下面还要用到demo1表)
.table 在插入前先查看数据库中的表是否存在
insert into demo1 values (1, 'zhao'); 插入记录(注意'zhao'的单引号)
select * from demo1 查看表中所有字段,查询语句可以用where来具体查询哪一条记录,比如下面的语句
select * from demo1 where id = 1; 具体查看id = 1 的那条记录
.header on 显示字段的名称
.mode column 以列模式显示记录
多插了几条记录,看起来直观一点
order by id desc 按id 降序 asc升序
更新表中记录,如果没有where,则会修改表中所有记录
删除记录,如果没有where,则会删除所有记录(跟drop不同,drop是删除表,delete是删除记录,表可以是空的)
下面是用C语言实现的SQL语句
我这里是创建的动态库,不过不影响语句(代码有点长)
#include "59108.h"
#include <iostream>
#include <string.h>
#pragma comment(lib,"..\\debug\\sqlite_win32_dll.lib")
using namespace std;
sqlite3 * ppDb = NULL;
int ret = 0;
char * errmsg;
//菜单
void menu()
{
cout << " ~菜单~ : " << endl;
cout << " ~1.打开数据库~ " << endl;
cout << " ~2.创建表单~ " << endl;
cout << " ~3.插入记录~ " << endl;
cout << " ~4.删除记录~ " << endl;
cout << " ~5.修改记录~ " << endl;
cout << " ~6.查看记录~ " << endl;
cout << " ~7.关闭数据库~ " << endl;
cout << " ~8.退出程序~ " << endl;
cout << " by ljl " << endl;
}
//打开,创建数据库
void open_create_db()
{
//sqlite3 * ppDb = NULL;
char str[20] = {0}; //str[20]放的是数据库的名字
cout << "输入要打开的数据库" << endl;
cin >> str;
ret = sqlite3_open(str,&ppDb);
if(ret == SQLITE_OK)
{
cout << str << " 打开成功!" << endl;
}
else
{
cout << str << " 打开失败!" << endl;
}
}
//创建表
void create_form() //str2[20]存放的是表的名字
{
cout << "输入要创建的表的名字" << endl;
char str[20] = {0};
cin >> str;
char sql[1024] = {0};
sprintf(sql,"drop table if exists %s",str);
sqlite3_exec(ppDb,sql,NULL,NULL,&errmsg); //在创建表时,如果表存在,则删除
memset(sql,0,sizeof(sql)); //清空sql
sprintf(sql,"create table %s(id int primary key,name text)",str);//创建表
ret = sqlite3_exec(ppDb,sql,NULL,NULL,&errmsg);
if(ret == SQLITE_OK)
{
cout << str << " 表创建成功!" << endl;
}
else
{
cout << str << " 表创建失败!" << endl;
}
}
//插入记录
void insert_record()
{
cout << "输入要插入记录的表单"<< endl;
char str[20] = {0};
cin >> str;
cout << "输入要插入的id"<< endl;
int x = 0;
cin >> x;
cout << "输入要插入的name"<< endl;
char name[20] = {0};
cin >> name;
char sql[1024] = {0};
sprintf(sql,"insert into %s values( %d, '%s')",str,x,name); //格式 value(id , 'name') 单引号不能少
ret = sqlite3_exec(ppDb,sql,NULL,NULL,&errmsg);
if(ret == SQLITE_OK)
{
cout << "记录插入成功!" << endl;
}
else
{
cout << "记录插入失败!" << endl;
}
}
//删除记录
void delete_record() //str2[20]存放的是表的名字
{
char sql[1024] = {0};
cout << "输入要删除记录的表单"<< endl;
char str[20] = {0};
cin >> str;
cout << "1按id删除,2按name删除" << endl;
int x = 0;
int y = 0;
char Name[20] = {0};
cin >> x ;
while(1)
{
if ( x == 1 )
{
cout << "输入要删除的id" << endl;
cin >> y ;
memset(sql,0,sizeof(sql));
sprintf(sql,"delete from %s where id = %d",str,y);
ret = sqlite3_exec(ppDb,sql,NULL,NULL,&errmsg);
if(ret == SQLITE_OK)
{
cout << "记录删除成功!" << endl;
}
else
{
cout << "记录删除失败!" << endl;
}
break;
}
else if ( x == 2)
{
cout << "输入要删除的name" << endl;
cin >> Name;
memset(sql,0,sizeof(sql));
sprintf(sql,"delete from %s where name = %s",str,Name);
ret = sqlite3_exec(ppDb,sql,NULL,NULL,&errmsg);
if(ret == SQLITE_OK)
{
cout << "记录删除成功!" << endl;
}
else
{
cout << "记录删除失败!" << endl;
}
break;
}
else if ( x !=1 && x != 2)
{
cout << "重新输入" << endl;
}
}
}
//修改记录
void update_record()
{
char name[20] = {0};
char sql[1024] = {0};
int y = 0;
cout << "输入要修改记录的表单"<< endl;
char str[20] = {0};
cin >> str;
cout << "1根据名字修改id,2根据id修改名字" << endl;
int x = 0;
cin >> x;
while(1)
{
if ( x == 2 )
{
cout << "你想修改id是多少的名字" << endl;
cin >> y;
cout << "你想把名字改成什么" << endl;
cin >> name;
memset(sql,0,sizeof(sql));
sprintf(sql,"update %s set name = '%s' where id = %d",str,name,y);
ret = sqlite3_exec(ppDb,sql,NULL,NULL,&errmsg);
if(ret == SQLITE_OK)
{
cout << "记录修改成功!" << endl;
}
else
{
cout << "记录修改失败!" << endl;
}
break;
}
else if ( x == 1)
{
cout << "你想修改谁的id" << endl;
cin >> name;
cout << "你想把id改成什么" << endl;
cin >> y;
memset(sql,0,sizeof(sql));
sprintf(sql,"update %s set id = %d where name = '%s'",str,y,name);
ret = sqlite3_exec(ppDb,sql,NULL,NULL,&errmsg);
if(ret == SQLITE_OK)
{
cout << "记录修改成功!" << endl;
}
else
{
cout << "记录修改失败!" << endl;
}
break;
}
else if ( x !=1 && x != 2)
{
cout << "重新输入" << endl;
}
}
}
//查看记录
void view_record()
{
char **dbresult;
int j,nrow,ncolumn,index;
char sql[1024] = {0};
cout << "输入要查看记录的表单" << endl;
char str[20] = {0};
cin >> str;
sprintf(sql,"select * from %s",str);
ret = sqlite3_get_table(ppDb,sql,&dbresult,&nrow,&ncolumn,&errmsg);
if(ret == SQLITE_OK)
{
cout << "查询到 " << nrow << " 行结果" << endl;
index = ncolumn;
for(int i = 0;i < nrow; i++)
{
printf("[%2i]",i);
for(j = 0;j < ncolumn;j++)
{
printf("%s",dbresult[index]);
index++;
}
cout << endl;
}
sqlite3_free_table(dbresult);
}
}
//关闭数据库
void close_db()
{
char str[20] = {0}; //str[20]放的是数据库的名字
cout << "输入要关闭的数据库" << endl;
cin >> str;
ret = sqlite3_close(ppDb);
if(ret == SQLITE_OK)
{
cout << str << " 关闭成功!" << endl;
}
else
{
cout << str << " 关闭失败!" << endl;
}
}
我这边写了几个常用的功能.下面附带整个项目的代码,有兴趣的可以下载下来看看,运行运行
百度网盘
链接https://pan.baidu.com/s/1idcPA3jV9wyKQR9YCz43yw
密码unnb
life means struggle.
生活就是斗争.