sqlite |

基础

可以把 SQL 分为两个部分：数据操作语言 DML 和数据定义语言 DDL

SQL (结构化查询语言)是用于执行查询的语法。但是 SQL 语言也包含用于更新、插入和删除记录的语法。

查询和更新指令构成了 SQL 的 DML 部分：

SELECT - 从数据库表中获取数据
UPDATE - 更新数据库表中的数据
DELETE - 从数据库表中删除数据
INSERT INTO - 向数据库表中插入数据

SQL 的数据定义语言 DDL 部分使我们有能力创建或删除表格。我们也可以定义索引（键），规定表之间的链接，以及施加表间的约束。

SQL 中最重要的 DDL 语句:

CREATE DATABASE - 创建新数据库
ALTER DATABASE - 修改数据库
CREATE TABLE - 创建新表
ALTER TABLE - 变更（改变）数据库表
DROP TABLE - 删除表
CREATE INDEX - 创建索引（搜索键）
DROP INDEX - 删除索引

Example

001

CREATE TABLE item (
       stash TEXT,
       k TEXT,
       lastaccess INTEGER,
       expiry INTEGER,
       modtime INTEGER,
       etag TEXT,
       payload BLOB,
       UNIQUE(k, stash) ON CONFLICT FAIL);

CREATE INDEX item_key_idx ON item(stash, k);

, 分割开 列名称数据类型，UNIQUE 约束唯一标识数据库表中的每条记录

创建索引以便快速高效地查询数据 CREATE INDEX index_name ON table_name (column_name)

ON CONFLICT

ON CONFLICT 子句不是独立的SQL命令，不是标准的SQL语言，在 CREATE TABLE 中使用 ON CONFLICT，而对于 INSERT UPDATE 关键词 ON CONFLICT 替换为 OR。例如，不用写 INSERT ON ON CONFLICT 而是 INSERT OR FAIL

ON CONFLICT 子句定义了解决约束冲突的算法，有以下选择：

ROLLBACK
ABORT
FAIL
IGNORE
REPLACE

002

CREATE TABLE url (
       id INTEGER PRIMARY KEY,
       url TEXT NOT NULL UNIQUE
       );

CREATE INDEX url_url_idx ON url(url);

CREATE TABLE page_kv (
       url_id INTEGER REFERENCES url(id) ON DELETE CASCADE,
       key TEXT NOT NULL,
       value,
       UNIQUE (url_id, key));

CREATE INDEX page_kv_url_id_idx ON page_kv(url_id);

UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。PRIMARY KEY 拥有自动定义的 UNIQUE 约束。请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

主键必须包含唯一的值。主键列不能包含 NULL 值。每个表都应该有一个主键，并且每个表只能有一个主键。

NOT NULL 约束强制列不接受 NULL 值。 NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。

REFERENCES 为外来键，外键设置的模式：

CASCADE，父表 update/delete记录时，同步操作子表记录
SET NULL，在父表上update/delete记录时，将子表上匹配记录的列设为null (要注意子表的外键列不能为not null)
SET DEFAULT，父表有变更时,子表将外键列设置成一个默认的值
NO ACTION，如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作
RESTRICT，同no action, 都是立即检查外键约束

003

ALTER TABLE item ADD COLUMN restartposition INTEGER DEFAULT 0;

UPDATE item SET restartposition = (SELECT restartposition FROM videoitem WHERE item_id = id);

CREATE TABLE videoitem_tmp(
       item_id INTEGER UNIQUE REFERENCES item(id) ON DELETE CASCADE,
       title TEXT,
       duration INTEGER,
       format TEXT);

INSERT INTO videoitem_tmp SELECT item_id, title, duration, format FROM videoitem;
DROP TABLE videoitem;

ALTER TABLE videoitem_tmp RENAME TO videoitem;

CREATE INDEX videoitem_item_id_idx ON videoitem(item_id);

ALTER TABLE

ALTER TABLE 除了重命名表和在已有的表中添加列，ALTER TABLE 命令不支持其他操作

ALTER TABLE database_name.table_name RENAME TO new_table_name;
ALTER TABLE database_name.table_name ADD COLUMN column_def...;

UPDATE TABLE

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

SELECT

SELECT 语句用于从表中选取数据。结果被存储在一个结果表中（称为结果集）。

SELECT 列名称 FROM 表名称
SELECT * FROM 表名称

INSERT INFO

INSERT INTO 表名称 VALUES (值1, 值2,....)
INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

004

ALTER TABLE stream ADD COLUMN title TEXT;
DELETE FROM videoitem;

DELETE

DELETE 语句用于删除表中的行。

DELETE FROM 表名称 WHERE 列名称 = 值
DELETE FROM table_name

Trigger

CREATE  TRIGGER trigger_name [BEFORE|AFTER] event_name ON table_name
BEGIN
-- Trigger logic goes here....
END;

事务控制

使用下面的命令来控制事务：

BEGIN TRANSACTION：开始事务处理。
COMMIT：保存更改，或者可以使用 END TRANSACTION 命令。
ROLLBACK：回滚所做的更改。

事务控制命令只与 DML 命令 INSERT、UPDATE 和 DELETE 一起使用。他们不能在创建表或删除表时使用，因为这些操作在数据库中是自动提交的。

基础