Master SQLite: Installation, GUI Tools, Data Types, and Essential Commands

Introduction

SQLite is a lightweight, server‑less database that requires no installation or complex configuration, making it ideal for beginners and small‑scale projects while supporting databases up to 2 TB.

1. Installation

Download SQLite from the provided URL, add the executable to your system PATH, and verify the installation by opening a command prompt and seeing the SQLite prompt. https://u062.com/dir/7715018-39794633-fbf2ca After adding SQLite to the PATH, you can run it directly from the command line.

2. Graphical Interface Management

For a more user‑friendly experience, use SQLiteAdmin as a GUI tool. Download the additional dynamic library required for parsing SQLite statements and place it in the program directory.

https://u062.com/file/7715018-452195159

3. Basic Syntax

1. Data Types

Null         空值
SMALLLINT    短整型
INTEGER      带符号的整数（布尔 0/1）
TINYINT      长整型
REAL         浮点值
TEXT         大量文本字符串
BLOB         二进制对象
NUMERIC      使用所有五个存储类的值
VARCHAR(n)   变长字符串（英文，最多8000字符）
CHAR(n)      固定长度字符串
DATE         年月日
TIME         时分秒
DOUBLE       双精度浮点数
FLOAT        单精度浮点数
TIMESTAMP    存储年月日时分秒
NCHAR        Unicode 固长字符串（最多4000字符）
NVARCHAR     Unicode 变长字符串
NTEXT        Unicode 大量文本字符串
DATETIME     日期时间

2. Basic Keywords

AND          与
BETWEEN      最小值和最大值之间
EXISTS       判断是否存在
IN           判断某个值是否属于另一个值
NOT IN       判断某个值是否不属于另一个值
LIKE         使用通配符比较值（% 表示零个或多个，_ 表示一个）
GLOB         使用通配符比较值（* 表示零个或多个，? 表示一个）
LIMIT        限制返回行数
ORDER BY     排序（ASC/DESC）
GROUP BY     数据聚合
HAVING       过滤分组（需配合 GROUP BY）
DISTINCT     消除重复记录
NOT          否定（常与 IN 配合）
OR           或
IS NULL      判断是否为空值
IS           等价于 =
IS NOT       判断不等于（!=）
UNIQUE       唯一约束
UNION        合并并消除重复行
UNION ALL    合并保留所有行
WHERE        条件过滤
SELECT       选择列
FROM         数据来源表
INSERT INTO  插入数据
PRIMARY KEY  主键
DEFAULT      默认值
CHECK        检查约束

4. Database Operations

1) Create a Database File

Create a file with a .db extension, either via the GUI or directly in the command prompt.

2) View Databases

.database   .databases

3) Exit SQLite

.quit

4) Export Database

sqlite3 test.db .dump > test.sql

5) Attach Additional Databases

ATTACH DATABASE 'test1.db' as 'test1';
ATTACH DATABASE 'test2.db' as 'test2';

6) List Attached Databases

.database   .databases

7) Detach a Database

DETACH DATABASE 'test1';

Conclusion

This article covered SQLite installation, a graphical management tool, common data types, essential keywords, and core database operations such as creating, viewing, exiting, exporting, attaching, and detaching databases, providing a solid foundation for further exploration of SQLite features.