CREATE, UPDATE, ALTER, VIEW – tworzenie i modyfikacja widoków – Kurs języka SQL

Widok/perspektywa (VIEW) – jest pewnym rodzajem tabeli, którą tworzymy na podstawie już istniejących tabel, danych, warunków wykorzystując zapytanie typu “SELECT”. Widoki tworzone są po to, aby ograniczyć dostęp do innych danych z istniejących już tabel użytkownikom nieuprawnionym. Możemy także ich użyć w przypadku, kiedy korzystamy często z konkretnych zapytań SQL wraz z warunkami. Tworząc widoki często przyczyniamy się do zwiększenia wydajności naszych zapytań w skryptach czy też aplikacjach.

Poniżej przedstawię w jaki sposób możemy tworzyć, modyfikować, czy też usuwać widoki (perspektywy) w języku SQL.

Zanim jednak przejdziemy do widoków, przygotujmy sobie tabele na których będziemy “ćwiczyć”. W naszym przykładzie będziemy mieli dwie tabele: pracownik oraz wynagrodzenie.
Celem naszego ćwiczenia będzie utworzenie widoku opierającego się na obu tabelach i zwracającego dane imie, nazwisko, miesiac, rok, pensje wybranego pracownika.

CREATE TABLE pracownik
(
id_pracownika INT(5) NOT NULL,
imie CHAR(20) NOT NULL,
nazwisko CHAR(50) NOT NULL,
data_dodania DATETIME,
data_mod DATETIME,
PRIMARY KEY (id_pracownika)
);

CREATE TABLE wynagrodzenie
(
id_pracownika INT(5) NOT NULL,
miesiac INT(2) NOT NULL,
rok INT(4) NOT NULL,
pensja FLOAT(6,2) NOT NULL
);

INSERT INTO pracownik (id_pracownika, imie, nazwisko, data_dodania, data_mod) VALUES (1, 'Jan', 'Nowak', '2015-02-02 08:00:00', '2016-03-06 09:00:00');
INSERT INTO pracownik (id_pracownika, imie, nazwisko, data_dodania, data_mod) VALUES (2, 'Adam', 'Kowalski', '2015-05-02 08:00:00', '2016-02-02 08:00:00');
INSERT INTO wynagrodzenie (id_pracownika, miesiac, rok, pensja) VALUES (1, 1, 2016, 1000.0);
INSERT INTO wynagrodzenie (id_pracownika, miesiac, rok, pensja) VALUES (1, 2, 2016, 1100.0);
INSERT INTO wynagrodzenie (id_pracownika, miesiac, rok, pensja) VALUES (1, 3, 2016, 1100.0);
INSERT INTO wynagrodzenie (id_pracownika, miesiac, rok, pensja) VALUES (2, 1, 2016, 50.0);
INSERT INTO wynagrodzenie (id_pracownika, miesiac, rok, pensja) VALUES (2, 2, 2016, 75.0);
INSERT INTO wynagrodzenie (id_pracownika, miesiac, rok, pensja) VALUES (2, 3, 2016, 80.0);

Tworzenia widoku

Widok tworzymy poleceniem:

CREATE VIEW nazwa_widoku AS
SELECT kolumna1, kolumna2, kolumna2 FROM tabela WHERE kolumna1='wartość';

W naszym przykładzie utworzymy widok o nazwie v_pensja, który zwróci nam imię, nazwisko oraz wynagrodzenie pracownika w miesiącu 3 roku 2016. Polecenie zatem wyglądać będzie następująco:

CREATE VIEW v_pensja AS
SELECT p.imie, p.nazwisko, w.pensja FROM wynagrodzenie w
LEFT JOIN pracownik p on p.id_pracownika=w.id_pracownika
WHERE w.miesiac='3' and w.rok='2016';

Odczytanie zawartości widoku pensja odbywa się tak samo jak w przypadku tabeli. Zatem polecenie:

SELECT * FROM v_pensja;

zwróci nam poniższą zawartość:

imie	nazwisko	pensja
Jan	Nowak	1100.0
Adam	Kowalski	80.0

Modyfikacja widoku

Mamy utworzyony widok, czas go teraz zmodyfikować. Zmiany widoku (perspektywy) możemy wykonać na dwa sposoby.

Pierwszym z nich jest opcja REPLACE. Zmodyfikujmy zatem nasz widok, tak aby zwracał on jeszcze numer miesiąca dla którego pokazywana jest pensja oraz z warunku WHERE usuniemy ograniczenie miesiąca.
Składa SQL wygląda zatem następująco:

CREATE or replace VIEW v_pensja AS
SELECT p.imie, p.nazwisko, w.miesiac, w.pensja FROM wynagrodzenie w
LEFT JOIN pracownik p ON p.id_pracownika=w.id_pracownika
WHERE w.rok='2016';

a “odczytanie” widoku:

SELECT * FROM v_pensja;

zwróci nam wynik:

imie	nazwisko	miesiac	pensja
Jan	Nowak	1	1000.0
Jan	Nowak	2	1100.0
Jan	Nowak	3	1100.0
Adam	Kowalski	1	50.0
Adam	Kowalski	2	75.0
Adam	Kowalski	3	80.0

Drugą opcją zmiany widoku jest opcja ALTER. Jej składnia wygląda następująco:

alter VIEW v_pensja AS
SELECT p.imie, p.nazwisko, w.miesiac, w.pensja FROM wynagrodzenie w
LEFT JOIN pracownik p on p.id_pracownika=w.id_pracownika
WHERE w.rok='2016'

Jej działanie i wynik jest analogiczny jak w przypadku opcji REPLACE.

Na widokach możemy dokonywać operacji modyfikacji danych.

Modyfikacja danych w widoku

Naszym zadaniem będzie teraz modyfikacja zawartości widoku pensja i pola pensja z widoku – zwiększenie o 25% w miesiącu marcu. Konstrukcja zapytania jest następująca:

UPDATE nazwa_widoku SET pole_widoku='wartosc' WHERE pole_widoku='wartosc';

Zatem dla naszego widoku pensja SQL wygląda następująco:

UPDATE v_pensja SET pensja=pensja*1.25 WHERE miesiac='3';

Nasz widok zawiera teraz zmodyfikowane dane:

imie	nazwisko	miesiac	pensja
Jan	Nowak	1	1000.0
Jan	Nowak	2	1100.0
Jan	Nowak	3	1375.0
Adam	Kowalski	1	50.0
Adam	Kowalski	2	75.0
Adam	Kowalski	3	100.0

Należy pamiętać, modyfikując dane w widoku modyfikujemy dane w tabelach źródłowych.

Usunięcie widoku

Aby skasować widok należy użyć komendy:

DROP VIEW nazwa_widoku;

W naszym przypadku będzie to zatem:

DROP VIEW v_pensja;

Dobrą praktyką przy tworzeniu widoków jest odpowiednie nazewnictwo – dobrze jest poprzedzać nazwę widoku jakimś wyróżnikiem np: “v_”, “wiev_” itp. tak aby odróżnić widok od tabeli w bazie danych.