sqlite3

sqlite3

SQLite简介

百度百科-SQLite：https://baike.baidu.com/item/SQLite

命令使用说明

# 系统终端命令：
sqlite3/sqlite -version         # 查看sqlite3版本信息
sqlite3/sqlite database_name.db # 创建或打开database_name.db文件并进入sqlite3终端

# sqlite3终端命令：
sqlite> .database          # 查看数据库文件信息命令
sqlite> .schema            # 查看所有表创建语句
sqlite> .schema table_name # 查看指定表创建语句
sqlite> .dump table_name   # 以SQL语句形式列出表内容
sqlite> .help              # 显示帮助信息
sqlite> .quit/.exit        # 退出sqlite3终端

命令使用

sqlite3 /mnt/etc/data_files.db # 创建或打开/mnt/etc/data_files.db文件并进入sqlite3终端

SQL语句

查询数据记录

语句

select * from table_name;

SELECT * FROM table_name;

使用

SELECT * FROM files;

SELECT * FROM files WHERE trans_sta = 1 AND rec_sta = 2 AND active_upload = 1 ORDER BY mtime DESC, name DESC LIMIT 1;

UPDATE files SET active_upload = 2 WHERE trans_sta = 1 AND rec_sta = 2 AND active_upload = 1 ORDER BY mtime DESC, name DESC LIMIT 1; # Error: near "ORDER": syntax error

UPDATE files SET active_upload = 2 WHERE name = "file_name_2.dat";
UPDATE files SET active_upload = 1 WHERE name = "file_name_2.dat";

增加数据记录

语句

insert into table_name (field1, field2, ...) values (val1, val2, ...);

INSERT INTO table_name (field1, field2, ...) VALUES (val1, val2, ...);

insert into table_name values (val1, val2, ...);

INSERT INTO table_name VALUES (val1, val2, ...);

使用

数据记录示例：

file_name.dat|1645163092|1|2|1|2da4175796f8a7e74d87c5bd27dcdb73|1|0|1

# 每次执行单条语句
INSERT INTO files VALUES ("file_name_0.dat", 1645163100, 1, 2, 1, "4325d0dbf1ef8b62974c61bcbb569134", 1, 0, 1);
INSERT INTO files VALUES ("file_name_1.dat", 1645163200, 1, 2, 1, "3a3d38458bef119e75a29631475622c3", 1, 0, 1);
INSERT INTO files VALUES ("file_name_2.dat", 1645163300, 1, 2, 1, "0f18e78731bcbd9e3dcdd4869bafb884", 1, 0, 1);

# 一次执行多条语句
INSERT INTO files VALUES ("file_name_0.dat", 1645163100, 1, 2, 1, "4325d0dbf1ef8b62974c61bcbb569134", 1, 0, 1);INSERT INTO file VALUES ("file_name_1.dat", 1645163200, 1, 2, 1, "3a3d38458bef119e75a29631475622c3", 1, 0, 1);INSERT INTO file VALUES ("file_name_2.dat", 1645163300, 1, 2, 1, "0f18e78731bcbd9e3dcdd4869bafb884", 1, 0, 1);

删除数据记录

语句

delete from table_name [where expression];

DELETE FROM table_name [WHERE expression];

使用

DELETE FROM files WHERE md5 = "a";