SQLite(一)

发表于 | 分类于 | 阅读次数:

简介

SQLite特点：无服务器、零配置、事务性的 SQL 数据库引擎，SQLite 引擎不是一个独立的进程，可以按应用程序需求进行静态或动态连接；非常小轻量级，兼容ACID原则，是进程或线程安全的

ACID是一组强调高可靠性的数据库系统设计原则，在软件崩溃甚至是硬件故障的情况下，数据也不会损坏。当你需要依赖兼容ACID原则的业务时，你不必重复造轮子去实现一致性检查和崩溃恢复机制。如果你有额外的安全保证机制，可以调整牺牲掉ACID的一些可靠性换取更高的性能和数据吞吐量
A: atomicity (原子性)
C: consistency (一致性)
I: isolation （隔离性）
D: durability （持久性）

SQL92 不支持的特性如下
RIGHT OUTER JOIN 只实现了 LEFT OUTER JOIN
FULL OUTER JOIN 只实现了 LEFT OUTER JOIN
ALTER TABLE 支持 RENAME TABLE 和 ALTER TABLE 的 ADD COLUMN variants 命令，不支持 DROP COLUMN、ALTER COLUMN、ADD CONSTRAINT
Trigger 支持 FOR EACH ROW 触发器，但不支持 FOR EACH STATEMENT 触发器
VIEWs 在 SQLite 中，视图是只读的,不可以在视图上执行 DELETE、INSERT 或 UPDATE 语句
GRANT 和 REVOKE 可以应用的唯一的访问权限是底层操作系统的正常文件访问权限

DDL 数据定义语言
CREATE 创建一个新的表，一个表的视图，或者数据库中的其他对象
ALTER 修改数据库中的某个已有的数据库对象，比如一个表
DROP 删除整个表，或者表的视图，或者数据库中的其他对象

DML 数据操作语言
INSERT 创建一条记录
UPDATE 修改记录
DELETE 删除记录

DQL 数据库查询语言
SELECT 从一个或多个表中检索某些记录

  • 安装sqlite3，设置PATH，cmd运行sqlite3
  • SQLite点命令
    • .help 获取帮助
    • .show 命令查看 SQLite 命令提示符的默认设置,下面是修改默认设置属性

      sqlite>.header on
      sqlite>.mode column
      sqlite>.timer on

    • sqlite_master 主表中保存数据库表的关键信息，并把它命名为 sqlite_master，如要查看表概要
      1
      2
      3
      4
      5
      6
      7
       CREATE TABLE sqlite_master (
        type text,
        name text,
        tbl_name text,
        rootpage integer,
        sql text
      );

SQLite语法

  • 不区分大小写，但是有些命令大小写敏感，GLOB和glob在SQLite不同 SQLite3练习

    The GLOB operator is similar to LIKE but uses the Unix file globbing syntax for its wildcards. Also, GLOB is case sensitive, unlike LIKE
    The glob(X,Y) function is equivalent to the expression “Y GLOB X”

1
2
3
4
5
6
7
SELECT
 trackid,
 name
FROM
 tracks
