sqlite学习笔记（一）

发表于 分类于

Sqlite学习笔记(一)

1. 常量

表示确切的值，包含三种类型，字符串常量，数字常量和二进制常量

  • 字符串常量
    推荐使用单引号界定字符串，虽然也可以使用双引号界定
    例如：'jerry', 'john',若字符串本身包含单引号，需要连续两个单引号，例如 'jerry ''s children'

  • 数字常量
    数字常量有整型、十进制数和科学计数法表示的数，其中二进制使用x’0000’来表示，二进制数必须有两个16进制数的整数倍组成

2. 关键字和标识符

  • 关键字：select, update, insert,create, drop, begin等。
  • 标识符：指数据库中的具体对象，如表和索引。
  • SQL不区分关键字和标识符的大小写，但是默认字符常量是大小写敏感。

3. 注释

  • 单行采用 – 形式
  • 多行注释采用/**/形式

4. sqlite的五5种基本类型

integer,real, text, blob, null

5. 创建表

crate [temp | temptory]table table_name(column_define[, constraints]);

  • temp和temptory是创建临时表用的，属于可选项,这种表是临时的，只存活于当前会话，一旦断开会自从销毁。 |表示二者任选其一
  • 若没有明确指出创建临时表，则表示创建基本表，会在数据库中持久存在。例如
1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE contacts (
    id    INTEGER PRIMARY KEY,
    name  TEXT    NOT NULL
                  COLLATE nocase,//排序部分大小写
    phone TEXT    NOT NULL
                  DEFAULT 'UNKNOWN',
    UNIQUE (
        name,
        phone
    )//表一级的约束
);

6. 修改表

alter table table_name{rename to name| add column column_def}

  • {}表示一个选项列表，表示必须从选项中选择一个
  • .schema table_name 会显示出表的定义
  • 可以使用alter命令重命名表或者添加新的列，例如
1
2
3
4
5
ALTER TABLE contacts ADD email TEXT NOT NULL
DEFAULT ''
COLLATE nocase;

ALTER TABLE contacts RENAME TO contacts1;

7. 查询表

大部分的SQL实现中，select语句提供混合、比较和过滤数据的”关系操作”,通常可以划分为三类

基本操作

select命令的通用模式

1
2
3
4
5
6
7
SELECT dintinct heading
  FROM _tables
 WHERE predicate
 GROUP BY columns
HAVING predicate
 ORDER BY columns
 LIMIT count, [offset];

例如，常用的select命令

1
select id, name from food_types;

过滤

  • 通过where子句过滤（后面跟着的逻辑子句）

    • 二元操作符

      • 算数操作符（加减乘除）

      • 关系操作符（>、 < 、= )
        在sqlite中，false可由数字0代替，true可由其他任意非零代替，如

        1
        2
        select 1 > 2//输出0
        select 2 > 1 //输出1

    • 逻辑操作符（AND、OR、NOT、IN）
      例如：

      1
      select * from people where name = 'Tom' and age = 19

  • LIKE与GLOB操作符

    • LIKE的作用与相等（=）类似，是通过一个模式来做匹配，例如查询people中的所有名称以字符T开头的人

      1
      select id, name from foods where name like 'T%';

      注：模式中的百分号（%）可以与任意0个或者多个字符匹配，百分号（%）是贪婪匹配，下划线_可以与任意单个字符匹配，若百分号在模式的最左边或者是最右边，他将匹配字符串的另外一边儿,例如

      1
      select id,name from foods where name like '%ac%P%';

      另外一个方法可以使用NOT 否定某些模式

      1
      select id,name from foods where name like '%ac%P%' and name not like '%Sch%';

    • GLOB的操作符在行为上与LIKE操作符非常类似，它会使用文件名替换相关的通配符，例如*和_，并且是大小写敏感的，例如

      1
      select id, name from foods where name glob 'Pine*";

限定和排序

可以使用limit和offset关键字限定结果集的大小和范围

  • limit指定返回记录的最大数量
  • offset指定偏移的记录数
    例如
1
select * from food_types order by id limit 1 offset 1;

order by默认是升序排列（asc）,可以使用降序desc,例如

1
select * from foods where name like 'B%' order by type_id desc, name limit 10;

注意：limit和offset一起使用时，可以用逗号代替offset关键字，例如limit 1 offset 2可以用limit 2， 1代替，此处数字没有写反，因为在sqlite使用缩写时，offset总是优先于limit，紧随limit关键字的是offset 2

函数和聚合

  • sqlite有很多内置函数，如abs(), upper(), lower(),例如
1
select upper('hello'), length('hello'), abs(-6);
函数名不区分大小写，函数可以接受字段值作为参数

1
select id, upper(name), length(name) from foods where type_id = 1 and length(name) < 5 limit 10

  • 聚合是一类特殊的函数，它从一组值中计算聚合值。标准的聚合函数包括sum()、avg()、count()、min()、max(),聚合即对表中的每一行做某种运行，聚合不仅可以聚合字段，也可以聚合任何表达式，包括函数。

    1
    select avg(length(name)) from foods;

