Transcript Tworzenie bazy danych – mySQL • Sposób utworzenia bazy danych w mySQL: – uruchomienie programu: mysql – utworzenie bazy: CREATE DATABASE nazwa; – przełączenie się.

Tworzenie bazy danych – mySQL
• Sposób utworzenia bazy danych w mySQL:
– uruchomienie programu: mysql
– utworzenie bazy:
CREATE DATABASE nazwa;
– przełączenie się do bazy:
USE nazwa;
– teraz można utworzyć tabele – utworzenie tabeli
wymaga podania nazw pól (kolumn) oraz ich typów.
1
Operacje na bazach danych
• Tworzenie bazy danych:
• CREATE DATABASE baza;
• Usuwanie całej bazy:
• DROP DATABASE baza;
• Wyświetlenie istniejących baz danych:
• SHOW DATABASES;
• Przełączenie się na inną bazę danych:
• USE baza;
2
Tworzenie bazy danych – mySQL
• CREATE DATABASE SQL_w_3_tygodnie;
• Use SQL_w_3_tygodnie;
• DROP DATABASE ............;
3
Typy danych
• Typy danych mySQL – liczby całkowite:
–
–
–
–
–
TINYINT (1 bajt)
SMALLINT (2 bajty)
MEDIUMINT (3 bajty)
INT (4 bajty)
BIGINT (8 bajtów)
• Dodatkowe atrybuty:
– UNSIGNED – liczba bez znaku
– ZEROFILL – dopełnienie zerami
– (M) – wyświetlenie M cyfr
4
Typy danych
• Typy danych mySQL – liczby
zmiennoprzecinkowe:
– FLOAT (n) – pojedyncza precyzja, n liczb
– DOUBLE (M,D) – podwójna precyzja
– DECIMAL (M,D) – liczba zapisywana jako tekst
• Dodatkowe atrybuty:
–
–
–
–
UNSIGNED – liczba bez znaku
ZEROFILL – dopełnienie zerami
(M) – wyświetlenie M cyfr
(M,D) – wyświetlenie M cyfr, D cyfr po przecinku
5
Typy danych
• Typy danych mySQL – data i czas:
– DATETIME – data + czas (2003-10-15
15:00:12)
– DATE – data (2003-10-15)
– TIME – czas (15:00:12)
– YEAR – rok (2003 lub 03)
– TIMESTAMP (n) – znacznik czasu (n – liczba
znaków)
6
SQL – liczby i napisy
• Łańcuchy znaków: 'napis' lub "napis"
•
użycie backslasha (\): 'napis \'03'
• Liczby całkowite: 1221 0 -32
• Liczby zmiennoprzecinkowe: 294.42 32032.6809e+10
• Liczby szesnastkowe: x'4D7953514C' 0x5061756c
• Wartość pusta: NULL
7
Operacje na tabelach
• Tworzenie tabeli (przykład):
• CREATE TABLE tabela (id INT,
nazwa VARCHAR(30));
• Usuwanie tabeli:
• DROP TABLE tabela;
• Wyświetlenie istniejących baz danych:
• SHOW TABLES;
• Wyświetlenie struktury tabeli:
• DESCRIBE tabela;
8
Tworzenie tabeli MySQL
CREATE TABLE czeki (
nr_czeku INT AUTO_INCREMENT,
odbiorca VARCHAR(30),
kwota float,
uwagi char(30),
PRIMARY KEY (nr_czeku));
Usuń tabele ?
9
Operacje na tabelach
• Zmiana nazwy:
• RENAME TABLE tabela TO
nowa_tabela;
• Zmiana struktury tabeli – ALTER TABLE
• Dodanie kolumny:
• ALTER TABLE tabela ADD (opis
TEXT);
• Usuwanie kolumny:
• ALTER TABLE tabela DROP opis; 10
Operacje na tabelach
• Modyfikacja typu kolumny (ograniczenia typu!):
• ALTER TABLE tabela MODIFY opis
VARCHAR(50);
• Zmiana nazwy kolumny:
• ALTER TABLE tabela CHANGE opis
info VARCHAR(50);
• Za pomocą ALTER TABLE możliwe jest również
dodawanie i usuwanie atrybutów pól.
11
Atrybuty pól tabeli
• Przy tworzeniu lub zmianie tabeli można podać opcjonalne
atrybuty pól (kolumn) tabeli:
• CREATE TABLE (pole typ atrybuty, ...);
• Dostępne atrybuty:
– NULL – można nie podawać wartości (domyślnie)
– NOT NULL – wartość musi być podana
– DEFAULT wartość – gdy nie podamy wartości
– AUTO_INCREMENT – automatycznie zwiększany licznik
– COMMENT 'opis' – komentarz
– PRIMARY KEY, KEY – indeksy główne
12
AUTO_INCREMENT i
DEFAULT
• AUTO_INCREMENT – nie wpisujemy
danych, baza wpisuje aktualny stan licznika
i zwiększa go o 1.
• DEFAULT – jeżeli nie wprowadzimy
danych, zostanie wpisana wartość domyślna
13
AUTO_INCREMENT i
DEFAULT
• CREATE TABLE wykonawcy {
id
INT NOT NULL
AUTO_INCREMENT,
wykonawca VARCHAR(30),
opis
TEXT DEFAULT 'brak opisu'
};
14
TIMESTAMP
• Wartością domyślną dla kolumny o typie
TIMESTAMP
jest aktualny znacznik czasu (data i czas).
• Kolumna tego typu umożliwia zachowanie
czasu wprowadzenia lub ostatniej
modyfikacji rekordu.
• Jeżeli zostanie podana wartość – jest ona
wpisywana.
• Jeżeli nie zostanie podana wartość (NULL)
– wpisywany jest znacznik czasu.
15
Wstawianie danych do tabeli
INSERT INTO nazwa_tabeli
(kolumna1, kolumna2,....)
VALUES
(wartość1, wartość2, wartość3);
INSERT INTO CZEKI
(odbiorca, uwagi, kwota )
VALUES ('Ma Bell', 'Lepiej miec syna', 150);
Co z polem nr_czeku ?
16
Wypełnianie tabeli MySQL
Tabela czeki :
• nr_czeku INT AUTO_INCREMENT
PRIMARY KEY,
• odbiorca VARCHAR(30),
• kwota float,
• uwagi char(30),
INSERT INTO CZEKI
(odbiorca, uwagi, kwota )
VALUES
('Ma Bell', 'Lepiej miec syna', 150);
Co z polem
nr_czeku ?
17
Wyszukiwanie danych
• Wyszukiwanie danych w tabeli – instrukcja
SELECT
• Ogólna postać instrukcji SELECT:
• SELECT które_kolumny
• FROM z_której_tabeli
• WHERE które_rekordy;
18
Wyszukiwanie danych
• SELECT [DISTINCT]
• { {funkcja agregująca | wyrażenie [ AS
nazwa_kolumny ]}
| {kwalifikator.*}
| *
• INTO specyfikacja_obiektu_docelowego, ...
• FROM { {nazwa_tabeli [AS] {alias}
[(nazwa_kolumny, ...]}
| podzapytanie
|tabela_złączona
[ WHERE predykat]
19
Zdanie SELECT cd.
• [ GBROUP BY {nazwa tabeli | ALIAS }.nazwa
kolumny]
• [HAVING predykat]
• [ORDER BY {{kolumna_wyjściowa {ASC |
DESC]},..}];
20
Zdanie SELECT cd.
• INTO - określa obiekt docelowy
• FROM - wskazuje tabele z których zapytanie
pobiera lub wylicza wartości
• WHERE - kryteria, których spełnienia wymaga się
od wierszy wyjściowych
• GROUP BY - grupuje wyniki zapytania w oparciu o
wartości w określonych kolumnach
• HAVING określa wymagania dotyczące grup
wierszy określonych klauzulą GROUP BY
• ORDER BY - porządkuje wynik wg określonych
kryteriów
21
Wyszukiwanie danych
• Najprostsza postać instrukcji SELECT
• SELECT * FROM albumy;
• Wyszukiwanie:
– w tabeli albumy
– wszystkich pól (kolumn) – „*”
– wszystkich rekordów (wierszy) – brak warunku
WHERE
22
Wyszukiwanie – wybór kolumn
• Wyszukiwanie danych – wyświetlenie
kolumntytuł, wykonawca
• wybranych
SELECT rok,
FROM albumy;
• SELECT wykonawca FROM albumy;
• W ten sposób można uzyskać powtarzające
• SELECT DISTINCT wykonawca
się wyniki:
FROM albumy;
23
24
# Query:
# select * from czeki
#
'nr_czeku',
’odbiorca’,
'kwota',
'1',
'Ma Bell',
'150',
'2',
'Reading R.R', '245.34',
'3',
'Ma Bell',
'200.32',
'4',
'Local utilities','98',
'5',
'Joes Stale $ Dent','150',
'6',
'Cash',
'25',
'7',
'Joan Gas',
'25.1',
'uwagi',
'Lepiej miec syna',
'pociag do Chicago',
'telefon komórkowy',
'Paliwo',
'Artykuly spozywcze',
'Szalona noc',
'Paliwo',
25
Jak pisać instrukcje
select odbiorca, uwagi, kwota, nr_czeku from czeki;
select odbiorca, uwagi, kwota, nr_czeku
from czeki;
26
Wybieranie kolumn
27
Zapytania z rozróżnieniem
28
Zapytania z rozróżnieniem
29
Czy umiesz?
Czy zdania są równoważne:
SELECT * FROM CZEKI;
select * from czeki;
Zapytania nie działają:
Select *
Select * from czeki
Select kwota nazwisko odbiorca from czeki;
Które zapytanie są dobre:
select *
from czeki;
select * from czeki;
select * from czeki
30
Wyszukiwanie – wybór wierszy
• Wyszukiwanie rekordów spełniających
zadany warunek
– instrukcja WHERE
31
Operatory
• Operatory używane w instrukcji SELECT
... WHERE:
–
–
–
–
porównania: = <> < > <= >= <=>
logiczne: NOT ! AND && OR || XOR
IS NULL, IS NOT NULL
expr BETWEEN min AND max (NOT
BETWEEN)
– expr IN (lista)
(NOT IN)
32
Wyszukiwanie – wybór wierszy
• SELECT tytuł FROM albumy
WHERE wykonawca = 'Pink
Floyd';
• SELECT wykonawca, rok FROM
albumy
WHERE tytuł = 'The Best Of'
AND rok < 1970;
33
34
Operatory
• SELECT * FROM albumy
WHERE wykonawca IN ('Pink
Floyd', ‘Dire Straits')
AND (rok < 1975 OR rok
BETWEEN 1979 AND 1983);
35
Symbole wieloznaczne
• Symbole wieloznaczne używane w
instrukcji WHERE:
–%
–_
zastępuje dowolny ciąg znaków
zastępuje jeden znak
• SELECT * FROM albumy
WHERE wykonawca LIKE 'The
%s';
36
Symbole wieloznaczne
• Symbole wieloznaczne używane w
instrukcji WHERE:
– Operator symboli wieloznacznych: LIKE, NOT
LIKE
• SELECT * FROM albumy
WHERE album NOT LIKE 'The
Best in 197_';
37
Operator podobieństwa LIKE
38
Sortowanie wyników
• Sortowanie wyników wg zadanej kolumny:
– ORDER BY pole – w porządku rosnącym
– ORDER BY pole ASC – jw.
– ORDER BY pole DESC – w porządku
malejącym
• SELECT * FROM albumy
ORDER BY rok DESC, wykonawca;
39
Sortowanie wyników wg pola ‘kwota’
40
Sortowanie wyników z frazą WHERE
41
Pytania, niejasności,
powtórki
42
Syntax.txt:
Należy podać
lokalizację pliku i
wtedy wybrane
wyrazy pisze w
kolorze niebieskim
[1 1]
ADD
AGAINST
ALL
ALTER
ANALYZE
AND
AS
ASC
BETWEEN
BINARY
BINLOG
BOTH
BY
CASCADE
CASE
CHANGE
CHARACTER
COLUMN
CONSTRAINT
CREATE
CROSS
DATABASE
DATABASES
....
43
44
45
46
47
48
49
Struktura zapytania (kwerendy)
SELECT lista atrybutów wyświetlanych w odpowiedzi
*, ALL wskazuje że wszystkie atrybuty mają być wyświetlone
FROM tabela lub tabele do których jest zapytanie
WHERE warunki wyboru;
50
Tabela Persons ( ahv, surname, forename, phone, fax, email,
www)
SELECT surname, email
FROM Persons
WHERE surname='Smith' AND forename='John' ;
ze zmianą nazw atrybutów
SELECT surname AS name, email AS contact
FROM Persons
WHERE surname='Smith' AND forename='John' ;
51
52
53
Eliminacja duplikatów
SQL nie eliminuje duplikatów, chyba że użyjemy instrukcji DISTINCT
Persons ( ahv, surname, forename, phone, fax, email, www)
SELECT DISTINCT forename
FROM Persons
WHERE surname='Smith' ;
54
55
Porównywanie ciągów znakowych
SELECT surname, forename, email
FROM Persons
WHERE email LIKE '%@inf.ethz.ch' ;
SELECT surname, forename, email
FROM Persons
WHERE surname < 'N' ;
56
57
System aliasów
System aliasów można wykorzystać,
•jeśli chcemy porównywać zmienne z tej samej tabeli.
• jest wygodny, gdy chcemy uniknąć dwuznaczności
używając powtarzających się nazw atrybutów.
SELECT p1.surname, p2.surname
FROM Persons p1, Persons p2
WHERE p1.phone = p2.phone
58
Połączenie typu
‘każdy z każdym’
59
Grouping
Specify list of grouping attributes in GROUP BY clause
Aggregate operations in SELECT clause applied within
groups
Persons ( ahv, surname, forename, phone, fax, email, www)
SELECT forename, COUNT(ahv)
FROM Persons
GROUP BY forename ;
60
Funkcje agregujące
COUNT - zwraca liczbę rekordów spełniających warunek
SUM - zwraca sumę wartości w kolumnie
AVG - znajduje wartość średnią w kolumnie
MAX - zwraca wartość maksymalną
MIN - zwraca wartość minimalną
VARIANCE - zwraca kwadrat odchylenia standardowego
STDDEV - podaje odchylenie standardowe
61
Funkcje arytmetyczne
ABS - wartość bezwzględna
CEIL - zaokrągla w górę
FLOOR - zaokrągla w dół
COS, COSH, SIN, SINH, TAN, TANH funkcje trygonometryczne
EXP, LN, LOG - funkcje logarytmiczne
MOD - reszta z dzielenia
SQRT - pierwiastek kwadratowy
POWER - podnoszenie liczby do potęgi
62
Funkcje znakowe
CHR - zwraca znak odpowiadający podanej liczbie w kodzie ASCII
CONCAT - łączy dwa łańcuchy znakowe
UPPER - wyświetla WIELKIMI LITERAMI
LOWER - wyświetla małymi literami
LPAD - dodaje tekst z lewej strony
RPAD - dodaje tekst z prawej strony
LTRIM - usuwa wybrany znak z tekstu z lewej strony
RTRIM - usuwa wybrany znak z tekstu z prawej strony
SUBSTR - wyjmuje ciąg znaków ze łańcucha
63
Frazy w SQL
WHERE
- wybiera rekordy z tabeli
STARTING WITH - działa podobnie jak LIKE(wyrażenie)
ORDER BY - określa kolumnę sortującą wyniki
Group BY
- pozwala wyświetlać i obliczać informacje
dotyczące kilku rekordów
HAVING
- wybiera dane zwracane przez „GROUP BY’
64
65
66
67
Więcej niż jedna funkcja agregująca...
Ile pieniędzy dostali odbiorcy i ile czeków wystawiono
68
Ile pieniędzy dostali odbiorcy
na takie same okazje
69
Ile czeków wystawiono na tego samego odbiorcę:
70
Fraza WHERE wybiera REKORDY spełniające określone
kryterium
Fraza HAVING umożliwia wybieranie ROZWIĄZAŃ
spełniających określone kryterium
Fraza WHERE nie działa z funkcjami agregującymi !!!
71
Persons ( ahv, surname, forename, phone, fax, email, www)
SELECT forename, COUNT(ahv)
FROM Persons
GROUP BY forename
HAVING surname < 'N' ;
);
72
Pytania, niejasności,
powtórki
73
Joins
Persons ( ahv, surname, forename, phone, fax, email, ww
Organisations ( orgname, phone, fax, email, www)
Worksfor ( ahv, orgname)
SELECT orgname, surname, forename
FROM Persons, Worksfor, Organisations
WHERE Persons.ahv=Worksfor.ahv
AND Worksfor.orgname=Organisations.orgname
74
JOIN operations
SQL standard also supports various JOIN operators
r1 CROSS JOIN r2 cross product
r1 JOIN r2 ON p theta-join with condition p
r1 NATURAL JOIN r2 natural join
75
JOIN operations ...
Various forms of OUTERJOIN operations also supported
r1 NATURAL FULL OUTER JOIN r2
r1 NATURAL LEFT OUTER JOIN r2
r1 NATURAL RIGHT OUTER JOIN r2
r1 FULL OUTER JOIN r2 ON p
r1 LEFT OUTER JOIN r2 ON p
r1 RIGHT OUTER JOIN r2 ON p
76
77
Grupowanie wyników
• Tworzenie zestawień przez grupowanie
wyników:
– użycie funkcji, np. COUNT, SUM, MAX,
MIN, AVG
– nazwanie kolumny z wynikami (opcjonalnie) –
AS
• –SELECT
wykonawca,
COUNT(*)
zgrupowanie
wyników – ORDER
BY
FROM albumy
• Przykład – obliczenie ilości albumów
GROUP BY wykonawca;
wykonawców: COUNT(*) AS
• wszystkich
SELECT wykonawca,
78
ilosc FROM albumy
Grupowanie wyników
• Ograniczenie rekordów uzyskanych w
wyniku grupowania
– operator HAVING
• Nie należy mylić instrukcji WHERE i
HAVING!
• •Przykład
ilości albumów
SELECT– obliczenie
wykonawca,
COUNT(*) AS
wszystkich
wykonawców,
ilosc FROM
albumywyświetlenie
tylko
tych,BY
którzy
mają więcej niż 5
GROUP
wykonawca
albumów:
HAVING ilosc > 5;
79
Ograniczenie liczby wyników
• Ograniczenie liczby zwracanych wyników –
LIMIT
• LIMIT n – n pierwszych wyników
• LIMIT m,n – n wyników, pomijając m
• pierwszych
SELECT wykonawca, COUNT(*) AS
ilosc FROM albumy
GROUP BY wykonawca
LIMIT
•• Przykład:
10
wykonawców COUNT(*)
o największejAS
SELECT
wykonawca,
10;
liczbie
iloscalbumów:
FROM albumy
80
GROUP BY wykonawca
LIMIT
Wyszukiwanie w wielu tabelach
• Pobieranie danych w więcej niż jednej
tabeli
•ID •Wykona •Alb •Ro •Gatune
•ID
•Utwó •Cza
•IDk
A
wca
um
k
• Przykład bazy danych – dwie tabele:
U r
s
A
– albumy
– utwory
• SELECT * FROM albumy, utwory;
• Wybranie wszystkich możliwych
kombinacji rekordów z obu tabel (iloczyn
81
Wyszukiwanie w wielu tabelach
• Uwzględnienie relacji między tabelami:
• SELECT * FROM albumy, utwory
WHERE albumy.IDA =
utwory.IDA;
• Łączy ze•IDsobą
rekordy•Alb
obu tabel
•Wykona
•Ro mające
•Gatune
takie same
są
A dane
wca w polach,
um które
k
k
•IDrelacją:
•Utwó •Cza •ID
połączone
U
– albumy
r
s
A
82
Wyszukiwanie w wielu tabelach
• Wybór kolumn:
• SELECT albumy.wykonawca,
albumy.album,
utwory.utwor, utwory.czas
FROM albumy, utwory
WHERE albumy.IDA =
• SELECT a.wykonawca, a.album,
utwory.IDA;
u.utwor, u.czas
• Krótsza wersja – aliasy nazw tabel:
FROM albumy a, utwory u
WHERE a.IDA = u.IDA;
83
Wstawianie danych – inne
metody
• Wstawianie do tabeli danych uzyskanych w
wyniku zapytania:
• INSERT INTO nowa (autor,
dzielo)
SELECT DISTINCT wykonawca,
album
FROM albumy;
LOAD DATA
LOCAL
INFILE
•• Wstawianie
danych
z pliku
na komputerze
‘dane.txt’
klienta
(pola rozdzielone tabulatorami,
84
INTO nowa_tabela;
rekordy
– znakiem nowej linii)