嵌入式数据库sqlite3「基础篇」基本命令操作小白一看就懂

数据在实际工作中应用非常广泛，数据库的产品也比较多，oracle、DB2、SQL2000、mySQL；基于嵌入式linux的数据库主要有SQLite,Firebird,BerkeleyDB,eXtremeDB。

本文主要讲解数据库SQLite，通过这个开源的小型的嵌入式数据库带领大家掌握一些基本的数据库操作，这些操作在很多系统中都是通用的，可谓学一通百。

SQLite，是一款轻型的数据库，是遵守ACID的关系型数据库管理系统，它包含在一个相对小的C库中。

2000年1月，Hipp开始和一个同事讨论关于创建一个简单的嵌入式SQL数据库的想法，这个数据库将使用GNUDBM哈希库（gdbm）做后台，同时这个数据库将不需要安装和管理支持。后来，一有空闲时间，Hipp就开始实施这项工作，2000年8月，版发布了。【在此向大神献上我的膝盖！】

它的设计目标是嵌入式的，而且已经在很多嵌入式产品中使用了它，它占用资源非常的低，在嵌入式设备中，可能只需要几百K的内存就够了。它能够支持Windows/Linux/Unix等等主流的操作系统，同时能够跟很多程序语言相结合，比如Tcl、C#、PHP、Java等，还有ODBC接口，同样比起Mysql、PostgreSQL这两款开源的世界著名数据库管理系统来讲，它的处理速度比他们都快。

零配置一无需安装和管理配置；

储存在单一磁盘文件中的一个完整的数据库；

数据库文件可以在不同字节顺序的机器间自由共享；

支持数据库大小至2TB；

足够小，全部源码大致3万行c代码，250KB；

比目前流行的大多数数据库对数据的操作要快。

现在运行的主要是sqlite3版本，在ubuntu下安装也非常方便。

sudoapt-getinstallsqlitesqlite3安装应用程序sudoapt-getinstalllibsqlite3-dev安装库+头文件，用代码操作数据库必须安装

查看版本号：输入命令sqlite3进入操作界面，输入命令.version,即可查看详细版本号。

也可以使用下面命令，安装图形化操作工具：

sudoapt-getinstallsqlitebrowser图形化工具建立数据库

除此之外，还可以部署在windows下，本文只讨论ubtuntu下，采用命令行方式来操作数据库。

操作数据库，主要就是操作表，表的每一列内容都有一定数据类型，比如整型值、字符串、布尔型等。

Sqlite3主要数据类型如下：

数据类型定义数据类型NULL表示该值为NULL值。INTEGER无符号整型值。REAL浮点值。TEXT文本字符串，存储使用的编码方式为UTF-8、UTF-16BE、UTF-16LE。BLOB存储Blob数据，该类型数据和输入数据完全相同，1表示true，0表示false。

同时sqlite3也接受如下的数据类型：

数据类型定义smallint16位的整数。interger32位的整数。decimal(p,s)精确值p是指全部有几个十进制数,s是指小数点后可以有几位小数。如果没有特别指定，则系统会默认为p=5s=0。float32位元的实数。double64位元的实数。char(n)n长度的字串，n不能超过254。varchar(n)长度不固定且其最大长度为n的字串，n不能超过4000。graphic(n)和char(n)一样，不过其单位是两个字节，n不能超过127。这个形态是为了支持两个字节长度的字体，如中文字。vargraphic(n)可变长度且其最大长度为n的双字元字串，n不能超过2000date包含了年份、月份、日期。time包含了小时、分钟、秒。timestamp包含了年、月、日、时、分、秒、千分之一秒。

表的每一列都有一些限制属性，比如有的列的数据不能重复，有的则限制数据范围等，约束就是用来进一步描述每一列数据属性的。SQLite数据库常用约束如下：

名称定义NOTNULL-非空UNIQUE唯一PRIMARYKEY主键FOREIGNKEY外键CHECK条件检查DEFAULT默认

有一些字段我们可能一时不知到该填些什么，同时它也没设定默认值，当添加数据时，我们把这样的字段空着不填，系统认为他是NULL值。但是还有另外一类字段，必须被填上数据，如果不填，系统就会报错。这样的字段被称为NOTNULL非空字段,需要在定义表的时候事先声明。

除了主列以为，还有一些列也不能有重复值。

一般是整数或者字符串，只要保证唯一就行。在SQLite中，主键如果是整数类型，该列的值可以自动增长。

我们的数据库中已经有Teachers表了，假如我们再建立一个Students表，要求Students表中的每一个学生都对应一个Teachers表中的教师。很简单，只需要在Students表中建立一个TeacherId字段，保存对应教师的Id号，这样，学生和教师之间就建立了关系。问题是：我们有可能给学生存入一个不在Teachers表中的TeacherId值，而且发现不了这个错误。这种情况下，可以把Students表中TeacherId字段声明为一个外键，让它的值对应到Teachers表中的Id字段上。这样，一旦在Students表中存入一个不存在的教师Id，系统就会报错。

有一些特别的字段列，在每一条记录中，他的值基本上都是一样的。只是在个别情况下才改为别的值，这样的字段列我们可以给他设一个默认值。

某些值必须符合一定的条件才允许存入，这是就需要用到这个CHECK约束。

