首页 > > 网络编程 > 其它 >

SQLite 带你入门

2018-06-17 20:06:41来源：未知阅读 ()

　　SQLite数据库相较于我们常用的Mysql,Oracle而言，实在是轻量得不行（最低只占几百K的内存）。平时开发或生产环境中使用各种类型的数据库，可能都需要先安装数据库服务（server）,然后才能通过代码、命令行或者客户端工具来操作数据库，但是SQLite却有点别具一格，它是一个文件型的关系数据库，完全不用你安装，也不需要任何的配置或依赖，去官网下载编译好的二进制文件解压就可以使用——第一次使用时，同事直接拷给我一个文件，说这就是SQLite了，我擦嘞还惊了一下，竟然直接对着文件就能执行CRUD命令，这完全刷新了我对数据库的认知。而它的官网/When to use SQLite也说得特别明确：SQLite is not directly comparable to client/server SQL database engines such as MySQL, Oracle, or SQL Server since SQLite is trying to solve a different problem。是的，它的出现并不是为了和 Mysql,Oracle等数据库竞争，而是为了解决不同的问题；那么哪些场景适合使用SQLite,哪些场景又适合client/server型数据库呢？博主本来也想问度娘的，但是发现官网中就有最最合适的解释：Client/server SQL database engines strive to implement a shared repository of enterprise data; SQLite strives to provide local data storage for individual applications and devices。意译过来就是说，client/server型的数据库适合于共享数据的存储—— 一个server端存储数据，N个客户端都可以对数据进行CRUD的操作；而SQLite就完全是一种本地化的文件存储，加之其非常的轻量，特别适合个人应用和设备，所以，你会发现SQLite在嵌入式设备开发比如移动开发中应用得非常广泛。博主虽然不是做嵌入式开发的，但是用了一下SQLite发现特别小巧好用，建议大家如果应用程序是并发量不是特别高本地应用，完全可以尝试使用SQLite这种轻量的数据库来代替，也省去了安装繁重的数据库服务对系统资源的占用。好了，现在正式切入正题，跟着布衣博主一起来尝试使用SQLite的一些简单招式——

库文件下载

　　要使用SQLite数据库，如果没有好同事拷贝给你，就自己去官网下吧。点击官网下载页就可以下载最新的SQLite版本（SQLite3），页面也给出了针对不同平台的下载版本——

　　对于Windows 64位操作系统,网上的一些教程都是说要把图中的 1 、2 一起下载解压在同一个文件，但是博主不太喜欢盲从，喜欢琢磨原因，一番尝试后发现，其实只需要下载工具压缩包 2 就可以进行正常的数据库操作了，而对于 1 中动态链接库文件的作用，博主也始终没搞明白，在此还望常用Sqlite的高人能指点一二，博主感激不尽！！！

数据库连接

　　① 命令行

　　上图中 1 下载的压缩包解压后会有三个文件可执行文件 sqldiff.exe、sqlite3.exe和sqlite3_analyzer.exe，开启cmd切换到解压目录，就可以直接通过sqlite3执行SQL命令来操作数据库。这里要注意，如果直接双击sqlite3打开命令行执行后续命令往往是没有效果的，博主一番尝试也无解；有效的方式是切换到该目录后以 sqlite3 + 数据库名 的方式开始令行，如果数据库存在就会直接使用，不存在就会创建一个新的数据库：

　　需要说明的是，博主尝试后发现创建的数据库名可以是任意的名字，并不一定是.db 结尾，只不过出于规范化的考量，最好还是加上后缀，这样一看便知是数据库文件。SQLite的命令都是点命令，也就是说都是用 . 开头，并且不以；结尾，使用过程中多熟悉一下就习惯了。这里博主也只是简单演示，具体命令行可以 .help 查看或者通过百度、官网渠道查询，博主不赘述。

　　② 可视化工具

　　百度一下，你会发现SQLite有一些专门针对性的可视化连接工具，应该都比较好用，博主没有去尝试过，不过使用通用版的Navicat也可以连接SQLite，本着工具归一的原则，也就没有必要单独去下载其他连接工具了。建立数据库连接也很简单，不同于其他数据库需要IP、端口以及用户名、密码，SQLite只需要找到我们的创建的数据库文件挂载，就相当于连接到相应的数据库了；而新建也很简单，在建库目录下定义好库文件名字就就可以了，下图中1 、2 分别显示了如何连接现有的库文件和创建新的数据库——

