Constraints (Week 3)

What constraints does your selected DBMS support?

     Constraints คือ ข้อบังคับหรือเงื่อนไขในการอนุญาติให้เก็บเฉพาะข้อมูลที่เหมาะสมลงในฐานข้อมูลเพื่อให้การเลือกข้อมูลจากฐานข้อมูลเป็นไปอย่างถูกต้อง

     - NOT NULL
     - UNIQUE
     - PRIMARY KEY
     - FOREIGN KEY
     - CHECK
     - DEFAULT

How to specify them?

      วิธีใช้งาน
CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
);

     - NOT NULL - บ่งบอกว่าใน column จะไม่สามารถเก็บค่า NULL ไว้
     - UNIQUE - กำหนดให้แต่ละ row ของ column จะเป็นค่าที่ไม่ซ้ำกัน
     - PRIMARY KEY - เป็นการกำหนดให้ column นั้นไม่เป็นค่า NULL และมีค่าที่ไม่ซ้ำกัน ช่วยในการการค้นหาและบันทึกข้อมูุลในตารางได้ง่ายและรวดเร็ว
     - FOREIGN KEY - เป็นการกำหนดค่าในตารางหนึ่งอ้างอิงไปยังค่าตารางอื่นมีความสัมพันธ์กัน
     - CHECK - เช็คค่าใน column ว่าเป็นไปตามที่กำหนดไว้
     - DEFAULT - ระบุค่าเริ่มต้นใน column

ทดลอง

NOT NULL , CHECK , DEFAULT

รูปภาพแสดงการสร้างตารางและรายละเอียด

     ทำการสร้างตารางใหม่ขึ้นมา โดยกำหนดให้ name และ lastname ไม่เป็น NULL และกำหนด check ให้ age ต้องมีค่ามากกว่า 16 และตั้งค่า default ให้ married ให้เป็น N

รูปภาพแสดงการใส่ข้อมูลลงตาราง

     สังเกตว่าถ้าเราไม่พิม name หรือ lastname จะทำให้ค่าเริ่มต้นเป็น default ซึ่งก็คือ NULL ทำให้ไม่สามารถใส่ข้อมูลได้

รูปภาพแสดงการใส่ข้อมูลแบบปกติ

     ทดลองใส่ค่าปกติลงไปในตาราง

รูปภาพแสดงการใส่ข้อมูลโดยไม่ใส่ married

     ทดลองไม่ใส่ค่า married ลงไป ทำให้ข้อมูลเป็นค่า Default ซึ่งจะกลายเป็น N ตรงช่อง married ตามที่เราได้กำหนดไว้ตอนแรก

ปัญหาที่พบ

รูปภาพแสดงการใส่ค่า age น้อยกว่า 16

     ทดลองใส่ค่า age น้อยกว่า 16 ตามที่ได้เขียน Check แต่ก็ยังสามารถเก็บข้อมูลได้ไม่เป็นไปตามที่คิดไว้


UNIQUE , PRIMARY KEY

รูปภาพแสดงการสร้างตาราง employee

     ทำการสร้าง Table ใหม่ขึ้นมา โดยกำหนดให้ Column employee_id เป็น primary key โดย primary key จะทำให้ Column นั้นเป็น Not NULL เสมอและ phone_number เป็น unique

รูปภาพแสดงการใส่ค่าซ้ำลงไป

     ทดสอบใส่ค่าที่ employee_id เดิมลงไปจะเห็นว่าไม่สามารถเพิ่มข้อมูลได้เพราะ Column employee_id เป็น primary key ต้องการค่าที่ไม่ซ้ำ ส่วนที่ Column phone_number ก็ไม่สามารถใส่ค่าซ้ำได้เช่นกันเพราะเป็น unique

