嵌入式数据库——sqlite

1.sql语法

sql语法比较简单，大致可以分为4类：

1.数据查询语言DQL
数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE
子句组成的查询块：
SELECT <字段名表>
FROM <表或视图名>
WHERE <查询条件>

2.数据操纵语言DML
数据操纵语言DML主要有三种形式：
1) 插入：INSERT
2) 更新：UPDATE
3) 删除：DELETE

3.数据定义语言DDL
数据定义语言DDL用来创建数据库中的各种对象—–表、视图、
索引、同义词、聚簇等如：
CREATE TABLE/VIEW/INDEX/SYN/CLUSTER
| | | | |
表 视图 索引 同义词 簇

DDL操作是隐性提交的！不能rollback

4.数据控制语言DCL
数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制
数据库操纵事务发生的时间及效果，对数据库实行监视等。如：

1) GRANT：授权。

2) ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一点。
回滚—ROLLBACK
回滚命令使数据库状态回到上次最后提交的状态。其格式为：SQL>ROLLBACK;

3) COMMIT [WORK]：提交。

在数据库的插入、删除和修改操作时，只有当事务在提交到数据
库时才算完成。在事务提交前，只有操作数据库的这个人才能有权看
到所做的事情，别人只有在最后提交完成后才可以看到。
提交数据有三种类型：显式提交、隐式提交及自动提交。下面分
别说明这三种类型。

(1) 显式提交
用COMMIT命令直接完成的提交为显式提交。其格式为：
SQL>COMMIT；

(2) 隐式提交
用SQL命令间接完成的提交为隐式提交。这些命令是：
ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，
EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。

(3) 自动提交
若把AUTOCOMMIT设置为ON，则在插入、修改、删除语句执行后，
系统将自动进行提交，这就是自动提交。其格式为：
SQL>SET AUTOCOMMIT ON；

举个例子：SQL由命令组成，每个命令以分号(;)结束。SELECT id, name FROM foods;INSERT INTO foods VALUES (NULL, 'Whataburger');

---

2.常量

常量，也称为Literals，表示确切的值，有3种：字符串常量、数据常量和二进制常量。
字符串常量如：’Jerry’ ‘Newman’ ‘JujyFruit’ ；
数据常量：数字常量有整数、十进制数和科学记数法表示的数，如：-1 3.142 6.0221415E23；
二进制值用如x’0000’的表示法，其中每个数据是一个16进制数。二进制值必须由两个两个的16进制数(8 bits)组成，如：x’01’ X’0fff’x’0F0EFF’；

---

保留字由SQL保留用做特殊的用途，如SELECT UPDATE、INSERT、CREATE、DROP和BEGIN等。标识符指明数据库里的具体对象，如表或索引。保留字预定义，不能用做标识符。SQL不区分大小写，下面是相同的语句：

SELECT * from foo;select * from foo;

---

3.数据库的操作

a.创建数据库

创建表
在SQL中，创建和删除数据库对象的语句一般被称为数据定义语言(data definition language，DDL)，操作这些对象中数据的语句称为数据操作语言(data manipulation language，DML)。创建表的语句属于DDL，用CREATE TABLE命令，如下定义：

CREATE [TEMP] TABLE table_name (column_definitions ,[constraints]); CREATE TABLE命令至少需要一个表名和一个字段名。命令中table_name表示表名，必须与其它所有的标识符不同。column_definitions表示一个用逗号分隔的字段列表。每个字段定义包括一个名称、一个域和一个逗号分隔的字段约束表。“域”一般情况下是一个类型，与编程语言中的数据类型同名，指明存储在该列的数据的类型。在SQLite中有5种本地类型：INTEGER、REAL、TEXT、BLOB和NULL一般比较常用的是interger，int型；text，char型；

举个例子

primary key，表示表中这一列中出现的内容不能重复出现；sprintf(sql, "create table if not exists student (name text, id integer, sex text);");中if not exists ，表示该表如果不存在就创建，如果存在就不执行创建；

---

b.改变表

你可以用ALTER TABLE命令改变表的结构。SQLite版的ALTER TABLE命令既可以改变表名，也可以增加字段。一般格式为：

ALTER TABLE table { RENAME TO name | ADD                               COLUMN column_def }改变表的结构是指增加表的列数，而不是行数；

c.查询表

SELECT是SQL命令中最大最复杂的命令。SELECT的很多操作都来源于关系代数。
SQL查询语句的基本结构
简单查询
多表连接查询
合并多个结果集
SELECT INTO
使用TOP限制结果集
子查询

SELECT <目标列名序列>FROM <数据源> [WHERE <检索条件表达式>] [GROUP BY <分组依据列>] [HAVING <分组提取条件>] [ORDER BY <排序依据列>] SELECT  <目标列名序列>（需要哪些列）FROM   <表名序列>     （从哪些表）WHERE <查询条件>  （根据什么条件） 

一些常用的查询条件

