SQL – Structured Query Language (3)

1. SQL – Structured Query Language (3) Wykład 7 Prowadzący: dr Paweł Drozda

2. Plan wykładu • Podzapytania • Perspektywy • Wybrane funkcje MySQL • Wyrażenia regularne dr Paweł Drozda

3. Podzapytania • Można stosować dla klauzuli: WHERE HAVING FROM • Taka sama postać jak zwykłe zapytanie – ujęte w nawiasy • Podzapytanie jako prawy argument predykatów =, <, <=, >, >=, <>, IN, NOT IN dr Paweł Drozda

4. Typy Podzapytań • Liczba wyników • Wierszowe – wynikiem podzapytania jest pojedynczy wiersz • Tablicowe – wynikiem podzapytania jest wiele wierszy • Zależność od wierszy zapytania głównego • Zwykłe – gdy podzapytanie nie zależy od zapytania głównego • Skorelowane – gdy zapytanie wykorzystuje elementy zapytania głównego dr Paweł Drozda

5. Podzapytania – przykłady (1) • Wierszowe SELECT * FROM pracownik WHERE zarobki = (SELECT MAX(zarobki ) FROM pracownik); • Tablicowe SELECT * FROM pracownik WHERE id_pracownika NOT IN (SELECT prowadzacy FROM przedmioty); SELECT * FROM student WHERE nazwisko LIKE ‘%a%’ AND nrindeksu IN (SELECT student FROM oceny WHERE ocena=5); INSERT INTO student(imie, nazwisko, adres, rok, telefon) SELECT imie, nazwisko, adres, 1, 997 from kandydaci; dr Paweł Drozda

6. Podzapytania – przykłady(2) • Zwykłe SELECT tytul, autor FROM ksiazki WHERE cena = (SELECT MAX(cena) from ksiazki); • Skorelowane SELECT a.imie, a.nazwisko, a.zarobki, a.stanowisko FROM pracownik a WHERE zarobki = (SELECT MAX(b.zarobki) FROM pracownik b WHERE b.stanowisko = a.stanowisko); dr Paweł Drozda

7. Podzapytania – kwantyfikatory (1) • ALL – dla wszystkich elementów podzapytania warunek musi być spełniony SELECT imie, nazwisko FROM pracownik WHERE zarobki > ALL (SELECT zarobki FROM pracownik WHERE stanowisko = ‘adiunkt’); • ANY(SOME) – co najmniej dla jednego elementu podzapytania warunek musi być spełniony SELECT imie, nazwisko FROM pracownik WHERE zarobki > ANY (SELECT zarobki FROM pracownik WHERE stanowisko = ‘adiunkt’); dr Paweł Drozda

8. Podzapytania – kwantyfikatory (2) • EXISTS – kwantyfikator egzystencjalny „istnieje” SELECT nazwisko FROM pracownik WHERE EXISTS (SELECT ‘x’ FROM przedmioty WHERE przedmioty.prowadzacy = pracownik.id_pracownika); • NOT EXISTS – kwantyfikator uniwersalny z negacją „dla każdego nieprawda że” SELECT nazwisko FROM pracownik WHERE NOT EXISTS (SELECT ‘x’ FROM przedmioty WHERE przedmioty.prowadzacy = pracownik.id_pracownika); dr Paweł Drozda

9. Podzapytania – tworzenie tabel • Po FROM SELECT a.stanowisko, 100*a.liczbaprac/b.liczbaprac as ‘procPracowników’, 100*a.zarob/b.zarob as ‘procZarobkow’ FROM (SELECT stanowisko, COUNT(*) AS liczbaprac, SUM(zarobki) as zarob FROM pracownik GROUP BY stanowisko) a, (SELECT COUNT(*) AS liczbaprac, SUM(zarobki) AS zarob FROM pracownik) b; • Tworzenie tabeli (po AS) CREATE TABLE nowa (Imie varchar(30), Nazwisko varchar(30)) AS SELECT imie, nazwisko FROM pracownik WHERE zarobki >4000; dr Paweł Drozda

10. Perspektywy (1) • Nazwana tabela • Nie może istnieć samodzielnie – dane pobiera z tabel bazowych (stworzonych przez CREATE TABLE) lub innych perspektyw • W MySQL może posłużyć do zapamiętywania wykonywanych zapytań • Gdy dane są aktualizowane w tabeli bazowej – odzwierciedlenie w perspektywie • Gdy struktura tabeli bazowych się zmienia – brak odzwierciedlenia w perspektywie dr Paweł Drozda

11. Perspektywy (2) • Określają widok na bazę danych dla pewnych grup użytkowników • Możliwe usuwanie, dodawanie, aktualizacja danych w perspektywie – dane w tabeli bazowej również zmieniana dr Paweł Drozda

12. Perspektywy – SQL (1) • Tworzenie – składnia: CREATE [OR REPLACE] VIEW nazwa AS zapytanie; • Przykład: CREATE OR REPLACE VIEW Pierwszy AS SELECT nazwisko FROM Student WHERE rok=1; • Usuwanie – składnia: DROP VIEW nazwa [RESTRICT/ CASCADE]; • Opcja sprawdzania (WITH CHECK OPTION) – sprawdza czy warunek podany w perspektywie nie zostaje zmieniony przez modyfikację bądź dodanie nowej krotki dr Paweł Drozda

13. Perspektywy – SQL (2) • Przykład CREATE VIEW bogacze AS SELECT * FROM Pracownik WHERE zarobki > 4000 WITH CHECK OPTION; INSERT INTO bogacze(nazwisko, imie , zarobki) VALUES (‘Biedak’, ‘Jan’, 2000); - takie zapytanie zwróci błąd INSERT INTO bogacze(nazwisko, imie , zarobki) VALUES (‘Bogaty’, ‘Stefan’, 5000); - krotka zostanie dodana UPDATE bogacze SET zarobki = zarobki – 400; - co się stanie dla zarobków pomiędzy 4000 – 4400? dr Paweł Drozda

