Pt91 - Pràctica de creació, manipulació i eliminació de vistes

Guillem Solà i Boeck (host A10BPC05)

Crear una vista pels paisos de Europe

MariaDB [world]> CREATE VIEW vPractica91 AS SELECT * FROM Country WHERE Continent="Europe" WITH CHECK OPTION;
Query OK, 0 rows affected (0.02 sec)

MariaDB [world]> SELECT * FROM vPractica91;
+------+-------------------------------+-----------+------------------+-------------+-----------+------------+----------------+------------+------------+--------------------------------+-------------------------------------+--------------------------+---------+-------+
| Code | Name                          | Continent | Region           | SurfaceArea | IndepYear | Population | LifeExpectancy | GNP        | GNPOld     | LocalName                      | GovernmentForm                      | HeadOfState              | Capital | Code2 |
+------+-------------------------------+-----------+------------------+-------------+-----------+------------+----------------+------------+------------+--------------------------------+-------------------------------------+--------------------------+---------+-------+
| ALB  | Albania                       | Europe    | Southern Europe  |    28748.00 |      1912 |    3401200 |           71.6 |    3205.00 |    2500.00 | Shqipëria                      | Republic                            | Rexhep Mejdani           |      34 | AL    |
+------+-------------------------------+-----------+------------------+-------------+-----------+------------+----------------+------------+------------+--------------------------------+-------------------------------------+--------------------------+---------+-------+
46 rows in set (0.00 sec)

Introduir un país en aquesta vista. Per comprovar que està introduït a Country

Afegeixo un registre amb uns quants valors
MariaDB [world]> INSERT INTO vPractica91 (Code, Name, Continent, Region, Population) VALUES ("GS", "Guillemland", "Europe", "Wektabyte", 1);
Query OK, 1 row affected (0.04 sec)

Verifico que s'ha introduït

MariaDB [world]> SELECT * FROM Country WHERE Name = "Guillemland";
+------+-------------+------------+-----------+-------------+-----------+------------+----------------+------+--------+-----------+----------------+-------------+---------+-------+
| Code | Name        | Continent  | Region    | SurfaceArea | IndepYear | Population | LifeExpectancy | GNP  | GNPOld | LocalName | GovernmentForm | HeadOfState | Capital | Code2 |
+------+-------------+------------+-----------+-------------+-----------+------------+----------------+------+--------+-----------+----------------+-------------+---------+-------+
| GS   | Guillemland | Europe | Wektabyte |        0.00 |      NULL |          1 |           NULL | NULL |   NULL |           |                | NULL        |    NULL |       |
+------+-------------+------------+-----------+-------------+-----------+------------+----------------+------+--------+-----------+----------------+-------------+---------+-------+
1 row in set (0.00 sec)

Eliminar aquest país des de la taula Country

Elimino el registre anterior

MariaDB [world]> DELETE FROM Country WHERE Name = "Guillemland";
Query OK, 1 row affected (0.06 sec)

Verifico que s'ha eliminat

MariaDB [world]> SELECT * FROM Country WHERE Name = "Guillemland";
Empty set (0.00 sec)

Canviar la vista, per a que siguin els paisos de South America

Executo l'ordre de CREATE VIEW amb REPLACE perquè es modifiqui la vista creada anteriorment i també modifico el SELECT perquè correspongui amb l'enunciat:

MariaDB [world]> CREATE OR REPLACE VIEW vPractica91 AS SELECT * FROM Country WHERE Continent="South America" WITH CHECK OPTION;
Query OK, 0 rows affected (0.04 sec)

Verifico que la vista sigui correcta (mostri els països de South America:

MariaDB [world]> SELECT * FROM vPractica91;
+------+------------------+---------------+---------------+-------------+-----------+------------+----------------+-----------+-----------+-------------------+-------------------------------+------------------------------+---------+-------+
| Code | Name             | Continent     | Region        | SurfaceArea | IndepYear | Population | LifeExpectancy | GNP       | GNPOld    | LocalName         | GovernmentForm                | HeadOfState                  | Capital | Code2 |
+------+------------------+---------------+---------------+-------------+-----------+------------+----------------+-----------+-----------+-------------------+-------------------------------+------------------------------+---------+-------+
| ARG  | Argentina        | South America | South America |  2780400.00 |      1816 |   37032000 |           75.1 | 340238.00 | 323310.00 | Argentina         | Federal Republic              | Fernando de la Rúa           |      69 | AR    |
+------+------------------+---------------+---------------+-------------+-----------+------------+----------------+-----------+-----------+-------------------+-------------------------------+------------------------------+---------+-------+
14 rows in set (0.00 sec)