sqlite使用简介

1. sqlite 简介

• 参考：https://docs.python.org/3.7/library/sqlite3.html?highlight=sqlite#module-sqlite3
• 参考：https://www.runoob.com/sqlite/sqlite-python.html
  数据库的基本操作是：建表，写入，查找，读出，删除；

1.1 命令行下操作sqlite

1.1.1 命令行下打开数据库文件

• 首先将sqlite3加到环境变量；
• 在数据库文件路径下，打开命令行；输入sqlite3，返回如下：
  SQLite version 3.23.1 2018-04-10 17:39:29
  Enter “.help” for usage hints.
  Connected to a transient in-memory database.
  Use “.open FILENAME” to reopen on a persistent database.
• 如上提示，输入命令：“.open xxx.db” 回车，就能打开数据库；如果不存在xxx.db文件则会创建该数据库文件；
• 输入“.databases” 可以看到当前打开的数据库的路径和名字；
• 输入“.table”可以看到当前数据库中含有的表；
• 输入“select * from table_name;”；回车，可以看都当前表含有的所有数据；

1.1.2 导出数据库文件dump 命令

使用.dump命令

sqlite3 testDB.db .dump > testDB.txt # 创建testDB.db并导出为testDB.txt
sqlite3 testDB.db < testDB.txt # 创建testDB.db 并将testDB.txt内容导入到数据库

1.1.3 创建表格 creat table

在命令行下输入如下命令，每输完一行都按一下回车键换行

creat table company(
id int primary key not null,
name text not null,
age int not null,
address char(50),
salary real
);

1.1.4 查看数据库的表格情况

• .tables # 查看数据库中表格的数量
• .schema company # 查看company这个表格的数据类型

1.1.5 更改表格里的数据

UPDATE historyData SET temperature = 33.8 WHERE date like '20221123' AND time like '141708';

上面命令意思是：将historyData表中，根据条件date列的值是’20221123’并且time列是’141708’，筛选出的那几行中的temperature列的值设置为33.8。
简单理解，就是将historyData表中特定的几行中temperature列的值改为某个值。

1.1.6 查找数据

select rowid,* from historyData where date = '20221205' and time like '%13'
select rowid,* from historyData where date = '20221205' and time like '%13%'
select rowid,* from historyData where date = '20221205' and time like '13%'

第一条是指，查找historyData表格中date列的值是字符串‘20221205’且time列的值是以字符串‘13’结尾的所有记录；
第二条是指，查找historyData表格中date列的值是字符串‘20221205’且time列的值包含字符串‘13’的所有记录；
第三条是指，查找historyData表格中date列的值是字符串‘20221205’且time列的值是以字符串‘13’开头的所有记录；

1.1.7 删除数据

delete from tableName
delete from tableName condition
DELETE FROM tableName WHERE ROWID IN  (SELECT ROWID FROM tableName ORDER BY ROWID limit (select count(*) -100 from tableName ));
delete from paramConfig where rowid not in (select MAX(rowid) from paramConfig group by datetime);

• 1. 删除tableName列表的所有数据；
• 2. 删除tableName列表满足condition条件的数据；
• 3. 保留后100条数据，删除tableName列表其他数据；需要注意，因为删除了前面的数据，删除后rowid就不是从1开始了。
• 4.删除paramConfig 表格中重复的记录

1.2 sqlite主键的特点

• 主键声明的关键字是primary key；
• 主键的值在表格中不能重复，即插入相同的值会失败和报错；
• 一个表格中只有一个主键；
• 只有主键可以实现自增；
• 主键写null值或者不写值就会自增；
• 主键不设置autoincrement方式也会自增；
• 主键不设置not null也不能写null值，因为写null值会自增；
• 拥有自增变量的表格时(建立表格时用了关键字autoincrement)，数据库会建立一个sqlite_sequence表格，用来记录各个表格中自增变量的值。

sqlite_sequence表格的格式如下

name	seq
table1_name	主键自增变量值
table2_name	主键自增变量值

主键声明方式

"id integer primary key autoincrement not null" // 意思是将id声明为整形主键且可以自增和非null；
"id integer primary key autoincrement" //笔者注：因为主键不能写null值，所以not null也可以不声明
"id integer primary key " // 意思是将id声明为整形主键

实测效果：

• 如果插入的id值在表格中已经存在，则会插入失败，因为一个表格中的主键不能有重复的值；
• 要实现自增的效果，必须插入null值，或者不插入值（即插入指定列的值）。

2.1 SQLite-python

基本步骤：

• 建立数据库连接：conn = sqlite3.connect(‘test.db’)
• 创建cursor对象：c = conn.cursor()
• 调用接口执行sqlite命令：c.execute(‘…’)
• 执行完，提交：c.commit()
• 关闭数据库连接：c.close()