ทดลองทำ SQL Tutorial

SQL  (Structured Query Language) 

    หลังจากทดลองทำ tutorial บนเว็ป w3schools.com พบว่า SQL คือภาษาที่ใช้จัดการ Database โดยมีมาตรฐาน ANSI (American National Standards Institute) แต่การใช้งาน SQL ส่วนแต่ต่างกันเล็กน้อยในเวอชั่นที่ต่างกันออกไป  

RDBMS (Relational Database Management System)

    คือ ระบบการจัดการข้อมูลที่เกี่ยวข้องกัน ซึ่งเป็นรากฐานของ SQL โดยข้อมูลใน RDBMS จะถูกเก็บไว้ในรูปแบบตารางที่มีความสัมพันธ์กันหลายตาราง และแต่ละตารางจะแบ่งย่อยเป็น field หรือ column (แนวตั้ง) และ records หรือ row (แนวนอน) 

    field จะเป็นส่วนของชื่อกลุ่มข้อมูลนั้นๆ เช่น ชื่อ ที่อยู่ เมือง รหัสไปรษณีย์ ประเทศ ส่วน record จะเป็นตัวข้อมูลตามหัวข้อใน field ที่เรียงกันในแนวนอนในแนวนอน

    ตารางแต่ละตารางจะมีชื่อเป็นของตัวเอง เช่น Customers ซึ่งเป็นชื่อตารางด้านล่างนี้ที่มี field ดังนี้ CustomerID, CustomerName, ContactName, Address, City, PostalCode และ Country โดยตารางนี้แสดงข้อมูลออกมาทั้งหมด 9 record

SYNTAX

    คำสั่งที่ใช้ในการสั่งจะมี semicolon ต่อท้ายเสอมเพื่อให้ง่ายต่อการเขียน script สำหรับรันคำสั่งหลายๆคำสั่งในเวลาเดียวกันได้ โดยคำสั่งจะมีลักษณะดังนี้

คำสังจัดการ Database ส่วนใหญ่จะใช้คำสั่งดังนี้ 

• SELECT - ดึงข้อมูลจาก database
• UPDATE - updates ข้อมูลบน database
• DELETE - ลบข้อมูลใน database
• INSERT INTO - ใส่ข้อมูลลงใน database
• CREATE DATABASE - สร้าง database ใหม่
• ALTER DATABASE - แก้ไข database
• CREATE TABLE - สร้าง table ใหม่
• ALTER TABLE - แก้ไขรูปแบบ table
• DROP TABLE - ลบ table
• CREATE INDEX - สร้าง keys เพื่อใช้ในการ search
• DROP INDEX - ลบ keys ทิ้ง

SELECT Syntax

    ในการเรียกดูข้อมูลปกติสามารถใช้ SELECT ได้ โดยมีรูปแบบดังนี้

    รูปแบบบนนี้จะนำข้อมูลมาแสดงจะมีแค่แค่ column ที่เราใส่ไว้เท่านั้นหรือถ้าต้องการแสดง column ทั้งหมด สามารถใช้ * แทนได้ 

    แต่ถ้าต้องการแสดงข้อมูลที่ไม่ซ้ำกัน (ตัด record ที่ซ้ำกัน) สามารถใช้ DISTINCT ได้ซึ่งมีรูปแบบดังนี้

    เมื่อใช้คำสั่ง SELECT Country FROM Customers; ก่อนที่จะมีการใช้ DISTINCT จะเห็นได้ว่ามี Germany ซ้ำกัน 2 รอบ

    แต่เมื่อใช้ SELECT DISTINCT Country FROM Customers; ข้อมูลตัวที่ซ้ำจะหายไปดังนี้

SQL WHERE Clause

    ใช้ในการกรองข้อมูลก่อนนำมาแสดงผล โดยมีรูปแบบดังนี้

    ส่วน condition จะเป็นตัวกรองเช่นใน table Customers เราสามารถเรียกข้อมูล เฉพาะข้อมูลที่มี Country = 'Germany' ได้โดยใช้คำสั่ง SELECT * FROM Customers WHERE Country='Germany'; 

จะได้ตารางดังนี้

    นอกจากเครื่องหมาย '=' แล้ว เราสามารถใช้เครื่องหมายอื่นๆ ในการกำหนด condition ได้โดยมีเครื่องหมายดังนี้

