SQL - notatki z wykładu część pierwsza PDF

Preview

Summary

dr Henryk TelegaJęzyk SQL cz.SQL – Structured Query Language.Wprowadzenie, historia powstania, standardy. 1970 – praca F. Codd ’a „A Relational Model of Data for Large Shared Data Banks” , wydana w czasopiśmie organizacji Association for Computing Machinery: Communications of the ACM , Volume 13, N...

Description

Materiały do nauki języka SQL w systemie Microsoft SQL Server dr Henryk Telega

Język SQL cz.1. SQL – Structured Query Language. Wprowadzenie, historia powstania, standardy. 







 

        

1970 – praca F.E. Codd’a „A Relational Model of Data for Large Shared Data Banks”, wydana w czasopiśmie organizacji Association for Computing Machinery: Communications of the ACM, Volume 13, Number 6, June 1970. Lata siedemdziesiąte XX wieku – rozwój teorii relacyjnych baz danych. Relacyjny model danych. Algebra relacji (relational algebra) oraz relacyjny rachunek krotek (tuple relational calculus). Lata siedemdziesiąte XX wieku – powstanie języka SEQUEL (Structured English Query Language), autorzy: Donald D. Chamberlin i Raymond F. Boyce w IBM San Jose Research Laboratory. Cel: obsługa systemu baz danych System R (IBM). Ostatecznie nazwa języka została zmieniona na SQL (ze względu na to, że SEQUEL był już nazwą zastrzeżoną, należącą do pewnej firmy brytyjskiej). Lata siedemdziesiąte XX wieku – powstanie pierwszych systemów baz danych opartych o model relacyjny, m.in. w 1979 Oracle – pierwszy komercyjny relacyjny system zarządzania bazami danych implementujący język SQL. 1986 – standard SQL-86, przyjęty w USA przez ANSI. 1987 – standard przyjęty przez międzynarodową organizację normalizacyjną ISO (w jej skład wchodzą krajowe komitety/organizacje normalizacyjne, m.in. ANSI, polski PKN, niemiecki DIN). 1989 – standard SQL-89. 1992 – standard SQL-92 („rdzeń języka SQL”, znany też jako SQL2). 1999 – standard SQL:1999 (znany tez jako SQL3, wprowadzono m.in. elementy proceduralne, wyzwalacze, kwerendy rekurencyjne). 2003 – standard SQL:2003 (wprowadzono m.in. funkcje okien, elementy XML, zestandaryzowano automatyczne numerowanie poprzez sekwencje). 2006 – standard SQL:2006 (wprowadzono m.in. pełną obsługę XML, integrację z XQuery). 2008 – standard SQL:2008 (m.in. wyzwalacze INSTEAD OF, zdanie TRUNCATE). 2011 – standard SQL:2011 (m.in. rozszerzenia funkcji okien). 2016 – standard SQL:2016 (m.in. dodano obsługę formatu JSON). 2019 – standard SQL:2019 (dodano rozdział 15 Multidimesional Arrays SQL/MDA).

Obecnie SQL jest standaryzowany przez komitet ISO/IEC JTC 1, Information technology, Subcommittee SC 32, Data management and interchange. Jest to komitet współtworzony przez ISO oraz IEC (International Electrotechnical Commision) w ramach Joint technical Commitee ISO/IEC JTC 1. Prace cały czas trwają.

Materiały do nauki języka SQL w systemie Microsoft SQL Server dr Henryk Telega