　　看似简单，本来无需多言的，但是爱折腾的布衣博主却有个匪夷所思的发现：使用 Navicat Premium 本进行SQLite的连库和创建新库的过程中，根本不需要依赖自己下载解压的那两个压缩包——新建连接的时候我甚至可以挂载一个txt文件并成功建立起连接，而且能进行正常的数据库SQL操作；创建库的时候我也可以在任意位置创建任意名字、后缀名的数据库，同样能正常SQL操作。这样就有点意思了，也就是说电脑上的一切非目录文件只要你心情好都可以建立连接当成数据库来使用，太腻（bian）害(tai)了吧？博主换了不同的计算机，包括使用Linux系统，尝试过后都是如此，唯一合理的解释就是 Navicat Premium 这厮本身就内置了SQLite数据库引擎，所以才能直接连接以及创建数据库。（其实不管是Windows系统还是Linux系统你都可以在系统用户目录发现Navicat的连接对象文件）

SQLite 的存储类型

　　学习一个新的数据库，大体从几个方面来了解它——存储结构、操作语言以及支持存储的数据类型。既然SQLite是一种关系型数据库，那么就支持通用的SQL语言，所以你大可将你深厚的SQL功力运用到SQLite身上愉快的进行你的CRUD操作（当然，一些内置函数和SQL语法还是有所不同）。SQLite比较特殊的其实在于它粗放式的数据存储类型，而且并不强制的进行类型约束，这点和其他关系型数据库有很大的不同。如此的结果就是，虽然创建表的时候你指定了某一列应该用什么数据类型，但实际上你是可以胡来的，比如向整型列中插入文本数据，向字符型中插入日期等等（有个特殊情况就是建表时主键设置若为INTEGER PRIMARY KEY【原文：except an INTEGER PRIMARY KEY column】就只能插入整数，插入其他数据类型会报错）。至于原因，SQLite官网数据类型页面中Datatypes In SQLite段讲得很清楚，博主转述过来就是说：传统的关系型数据库采用的是静态数据类型系统，一个字段值的数据类型由存储其值的列容器决定，而SQLite采用更通用的动态数据类型系统，一个字段的数据类型只与其值本身有关，而与存储它的容器无关。

　　按照官网描述，SQLite支持的数据类型分为以下 5 个存储类型：

NULL 空值
INTEGER 带符号整型，根据其大小存储在1, 2, 3, 4, 6,或 8 字节中
REAL　　浮点型，存储为一个 8 字节的IEEE 浮点数
TEXT　　文本类型（UTF-8, UTF-16BE 或 UTF-16LE编码格式）
BLOB 全型（布衣博主自己给的称谓），存什么就是什么

　　别看只有基本的 5 种存储类型支持，但由于SQLite采用的是动态数据类型系统，而且存储类较之普通的数据类型是更加笼统的包含关系，因此能完全兼容其它静态数据类型系统的关系型数据库。但是这种太自由化的存储还是有些问题的，比如一个数据列我同时存了一个 400 整型值和 '500' 的文本类型值，这两个值数据类型不一样，我这么比较？所以，根据SQLite官网的说法，为了最大限度与其他关系型数据库兼容，SQLite对数据类型进行了很精巧的设计，就是让数据列具有类型亲和性的特性（其实博主更喜欢某些人翻译的 类型近似 这种叫法）。当我们创建表做字段类型声明的时候，实际上只是表明了该列具有的近似类型，在正式插入数据的时候，SQLite引擎才会基于该列的近似类型优先推荐使用 5 种存储类型中的哪一种来存储你的数据——注意是推荐，并不强制，也就是说只要你想要乱搞，SQLite也并不会限制你，SQLite是列自由的。所以，建表时字段类型声明的限制在SQLite中是被弱化了的。在最新的SQLite版本中，数据库的每一列都被定义为以下 5 种近似类型的一种，其定义和规则官网/Determination Of Column Affinity段中有做出解释：

