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]