SQL Part 1 — DDL & Basic Queries

วิดีโอที่อาจารย์ให้ดูก่อนเรียนบทนี้ (แบ่ง 2 ตอน):

5.1SQL คืออะไร และแบ่งเป็นกลุ่มไหนบ้าง

SQL (Structured Query Language) เกิดจากภาษา Sequel ที่ IBM พัฒนาในโครงการ System R ที่ห้องวิจัย San Jose ต่อมาเปลี่ยนชื่อเป็น SQL · มาตรฐาน ANSI/ISO ไล่มาตั้งแต่ SQL-86, SQL-89, SQL-92, SQL:1999, SQL:2003

ระบบเชิงพาณิชย์ส่วนใหญ่รองรับฟีเจอร์ SQL-92 เกือบทั้งหมด บวกกับฟีเจอร์จากมาตรฐานรุ่นหลังบางส่วน และฟีเจอร์เฉพาะตัวของแต่ละเจ้า ตัวอย่างในสไลด์บางอันจึงอาจไม่ทำงานบนทุกระบบ

DDL ของ SQL ให้เราระบุ: schema ของแต่ละ relation · ชนิดของค่าในแต่ละ attribute · integrity constraints · ชุด index ที่ต้องดูแลของแต่ละ relation · ข้อมูลความปลอดภัยและสิทธิ์ · โครงสร้างการจัดเก็บเชิงกายภาพบนดิสก์

Domain Types ใน SQL

5.2เชื่อมต่อและสร้างฐานข้อมูล

mysql.server start                    -- เริ่มเซิร์ฟเวอร์
mysql -u root -p                      -- เชื่อมต่อ
mysql -u <username> -p <database>     -- เชื่อมต่อพร้อมเลือก db เลย

CREATE DATABASE uni_demo;
USE uni_demo;                         -- เลือก/สลับฐานข้อมูล
SELECT DATABASE();                    -- ตอนนี้ใช้ db ไหนอยู่
SHOW DATABASES;                       -- มี db อะไรบ้าง
SHOW TABLES;                          -- มีตารางอะไรบ้าง
DESCRIBE Student;                     -- โครงสร้างตาราง (= relation schema)

5.3CREATE TABLE และ Integrity Constraints

รูปแบบทั่วไป:

CREATE TABLE table_name (
    A₁ D₁,
    A₂ D₂,
    …,
    Aₙ Dₙ,
    (integrity-constraint₁),
    …,
    (integrity-constraintₖ)
);

โดย Aᵢ คือชื่อ attribute และ Dᵢ คือชนิดข้อมูลของ domain ของ attribute นั้น

ชนิดของ integrity constraint ที่ต้องรู้: primary key (A₁, …, Aₙ) · foreign key (Aₘ, …, Aₙ) references r · not null
SQL จะปฏิเสธการอัปเดตใด ๆ ที่ทำให้ integrity constraint ถูกละเมิด

CREATE TABLE department (
    dept_name  VARCHAR(20) PRIMARY KEY,
    building   VARCHAR(20),
    budget     NUMERIC(10,0)
);

CREATE TABLE instructor (
    ID         CHAR(5),
    name       VARCHAR(20) NOT NULL,
    dept_name  VARCHAR(20),
    salary     NUMERIC(8,2),
    PRIMARY KEY (ID),
    FOREIGN KEY (dept_name) REFERENCES department
);

CREATE TABLE student (
    ID         VARCHAR(5),
    name       VARCHAR(20) NOT NULL,
    dept_name  VARCHAR(20),
    tot_cred   NUMERIC(3,0),
    PRIMARY KEY (ID),
    FOREIGN KEY (dept_name) REFERENCES department(dept_name)
);

CREATE TABLE course (
    course_id  VARCHAR(8),
    title      VARCHAR(50),
    dept_name  VARCHAR(20),
    credits    NUMERIC(2,0),
    PRIMARY KEY (course_id),
    FOREIGN KEY (dept_name) REFERENCES department(dept_name)
);

-- composite primary key: ต้องใช้ 4 คอลัมน์รวมกันจึงระบุ section ได้
CREATE TABLE section (
    course_id   VARCHAR(8),
    sec_id      VARCHAR(8),
    semester    VARCHAR(6),
    year        NUMERIC(4,0),
    building    VARCHAR(15),      -- ตึกที่ใช้เรียน
    room_number VARCHAR(7),
    PRIMARY KEY (course_id, sec_id, semester, year)
);

