PDF de programación - SQL con mySQL Avanzado - Vistas

Lenguaje Sql con MySQL avanzado



Parte I. Vistas

PARTE I.
VISTAS



Tema 1. Vistas


1.1 Antes de comenzar.................................................................................2
1.2 Ventajas de su utilización......................................................................6
1.3 Creación. Diccionario de Datos.............................................................8
1.4 Modificación........................................................................................14
1.5 Borrado................................................................................................14
1.6 Operaciones sobre vistas. Restricciones..............................................15
1.7 Ejercicios resueltos..............................................................................19


Página 1

Lenguaje Sql con MySQL avanzado



Parte I. Vistas

TEMA 1.
Vistas


Alberto Carrera Martín

1.1 Antes de comenzar

a) COMPROBACIÓN DEL ESTADO DEL SERVIDOR

Antes de empezar a trabajar asegúrate que la base de datos está arrancada. Si utilizas
Windows XP podrás comprobarlo mediante:

Botón Inicio / Panel de Control / Rendimiento y Mantenimiento / Herramientas
Administrativas / Servicios



Ilustración 1. Servicio de MySQL


En este caso se encuentra iniciado, si no fuera así lo podrías arrancar haciendo clic con
el botón derecho sobre el servicio y eligiendo la opción de Iniciar.

También lo puedes saber de otras formas y de manera gráfica ejecutando el monitor de
sistema de MySQL que tras lanzarlo:

Botón Inicio / Todos los programas / MySQL / MySQL System Tray Monitor

aparecerá con el siguiente icono en la barra de estado
. Al hacer un clic con el botón
derecho sobre él se puede comprobar que la base de datos está “corriendo” o
ejecutándose tal y como se aprecia en la primera línea de siguiente ilustración 2. El
rectángulo verde del icono ya hace presagiar tal situación; si la figura que nos
hubiéramos encontrado dentro del icono hubiera sido un cuadrado rojo, entonces la base
de datos estaría parada y por tanto deberíamos arrancarla (opción Start Instance en
ilustración 3 siguiente)


Página 2

Lenguaje Sql con MySQL avanzado



Parte I. Vistas



Ilustración 3. Monitor de MySQL. Servicio
detenido

Ilustración 2. Monitor de MySQL. Servicio
ejecutándose

b) CREACIÓN DE LA BASE DE DATOS DE EJEMPLO

La realizaremos de dos maneras de las varias posibles existentes. Lee las dos y después
pon en práctica la que desees (o puedes experimentar con las dos)

b1) Creación de la base de datos ejemplo utilizando una ventana de consola de
windows

Los archivos binarios del servidor se encuentran instalados en la carpeta:

C:\Archivos de programa\MySQL\MySQL Server 5.0\bin

Nota: La unidad de disco anterior (C:) y la ruta de carpetas puede cambiar en función de
donde se realizara la instalación.

Dentro de la carpeta anterior crea a su vez una carpeta denominada bases y copia allí el
script tablas1.sql que encontrarás en la sección de materiales del curso. Una vez hecho,
arranca una ventana de consola (Botón Inicio / Todos los programas / Accesorios /
Símbolo de
(C:\Archivos de
programa\MySQL\MySQL Server 5.0\bin>) y ejecuta el comando siguiente que te
permitirá conectarte a la base de datos como usuario root y al mismo tiempo ejecutar
las instrucciones que contiene el script tablas1.sql copiado a la carpeta bases anterior:

mysql -u root -p < bases\tablas1.sql

La Instrucción anterior es equivalente a:
mysql -u root -p < "C:\Archivos de programa\MySQL\MySQL Server 5.0\bin\bases\tablas1.sql"

Tras el mensaje de introducción de la clave del usuario root

Enter password: ******

El script tablas1.sql crea, además de las tablas que puedes ver al final de este apartado,
un usuario1 de clave usuario1 con todos los permisos de trabajo sobre la base de datos

sitúate en

sistema),

la carpeta anterior



Página 3

Lenguaje Sql con MySQL avanzado



Parte I. Vistas

practica1 que se acaba de crear. Se puede comprobar si en la ventana de consola abres
una conexión con el usuario1:

D:\Archivos de programa\MySQL\MySQL Server 5.0\bin>mysql -u usuario1 -p
Enter password: ********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 41 to server version: 5.0.20a-nt

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

mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| practica1 |
| test |
+--------------------+
4 rows in set (0.77 sec)

mysql> USE practica1
Database changed
mysql> SHOW TABLES;
+---------------------+
| Tables_in_practica1 |
+---------------------+
| centros |
| departamentos |
| empleados |
+---------------------+
3 rows in set (0.01 sec)

b2) Creación de la base de datos ejemplo utilizando una herramienta cliente

