Pt91 - Pràctica de creació, manipulació i eliminació de vistes
< ASIX/M02/UF2
Salta a la navegació
Salta a la cerca
La revisió el 17:42, 18 gen 2018 per Guillem (discussió | contribucions)
Contingut
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 | | AND | Andorra | Europe | Southern Europe | 468.00 | 1278 | 78000 | 83.5 | 1630.00 | NULL | Andorra | Parliamentary Coprincipality | | 55 | AD | | AUT | Austria | Europe | Western Europe | 83859.00 | 1918 | 8091800 | 77.7 | 211860.00 | 206025.00 | Österreich | Federal Republic | Thomas Klestil | 1523 | AT | | BEL | Belgium | Europe | Western Europe | 30518.00 | 1830 | 10239000 | 77.8 | 249704.00 | 243948.00 | België/Belgique | Constitutional Monarchy, Federation | Albert II | 179 | BE | | BGR | Bulgaria | Europe | Eastern Europe | 110994.00 | 1908 | 8190900 | 70.9 | 12178.00 | 10169.00 | Balgarija | Republic | Petar Stojanov | 539 | BG | |---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | VAT | Holy See (Vatican City State) | Europe | Southern Europe | 0.40 | 1929 | 1000 | NULL | 9.00 | NULL | Santa Sede/Città del Vaticano | Independent Church State | Johannes Paavali II | 3538 | VA | | YUG | Yugoslavia | Europe | Southern Europe | 102173.00 | 1918 | 10640000 | 72.4 | 17000.00 | NULL | Jugoslavija | Federal Republic | Vojislav Koštunica | 1792 | YU | +------+-------------------------------+-----------+------------------+-------------+-----------+------------+----------------+------------+------------+--------------------------------+-------------------------------------+--------------------------+---------+-------+ 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"; 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 | | BOL | Bolivia | South America | South America | 1098581.00 | 1825 | 8329000 | 63.7 | 8571.00 | 7967.00 | Bolivia | Republic | Hugo Bánzer Suárez | 194 | BO | | BRA | Brazil | South America | South America | 8547403.00 | 1822 | 170115000 | 62.9 | 776739.00 | 804108.00 | Brasil | Federal Republic | Fernando Henrique Cardoso | 211 | BR | | CHL | Chile | South America | South America | 756626.00 | 1810 | 15211000 | 75.7 | 72949.00 | 75780.00 | Chile | Republic | Ricardo Lagos Escobar | 554 | CL | |----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | SUR | Suriname | South America | South America | 163265.00 | 1975 | 417000 | 71.4 | 870.00 | 706.00 | Suriname | Republic | Ronald Venetiaan | 3243 | SR | | URY | Uruguay | South America | South America | 175016.00 | 1828 | 3337000 | 75.2 | 20831.00 | 19967.00 | Uruguay | Republic | Jorge Batlle Ibáñez | 3492 | UY | | VEN | Venezuela | South America | South America | 912050.00 | 1811 | 24170000 | 73.1 | 95023.00 | 88434.00 | Venezuela | Federal Republic | Hugo Chávez Frías | 3539 | VE | +------+------------------+---------------+---------------+-------------+-----------+------------+----------------+-----------+-----------+-------------------+-------------------------------+------------------------------+---------+-------+ 14 rows in set (0.00 sec)