-- FK แบบหลายคอลัมน์ ชี้ไปยัง composite PK ของ section
CREATE TABLE takes (
    ID         VARCHAR(5),
    course_id  VARCHAR(8),
    sec_id     VARCHAR(8),
    semester   VARCHAR(6),
    year       NUMERIC(4,0),
    grade      VARCHAR(2),
    PRIMARY KEY (ID, course_id, sec_id, semester, year),
    FOREIGN KEY (ID) REFERENCES student,
    FOREIGN KEY (course_id, sec_id, semester, year)
        REFERENCES section (course_id, sec_id, semester, year)
);

เพิ่ม constraint ให้ตารางที่มีอยู่แล้ว

ALTER TABLE table_name ADD PRIMARY KEY (column_name);

ALTER TABLE table_name
  ADD CONSTRAINT fk_name
  FOREIGN KEY (column_name) REFERENCES referenced_table(referenced_column);

INSERT · DELETE · DROP

INSERT INTO instructor VALUES ('10101','Srinivasan','Comp. Sci.', 65000);

DELETE FROM instructor;          -- ลบทุกแถว โครงสร้างยังอยู่
DROP TABLE instructor;           -- ลบทั้งข้อมูลและโครงสร้าง

5.4โครงสร้างพื้นฐานของ Query

SELECT A₁, A₂, …, Aₙ      -- attribute ที่ต้องการ
FROM   r₁, r₂, …, rₘ      -- relation ที่เกี่ยวข้อง
WHERE  P;                 -- predicate

ผลลัพธ์ของ SQL query คือ relation เสมอ

SELECT clause — 5 เรื่องที่ต้องรู้

1. ตรงกับ projection ใน relational algebra · SELECT name FROM instructor
2. SQL ยอมให้มีแถวซ้ำ ทั้งใน relation และในผลลัพธ์ · SELECT DISTINCT dept_name เพื่อกำจัดซ้ำ · SELECT ALL dept_name เพื่อคงไว้ (เป็นค่าเริ่มต้น)

3. SELECT * = ทุก attribute · literal ได้โดยไม่มี FROM เช่น SELECT '437' ได้ตาราง 1 คอลัมน์ 1 แถว · ตั้งชื่อคอลัมน์ด้วย SELECT '437' AS FOO
4. literal พร้อม FROM เช่น SELECT 'A' FROM instructor ได้ตาราง 1 คอลัมน์ N แถว (เท่าจำนวน tuple ใน instructor) ทุกแถวมีค่า 'A'
5. ใส่นิพจน์คณิตศาสตร์ได้ ด้วย + − * / บนค่าคงที่หรือ attribute · SELECT ID, name, salary/12 AS monthly_salary FROM instructor

WHERE clause

ตรงกับ selection predicate ของ relational algebra · รวมผลการเปรียบเทียบด้วย AND, OR, NOT และเปรียบเทียบผลของนิพจน์คณิตศาสตร์ได้

SELECT name FROM instructor
WHERE  dept_name = 'Comp. Sci.' AND salary > 80000;

FROM clause · Cartesian product vs Join

FROM ตรงกับ Cartesian product ของ relational algebra — SELECT * FROM instructor, teaches สร้างทุกคู่ที่เป็นไปได้ พร้อม attribute จากทั้งสอง relation · attribute ที่ชื่อซ้ำกัน (เช่น ID) จะถูกเปลี่ยนชื่อโดยเติมชื่อ relation นำหน้า เช่น instructor.ID

SELECT name, department.dept_name
FROM   instructor, department;

SELECT name, department.dept_name
FROM   instructor, department
WHERE  instructor.dept_name = department.dept_name;

Rename ด้วย AS — และการ join ตารางกับตัวเอง

-- หาชื่ออาจารย์ที่เงินเดือนสูงกว่าอาจารย์บางคนในภาค COE
SELECT DISTINCT T.name
FROM   instructor AS T, instructor AS S
WHERE  T.salary > S.salary AND S.dept_name = 'COE';

คำว่า AS ละได้ — instructor AS T เท่ากับ instructor T
การให้ชื่อแทน (alias) สองชื่อกับตารางเดียวกัน ทำให้เปรียบเทียบแถวในตารางเดียวกันได้ — เรียกว่า self join

5.5String Operations และ LIKE

SQL มีตัวดำเนินการจับคู่สตริงชื่อ like ซึ่งใช้อักขระพิเศษ 2 ตัว

• เปอร์เซ็นต์ % — จับคู่กับสตริงย่อยใด ๆ (ยาว 0 ตัวขึ้นไป)
• ขีดล่าง _ — จับคู่กับอักขระเดียว

Pattern เป็น case-sensitive · SQL ยังรองรับ operation อื่นกับสตริง เช่น การต่อสตริงด้วย || · แปลงตัวพิมพ์ใหญ่เป็นเล็กและกลับกัน · หาความยาว · ตัดสตริงย่อย

