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