รูปภาพแสดงการใส่ค่าโดยไม่ใส่ Phone_number

     ทดสอบใส่ค่า name สามารถซ้ำกันได้เพราะไม่ได้กำหนด Constraints ให้เป็น Primary key หรือ Unique ส่วน Column Phone_Number ที่กำหนด Constraints ให้เป็น Unique สามารถใส่ค่า NULL ลงไปได้


FOREIGN KEY

     ทำการสร้างตารางขึ้นมาใหม่ โดยสร้างตารางชื่อ product และ orders โดยตาราง orders จะมี foreign key ที่ตัว product_id และชี้ไปยัง id ในตาราง product เพื่อเป็นการอ้างอิงข้อมูล

รูปภาพแสดงรายละเอียดตารางทั้ง 2

รูปภาพแสดงข้อมูลทั้งที่ใส่ลงไปทั้งสองตาราง

     ทดลองใส่ข้อมูลลงไปทั้งสองตาราง

ทดลองใส่ค่าที่ไม่มีในตาราง product

     ถ้าเราใส่ค่า product_id ที่ไม่มีอยู่ในตาราง product ลงไปในตาราง orders จะไม่สามารถใส่ค่าลงไปได้ เนื่องจากเรามีการกำหนดให้มีความสัมพันธ์กันแบบ foreign key
 

อ้างอิง
http://code.function.in.th/sqlserver/data-type
http://www.w3schools.com/sql/sql_constraints.asp

SQL Tutorial (Week 2)

SQL Tutorial 
     ฝึกใช้คำสั่งเบื้องต้นในการทำ database โดยเรียนรู้จากเว็บ http://www.w3schools.com/sql/default.asp


SQL Home
     SQLเป็นภาษาพื้นฐานในการเข้าถึงฐานข้อมูล การฝึก SQL ในเว็บ w3schools จะสอนวิธีการใช้ SQL การเข้าถึงและจัดการข้อมูลใน: MySQL, SQL Server, Access, Oracle, Sybase, DB2 และระบบฐานข้อมูลอื่น ๆ

SQL Intro
     SQL คืออะไร? SQL เป็นพื้นฐานมาจากโครงสร้างของ Query Language ช่วยให้เข้าถึงและจัดการข้อมูลได้ง่าย และ เป็นมาตรฐาน ANSI (American National Standards Institute)
    SQL ทำอะไรได้บ้าง?
          1. สามารถสอบถามข้อมูลกับฐานข้อมูล
          2. สามารถดึงข้อมูลจากฐานข้อมูล
          3. สามารถเพิ่มบันทึกลงฐานข้อมูล
          4. สามารถปรับปรุงเปลี่ยนแปลงฐานข้อมูล
          5. สามารถลบข้อมูลบนฐานข้อมูล
          6. สามารถสร้างฐานข้อมูลใหม่
          7. สามารถสร้างตารางใหม่ในฐานข้อมูล
          8. สามารถสร้างกระบวนการการจัดเก็บในฐานข้อมูล
          9. สามารถสร้างมุมมองในฐานข้อมูล
          10. สามารถกำหนดสิทธิ์ในการเข้าใช้ต่างๆ

SQL Syntax
     ใน Database Tables ส่วนใหญ่จะเก็บข้อมูลไว้ 1 ตารางหรือมากกว่า 1 ส่วนในรูปด้านล่างจะเป็นตารางของ Customers

รูปแสดงตารางข้อมูลของตาราง Customers

     ซึ่งในตัวอย่างของ SQL Statements จะมีให้เราเลือกว่าจะดึงข้อมูลจาก tableไหนออกมาดูโดยใช้คำสั่ง SELECT * FROM [tableที่เราต้องการในฐานข้อมูลที่เรามี]; ซึ่งในตัวอย่างจะมี table ให้เลือกอยู่ 8 อย่าง ดังภาพที่แสดงด้านล่าง

ภาพตัวอย่างการดึงข้อมูลจากตารางCategories

SQL Select

     วิธีการเลือกดูหัวข้อของข้อมูลที่เราต้องการจะใช้คำสั่ง

