SQLite教程1

2023-06-02 20:13 由磁石空杯发表于 #数据库

0简介

在这个SQLite初学者教程中，我们用详细的例子涵盖了所有关于SQLite的基础到高级主题。本课程将通过简单易懂的主题和例子帮助你学习SQLite。

什么是SQLite？

SQLite是一个开源的、嵌入式的、关系型的数据库管理系统，大约设计于2000年。它是一个轻量级的数据库，零配置，没有服务器或安装的要求。尽管它很简单，但它充满了数据库管理系统的流行功能。

SQLite教程纲要

👉 第一课下载和安装SQLite - 如何在Windows上下载和安装SQLite
👉 第2课 SQLite数据库--如何创建、打开、备份和删除文件
👉 第3课 SQLite创建、修改、删除表--通过实例学习
👉 第4课 SQLite主键和外键--通过实例学习
👉 第5课 SQLite数据类型--SQLite中的数据类型及示例
👉 第六课 SQLite查询--Select, Where, LIMIT, OFFSET, Count, Group By
👉 第7课 SQLite Join - 自然的左外链、内链、交叉链与表实例
👉 第8课 SQLite INSERT, UPDATE, DELETE查询 - 通过实例学习
👉 第9课 SQLite索引、触发器和视图--结合实例学习
👉 第10课 SQLite字符串函数 - REPLACE, SUBSTR, TRIM, ROUND (举例)
👉 第11课 SQLite面试问题--20大SQLite面试问题及答案

SQLite的主要特点

与其他数据库管理系统（如SQL Server或Oracle）相比，SQLite是非常轻量级的（它的大小小于500Kb）。
SQLite不是客户端-服务器数据库管理系统。它是一个内存库，你可以直接调用和使用。不需要安装，也不需要配置。
典型的SQLite数据库包含在计算机磁盘存储的文件中，所有的数据库对象（表、视图、触发器等）都包含在该文件中。不需要专门的服务器。

什么时候使用SQLite？

如果你正在为电视、移动电话、照相机、家用电子设备等设备开发嵌入式软件，那么SQLite是一个不错的选择。
SQLite可以处理中低流量的HTTP请求，并管理网站的复杂会话信息
当你需要存储文件的归档时，SQLite可以产生更小的归档，并且比普通的ZIP归档包含更少的元数据。
如果你想在应用程序中对数据进行处理，你可以使用SQLite作为一个临时数据集。你可以将数据加载到SQLite内存数据库中，并执行所需的查询。你可以以你想在你的应用程序中显示的格式提取数据。
提供了简单而有效的方法来处理使用内存变量。例如，你正在开发一个程序，你必须对一些记录进行计算。你可以创建一个SQLite数据库并在那里插入记录，只需一个查询，你就可以选择记录并进行计算。
当你需要一个用于学习和培训的数据库系统时，SQLite是一个很好的选择。正如我们前面解释的，不需要安装或配置。在你的电脑中复制SQLite库，你就可以开始学习了。

为什么使用SQLite？

它是免费的。SQLite开放源码，使用它不需要商业许可。
SQLite是跨平台的数据库管理系统。它可以在广泛的平台上使用，如Windows、Mac OS、Linux和Unix。它还可以在很多嵌入式操作系统上使用，如Symbian和Windows CE。
SQLite提供了一种高效的数据存储方式，列的长度是可变的，并不固定。因此，SQLite将只分配一个字段需要的空间。例如，如果你有一个varchar(200)列，而你在上面放了一个10个字符的长度值，那么SQLite将只为这个值分配20个字符的空间，而不是整个200个空间。
广泛的SQLite APIs - SQLite为广泛的编程语言提供API，例如.Net语言（Visual Basic、C#）、PHP、Java、Objective C、Python和许多其他编程语言。
SQLite是非常灵活的。
- SQLite变量是动态类型的，也就是说，在变量被赋值之前，变量的类型是不确定的，而不是在声明时定义。
- INSERT ON CONFLICT REPLACE语句。通过这个语句，你可以告诉SQLite尝试对一个表进行插入，如果发现具有相同主键的行，那么就用插入的值更新它们。
- 使用SQLite，你可以同时在同一个会话中处理多个数据库。只要连接这些数据库，然后你就可以同时访问所有数据库的对象（表、视图等）。

SQLite的限制和不支持的功能