注意，1.%表示所有，类似于Linux命令行中的*；SELECT Sname FROM Student WHERE Sname NOT LIKE ‘刘%’；查询student中所有性"刘"的学生。2.空值是未确定的值或其值尚不知道例如，学生选课，在开学初学生只有选课记录，没有修课成绩，这时成绩一项的值就是空值。不能用=或<>，只能用IS NULL或IS NOT NULLSELECT Sno, Cno FROM SC WHERE Grade IS NULL ；查询无成绩的学生的学号和相应的课程号。3.多重条件查询 ，用逻辑运算符AND和OR组成多条件查询。SELECT Sname FROM Student WHERE Sdept=‘CS’ AND Sage<20 查询计算机系年龄在20岁以下的学生姓名 4.对查询结果进行排序的子句为：ORDER BY <列名> [<列名> … ] [ASC | DESC ]说明：按<列名>进行升序（ASC）或降序（DESC）排序。5.SQL提供的计算函数 COUNT(*)：统计表中元组个数 COUNT(<列名>)：统计本列非空列值个数 SUM(<列名>)：计算列值总和（必须是数值型列） AVG(<列名>)：计算列值平均值（必须是数值型列） MAX(<列名>)：求列值最大值 MIN(<列名>)：求列值最小值 作用：可以控制计算的级别（对全表还是对一组）目的：细化计算函数的作用对象分组语句的一般形式： 1. [WHERE <条件> ] 2. [GROUP BY <分组条件>]  3. [HAVING <组自身条件>]

统计每门课程的选课人数，列出课程号和人数SELECT Cno, COUNT(Sno) FROM SC  GROUP BY Cno 对查询结果按Cno的值分组，所有具有相同Cno值的元组为一组，然后再对每一组使用COUNT计算，求得每组的学生人数。 

5.查询选修了3门以上课程的学生学号和选课门数SELECT Sno, COUNT(*) FROM SC GROUP BY Sno HANVING COUNT(*) > 3先用GROUP BY对Sno进行分组，然后再用统计函数COUNT分别对每一组进行统计

---

d.用INSERT语句记录

语法格式如下：INSERT [ INTO] table_name  { [ ( column_list ) ] { VALUES ( {  expression } [ ,...n] ) }   }

语法注释：
[INTO]：一个可选的关键字，可以将它用在 INSERT 和目标表之间。
table_name：将要接收数据的表或 table 变量的名称。
(column_list)：要在其中插入数据的一列或多列的列表。必须用圆括号将column_list括起来，并且用逗号进行分隔。
VALUES：引入要插入的数据值的列表。对于column_list（如果已指定）中或者表中的每个列，都必须有一个数据值。必须用圆括号将值列表括起来。如果 VALUES 列表中的值与表中列的顺序不相同，或者未包含表中所有列的值，那么必须使用column_list明确地指定存储每个传入值的列。

[示范案例1] 在“Northwind”数据库的“Order Details”表里插入一条记录，其中“OrderID”字段的值为10248；“ProductID”字段的值为1；“UnitPrice”字段的值为10；“Quantity”字段的值为2；“Discount”字段的值为0.8 。程序清单如下：INSERT [order details] (orderid,productid,unitprice,quantity,discount)VALUES(10248,1,10,2,0.8)标识列、允许为空字段的处理[示范案例2] 在“Northwind”数据库的“Categories”表里插入一条记录，其中“CategoryName”字段的值为“图书”；“Description”字段的值为“所有类型的图书” 。程序清单如下：INSERT INTO categories(categoryname,description)VALUES('图书','所有类型的图书')有默认值字段的处理[示范案例3] “学生”数据库中创建“教师”表（性别为有默认值列） 。在该表中录入数据。 程序清单如下：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);

---

e.修改数据

修改数据    Update <表名>    Set <列名>=<表达式>[{, <列名>=<表达式>}] Set  列名 表达式 [{,  列名 表达式 }] [Where <条件>]举例：将学生95001的年龄改为22岁   Update Student Set Sage = 22 Where Sno=95001将所有学生的年龄增加1岁   Update Student Set Sage=Sage+1

---

f.Delete语句

删除数据
Delete From <表名>[Where <条件>]
„ 删除学号为95019的学生的记录
Delete From Student
Where Sno=95019
„ 删除所有学生的选课记录
Delete From SC

---

4.API

a.打开

函数原型:
int sqlite3_open(const char* fileName, sqlite3** ppDB);
函数功能：打开一个数据库；若该数据库文件不存在，则自动创建。打开或者创建数据库的命令会被缓存，直到这个数据库真正被调用的时候才会被执行。
输入参数：fileName，待打开的数据库文件名称，包括路径，以’\0’结尾； 特别说明：SQLite 支持内存数据库，内存方式存储使用文件名“:memory:”
输出参数：ppDB,返回打开的数据库句柄；
返回值：执行成功返回SQLITE_OK，否则返回其他值；

---

b.关闭

函数原型:
int sqlite3_close(sqlite3* pDB);
函数功能：关闭一个打开的数据库；
输入参数：pDB,打开的数据库句柄
输出参数： 无
返回值：执行成功返回SQLITE_OK，否则返回其他值

---

c.调试

函数原型:
const char sqlite3_errmsg(sqlite3 pDB);
函数功能：获取最近调用的API 接口返回的错误说明，这些错误信息UTF-8的编码返回，并且在下一次调用任何SQLite API 函数时被自动清除；
输入参数：pDB,打开的数据库句柄
输出参数： 无
返回值：错误说明的字符串指针

函数原型:
int sqlite3_errcode(sqlite3* pDB);
函数功能：获取最近调用的API 接口返回的错误代码；
输入参数：pDB,打开的数据库句柄
输出参数： 无
返回值：错误代码

---

d.执行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，否则返回其他值

函数原型：
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，否则返回其他值

---

e.释放

函数原型：
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，否则返回其他值