SELECT column_name,column_name

FROM table_name;

     

ภาพแสดงตัวอย่างในการดึงข้อมูลออกมาเพียง 3 Column

     จากภาพจะเห็นว่าเราเลือกมาเพียง 3 Column คือ CustomerID,CustomerName,Address

     ส่วนการใช้ SELECT * FROM table_name; นั้นตัว '*' เป็นตัวบ่งบอกว่าเราจะเอาทุก Column ในตารางนั้นๆ

ภาพแสดงการเลือกทุก Column ในตาราง

SQL Distinct

     เป็นคำสั่งที่ใช้ดึงเฉพาะข้อมูลที่ไม่ซ้ำกันออกมา โดยใช้คำสั่ง

SELECT DISTINCT column_name,column_name

FROM table_name;

ภาพแสดงการดึงเฉพาะข้อมูลที่ไม่ซ้ำจากตาราง Orders

     จากภาพเราต้องการรู้ข้อมูลที่ไม่ซ้ำกันในตารางของ Orders จากหัวข้อ EmployeeID

SQL Where

     เป็นคำสั่งที่ต้องการค้นหาข้อมูลที่ระบุไว้

SELECT column_name,column_name

FROM table_name

WHERE column_name operator value;

ภาพแสดงการค้นหาคำว่า Mexico

     จากภาพเราต้องการค้นหาคำว่า Mexico ใน Country ข้อมูลที่ได้ก็จะแสดงออกมาแต่ประเทศ Mexico

     ส่วน Operators ที่ใช้ใน Where ก็สามารถใช้ได้หลายรูปแบบดังนี้

ภาพแสดง Operators ที่มีให้เลือกใช้

SQL And & Or

     เป็นคำสั่งที่ใช้แสดงเมื่อเราใส่เงื่อนไขเป็นจริง เช่น

SELECT * FROM Customers

WHERE Country='Germany'

AND City='Berlin';

     เราจะแสดง Customers ที่อยู่ในประเทศ Germany และ เมือง Berlin เท่านั้น ส่วน

SELECT * FROM Customers

WHERE City='Berlin'

OR City='München';

    เราจะแสดง Customers ที่อยู่ในเมือง Berlin หรือเมือง München ทั้ง2เมือง

    หรือเราจะใช้การเขียนผสมกันของ And & Or เช่น

SELECT * FROM Customers
WHERE Country='Germany'
AND (City='Berlin' OR City='München');

  เราจะแสดง Customers ที่อยู่ในประเทศ Germany และ เมือง Berlin หรือ München 

ภาพตัวอย่างการใช้ And และ Or ผสมกัน

SQL Order By
     เป็นคำสั่งที่ไว้ใช้เรียงข้อมูล จากน้อยไปมาก และ จากมากไปน้อยโดยใช้คำสั่ง

SELECT column_name, column_name
FROM table_name
ORDER BY column_name ASC|DESC, column_name ASC|DESC;

     โดย ASC ย่อมาจาก ascending คือ เรียงจากน้อยไปมาก DESC ย่อมากจาก descending คือ เรียงจากมากไปน้อยแต่ถ้าเราไม่ระบุชนิดของ ORDER BY column_name ASC|DESC ค่าเริ่มต้นที่กำหนดไว้คือ ASC

ภาพตัวอย่างการใช้ ASC และ DESC

     จากภาพด้านบนเราจะใช้การเรียงข้อมูลทั้ง ASC และ DESC โดยเราจะเรียงชื่อ Country ก่อนจากน้อยไปมาก คือ A ถึง Z และ อันดับต่อมาจะให้เรียง CustomerName จากมากไปน้อย คือ Z มา A

SQL Insert Into
     เป็นการเพิ่มข้อมูลเข้าไปในฐานข้อมูลเดิมโดยใช้คำสั่ง

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

     โดยเราจะลองใช้คำสั่ง

