📓 SQL

TWORZENIE BAZ DANYCH I ADMINISTROWANIE BAZAMI DANYCH:

Zalety korzystania z komputerowych baz danych:

• szybkie wyszukiwanie informacji
• łatwe wykonywanie obliczeń
• możliwość przechowywania dużej ilości danych na małym obszarze
• szybkie porządkowanie danych

Baza danych to uporządkowany zbiór danych na jakiś temat zorganizowany w sposób ułatwiający do nich dostęp. System zarządzania bazą danych (SZBD) to program zarządzający danymi w bazie i umożliwiający ich przetwarzanie. System bazy danych to baza danych i SZBD. 

Modele baz danych:

• model hierarchiczny (dane przechowywane są w postaci drzewa, informacja zawarta jest w dokumentach)
• model sieciowy (połączenia pomiędzy dokumentami tworzą sieć, informacja jest zawarta w dokumentach oraz w przebiegu połączeń sieci)
• model obiektowy (łączy cechy programów komputerowych tworzonych w językach programowania obiektowego z cechami aplikacji bazodanowych, obiekt w bazie reprezentuje obiekt w świecie rzeczywistym)
• model relacyjny (informacja zapisywana w tabeli w formie wierszy, tabele tworzą ze sobą powiązania - czyli tzw. relacje, najczęściej wykorzystywany)
• model postrelacyjny (bazy danych poszerzone np. o elementy obiektowości)

Model relacyjny wg Codda:

a) klucz (zbiór atrybutów mających określoną wartość)

• właściwość klucza pozwala jednoznacznie identyfikować krotki
• schemat może posiadać kilka kluczy (podstawowe, obce)
• każdy nadzbiór klucza jest kluczem

b) integralność danych 

• integralność encji (każdy schemat relacji posiada klucz główny i żaden element klucza głównego nie może posiadać wartości pustej (NULL))
• integralność referencyjna (każda wartość klucza obcego jest równa wartości klucza właściwego określonej krotki w relacji nadrzędnej lub wynosi NULL)
• więzy ogólne (dodatkowe warunki dotyczące poprawności danych określone przez użytkowników lub administratorów baz danych)

c) algebra relacji (zawiera zbiór jawnych operacji pozwalających na tworzenie wymaganych relacji z relacji dostępnych w bazie danych)

Relacyjny model baz danych:

• W tabeli przechowuje się dane
• Podając wartość klucza podstawowego, nazwy tabeli i nazwy kolumny powinno się mieć dostęp do dowolnych danych
• Musi być obsługiwana wartość NULL
• Występuje integralność danych

Klucz podstawowy (primary key) to zestaw atrybutów relacji, który jednoznacznie identyfikuje każdy rekord tej relacji. Nie może on zawierać powtarzających się danych oraz nie może być pusty. Kluczem tym  może być także kombinacja pól (np. imię i nazwisko). Często kluczem podstawowym jest klucz sztuczny, który jest polem zawierającym unikatowy numer identyfikacyjny nadany w sposób sztuczny każdemu obiektowi, który znajduje się w tabeli (ID). Klucz obcy jest kombinacją jednego lub wielu atrybutów tabeli, które wyrażają się w dwóch lub większej liczbie relacji. Wykorzystywany jest do tworzenia relacji pomiędzy dwoma tabelami, gdzie w jednej tabeli ten zbiór atrybutów jest kluczem obcym, a w drugiej kluczem głównym. 

Relacja to zdefiniowanie logicznego połączenia między tabelami bazy danych. Wyróżniamy 4 typy relacji:

• Związek "jest"

Jeśli zbiór encji A stanowi uogólnienie zbioru encji B to zbiór encji B jest specjalnym rodzajem zbioru encji A.

•  Związek jednojednoznaczny ("jeden do jednego")

Każda encja ze zbioru encji A jest skojarzona z co najwyżej jedną encją ze zbioru B i na odwrót.

• Związek jednoznaczny ("wiele do jednego")

Każda encja ze zbioru encji A jest skojarzona z co najwyżej jedną encją ze zbioru encji B, natomiast jednej encji ze zbioru encji B może odpowiadać wiele encji ze zbioru encji A. 

• Związek wieloznaczny ("wiele do wielu")

