Lian
文章目录 站点概览

Lian

make thing better and simpler.

95 文章
64 标签

SQLite简单使用手册

发布于 2023-01-30 | 标签: sqlite 、 database | 13分钟 | 2745字数

前言

使用环境

os: win10, 64bit pro

SQLite version: 3.38.2

一. 数据类型

虽然SQLite和MySQL一样在名字上有SQL, 但是SQLite在很多方面和MySQL有所差异

SQLite原生支持5中数据类型: NULL, INTEGER, REAL, TEXT, BLOB. 在SQLite中, 所有数据最终都转化为该5中类型进行存储.

存储类 描述
NULL 值是一个 NULL 值。
INTEGER 值是一个带符号的整数，根据值的大小存储在 1、2、3、4、6 或 8 字节中。
REAL 值是一个浮点值，存储为 8 字节的 IEEE 浮点数字。
TEXT 值是一个文本字符串，使用数据库编码（UTF-8、UTF-16BE 或 UTF-16LE）存储。
BLOB 值是一个 blob 数据，完全根据它的输入存储。

1.1 SQLite 亲和(Affinity)类型

SQLite支持列的亲和类型概念。任何列仍然可以存储任何类型的数据，当数据插入时，该字段的数据将会优先采用亲缘类型作为该值的存储方式。SQLite目前的版本支持以下五种亲缘类型：

亲和类型 描述
TEXT 数值型数据在被插入之前，需要先被转换为文本格式，之后再插入到目标字段中。
NUMERIC 当文本数据被插入到亲缘性为NUMERIC的字段中时，如果转换操作不会导致数据信息丢失以及完全可逆，那么SQLite就会将该文本数据转换为INTEGER或REAL类型的数据，如果转换失败，SQLite仍会以TEXT方式存储该数据。对于NULL或BLOB类型的新数据，SQLite将不做任何转换，直接以NULL或BLOB的方式存储该数据。需要额外说明的是，对于浮点格式的常量文本，如"30000.0"，如果该值可以转换为INTEGER同时又不会丢失数值信息，那么SQLite就会将其转换为INTEGER的存储方式。
INTEGER 对于亲缘类型为INTEGER的字段，其规则等同于NUMERIC，唯一差别是在执行CAST表达式时。
REAL 其规则基本等同于NUMERIC，唯一的差别是不会将"30000.0"这样的文本数据转换为INTEGER存储方式。
NONE 不做任何的转换，直接以该数据所属的数据类型进行存储。

1.2 SQLite 亲和类型(Affinity)及类型名称

下表列出了当创建 SQLite3 表时可使用的各种数据类型名称，同时也显示了相应的亲和类型：

数据类型 亲和类型
INTINTEGER INTEGER
SMALLINT INTEGER
TINYINT INTEGER
MEDIUMINT INTEGER
BIGINT INTEGER
UNSIGNED INTEGER
BIGINT INTEGER
INT2 INTEGER
INT8 INTEGER
CHARACTER(20) TEXT
VARCHAR(255) TEXT
VARYING CHARACTER(255) TEXT
NCHAR(55) TEXT
NATIVE CHARACTER(70) TEXT
TEXTCLOB TEXT
NVARCHAR(100) TEXT
BLOB NONE
no datatype specified BLOB
REAL REAL
FLOAT REAL
DOUBLE PRECISION REAL
DOUBLE REAL
NUMERIC NUMERIC
DECIMAL(10,5) NUMERIC
BOOLEAN NUMERIC
DATETIME NUMERIC
DATE NUMERIC

1.3 Boolean 数据类型

SQLite 没有单独的 Boolean 存储类。相反，布尔值被存储为整数 0(false) 和 1(true).

注: MySQL当中同样也没有布尔类型的数据, 一般使用tinyint(1)作为布尔值的代替.

1.4 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> .help

.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
从文本中导入数据表
.imposter INDEX TABLE    Create imposter table TABLE on index INDEX
创建一个"伪装"表的表在index的index
.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|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                    Exit this program
退出程序
.read FILE               Read input from FILE or command output
.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        Turn sqlite3_stmt_scanstatus() metrics on or off
.schema ?PATTERN?        Show the CREATE statements matching PATTERN
查看创建表的语句
.selftest ?OPTIONS?      Run tests defined in the SELFTEST table
.separator COL ?ROW?     Change the column and row separators
.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
.testcase NAME           Begin redirecting output to 'testcase-out.txt'
.testctrl CMD ...        Run various sqlite3_test_control() operations
.timeout MS              Try opening locked tables for MS milliseconds
锁定表若干毫秒
.timer on|off            Turn SQL timer on or off
关闭sql计时器
.trace ?OPTIONS?         Output each SQL statement as it is run
.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

连接本地数据库时

.open "E:/Calibre Library/metadata.db"
# 不支持'\'符号

