Technologie internetowe II

1. Technologie internetowe II Wykład 4 – Typy danych i podstawowe polecenia MySQL. Interakcje PHP z MySQL, łączenie z bazą danych, operowanie na danych. Dr Krzysztof Heller

2. Literatura • Meloni J.C.: PHP, MySQL i Apache dla każdego. Wydanie III, Helion, 2007 • Lis M.: PHP i MySQL dla każdego, Helion, 2005 • Leszek 'leon' Krupiński: Wykłady internetowe, 2007 • http://docs.wsei.pl/dydaktyka/Techn_Internet_II_wyklad4.ppt Technologie internetowe II

3. Plan wykładu 4 • Typy danych i podstawowe polecenia MySQL. • Tworzenie, wybieranie i usuwanie rekordów. • Interakcje PHP z MySQL. • Łączenie z bazą danych. • Operowanie na danych. Technologie internetowe II

4. Typy danych i podstawowe polecenia MySQL

5. Liczbowe typy danych Technologie internetowe II

6. Liczbowe typy danych Technologie internetowe II

7. Typy czasu i daty Technologie internetowe II

8. Typy łańcuchowe Technologie internetowe II

9. Typy łańcuchowe Typ SET jest podobny do typu ENUM w tym sensie, że definiowany jest jako lista, jednak typ SET jest przechowywany jako pełna wartość, a nie jako indeks do wartości, jak w przypadku typu ENUM. Technologie internetowe II

10. Tworzenie, wybieranie i usuwanie rekordów

11. Składnia tworzenia tabel • Tabele zawierają pola, które określają jakie dane będzie zawierał pojedynczy rekord (inaczej: wiersz). • Do utworzenia tabeli służy polecenie SQL CREATE TABLE: CREATE TABLE nazwa_tabeli struktura_tabeli; • Każda tabela powinna posiadać swoją nazwę. Definiuje się ją w miejscu nazwa_tabeli. • W miejsce struktura_tabeli wstawia się dokładną specyfikację poszczególnych pól, jakie powinna zawierać tabela. Technologie internetowe II

12. Tworzenie tabeli CREATE TABLE pracownicy (  imie VARCHAR(30),   nazwisko VARCHAR(30),  data_urodzenia DATE,   placa DECIMAL(10,2)); Tworzy tablicę o strukturze: Technologie internetowe II

13. Utworzona tabela Technologie internetowe II

14. Definicje kolumn: opcje • Opcje mogące wystąpić w definicji kolumny w instrukcji CREATE TABLE dzielą się na: • opcje ogólne, które mogą być stosowane do (prawie) wszystkich typów kolumn • opcje szczególne, stosujące się do niektórych (klas) typów. • Opcje szczególne muszą być podane w pierwszej kolejności (bezpośrednio po określeniu typu). • Niektóre z tych opcji wykluczają się wzajemnie, np. PRIMARY KEY i NULL. Technologie internetowe II

15. Definicje kolumn: opcje • PRIMARY KEY: określa daną kolumnę jako klucz główny tabeli. Tabela może posiadać tylko jeden klucz główny. • DEFAULT wartość_domyślna: określa wartość domyślną kolumny dla nowo wprowadzanych wierszy w przypadku, gdy instrukcja tworząca nowy wiersz nie zadaje tej wartości. Jeśli w definicji kolumny pominięto opcję DEFAULT (oraz nie podano opcji NOT NULL), to w takich wypadkach wartością domyślną jest NULL. Jeżeli natomiat kolumna bez opcji DEFAULT została zadeklarowana jakoNOT NULL, to w miejsce brakującej wartości zostanie automatycznie wprowadzona przez MySQL wartość domyślna zależna od typu. • 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). • AUTO_INCREMENT: opcja ta ma sens dla kolumn o numerycznych typach wartości. Oznacza ona, że jeżeli przy tworzeniu wiersza nie zada się jawnie wartości dla tej kolumny (lub zostanie podana wartość NULL bądź zero), to wartością zapisaną będzie największa z wcześniej występujących w tej kolumnie powiększona o jeden. Technologie internetowe II

