DDL - Creació de Vistes i altres Objectes
-
La vista és una consulta emmagatzemada, una consulta
SELECT, que s’executa cada vegada que es treballa sobre la vista. -
Una vista, igual que una taula, és un Objecte de la base de dades.
-
Una vista no ocupa més espai que la seva definició.
-
És una representació lògica de les taules existents o d’altres vistes.
-
És una altra forma d’accedir a les dades sense necessitat de conèixer-ne l’estructura real.
-
Les taules en què es basa una vista s’anomenen taules base.
CREATE VIEW VIEW_EMPLOYEES AS
SELECT EMPLOYEE_ID AS EMP_ID, FIRST_NAME,
LAST_NAME, EMAIL
FROM EMPLOYEES
WHERE EMPLOYEE_ID BETWEEN 100 AND 110;
SELECT * FROM VIEW_EMPLOYEES;
Ús de les vistes
Amb les vistes podem restringir l’accés a les taules ja una vista pot mostrar unes columnes selectives i fins i tot algunes files.
-
Les podem utilitzar per reduir la complexitat d’altres consultes basades en
SELECTs complexes. -
Podem crear vistes amb
JOINSper a presentar millor les dades, -
A l’usuari que consulta vistes se li simplifiquen les consultes.
Creació de vistes
Per a crear una vista utilitzem la comanda CREATE VIEW.
CREATE [OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = { user | CURRENT_USER | role | CURRENT_ROLE }]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW [IF NOT EXISTS] view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION];
CREATE OR REPLACE VIEW
VIEW_EMPLOYEES(EMP_ID, NOM, COGNOM, EMAIL) AS
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL
FROM EMPLOYEES
WHERE EMPLOYEE_ID BETWEEN 100 AND 110;
La sintaxis del CREATE VIEW té força opcions però d’entrada podem quedar-nos en només allò més bàsic i que hem utilitzat a l’exemple.
Les altres opcions més interessants són:
-
OR REPLACE: ens dóna la opció de no haver d’eliminar la Vista ja creada si volem redefinir-la. S’aconsella a afegir-ho sempre que volguem guardar la sentència per executar-la en qualsevol moment. -
SQL SECURITY: permet definir amb quins privilegis s’executarà la Vista quan la utilitzem. Pot ser que tinguem usuaris que no vulguem que accedeixin directament a la taula però si a la vista; en aquest cas la vista cal que s’executi amb els permisos d’un usuari que sí tingui accés a la taula. -
WITH CHECK OPTION: permet controlar les modificacions d’una vista no deixant que es faci cap canvi a les files,UPDATE, que provoqui que alguna fila ja no compleixi les condicions per aparèixer a la vista.
Exemples
Per aquests exemples, cal tenir creades unes taules, WF_, que trobareu a l'apartat d'Altres.
CREATE OR REPLACE VIEW VIEW_EURO_COUNTRIES AS
SELECT country_id, region_id, country_name, capitol
FROM wf_countries
WHERE location = 'Europe';
SELECT * FROM view_euro_countries
ORDER BY country_name;
Resposta a la consulta anterior
+------------+-----------+----------------------------+-------------------+
| country_id | region_id | country_name | capitol |
+------------+-----------+----------------------------+-------------------+
| 31 | 2 | BOSNIA AND HERZEGOVINA | Sarajevo |
| 65 | 2 | CZECH REPUBLIC | Prague |
| 83 | 2 | FRENCH | Paris |
| 91 | 2 | GERMANY | Berlin |
| 120 | 2 | ITALIAN REPUBLIC | Rome |
| 25 | 2 | KINGDOM OF BELGIUM | Brussels |
| 66 | 2 | KINGDOM OF DENMARK | Copenhagen |
| 183 | 2 | KINGDOM OF NORWAY | Oslo |
| 173 | 2 | KINGDOM_OF_THE_NETHERLANDS | Amsterdam |
| 197 | 2 | PORTUGUESE REPUBLIC | Lisbon |
| 5 | 2 | PRINCIPALITY OF ANDORRA | Andorra la Vella |
| 2 | 2 | REPUBLIC OF ALBANIA | Tirana |
| 16 | 2 | REPUBLIC OF AUSTRIA | Vienna |
| 24 | 2 | REPUBLIC OF BELARUS | Minsk |
| 62 | 2 | REPUBLIC OF CROATIA | Zagreb |
| 112 | 2 | REPUBLIC OF ICELAND | Reykjavik |
| 144 | 2 | REPUBLIC OF LITHUANIA | Vilnius |
| 196 | 2 | REPUBLIC OF POLAND | Warsaw |
| 200 | 2 | UKRAINE LOCAL | Kyiv |
| 202 | 2 | UNITED KINGDOM | London |
+------------+-----------+----------------------------+-------------------+
Normes en la creació de Vistes
-
La subconsulta d’una vista no hauria de contenir cap sintaxis complexe.
-
La subconsulta no ha de tenir un
ORDER BY, aquesta clàusula ja la posarem quan executem laSELECTcontra la vista. -
Si volem modificar una vista existent utilitzarem la clàusula
OR REPLACE. -
Podem definir Alies per les columnes que ens interessi.
Vistes Simples i Compostes
Diferenciem entre vistes simples i vistes complexes.
| Funció | Vistes Simples | Vistes Complexes |
|---|---|---|
| Nombre de taules utilitzades | Una | Una o més |
| Pot contenir funcions? | No | Sí |
| Pot contenir grups de dades? | No | Sí |
Pot realitzar operacions DML (INSERT, UPDATE, DELETE)? |
Sí | No sempre |
Vistes simples
La subconsulta en una vista simple només agafa dades d’una sola taula sense utilitzar cap funció d’unió ni de grup.
CREATE OR REPLACE VIEW VIEW_EURO_COUNTRIES AS
SELECT country_id, country_name, capitol
FROM wf_countries
WHERE location = 'Europe';
En aquesta vista podem fer INSERT, UPDATE i DELETE.
Modificació i eliminació de vistes
Per modificar una vista utilitzem la comanda CREATE OR REPLACE VIEW.
Per eliminar una vista ja creada utilitzem la comanda DROP VIEW.
CREATE OR REPLACE VIEW view_euro_countries AS .........
DROP VIEW <nom_vista>;
DROP VIEW [IF EXISTS]
<nom_vista1> [, <nom_vista2>] ...
[RESTRICT | CASCADE];
Les clàusules RESTRICT i CASCADE no s’utilitzen i per tant s’ignoren.
Vistes complexes
Les vistes complexes poden contenir funcions de grup i JOINS.
CREATE OR REPLACE VIEW
view_euro_countries(`ID`,`Country`, `Capitol City`, `Region`)
AS
SELECT c.country_id, c.country_name,
c.capitol, r.region_name
FROM wf_countries c JOIN wf_world_regions r USING (region_id)
WHERE location = 'Europe';
SELECT * FROM view_euro_countries;
Sortida de l'execució de la SELECT sobre la vista.
+-----+----------------------------+-------------------+--------+
| ID | Country | Capitol City | Region |
+-----+----------------------------+-------------------+--------+
| 2 | REPUBLIC OF ALBANIA | Tirana | EUROPE |
| 5 | PRINCIPALITY OF ANDORRA | Andorra la Vella | EUROPE |
| 16 | REPUBLIC OF AUSTRIA | Vienna | EUROPE |
| 24 | REPUBLIC OF BELARUS | Minsk | EUROPE |
| 25 | KINGDOM OF BELGIUM | Brussels | EUROPE |
| 31 | BOSNIA AND HERZEGOVINA | Sarajevo | EUROPE |
| 62 | REPUBLIC OF CROATIA | Zagreb | EUROPE |
| 65 | CZECH REPUBLIC | Prague | EUROPE |
| 66 | KINGDOM OF DENMARK | Copenhagen | EUROPE |
| 83 | FRENCH | Paris | EUROPE |
| 91 | GERMANY | Berlin | EUROPE |
| 112 | REPUBLIC OF ICELAND | Reykjavik | EUROPE |
| 120 | ITALIAN REPUBLIC | Rome | EUROPE |
| 144 | REPUBLIC OF LITHUANIA | Vilnius | EUROPE |
| 173 | KINGDOM_OF_THE_NETHERLANDS | Amsterdam | EUROPE |
| 183 | KINGDOM OF NORWAY | Oslo | EUROPE |
| 196 | REPUBLIC OF POLAND | Warsaw | EUROPE |
| 197 | PORTUGUESE REPUBLIC | Lisbon | EUROPE |
| 200 | UKRAINE LOCAL | Kyiv | EUROPE |
| 202 | UNITED KINGDOM | London | EUROPE |
+-----+----------------------------+-------------------+--------+
També podem afegir funcions de grup a les vistes complexes.
CREATE OR REPLACE VIEW
view_high_pop (`Region ID`, `Highest population`) AS
SELECT region_id, MAX(population)
FROM wf_countries
GROUP BY region_id;
SELECT * FROM view_high_pop;
Sortida de l'execució de la SELECT sobre la vista.
+-----------+--------------------+
| Region ID | Highest population |
+-----------+--------------------+
| 1 | 91,077,287 |
| 2 | 9,724,723 |
| 3 | 9,885,661 |
| 4 | 57,663 |
| 7 | 7,483,763 |
| 10 | 9,119,152 |
+-----------+--------------------+
O barrejar JOINS i funcions de grup.
CREATE OR REPLACE VIEW
view_high_pop (`Region`, `Highest population`) AS
SELECT region_name, MAX(population)
FROM wf_countries c
JOIN wf_world_regions r USING (region_id)
GROUP BY region_id, region_name;
SELECT * FROM view_high_pop;
Sortida de l'execució de la SELECT sobre la vista.
+---------------+--------------------+
| Region | Highest population |
+---------------+--------------------+
| ASIA | 91,077,287 |
| EUROPE | 9,724,723 |
| AFRICA | 9,885,661 |
| OCEANIA | 57,663 |
| NORTH AMERICA | 7,483,763 |
| SOUTH AMERICA | 9,119,152 |
+---------------+--------------------+