SNOOZE MONDAY

Basic SQL Part I พร้อมตัวอย่าง

—

by

Supanat Kritsana
in
  1. SQL คืออะไร ?
  2. สิ่งที่ต้องรู้ก่อนการเขียน SQL queries
    1. ER Diagram คืออะไร ?
    2. รูปแบบความสัมพันธ์ใน Relational Database
    3. SQL เป็นภาษาแบบ case-insensitive
    4. การใส่ Comment ใน SQL query
  3. SELECT
    1. SELECT All Columns by *
    2. SELECT Columns
    3. SELECT DISTINCT
    4. การเปลี่ยนชื่อคอลัมน์ด้วย AS และ การรวมคอลัมน์แบบ text เข้าด้วยกัน
  4. Functions in SQL
    1. Aggregate Functions
    2. Value Functions
    3. Window Functions ( Introduction )
  5. WHERE
    1. การเขียนเงื่อนไขด้วย logical operators
    2. การเขียนเงื่อนไขมากกว่าหนึ่งเงื่อนไขด้วย AND / OR
    3. การเขียนเงื่อนไขในด้วย IN operator แทนการเขียน OR หลายๆครั้ง
    4. การเขียนเงื่อนไขแบบช่วงด้วย BETWEEN AND
    5. การเขียนเงื่อนไขเพื่อหาค่า NULL (missing value)
    6. การเขียนเงื่อนไขด้วย NOT
    7. การเขียนเงื่อนไขแบบ pattern matching ด้วย LIKE
    8. การเขียนเงื่อนไขด้วย Regular Expressions

SQL คืออะไร ?

SQL ย่อมาจาก Structured Query Language ถูกพัฒนาขึ้นในปี 1970 โดยบริษัท IBM SQL เป็นภาษาที่เราใช้ทำงานกับ Relational Databases

การเขียน Query คือ การนำคำศัพท์ภาษาอังกฤษมาเขียนเรียงต่อกัน เพื่อเขียนคำถามไปถาม Database ถ้า Database เข้าใจคำถามก็จะส่งผลลัพธ์กลับมาให้เรา เราจะเรียกผลลัพธ์นั้นว่า “Result set”

สิ่งที่ต้องรู้ก่อนการเขียน SQL queries

ER Diagram คืออะไร ?

ย่อมาจากคำว่า Entity-Relationship Diagram ใช้บอกเราว่าข้อมูลใน database มีอะไรบ้างและแต่ละตารางมีความสัมพันธ์เป็นอย่างไร

ER Diagram of chinook database

สิ่งที่ต้องรู้เกี่ยวกับ ER Diagram of chinook database 

  • chinook database เป็นฐานข้อมูลธุรกิจเกี่ยวกับเพลง
  • มีทั้งหมด 11 ตาราง ใน Database เราจะเรียก “Entity” ชื่อตารางจะอยู่ในกรอบสีเทาอ่อนด้านบนสุด และ แต่ละตารางก็จะมีหลายคอลัมน์ ใน Database เราจะเรียก“Attribute” เช่น ตาราง tracks มีทั้งหมด 9 คอลัมน์ 
  • ในแต่ละตารางจะมี รูปแม่กุญแจอยู่ด้านหลังคอลัมน์ โดยคอลัมน์นั้นจะถูกเรียกว่า “Primary Key” ค่าในคอลัมน์นี้จะไม่ซ้ำกันเลยเหมือนเลขบัตรประชาชน แต่ใน ตาราง playlist_track ทั้งสองคอลัมน์มีแม่กุญแจทั้งคู่ เราจะเรียกว่า “Composite Key” ถ้าเรารวมสองคอลัมน์นี้เข้าด้วยกัน เราจะได้ primary key คอลัมน์ใหม่ที่มีค่าไม่ซ้ำกันเลย
  • ถ้า Primary Key ไปอยู่ในตารางอื่น เช่น ArtistId ไปอยู่ใน ตาราง albums เราจะเรียกว่า “Foreign Key” ค่าในคอลัมน์นี้สามารถซ้ำกันได้ 
  • ความสัมพันธ์ ( Relationship ) ของแต่ละตารางจะถูกแทนที่ด้วยเส้นที่ส้มที่เชื่อมแต่ละตารางต่างๆเข้าด้วยกัน ทำให้เราต้องเรียกฐานข้อมูลประเภทนี้ว่า Relational Database เพราะ แต่ละตารางมีเส้นความสัมพันธ์เชื่อมกันอยู่ จะเกิดจากการเชื่อมกันของ PK = FK

