Database System - slide week 3

https://docs.google.com/presentation/d/1lsH6lMC7ofC6Q_zZMTGXa_clC2xK8yZm1BTF6LgBKcg/edit?usp=sharing

Database System - work 3 insert data to DBMS

what you have done/learned

- การใช้งาน Module SQLconnector ของ Python  เบื้องต้น
- หลายๆ Module จะรองรับเฉพาะ version ที่ตรงกันเท่านั้น (เช่น python 3.4 ,2.7)
- การใช้ power shell ใน window
- การ insert data to database , create database , delete database , delete table, create table  การกำหนด PK, ForeignKey, NOTNULL ,type ,auto increatement,
การรับส่วน Error ผ่านทาง module SQLconnector
- สำเร็จในการ แปลง file csv จากที่ import มาจาก
https://docs.google.com/spreadsheets/d/16zSaz5njoh-vLxbrVzll4fLuU3ydmjfWm1_mYEMflKU/edit?usp=sharing
ให้กลายเป็น file csv ที่มีรูปแบบที่กำหนด จากนั้นนำไฟล์ที่ มีรูปแบบที่กำหนดไว้
ไป เข้า ใน database ได้ ซึ่งจะคือ ถ้ามีรูปแบบที่กำหนดถูกต้อง ก็สามารถ นำเข้าได้โดยไม่มีผลกระทบใดๆ
- แบ่งโค้ดเป็นหลายส่วน  ส่วนแปลงไฟล์ให้เป็นรูปแบบมาตรฐาน ส่วนสร้าง database ส่วนสร้าง table ส่วนนำข้อมูลเข้า table

- code https://github.com/poklonas/Python-GPA-cal
- commit
เตรียมฟังก์ชั่นในการสร้างไฟล์รูปแบบที่กำหนดไว้
ฟังก์ชั่น สร้างไฟล์ตามรูปแบบที่ต้องการเสร็จแล้ว
แก้ปัญหา มีบรรทัดว่างในไฟล์
เปลี่ยนให้สามารถรับค่าชื่อไฟล์ผ่าน command line
แยกไฟล์ระหว่างสร้างtable, insert
สร้าง database, table ได้แล้ว
insert ได้แล้ว

ผลลัพธ์สุดท้าย

any problem/solution

การทำงานเป็นกลุ่มใหญ่ ที่ไม่คุ้นเคย และไม่ค่อยได้เจอหน้ากัน ทำให้ติดต่อกันค่อนข้างลำบาก และอาจจะทำให้งานไม่ไปในทางเดียวกัน  แก้ปัญหาโดยการสร้างกลุ่ม ใน Facebook , Messenger ขึ้นมาเพื่อติดต่องานโดยเฉพาะ และ ลงรายละเอียดต่างๆ เกี่ยวกับงานเอาไว้ ใช้ git hub เพื่อ แสดงโค้ดที่แต่ละคนจะใช้ในการจัดการกับหัวข้อต่างๆ ใช้บล๊อคเพื่อเขียนรายละเอียดมากขึ้น และ คอยพูดคุยงานด้วยเสียงผ่านทาง discord หากต้องใช้

ไม่สามารถใช้งาน Module SQLconnector ได้ เนื่องจากใช้ version python ไม่ตรง และ ไม่ได้ลง module ทำให้ต้องลบpython และลง python 3.4 และ module ใหม่ 

ต้องการแยก subject id ออกจาก subject name ที่ไม่ได้แยก column ออกมาตั้งแต่ตอนแรก ในงานแรกที่ทำ ทำให้ต้องใช้ split ในการช่วยแยกข้อมูลออกมา

decision

งาน Tutorial ให้แต่ละคนแยกกันทำ เนื่องจากไม่มีความจำเป็นต้องทำร่วมกันก็ได้

ส่วน เรื่องการนำข้อมูลเข้า Database นั้น จะให้เริ่มจาก แยกกันไปศึกษาและทำกันเอง โดยให้เขียนลง blog และ อัพโค้ดลง github แต่ว่า ได้คุยกันเอาไว้ว่าจะให้ทำขั้นตอนดังนี้

ดึงข้อมูล CSV จาก google spreedsheet และดูว่า รูปแบบตรงกับที่ต้องการมั้ย หรือนำเข้า ฐานข้อมูลได้เลยหรือไม่

