sqlite常用SQL

查询相关

查询有什么表

SQL

SELECT name FROM sqlite_master WHERE type='table';

表结构

SQL

-- 第一种
-- 不要加分号
.schema table_name

-- 第二种
-- 每一行的字段解释
-- cid: 列的编号（从 0 开始）
-- name: 列的名称
-- type: 列的数据类型
-- notnull: 列是否可以为 NULL（0 表示可以为 NULL，1 表示不可为 NULL）
-- dflt_value: 列的默认值（如果有的话）
-- pk: 列是否是主键（0 表示不是主键，1 表示是主键）
PRAGMA table_info(table_name);

查询获取数据总数

SQL

SELECT 
    count(id)
FROM 
    television_links
WHERE
    valid_status = 0

查询指定初始位置和偏移量

SQL

SELECT 
    id
    ,tv_link as tvLink
FROM 
    television_links
WHERE
    valid_status = 0
ORDER BY
    id
LIMIT 
    10
OFFSET 
    0

启用外键约束

SQL

PRAGMA foreign_keys = ON;

表相关

创建表

SQL

-- 有五种数据类型类型
-- INTEGER 整型，无数据长度
-- REAL    浮点型
-- TEXT    文本
-- BOLB    二进制
-- NUMERIC 自动匹配，看看适合哪个类型就用哪个类型
-------------------------------------------
-- 字段约束
-- PRIMARY kEY    主键
-- AUTOINCREMENT  自增
-- UNIQUE         不可重复
-- NOT NULL       插入不可为NULL
-- DEFAULT ''     设置默认值，这里为默认空字符串
-- FOREIGN KEY    将某列与其他表某列关联，保证其他表没有这个值，就会插入失败
-------------------------------------------
-- 如果create_time，update_time由触发器更新，那么自己就可以不用插入这个数据
CREATE TABLE television_types(
     id INTEGER PRIMARY kEY AUTOINCREMENT -- 自增主键
    ,tv_id INTEGER UNIQUE -- 电视ID
    ,tv_name TEXT NOT NULL -- 电视名称
    ,tv_type_id INTEGER NOT NULL -- 电视所属类别
    ,create_time INTEGER DEFAULT 0 NOT NULL -- 创建时间
    ,update_time INTEGER DEFAULT 0 NOT NULL -- 更新时间
    ,FOREIGN KEY(tv_type_id) REFERENCES television_types(tv_type_id)
);

触发器

创建触发器

SQL

-- 创建了set_time_after_insert_television_types这个触发器
-- 在television_types表插入后进行操作
-- 在BEGIN与END中执行对应自动触发的SQL
-- 这里自动更新插入和更新的时间
CREATE TRIGGER set_time_after_insert_television_types
AFTER INSERT ON television_types
BEGIN
  UPDATE television_types set create_time = CAST(strftime('%s','now') AS INTEGER) WHERE id = NEW.id;
  UPDATE television_types set update_time = CAST(strftime('%s','now') AS INTEGER) WHERE id = NEW.id;
END;
-- 这个触发器是用于在television_types表更新后触发
CREATE TRIGGER set_time_after_update_television_types
AFTER UPDATE ON television_types
BEGIN
  UPDATE television_types set update_time = CAST(strftime('%s','now') AS INTEGER) WHERE id = NEW.id;
END;

删除触发器

SQL

-- 触发器存在则删除
DROP TRIGGER IF EXISTS set_time_after_insert_television_types