SELECT title FROM books WHERE title LIKE 'The%Adventure';

SELECT title FROM books WHERE title LIKE '___ of%';

5.6ORDER BY และ Predicate เพิ่มเติม

SELECT DISTINCT name FROM instructor ORDER BY name;
SELECT * FROM instructor ORDER BY name DESC;
SELECT * FROM instructor ORDER BY dept_name, name;   -- เรียงหลายคอลัมน์

ASC เป็นค่าเริ่มต้น · ระบุ DESC เพื่อเรียงจากมากไปน้อย · กำหนดทิศทางแยกกันแต่ละคอลัมน์ได้

-- BETWEEN: รวมปลายทั้งสองข้าง (>= 60000 AND <= 80000)
SELECT name FROM instructor WHERE salary BETWEEN 60000 AND 80000;

Tuple comparison — เปรียบเทียบหลายคอลัมน์พร้อมกัน

SQL ให้เราเอาคอลัมน์หลายตัวมาใส่วงเล็บแล้วเทียบเป็นชุดเดียว ผลลัพธ์เป็นจริงก็ต่อเมื่อตรงกันครบทุกตำแหน่ง — ตำแหน่งที่ 1 เทียบกับตำแหน่งที่ 1, ตำแหน่งที่ 2 เทียบกับตำแหน่งที่ 2

-- สองบรรทัดนี้ให้ผลเหมือนกันทุกประการ
WHERE (course_id, sec_id) = ('CS-101', '1')
WHERE course_id = 'CS-101' AND sec_id = '1'

ใช้จริงตอนไหน — เมื่อคีย์มีหลายคอลัมน์ (composite key) การเขียนเป็นชุดจะสั้นและอ่านง่ายกว่า:

-- นักศึกษาที่ลงเรียนกลุ่ม CS-101 sec 1 ของ Fall 2017
SELECT ID, grade
FROM   takes
WHERE  (course_id, sec_id, semester, year) = ('CS-101', '1', 'Fall', 2017);

WHERE (course_id, sec_id, semester, year) IN (
        SELECT course_id, sec_id, semester, year
        FROM   section WHERE building = 'Watson' )

5.7โจทย์แบบฝึกหัดในคาบ พร้อมเฉลย

ทุกข้อข้างล่างใช้ชุดข้อมูลนี้ (ตั้งชื่อตารางแบบพหูพจน์ตามใบงาน) — เปิดดูควบคู่กับเฉลยจะเห็นภาพ

CREATE TABLE IF NOT EXISTS departments (
    dept_name     VARCHAR(50) PRIMARY KEY,
    building_name VARCHAR(50)
);

CREATE TABLE IF NOT EXISTS courses (
    course_id  INT PRIMARY KEY,
    title      VARCHAR(60) NOT NULL,
    department VARCHAR(50),
    credit     INT CHECK (credit > 0),
    FOREIGN KEY (department) REFERENCES departments(dept_name)
);

SELECT * FROM instructors
WHERE  dept_name = 'COE' AND salary > 90000;

SELECT DISTINCT s.ID, s.name
FROM   students s, takes t, courses c
WHERE  s.ID = t.ID
  AND  t.course_id = c.course_id
  AND  c.department = 'COE';

SELECT DISTINCT s.ID, s.name
FROM   students s
JOIN   takes   t ON s.ID = t.ID
JOIN   courses c ON t.course_id = c.course_id
WHERE  c.department = 'COE';

ข้อ 4 เป็นโจทย์ LIKE — เฉลยอยู่ใน §5.5 เพราะต้องใช้เรื่อง pattern matching

CREATE TABLE Departments (
    dept_id   INT PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL
);
CREATE TABLE Employees (
    emp_id   INT PRIMARY KEY,
    emp_name VARCHAR(50),
    dept_id  INT,
    FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)
);

INSERT INTO Departments VALUES (1,'HR'), (2,'IT');
-- ตอนนี้ Departments มีอยู่แค่ 2 แถว: dept_id = 1 และ 2 เท่านั้น

a) INSERT INTO Employees VALUES (101, 'John Doe', 2);   -- สำเร็จ
b) INSERT INTO Employees VALUES (102, 'Jane Smith', 4); -- ล้มเหลว

5.8สรุปบทนี้

• DDL สำหรับสร้างและแก้ไขโครงสร้างฐานข้อมูล
• DML สำหรับ query และแก้ไขข้อมูล
• โครงสร้างพื้นฐาน SELECT · FROM · WHERE ตรงกับ Π · × · σ ตามลำดับ
• Integrity constraints รักษาความสอดคล้องของข้อมูล
• String operations และการจับคู่ pattern ด้วย LIKE
• การเรียงลำดับและกรองผลลัพธ์