วิธีการเขียนแบบสอบถาม SQL ของ Microsoft Access ตั้งแต่เริ่มต้น

วิธีการเขียนแบบสอบถาม SQL ของ Microsoft Access ตั้งแต่เริ่มต้น

Microsoft Access เป็นเครื่องมือที่ทรงพลังที่สุดในชุดโปรแกรม Microsoft Office ทั้งหมด แต่กลับทำให้ผู้ใช้ที่มีอำนาจของ Office สับสน (และบางครั้งก็ทำให้กลัว) ด้วยเส้นโค้งการเรียนรู้ที่ชันกว่า Word หรือ Excel ทุกคนควรคำนึงถึงการใช้เครื่องมือนี้อย่างไร สัปดาห์นี้ Bruce Epper จะพิจารณาปัญหาบางอย่างที่เกิดจากคำถามนี้จากผู้อ่านของเรา





ผู้อ่านถามว่า:

ฉันมีปัญหาในการเขียนแบบสอบถามใน Microsoft Access ฉันมีฐานข้อมูลที่มีตารางผลิตภัณฑ์สองตารางที่มีคอลัมน์ทั่วไปที่มีรหัสผลิตภัณฑ์ที่เป็นตัวเลขและชื่อผลิตภัณฑ์ที่เกี่ยวข้อง ฉันต้องการค้นหาว่าผลิตภัณฑ์ใดจากตาราง A ที่สามารถพบได้ ในตาราง B ฉันต้องการเพิ่มคอลัมน์ชื่อผลลัพธ์ซึ่งมีชื่อผลิตภัณฑ์จากตาราง A หากมี และชื่อผลิตภัณฑ์จากตาราง B เมื่อไม่มีอยู่ในตาราง A คุณมีคำแนะนำอะไรบ้าง



คำตอบของบรูซ:

Microsoft Access เป็นระบบจัดการฐานข้อมูล (DBMS) ที่ออกแบบมาเพื่อใช้กับทั้งเครื่อง Windows และ Mac ใช้เครื่องมือฐานข้อมูล Jet ของ Microsoft สำหรับการประมวลผลและจัดเก็บข้อมูล นอกจากนี้ยังมีอินเทอร์เฟซแบบกราฟิกสำหรับผู้ใช้ซึ่งแทบไม่จำเป็นต้องเข้าใจ Structured Query Language (SQL)





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

จุดเริ่ม

หากคุณยังไม่คุ้นเคยกับ Access หรือ RDBMS อื่น เราขอแนะนำให้คุณเริ่มต้นด้วยแหล่งข้อมูลเหล่านี้ก่อนดำเนินการต่อ:



  • แล้วฐานข้อมูลคืออะไร? โดย Ryan Dube ใช้ Excel เพื่อแสดงพื้นฐานของฐานข้อมูลเชิงสัมพันธ์
  • คู่มือฉบับย่อเพื่อเริ่มต้นใช้งาน Microsoft Access 2007 ซึ่งเป็นภาพรวมระดับสูงของ Access และส่วนประกอบที่ประกอบด้วยฐานข้อมูล Access
  • บทช่วยสอนด่วนสำหรับตารางใน Microsoft Access 2007 จะดูที่การสร้างฐานข้อมูลและตารางแรกของคุณเพื่อจัดเก็บข้อมูลที่มีโครงสร้างของคุณ
  • บทช่วยสอนด่วนเกี่ยวกับคิวรีใน Microsoft Access 2007 ดูที่วิธีการส่งคืนข้อมูลบางส่วนที่จัดเก็บไว้ในตารางฐานข้อมูล

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

ความสัมพันธ์ของฐานข้อมูลและการทำให้เป็นมาตรฐาน

ลองนึกภาพคุณกำลังเปิดบริษัทขายวิดเจ็ตต่างๆ กว่า 50 ประเภททั่วโลก คุณมีฐานลูกค้า 1,250 ราย และในเดือนเฉลี่ยขายวิดเจ็ต 10,000 รายการให้กับลูกค้าเหล่านี้ คุณกำลังใช้สเปรดชีตเดียวเพื่อติดตามการขายทั้งหมด - ตารางฐานข้อมูลเดียวอย่างมีประสิทธิภาพ และทุกปีจะเพิ่มแถวในสเปรดชีตของคุณหลายพันแถว



