SQL คืออะไร? ภาษากลางของการวิเคราะห์ข้อมูล

ปัจจุบัน Structured Query Language เป็นวิธีมาตรฐานในการจัดการและสืบค้นข้อมูลในฐานข้อมูลเชิงสัมพันธ์แม้ว่าจะมีส่วนขยายที่เป็นกรรมสิทธิ์ของผลิตภัณฑ์ก็ตาม ความง่ายและความแพร่หลายของ SQL ทำให้ผู้สร้าง "NoSQL" หรือที่เก็บข้อมูลที่ไม่เกี่ยวข้องเช่น Hadoop นำชุดย่อยของ SQL มาใช้หรือสร้างภาษาแบบสอบถามที่เหมือน SQL ของตนเอง

แต่ SQL ไม่ใช่ภาษา "สากล" สำหรับฐานข้อมูลเชิงสัมพันธ์เสมอไป ตั้งแต่เริ่มต้น (ประมาณปี 1980) SQL มีการโจมตีบางอย่าง นักวิจัยและนักพัฒนาหลายคนในเวลานั้นรวมทั้งฉันคิดว่าค่าใช้จ่ายของ SQL จะทำให้ SQL ไม่สามารถใช้งานได้จริงในฐานข้อมูลการผลิต

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

ประวัติ SQL

ก่อนที่จะมี SQL ฐานข้อมูลมีอินเทอร์เฟซการเขียนโปรแกรมการนำทางที่แน่นหนาและโดยทั่วไปได้รับการออกแบบโดยใช้โครงข่ายเครือข่ายที่เรียกว่าแบบจำลองข้อมูล CODASYL CODASYL (Committee on Data Systems Languages) เป็นกลุ่มที่รับผิดชอบภาษาโปรแกรม COBOL (เริ่มในปี 1959) และส่วนขยายภาษาฐานข้อมูล (เริ่ม 10 ปีให้หลัง)

เมื่อคุณตั้งโปรแกรมกับฐานข้อมูล CODASYL คุณกำลังนำทางไปยังระเบียนผ่านชุดซึ่งแสดงความสัมพันธ์แบบหนึ่งต่อกลุ่ม ฐานข้อมูลแบบลำดับชั้นที่เก่ากว่าอนุญาตให้บันทึกเป็นของชุดเดียวเท่านั้น ฐานข้อมูลเครือข่ายอนุญาตให้บันทึกเป็นของหลายชุด

สมมติว่าคุณต้องการแสดงรายชื่อนักเรียนที่ลงทะเบียนใน CS 101 ขั้นแรกคุณจะพบ"CS 101"ในCoursesชุดตามชื่อกำหนดให้เป็นเจ้าของหรือผู้ปกครองของEnrolleesชุดค้นหาสมาชิกคนแรก ( ffm) ของEnrolleesชุดซึ่งเป็นStudentระเบียนและรายการ มัน. จากนั้นคุณจะเข้าสู่วง: ค้นหาสมาชิกคนต่อไป ( fnm) และแสดงรายการ เมื่อfnmล้มเหลวคุณจะออกจากลูป

นั่นอาจดูเหมือนเป็นงานที่ยุ่งยากมากสำหรับโปรแกรมเมอร์ฐานข้อมูล แต่ก็มีประสิทธิภาพมากในเวลาดำเนินการ ผู้เชี่ยวชาญเช่น Michael Stonebraker จาก University of California at Berkeley และ Ingres ชี้ให้เห็นว่าการทำแบบสอบถามในฐานข้อมูล CODASYL เช่น IDMS ใช้เวลาประมาณครึ่งหนึ่งของ CPU และมีหน่วยความจำน้อยกว่าครึ่งหนึ่งของแบบสอบถามเดียวกันบนฐานข้อมูลเชิงสัมพันธ์โดยใช้ SQL .

สำหรับการเปรียบเทียบแบบสอบถาม SQL ที่เทียบเท่าเพื่อส่งคืนนักเรียนทั้งหมดใน CS 101 จะเป็นอย่างไร 

