สรุป PostgreSQL แบบสั้น ๆ จากการเรียนด้วยตัวเอง
PostgreSQL Cheatsheet from My Self-Paced Learning
บทความนี้เป็นสรุป SQL บน PostgreSQL ที่ผมเคยเรียนมานานแล้วจากคอร์สชื่อ The Complete Python & PostgreSQL Developer
ซึ่งผมสรุประหว่างเรียนเอาไว้ พอดีมีโอกาสได้กลับมาทวนความจำอีกครั้งแล้วเห็นว่ามันอาจจะมีประโยชน์กับคนอื่น ๆ เลยกด publish ออกมาครับ
ก่อนจะเริ่ม…รบกวนกด Like/Follow/Subscribe ให้ผมด้วยนะครับ 🙏
- 💙 Facebook: Nopnithi Tech
- ❤️ YouTube: Nopnithi Tech
- 💙 LinkedIn: Nopnithi (Game) Khaokaew
- 🖤 Medium: Nopnithi Khaokaew (Game)
______________________________________________________
Table:
- customers
- items
- purchases
______________________________________________________
SELECT command
SELECT * from customers;จะได้
first_name | id | last_name------------+----+-------------Rolf | 1 | SmithJose | 2 | SalvatierraAnne | 3 | WatsonCraig | 4 | ScottMichael | 5 | Adam(5 rows)
Select ออกมาแค่บาง column เท่านั้น
SELECT customers.first_name from customers;หรือ
SELECT first_name from customers;จะได้
first_name------------RolfJoseAnneCraigMichael(5 rows)
Select ออกมาหลาย ๆ column
SELECT first_name, last_name from customers;จะได้
first_name | last_name------------+-------------Rolf | SmithJose | SalvatierraAnne | WatsonCraig | ScottMichael | Adam(5 rows)
และสามารถเปลี่ยนชื่อ column ได้ เช่น เปลี่ยนจาก first_name เป็น “NAME”
SELECT first_name AS "NAME" from customers;จะได้
NAME---------RolfJoseAnneCraigMichael(5 rows)
______________________________________________________
WHERE
SELECT first_name, last_name from customers
WHERE first_name = 'Rolf' OR last_name = 'Watson';จะได้
first_name | last_name------------+-----------Rolf | SmithAnne | Watson
______________________________________________________
LIMIT
SELECT first_name, last_name from customers LIMIT 2;จะได้ออกมาแค่ 2 rows เท่านั้น
first_name | last_name------------+-------------Rolf | SmithJose | Salvatierra
______________________________________________________
UPDATE
UPDATE items SET price=5.00 WHERE id=3;แก้ไขค่าของ price เป็น 5.00 ที่ row ที่มี id=3 ใน table ชื่อ items
ก่อน update
name | id | price--------------+----+--------Pen | 1 | 5.00Fountain Pen | 2 | 11.30Ink | 3 | 3.50Laptop | 4 | 899.00Screen | 5 | 275.50Hard Drive | 6 | 89.99
หลัง update
name | id | price--------------+----+--------Pen | 1 | 5.00Fountain Pen | 2 | 11.30Laptop | 4 | 899.00Screen | 5 | 275.50Hard Drive | 6 | 89.99Ink | 3 | 5.00
______________________________________________________
DELETE
DELETE FROM items where id=4;แต่จาก database ใน lab จะไม่สามารถลบได้เพราะมีการอ้างอิง key (id)=(4) ไปยัง table ชื่อ “purchases” อยู่ ดังนั้นเราต้องไปลบที่เชื่อมกันไว้ออกก่อน
______________________________________________________
LIKE (Wildcard สำหรับใช้กรองข้อมูล)
SELECT * FROM customers WHERE last_name LIKE '%';ตัวอย่าง Pattern แบบต่าง ๆ
- % คือ แทนอะไรก็ได้ (any)
- _ คือ หนึ่งตัวแทนตัวอักษรหนึ่งตำแหน่ง เช่น _ _ _ แปลว่าต้องมีแค่ 3 ตัวอักษร
- %t% คือ มีตัว t อยู่ภายในนั้น
- %t_ คือ มีตัว t และหลังตัว t ต้องตามด้วยตัวอักษรอะไรก็ได้หนึ่งตัวเท่านั้น
______________________________________________________
JOIN
- คือ key element ของ relational database
- สามารถดึงข้อมูลจากหลาย table ได้ภายในครั้งเดียว
ประโยชน์คือเราจะสามารถดึงข้อมูลจากหลาย table มารวมกันได้
Table ที่ใช้ทดสอบ
customers
first_name | id | last_name------------+----+-------------Rolf | 1 | SmithJose | 2 | SalvatierraAnne | 3 | WatsonCraig | 4 | ScottMichael | 5 | Adam(5 rows)
purchases
id | item_id | customer_id----+---------+-------------2 | 5 | 13 | 6 | 14 | 1 | 35 | 3 | 56 | 2 | 58 | 2 | 49 | 3 | 410 | 6 | 5(8 rows)
สังเกตว่าใน purchases มันจะมองไม่เห็นข้อมูลอะไรเลยสำหรับรายการสั่งซื้อแต่ละอัน มันต้องไปอิงจาก table อื่น ๆ มาด้วย
INNER JOIN
- เป็น set ของการ intersection
- select rows จาก table1 และ table2 โดยทั้งสองมี column ที่เลือกตรงกัน
SELECT * FROM customers
INNER JOIN purchases
ON customers.id = purchases.customer_id;จะได้ตามนี้ สังเกตว่าข้อมูลอิงจาก purchases เป็นหลัก
first_name | id | last_name | id | item_id | customer_id------------+----+-----------+----+---------+-------------Rolf | 1 | Smith | 2 | 5 | 1Rolf | 1 | Smith | 3 | 6 | 1Anne | 3 | Watson | 4 | 1 | 3Michael | 5 | Adam | 5 | 3 | 5Michael | 5 | Adam | 6 | 2 | 5Craig | 4 | Scott | 8 | 2 | 4Craig | 4 | Scott | 9 | 3 | 4Michael | 5 | Adam | 10 | 6 | 5(8 rows)
LEFT JOIN
- select ทุก rows ของ table ซ้าย และ rows จาก table ขวาถ้า match กัน
SELECT * FROM customers
LEFT JOIN purchases
ON customers.id = purchases.customer_id;จะได้
first_name | id | last_name | id | item_id | customer_id------------+----+-------------+----+---------+-------------Rolf | 1 | Smith | 2 | 5 | 1Rolf | 1 | Smith | 3 | 6 | 1Anne | 3 | Watson | 4 | 1 | 3Michael | 5 | Adam | 5 | 3 | 5Michael | 5 | Adam | 6 | 2 | 5Craig | 4 | Scott | 8 | 2 | 4Craig | 4 | Scott | 9 | 3 | 4Michael | 5 | Adam | 10 | 6 | 5Jose | 2 | Salvatierra | | |(9 rows)
ความต่างคือ LEFT JOIN จะแสดง customer ออกมาจนครบทุกคนแม้จะไม่ได้ match กัน โดยคนที่ไม่ match ก็จะเป็น blank เอาไว้
ในขณะที่ INNER JOIN นั้นจะแสดงเฉพาะ customer ที่ match กับ customer_id ใน purchases เท่านั้น จะเห็นว่าไม่โชว์บรรทัดของ Jose เพราะเค้าไม่ได้ซื้ออะไรเลย
RIGHT JOIN
- ตรงข้ามกับ LEFT JOIN
- select ทุก rows ของ table ขวาและ rows จาก table ซ้ายถ้า match กัน
SELECT * FROM customers
RIGHT JOIN purchases
ON customers.id = purchases.customer_id;จะได้
first_name | id | last_name | id | item_id | customer_id------------+----+-----------+----+---------+-------------Rolf | 1 | Smith | 2 | 5 | 1Rolf | 1 | Smith | 3 | 6 | 1Anne | 3 | Watson | 4 | 1 | 3Michael | 5 | Adam | 5 | 3 | 5Michael | 5 | Adam | 6 | 2 | 5Craig | 4 | Scott | 8 | 2 | 4Craig | 4 | Scott | 9 | 3 | 4Michael | 5 | Adam | 10 | 6 | 5(8 rows)
สังเกตว่าจะไม่มี blank เลยเพราะเราดึงข้อมูลทุก rows ใน table ขวา (purchases) และ row ใน table ซ้าย (customers) ที่ match กัน และแน่นอนว่ามันจะต้อง match กันทั้งหมดเพราะข้อมูลการซื้อ (purchase) นั้นจะเกิดขึ้นได้ก็ต้องมีผู้ซื้อ (customer) ยกเว้นรายการซื้อบางรายการที่ไม่ได้ระบุ customer_id เอาไว้
FULL JOIN
- รวม LEFT JOIN กับ RIGHT JOIN เข้าด้วยกัน
- select ทุก rows ของ table ขวา และทุก rows จาก table ซ้ายถ้ามัน match กันก็เทียบกันมา ถ้าไม่ match ก็เป็น blank
SELECT * FROM customers
FULL JOIN purchases
ON customers.id = purchases.customer_id;จะได้
first_name | id | last_name | id | item_id | customer_id------------+----+-------------+----+---------+-------------Rolf | 1 | Smith | 2 | 5 | 1Rolf | 1 | Smith | 3 | 6 | 1Anne | 3 | Watson | 4 | 1 | 3Michael | 5 | Adam | 5 | 3 | 5Michael | 5 | Adam | 6 | 2 | 5Craig | 4 | Scott | 8 | 2 | 4Craig | 4 | Scott | 9 | 3 | 4Michael | 5 | Adam | 10 | 6 | 5Jose | 2 | Salvatierra | | |(9 rows)
หรือสามารถดึงเฉพาะ column ที่สนใจออกมาก็ได้ เช่น
SELECT customers.first_name, customers.last_name, purchases.id FROM customers
LEFT JOIN purchases
ON customers.id = purchases.customer_id;ก็จะได้
first_name | last_name | id------------+-------------+----Rolf | Smith | 2Rolf | Smith | 3Anne | Watson | 4Michael | Adam | 5Michael | Adam | 6Craig | Scott | 8Craig | Scott | 9Michael | Adam | 10Jose | Salvatierra |(9 rows)
สรุป JOIN
- ใช้กับ relational database คือช่วยให้เราดึงข้อมูลจากหลาย table ได้
- เร็วกว่าการดึงข้อมูลจาก table1 และ table2 แล้วค่อยมา match กันในโปรแกรม
- โดยทั่วไปนิยมใช้ INNER JOIN มากที่สุด ตามด้วย LEFT JOIN
______________________________________________________
GROUP BY
ใช้ในการรวมข้อมูลหลาย ๆ row ให้เหลือแค่ 1 row ในแต่ละชนิด หรือพูดง่าย ๆ ว่าเอา duplicated item ออกไปนั่นแหละ
หากเราต้องการนับจำนวน item ที่ลูกค้าแต่ละคนซื้อไป
เราสามารถใช้ GROUP BY, INNER JOIN และ COUNT มาช่วยได้ดังนี้
SELECT customers.first_name, customers.last_name, COUNT(purchases.id) FROM customers
LEFT JOIN purchases
ON customers.id = purchases.customer_id
GROUP BY customers.id;อธิบายก็คือ GROUP BY จะมาช่วยรวมกลุ่มให้กับ customers.id และในส่วนของ purchases ก็ใช้ COUNT มานับรวมจำนวน item ในแต่ละ row
จะได้
first_name | last_name | count------------+-------------+-------Rolf | Smith | 2Anne | Watson | 1Michael | Adam | 3Jose | Salvatierra | 0Craig | Scott | 2(5 rows)
หากเราต้องการรวมจำนวนเงินที่ลูกค้าแต่ละคนจ่ายให้กับร้านของเรา
เราจะใช้ GROUP BY + SUM
เราสามารถใช้ GROUP BY, INNER JOIN และ SUM เข้ามาช่วย ดังนี้
SELECT customers.first_name, customers.last_name, SUM(items.price) FROM customers
INNER JOIN purchases ON customers.id = purchases.customer_id
INNER JOIN items ON purchases.item_id = items.id
GROUP BY customers.id;จะได้
first_name | last_name | sum------------+-----------+--------Rolf | Smith | 365.49Anne | Watson | 5.00Michael | Adam | 111.29Craig | Scott | 21.30(4 rows)
ทีนี้ลองดูอีกตัวอย่างนึงเพื่อให้เข้าใจ GROUP BY มากขึ้น
SELECT items.name, items.price, customers.first_name FROM items
INNER JOIN purchases ON purchases.item_id = items.id
INNER JOIN customers ON customers.id = purchases.customer_id;จะได้ตามด้างล่างนี้
name | price | first_name--------------+--------+------------Screen | 275.50 | RolfHard Drive | 89.99 | RolfPen | 5.00 | AnneInk | 10.00 | MichaelFountain Pen | 11.30 | MichaelFountain Pen | 11.30 | CraigInk | 10.00 | CraigHard Drive | 89.99 | Michael(8 rows)
แต่เมื่อเราใส่ GROUP BY customers.id เข้าไป
SELECT items.name, items.price, customers.first_name FROM items
INNER JOIN purchases ON purchases.item_id = items.id
INNER JOIN customers ON customers.id = purchases.customer_id
GROUP BY customers.id;จะพบว่ามัน error ตามนี้
ERROR: column "items.name" must appear in the GROUP BY clause or be used in an aggregate functionLINE 1: SELECT items.name, items.price, customers.first_name FROM it...
เพราะว่าพอเรา GROUP BY ด้วย customers.id นั้น ให้มองง่าย ๆ ว่ายุบ row ของ customers แต่ละ row ไม่ให้ซ้ำกัน พอเราจะแสดงผล items.name มันก็จะแสดงไม่ได้เพราะมี row ที่หายไป
name | price | first_name--------------+--------+------------Screen | 275.50 | RolfHard Drive | 89.99 | Rolf...
จากตัวอย่างจะเห็นว่า Rolf มีข้อมูล 2 rows คือ Screen และ Hard Drive แต่พอ GROUP BY มันจะเหลือ Rolf แค่ row เดียว ถ้าจะแสดงผล items.name ออกมามันก็ดูจะไม่ make sense ถูกมั้ย? เพราะพอเหลืออันเดียวแล้วจะแสดง Screen หรือ Hard Drive ดีหละ?
ดังนั้นต้องเป็นแบบนี้
SELECT customers.first_name, SUM(items.price) FROM items
INNER JOIN purchases ON purchases.item_id = items.id
INNER JOIN customers ON customers.id = purchases.customer_id
GROUP BY customers.id;จะได้
first_name | sum------------+--------Rolf | 365.49Anne | 5.00Michael | 111.29Craig | 21.30(4 rows)
______________________________________________________
ORDER BY
ใช้เรียงลำดับผลลัพธ์ที่แสดงออกมา
SELECT * FROM items
ORDER BY items.price DESC;จะได้
name | id | price--------------+----+--------Screen | 5 | 275.50Hard Drive | 6 | 89.99Fountain Pen | 2 | 11.30Ink | 3 | 10.00Pen | 1 | 5.00(5 rows)
Option
- DESC = จากมากไปน้อย
- ASC = จากน้อยไปมาก
______________________________________________________
CREATE
ใช้สำหรับสร้าง table ขึ้นมา
CREATE TABLE public.users (
id integer PRIMARY KEY,
name character varying(100) NOT NULL
)จากตัวอย่างนี้ table ชื่อ users โดยมี id เป็น primary key (unique ในแต่ละ row) และ name เป็น NOT NULL หมายความว่าห้ามเป็น empty
แต่บางครั้งถ้าเราต้องการมี primary key มากกว่า 1 column ก็ทำแบบนี้
CREATE TABLE public.users (
id integer,
name character varying(100) NOT NULL,
CONSTRAINT users_id_pkey PRIMARY KEY (id, name)
);ทีนี้ลองดูอีก table นึง คราวนี้จะมี foreign key
CREATE TABLE public.videos (
id integer PRIMARY KEY,
user_id integer REFERENCES public.users,
name character varying(255) NOT NULL
)ตรง column ชื่อ user_id เอาไว้บอกว่า user ไหนเป็นคนอัพโหลดวิดีโอขึ้นมา และเราก็ต้องการให้ column นี้เป็น valid ที่มีอยู่จริงใน table ชื่อ users ด้วยก็ให้ใส่ตามด้านบน
______________________________________________________
INSERT INTO
การเพิ่มข้อมูลลงใน table
INSERT INTO public.videos (id, user_id, name)
VALUES (1, 10, 'Test Video');ถ้าใส่แบบนี้จะ error เพราะว่า foreign key คือเราไม่มี user id = 10 ใน users จริง ๆ
INSERT INTO public.videos (id, user_id, name)
VALUES (1, 1, 'Test Video');ตรงตัวหนานั้นสามารถเอาออกได้ ถ้าหากข้อมูลที่ใส่ไปมีเท่ากับจำนวน column ใน table จริง ๆ ที่ต้องใส่ก็เช่น อาจจะมี column เป็น phone_number อีกตัวแต่เราไม่ต้องการใส่เบอร์โทรศัพท์เข้าไปด้วยอะไรแบบนี้
______________________________________________________
SEQUENCE (SQL ตัวอื่นใช้ AUTO INCREMENTING)
เริ่มจากสร้าง Sequence เริ่มด้วย 3 (เพราะเรามี user อยู่ 2 คนแล้ว)
CREATE SEQUENCE users_id_seq START 3;ต่อมาก็เปลี่ยน default value ใน column
ALTER TABLE public.users
ALTER COLUMN id
SET DEFAULT nextval('users_id_seq');สุดท้ายก็บอกกับ PostgreSQL ว่าถ้าเราลบ table ก็ให้ลบ sequence นี้ด้วย
ALTER SEQUENCE users_id_seq OWNED BY public.users.id;______________________________________________________
CREATE INDEX
เป็นตัวที่ทำให้การ search ข้อมูลใน table เร็วขึ้นมาก สมมุติเราค้น “nopnithi”
SELECT * FROM users WHERE name='nopnithi';มันจะวิ่งจนครบทุก row ทั้ง table แม้มันจะเจอสิ่งที่ค้นหาแล้วก็ตาม
การใส่ LIMIT 1 ก็ทำให้เร็วขึ้นนิดหน่อยเพราะเมื่อเจอแล้วมันจะหยุดการ loop
SELECT * FROM users WHERE name='nopnithi' LIMIT 1;การทำ index จะทำให้ค้นหาเร็วขึ้นเพราะใช้การค้นหาแบบ binary search trees แต่มันจะต้องกินพื้นที่ในการทำ index และทำให้การ INSERT ข้อมูลช้าลงนิดหน่อยเพราะมันต้องจัดเรียง index
สำหรับการทำ index ให้ทำเฉพาะข้อมูลที่เราจะค้นหา ส่วนมากก็คือ ID แต่บางทีเป็น table เกี่ยวกับอากาศ มี ID ก็จริงแต่เวลาค้นเราค้นจาก location ก็ให้ทำที่ location
ปกติแล้ว PRIMARY KEY จะเป็น index โดย by default
วิธีสร้าง INDEX
ผมจะทำ index ที่ column ชื่อ name เพราะจะได้ค้นหาจาก name ได้เร็วขึ้น
CREATE INDEX users_name_index ON public.users(name);หรือบางครั้งที่เรามีการ filter ข้อมูลแบบนี้
SELECT * FROM movies WHERE id=1 AND user_id=5;ก็สามารถทำ Multi-index ได้แบบนี้
CREATE INDEX movies_by_index ON public.movies(id, user_id);วิธีลบ INDEX (REINDEX)
ใช้ในกรณีที่มีปัญหาบางอย่างจาก index ที่ทำให้การค้นหาช้า
REINDEX INDEX users_name_index;หรือจะทำทั้ง table
REINDEX TABLE users;หรือทั้ง database เลยยังได้
REINDEX DATABASE learning;______________________________________________________
DROP
ใช้ลบ table จาก database
DROP TABLE purchases;แต่จะเจอปัญหาเมื่อมี foreign key ที่มีคนมาผูกกับเราไว้ เช่น
DROP TABLE items;จะ error เนื่องจาก foreign key ของ purchases ทำการ reference มายัง items (แต่ถ้าเรา drop purchases ก่อนจะไม่มีปัญหาอะไรนะ)
ERROR: cannot drop table items because other objects depend on it DETAIL: constraint fk_purchase_item on table purchases depends on table items HINT: Use DROP ... CASCADE to drop the dependent objects too. SQL state: 2BP01หรือถ้าอยาก drop จริง ๆ ทำได้โดย
DROP TABLE items CASCADE;มันจะไปจัดการ foreign key ที่ purchases อ้างอิงมายัง items ให้โดยอัตโนมัติ แต่ไม่ได้ลบ column ใด ๆ ใน purchases
ป้องกันปัญหาเมื่อ table ไม่ได้มีอยู่จริง
DROP TABLE IF EXISTS items;ให้เติม IF EXISTS เข้ามา เพราะเมื่อ table ที่เราต้องการ drop ไม่มีอยู่แล้วมันจะได้ไม่ error นั่นเอง
______________________________________________________
VIEWS
เป็นตัวช่วยในการ query ข้อมูล พูดง่าย ๆ ว่าเป็นการ save วิธีในการ query ข้อมูลเอาไว้ คล้ายกับการสร้าง function ใน programming ทั่วไป
สร้าง VIEW
CREATE VIEW total_spent_per_customer AS
SELECT customers.first_name, customers.last_name, SUM(items.price) FROM customers
INNER JOIN purchases ON customers.id = purchases.customer_id
INNER JOIN items ON purchases.item_id = items.id
GROUP BY customers.id;ทีนี้พอเราสร้าง VIEW ชื่อ total_spent_per_customer ขึ้นมาแล้ว คราวต่อไปเวลาเราจะ query ข้อมูลชุดนี้ก็เรียกแค่
SELECT * FROM total_spent_per_customer;จะได้
first_name | last_name | sum------------+-------------+---------Rolf | Smith | 1264.49Anne | Watson | 5.00Michael | Adam | 104.79Craig | Scott | 14.80Jose | Salvatierra | 899.00(5 rows)
ลบ VIEW
DROP VIEW total_spent_per_customer;เราสามารถสร้าง VIEW จาก VIEW เดิมได้
CREATE VIEW vip_customers AS
SELECT * FROM total_spent_per_customer
WHERE sum > 500;อันนี้จะแสดงลูกค้าที่ซื้อของจากเราตั้งแต่ 500$ ขึ้นไป
การ INSERT ข้อมูลลงใน VIEW
สร้าง VIEW ใหม่ชื่อ expensive_items คือเป็นสินค้าที่มีราคาตั้งแต่ 100$ ขึ้นไป
CREATE VIEW expensive_items AS
SELECT * FROM items WHERE price > 100;ลอง query ข้อมูลออกมา
SELECT * FROM expensive_items;จะได้
name | id | price--------+----+--------Laptop | 4 | 899.00Screen | 5 | 275.50(2 rows)
ทำการ INSERT ข้อมูล
INSERT INTO expensive_items(id, name, price)
VALUES(7, 'DSLR', 400.00);จะได้
name | id | price--------------+----+--------Pen | 1 | 5.00Fountain Pen | 2 | 11.30Ink | 3 | 3.50Laptop | 4 | 899.00Screen | 5 | 275.50Hard Drive | 6 | 89.99DSLR | 7 | 400.00(7 rows)
LOCAL CHECK OPTION
แต่สังเกตว่าเงื่อนไขของ VIEW คือราคามากกว่า 100 ถูกต้องมั้ย แต่ปัญหาคือผมยังสามารถ INSERT ของที่มีราคาน้อยกว่านั้นได้อยู่
ซึ่งถ้าอยากให้มันเช็คตามเงื่อนไขด้วยให้ใส่ WITH LOCAL CHECK OPTION ต่อท้ายตอนสร้าง VIEW
CREATE VIEW expensive_items AS
SELECT * FROM items WHERE price > 100
WITH LOCAL CHECK OPTION;ทีนี้เราจะ INSERT สินค้าที่มีราคาน้อยกว่าหรือเท่ากับ 100 ไม่ได้แล้ว
CASCADED CHECK OPTION
ปัญหาของ LOCAL CHECK OPTION ก็คือตามชื่อเลย มันจะเช็คเฉพาะเงื่อนไขของ view ที่เราใช้งานอยู่เท่านั้น เช่นเราสร้าง view1 ขึ้นมาดังนี้
VIEW1: expensive_items
CREATE VIEW expensive_items AS
SELECT * FROM items WHERE price >= 100;จากนั้นผมก็สร้าง VIEW2 โดย based on VIEW1
CREATE VIEW non_luxury_items AS
SELECT * FROM expensive_items WHERE price < 10000
WITH LOCAL CHECK OPTION;ฉะนั้นจะมี 2 เงื่อนไขแล้ว
- expensive_items คือ สินค้าราคา (ราคาตั้งแต่ 100 ขึ้นไป)
- non_luxury_items คือ สินค้าที่ไม่ใช่ของหรูหรา (ราคาน้อยกว่า 10,000)
เอาง่าย ๆ คือ แพง แต่ยังไม่ถึงขั้นโคตรแพงนั่นแหละ
คำถามคือเราสามารถเพิ่ม Pencil ราคา 5 บาทเข้าไปใน non_luxury_items ได้หรือไม่?
INSERT INTO non_luxury_items(id, name, price)
VALUES(8, 'Pencil', 5.00);คำตอบคือได้ครับ เพราะเรา INSERT ข้อมูลลงใน non_luxury_items ใช่มั้ย และมันก็ผ่านเงื่อนไขซะด้วยคือ price < 10000 แต่ปัญหาคือเราไม่ต้องการให้มันผ่านครับ เพราะจริง ๆ non_luxury_items มัน based on expensive_items คือควรจะมีราคา 100 ขึ้นไป
ดังนั้นให้สร้าง non_luxury_items ใหม่ตามนี้
CREATE VIEW non_luxury_items AS
SELECT * FROM expensive_items WHERE price < 10000
WITH CASCADED CHECK OPTION;และเมื่อลอง INSERT ยางลบที่มีราคาแค่ 3 บาทเข้าไป
INSERT INTO non_luxury_items(id, name, price)
VALUES(9, 'Eraser', 3.00);จะ error ตามนี้
ERROR: new row violates check option for view "expensive_items" DETAIL: Failing row contains (Eraser, 9, 3.00). SQL state: 44000มันจะไปติดที่เงื่อนไขของ expensive_items ด้วยนั่นเอง
______________________________________________________
SQL built-in functions และ HAVING construct
- COUNT (นับจำนวน)
SELECT COUNT(items.id) FROM items;- SUM (รวมค่า)
SELECT SUM(items.price) FROM items;- AVG (เฉลี่ย)
SELECT AVG(items.price) FROM items;- MAX และ MIN
SELECT MAX(items.price) FROM items;- HAVING
ใช้สำหรับ filter หลังจากที่ทำ aggregation
SELECT customers.first_name, customers.last_name, COUNT(purchases.id) AS purchase_count FROM customers
INNER JOIN purchases ON customers.id = purchases.customer_id
GROUP BY customers.id;ผลที่ได้คือลูกค้าแต่ละคนซื้อสินค้าไปจำนวนกี่ชิ้น
first_name | last_name | purchase_count------------+-------------+----------------Rolf | Smith | 3Anne | Watson | 1Michael | Adam | 3Craig | Scott | 2Jose | Salvatierra | 1(5 rows)
คำถามคือถ้าเราต้องการลูกค้าที่ซื้อของมากกว่า 2 ครั้งหละ?
ผมจะไปใส่ WHERE ที่บรรทัดสุดท้ายด้านล่างของ GROUP BY ก็ไม่ได้ เพราะ WHERE จะใช้ไม่ได้หลังจากการ aggregation ข้อมูล
SELECT customers.first_name, customers.last_name, COUNT(purchases.id) AS purchase_count FROM customers
INNER JOIN purchases ON customers.id = purchases.customer_id
GROUP BY customers.id
WHERE purchase_count > 2;ดังนั้นเราจึงแค่เปลี่ยนไปใช้ HAVING แทนก็จบแล้ว
SELECT customers.first_name, customers.last_name, COUNT(purchases.id) AS purchase_count FROM customers
INNER JOIN purchases ON customers.id = purchases.customer_id
GROUP BY customers.id
HAVING COUNT(purchases.id) > 2;แก้นิดเดียวคือจะใช้ purchase_count ไม่ได้ ก็ให้ทำฟังก์ชั่นใหม่เอาแค่นั้นเอง
______________________________________________________
DATES
Data types เกี่ยวกับเวลาใน PostgreSQL
- timestamp
- date
- time
- interval
Standard ของ time format ที่ใช้ คือ ISO8601
2019–12–31 05:10:40แสดงเวลาปัจจุบันตาม format ที่ต้องการ
SELECT TO_CHAR(NOW(), 'DD-MM-YYYY HH:MI:SS');ถ้าอยากได้วันด้วยก็…
SELECT TO_CHAR(NOW(), 'Day, DD-MM-YYYY HH:MI:SS');จะได้
Monday , 29-07-2019 08:46:58ทำไมหลัง Monday ถึงมีเว้นวรรค? เพราะว่ามันจะยึดจากวันที่มีความยาวที่สุด ทีนี้ Monday มันสั้นกว่าวันดังกล่าวก็เลยเติม space ให้เท่ากัน
แก้ไขได้โดยปิด Fill Mode ซะ
SELECT TO_CHAR(NOW(), 'FMDay, DD-MM-YYYY HH:MI:SS');ก็จะได้
Monday, 29-07-2019 08:48:03และก็สามารถทำแบบนี้ได้
SELECT TO_CHAR(NOW(), 'FMDay, DDth FMMonth YYYY HH:MI:SS');จะได้
Monday, 29th July 2019 08:51:36การ convert จาก string เป็น timestamp
SELECT TO_TIMESTAMP('Monday, 29th July 2019 08:51:36', 'FMDay, DDth FMMonth YYYY HH:MI:SS');ข้อแม้คือ format ต้องถูกต้องด้วยนะ จะได้
2019-07-29 08:51:36+07อ้อ +07 คือเวลาประเทศไทยที่ +7 ชั่วโมงครับ
______________________________________________________
Special Data Types
BYTEA
ใช้เก็บข้อมูลเป็น binary แน่นอนว่าสามารถเก็บรูปได้ขนาดสูงสุด 1GB แต่แนะนำให้ใช้วิธีเก็บ path ของรูปใน database จะดีกว่า
ENUM
ใช้สร้าง data type ใหม่ของเราได้ เช่น
- very unhappy
- unhappy
- ok
- happy
- very happy
CREATE TYPE mood AS ENUM('very unhappy', 'unhappy', 'ok', 'happy', 'very happy');แล้วก็เอา data type อันนี้ไปใช้งาน
CREATE TABLE employee (
name character varying(255),
emotion mood
);และเมื่อ INSERT ข้อมูลผิดจาก ENUM ก็จะ error เพราะเราไม่ได้กำหนด “Fine” ไว้
INSERT INTO students(name, emotion)
VALUES('Yada', 'fine');แบบนี้
ERROR: invalid input value for enum mood: "fine"LINE 2: VALUES('Yada', 'fine');^
และยังสามารถใส่เงื่อนไขในการ query ได้ด้วย เช่น
SELECT * FROM employee WHERE emotion >= 'ok';ก็คือแสดงเฉพาะคนที่มีอารมณ์ตั้งแต่ “ok” เป็นต้นไป โดยมันเรียงจากลำดับที่เรา add เข้าไปใน enumeration
______________________________________________________
NESTED SELECT
ถ้าเราต้องการแสดงสินค้าที่มีราคาสูงกว่าค่าเฉลี่ยของทั้งหมด
SELECT * FROM items WHERE items.price > AVG(items.price);จะได้
ERROR: aggregate functions are not allowed in WHERELINE 1: SELECT * FROM items WHERE items.price > AVG(items.price);^
มันบอก aggregate functions ใช้ใน WHERE ไม่ได้ ถ้าลองคิดดูว่าทำไมนึกภาพว่า AVG() เนี่ย มันเหมือนเราต้องได้ข้อมูลราคาทุกอันออกมาก่อน แต่การทำแบบนี้มันจะยังไม่ทันได้ข้อมูลทุกอันออกมาเลยมันก็คำนวณ average ไม่ได้
เราเลยต้องใช้ nested select แทนดังนี้
SELECT * FROM items WHERE price > (SELECT AVG(items.price) FROM items);ข้างในวงเล็บคือสิ่งที่เกิดขึ้นเป็นอันดับแรกเพื่อหาค่าเฉลี่ยของราคาออกมา ซึ่งมีค่าเท่ากับ 211.161250 แล้วจึงเอาค่าเฉลี่ยไปใช้เป็นเงื่อนไขใน WHERE ก็จะได้
name | id | price--------+----+--------Laptop | 4 | 899.00Screen | 5 | 275.50DSLR | 7 | 400.00(3 rows)
ต้องการหาว่าราคาสินค้าแต่ละชิ้นต่างจากราคาเฉลี่ยเท่าไร
SELECT items.name, items.price, items.price - (SELECT AVG(items.price) FROM items) AS price_diff_from_avg FROM items;จะได้
name | price | price_diff_from_avg--------------+--------+-----------------------Pen | 5.00 | -206.1612500000000000Fountain Pen | 11.30 | -199.8612500000000000Ink | 3.50 | -207.6612500000000000Laptop | 899.00 | 687.8387500000000000Screen | 275.50 | 64.3387500000000000Hard Drive | 89.99 | -121.1712500000000000DSLR | 400.00 | 188.8387500000000000Pencil | 5.00 | -206.1612500000000000(8 rows)
- ค่าบวกคือมากกว่า
- ค่าลบคือน้อยกว่า
ก่อนจะไป…รบกวนกด Like/Follow/Subscribe ให้ผมด้วยนะครับ 🙏
- 💙 Facebook: Nopnithi Tech
- ❤️ YouTube: Nopnithi Tech
- 💙 LinkedIn: Nopnithi (Game) Khaokaew
- 🖤 Medium: Nopnithi Khaokaew (Game)
