# SQL 學習筆記

## Manipulation
:::spoiler

- explain analyze 實際上要執行的指令

Ex:

    explain analyze select * from lab;

在執行

    select * from lab

的同時會顯示出中間是如何執行的
- create table <表名>(要放入的資料、型別、限制)
在database 當中建立一個table
Ex:

    create table lab( 
        id serial primary key, 
        name text not null
    );

建立一個叫做lab的table
在這個table當中有兩個資料:
1.id 資料型別是serial，不需要人為操作，在新增的時候會自動增加,**不需要寫到insert當中**
2.name 資料型別是text
:::info
:bulb: primary key、 not null 就是constraint, 若我們要存取表格時出現未符合限制的情況就會有error產生
:::
- insert into <表名> (放入這一個表內的元素) values (填入要放的資料)
> 用於每次新增一筆資料

Ex:

    insert into lab(name) values ('become');

在lab這個table多一筆資料，其中name = become
:::warning
在輸入text時 使用 **single quotation**
:::

- insert into <表名> (放入這一個表內的元素) select column1, column2,..... from <表2>

Ex:

    insert into users (name, karma)
    select 'User ' || s, round(random() * 100)
    from generate_series(1, 10) as s;

使用generate_series 產生1到10的數列 並alias 成 s
將 User 跟 s 合併成 name
亂數產生數字做為karma
將這10個數字插入到表中

- select <項目> from <表名>
Ex:

        select name from lab;

將lab這個表當中的name 那一column印出來
:::success
若要將怎麼table 印出來，可以使用wildcard *

    select * from lab;

:::
- alter table <表名> add <列名> <列資料型別>
Ex:

    alter table lab add gender text no null;

在lab這個table當中新增一列資料
名稱:gender, 型別:text, 且限制這一column不能為null

- alter table <表名> drop <列名>
Ex:

    alter table lab drop gender;

在lab這個table當中將gender這一列資料移除

- delete from <表名> where 指定某列資料
Ex:

    delete from lab where gender = 'male';

將lab當中gender 為male的 row都刪除

- update <表名> set 資料 = 新值 where 指定某列資料
Ex:

    update table set gender = 'boy' where gender = 'male';

將gender = male的row 中的gender 改為boy
:::

## Queries
:::spoiler
- 敘述1 and 敘述2
Ex:

    where gender = 'boy' and name = 'become';

選出同時滿足gender = 'boy' 以及 name = 'become'的部分

- 敘述1 or 敘述2
Ex:

    where gender = 'boy' or name = 'become';

選出滿足gender = 'boy' 或者 name = 'become'的部分其，兩個只要滿足一個即可

- select <列名> as <alias 後的新名稱> from <表名>

Ex:

    select name as 'student' from lab;

將lab中的name 這一column 列出來，以student為這一column的title
:::warning
:warning: alias的名稱是個text 所以記得要加 **single quotation**
:::

- like 配上 wildcard character %/_
Ex:
1.

    select name from lab where name like 'Star_';

_有必定有任意一個的意思，所以name當中為Star + 任意一字元的會被選出來
2.

    select name from lab where name like 'Star%';

%有0或多個字元的意思，因此name當中，開頭為Star的皆會被選出來，不論Star後面接了多少個字

:::warning
:warning: like 是 case insensitive，以上面的例子來說, star、Star都會被選出來

:::

- select distinct <項目> from <表名>
Ex:

    select ditinct gender from lab;

將gender這一column中不重複的項目各挑一個出來

- select <項目> from <表名> order by 要排序的項目 排法
Ex:

    select * gender from lab order by name dexc;

將lab中的所有資料取出，用名字來排序
- desc 代表 descending order
- asc 代表 asceinding order

- 資料 is/ is not NULL
Ex:

    where name is null;

NULL的資料我們無法使用 = NULL 來判斷，要使用 is null 或者 is not null

- between 左邊界 and 右邊界
Ex:
1.

    where year between 1990 and 1999;

將year 介於1990到1999的資料全部選出來(**包含邊界**)
2.

    where name between 'B' and 'D';

將名字開頭為B到D的資料選出來
:::danger
:skull_and_crossbones: 英文使用字典序排列，所以雖然包含有D，但是其餘的字串排序在D單一一個字元後，所以沒有被選擇到

:bulb:使用以下方法來正確的選擇B到D開頭的名字

    where name between 'B' and 'E' and name != 'E';

:::
- limit 行數
Ex:

    select * from lab limit 2;

將returning result中的前兩筆出來

:::

## Aggregate functions
::: spoiler
- count(column title)
Ex:

    select count(name) from lab;

計算在lab這個table當中，name總共有幾個row

    select count(*) from  lab where gender is 'male';

計算這個table中gender 是 male的row有幾個

- sum(column title)
Ex:

    select sum(id) from lab;

將lab當中，id這個column的所有數值相加

- max/min (column title)
Ex:

    select max(id) from lab;

找出在lab這個table當中ID最大的數字

- avg (column title)
Ex:

    select avg(id) from lab;

找出在lab這個table當中id的平均

- round (target number, 精確度要到第幾位)
Ex:

    select round(avg(id), 2) from lab;

找出在lab這個table當中id的平均，四捨五入到小數點下第二位

- group by 資料
Ex:

    select gender, avg(id) from lab group by gender;

以gender的分類來分別印出兩種gender的ID平均
:::success
group by後面的args也可以使用數字來簡化，１代表要印出來的第一個東西，在這邊指的就是gender, 2就是avg(id)
:::

- having 條件
Ex:

    select gender,avg(id) from lab 
    group by 1 having count(*) > 10;

以gender的分類，並分別計算male跟female分別有多少人，只印出人數>10的類別
:::

## Multiple tables
:::spoiler

- <表1> join <表2>
Ex:

    select * from order join subscription 
    on 
    orders.subscription_id = subscriptions.subscription_id;

將order跟subsrciption 兩個表中 subscription_id相同的資料們合併在一起印出來
:::success
如果表1跟表2在我們指定要相同的欄位並不相同，那麼他們的資料就不會合並在一起，而是直接不顯示
:::

- <表1> left join <表2>
Ex:

    select * from order left join subscription 
    on 
    orders.subscription_id = subscriptions.subscription_id;

如此一來在合併資料的時候，若有資料是不同時滿足order跟subscription的，order的資料(左邊的table)仍然會保留並印出(以資料 = null的形式)

- <表1> cross join <表2>
Ex:

    select from order cross join subscription;

並沒有將兩張table的資料對上，而只是單純地將兩張表合在一起，通常在使用的時候會搭配 where 來將資料進行篩選

- <select 1> union <select 2>

Ex:

    select * from order
    union 
    select * from subscription

將order 跟 subscription兩張table的結果上下疊在一起印出來

:::warning
:warning: 要將兩個結果要疊在一起是有幾個限制要遵守的
1. 兩張表格有相同數量的column
2. 兩張表格的column 要有相同的data type
:::

:::

## Handling big data

:::spoiler

- create index <index 名稱> on <table名稱> using 資料結構(要處理的資料)
### handling numbers
- b tree
Ex:

    create index posts_idx_ts on posts using btree(ts);

建立一棵btree，使用table這張表當中的ts當中key

### handling text
- generalized inverted index
Ex:

    create extension pg_trgm;
    create index posts_idx_text_trgm on posts
    using gin(text gin_trgm_ops);

:::