SQLite Part One

Download

• ดาวน์โหลดโปรแกรม SQLite สำหรับนักเรียนที่ใช้ Windows
• วิธีการติดตั้งอยู่ในวีดีโอ ITF11 ให้นักเรียนดาวน์โหลดไฟล์ precompiled binaries for Windows แตกซิป และวางไฟล์ C:\sqlite
• เสร็จแล้วเพิ่ม sqlite ที่ PATH variable ของ Windows
• นักเรียนที่ใช้ Linux/ Mac OS สามารถพิมพ์ sqlite3 ใน terminal เพื่อเริ่มใช้งานได้เลย (ติดตั้งมาเรียบร้อยแล้ว)

[su_spoiler title=”วิธีแก้ปัญหาสำหรับนักเรียนที่เรียก sqlite3 ไม่ได้จาก CMD”]

วิธีแก้ ให้แอด sqlite3 ไปที่ PATH ของ System Variable ตามรูปด้านล่าง ปิดและเปิด command prompt ใหม่ด้วยสิทธิ์แอดมิน (คลิกขวา เลือก Run as Administrator)

[/su_spoiler]

Key Concept

• SQL ย่อมาจาก Structured Query Language พัฒนาโดย IBM ในปี 1970s เป็นภาษาทางการที่เราใช้ทำงานกับ (relational) databases
• SQL มีให้เราเลือกใช้หลายเวอร์ชัน สำหรับคอร์สนี้เราเลือกสอน SQLite command line เพราะว่าติดตั้งง่าย และมีฟีเจอร์พื้นฐานครบ
• sqlite commands จะขึ้นต้นด้วยตัว . เช่น .help .tables .mode
• เราสร้างไฟล์ฐานข้อมูลใหม่ได้ง่ายๆแค่พิมพ์ sqlite3 new_database.db ใน command prompt
• เราใช้ create table และ insert into เพื่อสร้างตารางในฐานข้อมูล
• SQL statements ต้องปิดท้ายด้วย semilocon ; เสมอ

Review

รีวิวบทเรียน และ commands ที่เราใช้ใน module นี้ด้านล่าง

Enter SQLite in Command Prompt

หลังจากติดตั้งโปรแกรม SQLite เสร็จแล้ว (add sqlite3 ไปที่ PATH variable ของ Windows) เราจะสามารถเรียกใช้งาน sqlite3 ได้ใน command prompt แค่พิมพ์ sqlite3

sqlite3

วิธีการสร้าง database ใหม่ที่ง่ายที่สุดคือพิมพ์ sqlite3 ตามด้วยชื่อ database (ไฟล์นามสกุล .db) ตัวอย่างด้านล่างเราสร้าง school.db ขึ้นมา ไฟล์นี้จะถูกเซฟอยู่ที่ working directory

sqlite3 school.db

Note – database หรือฐานข้อมูลประกอบด้วยหลายๆตาราง (tables) หลังจากเรามีไฟล์ .db แล้ว ขั้นตอนต่อไปคือสร้าง table ไว้เก็บข้อมูลที่เราต้องการ เช่น student, address, course เป็นต้น

Basic SQLite Commands

เมื่อเข้าสู่โหมด sqlite3 แล้ว ลองพิมพ์ .help เพื่อเรียกดูรายชื่อ commands ทั้งหมดของ sqlite3 สังเกตว่าชื่อคอมมานด์จะขึ้นต้นด้วยตัว .

พิมพ์ .help ตามด้วยชื่อ command ที่เราต้องการ

.help
.help [command name]
.help shell

ถ้าต้องการเรียกใช้งาน command line ที่เราเรียนมาใน module 1-2 ให้พิมพ์ .shell ตามด้วยชื่อ command เช่น cls เพื่อเคลียร์หน้าจอ หรือ dir /B เพื่อดูรายชื่อไฟล์และโฟลเดอร์ bare format ใน working directory

.shell cls
.shell dir /B
.shell cd

โดย default ตัว sqlite3 จะทำงานได้ทีละหนึ่ง database เท่านั้น พิมพ์ .databases เพื่อดูชื่อ .db ที่เรากำลังใช้งานอยู่ และพิมพ์ .tables เพื่อดูชื่อ tables ทั้งหมดในฐานข้อมูล

.databases
.tables

ถ้าต้องการออกจากโหมด sqlite3 ให้พิมพ์ .quit

.quit

[su_spoiler title=”วิธีแก้ไขเวลาพิมพ์ชื่อคอมมานด์ผิด”]