14. Funkcje MySQL • Ułatwiają wykonanie wielu zapytań • Dają dodatkowe możliwości • Typy funkcji • Warunkowe • Tekstowe • Liczbowe • Daty i Czasu • Zbiór wszystkich funkcji http://dev.mysql.com/doc/refman/5.0/en/functions.html dr Paweł Drozda

15. Funkcje warunkowe(1) • CASE – wartość porównywana z opcjami i zwracany odpowiedni wynik CASE wartosc WHEN opcja1 THEN wynik1 [WHEN opcja2 THEN wynik2] … [ELSE wynikn] END; SELECT CASE 1 WHEN 1 THEN ‘jeden’ WHEN 2 THEN ‘dwa’ ELSE ‘wiecej’ END; - wynik ‘jeden’ • IF – jeśli wyrażenie jest prawdziwe zwracany wynik1, wpp wynik2 IF (wyrazenie, wynik1, wynik2); SELECT IF (1>0,’true’,’false’); - wynik ‘true’ dr Paweł Drozda

16. Funkcje warunkowe(2) • IFNULL(wynik1,wynik2) – jeśli wynik1 nie jest null – jest zwracany, jeśli jest null zwracany wynik2 CREATE TABLE nowa AS SELECT IFNULL(‘xxxxx’,’test’) AS test; • NULLIF (wyr1,wyr2) – gdy wyrażenia są równe zwraca NULL gdy nie – zwraca wyr1 SELECT NULLIF(2,2); dr Paweł Drozda

17. Funkcje tekstowe (1) • ASCII(wyr) – zwraca kod ASCII dla pierwszego znaku wyr SELECT ASCII(‘xd’); • CHAR(licz1, licz2, …) – odwrotne do ASCII SELECT CHAR(77,121,83,81,'76'); • BIT_LENGTH(string) – zwraca ilość bitów zajmowanych przez string SELECT BIT_LENGTH('text'); • CONCAT(str1,str2,...) – łączy str1, str2, … w jeden ciąg znaków SELECT CONCAT(Imie, „ ”, Nazwisko) FROM student; dr Paweł Drozda

18. Funkcje tekstowe (2) • ELT(N,str1,str2,str3,...) – dla N=i zwraca stri SELECT ELT(3,’jeden’,’dwa’,’trzy’,’cztery’); • FIND_IN_SET(str,strlist) – szuka miejsca str na liście SELECT FIND_IN_SET(‘a’,’a,b,c,d’); • LENGTH(str) – zwraca długość ciągu znaków SELECT LENGTH(‘test’); • LOAD_FILE(file) – ładuje plik do MySQL file – ścieżka dostępu do pliku • LOWER(str) – zmienia str na małe litery dr Paweł Drozda

19. Funkcje tekstowe (3) • UPPER(str) – zmienia str na wielkie litery SELECT UPPER(‘test’); • MAKE_SET(bits,str1,str2,...) – tworzy zbiór z wybranych str1,… w zależności od wartości bits SELECT MAKE_SET(1|4|16, ‘jeden’,’dwa’,’trzy’,’cztery’,’piec’); • REPLACE(str,from_str,to_str) – w str zamienia wystąpienia from_str na to_str SELECT REPLACE (‘wuawuawiwa’,’wua’,’x’); • SUBSTRING(str,pos,len) ze str od pozycyji pos wyciąga ciąg znaków o długości len SELECT SUBSTRING(‘testtesttesttest',5,6); dr Paweł Drozda

20. Funkcje liczbowe • SIN(), COS(), COT(), TAN() – funkcje trygonometryczne • SQRT() – pierwiastek, SIGN() – znak liczby • LN(), LOG10(), LOG2() – logarytmy • ABS() – wartość bezwzględna • CONV(N,from_base,to_base) – zamienia liczbę N podaną w systemie from_base na liczbę w systemie to_base SELECT CONV(123, 4,16); • PI() – zwraca liczbę pi • TRUNCATE(X,D) – obcina liczbę X do D miejsc po przecinku dr Paweł Drozda

21. Funkcje daty i godziny • CURDATE() – dzisiejsza data • NOW() – obecny czas i data • CURTIME() – obecny czas • DATE_ADD() – dodaje daty DATE_SUB() – odejmuje daty SELECT DATE_ADD('2008-01-02', INTERVAL 31 DAY); SELECT ADDDATE('2008-01-02', 31); • DAYNAME(data) – nazwa dnia • DAYOFMONTH (data) – numer dnia w miesiącu • MONTH(), YEAR() – numery miesiąca i roku dr Paweł Drozda

22. Wyrażenia regularne • Tworzone za pomocą REGEXP • Dostępne symbole ^ - dopasowanie początku ciągu SELECT ‘test’ REGEXP ‘^t’; $ - dopasowanie końca ciągu SELECT ‘test’ REGEXP ‘t$’; . – dopasowanie dowolnego znaku a* - dopasowanie 0 lub wielu wystąpień a a+ - dopasowanie 1 lub wielu wystąpień a a? – dopasowanie 0 lub 1 wystąpień a a|b – dopasowanie do a lub do b [a-zA-Z] – dopasowanie do dowolnej litery [^0-9] – brak możliwości wystąpienia cyfry dr Paweł Drozda

23. Wyrażenia regularne - przykłady SELECT 'test@com.com' REGEXP '[a-z]@[a-z]\.[a-z]'; SELECT ‘123506’ REGEXP ’[0-9]’; SELECT ‘asder12’ REGEXP ‘[a-z][0-9]’ dr Paweł Drozda