数据库(DataBase,简记为DB):就是一个有结构的、集成的、可共享的统一管理的数据集合。它不仅包括数据本身,而且包括相关数据之间的联系。数据库技术主要研究如何存储、使用和管理数据;
数据库管理系统 (DataBase Management System,DBMS)
数据管理技术的发展过程:
人工管理阶段(40年代中--50年代中)
文件系统阶段(50年代末--60年代中)
数据库系统阶段(60年代末--现在)
关系术语和定义:
关系(Relation):一个关系就是一张二维表,每一个关系有一个关系名,可以存储为一个文件。其定义可以描述为 的子集叫做域 , ,…, 上的关系,表示为 ,R表示关系的名称,n表示关系的目或度(Degree)。
元组:表中的行称为元组,一行就是一个元组,对应存储文件中的一个记录。
属性:关系中的一列称为一个属性。一个属性必须有唯一的属性名,一个关系可以有若干个属性值。
域(Domain):属性的取值范围,是一组具有相同数据类型的值的集合。
关系的候选键与主键:表中某一列(或若干列的最小组合)的值能惟一标识一个行,称该列或列组为候选键。如Students表的学号列,就是一个候选键。 对于一个表,可能有多个候选键 。 候选键取决于应用范围,如给定条件,没有重复的学生姓名,则姓名是学生基本情况表的一个候选键。
关系的候选键与主键:
如果一个表有多个候选键,数据库设计者通常选择其中一个候选键作为区分行的惟一性标识符,称为主键(primary key,PK)。如果一个表只有一个候选键,那么这个候选键就作为主键。例如,在学生基本情况表中,把学号作为该表的主键。 因为主键是候选键之一,而根据候选键的定义,候选键列上的各个值都惟一,因此主键列上的各个值也都惟一
外键(Foreign Key,FK):若一个表R1中的一个列或列组对应另一个表R2的主键,那么该列或列组称为表R1的外键。 外键可以由一个或多个列组成,可以有重复值
如图:
sqlite3中的存储数据类型:
空:这个值为空值
整数:值被标识为整数,依据值的大小可以依次被存储1,2,3,4,5,6,7,8.
实数:所有值都是浮动的数值,被存储为8字节的IEEE浮动标记序号.
文本:值为文本字符串,使用数据库编码存储(TUTF-8, UTF-16BE or UTF-16-LE).
BLOB: 值是BLOB数据,如何输入就如何存储,不改变格式.
插入操作:sqlite> insert into test (id,value) values(1,’zhang’);
sqlite> insert into test (id,value) values(2,’wang’);
sqlite> insert into test (value) values(‘liang’);
sqlite> insert into test (value) values(‘liang’);
查看数据: sqlite> select * from test;
退出: sqlite> .exit
部分指令代码:
jsetc@linux:~$ sqlite3 database.db
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
sqlite> .database
main: /home/jsetc/database.db
sqlite> .table
sqlite> .tables
sqlite> create table mytable(id integer primary key,name text)
...> ;
sqlite> .table
mytable
sqlite> insert into mytable (id,name) values (1,'chen');
sqlite> insert into mytable (id,name) values (2,'chen');
sqlite> insert into mytable (id,name) values (2,'zhang');
Error: UNIQUE constraint failed: mytable.id
sqlite> insert into mytable (id,name) values (3,'zhang');
sqlite> insert into mytable (id,name) values (4,'kuang');
sqlite> select * from mytable
...> ;
1|chen
2|chen
3|zhang
4|kuang
sqlite> .mode column
sqlite> select * from mytable
...> ;
1 chen
2 chen
3 zhang
4 kuang
sqlite> .head on
sqlite> select * from mytable;
id name
---------- ----------
1 chen
2 chen
3 zhang
4 kuang
sqlite> delete from mytable where id = 2;
sqlite> select * from mytable;
id name
---------- ----------
1 chen
3 zhang
4 kuang
sqlite> update mytable set name = 'wang' where name = 'zhang';
sqlite> select * from mytable
...> ;
id name
---------- ----------
1 chen
3 wang
4 kuang
sqlite>
sqlite> alter table mytable add column email text not null default ' ';
sqlite> select * from mytable ;
id name email
---------- ---------- ----------
1 chen
3 wang
4 kuang
//两个‘’相连不能有空格
sqlite> update mytable set email = '719571625@qq.com';
sqlite> select * from mytable ;
id name email
---------- ---------- ----------------
1 chen 719571625@qq.com
3 wang 719571625@qq.com
4 kuang 719571625@qq.com
sqlite> update mytable set email = '520@qq.com' where id = '4';
sqlite> select * from mytable ;
id name email
---------- ---------- ----------------
1 chen 719571625@qq.com
3 wang 719571625@qq.com
4 kuang 520@qq.com
sqlite> create table newtable as select id,name from mytable;
sqlite> .tables
mytable newtable
sqlite> select * from newtable ;
id name
---------- ----------
1 chen
3 wang
4 kuang
sqlite> select * from mytable ;
id name email
---------- ---------- ----------------
1 chen 719571625@qq.com
3 wang 719571625@qq.com
4 kuang 520@qq.com
sqlite> drop table mytable;
sqlite> .tables
newtable
sqlite> alter table newtable rename to mytable;
sqlite> .table
mytable
sqlite> select * from mytable
...> ;
id name
---------- ----------
1 chen
3 wang
4 kuang
SQL:功能:
数据定义(DDL):用户定义、删除和修改数据模式
数据查询(DQL):用于查询数据
数据操纵(DML):用于增、删、改数据
数据控制(DCL):用于控制数据访问权限
sql命令语句:
语法:SQL的语法很像自然语言。每个语句都是一个祈使句,以动词开头,表示所要做的动作。
注意:每一句SQL语句的结束都必须加上 ;
创建表:CREATE [TEMP] TABLE table_name (column_definitions [, constraints]);
改变表:ALTER TABLE table { RENAME TO name | ADD COLUMN column_def }
查询:select语句
如:SELECT <目标列名序列> FROM <数据源> [WHERE <检索条件表达式>] [GROUP BY <分组依据列>] [HAVING <分组提取条件>] [ORDER BY <排序依据列>]
基本结构:SELECT <目标列名序列>(需要哪些列) FROM <表名序列> (从哪些表) WHERE <查询条件> (根据什么条件)
如:SELECT Sname, Sage FROM Student WHERE NOT Sage >= 20
BETWEEN … AND … 包括临界值
NOT BETWEEN… AND … 不包括临界值
作用:查找属性值在或不在指定范围内的元组
查询年纪在20-23岁之间的:SELECT Sname, Sdept, Sage FROM Student WHERE Sage BETWEEN 20 AND 23
等价于: SELECT Sname, Sdept, Sage FROM Student WHERE Sage >=20 AND Sage<=23
查询字符匹配:字符匹配 (LIKE)
一般形式为: 列名 [NOT ] LIKE 匹配串
作用:查找指定列名与匹配串常量匹配的元组。
匹配串类型:匹配串可以是字符串常量,也可以含有通配符。
通配符种类: %(百分号):匹配0个或多个字符。 _(下划线):匹配一个字符。
[ ]:匹配括号中的字符 [∧ ]:不匹配括号中的字符
如:查询名字中第2个字为“海”字的学生的姓名和学号
SELECT Sname, Sno FROM Student WHERE Sname LIKE ‘_海%’
如:查询所有不姓“刘”的学生
SELECT Sname FROM Student WHERE Sname NOT LIKE ‘刘%’
如:查询以a,b,c开始的产品名称:
SELECT productName FROM products WHERE productName LIKE '[a-c]%'
涉及空值的查询:不能用=或<>,只能用IS NULL或IS NOT NULL
SELECT Sno, Cno FROM SC WHERE Grade IS NULL
注意:空值不是一个确定的值,所以不可以用等于或不等于来比较或衡量; 空值只能说是空值(IS NULL)或不是空值(IS NOT NULL)。
多重条件查询 :用逻辑运算符AND和OR组成多条件查询
ELECT Sname FROM Student WHERE Sdept=‘CS’ AND Sage<20
对查询结果进行排序的子句为:
ORDER BY <列名> [<列名> … ] [ASC | DESC ] 说明:按<列名>进行升序(ASC)或降序(DESC)排序
SQL提供的计算函数:
COUNT(*):统计表中元组个数
COUNT(<列名>):统计本列非空列值个数
SUM(<列名>):计算列值总和(必须是数值型列)
AVG(<列名>):计算列值平均值(必须是数值型列)
MAX(<列名>):求列值最大值
MIN(<列名>):求列值最小值
对查询结果分组:
作用:可以控制计算的级别(对全表还是对一组)
目的:细化计算函数的作用对象 分组语句的一般形式:
[WHERE <条件> ]
[GROUP BY <分组条件>]
[HAVING <组自身条件>]
GROUP BY:统计每门课程的选课人数,列出课程号和人数
SELECT Cno, COUNT(Sno) FROM SC GROUP BY Cno
查询选修了3门以上课程的学生学号和选课门数
SELECT Sno, COUNT(*) FROM SC GROUP BY Sno HANVING COUNT(*) > 3
insert语句:
INSERT [ INTO] table_name { [ ( column_list ) ] { VALUES ( { expression } [ ,...n] ) } }
实例:
INSERT INTO teachers(teach_id,teach_name,tsex,tdegree) VALUES('rj001', '李波',DEFAULT, '本科')
insert Into Student Values (95020, '陈冬', '男','IS', 18); insert Into SC (Sno, Cno,grade) Values(95020, 1,100);
修改数据
Update <表名> Set <列名>=<表达式>[{, <列名>=<表达式>}] Set 列名 表达式 [{, 列名 表达式 }] [Where <条件>]
将学生95001的年龄改为22岁 Update Student Set Sage = 22 Where Sno=95001
将所有学生的年龄增加1岁 Update Student Set Sage=Sage+1
Delete语句:
删除数据 Delete From <表名>[Where <条件>]
删除学号为95019的学生的记录 Delete From Student Where Sno=95019
删除所有学生的选课记录 Delete From SC
C API:
打开:
函数原型: int sqlite3_open(const char* fileName, sqlite3** ppDB);
函数功能:打开一个数据库;若该数据库文件不存在,则自动创建。打开或者创建数据库的命令会被缓存,直到这个数据库真正被调用的时候才会被执行。
输入参数:fileName,待打开的数据库文件名称,包括路径,以’\0’结尾;
特别说明:SQLite 支持内存数据库,内存方式存储使用文件名“:memory:”
输出参数:ppDB,返回打开的数据库句柄;
返回值:执行成功返回SQLITE_OK,否则返回其他值;
关闭:
函数原型: int sqlite3_close(sqlite3* pDB);
函数功能:关闭一个打开的数据库;
输入参数:pDB,打开的数据库句柄
输出参数: 无
返回值:执行成功返回SQLITE_OK,否则返回其他值
调试:
函数原型: const char *sqlite3_errmsg(sqlite3* pDB);
函数功能:获取最近调用的API 接口返回的错误说明,这些错误信息UTF-8的编码返回,并且在下一次调用任何SQLite API 函数时被自动清除;
输入参数:pDB,打开的数据库句柄
输出参数: 无
返回值:错误说明的字符串指针
调试:
函数原型: int sqlite3_errcode(sqlite3* pDB);
函数功能:获取最近调用的API 接口返回的错误代码;
输入参数:pDB,打开的数据库句柄
输出参数: 无
返回值:错误代码
执行sql:
函数原型: int sqlite3_exec(sqlite3* pDB, const char *sql, sqlite_callback callback, void*para, char** errMsg);
函数功能:编译和执行零个或多个SQL 语句,查询的结果返回给回调函数callback
输入参数:pDB,数据库句柄;sql,待执行的SQL 语句字符串,以’\0’结尾;callback,回调函数,用来处理查询结果,如果不需要回调(比如做insert 或者delete 操作时),可以输入NULL;para,用户传入的参数,可以为NULL,该参数指针最终会被传给回调函数callback,供用户在回调函数中使用;
输出参数:errMsg,返回错误信息,注意是指针的指针。
返回值:执行成功返回SQLITE_OK,否则返回其他值
回调函数sqlite_callback介绍
typedef int (*sqlite_callback)(void* para,int columnCount,char** columnValue,char** columnName);
函数功能:由用户处理查询的结果
输入参数:para,从sqlite3_exec()传入的参数指针;columnCount, 查询到的这一条记录有多少个字段(即这条记录有多少列);columnValue,查询出来的数据都保存在这里,它实际上是个1 维数组(不要以为是2 维数组),每一个元素都是一个char * 值,是一个字段内容(用字符串来表示,以‘\0’结尾);columnName,与columnValue 是对应的,表示这个字段的字段名称。
输出参数:无
返回值:执行成功返回SQLITE_OK,否则返回其他值
执行sql:
函数原型: int sqlite3_get_table(sqlite3* pDB, const char *sql,char ***pResult, int * rowCount,int * columnCount, char** errMsg); 函数功能:执行SQL 语句,通过一维数组返回结果;一般用于数据记录查询
输入参数:pDB,数据库句柄;sql,待执行的SQL 语句字符串,以’\0’结尾;callback,回调函数,用来处理查询结果,如果不需要回调(比如做insert 或者delete 操作时),可以输入NULL;para,用户传入的参数,可以为NULL,该参数指针最终会被传给回调函数callback,供用户在回调函数中使用; 输出参数:errMsg,返回错误信息,注意是指针的指针。 返回值:执行成功返回SQLITE_OK,否则返回其他值
回调函数:
sqlite_callback介绍 typedef int (*sqlite_callback)(void* para,int columnCount,char** columnValue,char** columnName);
函数功能:由用户处理查询的结果
输入参数:para,从sqlite3_exec()传入的参数指针;columnCount, 查询到的这一条记录有多少个字段(即这条记录有多少列);columnValue,查询出来的数据都保存在这里,它实际上是个1 维数组(不要以为是2 维数组),每一个元素都是一个char * 值,是一个字段内容(用字符串来表示,以‘\0’结尾);columnName,与columnValue 是对应的,表示这个字段的字段名称。
输出参数:无
返回值:执行成功返回SQLITE_OK,否则返回其他值
执行sql:
函数原型: int sqlite3_get_table(sqlite3* pDB, const char *sql,char ***pResult, int * rowCount,int * columnCount, char** errMsg); 函数功能:执行SQL 语句,通过一维数组返回结果;一般用于数据记录查询
输入参数:pDB,打开的数据库句柄;sql,待执行的SQL 字符串,以’\0’结尾; 输出参数:rowCount,查询出多少条记录(即查出多少行);columnCount,查询出来的记录有多少个字段(多少列); errMsg,返回错误信息;pResult,查询结果,是由字符串组成的一维数组(不要以为是二维数组,更不要以为是三维数组)。它的内存布局是:第一行是字段名称,后面紧接着每个字段的值; 返回值:执行成功返回SQLITE_OK,否则返回其他值
释放:当使用sqlite3_get_table();时必须调用此释放!
函数原型: void sqlite3_free_table(char **result);
函数功能:释放查询结果占用的内存;
输入参数:result,通过函数sqlite3_get_table()查询到的记录结果
输出参数:无
返回值:无
函数原型: int sqlite3_prepare(sqlite3* pDB, const char* sql , int maxSqlLen,sqlite3_stmt** ppStmt, const char**pTail); 函数功能:将SQL 语句编译成SQLite 能够识别的二进制字节码(也即SQL声明,statement),结果保存在ppStat 中。注:输入的参数中只有第一个SQL语句会被编译。SQL 语句间通过分号‘;’分隔。
输入参数:pDB ,数据库句柄;sql ,待编译的SQL 语句字符串; maxSqlLen,SQL 语句的最大长度,如果此值小于0,SQLite 会自动计算字符串长度; 输出参数:ppStmt,编译后SQL 语句存放位置;pTail,未编译的SQL 语句的起始位置,一般输入NULL 就可以了。 返回值:执行成功返回SQLITE_OK,否则返回其他值.
今日代码展示:
/*****************************************************
copyright (C), 2014-2015, Lighting Studio. Co., Ltd.
File name锛?Author锛欽erey_Jobs Version:0.1 Date:
Description锛?Funcion List:
*****************************************************/
#include <stdio.h>
#include <sqlite3.h>
#include <stdlib.h>
int create_table(sqlite3 * pdb)
{
char *sql = NULL;
char *errmsg = NULL;
int ret;
sql = "create table if not exists mytable (id integer primary key,name text);";
ret = sqlite3_exec(pdb,sql,NULL,NULL,&errmsg);
if(SQLITE_OK != ret)
{
printf("create table error! %s\n",errmsg);
return -1;
}
else
{
return SQLITE_OK;
}
}
int insert_record(sqlite3 * pdb)
{
//char *sql = NULL;
char sql[100];
char *errmsg = NULL;
int ret;
int id;
char name[20];
printf("please input in and name :\n");
scanf("%d",&id);
scanf("%s",name);
//sql = "insert into mytable (id,name) values (NULL,'chen');";
sprintf(sql,"insert into mytable (id,name) values (%d,'%s');",id,name);
ret = sqlite3_exec(pdb,sql,NULL,NULL,&errmsg);
if(SQLITE_OK != ret)
{
printf("insert record 1 error! %s\n",errmsg);
return -1;
}
#if 0
sql = "insert into mytable (id,name) values (NULL,'zhang');";
ret = sqlite3_exec(pdb,sql,NULL,NULL,&errmsg);
if(SQLITE_OK != ret)
{
printf("insert record 2 error! %s\n",errmsg);
return -1;
}
sql = "insert into mytable (id,name) values (NULL,'liu');";
ret = sqlite3_exec(pdb,sql,NULL,NULL,&errmsg);
if(SQLITE_OK != ret)
{
printf("insert record 3 error! %s\n",errmsg);
return -1;
}
#endif
return SQLITE_OK;
}
int display(void * para,int ncol,char * col_val[],char ** col_name)
{
int i;
int * flag = NULL;
flag = (int *)para;
if(0 == *flag)
{
*flag = 1;
printf("column number is : %d\n",ncol);
for(i = 0; i < ncol;i++)
{
printf("%10s",col_name[i]);
}
printf("\n");
}
for(i = 0;i < ncol;i++)
{
printf("%10s",col_val[i]);
}
printf("\n");
return 0;
}
int inquire_uscb(sqlite3 * pdb)
{
char *sql = NULL;
char *errmsg = NULL;
int ret;
int flag = 0;
sql = "select * from mytable;";
ret = sqlite3_exec(pdb,sql,display,(void *) &flag,&errmsg);
if(SQLITE_OK != ret)
{
printf("select error! %s\n",errmsg);
return -1;
}
else
{
return SQLITE_OK;
}
}
int delete_record(sqlite3 * pdb)
{
char sql[100];
char *errmsg = NULL;
int ret;
int id;
printf("please input delete id:\n");
scanf("%d",&id);
sprintf(sql, "delete from mytable where id = %d;",id);
ret = sqlite3_exec(pdb,sql,NULL,NULL,&errmsg);
if(SQLITE_OK != ret)
{
printf("delete error! %s\n",errmsg);
return -1;
}
else
{
return SQLITE_OK;
}
}
int inquire_nocb(sqlite3 *pdb)
{
char *sql = NULL;
char ** ret_val = NULL;
char * errmsg = NULL;
int nrow;
int ncol;
int ret;
int i;
sql = "select * from mytable;";
ret = sqlite3_get_table(pdb,sql,&ret_val,&nrow,&ncol,&errmsg);
if(SQLITE_OK == ret)
{
printf("nrow = %d ncol = %d\n",nrow,ncol);
for(i = 0; i < (nrow + 1) * ncol;i++)
{
printf("%10s",ret_val[i]);
if((i + 1) % ncol == 0)
{
printf("\n");
}
}
}
else
{
sqlite3_free_table(ret_val);
return -1;
}
sqlite3_free_table(ret_val);
}
int main()
{
sqlite3 *pdb;
int ret;
ret = sqlite3_open("mydatabase.db",&pdb);
if(SQLITE_OK != ret)
{
printf("open database fail! %s\n",sqlite3_errmsg(pdb));
exit(EXIT_FAILURE);
}
else
{
printf("open database successfully!\n");
}
if(SQLITE_OK == create_table(pdb))
{
printf("create table success!\n");
}
else
{
sqlite3_close(pdb);
return 0;
}
if(insert_record(pdb) != 0)
{
sqlite3_close(pdb);
exit(-1);
}
inquire_uscb(pdb);
delete_record(pdb);
inquire_nocb(pdb);
sqlite3_close(pdb);
return 0;
}
sqlite3_exec的使用详情可见此博主:(10条消息) sqlite_exec回调函数的使用_zjq_smile的博客-CSDN博客_sqlite3_exec