sqlite学习3
SQL for SQLite
语法
verb + subject + predicate
commannds(命令)
SQL由命令组成，以分号为结束。命令有token组成，token由white space分隔，包括空格、tab、换行。
literals
有三种
- 字符串。由单引号
'包围。如果字符中要有'，用两个连续的'。比如'kenny''s chicken' - 数值。整型、小数、科学计数法
- 二进制。
x'0000'。二进制值长度必须是8bit的倍数。
关键字和标识符
于此，SQL是对大小写不敏感的。
SELECT * from foo;
SeLeCt * FrOm FOO:
是一样的。对字符串，是大小写敏感的。Mike和mike不一样。
注释
- 单行：
--开头 - 多行。
/**/
创建数据库
创建table
SQL由几部分组成。
- DDL(data definition language). 用于创建和destory数据库对象。
- DML(data manipulation language). 用于操作对象。
创建数据库属于DDL。语法如下：
creat [temp] table table_name (column_definitions[, constraints])
temp是指创建的数据库会在session结束时被消失。(数据库连接不存在)
column_definitions由逗号,分隔的列定义组成。每一列的定义包括name、domain、逗号分隔的一系列列约束。
domain,也叫type,也叫"storage class".有五种类型integer, real, text, blob, null.
alter table
可以重命名table或者增加列。
alter table table {rename to name | add column column_def}
查询数据库
关系操作
- 基础操作（在集合理论中有基础）
- 约束
- 投射
- 笛卡尔积
- 联合
- 差(difference)
- 重命名
- additional operations(为了使用方便，一些常用的操作)
- 交叉
- 自然联接
- 赋值
- 扩展操作
- 一般(generalized) 投射
- 左外联
- 右外联
- 全外联
SQLite支持除了右外联和全外联外所有的ANSI SQL定义的关系操作。
select and Operational Pipeline
select [distinct] heading
from tables
where predicate
group by columns
having predicate
order by columns
limit count, offset
过滤
为每一行执行where语句。
values
表达方式有很多。
- literal values
- 变量
- 表达式
- 函数的值
- ...
操作
- like. 用于匹配字符串和给定的pattern。通配符
%和_ - glob. 和like很像。大小写敏感，通配符
*和?
限制和排序
-
limit. 不在ANSI标准里 -
offset. 不在ANSI标准里 -
order by, asc default.
同时使用limit和offset时，可以用,代替offset
可以使用limit不用offset，使用offset必须使用limit
函数和总计（aggregate）
分类(grouping)
group by. 执行在where语句和select语句之间。输入为where语句的输出，将其分为若干组。每一组输入select语句，输出一个总计(aggregate)结果。
having 过滤group by输出的group。以统计形式表达。
select type_id, count(*) from foods group by type_id having count(*) < 20;
去除重复
distinct
joining tables
外键：一个表中某一列中的值在另一个表中是主值，这种关系叫做外键。
使用外键可以将表连接起来。
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
内联
内联：两个表通过表中列的关系连接起来，最常用也最重要。
内联使用了交集(interdsection)操作。
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
8 1 Carrot Cak 8 Fruit
9 1 Chips Ahoy 9 Junkfood
10 1 Chocolate 10 Meat
11 1 Chocolate 11 Rice/Pasta
12 1 Chocolate 12 Sandwiches
13 1 Cinnamon B 13 Seafood
14 1 Cinnamon S 14 Soup
15 1 Cookie 15 Vegetables
交叉连接
把A表每一列和B表每一列连接起来，会产生很多无意义的结果。
外联
外联：外联选出内联的所有行和不符合关系条件的一些行。
select *
from foods left outer join foods_episodes on foods.id=foods_episodes.food_id;
foods是左表，对于foods的每一行，都尝试建立连接，并通过关系条件。符合条件的行被展示，不符合的也会被展示，episodes列展示为null。
全外联:左外联和右外联的合集。
自然连接
内联的特殊情况。通过相同的列名连接。如果列的名称改变，结果会不同，所以尽量不要使用。
推荐的语法
使用显式的join语法。
select * from foods, food_types where foods.id=food_types.food_id;
这个是旧式的语法。
select * from foods inner join food_types on foods.id=food_types.food_id;
这个是新式的语法。
names and aliases
select f.name as food, e1.name, e1.season, e2.name, e2.season
from episodes e1, foods_episodes fe1, foods f,
episodes e2, foods_episodes fe2
where
-- Get foods in season 4
(e1.id = fe1.episode_id and e1.season = 4) and fe1.food_id = f.id
-- Link foods with all other epsisodes
and (fe1.food_id = fe2.food_id)
-- Link with their respective episodes and filter out e1's season
and (fe2.episode_id = e2.id AND e2.season != e1.season)
order by f.name;
子查询(subquey)
把select的结果作为from、orderby语句的输入。
联合查询(compound query)
有点像子查询的对面。使用union,intersect,except来处理多个查询的结果。
前提条件
- 需要处理的结果的列数相同
- 只能有一个
order by语句，在联合查询的最后。
条件结果(conditional results)
- 使用静态值
select name || case type_id
when 7 then ' is a drink'
when 8 then ' is a fruit'
when 9 then ' is junkfood'
when 13 then ' is seafood'
else null
end description
from foods
where description is not null
order by name
limit 10;
- 在when中使用表达式
select name (select
case
when count(*) > 4 then 'Very High'
when count(*) = 4 then 'High'
when count(*) in (2,3) then 'Moderate'
else 'Low'
end
from foods_episodes
where food_id=f.id) frequency
from foods f
where frequency like '%High';
