SQLite笔记

code
2k words

SQLite 是一款轻型的数据库，在嵌入式设备中应用十分广泛，它支持Windows/Linux/Unix等等主流的操作系统，其源代码不受版权限制。

优点：

  • 不需要安装与配置，以文件的形式作为数据库
  • 轻量、小型，对嵌入式设备十分友好
  • 事务是完全兼容 ACID 的，允许从多个进程或线程安全访问
  • 支持 SQL92（SQL2）标准的大多数查询语言的功能

SQLite的命令包括：

分类 命令 描述
表操作 CREATE 创建表
表操作 ALTER 修改表
表操作 DROP 删除表
记录操作 INSERT 插入记录
记录操作 UPDATE 修改更新表记录
记录操作 DELETE 删除记录
检索 SELECT 查询记录

基本操作

表操作

  1. 创建和删除表

    创建格式：CREATE TABLE IF NOT EXISTS 表名 (列名 类型)
    删除格式：DROP 表名

    1
    2
    3
    4
    CREATE TABLE IF NOT EXISTS "student" (
        id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
        name VARCHAR(30) NOT NULL,
        age FLOAT);

  2. 更新sqlite_sequence

    由于在Sqlite中使用自增长字段，引擎会自动产生一个sqlite_sequence表，用于记录每个表的自增长字段的已使用的最大值，所以要一起更新下。如果有设置自增长，则跳过此步骤。

    1
    UPDATE "sqlite_sequence" SET seq = 3 WHERE name = 'Student';

  3. 修改表名称

    格式：ALTER TABLE 旧表名 RENAME TO 新表名

    1
    ALTER TABLE or_sql_table RENAME TO new_table;

  4. 增加表字段

    格式：ALTER TABLE 表名 ADD COLUMN 列名 数据类型

    1
    ALTER TABLE new_table ADD COLUMN sex Text;

  5. 查询表结构

    格式：RAGMA TABLE_INFO (表名)

    1
    PRAGMA TABLE_INFO (new_table);

记录操作

  1. 插入和删除记录

    插入格式：INSERT INTO 表名 (列名1,列名2,...) VALUES (值1,值2,...)

    删除格式：DELETE FROM 表名

    1
    2
    INSERT INTO sutdent (name,age) VALUES ('xiaoming',18);
    DELETE FROM student WHERE name = 'xiaoming';

  2. 更新记录

    格式：UPDATE 表名 SET 列名 = 列值 WHERE 条件

    1
    UPDATE student SET age = 18 WHERE name = 'xiaoming';

  3. 不存在则插入

    格式：INSERT INTO 表名 (列名1,列名2,...) SELECT 值1,值2,... WHERE NOT EXISTS (SELECT * FROM 表名 WHERE 列名=列值)

    1
    INSERT INTO tb_test (name) select ('test4') where not exists(select * from tb_test where name='test4')

搜索

  1. LIKE与GLOB搜索

    格式：SELECT 列名 FROM 表名 WHERE 列名 LIKE "XXX"或SELECT 列名 FROM 表名 WHERE 列名 GLOB "XXX"

    • LIKE关键字通配符包括：%：表示有N个字符和 _：表示有1个字符，不区分大小写

    • GLOB关键字通配符包括：*：表示有N个字符和 ?：表示有1个字符，区分大小写

      1
      2
      SELECT * FROM student WHERE name LIKE  '%ming'
      SELECT * FROM student WHERE name LIKE  '%min_'

  2. LIMIT和OFFSET翻页

    格式：SELECT 列名 FROM 表名 LIMIT 数量 OFFSET 偏移;

    1
    2
    -- 显示1到4的行
    SELECT * FROM student LIMIT 3 OFFSET 1

  3. 分组与排序

    分组格式：SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 列名

    排序格式：SELECT 列名 FROM 表名 WHERE 条件 ORDER BY 列名 关键字;

    关键字包括：

    • ASC：升序

    • DESC：降序

      GROUP BY一般与函数结合使用，比如：

      1
      2
      -- 查询所有学生名的最新成绩
      SELECT * FROM student WHERE id IN(SELECT MAX(id) FROM student GROUP BY name)

  4. 唯一值

    格式：SELECT DISTINCT 列名 FROM 表名

    1
    2
    -- 查询所有学生名
    SELECT DISTINCT name FROM student

  5. 函数

    • 计数COUNT

    • 最大最小值MAX\MIN

    • 平均值AVG

    • 和SUM

    • 绝对值ABS

    • 获取字符串长度LENGTH

    • 转换大小写UPPER\LOWER

Comments