Изгледи (views) за сложни заявки

В седмица 4 написахте справка за среден успех по ученик: съединение на три таблици, групиране, закръгляне. Работи. Следващата седмица трябва същото, но само за 11А. После — само отличниците. После — броят на отличниците по клас. Всеки път копирате същите шест реда и променяте по нещо; при четвъртото копие някой ще забрави JOIN-а с classes и справката тихо ще излъже.

SQL има решение за това от самото си начало: заявката се записва в базата под име и оттам нататък се ползва като таблица.

CREATE VIEW v_uspeh AS
SELECT s.id AS student_id, s.name, c.name AS klas,
       ROUND(AVG(m.mark), 2) AS sreden
FROM students s
JOIN classes c ON c.id = s.class_id
JOIN marks m ON m.student_id = s.id
GROUP BY s.id;

-- Оттук нататък сложната заявка е една дума:
SELECT name, klas, sreden FROM v_uspeh WHERE klas = '11А' ORDER BY sreden DESC;

• Всяка колона на изгледа трябва да има име. За изрази и агрегати това значи задължително AS — MySQL отказва изглед с две колони с еднакво име или с колона без име.
• Изгледът може да е върху всичко, което SELECT може: JOIN, GROUP BY, HAVING, подзаявки, дори друг изглед.
• ORDER BY вътре в изгледа е безсмислен — редът се задава при SELECT от изгледа. Слагайте го отвън.

Смисълът на изгледа се вижда, когато върху него започнете да строите. Всичко, което умеете от седмици 2–4, работи и тук:

-- филтър и сортиране върху изгледа
SELECT name, sreden FROM v_uspeh WHERE sreden >= 5.50 ORDER BY sreden DESC;

-- групиране върху изгледа (изгледът вече е групирал по ученик - това е второ ниво)
SELECT klas, COUNT(*) AS broy, ROUND(AVG(sreden), 2) AS sreden_na_klasa
FROM v_uspeh GROUP BY klas;

-- JOIN между изглед и таблица
SELECT v.name, v.sreden, s.grade AS godishen
FROM v_uspeh v JOIN students s ON s.id = v.student_id
WHERE v.sreden < s.grade;

-- изглед върху изглед
CREATE VIEW v_otlichnici AS
SELECT name, klas, sreden FROM v_uspeh WHERE sreden >= 5.50;

Щом изгледът се държи като таблица, може ли да се пише в него? Зависи дали СУБД може еднозначно да реши кой ред на коя таблица да промени.

DROP VIEW v_otlichnici;                 -- изтрива изгледа, данните остават
DROP VIEW IF EXISTS v_uspeh;            -- без грешка, ако го няма

CREATE OR REPLACE VIEW v_uspeh AS ...;  -- MySQL: пренаписване на място
SHOW CREATE VIEW v_uspeh;               -- MySQL: показва запазената заявка

Има и трета причина за изгледи освен удобството и актуалността: достъп. Таблицата staff съдържа ЕГН и заплата. Библиотекарят трябва да вижда кой на каква длъжност е — и нищо повече. Вместо да му давате право върху таблицата (цялата), правите изглед с двете безобидни колони и давате право само върху изгледа.

CREATE VIEW staff_public AS
SELECT name, position FROM staff;

-- следващия урок, в MySQL:
GRANT SELECT ON school.staff_public TO 'bibliotekar'@'localhost';
-- потребителят вижда изгледа, но SELECT * FROM staff му е отказан

Същото важи за редове: изглед с WHERE class_id = 1 показва на класния на 11А само неговите ученици. Изгледът е прозорец към базата, а правата решават кой през кой прозорец гледа. Продължаваме с това в урок 8.

• Изглед = SELECT с име; държи се като таблица, но не пази данни — изпълнява се при всяко обръщение.
• Ползва се като източник: SELECT с WHERE и ORDER BY, GROUP BY върху него, JOIN с таблици, изглед върху изглед.
• Всяка колона на изгледа има име — изразите и агрегатите винаги с AS; ORDER BY се слага отвън.
• Изгледът спестява писане и пази логиката на едно място, но не ускорява — за това са индексите.
• Обновяем е само прост изглед върху една таблица; изглед с JOIN/GROUP BY е само за четене.
• DROP VIEW маха изгледа, не данните; през изглед се дават права само върху част от таблицата.