SQL

1
SQL Strukturalny jezyk zapytan

• Wprowadzenie do systemów baz danych

2
Historia

• Pierwotnie przewidziany dla serwerów danych
  (serwerów SQL) obecnie praktycznie wszystkie bazy
  danych sa wyposazone w interfejs SQL
• Prace nad jezykiem rozpoczeto 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)
• 2006 SQL2006
• 2008 SQL2008
• 2016 SQL2016

3
Funkcje jezyka

• Jezyk definiowania danych (ang. Data Definition
  Language DDL)
• Definiowanie struktury bazy danych
• Kontrola integralnosci
• Jezyk zarzadzania (manipulowania) danymi (ang.
  Data Manipulation Language DML)
• Wprowadzanie, modyfikacja i kasowanie danych
• Przeszukiwanie danych
• Jezyk kontroli danych (ang. Data Control Language
  DCL)
• Nadawanie i odbieranie uprawnien do korzystania
  z bazy danych

4
Typy danych - napisowe

• CHARACTER(n) tablica znaków o stalej dlugosci n
• Skrót CHAR
• CHARACTER VARYING(n) tablica znaków o zmiennej
  dlugosci liczba n okresla dlugosc maksymalna
• 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 (staloprzecinkowe)
• INTEGER liczba calkowita
• NUMERIC(m,n) liczba dziesietna o dlugosci m z n
  miejscami po przecinku (m precision, n scale)
• DECIMAL(m,n) liczba dziesietna o dlugosci m z n
  miejscami po przecinku (rzeczywista dlugosc
  liczby moze byc wieksza od n)
• APPROXIMATE NUMERIC (zmiennoprzecinkowe)
• FLOAT(m) liczba zmiennoprzecinkowa
• REAL liczba zmiennoprzecinkowa (krótka)
• DOUBLE PRECISION liczba zmiennoprzecinkowa
  podwójnej precyzji

6
Typy danych okreslajace czas

• DATE data
• DATE '1999-12-12' - DATE '1900-01-01' 36504
• TIME czas
• TIME '120000' - TIME '151000' -031000
• TIMESTAMP data i czas (stempel czasowy)
• TIMESTAMP '2001-01-01 122010'
• TIMESTAMP '2007-12-31 235959' - TIMESTAMP
  '2007-01-01 000000 ' 364 days 235959 364
  235959.0
• INTERVAL róznica 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 000000 (PostgreSQL)
• DATE '0000-01-01' INTERVAL '2001 YEAR 3 MONTH
  10 DAY' 2001-04-11 000000 (PostgreSQL)
• TIMESTAMP '2007-01-01 000000' INTERVAL '2
  month 5 day 151000' 2007-03-06 151000
  (PostgreSQL)
• Funkcje czasu i datyCURRENT_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_zewnetrzna(nazwa_kolumny)ON
  DELETE reakcjaON UPDATE reakcja
• DEFAULT wartosc
• Aby wpisac wartosc domyslna, w instrukcji INSERT
  INTO, nalezy wpisac DEFAULT

9
Ograniczenia tabeli

• PRIMARY KEY (nazwa_kolumny , ...)
• UNIQUE (nazwa_kolumny , ...)
• CHECK (warunek)
• FOREIGN KEY (nazwa_kolumny , ...) REFERENCES
  tabela(nazwa_kolumny , ...) ON DELETE
  reakcjaON UPDATE reakcja
• Ograniczenia tabeli mozna poprzedzic
  nazwaCONSTRAINT Nazwa_ograniczenia Ograniczenie
  tabeli

10
Naruszenie integralnosci referencyjnej

• Mozna okreslic reakcje systemu na próbe
  naruszenia zasad integralnosci referencyjnej
  poprzez usuniecie badz zmiane wartosci klucza
  rodzica w powiazanej tabeli
• Reakcje to
• Odmowa wykonania modyfikacji (domyslna) NO
  ACTION, RESTRICT
