מה זה SQL? לשון הפרנקה של ניתוח הנתונים

כיום, שפת שאילתות מובנית היא האמצעי הסטנדרטי של מניפולציה ושאילתות של נתונים בבסיסי נתונים יחסיים, אם כי עם סיומות קנייניות בין המוצרים. הקלות והנמצאות בכל מקום ב- SQL אף הביאו את היוצרים של מאגרי נתונים רבים מסוג "NoSQL" או לא-יחסיים, כמו Hadoop, לאמץ קבוצות משנה של SQL או להמציא שפות שאילתות דמויי SQL משלהם.

אך SQL לא תמיד הייתה השפה "האוניברסאלית" עבור מסדי נתונים יחסיים. מההתחלה (בסביבות 1980), ל- SQL היו שביתות מסוימות נגדה. חוקרים ומפתחים רבים באותה תקופה, כולל אותי, חשבו שהתקורה של SQL תמנע ממנה להיות מעשית במאגר ייצור.

ברור שטעינו. אך רבים עדיין מאמינים שלמרות כל הקלות והנגישות של SQL, המחיר הנדרש בביצועי זמן ריצה הוא לעתים קרובות גבוה מדי.

היסטוריית SQL

לפני שהיה SQL, לבסיסי נתונים היו ממשקי תכנות ניווט הדוקים, ובדרך כלל תוכננו סביב סכימת רשת הנקראת מודל הנתונים CODASYL. CODASYL (הוועדה לשפות מערכות נתונים) היה קונסורציום שהיה אחראי על שפת התכנות COBOL (החל משנת 1959) ועל הרחבות שפות מסדי הנתונים (החל 10 שנים מאוחר יותר).

כאשר תיכנתת כנגד מסד נתונים של CODASYL, ניווטת לרשומות דרך קבוצות המבטאות קשרים רבים-רבים. מסדי נתונים היררכיים ישנים יותר מאפשרים רשומה להשתייך לקבוצה אחת. מאגרי מידע ברשת מאפשרים לרשומה להשתייך למספר קבוצות.

תגיד שאתה רוצה לפרט את לומדי CS 101. ראשית היית מוצא "CS 101"את Coursesהסט לפי שם, להגדיר כי כבעלים או ההורה של Enrolleesהסט, למצוא את החבר הראשון ( ffm) של Enrolleesהסט, המהווה Studentשיא, ואת הרשימה זה. ואז היית נכנס לולאה: מצא את החבר הבא ( fnm) ורשום אותו. אם fnmנכשל, היית יוצא מהלולאה.

זה אולי נראה כמו הרבה עבודות סקוט עבור מתכנת מסד הנתונים, אבל זה היה יעיל מאוד בזמן הביצוע. מומחים כמו מייקל סטונברייקר מאוניברסיטת קליפורניה בברקלי ואינגרס ציינו כי ביצוע שאילתות מסוג זה במאגר CODASYL כגון IDMS לקח כמחצית מזמן המעבד ופחות ממחצית הזיכרון כמו אותה שאילתה במסד נתונים יחסי המשתמש ב- SQL. .

לשם השוואה, שאילתת SQL המקבילה להחזרת כל התלמידים במדעי המחשב 101 תהיה דומה למשהו 

בחר student.name מתוך קורסים, נרשמים, סטודנטים WHERE קורס.שם

התחביר הזה מרמז על צירוף פנימי יחסי (למעשה שניים מהם), כפי שאסביר בהמשך, ומשאיר כמה פרטים חשובים, כמו השדות המשמשים לצירופים.

מאגרי מידע יחסיים ו- SQL

מדוע שתוותרו על גורם של שיפור שני במהירות הביצוע ובשימוש בזיכרון? היו שתי סיבות גדולות: קלות פיתוח וניידות. לא חשבתי שאף אחד לא היה חשוב הרבה בשנת 1980 בהשוואה לדרישות הביצועים והזיכרון, אך ככל שחומרת המחשב השתפרה והפכה לזולה יותר אנשים הפסיקו לדאוג למהירות הביצוע ולזיכרון ודאגו יותר לעלות הפיתוח.

במילים אחרות, חוק מור הרג מסדי נתונים של CODASYL לטובת מאגרי יחס. כשזה קרה, השיפור בזמן הפיתוח היה משמעותי, אך ניידות SQL התגלה כחלום צינור.