• =         เท่ากับ  
• <>      ไม่เท่ากับ
• >         มากกว่า (กรณีใช้กับตัวอักษรจะเลือกเฉพาะ String ที่ Sort แล้วมีค่ามากกว่ามาแสดง)
• <         น้อยกว่า (กรณีใช้กับตัวอักษรจะเลือกเฉพาะ String ที่ Sort แล้วมีค่าน้อยกว่ามาแสดง)
• >=       มากกว่าหรือเท่ากับ (สามารถใช้กับ String ได้)
• <=       น้อยกว่าหรือเท่ากับ (สามารถใช้กับ String ได้)
• BETWEEN    เลือกเฉพาะข้อมมูลในช่วง โดยมีรูปแบบการใช้ดังนี้ 

          ซึ่งหมายถึงการเลือกข้อมูลเฉพาะในช่วง value1 ถึง value2 เท่านั้น (สามารถใช้กับ String ได้)

• LIKE    เลือกเฉพาะข้อมูลที่มีรูปแบบที่กำหนดโดยมีรูปแบบการใช้ดังนี้ 

          โดย pattern สามาถกำหนดได้โดยใช้ % (แทนตัวอักษรที่จะมีกี่ตัวหรือไม่มีก็ได้) และ _ (แทนตัวอักษรตัวเดียว) เช่น

         WHERE CustomerName LIKE 'a%'        จะหมายถึง String ที่ขึ้นต้นด้วย a หรือ a ตัวเดียว  

       WHERE CustomerName LIKE '%ion%'  จะหมายถึง String ที่มี ion อยู่ในตำแหน่งใดก็ได้

       WHERE CustomerName LIKE '_u%'      จะหมายถึง String ที่มี u เป็นตัวที่ 2

       WHERE CustomerName LIKE 'u_%_%' จะหมายถึง String ที่มี u เป็นตัวแรก และมีความยาว 3 ตัวอักษรขึ้นไป

• IN    เลือกข้อมูลเฉพาะที่ระบุเท่านั้น โดยมีรูปแบบดังนี้

          เช่น WHERE Country IN ('Germany', 'France', 'UK');

AND, OR and NOT Operators

    เป็น Operator ที่ใช้ต่อจาก WHERE โดย

• AND จะหมายถึง ต้องการข้อมูลที่ตรงเงื่อนไขทุกอัน

• OR จะหมายถึง ต้องการข้อมูลที่ตรงกับเงื่อนไขใดเงื่อนไขหนึ่งก็ได้

• NOT จะหายถึง ต้องการข้อมูลที่ไม่ตรงกับเงื่อนไขนั้นๆ

ซึ่งเราสามารถใช้ Operator คละกันหลัง WHERE ได้เช่น  

    

        SELECT * FROM Customers WHERE (NOT City = 'Berlin' AND Country = 'Germany') OR Country = 'USA';

        จะได้ผลดังนี้

        จะเห็นได้ว่าข้อมูลที่ได้ จะมี Country เป็น USA และ Germany แต่ไม่มีข้อมูลที่ City เป็น Berlin

INSERT INTO

        เป็นการเพิ่มข้อมูลอันใหม่ลงในตารางโดยจะไม่เขียนทับข้อมูลที่มีอยู่แล้วซึ่งมีรูปแบบดังนี้ 

        ถ้าใส่ Column ไม่ครบข้อมูลในตารางจะขึ้นเป็น null หรือถ้าเราต้องการเพิ่มข้อมูลในทุกๆ column เราไม่จำเป็นต้องระบุ column ให้เว้นว่างไว้ดังนี้

NULL Value

        Null value จะเกิดขึ้นเมื่อเราเพิ่มข้อมูลลงใน table ไม่ครบทุก field หรือ column ซึ่งมีวิธีการ Test ดังนี้

• IS NULL จะใช้ตามหลัง WHERE โดยจะเป็นการดึงข้อมูลทั้งหมด ที่เป็น Null ออกมามีรูปแบบการใช้ดังนี้

        เช่น WHERE Country IS NULL;  จะเลือกข้อมูลที่มี Country เป็น NULL ออกมาแสดง

• IS NOT NULL จะใช้ตามหลัง WHERE โดยจะเป็นการดึงข้อมูลทั้งหมดที่ ไม่ใช่ Null ออกมา

เช่น WHERE Country IS NOT NULL;  จะเลือกข้อมูลที่มี Country ไม่เป็น Null ออกมาแสดง

