SQLite 备忘清单 & sqlite cheatsheet & Quick Reference

SQLite 备忘清单

本备忘单旨在快速理解 SQLite 所涉及的主要概念，提供了最常用的SQL语句，供您参考。

入门

介绍

SQLite 是一个轻量级的嵌入式关系数据库管理系统，遵循 ACID 原则，广泛用于浏览器、操作系统等应用中，实现本地数据存储。

安装

windows

  • 从 SQLite 下载两个压缩文件：sqlite-tools-win32-*.zip、sqlite-dll-win32-*.zip
  • 创建文件夹 C:\sqlite，将这两个压缩文件解压到该文件夹下。
  • 解压后，您将看到 3 个文件： sqlite3.def、 sqlite3.dll、 sqlite3.exe
  • 将 C:\sqlite 添加到 PATH 环境变量中，以便在命令行中使用 SQLite。

linux

linux 自带 sqlite3，或者通过 apt-get/yum/brew 等安装。

macOS

brew install sqlite 安装

连接 SQLite 数据库

SQLite 通常无需复杂配置，当指定的数据库文件不存在时，它会自动创建一个新文件。

sqlite3 mydatabase.db

若数据库文件不存在则会自动创建

数据库操作

显示数据库名称及对应文件

sqlite> .databases
main: /home/user/sqlite/database.db r/w

备份数据库

sqlite> .backup back

显示已经设置的值

sqlite> .show 
        echo: off
         eqp: off
     explain: auto
     headers: off
        mode: list
   nullvalue: ""
      output: stdout
colseparator: "|"
rowseparator: "\n"
       stats: off
       width:
    filename: api.db

备份单张表

sqlite> .dump user

退出

sqlite> .exit

以 sql 的形式 dump 数据库

sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE api (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    host TEXT NOT NULL,
    port INTEGER NOT NULL,
    path TEXT NOT NULL
);
INSERT INTO api VALUES(1,'example.com',8080,'/api/v1');

导入与导出数据库

导出数据库

sqlite3 mydatabase.db .dump > backup.sql

导入数据库

sqlite3 mydatabase.db < backup.sql

输出模式设置

设置输出模式为 csv

sqlite> .mode csv
sqlite> select * from api;
id,host,port,path
1,example.com,8080,/api/v1

输出为 markdown

sqlite> select * from api;
| id |      host       | port |  path   |
|----|-----------------|------|---------|
| 1  | example.com     | 8080 | /api/v1 |

支持 ascii box column csv html insert json line list markdown qbox quote table tabs tcl 等类型

数据表操作

常用表操作

创建表

sqlite> create table user(id integer primary key, name text);

查看所有表

sqlite> .tables

查看表结构

sqlite> .schema user

导入文件到表中

sqlite> .import user.csv user

设置查询显示列名称

sqlite> .head on

常用 SQL

-- 创建表
create table user(id integer primary key, name text);

-- 删除表
drop table user;

-- 重命名表
alter table user rename to user_new;

-- 插入
-- 单条
insert into user(name) values('test');
-- 多条
insert into user(name) values('test1'),('test2');

-- 查询
select * from user;
-- 去重查询
select distinct name from user;
-- 统计
select count(id) from user;
-- limit
select * from user limit 2;
-- 条件查询
select * from user where id > 1;
-- 模糊查询
select * from user where name like '%test%';
-- group by
select name, count(id) from user group by name;
-- 排序
select * from user order by id desc;
-- 聚合函数
select max(id) from user;

-- 更新
update user set name='test3' where id=1;

-- 删除
delete from user where id=1;

事务支持

事务具有原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)四个标准属性，缩写为 ACID。

-- 开始事务
begin transaction;
-- 操作
update user set name='test4' where id=1;
-- 回滚
rollback;
-- 提交
commit;

命令行帮助

在命令行中通过 .help 命令显示帮助文档

另见