รูปแบบความสัมพันธ์ใน Relational Database

  1. one to one 
  2. one to many
  3. many to many 

one to many เป็นรูปแบบความสัมพันธ์ที่ใช้เยอะที่สุดในการสร้าง relational database

SQL เป็นภาษาแบบ case-insensitive

  • เวลาเขียน query เราสามารถเขียนได้ทั้งตัวพิมพ์เล็ก ( lower case ) หรือตัวพิมพ์ใหญ่ ( UPPER CASE )ก็ได้
  • แต่ในการทำงานจริง เรานิยมเขียน SQL clause เป็นตัวพิมพ์ใหญ่ ส่วนชื่อคอลัมน์ และตารางเป็นตัวพิมพ์เล็ก เรานิยมเขียนชื่อคอลัมน์แต่ละชื่อบนบรรทัดใหม่และจะถูกแยกด้วย comma 
-- lower case
select firstname, lastname from customers;
-- UPPER CASE
SELECT FIRSTNAME, LASTNAME FROM CUSTOMERS;
-- Good  Practice
SELECT 
        first name, 
        last name 
FROM customers;

การใส่ Comment ใน SQL query

  • Single-line comment เราใช้ syntax — ตามด้วยข้อความ ข้อดีคือ เราสามารถ comment โค้ดบางบรรทัดของเราได้
  • Multiple-line comment เราใช้ syntax /* ข้อความ */
-- select 5 customers
SELECT
	firstname,
	-- lastname,
	country
FROM customers
LIMIT 5;

/*
SELECT
	firstname,
	-- lastname,
	country
FROM customers
LIMIT 5;
*/

SELECT

  • การเขียน SQL query สำหรับดึงและวิเคราะห์ข้อมูลจาก database จะขึ้นต้นด้วย SELECT Clause เสมอ
  • ใช้ SELECT Clause เพื่อเลือกคอลัมน์ที่เราต้องการจากตารางที่เราต้องการ
  • ใช้ บวก ลบ คูณ หาร ง่ายๆ
SELECT  
	10 + 2,
	10 - 2,
	10 * 2,
	10 / 2,
	10 % 2;

SELECT All Columns by *

  • ดึงข้อมูลทุกคอลัมน์ด้วยเครื่องหมาย asterisk (*)
  • query ด้านล่างเราจะดึงทุกคอลัมน์จากตาราง artists
SELECT 
		*
FROM artists;

SELECT Columns

  • สามารถเขียนระบุชื่อคอลัมน์ที่เราต้องการได้ใน SELECT clause
  • ใช้ LIMIT เพื่อกำหนดจำนวนแถวของ result set
  • query ด้านล่างเราจะดึงคอลัมน์ firstname, lastname และ country ในตาราง customers และ ใช้ LIMIT เพื่อกำหนดจำนวนแถว 5 แถว
SELECT 
	firstname,
	lastname,
	country 
FROM customers
LIMIT 5;

SELECT DISTINCT

  • ใช้เพื่อดู unique values ( ค่าที่ไม่ซ้ำกัน )ในคอลัมน์ที่เราต้องการ
  • query ด้านล่างเราจะดูค่าที่ไม่ซ้ำกันเลยในคอลัมน์ country
SELECT 
	DISTINCT(country) 
FROM customers;

การเปลี่ยนชื่อคอลัมน์ด้วย AS และ การรวมคอลัมน์แบบ text เข้าด้วยกัน

  • สามารถเปลี่ยนชื่อคอลัมน์ได้ด้วย AS ย่อมาจาก Aliases 
  • เขียน AS ต่อท้ายคอลัมน์ที่เราต้องการเปลี่ยนชื่อและตามด้วยชื่อใหม่
  • AS เป็น Optional สามารถเปลี่ยนชื่อคอลัมน์ได้ด้วยการเว้นวรรคและตามด้วยชื่อคอลัมน์ใหม่ แต่ในการทำงานเขียน AS จะทำใหม่เพื่อนร่วมงานอ่านเข้าใจได้ง่ายกว่า
  • query ด้านล่างเราจะดึงคอลัมน์ firstname, lastname จากตาราง customers และ นำ 2 คอลัมน์มา merge เข้าด้วยกันด้วยเครื่องหมาย Pipe และ ตั้งชื่อ คอลัมน์ใหม่ว่า fullName