16. Definicje kolumn: opcje • UNSIGNED: opcja ta stosuje się do typów numerycznych i oznacza, że dopuszczone są wyłącznie wartości nieujemne. Zastosowanie jej zwiększa również zakres dopuszczalnych wartości (dodatnich) w sposób zależny od konkretnego typu numerycznego. • ZEROFILL: stosuje się do typów opisujących liczby całkowite. Powoduje dopełnienie pola danych zerami do jego maksymalnej długości. • BINARY: stosuje się do typów CHAR i VARCHAR. W MySQL sortowanie i porównywanie wartości znakowych odbywa się domyślnie z utożsamieniem małych i wielkich liter (zgodnie z kodowaniem ISO-8859-1). Opcja BINARY wyłącza to utożsamienie dla danej kolumny. Technologie internetowe II

17. Definicja tabeli: deklaracje dodatkowe • PRIMARY KEY (nazwa_kolumny_indeksowej, ...): klucz główny w tabeli może być tylko jeden, ale może obejmować więcej niż jedną kolumnę. Wówczas cały zestaw wartości z odpowiednich kolumn traktowany jest jako wartość klucza głównego. • KEY [nazwa_indeksu] (nazwa_kolumny_indeksowej, ..): deklaruje indeksowanie ze względu na wartości z odpowiednich kolumn. Indeks może być nazwany (zgodnie z wcześniej omówionymi regułami tworzenia nazw). Wartości klucza indeksowania nie mogą być NULL. W MySQL istnieje możliwość zadeklarowania długości klucza indeksowania, w postacikolumna(długość), mniejszej niż długość pola danych odpowiedniej kolumny. Wówczas do tworzenia indeksu wykorzystana jest jedynie część pola danych, co owocuje czasami o wiele mniejszymi (w sensie zużycia miejsca na dysku) i szybciej przeszukiwanymi indeksami.Kolumna typu BLOB nie może być indeksowana. • INDEX [nazwa_indeksu] (nazwa_kolumny_indeksowej, ...): deklaracja ta jest synonimem dla KEY. • UNIQUE [nazwa_indeksu] (nazwa_kolumny_indeksowej, ...): deklaruje indeks o nie powtarzających się wartościach. Technologie internetowe II

18. Polecenie INSERT • Polecenie INSERT służy do wypełnienia tabeli danymi po utworzeniu. INSERT INTO pracownicy VALUES ('Jan', 'Kowalski', '2002-07-20', '1200.00'); • Powyższe polecenie wstawi pojedynczy rekord do tabeli pracownicy. • Aby wstawić kolejne rekordy należy ponowić powyższe zapytanie używając nowych danych: INSERT INTO pracownicy VALUES ('Aleksander', 'Borowiecki', '1952-08-06',  '1500.34');INSERT INTO pracownicy VALUES ('Aniela', 'Michałkowska', '1970-05-23', '854.29');INSERT INTO pracownicy VALUES ('Katarzyna', 'Kowalska', '2002-07-02', '1200.00'); • Polecenia INSERT można użyć także w innej formie. Za nazwą tabeli można wyspecyfikować listę pól, które będzią wypełniane danymi. Pozostałe pola przyjmą puste lub standardowe wartości. Zapytanie z wyspecyfikowaną listą pól wygląda następująco: • INSERT INTO pracownicy (imie, nazwisko) VALUES ('Izabela', 'Kwiatkowska'); Technologie internetowe II

19. Polecenie SELECT • Aby pobrać dane zapisane w tabeli należy użyć zapytania SELECT. Jego postać ogólna prezentuje się następująco: • SELECT co_zaprezentować FROM nazwa_tabeli   [WHERE warunki_wyszukiwania]  [ORDER BY sortowanie [ASC | DESC], ...]  [LIMIT [ofset,] ilość_wierszy]; • W miejscu co_zaprezentować należy podać (po przecinku) listę kolumn, które mają być w zestawieniu. • W miejscu nazwa_tabeli podać nazwę tabeli, z której mają być pobierane dane. Technologie internetowe II

