abd_notatki

abd_notatki
63 Downloads

ZADANIE na POWTÓRKĘ

Na sprawdzianie będzie pytanie o dopasowanie stosowanej instrukcji SQL do kategorii składniowej, więc obowiązuje tabelka (część niebieska):

DDL

Data Definition

Language

CREATE – tworzenie obiektów

ALTER – modyfikowanie obiektów

DROP – kasowanie obiektów

TRUNCATE – szybkie usuwanie zawartości tabeli (nie tabeli)

JDD

Język Definiowania Danych definiuje strukturę danych w formie schematu danych.

DML

Data Manipulation

Language

INSERT – wstawia rekordy do tabeli, widoku

UPDATE – modyfikuje rekordy w tabelach, widokach

DELETE – kasuje rekordy w tabelach, widokach

JMD

Język Manipulowania Danymi wypełnia BD danymi, usuwa i modyfikuje dane.

DCL

Data Control

Language

GRANTnadanie uprawnień

DENY

REVOKE odwołanie uprawnień nadanych poleceniem GRANT

JKD

Język Kontrolowania Danych steruje transakcjami. Używany przez DBA.

DQL

Data Query

Language

SELECT – wydobywa dane z tabel, widoków, sekwencji

JZ

Język Zapytań pobierający informacje spełniające określone warunki.

TCL

Transaction Control

Language

COMMIT

ROLLBACK

SAVEPOINT

JSP

Język Sterowania Przepływem danych (transakcji)

Czynności do przećwiczenia:

Utwórz BD SIO: szkola (klasa, profil, szkola)

uczen_gp (id, inicjal)

uczen (id, inicjal)

na koniec polecenia wstawi informację o czcionce: DEFAULT CHARSET=utf8

  1. Zastosuj wszystkie więzy spójności na wybranych kolumnach ( wykaz więzów ).
  2. Wprowadź minimum 3 krotki do tabeli poleceniem INSERT INTO .
  3. Przy ustalaniu klucza obcego zastosuj dodatkowo formułę  ON DELETE CASCADE , by wybrana dana została usunięta także w tabelach powiązanych kluczem obcym.
  4. Usuń wszystkie krotki z tabeli poleceniem TRUNCATE TABLE
  5. Zsumuj tabele uczen_gp i uczenz powtórzeniami i bez powtórzeń. Zastanów się, jakie kryteria muszą być spełnione, by sumowanie tabel było możliwe.
  6. Tymczasowo zmień nazwę tabeli lub kolumny przy pomocy aliasu.
  7. Wybierz tabelę i wyświetl jej zawartość limitowaną.
  8. Utwórz tabelę  korzystając z konstrukcji unii .

ZADANIE DODATKOWE:

  1. Wybierz dowolną tabelę zawierającą minimum 10 zróżnicowanych krotek i skonstruuj zapytanie zagnieżdżone (=wielopoziomowe), np. z tabeli faktura pokaż faktury tylko z dnia 11.01.2017 pod warunkiem, że ich kwota jest maksymalna. Wzór struktury kwerendy zagnieżdżonej jest tutaj.

Przykład: korzystałam z tabeli o strukturze tabela(kp,imie,nazwisko,haslo,wiek) i zadałam zapytanie zagnieżdżone do niej:

SELECT * FROM tabela WHERE wiek IN (SELECT wiek FROM tabela WHERE wiek = 20)

W odpowiedzi otrzymałam pełną informację o osobach w wieku 20 lat zarejestrowanych w bazie.

12. Dokonaj złączenia 2 tabel mających wspólny atrybut – struktura polecenia JOINS

13. Przygotuj perspektywę (widok) na wybranej tabeli poleceniem VIEWS

14. W poprzednich zadaniach do zachowania spójności BD (zgodności z rzeczywistością) stosowaliśmy: PRIMARY KEY, FOREIGN KEY, CHECK, NULL, UNIQUE.

Taką rolę pełnią też TRIGGERY (wyzwalacze), których ogólna struktura wygląda tak:

TWORZENIE TRIGGERA:

 CREATE [OR REPLACE ] TRIGGER trigger_name 
 {BEFORE | AFTER | INSTEAD OF } 
 {INSERT [OR] | UPDATE [OR] | DELETE} 
 [OF col_name] 
 ON table_name 
 [REFERENCING OLD AS o NEW AS n] 
 [FOR EACH ROW] 
 WHEN (condition)  
 BEGIN 
   --- sql statements  
 END; 

USUWANIE TRIGGERA: DROP TRIGGER trigger_name;
przegląd triggerów: SHOW TRIGGERS;

ZADANIE:

Powtórz to zadanie  lub wykonaj samodzielnie:

