一、创建一张表
1.创建表名student主键为id的表:
CREATE TABLE student(id integer PRIMARY KEY,name text,age integer,score real)
2.如果不存在则创建:
CREATE TABLE IF NOT EXISTS student(id integer PRIMARY KEY,name text,age integer,score real)
3.主键自增长:
CREATE TABLE IF NOT EXISTS student(id integer PRIMART KEY AUTOINCREMENT,name text,age integer,score real)
二、插入数据:
1.添加一条数据:
INSERT INTO student(name,age,score)VALUES('James',10,10)
2.更新score数据:
UPDATE student SET score=99
三、删除数据:
1.从表内某处删除数据:
DELETE FROM student WHERE id=2
2.删除表内全部数据:
DELETE FROM student
3.删除整张表:
DELETE TABLE student
四、查找数据:
1.查找:
SELECT * FROM student WHERE age>21
2.查找总数:
SELECT count(name) FROM student WHERE age>21
3.排序:
SELECT * FROM student WHERE score>70 ORDER BY age DESC
五、简单约束
主键 -- PRIMARY KEY
自动增长 -- AUTOINCREMENT
不会空 -- NOT NULL
默认 -- DEFAULT 1
CREATE TABLE IF NOT EXISTS t_student(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER)
CREATE TABLE IF NOT EXISTS t_student(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, age INTEGER NOT NULL)
CREATE TABLE IF NOT EXISTS t_student(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT UNIQUE, age INTEGER)
CREATE TABLE IF NOT EXISTS t_student(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER DEFAULT 1)
六、Sqlite3常用API:
最重要的三个函数:
int sqlite3_open(const char*, sqlite3**);
int sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void*, char**);
int sqlite3_close(sqlite3*);
还有2个:
const char *sqlite3_errmsg(sqlite3*);
void sqlite3_free(void*);
1.sqlite3_open函数用于打开/创建一个函数库,返回一个整数错误代码,0 表示成功码,大于0都是错误码,详情看Sqlite3手册说明
const char* 指定文件名,sqlite3** 指定数据库句柄,用户通过数据库句柄操作数据库。
2.sqlite3_exec函数用于执行一条或多条SQL语句,SQL语句之间用";"隔开,返回0表示sql指令执行完毕,否则说明这次执行没有成功。
sqlite3* 指定已打开的数据库句柄,const char *sql指定SQL指令,sqlite_callback在回调函数中可以获得SQL执行的结果。
void* 指定传给回调函数的数据, char** 指定命令执行失败的详细错误信息。回调函数格式如下:
int callback(void *pv, /* 由sqlite3_exec()的第四个参数传递而来 */
int argc, /* 表的列数 */
char **argv, /* 指向查询结果的指针数组,可以由sqlite3_column_text()函数得到 */
char **col) /* 指向表头名的指针数组,可以由sqlite3_column_name()函数得到 */
3. sqlite3_close函数关闭数据库文件,参数是数据库句柄
4.sqlite3_errmsg函数返回错误码所对应的文字说明,参数是数据库句柄
5.sqlite3_free函数释放存放错误信息的内存空间,sqlite3_errmsg返回的errmsg必须用此函数释放
code:
#include <stdio.h>
#include <sqlite3.h>
int callback(void *pv,int argc,char **argv,char **col)
{
int cnt_i = 0;
for(cnt_i =0;cnt_i < argc;cnt_i++)
{
printf("%s\t%s\n",col[cnt_i],argv[cnt_i]);
}
printf("\n");
return 0;
}
int main(void)
{
sqlite3 *db;
int result = 0;
char *rerrmsg = NULL;
char *sql = NULL;
char *data = "callback";
result = sqlite3_open("sample.db",&db);
if(result > 0)
{
printf("open database err:%s\n",sqlite3_errmsg(db));
return -1;
}
else
{
printf("open database successfully!\n");
sql = "CREATE TABLE STUDENT(" \
"NUM INT PRIMARY KEY NOT NULL," \
"NAME TEXT NOT NULL," \
"AGE INT NOT NULL," \
"SORCE REAL);";
result = sqlite3_exec(db,sql,callback,NULL,&rerrmsg);
if(result != 0)
{
printf("creat table err:%s\n",rerrmsg);
sqlite3_free(rerrmsg);
return -2;
}
else
{
printf("create table successfully!\n");
sql = "INSERT INTO STUDENT(NUM,NAME,AGE,SORCE)" \
"VALUES(1,'Paul',13,99.1);" \
"INSERT INTO STUDENT(NUM,NAME,AGE,SORCE)" \
"VALUES(2,'Kate',15,94.1);" \
"INSERT INTO STUDENT(NUM,NAME,AGE,SORCE)" \
"VALUES(3,'Jim',12,95.1);" \
"INSERT INTO STUDENT(NUM,NAME,AGE,SORCE)" \
"VALUES(4,'Tom',13,99.4);" \
"INSERT INTO STUDENT(NUM,NAME,AGE,SORCE)" \
"VALUES(5,'Jack',13,89.1);";
result = sqlite3_exec(db,sql,callback,NULL,&rerrmsg);
if(result != 0)
{
printf("insert data err:%s\n",rerrmsg);
sqlite3_free(rerrmsg);
return -3;
}
else
{
printf("insert data successfully!\n");
sql = "SELECT * FROM STUDENT";
result = sqlite3_exec(db,sql,callback,(void *)data,&rerrmsg);
if(result != 0)
{
printf("select data err:%s\n",rerrmsg);
sqlite3_free(rerrmsg);
return -4;
}
else
{
printf("select data successfully!\n");
}
}
}
}
sqlite3_close(db);
return 0;
}
1.创建表名student主键为id的表:
CREATE TABLE student(id integer PRIMARY KEY,name text,age integer,score real)
2.如果不存在则创建:
CREATE TABLE IF NOT EXISTS student(id integer PRIMARY KEY,name text,age integer,score real)
3.主键自增长:
CREATE TABLE IF NOT EXISTS student(id integer PRIMART KEY AUTOINCREMENT,name text,age integer,score real)
二、插入数据:
1.添加一条数据:
INSERT INTO student(name,age,score)VALUES('James',10,10)
2.更新score数据:
UPDATE student SET score=99
三、删除数据:
1.从表内某处删除数据:
DELETE FROM student WHERE id=2
2.删除表内全部数据:
DELETE FROM student
3.删除整张表:
DELETE TABLE student
四、查找数据:
1.查找:
SELECT * FROM student WHERE age>21
2.查找总数:
SELECT count(name) FROM student WHERE age>21
3.排序:
SELECT * FROM student WHERE score>70 ORDER BY age DESC
五、简单约束
主键 -- PRIMARY KEY
自动增长 -- AUTOINCREMENT
不会空 -- NOT NULL
默认 -- DEFAULT 1
CREATE TABLE IF NOT EXISTS t_student(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER)
CREATE TABLE IF NOT EXISTS t_student(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, age INTEGER NOT NULL)
CREATE TABLE IF NOT EXISTS t_student(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT UNIQUE, age INTEGER)
CREATE TABLE IF NOT EXISTS t_student(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER DEFAULT 1)
六、Sqlite3常用API:
最重要的三个函数:
int sqlite3_open(const char*, sqlite3**);
int sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void*, char**);
int sqlite3_close(sqlite3*);
还有2个:
const char *sqlite3_errmsg(sqlite3*);
void sqlite3_free(void*);
1.sqlite3_open函数用于打开/创建一个函数库,返回一个整数错误代码,0 表示成功码,大于0都是错误码,详情看Sqlite3手册说明
const char* 指定文件名,sqlite3** 指定数据库句柄,用户通过数据库句柄操作数据库。
2.sqlite3_exec函数用于执行一条或多条SQL语句,SQL语句之间用";"隔开,返回0表示sql指令执行完毕,否则说明这次执行没有成功。
sqlite3* 指定已打开的数据库句柄,const char *sql指定SQL指令,sqlite_callback在回调函数中可以获得SQL执行的结果。
void* 指定传给回调函数的数据, char** 指定命令执行失败的详细错误信息。回调函数格式如下:
int callback(void *pv, /* 由sqlite3_exec()的第四个参数传递而来 */
int argc, /* 表的列数 */
char **argv, /* 指向查询结果的指针数组,可以由sqlite3_column_text()函数得到 */
char **col) /* 指向表头名的指针数组,可以由sqlite3_column_name()函数得到 */
3. sqlite3_close函数关闭数据库文件,参数是数据库句柄
4.sqlite3_errmsg函数返回错误码所对应的文字说明,参数是数据库句柄
5.sqlite3_free函数释放存放错误信息的内存空间,sqlite3_errmsg返回的errmsg必须用此函数释放
code:
#include <stdio.h>
#include <sqlite3.h>
int callback(void *pv,int argc,char **argv,char **col)
{
int cnt_i = 0;
for(cnt_i =0;cnt_i < argc;cnt_i++)
{
printf("%s\t%s\n",col[cnt_i],argv[cnt_i]);
}
printf("\n");
return 0;
}
int main(void)
{
sqlite3 *db;
int result = 0;
char *rerrmsg = NULL;
char *sql = NULL;
char *data = "callback";
result = sqlite3_open("sample.db",&db);
if(result > 0)
{
printf("open database err:%s\n",sqlite3_errmsg(db));
return -1;
}
else
{
printf("open database successfully!\n");
sql = "CREATE TABLE STUDENT(" \
"NUM INT PRIMARY KEY NOT NULL," \
"NAME TEXT NOT NULL," \
"AGE INT NOT NULL," \
"SORCE REAL);";
result = sqlite3_exec(db,sql,callback,NULL,&rerrmsg);
if(result != 0)
{
printf("creat table err:%s\n",rerrmsg);
sqlite3_free(rerrmsg);
return -2;
}
else
{
printf("create table successfully!\n");
sql = "INSERT INTO STUDENT(NUM,NAME,AGE,SORCE)" \
"VALUES(1,'Paul',13,99.1);" \
"INSERT INTO STUDENT(NUM,NAME,AGE,SORCE)" \
"VALUES(2,'Kate',15,94.1);" \
"INSERT INTO STUDENT(NUM,NAME,AGE,SORCE)" \
"VALUES(3,'Jim',12,95.1);" \
"INSERT INTO STUDENT(NUM,NAME,AGE,SORCE)" \
"VALUES(4,'Tom',13,99.4);" \
"INSERT INTO STUDENT(NUM,NAME,AGE,SORCE)" \
"VALUES(5,'Jack',13,89.1);";
result = sqlite3_exec(db,sql,callback,NULL,&rerrmsg);
if(result != 0)
{
printf("insert data err:%s\n",rerrmsg);
sqlite3_free(rerrmsg);
return -3;
}
else
{
printf("insert data successfully!\n");
sql = "SELECT * FROM STUDENT";
result = sqlite3_exec(db,sql,callback,(void *)data,&rerrmsg);
if(result != 0)
{
printf("select data err:%s\n",rerrmsg);
sqlite3_free(rerrmsg);
return -4;
}
else
{
printf("select data successfully!\n");
}
}
}
}
sqlite3_close(db);
return 0;
}