Każdej encji ze zbioru encji A może odpowiadać wiele encji ze zbioru encji B i na odwrót. 

Encja to każdy obiekt (przedmiot, stan, zjawisko itp.), który jesteśmy w stanie odróżnić od innych obiektów. Cechy encji nazywamy atrybutami (np. jeśli encją jest człowiek, to jego atrybutami jest numer pesel, imię, nazwisko).  Przykład dotyczący planowania projektowania bazy danych Reguły projektowania tabel:

• Do opisu każdego zbioru podobnych encji stosuje się oddzielną tabelę. Jednej encji odpowiada jeden wiersz. Atrybutowi odpowiada kolumna. Dla każdego atrybutu określa się typ informacji.
• Do opisu każdego dwustronnego związku między encjami można użyć oddzielnej tabeli. Kolumny tabeli są tworzone z kluczy encji należących do związku.
• Zapis związku jeden do jednego lub wiele do jednego może być umieszczony w dodatkowych kolumnach tabel pozostających w związku. W przypadku związku jeden do jednego kolumna ta może znaleźć się w dowolnej tabeli, w przypadku związku wiele do jednego musi znaleźć się w tabeli ze strony "wiele". Dołączona kolumna zawiera klucz encji, z którą zachodzi związek.
• Związek wiele do wielu opisuje się w oddzielnej tabeli, której kolumny tworzone są z kluczy encji należących do związku.
• Jeśli klucze w tabeli opisującej związek składają się z wielu atrybutów lub są za długie, należy zastąpić je kluczami sztucznymi.

Normalizację tabel stosuje się po to, aby sprawdzić, czy zaprojektowane tabele mają prawidłową strukturę. Normalizacja ma prowadzić do:

• zlikwidowania problemu powtarzania danych
• zoptymalizowania objętości bazy
• zoptymalizowania efektywności obsługi bazy
• najmniejszego prawdopodobieństwa wystąpienia błędu podczas wprowadzania danych.

Pierwsza postać normalna:

Tabela jest w pierwszej postaci normalnej, gdy pojedyncze pole tabeli zawiera informację elementarną (nie występuje lista wartości! - czyli jedno pole -> jedna wartość).

Druga postać normalna: Tabela jest w drugiej postaci normalnej, gdy jest w pierwszej postaci normalnej oraz, gdy każde z pól niewchodzących w skład klucza podstawowego zależy od całego klucza, a nie od jego części.

Trzecia postać normalna: Tabela jest w trzeciej postaci normalnej, gdy jest w pierwszej i drugiej postaci normalnej oraz każde z pól niewchodzących w skład klucza podstawowego niesie informację bezpośrednio o kluczu i nie odnosi się do żadnego innego pola. Systemy zarządzania bazą danych służą do manipulowania i aktualizowania danych, które są zgromadzone w systemach komputerowych. Pozwalają one prowadzić operacje na dużych i bardzo dużych zbiorach danych oraz na zarządzanie złożonymi strukturami. Interakcja aplikacji z bazą danych odbywa się zwykle przy pomocy języka SQL. W powyższym schemacie systemu zarządzania bazą danych:

• moduł zarządzania pamięcią przechowuje informacje o miejscu zapisania plików bazy danych na dysku oraz obsługuje pamięć operacyjną
• procesor zapytań przekształca zapytanie lub operację języka zapytań w ciąg poleceń żądających określonych danych
• moduł zarządzania transakcjami kontroluje poprawność i kompletność wykonania wszystkich transakcji

Sposoby komunikacji z bazą danych:

• architektura klient-serwer (aplikacje zainstalowane na stacjach użytkowników komunikują się z bazą danych, wykorzystując sieciowe oprogramowanie dedykowane do komunikacji z systemem zarządzania bazą danych)
• architektura trójwarstwowa (pomiędzy użytkownikami, a serwerem bazy danych znajduje się serwer aplikacji udostępniający umieszczone na nim aplikacje)

Cechy dobrze zrobionej bazy danych:

