SQLite中的SQL

来源：互联网发布：软件0x0000007b 编辑：程序博客网时间：2024/05/16 14:01

内容来自《The Definitive Guide to SQLite》

.echo on 在屏幕上打印执行的SQL语句

.mode column 以列模式显示结果

.headers on 包含列名称

.nullvalue NULL 将nulls打印成NULL

.width 设置输出结果中各个列的宽度

SQL不区分关键字和标示符大小写的，默认的字符常量值是大小写敏感的，例如，‘Mike' 和 ’mike' 是不同的。

注释：-- 或者 /**/

创建表

可以用如下定义的create table命令创建表：

create [temp] table table_name (column_definitions [, constrains]);

temp或temporary关键字表示声明的表是临时表。临时表只存活于当前回话，一段断开，就会就自动销毁。
table_name表示表名
column_definitions由用逗号分隔的字段列表组成，每个字段定义包括一个名词、一个域和一个逗号分隔的字段约束。

例如：

[sql] view plain copy

print?

sqlite> create table contacts (id integer primary key,
...> name text not null collate nocase,
...> phone text not null default 'UNKNOWN',
...> unique (name,phone));

sqlite> create table contacts (id integer primary key,   ...> name text not null collate nocase,   ...> phone text not null default 'UNKNOWN',   ...> unique (name,phone));

字段id声明为integer型，限制为主键，该字段是自增长字段
字段name声明为text类型，约束不能为null，并且排序不区分大小写。
字段phone是text类型
表一级的约束是unique，定义在字段name和phone上。

修改表

alter table命令改变表的部分结构。alter table命令既可以改变表名，也可以增加字段。格式为：

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

{}表示必须从各选项中选择一个：

alter table table rename... 重命名表
alter table table add column... 添加列

[sql] view plain copy

print?

sqlite> alter table contacts
...> add column email text not null default '' collate nocase;
sqlite> .schema contacts
CREATE TABLE contacts (id integer primary key,
name text not null collate nocase,
phone text not null default 'UNKNOWN', email text not null default '' collate nocase,
unique (name,phone));

sqlite> alter table contacts   ...> add column email text not null default '' collate nocase;sqlite> .schema contactsCREATE TABLE contacts (id integer primary key,name text not null collate nocase,phone text not null default 'UNKNOWN', email text not null default '' collate nocase,unique (name,phone));

.schema 会打印出整个数据库的模式。

数据库查询

select命令的通用形式如下：

[sql] view plain copy

print?

select [distinct] heading
from tables
where predicate
group by columns
having predicate
order by columns
limit count, offset;

select [distinct] headingfrom tableswhere predicategroup by columnshaving predicateorder by columnslimit count, offset;

最常见的select命令形式如下：

[sql] view plain copy

print?

select heading from tables where predicate;

select heading from tables where predicate;

[sql] view plain copy

print?

sqlite> select id,name from food_types;

sqlite> select id,name from food_types;

[sql] view plain copy

print?

sqlite> select * from food_types;

sqlite> select * from food_types;

星号(*)表示所有的列。

过滤where

[sql] view plain copy

print?

sqlite> select * from foods where name = 'JujyFruit' and type_id = 9;
id type_id name
---------- ---------- ----------
244 9 JujyFruit

sqlite> select * from foods where name = 'JujyFruit' and type_id = 9;id          type_id     name      ----------  ----------  ----------244         9           JujyFruit

LIKE与GLOB操作符

LIKE的作用与相等（=）类似，都是通过一个模式来进行字符串匹配的。%可以任意0个或多个字符匹配，下划线（_）可以与任何单个字符匹配。

[sql] view plain copy

print?

sqlite> select id, name from foods where name like '%ac%P%'and name not like '%Sch%';
id name
---------- --------------------
38 Pie (Blackberry) Pie
127 Guacamole Dip
198 Macinaw peaches

sqlite> select id, name from foods where name like '%ac%P%'and name not like '%Sch%';id          name                ----------  --------------------38          Pie (Blackberry) Pie127         Guacamole Dip       198         Macinaw peaches

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

[sql] view plain copy

print?

sqlite> select id, name from foods
...> where name glob 'Pine*';
id name
---------- ----------
205 Pineapple
258 Pineapple

sqlite> select id, name from foods    ...> where name glob 'Pine*';id          name      ----------  ----------205         Pineapple 258         Pineapple

限定和排序

limit指定返回记录的最大数量，offset指定偏移的记录数。order by排序，以逗号分隔的一系列字段，每个字段项都可能配合排序方向——asc(默认的升序)或desc(降序)，例如：