下面介绍Shell模式中的各常用命令的使用。

命令功能.help可显示shell模式中可使用的所有命令列表.database显示数据库信息；包含当前数据库的位置.modecolumn使得SQL语句处理的结果以列对齐的方式显示./off打开关闭列标题显示开关，以使得查询结果在屏幕显示时具有列标题.tables列出当前打开的数据库中共有多少张表.exit退出SQLite环境.schemafoods显示表foods创建时的SQL语句.schema显示所有表被创建时的语句.nullvalueSTRING查询时用指定的串代替输出的NULL串默认为.nullvalue''.show显示shell模式中定义的与输出相关的一些设置.设置输出文件格式为CSV，文件名为,设置select语句输出的列数据间以“，”分隔.outputstdout恢复输出内容到标准输出设备(屏幕)

【注意】sqlite的命令都是.开头的，操作语句前面是没有.的。

数据库的操作语句主要是增删改查，下面我们通过一些实例让大家了解数据库的这些基本操作。

假设我们要创建一个教学管理的数据库，数据库中要保存学生表STUDENT。

snosnamessexsagesdept95001yikoum21cs95002pengm21cs

根据我们的常识，可知每一列的数据有以下特性：

sno学号：整型值，每个人学号是唯一的，学校一般用学号来区分所有的学生，而且一般学号是递增的，所以我们设置sno为primarykey；

sname姓名：一般是字符串，可以重复，但是不能为空；

ssex性别：字符串，可以为空；

sage年龄：整型值，假定年龄要大于14；

sdept专业：字符串，可以为空,此处我们默认为'CS'。

下面我们一步步实现这个数据库的所有操作。

数据库的打开和退出，使用如下命令。

CREATETABLEIFNOTEXISTSSTUDENT(Snointegerprimarykey,Snametextnotnull,Ssextext,Sageintegercheck(Sage14),Sdepttextdefault'CS');

该表的属性就是按照上一节表属性执行结果：

看到STUDENT，说明该表创建好了。【注意】

操作语句不是命令，前面不要加.；

操作语句后面一定要；结尾，如果漏掉了，一定要补上分号；

操作语句对字母的全角半角很敏感，所有的符号都要用半角。

插入数据采用insertinto语句来实现，如下所示：

INSERTINTOSTUDENTVALUES('95001','李勇','M',20,'CS');INSERTINTOSTUDENTVALUES('95002','刘晨','F',19,'IS');INSERTINTOSTUDENTVALUES('95003','王敏','F',18,'MA');INSERTINTOSTUDENTVALUES('95004','张立','M',18,'IS');

执行结果如下:

在这里插入图片描述

设置了notnull那一列必须要赋值，而且表名字不区分大小写。

insertintostudent(sname,sage)values('一口',19);

在这里插入图片描述

用SELECT语句查看表中的内容：

SELECT*FROMSTUDENT;

其中的*表示查看所有的数据信息。

有没有看到，结果看起来不舒服啊，下面我们来调整下显示格式：

显示列名列对齐

在这里插入图片描述

deletefromstudentwheresname='一口';

由上图可见，名字为“一口”的那条记录被删除了。

UPDATEstudentSETsage=29WHEREsname='张立';

在这里插入图片描述

修改表结构，需要用到语句ALTERTABLE。下面我们向STUDENT表增加“spwd”列，其数据类型为TEXT型，用SELECT命令查看表中内容。

ALTERTABLESTUDENTADDspwdTEXTdefault'123456';

在这里插入图片描述

altertablestudentrenametostu;

在这里插入图片描述

DROPTABLESTUDENT;

sqlite3没有实现删除一列的命令，要实现这个操作，需要先将该表拷贝到一个新表，但是只集成需要的列，要删除的列不继承过来。可以用以下方式操作删除一列：

sqlitecreatetabletestasselectsno,sname,ssex,sage,sdeptfromstu;sqlitedroptablestu;sqlitealtertabletestrenametostu;

由上图可知，我们刚才增加的列spwd被删除了。

如果我不想查看所有的数据，而指向查看某一个人的信息要怎么操作呢？我们就要借助where子句来实现。where子句可以结合操作语句来进程增删改查，是最常用的一个子句。

根据姓名查找记录：

Select*fromstudentwheresname='一口';

根据学号查找记录：

Select*fromstudentwheresno=95001;

根据姓名和年龄同时查找记录：

select*fromstudentwheresname='一口'andsage=19;

显示某两列的数据

selectsno,snamefromstudent;

在这里插入图片描述

现在我们假设要将表foods备份出为，并用命令回复该数据库。按照以下步骤操作即可。

把创建表及向表中插入记录的所有SQL语句显示在屏幕上指定dump命令输出到文件的文件名输出创建并插入数据到基本表的SQL语句到output指定的文件恢复输出内容到标准输出设备(屏幕)此时输出的SQL语句转回到屏幕sqliteDroptablefoods;--删除foods表语句说明：

上述Drop为SQL语句，用于删除指定的表。因为是SQL语句，因此以“;”结尾

执行中的包含的所有SQL语句，用来重建刚删除的4张表及相关数据

好了，跟着一口君一步步操作，基本上sqlite3的增删改查操作，相信各位应该没什么问题了，sqlite还有其他一些子句以及函数的用法，留待下一篇继续介绍。