Dzisiaj Structured Query Language jest standardowym sposobem manipulowania i odpytywania danych w relacyjnych bazach danych, choć istnieją również jego własne rozszerzenia. Łatwość i wszechobecność języka SQL doprowadziła nawet do tego, że twórcy wielu nierelacyjnych baz danych (NoSQL), takich jak Hadoop, zaadoptowali jego podzbiory lub wymyślili własne języki zapytań podobne do SQL.
Jednakże SQL nie zawsze był „uniwersalnym” językiem relacyjnych baz danych. Od samego początku (około 1980 roku), SQL miał pewne atuty. Wielu ówczesnych badaczy i programistów, w tym ja, uważało, że koszty ogólne języka SQL sprawią, że nigdy nie będzie on praktyczny w produkcyjnej bazie danych.
Jasno widać, że się myliliśmy. Jednak wielu nadal uważa, że przy całej łatwości i dostępności języka SQL, cena za jego wydajność jest często zbyt wysoka.
Historia baz danych
Zanim pojawił się SQL, bazy danych miały ciasne, nawigacyjne interfejsy programowania i zazwyczaj były projektowane wokół schematu sieciowego zwanego modelem danych CODASYL. CODASYL (Committee on Data Systems Languages) to konsorcjum, które było odpowiedzialne za język programowania COBOL (od 1959 r.) i rozszerzenia języka baz danych (od 10 lat później).
Gdy programowałeś na bazie danych CODASYL, nawigowałeś do rekordów poprzez zestawy, które wyrażają relacje jeden do wielu. Starsze hierarchiczne bazy danych pozwalają na przynależność rekordu tylko do jednego zestawu. Sieciowe bazy danych pozwalają na przynależność rekordu do wielu zestawów.
Powiedzmy, że chcesz wyświetlić listę studentów zapisanych na kurs CS 101. Najpierw znalazłbyś "CS 101" w zestawie Courses według nazwy, ustaw to jako właściciela lub rodzica zestawu Enrollees, znajdź pierwszego członka (ffm) zestawu Enrollees, który jest rekordem Student, i wymień go. Następnie przeszedłbyś do pętli: Znajdź następnego członka (fnm) i wymień go. Kiedy fnm nie powiedzie się, opuścisz pętlę.
Może się to wydawać dużo pracy dla programisty bazy danych, ale było to bardzo wydajne w czasie wykonywania. Eksperci tacy jak Michael Stonebraker z Uniwersytetu Kalifornijskiego w Berkeley i Ingres zauważyli, że wykonanie tego rodzaju zapytania w bazie CODASYL, takiej jak IDMS, zajmowało mniej więcej połowę czasu procesora i mniej niż połowę pamięci, niż to samo zapytanie w relacyjnej bazie danych przy użyciu SQL.
Dla porównania, równoważne zapytanie SQL zwracające wszystkich studentów CS 101 byłoby czymś w rodzaju
SELECT student.name FROM courses, enrollees, students WHERE course.name ="CS 101"
Ta składnia implikuje relacyjne złączenie wewnętrzne (a właściwie dwa takie złączenia), jak wyjaśnię poniżej, i pomija pewne ważne szczegóły, takie jak pola używane w złączeniach.
Relacyjne bazy danych i SQL
Dlaczego miałbyś zrezygnować z dwukrotnej poprawy szybkości wykonywania i użycia pamięci? Istniały dwa ważne powody: łatwość rozwoju i przenośność. Nie sądziłem, że którykolwiek z nich miał w 1980 roku większe znaczenie niż wydajność i wymagania pamięciowe, ale w miarę jak sprzęt komputerowy stawał się coraz tańszy, ludzie przestawali przejmować się szybkością wykonywania i pamięcią, a bardziej kosztami rozwoju.
Innymi słowy, prawo Moore’a zabiło bazy CODASYL na rzecz relacyjnych baz danych. Tak się złożyło, że poprawa czasu tworzenia oprogramowania była znaczna, ale przenośność SQL okazała się mrzonką.
Skąd się wziął model relacyjny i SQL? E.F. „Ted” Codd był informatykiem z Laboratorium Badawczego IBM San Jose, który opracował teorię modelu relacyjnego w latach sześćdziesiątych i opublikował ją w 1970 roku. IBM ociągał się z wdrożeniem relacyjnej bazy danych, starając się chronić przychody swojej bazy CODASYL IMS/DB. Kiedy IBM w końcu rozpoczął projekt System R, zespół programistów (Don Chamberlin i Ray Boyce) nie podlegał Coddowi i zignorował dokument Codda z 1971 roku dotyczący języka relacyjnego Alpha, aby zaprojektować własny język, SEQUEL (Structured English Query Language). W 1979 roku, zanim jeszcze IBM wypuścił swój produkt, Larry Ellison włączył ten język do swojej bazy danych Oracle (używając jako specyfikacji publikacji IBM dotyczących SEQUEL sprzed premiery). SEQUEL wkrótce stał się SQL, aby uniknąć naruszenia międzynarodowego znaku towarowego.
„Tom-tomy bijące dla SQL” (jak to ujął Michael Stonebraker) pochodziły nie tylko od Oracle i IBM, ale także od klientów. Nie było łatwo zatrudnić lub wyszkolić projektantów i programistów baz danych CODASYL, więc SEQUEL (i SQL) wyglądał o wiele bardziej atrakcyjnie. SQL był tak atrakcyjny w późniejszych latach osiemdziesiątych, że wielu dostawców baz danych w zasadzie przykleiło procesor zapytań SQL do swoich baz CODASYL, ku wielkiemu przerażeniu Codda, który uważał, że relacyjne bazy danych muszą być zaprojektowane od podstaw, aby były relacyjne.
Czysta relacyjna baza danych, tak jak ją zaprojektował Codd, jest zbudowana na krotkach pogrupowanych w relacje, zgodne z logiką predykatów pierwszego rzędu. Prawdziwe relacyjne bazy danych posiadają tabele, które zawierają pola, ograniczenia i wyzwalacze, a tabele są powiązane poprzez klucze obce. SQL jest używany do deklarowania danych, które mają być zwrócone, a procesor zapytań SQL i optymalizator zapytań przekształcają deklarację SQL w plan zapytania, który jest wykonywany przez silnik bazy danych.
SQL zawiera podjęzyk do definiowania schematów, język definicji danych (DDL), wraz z podjęzykiem do modyfikowania danych, język manipulacji danymi (DML). Oba te języki wywodzą się z wczesnych specyfikacji CODASYL. Trzecim podjęzykiem w SQL jest deklarowanie zapytań, poprzez SELECT instrukcję i złączenia relacyjne.
Konstrukcja SELECT
Konstrukcja SELECT mówi optymalizatorowi zapytań, jakie dane zwrócić, w jakich tabelach szukać, jakie relacje śledzić i jaki porządek narzucić na zwrócone dane. Optymalizator zapytań musi sam dowiedzieć się jakich indeksów użyć, aby uniknąć brutalnego skanowania tabel i osiągnąć dobrą wydajność zapytania, chyba że dana baza danych obsługuje podpowiedzi indeksów.
Część sztuki projektowania relacyjnych baz danych polega na rozsądnym używaniu indeksów. Jeśli pominiesz indeks dla częstych zapytań, cała baza danych może zwolnić przy dużym obciążeniu odczytem. Jeśli masz zbyt wiele indeksów, cała baza danych może zwolnić przy dużym obciążeniu zapisem i aktualizacją.
Inną ważną sztuką jest wybór dobrego, unikalnego klucza głównego dla każdej tabeli. Musisz nie tylko rozważyć wpływ klucza głównego na typowe zapytania, ale także to, jak będzie on grał w złączeniach, gdy pojawi się jako klucz obcy w innej tabeli, oraz jak wpłynie na lokalność odniesienia danych.
W zaawansowanym przypadku tabel bazy danych, które są podzielone na różne objętości w zależności od wartości klucza głównego, zwanego shardingiem poziomym, musisz także rozważyć, jak klucz główny wpłynie na sharding. Wskazówka: Chcesz, aby tabela była rozłożona równomiernie na woluminy, co sugeruje, że nie chcesz używać datowników lub kolejnych liczb całkowitych jako kluczy podstawowych.
Dyskusja na temat oświadczenia SELECT może zacząć się prosto, ale szybko może stać się myląca. Rozważmy:
SELECT * FROM Customers;
Proste, prawda? Pyta o wszystkie pola i wszystkie wiersze tabeli Customers. Załóżmy jednak, że tabela Customers ma sto milionów wierszy i sto pól, a jedno z pól jest dużym polem tekstowym dla komentarzy. Jak długo zajmie ściągnięcie tych wszystkich danych przez połączenie sieciowe o przepustowości 10 megabitów na sekundę, jeśli każdy wiersz zawiera średnio 1 kilobajt danych? Rozważ:
SELECT TOP 100 companyName, lastSaleDate, lastSaleAmount, totalSalesAmount FROM Customers
WHERE state ="Ohio" AND city ="Cleveland"
ORDER BY lastSaleDate DESCENDING;
Teraz będziesz ściągał znacznie mniej danych. Poprosiłeś bazę danych o podanie tylko czterech pól, o uwzględnienie tylko firm w Cleveland i o podanie tylko 100 firm z najnowszą sprzedażą. Aby zrobić to najbardziej efektywnie na serwerze bazy danych, jednak, tabela Customers potrzebuje indeksu na state+city dla klauzuli WHERE klauzula i indeks na lastSaleDate dla klauzul ORDER BY i TOP 100.
Na marginesie, TOP 100 jest ważny dla SQL Server i SQL Azure, ale nie MySQL lub Oracle. W MySQL użyłbyś LIMIT 100 po klauzuli WHERE. W Oracle użyłbyś bound na ROWNUM jako część klauzuli WHERE, czyli WHERE... AND ROWNUM <=100. Niestety, standardy ANSI/ISO SQL (a jest ich do tej pory dziewięć, rozciągających się od 1986 do 2016 roku) sięgają tylko tak daleko, poza które każda baza danych wprowadza swoje własne, zastrzeżone klauzule i cechy.
SQL joins
Do tej pory opisałem składnię SELECT dla pojedynczych tabel. Zanim wyjaśnię JOIN klauzule, musisz zrozumieć klucze obce i relacje między tabelami. Wyjaśnię to na przykładach w DDL, używając składni SQL Server.
Krótka wersja tego jest dość prosta. Każda tabela, którą chcesz wykorzystać w relacjach, powinna mieć ograniczenie klucza podstawowego; może to być pojedyncze pole lub kombinacja pól określona przez wyrażenie. Na przykład:
CREATE TABLE Persons (
PersonID int NOT NULL PRIMARY KEY,
PersonName char(80),
...
Każda tabela, która musi odnosić się do Persons powinna mieć pole, które odpowiada kluczowi głównemu Persons i aby zachować integralność relacyjną, pole to powinno mieć ograniczenie klucza obcego. Na przykład:
CREATE TABLE Orders (
OrderID int NOT NULL PRIMARY KEY,
...
PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);
Istnieją dłuższe wersje obu stwierdzeń, które używają słowa kluczowego CONSTRAINT, które pozwala na nazwanie ograniczenia. To właśnie generuje większość narzędzi do projektowania baz danych.
Klucze główne są zawsze indeksowane i unikalne (wartości pola nie mogą być powielane). Inne pola mogą być opcjonalnie indeksowane. Często przydatne jest tworzenie indeksów dla pól kluczy obcych oraz dla pól, które pojawiają się w klauzulach WHERE i ORDER BY, choć nie zawsze, ze względu na potencjalny narzut związany z zapisami i aktualizacjami.
Jak napisałbyś zapytanie, które zwraca wszystkie zamówienia złożone przez Johna Doe?
SELECT PersonName, OrderID FROM Persons
INNER JOIN Orders ON Persons.PersonID = Orders.PersonID
WHERE PersonName ="John Doe";
W rzeczywistości istnieją cztery rodzaje JOININNEROUTERLEFT, oraz RIGHTINNER JOIN jest domyślny (możesz pominąć słowo INNER) i jest to ten, który zawiera tylko wiersze zawierające pasujące wartości w obu tabelach. Jeśli chcesz wyświetlić listę osób, które złożyły lub nie złożyły zamówienia, użyjesz LEFT JOIN, na przykład:
SELECT PersonName, OrderID FROM Persons
LEFT JOIN Orders ON Persons.PersonID = Orders.PersonID
ORDER BY PersonName;
Gdy zaczniesz wykonywać zapytania, które łączą więcej niż dwie tabele, które używają wyrażeń lub które wymuszają typy danych, składnia może stać się nieco trudna na początku. Na szczęście istnieją narzędzia do tworzenia baz danych, które mogą wygenerować poprawne zapytania SQL, często poprzez przeciąganie i upuszczanie tabel i pól ze schematu do schematu zapytania.
Procedury składowane w bazie danych
Czasami deklaratywna natura SELECT oświadczenia nie pozwala dotrzeć tam, gdzie chcesz. Niestety jest to obszar, w którym prawie wszystkie bazy danych używają własnych rozszerzeń standardów ANSI/ISO SQL.
W SQL Server, początkowym dialektem dla procedur składowanych (lub stored procs) był Transact-SQL, aka T-SQL; w Oracle, był to PL-SQL. Obie bazy danych dodały dodatkowe języki dla procedur składowanych, takie jak C#, Java i R. Prosta procedura składowana T-SQL może być jedynie sparametryzowaną wersją instrukcji SELECT. Jej zaletą jest łatwość użycia i wydajność. Procedury składowane są optymalizowane w momencie ich zapisywania, a nie za każdym razem, gdy są wykonywane.
A bardziej skomplikowana procedura składowana T-SQL może wykorzystywać wiele instrukcji SQL, parametry wejściowe i wyjściowe, zmienne lokalne, BEGIN...END bloki, IF...THEN...ELSE warunki, kursory (przetwarzanie zbioru wiersz po wierszu), wyrażenia, tabele tymczasowe i całą masę innej składni proceduralnej. Oczywiście, jeśli językiem procedury składowanej jest C#, Java lub R, będziesz używał funkcji i składni tych języków proceduralnych. Innymi słowy, pomimo tego, że motywacją dla SQL było użycie standardowych, deklaratywnych zapytań, w prawdziwym świecie można zaobserwować wiele specyficznego dla bazy danych, proceduralnego programowania serwera.
To nie do końca przenosi nas z powrotem do starych, złych czasów programowania baz danych CODASYL (chociaż kursory są blisko), ale odchodzi od idei, że instrukcje SQL powinny być ustandaryzowane, a kwestie wydajności powinny być pozostawione optymalizatorowi zapytań do bazy danych. W końcu, podwojenie wydajności to często zbyt wiele, aby zostawić to na stole.
Nauka SQL
Poniższe strony internetowe mogą pomóc w nauce SQL, lub odkryć dziwactwa różnych dialektów SQL.
- Codecademy. Naucz się SQL. Darmowa i interaktywna. Pro upgrade dostępny za opłatą.
- Khan Academy. Wprowadzenie do SQL: Zapytania i zarządzanie danymi. Darmowy film instruktażowy.
- SoloLearn. Podstawy SQL. Zorientowany na MySQL. Darmowy.
- SQL Problems and Solutions oraz SQL Exercises. Interaktywny podręcznik i ćwiczenia. Darmowy.
- SQLZoo. Interaktywny samouczek SQL opracowany i utrzymywany przez Edinburgh Napier University. Obsługuje Microsoft SQL Server, Oracle Database, MySQL, IBM DB2 oraz PostgreSQL. Darmowy.
- Tutorialspoint. Naucz się SQL. Tylko tekst, nie interaktywny. Darmowy.
- Udacity. Wprowadzenie do relacyjnych baz danych. Wykorzystuje Pythona i wymaga znajomości Pythona. Darmowy.
- Udemy. Darmowe kursy obejmują Wprowadzenie do baz danych i zapytań SQL, MySQL Database for Beginners, Microsoft SQL for Beginners, Hands-on SQL for Beginners (
SELECTFROM, iWHERE), i Sachin Quickly Learns (SQL). - Vertabelo Academy. Podstawy SQL, Operowanie na danych w SQL, Tworzenie tabel w SQL i osiem innych interaktywnych kursów SQL. Niektóre kursy mają darmową jazdę próbną, po której może być pobierana opłata. Istnieje siedem dodatkowych kursów dla Microsoft SQL Server. Strona posiada również graficzne narzędzie do projektowania baz danych dla PostgreSQL, MySQL, Oracle Database, SQL Server, SQLite i IBM DB2.
- W3Schools. SQL Tutorial. Darmowy i dostępny bez rejestracji.
Dokumentacja baz danych: