SQLite

参考:

数据类型

数据类型	说明
NULL	0
INTEGER	有符号整数，最多 8 字节
REAL	8字节浮点数
TEXT	字符，无限制
BLOB	二进制对象

命令

连接与配置

sqlite3 数据库名.db //创建或打开数据库

.open 数据库名.db //打开数据库
.mode column //设置显示模式为柱
.headers on //开启表头显示
.schema //查看数据库结构
.nullvalue NULL //将 NULL 值显示为指定字符串
.indices //查看索引信息

数据库操作

参照隔壁MySQL，这里记几个例子算了

创建表

create table students (
    id integer primary key autoincrement,  -- 添加自增属性
    name text not null,                   -- 姓名不能为空
    age integer check(age > 0),           -- 添加年龄检查约束
    score real check(score >= 0),         -- 添加分数检查约束
    created_at text default (datetime('now'))  -- 添加创建时间戳
);

改表

1
2
3

alter table students add email text;
alter table students rename to stu;
alter table stu rename column id to sid;

1 2	create table newstudent as select * from stu; -- 复制表结构和数据，但不复制约束 drop table stu;

加数据

1
2
3

insert into student values (1, '张三', 18, 90, '2023-01-01');
insert into student (name, age, score) values ('李四', 19, 85);
insert into student (name, age, score) select name, age, score from stu; -- 复制表数据

改数据

1	update student set score = 95 where name = '张三';

1	delete from student where sid = 1;

排序和分组

select * from student order by score desc limit 2;  -- 降序排列，显示前两条
select * from student order by score asc limit 2;  -- 升序排列，显示前两条
select * from student limit 5 offset 2;  -- 每页显示5条，跳2页，显示第3页
select * from student limit 2, 5;  -- 同上，与MySQL不同
select * from student order by score desc;
select age, count(*) from student group by age;
select age, count(*) from student where age > 18 group by age;
select age, sum(score) from student group by age having sum(score) > 200;

select info.id, info.name, info.age, info.score, dept.dname from info, dept where info.did = dept.did;  -- 连接查询(隐式内连接)
select info.id, info.name, info.age, info.score, dept.dname from info join dept on info.did = dept.did;  -- 内连接查询
select info.id, info.name, info.age, info.score, dept.dname from info left join dept on info.did = dept.did;  -- 左外连接查询
select info.id, info.name, info.age, info.score, dept.dname from info right join dept on info.did = dept.did;  -- 右外连接查询
select info.id, info.name, info.age, info.score, dept.dname from info full join dept on info.did = dept.did;  -- 全外连接查询

create view result as select info.id, info.name, info.age, info.score, dept.dname from info, dept where info.did = dept.did;  -- 虚拟表

事务

命令	说明
BEGIN	开启事务
ROLLBACK	回滚事务
COMMIT	提交事务
SAVEPOINT 名称	创建保存点
ROLLBACK TO 名称	回滚到指定保存点
RELEASE 名称	释放保存点

BEGIN;
INSERT INTO student (name, age, score) VALUES ('王五', 20, 92);
INSERT INTO student (name, age, score) VALUES ('赵六', 21, 88);
COMMIT;

函数

聚合函数

命令	说明
COUNT(列名)	统计行数
SUM(列名)	求和
AVG(列名)	求平均值
MAX(列名)	求最大值
MIN(列名)	求最小值

select count(*) from student;

select sum(score) from student;

select avg(score) from student;

select max(score) from student;

select min(score) from student;

文本函数

命令	说明
LENGTH(文本)	返回文本长度
UPPER(文本)	转换为大写
LOWER(文本)	转换为小写
SUBSTR(文本, 开始位置, 长度)	截取子字符串
REPLACE(文本, 旧文本, 新文本)	替换文本
TRIM(文本)	去掉首尾空格
CONCAT(文本1, 文本2, …)	连接文本
INSTR(文本, 子文本)	返回子文本位置
LEFT(文本, 长度)	从左侧截取指定长度
RIGHT(文本, 长度)	从右侧截取指定长度
REVERSE(文本)	反转文本
SUBSTRING(文本, 开始位置, 长度)	截取子字符串

select strftime('%Y-%m-%d %H:%M:%S', datetime('now','+8 hours')) as current_datetime;
select round(3.14159, 2) as rounded_value;

数值函数

命令	说明
ROUND(数值, 小数位数)	四舍五入
CEIL(数值)	向上取整
FLOOR(数值)	向下取整
ABS(数值)	取绝对值
SQRT(数值)	平方根
LOG(数值)	自然对数
EXP(数值)	指数函数

日期函数

命令	说明
STRFTIME(格式, 时间戳)	格式化时间
DATE(时间戳)	提取日期
TIME(时间戳)	提取时间
DATETIME(时间戳, 格式)	格式化日期时间
JULIANDAY(日期)	计算日期到儒略日的天数
STRFTIME(‘%Y-%m-%d %H:%M:%S’, DATETIME(‘now’, ‘+8 hours’))	获取当前时间

约束

约束	说明
PRIMARY KEY	主键，唯一且非空
NOT NULL	非空
UNIQUE	唯一
CHECK(表达式)	检查约束
DEFAULT 值	默认值
FOREIGN KEY	外键

触发器

触发器	说明
BEFORE	触发前
AFTER	触发后
FOR EACH ROW	行级触发器（一般默认）
WHEN 表达式	触发条件
DO 语句	触发语句
BEGIN	开始
END	结束
OLD.列名	旧值
NEW.列名	新值
RAISE EXCEPTION ‘错误信息’	触发异常

create trigger 触发器名 before/after insert/update/delete on table_name [for each row] [when (condition)] [begin ... end];

create trigger update_score before update on student begin set NEW.score = OLD.score + 1; end;

create trigger check_age before insert on student begin if NEW.age < 0 or NEW.age > 100 then raise exception '年龄必须在0到100之间'; end if; end;

create trigger deldtu after delete on student begin delete from info where info.id = OLD.id; end;

日志

create table info (
    id integer primary key autoincrement,
    name text,
    addr text
);

create table log (id integer primary key autoincrement, datetime text, info text);

create trigger log_insert after insert on info begin insert into log values (null, datetime('now', '+8 hours'), '新增记录：' || NEW.id || ' | ' || NEW.name || ' | ' || NEW.addr); end;

create trigger log_update after update on info begin insert into log values (null, datetime('now', '+8 hours'), '更新记录：' || OLD.id || ' | ' || OLD.name || ' | ' || OLD.addr || ' -> ' || NEW.id || ' | ' || NEW.name || ' | ' || NEW.addr); end;


create trigger log_delete after delete on info begin insert into log values (null, datetime('now', '+8 hours'), '删除记录：' || OLD.id || ' | ' || OLD.name || ' | ' || OLD.addr); end;