Otra forma más atractiva de realizar la tarea anterior es utilizar la herramienta gráfica de
MySQL Query Browser (para este
curso se ha utilizado la versión 1.1.2.0
que se encuentra en el fichero mysql-
query-browser-1.1.20-win.msi).
Su
instalación es muy sencilla. Tras
lanzarla desde el monitor de MySQL
visto anteriormente en el apartado
1.1.a (ilustraciones 2 y 3) o también
mediante Botón Inicio / Todos los
programas /MySQL / MySQL Query
Browser aparecerá la pantalla inicial
de la ilustración 4 de la derecha. Tras
introducir el usuario/clave de root,
pulsa OK para avanzar a la siguiente
pantalla.

Ilustración 4. MySQL Query Browser



Página 4

Lenguaje Sql con MySQL avanzado



Parte I. Vistas

se

que

Observa
ha
introducido el usuario y la
clave del Administrador root
pero no la base de datos a la
(Default
que
Schema) por
tras
pulsar el botón OK puede
aparecer
siguiente
ilustración 5 de la izquierda
pantalla para recordárnoslo:


lo que

se

conecta

la

Ilustración 5. MySQL Query Browser

Pulsaremos el botón Ignore para continuar y entrar en la herramienta cliente. Una vez
en ella abriremos (opción File / Open Script… del menú) el script tablas1.sql que
podrás encontrar en la sección de materiales del curso (ver ilustración 6)


Ilustración 6. Cargando un script en MySQL Query Browser


A continuación puedes ejecutar el script mediante el botón Execute (parte superior
derecha como se puede apreciar en la ilustración 6 anterior) con lo que habrás creado el



Página 5

Lenguaje Sql con MySQL avanzado



Parte I. Vistas

usuario usuario1 de clave usuario1 y la base de datos práctica1 con las 3 tablas que la
componen (haciendo doble clic en cada una de ellas podrás ver sus registros)


Ilustración 7. Base de datos practica1 creada



1.2 Ventajas de su utilización

Nota: Para probar los ejemplos siguientes puedes hacerlo conectándote a la base de
datos (default schema) practica1 como usuario usuario1 de clave usuario1 mediante las
dos formas vistas en el apartado anterior. Por su sencillez y claridad utilizaremos la
segunda de ellas, la herramienta gráfica MySQL Query Browser. Después de introducir
cada sentencia recuerda pulsar el botón Execute.

Antes de definir lo que es una vista vamos a ver las ventajas que nos aportan.

- Seguridad y confidencialidad: Si un empleado de la centralita telefónica necesita para
su trabajo conocer exclusivamente el nombre y extensión telefónica de todos sus
Compañeros, el resto de columnas con datos privados como el número de hijos,
sueldo… de la tabla debe ser “invisible” para él. Por tanto el Administrador de la base
de datos creará una “visión parcial” (vista1a en el ejemplo) de la tabla:


Instrucción 1


y sólo le permitirá consultar dicha vista y no la tabla original:



Página 6

Lenguaje Sql con MySQL avanzado



Parte I. Vistas

Instrucción 2



Desde el punto de vista de la administración, es más fácil administrar permisos sobre
una vista que asignar privilegios a las columnas de las tablas.

- Facilidad de uso para consultas “complejas”. Si un Jefe desea consultar los Centros
junto con sus Departamentos y nombres de los Directores:


Ilustración 8. Centros, Departamentos y Directores


en lugar de tener que realizar la siguiente consulta:


Instrucción 3


el Administrador puede preparar la siguiente vista:



Página 7

Lenguaje Sql con MySQL avanzado



Parte I. Vistas

Instrucción 4



para que el Jefe sólo tenga que consultar:



Instrucción 5

y con ello obtendrá las 8 filas y 5 columnas anteriores con toda la información sobre
centros, departamentos y sus directores.

Nota: Si no estás acostumbrado a la sintaxis anterior en la que se utiliza la cláusula
JOIN … ON para unir tablas puedes utilizar esta otra:


Instrucción 6



Hay que advertir que aunque el aspecto de una vista sea el de una tabla, una vista no es
nada más que una instrucción SELECT del SQL almacenada con un nombre (vista1a,
vista1b…). Por tanto no contiene datos (filas) permanentemente como una tabla y de
esta manera no hay ficheros asociados a ellas. De todas formas, como se puede
comprobar con la última instrucción lanzada, la forma de manejar vistas y tablas es muy
similar y podemos estar consultando un origen de datos sin saber si éste es una tabla o
una vista.

1.3 Creación. Diccionario de Datos

Pueden utilizarse las vistas a partir de la versión de MySql 5.0.1.
Para poder crear vistas se requiere el privilegio CREATE VIEW así como los
privilegios para poder seleccionar las columnas que forman