1 MySQL bazy danych dla witrynyTechnologie internetowe Wykład 7 MySQL bazy danych dla witryny

2 Serwer MySQL – język zapytańFirma Oracle wersje – od 3 przez 5.1 aż do 7.1 (MySQLCluster) też wersje dla Windows

3 Konsola MySQL (lokalnie lub zdalnie)Metody zarządzania bazą: proces mysqld.exe lub mysql_nt.exe(menedżer zadań Windows). PhpMyAdmin (przeglądarka) Konsola MySQL (lokalnie lub zdalnie) Uruchomienie wiersza polecenia (START - Uruchom… cmd – lub Programy –Akcesoria - Wiersz polecenia). Przechodzimy do foldera c:\usr\mysql\bin Z poziomu plików PHP Pliki baz danych tworzone są zwykle w folderze mysql/data

4 Wyświetlenie listy baz:c:\usr\mysql\bin>mysqlshow mysql i inne Baza mysql jest bazą administracyjną – dostęp do niej ma tylko ROOT (bądź inny użytkownik o przydzielonych przez administratora prawach dostępu). Jej podstawowa rola to umożliwienie administratorowania prawami dostępu do baz, tabel

5 Uruchomienie konsoli MySQLc:\usr\mysql\bin>mysql –u root –p Password: Zgłasza się konsola MySQL – gotowość do wpisywania poleceń: mysql> polecenie help

6 Baza danych składa się z tabel (zapis encji)Tabele to zestaw rekordów danych elementarnych (pola rekordu) Model relacyjny Normalizacja tabel i związki między nimi (złączenia) - ERD pole id nazwa adres 1 ABC Rzeszów... 2 ... rekord rekord

7 OPERACJE SQL NA BAZIE (kwerendy)tworzenie bazy i tabeli selekcja rekordów wstawianie rekordów modyfikacja treści rekordów modyfikacja struktury usuwanie rekordów, tabeli, bazy Poznanie języka SQL będzie nam potrzebne do wykonania opreacji na bazie w skryptach PHP

8 SHOW DATABASES; USE nazwa_bazy TWORZENIE BAZY DANYCH:CREATE DATABASE nazwa_bazy; Np.. CREATE DATABASE firma; Lista baz: SHOW DATABASES; Otwarcie bazy (w konsoli): USE nazwa_bazy

