Transcript Slajd 1 - Wojciech Mościbrodzki

Bazy Danych
W03
Wojciech St. Mościbrodzki
[email protected]
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:




Inny podział:




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, …
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
SELECT
SELECT
SELECT

nazwisko FROM student;
imie, nazwisko FROM pracownik;
DISTINCT imie FROM dostawca;
COUNT(id) FROM klient;
Wynikiem zapytania SELECT jest zbiór danych w postaci tabeli
mysql> 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 rezultatu
SELECT 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 klasyczna
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
{VALUES | VALUE} ({expr | DEFAULT},...),(...),...
INSERT INTO student VALUES (13,'Mazan','Pruszkowo');
INSERT INTO student(nazwisko, miasto) VALUES ('Kowalewski','Warszawa');

INSERT – wersja z SET (podobna do UPDATE)
INSERT [INTO] tbl_name
SET col_name={expr | DEFAULT}, ...
INSERT INTO student SET nazwisko='Piotrowski';

INSERT – wersja z SELECT
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 - 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

Tabela "główna" musi istnieć podczas tworzenia tabeli "potomnej":
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;

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;
select * from telefon; delete from pracownik where id=3;
+----+--------+--------+
| id | serial | id_pra |
+----+--------+--------+
| 1 | NP3451 |
1 |
| 2 | ED2221 |
2 |
| 3 | AP1111 |
3 |
| 4 | AP1311 |
3 |
| 5 | AS8751 |
4 |
| 6 | SU9458 |
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')
)