• trwałość danych (dane zapisane są w sposób nieulotny)
• integralność danych (poprawność danych - dane muszą być rzeczywiste, spełniać ograniczenia nałożone przez użytkowników, odporne na błędy i awarie oraz na błędy użytkowników)
• bezpieczeństwo danych (dostęp do bazy mają tylko użytkownicy identyfikowani loginem i hasłem)
• współdzielenie danych (równoczesna praca wielu użytkowników)
• abstrakcja danych (struktura bazy powinna w poprawny sposób odzwierciedlać obiekty świata rzeczywistego oraz powiązania pomiędzy tymi obiektami)
• niezależność danych (oddzielenie danych od aplikacji, które używają tych danych)
• integracja danych (zapewnienie współpracy danych przechowywanych w różnych miejscach i obsługiwanych przez różne systemy)

Język SQL realizuje trzy podstawowe typy zadań:

• Instrukcje DDL (definiowanie danych oraz tworzenie, modyfikowanie i usuwanie obiektów bazy danych)
• Instrukcje DML (manipulowanie danych, odczyt i modyfikacja danych)
• Instrukcje DCL (kontrola dostępu do danych, modyfikacja uprawnień użytkowników)

Typy danych w języku SQL:

Podstawowe instrukcje języka SQL:

• SELECT nazwakolumny1, nazwakolumny2 FROM nazwa_tabeli; - wyświetla wszystkie dane z wybranych kolumn znajdujących w danej tabeli (podstawowa instrukcja)
• SELECT DISTINCT nazwakolumny1, nazwakolumny2 FROM nazwa_tabeli; - wyświetla  dane z wybranych kolumn znajdujących w danej tabeli, wyniki nie powtarzają się
• SELECT nazwakolumny1, nazwakolumny2 FROM nazwa_tabeli WHERE warunek; - wyświetla dane z wybranych kolumn znajdujących się w danej tabeli spełniających dany warunek
• Operatory AND (oraz), OR (lub), NOT (nie są) używamy w zapytaniach, w których chcemy podać kilka warunków 
• ORDER BY - sortowanie w sposób alfabetyczny według danej kolumny (ASC - od a do z, DESC od z do a)
• INSERT INTO nazwatabeli VALUES (wartośćkolumny1, wartość_kolumny2);- instrukcja dodająca dane do danej tabeli 
• IS NULL / IS NOT NULL - sprawdzanie, które rekordy mają wartość NULL / nie mają wartości NULL
• UPDATE nazwatabeli SET nazwakolumny1 = nowa_ wartość_kolumny1 WHERE warunek; - modyfikowanie danych będących już w tabeli
• DELETE FROM nazwa_tabeli WHERE warunek; - usuwanie rekordów z tabeli, które spełniają podany warunek
• SELECT TOP/LIMIT/ROWNUM - wybiera pierwszych n rekordów z tabeli (liczbę n podajemy w instrukcji)
• MIN() oraz MAX() - funkcja zwracająca najmniejszą / największą wartość z wybranych kolumn
• COUNT() - funkcja zwracająca liczbę rekordów, które spełniają ewentualne kryteria podane w warunku
• AVG() - funkcja zwracająca średnią arytmetyczną z rekordów, które spełniają ewentualne kryteria podane w warunku
• SUM() - funkcja zwracająca sumę wartości rekordów, które spełniają ewentualne kryteria podane w warunku
• LIKE - funkcja szukająca rekordów, które spełniają specyficzny warunek (taki jak "szukaj wyrazy zaczynające się na daną literę np. SELECT nazwakolumny1, nazwakolumny2 FROM nazwatabeli WHERE nazwakolumny1 LIKE 'a%';)
• IN() - operator podawany w warunku (skrócenie zwielokrotnionego operatora OR), w nawiasie podaje się wartości rekordów, które mają zostać wyszukane
• BETWEEN wartość1kolumny AND wartość2kolumny; - operator podawany w warunku, pokazuje wartości, które mogą być w podanym zakresie, włącznie z wartościami granicznymi (np. pomiędzy 2000, a 2004, pokaże także wyniki z 2000 i 2004)
• SELECT nazwakolumny1 AS nazwaaliasu FROM nazwa_tabeli; - wybiera wszystkie rekordy z kolumny1 nazywając kolumnę wynikową nazwa_aliasu
• SELECT nazwatabeli1.nazwakolumny1, nazwatabeli2.nazwakolumny2 FROM nazwatabeli1 INNER JOIN nazwatabeli2 ON nazwatabeli1.nazwakluczagłównegowtabeli1 = nazwatabeli2.nazwakluczagłównegowtabeli2; (gdzie klucz główny w tabeli1 jest kluczem obcym w tabeli2) - łączenie danych z kilku tabel (INNER JOIN/ LEFT JOIN / RIGHT JOIN / FULL JOIN / SELF JOIN)
• SELECT zapytanie1 UNION SELECT zapytanie2; - pokazuje wszystkie elementy, które są wynikiem pierwszego zapytania oraz drugiego zapytania
• GROUP BY - grupowanie wierszy według określonej kolumny
• HAVING - filtrowanie całych grup rekordów
• CASE - tworzenie warunku "co ma się stać gdy..."
• Komentarze w języku SQL ( -- oraz /* ... */ )
• Daty w języku SQL 
• REPAIR TABLE nazwa_tabeli - naprawa uszkodzonej bazy danych (QUICK - naprawa tylko pliku indeksu)

Instrukcje SQL dotyczące tabel baz danych:

• CREATE DATABASE nazwa_bazy; - tworzenie bazy o określonej nazwie
• DROP DATABASE nazwa_bazy; - usuwanie określonej bazy danych
• BACKUP DATABASE nazwabazy TO DISK 'ścieżkadomiejscana_dysku'; - tworzenie kopii zapasowej bazy wskazując ścieżkę, gdzie ma się ona zapisać
• CREATE TABLE ( ... ); - tworzenie tabeli, w nawiasie podajemy nazwę kolumny oraz typ  danych, możemy także określić klucz podstawowy (PRIMARY KEY), klucz obcy (FOREIGN KEY), wartość NOT NULL, wartość unikatowa UNIQUE, indeksowanie - INDEX, wartość domyślną - DEFAULT, sprawdzanie określonego warunku - CHECK, autonumerowanie - AUTO INCREMENT )
• DROP TABLE nazwa_tabeli; - usuwanie tabeli z bazy danych
• ALTER TABLE nazwa_tabeli operacja; - operacje na tabeli takie jak dodawanie kolumny (ADD nazwa_kolumny), usuwanie kolumny (DROP nazwa_kolumny), modyfikacja kolumny (ALTER lub MODIFY nazwa_kolumny)