• Kaskadowa aktualizacja wierszy powiazanych
  CASCADE
• Kaskadowe usuniecie wierszy powiazanych CASCADE
  
• Wstawienie do wierszy powiazanych wartosci NULL
  SET NULL
• Wstawienie do wierszy powiazanych wartosci
  domyslnej SET DEFAULT

11
Klucz obcy zwiazek 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, jesli wartosc logiczna
  warunku jest TRUE
• Klauzula CHECK wymusza integralnosc dziedziny
• Przyklady
• CHECK (wzrost BETWEEN 50 AND 250)
• CHECK kierunek IN ('KSS', 'SiST', 'EO', 'EM')
• Warunek powinien byc staly, nie zalezec od
  zmiennych, danych w innych tabelach ani od
  biezacej daty (czasu)
• Porównania wpisywanej daty z data biezaca mozna
  dokonac przez procedure wyzwalana

13
Odraczanie sprawdzania wiezów

• Wiezy mozna zadeklarowac jako mozliwe do
  odroczenia DEFERRABLE lub jako niemozliwe do
  odroczenia NOT DEFERRABLE (wartosc domyslna).
• Odroczenie sprawdzania wiezów mozliwe jest na
  czas trwania transakcji i stosowane w przypadkach
  wiezów zapetlonych.
• Wiezy odraczane mozna dodatkowo zadeklarowac jako
  odraczane od poczatku INITIALLY DEFERRED lub
  natychmiastowe od poczatku INITIALLY IMMEDIATE
• Okreslenie trybu sprawdzania ograniczenSET
  CONSTRAINS nazwa_ograniczenia ALLDEFERRED
  IMMEDIATE

14
Przyklad

• CREATE TABLE studenci(pid CHAR(4) PRIMARY
  KEY,imie VARCHAR(15),nazwisko VARCHAR(15) NOT
  NULL,imie_ojca VARCHAR(15),semestr INTEGER
  CHECK (semestrlt10))
• CREATE TABLE studenci1(id_studenta
  CHAR(4),nazwisko VARCHAR(25) NOT
  NULL,imie VARCHAR(25),imie_ojca
  VARCHAR(25),CONSTRAINT Klucz_glowny_studenc
  i PRIMARY KEY(id_studenta))

15
Przyklad

• 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
  COLUMNstara_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 tworzacy ta baze
(PostgreSQL) Skrypt SQL tworzacy tab.
Oceny skrypt SQL tworzacy baze danych
18
Diagram (MS SQL Server) prostej bazy danych i
skrypt SQL tworzacy ta baze
(MS SQL Server) Skrypt SQL tworzacy tab.
oceny skrypt SQL tworzacy baze danych
19
Diagram prostej bazy danych HR
(Oracle) skrypt SQL tworzacy tabeleEMPLOYEES
20
Diagram prostej bazy danych HR (kurze lapki)
21
Narzedzia graficzne do tworzenia tabel baz danych
SQL Server Enterprise Manager
22
Narzedzia graficzne do tworzenia tabel
Oracle
23
Indeksy

• Krotki sa zapisywane w pliku w kolejnosci
  pojawiania sie
• Przeszukiwanie duzego pliku w poszukiwaniu krotki
  moze zajac wiele czasu
• W celu przyspieszenia wyszukiwania krotek, na
  podstawie wartosci atrybutu, tworzy sie 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 , ...)
• Przyklady
• CREATE UNIQUE INDEX indeks_studenciON
  studenci(nazwisko, imie)
• CREATE INDEX indeks_przedmiotyON oceny(przedmiot)

25
Usuwanie tabel i indeksów

• DROP TABLE nazwa_tabeli
• DROP INDEX nazwa_indeksu
• Przyklady
• DROP TABLE studenci
• DROP INDEX indeks_studenci

26
Wprowadzanie danych do tabel

• INSERT INTO nazwa_tabeli(nazwa_kolumny
  ,..)VALUES (wartosc , ...)
