SQL  Structured Query
Language (3)
Wykład 7
Prowadzący: dr Paweł Drozda
Plan wykładu
oðPodzapytania
oðPerspektywy
oðWybrane funkcje MySQL
oðWyrażenia regularne
dr Paweł Drozda
Podzapytania
oð Można stosować dla klauzuli:
WHERE
HAVING
FROM
oð Taka sama postać jak zwykÅ‚e zapytanie  ujÄ™te
w nawiasy
oð Podzapytanie jako prawy argument predykatów
=, <, <=, >, >=, <>, IN, NOT IN
dr Paweł Drozda
Typy Podzapytań
oð Liczba wyników
Wierszowe  wynikiem podzapytania jest pojedynczy
wiersz
Tablicowe  wynikiem podzapytania jest wiele
wierszy
oð 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
Podzapytania  przykłady (1)
oð Wierszowe
SELECT * FROM pracownik WHERE zarobki = (SELECT
MAX(zarobki ) FROM pracownik);
oð 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
Podzapytania  przykłady(2)
oðZwykÅ‚e
SELECT tytul, autor FROM ksiazki WHERE cena
= (SELECT MAX(cena) from ksiazki);
oð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
Podzapytania  kwantyfikatory (1)
oð 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 );
oð 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
Podzapytania  kwantyfikatory (2)
oð EXISTS  kwantyfikator egzystencjalny  istnieje
SELECT nazwisko FROM pracownik WHERE EXISTS (SELECT  x
FROM przedmioty WHERE przedmioty.prowadzacy =
pracownik.id_pracownika);
oð 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
Podzapytania  tworzenie tabel
oð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;
oð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
Perspektywy (1)
oð Nazwana tabela
oð Nie może istnieć samodzielnie  dane pobiera z
tabel bazowych (stworzonych przez CREATE
TABLE) lub innych perspektyw
oð W MySQL może posÅ‚użyć do zapamiÄ™tywania
wykonywanych zapytań
oð Gdy dane sÄ… aktualizowane w tabeli bazowej 
odzwierciedlenie w perspektywie
oð Gdy struktura tabeli bazowych siÄ™ zmienia 
brak odzwierciedlenia w perspektywie
dr Paweł Drozda
Perspektywy (2)
oð OkreÅ›lajÄ… widok na bazÄ™ danych dla pewnych
grup użytkowników
oð Możliwe usuwanie, dodawanie, aktualizacja
danych w perspektywie  dane w tabeli
bazowej również zmieniana
dr Paweł Drozda
Perspektywy  SQL (1)
oð Tworzenie  skÅ‚adnia:
CREATE [OR REPLACE] VIEW nazwa AS zapytanie;
oð PrzykÅ‚ad:
CREATE OR REPLACE VIEW Pierwszy AS SELECT
nazwisko FROM Student WHERE rok=1;
oð Usuwanie  skÅ‚adnia:
DROP VIEW nazwa [RESTRICT/ CASCADE];
oð Opcja sprawdzania (WITH CHECK OPTION) 
sprawdza czy warunek podany w perspektywie
nie zostaje zmieniony przez modyfikacjÄ™ bÄ…dz

dodanie nowej krotki
dr Paweł Drozda
Perspektywy  SQL (2)
oð 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
Funkcje MySQL
oðUÅ‚atwiajÄ… wykonanie wielu zapytaÅ„
oðDajÄ… dodatkowe możliwoÅ›ci
oðTypy funkcji
Warunkowe
Tekstowe
Liczbowe
Daty i Czasu
oðZbiór wszystkich funkcji
http://dev.mysql.com/doc/refman/5.0/en/functions.html
dr Paweł Drozda
Funkcje warunkowe(1)
oð 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
oð 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
Funkcje warunkowe(2)
oð 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;
oð NULLIF (wyr1,wyr2)  gdy wyrażenia sÄ… równe
zwraca NULL gdy nie  zwraca wyr1
SELECT NULLIF(2,2);
dr Paweł Drozda
Funkcje tekstowe (1)
oð ASCII(wyr)  zwraca kod ASCII dla pierwszego
znaku wyr
SELECT ASCII( xd );
oð CHAR(licz1, licz2, & )  odwrotne do ASCII
SELECT CHAR(77,121,83,81,'76');
oð BIT_LENGTH(string)  zwraca ilość bitów
zajmowanych przez string
SELECT BIT_LENGTH('text');
oð CONCAT(str1,str2,...)  Å‚Ä…czy str1, str2, & w
jeden ciąg znaków
SELECT CONCAT(Imie,   , Nazwisko) FROM student;
dr Paweł Drozda
Funkcje tekstowe (2)
oð ELT(N,str1,str2,str3,...)  dla N=i zwraca stri
SELECT ELT(3, jeden , dwa , trzy , cztery );
oð FIND_IN_SET(str,strlist)  szuka miejsca str na
liście
SELECT FIND_IN_SET( a , a,b,c,d );
oð LENGTH(str)  zwraca dÅ‚ugość ciÄ…gu znaków
SELECT LENGTH( test );
oð LOAD_FILE(file)  Å‚aduje plik do MySQL file 
ścieżka dostępu do pliku
oð LOWER(str)  zmienia str na maÅ‚e litery
dr Paweł Drozda
Funkcje tekstowe (3)
oð UPPER(str)  zmienia str na wielkie litery
SELECT UPPER( test );
oð 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 );
oð REPLACE(str,from_str,to_str)  w str zamienia
wystÄ…pienia from_str na to_str
SELECT REPLACE ( wuawuawiwa , wua , x );
oð 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
Funkcje liczbowe
oð SIN(), COS(), COT(), TAN()  funkcje trygonometryczne
oð SQRT()  pierwiastek, SIGN()  znak liczby
oð LN(), LOG10(), LOG2()  logarytmy
oð ABS()  wartość bezwzglÄ™dna
oð 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);
oð PI()  zwraca liczbÄ™ pi
oð TRUNCATE(X,D)  obcina liczbÄ™ X do D miejsc po
przecinku
dr Paweł Drozda
Funkcje daty i godziny
oð CURDATE()  dzisiejsza data
oð NOW()  obecny czas i data
oð CURTIME()  obecny czas
oð DATE_ADD()  dodaje daty DATE_SUB() 
odejmuje daty
SELECT DATE_ADD('2008-01-02', INTERVAL 31 DAY);
SELECT ADDDATE('2008-01-02', 31);
oð DAYNAME(data)  nazwa dnia
oð DAYOFMONTH (data)  numer dnia w miesiÄ…cu
oð MONTH(), YEAR()  numery miesiÄ…ca i roku
dr Paweł Drozda
Wyrażenia regularne
oð Tworzone za pomocÄ… REGEXP
oð 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
Wyrażenia regularne - przykłady
SELECT '[e-mail]' REGEXP '[a-z]@[a-z]\.[a-z]';
SELECT  123506 REGEXP  [0-9] ;
SELECT  asder12 REGEXP  [a-z][0-9]
dr Paweł Drozda