เลือกชื่อนักเรียนจากหลักสูตรผู้ลงทะเบียนนักเรียน WHERE course.name

ไวยากรณ์นั้นแสดงถึงการรวมภายในเชิงสัมพันธ์ (จริงๆแล้วมีสองตัว) ดังที่ฉันจะอธิบายด้านล่างและทิ้งรายละเอียดที่สำคัญบางอย่างเช่นฟิลด์ที่ใช้สำหรับการรวม

ฐานข้อมูลเชิงสัมพันธ์และ SQL

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

กล่าวอีกนัยหนึ่งกฎของมัวร์ได้ฆ่าฐานข้อมูล CODASYL เพื่อสนับสนุนฐานข้อมูลเชิงสัมพันธ์ ขณะที่มันเกิดขึ้นการปรับปรุงเวลาในการพัฒนามีความสำคัญ แต่การพกพา SQL กลายเป็นความฝัน

โมเดลเชิงสัมพันธ์และ SQL มาจากไหน? EF“ Ted” Codd เป็นนักวิทยาศาสตร์คอมพิวเตอร์ที่ IBM San Jose Research Laboratory ซึ่งทำงานตามทฤษฎีของแบบจำลองเชิงสัมพันธ์ในทศวรรษที่ 1960 และเผยแพร่ในปี 1970 IBM ใช้ฐานข้อมูลเชิงสัมพันธ์ได้ช้าเพื่อพยายามปกป้องรายได้ของ ฐานข้อมูล CODASYL IMS / DB เมื่อ IBM เริ่มโครงการ System R ในที่สุดทีมพัฒนา (Don Chamberlin และ Ray Boyce) ไม่ได้อยู่ภายใต้ Codd และพวกเขาไม่สนใจเอกสารภาษาเชิงสัมพันธ์ Alpha ของ Codd ในปี 1971 เพื่อออกแบบภาษาของตนเอง SEQUEL (Structured English Query Language) ในปี 1979 ก่อนที่ IBM จะเปิดตัวผลิตภัณฑ์ Larry Ellison ได้รวมภาษาไว้ในฐานข้อมูล Oracle ของเขา (โดยใช้สิ่งพิมพ์ SEQUEL ก่อนเปิดตัวของ IBM เป็นข้อมูลจำเพาะของเขา) SEQUEL กลายเป็น SQL ในไม่ช้าเพื่อหลีกเลี่ยงการละเมิดเครื่องหมายการค้าระหว่างประเทศ

“ tom-toms beating for SQL” (ตามที่ Michael Stonebraker กล่าวไว้) ไม่เพียง แต่มาจาก Oracle และ IBM เท่านั้น แต่ยังมาจากลูกค้าด้วย การจ้างหรือฝึกอบรมนักออกแบบฐานข้อมูลและโปรแกรมเมอร์ CODASYL ไม่ใช่เรื่องง่ายดังนั้น SEQUEL (และ SQL) จึงดูน่าสนใจกว่ามาก SQL มีความน่าสนใจอย่างมากในช่วงทศวรรษที่ 1980 ต่อมาผู้จำหน่ายฐานข้อมูลจำนวนมากได้ทำการเย็บตัวประมวลผลแบบสอบถาม SQL ไว้ที่ด้านบนของฐานข้อมูล CODASYL ซึ่งทำให้ Codd ผิดหวังอย่างมากซึ่งรู้สึกว่าฐานข้อมูลเชิงสัมพันธ์ต้องได้รับการออกแบบตั้งแต่เริ่มต้นเพื่อให้มีความสัมพันธ์กัน

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

SQL ประกอบด้วยภาษาย่อยสำหรับการกำหนดสกีมาภาษานิยามข้อมูล (DDL) พร้อมกับภาษาย่อยสำหรับการแก้ไขข้อมูลภาษาจัดการข้อมูล (DML) ทั้งสองอย่างนี้มีรากฐานมาจากข้อกำหนดเบื้องต้นของ CODASYL ภาษาย่อยที่สามใน SQL ประกาศแบบสอบถามผ่านSELECTคำสั่งและการรวมเชิงสัมพันธ์