SQLite不支持RIGHT OUTER JOIN和FULL OUTER JOIN。它只支持 LEFT OUTER JOIN。
ALTER表语句的限制：在SQLite中使用ALTER表语句，你只能添加一列或重命名一个表（我们将在下面的教程中看到）。但是，你不能做以下事情：
- ALTER列。
- DROP列。
- 增加一个约束条件。
视图是只读的 - 你不能在视图中写入INSERT、DELETE或UPDATE语句。然而，你可以在视图上创建一个触发器，并在其中进行INSERT、DELETE或UPDATE语句。
在QSLite中没有实现GRANT和REVOKE命令。在SQLite中只实现了普通的文件访问权限。这是因为SQLite对磁盘文件进行读写，与其他数据库管理系统不同。
触发器 - 正如我们在接下来的教程中看到的，SQLite只支持FOR EACH ROW触发器，不支持FOR EACH STATEMENT触发器。

安装

下载并安装SQLite包安装器

从SQLite官方网站：https://www.sqlite.org/index.html

https://www.sqlite.org/2023/sqlite-tools-win32-x86-3420000.zip

解压后执行sqlite3.exe即可

SQLite Studio

有很多SQLite管理工具可以使SQLite数据库的工作更容易。这些工具不是用命令行来创建和管理数据库，而是提供一套GUI工具，让你创建和管理数据库。

https://www2.sqlite.org/cvstrac/wiki?p=ManagementTools列出了几十个这样的工具；推荐使用SQLite Studio。

SQLite Studio：它是一个不需要安装的便携式工具。它同时支持SQLite3和SQLite2。你可以轻松地将数据导入和导出为各种格式，如CSV、HTML、PDF、JSON。它是开源的，支持Unicode。

官网：https://sqlitestudio.pl/ 下载安装即可。

样本数据库

编辑文件：TutorialsSampleDB.sql

CREATE TABLE [Departments] (  
    [DepartmentId] INTEGER  NOT NULL PRIMARY KEY,  
    [DepartmentName] NVARCHAR(50)  NULL  
);  
INSERT INTO Departments VALUES(1, 'IT');
INSERT INTO Departments VALUES(2, 'Physics');
INSERT INTO Departments VALUES(3, 'Arts');
INSERT INTO Departments VALUES(4, 'Math');

CREATE TABLE [Students] (  
    [StudentId] INTEGER  PRIMARY KEY NOT NULL,  
    [StudentName] NVARCHAR(50) NOT NULL,  
    [DepartmentId] INTEGER  NULL, 
    [DateOfBirth] DATE NULL,
    FOREIGN KEY(DepartmentId) REFERENCES Departments(DepartmentId)
);  
INSERT INTO Students VALUES(1, 'Michael', 1, '1998-10-12');
INSERT INTO Students VALUES(2, 'John', 1, '1998-10-12');
INSERT INTO Students VALUES(3, 'Jack', 1, '1998-10-12');
INSERT INTO Students VALUES(4, 'Sara', 2, '1998-10-12');
INSERT INTO Students VALUES(5, 'Sally', 2, '1998-10-12');
INSERT INTO Students VALUES(6, 'Jena', NULL, '1998-10-12');
INSERT INTO Students VALUES(7, 'Nancy', 2, '1998-10-12');
INSERT INTO Students VALUES(8, 'Adam', 3, '1998-10-12');
INSERT INTO Students VALUES(9, 'Stevens', 3, '1998-10-12');
INSERT INTO Students VALUES(10, 'George', NULL, '1998-10-12');

CREATE TABLE [Tests] (
    [TestId] INTEGER NOT NULL PRIMARY KEY,
    [TestName] NVARCHAR(50) NOT NULL,
    [TestDate] DATE NULL
);
INSERT INTO [Tests] VALUES(1, 'Mid Term IT Exam', '2015-10-18');
INSERT INTO [Tests] VALUES(2, 'Mid Term Physics Exam', '2015-10-23');
INSERT INTO [Tests] VALUES(3, 'Mid Term Arts Exam', '2015-10-10');
INSERT INTO [Tests] VALUES(4, 'Mid Term Math Exam', '2015-10-15');

CREATE TABLE [Marks] (  
    [MarkId] INTEGER NOT NULL PRIMARY KEY,
    [TestId] INTEGER NOT NULL,
    [StudentId] INTEGER  NOT NULL,  
    [Mark] INTEGER  NULL,
    FOREIGN KEY(StudentId) REFERENCES Students(StudentId),
    FOREIGN KEY(TestId) REFERENCES Tests(TestId) 
);  