PRAGMA table_info(table_name);
# 查看表的字段的情况
.show
        echo: off
         eqp: off
     explain: auto
     headers: off
        mode: list
   nullvalue: ""
      output: stdout
colseparator: "|"
rowseparator: "\n"
       stats: off
       width:
    filename: E:/Calibre Library/metadata.db

# 查看创建表的语句
.schema table_name

2.1 PRAGMA

SQLite 的 pragma命令是一个特殊的命令, SQLite环境内可以用来控制各种环境变量和状态标志. 可以读取一个pragma值, 也可以根据需求设置.

语法

要查询当前的 PRAGMA 值，只需要提供该 pragma 的名字

PRAGMA pragma_name;

要为 PRAGMA 设置一个新的值，语法如下

PRAGMA pragma_name = value;

设置模式，可以是名称或等值的整数，但返回的值将始终是一个整数

pragma 说明
auto_vacuum 获取或设置 auto-vacuum 模式
cache_size 可获取或暂时设置在内存中页面缓存的最大尺寸
case_sensitive_like 控制内置的 LIKE 表达式的大小写敏感度
count_changes 获取或设置数据操作语句的返回值
database_list 用于列出了所有的数据库连接
data_version 数据库版本
encoding 控制字符串如何编码及存储在数据库文件中
foreign_key_check 外键检查
freelist_count 返回一个整数，表示当前被标记为免费和可用的数据库页数
index_info 返回关于数据库索引的信息
index_list 列出所有与表相关联的索引
journal_mode 获取或设置控制日志文件如何存储和处理的日志模式
max_page_count 为数据库获取或设置允许的最大页数
page_count 返回当前数据库中的页数量
page_size 获取或设置数据库中页的大小
parser_trace 随着它解析 SQL 命令来控制打印的调试状态
recursive_triggers 获取或设置递归触发器功能
schema_version 获取或设置存储在数据库头中的的架构版本值
secure_delete 用来控制内容是如何从数据库中删除
sql_trace 用于把 SQL 跟踪结果转储到屏幕上
synchronous 获取或设置当前磁盘的同步模式
temp_store 获取或设置临时数据库文件所使用的存储模式
temp_store_directory 获取或设置用于临时数据库文件的位置
user_version 获取或设置存储在数据库头的用户自定义的版本值
writable_schema 获取或设置是否能够修改系统表

2.2 更新数据

注意语法上的差异: 这里的更新数据指的是, 数据存在则更新, 不存在则写入.

这项功能再MySQL上是使用 ON DUPLICATE KEY UPDATE(当然也可以使用select返回结果判断间接实现)

How to do INSERT with an UPDATE on duplicate using SQLite

How to do an UPDATE from an INSERT with SQLite when there is a duplicate key value.

With SQLite you cannot do the simple MySQL INSERT on duplicate key UPDATE:

INSERT INTO table (id, name, price, quantity) VALUES**(1, 'test', '2.50', 164)**

​ ON DUPLICATE KEY UPDATE quantity = 164, price = '2.50'

Instead, you have to do what is called an upsert.

The concept is very similar to the MySQL example above. The differences being you have to specify which column is the indexed/key column (unique) and then state the DO UPDATE:

INSERT INTO users**(username,score)** VALUES**('Johnny', 388)**

ON CONFLICT**(username)** DO UPDATE SET score = '388';

注: 这里可以插入更新的数据, 同时可以更新指定的数据

Had you need to update multiple columns simply separate each update instance with a comma:

DO UPDATE SET score = '388', rating = 'A';

From the example if a row already has the value “Johnny” in the name column then the score column value will be updated to be 388.

三. Python中的使用

SQLite is a C library that provides a lightweight disk-based database that doesn’t require a separate server process and allows accessing the database using a nonstandard variant of the SQL query language. Some applications can use SQLite for internal data storage. It’s also possible to prototype an application using SQLite and then port the code to a larger database such as PostgreSQL or Oracle.

在使用上和MySQL_connector类似, sqlite3是python的内置库, 不需要额外安装.

import sqlite3 as sqlite
# 连接, 如果数据库尚未创建, 会自动创建
sql = sqlite.connect('test.db')
# 创建游标
cursor = sql.cursor()
# 事务, 和MySQL类似, 执行是在事务中执行的
sql.commit()
# 关闭, 关闭时, 未执行的事务会自动提交?
cursor.close()
sql.close()
# 其他诸如, 数据查询返回, 和MySQL都是类似的
cursor.fetchall()

需要注意的是: 批量插入数据中的cmd语句占位符号使用的 "?", 而不是像MySQL使用的是 "%s"

position
cursor.executemany(cmd, data_list)
# sqlite # database
Python坑-两种不同二维列表生成的差异 上一篇
Python关于路径 & 文本读取 下一篇
已运行:
浏览数: 次 | 访客数: 人
Powered by Kyouichirou