SELECT 
	firstname,
	lastname,
	firstname || '	' || lastname AS fullName 
FROM customers;
  • การตั้งชื่อคอลัมน์ใน SQL
    • Camel case เป็นการเขียน word ตัวแรกเป็นตัวพิมพ์เล็ก และใช้ตัวพิมพ์ใหญ่เวลาขึ้น word ใหม่ เช่น fullName
    • Snake case เป็นการการเชื่อม words เข้าด้วยกันด้วยเครื่องหมาย underscore เช่น full_name
  • การรวมคอลัมน์แบบ text เข้าด้วยกัน
    • ใช้เครื่องหมาย pipe หรือ || เพื่อรวมคอลัมน์แบบ text เข้าด้วยกัน
    • สามารถใช้เครื่องหมาย pipe เพื่อรวมมากกว่าสองคอลัมน์ก็ได้ เขียน || ต่อกันเรื่อยๆ
    • จาก query ด้านบนเรา ดึงคอลัมน์ firstname ต่อด้วย ช่องว่างหนึ่งช่องในเครื่องหมาย single quote และ ต่อด้วย คอลัมน์ lastname

Functions in SQL

แบ่งออกได้เป็น 3 กลุ่ม

  • Aggregate Functions
    • ใช้สรุปผลสถิติเบื้องต้น
  • Value Functions
    • ใช้ปรับรูปแบบของค่าในคอลัมน์ที่เราต้องการ
  • Window Functions
    • ฟังก์ชัน analytics ใช้สร้างคอลัมน์ใหม่ใน result set 

Aggregate Functions

Aggregate Functionsการใช้งาน
AVGหาค่าเฉลี่ย
SUMหาผลรวม
MINหาค่าต่ำสุด
MAXหาค่าสูงสุด
COUNTนับจำนวนแถว
SELECT 
	AVG(total) AS avg_total,
	SUM(total) AS sum_total,
	MIN(total) AS min_total,
	MAX(total) AS max_total,
	COUNT(total) AS count_total
FROM invoices;
  • ใช้ฟังก์ชัน COUNT นับจำนวนและจะนับเฉพาะแถวที่มีค่าอยู่เท่านั้น
    • query ด้านล่างเราจะนับจำนวนแถวทั้งหมดในตาราง customers และนับจำนวนแถวใน คอลัมน์ company
      • 59 คือจำนวนแถวทั้งหมดของตาราง customers
      • 10 คือจำนวนแถวที่มีค่าอยู่ทั้งหมด ( NOT NULL ) ของคอลัมน์ company
SELECT 
	count(*),
	count(company) 
FROM customers;
  • ใช้ฟังก์ชัน COUNT กับ DISTINCT นับจำนวน unique values ที่อยู่ในคอลัมน์นั้นๆ
    • query ด้านล่างเราจะนับจำนวนประเทศที่ไม่ซ้ำกันของคอลัมน์ country ในตาราง customers
SELECT 
	COUNT( DISTINCT(country))  AS unique_country
FROM customers;

Value Functions

Aggregate Functionsการใช้งาน
LOWERปรับ text เป็นตัวพิมพ์เล็กทั้งหมด
UPPERปรับ text เป็นตัวพิมพ์ใหญ่ทั้งหมด
ROUNDปรับจำนวนทศนิยม
SUBSTRดึง characters ที่จุดเริ่มต้นและจำนวนที่เรากำหนด
STRFTIMEปรับ format คอลัมน์ datatime
COALESCEแทนค่า NULL ในคอลัมน์
TYPEOFใช้ตรวจสอบ data type

เราจะใช้ UPPER, LOWER, SUBSTR ในการปรับรูปแบบของค่าในคอลัมน์ firstname ที่อยู่ในตาราง customers

