CRUD & SQL

ฐานข้อมูลจะมีประโยชน์ก็ต่อเมื่อคุณใส่ข้อมูลเข้าไป ดึงกลับออกมา แก้ไข และลบได้ ใน PostgreSQL คุณทำทั้งหมดนั้นได้ด้วยการส่ง SQL — ภาษาแบบข้อความที่ server เข้าใจ คุณไม่ต้องแก้ไฟล์ table ด้วยมือ แค่เขียนคำสั่ง (statement) แล้ว server จะ parse และรันให้ ก่อนส่งกลับมาเป็นจำนวน row ที่ได้รับผลกระทบหรือชุดผลลัพธ์ (result set)

แทบทุกคำสั่ง SQL ที่คุณเขียนจะตรงกับหนึ่งในสี่แนวคิด ซึ่งรวมเรียกว่า CRUD: create, read, update และ delete โมดูลนี้จะพาเดินผ่านแต่ละอันด้วยตัวอย่างที่คุณวางลงใน psql ได้ตรง ๆ พร้อมกับ operation เดียวกันเป๊ะ ๆ ที่เขียนในรูปแบบ driver ยอดนิยมสี่ตัว เพื่อให้คุณนำความรู้นี้ไปใช้ในโค้ดแอปพลิเคชันจริงได้

สี่คำกริยา

ตัวย่อ CRUD เรียงตรงกับ SQL keyword สี่คำ เรียนรู้สิ่งเหล่านี้แล้วคุณก็ทำงานฐานข้อมูลในแต่ละวันได้แทบทั้งหมดแล้ว

CRUD action	SQL keyword	What it does
Create	`INSERT`	Adds new rows to a table
Read	`SELECT`	Returns rows matching your criteria
Update	`UPDATE`	Changes columns in existing rows
Delete	`DELETE`	Removes rows from a table

ทุกอย่างถูกสร้างขึ้นรอบ ๆ row ใน table table คือ table ที่มีชื่อ ประกอบด้วย column ที่มี type ตายตัว ส่วน row คือหนึ่งระเบียน (record) คำสั่ง SQL อธิบายว่า อะไร คือสิ่งที่คุณต้องการ — “ขอหนังสือที่ตีพิมพ์หลังปี 2000 ให้หน่อย” — แล้ว server จะคิดเองว่า จะทำอย่างไร

คำสั่งหนึ่งเดินทางอย่างไร

ไม่ว่าคุณจะพิมพ์ใน psql หรือเรียก driver จากแอปของคุณ เส้นทางเหมือนกัน: โค้ดของคุณส่ง SQL string ไปให้ server แล้ว server ทำงานกับ row ที่จัดเก็บอยู่ ก่อนส่งคำตอบกลับมา

flowchart LR
  A[Your app or psql] -->|SQL text| B[PostgreSQL server]
  B -->|reads and writes| C[(Table rows on disk)]
  C -->|result set or row count| B
  B -->|reply| A

round trip ของคำสั่ง SQL

schema ตัวอย่าง

เพื่อให้ทุกอย่างเป็นรูปธรรม ทุกบทเรียนในโมดูลนี้ใช้ schema เล็ก ๆ อันเดียวซ้ำกัน: table ของ authors และ table ของ books นี่คือการตั้งค่า ซึ่งคุณรันครั้งเดียวเพื่อทำตามไปด้วยกันได้

CREATE TABLE authors (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name  text NOT NULL,
  born  int
);

CREATE TABLE books (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title       text NOT NULL,
  author_id   bigint REFERENCES authors (id),
  published   int,
  copies_sold int NOT NULL DEFAULT 0
);

await pool.query(`
  CREATE TABLE authors (
    id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    born int
  )
`);

cur.execute("""
    CREATE TABLE authors (
      id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
      name text NOT NULL,
      born int
    )
""")

_, err := conn.Exec(ctx, `
  CREATE TABLE authors (
    id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    born int
  )`)

sqlx::query(
    "CREATE TABLE authors (
       id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
       name text NOT NULL,
       born int
     )",
)
.execute(&pool)
.await?;

ใน pgAdmin: เปิด Query Tool บนฐานข้อมูลของคุณ วางคำสั่ง CREATE TABLE แล้วกดปุ่มรัน คุณจะเห็น table ใหม่ปรากฏขึ้นใต้ Schemas แล้ว Tables ในแผนผัง browser ฝั่งซ้าย

โมดูลนี้ครอบคลุมอะไรบ้าง

Insert — เพิ่ม row ทีละ row หรือเป็นชุด ปล่อยให้ column รับค่า default และดึง id ที่ถูกสร้างขึ้นกลับมา
Select and filtering — เลือก column จำกัดด้วย WHERE เรียงลำดับ และแบ่งหน้า
Update and delete — แก้ไขและลบ row อย่างปลอดภัย และทำไม WHERE ที่ลืมใส่จึงอันตราย
Upsert — เพิ่ม row ใหม่ หรือ update row เดิมถ้ามีอยู่แล้ว ในคำสั่งเดียว

เคล็ดลับและข้อควรระวัง

SQL keyword ไม่สนใจตัวพิมพ์เล็กพิมพ์ใหญ่ (select เท่ากับ SELECT) แต่การเขียน keyword เป็นตัวพิมพ์ใหญ่เป็นธรรมเนียมเพื่อความอ่านง่ายที่ใช้ตลอดทั้งคอร์สนี้
identifier อย่างชื่อ table และ column จะถูกแปลงเป็นตัวพิมพ์เล็กเว้นแต่คุณจะใส่เครื่องหมายอัญประกาศคู่ครอบ ดังนั้น Authors และ authors จึงหมายถึง table เดียวกัน
คำสั่งหนึ่งจบด้วยเซมิโคลอนใน psql driver มักส่งหนึ่งคำสั่งต่อการเรียกหนึ่งครั้ง ดังนั้นเซมิโคลอนจึงไม่บังคับ
การอ่าน (SELECT) ไม่เคยเปลี่ยนแปลงข้อมูล ส่วนอีกสามคำกริยาที่เหลือทำ ดังนั้นจงใช้ด้วยความระมัดระวังมากกว่า