Polecenia DCL (tworzenie użytkownika, dodawanie uprawnień itd.): Tworzenie użytkownika: CREATE USER nazwa IDENTIFIED BY haslo; Tworzenie użytkownika z określonymi uprawnieniami: GRAND prawa ON tabela TO nazwa_użytkownika; Prawa, czyli prawa dostępu do wykonania określonych poleceń przez użytkownika np. SELECT, INSERT, UPDATE, DELETE, REFERENCE, CREATE, DROP. Jeśli natomiast ma mieć wszystkie te prawa, wówczas: ALL PRIVILEGES. np. GRAND SELECT, INSERT ON pracownicy TO szef; Odbieranie wcześniej nadanych uprawnień wygląda w ten sposób: REVOKE prawa ON tabela FROM nazwa_użytkownika; * *Inne polecenia:

• DROP USER nazwa_użytkownika; - usuwa użytkownika
• ALTER USER nazwaużytkownika IDENTIFIED BY nowehasło; - zmiana hasła użytkownika

Typy tabel w MySQL:

• MyISAM (domyślny typ, szybkie odczytywanie danych, nie obsługują transakcji)
• MEMORY (tabele przechowywane są w pamięci operacyjnej, tracone są bezpowrotnie po wyłączeniu serwera, traktuje się je jako tabele tymczasowe)
• FEDERATED (definiują dane znajdujące się na innym serwerze)
• BLACKHOLE (dane zapisywane na tabelach nie są przechowywane, zapisywane wiersze są automatycznie usuwane)
• CSV (przechowywanie danych w plikach tekstowych typu CSV, używane do importowania i eksportowania danych pomiędzy serwerem MySQL, a innymi serwerami)
• ARCHIVE (przechowywanie dużej ilości danych, przechowywane dane są bez indeksów oraz są kompresowane przed umieszczeniem w tabeli, zmiana lub usunięcie danych jest niedozwolona)
• InnoDB (tabele obsługujące transakcje, MySQL automatycznie blokuje odczytywane i modyfikowane wiersze tych tabel)