• INSERT INTO nazwa_tabeli(nazwa_kolumny
  ,..)zapytanie SELECT
• Przyklady
• 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 wartosci mozna wpisac DEFAULT lub NULL

27
Wybieranie danych

• SELECT DISTINCTwyrazenie AS nazwa_kolumny
  ,...FROM zródlo_elementówWHERE
  warunekGROUP BY wyrazenie ,...HAVING
  warunekUNION INTERSECT EXCEPT
  instrukcja_SELECTORDER BY wyrazenie ASC
  DESC

28
Proste przyklady 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 okresla wynikowe kolumny, i moze
  zawierac
• Nazwy kolumn wynikowych
• Wyrazenia zawierajace funkcje, nazwy kolumn i
  stale
• Stosujac AS mozna nadac wynikowym kolumnom nazwy
  inne od domyslnych
• Symbol oznacza wszystkie kolumny
• Dyrektywe DISTINCT likwidujaca powtórzenia
  wierszy
• Nazwy kolumn
• nazwa_tabeli.nazwa_kolumny
• nazwa_kolumny jesli nazwa kolumny jednoznacznie
  okresla tabele, z której pochodzi (brak w zródle
  elementów kolumn o tych samych nazwach)

30
Wyrazenia arytmetyczne

• Operatory , -, , /
• Funkcje matematyczne ABS(x), POWER(x,y),
  SQRT(x), RAND() generuje liczbe losowa z
  zakresu od 0 do 1
• Funkcje zaokraglania liczb
• CEILING(x) funkcja zaokragla swój argument w
  góre do najblizszej liczy calkowitej (Oracle
  CEIL(x))
• FLOOR(x) funkcja zaokragla swój argument w dól
  do najblizszej liczy calkowitej
• ROUND(x,n) zaokragla liczbe x do n miejsc po
  przecinku
• Jesli w wyrazeniu matematycznym choc jeden z
  argumentów jest NULL, to wynik tego wyrazenia
  zawsze jest NULL

31
Wyrazenia tekstowe (napisowe)

• Laczenie (konkatenacja) lancuchó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ózny od NULL (uzywana przy
  laczeniu lancuchów, z których jeden lub wiecej
  moze byc NULL)

x, x1, x2 wyrazenie lancuchowe (napis)
33
Konwersje typów

• CAST(wyrazenie AS typ_danych)
• CAST(ocena AS NUMERIC(2, 1))

34
CASE (2 formy)

• Forma 1
• CASE
• lista klauzul WHEN
• ELSE wyrazenie skalarne
• END
• Klauzula WHEN ma postac
• WHEN wyrazenie_warunkowe THEN
  wyrazenie_skalarne
• Forma 2
• CASE wyrazenie_skalarne
• lista klauzul WHEN
• ELSE wyrazenie skalarne
• END
• Klauzula WHEN ma postac
• WHEN wyrazenie_skalarne THEN
  wyrazenie_skalarne
• Klauzule WHEN sa wykonywane kolejno, pierwszy
  spelniony warunek okresla wynik koncowy i
  sprawdzanie klauzul zostaje zakonczone

35
Przyklad uzycia CASE (2 formy)

• SELECT ocena,
• CASE
• WHEN ocena gt 4 THEN 'gratuluje'
• WHEN ocena gt 3 THEN 'dobrze'
• WHEN ocena gt 2 THEN 'nienajlepiej'
• WHEN ocena 2 THEN 'moze nastepnym razem'
• END AS "komentarz 1",
• CASE ocena
• WHEN 5 THEN 'gratuluje'
• WHEN 4.5 THEN 'tez gratuluje'
• WHEN 4 THEN 'dobrze'
• WHEN 3.5 THEN 'no srednio'
• WHEN 3 THEN 'nienajlepiej'
• ELSE 'moze nastepnym razem'
• END AS "komentarz 2"
• FROM oceny

