SQL – Strukturalny język zapytań

Wprowadzenie do systemów baz danych

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

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

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

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

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[()]

Tworzenie tabeli CREATE TABLE nazwa_tabeli

(nazwa_kolumny typ_kolumny [ograniczenia_kolumny], nazwa_kolumny typ_kolumny [ograniczenia_kolumny],..............,[ograniczenia_tabeli])

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

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

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

Klucz obcy – związek jeden do wiele

Diagram standardu Integration DEFinition (IDEF1X)

Diagram MS SQL Server

MS Access

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ą

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}

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 PRIMARY KEY(id_studenta));

Przykład CREATE TABLE oceny

(pid CHAR(4) REFERENCES studenci(pid),przedmiot VARCHAR(15),ocena VARCHAR(10),data DATE,PRIMARY KEY (pid, przedmiot))

Zmiana struktury bazy danych ALTER TABLE nazwa_tabeli

ADD [COLUMN] nazwa_kolumny typ_kolumny ALTER TABLE nazwa_tabeli

ALTER | 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

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

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

Diagram prostej bazy danych HR

(Oracle)

skrypt SQL tworzący tabelęEMPLOYEES

Diagram prostej bazy danych HR (kurze łapki)

Narzędzia graficzne do tworzenia tabel baz danych

SQL Server Enterprise Manager

Narzędzia graficzne do tworzenia tabel

Oracle

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

Tworzenie indeksu CREATE [UNIQUE] INDEX nazwa_indeksu

ON nazwa_tabeli(nazwa_kolumny [, ...]) Przykłady:

CREATE UNIQUE INDEX indeks_studenciON studenci(nazwisko, imię)

CREATE INDEX indeks_przedmiotyON oceny(przedmiot)

Usuwanie tabel i indeksów

DROP TABLE nazwa_tabeli DROP INDEX nazwa_indeksu Przykłady:

DROP TABLE studenci DROP INDEX indeks_studenci

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

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]

Proste przykłady SELECT SELECT * FROM studenci SELECT * FROM oceny

WHERE pid = ’0001’ SELECT * FROM studenci NATURAL JOIN oceny SELECT * FROM studenci NATURAL JOIN oceny

WHERE 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

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)

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

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'

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)

Konwersje typów

CAST(wyrażenie AS typ_danych)

CAST(ocena AS NUMERIC(2, 1))

CASE (2 formy)Forma 1

CASE lista klauzul WHEN

ELSE wyrażenie skalarneEND

Klauzula WHEN ma postać: WHEN wyrażenie_warunkowe THEN wyrażenie_skalarne

Forma 2 CASE wyrażenie_skalarne lista klauzul WHEN

ELSE wyrażenie skalarneEND

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

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

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

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

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"

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

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

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

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)

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

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ą

Przykład UNION

SELECT 'student' AS funkcja, nazwisko AS "Nazwisko", imie AS "Imię" FROM studenciUNIONSELECT "Stopien_naukowy", "Nazwisko", "Imie"FROM wykladowcyORDER BY 2

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

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

Usuwanie danych DELETE FROM nazwa_tabeli

WHERE wyrażenie_ograniczające Przykłady:

DELETE FROM studenciWHERE pid = ’0001’

DELETE FROM studenciWHERE pid < ’0009’

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%’

Obsługa dużych obiektów Współczesne bazy danych często muszą przechowywać duże

obiekty takie jak Obiekty graficzne – fotografie osób, odcisków palców, wzory

podpisów itp. Pliki dźwiękowe Programy Duże obiekty tekstowe

Do przechowywania dużych obiektów służą typy danych BLOB (Binary Large Object) CLOB (Chracter Large Object) NCLOB (National Chracter Large Object)

Nawiązanie połączenia z SZBD CONNECT TO {DEFAULT |

nazwa_serwera[AS nazwa połączenia][USER nazwa_użytkownika] }

Przykłady CONNECT student/hasło@localhost:1521/XE (ORACLE) PSQL [opcje] [nazwa_bazy_danych [nazwa_użytkownika]]

(PostgreSQL)\connect [nazwa_bazy_danych [nazwa_użytkownika]]