Transcript Document
Wykład 3
Prowadzący: dr Paweł Drozda
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
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
Tworzenie tabel, baz danych, itd. CREATE
Modyfikacja schematu bazy danych – ALTER
Usuwanie tabel, baz danych itd. - DROP
dr P. Drozda
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
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
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
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
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
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 daty i czasu
Typ
DATE
TIME
DATETIME
TIMESTAMP[(M)]
YEAR[(2|4)]
Formaty dla Timestamp
Format
Podany typ
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Silnika
ALTER TABLE t ENGINE = InnoDB;
Wartość pola auto_increment
ALTER TABLE t AUTO_INCREMENT = value;
dr P. Drozda
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
RENAME TABLE tabela TO tabela1;
Przykład
RENAME TABLE Pracownicy TO naukowcy;
dr P. Drozda