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:38, 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 | | BIH | Bosnia and Herzegovina | Europe | Southern Europe | 51197.00 | 1992 | 3972000 | 71.5 | 2841.00 | NULL | Bosna i Hercegovina | Federal Republic | Ante Jelavic | 201 | BA | | BLR | Belarus | Europe | Eastern Europe | 207600.00 | 1991 | 10236000 | 68.0 | 13714.00 | NULL | Belarus | Republic | Aljaksandr Lukašenka | 3520 | BY | | CHE | Switzerland | Europe | Western Europe | 41284.00 | 1499 | 7160400 | 79.6 | 264478.00 | 256092.00 | Schweiz/Suisse/Svizzera/Svizra | Federation | Adolf Ogi | 3248 | CH | | CZE | Czech Republic | Europe | Eastern Europe | 78866.00 | 1993 | 10278100 | 74.5 | 55017.00 | 52037.00 | ¸esko | Republic | Václav Havel | 3339 | CZ | | DEU | Germany | Europe | Western Europe | 357022.00 | 1955 | 82164700 | 77.4 | 2133367.00 | 2102826.00 | Deutschland | Federal Republic | Johannes Rau | 3068 | DE | | DNK | Denmark | Europe | Nordic Countries | 43094.00 | 800 | 5330000 | 76.5 | 174099.00 | 169264.00 | Danmark | Constitutional Monarchy | Margrethe II | 3315 | DK | | ESP | Spain | Europe | Southern Europe | 505992.00 | 1492 | 39441700 | 78.8 | 553233.00 | 532031.00 | España | Constitutional Monarchy | Juan Carlos I | 653 | ES | | EST | Estonia | Europe | Baltic Countries | 45227.00 | 1991 | 1439200 | 69.5 | 5328.00 | 3371.00 | Eesti | Republic | Lennart Meri | 3791 | EE | | FIN | Finland | Europe | Nordic Countries | 338145.00 | 1917 | 5171300 | 77.4 | 121914.00 | 119833.00 | Suomi | Republic | Tarja Halonen | 3236 | FI | | FRA | France | Europe | Western Europe | 551500.00 | 843 | 59225700 | 78.8 | 1424285.00 | 1392448.00 | France | Republic | Jacques Chirac | 2974 | FR | | FRO | Faroe Islands | Europe | Nordic Countries | 1399.00 | NULL | 43000 | 78.4 | 0.00 | NULL | Føroyar | Part of Denmark | Margrethe II | 901 | FO | | GBR | United Kingdom | Europe | British Islands | 242900.00 | 1066 | 59623400 | 77.7 | 1378330.00 | 1296830.00 | United Kingdom | Constitutional Monarchy | Elisabeth II | 456 | GB | | GIB | Gibraltar | Europe | Southern Europe | 6.00 | NULL | 25000 | 79.0 | 258.00 | NULL | Gibraltar | Dependent Territory of the UK | Elisabeth II | 915 | GI | | GRC | Greece | Europe | Southern Europe | 131626.00 | 1830 | 10545700 | 78.4 | 120724.00 | 119946.00 | Elláda | Republic | Kostis Stefanopoulos | 2401 | GR | | HRV | Croatia | Europe | Southern Europe | 56538.00 | 1991 | 4473000 | 73.7 | 20208.00 | 19300.00 | Hrvatska | Republic | Štipe Mesic | 2409 | HR | | HUN | Hungary | Europe | Eastern Europe | 93030.00 | 1918 | 10043200 | 71.4 | 48267.00 | 45914.00 | Magyarország | Republic | Ferenc Mádl | 3483 | HU | | IRL | Ireland | Europe | British Islands | 70273.00 | 1921 | 3775100 | 76.8 | 75921.00 | 73132.00 | Ireland/Éire | Republic | Mary McAleese | 1447 | IE | | ISL | Iceland | Europe | Nordic Countries | 103000.00 | 1944 | 279000 | 79.4 | 8255.00 | 7474.00 | Ísland | Republic | Ólafur Ragnar Grímsson | 1449 | IS | | ITA | Italy | Europe | Southern Europe | 301316.00 | 1861 | 57680000 | 79.0 | 1161755.00 | 1145372.00 | Italia | Republic | Carlo Azeglio Ciampi | 1464 | IT | | LIE | Liechtenstein | Europe | Western Europe | 160.00 | 1806 | 32300 | 78.8 | 1119.00 | 1084.00 | Liechtenstein | Constitutional Monarchy | Hans-Adam II | 2446 | LI | | LTU | Lithuania | Europe | Baltic Countries | 65301.00 | 1991 | 3698500 | 69.1 | 10692.00 | 9585.00 | Lietuva | Republic | Valdas Adamkus | 2447 | LT | | LUX | Luxembourg | Europe | Western Europe | 2586.00 | 1867 | 435700 | 77.1 | 16321.00 | 15519.00 | Luxembourg/Lëtzebuerg | Constitutional Monarchy | Henri | 2452 | LU | | LVA | Latvia | Europe | Baltic Countries | 64589.00 | 1991 | 2424200 | 68.4 | 6398.00 | 5639.00 | Latvija | Republic | Vaira Vike-Freiberga | 2434 | LV | | MCO | Monaco | Europe | Western Europe | 1.50 | 1861 | 34000 | 78.8 | 776.00 | NULL | Monaco | Constitutional Monarchy | Rainier III | 2695 | MC | | MDA | Moldova | Europe | Eastern Europe | 33851.00 | 1991 | 4380000 | 64.5 | 1579.00 | 1872.00 | Moldova | Republic | Vladimir Voronin | 2690 | MD | | MKD | Macedonia | Europe | Southern Europe | 25713.00 | 1991 | 2024000 | 73.8 | 1694.00 | 1915.00 | Makedonija | Republic | Boris Trajkovski | 2460 | MK | | MLT | Malta | Europe | Southern Europe | 316.00 | 1964 | 380200 | 77.9 | 3512.00 | 3338.00 | Malta | Republic | Guido de Marco | 2484 | MT | | NLD | Netherlands | Europe | Western Europe | 41526.00 | 1581 | 15864000 | 78.3 | 371362.00 | 360478.00 | Nederland | Constitutional Monarchy | Beatrix | 5 | NL | | NOR | Norway | Europe | Nordic Countries | 323877.00 | 1905 | 4478500 | 78.7 | 145895.00 | 153370.00 | Norge | Constitutional Monarchy | Harald V | 2807 | NO | | POL | Poland | Europe | Eastern Europe | 323250.00 | 1918 | 38653600 | 73.2 | 151697.00 | 135636.00 | Polska | Republic | Aleksander Kwasniewski | 2928 | PL | | PRT | Portugal | Europe | Southern Europe | 91982.00 | 1143 | 9997600 | 75.8 | 105954.00 | 102133.00 | Portugal | Republic | Jorge Sampãio | 2914 | PT | | ROM | Romania | Europe | Eastern Europe | 238391.00 | 1878 | 22455500 | 69.9 | 38158.00 | 34843.00 | România | Republic | Ion Iliescu | 3018 | RO | | RUS | Russian Federation | Europe | Eastern Europe | 17075400.00 | 1991 | 146934000 | 67.2 | 276608.00 | 442989.00 | Rossija | Federal Republic | Vladimir Putin | 3580 | RU | | SJM | Svalbard and Jan Mayen | Europe | Nordic Countries | 62422.00 | NULL | 3200 | NULL | 0.00 | NULL | Svalbard og Jan Mayen | Dependent Territory of Norway | Harald V | 938 | SJ | | SMR | San Marino | Europe | Southern Europe | 61.00 | 885 | 27000 | 81.1 | 510.00 | NULL | San Marino | Republic | NULL | 3171 | SM | | SVK | Slovakia | Europe | Eastern Europe | 49012.00 | 1993 | 5398700 | 73.7 | 20594.00 | 19452.00 | Slovensko | Republic | Rudolf Schuster | 3209 | SK | | SVN | Slovenia | Europe | Southern Europe | 20256.00 | 1991 | 1987800 | 74.9 | 19756.00 | 18202.00 | Slovenija | Republic | Milan Kucan | 3212 | SI | | SWE | Sweden | Europe | Nordic Countries | 449964.00 | 836 | 8861400 | 79.6 | 226492.00 | 227757.00 | Sverige | Constitutional Monarchy | Carl XVI Gustaf | 3048 | SE | | UKR | Ukraine | Europe | Eastern Europe | 603700.00 | 1991 | 50456000 | 66.0 | 42168.00 | 49677.00 | Ukrajina | Republic | Leonid Kutšma | 3426 | UA | | 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 | | COL | Colombia | South America | South America | 1138914.00 | 1810 | 42321000 | 70.3 | 102896.00 | 105116.00 | Colombia | Republic | Andrés Pastrana Arango | 2257 | CO | | ECU | Ecuador | South America | South America | 283561.00 | 1822 | 12646000 | 71.1 | 19770.00 | 19769.00 | Ecuador | Republic | Gustavo Noboa Bejarano | 594 | EC | | FLK | Falkland Islands | South America | South America | 12173.00 | NULL | 2000 | NULL | 0.00 | NULL | Falkland Islands | Dependent Territory of the UK | Elisabeth II | 763 | FK | | GUF | French Guiana | South America | South America | 90000.00 | NULL | 181000 | 76.1 | 681.00 | NULL | Guyane française | Overseas Department of France | Jacques Chirac | 3014 | GF | | GUY | Guyana | South America | South America | 214969.00 | 1966 | 861000 | 64.0 | 722.00 | 743.00 | Guyana | Republic | Bharrat Jagdeo | 928 | GY | | PER | Peru | South America | South America | 1285216.00 | 1821 | 25662000 | 70.0 | 64140.00 | 65186.00 | Perú/Piruw | Republic | Valentin Paniagua Corazao | 2890 | PE | | PRY | Paraguay | South America | South America | 406752.00 | 1811 | 5496000 | 73.7 | 8444.00 | 9555.00 | Paraguay | Republic | Luis Ángel González Macchi | 2885 | PY | | 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)
