Transcript mysql
Relacyjne Bazy Danych
Wykład 02
Wojciech St. Mościbrodzki
[email protected]
Encje - przypomnienie
Encja i encja-typ
każda unikalna (identyfikowalna) rzecz
posiada atrybuty (cechy) przyjmujące określone wartości
zbiór wartości atrybutu, nazywamy dziedziną
encja (ściślej: encja-typ) to zbiór wszystkich encji identyfikowalnych przez ten
sam zbiór atrybutów
Przykłady:
Jan Kowalski = encja
cechy (atrybuty) Jana Kowalskiego = {wzrost, kolor oczu, imię, nazwisko}
wartości atrybutów Jana Kowalskiego = {174, zielony, Jan, Kowalski}
student (np. Jan Kowalski) = encja (ściślej: encja-typ)
atrybut encji student: kolor oczu
wartości atrybutu kolor oczu (dziedzina atrybutu) = {zielony, piwny, niebieski}
Encja - identyfikowalność
klucz kandydujący
nieformalnie: atrybuty, których wartości identyfikują encje
formalnie: to każdy zbiór atrybutów, który posiada dwie własności:
• wartości atrybutów są różne dla każdej encji
• usunięcie dowolnego atrybutu ze zbioru, sprawia, że warunek powyżej nie
jest spełniony
klucz główny
wybrany (arbitralnie) jeden z kluczy kandydujących
Klucze kandydujące: {imię}, {semestr, miasto}, {semestr, wiek} itd.
Diagram Związków Encji (ERD)
ERD służy do modelowania danych i związków pomiędzy nimi
ENCJA
atrybut
wyliczony
klucz zew.
klucz
Formalnie – na diagramach ER NIE OZNACZAMY TYPÓW DANYCH
encja
klucz główny
atrybut
Diagram Związków Encji (ERD) - RELACJE
ERD służy do modelowania danych i związków pomiędzy nimi
różne notacje
(różni autorzy)
struktura pól
Klasyczna Krucza Stopka
krotność relacji
Notacja Chena
ENCJA2
ENCJA 1
1
1
ENCJA2
ENCJA2
ENCJA 1
1
0
ENCJA2
ENCJA 1
ENCJA2
ENCJA 1
N
1
ENCJA2
ENCJA 1
ENCJA2
ENCJA 1
N
N
ENCJA2
ENCJA 1
ENCJA 1
0
Notacja J. Martina
Notacja Barkera
ENCJA 1
ENCJA2
ENCJA 1
ENCJA2
ENCJA 1
ENCJA2
ENCJA 1
ENCJA2
ENCJA 1
ENCJA2
ENCJA 1
ENCJA2
ENCJA 1
ENCJA2
ENCJA 1
ENCJA2
Diagram Związków Encji (ERD) – przypadki szczególne
Encja słaba
nagłówek faktury
encja słaba to taka encja, która nie jest
rozróżnialna (identyfikowalna) przez własne
atrybuty (wymaga klucza zewnętrznego)
numer
data
linia faktury
numer
towar
ilość
ilość
Asocjacja (encja asocjacyjna, relacja wiele-do-wiele)
asocjacja to relacja wiele-do-wiele (posiada cechy encji, może mieć atrybuty)
w bazie danych odpowiada jej osobna tabela, zwana tabelą pośredniczącą)
ENCJA 1
ENCJA2
ENCJA 1
ENCJA2
ENCJA 1
ENCJA2
Model logiczny danych (ERD) a model fizyczny
Model logiczny:
zawiera informacje o encjach i relacjach
nie zawiera informacji o typach danych (np. na poziomie SQL)
jest niezależny od bazy danych
Model fizyczny:
zawiera informacje o encjach, relacjach i typach
określa typy danych
jest związany z implementacją
Podstawy SQL
Programowanie w SQL
SQL nie jest językiem programowania – jest językiem zapytań
'70s
'87
SEQEL
System R
(IBM)
RDMS
(MIT)
quel
Ingres
(Berkley)
SQL
Oracle
(Oracle)
SQL-86 (standard ANSI): w kierunku wieloplatformowości
SQL-92: kursory, ALTER, DATA, TIMESTAMP, VARCHAR...
'90s
SQL-1999 (SQL3): triggery, BOOLEAN, control-flow (IF...) ...
SQL-2003: funkcje okien danych, XML, MERGE
SQL-2006: rozbudowa XML, funkcje WWW
'00s
SQL-2008: INSTEAD, TRUNCATE...
Programowanie z użyciem SQL
Metody na "uprogramowienie" SQL:
Embedded SQL (SQL osadzony):
• konieczny odpowiedni kompilator
• wartości przenoszone przez uwspólnione zmienne (widziane w SQL i w
języku programowania)
Modular SQL (moduły SQL dla języków programowania)
• konieczne są odpowiednie moduły dołączane
• przekazywanie wartości zależne od języka
Dynamic SQL (część "programistyczna" w innym języku – np. PHP)
• wymaga odpowiednich konstrukcji językowych
Programming Extensions for SQL (rozbudowa składni SQL – poza standardem
ANSI)
• wymaga serwera SQL rozumiejące "rozbudowaną" składnię
• np. PL/SQL (Oracle), PSM (MySQL), PL PSM (PostgreSQL)
Podstawy SQL i pracy z klientem MySQL
Polecenia klienta MySQL:
są specyficzne dla dialektu MySQL - NIE NALEŻĄ do standardu SQL
w rzeczywistości są poleceniami, które klient "tłumaczy" na zwykłe zapytania
SELECT i kieruje do systemowej bazy danych
mysql> show tables;
+------------------+
| Tables_in_wojmos |
+------------------+
| czlowiek
|
| faktura
|
+------------------+
mysql> show databases;
+--------------------+
| Database
|
+--------------------+
| information_schema |
| gcs
|
| kardio
|
| mysql
|
| wojmos
|
+--------------------+
mysql> explain czlowiek;
+----------+----------+------+-----+---------+-------+
| Field
| Type
| Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| imie
| char(30) | YES |
| NULL
|
|
| nazwisko | char(50) | YES |
| NULL
|
|
+----------+----------+------+-----+---------+-------+
Podstawowe typy zapytań
Zapytania wyszukujące dane:
Zapytania modyfikujące dane:
INSERT
DELETE
UPDATE
Zapytania modyfikujące bazę danych i jej struktury:
SELECT
CREATE
DROP
ALTER
GRANT / REVOKE
Pseudozapytania MySQL:
SHOW, EXPLAIN, DESCRIBE
CREATE DATABASE
Polecenie CREATE służy do tworzenia baz:
CREATE {DATABASE} [IF NOT EXISTS] db_name[create_specification]
create_specification:
[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name
W dokumentacji obowiązują następujące konwencje:
{xxx} – xxx musi wystąpić
xxx | yyy – musi wystąpić xxx ALBO yyy
[xxx] – xxx może wystąpić (albo i nie)
zzz: - wyjaśnienie symbolu zzz
[ xxx | yyy ] – może wystąpić xxx ALBO yyy albo nic
{ xxx | yyy } – musi wystąpić xxx ALBO yyy
CREATE TABLE
Polecenie CREATE TABLE służy do tworzenia tablic:
CREATE TABLE {tablica} [IF NOT EXISTS] (specyfikacja) [opcje]
specyfikacja: nazwa_kolumny, definicja_kolumny
definicja_kolumny: typ [NULL|NOT NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string']
[reference_definition]
CREATE TABLE player (
ID int auto_increment primary key,
name char(50) NOT NULL,
dex int,
str int,
cha int
);
Podstawowe typy danych SQL
Typy danych języka SQL:
numeryczne:
• całkowite: INT, TINYINT, SMALLINT, MEDIUMINT, BIGINT
• rzeczywiste: FLOAT(M,D), DOUBLE(M,D), DECIMAL(M,D)
• logiczne: BOOL,
znakowe:
• łańcuchy krótkie: CHAR, VARCHAR
• obiekty tekstowe: TEXT
daty i czasu:
• czasowe: DATE, TIME, DATETIME, YEAR
specjalne:
• znacznik czasowy: TIMESTAMP
• obiekt binarny: BLOB
• wyliczeniowy: ENUM
Zapytania typu SELECT
Typowy SELECT składa się z:
wskazania kolumn
wskazania tabeli FROM
filtru rekordów WHERE warunek
żądania sortowania ORDER BY
select imie from czlowiek;
+-------+
| imie |
+-------+
| Roman |
| Roman |
+-------+
mysql> select distinct imie from
czlowiek where imie like 'jan%';
+--------+
| imie
|
+--------+
| Jan
|
| Janusz |
+--------+
mysql> select * from czlowiek;
+-------+-------------+
| imie | nazwisko
|
+-------+-------------+
| Roman | Pipokucki
|
| Roman | Popopizacki |
+-------+-------------+
mysql> select * from czlowiek order by imie DESC, nazwisko ASC;
+-------+-----------+
| imie | nazwisko |
+-------+-----------+
| Roman | Kukurocki |
| Roman | Kupikucki |
+-------+-----------+
Zapytania typu INSERT
Zapytania typu INSERT dodają do tablicy dane
INSERT
[LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
{VALUES | VALUE} ({expr | DEFAULT},...),(...),...
insert into player (name, dex, str, cha) values ('Raven',18,8,10);
Query OK, 1 row affected (0.02 sec)
insert into czlowiek values ('Jan','Nowak');
Query OK, 1 row affected (0.02 sec)
Specjalna forma INSERT (SELECT AS INSERT):
insert into player (name) select imie from czlowiek;
Zapytania typu UPDATE
UPDATE służy do aktualizacji (modyfikacji) rekordów
UWAGA1: WHERE nie jest konieczne do działania UPDATE
UWAGA2: brak WHERE może przynieść opłakane skutki
UPDATE table
SET
col_name1={expr1|DEFAULT}
[, col_name2={expr2|DEFAULT}]
...
[WHERE where_condition]
update player set dex=20 where name like 'Raven';
update pracownik set placa=placa+200 where name like 'Kowalski';
update pracownik set placa=placa+200 where ID=23445;
update pracownik set placa=placa+200;
Polecenie ALTER służy do zarządzania strukturą
Polecenie ALTER służy do zarządzania strukturą:
ALTER TABLE tbl_name alter_specification [, alter_specification] ...
alter_specification:
ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name ]
| ADD {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
| ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type]
(index_col_name,...) [index_type]
| ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...)
reference_definition
| CHANGE [COLUMN] old_col_name new_col_name column_definition
| MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
| DROP [COLUMN] col_name
| DROP PRIMARY KEY
| DROP {INDEX|KEY}
| RENAME [TO] new_tbl_name
| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
| [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]