WHERE
 name GLOB 'Man*';
  • 注释sqlite>.help -- This is a single line comment
  • SELECT、INSERT、UPDATE、DELETE、ALTER、DROP 等，所有的语句以分号;结束

  • SELECT

    1
    2
    SELECT column1, column2....columnN
    FROM   table_name;

  • ANALYZE

    1
    2
    3
    4
    5
    ANALYZE;
    or
    ANALYZE database_name;
    or
    ANALYZE database_name.table_name;

  • AND/OR

    1
    2
    3
    SELECT column1, column2....columnN
    FROM   table_name
    WHERE  CONDITION-1 {AND|OR} CONDITION-2;

  • ALTER TABLE (RENAME TO)

    1
    2
    3
    ALTER TABLE table_name ADD COLUMN column_def...;

    ALTER TABLE table_name RENAME TO new_table_name;

  • ATTACH DATABASE

    1
    ATTACH DATABASE 'DatabaseName' As 'Alias-Name';

  • DETACH DATABASE

    1
    DETACH DATABASE 'Alias-Name';

  • BEGIN TRANSACTION

    1
    2
    3
    BEGIN;
    or
    BEGIN EXCLUSIVE TRANSACTION;

  • BETWEEN

    1
    2
    3
    SELECT column1, column2....columnN
    FROM   table_name
    WHERE  column_name BETWEEN val-1 AND val-2;

  • CREATE (UNIQUE) INDEX

    1
    2
    3
    4
    5
    CREATE INDEX index_name
    ON table_name ( column_name COLLATE NOCASE );

    CREATE UNIQUE INDEX index_name
    ON table_name ( column1, column2,...columnN);

  • ROLLBACK：

    1
    2
    3
    ROLLBACK;
    or
    ROLLBACK TO SAVEPOINT savepoint_name;

  • SAVEPOINT

    1
    SAVEPOINT savepoint_name;

  • CREATE TABLE

    1
    2
    3
    4
    5
    6
    7
    8
    CREATE TABLE table_name(
       column1 datatype,
       column2 datatype,
       column3 datatype,
       .....
       columnN datatype,
       PRIMARY KEY( one or more columns )
    );

  • CREATE TRIGGER

    1
    2
    3
    4
    5
    6
    7
    CREATE TRIGGER database_name.trigger_name 
    BEFORE INSERT ON table_name FOR EACH ROW
    BEGIN 
       stmt1; 
       stmt2;
       ....
    END;

  • CREATE VIEW

    1
    2
    CREATE VIEW database_name.view_name  AS
    SELECT statement....;

  • CREATE VIRTUAL TABLE

    1
    2
    3
    CREATE VIRTUAL TABLE database_name.table_name USING weblog( access.log );
    or
    CREATE VIRTUAL TABLE database_name.table_name USING fts3( );

  • COUNT

    1
    2
    3
    SELECT COUNT(column_name)
    FROM   table_name
    WHERE  CONDITION;

  • DELETE

    1
    2
    DELETE FROM table_name
    WHERE  {CONDITION};

  • DISTINCT

    1
    2
    SELECT DISTINCT column1, column2....columnN
    FROM   table_name;

  • DROP TABLE/INDEX

    1
    DROP TABLE/INDEX database_name.table_name/index_name;

  • DROP VIEW/TRIGGER 

    1
    DROP VIEW/TRIGGER  view_name/trigger_name;

  • EXISTS 

    1
    2
    3
    SELECT column1, column2....columnN
    FROM   table_name
    WHERE  column_name EXISTS (SELECT * FROM   table_name )

  • GLOB

    1
    2
    3
    SELECT column1, column2....columnN
    FROM   table_name
    WHERE  column_name GLOB { PATTERN };

  • GROUP BY 

    1
    2
    3
    4
    SELECT SUM(column_name)
    FROM   table_name
    WHERE  CONDITION
    GROUP BY column_name;

  • HAVING

    1
    2
    3
    4
    5
    SELECT SUM(column_name)
    FROM   table_name
    WHERE  CONDITION
    GROUP BY column_name
    HAVING (arithematic function condition);

  • WHERE

    1
    2
    3
    SELECT column1, column2....columnN
    FROM   table_name
    WHERE  CONDITION;

  • UPDATE

    1
    2
    3
    UPDATE table_name
    SET column1 = value1, column2 = value2....columnN=valueN
    [ WHERE  CONDITION ];

  • INSERT INTO

    1
    2
    INSERT INTO table_name( column1, column2....columnN)
    VALUES ( value1, value2....valueN);

  • IN/NOT IN

    1
    2
    3
    SELECT column1, column2....columnN
    FROM   table_name
    WHERE  column_name IN/NOT IN (val-1, val-2,...val-N);

  • Like

    1
    2
    3
    SELECT column1, column2....columnN
    FROM   table_name
    WHERE  column_name LIKE { PATTERN };

  • ORDER BY

    1
    2
    3
    4
    SELECT column1, column2....columnN
    FROM   table_name
    WHERE  CONDITION
    ORDER BY column_name {ASC|DESC};

SQLite数据类型

  • 存储类

    NULL 值是一个 NULL 值
    INTEGER 值是一个带符号的整数，根据值的大小存储在 1、2、3、4、6 或 8 字节中
    REAL 值是一个浮点值，存储为 8 字节的浮点数字
    TEXT 值是一个文本字符串，使用数据库编码（UTF-8、UTF-16BE 或 UTF-16LE）存储
    BLOB 值是一个 blob 数据，完全根据它的输入存储

  • 亲和类 该字段的数据将会优先采用亲缘类型作为该值的存储方式

    TEXT 数值型数据在被插入之前，需要先被转换为文本格式，之后再插入到目标字段中
    NUMERIC 当文本数据被插入到亲缘性为NUMERIC的字段中时，如果转换操作不会导致数据信息丢失以及完全可逆，那么SQLite就会将该文本数据转换为INTEGER或REAL类型的数据，如果转换失败，SQLite仍会以TEXT方式存储该数据。对于NULL或BLOB类型的新数据，SQLite将不做任何转换，直接以NULL或BLOB的方式存储该数据。需要额外说明的是，对于浮点格式的常量文本，如”30000.0”，如果该值可以转换为INTEGER同时又不会丢失数值信息，那么SQLite就会将其转换为INTEGER的存储方式
    INTEGER 对于亲缘类型为INTEGER的字段，其规则等同于NUMERIC，唯一差别是在执行CAST表达式时
    REAL 其规则基本等同于NUMERIC，唯一的差别是不会将”30000.0”这样的文本数据转换为INTEGER存储方式
    NONE 不做任何的转换，直接以该数据所属的数据类型进行存储

  • Boolean数据类型 SQLite 没有单独的 Boolean 存储类。相反，布尔值被存储为整数 0（false）和 1（true）

  • Date 与 Time 数据类型 SQLite 没有一个单独的用于存储日期和/或时间的存储类，但 SQLite 能够把日期和时间存储为 TEXT、REAL 或 INTEGER 值，并且可以使用内置的日期和时间函数来自由转换不同格式

    TEXT 格式为 “YYYY-MM-DD HH:MM:SS.SSS” 的日期
    REAL 从公元前 4714 年 11 月 24 日格林尼治时间的正午开始算起的天数
    INTEGER 从 1970-01-01 00:00:00 UTC 算起的秒数

willkernel wechat
关注微信公众号
帅哥美女们，请赐予我力量吧!