live with scope

序

最近突發奇想, 想寫個coreutils的增強版, 實現一個非常簡單的功能: 給目錄加書籤. 比如, cd abc是切換到abc目錄, 但cd ,abc就是切換到abc書籤, 這個書籤可能對應很長的目錄, 比如/etc/apt/sources.d/等.

初步思考了下書籤和目錄的對應用文件存取, 但發現控制邏輯太過複雜, 想著用資料庫看看. 這裡是學習筆記, 主要學習菜鳥教程提供的資料.

至於為什麼用sqlite3, 因為簡單, 輕量級. 本來要實現的項目就不大, 再搞個mysql或postgresql就小題大作了. android上好像有存儲數據需求時也用的是sqlite.

其實項目的難點在於我還沒想好要實現成什麼樣, 如果只是上面的cd, 其實可以簡單的寫個函數: ncd() { [[ ${1:0:1} = "," ]] && cd $(sqlite3 file.db "select path from tbname where bm==${1:1} || cd $1; }. 函數名不能是cd, 會提醒無限嵌套. 也不能代理給ruby腳本, 因為腳本會在子shell執行, 是不會改變當前shell的當前目錄的.

注意, 一般在公眾號發布的都不是最終稿, 筆記嘛, 總會有新認識或新見解, 又不能在公眾號一發再發.可以關注我的github中的筆記.

開始

安裝:

• Ubuntu: sudo apt-get install sqlite3
• Mac: brew install sqlite3

sqlite3 dbname: 若庫名存在則打開,不存在則創建,不指定則創建臨時的庫,退出時刪除.

可以直接在命令行執行命令: sqlite3 dbname .tables 或執行sql語句: sqlite3 dbname "select * from tb" (不需要』;』).

sqlite_master是一個特殊表, 存儲有資料庫的元信息, 如表(table), 索引(index), 視圖(view), 觸發器(trigger), 可通過select查詢相關信息.

sql關鍵字以常用函數, 大小寫不敏感, 反正我喜歡都小寫, 因為caps lock要給esc.

常用命令

sqlite3提供的特殊命令, 以.開頭:

• .help: 幫助
• .databases: 列出資料庫
• .tables: 列出表名
• .open dbname: 打開資料庫
• .save dbname: 保存為資料庫
• .exit: 退出, 或Ctrl-D
• .schema [tbname]: 列出表, 索引, 觸發器的創建語句
• .output fname.txt: 寫結果到文件
• .show, 顯示各種設置的默認值
• .indices tbname, 列出某表的索引

各種設置:

• .echo on|off, 開啟或關閉命令回顯
• .explain on|off, 開啟或關閉適合於EXPLAIN的輸出模式, 更適合人閱讀
• .headers on|off, 是否顯示欄位信息頭
• .stats on|off, 開啟或關閉統計信息
• .timer on|off, 開啟或關閉命令執行的時間測量

模式, 即數據的顯示方式, 有很多種, 但常用的就兩個, .mode line用於表達式運算, .mode column用於表查詢的多欄位顯示. 第二種通常還會開啟信息頭, 即欄位名顯示, .header on. 如果你想知道sql語句的執行時間, 可以.timer on.

常用操作

創建新表create:

create table company (
id int primary key not null autoincrement,
name text not null,
age int not null unique,
address char(50),
salary real default 50000.00 check(salary>0)
);


注:

• int, text, real, char(5), 都是欄位的類型
• char(50), 表明此欄位存儲字元不超過50個
• not null, 表明此欄位不能為空
• primary key, 表明此欄位為基鍵, 不能重複
• unique, 確保某列中沒有重複值
• default, 當列沒有值時,提供默認值
• check, 確保某列中的所有值滿足一定條件
• autoincrement, 確保列中值自動增加, 自然無需手動提供

修改表alter:

alter table company add column sex char(1);
--為company表添加列sex, 類型為char(1)
alter table company rename to old_company;
--為表重全名


注:

• Sqlite3中, alter允許用戶重命名錶,或向現有表添加一個新列
• 但不能重命名列, 刪除列, 從表中添加或刪除約束

刪除表drop:

drop table company;


創建記錄insert:

insert into company (id, name, age, address, salary) values (1, Paul, 32, Beijing, 20000.00);
insert into company values (1, Paul, 32, Beijing, 20000.00);
--插入所有欄位時可省略列名
insert into company_bkp select * from company;
--將company表中的所有記錄全部插入到company_bkp表中, 兩表結構必須相似


更新記錄update:

update company set address=Texas where id==6;
--將id為6的記錄更新address欄位為Texas
update company set address=Texas, salary=20000.00;
--將所有記錄的address欄位更新為Texas, salary欄位為20000


檢索記錄select:

select * from company;
select id, name from company;
--查詢company表中id和name欄位
select tbl_name from sqlite_master where type==table;
--查詢當前資料庫存在的表
select current_timestamp;
--查詢當前時間戳
select * from company limit 6;
--只顯示查詢結果的前6行
select * from company limit 3 offset 2;
--只顯示從第3行起, 再多2行, 一共3行
select * from company order by salary asc;
--以salary欄位升序顯示記錄, desc為降序
select * from company order by name, salary asc;
--將結果按name和salary欄位升序顯示, 即name相同的按salary排序
select name, sum(salary) from company group by name;
--將結果中相同name的salary相加, 再構成name, sum(salary)列表
select name, sum(salary) from company group by name order by name;
--同上, 將結果以name升序顯示
select * from company group by name having count(name) < 2;
--以name分組, 相同name記錄數小於2, having設置分組的過濾條件
select distinct name from company;
--去重, 相同name不顯示
select * from company where salary>10000 group by name having count(name)>=2 order by name
--相同name的記錄數大於或等於2, 且salary大於10000, 以name升序顯示
select * from company cross join department;
select * from company, department;
--將company的每一行與第二個表的每一行進行匹配, 分別有x和y行, 則結果有x*y行, 分別有x和y列, 則結果有x+y列. 交叉連接可能產生非常大的表
select * from company [inner] join department on company.id==department.emp_id;
--選取company的id列與department的emp_id列相等的行進行連接, 內連接是默認連接, 可省略inner, 橫向連接
select * from company join department using (id);
--使用兩表共有的id列進行相同值連接
select * from company natural join department;
--自動測試存在兩個表中的每一列的值之間相等值
select * from company left outer join department on company.id==department.emp_id;
--不同於內連接, 左外連接還會合併進第一個表的非匹配行, 這些行多餘的列, 即對應第二個表的列為null. 之所以第一表顯示, 因為是left嘛.
select col1, col2, ... from table1 where conditions
union [all]
select col1, col2, ... from table2 where conditions;
--不侷限於上面的語句, 事實上union將兩個select的結果縱向連接去重.因此這要求結果必須列相同, 列類型相同. join則是橫向連接. union all不去重.
select c.id, c.name, c.age, d.dept from company as c, department as d where c.id==d.emp_id;
select c.id, c.name, c.age, d.dept from company as c join department as d on c.id==d.emp_id;
--通過as給表起別名
select * from company where id in (select id from company where salary > 45000);
select * from company where salary > 45000;
--子查詢, `()`中的select先執行, 此處兩個查詢相同, 可與select, insert, update, delete混合使用


注:

• where在所選列上設置條件, having在由group by創建的分組上設置條件
• where -> group by -> having -> order by

刪除記錄delete:

delete from company where id==7
--刪除id為7的記錄
delete from company;
--刪除所有記錄


觸發器trigger:

create trigger audit_log after insert on company
begin
insert into audit (emp_id, entry_date) values (new.ID, datetime(now));
end;
--創建觸發器audit_log, 當向company表執行insert操作後, 會觸發向audit表插入記錄, 值為插入company表的id和執行時間戳.
select name from sqlite_master where type==trigger and tbl_name==company;
--列出關聯於company表的觸發器
drop trigger audit_log;
--刪除觸發器


索引index:

create index salary_index on company (salary);
--對company表的salary列創建索引salary_index
select name from sqlite_master where type==index and tbl_name==company;
--列出對應於company表的索引
select * from company indexed by salary_index where salary > 5000;
--使用索引從company表中選擇數據
drop index salary_index
--刪除索引


注:

• 索引可加快數據檢索, 但不利於數據更新和插入
• 索引不影響數據
• 可對多列索引, (col1, col2)
• 創建表時會自動創建主鍵primary key的索引

視圖view:

create view company_view as
select id, name, age from company;
--為company表的id,name,age列創建視圖
select * from company_view;
--列出視圖的所有數據, 因為其只有真表的id,name,age三個列, 因此這裡只列出三列
drop view company_view;
--刪除視圖


注:

• 可將視圖認為是虛表, 它本身不真正存儲數據, 它只是提供真正表的一個觀察角度
• 因為視圖不是真正的表, 因此並不能插入或更新數據, 但可能創建觸發器, 當插入或更新數據時, 執行真正的操作.

事務:

begin; --事務開始
delete from company where age==25; --刪除age等於25的所有記錄
rollback; --回滾, 即恢複數據
commit; --提交更改


注:

• 事務具有原子性, 即事務要麼成功要麼失敗, 而不會停留在中間狀態
• 事務只與insert, update, delete一起使用

表達式

算術運算符, + - * / %, 加減乘除餘.

select 10+20;


邏輯運算符: ==, !=, >, <, >=, <=. and, or

where col1 >= 25 and col2 <= 90
where col is not null
where col like Ki%
--欄位為Ki開頭的字串, %:零或一或多個, _:一個
where col glob Ki*
--同上, 大小寫敏感, *:零或一或多個, ?:一個
where col in (25, 27)
--欄位為25或者27
where col not in (25, 27)
--欄位不是25也不是27
where col between 25 and 27
--欄位在25到27之間
select age from company
where exists (select age from company where salary > 65000)
-- 子查詢, 如果存在salary大於65000的age欄位, 則列出所有age欄位


位運算符: & | ~ << >>, 並或反左右移.

select 60 | 13


null值, 只能用where col is null/not null, 而不能跟別的值比較. null值與零值或包含空格的欄位是不同的, null是沒有值, 而非值為空.

時間函數

• date, 日期
• time, 時間
• datetime, 日期和時間
• strtime, 格式化字串

select date(now);
select strtime(%s, now);


常用函數

sqlite提供了少量常用的函數:

• count, 謀算表的行數
• max, min, 選擇某列的最大值, 最小值
• avg, 計算某列的平均值
• sum, 計算某列的總和
• random, 返回偽隨機數
• abs, 返回絕對值, 所有字串返回0.0
• upper, 將字元串轉換為大寫字母
• lower, 將字元串轉換為小寫字母
• length, 返回字串的長度
• sqlite_version, 返回sqlite的版本

select count(*) from company;
--company表的行數, 注意, 指定特定列時, 為null值的記錄不計數
select max(salary) from company;
--選擇company表的salary列的最大值
select avg(salary) from company;
select sum(salary) from company;
select random();
select abs(-5);
select upper(name) from company;
--列出company表的name列的大寫


?
推薦閱讀：

相關文章