INTEGER 整型，如果声明的字段类型包含字符串“INT”（注意，SQLite大多数情况都不区分大小写,下同），那么该字段类型被分配为INTEGER 近似类型；
TEXT 文本，如果为字段声明的类型中包含了'CHAR'、'CLOB'或'TEXT'，该字段被分配为TEXT亲和性。比如'VARCHAR'包含了'CHAR'，所以被分配为TEXT 近似类型；
BLOB 无类型,如果为字段声明的类型中包含了'BLOB'，或者没有为该字段声明类型，该字段被分配为BLOB 近似类型；
REAL 浮点型，如果为字段声明的类型中包含了'REAL'、'FLOA'或'DOUB'，则该字段被分配为REAL 近似类型；
NUMERIC 数值型，除以上情况外的类型，则被分配为NUMERIC 近似类型。

　　官网上/Affinity Name Examples段落中有一份表格罗列了传统数据类型作为子集与上述 5 种近似类型如何对应的关系。基于上述近似类型的列叙，可以看出SQLite是从声明类型的字符串中去匹配列的近似类型来决定数据的存储类的，因此SQLite很有意思的是创建表时类型声明可以很随意，比如我可以声明一个不存在类型的字符串”chenbenbuyi”，但SQLite会根据规则自动识别为NUMERIC的近似类型，并据此近似类型存储我的数据——

　　可能有些人对于上面的数据存储类还是有些疑惑——根据列的近似类型是如何推断数据存入后的最终类型呢？按博主的理解，SQLite在基于类型近似做数据存储转换的时候是有个推荐优先级的。比如上面的声明中，并不存在name声明的数据类型，所以数据库会自动匹配为NUMERIC的近似类型。当文本数据（'23'）被插入到该列时，如果转换操作不会导致数据信息丢失以及完全可逆，那么SQLite就会将该文本数据优先转换为INTEGER或REAL类型的数据（NULL或BLOB类型数据不做转换），转换不成功才会按照文本数据存储——这里能转换INTEGER成功；如果存储列的近似类型为TEXT,那么自然数据存储的优先存储类是TEXT,所以哪怕你存储的是整型数字，结果存的依然是TEXT。这就是类型近似在数据存储中的应用。

代码连接数据库

　　代码连接比较基础简单，博主简单操作一盘，权当复习,虽然实际项目中很少用得这么基础。基本上呢，代码操作数据库的步骤可以概括为：加载驱动、获取连接、创建预编译对象、执行SQL、释放资源，顺口溜一句就是“贾琏欲执释”。按照这个步骤，首先是要下载SQLite的驱动程序包，将包添加到IDE中并Build Path，然后愉快的撸码操作——

 1 import java.sql.*;
 2 
 3 public class SQLiteTest {
 4     public static void main(String[] args) {
 5         String driver = "org.sqlite.JDBC";
 6         String url = "jdbc:sqlite:E:\\test.db";
 7         String sql = "select * from chenbenbuyi";
 8         Connection conn = null;
 9         Statement stmt = null;
10         ResultSet rs = null;
11         try {
12             //加载驱动
13             Class.forName(driver);
14             //获取连接
15             conn = DriverManager.getConnection(url); //sqlite不需要密码
16             //创建预编译对象
17             stmt = conn.createStatement();
18             //执行sql
19             rs = stmt.executeQuery(sql);
20             while (rs.next()) {
21                 String s = rs.getString("x") +":"+rs.getString("y")+":"+rs.getString("z");
22                 System.out.println(s);
23             }
24         } catch (Exception e) {
25             e.printStackTrace();
26         } finally {
27             //释放资源：一定要在finally中确保资源被释放
28             if (null != rs) {
29                 try {
30                     rs.close();
31                 } catch (SQLException e) {
32                     e.printStackTrace();
33                 }
34             }
35             if (null != stmt) {
36                 try {
37                     stmt.close();
38                 } catch (SQLException e) {
39                     e.printStackTrace();
40                 }
41             }
42             if (null != conn) {
43                 try {
44                     conn.close();
45                 } catch (SQLException e) {
46                     e.printStackTrace();
47                 }
48             }
49         }
50 
51     }
52 }