SQL

1
SQL Structured Query Language (3)

• Wyklad 7
• Prowadzacy dr Pawel Drozda

2
Plan wykladu

• Podzapytania
• Perspektywy
• Wybrane funkcje MySQL
• Wyrazenia regularne

3
Podzapytania

• Mozna stosowac dla klauzuli
• WHERE
• HAVING
• FROM
• Taka sama postac jak zwykle zapytanie ujete w
  nawiasy
• Podzapytanie jako prawy argument predykatów
• , lt, lt, gt, gt, ltgt, IN, NOT IN

4
Typy Podzapytan

• Liczba wyników
• Wierszowe wynikiem podzapytania jest pojedynczy
  wiersz
• Tablicowe wynikiem podzapytania jest wiele
  wierszy
• Zaleznosc od wierszy zapytania glównego
• Zwykle gdy podzapytanie nie zalezy od zapytania
  glównego
• Skorelowane gdy zapytanie wykorzystuje elementy
  zapytania glównego

5
Podzapytania przyklady (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
  ocena5)
• INSERT INTO student(imie, nazwisko, adres, rok,
  telefon) SELECT imie, nazwisko, adres, 1, 997
  from kandydaci

6
Podzapytania przyklady(2)

• Zwykle
• 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)

7
Podzapytania kwantyfikatory (1)

• ALL dla wszystkich elementów podzapytania
  warunek musi byc spelniony
• SELECT imie, nazwisko FROM pracownik WHERE
  zarobki gt ALL (SELECT zarobki FROM pracownik
  WHERE stanowisko adiunkt)
• ANY(SOME) co najmniej dla jednego elementu
  podzapytania warunek musi byc spelniony
• SELECT imie, nazwisko FROM pracownik WHERE
  zarobki gt ANY (SELECT zarobki FROM pracownik
  WHERE stanowisko adiunkt)

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 negacja
  dla kazdego nieprawda ze
• SELECT nazwisko FROM pracownik WHERE NOT EXISTS
  (SELECT x FROM przedmioty WHERE
  przedmioty.prowadzacy pracownik.id_pracownika)

9
Podzapytania tworzenie tabel

• Po FROM
• SELECT a.stanowisko, 100a.liczbaprac/b.liczbaprac
  as procPracowników, 100a.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 gt4000

10
Perspektywy (1)

• Nazwana tabela
• Nie moze istniec samodzielnie dane pobiera z
  tabel bazowych (stworzonych przez CREATE TABLE)
  lub innych perspektyw
• W MySQL moze posluzyc do zapamietywania
  wykonywanych zapytan
• Gdy dane sa aktualizowane w tabeli bazowej
  odzwierciedlenie w perspektywie
• Gdy struktura tabeli bazowych sie zmienia brak
  odzwierciedlenia w perspektywie

11
Perspektywy (2)

• Okreslaja widok na baze danych dla pewnych grup
  uzytkowników
• Mozliwe usuwanie, dodawanie, aktualizacja danych
  w perspektywie dane w tabeli bazowej równiez
  zmieniana

12
Perspektywy SQL (1)

• Tworzenie skladnia
• CREATE OR REPLACE VIEW nazwa AS zapytanie
• Przyklad
• CREATE OR REPLACE VIEW Pierwszy AS SELECT
  nazwisko FROM Student WHERE rok1
• Usuwanie skladnia
• DROP VIEW nazwa RESTRICT/ CASCADE
• Opcja sprawdzania (WITH CHECK OPTION) sprawdza
  czy warunek podany w perspektywie nie zostaje
  zmieniony przez modyfikacje badz dodanie nowej
  krotki

13
Perspektywy SQL (2)

• Przyklad
• CREATE VIEW bogacze AS SELECT FROM Pracownik
  WHERE zarobki gt 4000 WITH CHECK OPTION
• INSERT INTO bogacze(nazwisko, imie , zarobki)
  VALUES (Biedak, Jan, 2000) - takie zapytanie
  zwróci blad
• INSERT INTO bogacze(nazwisko, imie , zarobki)
  VALUES (Bogaty, Stefan, 5000) - krotka
  zostanie dodana