聚合不仅可以在select子句中操作，也可以对from的子句选中的行的值进行计算

分组

  • group by接收where的输出，并将其分隔成共享某个字段（或者多个字段）上的同等值的小组，这些值再传给select子句

  • having和group by一起工作，对group by有过滤的作用，通过过滤的组传递给select子句来做聚合和映射。例如：

    1
    select type_id, count(*) from foods group by type_id having count(*) < 20;

去掉重复

  • distinct处理select的结果并过滤其中重复的行，例如

    1
    select distinct type_id from foods;

多表链接

连接(join)是多表(关系)数据工作的关键，连接操作的结果作为输入，供select语句的其他部分处理。

  • 内连接（使用关系代数的的另一种集合操作，称为交叉）
    foods表一个type_id,该字段的值都与foods_type表中id字段对应，这样子两个表之间就存在关联了，即foods.type_id的任何值都必须在foods_type表中存在，id是foods_type的主键，foods.type_id由于这种关系，被称为外键。
1
2
3
4
5
select foods.name, foods_type.name
from foods, foods_type
where foods.type_id = foods_type.id limit 10;
//另一种表示方式（比较规范的连接写法）
select * from foods inner join foods_type on foods.type_id = foods_type.id;

  • 交叉连接
    两个表没有通过任何方式关联（笛卡尔积的方式）称为交叉连接，它是几乎无意义的连接

  • 外连接(选择内连接的所有行外加一些关系之外的行，)

    • 左外连接（操作sql命令中的”左表”）
    1
    2
    select * from foods
    left outer join foods_episodes on foods.id = foods_episodes.food_id;

    foods 是其中的左表，是外连接中很重要的表，左外连接试图将foods中的所有行与foods_episodes的所有行进行连接关系（foods.id = foods_episodes.food_id）的匹配，所有行都包含在结果集中，但是，如果在foods表中的一些食品，没有在foods_episodes表中出现，则这些食品也会在结果集中，foods_episodes中没有提供相应的行，会以null补充

    • 右外连接（工作方式类似，不管是否匹配，右表中所有行都包含在结果集中）
      左外连接与右外连接类似，它们坐着同样的事情，只是在顺序和语法上不同，这意味着任何可以右外连接的解决方式都可以通过左外连接的方式来解决
    • 全外连接
      是左外连接和右外连接的结合，它包含所有匹配的行，然后是右边和左边表的不匹配行。

  • 自然连接
    其实是内连接的一种形式，它是通过表中共有的字段名称将两个表连接起来，因为使用自然连接时，不用添加连接条件，即可获得内连接的结果。
    自然连接会连接两个表中所具有的相同名称的字段，最好清晰定义查询连接中的条件，否则向表中添加和删除一个字段可能极大的影响连接查询的结果。

名称和别名

别名实际上是一种重命名的关系操作，重命名可以简单的将一个关系命名为另一个关系，例如

1
2
3
select f.name, t.name from foods f, foods_type t
where f.type_id = t.id
limit 10;

别名使自我连接（表与自身连接）称为可能
别名的一般语法

1
select base_name [[as] alias]

as关键字是可选的，一般选择保留，这样子别名更清晰，就不会将别名与基本的字段名和表达式混淆。

子查询

子查询是指select语句中又嵌套select语句，子查询最常用的地方是where子句，特别是在in操作符中，in操作符是一个双目操作符，输入一个值和一列值，如果输入的单个值存在列表中，返回真，否则，返回假。

1
2
3
4
5
select 1 in (1, 2, 3);

select count(*) from foods
where type_id in
(select id from foods_type where name = 'Bakery' or name = 'Cereal');

复合查询

复合查询与子查询相反，它是使用三个特殊的关系操作符（联合、交叉连接和差集）处理多个查询的结果，对应的关键字为union、intersect和except

  • union操作输入两个关系A和B，将两者联合成一个只包含A和B中非重复字段的单一关系。在sql中，union会联合两个select的结果，默认情况下，union会消除重复数据，如果想在结果中保留数据，可以使用union all。
  • intersect操作两个输入关系A和B，选择那些即在A也在B中的行
  • except 操作两个输入关系A和B，找出所有在A但不在B中的行

条件结果

case表达式允许在select语句中处理各种情况，有两种形式

  • 接收静态值并列出各种情况的case返回值
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
case vlaue
when  x then value_x
when y then value_y
else default_value
end
//例子
select name || case type_id
    when 7 then 'is a drink'
    when 8 then 'is a fruit'
    else null
    end
from foods
where description is not null
order by name
limit 10;
  • case形式允许when中有表达式
1
2
3
4
5
case vlaue
when  condition1 then value_1
when condition2 then value_2
else default_value
end

case 语句只执行一个条件。如果满足条件有多个，只执行第一个，若没有满足，且没有定义else，case则返回null

sqlite中的null

null是缺失信息的占位符，本身不是值

  • 在逻辑表达式中使用null,sqlite使用所谓的三值逻辑，null是真假值之一
  • 可以通过is null 或者is not null 检测null是否存在
  • null不等于其他任何值，包括null，不可以将null和其他值进行比较