SQL – Strukturalny język zapytań

1. SQL – Strukturalny język zapytań Wprowadzenie do systemów baz danych

2. Historia • Prace nad językiem rozpoczęto w 1982 roku • 1986 – standard ANSI (American National Standards Committee) – SQL-86 • 1987 – standard ISO (International Standards Organization) – SQL-87 (SQL1) • 1992 – SQL-92 (SQL2) • 1999 – SQL-99 (SQL3) • 2003 – SQL2003

3. Funkcje języka • Język definiowania danych (ang. Data Definition Language – DDL) • Definiowanie struktury bazy danych • Kontrola integralności • Język zarządzania (manipulowania) danymi (ang. Data Manipulation Language – DML) • Wprowadzanie, modyfikacja i kasowanie danych • Przeszukiwanie danych • Język kontroli danych (ang. Data Control Language – DCL) • Nadawanie i odbieranie uprawnień do korzystania z bazy danych

4. Typy danych - napisowe • CHARACTER(n) – tablica znaków o stałej długości n • Skrót CHAR • CHARACTER VARYING(n) – tablica znaków o zmiennej długości – liczba n określa długość maksymalną • Skróty: VARCHAR, CHAR VARYING • NATIONAL CHARACTER(n) • Skróty: NATIONAL CHAR, NCHAR • NATIONAL CHARACTER VARYING(n) • Skróty: NATIONAL CHAR VARYING, NCHAR VARYING, NVARCHAR

5. Typy danych - liczbowe • EXACT NUMERIC (stałoprzecinkowe) • INTEGER – liczba całkowita • NUMERIC(m,n) – liczba dziesiętna o długości m z n miejscami po przecinku (m – precision, n – scale) • DECIMAL(m,n) – liczba dziesiętna o długości m z n miejscami po przecinku (rzeczywista długość liczby może być większa od n) • APPROXIMATE NUMERIC (zmiennoprzecinkowe) • FLOAT[(m)] – liczba zmiennoprzecinkowa • REAL – liczba zmiennoprzecinkowa (krótka) • DOUBLE PRECISION – liczba zmiennoprzecinkowa podwójnej precyzji

6. Typy danych określające czas • DATE – data • DATE '1999-12-12' - DATE '1900-01-01' = 36504 • TIME – czas • TIME '12:00:00' - TIME '15:10:00' = -03:10:00 • TIMESTAMP – data i czas (stempel czasowy) • TIMESTAMP '2001-01-01 12:20:10' • TIMESTAMP '2007-12-31 23:59:59' - TIMESTAMP '2007-01-01 00:00:00 ' = 364 days 23:59:59 = 364 23:59:59.0 • INTERVAL – różnica czasu • DATE '1900-01-01' + 36504 = 1999-12-12 • DATE '2007-01-01' + 1 YEAR = 07/01/02 (Oracle) • DATE '2007-01-01' + INTERVAL '1 YEAR' = 2008-01-01 00:00:00 (PostgreSQL) • DATE '0000-01-01' + INTERVAL '2001 YEAR 3 MONTH 10 DAY' = 2001-04-11 00:00:00 (PostgreSQL) • TIMESTAMP '2007-01-01 00:00:00' + INTERVAL '2 month 5 day 15:10:00' = 2007-03-06 15:10:00 (PostgreSQL) • Funkcje czasu i daty:CURRENT_DATE, CURRENT_TIME[()], CURRENT_TIMESTAMP[()]

7. Tworzenie tabeli • CREATE TABLE nazwa_tabeli(nazwa_kolumny typ_kolumny [ograniczenia_kolumny], nazwa_kolumny typ_kolumny [ograniczenia_kolumny],..............,[ograniczenia_tabeli])

8. Ograniczenia kolumny • PRIMARY KEY • NOT NULL • UNIQUE • CHECK (warunek) • REFERENCES tabela_zewnętrzna(nazwa_kolumny)[ON DELETE reakcja][ON UPDATE reakcja] • DEFAULT wartość • Aby wpisać wartość domyślną, w instrukcji INSERT INTO, należy wpisać DEFAULT

9. Ograniczenia tabeli • PRIMARY KEY (nazwa_kolumny [, ...]) • UNIQUE (nazwa_kolumny [, ...]) • CHECK (warunek) • FOREIGN KEY (nazwa_kolumny [, ...]) REFERENCES tabela(nazwa_kolumny [, ...]) [ON DELETE reakcja][ON UPDATE reakcja] • Ograniczenia tabeli można poprzedzić nazwą:CONSTRAINT Nazwa_ograniczenia Ograniczenie tabeli

