SQLite3-一些常用的指令

# 数据库的一些操作
table_name = 'my_table'


# 创建一个表
# create table if not exists 表名 (列名1 类类型1, ... , 列名n 列类型n [PRIMARY KEY])
query_str1 = f'create table if not exists {table_name} (key TEXT, date TIMESTAMP, UserID TEXT)'


# 从表中获取数据
# select * from 表名
# select key, date from 表名
qeury_str2 = f'select * from {table_name}'
# 按条件筛选需要的数据
query_str3 = f'select * from {table_name} where date == datetime("2019-07-22")'
# 取时间的区间
query_str4 = f'select * from {table_name} where date between datetime("2019-07-22") and datetime("2019-07-25")'
# 多条件筛选，使用 and 或者 or
query_str5 = f'select * from {table_name} where date == datetime("2019-07-22") and key == "1"'
# 字符串的包含筛选
query_str6 = f'select * from {table_name} where UserID in ("EE3F593603104141BE02CB5FED442A62", "C7EEA15E4FDD4FCDA9741F883F2E40D3")'
# 字符串匹配
query_str7 = f'select * from TestTable where UserID like "%D6%"' # 找到 UserID 中带 D6 的用户
query_str8 = f'select * from TestTable where UserID like "%E5"' # 找到 UserID 中以 E5 结尾的用户
# 将获取数据按照某列排序
query_str9 = f'select * from {table_name} order by UserID' # 默认是升序
query_str10 = f'select * from {table_name} order by UserID asc'  # 升序
query_str11 = f'select * from {table_name} order by UserID desc'  # 降序
# 将获取的数据分组
query_str12 = f'select count(*) from {table_name} group by date'  # 统计每个日期有多少个人
# 统计每个日期多少人并重新命名列名，求key的最大值，key的最小值
query_str13 = f'select count(*)Number, max(key), min(key) from {table_name} group by date'
# 多重筛选，先从数据中筛选一部分用户，在统计这部分用户的其他信息
query_str14 = f'select count(*)Number,max(UserID) from (select * from {table_name} where date == datetime("2019-07-26"))'
# 只取数据的前几项
query_str15 = f'select * from {table_name} limit 2' # 表示取数据的前两行，也就是1行、2行
# 选取数据的中间几行
query_str16 = f'select * from {table_name} limit 10, 1' # 表示取第十行后的一行，也就是11行
# 选取数据的最后一行
query_str17 = f'select * from {table_name} limit (select count(*) from {table_name})-1, 1'


# 删除数据表中的某些行，可以设置条件，不设条件，就是清空表
query_str18 = f'delete from {table_name} where UserID == "ECA467915D0F4C86B2760897DBFA0DA6"'


# 删除数据库中的某个表
# DROP TABLE IF EXISTS 表名
qeury_str19 = f'drop table if exists {table_name}'


# 清空数据库中多余的空间，我们删除一个表之后，数据库并不会释放空间，需要调用下面的语句进行释放
# VACUUM
query_str20 = 'VACUUM'


# 获取数据某个表的信息（列名，列类型，默认值等），PRAGMA 关键词必须大写
# PRAGMA table_info(表名)
query_str21 = f'PRAGMA table_info({table_name})'
"""
得到的结果是一个列表:
   cid             name       type  notnull dflt_value  pk
0    0       event_date       TEXT        0       None   0
1    1           UserID       TEXT        0       None   0
2    2             IDFA       TEXT        0       None   0
3    3             IDFV       TEXT        0       None   0
4    4             City       TEXT        0       None   0
5    5          Country       TEXT        0       None   0
6    6             Date  TIMESTAMP        0       None   0
7    7     DownloadDate  TIMESTAMP        0       None   0
8    8  DownloadVersion       TEXT        0       None   0
9    9    LastLoginDate  TIMESTAMP        0       None   0
"""


# 获取数据库中所有表的表名，索引名
# select name from sqlite_master where type='table' [order by name]
query_str22 = "select name from sqlite_master where type='table' order by name"
query_str23 = "select name from sqlite_master where type='index' order by name"


# 为已有的数据库表添加新列，后面可以设置默认值，不设就是空
# alter table 表名 add column 列名 列类型 [DEFAULT 默认值]
query_str24 = f'ALTER TABLE {table_name} ADD COLUMN "DownloadDate" TIMESTAMP'
query_str25 = f'ALTER TABLE {table_name} ADD COLUMN "Level" TIMESTAMP DEFAULT 0'


# 删除表，不能使用drop，sqlite不支持
# alter table 表名 drop column 列名
# query_str = f'alter table {table_name} drop column DownloadDate'
# 用一下方式代替：
# 1.先创建一个临时的表，这个表里面包含了老表的所有列，出了要删除的列
query_str26 = f'create table if not exists temp as select key, date, UserID from {table_name} where 1=1'
# 2.然后删除老表
query_str27 = f'drop table if exists {table_name}'
# 3.重新命名新表
query_str28 = f'alter table temp rename to {table_name}'