Transcript Document

SQL – Structured Query
Language (3)
Wykład 7
Prowadzący: dr Paweł Drozda
Plan wykładu




Podzapytania
Perspektywy
Wybrane funkcje MySQL
Wyrażenia regularne
dr Paweł Drozda
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Wyrażenia regularne - przykłady
SELECT '[email protected]' REGEXP '[a-z]@[a-z]\.[a-z]';
SELECT ‘123506’ REGEXP ’[0-9]’;
SELECT ‘asder12’ REGEXP ‘[a-z][0-9]’
dr Paweł Drozda