SELECTคำสั่งSQL 

SELECTคำสั่งบอกเพิ่มประสิทธิภาพการค้นหาข้อมูลที่จะตอบแทนสิ่งที่ตารางเพื่อดูในสิ่งที่ความสัมพันธ์จะปฏิบัติตามและสิ่งเพื่อที่จะกำหนดเกี่ยวกับข้อมูลที่ส่งกลับ เครื่องมือเพิ่มประสิทธิภาพการสืบค้นต้องค้นหาด้วยตัวเองว่าจะใช้ดัชนีใดเพื่อหลีกเลี่ยงการสแกนตารางแรงดุร้ายและบรรลุประสิทธิภาพการสืบค้นที่ดีเว้นแต่ฐานข้อมูลเฉพาะจะสนับสนุนคำแนะนำดัชนี

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

ศิลปะที่สำคัญอีกอย่างคือการเลือกคีย์หลักที่ดีและเป็นเอกลักษณ์สำหรับทุกโต๊ะ คุณไม่เพียง แต่ต้องพิจารณาถึงผลกระทบของคีย์หลักในการสืบค้นทั่วไปเท่านั้น แต่จะเล่นอย่างไรในการรวมเมื่อปรากฏเป็นคีย์ต่างประเทศในตารางอื่นและจะส่งผลต่อตำแหน่งของข้อมูลอ้างอิงอย่างไร

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

การสนทนาของSELECTข้อความอาจเริ่มเรียบง่าย แต่อาจทำให้สับสนได้อย่างรวดเร็ว พิจารณา:

เลือก * จากลูกค้า;

ง่ายใช่มั้ย? มันขอฟิลด์ทั้งหมดและทุกแถวของCustomersตาราง อย่างไรก็ตามสมมติว่าCustomersตารางมีแถวร้อยล้านแถวและร้อยช่องและหนึ่งในช่องนั้นเป็นช่องข้อความขนาดใหญ่สำหรับแสดงความคิดเห็น จะใช้เวลานานเท่าใดในการดึงข้อมูลทั้งหมดนั้นผ่านการเชื่อมต่อเครือข่าย 10 เมกะบิตต่อวินาทีหากแต่ละแถวมีข้อมูลเฉลี่ย 1 กิโลไบต์

บางทีคุณควรลดจำนวนเงินที่คุณส่งผ่านสาย พิจารณา:

เลือกอันดับสูงสุด 100 companyName, lastSaleDate, lastSaleAmount, totalSalesAmount จากลูกค้า
รัฐและเมืองที่ไหน
สั่งซื้อตาม lastSaleDate DESCENDING;

ตอนนี้คุณจะดึงข้อมูลน้อยลงมาก คุณได้ขอให้ฐานข้อมูลให้คุณเพียงสี่ช่องเพื่อพิจารณาเฉพาะ บริษัท ในคลีฟแลนด์และให้คุณเพียง 100 บริษัท ที่มียอดขายล่าสุด ในการดำเนินการดังกล่าวอย่างมีประสิทธิภาพสูงสุดที่เซิร์ฟเวอร์ฐานข้อมูลCustomersตารางต้องการดัชนีstate+cityสำหรับWHEREอนุประโยคและดัชนีlastSaleDateสำหรับORDER BYและTOP 100อนุประโยค

อย่างไรก็ตามTOP 100ใช้ได้กับ SQL Server และ SQL Azure แต่ไม่ใช่ MySQL หรือ Oracle ใน MySQL คุณจะใช้LIMIT 100หลังWHEREอนุประโยค ใน Oracle คุณต้องการใช้ที่ถูกผูกไว้บนROWNUMเป็นส่วนหนึ่งของข้อคือWHERE WHERE... AND ROWNUM <=100น่าเสียดายที่มาตรฐาน ANSI / ISO SQL (และมีเก้ามาตรฐานจนถึงปัจจุบันซึ่งขยายจากปี 1986 ถึงปี 2016) ไปไกลกว่านั้นซึ่งแต่ละฐานข้อมูลจะแนะนำส่วนคำสั่งและคุณสมบัติที่เป็นกรรมสิทธิ์ของตนเอง