ถ้า ไม่ จะไปยังรูป ไฟล์ที่เป็นตัว X ในภาพ และผ่าน ฟังก์ชั่นทำให้เป็นรูปแบบที่กำหนดไว้ ดังไฟล์รูป ถูกต้อง หรือหาก ดึงจาก google spreedsheet และ รูปแบบตรงก็มาขั้นตอนนี้ได้เลย จากนั้น นำเข้าฟังก์ชั่น ที่จะนำข้อมูลนั้นเข้าฐานข้อมูลได้

การทำ TABLE 2 Table ดังนี้

	"CREATE TABLE Student ("
	" `Student_id` varchar(13) NOT NULL, "
	" `First_name` varchar(100) NOT NULL,"
	" `Last_name` varchar(100) NOT NULL,"
	" PRIMARY KEY (`Student_id`),"
	" UNIQUE (Student_id))")
	"CREATE TABLE Student_Records ("
	" PK int(11) NOT NULL AUTO_INCREMENT,"
	" SubjectID varchar(10) NOT NULL,"
	" SubjectName varchar(100) NOT NULL,"
	" Weight int(1) NOT NULL,"
	" Section int(3) NOT NULL,"
	" Grade varchar(2) NOT NULL,"
	" Term int(2) NOT NULL,"
	" Student_id varchar(13) NOT NULL,"
	" PRIMARY KEY (`PK`),"
	" FOREIGN KEY (Student_id) REFERENCES Student(Student_id))")

โดยจะมี TABLE Student และ Student record ที่จะมี Foreign key ชี้ไปยัง ข้อมูลในตาราง Student นั้นเอง

แยกฟังก์ชั่นสร้าง Database , Table และ เพิ่มข้อมูลแยกกันเอาไว้ เพราะไม่มีความจำเป็นต้องสร้างใหม่ตลอดเวลา (และ mysql ก็ไม่ยอมอยู่แล้วด้วย)

ตัวอย่างข้อมูลที่ ไม่ตรงตามรูปแบบที่ต้องการ

และ รูปแบบที่ต้องการ

related info/link/reference

https://www.python.org/downloads/release/python-340/  python 3.4

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

https://dev.mysql.com/doc/mysql-getting-started/en/

https://dev.mysql.com/downloads/connector/python/ 

sql connector module for python 3.4 and 2.7

https://dev.mysql.com/doc/connector-python/en/ how to use sql connector module

Database System - work 3 learn SQL

ศึกษา เกี่ยวกับ SQL จาก

https://www.w3schools.com/sql/default.asp

โดย ได้เรียนรู้เพิ่มเติมดังนี้

คำสั่ง

SELECT * FROM  TABLENAME;

* แทน Column ทั้งหมด หรือสามารถระบุเป็นชื่อ Column ก็ได้

ส่วน TABLE NAME เป็นชื่อ table ใน ฐานข้อมูลนั้น

ผลลัพธ์จะ หมายถึง ข้อมูลใน column ทั้งหมดใน ตารางนั้นๆ

..............................................................................

จาก SELECT หากต้องการเลือกหลายๆ Column นั้นทำได้โดยการใช้ '  ,  ' คั่นไว้เช่น
SELECT A, B , C FROM TABLE NAME

SELECT DISTINCT A FROM TABLENAME

DISTINCT จะทำให้เราเลือกเฉพาะ ข้อมูลที่ไม่ซ้ำกันมาเท่านั้น 

SELECT COUNT(DISTINCT Country) FROM Customers;

COUNT( X  ) จะนับข้อมูลที่มีอยู่ใน ( ) แล้วคืนค่าเป็นตัวเลขกลับมา แต่ในตัวอย่างนี้จะได้อยู่ใน Column count(distinct country), row 1

แต่มีวิธีที่ทำให้สามารถ กำหนดชื่อ column ได้ เช่น

SELECT Count(*) AS DistinctCountries
FROM (SELECT DISTINCT Country FROM Customers);

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

                           ..............................................................................

SELECT * FROM Customers

WHERE CustomerID=1;

การใช้ WHERE ในการเลือกข้อมูลที่ตรงตามเงื่อนไข โดยในที่นี้กำหนดให้ CustomerID ต้องมีเลขเท่ากับ 1 

และสามารถมีหลายเงื่อนไขได้เช่น

SELECT * FROM Customers

WHERE NOT Country='Germany' AND NOT Country='USA';

                           ..............................................................................

SELECT * FROM Customers

ORDER BY Country ASC, CustomerName DESC;

