Transcript Document
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
TIME – czas
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
TIME '12:00:00' - TIME '15:10:00' = -03:10:00
TIMESTAMP – data i czas (stempel czasowy)
DATE '1999-12-12' - DATE '1900-01-01' = 36504
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)
MS Access
Diagram MS SQL Server
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_tabeli
DROP [COLUMN] nazwa_kolumny [RESTRICT | CASCADE ]
ALTER TABLE nazwa_tabeli
ADD definicja ograniczenia tabeli
ALTER TABLE nazwa_tabeli
DROP 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
(Oracle)
skrypt SQL tworzący tabelę
EMPLOYEES
HR
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_studenci
ON studenci(nazwisko, imię)
CREATE INDEX indeks_przedmioty
ON 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 studenci
VALUES (’0001’, ’Jan’, ’Papkin’, ’Jan’)
INSERT INTO studenci(pid, nazwisko)
VALUES (’0002’, ’Ramzes’)
INSERT INTO studenci
SELECT pid, nazwisko, imie, imie_ojca FROM kandydaci
WHERE …
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.data
FROM studenci, oceny
WHERE oceny. przedmiot = ‘OiSA'
AND studenci.pid = oceny.pid
ORDER 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 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
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 = A CROSS JOIN B
Złączenia tabel
A, B – oznacza iloczyn kartezjański (złączenie krzyżowe) tabel A i B
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ów
W 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 studenci
UNION
SELECT "Stopien_naukowy", "Nazwisko", "Imie"
FROM wykladowcy
ORDER 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 studenci
WHERE pid = ’0001’
DELETE FROM studenci
WHERE 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 * 2
WHERE nazwisko = ’Kowalski’
UPDATE płace SET płaca = płaca + 10
WHERE 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]]