SELECT 
	firstname,
	UPPER(firstname) AS upper_name,
	LOWER(firstname) AS lower_mame,
	SUBSTR(firstname, 1, 3) AS short_name 
FROM customers 
LIMIT 5;
  • ROUND
    • ใช้ปรับจำนวนทศนิยมของคอลัมน์ที่เป็นตัวเลขแบบ numeric ( ตัวเลขที่มีทศนิยม ) 
SELECT 
	AVG(total) AS avg_total,
	ROUND(AVG(total), 2) AS round_mean_total
FROM invoices;
  • COALESCE
    • ใช้แทนที่ค่า NULL ในคอลัมน์
    • query ด้านล่างเราจะดึงคอลัมน์ company จากตาราง customers และ ใช้ฟังก์ชัน COALESCE เพื่อแทนที่ค่า NULL ในคอลัมน์ company ด้วย ‘Customer’
SELECT 
	company,
	COALESCE(company , 'Customer') AS segment
FROM customers;
  • STRFTIME
    • ใน SQLite ไม่ได้รองรับคอลัมน์แบบ datetime เช่น yyyy-mm-dd ใน คอลัมน์ invoicedate เป็นเพียงการเก็บข้อมูล text ที่ format เหมือนกับ datetime
SELECT 
	invoicedate,
	STRFTIME('%Y', invoicedate) AS year,
	STRFTIME('%m', invoicedate) AS month,
	STRFTIME('%d', invoicedate) AS day
FROM invoices
LIMIT 5;
  • TYPEOF
    • ใช้ตรวจสอบ datatype 
SELECT 
	TYPEOF(invoicedate),
	TYPEOF(STRFTIME('%Y', invoicedate)) AS year,
	TYPEOF(STRFTIME('%m', invoicedate)) AS month,
	TYPEOF(STRFTIME('%d', invoicedate)) AS day
FROM invoices
LIMIT 5;

Window Functions ( Introduction )

  • ฟังก์ชัน analytics ที่ใช้สร้างคอลัมน์ใหม่ใน result set 
  • สังเกตุ window function ให้มองหา OVER( ) ใน SELECT clause เพราะทุก window functions ต้องตามหลังด้วย OVER( ) เสมอ
  • query ด้านล่าง เราดึงคอลัมน์ firstname และ lastname มา merge เข้าด้วยกัน และ ดึงคอลัมน์ country และ ใช้ ROW_NUMBER( ) หนึ่งใน window functions เพื่อสร้างเลขแถว เดี๋ยวจะมาอธิบายเรื่อง window function อีก part นึง
SELECT 
	firstname ||'	' || lastname AS fullName,
	country,
	ROW_NUMBER() OVER()AS rowNum
FROM customers 
LIMIT 5;

WHERE

  • ใช้ WHERE เพื่อฟิลเตอร์ข้อมูล rows ที่เราต้องการด้วยเงื่อนไขแบบต่างๆ

การเขียนเงื่อนไขด้วย logical operators

Logical Operatorsความหมาย
=เท่ากับ
>มากกว่า
<น้อยกว่า
>=มากกว่าหรือเท่ากับ
<=น้อยกว่าหรือเท่ากับ
<>ไม่เท่ากับ
  • ถ้าคอลัมน์ที่เราต้องการฟิลเตอร์เป็น text เช่น คอลัมน์ country เราต้องใส่ชื่อ ประเทศในเครื่องหมาย single quote เช่น ‘France’
  • query เราดึงทุกคอลัมน์จากตาราง customers ฟิลเตอร์เฉพาะลูกค้าที่อาศัยอยู่ในประเทศฝรั่งเศษ
SELECT 
	* 
FROM customers 
WHERE country = 'France';
  • เวลาที่เราต้องการฟิลเตอร์คอลัมน์ที่เป็น text เช่น ชื่อประเทศ ถ้าชื่อประเทศที่อยู่ในคอลัมน์นี้เขียนไม่เหมือนกัน เช่น ชื่อ ประเทศฝรั่งเศษ ในคอลัมน์ country มีอยู่หลายแบบ france, France, FRANCE เป็นต้น เราจะนำ value function มาใช้ใน WHERE Clause ด้วย 
