《sqlite權威指南》讀書筆記 （一）

標籤：des   http   io   ar   使用   sp   for   on   資料   

 

一 常量

字串常量   (使用單引號括住。如果常量中有單引號，使用兩個單引號來表示。大小寫敏感)

數字常量

二進位常量

 

二 關鍵字

關鍵字大小寫不敏感

 

三 注釋

單行注釋使用 --XXXXXXX

多行注釋使用/*XXXXXX*/

 

四 建立表

CREATE [TEMP | TEMPORARY] TABLE table_name (column_definitions [constraints,]);

 

五 修改表

ALTER TABLE table_name {RENAME TO new_table_name | ADD COLUMN column_definitions};

 

六 關係操作

基本操作

Restriction （限制）

Projection （投影）

Cartesian Product （笛卡爾積）

Union （聯合）

Difference （差）

Rename （重新命名）

附加操作

Intersection （交叉）

Natural Join （自然串連）

Assign （賦值）

擴充操作

Generalized Projection （廣義投影）

Left Outer Join （左外串連）

Right Outer Join （右外串連）

Full Outer Join （全外串連）

 

七 LIKE 和 GLOB

SELECT  column_name FROM table_name WHERE column_name LIKE ‘xxx‘;

 

LIKE 後跟的模式可以進行字串匹配。

%可以與任意0個或多個字元匹配。為貪婪匹配。大小寫不敏感。

‘%x‘ 表示以x結尾

‘x%‘ 表示以x開頭

‘%x%‘ 表示包含x

_可以與任意一個字元匹配。

 

可以使用 NOT LIKE ‘xxx‘表示不包含某模式。

PS：‘xx‘表示一個模式，並不是只能與text類型的列進行匹配，同樣可以與integer類型的列進行匹配。

 

可以將LIKE改為GLOB，兩者用法十分類似，不過 GLOB大小寫敏感，且＊和_來表示。

 

八 限定和排序

SELECT column_name FROM table_name  ORDER BY column_name [ASC|DESC] LIMIT 1...9 OFFSET 1....9;

 

ORDER BY 表示排序。預設為升序，加入desc表示降序。可以有多列，用逗號分開。第一欄位重複，則根據第二欄位排序，以此類推。

 

LIMIT 後的數字表示，在排序結果中返回的行數。

OFFSET後的數字表示跳過的行數。例如：OFFSET 1 表示跳過一行，重第二行開始。

可以簡略的寫作LIMIT 2，3 和LIMIT 3 OFFSET 2意思一樣。

 

九 函數和彙總

函數：可以把 upper(column_name) 作為一列，執行SELECT upper(name)......可以將name列的所有行換為大寫。

彙總：理解做“對錶中的每一行執行某種操作”。  執行SELECT count(*) From table;可以返回table表中行的數量。

 

十 分組

“彙總的主要部分就是分組，也就是說，彙總不只是能夠計算整個結果集的彙總值，還可以把結果集分成多個組，然後計算每個組的彙總值。”

 

select type_id,name from foods group by type_id;

這一句，會根據type_id進行分組，name返回該組的最後一條。

select type_id，count(*) from foods group by type_id;

這一句，依然是根據type_id進行分組，count(*) 則是對改組進行彙總求職。

 

十一 去掉重複

SELECT DISTINCT column_name FROM table_name;

該句先得到所有，再進行刪除操作

 

十二 多表串連

內串連例句：

SELECT * FROM foods INNER JOIN food_types ON foods.id == food_types.id;

交叉串連例句：

SELECT * FROM foods,food_types;

左串連例句：

SELECT * FROM foods LEFT OUTER JOIN food_types ON foods.id == food_types.id;

應該避免使用隱式串連，雖然可以簡潔的實現。

標準形式：

select heading from left_table join_type right_table on join_condition;

 

插入紀錄

INSERT INTO table_name (column_list) VALUES (value_list);

 

插入一行例句

INSERT INTO foods (name, type_id) VALUES (‘Cinnamon Bobka‘,1);

column_list 中可以包含主鍵欄位，但要保證插入的主鍵欄位的唯一性，否則會報“PRIMARY KEY must be unique”

last_insert_rowid();函數返回最後一個增長值。

插入一組行例句

INSERT INTO foods

SELECT NULL, type_id, name FROM foods where name = ‘Choco‘;
插入多行例句

