SQL for SQLite

標籤：

文法

verb + subject + predicate

commannds(命令)

SQL由命令組成，以分號為結束。命令有token組成，token由white space分隔，包括空格、tab、換行。

literals

有三種

1. 字串。由單引號'包圍。如果字元中要有'，用兩個連續的'。比如'kenny''s chicken'
2. 數值。整型、小數、科學計數法
3. 二進位。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] headingfrom tableswhere predicategroup by columnshaving predicateorder by columnslimit 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      BakeryBagels, ra  BakeryBavarian C  BakeryBear Claws  BakeryBlack and   BakeryBread (wit  BakeryButterfing  BakeryCarrot Cak  BakeryChips Ahoy  BakeryChocolate   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           Bakery2           1           Bagels, ra  2           Cereal3           1           Bavarian C  3           Chicken/Fo4           1           Bear Claws  4           Condiments5           1           Black and   5           Dairy6           1           Bread (wit  6           Dip7           1           Butterfing  7           Drinks8           1           Carrot Cak  8           Fruit9           1           Chips Ahoy  9           Junkfood10          1           Chocolate   10          Meat11          1           Chocolate   11          Rice/Pasta12          1           Chocolate   12          Sandwiches13          1           Cinnamon B  13          Seafood14          1           Cinnamon S  14          Soup15          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.seasonfrom episodes e1, foods_episodes fe1, foods f,     episodes e2, foods_episodes fe2where  -- 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 descriptionfrom foodswhere description is not nullorder by namelimit 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) frequencyfrom foods fwhere frequency like '%High';

處理 NULL

SQL for SQLite