9 CREATE TABLE nazwa_tabeli ( TWORZENIE TABELI CREATE TABLE nazwa_tabeli ( nazwapola1 typ parametry, nazwapola2 typ parametry, itd); parametry: NOT NULL - niepuste PRIMARY KEY – klucz główny AUTO_INCREMENT - autonumerowanie DEFAULT wartosc_domyślna Np. CREATE TABLE dostawcy (Id INT(10) AUTO_INCREMENT PRIMARY KEY, Nazwa VARCHAR(10), Adres VARCHAR(30));

10 TYPY DANYCH Standard ISO SQL (1992) przewiduje około piętnastu typów danych, podzielonych na grupy:

11 Typy tekstowe - alfanumeryczne (String)CHAR(N) definiuje pole napisowe o stałej długości (ew. uzupełniane spacjami), VARCHAR(N) jest polem o zmiennej długości nie przekraczającej N TEXT Także BLOB (binarne), SET (zbiór).

12 Typy liczbowe (Numeric)INT, BIGINT, FLOAT, DECIMAL Na ogół dostępnych jest wiele różnych typów liczbowych, różniących się możliwym zakresem wartości (INT, BIGINT, SMALLINT, ...) i precyzją (FLOAT, DOUBLE PRECISION, ...). Typ DECIMAL(M,D) to liczba (ułamek) dziesiętny o ustalonej liczbie cyfr dziesiętnych w części całkowitej i ułamkowej.

13 Typy daty i godziny (Datetime):TIMESTAMP

14 USUWANIE REKORDÓW DELETE FROM nazwa_tabeli WHERE warunki; np.:DELETE FROM dostawcy; usuwa wszystkie rekordy !!! DELETE FROM dostawcy WHERE nazwa LIKE 'ABC'; usuwa wszystkie rekordy w których polu Nazwa jest wartość 'ABC'

15 ZMIANA NAZWY TABELI ALTER TABLE old_name RENAME new_name;

16 ZMIANA NAZWY I TYPU POLAALTER TABLE tabela CHANGE nazwa_stara nazwa_nowa typ; Np.: ALTER TABLE dostawcy CHANGE id id_nowe INT(5);

17 ALTER TABLE nazwa_tabeli DROP nazwa_pola;USUWANIE PÓL (KOLUMN TABELI) ALTER TABLE nazwa_tabeli DROP nazwa_pola; np.: ALTER TABLE dostawcy DROP idnowe;

18 DODANIE POLA W TABELI np.:ALTER TABLE nazwa_tabeli ADD id int(5) default 10; np.: ALTER TABLE dostawcy ADD id int(5) default 10;

19 ZMIANA TREŚCI PÓL REKORDÓW W TABELIUPDATE tabela SET nazwa_pola1=nowa_wartość WHERE warunek; Przykładowo: UPDATE dostawcy SET nazwa="INTERRES" where id=1; jeśli id jest primary key to zmieni tylko w 1 rekordzie UPDATE dostawcy SET cena=cena*0.9; we wszystkich rekordach

20 USUWANIE TABELI DROP TABLE nazwa_tabeli;

21 USUWANIE BAZY DROP DATABASE nazwa_bazy;

22 POBIERANIE WIELU POLECEŃ SQL Z PLIKU:SOURCE nazwa_pliku; Uwaga: plik powinien być w folderze MYSQL\BIN, w przeciwnym przypadku trzeba podać całą ścieżkę dostępu w apostrofach

23 PRZYKŁAD: C:\usr\mysql\bin> mysql -u root -p Enter password: *USE mysql (tylko konsola) SHOW DATABASES CREATE DATABASE ksiegarnia;

24 SHOW DATABASES; Database abc firma mysql test baza administracyjna

25 CREATE DATABASE ksiegarnia;USE ksiegarnia; CREATE TABLE ksiazki (id_ks int unsigned not null auto_increment primary key, autor char(30) not null, tytul char(40) not null); EXPLAIN ksiazki; INSERT INTO ksiazki VALUES (Null, "Mickiewicz Adam", "Pan Tadeusz");

26 SELECT * FROM ksiazki; INSERT INTO ksiazki VALUES (Null, "Mickiewicz Adam", "Konrad Wallenrod"); INSERT INTO ksiazki VALUES -Null, "Mickiewicz Adam", „Ballady i romanse"); itd...

27 SELECT * FROM ksiazki; DELETE FROM ksiazki WHERE tytul LIKE "pan Wołodyjowski"; mysql> SELECT * FROM ksiazki;

28 SQL selekcja rekordów klauzule operatory, metaznaki złączenie tabel

29 SELECT [tabela.]{* | [lista_pól]} FROM lista_nazw_tabelWYŚWIETLENIE (WYBÓR - SELEKCJA) REKORDÓW TABELI z wykorzystaniem klauzul SELECT [tabela.]{* | [lista_pól]} FROM lista_nazw_tabel [WHERE warunek] [ORDER BY .... ] /sortowanie [GROUP BY ...]; /agregacja – sumowanie lub zliczanie z grupowaniem wg wybranego pola [ opcja] { do wyboru albo | albo}

30 Można też używać kwerendę SELECT do obliczeń: mysql> SELECT 4*5; Można też wykonywać operacje na polach: mysql> SELECT nazwa, cena*5 from towary; mysql> SELECT 4*5; mysql> SELECT kat, count(id_t) from towary group by kat; Porada: wypróbowywać poprawność kwerend w MyPHPAdmin

31 % - zastępuje dowolny ciąg znaków Metaznaki % - zastępuje dowolny ciąg znaków _ (podkreślnik) – zastępuje 1 dowolny znak Przykłady: select * from dostawcy where nazwa like "a%"; nazwa rozpoczyna sie od "a" select * from dostawcy where nazwa like "%al%"; nazwa zawiera frazę "al" select * from dostawcy where nazwa like "_a%"; litera "a" na drugim miejscu nazwy

32 WSTAWIENIE REKORDU Z DANYMI DO WYBRANYCH PÓLINSERT INTO tabela VALUES (NULL, wartość1, wartość2...); INSERT INTO tabela SET p_key=NULL, pole1=wartość1, pole2=wartość2...; Pozostałe pola pozostaną puste, jeśli nie mają cechy WYMAGANIE – not null

33 Operatory logiczne w klauzuli WHERENOT: logiczna negacja. MySQL dopuszcza jako alternatywną notację wykrzyknik (!). OR: logiczna alternatywa. MySQL dopuszcza alternatywną notację || AND: logiczna koniunkcja. Tu również MySQL dopuszcza notację &&.

34 Wyrażenia logiczne Każda wartość różna od zera i NULL odpowiada w wyrażeniu logicznym prawdzie; NULL i zero reprezentują wartość fałsz Złożone wyrażenia logiczne, zbudowane za pomocą operatorów logicznych, zwracają: 1 jako reprezentację wartości prawdziwej, 0 jako reprezentację fałszu.

35 W teorii relacyjnych baz danych wartość NULL Specjalna wartość reprezentująca wartość nieokreśloną, brakującą lub nieznaną. Jest różna od wszelkich konkretnych wartości, w szczególności — od ciągu pustego (" ") i zera (0). Dowolne wyrażenie, w którym wystąpi ta wartość, również przybierze wartość Null Nie możemy sprawdzać, jaka jest wartości Null — jedynie czy jakaś wartość jest nieokreślona. za pomocą funkcji ISNULL(x)

36 Parametr pola - NOT NULL | NULL: określa, czy NULL jest dopuszczalną wartością w tej kolumnie.Domyślnie wartość NULL jest dopuszczalna, za wyjątkiem kluczy (kolumn indeksowanych) ISNULL(A) zwraca jedynkę, jeżeli wartością A jest NULL, w przeciwnym wypadku zwraca zero.

37 W wypadku porównań wartości które nie są obie tekstowe ani całkowitoliczbowe, MySQL dokonuje automatycznych konwersji typów, co na ogół prowadzi do porównania wyrażeń jako wartości zmiennoprzecinkowych. Jeżeli wartością któregokolwiek z argumentów porównania jest NULL, porównanie zwraca jako wynik NULL (za wyjątkiem funkcji ISNULL.) select id , nazwa, isnull(nazwa) from dostawcy ; | id | nazwa | isnull(nazwa) | 1 | ABC | 0 | 2 | Exbud | 0 | 3 | Bako | 0 | 4 | NULL | 1 | 5 | | 0 select id , nazwa, isnull(nazwa) from dostawcy where !nazwa; | id | nazwa |isnull(nazwa) | | 1 | ABC | | | 2 | Exbud | | | 3 | Bako | | | 5 | | |

38 Porównania w warunkach kwerendOperatory porównania można stosować do wyrażeń liczbowych i tekstowych Wynik porównania wyrażeń tekstowych : wartości zadeklarowane jako BINARY - znosi utożsamienie małych i wielkich liter. jeśli VARCHAR(dlugosc) to utożsamianie małych i wielkich liter

39 Operatory porównania Równość: = Nierówność: <> lub !=Mniejszy lub równy <= Większy lub równy >= Większy niż > Mniejszy niż < Porównanie do wzorca LIKE A BETWEEN B AND C: wyrażenie takie jest równoważne A>=B AND A<=C o ile porównywane wyrażenia są tego samego typu.

40 Przykłady klauzuli WHEREWHERE nazwa_pola1=wartość WHERE n_p1=war1 AND n_p2=war2 WHERE n_p1=war1 OR n_p2=war2 WHERE n_p between war1 AND war2 WHERE cena<100 WHERE n_p LIKE war1 (dla tekstów) WHERE n_p LIKE '%fraza%' WHERE n_p1=wartosc1 OR n_p2=wartosc2

41 Przykłady SELECT id, nazwa FROM dostawcy; SELECT * FROM magazyn;SELECT id_t, nazwa from magazyn WHERE cena_n>50; SELECT id_t, nazwa from magazyn WHERE cena_n between 50 and 80; SELECT id_t, nazwa from magazyn WHERE cena_n>50 and cena_n<80 ; SELECT id_t, nazwa from magazyn WHERE cena_n>50 and cena_n<80 ORDER BY cena_n ; SELECT id_t, nazwa from magazyn WHERE cena_n>50 and cena_n<80 ORDER BY cena_n desc;//desc oznacza kolejność malejącą lub użycie operatora between

42 WHERE Szczególne znaczenie ma klauzula WHERE w postaci:tab1.pole_klucz_glowny=tab2.pole_key_kl_obcy Posiada ważną rolę przy konieczności wyboru danych z dwóch tabel o typie złączenia 1:N

43 Wybór danych z kilku tabelSELECT tabela1.P1, tabela2.K1,... FROM tabela1,tabela2 WHERE tabela1.key_t1=tabela2.f_key_t2 Key_t1 P1 P2 P3 P4 Key_t2 K1 K2 K3 f_key_t2 tabela1 tabela2

44 SELECT typ.nazwa_t, marka. nazwa_p FROM marka, typ id_m producent nazwa_p 1 Niemcy AUDI 2 Polska Fiat 3 Francja Citroen 4 USA Ford id_t marka_id nazwa_t 1 A6 2 A8 3 100 4 126p 5 Cinquecento 6 Panda 7 Berlingo 8 Xara 9 C3 1:N SELECT typ.nazwa_t, marka. nazwa_p FROM marka, typ WHERE marka.id_m=typ.marka_id Uwaga – wymaganie NOT NULL dla pola marka_id

45 dostawcy magazyn Id_dostawcy Nazwa 1 ABC 2 Exbud 3 Bako Id_towaru Nazwa Id_dostawcy 1 Narty 2 Sanki klucz obcy SELECT id, nazwa FROM dostawcy WHERE id<3; SELECT id, nazwa FROM dostawcy WHERE nazwa LIKE 'E%'; SELECT id, nazwa FROM dostawcy ORDER BY nazwa; Proste złączenie przez warunek: SELECT dostawcy.nazwa, magazyn.nazwa FROM dostawcy, magazyn WHERE dostawcy.id_dostawcy= magazyn.id_dostawcy; Inny sposób: SELECT dostawcy.nazwa, magazyn.nazwa_towaru FROM dostawcy INNER JOIN magazyn ON (magazyn.id_dostawcy=dostawcy.id_dostawcy) where id_dostawcy=1;

46 ZŁĄCZENIA JOIN towary kategorie id nazwa id_k 1 masło 1 id_k nazwa2 kiełbasa 1 3 szynka 1 4 rower 2 5 komputer id_k nazwa 1 spożywcze 2 przemysłowe 3 zabawki kategorie klucz obcy klucz główny

47 SELECT towary.nazwa, kategorie.nazwa_k nazwa nazwa_k masło spożywcze kiełbasa spożywcze szynka spożywcze rower przemysłowe NULL zabawki SELECT towary.nazwa, kategorie.nazwa_k FROM towary RIGHT JOIN kategorie ON kategorie.id_k = towary.id_k; nazwa nazwa _k masło spożywcze kiełbasa spożywcze szynka spożywcze rower przemysłowe komputer NULL SELECT towary.nazwa, kategorie.nazwa_k FROM towary LEFT JOIN kategorie ON kategorie.id_k = towary.id_k; SELECT towary.nazwa, kategorie.nazwa_k FROM towary INNER JOIN kategorie ON kategorie.id_k = towary.id_k; nazwa nazwa _k masło spożywcze kiełbasa spożywcze szynka spożywcze rower przemysłowe RIGHT i LEFT to tzw. złączenia zewnętrzne można pisać LEFT OUTER i RIGHT OUTER

48 Jeśli korzystamy z 3 złączonych tabel:sprzedaz SPRZEDAZ_SZCZEGOLY magazyn SELECT SPRZEDAZ.Klient , SPRZEDAZ.data_sp FROM MAGAZYN INNER JOIN SPRZEDAZ_SZCZEGOLY ON (MAGAZYN.ID_towaru=SPRZEDAZ_SZCZEGOLY.Id_produktu) INNER JOIN SPRZEDAZ ON (SPRZEDAZ.ID_sprzedazy= SPRZEDAZ_SZCZEGOLY.Id_sprzedazy) WHERE MAGAZYN.ID_towaru<5;

49 unikalność aliasów sprzedaz magazyn kategorie SPRZEDAZ_SZCZEGOLY----< sprzedaz SPRZEDAZ_SZCZEGOLY magazyn >----- kategorie unikalność aliasów SELECT SPRZEDAZ.Klient , SPRZEDAZ.data_sp, KATEGORIE.opis FROM MAGAZYN as mag INNER JOIN SPRZEDAZ_SZCZEGOLY ON (mag.ID_towaru=SPRZEDAZ_SZCZEGOLY.Id_produktu) INNER JOIN SPRZEDAZ ON (SPRZEDAZ.ID_sprzedazy=SPRZEDAZ_SZCZEGOLY.Id_sprzedazy), KATEGORIE INNER JOIN MAGAZYN as mag ON KATEGORIE.Id_kat = mag1.Kod_towaru WHERE mag.ID_towaru<5;