sqlite

Posted on   |   In

sqlite

格式化输出

.heade on
.mode column

LIKE

% 任意多个字符 LIKE ‘XX*’;
_ 唯一一个字符 LIKE ‘_X’;

GLOB

* 任意字符
? 单一数字或者字符

LIMIT

LIMIT n
从开始到n项
LIMIT n OFFSET m;
以 n项为中心,m正向下m项,m负向上m项

SELECT [] FROM [table.name] ORDER BY [column] ASC|DESC;
SELECT 语句中，GROUP BY 子句放在 WHERE 子句之后，放在 ORDER BY 子句之前
聚集函数

COUNT(DISTINCT|ALL<列名>);
SUM(DISTINCT|ALL<列名>);
AVG(DISTINCT|ALL<列名>);
MAX/MIN(DISTINCT|ALL<列名>);

HAVING [condition]
HAVING 子句必须放在 GROUP BY 子句之后，必须放在 ORDER BY 子句之前
DISTINCT

SELECT DISTINCT [] FROM
与 SELECT 语句一起使用，来消除所有重复的记录

链接实现:
1、nested-loop
2、排序合并：子表排序后顺序查找

sqlite约束:

NOT NULL
DEDFAULT:创建表格时设置默认值
UNIQUE
PRIMMARY KET
CHECK:输入值时check,false则输入无效
example:SALARY REAL CHECK(SALARY> 0);

重命名列,删除列,删除约束在sqlite不可实现

sqlite joins

CROSS JOIN

  • SELECT COLUMN1,…COLUMN2 FROM TABLE1 CROSS JOIN TABLE2
    INNER JOIN
  • SELECT COLUMN,…COLUMNn FROM TABLE1 JOIN TABLE2 USING COLUMN1…COLUMNn;
  • SELECT COLUMN,…COLUMNn FROM TABLE1 INNER JOIN TABLE2 ON condition;
  • SELECT COLUMN,…COLUMNn FROM TABLE1 NATURE JOIN TABLE2 COLUMN1 … COLUMNn;
  • OUTER JOIN:LEFT OUTER JOIN\ RIGTH OUTER JOIN\ FULL OUTER JOIN
  • SELECT…..FROM table1 [ ] OUTER JOIN table2 ON condition/ USING column….

UNION

每个 SELECT 被选择的列数必须是相同的，相同数目的列表达式，相同的数据类型，并确保它们有相同的顺序，但它们不必具有相同的长度。

1
2
3
4
SELECT .... FROM TABLE1....WHERE .....
UNION
SELECT .... FROM TABLE2....WHERE .....
//UNION 去重，UNION ALL 不去重

Alias

1
2
3
4
5
6
7
8
table：
SELECT  aliase_name.column... 
FROM  table.name AS aliase_name
WHERE [condition];
column:
SELECT column_name AS alias_name
FROM table_name;
WHERE [condition]；

TRIIGGER

1
2
3
4
CREATE TRIGGER tiggername [AFTER/BEFORE] [action] ON tablename
BEGIN
.....
END;

  • 显示触发器：
    SELECT name FROM SQLITE——master
    WHERE type=’trigger’;
  • 删除触发器：
    DROP TRIGGER triggername；
  • INDEX:
    CREAT [UNIQUE] INDEX indwx_name ON table_name(column1,column);
  • 显示单表中index：
    .indices table
  • 显示全部：
    SELECT * FROM sqlite_master WHERE type = ‘index’;
  • DROP INDEX index_name;
    索引不应该使用在较小的表上。
    索引不应该使用在有频繁的大批量的更新或插入操作的表上。
    索引不应该使用在含有大量的 NULL 值的列上。
    索引不应该使用在频繁操作的列上。

  • ALERT
    ALTER TABLE tablename RENAME TO new_tablename
    ALERT TABLE databasename.tablename ADD COLUMN columnname def…..

  • VIEW
    CREATE [TEMPORY|TEMP]VIEW viewname AS
    SELECT column1 column2….
    FROM tablename
    WHERE [condition];
    SELECT * FROM viewname;
    DROP VIEW viewname;

  • TRANSICTION

    原子性（Atomicity）：确保工作单位内的所有操作都成功完成，否则，事务会在出现故障时终止，之前的操作也会回滚到以前的状态。
    一致性（Consistency)：确保数据库在成功提交的事务上正确地改变状态。
    隔离性（Isolation）：使事务操作相互独立和透明。
    持久性（Durability）：确保已提交事务的结果或效果在系统发生故障的情况下仍然存在。

BEGIN;/BEGIN TRANSACTION; ROLLBACK/COMMIT;

  • VACUUM
    VACUUM 命令通过复制主数据库中的内容到一个临时数据库文件，然后清空主数据库，并从副本中重新载入原始的数据库文件。这消除了空闲页，把表中的数据排列为连续的，另外会清理数据库文件结构
    VACUUM 命令只适用于主数据库，附加的数据库文件是不可能使用 VACUUM 命令。
    1
    2
    3
    sqlite> PRAGMA auto_vacuum = NONE;  -- 0 means disable auto vacuum
    sqlite> PRAGMA auto_vacuum = INCREMENTAL;  -- 1 means enable incremental vacuum
    sqlite> PRAGMA auto_vacuum = FULL;  -- 2 means enable full auto vacuum