前置知识
了解以下知识，对更好地了解SQLite数据库会有帮助：
- 关系型数据库
- JDBC
- SQL语言
一. SQLite简介
SQLite是一种关系型数据库，它的数据库就是一个文件。
SQLite实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。它是一个零配置的数据库，这意味不需要在系统中进行配置。
二. SQLite 安装
在 Windows 上安装 SQLite
- 访问下载页面
- 下载 sqlite-tools-win32-.zip 和 sqlite-dll-win32-.zip 压缩文件。
- 创建文件夹 C:\sqlite，并在此文件夹下解压上面两个压缩文件，将得到 sqlite3.def、sqlite3.dll 文件和 sqlite-tools-win32-x86-3270200 文件夹。
- 添加 C:\sqlite\ sqlite-tools-win32-x86-3270200到 PATH 环境变量，最后在命令提示符下，使用 sqlite3 命令，将显示如下结果。
C:\>sqlite3
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
三. SQLite 点命令
SQLite 中有一些简单却有用的命令。这些命令被称为 SQLite 的点命令，这些命令的不同之处在于它们不以分号 ; 结束。
SQLite进入命令行：
#sqlite3
SQLite version 3.7.14.1 2012-10-04 19:37:12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
在sqlite>提示符后面就可以使用sqlite命令了。注意：提示符与点命令之间不能有空格。
查看可用的点命令清单：
sqlite>.help
上面的命令会显示各种重要的 SQLite 点命令的列表，如下所示：
| 命令 | 描述 |
|---|---|
| .backup ?DB? FILE | 备份 DB 数据库（默认是 "main"）到 FILE 文件。 |
| .bail ON/OFF | 发生错误后停止。默认为 OFF。 |
| .databases | 列出数据库的名称及其所依附的文件。 |
| .dump ?TABLE? | 以 SQL 文本格式转储数据库。如果指定了 TABLE 表，则只转储匹配 LIKE 模式的 TABLE 表。 |
| .echo ON/OFF | 开启或关闭 echo 命令。 |
| .exit | 退出 SQLite 提示符。 |
| .explain ON/OFF | 开启或关闭适合于 EXPLAIN 的输出模式。如果没有带参数，则为 EXPLAIN on，及开启 EXPLAIN。 |
| .header(s) ON/OFF | 开启或关闭头部显示。 |
| .help | 显示消息。 |
| .import FILE TABLE | 导入来自 FILE 文件的数据到 TABLE 表中。 |
| .indices ?TABLE? | 显示所有索引的名称。如果指定了 TABLE 表，则只显示匹配 LIKE 模式的 TABLE 表的索引。 |
| .load FILE ?ENTRY? | 加载一个扩展库。 |
| .log FILE/off | 开启或关闭日志。FILE 文件可以是 stderr（标准错误）/stdout（标准输出）。 |
| .mode MODE | 设置输出模式，MODE 可以是下列之一：1. csv 逗号分隔的值 2. column 左对齐的列3. html HTML 的 <table> 代码4. insert TABLE 表的 SQL 插入（insert）语句5. line 每行一个值6. list 由 .separator 字符串分隔的值7. tabs 由 Tab 分隔的值8. tcl TCL 列表元素 |
| .nullvalue | STRING 在 NULL 值的地方输出 STRING 字符串。 |
| .output FILENAME | 发送输出到 FILENAME 文件。 |
| .output stdout | 发送输出到屏幕。 |
| .print STRING... | 逐字地输出 STRING 字符串。 |
| .prompt MAIN CONTINUE | 替换标准提示符。 |
| .quit | 退出 SQLite 提示符。 |
| .read FILENAME | 执行 FILENAME 文件中的 SQL。 |
| .schema ?TABLE? | 显示 CREATE 语句。如果指定了 TABLE 表，则只显示匹配 LIKE 模式的 TABLE 表。 |
| .separator STRING | 改变输出模式和 .import 所使用的分隔符。 |
| .show | 显示各种设置的当前值。 |
| .stats ON/OFF | 开启或关闭统计。 |
| .tables ?PATTERN? | 列出匹配 LIKE 模式的表的名称。 |
| .timeout MS | 尝试打开锁定的表 MS 毫秒。 |
| .width NUM NUM | 为 "column" 模式设置列宽度。 |
| .timer ON/OFF | 开启或关闭 CPU 定时器。 |
退出sqlite提示符：
sqllite>.quit 或是 sqlite>.exit
查看 SQLite 命令提示符的默认设置。
sqlite>.show
echo: off
explain: off
headers: off
mode: column
nullvalue: ""
output: stdout
separator: "|"
width:
sqlite>
格式化输出
sqlite>.header on
sqlite>.mode column
sqlite>.timer on
sqlite>
上面设置将产生如下格式的输出：
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0
CPU Time: user 0.000000 sys 0.000000
四. SQLite 语法
- 不区分大小写
- 以两个连续的
-作为注释。sqlite>.help -- 这是一个简单的注释 - 语句以分号（;）结束
SQLite 语句
所有的 SQLite 语句可以以任何关键字开始，如 SELECT、INSERT、UPDATE、DELETE、ALTER、DROP 等，所有的语句以分号（;）结束。
这些语句类似于SQL语句，但是SQLite不支持其中的一些特性，
在 SQLite 中，SQL92 不支持的特性如下所示：
| 特性 | 描述 |
|---|---|
| RIGHT OUTER JOIN | 只实现了 LEFT OUTER JOIN。 |
| FULL OUTER JOIN | 只实现了 LEFT OUTER JOIN。 |
| ALTER TABLE | 支持 RENAME TABLE 和 ALTER TABLE 的 ADD COLUMN variants 命令，不支持 DROP COLUMN、ALTER COLUMN、ADD CONSTRAINT。 |
| Trigger 支持 | 支持 FOR EACH ROW 触发器，但不支持 FOR EACH STATEMENT 触发器。 |
| VIEWs | 在 SQLite 中，视图是只读的。您不可以在视图上执行 DELETE、INSERT 或 UPDATE 语句。 |
| GRANT 和 REVOKE | 可以应用的唯一的访问权限是底层操作系统的正常文件访问权限。 |
五. SQLite 数据类型
5.1 SQLite 存储类
每个存储在 SQLite 数据库中的值都具有以下存储类之一：
| 存储类 | 描述 |
|---|---|
| NULL | 值是一个 NULL 值。 |
| INTEGER | 值是一个带符号的整数，根据值的大小存储在 1、2、3、4、6 或 8 字节中。 |
| REAL | 值是一个浮点值，存储为 8 字节的 IEEE 浮点数字。 |
| TEXT | 值是一个文本字符串，使用数据库编码（UTF-8、UTF-16BE 或 UTF-16LE）存储。 |
| BLOB | 值是一个 blob 数据，完全根据它的输入存储。 |
5.2 Date 与 Time 数据类型
SQLite 没有一个单独的用于存储日期和/或时间的存储类，但 SQLite 能够把日期和时间存储为 TEXT、REAL 或 INTEGER 值。
| 存储类 | 日期格式 |
|---|---|
| TEXT | 格式为 "YYYY-MM-DD HH:MM:SS.SSS" 的日期。 |
| REAL | 从公元前 4714 年 11 月 24 日格林尼治时间的正午开始算起的天数。 |
| INTEGER | 从 1970-01-01 00:00:00 UTC 算起的秒数。 |
您可以以任何上述格式来存储日期和时间，并且可以使用内置的日期和时间函数来自由转换不同格式。
六. SQLite 约束
约束是在表的数据列上强制执行的规则。这些是用来限制可以插入到表中的数据类型。这确保了数据库中数据的准确性和可靠性。
约束可以是列级或表级。列级约束仅适用于列，表级约束被应用到整个表。
以下是在 SQLite 中常用的约束。
-
NOT NULL 约束：确保某列不能有 NULL 值。
-
DEFAULT 约束：当某列没有指定值时，为该列提供默认值。
-
UNIQUE 约束：确保某列中的所有值是不同的。
-
PRIMARY Key 约束：唯一标识数据库表中的各行/记录。
-
CHECK 约束：CHECK 约束确保某列中的所有值满足一定条件。
删除约束
SQLite 支持 ALTER TABLE 的有限子集。在 SQLite 中，ALTER TABLE 命令允许用户重命名表，或向现有表添加一个新的列。重命名列，删除一列，或从一个表中添加或删除约束都是不可能的。
七. SQLite 数据库操作
7.1 创建数据库
SQLite 的 sqlite3 命令被用来创建新的 SQLite 数据库。您不需要任何特殊的权限即可创建一个数据。
语法
$sqlite3 DatabaseName.db
实例
如果您想创建一个新的数据库 <testDB.db>，SQLITE3 语句如下所示：
D:\>sqlite3 test.db
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
上面的命令将在当前目录下创建一个文件 testDB.db。该文件将被 SQLite 引擎用作数据库。sqlite3 命令在成功创建数据库文件之后，将提供一个 sqlite> 提示符。
一旦数据库被创建，您就可以使用 SQLite 的 .databases 命令来检查它是否在数据库列表中，如下所示：
sqlite> .database
main: D:\test.db
7.2 创建表
SQLite 的 CREATE TABLE 语句用于在任何给定的数据库创建一个新表。创建基本表，涉及到命名表、定义列及每一列的数据类型。
语法
CREATE TABLE database_name.table_name(
column1 datatype PRIMARY KEY(one or more columns),
column2 datatype,
column3 datatype,
.....
columnN datatype,
);
实例
sqlite> CREATE TABLE DEPARTMENT(
ID INT PRIMARY KEY NOT NULL,
DEPT CHAR(50) NOT NULL,
EMP_ID INT NOT NULL
);
您可以使用 SQLIte 命令中的 .tables 命令来验证表是否已成功创建，该命令用于列出附加数据库中的所有表。
sqlite> .table
DEPARTMENT
您可以使用 SQLite .schema 命令得到表的完整信息，如下所示：
sqlite> .schema DEPARTMENT
CREATE TABLE DEPARTMENT(
ID INT PRIMARY KEY NOT NULL,
DEPT CHAR(50) NOT NULL,
EMP_ID INT NOT NULL
);
7.3 删除表
SQLite 的 DROP TABLE 语句用来删除表定义及其所有相关数据、索引、触发器、约束和该表的权限规范。
使用此命令时要特别注意，因为一旦一个表被删除，表中所有信息也将永远丢失。
语法
DROP TABLE 语句的基本语法如下。您可以选择指定带有表名的数据库名称，如下所示：
DROP TABLE database_name.table_name;
实例
让我们先确认 DEPARTMENT 表已经存在。
sqlite> .tables
DEPARTMENT
这意味着 DEPARTMENT 表已存在数据库中，接下来让我们把它从数据库中删除，如下：
sqlite>DROP TABLE DEPARTMENT;
sqlite>
现在，如果尝试 .TABLES 命令，那么将无法找到 COMPANY 表了：
sqlite>.tables
sqlite>
显示结果为空，意味着已经成功从数据库删除表。
八. SQLite - Java
8.1 连接数据库
下面的 Java 程序显示了如何连接到一个现有的数据库。如果数据库不存在，那么它就会被创建，最后将返回一个数据库对象。
//导入需要的包
import java.sql.*;
public class SQLiteJDBC
{
public static void main( String args[] )
{
Connection c = null;
try {
//加载JDBC驱动程序（Driver）至DriverManager
Class.forName("org.sqlite.JDBC");
//从DriverManager中，通过JDBC URL，用户名，密码来获取相应的数据库连接（Connection）
c = DriverManager.getConnection("jdbc:sqlite:test.db");
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("Opened database successfully");
}
}
这在当前目录中创建数据库 test.db
8.2 创建表
下面的 Java 程序将用于在先前创建的数据库中创建一个表：
import java.sql.*;
public class SQLiteJDBC
{
public static void main( String args[] )
{
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:test.db");
System.out.println("Opened database successfully");
stmt = c.createStatement();
String sql = "CREATE TABLE COMPANY " +
"(ID INT PRIMARY KEY NOT NULL," +
" NAME TEXT NOT NULL, " +
" AGE INT NOT NULL, " +
" ADDRESS CHAR(50), " +
" SALARY REAL)";
stmt.executeUpdate(sql);
stmt.close();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("Table created successfully");
}
}
上述程序编译和执行时，它会在 test.db 中创建 COMPANY 表。
8.3 INSERT 操作
下面的 Java 代码显示了如何在上面创建的 COMPANY 表中创建记录：
import java.sql.*;
public class SQLiteJDBC
{
public static void main( String args[] )
{
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:test.db");
c.setAutoCommit(false);
System.out.println("Opened database successfully");
stmt = c.createStatement();
String sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
"VALUES (1, 'Paul', 32, 'California', 20000.00 );";
stmt.executeUpdate(sql);
sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
"VALUES (2, 'Allen', 25, 'Texas', 15000.00 );";
stmt.executeUpdate(sql);
sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
"VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );";
stmt.executeUpdate(sql);
sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
"VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";
stmt.executeUpdate(sql);
stmt.close();
c.commit();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("Records created successfully");
}
上述程序编译和执行时，它会在 COMPANY 表中创建给定记录，并会显示以下两行：
Opened database successfully
Records created successfully
8.4 SELECT 操作
下面的 Java 程序显示了如何从前面创建的 COMPANY 表中获取并显示记录：
import java.sql.*;
public class SQLiteJDBC
{
public static void main( String args[] )
{
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:test.db");
c.setAutoCommit(false);
System.out.println("Opened database successfully");
stmt = c.createStatement();
ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
while ( rs.next() ) {
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
String address = rs.getString("address");
float salary = rs.getFloat("salary");
System.out.println( "ID = " + id );
System.out.println( "NAME = " + name );
System.out.println( "AGE = " + age );
System.out.println( "ADDRESS = " + address );
System.out.println( "SALARY = " + salary );
System.out.println();
}
rs.close();
stmt.close();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("Operation done successfully");
}
}
上述程序编译和执行时，它会产生以下结果：
Opened database successfully
ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 20000.0
ID = 2
NAME = Allen
AGE = 25
ADDRESS = Texas
SALARY = 15000.0
ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0
ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0
Operation done successfully
8.5 UPDATE 操作
下面的 Java 代码显示了如何使用 UPDATE 语句来更新任何记录，然后从 COMPANY 表中获取并显示更新的记录：
import java.sql.*;
public class SQLiteJDBC
{
public static void main( String args[] )
{
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:test.db");
c.setAutoCommit(false);
System.out.println("Opened database successfully");
stmt = c.createStatement();
String sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1;";
stmt.executeUpdate(sql);
c.commit();
ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
while ( rs.next() ) {
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
String address = rs.getString("address");
float salary = rs.getFloat("salary");
System.out.println( "ID = " + id );
System.out.println( "NAME = " + name );
System.out.println( "AGE = " + age );
System.out.println( "ADDRESS = " + address );
System.out.println( "SALARY = " + salary );
System.out.println();
}
rs.close();
stmt.close();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("Operation done successfully");
}
}
上述程序编译和执行时，它会产生以下结果：
Opened database successfully
ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 25000.0
ID = 2
NAME = Allen
AGE = 25
ADDRESS = Texas
SALARY = 15000.0
ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0
ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0
Operation done successfully
8.6 DELETE 操作
下面的 Java 代码显示了如何使用 DELETE 语句删除任何记录，然后从 COMPANY 表中获取并显示剩余的记录：
import java.sql.*;
public class SQLiteJDBC
{
public static void main( String args[] )
{
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:test.db");
c.setAutoCommit(false);
System.out.println("Opened database successfully");
stmt = c.createStatement();
String sql = "DELETE from COMPANY where ID=2;";
stmt.executeUpdate(sql);
c.commit();
ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
while ( rs.next() ) {
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
String address = rs.getString("address");
float salary = rs.getFloat("salary");
System.out.println( "ID = " + id );
System.out.println( "NAME = " + name );
System.out.println( "AGE = " + age );
System.out.println( "ADDRESS = " + address );
System.out.println( "SALARY = " + salary );
System.out.println();
}
rs.close();
stmt.close();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("Operation done successfully");
}
}
上述程序编译和执行时，它会产生以下结果：
Opened database successfully
ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 25000.0
ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0
ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0
Operation done successfully
网友评论