20. Polecenie SELECT • Wybór trzech kolumn do zestawienia z tabeli pracownicy wygląda następująco: SELECT imie, nazwisko, placa FROM pracownicy; • Spowoduje to wyświetlenie wszystkich rekordów, jednak w zestawieniu zostaną zaprezentowane jedynie wartości trzech pól: imie, nazwisko i placa • Aby w zestawieniu umieścić wszystkie pola można użyć w miejscu co_zaprezentować znaku '*' (gwiazdki): SELECT * FROM pracownicy; Technologie internetowe II

21. Polecenie SELECT • Dzięki klauzuli WHERE można wpłynąć na zakres prezentowanych danych - dokładnie definiować co chce się uzyskać swoim zapytaniem. • Klauzulę WHERE stosuje się najczęściej w poleceniu SELECT. • Ma ona jednak zastosowanie także w innych poleceniach operujących na danych, takich jak UPDATE, DELETE itp. • Stosując operatory przyrównania można dokładnie określić, jakie informacje chce się pobrać. Dozwolone w MySQL operatory przyrównania to: =równe >większe >=większe równe <mniejsze <=mniejsze równe <> lub !=różne LIKEsłuży głównie do porównywania danych łańcuchowych Technologie internetowe II

22. Polecenie SELECT • Przykłady zastosowania: SELECT * FROM pracownicy WHERE placa >= 1000; • Spowoduje wyświetlenie listy pracowników, których płaca jest większa lub równa 1000: SELECT imie, nazwisko, placa FROM pracownicy WHERE nazwisko = 'Kowalski'; • Spowoduje wyświetlenie danych (tylko imię, nazwisko i placa) wszystkich pracowników, których nazwisko brzmi dokładnie Kowalski: SELECT * FROM pracownicy WHERE nazwisko LIKE 'K%'; • Spowoduje wyświetlenie wszystkich pracowników, których nazwisko rozpoczyna się na literę 'K': Technologie internetowe II

23. Polecenie SELECT • Dzięki zastosowaniu znaków globalnych (% i _) istnieje możliwość przyrównania do dowolnego ciągu znaków. Znak '%' (procent) zastępuje dowolną ilość znaków. Znak '_' (podkreślenie) zastępuje dokładnie jeden znak. Zapytanie: SELECT imie, nazwisko FROM pracownicy WHERE nazwisko LIKE 'Kowalsk_'; • spowoduje wyświetlenie wszystkich pracowników, których nazwisko zaczyna się ciągiem znaków 'Kowalsk' i zaraz po nim występuje jeden dowolny znak. • Warunki wyboru podawane za WHERE można łączyć ze sobą stosując operatory AND oraz OR. Dzięki temu istnieje możliwość zbudowania zapytania bardziej złożonego, a co za tym idzie bardziej dokładnego. Wydając zapytanie: SELECT * FROM pracownicy WHERE (placa > 500 AND placa < 1000) OR nazwisko = 'Kowalski'; • spowoduje wyświetlenie w wyniku wszystkich pracowników, których płaca mieści się w zakresie 500-1000 oraz pracowników o nazwisku 'Kowalski': Technologie internetowe II

24. Polecenie SELECT • Dane w tabeli mogą być przechowywane w dowolnej kolejności. Można jednak spowodować ich pobranie w ściśle określonym porządku. Kolumny, według których MySQL ma posortować dane podaje się po klauzuli ORDER BYoddzielone przecinkami. • Chcąc uszeregować listę pracowników rosnąco według nazwiska i malejąco według płacy należy wydać następujące polecenie: SELECT * FROM pracownicy ORDER BY nazwisko ASC, placa DESC; • ASC oznacza sortowanie rosnąco według podanego pola, DESC natomiast oznacza sortowanie malejące Technologie internetowe II

