常用Sqlite语句

最新推荐文章于 2025-06-14 14:14:35 发布

原创最新推荐文章于 2025-06-14 14:14:35 发布 · 1.1k 阅读

6 ·

CC 4.0 BY-SA版权

文章标签：

#sqlite3 #数据库

数据库专栏收录该内容

4 篇文章

订阅专栏

SQL的指令格式
所以的SQL指令都是以分号（;）结尾的。如果遇到两个减号（–）则代表注解，sqlite3会略过去。

请记住…
SQL 对大小写不敏感：SELECT 与 select 是相同的。

一些最重要的 SQL 命令

CREATE TABLE - 创建新表
INSERT INTO - 向数据库中插入新数据
SELECT - 从数据库中提取数据
UPDATE - 更新数据库中的数据
DELETE - 从数据库中删除数据

CREATE DATABASE - 创建新数据库
ALTER DATABASE - 修改数据库
ALTER TABLE - 变更（改变）数据库表
DROP TABLE - 删除表
CREATE INDEX - 创建索引（搜索键）
DROP INDEX - 删除索引

1、建立一张表格，并创建对应的表选项

CREATE TABLE TABLE_BUSINESS(选项1, 选项2, 选项3, 选项4，...);

#define TABLE_BUSINESS                 "TABLE_BUSINESS"
#define TABLE_BUSINESS_ID    "id"
//id和serialNum配合，可以在清理表的时候，起到删除但不重复的作用。
#define TABLE_BUSINESS_ID_TYPE          "int"
#define TABLE_BUSINESS_SERIALNUM    "SerialNum"
//ct100用不到，放空
#define TABLE_BUSINESS_SERIALNUM_TYPE   "int"
#define TABLE_BUSINESS_BUSINESSCODE     "Business_Code"
//同一笔交易，该号码相同
#define TABLE_BUSINESS_BUSINESSCODE_TYPE "int"
#define TABLE_BUSINESS_GOODSCODE    "GoodsCode"
//add goodscode,商品编码
#define TABLE_BUSINESS_GOODSCODE_LEN    8
#define TABLE_BUSINESS_GOODSCODE_TYPE   "varchar(8)"
#define TABLE_BUSINESS_BATCHCODE    "BatchCode"
//BatchCode,批次码
#define TABLE_BUSINESS_BATCHCODE_LEN    20
#define TABLE_BUSINESS_BATCHCODE_TYPE   "varchar(20)"
#define TABLE_BUSINESS_TRACECODE    "TraceCode"
//add tracecode，追溯码
#define TABLE_BUSINESS_TRACECODE_LEN    20
#define TABLE_BUSINESS_TRACECODE_TYPE   "varchar(20)"

const char* table_business[][2] =
{
{TABLE_BUSINESS_ID,TABLE_BUSINESS_ID_TYPE},
{TABLE_BUSINESS_SERIALNUM,TABLE_BUSINESS_SERIALNUM_TYPE),{TABLE_BUSINESS_BUSINESSCODE,TABLE_BUSINESS_BUSINESSCODE_TYPE},
{TABLE_BUSINESS_GOODSCODE,TABLE_BUSINESS_GOODSCODE_TYPE},
{TABLE_BUSINESS_BATCHCODE,TABLE_BUSINESS_BATCHCODE_TYPE},
{TABLE_BUSINESS_TRACECODE,TABLE_BUSINESS_TRACECODE_TYPE}
};

char createTable_Cmd[1024];
memset(createTable_Cmd,0,1024);
table_size = sizeof(table_business) / sizeof(const char *) / 2;
strcat(createTable_Cmd, "CREATE TABLE ");
strcat(createTable_Cmd, TABLE_BUSINESS);
strcat(createTable_Cmd, " (");
for(i = 0; i < table_size; i++)
{
    if(i>0 && i<table_size -1)
        strcat(createTable_Cmd, "\t");

    strcat(createTable_Cmd, table_business[i][0]);
    strcat(createTable_Cmd, "\t\t");
    strcat(createTable_Cmd, table_business[i][1]);
    if(i<table_size -1)
        strcat(createTable_Cmd, ",\n");
    else
        strcat(createTable_Cmd, ");\n");
}
printf("%s\n",createTable_Cmd);
sqlite3_exec(db, createTable_Cmd,0, 0, 0);

这样我们就建立了一个名叫TABLE_BUSINESS的资料表，里面有id、SerialNum、Business_Code、GoodsCode、BatchCode、TraceCode几个字段

2、根据条件查找数据库内容