INSERT INTO Marks VALUES(1, 1, 1, 18);
INSERT INTO Marks VALUES(2, 1, 2, 20);
INSERT INTO Marks VALUES(3, 1, 3, 16);
INSERT INTO Marks VALUES(4, 2, 4, 19);
INSERT INTO Marks VALUES(5, 2, 5, 14);
INSERT INTO Marks VALUES(6, 2, 7, 20);
INSERT INTO Marks VALUES(7, 3, 8, 20);
INSERT INTO Marks VALUES(8, 3, 9, 20);

导入：

sqlite3 TutorialsSampleDB.db < d:\TutorialsSampleDB.sql

可以看到生成了TutorialsSampleDB.db。

使用SQLite Studio可以打开数据库：

2数据库文件操作

创建

sqlite3 SchoolDB.db
sqlite> .databases
main: D:\program\sqlite-tools-win32-x86-3420000\test.db r/w
sqlite> .open test2.db #  若不存在则创建
>sqlite3 TutorialsSampleDB.db
SQLite version 3.42.0 2023-05-16 12:36:15
Enter ".help" for usage hints.
sqlite> .help # 帮助
.archive ...             Manage SQL archives
.auth ON|OFF             Show authorizer callbacks
.backup ?DB? FILE        Backup DB (default "main") to FILE
.bail on|off             Stop after hitting an error.  Default OFF
.binary on|off           Turn binary output on or off.  Default OFF
.cd DIRECTORY            Change the working directory to DIRECTORY
.changes on|off          Show number of rows changed by SQL
.check GLOB              Fail if output since .testcase does not match
.clone NEWDB             Clone data into NEWDB from the existing database
.connection [close] [#]  Open or close an auxiliary database connection
.databases               List names and files of attached databases
.dbconfig ?op? ?val?     List or change sqlite3_db_config() options
.dbinfo ?DB?             Show status information about the database
.dump ?OBJECTS?          Render database content as SQL
.echo on|off             Turn command echo on or off
.eqp on|off|full|...     Enable or disable automatic EXPLAIN QUERY PLAN
.excel                   Display the output of next command in spreadsheet
.exit ?CODE?             Exit this program with return-code CODE
.expert                  EXPERIMENTAL. Suggest indexes for queries
.explain ?on|off|auto?   Change the EXPLAIN formatting mode.  Default: auto
.filectrl CMD ...        Run various sqlite3_file_control() operations
.fullschema ?--indent?   Show schema and the content of sqlite_stat tables
.headers on|off          Turn display of headers on or off
.help ?-all? ?PATTERN?   Show help text for PATTERN
.import FILE TABLE       Import data from FILE into TABLE
.indexes ?TABLE?         Show names of indexes
.limit ?LIMIT? ?VAL?     Display or change the value of an SQLITE_LIMIT
.lint OPTIONS            Report potential schema issues.
.load FILE ?ENTRY?       Load an extension library
.log FILE|on|off         Turn logging on or off.  FILE can be stderr/stdout
.mode MODE ?OPTIONS?     Set output mode
.nonce STRING            Suspend safe mode for one command if nonce matches
.nullvalue STRING        Use STRING in place of NULL values
.once ?OPTIONS? ?FILE?   Output for the next SQL command only to FILE
.open ?OPTIONS? ?FILE?   Close existing database and reopen FILE
.output ?FILE?           Send output to FILE or stdout if FILE is omitted
.parameter CMD ...       Manage SQL parameter bindings
.print STRING...         Print literal STRING
.progress N              Invoke progress handler after every N opcodes
.prompt MAIN CONTINUE    Replace the standard prompts
.quit                    Stop interpreting input stream, exit if primary.
.read FILE               Read input from FILE or command output
.recover                 Recover as much data as possible from corrupt db.
.restore ?DB? FILE       Restore content of DB (default "main") from FILE
.save ?OPTIONS? FILE     Write database to FILE (an alias for .backup ...)
.scanstats on|off|est    Turn sqlite3_stmt_scanstatus() metrics on or off
.schema ?PATTERN?        Show the CREATE statements matching PATTERN
.separator COL ?ROW?     Change the column and row separators
.session ?NAME? CMD ...  Create or control sessions
.sha3sum ...             Compute a SHA3 hash of database content
.shell CMD ARGS...       Run CMD ARGS... in a system shell
.show                    Show the current values for various settings
.stats ?ARG?             Show stats or turn stats on or off
.system CMD ARGS...      Run CMD ARGS... in a system shell
.tables ?TABLE?          List names of tables matching LIKE pattern TABLE
.timeout MS              Try opening locked tables for MS milliseconds
.timer on|off            Turn SQL timer on or off
.trace ?OPTIONS?         Output each SQL statement as it is run
.version                 Show source, library and compiler versions
.vfsinfo ?AUX?           Information about the top-level VFS
.vfslist                 List all available VFSes
.vfsname ?AUX?           Print the name of the VFS stack
.width NUM1 NUM2 ...     Set minimum column widths for columnar output
sqlite> .tables # 查看表
Departments  Marks        Students     Tests
sqlite> .backup bak.db # 备份

注意数据库名区分大小写，没有DROP命令，直接删除文件即可删除数据库。

3表操作

创建表

CREATE TABLE table_name(
column1 datatype,
column1 datatype
);

删除表

DROP TABLE table_name;

修改表

ALTER TABLE table_name RENAME TO table_name2;

添加列

ALTER TABLE table_name ADD COLUMN Age INT;

可以使用 “.schema”查看字段， “.tables” 查看表。

INSERT

INSERT INTO Tablename(colname1, colname2, ….) VALUES(valu1, value2, ….);
INSERT INTO Tablename VALUES(value1, value2, ….); # 忽略列名
INSERT INTO Tablename DEFAULT VALUES; # 默认值插入，可能会报错。

4列约束

列约束对插入到列上的值执行约束和规则，以验证插入的数据。列的约束是在创建表时在列定义中定义的。

SQLite主键

主键列上的所有值都应该是唯一的，并且不为空。

主键可以只应用于一个列，也可以应用于列的组合，在后一种情况下，列的值的组合对所有表的行都应该是唯一的。

语法：

ColumnName INTEGER NOT NULL PRIMARY KEY; # 在列本身的定义中
PRIMARY KEY(ColumnName)； # 单独的定义：
PRIMARY KEY(ColumnName1, ColumnName2)；

非空约束

SQLite非空约束可以防止列有空值：

ColumnName INTEGER  NOT NULL;

默认约束

ColumnName INTEGER DEFAULT 0;

如果你写一个插入语句，而你没有为该列指定任何值，那么该列的值将为0。

SQLite UNIQUE 约束

防止列的所有值中出现重复的值。

例如：

EmployeeId INTEGER NOT NULL UNIQUE;

这将强制要求 "EmployeeId "的值是唯一的，不允许有重复的值。注意，这只适用于 "EmployeeId "列的值。

SQLite CHECK 约束

SQLite检查约束是检查插入值的条件，如果该值不符合条件将不会被插入。

Quantity INTEGER NOT NULL CHECK(Quantity > 10);

你不能在Quantity 列中插个小于10的值。

SQLite外键

SQLite外键验证表中的值是否存在于另一个表中。

在处理多个表的时候，当有两个表相互之间有一个共同的列时。如果你想确保在其中表中插入的值必须存在于另一个表的列中，那么你应该在这个共同的列上使用 "外键约束"。

在这种情况下，当你试图在该列上插入值时，那么外键将确保插入的值存在于该表的列中。

请注意，在SQLite中，外键约束不是默认启用的，你必须首先通过运行下面的命令来启用它们：

PRAGMA foreign_keys = ON;

从3.6.19版本开始，外键约束被引入SQLite中。

SQLite外键的例子
假设我们有两个表：Students 和Departments。

Students 表有学生的列表，Departments表有部门的列表。每个学生都属于一个部门，也就是说，每个学生都有一个departmentId列。

现在，我们将看到外键约束是如何帮助确保学生表中的部门ID值必须存在于部门表中的。

CREATE TABLE [Departments] (
	[DepartmentId] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
	[DepartmentName] NVARCHAR(50)  NULL
);
CREATE TABLE [Students] (
	[StudentId] INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
	[StudentName] NVARCHAR(50)  NULL,
	[DepartmentId] INTEGER  NOT NULL,
	[DateOfBirth] DATE  NULL,
	FOREIGN KEY(DepartmentId) REFERENCES Departments(DepartmentId)
);

为了检查外键约束如何防止在一个与另一个表有关系的表中插入未定义的元素或值，我们将看看下面的例子。

让我们在部门表中插入两个部门 "IT "和 "艺术"，如下所示：

INSERT INTO Departments VALUES(1, 'IT');
INSERT INTO Departments VALUES(2, 'Arts');

然后尝试插入一个新的学生，这个学生的departmentId在department表中并不存在：

INSERT INTO Students(StudentName,DepartmentId) VALUES('John', 5);

该行不会被插入，你会得到错误“ FOREIGN KEY constraint failed”

5数据类型

与其他数据库管理系统相比，SQLite中的数据类型是不同的。在SQLite中，你可以正常声明数据类型，但你仍然可以在任何数据类型中存储任何值。

SQLite的类型较少。没有数据类型，你可以在任何列中存储任何你喜欢的数据类型。这就是所谓的动态类型。

在静态类型中，像在其他数据库管理系统中，如果你声明了一个数据类型为整数的列，你只能插入数据类型为整数的值。然而，在像SQLite这样的动态类型中，列的类型是由插入的值决定的。然后SQLite会根据其类型来存储该值。

SQLite存储类

在SQLite中，根据值的类型有不同的存储方法，这些不同的存储方法在SQLite中称为存储类。

以下是SQLite中可用的存储类：

NULL - 这个存储类是用来存储任何NULL值的。
INTEGER - 任何数字值都被存储为有符号的整数值（它可以容纳正负整数值）。在SQLite中，INTEGER值被存储在1、2、3、4、6或8字节的存储空间中，这取决于数字的值。
REAL - 这个存储类用于存储浮点值，它们被存储在8字节的存储空间中。
TEXT - 存储文本字符串。它也支持不同的编码，如UTF-8, UTF-16 BE, 或 UTF-26LE。
BLOB - 用于存储大文件，如图像或文本文件。该值被存储为与输入值相同的字节数组。

SQLite亲和类型

类型亲和性是指在列中存储数据的推荐类型。然而，你仍然可以按照你的意愿存储任何类型的数据，这些类型是推荐的，不是必须的。

在SQLite中引入这些类型是为了最大限度地提高SQLite和其他数据库管理系统之间的兼容性。

在SQLite数据库中声明的任何列都会根据其声明的数据类型分配一个类型亲和力。下面是SQLite中类型亲和性的提升：

TEXT。
NUMERIC
INTEGER
REAL
BLOB

下面是SQLite如何从其声明的数据类型中确定列的亲和性：

如果声明的类型包含字符串 "INT"，则分配INTEGER亲和力。
如果列的数据类型包含以下字符串 "TEXT"，"CHAR "或 "CLOB "之一，则分配TEXT亲和力。例如，VARCHAR类型将被分配为TEXT亲和力。
如果列没有指定类型或者数据类型是BLOB，则分配BLOB亲和力。
如果类型包含以下字符串之一 "DOUB"，"REAL "或 "FLOAT"，则分配REAL亲和力。
对于任何其他数据类型，NUMERIC亲和力被分配。

在SQLite中存储数据类型的例子：

用SQLite integer存储数字：

任何数据类型的列包含 "INT "字样，它将被分配一个INTEGER类型的亲和力。它将被存储在一个INTEGER存储类中。

所有下列数据类型都被分配为INTEGER类型的亲和力：

int, integer, bigint.
int2, int4, int8.
tinyint, smallint, medium int.

在SQLite中的INTEGER类型亲和力可以容纳任何指定的整数（正数或负数），从1字节到最大8字节。

用SQLite REAL存储数字：

REAL数字是具有双浮点精度的数字。SQLite将实数存储为8字节的数组。下面是SQLite中可以用来存储实数的数据类型列表：

REAL
DOUBLE
DOUBLE PRECISION
FLOAT

用SQLite BLOB存储大数据：

只有一种方法可以将大文件存储到SQLite数据库中，那就是使用BLOB数据类型。这种数据类型用于存储大文件，如图像、文件（任何类型）等。文件被转换为字节数组，然后以与输入文件相同的大小存储。

存储SQLite布尔运算：

SQLite没有单独的BOOLEAN存储类。然而，BOOLEAN值被存储为INTEGERS，其值为0（为假）或1（为真）。

存储SQLite的日期和时间：

你可以使用以下数据类型之一在SQLite中声明日期或日期时间：

DATE
DATETIME
TIMESTAMP
TIME
注意，在SQLite中没有单独的DATE或DATETIME存储类。相反，任何用前述数据类型之一声明的值都会根据插入值的日期格式存储在一个存储类中，如下所示：
TEXT - 如果你插入的日期值是ISO8601字符串的格式（"YYYY-MM-DD HH:MM:SS.SSS"）。
REAL-- 如果你在朱利安日数字中插入日期值，即公元前4714年11月24日格林威治正午后的天数，那么日期值将被存储为REAL。
INTEGER作为Unix时间，自1970-01-01 00:00:00 UTC以来的秒数。