Bazy DanychW03

Wojciech St. Mościbrodzkiwojmos@pjwstk.edu.pl

Podstawy SQL

SQL jest językiem stosunkowo prostym (i archaicznym) Założeniem SQL była realizacja podstawowych operacji

bazodanowych: wyszukiwanie rekordów - SELECT dodawanie rekordów - INSERT usuwanie rekordów - DELETE modyfikacja rekordów - UPDATE

Poza „wielką czwórką” istnieją polecenia do zarządzania tabelami, bazami, tworzące indeksy, itp.

Polecenia wydawane w SQL nazywamy zapytaniami SQL (a nie – kwerendami; terminologia Microsoft)

SQL jako język zapytań

Typowy podział poleceń (zapytań) SQL: SQL DML (ang. Data Manipulation Language – „język manipulacji danymi”),

• SELECT, INSERT, UPDATE, DELETE SQL DDL (ang. Data Definition Language – „język definicji danych”),

• CREATE, DROP, ALTER, … SQL DCL (ang. Data Control Language – „język kontroli nad danymi”).

• GRANT, REVOKE, …

Inny podział: polecenia standardowego (lub dialektowego) SQL (wykonuje je serwer) polecenia typowe dla serwera konkretnego producenta (wykonuje je serwer) polecenia dla klienta (oprogramowanie klienckie) – np. DELIMITER (mysql)

UWAGA: średnik (;) nie jest elementem zapytania, tylko znakiem terminalnym (po jego odebraniu klient przesyła zapytanie do serwera)

Podstawy SQL

Typy danych: Numeryczne (mogą być SIGNED lub UNSIGNED):

• Całkowite: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT• Rzeczywiste: FLOAT, DOUBLE, DECIMAL, NUMERIC• Bitowe: BIT• Logiczne: BOOL lub BOOLEAN (implementowane w Mysql jako TINYINT(1))

Tekstowe:• CHAR – 0..255 znaków, przy przechowywaniu uzupełniane spacjami do

podanego rozmiaru• VARCHAR – 0..65535 znaków, zajmują długość łańcucha + informacja o

dłougości (1 lub 2 bajty)• BINARY i VARBINARY – przechowują łańcuchy w postaci binarnej• TEXT – duży obiekt tekstowy (BLOB – wersja binarna)• ENUM – typ wyliczeniowy, elementami są napisy• SET – typ specjalny, może zawierać dowolną liczbę (!!!) wartości ze zbioru

Typy danych: Typy związane z czasem:

• DATETIME – data (domyślnie) w formacie: YYYY-MM-DD HH:MM:SS • DATE – data w formacie: YYYY-MM-DD • TIME – czas w formacie: HH:MM:SS

Typy specjalne:• BLOB – duże obiekty binarne (np. obrazki, pliki mp3)• TIMESTAMP – specjalny typ (znacznik czasowy, w formacie DATETIME)

Podstawowe formy najważniejszych zapytań

SELECT – zapytanie zwracające zbiór kolumn z podanej tabeli

SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] select_expr [, select_expr ...] [FROM table_references]

SELECT nazwisko FROM student;SELECT imie, nazwisko FROM pracownik;SELECT DISTINCT imie FROM dostawca;SELECT COUNT(id) FROM klient;

Wynikiem zapytania SELECT jest zbiór danych w postaci tabelimysql> select * from student;+----+------------+--------+| id | nazwisko | miasto |+----+------------+--------+| 1 | Kowalski | Gdansk || 2 | Nowak | Poznan || 3 | Malinowski | Krakow |+----+------------+--------+3 rows in set (0.00 sec)

mysql> select count(*) from user;+----------+| count(*) |+----------+| 4 |+----------+1 row in set (0.00 sec)

SELECT i WHERE i ORDER BY

WHERE jest klauzulą filtra, nakładanego na wynik zapytania (PO wyliczeniu jego wyników)

ORDER BY jest poleceniem posortowania ostatecznego rezultatuSELECT select_expr [, select_expr ...] [FROM table_references][WHERE where_condition][ORDER BY {col_name | expr | position} [ASC | DESC], ...]

mysql> select nazwisko from student where id<=2;+----------+| nazwisko |+----------+| Kowalski || Nowak |+----------+

mysql> select distinct miasto from student order by miasto asc;+--------+| miasto |+--------+| Gdansk || Krakow |+--------+

INSERT – wstawianie rekordów

INSERT – wersja klasycznaINSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),...

INSERT [INTO] tbl_name SET col_name={expr | DEFAULT}, ...

INSERT INTO student SET nazwisko='Piotrowski';

INSERT INTO student VALUES (13,'Mazan','Pruszkowo');INSERT INTO student(nazwisko, miasto) VALUES ('Kowalewski','Warszawa');

INSERT [INTO] tbl_name [(col_name,...)] SELECT ...

mysql> insert into student(nazwisko) select n from pracownik;Query OK, 6 rows affected (0.00 sec)Records: 6 Duplicates: 0 Warnings: 0

INSERT – wersja z SET (podobna do UPDATE)

INSERT – wersja z SELECT

INSERT - uwagi

Wersja INSERT bez jawnego podawania kolumn wymaga, abyśmy określili wartości wszystkich istniejących w tabeli kolumn

Istnieje możliwość wstawiania jednym INSERTem wielu rekordów Jeśli kolumna ma oznaczenie DEFAULT, w przypadku braku