מהיכן נבע מודל ההתייחסות ו- SQL? EF "טד" קודד היה מדען מחשבים במעבדת המחקר של IBM סן חוזה, שעבד את תיאוריית המודל ההתייחסותי בשנות השישים ופרסם אותה בשנת 1970. יבמ איחרה ליישם מאגר מידע יחסי במטרה להגן על ההכנסות של מסד הנתונים CODASYL שלו IMS / DB. כאשר IBM התחילה לבסוף את פרויקט System R שלה, צוות הפיתוח (דון צ'מברלין וריי בויס) לא היה תחת קוד, והם התעלמו ממאמר שפת ההתייחסות האלפאית של קודד משנת 1971 כדי לעצב את השפה שלהם, SEQUEL (שפת שאילתות מובנית באנגלית). בשנת 1979, לפני ש- IBM בכלל פרסמה את המוצר שלה, לארי אליסון שילב את השפה במסד הנתונים של אורקל (תוך שימוש בפרסומי SEQUEL של IBM לפני ההשקה כמפרט שלו). SEQUEL הפכה במהרה ל- SQL כדי למנוע הפרה של סימני מסחר בינלאומיים.

"הטום-טום מכים עבור SQL" (כהגדרתו של מייקל סטונברקר) הגיע לא רק מאורקל ו- IBM, אלא גם מלקוחות. לא היה קל להעסיק או להכשיר מעצבי מתכנתים ומתכנתים של CODASYL, כך ש- SEQUEL (ו- SQL) נראו הרבה יותר מושכים. SQL היה כה אטרקטיבי בשנות השמונים המאוחרות, עד כי ספקי מסדי נתונים רבים הידוקו למעשה מעבד שאילתות SQL על גבי מאגרי ה- CODASYL שלהם, למורת רוחם הגדולה של קודד, שחשבו כי יש לתכנן מאגרי מידע יחסיים מאפס כדי להיות יחסיים.

מסד נתונים יחסי טהור, שתוכנן על ידי קודד, בנוי על צמרות מקובצות ביחסים, תואמות לוגיקת פרדיקט מסדר ראשון. בבסיסי נתונים יחסיים אמיתיים יש טבלאות המכילות שדות, אילוצים וטריגרים, וטבלאות קשורות באמצעות מפתחות זרים. SQL משמש להצהרת הנתונים שיוחזרו, ומעבד שאילתות SQL ומייעל שאילתות הופכים את הצהרת SQL לתוכנית שאילתות שמבוצעת על ידי מנוע מסד הנתונים.

SQL כולל שפת משנה להגדרת סכמות, שפת הגדרת הנתונים (DDL), יחד עם שפת משנה לשינוי נתונים, שפת מניפולציה של נתונים (DML). לשני אלה שורשים במפרטי CODASYL המוקדמים. שפת המשנה השלישית ב- SQL מצהירה על שאילתות, באמצעות SELECTהצהרה והצטרפות יחסים.

SELECTהצהרת SQL 

SELECTאמירה אומרת שאילתא האופטימיזציה אילו נתונים בתמורה, מה טבלאות להסתכל, מה יחסים לעקוב, ומה כדי להטיל על נתון שהוחזרו. על אופטימיזציית השאילתות להבין לבד באילו אינדקסים להשתמש כדי להימנע מסריקות טבלה של כוח אכזרי ולהשיג ביצועי שאילתות טובים, אלא אם כן מסד הנתונים המסוים תומך ברמזים לאינדקס.

חלק מאמנות עיצוב מסדי נתונים יחסיים תלוי בשימוש נבון באינדקסים. אם תשמיט אינדקס לשאילתה תכופה, כל מסד הנתונים יכול להאט בעומסי קריאה כבדים. אם יש לך יותר מדי אינדקסים, כל מסד הנתונים יכול להאט בעת עומסי כתיבה ועדכון כבדים.

אומנות חשובה נוספת היא בחירת מפתח עיקרי טוב וייחודי לכל שולחן. אתה לא צריך רק לשקול את ההשפעה של המפתח הראשי על שאילתות נפוצות, אלא איך זה ישחק מצטרף כאשר הוא מופיע כמפתח זר בטבלה אחרת, וכיצד הוא ישפיע על יישוב הייחוס של הנתונים.

במקרה המתקדם של טבלאות מסד נתונים המתחלקות לנפחים שונים בהתאם לערך של המפתח הראשי, הנקרא sharding אופקי, עליכם גם לשקול כיצד ישפיע המפתח הראשי על השיתוף. רמז: אתה מעוניין שהטבלה תפוזה באופן אחיד על כרכים, דבר המצביע על כך שאינך רוצה להשתמש בבולי תאריך או במספרים שלמים עוקבים כמפתחות ראשיים.

הדיונים SELECTבהצהרה עשויים להתחיל בפשטות, אך עלולים להפוך מבלבלים במהירות. לשקול:

בחר * מלקוחות;

פשוט, נכון? הוא מבקש את כל השדות ואת כל שורות Customersהטבלה. נניח, עם זאת, Customersבטבלה יש מאה מיליון שורות ומאה שדות, ואחד השדות הוא שדה טקסט גדול להערות. כמה זמן ייקח להוריד את כל הנתונים לאורך חיבור רשת של 10 מגה-ביט לשנייה אם כל שורה מכילה בממוצע 1 קילובייט של נתונים?

אולי כדאי לקצץ כמה תשלח את החוט. לשקול:

בחר למעלה 100 companyName, lastSaleDate, lastSaleAmount, totalSalesAmount מלקוחות

היכן מדינה ועיר

סדר לפי lastSaleDate בירידה;

עכשיו אתה הולך להוריד הרבה פחות נתונים. ביקשת ממאגר המידע לתת לך רק ארבעה תחומים, להתחשב רק בחברות בקליבלנד ולתת לך רק את 100 החברות עם המכירות האחרונות. כדי לעשות זאת בצורה היעילה ביותר בשרת מסד הנתונים, עם זאת, Customersהטבלה זקוקה לאינדקס state+cityעבור WHEREהסעיף, ואינדקס מופעל lastSaleDateעבור ה- ORDER BYו- TOP 100סעיפים.

אגב, TOP 100תקף עבור SQL Server ו- SQL Azure, אך לא MySQL או Oracle. ב- MySQL, היית משתמש LIMIT 100אחרי WHEREהסעיף. באורקל, היית משתמש ב- bound on ROWNUMכחלק WHEREמהסעיף, כלומר WHERE... AND ROWNUM <=100. למרבה הצער, תקני ANSI / ISO SQL (וישנם תשעה מהם עד כה, שנמשכים בין השנים 1986 ל -2016) רק מגיעים עד כה, שמעבר להם כל בסיס נתונים מציג סעיפים ותכונות קנייניים משלו.

SQL מצטרף 

עד כה תיארתי את SELECTהתחביר לטבלאות בודדות. לפני שאוכל להסביר  JOINסעיפים, עליכם להבין מפתחות זרים ויחסים בין טבלאות. אסביר זאת באמצעות דוגמאות ב- DDL, תוך שימוש בתחביר של שרת SQL.

הגרסה הקצרה לכך פשוטה למדי. לכל טבלה שאתה רוצה להשתמש ביחסים צריכה להיות מגבלת מפתח ראשונית; זה יכול להיות שדה יחיד או שילוב של שדות המוגדרים על ידי ביטוי. לדוגמה:

צור אנשים שולחן (

    PersonID int לא מפתח ראשוני אפס,

    אות Character (80),

    ...

לכל טבלה שצריך להתייחס אליה Personsצריך להיות שדה שמתאים Personsלמפתח הראשי, ולשמור על שלמות ההתייחסות לשדה זה צריך להיות מגבלת מפתח זר. לדוגמה:

צור טבליות הזמנות (

    OrderID int לא מפתח ראשוני אפס,

    ...

    הפניות למפתח מפתח חוץ לאנשים (PersonID)

);

ישנן גרסאות ארוכות יותר של שתי ההצהרות המשתמשות CONSTRAINTבמילת המפתח, המאפשרת לך למנות את האילוץ. זה מה שמפיקים רוב כלי עיצוב מסדי הנתונים.

מפתחות ראשי תמיד באינדקס וייחודיים (לא ניתן לשכפל את ערכי השדה). ניתן להוסיף לאינדקס שדות אחרים. לעתים שימושי כדי ליצור אינדקסים עבור שדות מפתח זרים ולמען שדות שמופיעים WHEREו ORDER BYסעיפים, אם כי לא תמיד, בגלל התקורה הפוטנציאלי כותב ומעדכן.

איך היית כותב שאילתה שמחזירה את כל ההזמנות שביצע ג'ון דו?

בחר שם משתמש, OrderID מאנשים

הצטרפות פנימית הזמנות על Persons.PersonID = Orders.PersonID

WHERE PersonName;

למעשה, ישנם ארבעה סוגים של JOIN: INNER, OUTER, LEFT, ו RIGHT. INNER JOINהיא ברירת המחדל (ניתן להשמיט את המילה INNER), וזה אחד שכולל רק שורות המכילות ערכים תואמים בשני השולחנות. אם ברצונך לרשום אנשים אם יש להם הזמנות או לא, היית משתמש בסימן LEFT JOIN, למשל:

בחר שם משתמש, OrderID מאנשים

LEFT JOIN Orders ON Persons.PersonID = Orders.PersonID

סדר לפי שם משתמש;

כשאתה מתחיל לעשות שאילתות שמצטרפות ליותר משתי טבלאות, המשתמשות בביטויים או שמכריחים סוגי נתונים, התחביר יכול להיות מעט שעיר בהתחלה. למרבה המזל, ישנם כלים לפיתוח מסדי נתונים שיכולים ליצור שאילתות SQL נכונות עבורך, לעתים קרובות על ידי גרירה ושחרור טבלאות ושדות מתרשים הסכימה לתרשים שאילתות.

נהלים מאוחסנים ב- SQL

לפעמים האופי ההצהרתי של SELECTההצהרה לא מביא אותך לאן שאתה רוצה להגיע. ברוב מסדי הנתונים יש מתקן שנקרא נהלים מאוחסנים; למרבה הצער זהו אזור שבו כמעט כל מאגרי המידע משתמשים בתוספות קנייניות לתקני ANSI / ISO SQL.

ב- SQL Server, הניב הראשוני לנהלים המאוחסנים (או procs המאוחסנים) היה Transact-SQL, המכונה T-SQL; באורקל, זה היה 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.