SQLite3基本介绍与常用语句汇总(最新整理)

sqlite3 mydatabase.db

select name from sqlite_master where type='table';

.tables

pragma table_info(table_name);

pragma table_info(users);

cid | name  | type    | notnull | dflt_value | pk
----+-------+---------+---------+------------+----
0   | id    | integer | 0       | null       | 1
1   | name  | text    | 0       | null       | 0
2   | age   | integer | 0       | null       | 0

create table table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
);

create table users (
    id integer primary key,
    name text not null,
    age integer
);

sqlite> .schema users
create table users (
    id integer primary key,
    name text not null,
    age integer
);

#修改表名
alter table table_name rename to new_table_name;
#新增列
alter table table_name add column column_def;

alter table users add column email text;
sqlite> .schema users
create table users (
    id integer primary key,
    name text not null,
    age integer,
    email text
);

drop table [if exists] table_name;

drop table if exists users;

create table new_table as select * from old_table;

create table new_table as select * from old_table where 0;

insert into table_name (column1, column2, ...) values (value1, value2, ...);

insert into table_name values (value1, value2, ...);

insert info uesrs (id, name, age, email) values (1, "alice", 25, "a@.com");
sqlite> select * from users;
id | name  | age | email
---+-------+-----+--------------------
1  | alice | 25  | a@.com

update table_name set column1 = value1, column2 = value2, ... where condition;

update users set age = 26 whrer id = 1;
sqlite> select * from users;
id | name  | age | email
---+-------+-----+--------------------
1  | alice | 26  | a@.com

delete from table_name where condition;
注意：如果不加 where，会删除整张表的数据

sqlite> select * from users;
id | name  | age | email
---+-------+-----+--------------------
1  | alice | 26  | a@.com
delete from users where id = 1;
sqlite> select * from users;
-- 空表，无结果

select column1, column2, ... from table_name [where ...] [order by ...] [limit ...];

select column1, column2 from table_name where condition;

select * from users where age > 25;
id | name  | age | email
---+-------+-----+---------------------
2  | bob   | 30  | bob@example.com
3  | carol | 28  | carol@example.com

select * from table_name order by column [asc|desc];

select * from users order by age desc;
id | name  | age | email
---+-------+-----+---------------------
2  | bob   | 30  | bob@example.com
3  | carol | 28  | carol@example.com
1  | alice | 25  | alice@example.com

select group_column, aggregate_function(...) from table_name group by group_column [having condition];

select age, count(*) from users group by age having count(*) > 1;
假设有两名用户都 30 岁
age | count(*)
----+----------
30  | 2

select columns from table1
join table2 on table1.column = table2.column;

表users
id | name  | age | email
---+-------+-----+---------------------
2  | bob   | 30  | bob@example.com
3  | carol | 28  | carol@example.com
1  | alice | 25  | alice@example.com
表orders
user_id | amount
--------+--------
2       | 100
3       | 150
select users.name, orders.amount
from users
join orders on users.id = orders.user_id;
-- 输出：
name  | amount
------+--------
bob   | 100
carol | 150
 

select * from table where column in (select ... from ... where ...);

select name from users where id in (select user_id from orders where amount > 100);
输出：
name
-----
carol

select * from table_name limit 限制行数 offset 起始行偏移量;
指令说明
   limit：限制最多返回多少行结果。
   offset：跳过前面多少行数据再开始返回（可选）。

select * from users order by id limit 10 offset 0;   -- 第1页
select * from users order by id limit 10 offset 10;  -- 第2页
select * from users order by id limit 10 offset 20;  -- 第3页

limit 每页条数 offset (页码 - 1) * 每页条数

原始数据为：
id | name  | age
---+-------+-----
1  | alice | 25
2  | bob   | 30
3  | carol | 28
select * from users order by id limit 2 offset 1;
按 id 排序后，跳过第1条数据，从第2条开始取，最多取2条。
执行结果：
id | name  | age
---+-------+-----
2  | bob   | 30
3  | carol | 28

select * from users where age > 25;

select * from users order by name;

select * from users join orders on users.id = orders.user_id;

create [unique] index index_name on table_name(column_name);
unique 表示不允许重复值（可选）。

create index idx_users_age on users(age);
#查看是否命中索引
explain query plan select * from users where age > 25;
#输出
search table users using index idx_users_age (age>?)
说明查询使用了你创建的索引。

create index index_name on table_name(column1, column2, ...);

select * from users where name = 'alice' and age = 25;
#查看是否命中索引
explain query plan select * from users where name = 'alice' and age = 25;
#输出
search table users using index idx_users_name_age (name=? and age=?)

id | name  | age
---+-------+-----
1  | alice | 25
2  | bob   | 30
3  | carol | 28
#创建复合索引
create index idx_name_age on users(name, age);

explain query plan select * from users where name = 'alice';
search table users using index idx_name_age (name=?)

explain query plan select * from users where name = 'alice' and age = 30;
search table users using index idx_name_age (name=? and age=?)

explain query plan select * from users where age = 30;
scan table users

explain query plan select * from users where age = 30 and name = 'alice';
search table users using index idx_name_age (age=? and name=?)

drop index [if exists] index_name;

begin;

commit;

begin;
insert into users (name, age, city) values ('eve', 40, 'chengdu');
update users set age = 45 where name = 'alice';
commit;

rollback;

begin;
insert into users (name, age, city) values ('eve', 40, 'chengdu');
update users set age = 45 where name = 'alice';
-- 假设此时发生了错误，我们决定回滚事务
rollback;