25. Polecenie SELECT • Użycie klauzuli LIMIT spowoduje wyświetlenie jedynie części rekordów. Aby pobrać dwa pierwsze rekordy napisz SELECT * FROM pracownicy LIMIT 2; • Wypełniając pole ofset wyświetla się podaną ilość rekordów od pewnego miejsca w tabeli. Chcąc pobrać rekordy od 3 do 7 należy napisać: SELECT * FROM pracownicy LIMIT 2, 5; • Spowoduje to wyświetlenie pięciu rekordów (o ile tyle istnieje w bazie) poczynając od rekordu trzeciego. • MySQL pozwala na połączenie ze sobą wielu opcji, dzięki którym można bardzo dokładnie zawęzić poszukiwaną ilość informacji. Przykład bardziej złożonego zapytania można przedstawić następująco: SELECT imie, nazwisko, placa FROM pracownicy  WHERE placa >= 500 AND placa < 1200  ORDER BY nazwisko  LIMIT 5; • Jak widać połączono w tym momencie warunek wyboru według płacy pomiędzy 500 a 1200 z sortowaniem danych według nazwiska i ograniczeniem wyniku tylko do pięciu pierwszych rekordów. Technologie internetowe II

26. Polecenie SELECT – kilka tabel • Tabele: owoc, kolor Technologie internetowe II

27. Polecenie SELECT – kilka tabel • ID SELECT * FROM owoc, kolor ID nazwa_owocu ID nazwa_koloru 1 jabłko 1 czerwony 2 pomarańcza 1 czerwony 3 banan 1 czerwony 1 jabłko 2 pomarańczowy 2 pomarańcza 2 pomarańczowy 3 banan 2 pomarańczowy 1 jabłko 3 żółty 2 pomarańcza 3 żółty 3 banan 3 żółty Technologie internetowe II

28. Polecenie SELECT – kilka tabel • SELECT Nazwa_owocu, Nazwa_koloru FROM owoc, kolor WHERE owoc.id=kolor.id jabłko czerwony pomarańcza pomarańczowy banan żółty Technologie internetowe II

29. Polecenie JOIN • SELECT Nazwa_owocu, Nazwa_koloru FROM owoc -> INNER JOIN kolor ON owoc.id=kolor.id • LEFT JOIN – zwraca wiersze w pierwszej tabeli niezależnie od istnienia wierszy w drugiej tabeli • RIGHT JOIN – jak LEFT JOIN, ale kolejność odwrotna • Są jeszcze CROSS JOIN, STRAIGHT JOIN, NATURAL JOIN i inne Zastępuje WHERE Technologie internetowe II

30. Podselekcja • SELECT wyrażenia i kolumny FROM nazwa_tabeli WHERE wybrana_kolumna (PODSELEKCJA) • Podselekcja musi występować w nawiasach • Można uzywać z SELECT, INSERT, UPDATE, DELETE, SET, DO Technologie internetowe II

31. Polecenie UPDATE • Polecenie UPDATE służy do modyfikacji danych już wcześniej umieszczonych w tabeli. Jego składnia jest następująca: UPDATE nazwa_tabeli SET nazwa_pola='nowa_wartość'; • Po słowie kluczowym SET  kolejno (po przecinku) podaje się nazwy kolumn wraz z nowymi wartościami, jakie powinny przyjąć. Po wydaniu poniższego polecenia: UPDATE pracownicy SET imie='Zofia'; • wszystkie rekordy w polu imie będą miały wartość 'Zofia': Technologie internetowe II

32. Polecenie UPDATE • Zazwyczaj chcemy zmienić dane dotyczące tylko jednego lub wybranych rekordów. Do określenia czego ma dotyczyć zmiana służy klauzula WHERE podawana na końcu polecenia UPDATE. • W celu zmiany imienia tylko dla Izabeli Kwiatkowskiej polecenie UPDATE będzie wyglądać następująco: • UPDATE pracownicy SET imie='Zofia' WHERE nazwisko='Kwiatkowska'; Technologie internetowe II