36
Funkcje agregujace

• Funkcje agregujace wyznaczaja jedna wartosc z
  grupy wartosci w kolumnie grupy wierszy okresla
  klauzula GROUP BY
• COUNT zlicz ilosc wszystkich wartosci (w
  kolumnie) róznych od NULL
• COUNT() zlicza wszystkie wiersze
• AVG oblicza srednia ze wszystkich wartosci
• SUM sumuje wartosci
• MAX zwraca wartosc najwieksza
• MIN zwraca wartosc najmniejsza

W przypadku uzycia jednoczesnie wyrazen i funkcji
agregujacych wszystkie wyrazenia musza wystapic
w klauzuli GROUP BY
37
Klauzula FROM

• Klauzula FROM zawiera wyrazenie tabelowe
  okreslajace zródlo danych dla zapytania
• Wyrazenie tabelowe klauzuli FROM moze zawierac
• Liste tabel i perspektyw zródlowych
• A, B oznacza iloczyn kartezjanski (zlaczenie
  krzyzowe) tabel A i B
• A, B A CROSS JOIN B
• Zlaczenia tabel
• A NATURAL typ JOIN B zlaczenie naturalne (po
  kolumnach o tych samych nazwach w obu tabelach)
• A typ JOIN B ON warunek
• A typ JOIN B USING (kolumna ,...)
• Typy zlaczen zewnetrznychLEFT RIGHT FULL
  OUTER
• Podzapytania umieszczone w nawiasach i z
  nadanymi aliasami
• Tabelom mozna nadac zastepcze nazwy nazywane
  aliasami, nazwami skorelowanymi i zmiennymi
  zakresu
• format nazwa_tabeli_zródlowej AS alias
• nadawanie aliasów tabelom zródlowym stosuje sie
  przy samozlaczeniach tabel
• alias trzeba nadac wynikowi podzapytania, które
  moze byc uzyte w zastepstwie tabeli

38
Przyklad samozlaczenia

• select
• "PRACOWNICY"."FIRST_NAME" as "Imie",
• "PRACOWNICY"."LAST_NAME" as "Nazwisko",
• "SZEFOWIE"."FIRST_NAME" as "Imie(szefa)",
• "SZEFOWIE"."LAST_NAME" as "Nazwisko(szefa)"
• from
• "EMPLOYEES" "PRACOWNICY" LEFT JOIN
  "EMPLOYEES" "SZEFOWIE" ON "PRACOWNICY"."MANAG
  ER_ID""SZEFOWIE"."EMPLOYEE_ID"

39
Klauzula WHERE

• Zawiera warunek selekcji wierszy tabeli wynikowej
  nazywany czesto wyrazeniem ograniczajacym
• Warunek (predykat) jest wyrazeniem logicznym
  (funkcja zdaniowa) w tabeli wynikowej znajda
  sie tylko wiersze spelniajace warunek
• Predykat moze zawierac nazwy kolumn, wyrazenia
  logiczne, operatory porównan, funkcje i
  zagniezdzone instrukcje SELECT (podzapytania)
• Predykaty moga miec wartosc logiczna TRUE, FALSE
  lub NULL
• Na logicznym rachunku predykatów opiera sie
  rachunek relacyjny w przypadku jezyka SQL jest
  to rachunek relacyjny na krotkach

40
Predykaty - przyklady

• Porównania , lt, gt, lt, gt, ltgt
• a gt b a ltb itp.
• BETWEEN x BETWEEN 23 AND 143 ? x gt 23 AND x
  lt 143
• IN x IN (1, 2, 3) x IN (SELECT y FROM tabela_A)
• LIKE nazwisko LIKE Kowal_ zastepuje
  dowolny pojedynczy znak zastepuje ciag
  pusty lub skladajacy sie z dowolnej ilosci
  znakówW programie MS Access znaki _, sa
  zastapione przez ?,