SELECT 
	firstname || '	' || lastname AS fullName,
	country
FROM customers 
WHERE LOWER(country) = 'france';

การเขียนเงื่อนไขมากกว่าหนึ่งเงื่อนไขด้วย AND / OR

  • AND ทุกเงื่อนไขต้องเป็นจริงทั้งหมด SQL ถึงจะดึงข้อมูลกลับมาให้เรา
  • OR ถ้าเงื่อนไขใดเงื่อนไขหนึ่งเป็นจริง SQL ก็จะดึงข้อมูลกลับมาให้เรา
  • AND
    • query ด้านล่าง เราจะดึงทุกคอลัมน์จากตาราง customers ฟิลเตอร์เฉพาะชื่อประเทศ USA และรัฐ NY
SELECT 
	* 
FROM customers 
WHERE country = 'USA' AND state = 'NY';
  • OR
    • query ด้านล่าง เราจะดึงทุกคอลัมน์จากตาราง customers ฟิลเตอร์ชื่อประเทศ USA หรือ France หรือ United Kingdom 
SELECT 
	* 
FROM customers 
WHERE country = 'USA' OR country = 'France' OR country = 'United Kingdom';

การเขียนเงื่อนไขในด้วย IN operator แทนการเขียน OR หลายๆครั้ง

  • จะได้ผลลัพธ์เหมือน query ด้านบน
SELECT 
	* 
FROM customers 
WHERE country IN ( 'USA', 'France', 'United Kingdom');

การเขียนเงื่อนไขแบบช่วงด้วย BETWEEN AND

  • BETWEEN..AND… จะเป็นแบบ inclusive เสมอ inclusive แปลว่า “รวมอยู่ด้วย” เช่น WHERE total BETWEEN 10 AND 20 แปลว่าเราจะฟิลเตอร์ total ที่มีค่าตั้งแต่ 10 ถึง 20 ในทางคณิตศาสตร์เราจะเขียนได้เป็น [10,20]
SELECT 
	invoiceid,
	total
FROM invoices 
WHERE total BETWEEN 10 AND 20;
  • สามารถเขียนกับข้อมูลที่เป็น integer, numeric / real ( ตัวเลขที่มีทิศนิยม ), text หรือ datetime (timestamp : yyyy-mm-dd hh:mm:ss) ได้หมดเลย
  • query ด้านล่างเราจะดึงคอลัมน์ invoicedate และ billingcountry จากตาราง invoices และ ฟิลเตอร์คอลัมน์ billingcountry ที่ชื่อประเทศอยู่ระหว่าง Australia และ Belgium
SELECT 
	invoicedate,
	billingcountry 
FROM invoices 
WHERE billingcountry BETWEEN 'Australia' AND 'Belgium';
  • เวลาต้องฟิลเตอร์คอลัมน์ datetime หรือ timestamp จะใช้ฟงก์ชัน STRFTIME เพื่อปรับ format ของคอลัมน์ก่อน แล้วค่อยใช้เครื่องหมาย = เพื่อเลือกปีและเดือนที่ต้องการ
  • query ด้านล่างเราจะ เลือกทุกคอลัมน์ในตาราง invoices และฟิลเตอร์เฉพาะเดือนที่ 2 ของปี 2011 
SELECT 
	* 
FROM invoices 
WHERE STRFTIME('%Y-%m', invoicedate) =  '2011-02';

การเขียนเงื่อนไขเพื่อหาค่า NULL (missing value)

  • NULL ไม่เท่ากับเลขศูนย์ และไม่เท่ากับ blank space
  • เราจะใช้ syntax column_name IS NULL
  • query ด้านล่างเราจะฟิลเตอร์บริษัทที่มีค่า NULL และจำกัดจำนวนแถว 5 แถวใน result set 
SELECT 
	firstname ||'  '|| lastname AS fullName,
	company 
FROM customers
WHERE company IS NULL
LIMIT 5;