• UPDATE bogacze SET zarobki zarobki 400 - co
  sie stanie dla zarobków pomiedzy 4000 4400?

14
Funkcje MySQL

• Ulatwiaja wykonanie wielu zapytan
• Daja dodatkowe mozliwosci
• Typy funkcji
• Warunkowe
• Tekstowe
• Liczbowe
• Daty i Czasu
• Zbiór wszystkich funkcji http//dev.mysql.com/doc/
  refman/5.0/en/functions.html

15
Funkcje warunkowe(1)

• CASE wartosc 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 jesli wyrazenie jest prawdziwe zwracany
  wynik1, wpp wynik2
• IF (wyrazenie, wynik1, wynik2)
• SELECT IF (1gt0,true,false) - wynik true

16
Funkcje warunkowe(2)

• IFNULL(wynik1,wynik2) jesli wynik1 nie jest
  null jest zwracany, jesli jest null zwracany
  wynik2
• CREATE TABLE nowa AS SELECT IFNULL(xxxxx,test)
  AS test
• NULLIF (wyr1,wyr2) gdy wyrazenia sa równe
  zwraca NULL gdy nie zwraca wyr1
• SELECT NULLIF(2,2)

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 ilosc bitów
  zajmowanych przez string
• SELECT BIT_LENGTH('text')
• CONCAT(str1,str2,...) laczy str1, str2, w
  jeden ciag znaków
• SELECT CONCAT(Imie, , Nazwisko) FROM student

18
Funkcje tekstowe (2)

• ELT(N,str1,str2,str3,...) dla Ni zwraca stri
• SELECT ELT(3,jeden,dwa,trzy,cztery)
• FIND_IN_SET(str,strlist) szuka miejsca str na
  liscie
• SELECT FIND_IN_SET(a,a,b,c,d)
• LENGTH(str) zwraca dlugosc ciagu znaków
• SELECT LENGTH(test)
• LOAD_FILE(file) laduje plik do MySQL file
  sciezka dostepu do pliku
• LOWER(str) zmienia str na male litery

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 zaleznosci od wartosci bits
• SELECT MAKE_SET(1416, jeden,dwa,trzy,czte
  ry,piec)
• REPLACE(str,from_str,to_str) w str zamienia
  wystapienia from_str na to_str
• SELECT REPLACE (wuawuawiwa,wua,x)
• SUBSTRING(str,pos,len) ze str od pozycyji pos
  wyciaga ciag znaków o dlugosci len
• SELECT SUBSTRING(testtesttesttest',5,6)

20
Funkcje liczbowe

• SIN(), COS(), COT(), TAN() funkcje
  trygonometryczne
• SQRT() pierwiastek, SIGN() znak liczby
• LN(), LOG10(), LOG2() logarytmy
• ABS() wartosc bezwzgledna
• CONV(N,from_base,to_base) zamienia liczbe N
  podana w systemie from_base na liczbe w systemie
  to_base
• SELECT CONV(123, 4,16)
• PI() zwraca liczbe pi
• TRUNCATE(X,D) obcina liczbe X do D miejsc po
  przecinku

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 miesiacu
• MONTH(), YEAR() numery miesiaca i roku

22
Wyrazenia regularne

• Tworzone za pomoca REGEXP
• Dostepne symbole
• - dopasowanie poczatku ciagu
• SELECT test REGEXP t
• - dopasowanie konca ciagu
• SELECT test REGEXP t
• . dopasowanie dowolnego znaku
• a - dopasowanie 0 lub wielu wystapien a
• a - dopasowanie 1 lub wielu wystapien a
• a? dopasowanie 0 lub 1 wystapien a
• ab dopasowanie do a lub do b
• a-zA-Z dopasowanie do dowolnej litery
• 0-9 brak mozliwosci wystapienia cyfry

23
Wyrazenia regularne - przyklady

• SELECT 'test_at_com.com' REGEXP
  'a-z_at_a-z\.a-z'
• SELECT 123506 REGEXP 0-9
• SELECT asder12 REGEXP a-z0-9