SQLite是一种轻量级的嵌入式关系型数据库管理系统(RDBMS),它在单个文件中实现了一个完整的、自包含的、无服务器的SQL数据库引擎。与传统的数据库系统不同,SQLite没有独立的服务器进程,而是直接与应用程序进行交互,目前使用的是sqlite3。如果您学过其它基于SQL的数据库,那么学习sqlite将毫无压力。以下是SQLite的一些主要特点和优势:
嵌入式数据库: SQLite以静态库的形式提供,可以直接嵌入到应用程序中。它不需要单独的服务器进程或配置,使得部署和管理变得简单。
轻量级和高效: SQLite设计的目标之一是节省资源和提供高性能。它的核心库非常小巧,使用内存非常少,对磁盘空间的占用也较小。同时,SQLite使用精简的SQL语法和优化的查询执行算法,能够快速处理大量的数据。
跨平台兼容: SQLite支持跨多个操作系统和编程语言使用。它可以在各种操作系统(如Windows、Linux、macOS)上运行,并提供了对多种编程语言(如C/C++、Java、Python、Node.js)的良好支持。
无服务器架构: SQLite数据库以文件的形式存储在磁盘上,应用程序直接访问该文件即可。这种无服务器的架构简化了数据库管理和部署,减少了与网络连接和服务器配置相关的复杂性。
事务支持: SQLite支持完整的事务处理,包括原子性、一致性、隔离性和持久性(ACID属性)。这使得SQLite非常适合处理需要数据一致性和可靠性的应用程序。
丰富的功能: 尽管SQLite是轻量级的数据库,但它提供了许多高级功能,如复杂的查询、索引、触发器、视图、存储过程等。这些功能使得开发人员能够构建更复杂和功能强大的应用程序。
创建数据表
CREATE TABLE IF NOT EXISTS users ( id INTEGER PRIMARY KEY, name TEXT, age INTEGER )
数据类型
NULL:表示空值。
INTEGER:表示整数类型,可以存储整数数据。
REAL:表示浮点数类型,可以存储浮点数数据。
TEXT:表示文本字符串类型,可以存储字符、字符串和文本数据。
BLOB:表示二进制数据类型,可以存储任意二进制数据,如图像、音频或其他文件。
NUMERIC:表示数值类型,可以存储数字数据。在 SQLite 中,
NUMERIC
类型可以存储整数或浮点数数据。BOOLEAN:表示布尔类型,可以存储布尔值
TRUE
或FALSE
。注意,SQLite 并不直接支持布尔类型,但可以使用其他数据类型(如整数)来模拟布尔值的存储。DATE:表示日期类型,可以存储日期数据。
TIME:表示时间类型,可以存储时间数据。
DATETIME:表示日期和时间类型,可以存储日期和时间数据。
SQLite 的数据类型是动态的,即一个列可以在不同的行中存储不同的数据类型。SQLite 对数据类型的处理相对灵活,具体的数据类型解释和转换取决于存储的实际值。
请注意,SQLite 没有严格限制数据类型,允许在同一列中存储不同类型的数据。这种灵活性可以提供更大的自由度,但也需要开发者在应用程序中负责确保数据类型的一致性和正确性。
亲和类型
为了最大化 SQLite 和其它数据库引擎之间的兼容性,SQLite 支持列上的“亲和类型”概念。一个列的亲和类型是存储在该列的数据的推荐类型。在这里最重要的是,类型是被推荐的,而不是必须的。所有列仍可以存储任意类型的数据。这只是一些列,给出选择,将会使用一个存储类覆盖原先的。一个列首选的存储类称为它的“亲和类型”。SQLite 3 数据库中的每个列被分配了以下亲和类型之一:TEXT、INTEGER、NONE、REAL、NUMERIC
字段约束
PRIMARY KEY:指定字段为主键,唯一标识表中的每一行数据。
UNIQUE:确保字段的值在表中是唯一的。
NOT NULL:确保字段的值不为空,必须包含有效的数据。
CHECK:定义一个条件,确保字段的值满足指定的条件。
FOREIGN KEY:定义与其他表的关联关系,用于实现表之间的引用完整性。
DEFAULT:指定字段的默认值,在插入新行时使用该值,如果未提供值。
COLLATE:指定字符串比较和排序规则,用于对文本字段进行排序和比较操作。
AUTOINCREMENT:用于自动为字段生成唯一的递增整数值,适用于整数型主键。
这些字段约束允许你定义数据表的结构和数据的完整性规则。它们可以根据具体的需求和设计来组合使用,以确保数据的正确性和一致性。
以下是一个示例,演示了如何在 SQLite 中使用字段约束:
CREATE TABLE users ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER, email TEXT UNIQUE, password TEXT CHECK(length(password) >= 8), department_id INTEGER, FOREIGN KEY (department_id) REFERENCES departments(id) );
在上述示例中,users
表定义了几个字段和约束:
id
字段被指定为主键,唯一标识每一行数据。name
字段被指定为文本类型且不能为空。age
字段是一个整数,不带任何约束。email
字段是一个文本类型,要求唯一。password
字段是一个文本类型,并且使用CHECK
约束确保密码长度至少为8个字符。department_id
字段是一个整数,与departments
表的id
字段建立外键关系。删除数据表
DROP TABLE 数据表名;
SQLite子句
SQLite 支持的常见子句列表:
SELECT:用于从数据库中检索数据,指定要返回的列、表和条件等。
INSERT INTO:用于将新的行插入到表中,指定要插入的值和要插入的列。
UPDATE:用于更新表中现有行的数据,指定要更新的列、新的值和更新的条件。
DELETE FROM:用于从表中删除行,指定要删除的行和删除的条件。
CREATE TABLE:用于创建新的数据表,指定表的名称、列和约束等。
ALTER TABLE:用于修改现有数据表的结构,如添加、修改或删除列等。
DROP TABLE:用于删除现有的数据表,指定要删除的表的名称。
CREATE INDEX:用于创建索引,以加快查询性能,指定索引的名称、表和列等。
DROP INDEX:用于删除索引,指定要删除的索引的名称。
BEGIN / COMMIT / ROLLBACK:用于管理事务,即启动、提交或回滚数据库操作的组合。
WHERE:用于指定条件,限制返回的行满足特定的条件。
ORDER BY:用于指定结果集的排序顺序,可以按一个或多个列进行排序。
GROUP BY:用于将结果集按指定的列分组,通常与聚合函数(如SUM、AVG)一起使用。
HAVING:用于过滤分组后的结果集,指定分组的条件。
LIMIT:用于限制返回的行数,指定要返回的行数或限制结果集的偏移量和行数。
这些子句是在执行SQL查询和操作时常用的语句。它们允许你指定操作的范围、条件、排序、分组和限制等。根据具体的需求,可以组合使用这些子句来实现复杂的查询和数据操作。
聚合函数
SQLite 支持的常见聚合函数列表:
COUNT:用于计算符合条件的行数。
SUM:用于计算指定列的总和。
AVG:用于计算指定列的平均值。
MIN:用于找到指定列的最小值。
MAX:用于找到指定列的最大值。
GROUP_CONCAT:用于将指定列的值连接为一个字符串,并可选地使用分隔符进行分隔。
TOTAL:用于计算指定列的总和,包括NULL值。
AVG(rowid):用于估算平均行的大小。
COUNT(*):用于计算表中的总行数。
GROUP_CONCAT(DISTINCT column):用于将指定列的唯一值连接为一个字符串。
这些聚合函数可以用于在查询结果中计算汇总信息,如计算行数、总和、平均值、最小值、最大值等。通过结合其他子句(如SELECT、GROUP BY、HAVING),可以更灵活地对数据进行聚合和分析。
索引
普通索引
创建索引可以使用 CREATE INDEX
语句来创建,这里说一种比较简单的在创建表的时候创建索引的方法:
CREATE TABLE employees ( id INTEGER PRIMARY KEY, name TEXT, age INTEGER, INDEX idx_name (name) );
上述语句创建了一个名为 employees
的表,其中包含 id
、name
和 age
列。它还定义了一个名为 idx_name
的索引,该索引包含 name
列。
唯一索引
要在创建表时定义唯一索引,可以使用 SQLite 中的内联索引方式。下面是一个示例:
CREATE TABLE table_name ( column1 datatype, column2 datatype, ... CONSTRAINT constraint_name UNIQUE (column1, column2, ...) );
在上述示例中,你需要将 table_name
替换为要创建的表的名称,column1, column2, ...
替换为要包含在唯一索引中的列的名称,并为约束提供一个名称 constraint_name
。
例如,假设你要创建一个名为 employees
的表,其中包含 id
、name
和 email
列,并为 email
列创建唯一索引,可以使用以下语句:
CREATE TABLE employees ( id INTEGER PRIMARY KEY, name TEXT, email TEXT, CONSTRAINT idx_email UNIQUE (email) );
上述语句创建了一个名为 employees
的表,其中包含 id
、name
和 email
列。它还定义了一个名为 idx_email
的唯一索引,该索引包含 email
列。
请注意,唯一索引将确保在列或列组合中的每个值都是唯一的。这意味着在插入或更新数据时,数据库将强制执行唯一性约束,并拒绝具有重复值的行。