Zajęcia -Tomasz Sadowski

Lekcja: Zaawansowane Użycie Widoków w MySQL

Cel lekcji:

Pogłębienie wiedzy o widokach w MySQL, ich zaawansowanych zastosowaniach oraz omówienie dodatkowych możliwości i ograniczeń związanych z ich użyciem.

Zaawansowane tematy związane z widokami

1. Widoki z kolumnami wyliczanymi

Widoki mogą zawierać kolumny obliczane na podstawie istniejących danych w tabelach.

Przykład: Załóżmy, że mamy tabelę produkty:

CREATE TABLE produkty (
    id INT PRIMARY KEY,
    nazwa VARCHAR(50),
    cena DECIMAL(10,2),
    ilosc INT
);

Tworzymy widok pokazujący całkowitą wartość produktów (cena × ilość):

CREATE VIEW wartosc_magazynu AS
SELECT nazwa, cena, ilosc, cena * ilosc AS wartosc
FROM produkty;

Zapytanie korzystające z widoku:

SELECT * FROM wartosc_magazynu WHERE wartosc > 5000;

2. Widoki z aliasami i formatowaniem danych

Widoki mogą używać aliasów dla kolumn oraz funkcji do formatowania danych.

Przykład: Formatowanie cen jako waluty:

CREATE VIEW produkty_waluta AS
SELECT nazwa, CONCAT('PLN ', FORMAT(cena, 2)) AS cena_waluta
FROM produkty;

3. Aktualizowalne widoki

Niektóre widoki mogą być aktualizowalne, co oznacza, że można w nich zmieniać dane, a zmiany będą odzwierciedlane w tabelach bazowych.

Warunki aktualizowalnych widoków:

Widok musi odnosić się do jednej tabeli.
Widok nie może zawierać funkcji agregujących, DISTINCT, GROUP BY, HAVING, podzapytania w liście kolumn, ani operatorów UNION lub JOIN.

Przykład: Widok dla tabeli produkty:

CREATE VIEW produkty_zmienne AS
SELECT id, nazwa, cena
FROM produkty;

Możemy aktualizować dane za pomocą widoku:

UPDATE produkty_zmienne
SET cena = 25.99
WHERE id = 1;

4. Widoki z JOIN

Widoki mogą łączyć dane z wielu tabel.

Przykład: Załóżmy, że mamy dwie tabele:

klienci
zamowienia

Tworzymy widok łączący dane klientów z ich zamówieniami:

CREATE VIEW klienci_zamowienia AS
SELECT klienci.imie, klienci.nazwisko, zamowienia.kwota, zamowienia.status
FROM klienci
JOIN zamowienia ON klienci.id = zamowienia.klient_id;

Zapytanie korzystające z widoku:

SELECT * FROM klienci_zamowienia WHERE status = 'Zatwierdzone';

5. Widoki tylko do odczytu

Widoki złożone, które zawierają funkcje agregujące, grupowania, podzapytania czy JOIN, stają się widokami tylko do odczytu.

Przykład: Widok obliczający łączną wartość zamówień:

CREATE VIEW laczna_wartosc AS
SELECT klient_id, SUM(kwota) AS suma_zamowien
FROM zamowienia
GROUP BY klient_id;

Próba aktualizacji tego widoku zwróci błąd:

UPDATE laczna_wartosc
SET suma_zamowien = 10000; -- BŁĄD

Zarządzanie widokami

1. Tworzenie lub aktualizowanie widoku

Jeśli widok już istnieje i chcemy go zmienić, używamy polecenia CREATE OR REPLACE VIEW:

CREATE OR REPLACE VIEW nazwa_widoku AS
SELECT ...

Przykład: Zmiana widoku produkty_zmienne, aby uwzględnić kolumnę ilosc:

CREATE OR REPLACE VIEW produkty_zmienne AS
SELECT id, nazwa, cena, ilosc
FROM produkty;

Zaawansowane ograniczenia widoków

Widoki i wydajność:
- Widoki nie przechowują wyników zapytań. Za każdym razem, gdy są używane, zapytanie wewnętrzne jest wykonywane ponownie, co może obciążyć bazę danych.
Brak indeksów na widokach:
- Widoki same w sobie nie mogą mieć indeksów, ponieważ są wirtualne. Indeksy działają tylko na tabelach bazowych.
Brak tabel tymczasowych w widokach:
- Widoki nie mogą odwoływać się do tabel tymczasowych.
Widoki i ograniczenia złożoności:
- Niektóre bazy danych nakładają ograniczenia na złożoność zapytań wewnętrznych widoków (np. liczba podzapytań, głębokość JOIN).

Zadania praktyczne

Widok z obliczeniami: Stwórz widok ceny_brutto na podstawie tabeli produkty, który pokaże nazwę produktu oraz jego cenę brutto (przy stawce VAT 23%).
Widok z aliasami: Stwórz widok klienci_lista na podstawie tabeli klienci, w którym kolumny będą miały aliasy: imię jako Imię, nazwisko jako Nazwisko.
Widok z JOIN: Stwórz widok zamowienia_klienci, łączący dane z tabel zamowienia i klienci, który pokaże imię, nazwisko klienta i kwotę zamówienia.
Widok tylko do odczytu: Stwórz widok raport_zamowien, który zliczy liczbę zamówień każdego klienta, wyświetlając klient_id i liczbę zamówień.
Aktualizacja widoku: Stwórz widok produkty_podstawowe dla tabeli produkty, który pokaże id, nazwa, cena. Zmień cenę dowolnego produktu za pomocą widoku.

Poniżej znajduje się zestaw poleceń SQL potrzebnych do utworzenia tabel oraz wstawienia przykładowych danych, które będą używane w ćwiczeniach z widokami.

Tworzenie tabel

-- Tabela: produkty
CREATE TABLE produkty (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nazwa VARCHAR(50) NOT NULL,
    cena DECIMAL(10, 2) NOT NULL,
    ilosc INT NOT NULL
);

-- Tabela: klienci
CREATE TABLE klienci (
    id INT AUTO_INCREMENT PRIMARY KEY,
    imie VARCHAR(50) NOT NULL,
    nazwisko VARCHAR(50) NOT NULL
);

-- Tabela: zamowienia
CREATE TABLE zamowienia (
    id INT AUTO_INCREMENT PRIMARY KEY,
    klient_id INT NOT NULL,
    kwota DECIMAL(10, 2) NOT NULL,
    status VARCHAR(20) NOT NULL,
    FOREIGN KEY (klient_id) REFERENCES klienci(id)
);

Wstawianie danych

-- Dane do tabeli: produkty
INSERT INTO produkty (nazwa, cena, ilosc) VALUES
('Laptop', 3000.00, 10),
('Monitor', 800.00, 20),
('Klawiatura', 150.00, 50),
('Myszka', 100.00, 30),
('Drukarka', 1200.00, 5);

-- Dane do tabeli: klienci
INSERT INTO klienci (imie, nazwisko) VALUES
('Jan', 'Kowalski'),
('Anna', 'Nowak'),
('Piotr', 'Wiśniewski'),
('Katarzyna', 'Zielinska');

-- Dane do tabeli: zamowienia
INSERT INTO zamowienia (klient_id, kwota, status) VALUES
(1, 3500.00, 'Zatwierdzone'),
(2, 1200.00, 'Oczekujące'),
(3, 500.00, 'Zatwierdzone'),
(1, 800.00, 'Anulowane'),
(4, 3000.00, 'Zatwierdzone');