---
# System prepended metadata

title: SQL 學習筆記

---

# 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);
    ```
:::