[sql] view plain copy

print?

sqlite> select * from foods where name like 'B%'
...> order by type_id desc, name limit 10;
id type_id name
---------- ---------- -----------
382 15 Baked Beans
383 15 Baked Potat
384 15 Big Salad
385 15 Brocolli
362 14 Bouillabais
328 12 BLT
327 12 Bacon Club
326 12 Bologna
329 12 Brisket San
274 10 Bacon

sqlite> select * from foods where name like 'B%'   ...> order by type_id desc, name limit 10;id          type_id     name       ----------  ----------  -----------382         15          Baked Beans383         15          Baked Potat384         15          Big Salad  385         15          Brocolli   362         14          Bouillabais328         12          BLT        327         12          Bacon Club 326         12          Bologna    329         12          Brisket San274         10          Bacon

limit和offset一起使用时，可以用逗号替代offset关键字，例如：

[sql] view plain copy

print?

sqlite> select * from foods where name like 'B%'order by type_id desc, name limit 1 offset 2;

sqlite> select * from foods where name like 'B%'order by type_id desc, name limit 1 offset 2;

等同于：

[sql] view plain copy

print?

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

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

函数（Function）和聚合（Aggressive）

abs()计算绝对值，upper()将字符串的值转化为大写，lower()将字符串的值转化为小写，length()计算长度。

[sql] view plain copy

print?

sqlite> select upper('hello newman'),length('hello newman'),abs(-12);
upper('hello newman') length('hello newman') abs(-12)
--------------------- ---------------------- ----------
HELLO NEWMAN 12 12

sqlite> select upper('hello newman'),length('hello newman'),abs(-12);upper('hello newman')  length('hello newman')  abs(-12)  ---------------------  ----------------------  ----------HELLO NEWMAN           12                      12

聚合是一类特殊的函数，它从一组记录中计算聚合值。标准的聚合函数包括sum(),avg(),count(),min()和max().例如，要得到type_id = 1的数量，可以使用如下语句：

[sql] view plain copy

print?

sqlite> select count(*) from foods where type_id = 1;
count(*)
----------
47

sqlite> select count(*) from foods where type_id = 1;count(*)  ----------47

分组（Grouping）

聚合的主要部分是分组。聚合不只是能够计算整个结果集的聚合值，还可以把结果集分成多个组。使用group by子句，例如，获取每个type_id组的记录数目：

[sql] view plain copy

print?

sqlite> select type_id,count(*) from foods group by type_id;
type_id count(*)
---------- ----------
1 47
2 15
3 23
4 22
5 17
6 4
7 60
8 23
9 61
10 36
11 16
12 23
13 14
14 19
15 32

sqlite> select type_id,count(*) from foods group by type_id;type_id     count(*)  ----------  ----------1           47        2           15        3           23        4           22        5           17        6           4         7           60        8           23        9           61        10          36        11          16        12          23        13          14        14          19        15          32

having是一个可以应用到group by的断言，它从group by中过滤组的方式与where子句从from子句中过滤行的方式相同。where子句的预测是针对单个行的，而having的断言是针对聚合值的。例如：

[sql] view plain copy

print?

sqlite> select type_id,count(*) from foods group by type_id having count(*) < 20;
type_id count(*)
---------- ----------
2 15
5 17
6 4
11 16
13 14
14 19

sqlite> select type_id,count(*) from foods group by type_id having count(*) < 20;type_id     count(*)  ----------  ----------2           15        5           17        6           4         11          16        13          14        14          19

group by和having一起工作可以对group by有约束。

去掉重复

distinct处理select的结果并过滤其中重复的行，例如，取得所有不同的type_id的值：

[sql] view plain copy

print?

sqlite> select distinct type_id from foods;

sqlite> select distinct type_id from foods;

多表连接

id是food_types的主键，foods.type_id由于这种关系，称为外键，它引用另一个表的主键，这种关系称为外键关系。

[sql] view plain copy

print?