Standard SQL podzielony jest na 10 zasadniczych części oznaczonych jako: ISO/IEC 9075-1:2016 Part 1, ISO/IEC 9075-2:2016 Part 2, ISO/IEC 9075-4:2016 Part 4 itd. (numery części nie są kolejnymi liczbami całkowitymi), Part 9, Part 10, Part 11, Part 13, Part 14 oraz ISO/IEC 9075-15:2019 Part 15. Ponadto są dodatkowe standardy, np. opisujące obsługę wideo, audio, danych przestrzennych (ISO/IEC 13249 SQL Multimedia and Application Packages) oraz szereg innych, np. w ISO/IEC 14651 określono co to jest collation („a process by which two strings are determined to be in exactly one of the relationships of less than, greater than, or equal to one another”). Opis standardu składa się zatem z wielu tomów i zajmuje tysiące stron. Jest publicznie dostępny, ale nie jest darmowy. Poszczególne części można kupić w ISO lub IEC (koszt jednej części jest rzędu 100-200 euro). Za darmo można pobrać cały standard SQL:2006 (http://www.wiscorp.com/sql200n.zip). Oficjalny opis standardu nie jest podręcznikiem i w praktyce nie nadaje się jako podstawowy dokument przydatny do nauki języka SQL. Implementacje języka SQL różnią się w zależności od producenta i obejmują wybrane części różnych standardów. Różne implementacje nie są ze sobą w pełni kompatybilne, chociaż jest wiele wspólnych elementów. Żaden z popularnych systemów zarządzania bazami danych nie implementuje w całości żadnego standardu(!). Powody tego stanu rzeczy są różne, np.:  Złożoność standardu SQL.  Standard nie określa różnych ważnych zagadnień, np. indeksów, organizacji plików.  Standard SQL dokładnie określa składnię, którą musi zaimplementować zgodny system baz danych. Jednak specyfikacja semantyki konstrukcji językowych w normie jest mniej precyzyjnie zdefiniowana, co prowadzi do niejednoznaczności.  Producenci różnych systemów baz danych mogą chcieć przywiązywać klientów do swoich rozwiązań i utrudniać zmianę systemu na inny.  Użytkownicy oceniający oprogramowanie bazodanowe mają tendencję do umieszczania innych czynników, takich jak wydajność, wyżej w swoich priorytetach niż zgodność ze standardami. Microsoft w systemie o nazwie SQL Serwer nazywa swoją implementację języka SQL – Transact SQL (często spotykane są skróty TSQL, T-SQL).

Podstawy, słowa kluczowe, składnia, zdanie SELECT. Wielkość liter w słowach kluczowych w języku SQL nie ma znaczenia, chociaż zwyczajowo na ogół używane są duże litery. Wielkość liter w nazwach obiektów (np. baz danych, tabel, kolumn) w systemie SQL Serwer (w innych systemach też) może mieć znaczenie lub nie. W Microsoft SQL Serwerze mówi o tym opcja konfiguracyjna o nazwie collation (może być ustawiona niezależnie na poziomie

Materiały do nauki języka SQL w systemie Microsoft SQL Server dr Henryk Telega

całego serwera, pojedynczej bazy danych lub nawet pojedynczej kolumny). Jeśli collation na poziomie całego serwera uwzględnia rozróżnianie wielkich i małych liter (mówią o tym litery CS – Case Sensitive w nazwie collation), wówczas w nazwach baz danych istotna będzie wielkość liter. Podobnie, jeśli collation w bazie danych uwzględnia rozróżnianie wielkich i małych liter, wówczas ma znaczenie wielkość liter w nazwach tabel i innych obiektów. Podobnie wielkość liter w danych w systemie SQL Serwer (i innych też) może mieć znaczenie lub nie. W systemie Microsoft SQL Serwer mówi o tym ustawienie COLLATION. W standardzie SQL określono, że instrukcje (zdania, ang. statements) powinny być zakończone średnikiem. W systemie SQL Serwer średnik kończący nie jest wymagany (ale jest polecany). Instrukcje następujące po sobie mogą być ujęte w tzw. wsad (ang. batch). Instrukcje z jednego wsadu kompilowane są razem i wyznaczany jest plan wykonania każdej z nich, a także szacowany jest względny koszt każdej instrukcji (podawany w procentach, 100% = cały wsad). W programie SQL Serwer Management Studio (SSMS) wsad stanowią instrukcje, które zostały zaznaczone myszką lub instrukcje zakończone słowem GO (jest to ogranicznik wsadu). Uwaga. Ogranicznik wsadu można w programie SSMS zmienić w opcjach z domyślnego GO na inny napis (Tools->Options->Query Execution->SQL Server->General->Batch separator). Komentarze (dwa sposoby: /* komentarz */ oraz --komentarz) /* SELECT * FROM Products; SELECT * FROM Customers; -- dane klientow */ Wyświetlenie wszystkich kolumn z tabeli SELECT * FROM Products; SELECT * FROM Customers;

UWAGA! Stosowanie symbolu gwiazdki w zdaniu SELECT nie jest polecane! Proszę zdecydowanie unikać tej konstrukcji poza środowiskiem testowym. Po słowie SELECT należy wymienić tylko te kolumny, które są niezbędne. Wyświetlanie wszystkich kolumn może powodować znaczne (niepotrzebne) wydłużenie czasu przetwarzania, ponadto użycie symbolu * może spowodować nieprzewidziane problemy, np. w sytuacji, kiedy po pewnym czasie używania aplikacji, do tabeli zostanie dodana nowa kolumna (lub któraś zostanie usunięta). Aplikacja może wówczas przestać poprawnie działać. Wyświetlenie wybranych trzech z tabeli SELECT CustomerID, CompanyName, Region FROM Customers;

Jeśli w nazwie tabeli lub kolumny są znaki specjalne, np. spacja, /, #, to nazwa powinna być ujęta w nawiasy prostokątne lub cudzysłów: SELECT * FROM [Order Details] SELECT * FROM "Order Details"

Materiały do nauki języka SQL w systemie Microsoft SQL Server dr Henryk Telega

Uwaga. Ujmowanie nazw obiektów w cudzysłów działa poprawnie tylko, jeśli ustawiona została opcja QUOTED_IDENTIFIERS na ON SET QUOTED_IDENTIFIER { ON | OFF }

Kolumny wyliczane SELECT CustomerID, CompanyName, Region, -- kolumny z tabeli Customers LEFT(CompanyName,1) AS [First Letter], 3.14*2 AS Number, GETDATE() - kolumny wyliczane FROM Customers

„Rdzeń” języka SQL jest prawie taki sam we wszystkich systemach. Jednak systemy mogą się różnić na przykład dostępnymi funkcjami wbudowanymi. Funkcja GETDATE() jest dostępna w systemie Microsoft SQL Serwer, ale nie ma jej np. w systemie Oracle (i wielu innych). Podobnie jest z innymi funkcjami. Funkcja GETDATE() zwraca datę z godziną, z dokładnością do milisekundy. AS – „Alias”, tymczasowa nazwa kolumny (napis wyświetlany u góry kolumny). Jeśli dla kolumny wyliczanej (na podstawie pewnego wzoru, z wykorzystaniem funkcji itp.) nie zdefiniujemy aliasu, to w programie Management Studio nad kolumną pojawi się napis „No column name”. W większości systemów (ale nie we wszystkich) słowo kluczowe AS jest opcjonalne, można je pominąć, wystarczy wstawić odstęp tak jak w przykładzie poniżej. SELECT CustomerID, CompanyName, Region, LEFT(CompanyName,1) [First Letter], 3.14*2 Number, GETDATE() FROM Customers

W systemie Microsoft SQL Serwer zdanie SELECT nie musi mieć klauzuli FROM (nie we wszystkich systemach tak jest). SELECT SELECT SELECT SELECT

3*5 GETDATE() SUSER_SNAME() -- wyświetla bieżący login CURRENT_USER -- wyświetla nazwę bieżącego użytkownika w bazie danych

Uwaga. W systemie Microsoft SQL Serwer po zalogowaniu jesteśmy rozpoznawani (w ramach całego serwera) jako pewien login. Przy dostępie do konkretnej bazy danych jesteśmy w niej postrzegani jako użytkownik (user, NIE login). Jeśli pewien login ma uprawnienia do łączenia się z konkretną bazą danych, to jest w niej rozpoznawany jako użytkownik ( user) o nazwie, która może być taka sama jak login, ale nie musi.

Klauzula WHERE, typy danych. Klauzula WHERE służy do wyboru rekordów (wierszy) z tabeli. Po słowie WHERE należy wpisać wyrażenie logiczne. W języku SQL wyrażenie to może mieć jedną z trzech wartości: prawda (true), fałsz (false) oraz wartość nieznana (unknown, reprezentowana przez NULL). W systemie Microsoft SQL Serwer słowa true, false ani unknown nie są wprost używane, chociaż wyrażenia logiczne zwracają takie wartości. Nie można jednak bezpośrednio wyświetlić wartości wyrażenia logicznego.

Materiały do nauki języka SQL w systemie Microsoft SQL Server dr Henryk Telega

W zależności od typu danych, wyrażenia logiczne można konstruować z użyciem różnych operatorów. Typy danych. Tekstowe: CHAR, VARCHAR, NCHAR, NVARCHAR, TEXT, NTEXT (Microsoft zapowiada wycofanie typów TEXT i NTEXT w przyszłych wersjach systemu; zamiast nich powinno się używać odpowiednio VARCHAR(MAX) i NVARCHAR(MAX)). Numeryczne przybliżone (nie wszystkie liczby precyzyjnie zapisane w systemie dziesiętnym da się zapisać w typie przybliżonym): FLOAT, REAL (=FLOAT(24)), DOUBLE PRECISION (=FLOAT(53)). Numeryczne dokładne: INT, TINYINT, SMALLINT, BIGINT, NUMERIC (DECIMAL), MONEY, SMALLMONEY, BIT. Binarne: BINARY, VARBINARY, IMAGE (Microsoft zapowiada wycofanie typu IMAGE w przyszłych wersjach systemu; zamiast powinno się używać VARBINARY(MAX)). Daty i czasu: DATE, DATETIME, TIME, DATETIME2, SMALLDATETIME, DATETIMEOFFSET. Inne: XML, UNIQUEIDENTIFIER, ROWVERSION, SQL-VARIANT, HIERARCHYID, GEOMETRY, GEOGRAPHY, TABLE, CURSOR. Typy danych w systemie Microsoft SQL Serwer 2019: https://docs.microsoft.com/en-us/sql/tsql/data-types/data-types-transact-sql?view=sql-server-ver15. W wyrażeniach z różnymi typami danych następuje niejawna konwersja (o ile jest możliwa), przy czym dane o typie z niższym priorytetem zamieniane są na dane o typie z wyższym priorytetem. Priorytety typów danych opisane są tu: https://docs.microsoft.com/en-us/sql/tsql/data-types/data-type-precedence-transact-sql?view=sql-server-ver15. Dla prawie wszystkich typów danych (z wyjątkiem text, ntext i image) można używać następujących operatorów porównania: > (większe), < (mniejsze, >= (większe lub równe), (nie większe od; operator niezgodny ze standardem ISO). Fragment z dokumentacji Microsoft:

Materiały do nauki języka SQL w systemie Microsoft SQL Server dr Henryk Telega The result of a comparison operator has the Boolean data type. This has three values: TRUE, FALSE, and UNKNOWN. Expressions that return a Boolean data type are known as Boolean expressions. Unlike other SQL Server data types, a Boolean data type cannot be specified as the data type of a table column or variable and cannot be returned in a result set. When SET ANSI_NULLS is ON, an operator that has one or two NULL expressions returns UNKNOWN. When SET ANSI_NULLS is OFF, the same rules apply, except for the equals (=) and not equals () operators. When SET ANSI_NULLS is OFF, these operators treat NULL as a known value, equivalent to any other NULL, and only return TRUE or FALSE (never UNKNOWN).

Przykłady: SELECT * FROM Products WHERE UnitPrice >= 20 SELECT CustomerID, CompanyName, Region FROM Customers WHERE Country = 'Poland' SELECT CustomerID, CompanyName, Region, Country FROM Customers WHERE Country = 'Germany'

W systemie Microsoft SQL Server literał typu NVARCHAR() oraz NCHAR() (napis) zapisuje się z dużą literą N przed znakiem apostrofu. Jeśli się tej litery nie wpisze, następuje automatyczna konwersja (o ile jest możliwa). Uwaga! Należy unikać konwersji automatycznych, jeśli miałyby być wykonywane wielokrotnie dla jednej instrukcji SQL (np. w bardzo wielu rekordach), ponieważ konwersja taka wpływa na wydajność kwerend. Konwersja nie jest jednak „szkodliwa”, jeśli wykonywana jest „niewiele” razy (np. kilka, kilkanaście). SELECT CustomerID, CompanyName, Region, Country FROM Customers WHERE Country = N'Germany'

Operator LIKE. Dla typów znakowych często używanym operatorem jest operator LIKE. Operator LIKE oznacza dopasowanie napisu do wzorca. We wzorcach można używać znaków specjalnych (tzw. metaznaków). W większości systemów baz danych rozpoznawane są następujące dwa metaznaki: % - oznacza dowolny ciąg znaków. _ (podkreślenie) – oznacza jeden dowolny znak. W systemie Microsoft SQL Server wykorzystywane są jeszcze inne wzorce. Niektóre systemy umożliwiają wykorzystanie klasycznych wyrażeń regularnych – regular expressions, regex (prosty, krótki tutorial: https://medium.com/factory-mind/regex-tutoriala-simple-cheatsheet-by-examples-649dc1c3f285, bardziej rozbudowany: https://blog.usejournal.com/regular-expressions-a-complete-beginners-tutorialc7327b9fd8eb).

Materiały do nauki języka SQL w systemie Microsoft SQL Server dr Henryk Telega

Na przykład w systemie Oracle można wykorzystać funkcję REGEXP_LIKE(), podobnie jest w MySQL (w MySQL można wykorzystać operator REGEXP. W języku Transact SQL nie ma takiej możliwości. W systemie SQL Server w operator LIKE rozpoznaje dodatkowo poniższe wzorce. [ac,x] – znaki umieszczone w nawiasach prostokątnych dopasowywane są do jednego znaku w danych, przy czym np. [ac,x] oznacza dopasowanie litery a, litery c, x oraz przecinka. [b-d] – dopasowanie jednego znaku, przy czym w konkretnym przypadku [b-d] dopasowanie następuje, jeśli w danych w odpowiednim miejscu występuje znak z zakresu od b do d włącznie. [^adk-m] – dopasowanie jednego znaku, przy czym znak ^ na początku listy znaków w nawiasach prostokątnych oznacza zaprzeczenie; w tym konkretnym przypadku wzorzec oznacza dopasowanie jednego znaku jeśli jest on inny niż litery a, d, oraz liter z zakresu k-m. [%], [_], [^] umieszczenie jednego specjalnego znaku w nawiasach prostokątnych (%, _, ^) anuluje specjalne znaczenie tego znaku. Znak kasujący specjalne znaczenie innego znaku może być samodzielnie zdefiniowany po słowie ESCAPE. Przykład: WHERE comment LIKE '%30!%%' ESCAPE '!'

oznacza dopasowanie tych rekordów, w których w kolumnie o nazwie comment znajduje się napis 30%. Specjalne znaczenie znaku % zostało tu anulowane przez znak !. Takie samo znaczenie miałoby kryterium: WHERE comment LIKE '%30x%%' ESCAPE 'x'

Przykłady. SELECT * FROM Customers WHERE CompanyName LIKE N'A%' -- Klienci o nazwie rozpoczynającej się na literę A SELECT * FROM Customers WHERE CompanyName LIKE N'_a%a' -- Klienci, których druga i ostatnia litera nazwy to a SELECT * FROM Products WHERE ProductName LIKE N'[a-cnp-sz]%' -- wszystkie produkty o nazwach rozpoczynających się na litery z zakresu od a do c lub literę n lub litery z zakresu od p do s lub literę z. SELECT * FROM Products WHERE ProductName LIKE '[^a-c]%' -- produkty o nazwach zaczynających się na litery inne niż z zakresu od a do c. SELECT * FROM Products WHERE Code LIKE '[1-9]_[a-ż][a-ż][_][^a]' – produkty, których kod rozpoczyna się od cyfry (1 do 9), potem może być dowolny jeden znak, potem muszą być dwie litery (z zakresu od a do ż), potem musi być znak podkreślenia (bo znak _ jest umieszczony w nawiasach prostokątnych) a na końcu musi być dowolny znak różny od litery a. Przykład dopasowania: '2$cp_b'

Operatory porównania mogą być stosowane do typów tekstowych, jednak należy zwrócić uwagę na specyfikę ich działania. Na przykład wyrażenie 'BBB' > 'AAA' jest prawdziwe, ale prawdziwe jest również wyrażenie

Materiały do nauki języka SQL w systemie Microsoft SQL Server dr Henryk Telega 'AAA' > 'A', ponieważ napisy są ułożone w tzw. porządku leksykograficznym, wyznaczonym

przez wykorzystywany zestaw znaków oraz ustawienia, związane z sortowaniem (collation, ustalane na poziomie bazy danych, tabeli lub kolumny). Zatem, jeśli chcemy wyświetlić klientów o nazwach rozpoczynających się na B lub późniejszych, NIE powinniśmy wpisywać takiego kryterium: SELECT * FROM Customers WHERE CompanyName > 'A'

gdyż spowoduje ono wyświetlenie danych klienta np. o nazwie 'Alfreds' – ten napis jest w porządku leksykograficznym po (operator >) napisie składającym się tylko z litery A. Lepiej wpisać to tak: SELECT * FROM Customers WHERE CompanyName >= 'B'

Operatory porównania mogą być stosowane w stosunku do dat, przy czym daty wcześniejsze są traktowane tak jak „mniejsze liczby”. Zatem porządek rosnący jest od dat wcześniejszych do późniejszych. Na przykład wyrażenie: '2020-09-10' > '2020-09-09'

jest prawdziwe.

Uwaga. Należy zachować ostrożność przy stosowaniu operatora LIKE w stosunku do innych typów danych niż teksty (np. do liczb albo dat). Generalnie można ten operator zastosować, ale jego działanie może być „nieintuicyjne” i inne w różnych systemach baz danych. Na przykład w przypadku zastosowania operatora LIKE do liczby, liczba zamieniana jest na tekst, czyli poniższe kryterium wyświetli produkty o cenach zaczynających się na cyfrę 1 i w środku napisu (lub na końcu) musi być cyfra 2: SELECT * FROM Products WHERE UnitPrice Like '1%2%'

Inny przykład (należy pamiętać, że w języku SQL znak rozdzielający część całkowitą liczby od części ułamkowej to zawsze kropka (NIE przecinek): SELECT * FROM Products WHERE UnitPrice Like '1%.0%'

Operator LIKE nie działa „poprawnie” do dat, na przykład, żeby wyświetlić zamówienia z 1996 roku NIE można napisać tak: SELECT * FROM Orders WHERE OrderDate LIKE '1996%'

W systemie Microsoft SQL Server można na przykład użyć funkcji YEAR( ), która zwraca liczbę całkowitą – numer roku od daty podanej jako argument funkcji. W innych systemach baz danych są jednak na ogół inne funkcje operujące na datach (różne w różnych systemach).

Materiały do nauki języka SQL w systemie Microsoft SQL Server dr Henryk Telega SELECT * FROM Orders WHERE YEAR(OrderDate) = 1996

Bardziej uniwersalnym (działającym we wszystkich systemach) rozwiązaniem będzie jednak użycie złożonego kryterium WHERE: SELECT * FROM Orders WHERE OrderDate >= '1996-01-01' AND OrderDate = 20 AND UnitPrice < 30 --To jest źle: SELECT * FROM Products WHERE UnitPrice >= 20 AND < 30 --Poniżej wynikiem będzie pusty zestaw wierszy --(żaden produkt nie spełnia takiego kryterium WHERE): SELECT * FROM Products WHERE UnitPrice = 30

Materiały do nauki języka SQL w systemie Microsoft SQL Server dr Henryk Telega SELECT * FROM Products WHERE UnitPrice >= 20 AND UnitPrice NULL równa się NULL, 3=NULL równa s...