[PostgreSQL] 常用SQL指令集

 

- 查詢Table的某筆資料

select "欄位名" from "表格名" where "條件"

範例：

select * from tb_customer where customer_id='xxxxxx';

select name from tb_customer where customer_id='xxxxxx' order by age asc;      (order by "欄位名" [desc, asc])

- 查詢數量

select count(*) from "表格名" where "條件"

範例：

select count(*) from tb_customer where age > 20;     (從tb_customer表格找age>20的數量)

- 查詢欄位值的種類與數量

select distinct "欄位名" from "表格名"

會秀出欄位目前有哪些值

select "欄位名", count(*)  from "表格名" group by "欄位名"

範例：

select gender, count('gender') as gender_count from tb_customer group by gender;     (依照性別gender分，可以看男女數量)

- 更新欄位值

update "表格名" set "欄位名" = "新值" where "條件"

範例：

update tb_customer set points = 100 where customer_id = 'xxxxxxxx';

- 新增一筆數據

insert into "表格名" ("欄位1", "欄位2", ...) values ("欄位1的值", "欄位2的值")

範例：

insert into tb_customer (customer_id, name, age) values ('12345', 'Ann', 21);

- JSON欄位的取值

假設 tb_customer 有個 extra 欄位是 json type，裡面的值大概都長這樣 {"money": 1000, "point": {"free": 100, "paid": 0}}

文件：https://www.postgresql.org/docs/9.4/functions-json.html

檢查 json是否有特定key

select * from tb_customer where extra::jsonb?'money';    (此指令會撈出extra json裡有'money'這個key的tb_customer數據)

取json裡特定值

select * from tb_customer where (extra->>'money')::integer> 1000;    (此指令會撈出extra json裡'money'值 > 1000 的tb_customer數據，:: 是轉型用)

select * from tb_customer where (extra ->'point'->>'paid')::integer> 100;      ( ->：get JSON object field by key，->>：get JSON object field as text，JSON object沒辦法轉型成integer，所以像上面的 extra->>'money' 如果改寫成 extra->'money' 會噴cannot cast type json to integer error，extra->>'money' 是text則可以轉integer所以沒問題)    

- 合併表

表一  tb_student

class	name
A	Andy
B	Mary

表二  tb_point

name	subject	point
Andy	Math	80
Mary	Math	90
Ann	Math	100

- INNER JOIN

select tb_student.name, class, point from tb_student inner join tb_point on tb_student.name= tb_point.name;

(name 在 tb_student 和 tb_point 都有同名的欄，所以一定要指名是哪個表的欄位 tb_student.name，像 class 只有 tb_student 表有就可以寫 class 就好 )

name	class	point
Andy	A	80
Mary	B	90

- LEFT OUTER JOIN：左表多的列會保留

select tb_student.name, class, point from tb_student left outer join tb_point on tb_student.name= tb_point.name;

name	class	point
Andy	A	80
Mary	B	90
Lucy	A

- RIGHT OUTER JOIN：右表多的列會保留

select tb_student.name, class, point from tb_student right outer join tb_point on tb_student.name= tb_point.name;

name	class	point
Andy	A	80
Mary	B	90
		100

- FULL OUTER JOIN：左、右表多的列都會保留

select tb_student.name, class, point from tb_student full outer join tb_point on tb_student.name= tb_point.name;

name	class	point
Andy	A	80
Mary	B	90
Lucy	A
		100