UPDATE Statement

        เป็นการเขียนทับข้อมูลที่มีอยู่ โดยจะอัพเดทข้อมูลตัวที่ตรงเงื่อนไขหลัง WHERE ( ถ้าไม่กำหนด WHERE ข้อมูลทุกตัวจะถูก update ไปด้วย ) ซึ่งมีรูปแบบดังนี้

        เช่น UPDATE Customers SET ContactName = 'Alfred Schmidt',City= 'Frankfurt' WHERE CustomerID = 1;

    จะเป็นการ อัพเดท ค่า ContactName และ City ที่ CussstomerID เป็น 1

DELETE Statement

        เป็นการลบ ข้อมูลในตารางที่ตรงตามเงื่อนไขหลัง WHERE ทิ้ง ถ้าไม่มี WHERE ข้อมูลในตารางทั้งหมดซึ่งมีรูปแบบการใช้งานดังนี้ 

       เช่น DELETE FROM Customers WHERE City = 'Berlin;  จะหมายถึงลบข้อมูลใน Table Customers ที่มี City เป็น Berlin

SELECT TOP Clause

        เป็นการเลือกแสดงข้อมูลตัวอย่างจากข้อมูลทั้งหมดตามจำนวนที่ระบุ ซึ่งเหมาะสมกับ Database ที่มีขนาดใหญ่ แต่การใช้งานอาาจะต่างกันเมื่อใช้ version ที่ต่างกัน

• SQL Server / MS Access Syntax

        เป็นการเลือกข้อมูลได้ทั้งแบบ % ของข้อมูลทั้งหมด หรือ จะกำหนดเป็นตัวเลขก็ได้เช่น

    SELECT TOP 50 * FROM Customers;  จะเป็นการเอาตัวอย่างแค่ 50 ตัวแรกของ table Customers มาแสดงเท่านั้น

    SELECT TOP 50 PERCENT * FROM Customers;  จะเป็นการเอาตัวอย่างแค่ 50 % แรกของ table Customers มาแสดงเท่านั้น

• MySQL Syntax

        เป็นการเลือกข้อมูลมาตามจำนวนเลขที่อยู่หลัง LIMIT 

• Oracle Syntax

        สามารถเลือกจำนวนตัวอย่างข้อมูลโดยใข้ ROWNUM <= number หลัง WHERE

Join

        เมื่อต้องการสร้างตารางใหม่รูปแบบใหม่จาก 2 ตารางสามารถใช้ Join ในการสร้างตารางขึ้นมาใหม่ที่มี่ Column มาจากตารางทั้ง 2 โดยจะมีการนำตารางทั้งสองมา Cross กันทำให้เกิดตารางรูปแบบที่มี Column ของทั้ง 2 ตารางและมี จำนวนข้อมูล = จำนวณข้อมูลจากตาราง1 x จำนวณข้อมูลจากตาราง2
       
        แต่ในการ join ถ้าต้องการให้ข้อมูลมีความหมาย ควรจะมี key ที่ชี้ข้อมูลไปยัง table อีกอัน
       
        ลักษณะการ Inner join (การ join ที่เอาแต่ข้อมมูลที่ทับซ้อนกันมาแสดง) มีรูปแบบการเขียนดังนี้
        

        กำหนดให้ตารางแรกมีชื่อว่า table1 และ ตารางที่ 2 มีชื่อว่า table2

   Cross กัน

   

    จะได้ตารางใหม่ดังนี้

    จะเห็นได้ว่ามีจำนวณข้อมูลเพิ่มมากขึ้น แต่วิธีนี้จะมีประโยชน์เมื่อมี key ที่ใช้ระบุข้อมูลที่อยู่อีกตาราง ดังนี้ เมื่อ Cross แล้วต้องบอกเงื่อนไขในการนำข้อมูลมาแสดงผลด้วย ซึ่งในกรณีนี้ จะเลือกข้อมูลมาเฉพาะ ที่มี table1.Shirt_color = table2.Index เท่านั้นและ มี Column แค่ Name Age และ Shirt_color เท่านั้นซึ่งสามารถเขียนในรูปแบบ SQL ได้ดังนี้

        SELECT table1.Name, table1.Age, table2.Shirt_color FROM table1 

        INNER JOIN table2 ON table1.Shirt_color = table2.Index;

    ผลลัพธ์จะออกมาเป็น

    และเมื่อลองกับ table ในเว็ป w3schools.com จะได้ดังนี้

    SELECT Orders.OrderID, Customers.CustomerName

    FROM Orders

    INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

    จะได้ตารางดังนี้