• NULL x IS NULL y IS NOT NULL
• EXISTS EXISTS (SELECT FROM zakupy WHERE
  id_klienta0123)
• OVERLAPS

41
Porównania z wynikami podzapytan

• ANY, SOME
• X gt ANY (SELECT )
• X gt ANY (1,2,3) X gt1
• X lt ANY (1,2,3) X lt3
• ALL
• X gt ALL (SELECT )
• X gt ALL (1,2,3) X gt3
• X lt ALL (1,2,3) X lt1
• UNIQUE (podzapytanie)
• MATCH

42
Klauzule GROUP BY i HAVING

• Klauzula GROUP BY jest uzywana do definiowania
  grup wyjsciowych wierszy
• Dla grup wierszy mozna uzywac funkcji
  agregujacych (COUNT, MIN, MAX,SUM, AVG)
• Klauzula HAVING (podobnie jak WHERE) odrzuca
  grupy wierszy nie spelniajace warunku (predykatu)

43
Przyklad zapytania grupujacego

• SELECT studenci.nazwisko studenci.imie AS
  student, srednie."srednia"
• FROM
• (SELECT oceny.id_studenta, AVG(oceny.ocena) AS
  "srednia"
• 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 przeciecia wyników dwóch
  instrukcji SELECT
• EXCEPT operator róznicy wyników dwóch
  instrukcji SELECT
• ORDER BY klauzula okreslajaca kryterium
  sortowania
• ASC oznacza kolejnosc sortowania rosnaca
  (domyslna)
• DESC oznacza kolejnosc sortowania malejaca

45
Przyklad UNION
SELECT 'student' AS funkcja, nazwisko AS
"Nazwisko", imie AS "Imie" 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 tabela wirtualna
• Perspektywa jest traktowana jak tabela, ale jej
  definicja zawiera instrukcje SELECT
• Kiedy w instrukcji uzywa sie perspektywy, wynik
  zapytania staje sie jej zawartoscia na czas
  trwania instrukcji
• Przyklad

47
(No Transcript)
48
Tworzenie schematu

• Obiekty bazy danych moga byc grupowane w schematy
• Nazwy obiektów musza byc unikalne w obrebie
  schematu, ale nie musza sie róznic miedzy
  schematami
• CREATE SCHEMA nazwa_schematu AUTHORIZATION
  identyfikator_uprawnien
• CREATE SCHEMA student1 AUTHORIZATION student1

49
Usuwanie danych

• DELETE FROM nazwa_tabeliWHERE wyrazenie_ogranicza
  jace
• Przyklady
• DELETE FROM studenciWHERE pid 0001
• DELETE FROM studenciWHERE pid lt 0009

50
Aktualizacja danych

• UPDATE nazwa_tabeli SET nazwa_kolumny wyrazenie
  ,WHERE wyrazenie_ograniczajace
• Przyklady
• UPDATE place SET placa placa 2WHERE nazwisko
  Kowalski
• UPDATE place SET placa placa 10WHERE
  nazwisko LIKE Kow

51
Obsluga duzych obiektów

• Wspólczesne bazy danych czesto musza przechowywac
  duze obiekty takie jak
• Obiekty graficzne fotografie osób, odcisków
  palców, wzory podpisów itp.
• Pliki dzwiekowe
• Programy
• Duze obiekty tekstowe
• Do przechowywania duzych obiektów sluza typy
  danych
• BLOB (Binary Large Object)
• CLOB (Chracter Large Object)
• NCLOB (National Chracter Large Object)

52
Nawiazanie polaczenia z SZBD

• CONNECT TO DEFAULT nazwa_serweraAS nazwa
  polaczeniaUSER nazwa_uzytkownika
• Przyklady
• CONNECT student/haslo_at_localhost1521/XE
  (ORACLE)
• PSQL opcje nazwa_bazy_danych
  nazwa_uzytkownika (PostgreSQL)\connect
  nazwa_bazy_danych nazwa_uzytkownika