wartości w INSERT, zostanie wstawiona wartość domyślna

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

Spójność relacyjna

Spójność relacyjna (relational integrity) – sytuacja, w której dane są w takiej postaci, która jest "wysokiej jakości":

Spójność encji (entity integrity) – każde dwa elementy danych (obiekty) są rozróżnialne. Innymi słowy: istnieje klucz główny, a wartości tego klucza są ustalone (nie występuje NULL, czyli wartość nieustalona)

Spójność wartości nieustalonych (null integrity) – dla określonych danych wartość NULL jest nieakceptowalna

Spójność dziedzinowa (domain integrity) – wartości atrybutów są w dopuszczalnym zakresie (np. wiek nie może być ujemny, liczba pracowników – wyrażona ułamkiem itp.). Spójność domenowa sugeruje, że dane z różnych dziedzin nie powinny być porównywane "wprost".

Spójność referencyjna (referential integrity) – odnosi się do relacji pomiędzy dwoma tabelami; wymaga aby wartości klucza obcego odpowiadały istniejącym wartościom tabeli wskazywanej (klucz główny)

Spójność referencyjna

Przypomnienie: Klucz kandydujący – jeden z zestawów atrybutów, który może pełnić rolę klucza Klucz główny – wybrany arbitralnie klucz spośród kluczy kandydujących Klucz obcy – zestaw atrybutów, który odpowiada kluczowi głównemu innej tabeli

Naruszenie spójności referencyjnej

Naruszenia spójności DELETE: usunięcie rekordu, do którego ID jest wartością klucza obcego UPDATE: zmiana wartości klucza obcego na nieistniejący (brak wartości klucza

głównego) lub zmiana wartości klucza głównego, któremu odpowiada klucz obcy INSERT: wstawienie rekordu, którego klucz obcy odpowiada nieistniejącej

wartości klucza głównego

Więzy integralności

Obsługa więzów integralności w mysql (UWAGA: tylko InnoDB):[CONSTRAINT] FOREIGN KEY (index_col_name, ...) REFERENCES tbl_name (index_col_name,...) [ON DELETE reference_option] [ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION

create table dzial ( id int auto_increment primary key, nazwa char(10)) ENGINE=InnoDB;create table pracownik ( id int auto_increment primary key, imie char(10), nazwisko char(15), id_dzi int, CONSTRAINT FOREIGN KEY (id_dzi) REFERENCES dzial(id)) ENGINE=InnoDB;

Tabela "główna" musi istnieć podczas tworzenia tabeli "potomnej":

Usuwanie tabel również wymaga odpowiedniej kolejności!

Spójność referencyjna

Więzy typu RESTRICT (w MySQL = NO ACTION) zabraniają wykonania operacji

create table pracownik ( id int auto_increment primary key, nazwisko char(15), id_dzi int, CONSTRAINT FOREIGN KEY (id_dzi) REFERENCES dzial(id)) ENGINE=InnoDB;

insert into pracownik values (1,'Jan','Nowak',1);ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`rbd3/pracownik`, CONSTRAINT `pracownik_ibfk_1` FOREIGN KEY (`id_dzi`) REFERENCES `dzial` (`id`))

mysql> delete from dzial where id=1;ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`rbd3/pracownik`, CONSTRAINT `pracownik_ibfk_1` FOREIGN KEY (`id_dzi`) REFERENCES `dzial` (`id`))

Spójność referencyjna

Więzy typu CASCADE przenoszą operacje (DELETE lub CASCADE) z rekordów tabeli "rodzica" na tabelę "potomną"

create table pracownik ( id int auto_increment primary key, nazwisko char(15), id_dzi int, CONSTRAINT FOREIGN KEY (id_dzi) REFERENCES dzial(id) ON DELETE CASCADE) ENGINE=InnoDB;

mysql> select count(id) from pracownik; delete from dzial where id=1; select count(id) from pracownik;+-----------+| count(id) |+-----------+| 4 |+-----------++-----------+| count(id) |+-----------+| 2 |+-----------+

Spójność referencyjna

Więzy typu SET NULL po operacji DELETE/UPDATE na rekordach tabeli "rodzica" ustawiają wartości klucza obcego na NULL

create table pracownik ( id int auto_increment primary key, nazwisko char(15), id_dzi int, CONSTRAINT FOREIGN KEY (id_dzi) REFERENCES dzial(id) ON DELETE CASCADE) ENGINE=InnoDB;

+----+--------+--------+| id | serial | id_pra |+----+--------+--------+| 1 | NP3451 | 1 || 2 | ED2221 | 2 || 3 | AP1111 | 3 || 4 | AP1311 | 3 || 5 | AS8751 | 4 || 6 | SU9458 | 3 |+----+--------+--------+

select * from telefon; delete from pracownik where id=3;

+----+--------+--------+| id | serial | id_pra |+----+--------+--------+| 1 | NP3451 | 1 || 2 | ED2221 | 2 || 3 | AP1111 | NULL || 4 | AP1311 | NULL || 5 | AS8751 | 4 || 6 | SU9458 | NULL |+----+--------+--------+

Spójność dziedzinowa - CHECK

CHECK zapewnia ochronę przed wstawieniem danych nie spełniających określonego warunku

CREATE TABLE user(    id auto_increment PRIMARY KEY,    login VARCHAR(16),    password VARCHAR(20), CONSTRAINT CHECK(LENGTH(password) BETWEEN 8 AND 16))

CREATE TABLE Person( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes'))