ภาพด้านบนเป็นส่วนหนึ่งของสเปรดชีตการติดตามคำสั่งซื้อที่คุณใช้ สมมติว่าลูกค้าทั้งสองนี้ซื้อวิดเจ็ตจากคุณปีละหลายครั้ง เพื่อให้คุณมีแถวสำหรับทั้งสองมากขึ้น



ถ้า Joan Smith แต่งงานกับ Ted Baines และใช้นามสกุลของเขา ตอนนี้จะต้องเปลี่ยนทุกแถวที่มีชื่อของเธอ ปัญหาจะทวีคูณขึ้นหากคุณมีลูกค้าสองรายที่ชื่อ 'โจน สมิธ' การรักษาข้อมูลการขายของคุณให้สอดคล้องกันนั้นยากขึ้นมากเนื่องจากเหตุการณ์ที่ค่อนข้างธรรมดา

ด้วยการใช้ฐานข้อมูลและทำให้ข้อมูลเป็นมาตรฐาน เราสามารถแยกรายการออกเป็นหลายตาราง เช่น สินค้าคงคลัง ลูกค้า และคำสั่งซื้อ

เพียงแค่ดูที่ส่วนลูกค้าในตัวอย่างของเรา เราจะลบคอลัมน์สำหรับชื่อลูกค้าและที่อยู่ลูกค้าและใส่ลงในตารางใหม่ ในภาพด้านบน ฉันได้แยกแยะสิ่งต่าง ๆ ให้ดีขึ้นเพื่อให้เข้าถึงข้อมูลได้ละเอียดยิ่งขึ้น ตารางใหม่ยังมีคอลัมน์สำหรับคีย์หลัก (ClientID) ซึ่งเป็นตัวเลขที่จะใช้ในการเข้าถึงแต่ละแถวในตารางนี้

ในตารางเดิมที่เราลบข้อมูลนี้ เราจะเพิ่มคอลัมน์สำหรับ Foreign Key (ClientID) ซึ่งเป็นสิ่งที่เชื่อมโยงไปยังแถวที่เหมาะสมซึ่งมีข้อมูลสำหรับลูกค้ารายนี้โดยเฉพาะ

ตอนนี้ เมื่อ Joan Smith เปลี่ยนชื่อเธอเป็น Joan Baines การเปลี่ยนแปลงจะต้องทำเพียงครั้งเดียวในตาราง Client การอ้างอิงอื่น ๆ จากตารางที่เข้าร่วมจะดึงชื่อลูกค้าที่เหมาะสมและรายงานที่ระบุว่า Joan ซื้ออะไรในช่วง 5 ปีที่ผ่านมาจะได้รับคำสั่งซื้อทั้งหมดภายใต้ชื่อเดิมและชื่อที่แต่งงานแล้วโดยไม่ต้องเปลี่ยนวิธีการสร้างรายงาน .

นอกจากนี้ ยังช่วยลดปริมาณการใช้พื้นที่เก็บข้อมูลโดยรวมอีกด้วย

เข้าร่วมประเภท

SQL กำหนดห้าประเภทของการรวม: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER และ CROSS คีย์เวิร์ด OUTER เป็นทางเลือกในคำสั่ง SQL

Microsoft Access อนุญาตให้ใช้ INNER (ค่าเริ่มต้น) , LEFT OUTER, RIGHT OUTER และ CROSS ไม่รองรับ FULL OUTER เช่นนี้ แต่ด้วยการใช้ LEFT OUTER, UNION ALL และ RIGHT OUTER มันสามารถปลอมแปลงได้ในราคารอบ CPU และการทำงานของ I/O ที่มากขึ้น

ผลลัพธ์ของการรวม CROSS มีทุกแถวของตารางด้านซ้ายที่จับคู่กับทุกแถวของตารางด้านขวา ครั้งเดียวที่ฉันเคยเห็นการใช้ CROSS เข้าร่วมคือระหว่างการทดสอบโหลดของเซิร์ฟเวอร์ฐานข้อมูล

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

เริ่มต้นด้วยการสร้างสองตาราง ProdA และ ProdB โดยมีคุณสมบัติการออกแบบดังต่อไปนี้

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

ตอนนี้เติมข้อมูลบางส่วน

เพื่อแสดงความแตกต่างในการทำงานของ 3 ประเภทการรวม ฉันได้ลบรายการที่ 1, 5 และ 8 ออกจาก ProdA

ต่อไป, สร้างแบบสอบถามใหม่ โดยไปที่ สร้าง > การออกแบบแบบสอบถาม . เลือกทั้งสองตารางจากกล่องโต้ตอบแสดงตารางและ คลิกเพิ่ม , แล้ว ปิด I .