Wykorzystaj triggera do zachowania spójności w tabeli abc poprzez:

– odmowę dodawania rekordów osób niepełnoletnich i/lub z oceną 1 ( _bi BEFORE INSERT)

– zgodę na zamienianie ocen osobom wybranej płci np. na 6 ( _au AFTER UPDATE)

Sprawdź działanie triggera, wprowadzając przykładowe dane.

abc

id

plec

ocena

Efekty ćwiczenia zgłoś.

 

 

 

 

 

 

 

 

 

 

phpMySQL

Spójność BD

Więzy spójności są ważne przy transakcjach użytkowników, wyzwalaczach BD, ładowaniu lub imporcie danych do BD.

Metody ustalania więzów spójności:

  1. mechanizm wyzwalaczy – TRIGGERY
  2. mechanizm deklaratywnych  więzów spójności w definicjach tabel (instrukcje CREATE TABLE, ALTER TABLE) dotyczy:

a) encji (więzy ograniczają wartości, jakie mogą się pojawić w krotkach)

  • więzy klucza głównego PRIMARY KEY – ma jednoznacznie identyfikować krotkę, NOT NULL, automatycznie indeksowany. Jeżeli jest złożony z więcej niż 1 kolumny – definiujemy go tylko metodą „poza linią”.
  • więzy klucza jednoznacznego UNIQUE – ma jednoznacznie identyfikować krotkę, NULL, automatycznie indeksowany
  • więzy NOT NULL dla kolumny
  • więzy CHECK – warunek dla wszystkich krotek i dla min. 1 kolumny.

b) referencji (wartości klucza obcego mają być podzbiorem odpowiadającego mu klucza głównego lub jednoznacznego. Może być NULL, indeks zakładamy samodzielnie poleceniem CREATE INDEX).

Więzy, które nazwiemy poleceniem

ALTER TABLE nazwa tabeli

ADD (CONSTRAINT nazwa_więzi (nazwa_kolumny warunek));

możemy:

włączać: ALTER TABLE nazwa_tabeli ENABLE CONSTRAINT nazwa więzi;

wyłączać: ALTER TABLE nazwa_tabeli DISABLE CONSTRAINT nazwa więzi;

usunąć: ALTER TABLE nazwa_tabeli DROP CONSTRAINT nazwa więzi;

Ustalanie więzów spójności

 

Dołączanie krotek

INSERT INTO nazwa_tabeli (atrybut_1, atrybut_2)

VALUES

(wartość_1a, wartość_1b)

(wartość_2a, wartość_2b);

 

Strukturalny język zapytań SQL – historia.

a) Pochodzenie języka :.

  • SQL to najpopularniejszy mechanizm definiowania poleceń i modyfikacji w relacyjnych systemach baz danych, tzn. jest standardem języka relacyjnych baz danych.

  • Stworzony przez IBM dla relacyjnego DBMS o nazwie DB2 w latach siedemdziesiątych pod nazwą Structured English Query Language łączy cechy języka algebraicznego (ma operatory algebry relacji) oraz opartego na rachunku predykatów, w których definiuje się schemat relacji wynikowej oraz określa warunek, któy muszą spełniać krotki (=wiersz tabeli) tej relacji.

  • Jest zaimpementowany (=wdrożony, zastosowany) w następujących DBMS (Data Base Management System): DB2, Oracle, InterBase, MySQL, dBase, Sybase, Informix, Parasox, MS SQL.

  • Podstawowy rdzeń języka SQL jest implementacją (=wdrożeniem, zastosowaniem) algebry relacyjnej.

  • Jest językiem znormalizowanym w standardzie (=wspólnie ustalone kryterium) ANSIISO (=American National Standards Institute) od roku 1986 z aktualizacjami: SQL-89, SQL 2, SQL 3.

  • Język SQL konkretnej bazy (DBMS) zawiera: polecenia SQL zawarte w standardzie i rozszerzenia standardu – polecenia specyficzne dla konkretnego DBMS.

  • SELECT to podstawowa operacja SQL

b) Podgrupy wyrażeń języka SQL i operacje wykonywane przez wyrażenia języka SQL.

Zasady pisowni w języku SQL:

nie rozróznia wielkości liter w słowach kluczowych i nazwach (baz danych, tabel, indeksów i kolumn)

– słowa zarezerwowane piszemy tak: SELECT, FROM, GROUP BY

– słowa zdefiniowane prze użytkownika piszemy małymi literami, np. nazwy indeksów, widoków, tabeli, kolumn

– nazwy danych piszemy dokładnie tak, jak w tabeli

– nie stosujemy cyfr na początku nazwy lub w całej nazwie