การเขียนเงื่อนไขด้วย NOT

  • NOT operator เพื่อกลับค่า TRUE เป็น FALSE หรือ FALSE เป็น TRUE ของเงื่อนไขใน WHERE clause ได้ เช่น เปลี่ยนจาก IS NULL เป็น IS NOT NULL 
  • สามารถใช้ NOT ได้กับทุก operators
  • query ด้านล่างเราจะฟิลเตอร์เฉพาะบริษัทที่ไม่มีค่า NULL
SELECT 
	firstname || ' 	' || lastname AS fullName,
	company 
FROM customers
WHERE company IS NOT NULL;
  • query ด้านล่างเราจะดึงข้อมูลของลูกค้าที่ไม่ได้อาศัยอยู่ในประเทศ USA, France, United Kingdom
SELECT 
	firstname || '	' || lastname AS fullName,
	country
FROM customers 
WHERE country NOT IN ( 'USA', 'France', 'United Kingdom')
LIMIT 5;

การเขียนเงื่อนไขแบบ pattern matching ด้วย LIKE

  • LIKE สำหรับทำ pattern matching หรือ การหา pattern ที่เราต้องการ แบบ case-insensitive เช่น ‘A%’ กับ ‘a%’ จะหา pattern เดียวกันคือ ชื่อขึ้นต้นด้วยตัว a ไม่สนว่าเป็นตัวพิมพ์ใหญ่หรือตัวพิมพ์เล็ก
  • เราจะใช้ LIKE คู่กับ wildcards หรือ อักขระพิเศษ
    • % ใช้ match ตัวอักษรหรือตัวเลขมากว่าหรือเท่ากับ 1 character ขึ้นไป
    • _ ใช้ match ตัวอักษรหรือตัวเลขเพียง 1 character
  • query ด้านล่างเราจะหาชื่อจริงของลูกค้าที่ขึ้นต้วยตัวอักษร A
SELECT 
	firstname 
FROM customers 
WHERE firstname LIKE 'A%';
  • query ด้านล่างเราจะหาจริงชื่อของลูกค้าที่ขึ้นต้วยตัวอักษรหรือตัวเลขหนึ่งตัวอะไรก็ได้แต่ต้องตามด้วย ark ตามลำดับ
SELECT
	* 
FROM customers
WHERE firstname LIKE '_ark';

การเขียนเงื่อนไขด้วย Regular Expressions

  • ใช้ในการทำ pattern matching เหมือน LIKE แต่ยืดหยุ่นกว่า 
  • REGEXP จะหา pattern แบบ case-sensitive
Regular ExpressionsWhat the Pattern matches
[a-z]ตัวอักษร a-z แบบตัวพิมพ์เล็ก
[A-Z]ตัวอักษร A-Z แบบตัวพิมพ์ใหญ่
[0-9] or /dตัวเลข
/d{4}ตัวเลขติดกัน 4 ตัว
\sหา white space
^Wขึ้นต้นด้วยตัวอักษร W
W$ลงท้ายด้วยตัวอักษร W
a|bหาตัวอักษร a หรือ b
  • query ด้านล่างเราจะฟิลเตอร์ชื่อจริงลูกค้าที่ขึ้นต้นด้วยตัวอักษร A
SELECT 
	firstname,
	country
FROM customers 
WHERE firstname REGEXP '^A';
  • ถ้าเราลองเปลี่ยนฟิลเตอร์ชื่อจริงลูกค้าที่ขึ้นต้นด้วยตัวอักษร A เป็น a จะหาไม่เจอ เพราะ REGEXP จะหา pattern แบบ case-sensitive ตัวพิมพ์เล็กหรือตัวพิมพ์ใหญ่ มีผลกับการหา
  • query ด้านล่างเราจะฟิลเตอร์ชื่อลูกค้าที่ลงท้ายด้วยตัวอักษร m หรือ a
SELECT 
	firstname,
	country
FROM customers 
WHERE firstname REGEXP '(m|a)$';
  • query ด้านล่างเราจะฟิลเตอร์ postalcode ของลูกค้าที่เป็นตัวเลข 4 ตัวติดกัน
SELECT 
	firstname,
	country,
	postalcode 
FROM customers 
WHERE postalcode REGEXP '^\d{4}$';

จบ part I แล้วครับ หวังว่าจะเป็นประโยชน์กับผู้อ่านทุกคน

Comments

Leave a comment