คลิกที่ ProductID ในตาราง ProdA ลากไปที่ ProductID ในตาราง ProdB แล้วปล่อยปุ่มเมาส์เพื่อสร้างความสัมพันธ์ระหว่างตาราง

คลิกขวาที่เส้นระหว่างตารางที่แสดงความสัมพันธ์ระหว่างรายการและ เลือกเข้าร่วมคุณสมบัติ .

โดยค่าเริ่มต้น การรวมประเภท 1 (INNER) จะถูกเลือก ตัวเลือกที่ 2 คือการเข้าร่วม LEFT OUTER และ 3 คือการเข้าร่วม OUTER ด้านขวา

เราจะดูการเข้าร่วม INNER ก่อน ดังนั้นคลิกตกลงเพื่อปิดกล่องโต้ตอบ

ในตัวออกแบบคิวรี เลือกเขตข้อมูลที่เราต้องการดูจากรายการดรอปดาวน์

เมื่อเราเรียกใช้แบบสอบถาม (เครื่องหมายอัศเจรีย์สีแดงในริบบิ้น) จะแสดงเขตข้อมูล ProductName จากทั้งสองตารางที่มีค่าจากตาราง ProdA ในคอลัมน์แรกและ ProdB ในคอลัมน์ที่สอง

สังเกตว่าผลลัพธ์จะแสดงเฉพาะค่าที่ ProductID เท่ากันในทั้งสองตาราง แม้ว่าจะมีรายการสำหรับ ProductID = 1 ในตาราง ProdB แต่จะไม่แสดงในผลลัพธ์เนื่องจาก ProductID = 1 ไม่มีอยู่ในตาราง ProdA เช่นเดียวกับ ProductID = 11 มีอยู่ในตาราง ProdA แต่ไม่มีในตาราง ProdB

โดยใช้ปุ่มมุมมองบน ribbon และสลับไปยังมุมมอง SQL คุณสามารถดูแบบสอบถาม SQL ที่สร้างโดยตัวออกแบบที่ใช้เพื่อให้ได้ผลลัพธ์เหล่านี้

SELECT ProdA.ProductName, ProdB.ProductName FROM ProdA INNER JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;

กลับไปที่ Design View เปลี่ยนประเภทการรวมเป็น 2 (LEFT OUTER) เรียกใช้แบบสอบถามเพื่อดูผลลัพธ์

อย่างที่คุณเห็น ทุกรายการในตาราง ProdA จะแสดงในผลลัพธ์ ในขณะที่รายการใน ProdB ที่มีรายการ ProductID ที่ตรงกันในตาราง ProdB จะแสดงขึ้นในผลลัพธ์

ช่องว่างในคอลัมน์ ProdB.ProductName เป็นค่าพิเศษ (NULL) เนื่องจากไม่มีค่าที่ตรงกันในตาราง ProdB สิ่งนี้จะพิสูจน์ความสำคัญในภายหลัง

SELECT ProdA.ProductName, ProdB.ProductName FROM ProdA LEFT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;

ลองทำสิ่งเดียวกันกับการรวมประเภทที่สาม (RIGHT OUTER)

ผลลัพธ์จะแสดงทุกอย่างจากตาราง ProdB ในขณะที่แสดงค่าว่าง (เรียกว่า NULL) โดยที่ตาราง ProdA ไม่มีค่าที่ตรงกัน จนถึงตอนนี้ ทำให้เราได้ผลลัพธ์ที่ใกล้เคียงที่สุดตามที่ต้องการในคำถามของผู้อ่าน

SELECT ProdA.ProductName, ProdB.ProductName FROM ProdA RIGHT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;

การใช้ฟังก์ชันในแบบสอบถาม

ผลลัพธ์ของฟังก์ชันอาจถูกส่งคืนโดยเป็นส่วนหนึ่งของการสืบค้น เราต้องการให้คอลัมน์ใหม่ชื่อ 'ผลลัพธ์' ปรากฏในชุดผลลัพธ์ของเรา ค่าจะเป็นเนื้อหาของคอลัมน์ ProductName ของตาราง ProdA หาก ProdA มีค่า (ไม่ใช่ NULL) มิฉะนั้น ควรนำมาจากตาราง ProdB