try {
        executeorthrow(db, "begin;");
        executeorthrow(db, "update accounts set balance = balance - 100 where account_id = 'a001';");
        // 故意出错：列名 balxxx 不存在
        executeorthrow(db, "update accounts set balxxx = balance + 100 where account_id = 'a002';");
        executeorthrow(db, "insert into transactions (from_account, to_account, amount) values ('a001', 'a002', 100);");
        //没问题就提交
        executeorthrow(db, "commit;");
        std::cout << "transaction committed.\n";
    } catch (const std::exception& ex) {
        std::cerr << ex.what() << "\n";
        //出错回滚
        sqlite3_exec(db, "rollback;", nullptr, nullptr, nullptr);
        std::cout << "transaction rolled back.\n";
    }

-- 查询最近30天订单金额前10的用户
select * from top_users_last_30_days;

-- 直接查视图
select * from user_purchases_summary where total_spent > 1000;

create [temp | temporary] view view_name as
select ...;
view_name：视图名称
select ...：视图对应的查询语句
temp：可选，创建临时视图，仅在当前连接中可见

create view active_users as
select id, name, email
from users
where status = 'active';

select * from active_users;

假设 users 表如下：
| id | name  | email                                   | status   |
| -- | ----- | --------------------------------------- | -------- |
| 1  | alice | [alice@mail.com](mailto:alice@mail.com) | active   |
| 2  | bob   | [bob@mail.com](mailto:bob@mail.com)     | inactive |
| 3  | carol | [carol@mail.com](mailto:carol@mail.com) | active   |
那么 active_users 视图返回：
| id | name  | email                                   |
| -- | ----- | --------------------------------------- |
| 1  | alice | [alice@mail.com](mailto:alice@mail.com) |
| 3  | carol | [carol@mail.com](mailto:carol@mail.com) |

drop view active_users;

create temp table temp_summary as
select user_id, sum(amount) as total
from orders
group by user_id;

-- 避免重复 join 操作，提高整体查询速度
select * from temp_result where score > 80;

create temp table temp_table_name (
    column1 type,
    column2 type,
    ...
);

create temp table temp_orders (
    id integer,
    product text,
    quantity integer
);
insert into temp_orders values (1, 'book', 2);
insert into temp_orders values (2, 'pen', 5);
select * from temp_orders;
查询结果（临时表内容）：
| id | product | quantity |
| -- | ------- | -------- |
| 1  | book    | 2        |
| 2  | pen     | 5        |

-- 创建客户表
create table customers (
    id integer primary key,           -- 客户 id，主键
    name text,                        -- 客户名称
    email text                        -- 客户邮箱
);
-- 创建订单表
create table orders (
    id integer primary key,           -- 订单 id，主键
    customer_id integer,              -- 关联客户 id
    product_name text,                -- 商品名称
    price real,                       -- 商品单价
    quantity integer,                 -- 购买数量
    order_date text,                  -- 下单时间（格式：yyyy-mm-dd hh:mm:ss）
    foreign key (customer_id) references customers(id)  -- 外键关联客户表
);

-- 插入客户
insert into customers (id, name, email) values
(1, 'alice', 'alice@example.com'),
(2, 'bob', 'bob@example.net'),
(3, 'charlie', 'charlie@example.org');
-- 插入订单
insert into orders (customer_id, product_name, price, quantity, order_date) values
(1, 'laptop', 899.99, 1, '2025-01-15 10:00:00'),
(1, 'mouse', 19.99, 2, '2025-02-10 12:30:00'),
(2, 'keyboard', 49.99, 1, '2025-03-05 14:20:00'),
(2, 'monitor', 199.99, 1, '2025-03-06 15:10:00'),
(2, 'usb cable', 9.99, 3, '2025-04-01 09:00:00'),
(3, 'desk chair', 129.99, 1, '2025-01-22 16:00:00');

select
    c.name as customer_name,  -- 客户名称
    upper(substr(c.email, 1, instr(c.email, '@') - 1)) as email_user,  
        -- 提取 email @ 前部分并转为大写
    count(o.id) as total_orders,  -- 订单总数
    sum(o.price * o.quantity) as total_spent,  -- 总消费金额
    round(avg(o.price * o.quantity), 2) as avg_order_value,  -- 平均订单金额（保留2位小数）
    max(o.order_date) as last_order_date,  -- 最后一笔订单的时间
    strftime('%y-%m', o.order_date) as order_month,  -- 订单月份（用于聚合）
    -- 消费金额区间分级：vip / gold / regular
    case 
        when sum(o.price * o.quantity) > 1000 then 'vip'
        when sum(o.price * o.quantity) > 500 then 'gold'
        else 'regular'
    end as customer_level
from
    customers c
left join
    orders o on c.id = o.customer_id  -- 关联订单表
where
    o.order_date >= date('now', '-6 months')  -- 仅查询最近6个月的订单
group by
    c.id
having
    total_orders > 0  -- 排除没有订单的客户
order by
    total_spent desc  -- 按总消费金额降序排列
limit 10;  -- 仅显示前10个客户

-- 普通自增主键
create table users (
    id integer primary key,
    name text
);
-- 带 autoincrement 的主键
create table logs (
    id integer primary key autoincrement,
    message text
);

-- 默认带有 rowid
create table cities (
    name text primary key,
    population integer
);
-- 不使用 rowid
create table cities_norowid (
    name text primary key,
    population integer
) without rowid;