学习使用SQLite（版本3.7.4）（中E）

针对两个关系A、B ，union关键字将两者连接成为一个只包含A和B中非重复字段的单一关系。SQL中，union联合两个select结果，默认消除重复数据（利用union all保留全部数据）：

select f.*,top_foods.count from foods f
inner join
(select food_id,count(food_id) as count from foods_episodes
group by food_id
order by count(food_id) desc limit 1) top_foods
on f.id = top_foods.food_id
union
select f.*,bottom_foods.count from foods f
inner join
(select food_id,count(food_id) as count from foods_episodes
group by food_id
order by count(food_id) desc limit 1) bottom_foods


on f.id = bottom_foods.food_id
order by top_foods.count desc;

，这是要找出foods表中最高频率和最低频率的食品。

intersect操作两个关系A和B，选择在A也在B中的行。会用intersect找出处于3和5之间的处于前10位的食品：

select f.* from foods f
inner join
(select food_id,count(food_id) as count from foods_episodes
group by food_id
order by count(food_id) desc limit 10) top_foods
on f.id = top_foods.food_id
intersect
select f.* from foods f
inner join foods_episodes fe on f.id=fe.food_id
inner join episodes e on fe.episode_id = e.id
where e.season between 3 and 5
order by f.name;

except 操作两个关系A和B，找出所有在A而不在B的行：

select f.* from foods f
inner join
(select food_id,count(food_id) as count from foods_episodes
group by food_id
order by count(food_id) desc limit 10) top_foods
on f.id = top_foods.food_id


except


select f.* from foods f
inner join foods_episodes fe on f.id=fe.food_id
inner join episodes e on fe.episode_id = e.id
where e.season between 3 and 5
order by f.name;

注意：复合查询只是要求在结尾有一个order by 语句。

处理SQLite中的null：

null是缺失信息的占位符，本身不是值。null与真假值之间的关系：