sqlite> select foods.name, food_types.name
...> from foods, food_types
...> where foods.type_id = food_types.id limit 10;
name name
---------- ----------
Bagels Bakery
Bagels, ra Bakery
Bavarian C Bakery
Bear Claws Bakery
Black and Bakery
Bread (wit Bakery
Butterfing Bakery
Carrot Cak Bakery
Chips Ahoy Bakery
Chocolate Bakery

sqlite> select foods.name, food_types.name   ...> from foods, food_types   ...> where foods.type_id = food_types.id limit 10;name        name      ----------  ----------Bagels      Bakery    Bagels, ra  Bakery    Bavarian C  Bakery    Bear Claws  Bakery    Black and   Bakery    Bread (wit  Bakery    Butterfing  Bakery    Carrot Cak  Bakery    Chips Ahoy  Bakery    Chocolate   Bakery

内连接

内连接就是通过表中的两个字段进行连接。内连接使用关系代数的另一种集合操作，称为交叉。

[sql] view plain copy

print?

sqlite> select * from foods inner join food_types on foods.id = food_types.id;
id type_id name id name
---------- ---------- ---------- ---------- ----------
1 1 Bagels 1 Bakery
2 1 Bagels, ra 2 Cereal
3 1 Bavarian C 3 Chicken/Fo
4 1 Bear Claws 4 Condiments
5 1 Black and 5 Dairy
6 1 Bread (wit 6 Dip
7 1 Butterfing 7 Drinks

sqlite> select * from foods inner join food_types on foods.id = food_types.id;id          type_id     name        id          name      ----------  ----------  ----------  ----------  ----------1           1           Bagels      1           Bakery    2           1           Bagels, ra  2           Cereal    3           1           Bavarian C  3           Chicken/Fo4           1           Bear Claws  4           Condiments5           1           Black and   5           Dairy     6           1           Bread (wit  6           Dip       7           1           Butterfing  7           Drinks

交叉连接

[sql] view plain copy

print?

sqlite> select * from foods, food_types;
id type_id name id name
---------- ---------- ---------- ---------- ----------
1 1 Bagels 1 Bakery
1 1 Bagels 2 Cereal
1 1 Bagels 3 Chicken/Fo
1 1 Bagels 4 Condiments
1 1 Bagels 5 Dairy
1 1 Bagels 6 Dip
1 1 Bagels 7 Drinks
1 1 Bagels 8 Fruit <span style="font-family: Arial, Helvetica, sans-serif;"> </span>

sqlite> select * from foods, food_types;id          type_id     name        id          name      ----------  ----------  ----------  ----------  ----------1           1           Bagels      1           Bakery    1           1           Bagels      2           Cereal    1           1           Bagels      3           Chicken/Fo1           1           Bagels      4           Condiments1           1           Bagels      5           Dairy     1           1           Bagels      6           Dip       1           1           Bagels      7           Drinks    1           1           Bagels      8           Fruit    <span style="font-family: Arial, Helvetica, sans-serif;"> </span>

外连接

例如：

[sql] view plain copy

print?

sqlite> select * from foods left outer join foods_episodes on foods.id = foods_episodes.food_id;
id type_id name food_id episode_id
---------- ---------- ---------- ---------- ----------
1 1 Bagels 1 165
2 1 Bagels, ra 2 154
2 1 Bagels, ra 2 165
3 1 Bavarian C 3 115
4 1 Bear Claws 4 67

sqlite> select * from foods left outer join foods_episodes on foods.id = foods_episodes.food_id;id          type_id     name        food_id     episode_id----------  ----------  ----------  ----------  ----------1           1           Bagels      1           165       2           1           Bagels, ra  2           154       2           1           Bagels, ra  2           165       3           1           Bavarian C  3           115       4           1           Bear Claws  4           67

foods是其中的左表，左外连接试图将foods中的所有行与foods_episodes的所有行进行连接关系（foods.id = foods_episodes.food_id）的匹配，所有匹配的行都包含在结果集中。

名称和别名

[sql] view plain copy

print?

sqlite> select foods.name, food_types.name
...> from foods, food_types
...> where foods.type_id = food_types.id
...> limit 10;

sqlite> select foods.name, food_types.name   ...> from foods, food_types   ...> where foods.type_id = food_types.id   ...> limit 10;

等价于

[sql] view plain copy

print?

sqlite> select f.name, t.name
...> from foods f, food_types t
...> where f.type_id = t.id
...> limit 10;

sqlite> select f.name, t.name   ...> from foods f, food_types t   ...> where f.type_id = t.id   ...> limit 10;

子查询

子查询最常用的地方是where子句，特别是在in操作符中。

[sql] view plain copy

print?

sqlite> select count(*)
...> from foods
...> where type_id in
...> (select id
...> from food_types
...> where name = 'Bakery' or name = 'Cereal');

sqlite> select count(*)   ...> from foods   ...> where type_id  in    ...> (select id   ...> from food_types   ...> where name = 'Bakery' or name = 'Cereal');

阅读全文

0 0