– nie stosujemy znaków przestankowych i @

* zastępuje nazwy wszystkich kolumn

– każdą komendę SQL kończymy średnikiem ;

– wartości napisowe w cudzysłowach: „witaj szkoło” lub ‚witaj szkoło’

DDL

Data Definition

Language

CREATE – tworzenie obiektów

ALTER – modyfikowanie obiektów

DROP – kasowanie obiektów

TRUNCATE – szybkie usuwanie zawartości tabeli (nie tabeli)

JDD

Język Definiowania Danych definiuje strukturę danych w formie schematu danych.

DML

Data Manipulation

Language

INSERT – wstawia rekordy do tabeli, widoku

UPDATE – modyfikuje rekordy w tabelach, widokach

DELETE – kasuje rekordy w tabelach, widokach

JMD

Język Manipulowania Danymi wypełnia BD danymi, usuwa i modyfikuje dane.

DCL

Data Control

Language

GRANTnadanie uprawnień

DENY

REVOKE odwołanie uprawnień nadanych poleceniem GRANT

JKD

Język Kontrolowania Danych steruje transakcjami. Używany przez DBA.

DQL

Data Query

Language

SELECT – wydobywa dane z tabel, widoków, sekwencji

JZ

Język Zapytań pobierający informacje spełniające określone warunki.

TCL

Transaction Control

Language

COMMIT

ROLLBACK

SAVEPOINT

JSP

Język Sterowania Przepływem danych (transakcji)

Podstawowe operacje algebry relacynej – selekcja, projekcja, złączenie, suma.

a) Algebra relacyjna w relacyjnych bazach danych.

– Algebra relacji to dwa typy operacji: mnogościowe (suma, różnica, iloczyn, iloczyn kartezjański), relacyjne (selekcja, projekcja, złączenie, dzielenie).

– To zbiór operatorów, których argumentami jest jedna relacja lub więcej relacji, a wynikiem jest relacja.

– Edward Codd jako pierwszy zaproponował algebrę zbiorów krotek (relacji). W skład tej algebry wchodziło 5 operatorów działań na zbiorach: suma, iloczyn kartezjański, różnica oraz selekcja i rzutowanie (projekcja).

b) Definicja operacji algebry relacyjnej:

Zawodnicy

Nr

Integer

Imię

Varchar(50)

Wiek (lat)

Integer

1

Janek

12

2

Marek

15

3

Heniek

22

4

Kornel

34

5

Wojtek

25

6

Tadeusz

43

Pokoje

Id_pokoju

Integer

Cena_za_dobę

Integer

Id_gościa

Integer

1

50

1

2

60

2

3

70

3

4

20

4

5

10

5

6

23

6

  • selekcja (ograniczanie) σ – bierze jedną relację jako swój argument i produkuje w wyniku jedną relację wynikową spełniającą warunek; to „pozioma maszyna do cięcia”, bo usuwa wiersze nie spełniające warunku

    σ Wiek > 18 (Zawodnicy)

W języku SQL instrukcja SELECT może odwoływać się do wielu relacji:

SELECT *

FROM Zawodnicy

WHERE wiek > 18;

Nr

Integer

Imię

Varchar(50)

Wiek (lat)

Integer

1

Janek

12

2

Marek

15

3

Heniek

22

4

Kornel

34

5

Wojtek

25

6

Tadeusz

43

  • projekcja (rzut) π – bierze jedną relację jako swój argument i produkuje w wyniku jedną relację wynikową spełniającą warunek; to „pozioma maszyna do cięcia”, bo usuwa kolumny nie spełniające warunku;

    π Nr, Imię (Zawodnicy)

    SELECT Nr, Imię

FROM Zawodnicy;

Nr

Integer

Imię

Varchar(50)

Wiek (lat)

Integer

1

Janek

12

2

Marek

15

3

Heniek

22

4

Kornel

34

5

Wojtek

25

6

Tadeusz

43

  • złączenie ►◄– operator bierze 2 relacje ako argumenty i produkuje jedną relację wynikową; zostają tylko te wiersze, które występują w obydwu relacjach i mają tę samą wartość w kolumnach.

(π Imię (Zawodnicy)) ►◄ Zawodnicy.Nr = Pokoje.Id_pokoju ( π Cena_za_dobę (Pokoje))

SELECT Imię, Cena_za_dobę

FROM Zawodnicy JOIN Pokoje ON Nr=Id_gościa;

Zawodnicy

Nr

Integer

Imię

Varchar(50)

Wiek (lat)

Integer

1

Janek

12

2

Marek

15

3

Heniek

22

4

Kornel

34

5

Wojtek

25

6

Tadeusz

43

Pokoje

Id_pokoju

