Diferència entre revisions de la pàgina «ASIX/M02/UF2/A09/E1»
Salta a la navegació
Salta a la cerca
m (Guillem ha mogut M02/UF2/A09/E1 a ASIX/M02/UF2/A09/E1 sense deixar una redirecció: Crear subnivell ASIX) |
|||
| (Hi ha 7 revisions intermèdies del mateix usuari que no es mostren) | |||
| Línia 1: | Línia 1: | ||
| + | [[Category:M02]] | ||
{{titol|Pt91 - Pràctica de creació, manipulació i eliminació de vistes}} | {{titol|Pt91 - Pràctica de creació, manipulació i eliminació de vistes}} | ||
{{Capcalera|Guillem Solà i Boeck|05}} | {{Capcalera|Guillem Solà i Boeck|05}} | ||
| Línia 13: | Línia 14: | ||
+------+-------------------------------+-----------+------------------+-------------+-----------+------------+----------------+------------+------------+--------------------------------+-------------------------------------+--------------------------+---------+-------+ | +------+-------------------------------+-----------+------------------+-------------+-----------+------------+----------------+------------+------------+--------------------------------+-------------------------------------+--------------------------+---------+-------+ | ||
| ALB | Albania | Europe | Southern Europe | 28748.00 | 1912 | 3401200 | 71.6 | 3205.00 | 2500.00 | Shqipëria | Republic | Rexhep Mejdani | 34 | AL | | | 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) | 46 rows in set (0.00 sec) | ||
| Línia 64: | Línia 20: | ||
==Introduir un país en aquesta vista. Per comprovar que està introduït a ''Country''== | ==Introduir un país en aquesta vista. Per comprovar que està introduït a ''Country''== | ||
<source lang="mysqle"> | <source lang="mysqle"> | ||
| + | Afegeixo un registre amb uns quants valors | ||
MariaDB [world]> INSERT INTO vPractica91 (Code, Name, Continent, Region, Population) VALUES ("GS", "Guillemland", "Europe", "Wektabyte", 1); | MariaDB [world]> INSERT INTO vPractica91 (Code, Name, Continent, Region, Population) VALUES ("GS", "Guillemland", "Europe", "Wektabyte", 1); | ||
Query OK, 1 row affected (0.04 sec) | Query OK, 1 row affected (0.04 sec) | ||
</source> | </source> | ||
| + | Verifico que s'ha introduït | ||
<source lang="mysqle"> | <source lang="mysqle"> | ||
MariaDB [world]> SELECT * FROM Country WHERE Name = "Guillemland"; | MariaDB [world]> SELECT * FROM Country WHERE Name = "Guillemland"; | ||
| Línia 79: | Línia 37: | ||
==Eliminar aquest país des de la taula ''Country''== | ==Eliminar aquest país des de la taula ''Country''== | ||
| + | Elimino el registre anterior | ||
| + | <source lang="mysqle"> | ||
| + | MariaDB [world]> DELETE FROM Country WHERE Name = "Guillemland"; | ||
| + | Query OK, 1 row affected (0.06 sec) | ||
| + | </source> | ||
| + | |||
| + | Verifico que s'ha eliminat | ||
| + | <source lang="mysqle"> | ||
| + | MariaDB [world]> SELECT * FROM Country WHERE Name = "Guillemland"; | ||
| + | Empty set (0.00 sec) | ||
| + | </source> | ||
==Canviar la vista, per a que siguin els paisos de ''South America''== | ==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: | ||
| + | <source lang="mysqle"> | ||
| + | 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) | ||
| + | </source> | ||
| + | |||
| + | Verifico que la vista sigui correcta (mostri els països de ''South America'': | ||
| + | <source lang="mysqle"> | ||
| + | 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) | ||
| + | </source> | ||
Revisió de 11:19, 15 abr 2020
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 | +------+-------------------------------+-----------+------------------+-------------+-----------+------------+----------------+------------+------------+--------------------------------+-------------------------------------+--------------------------+---------+-------+ 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)
