Docstoc

SQL - Gdynia

Document Sample
SQL - Gdynia Powered By Docstoc
					   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)

                                                    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   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_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 – 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ó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]]

				
DOCUMENT INFO
Categories:
Tags:
Stats:
views:3
posted:9/15/2011
language:Polish
pages:52