ซึ่งมาจากตาราง Order

และตาราง Customers

Link ที่เกี่ยวข้อง :

• https://www.w3schools.com/sql/

ทดลอง MySQL

ได้มีการ Download ตัว installer ของ mySQL จาก https://dev.mysql.com/downloads/installer/

จากนั้นได้ลองทำตาม tutorial ใน link นี้ https://dev.mysql.com/doc/mysql-getting-started/en/

 
    ป้อนคำสั่ง SHOW DATABASE; เพื่อดู database ทั้งหมดที่มี 

    จากนั้นเพิ่มคำสั่ง CREATE DATABASE pets; แล้วป้อนคำสั่ง SHOW DATABASES; จะเห็นได้ว่ามี database pets เพิ่มขึ้นมา
 

    ป้อนคำสั่ง USE pets; เพื่อเลือกใช้ database pets

    ป้อนคำสั่ง CREATE TABLE cats; พร้อมกับป้อนรายละเอียดของตารางต่างๆ เช่น id name owner โดยใช้ column เป็น id    

    เมื่อป้อนคำสั่ง SHOW TABLES; จะพบว่ามี table cats เพิ่มขึ้นมาใน database ของ pets    

    ต่อมาป้อนคำสั่ง DESCRIBE cats; เพื่อดูว่าแต่ละ column มีชื่อว่าอะไร และเก็บข้อมูลแบบไหน column ไหนเป็น primary key มีค่าเป็น null หรือไม่

    

    เมื่อมีตารางแล้วก็สามารถเพิ่มข้อมูลในตารางได้โดยใช้คำสั่ง INSERT INTO cats (name, owner, birth) VALUES และเพิ่มรายละเอียดต่างๆ 

    

    เสร็จแล้วให้เช็ค table cats โดยใช้คำสั่ง SELECT * FROM cats; จะเห็นว่าตารางมีการอัพเดทข้อมูลตามทีป้อนไว้ก่อนหน้า

    จากการเรียกดูข้อมูลก่อนหน้านี้เป็นการเรียกดูข้อมูลจาก table โดยให้แสดงผลทุก record และ และทุก column แต่ตอนนี้ต้องการดูแค่ column name และแสดง record เฉพาะตัวที่มี owner = 'Casey''

    นอกจากการเพิ่มข้อมูลลง table แล้ว เราสารมาถลบข้อมูลข้างในนั้นได้เช่นกันโดยใช้คำสั่ง 

DELETE FROM cats WHERE name='Cookie'; จากนั้นเรียกดู table โดยใช้คำสั่ง 

SELECT * FROM cats; จะได้ตารางออกมาดังนี้

    จากตารางจะเห็นได้ว่ามีคำสั่งลบ record ที่มี name='Cookie' ออกไปเรียบร้อยแล้ว

    นอกจากการเพิ่ม/ลด จำนวณ record แล้วเรายังสามารถเพิ่ม column ให้ตารางได้อีกด้วย โดยใช้คำสั่ง ALTER TABLE cats ADD gender CHAR(1) AFTER name; ซึ่งหมายถึงการ เพิ่ม column ที่มีชื่อว่า gender เข้าไปโดย column นี้จะอยู่ถัดจาก column name และจะมีหน้าที่เก็บข้อมูลเป็นตัวอักษรตัวเดียว ( CHAR(1) )

    เมื่อใช้คำสั่ง DESCRIBE cats; เพื่อดูข้อมูล table จะเห็นที่ช่อง Field ว่ามี gender เพิ่มมา 

    

    หรือจะใช้คำสั่ง SHOW CREATE TABLE cats\G; เพื่อดูข้อมูลการสร้าง table ก็ได้

    

    ในกรณีต้องการจะลบ column ออกสารมารถใช้คำสั่ง ALTER TABLE cats DROP gender; เพื่อลบ column gender ออกไปและเมื่อใช้คำสั่ง DESCRIBE cats; จะเห็นได้ว่า column gender ได้หายไปแล้ว

ปัญหาที่พบ

  - เมื่อเปิด MySQL command line แล้วไม่สามารถต่อกับ database ได้ แก้ไขโดย เข้าไปเปิดการใช้งานของ database ในโปรแกรม MySQL

สิ่งที่ได้เรียนรู้

  ได้รู้วิธีการใช้งานภาษา SQL เบื้องต้น 