INSERT INTO Shippers (ShipperName, Phone)
VALUES ('Test','0123456789');

ภาพแสดงผลลัพธ์ที่ได้จากการใช้คำสั่ง Insert Into

     จากรูปแสดงให้เห็นผลลัพธ์จากการลองใช้คำสั่งด้านบน จะเห็นว่ามีการเพิ่มข้อมูลเข้าไปในฐานข้อมูลเดิม

SQL Update
     เป็นการแก้ไขข้อมูลที่มีอยู่แล้วในฐานข้อมูล โดยใช้คำสั่ง

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

     update ตามด้วยชื่อของตารางที่เราต้องการ , set คือการจะแก้ไขเป็นชื่ออะไร , where เพื่อบอกว่าแก้ไขแทนที่ตรงไหนในฐานข้อมูล
     ข้อควรระวัง ถ้าลืมใส่ where ลงในคำสั่งจะทำให้เกิดการเปลี่ยนแปลงทุก Column ดังภาพด้านล่าง

ภาพแสดงผลลัพธ์ที่ได้เมื่อไม่ใส่ Where

SQL Delete
     เป็นการลบข้อมูลใน row ออกจาก table นั้นๆ โดยใช้คำสั่ง

DELETE FROM table_name
WHERE some_column=some_value;

ภาพแสดงผลลัพธ์ที่ได้จากการใช้คำสั่ง Delete

     จากภาพได้ลองลบ Country Mexico ออกจากฐานข้อมูล ซึ่งจะเห็นได้ว่า แถวที่ 2 3 13 นั้นได้หายไปเพราะเดิมที่ 3 แถวนั้นเก็บข้อมูลประเทศของ Mexico เอาไว้นั้นเอง
     หากต้องการลบข้อมูลทั้ง Table ก็ให้ใช้คำสั่ง DELETE FROM table_name; หรือ DELETE * FROM table_name;
     ข้อควรระวัง หลังจากลบข้อมูลทิ้งไปแล้ว เราไม่สามารถยกเลิกได้

     หลักจากทดลองคำสั่งเบื้องต้นแล้ว ได้ทำการโหลดโปรแกรมการจัดการฐานข้อมูลมาใช้ โดยตัวที่เลือกคือ Mysql เพราะเป็น Open Source License โดย download จาก http://dev.mysql.com/downloads/windows/installer/ เป็น version 5.7.10

     เมื่อลงโปรแกรมเสร็จเรียบร้อยแล้วเราจะเปิดโปรแกรม เป็นแบบ command line ใส่ passwordและเริ่มการใช้งานได้ทันที

รูปภาพหลังจากใส่รหัสผ่านแล้ว

     จากนั้นใช้คำสั่ง CREATE DATABASE movie; จะสร้าง Database ขึ้นมาใหม่ และพิมพ์คำสั่ง show databases; เพื่อให้แสดงข้อมูลใน database ที่เรามี

รูปภาพแสดงข้อมูล database ที่เรามี

  
     เมื่อเราต้องการเข้าไปแก้ไขข้อมูลใน movie ให้ใช้คำสั่ง use movie;
     จากนั้นทำการสร้าง Table โดยใช้คำสั่ง

CREATE TABLE horror (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
name VARCHAR(50),
time_minutes int,
rate float);

     โดยค่าของ id จะไม่มีทางเป็น NULL และจะเพิ่มขึ้นที่ละ 1
     จากนั้นพิมคำสั่ง show tables; เพื่อดูตารางที่เราสร้าง

รูปภาพแสดงชื่อ Table ใน Database Movie

     เราสามารถดูรายละเอียดของชนิดตัวแปรที่เรากำหนดใน tableได้ด้วยคำสั่ง describe horror;

รูปภาพรายละเอียดของ Table horror

     หลังจากนั้นได้ทดลองใส่ข้อมูล โดยใช้คำสั่ง Insert Into

ผลลัพธ์ที่ได้หลังจากใส่ข้อมูลลง Table