ถ้าเราเขียนชื่อคอมมานด์ผิด (เช่น hello ในตัวอย่างด้านล่าง) เวลาเรากด enter ใน command prompt จะขึ้นเครื่องหมาย ...> ไปเรื่อยๆจนกว่าเราจะพิมพ์ semicolon ; และกด enter เท่านั้น

[/su_spoiler]

Create Table

วิธีการสร้าง table ใหม่ให้เราพิมพ์ CREATE TABLE ตามด้วยชื่อ table ใหม่ที่เราต้องการ ภายในวงเล็บจะเป็นชื่อคอลัมน์ของตาราง เมื่อเขียน statement เสร็จแล้ว ให้ปิดท้ายด้วยตัว semicolon ; เสมอ

CREATE TABLE table_name (col1, col2, col3, col4);

เราสามารถกำหนดประเภทข้อมูลของแต่ละคอลัมน์ได้ด้วย syntax ด้านล่าง ใน SQLite จะมีประเภทข้อมูลหลักๆอยู่สามแบบคือ integer ตัวเลขจำนวนเต็ม real ตัวเลขที่มีทศนิยม และ text คือ string/ characters

CREATE TABLE student (
  id    INTEGER,
  name  TEXT,
  major TEXT,
  gpa   REAL
);

เสร็จแล้วใช้ INSERT INTO เพื่อใส่ข้อมูลในตาราง ข้อมูลแต่ละแถวจะอยู่ในวงเล็บตามตัวอย่างด้านล่าง เมื่อเขียน statement เสร็จแล้วให้ปิดท้ายด้วยตัว semicolon ;

INSERT INTO table_name VALUES (val1, val2, val3, val4);

ถ้าต้องการใส่ข้อมูลหลายๆแถว (หรือ records) พร้อมกันทีเดียว ให้เราใช้ , เพื่อ split record ถ้าคอลัมน์นั้นเป็น text เราต้องใส่เครื่องหมาย double quote ด้วย

INSERT INTO student VALUES 
  (1, "David" , "Economics"   , 3.52),
  (2, "Annie" , "Engineer"    , 2.87),
  (3, "John"  , "Business"    , 3.86),
  (4, "Joanna", "Data Science", 3.25);

Note – เราไม่สามารถใช้ create table เพื่อสร้างตารางที่ชื่อซ้ำกับ existing tables ได้ สมมติว่าเรามี student table อยู่แล้ว ต้องใช้ drop table เพื่อลบตาราง student เก่าก่อน แล้วค่อยสร้างใหม่

Drop Table

วิธีการลบตารางที่เราไม่ใช้แล้ว พิมพ์ DROP TABLE ตามด้วยชื่อตารางนั้น ปิดท้ายด้วย ; ก่อนจะลบตารางต้องดูชื่อให้ดีก่อน เพราะว่า sqlite ไม่มีปุ่ม undo

DROP TABLE table_name;

Preview Data

หลังจากที่เราใส่ข้อมูลในตารางแล้ว มาลองพรีวิวข้อมูลด้วย SELECT statement เราตั้งค่า .mode column และ .headers on สำหรับการแสดงผลข้อมูลและมีชื่อคอลัมน์ใน output

เครื่องหมาย * ใน select แปลว่า จงดึงทุกคอลัมน์ของตาราง student มาแสดงผลใน console

.mode column
.headers on
SELECT * FROM student;

Read SQL Script

เราสามารถใช้ .read เพื่ออ่านไฟล์ .sql ของเรา สำหรับสร้างตารางข้อมูลได้เลย

• เขียน CREATE TABLE และ INSERT INTO เซฟไฟล์ด้วยนามสกุล .sql ใน working directory ของเรา
• เข้าสู่โหมด sqlite และใช้ .read เพื่ออ่านไฟล์ script ที่เราเตรียมไว้
• ใช้ .tables เพื่อเรียกดูชื่อตารางทั้งหมดที่เราสร้างด้วย script
• ลองเขียน select เพื่อพรีวิวข้อมูลในตาราง

.read file.sql
.tables
SELECT * FROM table;

ตัวอย่างไฟล์ .sql สำหรับสร้างตาราง ใส่ข้อมูล และดึงข้อมูล เช่น

-- create table professor
CREATE TABLE professor (
  id        INTEGER,
  firstname TEXT,
  lastname  TEXT,
  age       INTEGER
);