คำณวน GPA ด้วย Pandas

จากไฟล์ csv ที่เก็บข้อมูลผลการเรียน

    สามารถคำณวนเกรดโดยใช้ Library Pandas ซึ่งเป็นเครื่องมือที่ใช้จัดการข้อมูลเข้ามาช่วยได้ดังนี้

    เรียกใช้ Pandas บน Python

    เปิดไฟล์ csv ที่เก็บเกรดไว้ด้วย Pandas โดยตัว Pandas จะโหลดข้อมูลมาเก็บไว้เป็นตารางโดยเมื่อนำ self.score_data มาใส่ฟังก์ชั่น print() จะได้ตารางดังนี้

 

    เห็นได้ว่าตารางที่แสดงจะมี keys เป็นตัวเลขที่เริ่มจาก 0 และนับเพิ่มขึ้นเรื่อยๆ   

    เลือก column ที่จะมาแสดงให้มีแค่ semester และ credit แล้ว sum กันโดยมอง column semester เป็นหลัก โดยผลการ sum จะเป็นจำนวณ credit ทั้งหมดในแต่ละเทอมซึ่งมีหน้าตาดังนี้

    

    จากตารางที่ print() จะเห็นได้ว่า keys จากตัวเลขจะกลายเป็น semester แล้ว และพบว่ามีการรวม credit ในแต่ละ semester ได้ถูกต้อง

    จากนั้นเก็บเทอมต่างๆ จาก column semester แต่จะเจอปัญหาคือ เกิดการ sort ที่ไม่ถูกต้องตามลำดับเทอม

    การกำหนด key ในการ sort มีการใช้ lambda ซึ่งเป็นฟังก์ชั่นที่ไม่แสดงตัวและจะรีเทิร์นค่า x ออกมาซึ่งในฟังก์ชั่นนี้ได้มีการนำ string ที่เป็น เทอมมาแบ่งโดยตัว '_' เช่น เทอม '1_58' จะได้เป็น List 

['1', '58'] และกำหนดให้ sort จากเลขปี เป็นหลัก    

    เมื่อ sort โดยกำหนด key ในการ sort เองจะเห็นได้ว่า เทอมแต่ละเทอมเรียงกันได้อย่างถูกต้องแล้ว

    จากนั้นจึงคำนวณ GPAX หรือ เกรดเฉลี่ยต่อเทอมโดยการนำ list ที่เก็บเทอมที่ sort ไว้แล้วมาไล่แสดงผล โดยจะมีการกรอง record ให้เหลือแค่เทอม เทอมเดียมโดยใช้ 

data_field[data_field['semester'] == semester] 

จากนั้นเก็บผลที่ได้ไว้ใน df_temp แล้วจึงนำ column credit และ grade_number มาคูณกันแล้ว sum จากกันหารด้วย ผล sum ของ credit จะได้เกรดเฉลี่ยในเทอมนั้นออกมาตามสูตร

GPAX = Σ (grade x credit) / Σ (credit)

    จากนั้นจึงนำมาแสดงผล โดยเกรดที่ได้จะเป็นทศนิยม 2 ตำแหน่งแบบปัดเลขทิ้ง

    และใช้วิธีแบบเดียวกันในการคิด GPA หรือเกรดเฉลี่ยรวมทั้งหมด แต่จะไม่มีการกรองด้วยเทอมซึ่งสามารถทำได้ดังนี้

   จะได้หน้าตา report ทั้งหมดดังนี้

link ที่เกี่ยวข้อง

  - ไฟล์ CSV

  - Pandas

  - code

ปัญหาที่พบ

  - Pandas ไม่มีคำสั่ง weighted sum แก้โดยนำ column credit และ grade ของแต่ละวิชามาคูณกัน จากนั้นนำผลคูณทั้งหมดมาบวกกัน แล้วหารด้วย ผล sum credit ทั้งหมด

  - มีการ sort semester มาให้แต่ผิดลำดับเวลา แก้ไขโดย การ sort แบบกำหนด keys ให้

  - หลังจาก sum ข้อมูลโดย group by semester แล้ว ไม่สามารถเพิ่มแถว GPAX ได้โดยตรรง แก้ไขโดยการนำค่าในตารางมาคำณวน GPAX แยกตาหาก

สิ่งที่ได้เรียนรู้

  วิธีการใช้ pandas เบื้องต้น