Integer

Cena_za_dobę

Integer

Id_gościa

Integer

1

50

1

2

60

2

3

70

3

4

20

4

5

10

5

6

23

6

  • suma (union) U – argumentami są 2 zgodne relacje (te same kolumny określone na tych samych dziedzinach), które dają relację wynikową; operacja ta produkuje krotki z 2 relacji;

(π Nr, Imię,Wiek (Zawodnicy1)) U (π Nr, Imię, Wiek (Zawodnicy2))

Zawodnicy1

Nr

Integer

Imię

Varchar(50)

Wiek (lat)

Integer

1

Janek

12

2

Marek

15

3

Heniek

22

4

Kornel

34

5

Wojtek

19

6

Tadeusz

43

Zawodnicy2

Nr

Integer

Imię

Varchar(50)

Wiek (lat)

Integer

1

Janek

30

2

Marcin

15

3

Paweł

32

4

Iwo

33

5

Wojtek

19

6

Tadeusz

43

Zawodnicy

Nr

Integer

Imię

Varchar(50)

Wiek (lat)

Integer

2

Marcin

15

1

Janek

12

3

Paweł

32

2

Marek

15

3

Heniek

22

1

Janek

30

4

Iwo

33

4

Kornel

34

5

Wojtek

19

6

Tadeusz

43

Charakterystyka składni strukturalnego języka zapytań.

a) 5 głównych kategorii składniowych SQL.

Lp.

Identyfikator

Nazwy obiektów

Przykłady

1

Literały

Stałe

a) napisy – ‚Witaj szkoło!’

b) zapisy bitowe – B’1101′

c) napisy szesnastkowe – X’FA’

d) dokładne wartości liczbowe zapisywane jako liczba dziesiętna – ze znakiem lub bez – z możliwością zastosowania kropki dziesiętnej – -25.3, 0.07

2

Operatory

Spójniki

Arytmetyczne

używamy (wyrażenie)

iloczyn

*

iloraz

/

modulo

%

suma

+

różnica

Znakowe

konkatencja

+ lub ║

symbol wieloznaczny (zastępuje dowolną ilość znaków

%

symbol wieloznaczny (zastępuje 1 znak)

_

Logiczne

koniunkcja

AND

alternatywa

OR

negacja

NOT

Porównania

równy

=

mniejszy

<

większy

>

mniejszy lub równy

<=

większy lub równy

>=

różny

!= <>

z SQL

operator przynależności do zbioru przedziału domkniętego

BETWEEN n AND n

operator przynależności do zbioru

IN ()

operator dopasowania do wzorca

LIKE

operator dopasowania do wyrażenia regularnego

REGEXP

RLIKE

operator porównania sprawdzający występowanie znacznika braku wartości NULL

IS NULL

3

Słowa kluczowe

Wyrazy interpretowane przez serwer w określony sposób

Słowa kluczowe to zarerwowane – podobnie jak w językach programowania – ciągi znaków:

Instrukcje

CREATE, SELECT

Klauzule

WHERE, JOIN

ORDER BY

Nazwy typów danych

INTEGER, CHAR

Nazwy funkcji systemowych

ISNULL, ABS

Nazwy zarezerwowane do użycia w przyszłości

zależnie od BD

4

Komentarze

Ignorowane

(–) to komentarz, którego używamy, aby określony wiersz nie był interpretowany.

/* tu znajdują się wiersze,

które komputer zignoruje

/*

5

Operator konkatencji

+ łączy atrybut z:

– innym atrybutem

– literałem

– wyrażeniem arytmetycznym

– wartościa stałą

Operacja produkuje 1 kolumnę wynikową.

Zbiór znaków SQL_TEXT zawiera:

– wielkie litery A-Z

– małe litery a-z

– cyfry 0-9

– zbiór znaków specjalnych: ” % ‚ () * + , – . / : ; = , < > ? _ |

Pojęcie klauzuli strukturalnego języka zapytań.

a) Definicja kaluzuli w rozumieniu matematyki oraz w języku SQL.

Klauzula

a) w matematyce: zbiór formuł logicznych; zbiór literałów, który jest prawdziwy wtedy i tylko wtedy, gdy ich alternatywa jest prawdziwa

b) w języku SQL: klauzulę wprowadza słowo kluczowe, więc może być ona wymagana lub opcjonalna

b) Definicja tokenu.

token – podstawowa cząstka SQL, która nie może być zredukowana gramatycznie do prostszej postaci. Tokenem są: słowa kluczowe, identyfikatory, operatory, literały i znaki interpunkcyjne. Prawidłowa kombinacja tokenów wprowadzanych przez słowo kluczowe do polecenie SQL.