SQL

Podstawy technologii baz danych

Modele danych są zbiorami zasad dotyczących: definicji danych, operowania danymi oraz integralności danych. Wyróżnić możemy hierarchiczny, sieciowy, relacyjny i obiektowy model danych. Oprócz nich mamy często też doczynienia z prostymi modelami danych (np. plik tekstowy o ustalonej strukturze jednak bez odniesień do zawartości innych plików).

W modelu hierarchicznym mamy doczynienia z związkami pomiędzy rekordami (nadrzędność i podrzędność), różnymi typami rekordów, rekordy posiadają klucze (unikalne identyfikatory). Dane w tym modelu mają strukturę drzewiastą, utworzoną przez rekordy nadrzędne i podrzędne, nie da się usunąć rekordu bez usuwania rekordów podrzędnych wobec niego. Przykładem takiej bazy danych jest system plików. W modelu sieciowym oprócz zwykłych pól posiadamy pola kolekcji zawierające odniesienia do innych rekordów.

W modelu relacyjnym (RDBMS) relacje zazwyczaj reprezentowane są przez tabele stanowiące nieuporządkowany zbiór rekordów o atrybutach określonych w kolumnach. Kolumny, również stanowiące nieuporządkowany zbiór, określają typy wartości pól. Pola są wartościami atomowymi lub wartością NULL. Wyróżnia się klucze główne (stanowiące unikalny identyfikator rekordu w tabeli) oraz klucze obce stanowiące powiązania pomiędzy tabelami - kolumny będące kluczami obcymi muszą mieć ten sam typ co klucz główny tabeli do której mają prowadzić klucze obce. Powiązania takie tworzy się przez wstawianie do kolumny klucza obcego odpowiednich wartości klucza głównego. Operacje na danych opisuje algebra relacyjna możliwa jest:

