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;