33. Polecenie DELETE • Do usunięcia danych z tabeli służy polecenie DELETE. Aby usunąć wszystkie dane z tabeli należy wydać polecenie: DELETE FROM nazwa_tabeli; • Można użyć także opisywanego już wcześniej warunku wyboru, do wyspecyfikowania danych przeznaczonych do usunięcia. Aby usunąć z przykładowej tabeli pracownicy wszystkie rekordy, w których płaca jest wyższa od 1000 należy wydać następujące polecenie: DELETE FROM pracownicy WHERE placa > 1000; Technologie internetowe II

34. Funkcje operujące na datach Technologie internetowe II

35. Funkcje operujące na datach Technologie internetowe II

36. Obliczenia na datach • Funkcja ADDDATE() i SUBDATE(), będące synonimami DATE_ADD() i DATE_SUB(), przeprowadzają obliczenia na datach. Ich składnia jest następująca: ADDDATE(data, INTERVAL x typ) • Data może pochodzić z kolumny tabeli lub może być wprowadzona ręcznie. Wartość x jest różna dla każdego typu. Dostępne typy to: SECOND, MINUTE, HOUR, DAY, MONTH i YEAR. • Mogą one tez występować w nast. następujących kombinacjach: MINUTE_SECOND, HOUR_MINUTE, DAY_HOUR, oraz YEARS_MONTH. Technologie internetowe II

37. Obliczenia na datach • Aby dodać do wskazanej daty dwie godziny: ADDATE(data, INTERVAL 2 HOUR) • Aby dodać dwa tygodnie do 31 grudnia 2002: ADDATE(`2002-12-31`, INTERVAL 14 DAY) • Aby odjąć od daty 15 miesięcy, wpisz: SUBDATE(data, INTERVAL `1-3` YEAR_MONTH) • Ostatnie zapytanie informuje system, że chcesz odjąć od wartości przechowywanej w kolumnie jeden rok i trzy miesiące.  Technologie internetowe II

38. Interakcje PHP z MySQL

39. Warunki wykorzystania bazy danych • Istnienie działającej bazy danych w miejscu, które jest osiągalne przez serwer WWW. • Posiadanie dostępu (użytkownik i hasło) z odpowiednimi uprawnieniami. • Istnienie bazy danych (chyba, że ją tworzymy). • Używamy funkcji z rodziny mysqli Technologie internetowe II

40. Łączenie z bazą danych