ปัญหาที่พบ
     เนื่องจากเวลาที่เราใส่ข้อมูลลงไปใน Table เลข id จะเพิ่มขึ้นเรื่อยๆที่ละ 1 เมื่อเราลบข้อมูลทิ้งไป เลข id ที่โดนลบไปนั้นจะไม่ถูกแทนที่ด้วยข้อมูลใหม่ ทำให้เลข id นั้นถูกข้ามไปเลย ซึ่งวิธีแก้ไขคือ ตอนเราจะใส่ข้อมูลแทนที่เลข id ที่หายไปนั้น ให้ใช้คำสั่ง

insert into horror (id,name,time_minutes,rate)
value ('เลขที่หายไป','name','time','rate');

     เพื่อที่จะทำให้ id ที่หายไปเติมเต็มด้วยข้อมูลอื่นแทน สำหรับคนที่ต้องการให้เลข id มีการเรียงติดต่อกัน

การคำนวนข้อมูลเกรดเฉลี่ย (GPA)

     การคิดคำนวน GPA โดยใส่ข้อมูลลงใน spreadsheets ของ google doc เพราะว่าสามารถใช้งานง่ายเข้าถึงได้สะดวกเพียงแค่ต่ออินเตอร์เน็ตก็สามารถสร้างข้อมูลได้เลย และสามารถให้ผู้อื่นเข้าถึงข้อมูลได้ง่าย

Link >> https://docs.google.com/spreadsheets/d/18ZbDBeWd9Fh2hwvkCQgIOV8enUwV4P8DLgxKSig4a3E/edit#gid=0

รูปภาพตัวอย่างการใส่ข้อมูล

     โดยจะใส่รหัสวิชา ชื่อวิชา หน่วยกิต ตอนเรียน เกรดที่ได้และมีการแยกเกรดเฉลี่ยของแต่ละเทอมเอาไว้ ส่วนการแปลงตัวอักษรเกรดเป็นตัวเลขเราจะใช้สูตร 
=IF(D3="A",4*B3,IF(D3="B+",3.5*B3,IF(D3="B",3*B3,IF(D3="C+",2.5*B3,IF(D3="C",2*B3,IF(D3="D+",1.5*B3,IF(D3="D",1*B3)))))))  
ภายในสูตรจะทำการคูณหน่วยกิตไปแล้วในตัว และเกรดเฉลี่ยรวมของแต่ละเทอมจะนำค่า ที่ได้จากสูตรมาบวกกันแล้วหารด้วยจำนวนหน่วยกิตของแต่ละเทอมโดยใช้สูตร
=sum(E3:E10)/sum(B3:B10) 
ส่วนการหา GPA นัั้นจะใช้สูตร =Sum(E3:E10,E15:E22,E27:E33,E38:E43,E48:E54)/Sum(B3:B10,B15:B22,B27:B33,B38:B43,B48:B54)

Question
- Can we calculate average GPA of all students?
Ans  หากเราต้องการคำนวณเกรดเฉลี่ยรวมของนักเรียนทุกคนเราควรจะสร้างแบบฟอร์มในการใส่ข้อมูลลงใน row และ column ให้เหมือนๆกัน เช่นการ save file เป็น .csv เพื่อง่ายต่อการดึงข้อมูลมาคำนวน

- Can we calculate average grade of a subject?
Ans  ถ้าเราต้องการคำนวนเกรดเฉลี่ยรวมของแต่ละวิชา เราควรจะ search รหัสวิชาเพราะรหัสวิชานั้นจะไม่มีทางซ้ำกัน เมื่อหารหัสวิชาเจอในนั้นควรจะเก็บค่าเกรดและจำนวนนักศึกษาเอาไว้เพราะแต่ละวิชาอาจมีจำนวนนักศึกษาไม่เท่ากัน และเราสามารถดึงข้อมูลตรงนั้นออกมาใช้ในการคำนวนได้