10. Naruszenie integralności referencyjnej • Można określić reakcję systemu na próbę naruszenia zasad integralności referencyjnej poprzez usunięcie bądź zmianę wartości „klucza rodzica” w powiązanej tabeli • Reakcje to: • Odmowa wykonania modyfikacji (domyślna) – NO ACTION, RESTRICT • Kaskadowa aktualizacja wierszy powiązanych – CASCADE • Kaskadowe usunięcie wierszy powiązanych – CASCADE • Wstawienie do wierszy powiązanych wartości NULL – SET NULL • Wstawienie do wierszy powiązanych wartości domyślnej – SET DEFAULT

11. Klucz obcy – związek jeden do wiele Diagram standardu Integration DEFinition (IDEF1X) MS Access Diagram MS SQL Server

12. Ograniczenie CHECK • Ograniczenie CHECK powoduje sprawdzanie warunku podczas wprowadzania lub uaktualniania danych. • Operacja jest wykonywana, jeśli wartość logiczna warunku jest TRUE • Klauzula CHECK wymusza integralność dziedziny • Przykłady • CHECK (wzrost BETWEEN 50 AND 250) • CHECK kierunek IN ('KSS', 'SiST', 'EO', 'EM') • Warunek powinien być stały, nie zależeć od zmiennych, danych w innych tabelach ani od bieżącej daty (czasu) • Porównania wpisywanej daty z datą bieżącą można dokonać przez procedurę wyzwalaną

13. Odraczanie sprawdzania więzów • Więzy można zadeklarować jako możliwe do odroczenia DEFERRABLE lub jako niemożliwe do odroczenia NOT DEFERRABLE (wartość domyślna). • Odroczenie sprawdzania więzów możliwe jest na czas trwania transakcji i stosowane w przypadkach więzów zapętlonych. • Więzy odraczane można dodatkowo zadeklarować jako odraczane od początku INITIALLY DEFERRED lub natychmiastowe od początku INITIALLY IMMEDIATE • Określenie trybu sprawdzania ograniczeńSET CONSTRAINS {nazwa_ograniczenia | ALL}{DEFERRED | IMMEDIATE}

14. Przykład • CREATE TABLE studenci(pid CHAR(4) PRIMARY KEY,imię VARCHAR(15),nazwisko VARCHAR(15) NOT NULL,imię_ojca VARCHAR(15),semestr INTEGER CHECK (semestr<10)) • CREATE TABLE studenci1(id_studenta CHAR(4),nazwisko VARCHAR(25) NOT NULL,imie VARCHAR(25),imie_ojca VARCHAR(25),CONSTRAINT Klucz_glowny_studenci PRIMARYKEY(id_studenta));

15. Przykład • CREATE TABLE oceny(pid CHAR(4) REFERENCES studenci(pid),przedmiot VARCHAR(15),ocena VARCHAR(10),data DATE,PRIMARY KEY (pid, przedmiot))

16. Zmiana struktury bazy danych • ALTER TABLE nazwa_tabeliADD [COLUMN] nazwa_kolumny typ_kolumny • ALTER TABLE nazwa_tabeliALTER | RENAME [COLUMN]stara_nazwa TO nowa_nazwa • ALTER TABLE nazwa_tabeliDROP [COLUMN] nazwa_kolumny [RESTRICT | CASCADE ] • ALTER TABLE nazwa_tabeliADD definicja ograniczenia tabeli • ALTER TABLE nazwa_tabeliDROP CONSTRAINT nazwa_ograniczenia

17. Diagram (IDEF1X) prostej bazy danych i skrypt SQL tworzący tą bazę (PostgreSQL) Skrypt SQL tworzący tab. Oceny skrypt SQL tworzący bazę danych

18. Diagram (MS SQL Server) prostej bazy danych i skrypt SQL tworzący tą bazę (MS SQL Server) Skrypt SQL tworzący tab. oceny skrypt SQL tworzący bazę danych

19. Diagram prostej bazy danych HR (Oracle) skrypt SQL tworzący tabelęEMPLOYEES

20. Diagram prostej bazy danych HR (kurze łapki)

21. Narzędzia graficzne do tworzenia tabel baz danych SQL Server Enterprise Manager

22. Narzędzia graficzne do tworzenia tabel Oracle