สามารถใช้ฟังก์ชัน Immediate IF (IIF) เพื่อสร้างผลลัพธ์นี้ได้ ฟังก์ชันใช้พารามิเตอร์สามตัว เงื่อนไขแรกคือเงื่อนไขที่ต้องประเมินเป็นค่าจริงหรือเท็จ พารามิเตอร์ที่สองคือค่าที่จะส่งคืนหากเงื่อนไขเป็น True และพารามิเตอร์ที่สามคือค่าที่จะส่งคืนหากเงื่อนไขเป็น False

โครงสร้างฟังก์ชันเต็มรูปแบบสำหรับสถานการณ์ของเรามีลักษณะดังนี้:

IIF(ProdA.ProductID Is Null, ProdB.ProductName,ProdA.ProductName)

ขอให้สังเกตว่าพารามิเตอร์เงื่อนไขไม่ตรวจสอบความเท่าเทียมกัน ค่า Null ในฐานข้อมูลไม่มีค่าที่สามารถเปรียบเทียบกับค่าอื่นได้ รวมถึงค่า Null อื่น กล่าวอีกนัยหนึ่ง Null ไม่เท่ากับ Null เคย. เพื่อให้ผ่านพ้นไปได้ เราจะตรวจสอบค่าโดยใช้คีย์เวิร์ด 'คือ' แทน

เราอาจใช้ 'Is Not Null' และเปลี่ยนลำดับของพารามิเตอร์ True และ False เพื่อให้ได้ผลลัพธ์แบบเดียวกัน

เมื่อใส่สิ่งนี้ลงใน Query Designer คุณต้องพิมพ์ฟังก์ชันทั้งหมดลงในรายการ Field: หากต้องการสร้างคอลัมน์ 'ผลลัพธ์' คุณต้องใช้นามแฝง เมื่อต้องการทำเช่นนี้ นำหน้าฟังก์ชันด้วย 'ผลลัพธ์:' ตามที่เห็นในภาพหน้าจอต่อไปนี้

รหัส SQL ที่เทียบเท่าในการทำเช่นนี้จะเป็น:

SELECT ProdA.ProductName, ProdB.ProductName, IIF(ProdA.ProductID Is Null,ProdB.ProductName,ProdA.ProductName) AS Results FROM ProdA RIGHT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;

เมื่อเราเรียกใช้แบบสอบถามนี้จะให้ผลลัพธ์เหล่านี้

windows 10 เปิดศูนย์ปฏิบัติการไม่ได้

ที่นี่เราเห็นสำหรับแต่ละรายการที่ตาราง ProdA มีค่า ซึ่งค่านั้นจะแสดงอยู่ในคอลัมน์ผลลัพธ์ หากไม่มีรายการในตาราง ProdA รายการจาก ProdB จะปรากฏในผลลัพธ์ซึ่งตรงกับที่ผู้อ่านของเราถาม

สำหรับแหล่งข้อมูลเพิ่มเติมสำหรับการเรียนรู้ Microsoft Access โปรดดูที่ Joel Lee's How to Learn Microsoft Access: 5 แหล่งข้อมูลออนไลน์ฟรี

แบ่งปัน แบ่งปัน ทวีต อีเมล การอัพเกรดเป็น Windows 11 คุ้มค่าหรือไม่?

Windows ได้รับการออกแบบใหม่ แต่นั่นเพียงพอที่จะโน้มน้าวให้คุณเปลี่ยนจาก Windows 10 เป็น Windows 11 หรือไม่

อ่านต่อไป หัวข้อที่เกี่ยวข้อง
  • ผลผลิต
  • ถามผู้เชี่ยวชาญ
เกี่ยวกับผู้เขียน Bruce Epper(เผยแพร่บทความ 13 บทความ)

บรูซเล่นอุปกรณ์อิเล็กทรอนิกส์มาตั้งแต่ยุค 70 คอมพิวเตอร์ตั้งแต่ต้นยุค 80 และตอบคำถามเกี่ยวกับเทคโนโลยีที่เขาไม่เคยใช้และไม่เคยเห็นมาเลยอย่างแม่นยำ เขายังรบกวนตัวเองด้วยการพยายามเล่นกีตาร์

เพิ่มเติมจาก Bruce Epper

สมัครรับจดหมายข่าวของเรา

เข้าร่วมจดหมายข่าวของเราสำหรับเคล็ดลับทางเทคนิค บทวิจารณ์ eBook ฟรี และดีลพิเศษ!

คลิกที่นี่เพื่อสมัครสมาชิก