สามารถกำหนดได้ว่าจะให้ เรียงตามลำดับของ column ไหน รูปแบบไหน ASC จะเรียงตามปกติ น้อยไปมาก ส่วน DESC จะกลับกันคือมากไปน้อย และ ถ้ามีการเรียงหลาย Column จะเรียงจากซ้ายไปขวาก่อน คือเรียง Country ก่อนและจึงมาเรียงด้วย CustomerName อีกที (ภายใน Country เดียวกัน) 

                           ..............................................................................

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)

VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');

ตัวอย่างการเพิ่มข้อมูลลงใน Table customers โดยจะใช้คำสั่ง INSERT INTO + values โดย ในวงเล็บหลังชื่อ table จะเป็นลำดับของ column และหลัง value จะเป็น ค่าใน column ที่ตรงกัน

                           ..............................................................................

UPDATE Customers
SET ContactName='Juan';

จะทำการอัพเดทค่าใน table customers column contactname ทั้งหมดเป็น Juan

UPDATE Customers
SET ContactName='Juan'
WHERE Country='Mexico';

สามารถกำหนดเฉพาะที่ได้โดยใช้ WHERE

                           ..............................................................................


DELETE FROM Customers
WHERE CustomerName='Alfreds Futterkiste';
การลบ ข้อมูล จากตาราง 

หากไม่มี WHERE จะลบทั้งหมด

                           ..............................................................................

SELECT TOP 3 * FROM Customers;
เลือก สูงสุด 3 แถว

SELECT * FROM Customers LIMIT 3;

จำนวนข้อมูลสูงสุด 3

SELECT TOP 50 PERCENT * FROM Customers;

สูงสุด 50% แรก (ข้อมูล ครึ่งนึงด้านบน)

                           ..............................................................................

SELECT MIN(Price) AS SmallestPrice

FROM Products; 

SELECT MAX(Price) AS LargestPrice

FROM Products; 

หาค่าสูงสุดและต่ำสุด

                           ..............................................................................

SELECT COUNT(ProductID)

FROM Products;

SELECT AVG(Price)

FROM Products;

SELECT SUM(Quantity)

FROM OrderDetails;

หาค่าจำนวน ค่าเฉลี่ย และค่าผลรวม 

     

                           ..............................................................................

SELECT * FROM Customers

WHERE CustomerName LIKE 'a_%_%';

เครื่องหมาย _ หมายถึงตัวอักษรใดๆ % ถ้า a% หมายถึง นำหน้าด้วย a ถ้า %a หมายถึงลงท้ายด้วย และ %a% มี a ในตำแหน่งใดๆ จากตัวอย่างข้างบนหมายถึง นำหน้าด้วย a และมีตัวอักษรอย่างน้อย 3 ตัว

% wildcard

SELECT * FROM Customers

WHERE City LIKE '[bsp]%';

SELECT * FROM Customers

WHERE City LIKE '[!a-c]%';

จากรูปแบบนี้ อันแรกจะอะไรก็ได้ที่ขึ้นต้นด้วย bsp ส่วนอันแรก อะไรก็ได้ที่ไม่ได้ขึ้นต้นด้วย a b c

                           ..............................................................................

SELECT * FROM Customers

WHERE Country IN ('Germany', 'France', 'UK');

SELECT * FROM Customers

WHERE Country IN (SELECT Country FROM Suppliers);

เลือก จาก ในเมืองที่กำหนดไว้เท่านั้น (โดยใช้ IN ในเงื่อนไข)

SELECT * FROM Orders

WHERE OrderDate BETWEEN #07/04/1996# AND #07/09/1996#;

SELECT * FROM Products

WHERE (Price BETWEEN 10 AND 20)

AND NOT CategoryID IN (1,2,3);

การใช้เงื่อนไข BETWEEN เพื่อเลือกช่วง

                           ..............................................................................

SELECT o.OrderID, o.OrderDate, c.CustomerName

FROM Customers AS c, Orders AS o

WHERE c.CustomerName="Around the Horn" AND c.CustomerID=o.CustomerID;

การใช้ชื่อชัวคราว

                           ..............................................................................

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate

FROM Orders

INNER JOIN Customers

ON Orders.CustomerID=Customers.CustomerID;

เช่นการหา รายการสั่ง ของลูกค้าที่มี id ตรงกันโดย order จะมี pk ref to customerID ของอีกตารางหนึ่ง

SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName

FROM ((Orders

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

INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);

for 3 table join 

                           ..............................................................................

SELECT City, Country FROM Customers

WHERE Country='Germany'

UNION 

SELECT City, Country FROM Suppliers

WHERE Country='Germany'

ORDER BY City;

SELECT City, Country FROM Customers

WHERE Country='Germany'

UNION ALL

SELECT City, Country FROM Suppliers

WHERE Country='Germany'

ORDER BY City;

UNION จะทำการรวมข้อมูล จาก 2 SELECT เข้าด้วยกัน ถ้าไม่มี ALL จะรวมแบบ นำข้อมูลที่ไม่ซ้ำกันเท่านั้นแต่ถ้า ALL จะเพิ่มส่วนที่ซ้ำกันเข้าไปด้วย

                           ..............................................................................

SELECT COUNT(CustomerID), Country

FROM Customers

GROUP BY Country;

ผลลัพธ์ จะเป็นการ นับจำนวน ว่ามีลูกค้าในเมืองใดๆกี่คน         

SELECT COUNT(CustomerID), Country

FROM Customers

GROUP BY Country

HAVING COUNT(CustomerID) > 5

ORDER BY COUNT(CustomerID) DESC;

ทำหน้าที่คล้ายกับ WHERE แต่ว่าสามารถใช้กับ function ได้

                           ..............................................................................

SELECT SupplierName

FROM Suppliers

WHERE EXISTS (SELECT ProductName FROM Products WHERE SupplierId = Suppliers.supplierId AND Price < 20);

EXISTS return ture ถ้า ในเงื่อนไข มีค่าที่ตรงกับที่กำหนดไว้ ใช้ในการทดสอบได้ 

                           ..............................................................................

SELECT ProductName

FROM Products

WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity > 99);

ANY return ture ถ้ามีเงื่อนไขตรงเพียงชุดเดียวก็ได้

SELECT ProductName
FROM Products
WHERE ProductID = ALL (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);

ALL return ture ถ้าทั้งหมดตรงกับเงื่อนไข

                           ..............................................................................
SELECT column1, column2, column3, ...
INTO newtable [IN externaldb]
FROM oldtableWHERE condition;

copy จาก table หนึ่งไปอีก table หนึ่ง

INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers
WHERE Country='Germany';

การเพิ่มข้อมูล จากต่างตาราง และมีเงื่อนไข

                           ..............................................................................

SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0))
FROM Products

ฟังก์ชั่นเช็คถ้า null return 0

SELECT ProductName, UnitPrice * (UnitsInStock + COALESCE(UnitsOnOrder, 0))
FROM Products

ฟังก์ชั่นเช็คถ้า null return 0

SELECT ProductName, UnitPrice * (UnitsInStock + ISNULL(UnitsOnOrder, 0))
FROM Products


การใช้งาน comment

/*Select all the columns
of all the records
in the Customers table:*/

SELECT * FROM Customers WHERE (CustomerName LIKE 'L%'
OR CustomerName LIKE 'R%' /*OR CustomerName LIKE 'S%'
OR CustomerName LIKE 'T%'*/ OR CustomerName LIKE 'W%')
AND Country='USA'
ORDER BY CustomerName;

SELECT * FROM Customers -- WHERE City='Berlin';


ใช้เครื่องหมาย /* ......... */  สำหรับหลายบรรทัด และ -- สำหรับบรรทัดเดียว
                           ..............................................................................

เกี่ยวกับ SQL

SQL is an ANSI (American National Standards Institute) standard

you will need: (for web site)

  An RDBMS database program (i.e. MS Access, SQL Server, MySQL)

  To use a server-side scripting language, like PHP or ASP

  To use SQL to get the data you want

  To use HTML / CSS to style the page

SQL keywords are NOT case sensitive ( select = SELECT )

NULL = EMPTY OR NOT VALUES

Database System - work 2 MySQL การลง และการใช้งานเบื้องต้น

โหลด MySQL > > https://dev.mysql.com/downloads/file/?id=474802

จากนั้น กดท่ีตัวลงที่ทำการดาวน์โหลดมา และกดตามภาพไปเรื่อยๆ ( Next > > > )

ขั้นตอนนี้สามารถเลือกได้ว่าจะลง แบบไหน