23. Indeksy • Krotki są zapisywane w pliku w kolejności pojawiania się • Przeszukiwanie dużego pliku w poszukiwaniu krotki może zająć wiele czasu • W celu przyspieszenia wyszukiwania krotek, na podstawie wartości atrybutu, tworzy się indeks na tym atrybucie • Stosowanie indeksów przyspiesza wyszukiwanie danych, ale spowalnia ich zapis

24. Tworzenie indeksu • CREATE [UNIQUE] INDEX nazwa_indeksuON nazwa_tabeli(nazwa_kolumny [, ...]) • Przykłady: • CREATE UNIQUE INDEX indeks_studenciON studenci(nazwisko, imię) • CREATE INDEX indeks_przedmiotyON oceny(przedmiot)

25. Usuwanie tabel i indeksów • DROP TABLE nazwa_tabeli • DROP INDEX nazwa_indeksu • Przykłady: • DROP TABLE studenci • DROP INDEX indeks_studenci

26. Wprowadzanie danych do tabel • INSERT INTO nazwa_tabeli[(nazwa_kolumny [,..])]VALUES (wartość [, ...]) • INSERT INTO nazwa_tabeli[(nazwa_kolumny [,..])]zapytanie SELECT • Przykłady: • INSERT INTO studenciVALUES (’0001’, ’Jan’, ’Papkin’, ’Jan’) • INSERT INTO studenci(pid, nazwisko)VALUES (’0002’, ’Ramzes’) • INSERT INTO studenciSELECT pid, nazwisko, imie, imie_ojca FROM kandydaciWHERE … • W miejsce wartości można wpisać: DEFAULT lub NULL