รวม SQL 

จนถึงตอนนี้ฉันได้อธิบายSELECTไวยากรณ์สำหรับตารางเดี่ยวแล้ว ก่อนที่ฉันจะอธิบาย  JOINอนุประโยคได้คุณต้องเข้าใจคีย์ต่างประเทศและความสัมพันธ์ระหว่างตาราง ฉันจะอธิบายสิ่งนี้โดยใช้ตัวอย่างใน DDL โดยใช้ไวยากรณ์ของ SQL Server

เวอร์ชันสั้น ๆ นี้ค่อนข้างง่าย ทุกตารางที่คุณต้องการใช้ในความสัมพันธ์ควรมีข้อ จำกัด ของคีย์หลัก ซึ่งอาจเป็นฟิลด์เดียวหรือรวมกันของฟิลด์ที่กำหนดโดยนิพจน์ ตัวอย่างเช่น:

สร้างบุคคลในตาราง (
    PersonID int ไม่ใช่คีย์หลักที่เป็นโมฆะ
    ชื่อบุคคลถ่าน (80)
    ...

ทุกตารางที่ต้องเกี่ยวข้องPersonsควรมีฟิลด์ที่ตรงกับPersonsคีย์หลักและเพื่อรักษาความสมบูรณ์เชิงสัมพันธ์ฟิลด์นั้นควรมีข้อ จำกัด ของคีย์ต่างประเทศ ตัวอย่างเช่น:

สร้างตารางคำสั่งซื้อ (
    OrderID int ไม่ใช่คีย์หลักที่เป็นโมฆะ
    ...
    PersonID int คีย์ต่างประเทศอ้างอิงบุคคล (PersonID)
);

มีคำสั่งทั้งสองเวอร์ชันที่ยาวกว่าที่ใช้CONSTRAINTคำหลักซึ่งช่วยให้คุณสามารถตั้งชื่อข้อ จำกัด ได้ นั่นคือสิ่งที่เครื่องมือออกแบบฐานข้อมูลส่วนใหญ่สร้างขึ้น

คีย์หลักจะถูกจัดทำดัชนีเสมอและไม่ซ้ำกัน (ค่าฟิลด์ไม่สามารถทำซ้ำได้) ช่องอื่น ๆ สามารถเลือกจัดทำดัชนีได้ มักจะมีประโยชน์ในการสร้างดัชนีสำหรับฟิลด์คีย์ต่างประเทศและสำหรับฟิลด์ที่ปรากฏในWHEREและส่วนORDER BYคำสั่งแม้ว่าจะไม่เสมอไปเนื่องจากค่าใช้จ่ายที่อาจเกิดขึ้นจากการเขียนและการอัปเดต

คุณจะเขียนข้อความค้นหาที่ส่งคืนคำสั่งซื้อทั้งหมดที่วางโดย John Doe ได้อย่างไร

เลือก PersonName, OrderID จากบุคคล
INNER JOIN คำสั่งซื้อกับบุคคล PersonalID = Orders.PersonID
ชื่อบุคคลที่ไหน

ในความเป็นจริงมีสี่ชนิดJOIN: INNER, OUTER, และLEFT RIGHTค่าINNER JOINนี้เป็นค่าเริ่มต้น (คุณสามารถละคำได้INNER) และเป็นค่าที่รวมเฉพาะแถวที่มีค่าที่ตรงกันในทั้งสองตาราง หากคุณต้องการแสดงรายชื่อบุคคลว่ามีคำสั่งซื้อหรือไม่คุณต้องใช้ a LEFT JOINตัวอย่างเช่น:

เลือก PersonName, OrderID จากบุคคล
ซ้ายเข้าร่วมคำสั่งซื้อสำหรับบุคคล PersonalID = Orders.PersonID
สั่งซื้อตามชื่อบุคคล;

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

SQL เก็บโพรซีเดอร์

บางครั้งลักษณะที่เปิดเผยของSELECTข้อความไม่ได้ทำให้คุณรู้ว่าคุณต้องการไปที่ไหน ฐานข้อมูลส่วนใหญ่มีสิ่งอำนวยความสะดวกที่เรียกว่าขั้นตอนการจัดเก็บ น่าเสียดายที่นี่เป็นพื้นที่ที่ฐานข้อมูลเกือบทั้งหมดใช้ส่วนขยายที่เป็นกรรมสิทธิ์ของมาตรฐาน ANSI / ISO SQL

ใน SQL Server ภาษาเริ่มต้นสำหรับกระบวนงานที่จัดเก็บ (หรือ procs ที่เก็บไว้) คือ Transact-SQL หรือที่เรียกว่า T-SQL ใน Oracle มันคือ PL-SQL ฐานข้อมูลทั้งสองได้เพิ่มภาษาเพิ่มเติมสำหรับโพรซีเดอร์ที่จัดเก็บเช่น C #, Java และ R โพรซีเดอร์ที่เก็บ T-SQL แบบธรรมดาอาจเป็นเวอร์ชันที่กำหนดพารามิเตอร์ของSELECTคำสั่งเท่านั้น ข้อดีของมันคือใช้งานง่ายและมีประสิทธิภาพ กระบวนงานที่จัดเก็บจะได้รับการปรับให้เหมาะสมเมื่อถูกบันทึกไม่ใช่ทุกครั้งที่ดำเนินการ

ขั้นตอนการจัดเก็บ T-SQL ที่ซับซ้อนมากขึ้นอาจใช้คำสั่ง SQL พารามิเตอร์อินพุตและเอาต์พุตตัวแปรโลคัลBEGIN...ENDบล็อกIF...THEN...ELSEเงื่อนไขเคอร์เซอร์ (การประมวลผลทีละแถวของชุด) นิพจน์ตารางชั่วคราวและโฮสต์ทั้งหมดของอื่น ๆ ไวยากรณ์ขั้นตอน เห็นได้ชัดว่าถ้าภาษากระบวนงานที่จัดเก็บคือ C #, Java หรือ R คุณจะต้องใช้ฟังก์ชันและไวยากรณ์ของภาษาขั้นตอนเหล่านั้น กล่าวอีกนัยหนึ่งแม้ว่าแรงจูงใจสำหรับ SQL คือการใช้แบบสอบถามที่เปิดเผยที่เป็นมาตรฐาน แต่ในโลกแห่งความเป็นจริงคุณจะเห็นการเขียนโปรแกรมเซิร์ฟเวอร์ขั้นตอนเฉพาะฐานข้อมูลจำนวนมาก

นั่นไม่ได้พาเราย้อนกลับไปสู่ยุคเก่าที่เลวร้ายของการเขียนโปรแกรมฐานข้อมูล CODASYL (แม้ว่าเคอร์เซอร์จะเข้ามาใกล้) แต่ก็ย้อนกลับไปจากแนวคิดที่ว่าคำสั่ง SQL ควรเป็นมาตรฐานและข้อกังวลด้านประสิทธิภาพควรถูกทิ้งไว้ที่เครื่องมือเพิ่มประสิทธิภาพการสืบค้นฐานข้อมูล . ในท้ายที่สุดการเพิ่มขึ้นเป็นสองเท่ามักจะมากเกินไปที่จะทิ้งไว้บนโต๊ะ

เรียนรู้ SQL

ไซต์ด้านล่างนี้สามารถช่วยให้คุณเรียนรู้ SQL หรือค้นพบนิสัยใจคอของภาษา SQL ที่หลากหลาย