SELECT 列名1,列名2 FROM 表名;
/*如：SELECT id,SerialNum FROM TABLE_BUSINESS;
*在表TABLE_BUSINESS中找列名为id，SerialNum 所有的数据
*/

SELECT * FROM TABLE_BUSINESS;
//查询表中所有数据

SELECT 列名 FROM 表名  where  条件;

如：SELECT id FROM TABLE_BUSINESS  where  GoodsCode = '1001';
//查找表TABLE_BUSINESS中GoodsCode=1001对应的id值

如：SELECT id FROM TABLE_BUSINESS  where  GoodsCode != '1001';
//查找表TABLE_BUSINESS中GoodsCode!=1001对应的id值

如：SELECT id FROM TABLE_BUSINESS  where  GoodsCode like '%1001%';
//查找表TABLE_BUSINESS中GoodsCode=1001前缀对应的id值

如：SELECT id FROM TABLE_BUSINESS  where  GoodsCode between '1001' and '1009';
//查找表TABLE_BUSINESS中GoodsCode 1001 -- 1009 对应的id值

如：SELECT id FROM TABLE_BUSINESS  where  GoodsCode between '1001' or '1009';
//查找表TABLE_BUSINESS中GoodsCode 1001 or 1009 对应的id值

//如果资料太多了，我们或许会想限制笔数：
select * from TABLE_BUSINESS  limit 10;

select * from TABLE_BUSINESS  order by id limit 10;
//依照id来排列

select id, SerialNum from TABLE_BUSINESS  order by id desc limit 10;

//有时候我们只想知道数据库一共有多少笔资料：
select count(*) from TABLE_BUSINESS  ;

有时候我们只想知道1985年以后的电影有几部：
select count(*) from film where year >= 1985;

//查所有演员名字以茱蒂开头、年份晚于1985年、年份晚的优先列出、最多十笔，只列出电影名称和年份：

select title, year from film where starring like 'Jodie%' and year >= 1985 order by year desc limit 10;

3、向表中插入一组数据
数据的形式最好定义成struct

typedef struct
{
    int id;
    int serialNum;
    int business_Code;
    char goodsCode[TABLE_BUSINESS_GOODSCODE_LEN+1];
    char BatchCode[TABLE_BUSINESS_BATCHCODE_LEN+1];//存储皮重
    char TraceCode[TABLE_BUSINESS_TRACECODE_LEN+1];

} struct_tb_business;

insert into TABLE_BUSINESS values(data1, data2, data3, ...);

//获取最后一条数据的id，这里没做，要通过数据库查询语句来做
                                                                                                                                    strcat(insert_Cmd,"INSERT INTO ");                                                                  strcat(insert_Cmd,TABLE_BUSINESS);
                                                                                                                                                                                                        strcat(insert_Cmd," VALUES ('");                                                                    char id_temp[10];                                                                   strcat(insert_Cmd,itoa(id+1,id_temp));                          strcat(insert_Cmd,"' , '");                                                                     
                                                                    memset(id_temp,0,10);                                                                       strcat(insert_Cmd,itoa(struct_tb_business.businessCode,id_temp));   strcat(insert_Cmd,"' , '");
                                                                                                                                        strcat(insert_Cmd,struct_tb_business.goodsCode);            strcat(insert_Cmd,"' , '");
                                                                        strcat(insert_Cmd,struct_tb_business.batchCode);            strcat(insert_Cmd,"' , '");
                                                                        strcat(insert_Cmd,struct_tb_business.traceCode);                                                                                     strcat(insert_Cmd,"' );");

printf("%s\n",insert_Cmd);
sqlite3_exec(db,insert_Cmd_utf8,0, 0, 0);

4、更新一条数据
方法与插入一条数据类似，插入数据是在原有数据基础上新增一条，更新数据时在原有基础上修改更新数据
数据的形式最好定义成struct，然后对应更新数据库数据

//要在sqlite更改或删除一笔资料
update film set starring='Jodie Foster' where starring='Jodee Foster';

5、数据的删除

delete from film where year < 1970;
//删除 film表中year < 1970的数据

DELETE FROM tablename
//删除表

DELETE FROM tablename  where  条件（与查找数据相似）
条件：=、！=、like、or、between、and、>

6、数据开始写入和写入结束

//在大量插入资料时，你可能会需要先打这个指令：begin;
sqlite3_exec(db, "BEGIN;", 0, 0, 0);

//插入完资料后要记得打这个指令，资料才会写进数据库中：commit;
sqlite3_exec(db, "COMMIT;", 0, 0, 0);