27. Wybieranie danych • SELECT [DISTINCT]wyrażenie [AS nazwa_kolumny] [,...][FROM źródło_elementów][WHERE warunek][GROUP BY wyrażenie [,...]][HAVING warunek][{UNION | INTERSECT | EXCEPT} instrukcja_SELECT][ORDER BY wyrażenie [ASC | DESC]

28. Proste przykłady SELECT • SELECT * FROM studenci • SELECT * FROM ocenyWHERE pid = ’0001’ • SELECT * FROM studenci NATURAL JOIN oceny • SELECT * FROM studenci NATURAL JOIN ocenyWHERE pid='0017' • SELECT DISTINCT studenci.nazwisko, studenci.imie, oceny. przedmiot, oceny.ocena, oceny.dataFROM studenci, ocenyWHERE oceny. przedmiot = ‘OiSA'AND studenci.pid = oceny.pidORDER BY studenci.nazwisko

29. Klauzula SELECT • Klauzula SELECT określa wynikowe kolumny, i może zawierać • Nazwy kolumn wynikowych • Wyrażenia zawierające funkcje, nazwy kolumn i stałe • Stosując AS można nadać wynikowym kolumnom nazwy inne od domyślnych • Symbol * oznacza wszystkie kolumny • Dyrektywę DISTINCT likwidującą powtórzenia wierszy • Nazwy kolumn: • nazwa_tabeli.nazwa_kolumny • nazwa_kolumny – jeśli nazwa kolumny jednoznacznie określa tabelę, z której pochodzi (brak w źródle elementów kolumn o tych samych nazwach)

30. Wyrażenia arytmetyczne • Operatory: +, -, *, / • Funkcje matematyczne: ABS(x), POWER(x,y), SQRT(x), RAND() – generuje liczbę losową z zakresu od 0 do 1 • Funkcje zaokrąglania liczb: • CEILING(x) – funkcja zaokrągla swój argument w górę do najbliższej liczy całkowitej (Oracle – CEIL(x)) • FLOOR(x) – funkcja zaokrągla swój argument w dół do najbliższej liczy całkowitej • ROUND(x,n) – zaokrągla liczbę x do n miejsc po przecinku • Jeśli w wyrażeniu matematycznym choć jeden z argumentów jest NULL, to wynik tego wyrażenia zawsze jest NULL

31. Wyrażenia tekstowe (napisowe) • Łączenie (konkatenacja) łańcuchów • Operator konkatenacji || (Oracle, dB2, PostgreSQL) • 'tekst' || NULL = NULL • SELECT nazwisko || ' ' || imie FROM • Funkcja CONCAT(x,y) (Oracle, dB2, MySQL) • Operator + w MS SQL Server • Operatory: +, & w MS Access • 'tekst' + NULL = NULL • 'tekst' & NULL = 'tekst'

32. Funkcje tekstowe • LOWER(x), UPPER(x) • CHARACTER_LENGTH(x), LENGTH(x) • SUBSTRING(x FROM n FOR m) • POSITION(x1 IN x) • TRIM(ltb wzorzec FROM napis) • ltb – LEADING, TRAILING, BOTH • COALESCE(x1, x2, …) – funkcja zwraca pierwszy argument, który jest różny od NULL (używana przy łączeniu łańcuchów, z których jeden lub więcej może być NULL) x, x1, x2 – wyrażenie łańcuchowe (napis)

33. Konwersje typów • CAST(wyrażenie AS typ_danych) • CAST(ocena AS NUMERIC(2, 1))

34. CASE (2 formy) Forma 1 CASE lista klauzul WHEN ELSE wyrażenie skalarne END Klauzula WHEN ma postać: WHEN wyrażenie_warunkowe THEN wyrażenie_skalarne Forma 2 CASE wyrażenie_skalarne lista klauzul WHEN ELSE wyrażenie skalarne END Klauzula WHEN ma postać: WHEN wyrażenie_skalarne THEN wyrażenie_skalarne Klauzule WHEN są wykonywane kolejno, pierwszy spełniony warunek określa wynik końcowy i sprawdzanie klauzul zostaje zakończone

35. Przykład użycia CASE (2 formy) SELECT ocena, CASE WHEN ocena > 4 THEN 'gratuluję' WHEN ocena > 3 THEN 'dobrze' WHEN ocena > 2 THEN 'nienajlepiej' WHEN ocena = 2 THEN 'może następnym razem' END AS "komentarz 1", CASE ocena WHEN 5 THEN 'gratuluję' WHEN 4.5 THEN 'też gratuluję' WHEN 4 THEN 'dobrze' WHEN 3.5 THEN 'no średnio' WHEN 3 THEN 'nienajlepiej' ELSE 'może następnym razem' END AS "komentarz 2" FROM oceny

36. Funkcje agregujące • Funkcje agregujące wyznaczają jedną wartość z grupy wartości w kolumnie – grupy wierszy określa klauzula GROUP BY • COUNT – zlicz ilość wszystkich wartości (w kolumnie) różnych od NULL • COUNT(*) – zlicza wszystkie wiersze • AVG – oblicza średnią ze wszystkich wartości • SUM – sumuje wartości • MAX – zwraca wartość największą • MIN – zwraca wartość najmniejszą W przypadku użycia jednocześnie wyrażeń i funkcji agregujących wszystkie wyrażenia muszą wystąpić w klauzuli GROUP BY

37. Klauzula FROM • Klauzula FROM zawiera „wyrażenie tabelowe” określające źródło danych dla zapytania • Wyrażenie tabelowe klauzuli FROM może zawierać • Listę tabel i perspektyw źródłowych • A, B – oznacza iloczyn kartezjański (złączenie krzyżowe) tabel A i B • A, B = A CROSS JOIN B • Złączenia tabel • A NATURAL [typ] JOIN B – złączenie naturalne (po kolumnach o tych samych nazwach w obu tabelach) • A [typ] JOIN B ON warunek • A [typ] JOIN B USING (kolumna [,...]) • Typy złączeń zewnętrznych:{LEFT | RIGHT | FULL} [OUTER] • Podzapytania – umieszczone w nawiasach i z nadanymi aliasami • Tabelom można nadać zastępcze nazwy nazywane: aliasami, nazwami skorelowanymi i zmiennymi zakresu • format: nazwa_tabeli_źródłowej [AS] alias • nadawanie aliasów tabelom źródłowym stosuje się przy „samozłączeniach” tabel • alias trzeba nadać wynikowi podzapytania, które może być użyte w zastępstwie tabeli

38. Przykład samozłączenia select "PRACOWNICY"."FIRST_NAME" as "Imię", "PRACOWNICY"."LAST_NAME" as "Nazwisko", "SZEFOWIE"."FIRST_NAME" as "Imię(szefa)", "SZEFOWIE"."LAST_NAME" as "Nazwisko(szefa)" from "EMPLOYEES" "PRACOWNICY" LEFT JOIN "EMPLOYEES" "SZEFOWIE" ON "PRACOWNICY"."MANAGER_ID"="SZEFOWIE"."EMPLOYEE_ID"

39. Klauzula WHERE • Zawiera warunek selekcji wierszy tabeli wynikowej nazywany często wyrażeniem ograniczającym • Warunek (predykat) jest wyrażeniem logicznym (funkcją zdaniową) – w tabeli wynikowej znajdą się tylko wiersze spełniające warunek • Predykat może zawierać: nazwy kolumn, wyrażenia logiczne, operatory porównań, funkcje i zagnieżdżone instrukcje SELECT (podzapytania) • Predykaty mogą mieć wartość logiczną TRUE, FALSE lub NULL • Na logicznym rachunku predykatów opiera się rachunek relacyjny – w przypadku języka SQL jest to rachunek relacyjny na krotkach

40. Predykaty - przykłady • Porównania: =, <, >, <=, >=, <> • a >= b; a <b; itp. • BETWEEN: x BETWEEN 23 AND 143  x >= 23 AND x <= 143 • IN: x IN (1, 2, 3); x IN (SELECT y FROM tabela_A) • LIKE: nazwisko LIKE ’Kowal%’’_’ – zastępuje dowolny pojedynczy znak’%’ – zastępuje ciąg pusty lub składający się z dowolnej ilości znakówW programie MS Access znaki ’_’, ’%’ są zastąpione przez ’?’, ’*’ • NULL: x IS NULL; y IS NOT NULL • EXISTS: EXISTS (SELECT * FROM zakupy WHERE id_klienta=’0123’) • OVERLAPS

41. Porównania z wynikami podzapytań • ANY, SOME • X > ANY (SELECT … ) • X > ANY (1,2,3) ≡ X >1 • X <= ANY (1,2,3) ≡ X <=3 • ALL • X > ALL (SELECT … ) • X > ALL (1,2,3) ≡ X >3 • X <= ALL (1,2,3) ≡ X <=1 • UNIQUE (podzapytanie) • MATCH

42. Klauzule GROUP BY i HAVING • Klauzula GROUP BY jest używana do definiowania grup wyjściowych wierszy • Dla grup wierszy można używać funkcji agregujących (COUNT, MIN, MAX,SUM, AVG) • Klauzula HAVING (podobnie jak WHERE) odrzuca grupy wierszy nie spełniające warunku (predykatu)

43. Przykład zapytania grupującego SELECT studenci.nazwisko || studenci.imie AS student, srednie."średnia" FROM (SELECT oceny.id_studenta, AVG(oceny.ocena) AS "średnia" FROM oceny GROUP BY oceny.id_studenta) srednie NATURAL JOIN studenci

44. UNION, INTERSECT, EXCEPT, ORDER BY • UNION – operator sumowania wyników dwóch instrukcji SELECT • INTERSECT – operator przecięcia wyników dwóch instrukcji SELECT • EXCEPT – operator różnicy wyników dwóch instrukcji SELECT • ORDER BY – klauzula określająca kryterium sortowania • ASC – oznacza kolejność sortowania rosnącą (domyślna) • DESC – oznacza kolejność sortowania malejącą

45. Przykład UNION SELECT 'student' AS funkcja, nazwisko AS "Nazwisko", imie AS "Imię" FROM studenci UNION SELECT "Stopien_naukowy", "Nazwisko", "Imie" FROM wykladowcy ORDER BY 2

46. Tworzenie perspektyw • CREATE VIEW nazwa[(lista kolumn)]AS (instrukcja_SELECT) • Perspektywa (widok) jest tabelą wirtualną • Perspektywa jest traktowana jak tabela, ale jej definicja zawiera instrukcję SELECT • Kiedy w instrukcji używa się perspektywy, wynik zapytania staje się jej zawartością na czas trwania instrukcji • Przykład

48. Tworzenie schematu • Obiekty bazy danych mogą być grupowane w schematy • Nazwy obiektów muszą być unikalne w obrębie schematu, ale nie muszą się różnić miedzy schematami • CREATE SCHEMA nazwa_schematu AUTHORIZATION identyfikator_uprawnień • CREATE SCHEMA student1 AUTHORIZATION student1

49. Usuwanie danych • DELETE FROM nazwa_tabeliWHERE wyrażenie_ograniczające • Przykłady: • DELETE FROM studenciWHERE pid = ’0001’ • DELETE FROM studenciWHERE pid < ’0009’

50. Aktualizacja danych • UPDATE nazwa_tabeli SET nazwa_kolumny = wyrażenie [,…]WHERE wyrażenie_ograniczające • Przykłady • UPDATE płace SET płaca = płaca * 2WHERE nazwisko = ’Kowalski’ • UPDATE płace SET płaca = płaca + 10WHERE nazwisko LIKE ’Kow%’