CREATE TABLE foods2 AS SELECT * FROM FOODS;

 

更新資料

UPDATE table_name SET update_list WHERE predicate;

例如：

update foods set name = ‘apple‘,food_types = 3 where name ==‘banana‘;

 

刪除資料

DELETE FROM table_name WHERE predicate;

例如：

delete from foods where id > 500;

 

資料完整性

值域完整性 實體完整性 參考完整性 使用者定義完整性

 

實體完整性 

主鍵由至少帶有unique約束的一個或一組欄位組成。

唯一性限制式 -- unique

主鍵

如果未定義，則系統自動定義。為64bit的整形欄位，名為rowid，也叫做_rowid,oid。sqlite為主鍵欄位提供自增長性質。如果定義欄位類型為 integer primary key，sqlite將為該欄位建立預設值，並確保其唯一性。實際上，該欄位就是rowid的別名。最大值為2的64次方，當達到最大值時，會搜尋未使用的值。所以，新建立的rowid並不總是遞增的。

autoincrement 會組織sqlite回收主鍵，並在達到最大值時停止。

主鍵即使不使用整型值，系統也會在內部維護一個rowid欄位。

例如：

sqlite> create table pkey(x text, y text, primary key(x,y));
sqlite> insert into pkey values(‘x‘,‘y‘);
sqlite> insert into pkey values(‘x‘,‘x‘);
sqlite> select rowid, x, y from pkey;

rowid|x|y
1|x|y
2|x|x

 

值域完整性

預設值（default）

如果用insert語句插入記錄時沒有為該欄位制定值，關鍵字default將為欄位提供一個預設值。

是處理非null的一種策略。

格式為 default 值

例如 name not null default NUKNOW 插入語句為指定name欄位的值時，會自動用‘NUKNOW’當做name的值。

另外，default 接受三種預定義的保留字，為 current_time,current_data,curremt_timestamp

 

NOT NULL 約束

遇見null值會報錯。

 

check 約束

可以定義運算式來判斷值，例如： check(old > 0) 表示年齡必須大於0，插入-1歲會報錯。

 

外鍵約束

文法如下

create table table_name

( column_definition references foreign_table (column_name)

  on {delete | update} integrity_action

  [not] deferrable [initially {deferred|immediate},]

);

 

定序

關鍵字為：collate

collate nocase 大小寫不敏感 a==A

 

儲存類

sqlite有5個原始的資料類型，被稱作儲存類。

integer 整數                   沒有小數點被指派為該值。

real 實數                        有小數點。。。。

text 文本                         有引號括住。。。。

blob 二進位大對象         x‘abcd’。。。。

NULL 表示沒有值          用NULL說明的值。。。

typeof()傳回值的類型。

一個欄位可以有不同的資料類型

定序如下

blob > text > integer real (通過值的大小排序) > NULL

 

視圖

下面是一篇關於視圖的介紹性文章

視圖的介紹

定義視圖文法如下：

create view name as select-stmt；

刪除視圖的文法如下：

drop view name；

 

索引

建立索引文法： 

create index [unique] index_name on table_name(columns);

刪除索引：

drop index index_name;

索引的定序

建立文法：

create index foods_name_idx on foods (name collate nocase);

 

觸發器

文法：

create [temp|temporary] trigger name

[before|after] [insert|delete|update|update of columns] on table

action

可以使用觸發器在sqlite中實現可更新的視圖

 

事務

事務由3個命令控制 ：begin  commit  rollback

begin 開始一個事務，串連結束前，未發出commit，則全部命令撤銷，rollback還原begin之後的所有操作。

savepoint xxx 儲存一個位置，rollback to xxx，返回到某位置

 

資料庫鎖

sqlite有五種不同的鎖狀態 ：未加鎖 共用 預留 未決 排他

 

事務類型

sqlite有三種不同的失誤類型，為了避免死結，因為sqlite一個時刻允許多個串連讀取資料，卻只允許一個串連寫資料。

begin[deferred | immediate | exclusive] transaction;

基本準則：如果使用的資料庫沒有其他串連，用begin就夠了，但資料庫如果有其他會對資料庫執行寫操作的串連，就應該使用begin immediate或 begin exclusive

 

資料庫管理

附加資料庫

attach [database] filename as database_name;

分離資料庫

detach [database] database_name;

《sqlite權威指南》讀書筆記 （一）