VISTAS

Practica repaso de vistas

Enter password: ***

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 6.0.4-alpha-community-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database notas;

Query OK, 1 row affected (0.28 sec)

mysql> use notas;

Database changed

mysql> create table alumnos(matricula int not null primary key, nombre varchar(30), apellidos varchar(50), semestre varchar(20), modulo varchar(50), calif_1 int, calif_2 int, calif_3 in

t);

Query OK, 0 rows affected (0.33 sec)

mysql> insert into alumnos(matricula, nombre, apellidos, semestre, modulo, calif_1, calif_2, calif_3) values(123,"edith","martinez hernandez","cuarto","cobd",8,9,10), (456,"manuel","zav

ala contreras","cuarto","cobd",9,8,7), (678,"irving","gonzales mena","cuarto","cobd",7,6,5);

Query OK, 3 rows affected (0.15 sec)

Records: 3  Duplicates: 0  Warnings: 0

mysql> create view vista_alumnos as select matricula, nombre, apellidos,(calif_1+calif_2+calif_3)/3 as promedio from alumnos;

Query OK, 0 rows affected (0.33 sec)

mysql> show tables;

+-----------------+

| Tables_in_notas |

+-----------------+

| alumnos         |

| vista_alumnos   |

+-----------------+

2 rows in set (0.20 sec)

mysql> select * from vista_alumnos;

+-----------+--------+--------------------+----------+

| matricula | nombre | apellidos          | promedio |

+-----------+--------+--------------------+----------+

|       123 | edith  | martinez hernandez |   9.0000 |

|       456 | manuel | zavala contreras   |   8.0000 |

|       678 | irving | gonzales mena      |   6.0000 |

+-----------+--------+--------------------+----------+

3 rows in set (0.11 sec)

mysql> insert into alumnos values(912,"fabian","mora","sanchez","cuarto","cobd",10,9,8);

ERROR 1136 (21S01): Column count doesn't match value count at row 1

mysql> insert into alumnos values(912,"fabian","mora sanchez","cuarto","cobd",10,9,8);

Query OK, 1 row affected (0.00 sec)

mysql> select * from alumnos;

+-----------+--------+--------------------+----------+--------+---------+---------+---------+

| matricula | nombre | apellidos          | semestre | modulo | calif_1 | calif_2 | calif_3 |

+-----------+--------+--------------------+----------+--------+---------+---------+---------+

|       123 | edith  | martinez hernandez | cuarto   | cobd   |       8 |       9 |      10 |

|       456 | manuel | zavala contreras   | cuarto   | cobd   |       9 |       8 |       7 |

|       678 | irving | gonzales mena      | cuarto   | cobd   |       7 |       6 |       5 |

|       912 | fabian | mora sanchez       | cuarto   | cobd   |      10 |       9 |       8 |

+-----------+--------+--------------------+----------+--------+---------+---------+---------+

4 rows in set (0.00 sec)

mysql> select * from vista_alumnos;

+-----------+--------+--------------------+----------+

| matricula | nombre | apellidos          | promedio |

+-----------+--------+--------------------+----------+

|       123 | edith  | martinez hernandez |   9.0000 |

|       456 | manuel | zavala contreras   |   8.0000 |

|       678 | irving | gonzales mena      |   6.0000 |

|       912 | fabian | mora sanchez       |   9.0000 |

+-----------+--------+--------------------+----------+

4 rows in set (0.00 sec)

Practica 12

manejo de vistas mediante instrucciones del lenguaje mysql

mysql> create view vista_librosdeunaidentidad as select codigo, titulo, no_de_paginas, editorial from libros;

Query OK, 0 rows affected (0.01 sec)

mysql> show tables;

+----------------------------+

| Tables_in_libreria         |

+----------------------------+

| autor                      |

| ejemplares                 |

| libros                     |

| usuarios                   |

| vista_librosdeunaidentidad |

+----------------------------+

5 rows in set (0.01 sec)

mysql> select * from vista_librosdeunaidentidad;

+--------+----------------------+---------------+------------+

| codigo | titulo               | no_de_paginas | editorial  |

+--------+----------------------+---------------+------------+

|   1231 | el_oso_negro         | 140           | limusa     |

|   1232 | el_pio_pio           | 30            | banderas   |

|   1233 | la_bella_y_la_bestia | 100           | grupo nori |

|   1234 | la_cenicienta        | 50            | 2004       |

|   1235 | principito           | 120           | 2004       |

|   1236 | blancanieves         | 115           | 2003       |

|   1237 | crepuesculo          | 400           | limusa     |

|   1238 | el_mundo_perdido     | 200           | limusa     |

|   1239 | harry potter         | 200           | argentina_ |

|   1240 | historias_de_amador  | 150           | limusa     |

+--------+----------------------+---------------+------------+

10 rows in set (0.00 sec)

mysql> drop view vista_librosdeunaidentidad;

Query OK, 0 rows affected (0.07 sec)

mysql> select * from vista_librosdeunaidentidad;

ERROR 1146 (42S02): Table 'libreria.vista_librosdeunaidentidad' doesn't exist

mysql>