Transcript Document
SQL – Structured Query
Language (1)
Wykład 5
Prowadzący: dr Paweł Drozda
Zadania SQL
definiowanie danych
definiowanie perspektyw
przetwarzanie danych (interaktywne i
programowe)
definiowanie reguł integralności danych
autoryzacja
określanie początku transakcji,
potwierdzenie i wycofywanie transakcji
dr P. Drozda
Cechy SQL-a
„podjęzyk danych”
Trójwartościowa logika (TRUE, FALSE,
UNKNOWN)
Trzy sposoby użycia:
Interaktywny lub samodzielny SQL – do
wprowadzania lub wyszukiwania informacji
Statyczny SQL – stały kod SQL napisany przed
wykonaniem programu – dwa typy:
„Zanurzony” SQL
Modułowy SQL
Dynamiczny SQL – kod generowany przez
aplikacje w zależności od wyborów użytkownika
dr P. Drozda
Grupy instrukcji
Język definicji danych (DDL):
CREATE, ALTER, DROP
Język manipulowania danymi (DML):
SELECT, INSERT, UPDATE i DELETE
Instrukcje Sterowania Danymi:
GRANT i REVOKE
dr P. Drozda
Data Definition Language (DDL)
Tworzenie tabel, baz danych, itd.
CREATE
Modyfikacja schematu bazy danych –
ALTER
Usuwanie tabel, baz danych itd. DROP
dr P. Drozda
Polecenie CREATE
Tworzenie bazy danych
CREATE database nazwa_bazy;
Przykład: CREATE database restauracja;
Tworzenie tabeli
CREATE table nazwa(pole1 typ_danych1
ograniczenia1, pole2 typ_danych2
ograniczenia2, …, poleN typ_danychN
ograniczeniaN, ograniczeniaOgólne);
dr P. Drozda
Typy Danych (1)
Znakowe
CHAR(n), CHARACTER(n) – tekst o stałej ilości
znaków
VARCHAR(n), CHARACTER VARYING(n) – tekst o
zmiennej ilości znaków – max n
BINARY(n), VARBINARY(n) –odpowiedniki CHAR i
VARCHAR tyle, że używają postaci binarnej do
zapisu ciągu znaków
TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT –
pozwalają wprowadzić dłuższy tekst
TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB –
pozwalają przechowywać duży binarny obiekt
dr P. Drozda
Typy Danych (2)
Znakowe
ENUM(‘war1’, ‘war2’,’war3’,…,’warN’) – typ
wyliczeniowy określa dokładnie zbiór możliwych
wartości
SET(‘war1’, ‘war2’,’war3’,…,’warN’) – zbiór
możliwych wartości – można wybrać więcej niż
jedną
Przykład deklaracji relacji:
CREATE table Osoby(Nazwisko Varchar(20), Rasa
Enum(‘Biała’, ‘Czarna’,’Żółta’), opis MediumText);
dr P. Drozda
Typy Danych (3)
Liczbowe
Całkowite
Typ
Liczba bajtów
min liczba
max liczba
TINYINT
1
-27/0
27-1/28-1
SMALLINT
2
-215/0
215-1/216-1
MEDIUMINT
3
-223/0
223-1/224-1
INT
4
-231/0
231-1/232-1
BIGINT
8
-263/0
263-1/264-1
Ograniczenia nakładane na liczby całkowite
ZEROFILL – wstawia zera w niewykorzystane
pola
UNSIGNED – dozwolone liczby nieujemne
AUTO_INCREMENT – przy wstawieniu 0 lub
null automatycznie wstawiana kolejna liczba
dr P. Drozda
Typy Danych (4)
Przykład:
CREATE table Pracownicy(id_prac Smallint(3)
zerofill auto_increment, Nazwisko Char(20),
Zarobki Mediumint unsigned);
PRACOWNICY
Id_prac
Nazwisko
dr P. Drozda
Zarobki
Typy Danych (5)
Liczbowe
Zmiennoprzecinkowe
FLOAT(D,M) – typ pojedynczej precyzji (4 bajty),
D - liczba wyświetlanych cyfr
M - ilość wyświetlanych cyfr po przecinku
DOUBLE(D,M), REAL(D,M) – typ podwójnej
precyzji (8 bajtów)
D, M – jw.
NUMERIC(D,M), DECIMAL(D,M) – dokładna
precyzja, gdzie D – ilość cyfr znaczących, M –
ilość cyfr znaczących po przecinku
dr P. Drozda
Typy danych (6)
Typy daty i czasu
Typ
DATE
TIME
DATETIME
TIMESTAMP[(M)]
YEAR[(2|4)]
Formaty dla Timestamp
Podany typ
Format
Format wyświetlania
TIMESTAMP
RRRRMMDDGGMMSS
TIMESTAMP(14)
RRRRMMDDGGMMSS
TIMESTAMP(12)
RRMMDDGGMMSS
TIMESTAMP(10)
RRMMDDGGMM
TIMESTAMP(8)
RRRRMMDD
TIMESTAMP(6)
RRMMDD
TIMESTAMP(4)
RRMM
TIMESTAMP(2)
RR
RRRR-MM-DD.
GG:MM:SS.
RRRR-MM-DD GG:MM:SS
W zależności od M
RR lub RRRR
dr P. Drozda
Typy danych (7)
Przykład
CREATE table Osoby(id_osoby Smallint
auto_increment, Nazwisko Varchar(23),
data_ur Date, czas_maratonu Time,
rok_rozp YEAR(4), zarobki Decimal(7,2))
dr P. Drozda
Ograniczenia(1)
NOT NULL – wymusza wpisanie wartości dla
danego pola
UNIQUE – wartości w danym polu nie mogą się
powtarzać
CHECK (warunek) – nakłada warunek na relację
DEFAULT wartość – domyślnie wartość
Przykład
CREATE table Pracownicy(id_prac Smallint(3) zerofill
auto_increment, Nazwisko Varchar(25) Unique, zarobki
Decimal(7,2) Default 1500, Check(zarobki>0))
dr P. Drozda
Ograniczenia(2)
PRIMARY KEY – definicja klucza
głównego; może być definiowany przy
polu które jest kluczem, bądź na koniec
relacji – gdy więcej pól niż jedno
FOREIGN KEY (nazwa_pola)
REFERENCES
nazwa_tabeli(nazwa_pola1) – klucz obcy
dr P. Drozda
Ograniczenia(3)
Przykład definicji kluczy
1) CREATE table Pracownicy(id_prac
Smallint(3) auto_increment, Nazwisko
Varchar(25) Unique, zarobki Decimal(7,2),
PRIMARY KEY(id_prac));
2) CREATE table Projekty(nr_projektu
Smallint Primary Key, nazwa char(20),
kierownik Smallint, Foreign key(kierownik)
References Pracownicy(id_prac));
dr P. Drozda
Ograniczenia(4)
Wymuszanie więzi integralności
usuwanie
a) FOREIGN KEY(pole1) REFERENCES
tabela(pole2) ON DELETE SET NULL
b) FOREIGN KEY(pole1) REFERENCES
tabela(pole2) ON DELETE CASCADE
c) FOREIGN KEY(pole1) REFERENCES
tabela(pole2) ON DELETE SET DEFAULT
d) FOREIGN KEY(pole1) REFERENCES
tabela(pole2) ON DELETE RESTRICT
dr P. Drozda
Ograniczenia(5)
Modyfikacja – analogicznie (zamiast ON DELETE – ON
UPDATE)
Uwaga do MySQL 5.0 – engine = INNODB
Przykład
1) CREATE table Pracownicy(id_prac Smallint(3)
auto_increment PRIMARY KEY, Nazwisko Varchar(25)
Unique, zarobki Decimal(7,2));
2) CREATE table Projekty(nr_projektu Smallint Primary
Key, nazwa char(20), kierownik Smallint, Foreign
key(kierownik) References Pracownicy(id_prac) ON
DELETE CASCADE ON UPDATE SET DEFAULT) engine=
INNODB;
dr P. Drozda
Silniki składowania danych
MyISAM – domyślny silnik
Przechowywana na dysku w trzech plikach
Format tabeli – rozszerzenie .frm
Dane – rozszerzenie .myd
Indeks – rozszerzenie .myi
Tworzenie tabeli MyISAM
CREATE TABLE t (i INT) ENGINE/TYPE = MYISAM;
Zapewnia możliwość jednoczesnego wstawiania danych
Możliwość indeksowania kolumn typu TEXT i BLOB
Dopuszczalny null dla indeksowanych kolumn
Używane indeksy typu B-drzewo
dr P. Drozda
MyISAM engine
Trzy typy składowania danych
Statyczny
może być użyty gdy nie ma kolumn typu TEXT, BLOB
ma ustaloną z góry liczbę bajtów na każdy wiersz danych
Bardzo szybki dostęp do danych
Łatwe w rekonstrukcji po awarii
Przykład
CREATE TABLE t(int i) ENGINE = MyISAM, ROW_FORMAT=FIXED;
Dynamiczny
Wielkość pól dynamiczna
Zajmuje mniej miejsca od tabel statycznych
Trudne do rekonstrukcji po awarii
Przykład:
CREATE TABLE t(int i) ENGINE = MyISAM, ROW_FORMAT=DYNAMIC;
Spakowany – za pomocą komendy myisampack
dr P. Drozda
InnoDB engine
Pozwala na wywołanie transakcji
Zapewnia referencyjne więzi integralności
Długi czas przetwarzania tabel
Przykład:
CREATE TABLE t(int i) ENGINE = InnoDB;
dr P. Drozda
Modyfikacja schematu relacji
ALTER TABLE – dodawanie, usuwanie
atrybutów oraz ograniczeń
integralnościowych, modyfikacja definicji
atrybutu
Przykład:
ALTER TABLE Pracownicy ADD Primary
Key(Id_prac);
dr P. Drozda
ALTER TABLE - dodawanie
Dodawanie kolumny
ALTER TABLE nazwa_tabeli ADD COLUMN pole
typ_pola;
Przykład:
ALTER TABLE Pracownicy ADD COLUMN
stanowisko VARCHAR(20) AFTER NAZWISKO;
Dodawanie ograniczenia
ALTER TABLE nazwa ADD CONSTRAINT nazwa i
rodzaj ograniczenia (PRIMARY KEY, FOREIGN
KEY, CHECK, itd.)
dr P. Drozda
ALTER TABLE - usuwanie
Usuwanie kolumny
ALTER TABLE nazwa DROP COLUMN pole
Usuwanie ograniczenia
ALTER TABLE nazwa DROP CONSTRAINT
nazwa_ograniczenia;
Przykład
ALTER TABLE Pracownicy DROP
CONSTRAINT Klucz;
dr P. Drozda
ALTER TABLE - modyfikowanie
Tylko do atrybutów
ALTER TABLE nazwa MODIFY pole typ
ograniczenia;
Przykład
ALTER TABLE Pracownicy MODIFY Nazwisko
Char(30) not null;
Zmiana nazwy i typu atrybutu
ALTER TABLE nazwa CHANGE starepole
nowepole typ ograniczenia
dr P. Drozda
ALTER TABLE - modyfikacja
Silnika
ALTER TABLE t ENGINE = InnoDB;
Wartość pola auto_increment
ALTER TABLE t AUTO_INCREMENT = value;
dr P. Drozda
Usuwanie, kontrola integralności
Usunięcie tabeli
DROP TABLE nazwa_tabeli;
Usunięcie bazy danych
DROP DATABASE nazwa_bazy;
Stosowanie ograniczeń - klucze
ALTER TABLE nazwa ENABLE KEYS;
Wyłączanie ograniczeń - klucze
ALTER TABLE nazwa DISABLE KEYS;
dr P. Drozda
Zmiana nazwy
RENAME TABLE tabela TO tabela1;
Przykład
RENAME TABLE Pracownicy TO
naukowcy;
dr P. Drozda