รอการดาวน์โหลดและลงให้เสร็จสิ้น

 หลังจากกด Next มาเรื่อยๆเราจะได้ทำการตั้งค่า MySQL Server ตามขั้นตอน

 ตั้งค่า พอร์ท และรูปแบบ เป็น Developement Machine

 การกำหนดรหัส รูท และ เพิ่ม User ตามความต้องการ

กำหนดชื่อเซิฟเวอร์

รอติดตั้ง

การตั้งค่า MySQL Router

 พิมรหัส และกด เช็ค และกด Next เพื่อลงตัวอย่างการใช้งาน

รอการติดตั้งจนเสร็จ

จากนั้นก็การติดตั้งก็เป็นอันเสร็จเรียบร้อยแล้ว

ซึ่ง สามารถใช้งานได้ทั้งแบบ Shell และ แบบที่มี GUI

และ commandline client

วิธีการใช้งาน อ้างอิงจาก https://dev.mysql.com/doc/mysql-getting-started/en/

- \h คือตัวแสดงรายละเอียดคำสั่งต่างๆ

คำสั่ง SHOW DATABASES; จะแสดง ฐานข้อมูลออกมา

สามารถเพิ่ม Database โดยการพิม CREATE DATABASE name; 

ส่วนการเปลี่ยนเปลี่ยนแปลงสร้างตารางใน ดาต้าเบสนั้นๆเริ่มแรกต้องเลือก data base ที่เราสนใจจะไปเพิ่มก่อน โดยใช้ 

USE dbmame

จากนั้นลองเพิ่มตาราง ดังนี้

CREATE TABLE cats ( id INT unsigned NOT NULL AUTO_INCREMENT, # Unique ID for the record name VARCHAR(150) NOT NULL, # Name of the cat owner VARCHAR(150) NOT NULL, # Owner of the cat birth DATE NOT NULL, # Birthday of the cat PRIMARY KEY (id) # Make the id the primary key );

เป็นตาราง ที่มี column ที่เก็บค่าต่างๆ id ที่เก็บเป็น unsigned int ชื่อที่เก็บได้ไม่เกิน 150 ตัวอักษร เจ้าของที่เป็นชื่อเช่นกัน วันเกิด และ Primary key โดยข้อมูลทุกตัว จะต้องไม่เป็น null และ ในช่อง id จะ เพิ่มเลขแบบอัตโนมัต

จากนั้นจะลองดูรายละเอียดว่ามีตารางใน database นี้ ก็พิม

show tables;

จะได้ผลลัพธ์ดังนี้

แต่หากว่าต้องการดูรายละเอียดที่มากขึ้นให้พิมดังนี้

DESCRIBE name_of_table

การจะเพิ่มข้อมูลลงในตารางให้พิมดังนี้

INSERT INTO cats ( name, owner, birth) VALUES ( 'Sandy', 'Lennon', '2015-01-03' ), ( 'Cookie', 'Casey', '2013-11-13' ), ( 'Charlie', 'River', '2016-05-21' );

โดยจะใช้คำว่า INSERT INTO name (ar1, . . . . , arN) VALUES  ไล่ไปเรื่อยๆ ดังตัวอย่าง () = 1 row

หากต้องการจะดูข้อมูลในตารางจะใช้คำสั่ง

SELECT * FROM cats;

SELECT * FROM  name_of_table;

และยังเจาะจง เลือกได้เฉพาะสิ่งที่ต้องการได้ ยกตัวอย่างเช่น

SELECT name FROM cats WHERE owner = 'Casey';

เลือก ชื่อ จากตาราง cats ที่เจ้าข้อง(column) = 'Casey'  ผลก็จะแสดงแค่ column name ของแมวที่มีเจ้าของตรงกันเท่านั้น

 การลบข้อมูลทำได้โดย

DELETE FROM cats WHERE name='Cookie';

ใช้คำสั่ง DELETE 

เพิ่ม Column ได้โดยใช้คำสั่ง

ALTER TABLE cats ADD gender CHAR(1) AFTER name;

ALTER TABLE .... ADD ..... โดย gender char(1) จะเป็น column ใหม่ที่เกิดหลัง column name

SHOW CREATE TABLE cats\G

คำสั่งนี้จะแสดงรายละเอียดที่มากกว่าเดิมของตารางนั้นๆ 

ส่วนการลบ column จะใช้คำสั่ง

ALTER TABLE cats DROP gender;

DROP

โดยพื้นฐานเบื้องต้นก็จะมีเพียงเท่านี้ ต้องไปศึกษาคำสั่ง อื่นๆเพิ่มเติม