Baza zapewnia także integralność danych, w szczególności integralność referencyjną - dotyczącą kluczy głównych i obcych (nie jest możliwe usunięcie wiersza na który wskazują klucze obce bez usuwania wierszy wskazujących, nie jest możliwe dodanie wiersza z kluczem obcym wskazującym na nieistniejący wiersz, nie jest możliwe dodanie wiersza powodujące zduplikowanie klucza głównego. Z zachowaniem poprawnością danych w modelu relacyjnym związane są więzy. Są to narzucone przez projektanta ograniczenia i dodatkowe relacje które muszą być spełniane przez wartości danego pola.

W modelu obiektowym występuje niejednorodna struktura danych zawierająca (obok wartości - atrybutów) także procedury. W modelu tym występuje "jednoznaczna tożsamość", oznacza to że mamy te same atrybuty i metody, ale różne obiekty (danego typu). Procedury związane związane z obiektem nazywane są metodami. Wzorce wg których tworzone są obiekty (typ obiektu) określany jest mianem klasy. Model ten umożliwia dziedziczenie, czyli tworzenie klas potomnych na bazie już istniejących przez ich modyfikację oraz zapewnia enkapsulację czyli ukrycie szczegółów implementacji tak aby nie były dostępne z zewnątrz ("to co w środku nieważne").

Drugą sprawą z teorii baz danych, którą trzeba poruszyć jest normalizacja danych i tzw. postać normalna. Ma to na celu unikanie powtórzeń, redukcję anomalii w modyfikacji danych oraz uproszczenie reguł integralności. Anomaliami nazywamy efekt uboczny modyfikacji, wstawiania lub usuwania danych pociągający za sobą zbyt duże zmiany. Wyróżnia się kilka postaci normalnych, każda kolejna jest tą bliższą ideałowi:

SQL

SQL jest językiem zapytań, wykorzystywanym do komunikacji z bazą danych, może być używany w trybie interaktywnym, lub poprzez zapytania wykonywane przez aplikację na potrzeby jej funkcjonowania. Istnieje wiele implementacji systemów bazodanowych korzystających z SQL, praktycznie każdy z nich posiada jakieś własne rozszerzenia tego standardu, lub nie implementuje pewnych jego fragmentów. Do pracy interaktywnej na ogół wykorzystuje się oprogramowanie opracowane specjalnie dla danej bazy danych i często dostarczane wraz z nią, natomiast do przesyłania zapytań z aplikacji wykorzystać można biblioteki dedykowane danemu serwerowi bazodanowemu (np. C API MySQL lub wbudowane biblioteki php dla baz danych) lub sterownik standardu ODBC.

SQL posiada polecenia umożliwiające tworzenie, modyfikowanie i usuwanie baz danych oraz tabel, jak również wstawiania, kasowania pobierania i modyfikowania danych, umożliwia także nakładanie ograniczeń (więzów) na wprowadzane dane. SQL nie rozróżnia wielkości liter w nazwach poleceń.

	-- utworzenie bazy danych
	CREATE DATABASE `TESTOWA`;
	
	-- zmiana nazwy bazy danych
	ALTER DATABASE `TESTOWA` RENAME TO `test-db`;
	
	-- wybranie jej jako domyślnej, inaczej do tabeli aaa trzeba by się odwoływać przez test-db.aaa
	USE `test-db`;
	
	-- tworzymy tabele uzytkownicy
	--  z określonym kluczem głównym na kolumnie UID, która jest automatycznie zwiększana
	--  indeksem na kolumnie name (która może mieć wartość nieokreśloną)
	--  unikalnością na kolumnie PESEL (która nie może być pusta)
	CREATE TABLE `uzytkownicy` (
		`UID` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
		`name` VARCHAR( 25 ) NULL ,
		`PESEL` BIGINT( 11 ) UNSIGNED NOT NULL ,
		INDEX ( `name` ) ,
		UNIQUE ( `PESEL` )
	);
	
	-- dodaliśmy kolumnę password typu tekstowego
	-- o zmiennej długości wynoszącej maksymalnie 30 znaków
	--  została ona wstawiona po kolumnie name
	ALTER TABLE `uzytkownicy` ADD `password` VARCHAR( 30 ) NOT NULL AFTER `name`;
	
	-- zmieniliśmy definicję kolumny name tak aby napisy porównywane były wg kodowania utf8
	--  z uwzględnieniem kolejności języka polskiego
	ALTER TABLE `uzytkownicy` CHANGE `name` `name` VARCHAR( 25 )
		CHARACTER SET utf8 COLLATE utf8_polish_ci NULL DEFAULT NULL;
	
	-- wymagamy aby uzytkownicy byli urodzeni przed 1992 rokiem
	--  (ze względu na budowę peselu i prostotę tego warunku dopuszczamy urodzonych po 1999 ...)
	ALTER TABLE `uzytkownicy` ADD CONSTRAINT `wiek` CHECK(PESEL<92000000000);
	
	-- tworzymy kolejna tabelkę - na dane teleadresowe
	CREATE TABLE `dane` (`UID` INT UNSIGNED NOT NULL,
		`type` TINYINT UNSIGNED NULL, `value` VARCHAR(50));
	
	-- dodajemy wiąz unikalności określający iż cała 
	--  kombinacja identyfikatora użytkownika typu i wartości pola ma być unikalna ...
	ALTER TABLE `dane` ADD CONSTRAINT `unikalnosc` UNIQUE(`UID`, `type`, `value`);
	
	-- bawimy się wstawianiem i modyfikacją danych
	INSERT INTO `uzytkownicy` VALUES (NULL, NULL, NULL, 91000000000);
	INSERT INTO `uzytkownicy` VALUES (NULL, NULL, "super tajne", 91000000000);
	
	INSERT INTO `dane` (`UID`, `value`) VALUES (0, NULL);
	INSERT INTO `uzytkownicy` VALUES (NULL, 'rrp', "super tajne", 10000000000);
	
	-- korzystamy z podzapytania przy wstawianiu
	INSERT INTO `dane` VALUES (
		(SELECT UID FROM `uzytkownicy` WHERE name='rrp'),
		1, 'rrp@localhost' );
	
	-- podzapytań możemy używać także w warunkach oraz w części zwracającej wyniki
	SELECT name FROM `uzytkownicy` WHERE UID=(SELECT max(UID) FROM `uzytkownicy`);
	
	-- robimy join'a (połączenie kilku tabelek), korzystając z aliasów
	SELECT u.UID, u.name, d.value FROM `uzytkownicy` AS u
		JOIN `dane` AS d ON u.UID=d.UID;
	SELECT u.UID, u.name, d.value FROM `uzytkownicy` AS u
		LEFT JOIN `dane` AS d ON u.UID=d.UID;
	
	SELECT COUNT(*) AS 'ilosc' FROM `dane`;
	SELECT COUNT(*) AS 'ilosc' FROM `dane` WHERE value IS NOT NULL;

(uwaga poniższe były testowane tylko w PostgresSQL, ale myślę że conajwyżej po niewielkich zmianach powinny zadziałać w MySQL)

	-- jeżeli przed dodaniem warunku unikalności były dane z nim sprzeczne, możemy je łatwo wyszukać:
	SELECT UID, type, value FROM dane GROUP BY UID,type,value HAVING count(UID)>1;

	-- dodajemy komentarz do kolumny
	COMMENT ON COLUMN `uzytkownicy`.PESEL IS 'w przyszłości zrobić kontrolę sumy';
	
	-- gdybyśmy zapomnieli o primary key i indeksie przy tworzeniu tabeli to możemy dadać do istniejącej:
	--  ALTER TABLE uzytkownicy ADD INDEX(name);
	--  ALTER TABLE uzytkownicy ADD PRIMARY KEY (UID);
	
	-- możemy także usuwać kolumny, więzy itd:
	--  ALTER TABLE uzytkownicy DROP COLUMN "name";
	
	-- dodawanie do tabeli klucza obcego, z kasowaniem kaskadowym
	--  usunięcie użytkownika z `uzytkownicy` spowoduje usunięcie jego wpisów z `dane`
	--   jakby było bez "ON DELETE CASCADE" to proba usunięcia gdy są wpisy w `dane`
	--   zakonczyłaby się błędem
	ALTER TABLE dane ADD FOREIGN KEY (UID) REFERENCES uzytkownicy(UID) ON DELETE CASCADE;

(uwaga poniższe były testowane tylko w MySQL)

	-- danie użytkownika TESTER z hałem ABCDEF i pełnymi prawami do bazy TESTOWA
	--  w PostgresSQL komenda createuser (więcej w man  createuser)
	--  np, createuser -s -d -U postgres USERNAME utworzy uzytkownika z pełnią praw
	CREATE USER 'TESTER'@'localhost' IDENTIFIED BY 'ABCDEF';
	GRANT USAGE ON * . * TO 'TESTER'@'localhost' IDENTIFIED BY 'ABCDEF' WITH \
		MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
	GRANT ALL PRIVILEGES ON `TESTOWA` . * TO 'TESTER'@'localhost';
	--  w PostgresSQL użytkownik ma domyślnie pełnie praw do bazy o takiej samej nazwie ...
	--  dodatkowe uprawnienia można przyznawać analogicznie do powyższego kodu - np.
	--   GRANT ALL PRIVILEGES ON tabela TO user;
	--  wyświetlenie uprawnień \dp tabela
	
	-- zmiana hasła użytkownika
	UPDATE USER SET password=password('GHIJKL') WHERE user='AAAAAA';
	
	-- zmiana swojego hasła
	SET password = password('jakieshaselko');
	--  w PostgresSQL: alter user "nazwa_uzytkownika" with password 'jakieshaselko';
	
	-- przeładowanie uprawnień (wymagane aby działało nowe hasło)
	FLUSH PRIVILEGES;
	
	-- usunięcie użytkownika i przyznanych mu praw
	DROP USER 'AAAAAA'@'localhost';
	
	-- wyświetlenie baz danych
	--  w PostgresSQL polecenie \l w psql
	SHOW DATABASES;
	
	-- wyświetlenie tabel
	--  w PostgresSQL polecenie \d w psql
	--- w SQLlite  .tables
	SHOW TABLES;
	
	-- pokazanie struktury tabeli w SQLlite: .schema tabela
	
	-- wylosowanie zbioru rekordów (losowa kolejność 5 pierwszych)
	SELECT * FROM `uzytkownicy` ORDER BY rand() LIMIT 5;
	

SQL umożliwia ponadto deklarowanie zmiennych, funkcji i procedur działających po stronie serwera, tworzenie widoków ("wirtualnych" tabel opartych na zapytaniu o tabele istniejące), uruchamianie zaplanowanych działań w momencie wykonywania określonej operacji na bazie danych (trigger), korzystanie z transakcji (grup poleceń, które np. w wypadku niepowodzenia któregoś z nich mogą zostać cofnięte i są wykonywane w sposób niepodzielny). Powyższe przykłady zostały przygotowane dla MySQL 5. Więcej informacji można uzyskać korzystając np. z komendy help w konsoli wywołanej poleceniem mysql - np. help CREATE TRIGGER, help START TRANSACTION. Warto wspomnieć także o mysqldump zrzucającym wybraną (lub z opcja --all-databases wszystkie) bazę danych w postaci tekstowego SQL na standardowe wyjście oraz o pg_dump -cif nazawa_pliku bazadanych pełniącym analogiczną funkcję w PostgresSQL.

Zobacz też: dokumentacja składni SQL dla MySQL, MySQL @ Wikibooks.



Copyright (c) 1999-2015, Robert Paciorek (http://www.opcode.eu.org/), BSD/MIT-type license


Redystrybucja wersji źródłowych i wynikowych, po lub bez dokonywania modyfikacji JEST DOZWOLONA, pod warunkiem zachowania niniejszej informacji o prawach autorskich. Autor NIE ponosi JAKIEJKOLWIEK odpowiedzialności za skutki użytkowania tego dokumentu/programu oraz za wykorzystanie zawartych tu informacji.

This text/program is free document/software. Redistribution and use in source and binary forms, with or without modification, ARE PERMITTED provided save this copyright notice. This document/program is distributed WITHOUT any warranty, use at YOUR own risk.

Valid XHTML 1.1 Dokument ten (URL: http://www.opcode.eu.org/programing/databases) należy do serwisu OpCode. Autorem tej strony jest Robert Paciorek, wszelkie uwagi proszę kierować na adres e-mail serwisu: webmaster@opcode.eu.org.
Data ostatniej modyfikacji artykulu: '2015-10-10 11:34:00 (UTC)' (data ta może być zafałszowana niemerytorycznymi modyfikacjami artykułu).