-- insert data into professor
INSERT INTO professor VALUES
  (1, "John", "Mayer", 34),
  (2, "Dave", "Omelo", 42),
  (3, "Annie", "Smile", 29);

-- select all columns from professor
SELECT * FROM professor;

Comment

เราใช้เครื่องหมาย -- เพื่อใส่ comment ใน SQL script ของเรา

-- this is a comment
-- select all columns from student table
SELECT * FROM student;

Case Not Sensitive

เราสามารถเขียน SQL statement เป็นตัวพิมพ์เล็กหรือตัวพิมพ์ใหญ่ก็ได้ โค้ดสองบรรทัดด้านล่างได้ผลลัพธ์เหมือนกัน

.mode column
.headers on

-- select all columns from student
SELECT * FROM student;
select * from student;

ข้อยกเว้นอย่างเดียวคือเวลาเราฟิลเตอร์ชื่อนักเรียน (คอลัมน์ที่เป็น text) ต้องเขียนให้ถูกต้องตาม values ที่อยู่ในคอลัมน์นั้นๆ เช่น ถ้าชื่อ David อยู่ในคอลัมน์ name เวลาดึงข้อมูลเราต้องเขียน where name = “David” เท่านั้น

-- this will return David record
SELECT * FROM student WHERE name = "David";

-- return empty result
SELECT * FROM student WHERE name = "david";

เราใช้ where ในการฟิลเตอร์ rows/ records ที่ตรงกับเงื่อนไขที่เราต้องการ ในตัวอย่างด้านบน statement บรรทัดสองที่เขียน “david” จะไม่มี output อะไร return ออกมาเลย

Import CSV File

CSV ย่อมาจาก comma-separated values เป็น text file ที่เรานิยมใช้ในการรับส่งข้อมูลสำหรับทำ data analysis ข้อมูลในแต่ละคอลัมน์โดย default จะถูกคั่นด้วยเครื่องหมาย comma

วิธีการ import file csv เข้าสู่ SQLite database ให้เราเซฟไฟล์ .csv ไว้ที่ working directory แล้วเข้าสู่โหมด sqlite3 ใน command prompt และใช้ .import command เพื่อ import ไฟล์ csv สร้างเป็น table

• พิมพ์ .mode csv
• พิมพ์ .import [file.csv] [table name]

sqlite3 school.db
.mode csv
.import course.csv course

หลังจาก import file เสร็จแล้ว ลองเขียน select * from course limit 5; เพื่อพรีวิวข้อมูลใน table course บทต่อไปเราจะอธิบายวิธีการเขียน basic queries เช่น การใช้ SELECT WHERE และ JOIN

Code Used in Lesson ITF15

ด้านล่างคือ SQL script ที่เราเขียนในวีดีโอ ITF15

• drop table address ก่อน
• เสร็จแล้วสร้าง table address อีกครั้งหนึ่ง
• ใส่ข้อมูล 5 records เข้าไปที่ table address

-- drop table
DROP TABLE address;

-- create table address
CREATE TABLE address (id, city, country);

-- insert data into address table
INSERT INTO address VALUES
  (1, "Bangkok", "Thailand"),
  (2, "Tokyo", "Japan"),
  (3, "Washington", "United States"),
  (4, "London", "United Kingdom"),
  (5, "Paris", "France");

References

• แหล่งอ้างอิง SQL tutorial โดย w3schools
• เราสามารถกำหนด special features ของแต่ละคอลัมน์ใน create table statement ได้ด้วย เช่น primiary key, unique ลองดูตัวอย่างได้ที่นี่ (advanced)
• อ่านเพิ่มเติมเรื่อง where clause ได้ที่นี่

---

Formative Quiz

🏆 รีวิวความรู้ที่ได้เรียนใน module นี้ คลิกเพื่อเริ่มทำแบบทดสอบ 20 ข้อ นักเรียนสามารถทำแบบทดสอบซ้ำกี่รอบก็ได้ จนกว่าจะได้คะแนนเต็ม

เสร็จแล้วแอดรบกวนช่วยตอบแบบสอบถามความพอใจ module นี้ด้วยนะครับ -/\- ขอบคุณทุกคนมากๆครับ แล้วพบกันในบทต่อไปนะ

---

Keep Learning

Module ถัดไป เราจะเรียนการเขียน SQL Queries เพื่อดึงและวิเคราะห์ข้อมูลจาก database เช่น select, where, join, union และ aggregate functions เมื่อพร้อมแล้ว คลิกเมนูเพื่อเริ่มเรียนบทต่อไป