Tworzenie bazy danych – mySQL
description
Transcript of Tworzenie bazy danych – mySQL
1
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.
2
Operacje na bazach danych
• Tworzenie bazy danych:
• Usuwanie całej bazy:
• Wyświetlenie istniejących baz danych:
• Przełączenie się na inną bazę danych:
• CREATE DATABASE baza;
• DROP DATABASE baza;
• SHOW DATABASES;
• USE baza;
3
Tworzenie bazy danych – mySQL
• CREATE DATABASE SQL_w_3_tygodnie;
• Use SQL_w_3_tygodnie;
• DROP DATABASE ............;
4
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
5
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
6
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)
7
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
8
Operacje na tabelach• Tworzenie tabeli (przykład):
• Usuwanie tabeli:
• Wyświetlenie istniejących baz danych:
• Wyświetlenie struktury tabeli:
• CREATE TABLE tabela (id INT, nazwa VARCHAR(30));
• DROP TABLE tabela;
• SHOW TABLES;
• DESCRIBE tabela;
9
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 ?
10
Operacje na tabelach• Zmiana nazwy:
• Zmiana struktury tabeli – ALTER TABLE
• Dodanie kolumny:
• Usuwanie kolumny:
• RENAME TABLE tabela TO nowa_tabela;
• ALTER TABLE tabela ADD (opis TEXT);
• ALTER TABLE tabela DROP opis;
11
Operacje na tabelach
• Modyfikacja typu kolumny (ograniczenia typu!):
• Zmiana nazwy kolumny:
• Za pomocą ALTER TABLE możliwe jest również dodawanie i usuwanie atrybutów pól.
• ALTER TABLE tabela MODIFY opis VARCHAR(50);
• ALTER TABLE tabela CHANGE opis info VARCHAR(50);
12
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
13
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
• Wynik: (1, 'XYZ', 'brak opisu')
14
AUTO_INCREMENT i DEFAULT
• CREATE TABLE wykonawcy {id INT NOT NULL
AUTO_INCREMENT,wykonawca VARCHAR(30),opis TEXT DEFAULT 'brak
opisu'};
15
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.
16
INSERT INTO CZEKI (odbiorca, uwagi, kwota ) VALUES ('Ma Bell', 'Lepiej miec syna', 150);
Wstawianie danych do tabeli
INSERT INTO nazwa_tabeli(kolumna1, kolumna2,....)
VALUES(wartość1, wartość2, wartość3);
Co z polem nr_czeku ?
17
Wypełnianie tabeli MySQLTabela 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 ?
18
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;
19
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]
20
Zdanie SELECT cd.• [ GBROUP BY {nazwa tabeli | ALIAS }.nazwa
kolumny]• [HAVING predykat]• [ORDER BY {{kolumna_wyjściowa {ASC |
DESC]},..}];
21
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
22
Wyszukiwanie danych
• Najprostsza postać instrukcji SELECT
• Wyszukiwanie:– w tabeli albumy– wszystkich pól (kolumn) – „*”– wszystkich rekordów (wierszy) – brak warunku
WHERE
• SELECT * FROM albumy;
23
Wyszukiwanie – wybór kolumn
• Wyszukiwanie danych – wyświetlenie wybranych kolumn
• W ten sposób można uzyskać powtarzające się wyniki:
• Eliminacja powtórzeń wyników:
• SELECT rok, tytuł, wykonawca FROM albumy;
• SELECT wykonawca FROM albumy;
• SELECT DISTINCT wykonawca FROM albumy;
24
25
# Query:# select * from czeki#'nr_czeku', ’odbiorca’, 'kwota', 'uwagi','1', 'Ma Bell', '150', 'Lepiej miec syna','2', 'Reading R.R', '245.34', 'pociag do Chicago','3', 'Ma Bell', '200.32', 'telefon komórkowy','4', 'Local utilities','98', 'Paliwo','5', 'Joes Stale $ Dent','150', 'Artykuly spozywcze','6', 'Cash', '25', 'Szalona noc','7', 'Joan Gas', '25.1', 'Paliwo',
26
select odbiorca, uwagi, kwota, nr_czeku from czeki;
select odbiorca, uwagi, kwota, nr_czeku from czeki;
Jak pisać instrukcje
27
Wybieranie kolumn
28
Zapytania z rozróżnieniem
29
Zapytania z rozróżnieniem
30
Czy umiesz?Czy zdania są równoważne:SELECT * FROM CZEKI;select * from czeki;
Zapytania nie działają:Select *Select * from czekiSelect kwota nazwisko odbiorca from czeki;
Które zapytanie są dobre:select *from czeki;select * from czeki;select * from czeki
31
Wyszukiwanie – wybór wierszy• Wyszukiwanie rekordów spełniających
zadany warunek – instrukcja WHERE
32
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)
33
Wyszukiwanie – wybór wierszy
• SELECT tytuł FROM albumyWHERE wykonawca = 'Pink Floyd';
• SELECT wykonawca, rok FROM albumyWHERE tytuł = 'The Best Of' AND rok < 1970;
34
35
Operatory• SELECT * FROM albumyWHERE wykonawca IN ('Pink Floyd', ‘Dire Straits')AND (rok < 1975 OR rok BETWEEN 1979 AND 1983);
36
Symbole wieloznaczne
• Symbole wieloznaczne używane w instrukcji WHERE:– % zastępuje dowolny ciąg znaków– _ zastępuje jeden znak
• SELECT * FROM albumyWHERE wykonawca LIKE 'The %s';
37
Symbole wieloznaczne
• Symbole wieloznaczne używane w instrukcji WHERE:– Operator symboli wieloznacznych: LIKE, NOT
LIKE
• SELECT * FROM albumyWHERE album NOT LIKE 'The Best in 197_';
38
Operator podobieństwa LIKE
39
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 albumyORDER BY rok DESC, wykonawca;
40
Sortowanie wyników wg pola ‘kwota’
41
Sortowanie wyników z frazą WHERE
42
Pytania, niejasności, Pytania, niejasności, powtórkipowtórki
43
[1 1]ADDAGAINSTALLALTERANALYZEANDASASCBETWEENBINARYBINLOGBOTHBYCASCADECASECHANGECHARACTERCOLUMNCONSTRAINTCREATECROSSDATABASEDATABASES....
Syntax.txt:
Należy podać lokalizację pliku i wtedy wybrane wyrazy pisze w kolorze niebieskim
44
45
46
47
48
49
50
Struktura zapytania (kwerendy)SELECT lista atrybutów wyświetlanych w odpowiedzi*, ALL wskazuje że wszystkie atrybuty mają być wyświetloneFROM tabela lub tabele do których jest zapytanieWHERE warunki wyboru;
51
Tabela Persons ( ahv, surname, forename, phone, fax, email, www)
SELECT surname, emailFROM PersonsWHERE surname='Smith' AND forename='John' ;
ze zmianą nazw atrybutówSELECT surname AS name, email AS contactFROM PersonsWHERE surname='Smith' AND forename='John' ;
52
53
54
Eliminacja duplikatów SQL nie eliminuje duplikatów, chyba że użyjemy instrukcji DISTINCTPersons ( ahv, surname, forename, phone, fax, email, www)
SELECT DISTINCT forenameFROM PersonsWHERE surname='Smith' ;
55
56
Porównywanie ciągów znakowych
SELECT surname, forename, emailFROM PersonsWHERE email LIKE '%@inf.ethz.ch' ;
SELECT surname, forename, emailFROM PersonsWHERE surname < 'N' ;
57
58
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.surnameFROM Persons p1, Persons p2WHERE p1.phone = p2.phone
59
Połączenie typu ‘każdy z każdym’
60
Grouping Specify list of grouping attributes in GROUP BY clause Aggregate operations in SELECT clause applied withingroupsPersons ( ahv, surname, forename, phone, fax, email, www)SELECT forename, COUNT(ahv)FROM PersonsGROUP BY forename ;
61
Funkcje agregujące
COUNT - zwraca liczbę rekordów spełniających warunekSUM - zwraca sumę wartości w kolumnieAVG - znajduje wartość średnią w kolumnieMAX - zwraca wartość maksymalnąMIN - zwraca wartość minimalnąVARIANCE - zwraca kwadrat odchylenia standardowegoSTDDEV - podaje odchylenie standardowe
62
Funkcje arytmetyczne
ABS - wartość bezwzględnaCEIL - zaokrągla w góręFLOOR - zaokrągla w dółCOS, COSH, SIN, SINH, TAN, TANH -
funkcje trygonometryczneEXP, LN, LOG - funkcje logarytmiczneMOD - reszta z dzieleniaSQRT - pierwiastek kwadratowyPOWER - podnoszenie liczby do potęgi
63
Funkcje znakowe
CHR - zwraca znak odpowiadający podanej liczbie w kodzie ASCIICONCAT - łączy dwa łańcuchy znakoweUPPER - wyświetla WIELKIMI LITERAMILOWER - wyświetla małymi literami
LPAD - dodaje tekst z lewej stronyRPAD - dodaje tekst z prawej stronyLTRIM - usuwa wybrany znak z tekstu z lewej stronyRTRIM - usuwa wybrany znak z tekstu z prawej stronySUBSTR - wyjmuje ciąg znaków ze łańcucha
64
Frazy w SQL
WHERE - wybiera rekordy z tabeliSTARTING WITH - działa podobnie jak LIKE(wyrażenie) ORDER BY - określa kolumnę sortującą wynikiGroup BY - pozwala wyświetlać i obliczać informacje dotyczące kilku rekordówHAVING - wybiera dane zwracane przez „GROUP BY’
65
66
67
68
Więcej niż jedna funkcja agregująca...
Ile pieniędzy dostali odbiorcy i ile czeków wystawiono
69
Ile pieniędzy dostali odbiorcy na takie same okazje
70
Ile czeków wystawiono na tego samego odbiorcę:
71
Fraza WHERE wybiera REKORDY spełniające określone kryterium
Fraza HAVING umożliwia wybieranie ROZWIĄZAŃ spełniających określone kryteriumFraza WHERE nie działa z funkcjami agregującymi !!!
72
Persons ( ahv, surname, forename, phone, fax, email, www)SELECT forename, COUNT(ahv)FROM PersonsGROUP BY forenameHAVING surname < 'N' ;) ;
73
Pytania, niejasności, Pytania, niejasności, powtórkipowtórki
74
JoinsPersons ( ahv, surname, forename, phone, fax, email, www)Organisations ( orgname, phone, fax, email, www)Worksfor ( ahv, orgname)SELECT orgname, surname, forenameFROM Persons, Worksfor, OrganisationsWHERE Persons.ahv=Worksfor.ahvAND Worksfor.orgname=Organisations.orgname
75
JOIN operations SQL standard also supports various JOIN operatorsr1 CROSS JOIN r2 cross productr1 JOIN r2 ON p theta-join with condition pr1 NATURAL JOIN r2 natural join
76
JOIN operations ... Various forms of OUTERJOIN operations also supportedr1 NATURAL FULL OUTER JOIN r2r1 NATURAL LEFT OUTER JOIN r2r1 NATURAL RIGHT OUTER JOIN r2r1 FULL OUTER JOIN r2 ON pr1 LEFT OUTER JOIN r2 ON pr1 RIGHT OUTER JOIN r2 ON p
77
78
Grupowanie wyników• Tworzenie zestawień przez grupowanie
wyników:– użycie funkcji, np. COUNT, SUM, MAX, MIN,
AVG
– nazwanie kolumny z wynikami (opcjonalnie) – AS
– zgrupowanie wyników – ORDER BY
• Przykład – obliczenie ilości albumów wszystkich wykonawców:
• SELECT wykonawca, COUNT(*) FROM albumyGROUP BY wykonawca;
• SELECT wykonawca, COUNT(*) AS ilosc FROM albumyGROUP BY wykonawca ORDER BY ilosc DESC;
79
Grupowanie wyników• Ograniczenie rekordów uzyskanych w
wyniku grupowania– operator HAVING
• Nie należy mylić instrukcji WHERE i HAVING!
• Przykład – obliczenie ilości albumów wszystkich wykonawców, wyświetlenie tylko tych, którzy mają więcej niż 5 albumów:
• SELECT wykonawca, COUNT(*) AS ilosc FROM albumyGROUP BY wykonawcaHAVING ilosc > 5;
80
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
• Przykład: 10 wykonawców o największej liczbie albumów:
• 20 następnych wyników (11-30):
• SELECT wykonawca, COUNT(*) AS ilosc FROM albumyGROUP BY wykonawca LIMIT 10;
• SELECT wykonawca, COUNT(*) AS ilosc FROM albumyGROUP BY wykonawca LIMIT 10,20;
81
Wyszukiwanie w wielu tabelach
• Pobieranie danych w więcej niż jednej tabeli
• Przykład bazy danych – dwie tabele:– albumy– utwory
• Wybranie wszystkich możliwych kombinacji rekordów z obu tabel (iloczyn kartezjański):
•Gatunek•Rok•Album•Wykonawca•IDA
•IDA•Czas•Utwór•IDU
• SELECT * FROM albumy, utwory;
82
Wyszukiwanie w wielu tabelach
• Uwzględnienie relacji między tabelami:
• Łączy ze sobą rekordy obu tabel mające takie same dane w polach, które są połączone relacją:
– albumy
– utwory
• SELECT * FROM albumy, utworyWHERE albumy.IDA = utwory.IDA;
•Gatunek•Rok•Album•Wykonawca•IDA
•IDA•Czas•Utwór•IDU
83
Wyszukiwanie w wielu tabelach
• Wybór kolumn:
• Krótsza wersja – aliasy nazw tabel:
• SELECT albumy.wykonawca, albumy.album, utwory.utwor, utwory.czas FROM albumy, utworyWHERE albumy.IDA = utwory.IDA;
• SELECT a.wykonawca, a.album, u.utwor, u.czas FROM albumy a, utwory uWHERE a.IDA = u.IDA;
84
Wstawianie danych – inne metody
• Wstawianie do tabeli danych uzyskanych w wyniku zapytania:
• Wstawianie danych z pliku na komputerze klienta (pola rozdzielone tabulatorami, rekordy – znakiem nowej linii)– komenda mySQL (nie standard):
• INSERT INTO nowa (autor, dzielo)SELECT DISTINCT wykonawca, albumFROM albumy;
• LOAD DATA LOCAL INFILE ‘dane.txt’INTO nowa_tabela;