โปรแกรม Visual FoxPro ได้มีการพัฒนามาอย่างต่อเนื่อง ซึ่งไม่ว่าจะเป็นรุ่นใดก็ตามจะบรรจุชุดคำสั่ง Structure Query Language (SQL) ไว้ด้วยเสมอ อันที่จริงแล้วคำสั่ง SQL เริ่มมีมาตั้งแต่ FoxPro ซึ่งเป็นรุ่นบน ดอส(DOS) ซึ่งก็ขอยกประโยชน์ให้กับ IBM ที่ได้คิดค้นภาษานี้ขึ้นมาเมื่อช่วงกลากทศวรรษที่ 1970 ที่ห้องวิจัยของไอบีเอ็ทเมือ San Jose รัฐ แคลิฟอร์เนีย ตอนนั้นใช้คำว่า SEQUEL ต่อมาในปี 1980 ได้เปลี่ยนมาเป็นคำว่า SQL และก็ใช้กันมาจนถึงปัจจุบันนี้ คำสั่ง SQL เป็นคำสั่งที่ง่ายต่อการทำความเข้าใจ เพราะเป็นภาษาเขียนที่คล้ายๆกับภาษาอังกฤษทั่วๆไป ช่วยลดการเขียนโปรแกรมจากเดิมลงไปได้อย่างมากซึ่งถ้าเราเขียนคำสั่งด้วย Visual FoxPro ก็ต้องเขียนกันหลายบรรทัดหลายคำสั่งซึ่งอาจจะเกิดข้อผิดพลาดขึ้นได้ง่าย ดังตัวอย่างต่อไปนี้
ใช้คำสั่ง SQL
SELECT * FROM CUSTOMER WHERE SALES_ID = ‘10001’
ใช้คำสั่งที่ไม่ใช่ SQL
USE CUSTOMER
SET FILTER TO SALES_ID = ‘10001’
BROWSE
จะเห็นได้ว่าการใช้คำสั่ง SQL จะเป็นการช่วยให้เกิดความสะดวกสบายในการเขียนคำสั่งมากยิ่งขึ้น แต่มิได้หมายความว่าจะให้ท่านเลิกใช้คำสั่งปรกตินะครับ
ใน Visual FoxPro ได้ทำการบรรจุคำสั่ง SQL ไว้ดังนี้
SELECT – SQL เป็นคำสั่งที่ใช้ในการสอบถามข้อมูล (query)
ALTER TABLE – SQL เป็นคำสั่งที่ใช้ในการแก้ไขโครงสร้างของแฟ้มข้อมูล(table)
CREATE CURSOR – SQL เป็นคำสั่งที่ใช้ในการสร้างแฟ้มข้อมูลชั่วคราว(temporary table) ตามที่เรากำหนด
CREATE TABLE – SQL เป็นคำสั่งที่ใช้ในการสร้างแฟ้มข้อมูล(create table)
DELETE – SQL เป็นคำสั่งที่ใช้ลบข้อมูลตามเงื่อนไข
INSERT – SQL เป็นคำสั่งที่ใช้ในการเพิ่มข้อมูลเข้าสู่ตาราง (append a new record)
UPDATE – SQL เป็นคำสั่งที่ใช้ในการแก้ไขข้อมูลในตาราง ตามเงื่อนไข
จะสังเกตุว่าคำสั่งต่างๆดังที่กล่าวมานี้เราก็ได้เคยใช้กันมาบ้างแล้ว ต่อไปนี้ผมจะอธิบายคำสั่งต่างๆและเทคนิควิธีการใช้งาน ซึ่งหลังจากที่ท่านได้ทำความเข้าใจกับคำสั่งเหล่านี้แล้วเชื่อได้เลยว่าท่านจะเป็นนักจัดการฐานข้อมูลตัวยงอีกท่านหนึ่งเลยทีเดียว
คำสั่ง SELECT – SQL
คำสั่ง SELECT – SQL เป็นคำสั่งที่ใช้ในการสอบถามข้อมูล ซึ่งสามารถดึงข้อมูลจากตาราง หรือหลายตาราง ตามเงื่อนไขที่กำหนด แล้วให้แสดงผล ทาง จอภาพ เครื่องพิมพ์ อะเรย์ หรือให้ออกมาในรูปของ ตารางข้อมูล ก็ได้ ซึ่งมีรูปแบบคำสั่งดังนี้
SELECT [ALL | DISTINCT] [TOP nExpr [PERCENT]]
[Alias.] Select_Item [AS Column_Name]
[, [Alias.] Select_Item [AS Column_Name] …]
FROM [FORCE]
[DatabaseName!]Table [Local_Alias]
[[INNER | LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER] JOIN
DatabaseName!]Table [Local_Alias]
[ON JoinCondition …]
[[INTO Destination]
| [TO FILE FileName [ADDITIVE] | TO PRINTER [PROMPT]
| TO SCREEN]]
[PREFERENCE PreferenceName]
[NOCONSOLE]
[PLAIN]
[NOWAIT]
[WHERE JoinCondition [AND JoinCondition …]
[AND | OR FilterCondition [AND | OR FilterCondition …]]]
[GROUP BY GroupColumn [, GroupColumn …]]
[HAVING FilterCondition]
[UNION [ALL] SELECTCommand]
[ORDER BY Order_Item [ASC | DESC] [, Order_Item [ASC | DESC] …]]
เมื่อเห็นคำสั่งแล้วรูสึกว่าทำไม่มันช่างยาวอะไรอย่างนี้ แต่ Visual FoxPro ได้มีคำสั่งที่ช่วยในการเขียนคำสั่ง SELECT – SQL ไว้ให้ซึ่งก็คือคำสั่ง
CREATE QUERY [FileName | ?] [NOWAIT]
ซึ่งเป็นเครื่องมือตัวหนึ่งที่มาช่วยให้เราในการเขียนคำสั่งทำให้ผู้ใช้สะดวกสบายยิ่งขึ้น แต่ถึงอย่างไรคุณก็ต้องเรียนรู้คำสั่งอยู่ดี เพราะความสามารถบางอย่างนั้น Query ไม่สามารถทำได้ ทีนี้เรามาดูวิธีใช้งานกันก่อน
ให้ทำการสร้าง Query โดยป้อนคำสั่งดังนี้
CLOSE ALL
OPEN DATABASE MYDATABASE
CREATE QUERY MYQUERY
จากนั้นคุณก็จะเข้าสู่ Query Designer โปรแกรมก็จะแสดงชื่อตารางที่เก็บอยู่ใน MYDATABASE ขึ้นมาให้เราเลือก ตอนี้ให้คุณทำการเลือกตาราง customer โดยการ คลิ๊กเมาส์ 2 ครั้งที่ตาราง customer แล้วทำการคลิ๊กที่ปุ่ม Close คุณก็จะได้หน้าตาตามรูปด้านล่าง
ใน Query Designer จะประกอบไปด้วยแท๊ปทั้งหมด 6 แท๊ปประกอบด้วย
แท๊ป Field
ส่วนนี้ใช้กำหนดฟิลด์จากตาราง หรือ วิว (View) เพื่อใช้แสดงผลลัพธ์ คุณสามารถกำหนดฟังก์ชัน(Function) นิพจน์(Expression) เพือใช้ในการแสดงผลได้
Available Field เป็นส่วนที่ใช้แสดงชื่อฟิลด์ทั้งหมดของตารางหรือวิว ที่เราทำการเลือกมาใช้งาน
Function and Expression เป็นส่วนที่ใช้ในการกำหนดฟังก์ชัน หรือนิพจน์ให้กับฟิลด์ที่ต้องการ เช่น เราต้องการนับจำนวนลูกค้า ก็ใส่ว่า COUNT(CUSTOMER.CUST_ID) เสร็จแล้วคลิ๊กที่ปุ่ม Add
แท๊ป Join
ส่วนนี้ใช้ในเชื่อมตารางหรือวิวตั้งแต่ 2 ตารางหรือวิว เข้าด้วยกันตามเงื่อนไขที่กำหนด จะประกอบด้วยส่วนต่างๆดังนี้
Type กำหนดประเภทของการเชื่อมข้อมูล มีอยู่ 4 ชนิด คือ
Inner จะเลือกข้อมูลเฉพาะที่มีเงื่อนไขตรงกันเท่านั้น
Left Outer จะถึอเอาตรางด้านขวาเป็นหลัก(Field Name)โดยจะเอาข้อมูลมาทั้งหมด ส่วนฝั่งซ้าย(Value)จะนำมาเฉพาะที่มีเงื่อนไขตรงกันเท่านั้น
Right Outer จะถึอเอาตรางด้านซ้ายเป็นหลัก(Value)โดยจะเอาข้อมูลมาทั้งหมด ส่วนฝั่งซ้าย(Field Name)จะนำมาเฉพาะที่มีเงื่อนไขตรงกันเท่านั้น
Full จะนำข้อมูลทั้งหมดของสองตารางมาแสดง
Field Name กำหนดฟิลด์ที่จะเป็นเงื่อนไขในการเชื่อม
Criteria กำหนดเงื่อนไขในการเชื่อม
Value กำหนดฟิลด์จากตารางอื่นที่จะมาทำการเชื่อม
แท๊ป Filter
ส่วนนี้เป็นส่วนที่ใช้ในการกำหนดเงื่อนไขในการเลือกเรคคอร์ดจากตารางหรือวิว ซึ่งในส่วนนี้จะตรงกับ วลี WHERE ของคำสั่ง SELECT – SQL
แท๊ป GroupBy
เป็นส่วนที่ใช้รวมกลุ่มข้อมูลตามฟิลด์ที่กำหนด ในส่วนนี้จะตรงกับ วลี GROUP BY ของคำสั่ง SELECT – SQL ปรกติจะใช้ในการคำนวณผลสรุป ต่างๆ เช่น SUM , COUNT เป็นต้น ในส่วนของปุ่ม Having จะเป็นการกำหนดเงื่อนไขในการเลือกข้อมูล ลักษณะจะเหมือนกับ แท๊ป Filter แต่จะทำกับผลลัพธ์ของกลุ่มข้อมูล
แท๊ป Miscellaneous
ใช้กำหนดเงื่อนไขในการเลือกข้อมูล
No Duplicates กำหนดให้เลือกข้อมูลที่ซ้ำกันมาเพียงเรคคอร์เดียว
Cross-Tabulate กำหนดให้สร้างตารางแบบ คลอสแท๊ป(ไขว้ตาราง) ไว้จะกล่าวถึงอีกที
ที่กล่าวมาข้างต้นเพื่อแนะนำให้ท่านรู้จักกับ Query Desinger เท่านั้นเพราะ Query Designer จะเป็นเครื่องมือที่ช่วยในการเขียนคำสั่ง SELECT – SQL ให้แก่เรา และต่อไปนี้ผมจะยกตัวอย่างการเขียนประโยคคำสั่ง SELECT – SQL ในแบบต่างๆ เพื่อให้ท่านสามารถนำไปประยุกต์ใช้งานต่อไป
การเขียนประโยคคำสั่ง SELECT – SQL
คำสั่ง SELECT – SQL เป็นคำสั่งในการสอบถามข้อมูล ดังที่กล่าวไว้ข้างต้น คำสั่งนี้คำสั่งเดียวสามารถอธีบายวิธีใช้งานได้เยอะมาก มีวิธีใช้ที่ พิศดาลพันลึกยิ่งกว่าตำนานสามก๊กอีก ทีนี้เรามาดูกันที่ละฉากๆก็แล้วกัน
ตัวอย่างแรก จะเป็นการทดสอบเงื่อนไข จากการรับข้อมูลใน TextBox ของฟอร์ม ถ้ามีการป้อนข้อมูลจะมีการตรวจสอบเงื่อนไขเพื่อเลือกข้อมูลมาแสดง แต่ถ้าไม่ได้ป้อนข้อมูล(Empty) จะนำข้อมูลมาแสดงทั้งหมด
ให้ทำการสร้างฟอร์ม ดังนี้
CREATE FORM MYSQL1
ที่ Data Environment ให้กำหนดแฟ้มชื่อ CUSTOMER
ที่ ฟอร์ม ให้สร้าง TextBox ชื่อ Text1
สร้าง CommandButton ชื่อ Command1 แล้วป้อนคำสั่งที่ Click Event ดังนี้
SELECT * FROM CUSTOMER C ;
WHERE (ALLTRIM(C.CUST_ID) == ALLTRIM(THISFORM.TEXT1.VALUE)) ;
OR (EMPTY(THISFORM.TEXT1.VALUE) = .T.) ;
ORDER BY C.CUST_ID ;
INTO CURSOR MYQUERY
CREATE REPORT MYREPORT FROM MYQUERY
REPORT FORM MYREPORT PREVIEW
DELETE FILE MYREPORT.*
จากนั้นทำการ Save แล้วลองเรียกฟอร์มขึ้นมาดู
เรามาแกะคำสั่งข้างในดูกันก่อน
SELECT * (ดอกจันทน์) หมายถึงว่าให้เลือกฟิลด์ที่แสดงมาทุกฟิลด์ แต่ถ้าต้องการเฉพาะฟิลด์ ก็กำหนดชื่อฟิลด์เข้าไปเช่น C.NAME, C.ADDRESS ถ้ามีหลายฟิลด์เราจะใช้เครื่องหมายคอมม่า(,)เป็นตัวคั่นกล่างระหว่างฟิลด์
CUSTOMER คือชื่อตาราง แฟ้มข้อมูลลูกค้า ส่วนอักษร C เป็นชื่อ Alias ที่เราตั้งขึ้นเพื่อใช้แทนชื่อตาราง CUSTOMER ใช้เพื่อความสะดวกในการอ้างถึงชื่อตาราง
WHERE ประโยคคำสั่งหลัง WHERE จะเป็นนิพจน์ที่ใช้ในการเลือกข้อมูลมาแสดงตามเงื่อนไขที่กำหนด จากตัวอย่างข้างต้นจะเป็นการเลือก รหัสลูกค้า ส่วนเครื่องหมาย == จะเป็นโอเปเรเตอร์(operator) ที่ใช้ในการเปรียบเทียบซึ่งหมายถึงต้องเท่ากันทั้งหมด (Exactly Like) ซึ่งจะต่างกับเครื่องหมาย = (Equal)
ส่วนนิพจน์ที่อยู่หลัง OR คือ EMPTY(THISFORM.TEXT1.VALUE) = .T. อันนี้เป็นเทคนิคในการเลือกข้อมูลอย่างหนึ่งดังที่กล่าวไว้ข้างต้นว่า ถ้าไม่มีการป้อนข้อมูลจะทำการเลือข้อมูลมาทั้งหมด ลองพินิจพิเคราะห์แล้วคุณก็จะวงศ์กระจ่าง (ศรัญญู)
ORDER BY C.CUST_ID ให้เรียงลำดังตามรหัสลูกค้า
INTO เป็นการกำหนดว่าให้ผลลัพธ์ที่ได้ไปไว้ที่ไหน ในที่นี้ให้เก็บไว้ที่ตารางชั่วคราวซึ่งเราใช้วลีว่า CURSOR โดยตั้งชื่อตารางว่า MYQUERY
| เคล็ดไม่ลับ _TALLY เป็นตัวแปรหน่วยความจำ(System memory variable) ที่บอกให้คุณทราบถึงจำนวนผลลัพธ์ของ query เช่น SELECT * FROM CUSTOMER INTO CURSOR MYCURSOR ? _TALLY * หมายเหตุ สำหรับผมจะใช้ตรวจสอบว่าการ query นั้นมีข้อมูลหรือไม่ถ้าไม่มีจะมีค่าเท่ากับ 0 |
ตัวอย่างการใช้เงื่อนไขในการกรองข้อมูล(filter condition)
ในการสอบถามข้อมูลเราสามารถใช้ wild card _ , % เพื่อช่วยในการกรองข้อมูลได้ เช่น
cText = “a”
cFilter = “%” + ALLTRIM(cTEXT) + “%”
SELECT * FROM CUSTOMER C WHERE C.NAME LIKE cFilter
จากตัวอย่างข้างบนจะเป็นการสอบถามชื่อบริษัทที่มีอักษร a อยู่ ณ. ตำแหน่งใดๆก็ได้
cText = “a”
cFilter = “___” + ALLTRIM(cTEXT) + “%”
SELECT * FROM CUSTOMER C WHERE C.NAME LIKE cFilter
จากตัวอย่างข้างบนจะเป็นการสอบถามชื่อบริษัทที่มีอักษร a อยู่ ณ.ที่สี่ ตำแหน่งใดๆก็ได้
จะสังเกตได้ว่า % จะเหมือนกับ * ส่วน _ จะเหมือนกับ ? ใน DOS
ตัวอย่างการ เชื่อมตาราง(join table) จากบทที่แล้วเราได้ทำการสร้างฟอร์มป้อนข้อมูลใบส่งของ(invoice) เราก็ได้ทราบถึงการสร้างความสัมพันธ์ระหว่างตารางหลายๆตาราง เมื่อทำฟอร์มแล้วก็จะต้องมีการสร้างรายงานใบส่งของ แต่ก่อนจะเป็นรายงานได้นั้นต้องผ่านขั้นตอนหนึ่งก็คือการเลือกข้อมูลขึ้นมา ซึ่งเราจะใช้คำสั่ง SELECT-SQL ทำการเลือก ซึ่งจะเป็นการสร้าง SQL แบบ เชื่อมตารางเพื่อให้ได้ข้อมูลตามที่ต้องการ ให้คุณลองป้อนคำสั่งดังต่อไปนี้ที่ COMMAND WINDOW แล้วลองดูผลลัพธ์ที่ได้
STOR 1 TO MYINVOICE && กำหนดให้เลือกใบส่งสินค้าหมายเลข 1
SELECT H.*, C.name AS Customer , D.prod_id ,P.name AS Product , ;
D.quantity , D.unitprice , D.quantity * D.unitprice AS Amount ;
FROM mydatabase!inv_head H ;
LEFT OUTER JOIN mydatabase!customer C ;
ON H.cust_id = C.cust_id;
LEFT OUTER JOIN mydatabase!inv_dtl D ;
ON H.Inv_no = D.Inv_no ;
LEFT OUTER JOIN mydatabase!product P ;
ON D.prod_id = P.prod_id ;
WHERE H.inv_no = MYINVOICE ;
ORDER BY H.inv_no
จากตัวอย่างข้างต้นจะแสดงรายการของใบส่งสินค้าเลขที่ 1 ขึ้นมา เรามาดูวิธีการเชื่อมตารางว่าทำกันอย่างไร
ที่คำสั่ง SELECT H.*, C.name AS Customer , D.prod_id ,P.name AS Product , D.quantity , D.unitprice , D.quantity * D.unitprice AS Amount จะเป็นการเลือกฟิลด์ที่ต้องการ ให้คุณสังเกตุที่วลี AS จะใช้สำหรับตั้งชื่อให้กับชื่อฟิลด์จะแสดงออกมา เช่น ฟิลด์ C.name ให้แสดงเป็น Customer เป็นต้น
ในการเชื่อมตารางหลายๆตารางเราต้องคำนึงถึงว่าจะให้ตารางอะไรเป็นหลัก ในตัวอย่างนี้จะให้ตาราง INV_HEAD เป็นหลัก เพราะสิ่งที่เราสนใจก็คือใบส่งของ เราจะใส่ไว้หลังวลี FORM
ส่วนตารางที่เราจะนำมาเชื่อมนั้นก็ต้องดูด้วยว่าจะนำมาเชื่อมในลักษณะใด มีอยู่ 4 แบบด้วยกันคือ
INNER เป็นการเชื่อมตารางโดยจะนำข้อมูลของตารางที่เชื่อมมาแสดงเฉพาะที่มีเงื่อนไขตรงกันเท่านั้น
LEFT OUTER เป็นการเชื่อมตารางโดยจะนำข้อมูลของตารางด้านซ้ายมาเป็นหลักในการแสดง ส่วนด้านขวาจะมีข้อมูลตรงกันหรือไม่ก็ได้
RIGHT OUTER เป็นการเชื่อมตารางโดยจะนำข้อมูลของตารางด้านขวามาเป็นหลักในการแสดง ส่วนด้านซ้ายจะมีข้อมูลตรงกันหรือไม่ก็ได้
FULL OUTER เป็นการเชื่อมโดยนำของทั้งสองตารางมาเป็นหลักในการแสดง
สำหรับในตัวอย่างจะเป็นการเชื่อมในแบบ LEFT OUTER เนื่องจากว่าบางครั้งข้อมูลในตารางด้านขวาที่เชื่อมอยู่อาจถูกลบออกไป ถ้าเราใช้ INNER หรือ RIGHT OUTER ข้อมูลอาจจะได้มาไม่หมด หรือไม่ถูกต้อง ในส่วนนี้ต้องดูความเหมาะสมของแต่ละกรณีว่าจะใช้แบบไหน
แล้วจะรู้ได้อย่างไรว่าอันไหนเป็น LEFT หรือ RIGHT ? ให้คุณดูที่วลี ON เช่น ON H.cust_id = C.cust_id หมายถึง INV_HEAD เป็น LEFT ส่วน CUSTOMER เป็น RIGHT
เราจะนำตารางใดมาเชื่อมเราก็ใส่วิธีการเชื่อมว่า เป็น INNER, LEFT, RIGHT, FULL แล้วก็ใส่ชื่อตารางหลังวลี JOIN แล้วก็ตามด้วยเงื่อนไขในการเชื่อมหลังวลี ON
| ข้อควรระวัง ห้ามใช้คำสั่งแบบนี้ select * from INV_HEAD,CUSTOMER โดยไม่มีเงื่อนไขในการ join ถ้าทำคุณได้ได้ข้อมูลเท่ากับข้อมูลของตารางสองตารางมาคูณกัน เช่น ตาราง INV_HEAD = 20 เรคคอร์ด ตาราง CUSTOMER = 5 เรคคอร์ด ของมูลที่ได้หลังจากคุณ query แล้วจะเท่ากับ 20 * 5 = 100 เรคคอร์ด |
การใช้ฟังก์ชันของ
ในคำสั่ง SELECT มีฟังก์ชัน SUM,COUNT,AVG,MAX,MIN เราสามารถนำฟังก์ชันเหล่านี้ไปใช้งานได้ทันที โดยมีรูปแบบการใช้งานดังนี้
SELECT ชื่อฟังก์ชัน(ชือฟิลด์หรือชื่อคอลัมน์) FROM ตาราง ……
เช่นเราต้องการรวมยอดขายทั้งหมดเราก็พิมพ์คำสั่งว่า
SELECT SUM(INV_DTL.QUANTITY * INV_DTL.UNITPRICE) FROM INV_DTL
ถ้าเราไม่ได้ทำการแบ่งกลุ่มของข้อมูลคำสั่งข้างต้นจะเป็นการหายอดรวมของทั้งตาราง ดังนั้นถ้าเราต้องการจะหาผลรวมแยกตามเลขที่ใบส่งของ เราเขียนคำสั่งดังนี้
SELECT INV_DTL.INV_NO, SUM(INV_DTL.QUANTITY * INV_DTL.UNITPRICE) ;
FROM INV_DTL GROUP BY INV_DTL.INV_NO ORDER BY INV_DTL.INV_NO
การใช้ GROUP BY, HAVING และ ORDER BY
GROUP BY เป็นการจัดกลุ่มของข้อมูลเพื่อที่จะใช้ฟังก์ชันมากระทำกับกลุ่มของข้อมูลนั้นๆ ดังตัวอย่างข้างต้นซึ่งเป็นการแบ่งกลุ่มของใบส่งของ แล้วใช้ฟังก์ชัน SUM เพื่อหายอดรวมผลลัพธ์ของกลุ่ม เมื่อเราทำการแบ่งกลุ่มแล้วควรจะทำการเรียงลำดับของข้อมูลตามกลุ่มที่เราได้จัดแบ่งด้วยเสมอ ซึ่งเราใช้คำสั่ง ORDER BY ในการใช้คำสั่งเรียงลำดับนี้คุณสามารถจะสั่งให้เรียงจากมากไปหาน้อยโดยใช้ DESC ใ่ส่เพิ่มเข้าไปหลัง ชื่อฟิลด์หรือชื่อคอลัมน์ที่ต้องการเช่น ORDER BY INV_DTL.INV_NO DESC แต่ถ้าไม่ใส่หรือใส่คำว่า ASC จะถือว่าให้เรียงจากน้อยไปมาก
HAVING จะคล้ายๆกับคำสั่ง WHERE คือใช้ในกรณีต้องการเลือกข้อมูลจากกลุ่มตามเงื่อนไขที่กำหนด เช่น ถ้าเราต้องการหาว่ามีใบส่งของใบใหนบ้างที่มีราคารวมมากกว่า 5000 บาทบ้าง เราก็เขียนคำสั่งดังนี้
SELECT INV_DTL.INV_NO, SUM(INV_DTL.QUANTITY * INV_DTL.UNITPRICE) AS TOTAL ;
FROM INV_DTL GROUP BY INV_DTL.INV_NO ORDER BY INV_DTL.INV_NO ;
HAVING TOTAL > 5000
ให้คุณสังเกตว่าผมทำการเปลี่นชื่อคอลัมน์ผลรวมใหม่ จาก SUM(…) เป็น TOTAL คุณสามารถนำชื่อใหม่นี้มาใช้งานได้เลย
การสร้างตารางไข้ว(cross-tab)
ตารางไข้วแต่ไม่เขว คือการกลับข้อมูลจาก แถว ไปเป็นคอลัมน์ เพื่อความสะดวกในการนำเสนอ ส่วนใหญ่จะกระทำกับการหาผลรวมสรุปของข้อมูลต่างๆ
ในการสร้างตารางไข้วนั้น Visual FoxPro ได้เตรียม Cross-Tab Wizard ไว้อำนวยความสะดวกให้เราเรียบร้อยแล้ว คุณสามารถเข้าไปใช้งานได้โดยการเลือกเมนู File คลิ๊กที่ New แล้วเลือก Query ทำการคลิ๊กที่ปุ่ม Wizard แล้วเลือก Cross-Tab Wizard คลิ๊กปุ่ม Ok จากนั้นก็ทำตามที่ Wizard แจ้งมาให้ทำ แต่ผมขอบอกได้เลยถ้าข้อมูลคุณมีการ join กันแล้วละก็คุณก็ต้องทำการสร้าง View(เรื่องของ View จะนำเสนอในตอนต่อไป) ก่อนถึงจะทำได้ แต่ในที่นี้จะนำเสนอแบบลูกทุ่งเอ็ฟเอ็ม 95 เพื่อที่จะให้ท่านรู้ถึงการสร้าง Cross-Tab ซึ่งไม่ได้ยากอะไรเลยถ้ารู้หลักวิธีการที่ผมจะนำเสนอต่อไปนี้
สมมุติว่าผมอยากทราบยอดขายของลูกค้าแต่ละราย โดยแยกตามพนักงานขาย ดังตารางต่อไปนี้
| รหัสลูกค้า | พนักงานขาย 1 | พนักงานขาย 2 | พนักงานขาย 3… (จำนวนคอลัมน์จะเท่ากับจำนวนพนักงานขาย) | รวม |
| 10001 | 180.00 | 180.00 | ||
| 10002 | 25000.00 | 2500.00 | ||
| 10009 | 678.00 | 678.00 |
ก่อนอื่นเราต้องทำการเลือกข้อมูลก่อนโดยใช้ SELECT – SQL ดังตัวอย่าง
SELECT Inv_head.cust_id, Customer.sales_id, Inv_head.inv_no,;
SUM(INV_DTL.QUANTITY * INV_DTL.UNITPRICE);
FROM mydatabase!inv_head ;
LEFT OUTER JOIN mydatabase!inv_dtl;
ON Inv_head.inv_no = Inv_dtl.inv_no;
LEFT OUTER JOIN mydatabase!customer ;
ON Inv_head.cust_id = Customer.cust_id ;
GROUP BY Inv_dtl.inv_no ;
ORDER BY Inv_head.cust_id, Customer.Sales_id, Inv_dtl.Inv_no ;
INTO CURSOR MYCURSOR
เมื่อทำการรันคำสั่งเรียบร้อยแล้วเราจะได้ข้อมูลเก็บอยู่ที่ ตาราง MYCURSOR ให้ลองใช้คำสั่ง BROWSE เพื่อเรียกดูข้อมูล ซึ่งจะมีลักษณะดังรูป แต่ข้อมูลอาจแตกต่างกันบ้างขึ้นอยู่ที่ตอนคุณป้อนข้อมูล
เมื่อเราได้ข้อมูลแล้วสังเกตว่าตารางที่ได้ยังเป็นปรกติ สิ่งที่เราต้องการคือกลับข้อมูลของ Sales_id จากแถวไปเป็น คอลัมน์ ให้ทำการป้อนคำสั่งตามนี้
DO (_GENXTAB) WITH ‘MYTAB’ ,.T. ,.T., .T. ,1 ,2 ,4 , .T.
เมื่อทำการรันคำสั่งเรียบร้อยแล้วเราจะได้ข้อมูลเก็บอยู่ที่ ตาราง MYTAB ให้ลองใช้คำสั่ง BROWSE เพื่อเรียกดูข้อมูล ซึ่งจะมีลักษณะดังรูป
เป็นอันเรียบร้อยสำหรับการทำตารางไขว้ หลังจากนี้คุณจะนำข้อมูลที่ได้ไปทำอะไรต่อก็ได้ เช่น ออกรายงานเป็นต้น
| แนะนำคำสั่ง DO (_GENXTAB) เป็นโปรแกรมที่ใช้ในการสร้างตารางไขว้(cross-tab) ซึ่งมีรูปแบบคำสั่งดังนี้ DO (_GENXTAB) WITH <output file>, [<output type>], [<close source>], ; [<show thermometer>], [<row>], [<column>], [<cell>], [<row total>] เรามาดูกันว่า parameter แต่ละตัวใช้งานอย่างไร <output file> เป็นชื่อของตาราง cross-tab <output type> กำหนดเป็น .T. output file ตารางที่ได้จะเป็นตารางชั่วคราว(CURSOR) กำหนดเป็น .F. หรือไม่กำหนด output file ตารางที่ได้จะเป็นประเภทตามตารางต้นฉบับ <close source> กำหนดเป็น .T. หรือไม่กำหนด จะทำการปิดตารางต้นฉบับให้อัตโนมัติ กำหนดเป็น .F. จะยังไม่ปิดตารางต้นฉบับ <show thermoeter> กำหนดเป็น .T.หรือไม่กำหนด จะแสดงบาร์บอกการทำงาน กำหนดเป็น .F. จะยังไม่ให้แสดงบาร์บอกการทำงาน <row> กำหนดหมายเลขฟิลด์ ที่จะให้มาเป็น row ในตารางไขว้ จากตัวอย่างข้างต้น ที่ตาราง MYCURSOR ฟิลด์ Cust_id จะเป็นหมายเลข 1, Sales_id เป็นหมายเลข 2, Inv_no เป็นหมายเลข 3, Sum_exp_4 เป็นหมายเลข 4 <col> กำหนดหมายเลขฟิลด์ ที่จะให้มาเป็น column ในตารางไขว้ <cell> กำหนดหมายเลขฟิลด์ ที่จะให้มาเป็นข้อมูลในตารางไขว้ <row total> กำหนดเป็น .T. จะทำการสร้างฟิลด์ผลรวมให้ด้วย |
Rushmore Technology
Rushmore Technology เป็นเทคโนโลยีที่ช่วยเพิ่มความเร็วในการเข้าถึงข้อมูลใน Visual FoxPro ซึ่งเป็นลักษณะเด่นที่เกิดขึ้นตั้งแต่ FoxPro 2 สมัยที่ยังเป็น DOS อยู่ ซึ่งเป็นเทคโนโลยีที่ช่วยเพิ่มความเร็วในการเข้าถึงเรคคอร์ด โดยอาศัยแฟ้มดัชนีเข้าช่วย ซึ่งแฟ้มดัชนีของ Visual FoxPro มีอยู่ 2 ชนิด คือ แฟ้มดัชนีมาตรฐาน(.IDX) กับ compound index(.CDX) โดยที่แฟ้ม .CDX จะถูกเปิดโดยอัตโนมัติเมื่อเราทำการเปิดตารางขึ้นมาใช้งาน ฉะนั้น เราสามารถใช้ความสามารถของ Rushmore ได้ทันที แต่ถ้าเรามีแฟ้ม .IDX เราต้องทำการเปิดแฟ้มดัชนี้ที่มีความสัมพันธ์กับเงื่อนไขที่เราต้องการก่อน
แล้วเราจะสร้างแฟ้มดัชนีอย่างไรถึงจะใช้ความสามารถของ Rushmore Technology ได้?
ต้องขอบอกตรงนี้ก่อนเลยว่า การใช้ Rushmore Technology สามารถใช้งานได้กับคำสั่งต่างๆเหล่านี้โดยอาศัยวลี FOR ได้ด้วย
| AVERAGE BLANK BROWSE CALCULATE CHANGE COPY TO COPY TO ARRAY COUNT DELETE DISPLAY EDIT EXPORT |
INDEX LABEL LIST LOCATE RECALL REPLACE REPLACE FROM ARRAY REPORT SCAN SORT SUM TOTAL |
เทคโนโลยี Rushmore จะทำงานได้เร็วมากเมื่อคุณใช้ คอมพิวเตอร์เพียงเครื่องเดียวทำงานกับตารางเดียว แต่ใช่ว่าจะใช้กับหลายผู้ใช้ หรือหลายๆตารางไม่ได้ เพียงแต่ประสิทธิภาพการทำงานจะด้อยลงมาเท่านั้นเอง
| ข้อควรระวัง Rushmore Technology จะไม่ทำงานถ้าคุณสร้างแฟ้มดัชนีที่มีเงื่อนไข NOT เช่น INDEX ON CUST_ID FOR .NOT.DELETE() TAG CUSTOMER |
เช่น ถ้าต้องการคำนวณยอดรวมของ invoice ถ้าเราต้องการความเร็วในการคำนวณ ให้ทำการสร้างแฟ้มดัชนีดังตัวอย่างต่อไปนี้
OPEN DATABASE MYDATABASE
USE INV_DTL
INDEX ON INV_NO TAG INV_NO1
&& เมื่อเราใช้คำสั่งตามตัวอย่างด้านล่างเราก็จะได้ใช้ความสามารถของ rushmore
Calculate Sum(Inv_dtl.Quantity*Inv_dtl.UnitPrice) ;
For Inv_dtl.inv_no = 1 ;
To nMytotal
? nMytotal
จากที่กล่าวมานี้เราพอจะสรุปได้ว่า Rushmore Technology นั้นอาศัยหลักการเข้าถึงข้อมูลจากแฟ้มดัชนี้ ฉะนั้นเราจะเพิ่มความเร็วให้กับโปรแกรมของเรา เมื่อเราใช้วลี FOR ครั้งใด โปรดสำรวจแฟ้มดัชนีของคุณว่ามีลักษณะตรงกันกับเงื่อนไขใน FOR หรือไม่ เช่นต้องการเปรียบข้อมูลของฟิลด์ใด ก็ควรจะทำดัชนีให้กับฟิลด์นั้นๆด้วย
UPDATE INV_DTL SET QUANTITY = 30 WHERE INV_DTL.INV_NO = 1 .AND. PROD_NO = ‘10001’
ในส่วน SQL เราก็สามารถใช้ความสามารถของ Rushmore Technology ได้เช่นกัน ดังตัวอย่างต่อไปนี้
SELECT INV_DTL.INV_NO, SUM(INV_DTL.QUANTITY * INV_DTL.UNITPRICE) ;
FROM INV_DTL WHERE INV_DTL.INV_NO = 1
Visual FoxPro ตั้งแต่รุ่น 5.0 เป็นต้นมาได้เพิ่มฟังก์ชันในการตรวจสอบการทำงานของ rushmore technology กับ คำสั่ง SQL มาให้ซึ่งมีรูปแบบคำสั่งดังนี้
SYS(3054, 0 | 1 | 11 )
0 กำหนดไม่ให้แสดงสถานะการ optimize ของ rushmore technology
1 กำหนดให้แสดงสถานะการ optimize ของ rushmore technology แต่จะแสดงกับตารางเดี่ยว
11 กำหนดให้แสดงสถานะการ optimize ของ rushmore technology แต่จะแสดงกับตารางที่มีการเชื่อมกัน(join)
ตัวอย่างเช่น ถ้าต้องการตรวจสอบดูว่าคำสั่งต่อไปนี้จะใช้ความสามารถของ Rushmore หรือไม่ ให้พิมพ์คำสั่งดังนี้
? SYS(3054,1)
แล้วลองใช้คำสั่ง UPDATE – SQL ทำการปรับปรุงข้อมูลของตาราง INV_DTL โดยทำการเปลี่ยน quantity เป็น 30 ตามเงื่อนไขที่กำหนดดูว่าผลลัพธ์จะเป็นอย่างไร
UPDATE INV_DTL SET QUANTITY = 30 WHERE INV_DTL.INV_NO = 1 .AND. PROD_NO = ‘10001’
หลังจากที่เรียกคำสั่งแล้วโปรแกรมจะแสดงขอความดังนี้
Using index tag Inv_no1 to rushmore optimize table Inv_dtl
Using index tag Prod_id to rushmore optimize table Inv_dtl
Rushmore optimization level for table inv_dtl:full
แสดงว่าคำสั่งข้างต้นได้ใช้ความสามารถของ rushmore อย่างเต็มที่
ให้คุณลองสังเกตุคำสั่งต่อไปนี้ดูว่าเกิดอะไรขึ้นบ้างเมื่อเราทำการ เชื่อมตารางลายๆตารางเข้าด้วยกัน
? SYS(3054,11)
SELECT Inv_head.cust_id, Customer.sales_id, Inv_head.inv_no , ;
SUM(INV_DTL.QUANTITY * INV_DTL.UNITPRICE) ;
FROM mydatabase!inv_head ;
LEFT OUTER JOIN mydatabase!inv_dtl ;
ON Inv_head.inv_no = Inv_dtl.inv_no ;
LEFT OUTER JOIN mydatabase!customer ;
ON Inv_head.cust_id = Customer.cust_id ;
GROUP BY Inv_dtl.inv_no ;
ORDER BY Inv_head.cust_id, Customer.Sales_id, Inv_dtl.Inv_no ;
WHERE INV_DTL.INV_NO = 1 ;
INTO CURSOR MYCURSOR
จากที่กล่าวมาทั้งหมดนี้เราก็สามารถใช้ความสามารถของ rushmore technology ช่วยในการเพิ่มความเร็วในการเข้าถึงข้อมูลได้ดียิ่งขึ้น
ทีนี้เรามาดูรูปแบบการใช้งาน SELECT – SQL และเทคนิคต่างๆกันต่อ
กำจัดข้อมูลที่ซ้ำกันออกด้วย วลี DISTINCT
SELECT DISTINCT Inv_dtl.Inv_no FROM Inv_dtl
การใช้ตัวแปรมาโคร (Macro Substitution)
ในกรณีที่คำสั่งหรือเงื่อนไขใน query ของเรายาวมากๆ เราสามารถใช้ตัวแปรมาโครแทนคำสั่งหรือเงื่อนไขเหล่านั้นได้
cMymacro = “Inv_head.Inv_no # 1 AND Inv_head.Cust_Id = ‘10002’”
SELECT * FROM Inv_head WHERE &cMymarcro ในบางครั้งการใช้ตัวแปรมาโครก็ไม่สะดวกนักเราอาจใช้ตัวแปรแทนในการสั่งงาน query ได้เช่น
cMyfield = “CUST_ID”
cMycond = “10002”
SELECT * FROM Customer WHERE EVALUATE(cMyfield) = (cMycond)
การใช้งาน Subquery
Subquery คือ การใช้คำสั่ง SQL ภายใต้คำสั่ง SQL อีกที เพื่อทำการเลือกข้อมูลตามเงื่อนไขใน subquery อีกครั้งหนึ่ง เช่น ถ้าเราต้องการอยากทราบว่าลูกค้ารายใดที่ไม่เคยซื้อสินค้าของเราเลย เราสามารถสอบถามข้อมูลได้โดยใช้คำสั่งต่อไปนี้
SELECT * FROM Customer ;
WHERE Customer.Cust_Id NOT IN ;
(SELECT DISTINCT Inv_head.Cust_Id FROM Inv_head)
ตอนต่อไปจะเป็นการเรียนรู้คำสั่ง SQL คำสั่งอื่นๆ