SQL雜記

導航：首頁 > 程式語言、邏輯學> SQL雜記

SQL Pocket Guide的一些記錄

Ch1-Ch4

• SQL分成：
  • Microsoft SQL
  • MySQL
  • Oracle Database
  • PostgreSQL
  • SQLite
• 另有ANSI SQL這個標準

Select * from 表格名稱;
select... comment
from ..
where...
having...
group by 

口訣：sweaty feet wll give hirroble odors

執行順序：

1. from 取得所有資料
2. where 過濾資料
3. group by 組群於類間
4. having 過濾群
5. select 選擇欄
6. order by 排序結果

欄位、foreign key(FK)、一對多、一對一等、primary key (PK)

有些程式語言提供library操作SQL資料庫

資料庫瀏覽

• DBBrowser for sqlite
• pgadmin

CREATE DATABASE my_db; 建立資料庫

連結既有資料庫：

• host
• port
• database
• username
• password

帳密和程式碼要分開儲放。

• 函數、identifier、alias
• 句子sentence、子句clause

select database.table.id from database.table altername

select id, ... , (select avg(population) from county ) as average.pop ... #子查詢subquery

如果子查詢和外部表格欄位關聯，則應用join，以提升效能。

select id, name from 學生清單;
select * from student;
select name, round(age * 0.9) from student;
select name, age as foo from tab.col 點號.避免重名

select o.id, o.name,
count(o.is) as num_waterfalls,
from owner o left join waterfalls w,  o 為別名
on o.id = w.owner_id
group by o.id, o.name

select all x,y from ... where...

select distinct o.type ... 不重複資料

select count(distinct tyoe)as unique 

with o as (SUBQUERY) select ... from ...

為什麼使用subquery？

1. 分析子問題
2. 提升速度

select x from y limit 10;

使用predicate謂詞

where name not like "%fall%"
owner.id is null
owner.id in (select ...)

having : aggeration聚合的過濾
having 要搭配group by

例如 having count(*) = 6;

where vs having

where：過濾列 having：過濾having的集合

order by x DESC = order by x 遞增排序
order by x ASC 排序遞減
不能用於subquery

limit 10 出現多少的資料。SQLite、MySQL、etc.
group by t.name; 重複的會消除，將資料以AAA BBB CCCC排列

Ch5. 增刪更檔案

• 下列語法以PostgreSQL為準
• database object 是存資料的資料庫物件

• datamodel是綱要，schema是其實作

• 檢視現有資料庫

 \l

• 檢視目前使用之資料庫：

 select current_database;

• 刪除資料庫

drop database my_db;

Ch6 資料類型

建立表格，指定資料型別

CREATE TABLE mytable(
id INT,
name VARCHAR(30),
dt DATE);

資料型別

• integer: 123
• demical: 1.23
• floating pointL 123e10

字串

• charater 'Hello'
• unicode '西瓜'

• date: '2021-10-21'
• time: '2:21:00'
• 也有datetime

其他

• 布林
• 二進位檔

NULL=Null=null指空值

不是datatype

INT, SMALLINT, TINYINT有區別

ALTER TABLE可改變欄位的資料型別。

Numeric data 可以當做 SUM()、ROUND()等數值函數的參數，參Ch.7

oracle的語法區分double和float

CREATE TABLE mytable(foo INT);

INSERT INTO mytable VALUES
(25),
(-525),
(+123);

各個integer的數值範圍，各SQL軟體實作有所別。

mysql 有 signed（預設）和 unsigned兩種數值，範例：

CREATE TABLE tbl(
foo INT UNSIGNED);

匯出 Firefox 瀏覽紀錄

開啟 profile 設定檔的資料夾，先 sqlite3 places.sqlite，進入 sqlite 命令模式：

輸入：

sqlite> .headers on
sqlite> .mode csv
sqlite> .output selected_data.csv
sqlite> SELECT datetime(moz_historyvisits.visit_date/1000000,'unixepoch'), moz_places.url, moz_places.title FROM moz_places, moz_historyvisits WHERE moz_places.id = moz_historyvisits.place_id ; 
sqlite> .quit

導出到 selected_data.csv。