41. Połączenie z bazą danych Tworzenie nowego połączenia <?php $mysqli = mysqli_connect("localhost", "uzytkownik", "haslo”, "baza"); if (mysqli_connect_errno()) { printf("Nie moża się połączyć: %s\n", mysqli_connect_error()); exit(); } else { printf("Informacje o hoście: %s\n", mysqli_get_host_info($mysqli)); } ?> Sprawdzamy błąd Pobieramy komunikat błędu Pobieramy informacje o hoście Technologie internetowe II

42. Połączenie z bazą danych <?php $mysqli = mysqli_connect("localhost", "uzytkownik", "haslo”, "baza"); if (mysqli_connect_errno()) { printf("Nie moża się połączyć: %s\n", mysqli_connect_error()); exit(); } else { printf("Informacje o hoście: %s\n", mysqli_get_host_info($mysqli)); mysqli_close($mysqli); } ?> Połączenie lepiej zamknąć, choć jest zamykane automatycznie Technologie internetowe II

43. Utworzenie bazy danych <?php $mysqli = mysqli_connect("localhost", "uzytkownik", "haslo”, "baza"); if (mysqli_connect_errno()) { printf("Nie moża się połączyć: %s\n", mysqli_connect_error()); exit(); } else { $sql = "CREATE TABLE tabelaProbna (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, poleProbne VARCHAR (75))"; $rez = mysqli_query($mysqli, $sql); if ($rez === TRUE) { echo "Tabela tabelaProbna została utworzona."; } else { printf("Nie można utworzyć tabeli: %s\n", mysqli_error($mysqli)); } mysqli_close($mysqli); } ?> Treść zapytania przypisujemy zmiennej Średnik nie jest obowiązkowy Weryfikujemy utworzenie tabeli Technologie internetowe II

44. Operowanie na danych

45. Wstawianie rekordów <?php $mysqli = mysqli_connect("localhost", "uzytkownik", "haslo”, "baza"); if (mysqli_connect_errno()) { printf("Nie moża się połączyć: %s\n", mysqli_connect_error()); exit(); } else { $sql = "INSERT INTO tabelaProbna (poleProbne) VALUES ('some value')"; $rez = mysqli_query($mysqli, $sql); if ($rez === TRUE) { echo "Wstawiono rekord."; } else { printf("Nie można wstawić rekordu: %s\n", mysqli_error($mysqli)); } mysqli_close($mysqli); } ?> Tutaj wstawiamy kolejny wiersz do tabeli – stała wartość Technologie internetowe II

46. Formularz do wprowadzania danych <html> <head> <title>Formularz wstawiania rekordów</title> </head> <body> <form action="insert.php" method="POST"> <p>Tekst do dodania:<br> <input type="text" name="poleProbne" size="30"> <p><input type="submit" name="submit" value="wstaw rekord"></p> </form> </body> </html> Technologie internetowe II

47. Uzupełnianie bazy danych <?php $mysqli = mysqli_connect("localhost", "uzytkownik", "haslo”, "baza"); if (mysqli_connect_errno()) { printf("Nie moża się połączyć: %s\n", mysqli_connect_error()); exit(); } else { $sql = "INSERT INTO tabelaProbna (poleProbne) VALUES ('".$_POST["poleProbne"]."')"; $rez = mysqli_query($mysqli, $sql); if ($rez === TRUE) { echo "Wstawiono rekord."; } else { printf("Nie można wstawić rekordu: %s\n", mysqli_error($mysqli)); } mysqli_close($mysqli); } ?> Tutaj wstawiamy kolejny wiersz do tabeli – wartość z formularza Technologie internetowe II

48. Wybieranie rekordów <?php $mysqli = mysqli_connect("localhost", "uzytkownik", "haslo”, "baza"); if (mysqli_connect_errno()) { printf("Nie moża się połączyć: %s\n", mysqli_connect_error()); exit(); } else { $sql = "SELECT * FROM tabelaProbna"; $rez = mysqli_query($mysqli, $sql); if ($rez) { $liczba_wierszy = mysqli_num_rows($rez); printf("Liczba wierszy w tabeli: %d\n", $liczba_wierszy); } else { printf("Nie można pobrać rekordów: %s\n", mysqli_error($mysqli)); } mysqli_free_result($rez); mysqli_close($mysqli); } ?> Pobiera liczbę wierszy w zbiorze wyników zwróconych przez bazę Zwalnia zasoby pamięci zajmowane przez zapytanie Technologie internetowe II

49. Wybieranie rekordów <?php $mysqli = mysqli_connect("localhost", "uzytkownik", "haslo”, "baza"); if (mysqli_connect_errno()) { printf("Nie moża się połączyć: %s\n", mysqli_connect_error()); exit(); } else { $sql = "SELECT * FROM tabelaProbna"; $rez = mysqli_query($mysqli, $sql); if ($rez) { while ($newArray = mysqli_fetch_array($rez, MYSQLI_ASSOC)) { $id = $newArray['id']; $poleProbne = $newArray['poleProbne']; echo "Identyfikator to ".$id." a tekst to ".$poleProbne."<br/>"; } } else { printf("Nie można pobrać rekordów: %s\n", mysqli_error($mysqli)); } mysqli_free_result($rez); mysqli_close($mysqli); } • ?> WHILE przechodzi przez wszystkie rekordy zbioru wyników Technologie internetowe II