taller de base de datos - · pdf fileunidad uno introducción al sistema manejador de...

ISC

Taller de Base de Datos

D O C E N T E

L.I. JOSE HERNANDEZ

RODRIGUEZ

Ta

ller

de

Ba

se d

e D

ato

s

I S

C

INSTITUTO TECNOLOGICO SUPERIOR DE ACAYUCAN

Compilado por L.I. JHR V SEMESTRE PAG. 2

Aportación de la asignatura al perfil del egresado

Utiliza un sistema de base de datos (DBMS) comercial.

Objetivo general del curso

Proporcionará al estudiante las

habilidades para el desarrollo y

manipulación de bases de datos para la

integración de sistemas de información

transaccionales.

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



Temario

1 Introducción al Sistema Manejador de Base de Datos (DBMS) 1.1 Conceptos. 1.2 Características del DBMS 2 Lenguaje de Definición de Datos (DDL) 2.1 Creación de base de datos. 2.2 Creación de tablas.

2.2.1 Integridad. 2.2.2 Integridad referencial declarativa.

2.3 Creación de índices 3 Consultas y Lenguaje de Manipulación de Datos (DML) 3.1 Instrucciones INSERT, UPDATE, DELETE. 3.2 Consultas Básicas SELECT, WHERE y funciones a nivel de registro. 3.3 Consultas sobre múltiples tablas.

3.3.1 Subconsultas. 3.3.2 Operadores JOIN.

3.4 Agregación GROUP BY, HAVING. 3.5 Funciones de conjunto de registros COUNT, SUM, AVG, MAX, MIN 4 Control de Transacciones. 4.1 Propiedades de la transacción. 4.2 Grados de consistencia. 4.3 Niveles de aislamiento. 4.4 Instrucciones COMMIT y ROLLBACK. 5 Vistas 5.1 Definición y objetivo de las vistas. 5.2 Instrucciones para la administración de vistas. 6 Seguridad. 6.1 Esquemas de autorización. 6.2 Instrucciones GRANT y REVOKE. 7 Introducción al SQL Procedural. 7.1 Procedimientos almacenados. 7.2 Disparadores (Triggers).

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



UNIDAD UNO Introducción al Sistema Manejador de Base de Datos (DBMS)

Objetivo Educacional: El estudiante conocerá los elementos y características

principales del DBMS a utilizar en el curso.

1.1 Conceptos

“Consiste en una colección de datos interrelacionados y un

conjunto de programas para acceder a dichos datos.”

La colección de datos, normalmente se denomina base de datos,

contiene información relevante para una empresa.

Su objetivo primordial es: “Proporcionar una forma de

almacenar y recuperar la información de una bd de manera que

sea tanto practica como eficiente”

Se compone de un lenguaje de definición de datos (DDL: Data Definition Language), de

un lenguaje de manipulación de datos (DML: Data Manipulation Language) y de un lenguaje de

consulta (SQL: Structured Query Language).

El lenguaje de definición de datos (DDL) es utilizado para describir todas las

estructuras de información y los programas que se usan para construir, actualizar e introducir la información que contiene una base de datos.

El lenguaje de manipulación de datos (DML) es utilizado para escribir programas que crean, actualizan y extraen información de las bases de datos.

El lenguaje de consulta (SQL) es empleado por el usuario para extraer información de la base de datos. El lenguaje de consulta permite al usuario hacer requisiciones de datos sin tener que escribir un programa, usando instrucciones como el SELECT, el PROJECT y el JOIN.

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



Los sistemas de base de datos se diseñan para gestionar grandes cantidades de información. La

gestión de datos implica tanto la definición de estructuras para almacenar la información como

la provisión de mecanismos para la manipulación de la información. Además, deben

proporcionar la fiabilidad de la información almacenada, a pesar de las caídas del sistema o

intentos de acceso sin autorización.

Los sistemas de bases de datos se aplican en: La banca, líneas aéreas, universidades,

Transacciones de tarjetas de crédito, telecomunicaciones, finanzas, ventas, producción y recursos

humanos.

1.2 Características del DBMS (Data Base Management System)

Un SGBD debe proporcionar a los usuarios la capacidad de almacenar datos en la base de datos, acceder a ellos y actualizarlos. Esta es la función fundamental de un SGBD.

Un SGBD debe proporcionar un catálogo en el que se almacenan las descripciones de los datos y que sea accesible por los usuarios. Este catálogo es lo que se denomina diccionario de datos y contiene información que describe los datos de la base de datos (meta datos).

Un SGBD debe proporcionar un mecanismo que garantice que todas las actualizaciones correspondientes a una determinada transacción se realicen, o que no se realice ninguna. Una transacción es un conjunto de acciones que cambian el contenido de la base de da tos.

Un SGBD debe proporcionar un mecanismo que asegure que la base de datos se actualice correctamente cuando varios usuarios la están actualizando concurrentemente. Uno de los principales objetivos de los SGBD es el permitir que varios usuarios tengan acceso concurrente a los datos que comparten. El SGBD se debe encargar de que estas interferencias no se produzcan en el acceso simultáneo.

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



Un SGBD debe proporcionar un mecanismo capaz de recuperar la base de datos en caso de que ocurra algún suceso que la dañe llevándola a un estado consistente.

Un SGBD debe proporcionar un mecanismo que garantice que sólo los usuarios autorizados pueden acceder a la base de datos. La protección debe ser contra accesos no autorizados, tanto intencionados como accidentales.

Un SGBD debe proporcionar los medios necesarios para garantizar que tanto los datos de la base de datos, como los cambios que se realizan sobre estos datos, sigan ciertas reglas. La integridad de la base de datos requiere la validez y consistencia de los datos almacenados. Se puede considerar como otro modo de proteger la base de datos, pero además de tener que ver con la seguridad, tiene otras implicaciones. La integridad se ocupa de la calidad de los datos. Normalmente se expresa mediante restricciones, que son una serie de reglas que la base de datos no puede violar.

Un SGBD debe proporcionar una serie de herramientas que permitan administrar la base de datos de modo efectivo. Dichas herramientas deben proporcionar.

Herramienta administración de usuarios Analizador de logs(Registro oficial de eventos durante un periodo de tiempo

en particular. Para los profesionales en seguridad informática un log es usado para registrar datos o información sobre quién, que, cuando, donde y por qué, un evento ocurre para un dispositivo en particular o aplicación.

Administrador de procesos Herramientas para importar y exportar datos. Herramientas para monitorizar el uso y el funcionamiento de la base de datos. Programas de análisis estadístico para examinar las prestaciones o las

estadísticas de utilización. Herramientas para reorganización de índices.

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



ACTIVIDADES DE APRENDIZAJE

Realizar una comparación de las características de varios DBMSs. Discutir las ventajas y desventajas de los DBMS revisados. Buscar el proceso y requerimientos de instalación del DBMS que ha de utilizarse en el

curso. Instalar el DBMS

EVALUACION

Examen 40 % Participación 20 % Investigación 20 % Trabajo equipo e-r 10 % (En rotafolios) Ejercicios de Repaso 10 % 100 %

OTROS RECURSOS

Recurso 1. Cuadro Comparativo de algunos DBMS Recurso 2. Instalación de AppServ Recurso 3. Lista de ejercicios

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



Recurso uno:

Cuadro Comparativo de algunos DBMS. Ver anexos

Tipos de datos en Mysql

Recurso Dos:

Instalación de AppServ

Que es AppServ? Es un software que permite la instalación en nuestro entorno Windows, de los siguientes paquetes:

Apache Web Server (versión 2.2.3)

Lenguaje PHP (versión 5 o 4)

Base de datos MySQL (versión 5.0.24) Manejador de base de datos phpMyAdmin (versión 2.9.0.2)

Esto es lo que incluye el actual paquete AppServ 2.5.7, es obvio pero vale la pena recordar que como esta formado por 4 paquetes (apache, php, Mysql, phpMyAdmin), cada uno sigue lanzando nuevas versiones, que podemos instalar por separado o esperar la próxima versión de AppServ.

Como instalar AppServ 1) Damos doble click en el icono del instalado. Aparece la imagen que vemos debajo, recuerda que la versión puede ser más nueva cuando tú intentes ;). Debemos dar, siguiente (next).

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



2) Aparece el acuerdo de licencia, ahí debemos dar conformidad (I Agree)

3) En este punto, debemos elegir en cual de nuestros discos duros y en que carpeta vamos a instalar el AppServ, por defecto e s c:\AppServ, como muestra la imagen lo dejemos así, y le damos siguiente (Next)

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



4) En este punto debemos elegir los componentes que vamos a instalar, como ves aparecen marcados los cuatro elementos disponibles (apache, mysql, php, phpmyadmin) si bien puedes desmarcar alguno, es mejor instalar tod os para tener completo tu equipo de testeo y desarrollo Web, le damos siguiente (Next)

5) En este punto debes indicar el nombre del servidor y el email del administrador del sistema, ponemos localhost y email [email protected], respectivamente, el puerto 80. Clic en siguiente

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



6) Configuración MySQL, en este punto debemos prestar atención, ya que debemos indicar el Password, para el usuario principal de la base de datos, lo escribimos 2 veces !! Activamos Enable Innodb, ya que de lo contrario no podemos utilizar e ste tipo de tablas que serán de uso en este semestre.

Por tanto el usuario será root, que es por y tu Contraseña que acabas de escribir, eso lo usaras para abrir la base desde tus programas PHP o cuando entre al MYSQL, por eso es importante recordar. Le damos siguiente (Next)

7) Para culminar la instalación, como indica la imagen, podemos elegir si al finalizar ya comenzara a funcionar el Server Apache y el servidor de base de datos MySQL, lo dejamos marcado y le damos finalizar (Finish), listo hemos terminado.

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



En este punto si vas a tu navegador, puedes poner, http://localhost y debe mostrarse tal como muestra la imagen. Salvo las versiones que el tuyo puede ser mas nuevo.

En la configuración Standard usando c:\AppServ la ubicación que equivale a http://localhost es c:\AppServ\www por tanto si creas una carpeta pruebas dentro de www, quedand asi: c:\AppServ\www\pruebas, y dentro un archivo llamados test.php con el siguiente contenido:

<?php

echo phpinfo();

?>

Este archivo se ejecutara en tu navegador llamando lo así:

http://localhost/pruebas/test.php

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



Recurso Tres:

Lista de Ejercicios

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



UNIDAD DOS Lenguaje de Definición de Datos (DDL)

Objetivo Educacional: Creará una base de datos, y definirá su esquema en SQL.

DDL (Data Definition Language) Lenguaje de definición de datos. Es el lenguaje que se usa para crear bases de datos y tablas, y para modificar sus estructuras, así como los permisos y privilegios. Este lenguaje trabaja sobre unas tablas especiales llamadas diccionario de datos. DML (Data Manipulation Language) lenguaje de manipulación de datos. Es el que se usa para modificar y obtener datos desde las bases de datos. SQL engloba ambos lenguajes DDL+DML, ya que ambos forman parte del conjunto de sentencias de SQL.

2.1 Creación de base de datos.

Para crear una base de datos se usa una sentencia CREATE DATABASE mysql> CREATE DATABASE prueba;

Para mostrar las bases de datos existentes en nuestro sistema se usa la sentencia SHOW DATABASES mysql> SHOW DATABASES; +--------------------+ | Database |

+--------------------+ | mysql | | prueba | | test | +--------------------+

3 rows in set (0.00 sec)

Para seleccionar una base de datos se usa el comando USE, que no es exactamente una sentencia SQL, sino más bien de una opción de MySQL mysql> USE prueba; Database changed mysql>

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



2.2 Creación de tablas.

La sentencia CREATE TABLE que sirve para crear tablas. La sintaxis de esta sentencia es muy compleja, ya que existen muchas opciones y tenemos muchas posibilidades diferentes a la hora de crear una tabla. Deberemos indicar el nombre de la tabla y los nombres y tipos de las columnas

Leer el recurso 4. Tipos de datos en Mysql

mysql> CREATE TABLE gente (nombre VARCHAR(40), fecha DATE); Query OK, 0 rows affected (0.53 sec)

Podemos consultar cuántas tablas y qué nombres tienen en una base de datos, usando la sentencia SHOW TABLES mysql> SHOW TABLES; +------------------+

| Tables_in_prueba | +------------------+ | gente |

+------------------+ 1 row in set (0.01 sec)

La sintaxis para definir columnas es: nombre_col tipo [NOT NULL | NULL] [DEFAULT valor_por_defecto] [AUTO_INCREMENT] [[PRIMARY] KEY] [COMMENT 'string'] [definición_referencia] Valores nulos: Al definir cada columna podemos decidir si podrá o no contener valores nulos. La opción por defecto es que se permitan valores nulos, NULL, y para que no se permitan, se usa NOT NULL. Por ejemplo: mysql>CREATE TABLE ciudad1(nombre CHAR(20) NOT NULL, poblacion INT NULL); Query OK, 0 rows affected (0.98 sec)

Valores por defecto: Para cada columna también se puede definir, opcionalmente, un valor por defecto. El valor por defecto se asignará de forma automática a una columna cuando no se especifique un valor determinado al añadir filas. Si una columna puede tener un valor nulo, y no se especifica un valor por defecto, se usará NULL como valor por defecto. En el ejemplo anterior, el valor por defecto para poblacion es NULL.

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



Por ejemplo, si queremos que el valor por defecto para población sea 5000, podemos crear la tabla como: mysql>CREATE TABLE ciudad2(nombre CHAR(20) NOT NULL, poblacion INT NULL DEFAULT 5000);

Query OK, 0 rows affected (0.09 sec)

Claves primaria: También se puede definir una clave primaria sobre una columna, usando la palabra clave KEY o PRIMARY KEY. Sólo puede existir una clave primaria en cada tabla, y la columna sobre la que se define una clave primaria no puede tener valores NULL. Si esto no se especifica de forma explícita, MySQL lo hará de forma automática. Por ejemplo, si queremos crear un índice en la columna nombre de la tabla de ciudades, crearemos la tabla así: mysql> CREATE TABLE ciudad3 (nombre CHAR(20) NOT NULL PRIMARY KEY, poblacio n INT NULL DEFAULT 5000); Query OK, 0 rows affected (0.20 sec)

mysql> CREATE TABLE ciudad3 (nombre CHAR(20) NOT NULL, poblacion INT NULL DEFAULT 5000, PRIMARY KEY(nombre)); Query OK, 0 rows affected (0.20 sec)

Usar NOT NULL PRIMARY KEY equivale a PRIMARY KEY, NOT NULL KEY o sencillamente KEY. Columnas autoincrementadas: En MySQL tenemos la posibilidad de crear una columna autoincrementada, aunque esta columna sólo puede ser de tipo entero. Si al insertar una fila se omite el valor de la columna autoincrementada o si se inserta un valor nulo para esa columna, su valor se calcula automáticamente, tomando el valor más alto de esa columna y sumándole una unidad. Esto permite crear, de una forma sencilla, una columna con un valor único para cada fila de la tabla. Generalmente, estas columnas se usan como claves primarias 'artificiales'. MySQL está optimizado para usar valores enteros como claves primarias, de modo que la combinación de clave primaria, que sea entera y autoincrementada es ideal para usarla como clave primaria artificial: mysql> CREATE TABLE ciudad5 (clave INT AUTO_INCREMENT PRIMARY KEY, nombre CHAR(20) NOT NULL,poblacion INT NULL DEFAULT 5000);


Comentario: Adicionalmente, al crear la tabla, podemos añadir un comentario a cada columna. Este comentario sirve como información adicional sobre alguna característica especial de la columna, y entra en el apartado de documentación de la base de datos: mysql> CREATE TABLE ciudad6(clave INT AUTO_INCREMENT PRIMARY KEY COMMENT 'Clave principal ',nombre CHAR(50) NOT NULL, poblacion INT NULL DEFAULT 5000); Query OK, 0 rows affected (0.08 sec) mysql> show full columns from ciudad6;

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



Además de los comandos: Create database, use, create table, show database y show tables, también son DLL’s: ALTER TABLE y DROP Alter table

Alter table clientes change apaterno apaterno

varchar(50);

Alter table clientes rename tabla_clie;

Alter table tabla_clie drop domicilio;

Alter table tabla_clie add nombre varchar(30)

Alter table clientes add index (apellido_paterno)

Alter table Clientes add primary key (id_clientes)

Cambia el tipo de dato o nombre de la

columna

Cambia el nombre de la tabla

Elimina una columna

Añade una columna

Pone como columna indexada a

apellido_paterno

Hace de id_clientes la llave primaria

Drop

Drop table NombreTabla;

Drop database NombreBd;

Drop index apaterno on clientes

Alter table clients drop Primary Key

Elimina una tabla

Elimina toda la base de datos

Le quita la indexación a la columna

apaterno

Borra una clave primaria (en una tabla solo

existe una llave primaria por eso no se

pone el nombre de la columna)

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



2.2.1 Integridad.

DEFINICION: Se refiere a la corrección y completitud de los datos en una BD. Cuando los contenidos de una BD se modifican con sentencias INSERT, DELETE o UPDATE, la integridad de los datos almacenados puede perderse de muchas maneras diferentes. Por ejemplo: Pueden añadirse datos no válidos a la base de datos, tales como un pedido que especifica

un producto no existente. Pueden modificarse datos existentes tomando un valor incorrecto, como por ejemplo si

se reasigna un vendedor a una oficina no existente. Los cambios a la base de datos pueden perderse debido a un error del sistema o a un

fallo en el suministro de potencia. Los cambios pueden ser aplicados parcialmente, como por ejemplo si se añade un

pedido de un producto sin ajustar la cantidad disponible para vender. Una función importante de un DBMS (sistema gestor de la base de datos) re lacional es preservar la integridad de los datos almacenados en la mayor medida posible Para preservar la consistencia y corrección de los datos almacenados, un DBMS relacional impone generalmente una o más restricciones de integridad de datos. Estas res tricciones restringen los valores que pueden ser insertados en la base de datos o creados mediante una actualización de la base de datos.

Varios tipos diferentes de restricciones de integridad de datos suelen encontrarse en las bases de datos relaciónales, incluyendo: Datos requeridos: Algunas columnas en una base de datos deben contener un valor de

dato válido en cada fila, es decir, no se permite que contengan valores NULL o que falten. Pueden especificarse columnas requeridas cuando se crea un tabla, en cuyo caso, el DBMS impedirá los valores NULL en esas columnas.

Chequeo de validez: Cada columna de una base de datos tiene un dominio, o sea, un

conjunto de valores que son legales para esa columna. El DBMS puede ser preparado para impedir otros valores de datos en estas columnas.

Integridad de entidad: La clave primaria de una tabla debe contener un valor único en

cada fila, diferente de los valores de todas las filas restantes. Los valores duplicados son ilegales y el DBMS puede ser preparado para forzar esta restricción de valores únicos. Las restricciones de integridad de entidad aseguran que la clave primaria identifique unívocamente a cada entidad representada en la base de datos.

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



Consistencia: Muchas transacciones del mundo real producen múltiples actualizaciones a una base de datos. Las sentencias INSERT y UPDATE deben ejecutarse de modo que la base de datos continúe en un estado correcto y consistente. El DBMS puede ser preparado para forzar este tipo de regla de consistencia o para soportar aplicaciones que implementen tales reglas.

2.2.2 Integridad referencial declarativa.

Integridad referencial: Una clave secundaria (externa o foránea) en una base de datos relacional enlaza cada fila de la tabla hijo que contiene la clave foránea con la fila de la tabla padre que contiene el valor de clave primaria correspondiente. El DBMS puede ser preparado para forzar esta restricción de clave foránea/clave primaria. Las restricciones de integridad referencial aseguran que las relaciones entre entidades en la base de datos se preserven durante las actualizaciones. En particular, la integridad referencial debe incluir reglas que indiquen cómo manejar la supresión de filas que son referenciadas mediante otras filas.

Problemas de integridad referencial Existen cuatro tipos de actualizaciones de bases de datos que pueden corromper la integridad referencial de las relaciones padre/hijo de una base de datos.

1. La inserción de una nueva fila hijo: Cuando se inserta una nueva fila en la tabla hijo, su valor de clave foránea debe coincidir con uno de los valores de clave primaria en la tabla padre. Si el valor de clave foránea no coincide con ninguna clave primaria, la inserción de la fila corromperá la base de datos, ya que habrá un hijo sin un padre (un huérfano). Observe que insertar una fila en la tabla padre nunca representa un problema; simplemente se convierte en un padre sin hijos.

2. La actualización de la clave foránea en una fila hijo: Esta es una forma diferente del problema anterior. Si la clave foránea se modifica mediante una sentencia UPDATE, el nuevo valor deberá coincidir con un valor de clave primaria en la tabla padre. En caso contrario la fila actualizada será huérfana.

3. La supresión de una fila padre: Si una fila de la tabla padre, que tiene uno o más hijos se suprime, las filas hijo quedarán huérfanas. Los valores de clave foránea en estas filas ya no se corresponderán con ningún valor de clave primaria en la tabla padre. Observe que suprimir una fila de la tabla hijo nunca representa un problema; el padre de esta fila simplemente tendrá un hijo menos después de la supresión.

4. La actualización de la clave primaria en una fila padre: Esta es una forma diferente del problema anterior. Si la clave primaria de una fila en la tabla padre se modifica, todos los hijos actuales de esa fila quedarán huérfanos, puesto que sus claves forá neas ya no corresponden con ningún valor de clave primaria.

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



Claves foráneas en MySQL Estrictamente hablando, para que un campo sea una clave foránea, éste necesita ser definido como tal al momento de crear una tabla. Se pueden definir claves foráneas en cualquier tipo de tabla de MySQL, pero únicamente tienen sentido cuando se usan tablas del tipo InnoDB. Para trabajar con claves foráneas, necesitamos hacer lo siguiente:

1. Crear ambas tablas del tipo InnoDB. 2. Usar la sintaxis FOREIGN KEY(campo_fk) REFERENCES nombre_tabla (nombre_campo) 3. Crear un índice en el campo que ha sido declarado clave foránea.

InnoDB no crea de manera automática índices en las claves foráneas o en las claves referenciadas, así que debemos crearlos de manera explícita. Los índices son necesarios para que la verificación de las claves foráneas sea más rápida. A continuación se muestra como definir las dos tablas de ejemplo con una clave foránea. CREATE TABLE cliente(id_cliente INT NOT NULL,nombre VARCHAR(30), PRIMARY KEY

(id_cliente)) TYPE = INNODB;

CREATE TABLE venta(id_factura INT NOT NULL,id_cliente INT NOT NULL,cantidad

INT,PRIMARY KEY(id_factura), FOREIGN KEY (id_cliente) REFERENCES

cliente(id_cliente)) TYPE = INNODB;

La sintaxis completa de una restricción de clave foránea es la siguiente: [CONSTRAINT símbolo] FOREIGN KEY (nombre_columna, ...)

REFERENCES nombre_tabla (nombre_columna, ...)

[ON DELETE {CASCADE | SET NULL | NO ACTION

| RESTRICT}]

[ON UPDATE {CASCADE | SET NULL | NO ACTION

| RESTRICT}]

Las columnas correspondientes en la clave foránea y en la clave referenciada deben tener tipos de datos similares para que puedan ser comparadas sin la necesidad de hacer una conversión de tipos. El tamaño y el signo de los tipos enteros debe ser el mismo. En las columnas de tipo caracter, el tamaño no tiene que ser el mismo necesariamente. Por ejemplo, la creación de la clave foránea en la tabla venta que se mostró anteriormente pudo haberse hecho de la siguiente manera con el uso de una sentencia ALTER TABLE: CREATE TABLE venta

(

id_factura INT NOT NULL,

id_cliente INT NOT NULL,

cantidad INT,

PRIMARY KEY(id_factura),

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



INDEX (id_cliente)

) TYPE = INNODB;

ALTER TABLE venta ADD FOREIGN KEY(id_cliente) REFERENCES cliente(id_cliente);

La integridad referencial se puede comprometer básicamente en tres situaciones: cuando se está insertando un nuevo registro, cuando se está eliminando un registro, y cuando se está actualizando un registro. La restricción de clave foránea que hemos definido se asegura que cuando un nuevo registro sea creado en la tabla venta, éste debe tener su correspondiente registro en la tabla cliente. Una vez que hemos creado las tablas, vamos a insertar algunos datos que nos sirvan para demostrar algunos conceptos importantes: mysql> INSERT INTO cliente VALUES(1,'Juan Penas');

Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO cliente VALUES(2,'Pepe el toro');


mysql> INSERT INTO venta VALUES(1,1,23);




En este momento no hay ningún problema, sin embargo, vamos a ver que sucede cuando intentamos insertar un registro en la tabla venta que se refiera a un cliente no existente cuyo id_cliente es 3: mysql> INSERT INTO venta VALUES(2,3,39);

ERROR 1216: Cannot add or update a child row: a foreign key constraint fails

El hecho es que MySQL no nos permite insertar este registro, ya que el cliente cuyo id_cliente es 3 no existe. La restricción de clave foránea asegura que nuestros datos mantienen su integridad. Sin embargo, ¿qué sucede cuando eliminamos algún registro?. Vamos a agregar un nuevo cliente, y un nuevo registro en la tabla venta, posteriormente eliminaremos el registro de nuestro tercer cliente:

mysql> INSERT INTO cliente VALUES(3,'Pepe pecas');




mysql> DELETE FROM cliente WHERE id_cliente=3;

ERROR 1217: Cannot delete or update a parent row: a foreign key constraint fails

Debido a nuestra restricción de clave foránea, MySQL no permite que eliminemos el registro de cliente cuyo id_cliente es 3, ya que se hace referencia a éste en la tabla venta. De nuevo, se

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



mantiene la integridad de nuestros datos. Sin embargo existe una forma en la cuál podríamos hacer que la sentencia DELETE se ejecute de cualquier manera, y la veremos brevemente, pero primero necesitamos saber cómo eliminar (quitar) una clave foránea.

Eliminación de una clave foránea No podemos sólo eliminar una restricción de clave foránea como si fuera un índice ordinario. Veamos que sucede cuando lo intentamos. mysql> ALTER TABLE venta DROP FOREIGN KEY;

ERROR 1005: Can't create table '.test#sql-228_4.frm' (errno: 150)

Para eliminar la clave foránea se tiene que especificar el ID que ha sido generado y asignado internamente por MySQL a la clave foránea. En este caso, se puede usar la sentencia SHOW CREATE TABLE para determinar dicho ID. mysql> SHOW CREATE TABLE venta;

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

| Table | Create Table |

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

| venta | CREATE TABLE 'venta' ( |

| | 'id_factura' int(11) NOT NULL default '0', |

| | 'id_cliente' int(11) NOT NULL default '0', |

| | 'cantidad' int(11) default NULL, |

| | PRIMARY KEY ('id_factura'), |

| | KEY 'id_cliente' ('id_cliente'), |

| | CONSTRAINT '0_22' FOREIGN KEY ('id_cliente') |

| | REFERENCES 'cliente' ('id_cliente') ) TYPE=InnoDB |

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

1 row in set (0.00 sec)

En nuestro ejemplo, la restricción tiene el ID 0_22 (es muy probable que este valor sea diferente en cada caso). mysql> ALTER TABLE venta DROP FOREIGN KEY 0_22;


Records: 3 Duplicates: 0 Warnings: 0

Eliminación de registros con claves foráneas Una de las principales bondades de las claves foráneas es que permiten eliminar y actualizar registros en cascada. Con las restricciones de clave foránea podemos eliminar un registro de la tabla cliente y a la vez eliminar un registro de la tabla venta usando sólo una sentencia DELETE. Esto es llamado eliminación en cascada, en donde todos los registros relacionados son eliminados de acuerdo a las relaciones de clave foránea. Una alternativa es no eliminar los registros relacionados, y

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



poner el valor de la clave foránea a NULL (asumiendo que el campo puede tener un valor nulo). En nuestro caso, no podemos poner el valor de nuestra clave foránea id_cliente en la tabla venta, ya que se ha definido como NOT NULL. Las opciones estándar cuando se elimina un registro con clave foránea son: ON DELETE RESTRICT ON DELETE NO ACTION ON DELETE SET DEFAULT ON DELETE CASCADE ON DELETE SET NULL ON DELETE RESTRICT es la acción predeterminada, y no permite una eliminación si existe un registro asociado, como se mostró en el ejemplo anterior. ON DELETE NO ACTION hace lo mismo. ON DELETE SET DEFAULT actualmente no funciona en MySQL - se supone que pone el valor de la clave foránea al valor por omisión (DEFAULT) que se definió al momento de crear la tabla. Si se especifica ON DELETE CASCADE, y una fila en la tabla padre es eliminada, entonces se eliminarán las filas de la tabla hijo cuya clave foránea sea igual al valor de la clave referenciada en la tabla padre. Esta acción siempre ha estado disponible en MySQL. Si se especifica ON DELETE SET NULL, las filas en la tabla hijo son actualizadas automáticamente poniendo en las columnas de la clave foránea el valor NULL. Si se especifica una acción SET NULL, debemos asegurarnos de no declarar las columnas en la tabla como NOT NULL. A continuación se muestra un ejemplo de eliminación en cascada: mysql> ALTER TABLE venta ADD FOREIGN KEY(id_cliente)REFERENCES

cliente(id_cliente) ON DELETE CASCADE;



Vamos a ver como están nuestros registros antes de ejecutar la sentencia DELETE: mysql> SELECT * FROM cliente;

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

| id_cliente | nombre |

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

| 1 | Juan Penas |

| 2 | Pepe el toro |

| 3 | Pepe pecas |

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


Ta

ller

de

Ba

se d

e D

ato

s

I S

C



mysql> SELECT * FROM venta;

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

| id_factura | id_cliente | cantidad |

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

| 1 | 1 | 23 |

| 2 | 3 | 39 |

| 3 | 2 | 81 |

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


Ahora eliminaremos a Pepe Pecas de la base de datos: mysql> DELETE FROM cliente WHERE id_cliente=3;



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


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

| 1 | 1 | 23 |

| 3 | 2 | 81 |

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


mysql> SELECT * FROM cliente;

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

| id_cliente | nombre |

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

| 1 | Juan Penas |

| 2 | Pepe el toro |

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


Con la eliminación en cascada, se ha eliminado el registro de la tabla venta al que estaba relacionado Pepe Pecas.

Actualización de registros con claves foráneas Estas opciones son muy similares cuando se ejecuta una sentencia UPDATE, en lugar de una sentencia DELETE. Estas son: ON UPDATE CASCADE ON UPDATE SET NULL ON UPDATE RESTRICT Vamos a ver un ejemplo, pero antes que nada, tenemos que eliminar la restricción de clave foránea (debemos usar el ID específico de nuestra tabla). mysql> ALTER TABLE venta DROP FOREIGN KEY 0_26;



Ta

ller

de

Ba

se d

e D

ato

s

I S

C



mysql> ALTER TABLE venta ADD FOREIGN KEY(id_cliente)REFERENCES

cliente(id_cliente) ON DELETE RESTRICT ON UPDATE CASCADE;



NOTA: Se debe especificar ON DELETE antes de ON UPDATE, ya que de otra manera se recibirá un error al definir la restricción. Ahora está lista la clave foránea para una actualización en cascada. Este es el ejemplo: mysql> SELECT * FROM venta;

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


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

| 1 | 1 | 23 |

| 3 | 2 | 81 |

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


mysql> UPDATE cliente SET id_cliente=10 WHERE id_cliente=1;


Rows matched: 1 Changed: 1 Warnings: 0


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


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

| 1 | 10 | 23 |

| 3 | 2 | 81 |

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


En este caso, al actualizar el valor de id_cliente en la tabla cliente, se actualiza de manera automática el valor de la clave foránea en la tabla venta. Esta es la actualización en cascada.

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



Un ejemplo más Observar y estudiar detenidamente el diagrama entidad/relación de la figura que se muestra a continuación.

Queda como ejercicio al lector verificar que a partir de este diagrama se genera un código SQL similar al mostrado a continuación, que nos sirve para la creación de las tablas con sus correspondientes definiciones de claves foráneas: Considerar que se desean hacer eliminaciones y actualizaciones en cascada, y que en la tabla poema_libro la clave primaria está formada por ambos campos (id_poema y id_libro).

CREATE TABLE libro (

id_libro INT NOT NULL,

titulo VARCHAR(100) NULL,

precio NUMERIC(5,2) NULL,

PRIMARY KEY(id_libro)

) TYPE=InnoDB;

CREATE TABLE escritor (

id_escritor INT NOT NULL,

nombre VARCHAR(30) NULL,

apellidos VARCHAR(40) NULL,

direccion VARCHAR(100) NULL,

PRIMARY KEY(id_escritor)

) TYPE=InnoDB;

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



CREATE TABLE poema (

id_poema INT NOT NULL,

id_escritor INT NOT NULL,

titulo VARCHAR(50) NULL,

contenido TEXT NULL,

PRIMARY KEY(id_poema),

INDEX(id_escritor),

FOREIGN KEY(id_escritor) REFERENCES escritor(id_escritor)

ON DELETE CASCADE ON UPDATE CASCADE

) TYPE=InnoDB;

CREATE TABLE poema_libro (

id_poema INT NOT NULL,

id_libro INT NOT NULL,

PRIMARY KEY(id_poema, id_libro),

INDEX (id_poema), INDEX(id_libro),

FOREIGN KEY(id_poema) REFERENCES poema(id_poema)

ON DELETE CASCADE ON UPDATE CASCADE,

FOREIGN KEY(id_libro) REFERENCES libro(id_libro)

ON DELETE CASCADE ON UPDATE CASCADE

) TYPE=InnoDB;

2.3 Creación de índices

Un índice es una estructura interna que el sistema puede usar para encontrar uno o más registros en una tabla de forma rápida. En efecto, un índice de base de datos es, conceptualmente, similar a un índice encontrado al final de cualquier libro de texto. De la misma forma que el lector de un libro acudiría a un índice para determinar en qué páginas se encuentra un determinado tema, un sistema de base de datos leerá un índice para determinar las posiciones de registros seleccionados por una consulta SQL. En otras palabras, la presencia de un índice puede ayudar al sistema a procesar algunas consultas de un modo más eficiente. Un índice de base de datos se crea para una columna o grupo de columnas. La figura siguiente muestra un índice (XCNOMBRE) basado en la columna CNOMBRE de la tabla CURSO. Observemos que el índice, a diferencia de la tabla CURSO, representa valores CNOMBRE en orden. Además, el índice es pequeño en relación con el tamaño de la tabla. Por lo tanto, es, probablemente, más fácil que el sistema busque el índice para localizar un registro con un valor CNOMBRE dado, a que explore toda la tabla en busca de ese valor. Por ejemplo, el índice XCNOMBRE podría ser muy útil al sistema cuando ejecute la siguiente sentencia SELECT.

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



INDICE XCNOMBRE TABLA CURSO

VENTAJAS DE LOS INDICES:

Acceso directo a un registro especificado Ordenación

DESVENTAJAS:

Espacio de disco usado por el índice Costos de actualización

Tenemos tres tipos de índices. El primero corresponde a las claves primarias, que como vimos, también se pueden crear en la parte de definición de columnas. La sintaxis para definir claves primarias es: definición_columnas | PRIMARY KEY (index_nombre_col,...) mysql> CREATE TABLE ciudad4 (nombre CHAR(20) NOT NULL, poblacion INT NULL DEFAULT 5000, PRIMARY KEY (nombre));

Pero esta forma tiene más opciones, por ejemplo, entre los paréntesis podemos especificar varios nombres de columnas, para construir claves primarias compuestas por varias columnas: mysql> CREATE TABLE mitabla1 (id1 CHAR(2) NOT NULL, id2 CHAR(2) NOT NULL, texto CHAR(30),PRIMARY KEY (id1, id2));

El segundo tipo de índice permite definir índices sobre una columna, sobre varias, o sobre partes de columnas. Para definir estos índices se usan indistintamente las opciones KEY o INDEX. mysql> CREATE TABLE mitabla2(id INT, nombre CHAR(19), INDEX (nombre)) ;

COMUNISMO

ARQ. ORD ENADOR ES

ESTRUC TURA DE DATOS

CIRCUTOS DIGITALES

MAT. D ISCRETAS

EMPIRISM O

EXISTENC IALISM O

HEDONISMO

INTRODUC. A LOS CC .

RACIONAL ISMO

BASE D E DATOS R EL

ESCOLASTICISMO

SOLIPSISMO

CNO CNOMBRE

C22 ESTRUCTURA DE DATOS

T44 COMUNISMO

C55 ARQ. ORDENADORES

C33 MAT. DISCRETAS

P11 EMPIRISMO

T33 HEDONISMO

P33 EXISTENCIALISMO

C44 CIRCUTOS DIGITALES

T12 FUNDAMENTALISMO

C11 INTRODUC. A LOS CC.

P22 RACIONALISMO

P44 SOLIPSISMO

T11 ESCOLASTICISMO

C66 BASE DE DATOS REL

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



O su equivalente: mysql> CREATE TABLE mitabla3(id INT, nombre CHAR(19), KEY (nombre));

También podemos crear un índice sobre parte de una columna: mysql> CREATE TABLE mitabla4(id INT, nombre CHAR(19), INDEX (nombre(4)));

Este ejemplo usará sólo los cuatro primeros caracteres de la columna 'nombre' para crear el índice. El tercero permite definir índices con claves únicas, también sobre una columna, sobre varias o sobre partes de columnas. Para definir índices con claves únicas se usa la opción UNIQUE. La diferencia entre un índice único y uno normal es que en los únicos no se permite la inserción de filas con claves repetidas. La excepción es el valor NULL, que sí se puede repetir. mysql> CREATE TABLE mitabla5 (id INT, nombre CHAR(19), UNIQUE (nombre));

Una clave primaria equivale a un índice de clave única, en la que el valor de la clave no puede tomar valores NULL. Tanto los índices normales como los de claves únicas sí pueden tomar valores NULL. Por lo tanto, las definiciones siguientes son equivalentes: mysql> CREATE TABLE mitabla6(id INT, nombre CHAR(19) NOT NULL, UNIQUE (nombre));

mysql> CREATE TABLE mitabla7(id INT, nombre CHAR(19), PRIMARY KEY (nombre));

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



Recurso Cuatro:

Tipos de datos en Mysql Al diseñar nuestras tablas tenemos que especificar el tipo de datos y tamaño que podrá almacenar cada campo. Básicamente mysql admite dos tipos de datos: números y cadenas de caracteres. Junto a estos dos grandes grupos, se admiten otros tipos de datos especiales: formatos de fecha, etc.

Datos Numéricos: En este tipo de campos solo pueden almacenarse números, positivos o negativos, enteros o decimales, en notación hexadecimal, científica o decimal.

Los tipos numéricos tipo integer admiten los atributos SIGNED y UNSIGNED indicando en el primer caso que pueden tener valor negativo, y solo positivo en el segundo. Los tipos numéricos pueden además usar el atributo ZEROFILL en cuyo caso los números se completaran hasta la máxima anchura disponible con ceros (column age INT(5) zerofill => valor 23 se almacenará como 00023) BIT o BOOL, para un número entero que puede ser 0 ó 1 TINYINT es un número entero con rango de valores válidos desde -128 a 127. Si se configura como unsigned (sin signo), el rango de valores es de 0 a 255 SMALLINT, para números enteros, con rango desde -32768 a 32767. Si se configura como unsigned, 0 a 65535. MEDIUMINT para números enteros; el rango de valores va desde -8.388608 a 8388607. Si se configura como unsigned, 0 a 16777215 INT para almacenar números enteros, en un rango de -2147463846 a 2147483647. Si configuramos este dato como unsigned, el rango es 0 a 4294967295 BIGINT número entero con rango de valores desde -9223372036854775808 a 9223372036854775807. Unsigned, desde 0 a 18446744073709551615. FLOAT (m,d) representa números decimales. Podemos especificar cuantos dígitos (m) pueden utilizarse (término también conocido como ancho de pantalla), y cuantos en la parte decimal (d). Mysql redondeará el decimal para ajustarse a la capacidad.

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



DOUBLE Número de coma flotante de precisión doble. Es un tipo de datos igual al anterior cuya única diferencia es el rango numérico que abarca DECIMAL almacena los números como cadenas.

Caracteres O Cadenas

CHAR Este tipo se utiliza para almacenar cadenas de longitud fija. Su longitud abarca desde 1 a 255 caracteres. VARCHAR Al igual que el anterior se utiliza para almacenar cadenas, en el mismo rango de 1-255 caracteres, pero en este caso, de longitud variable. Un campo CHAR ocupará siempre el máximo de longitud que le hallamos asignado, aunque el tamaño del dato sea menor (añadiendo espacios adicionales que sean precisos). Mientras que VARCHAR solo almacena la longitud del dato, permitiendo que el tamaño de la base de datos sea menor. Eso si, el acceso a los datos CHAR es más rápido que VARCHAR. No pueden alternarse columnas CHAR y VARCHAR en la misma tabla. Mysql cambiará las columnas CHAR a VARCHAR. También cambia automáticamente a CHAR si usamos VARCHAR con valor de 4 o menos. TINYTEXT, TINYBLOB para un máximo de 255 caracteres. La diferencia entre la familia de datatypes text y blob es que la primera es para cadenas de texto plano (sin formato) y case -insensitive (sin distinguir mayúsculas o minúsculas) mientras que blob se usa para objetos binarios: cualquier tipo de datos o información, desde un archivo de texto con todo su formato (se diferencia en esto de el tipo Text) hasta imágenes, archivos de sonido o video TEXT y BLOB se usa para cadenas con un rango de 255 - 65535 caracteres. La diferencia entre ambos es que TEXT permite comparar dentro de su contenido sin distinguir mayúsculas y minúsculas, y BLOB si distingue. MEDIUMTEXT, MEDIUMBLOB textos de hasta 16777215 caracteres. LONGTEXT, LONGBLOB, hasta máximo de 4.294.967.295 caracteres

Varios

DATE para almacenar fechas. El formato por defecto es YYYY MM DD desde 0000 00 00 a 9999 12 31. DATETIME Combinación de fecha y hora. El rango de valores va desde el 1 de enero del 1001 a

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



las 0 horas, 0 minutos y 0 segundos al 31 de diciembre del 9999 a las 23 horas, 59 minutos y 59 segundos. El formato de almacenamiento es de año-mes-dia horas:minutos:segundos TIMESTAMP Combinación de fecha y hora. El rango va desde el 1 de enero de 1970 al año 2037. El formato de almacenamiento depende del tamaño del campo TIME almacena una hora. El rango de horas va desde -838 horas, 59 minutos y 59 segundos a 838, 59 minutos y 59 segundos. El formato de almacenamiento es de 'HH:MM:SS' YEAR almacena un año. El rango de valores permitidos va desde el año 1901 al año 2155. El campo puede tener tamaño dos o tamaño 4 dependiendo de si queremos almacenar el año con dos o cuatro dígitos. SET un campo que puede contener ninguno, uno ó varios valores de una lista. La lista puede tener un máximo de 64 valores. ENUM es igual que SET, pero solo se puede almacenar uno de los valores de la lista

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



Recurso Cinco:

Ejercicios de práctica Realizar la siguiente base de datos llamada BIBLIOTECA Libro(ClaveLibro, Título, Idioma, Formato, Categoría, ClaveEditorial) Tema(ClaveTema, Nombre) Autor(ClaveAutor, Nombre) Editorial(ClaveEditorial, Nombre, Dirección, Teléfono) Ejemplar(ClaveLibro, NúmeroOrden, Edición, Ubicación) Socio(ClaveSocio, Nombre, Dirección, Teléfono, Categoría) Préstamo(ClaveSocio, ClaveLibro, NúmeroOrden, Fecha_préstamo, Fecha_devolución, Notas) Trata_sobre(ClaveLibro, ClaveTema) Escrito_por(ClaveLibro, ClaveAutor) los tipos para las columnas:

Columna Tipo

ClaveLibro INT

Ti tulo VARCHAR(60)

Idioma VARCHAR(15) Formato VARCHAR(15)

Categoria(libro) CHAR

ClaveTema SMALLINT

Nombre(tema) VARCHAR(40)

ClaveAutor INT

Nombre(autor) VARCHAR(60)

ClaveEditorial SMALLINT

Nombre(editorial) VARCHAR(60)

Direccion(editorial) VARCHAR(60) Telefono(editorial) VARCHAR(15)

NumeroOrden SMALLINT

Edicion SMALLINT

Ubicacion VARCHAR(15)

ClaveSocio INT

Nombre(socio) VARCHAR(60)

Direccion(socio) VARCHAR(60) Telefono(socio) VARCHAR(15)

Categoria(socio) CHAR

Fecha_prestamo DATE

Fecha_devolucion DATE

Notas BLOB

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



EJERC. 2: Realizar la siguiente base de datos llamada EMPRESA

Examen 50%( 2 partes. 1parte el lunes 20%, 2ª parte el martes 30%)

Ejercicios de tareas 15%

Ejercicios de practica 15%

Practicas de laboratorio 20%

Examen el dia lunes 6 de octubre

Entrega de ejercicios lunes 6

Entrega de ejercicios de practica lunes 6

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



UNIDAD TRES Consultas y Lenguaje de Manipulación de Datos (DML)

Objetivo Educacional: Consultará y manipulará los datos de una base de datos.

3.1 Instrucciones INSERT, UPDATE, DELETE.

o Insert La forma más directa de insertar una fila nueva en una tabla es mediante una sentencia

INSERT. En la forma más simple de esta sentencia debemos indicar la tabla a la que queremos

añadir filas, y los valores de cada columna. Las columnas de tipo cadena o fechas deben estar

entre comillas sencillas o dobles, para las columnas numéricas esto no es imprescindible,

aunque también pueden estar entrecomilladas.

mysql> INSERT INTO gente VALUES ('Fulano','1974-04-12');


mysql> INSERT INTO gente VALUES ('Mengano','1978-06-15');


mysql> INSERT INTO gente VALUES('Tulano','2000-12-02'),('Pegano','1993-02-10');



mysql> SELECT * FROM gente;

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

| nombre | fecha |

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

| Fulano | 1974-04-12 |

| Mengano | 1978-06-15 |

| Tulano | 2000-12-02 |

| Pegano | 1993-02-10 |

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


Si no necesitamos asignar un valor concreto para alguna columna, podemos asignarle el

valor por defecto indicado para esa columna cuando se creó la tabla, usando la palabra

DEFAULT:

mysql> INSERT INTO ciudad2 VALUES ('Perillo', DEFAULT);


Ta

ller

de

Ba

se d

e D

ato

s

I S

C



mysql> SELECT * FROM ciudad2;

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

| nombre | poblacion |

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

| Perillo | 5000 |

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


En este caso, como habíamos definido un valor por defecto para población de 5000, se

asignará ese valor para la fila correspondiente a 'Perillo'.

Otra opción consiste en indicar una lista de columnas para las que se van a suministrar

valores. A las columnas que no se nombren en esa lista se les asigna el valor por defecto. Este

sistema, además, permite usar cualquier orden en las columnas, con la ventaja, con respecto a

la anterior forma, de que no necesitamos conocer el orden de las columnas en la tabla pa ra

poder insertar datos:

mysql> INSERT INTO ciudad5 (poblacion,nombre) VALUES (7000000, 'Madrid'), (9000000,

'París'), (3500000, 'Berlín');




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

| clave | nombre | poblacion |

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

| 1 | Madrid | 7000000 |

| 2 | París | 9000000 |

| 3 | Berlín | 3500000 |

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


Cuando creamos la tabla "ciudad5" definimos tres columnas: 'clave', 'nombre' y

'poblacion' (por ese orden). Ahora hemos insertado tres filas, en las que hemos omitido la clave,

y hemos alterado el orden de 'nombre' y 'poblacion'. El valor de la 'clave' se calcula

automáticamente, ya que lo hemos definido como auto-incrementado.

Existe otra sintaxis alternativa, que consiste en indicar el valor para cada columna:

mysql> INSERT INTO ciudad5 SET nombre='Roma', poblacion=8000000;



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


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

| 1 | Madrid | 7000000 |

| 2 | París | 9000000 |

| 3 | Berlín | 3500000 |

| 4 | Roma | 8000000 |

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


Ta

ller

de

Ba

se d

e D

ato

s

I S

C



Una vez más, a las columnas para las que no indiquemos valores se les asignarán sus

valores por defecto. También podemos hacer esto usando el valor DEFAULT.

Para las sintaxis que lo permiten, podemos observar que cuando se inserta más de una

fila en una única sentencia, obtenemos un mensaje desde MySQL que indica el número de filas

afectadas, el número de filas duplicadas y el número de avisos. Para que una fila se considere

duplicada debe tener el mismo valor que una fila existente para una clave principal o para una

clave única. En tablas en las que no exista clave primaria ni índices de clave única no tiene

sentido hablar de filas duplicadas. Es más, en esas tablas es perfectamente posible que existan

filas con los mismos valores para todas las columnas.

Por ejemplo, en mitabla5 tenemos una clave única sobre la columna 'nombre':

mysql> INSERT INTO mitabla5 (id, nombre) VALUES (1, 'Carlos'), (2, 'Felipe'),(3,

'Antonio'),(4, 'Carlos'),(5, 'Juan');

ERROR 1062 (23000): Duplicate entry 'Carlos' for key 1

Si intentamos insertar dos filas con el mismo valor de la clave única se produce un error

y la sentencia no se ejecuta. Pero existe una opción que podemos usar para los casos de claves

duplicadas: ON DUPLICATE KEY UPDATE. En este caso podemos indicar a MySQL qué debe

hacer si se intenta insertar una fila que ya existe en la tabla. Las opciones son limitadas: no

podemos insertar la nueva fila, sino únicamente modificar la que ya existe. Por ejemplo, en la

tabla 'ciudad3' podemos usar el último valor de población en caso de repetición:

mysql> INSERT INTO ciudad3 (nombre, poblacion) VALUES('Madrid', 7000000);


mysql> INSERT INTO ciudad3 (nombre, poblacion) VALUES

('París', 9000000),

('Madrid', 7200000)

ON DUPLICATE KEY UPDATE poblacion=VALUES(poblacion);




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


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

| Madrid | 7200000 |

| París | 9000000 |

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


En este ejemplo, la segunda vez que intentamos insertar la fila correspondiente a

'Madrid' se usará el nuevo valor de población. Si en lugar de VALUES(poblacion) usamos

población el nuevo valor de población se ignora. También podemos usar cualquier expresión:

Ta

ller

de

Ba

se d

e D

ato

s

I S

C




-> ('París', 9100000)

-> ON DUPLICATE KEY UPDATE poblacion=poblacion;



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


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

| Madrid | 7200000 |

| París | 9000000 |

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



-> ('París', 9100000)

-> ON DUPLICATE KEY UPDATE poblacion=0;



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


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

| Madrid | 7200000 |

| París | 0 |

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


o Update

Podemos modificar valores de las filas de una tabla usando la sentencia UPDATE. En su

forma más simple, los cambios se aplican a todas las filas, y a las columnas que especifiquemos.

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition] [ORDER BY ...] [LIMIT row_count]

Por ejemplo, podemos aumentar en un 10% la población de todas las ciudades de la

tabla ciudad3 usando esta sentencia:

mysql> UPDATE ciudad3 SET poblacion=poblacion*1.10;




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


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

| Berlín | 6600000 |

| Londres | 11000000 |

| Madrid | 7920000 |

| París | 10120000 |

| Roma | 10450000 |

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

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



Podemos, del mismo modo, actualizar el valor de más de una columna, separándolas en

la sección SET mediante comas:

mysql> UPDATE ciudad5 SET clave=clave+10, poblacion=poblacion*0.97;




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


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

| 11 | Madrid | 6790000 |

| 12 | París | 8730000 |

| 13 | Berlín | 3395000 |

| 14 | Roma | 7760000 |

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


En este ejemplo hemos incrementado el valor de la columna 'clave' en 10 y disminuido

el de la columna 'poblacion' en un 3%, para todas las filas.

Pero no tenemos por qué actualizar todas las filas de la tabla. Podemos limitar el número

de filas afectadas de varias formas. La primera es mediante la cláusula WHERE. Usando e sta

cláusula podemos establecer una condición. Sólo las filas que cumplan esa condición serán

actualizadas:

mysql> UPDATE ciudad5 SET poblacion=poblacion*1.03 WHERE nombre='Roma';




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


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

| 11 | Madrid | 6790000 |

| 12 | París | 8730000 |

| 13 | Berlín | 3395000 |

| 14 | Roma | 7992800 |

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


En este caso sólo hemos aumentado la población de las ciudades cuyo nombre sea

'Roma'. Las condiciones pueden ser más complejas. Existen muchas funciones y operadores que

se pueden aplicar sobre cualquier tipo de columna, y también podemos usar operadores

booleanos como AND u OR. Veremos esto con más detalle en otros capítulos.

Otra forma de limitar el número de filas afectadas es usar la cláusula LIMIT. Esta

cláusula permite especificar el número de filas a modificar:

mysql> UPDATE ciudad5 SET clave=clave-10 LIMIT 2;



Ta

ller

de

Ba

se d

e D

ato

s

I S

C




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


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

| 1 | Madrid | 6790000 |

| 2 | París | 8730000 |

| 13 | Berlín | 3395000 |

| 14 | Roma | 7992800 |

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


En este ejemplo hemos decrementado en 10 unidades la columna clave de las dos

primeras filas.Esta cláusula se puede combinar con WHERE, de modo que sólo las 'n' primeras

filas que cumplan una determinada condición se modifiquen.

Sin embargo esto no es lo habitual, ya que, si no existen claves primarias o únicas, el

orden de las filas es arbitrario, no tiene sentido seleccionarlas usando sólo la cláusula LIMIT.

La cláusula LIMIT se suele asociar a la cláusula ORDER BY. Por ejemplo, si queremos

modificar la fila con la fecha más antigua de la tabla 'gente', usaremos esta sentencia:

mysql> UPDATE gente SET fecha="1985-04-12" ORDER BY fecha LIMIT 1;

Query OK, 1 row affected, 1 warning (0.03 sec)



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

| nombre | fecha |

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

| Fulano | 1985-04-12 |

| Mengano | 1978-06-15 |

| Tulano | 2000-12-02 |

| Pegano | 1993-02-10 |

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


Si queremos modificar la fila con la fecha más reciente, usaremos el orden inverso, es

decir, el descendente:

mysql> UPDATE gente SET fecha="2001-12-02" ORDER BY fecha DESC LIMIT 1;




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

| nombre | fecha |

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

| Fulano | 1985-04-12 |

| Mengano | 1978-06-15 |

| Tulano | 2001-12-02 |

| Pegano | 1993-02-10 |

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


Cuando exista una clave primaria o única, se usará ese orden por defecto, si no se

especifica una cláusula ORDER BY.

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



o Delete Para eliminar filas se usa la sentencia DELETE. La sintaxis es muy parecida a la de

UPDATE:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM table_name [WHERE where_definition] [ORDER BY ...] [LIMIT row_count]

La forma más simple es no usar ninguna de las cláusulas opcionales:

mysql> DELETE FROM ciudad3;


De este modo se eliminan todas las filas de la tabla.

Pero es más frecuente que sólo queramos eliminar ciertas filas que cumplan

determinadas condiciones. La forma más normal de hacer esto es usar la cláusula WHERE:

mysql> DELETE FROM ciudad5 WHERE clave=2;



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


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

| 1 | Madrid | 6790000 |

| 13 | Berlín | 3395000 |

| 14 | Roma | 7992800 |

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


También podemos usar las cláusulas LIMIT y ORDER BY del mismo modo que en la

sentencia UPDATE, por ejemplo, para eliminar las dos ciudades con más población:

mysql> DELETE FROM ciudad5 ORDER BY poblacion DESC LIMIT 2;



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


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

| 13 | Berlín | 3395000 |

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


Ta

ller

de

Ba

se d

e D

ato

s

I S

C



3.2 Consultas Básicas SELECT, WHERE y funciones a nivel de registro.

La sintaxis de SELECT es compleja. Una forma más general consiste en la siguiente

sintaxis:

SELECT [ALL | DISTINCT | DISTINCTROW] expresion_select,... FROM referencias_de_tablas WHERE condiciones [GROUP BY {nombre_col | expresion | posicion} [ASC | DESC], ... [WITH ROLLUP]] [HAVING condiciones] [ORDER BY {nombre_col | expresion | posicion} [ASC | DESC] ,...] [LIMIT {[desplazamiento,] contador | contador OFFSET desplazamiento}]

La forma más sencilla es la que hemos usado hasta ahora, consiste en pedir todas las columnas y no especificar condiciones.

mysql>mysql> SELECT * FROM gente;

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

| nombre | fecha |

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

| Fulano | 1985-04-12 |

| Mengano | 1978-06-15 |

| Tulano | 2001-12-02 |

| Pegano | 1993-02-10 |

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


Mediante la sentencia SELECT es posible hacer una proyección de una tabla,

seleccionando las columnas de las que queremos obtener datos. En la sintaxis que hemos

mostrado, la selección de columnas corresponde con la parte "expresion_select". En el ejemplo

anterior hemos usado '*', que quiere decir que se muestran todas las columnas.

Pero podemos usar una lista de columnas, y de ese modo sólo se mostrarán esas

columnas:

mysql> SELECT nombre FROM gente;

+---------+

| nombre |

+---------+

| Fulano |

| Mengano |

| Tulano |

| Pegano |

+---------+


Ta

ller

de

Ba

se d

e D

ato

s

I S

C



mysql> SELECT clave,poblacion FROM ciudad5;

Empty set (0.00 sec)

Las expresiones_select no se limitan a nombres de columnas de tablas, pueden ser otras

expresiones, incluso aunque no correspondan a ninguna tabla:

mysql> SELECT SIN(3.1416/2), 3+5, 7*4;

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

| SIN(3.1416/2) | 3+5 | 7*4 |

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

| 0.99999999999325 | 8 | 28 |

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


Vemos que podemos usar funciones, en este ejemplo hemos usando la función SIN para calcular el seno de

π/2.

También podemos aplicar funciones sobre columnas de tablas, y usar esas columnas en

expresiones para generar nuevas columnas:

mysql> SELECT nombre, fecha, DATEDIFF(CURRENT_DATE(),fecha)/365 FROM gente;

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

| nombre | fecha | DATEDIFF(CURRENT_DATE(),fecha)/365 |

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

| Fulano | 1985-04-12 | 19.91 |

| Mengano | 1978-06-15 | 26.74 |

| Tulano | 2001-12-02 | 3.26 |

| Pegano | 1993-02-10 | 12.07 |

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


Aprovechemos la ocasión para mencionar que también es posible asignar un alias a

cualquiera de las expresiones select. Esto se puede hacer usando la palabra AS, aunque esta

palabra es opcional:

mysql> SELECT nombre, fecha, DATEDIFF(CURRENT_DATE(),fecha)/365 AS edad

-> FROM gente;

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

| nombre | fecha | edad |

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

| Fulano | 1985-04-12 | 19.91 |

| Mengano | 1978-06-15 | 26.74 |

| Tulano | 2001-12-02 | 3.26 |

| Pegano | 1993-02-10 | 12.07 |

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


Podemos hacer:

mysql> SELECT 2+3 "2+3";

+-----+

| 2+3 |

+-----+

| 5 |

+-----+

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



Mostrar filas repetidas

Ya que podemos elegir sólo algunas de las columnas de una tabla, es posible que se

produzcan filas repetidas, debido a que hayamos excluido las columnas únicas. Por ejemplo,

añadamos las siguientes filas a nuestra tabla:

mysql> INSERT INTO gente VALUES ('Pimplano', '1978-06-15'),

-> ('Frutano', '1985-04-12');



mysql> SELECT fecha FROM gente;

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

| fecha |

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

| 1985-04-12 |

| 1978-06-15 |

| 2001-12-02 |

| 1993-02-10 |

| 1978-06-15 |

| 1985-04-12 |

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

6 rows in set (0.00 sec

Vemos que existen dos valores de filas repetidos, para la fecha "1985-04-12" y para

"1978-06-15". La sentencia que hemos usado asume el valor por defecto (ALL) para el grupo de

opciones ALL, DISTINCT y DISTINCTROW. En realidad sólo existen dos opciones, ya que las dos

últimas: DISTINCT y DISTINCTROW son sinónimos.

La otra alternativa es usar DISTINCT, que hará que sólo se muestren las filas diferentes:

mysql> SELECT DISTINCT fecha FROM gente;

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

| fecha |

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

| 1985-04-12 |

| 1978-06-15 |

| 2001-12-02 |

| 1993-02-10 |

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


Otra de las operaciones del álgebra relacional era la selección, que consistía en

seleccionar filas de una relación que cumplieran determinadas condiciones.

Lo que es más útil de una base de datos es la posibilidad de hacer consultas en función

de ciertas condiciones. Generalmente nos interesará saber qué filas se ajustan a determinados

parámetros. Por supuesto, SELECT permite usar condiciones como parte de su sintaxis, es

decir, para hacer selecciones. Concretamente mediante la cláusula WHERE, veamos algunos

ejemplos:

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



mysql> SELECT * FROM gente WHERE nombre="Mengano";

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

| nombre | fecha |

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

| Mengano | 1978-06-15 |

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


mysql> SELECT * FROM gente WHERE fecha>="1986-01-01";

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

| nombre | fecha |

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

| Tulano | 2001-12-02 |

| Pegano | 1993-02-10 |

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


mysql> SELECT * FROM gente WHERE fecha>="1986-01-01" AND fecha < "2000-01-01";

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

| nombre | fecha |

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

| Pegano | 1993-02-10 |

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


Mas ejemplos con el select:

Select Nombre, edad, Alojamiento from empleado order by edad;

Select Nombre, edad, Alojamiento from empleado order by edad ASC;

Select Nombre, edad, Alojamiento from empleado order by edad DESC;

Select Nombre, edad, Alojamiento from empleado group by Edad, Alojamiento;

Nombre Edad Alojamiento

Diaz Jiménez 18 Barajas

Alonso Tellez 23 Toledo

Select distinct Alojamiento From empleado;

Select * from curso where ctarifa <150;

Select * from curso where cnombre>=”Racionalismo”;

Select ciudad, ventas, objetivo from oficinas where ventas > objetivo;

Select nombre, ventas, cuota from repventas where numempl= 105;

Select * from personal order by enombre

Select dept, cargo, esueldo from personal order by dept, esueldo desc;

Select ciudad, regios, ventas from oficinas order by region, ciudad;

Renombrando columnas:

Select ciudad, region, ventas-objetivo as SUPERHABIT from oficinas;

Select ciudad, region, ventas-objetivo “SUPERHABIT” from oficinas;

Con condiciones:

Select nombre from repventas where contrato<”1988-01-01”;

Select * from oficinas where ventas<0.8*objetivo;

Select * from oficinas where dir<>108;

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



Between

Select cnombre, cno, cdescrip from curso where cnombre between “ES” and “ESZ”

Select * from pedidos where fechapedido between “1989-10-01” and “1989-12-31”;

Select cno, ctarifa from curso where ctarifa not between 50 and 400;

In

Select * from curso where ctarifa in(12,50,75,90,100,500);

Select * from curso where ctarifa not in(12,50,75,90,100,500);

Select * from repventas where oficinarep in (11,13,22);

Select numpedido, fechapedido, importe from pedidos where fechapedido not in(“1990-

01-04”, “1990-01-11”, “1990-01-18”, “1990-01-05”);

Like

Select * from curso where cdesp like “pa%”

Select empresa, limitecredito from clientes where empresa like “s%”

Select * from clientes where limitecredito not like “20000”;

Select * from clientes where limitecredito <> “20000”;

Is null

Select fnombre, fnumdep, fdept from claustro where fnumdep is not null

Select nombre from ventas where oficinarep is not null

UNION

Permite combinar resultados de dos o más consultas en una única tabla. Por ejemplo,

visualizar todos los salarios de todo el personal y del claustro de la facultad en una única tabla:

Select esueldo from personal UNION select fsueldo from claustro;

Lista todos los productos cuyo precio unitario excede de 2000 o con importe de pedido

superior a 30000

Select idfab, idproducto from productos where precio >2000 UNION select distinct

fab, producto from pedidos where importe > 30000;

3.3 Consultas sobre múltiples tablas.

Hasta ahora todas las consultas que hemos usado se refieren sólo a una tabla, pero

también es posible hacer consultas usando varias tablas en la misma sentencia SELECT. Esto

nos permite realizar otras dos operaciones de álgebra relacional: el producto cartesiano y la

composición.

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



Insertemos:

mysql> INSERT INTO personas2 (nombre, fecha) VALUES ("Fulanito", "1956-12-

14"),("Menganito", "1975-10-15"),("Tulanita", "1985-03-17"),("Fusganita", "1976-08-25");



mysql> SELECT * FROM personas2;

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

| id | nombre | fecha |

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

| 1 | Fulanito | 1956-12-14 |

| 2 | Menganito | 1975-10-15 |

| 3 | Tulanita | 1985-03-17 |

| 4 | Fusganita | 1976-08-25 |

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


mysql> INSERT INTO telefonos2 (id, numero) VALUES (1, "123456789"),(1, "145654854"),(1,

"152452545"),(2, "254254254"),(4, "456545654"),(4, "441415414");



mysql> SELECT * FROM telefonos2;

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

| numero | persona |

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

| 123456789 | 1 |

| 145654854 | 1 |

| 152452545 | 1 |

| 254254254 | 2 |

| 456545654 | 4 |

| 441415414 | 4 |

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


El producto cartesiano de dos tablas son todas las combinaciones de todas las filas de las

dos tablas. Usando una sentencia SELECT se hace proyectando todos los atributos de ambas

tablas. Los nombres de las tablas se indican en la cláusula FROM separados con comas:

mysql> SELECT * FROM personas2,telefonos2;

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

| id | nombre | fecha | numero | id |

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

| 1 | Fulanito | 1956-12-14 | 123456789 | 1 |

| 2 | Menganito | 1975-10-15 | 123456789 | 1 |

| 3 | Tulanita | 1985-03-17 | 123456789 | 1 |

| 4 | Fusganita | 1976-08-25 | 123456789 | 1 |

| 1 | Fulanito | 1956-12-14 | 145654854 | 1 |

| 2 | Menganito | 1975-10-15 | 145654854 | 1 |

| 3 | Tulanita | 1985-03-17 | 145654854 | 1 |

| 4 | Fusganita | 1976-08-25 | 145654854 | 1 |

| 1 | Fulanito | 1956-12-14 | 152452545 | 1 |

| 2 | Menganito | 1975-10-15 | 152452545 | 1 |

| 3 | Tulanita | 1985-03-17 | 152452545 | 1 |

| 4 | Fusganita | 1976-08-25 | 152452545 | 1 |

| 1 | Fulanito | 1956-12-14 | 254254254 | 2 |

| 2 | Menganito | 1975-10-15 | 254254254 | 2 |

| 3 | Tulanita | 1985-03-17 | 254254254 | 2 |

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



| 4 | Fusganita | 1976-08-25 | 254254254 | 2 |

| 1 | Fulanito | 1956-12-14 | 456545654 | 4 |

| 2 | Menganito | 1975-10-15 | 456545654 | 4 |

| 3 | Tulanita | 1985-03-17 | 456545654 | 4 |

| 4 | Fusganita | 1976-08-25 | 456545654 | 4 |

| 1 | Fulanito | 1956-12-14 | 441415414 | 4 |

| 2 | Menganito | 1975-10-15 | 441415414 | 4 |

| 3 | Tulanita | 1985-03-17 | 441415414 | 4 |

| 4 | Fusganita | 1976-08-25 | 441415414 | 4 |

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


Las consultas multitablas o JOINS, también denominadas combinaciones o

composiciones, permiten recuperar datos de dos tablas o mas según las relaciones lógicas

entre ellas.

Ejemplo:

Combina tabla pedido y clientes (que se separan por coma en la cláusula from)

mostrando el numero de pedido y su importe y el numero de cliente y su límite de credito,

utilizando para la combinación la columna clie de la tabla pedidos y la columna numclie de la

tabla clientes:

Select numpedido, importe, empresa, limitecredito from pedidos, clientes where

clie=numclie;

O también(cuando hay duplicación de nombre en los campos de la tabla):

Select pedidos.numpedido, pedidos.importe, clientes. Empresa, clientes.limitecredito from

pedidos, clientes where clie=numclie;

3.3.1 Subconsultas.

Una subconsulta es una consulta dentro de otra. El SGBD usa los resultados de la

subconsulta para determinar los resultados de la consulta de alto nivel que contiene a la

subconsulta. En las formas mas simples de una subconsulta, esta aparece dentro de una

clausula WHERE o HAVING de otra instrucción SQL. Las subconsultas proporcionan una forma

natural y eficiente de manejar las solicitudes de consultas que se expresan en términos de los

resultados de otras.

Listar las oficinas en las que el objetivo de ventas exceda la suma de las cuotas de

cada representante.

La solicitud pide una lista de oficinas de la tabla OFICINAS donde el valor de la columna

OBJETIVO cumple alguna condición. Parece razonable que la instrucción SELECT que expresa la

consulta pueda ser como:

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



Select ciudad from oficinas where objetivo ???

El valor ??? se debe rellenar y debe ser igual a la suma de las cuotas de los

representantes asignados a la oficina en cuestión. ¿Cómo puede especificarse ese valor en la

consulta? (suponga que el numero de oficina es 21) entonces la consulta para la suma de las

cuotas de una oficina quedaría de la siguiente manera.

Select sum(cuota) from representantes where oficina_rep=21

Pero sería poco eficaz tener que escribir esta consulta, escribir los resultados y escribir

en la consulta anterior la cantidad correcta. ¿Cómo se pueden poner los resultados de esta

consulta en la consulta anterior en lugar de los signos de interrogación? Así:

Select ciudad from oficinas where objetivo > (Select sum(cuota) from repventas

where oficinarep=oficina)

Para cada oficina, la consulta interna(la subconsulta) calcula la suma de las cuotas de los

representantes que trabajan en esa oficina. La consulta externa (la consulta principal) compara

el objetivo de la oficina con el total calculado y decide si añade la oficina a los resultados de la

consulta principal. Al trabajar juntas, la consulta principal y la subconsulta expresan la solicitud

original y recuperan los datos solicitados de la base de datos.

CONCEPTO DE SUBCONSULTA

La subconsulta se encierra entre paréntesis; sin embargo tiene la forma familiar de una

instrucción SELECT, con una clausula FROM y clausulas opcionales WHERE, GROUP BY y

HAVING

Comparación en subconsultas

Ejemplos:

1. Listar los representantes cuyas cuotas son iguales o superiores al objetivo de la

oficina de ventas de Almería

Select nombre from repventas where cuota >= (select objetivo from oficinas where

ciudad= “los angeles” and oficinarep=oficina);

2. Listar todos los clientes a los que sirve Bruno Arteaga

Select empresa from clientes where repcli = (select numempl from repventas where

nombre =”peter brothers”);

3. Listar todos los productos del fabricante ACI para los que su Stock esta por encima del

stock del producto ACI-41004

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



Select descripción, stock from productos where id_fab = “ACI” and stock > (select

stock from productos where id_fab=”ACI” and id_producto= “41004”)

Pertenencias a conjuntos (IN)

El test de pertenencia a conjuntos (IN) en subconsultas es una forma modificada del test

de pertenencia a conjuntos simples. Compara un unico valor de datos con una columna de

valores de datos producidos por una subconsulta y devuelve un resultado TRUE si el valor de

los datos coincide con uno de los valores de la columna. Este test se usa cuando es necesario

comparar un valor de la fila que se está comprobando con un conjunto de valores producido

por una subconsulta.

Ejemplo:

Listar todos los representantes que trabajan en oficinas que están por encima de sus

objetivos

Select nombre from representantes where oficina_rep in (select oficina from

oficinas where ventas > obejtivo)

La subconsulta produce un conjunto de números de oficina donde las ventas son

superiores a sus objetivos. La consulta principal comprueba a continuación cada fila de la tabla

representantes para determinar si un representante en concreto trabaja en una oficina con uno

de estos números

Listar los representantes que no trabajan en oficinas dirigidas por Leon Freire

(empleado 108)

Select nombre from representantes where oficina_rep not in (select oficina from

oficinas where jef = 108)

Listar todos los clientes que han formulado pedidos de zapatos de ACI(fabricante ACI,

numeros de producto comienzan en 4100) entre enero y junio de 1990

Select empresa from clientes where num_cli in (select distinct cliente from pedidos

where fab= “ACI” and producto like “4100%” and fecha_pedido between “01-ene- 90” and “30-

jun-90”)

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



TABLAS USADAS EN LOS EJEMPLOS

Listar los representantes cuyas cuotas son iguales o superiores al objetivo de la oficina

de ventas de los ángeles

Tabla OFICINAS

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

| Oficina | Ciudad | Region | Dir | Objetivo | Ventas |

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

| 12 | Chicago | Este | 104 | 800000 | 735042 |

| 13 | Atlanta | Este | 105 | 350000 | 367911 |

| 21 | Los angeles | Oeste | 108 | 725000 | 835915 |

| 22 | Denver | Oeste | 108 | 300000 | 186042 |

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

Tabla REPVENTAS

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

| Numempl | Nombre | Edad | Oficinarep | Titulo | Contrato | Director | Cuota | Ventas |

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

| 101 | Dan Roberts | 45 | 11 | Rep Ventas | 1986-10-20 | 104 | 257143 | 305673 |

| 102 | Sue Smith | 48 | 21 | Rep Ventas | 1986-12-12 | 108 | 333333 | 474050 |

| 103 | Paul Cruz | 29 | 11 | Rep Ventas | 1987-03-01 | 104 | 235715 | 286775 |

| 104 | Bob Smith | 33 | 11 | Dir Ventas | 1987-05-19 | 106 | 171428 | 142594 |

| 105 | Bill Adams | 37 | 13 | Rep Ventas | 1988-02-12 | 104 | 333333 | 367911 |

| 107 | Nancy Angelli | 49 | 22 | Rep Ventas | 1988-11-14 | 108 | 285714 | 186042 |

| 108 | Larry Fitch | 62 | 21 | Dir Ventas | 1989-10-12 | 106 | 333333 | 361865 |

| 109 | Mary Jones | 31 | 11 | Rep Ventas | 1999-10-12 | 106 | 285714 | 392725 |

| 11 | Matías Clark | 35 | 15 | Dir Ventas | 1990-07-25 | 108 | 100000 | 0 |

| 0 | 35 | 11 | 0 | NULL | 0000-00-00 | 1990 | 103 | 15 |

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

Tabla CLIENTES

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

| Numclie | Empresa | Repclie | Limitecredito |

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

| 2101 | Jones Mfg. | 102 | 65000 |

| 2106 | Fred Lewis Corp. | 102 | 65000 |

| 2109 | Chen Associates | 103 | 25000 |

| 2111 | JCP Inc. | 103 | 50000 |

| 2112 | Zetacorp | 108 | 50000 |

| 2113 | Ian Schmidt | 104 | 20000 |

| 2114 | Orion Corp. | 102 | 20000 |

| 2117 | J.P. Sinclair | 102 | 35000 |

| 2118 | Midwest Systems | 108 | 60000 |

| 2120 | Rico Enterprises | 102 | 50000 |

| 2121 | QMA Assoc. | 103 | 45000 |

| 2123 | Carter Sons | 102 | 40000 |

| 2124 | Peter Brothers | 102 | 40000 |

| 2127 | Intercorp | 109 | 60000 |

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

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



Tabla PEDIDOS

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

| Numpedido | Fechapedido | Clie | Rep | Fab | Producto | Cant | Importe |

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

| 112963 | 1989-12-17 | 2103 | 105 | ACI | 41004 | 28 | 3276 |

| 112983 | 1989-12-27 | 2103 | 105 | ACI | 41004 | 6 | 702 |

| 112987 | 1989-12-31 | 2103 | 105 | ACI | 4100Y | 11 | 27500 |

| 112997 | 1990-01-08 | 2124 | 107 | BIC | 41003 | 1 | 652 |

| 113003 | 1990-01-25 | 2108 | 109 | IMM | 779C | 3 | 5625 |

| 113007 | 1990-01-08 | 2112 | 108 | IMM | 773C | 3 | 2925 |

| 113012 | 1990-01-11 | 2111 | 105 | ACI | 41003 | 35 | 3745 |

| 113013 | 1990-01-14 | 2118 | 108 | BIC | 41003 | 1 | 652 |

| 113024 | 1990-01-20 | 2114 | 108 | QSA | XK47 | 20 | 7100 |

| 113027 | 1990-01-22 | 2103 | 105 | ACI | 41002 | 54 | 4104 |

| 113042 | 1990-02-02 | 2113 | 101 | REI | 2A44R | 5 | 22500 |

| 113045 | 1990-02-02 | 2112 | 108 | REI | 2A44R | 10 | 45000 |

| 113048 | 1990-02-10 | 2120 | 102 | IMM | 779C | 2 | 3750 |

| 113049 | 1990-02-10 | 2118 | 108 | QSA | XK47 | 6 | 2130 |

| 113051 | 1990-02-10 | 2118 | 108 | QSA | XK47 | 4 | 1420 |

| 113055 | 1990-02-15 | 2108 | 101 | ACI | 4100X | 6 | 150 |

| 113057 | 1990-02-18 | 2111 | 103 | ACI | 4100X | 24 | 600 |

| 113058 | 1990-02-23 | 2108 | 109 | FEA | 112 | 10 | 1480 |

| 113062 | 1990-02-24 | 2124 | 107 | FEA | 114 | 10 | 2430 |

| 113065 | 1990-02-27 | 2106 | 102 | QSA | XK47 | 6 | 2130 |

| 113069 | 1990-03-02 | 2109 | 107 | IMM | 775C | 22 | 31350 |

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

Tabla PRODUCTOS

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

| Idfab | Idproducto | Descripcion | Precio | Existencias |

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

| ACI | 41001 | Artφculo Tipo 1 | 55 | 277 |




| ACI | 4100X | Ajustador | 25 | 37 |

| ACI | 4100Y | Extractor | 2750 | 25 |

| ACI | 4100Z | Montador | 2500 | 28 |

| BIC | 41003 | Manivela | 652 | 3 |

| BIC | 41089 | Retn | 225 | 78 |

| BIC | 41672 | Plate | 180 | 0 |

| FEA | 112 | Cubierta | 148 | 115 |

| FEA | 114 | Bancada Motor | 243 | 15 |

| IMM | 773C | Riostra 1/2 Tm | 975 | 28 |

| IMM | 775C | Riostra 1-Tm | 1425 | 5 |

| IMM | 779C | Riostra 2-Tm | 1875 | 9 |

| IMM | 878H | Soporte Riostra | 54 | 223 |

| IMM | 887P | Perno Riostra | 250 | 24 |

| IMM | 887X | Retenedor Riostra | 475 | 32 |

| QSA | XK47 | Reductor | 355 | 38 |

| QSA | XK48 | Reductor | 134 | 203 |

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



| QSA | XK48A | Reductor | 117 | 37 |

| REI | 2A44G | Pasador Bisagra | 350 | 14 |

| REI | 2A44L | Bisagra Izqda. | 4500 | 12 |

| REI | 2A44R | Bisagra Dcha. | 4500 | 12 |

| REI | 2A45C | V Stago Trinquete | 79 | 210 |

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

Obtener el nombre de la materia que cursa el alumno con número de control

97310211 con créditos igual a ocho.

SELECT NombreM FROM Materia WHERE creditos=‟8‟ and clave in(SELECT clave FROM cursa

WHERE NControl=‟97310211‟;

Obtener el número de control del alumno que tenga alguna calificación igual a 100

SELECT DISTINC(NControl) FROM Cursa WHERE Calif=‟100‟;

Obtener el nombre de las materias que cursa el alumno Salvador Chávez.

SELECT NombreM FROM Materia WHERE Clave in (SELECT DISTINC (Clave) FROM Cursa WHERE

NControl in (SELECT Ncontrol FROM Alumno WHERE NombreA=‟Salvador Chávez‟));

3.3.2 Operadores JOIN.

Un JOIN de dos tablas es una combinación entre las mismas basada en la coincidencia

exacta(u otro tipo de comparación) de dos columnas, una de cada tabla. El JOIN forma parejas

de filas haciendo coincidir los contenidos de las columnas relacionadas.

Ejemplo: Visualizar la información de las tablas curso y departamento para que los

valores de CDEPT en curso coinciden con los valores de DEPT en departamento;

Select * from curso, departamento where cdept=Dep;

Recordemos que se trata de un producto cartesiano restringido, las tuplas que se

emparejan deben cumplir una determinada condición.

En el álgebra relacional sólo hemos hablado de composiciones en general. Sin embargo,

en SQL se trabaja con varios tipos de composiciones.

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



Composiciones internas

Todas las composiciones que hemos visto hasta ahora se denominan composiciones

internas. Para hacer una composición interna se parte de un producto cartesiano y se eliminan

aquellas tuplas que no cumplen la condición de la composición.

En el ejemplo anterior tenemos 24 tuplas procedentes del producto cartesiano de las

tablas personas2 y teléfonos2. Si la condición para la composición es que

personas2.id=telefonos2.id, tendremos que eliminar todas las tuplas en que la condición no se

cumpla.

Estas composiciones se denominan internas porque en la salida no aparece ninguna

tupla que no esté presente en el producto cartesiano, es decir, la composición se hace en el

interior del producto cartesiano de las tablas.

Para consultar la sintaxis de las composiciones ver JOIN.

Las composiciones internas usan estas sintaxis:

referencia_tabla, referencia_tabla

referencia_tabla [INNER | CROSS] JOIN referencia_tabla [condición]

La condición puede ser:

ON expresión_condicional | USING (lista_columnas)

La coma y JOIN son equivalentes, y las palabras INNER y CROSS son opcionales.

La condición en la cláusula ON puede ser cualquier expresión válida para una cláusula

WHERE, de hecho, en la mayoría de los casos, son equivalentes.

La cláusula USING nos permite usar una lista de atributos que deben ser iguales en las

dos tablas a componer.

Siguiendo con el mismo ejemplo, la condición más lógica para la composición interna

entre personas2 y teléfonos2 es la igualdad entre el identificador de persona en la primera

tabla y el atributo persona en la segunda:

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



mysql> SELECT * FROM personas2, telefonos2 WHERE personas2.id=telefonos2.id;

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


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

| 1 | Fulanito | 1956-12-14 | 123456789 | 1 |

| 1 | Fulanito | 1956-12-14 | 145654854 | 1 |

| 1 | Fulanito | 1956-12-14 | 152452545 | 1 |

| 2 | Menganito | 1975-10-15 | 254254254 | 2 |

| 4 | Fusganita | 1976-08-25 | 456545654 | 4 |

| 4 | Fusganita | 1976-08-25 | 441415414 | 4 |

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


Esta consulta es equivalente a estas otras:

mysql> SELECT * FROM personas2 JOIN telefono2 ON (personas2.id = telefono2.id);

mysql> SELECT * FROM personas2 JOIN telefono2 WHERE (personas2.id =

telefono2.id);

mysql> SELECT * FROM personas2 INNER JOIN telefono2 ON (personas2.id =

telefono2.id);

mysql> SELECT * FROM personas2 CROSS JOIN telefono2 ON (personas2.id =

telefono2.id);

mysql> SELECT * FROM personas2 JOIN telefono2 USING(id);

En cualquier caso, la salida sólo contiene las tuplas que emparejan a personas con sus

números de teléfono. Las tuplas correspondientes a personas que no tienen ningún número no

aparecen, como por ejemplo las correspondientes a "Tulanita".

Para las personas con varios números, se repiten los datos de la persona para cada

número, por ejemplo con "Fulanito" o "Fusganita".

Composición interna natural

Consiste en una proyección sobre un producto cartesiano restringido. Es decir, sólo

elegimos determinadas columnas de ambas tablas, en lugar de seleccionar todas.

Podemos hacer esto a partir de una composición general, eligiendo todas las columnas

menos las repetidas:

mysql> SELECT personas2.id,nombre,fecha,numero FROM personas2, telefonos2 WHERE

personas2.id=telefonos2.id;

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

| id | nombre | fecha | numero |

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

| 1 | Fulanito | 1956-12-14 | 123456789 |

| 1 | Fulanito | 1956-12-14 | 145654854 |

| 1 | Fulanito | 1956-12-14 | 152452545 |

| 2 | Menganito | 1975-10-15 | 254254254 |

| 4 | Fusganita | 1976-08-25 | 456545654 |

| 4 | Fusganita | 1976-08-25 | 441415414 |

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



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


Como la columna id existe en ambas tablas estamos obligados a usar el nombre

completo para esta columna. En este caso hemos optado por personas2.id, pero hubiese sido

igual usar telefonos2.id.

También podemos definir alias para las tablas, y conseguir una consulta más compacta:

mysql> SELECT t1.id,nombre,fecha,numero FROM personas2 AS t1, telefonos2 AS t2

WHERE t1.id=t2.id;

Por supuesto, podemos usar JOIN y ON en lugar de la coma y WHERE:

mysql> SELECT t1.id,nombre,fecha,numero FROM personas2 AS t1 JOIN telefonos2 AS

t2 ON t1.id=t2.id;

Pero tenemos una sintaxis alternativa mucho mejor para hacer composiciones internas

naturales:

referencia_tabla NATURAL JOIN referencia_tabla

Por ejemplo:

mysql> SELECT * FROM personas2 NATURAL JOIN telefonos2;

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

| id | nombre | fecha | numero |

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

| 1 | Fulanito | 1956-12-14 | 123456789 |

| 1 | Fulanito | 1956-12-14 | 145654854 |

| 1 | Fulanito | 1956-12-14 | 152452545 |

| 2 | Menganito | 1975-10-15 | 254254254 |

| 4 | Fusganita | 1976-08-25 | 456545654 |

| 4 | Fusganita | 1976-08-25 | 441415414 |

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


Ta

ller

de

Ba

se d

e D

ato

s

I S

C



Composiciones externas

Al contrario que con las composiciones internas, las externas no proceden de un

producto cartesiano. Por lo tanto, en estas pueden aparecer tuplas que no aparecen en el

producto cartesiano.

Para hacer una composición externa se toman las tuplas de una de las tablas una a una y

se combinan con las tuplas de la otra.

Como norma general se usa un índice para localizar las tuplas de la segunda tabla que

cumplen la condición, y para cada tupla encontrada se añade una fila a la tabla de salida.

Si no existe ninguna tupla en la segunda tabla que cumpla las condiciones, se combina la

tupla de la primera con una nula de la segunda.

En nuestro ejemplo se tomaría la primera tupla de personas2, con un valor de id igual a

1, y se busca en la tabla telefonos2 las tuplas con un valor de id igual a 1. Lo mismo para la

segunda tupla, con id igual a 2.

En la tercera el id es 3, y no existe ninguna tupla en telefonos2 con un valor de id igual a

3, por lo tanto se combina la tupla de personas2 con una tupla de telefonos2 con todos los

atributos igual a NULL.

Por ejemplo:

mysql> SELECT * FROM personas2 LEFT JOIN telefonos2 USING(id);

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


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

| 1 | Fulanito | 1956-12-14 | 123456789 | 1 |

| 1 | Fulanito | 1956-12-14 | 145654854 | 1 |

| 1 | Fulanito | 1956-12-14 | 152452545 | 1 |

| 2 | Menganito | 1975-10-15 | 254254254 | 2 |

| 3 | Tulanita | 1985-03-17 | NULL | NULL | (1)

| 4 | Fusganita | 1976-08-25 | 456545654 | 4 |

| 4 | Fusganita | 1976-08-25 | 441415414 | 4 |

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


La quinta fila (1), tiene valores NULL para numero e id de telefonos2, ya que no existen

tuplas en esa tabla con un valor de id igual a 3.

Las sintaxis para composiciones externas son:

referencia_tabla LEFT [OUTER] JOIN referencia_tabla [join_condition]

referencia_tabla NATURAL LEFT [OUTER] JOIN referencia_tabla

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



referencia_tabla RIGHT [OUTER] JOIN referencia_tabla [condición]

referencia_tabla NATURAL RIGHT [OUTER] JOIN referencia_tabla

La condición puede ser:

ON expresión_condicional | USING (lista_columnas)

La palabra OUTER es opcional.

Existen dos grupos de composiciones externas: izquierda y derecha, dependiendo de

cual de las tablas se lea en primer lugar.

Composición externa izquierda

En estas composiciones se recorre la tabla de la izquierda y se buscan tuplas en la de la

derecha. Se crean usando la palabra LEFT (izquierda, en inglés).

Las sintaxis para la composición externa izquierda es:

referencia_tabla LEFT [OUTER] JOIN referencia_tabla [condición]

Veamos un ejemplo. Para empezar, crearemos un par de tablas:

mysql> CREATE TABLE tabla1 (id INT NOT NULL, nombre CHAR(10),PRIMARY KEY (id));


mysql> CREATE TABLE tabla2 (id INT NOT NULL, numero INT, PRIMARY KEY(id));


E insertaremos algunos datos:

mysql> INSERT INTO tabla1 VALUES (5, "Juan"), (6, "Pedro"), (7, "José"), (8,

"Fernando");



mysql> INSERT INTO tabla2 VALUES(3, 30), (4, 40), (5, 50), (6, 60);



Ta

ller

de

Ba

se d

e D

ato

s

I S

C



La composición izquierda sería:

mysql> SELECT * FROM tabla1 LEFT JOIN tabla2 USING(id);

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

| id | nombre | id | numero |

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

| 5 | Juan | 5 | 50 |

| 6 | Pedro | 6 | 60 |

| 7 | José | NULL | NULL |

| 8 | Fernando | NULL | NULL |

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


Se puede ver que aparecen dos filas con valores NULL, para los id 7 y 8.

En contraposición, una composición interna dará esta salida:

mysql> SELECT * FROM tabla1 JOIN tabla2 USING(id);

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


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

| 5 | Juan | 5 | 50 |

| 6 | Pedro | 6 | 60 |

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


Composición externa derecha

En este caso se recorre la tabla de la derecha y se buscan tuplas que cumplan la

condición en la tabla izquierda.

La sintaxis es equivalente:

referencia_tabla LEFT [OUTER] JOIN referencia_tabla [condición]

Usando las mismas tablas que en el ejemplo anterior:

mysql> SELECT * FROM tabla1 RIGHT JOIN tabla2 USING(id);

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


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

| NULL | NULL | 3 | 30 |

| NULL | NULL | 4 | 40 |

| 5 | Juan | 5 | 50 |

| 6 | Pedro | 6 | 60 |

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


Ta

ller

de

Ba

se d

e D

ato

s

I S

C



Es lo mismo usar una composición derecha de las tablas tabla1 y tabla2 que una

composición izquierda de las tablas tabla2 y tabla1. Es decir, la consulta anterior es equivalente

a esta otra:

mysql> SELECT * FROM tabla2 LEFT JOIN tabla1 USING(id);

Composiciones naturales externas

Por supuesto, también podemos hacer composiciones externas naturales:

referencia_tabla NATURAL LEFT [OUTER] JOIN referencia_tabla

referencia_tabla NATURAL RIGHT [OUTER] JOIN referencia_tabla

El problema es que si existen tuplas añadidas con respecto a la composición interna, no

se eliminará ninguna columna. Los mismos ejemplos anteriores, como composiciones naturales

externas serían:

mysql> SELECT * FROM tabla1 NATURAL LEFT JOIN tabla2;

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


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

| 5 | Juan | 5 | 50 |

| 6 | Pedro | 6 | 60 |

| 7 | José | NULL | NULL |

| 8 | Fernando | NULL | NULL |

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


mysql> SELECT * FROM tabla1 NATURAL RIGHT JOIN tabla2;

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


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

| NULL | NULL | 3 | 30 |

| NULL | NULL | 4 | 40 |

| 5 | Juan | 5 | 50 |

| 6 | Pedro | 6 | 60 |

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


También es posible realizar la operación de álgebra relacional unión entre varias tablas

o proyecciones de tablas. Para hacerlo se usa la sentencia UNION que permite combinar varias

sentencias SELECT para crear una única tabla de salida.

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



Las condiciones para que se pueda crear una unión son las mismas que vimos al estudiar

el álgebra relacional: las relaciones a unir deben tener el mismo número de atributos, y además

deben ser de dominios compatibles.

mysql> CREATE TABLE stock1 (

-> id INT NOT NULL,

-> nombre VARCHAR(30),

-> cantidad INT,

-> PRIMARY KEY (id));



-> id INT NOT NULL,


-> cantidad SMALLINT,




-> id INT NOT NULL,


-> numero MEDIUMINT,



mysql> INSERT INTO stock1 VALUES

-> (1, "tornillo M3x12", 100),

-> (2, "tornillo M3x15", 120),

-> (3, "tornillo M4x25", 120),

-> (4, "tornillo M5x30", 200);




-> (10, "tuerca M4", 120),

-> (11, "tuerca M3", 100),

-> (12, "tuerca M5", 87);




-> (20, "varilla 10", 23),

-> (1, "tornillo M3x12", 22),

-> (21, "varilla 12", 32),

-> (11, "tuerca M3", 22);



Podemos crear una unión de las tres tablas, a pesar de que los nombres y tamaños de

algunas columnas sean diferentes:

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



mysql> SELECT * FROM stock1 UNION

-> SELECT * FROM stock2 UNION

-> SELECT * FROM stock3;

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

| id | nombre | cantidad |

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

| 1 | tornillo M3x12 | 100 |

| 2 | tornillo M3x15 | 120 |

| 3 | tornillo M4x25 | 120 |

| 4 | tornillo M5x30 | 200 |

| 10 | tuerca M4 | 120 |

| 11 | tuerca M3 | 100 |

| 12 | tuerca M5 | 87 |

| 1 | tornillo M3x12 | 22 |

| 11 | tuerca M3 | 22 |

| 20 | varilla 10 | 23 |

| 21 | varilla 12 | 32 |

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


El resultado se puede ordenar usando ORDER BY y también podemos seleccionar un

número limitado de filas mediante LIMIT:

mysql> (SELECT * FROM stock1) UNION

-> (SELECT * FROM stock2) UNION

-> (SELECT * FROM stock3) ORDER BY id LIMIT 6;

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

| id | nombre | cantidad |

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

| 1 | tornillo M3x12 | 100 |

| 1 | tornillo M3x12 | 22 |

| 2 | tornillo M3x15 | 120 |

| 3 | tornillo M4x25 | 120 |

| 4 | tornillo M5x30 | 200 |

| 10 | tuerca M4 | 120 |

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


Con ALL se muestran todas las filas, aunque estén repetidas, con DISTINCT sólo se

muestra una copia de cada fila:

mysql> SELECT id,nombre FROM stock1 UNION

-> SELECT id,nombre FROM stock2 UNION

-> SELECT id,nombre FROM stock3;

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

| id | nombre |

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

| 1 | tornillo M3x12 |




| 10 | tuerca M4 |

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



| 11 | tuerca M3 |

| 12 | tuerca M5 |

| 20 | varilla 10 |

| 21 | varilla 12 |

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


mysql> SELECT id,nombre FROM stock1 UNION ALL

-> SELECT id,nombre FROM stock2 UNION ALL

-> SELECT id,nombre FROM stock3;

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

| id | nombre |

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





| 10 | tuerca M4 |

| 11 | tuerca M3 |

| 12 | tuerca M5 |


| 11 | tuerca M3 |

| 20 | varilla 10 |

| 21 | varilla 12 |

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


3.4 Agregación GROUP BY, HAVING.

GROUP BY

Permite combinar en un único registro los registros con valores idénticos en la lista de

campos especificada

Es posible agrupar filas en la salida de una sentencia SELECT según los distintos valores

de una columna, usando la cláusula GROUP BY. Esto, en principio, puede parecer redundante,

ya que podíamos hacer lo mismo usando la opción DISTINCT. Sin embargo, la cláusula GROUP

BY es más potente:

mysql> SELECT fecha FROM gente GROUP BY fecha;

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

| fecha |

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

| 1978-06-15 |

| 1985-04-12 |

| 1993-02-10 |

| 2001-12-02 |

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


Ta

ller

de

Ba

se d

e D

ato

s

I S

C



La primera diferencia que observamos es que si se usa GROUP BY la salida se ordena

según los valores de la columna indicada. En este caso, las columnas aparecen ordenadas por

fechas.

Otra diferencia es que se eliminan los valores duplicados aún si la proyección no

contiene filas duplicadas, por ejemplo:

mysql> SELECT nombre,fecha FROM gente GROUP BY fecha;

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

| nombre | fecha |

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

| Mengano | 1978-06-15 |

| Fulano | 1985-04-12 |

| Pegano | 1993-02-10 |

| Tulano | 2001-12-02 |

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


Pero la diferencia principal es que el uso de la cláusula GROUP BY permite usar

funciones de resumen o reunión. Por ejemplo, la función COUNT(), que sirve para contar las

filas de cada grupo:

mysql> SELECT fecha, COUNT(*) AS cuenta FROM gente GROUP BY fecha;

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

| fecha | cuenta |

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

| 1978-06-15 | 2 |

| 1985-04-12 | 2 |

| 1993-02-10 | 1 |

| 2001-12-02 | 1 |

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


HAVING

Es similar al where. La cláusula where determina que registros se seleccionan. De forma

parecida, una vez que los registros se agrupan con la cláusula group by, la cláusula having

determina que registros se van a mostrar. Utilice la cláusula where para eliminar registros que

no desea que se agrupen mediante la cláusula group by.

La cláusula HAVING permite hacer selecciones en situaciones en las que no es posible

usar WHERE. Veamos un ejemplo completo:

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



mysql> CREATE TABLE muestras (

-> ciudad VARCHAR(40),

-> fecha DATE,

-> temperatura TINYINT);


mysql> mysql> INSERT INTO muestras (ciudad,fecha,temperatura) VALUES

-> ('Madrid', '2005-03-17', 23),

-> ('París', '2005-03-17', 16),

-> ('Berlín', '2005-03-17', 15),

-> ('Madrid', '2005-03-18', 25),

-> ('Madrid', '2005-03-19', 24),

-> ('Berlín', '2005-03-19', 18);



mysql> SELECT ciudad, MAX(temperatura) FROM muestras

-> GROUP BY ciudad HAVING MAX(temperatura)>16;

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

| ciudad | MAX(temperatura) |

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

| Berlín | 18 |

| Madrid | 25 |

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


3.5 Funciones de conjunto de registros COUNT, SUM, AVG, MAX, MIN

mysql> SELECT MAX(nombre) FROM gente;

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

| max(nombre) |

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

| Tulano |

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


Ejemplos:

Obtener el número de alumnos que existen en la carrera de Ingeniería en Sistemas

Computacionales.

SELECT Count (*) FROM Alumno WHERE especialidad=‟ISC‟;

Obtener la máximo calificación que ha obtenido J.M. Cadena.

SELECT Max(Calif) FROM Cursa WHERE NControl IN (SELECT Ncontrol FROM Alumno WHERE NombreA=

„J.M. Cadena ‟);

Obtener el promedio de calificaciones de Salvador Chávez.

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



SELECT Avg (Calif) FROM Cursa WHERE NCotrol IN (SELECT Ncontrol FROM Alumno WHERE

NombreA=‟Salvador Chávez‟);

Obtener la suma total de las calificaciones obtenidas por Daniel Colín.

SELECT Sum (Calif) FROM Cursa WHERE NControl IN (SELECT NControl FROM Alumno WHERE

NombreA=‟Daniel Colín‟);


Conocer y aplicar comandos para realizar consultas básicas y de múltiples tablas. Consultar y manipular bases de datos ya existentes. Insertar, actualizar y borrar datos individuales y en conjunto. Desarrollar ejercicios de consulta, manipulación y agregación de datos utilizando el

DBMS.

EVALUACION

Examen Práctico 60 %

Participación 10 %

Laboratorio 30 %

100 %

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



OTROS RECURSOS

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



Unidad Cuatro

Control de Transacciones.

Objetivo Educacional: Aplicará las propiedades de las transacciones y analizará como

afectan a las aplicaciones.

4.1 Propiedades de la transacción.

¿Qué es una transacción?

“Secuencia de operaciones que se ejecutan completamente o bien no se realizan”.

o No puede quedarse en un estado intermedio. o Ej: una transferencia entre dos cuentas no puede quedarse en un estado

intermedio: O se deja el dinero en la primera cuenta o en la segunda, pero no se puede sacar el dinero de la primera cuenta, que falle algo en ese momento y no entregarlo en la segunda.

Es una secuencia de una o varias instrucciones de SQL que forman conjuntamente una

unidad lógica de trabajo

Cuando una transacción finaliza con éxito, se graba (COMMIT). Si fracasa, se restaura el

estado anterior (ROLLBACK)

Propiedades de una transacción:

o Atomicidad: Se realizan o todas las instrucciones o ninguna. o Corrección (Preservación consistencia): La transacción siempre deja la BD en un estado

consistente (Si no lo hace puede ser por errores lógicos o físicos)

Y además:

o Aislamiento: Los efectos de una transacción no se ven en el exterior hasta que esta finaliza.

o Persistencia: Una vez finalizada la transacción los efectos perduran en la BD.

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



o Seriabilidad: La ejecución concurrente de varias transacciones debe generar el mismo resultado que la ejecución en serie de las mismas. Los pasos para usar transacciones en MySQL son:

1. Iniciar una transacción con el uso de la sentencia BEGIN. 2. Actualizar, insertar o eliminar registros en la base de datos. 3. Si se quieren los cambios a la base de datos, completar la transacción con el uso de la

sentencia COMMIT. Únicamente cuando se procesa un COMMIT los cambios hechos por las consultas serán permanentes.

4. Si sucede algún problema, podemos hacer uso de la sentencia ROLLBACK para cancelar los cambios que han sido realizados por las consultas que han sido ejecutadas hasta el momento.

Vamos a ejecutar algunas consultas para ver como trabajan las transacciones. Lo

primero que tenemos que hacer es crear una tabla del tipo InnoDB e insertar algunos datos.

Para crear una tabla InnoDB, procedemos con el código SQL estándar CREATE TABLE,

pero debemos especificar que se trata de una tabla del tipo InnoDB (TYPE= InnoDB). Esto es

aplicable a cualquier tipo de tabla, pero cuando no se especifica nada, MySQL supone que se

trata de una tabla MyISAM.

mysql> CREATE TABLE innotest (campo INT NOT NULL PRIMARY KEY) TYPE = InnoDB;


mysql> INSERT INTO innotest VALUES(1);






mysql> SELECT * FROM innotest;

+-------+

| campo |

+-------+

| 1 |

| 2 |

| 3 |

+-------+


De acuerdo, nada espectacular. Ahora veamos como usar transacciones.

mysql> BEGIN;




Ta

ller

de

Ba

se d

e D

ato

s

I S

C




+-------+

| campo |

+-------+

| 1 |

| 2 |

| 3 |

| 4 |

+-------+


Si en este momento ejecutamos un ROLLBACK, la transacción no será completada, y los

cambios realizados sobre la tabla no tendrán efecto.

mysql> ROLLBACK;



+-------+

| campo |

+-------+

| 1 |

| 2 |

| 3 |

+-------+


Si queremos que la transacción se lleve a cabo pondremos COMMIT

4.2 Grados de consistencia.

4.3 Niveles de aislamiento.

El Nivel de Aislación (Isolation Level) tiene que ver con serializabilidad. A veces serializabilidad

estricta puede ser demasiado exigente Violaciones de Serializabilidad permitidos:

Lectura Sucia: Transacción T1 realiza una actualización de una tupla. T2 lee la tupla actualizada pero poco despues T1 termina con un Rollback (Abort). T2 ha visto información que no existe.

Lectura no repetible: Transacción T1 lee una tupla. T2 actualiza la misma tupla.T1 vuelve a leer la tupla ahora con diferente valor

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



Fantasmas: T1 lee todas las tuplas que satisfacen una condición. T2 inserta una tupla que también satisface. T1 repite la lectura y aparece una tupla nueva(fantasma)

Nivel de Aislación Sucia No Repetible Fantasma READ UNCOMMITTED SI SI SI READ COMMITTED NO SI SI REPEA REPEATABLE READABLE NO NO SI SERIALIZABLE NO NO NO

4.4 Instrucciones COMMIT y ROLLBACK .

SQL acoge las transacciones de base de datos mediante dos instrucciones de

procesamiento de transacciones de SQL:

COMMIT: La instrucción COMMIT señala la conclusión con éxito de una transacción. Indica al SGBD que la transacción se ha completado; se han ejecutado todas las instrucciones que conforman la transacción, y la base de datos es autoconsistente.

ROLLBACK: La instrucción ROLLBACK señala el fracaso de una transacción. Indica al SGBD que el usuario no desea completar la transacción; en lugar de ello, el SGBD debe volverse atrás de las modificaciones realizadas en las BD durante la transacción. En efecto, el SGBD devuelve la base de datos a su estado previo al comienzo de la transacción.

Tres operaciones fundamentales:

begin: define el inicio de una unidad de trabajo indivisible (puede ser implícito al ejecutar una

operación de categoría transaction-initiating)

commit: marca el término normal de la transacción, todos los cambios deben quedar reflejados

en forma definitiva en la BD y se liberan todos los locks (si los hubieran)

rollback: marca una situación anormal que hace necesario deshacer el camino recorrido

ya sea desde el inicio o desde un punto definido anteriormente (Savepoint) dependiendo de esto se liberará todos los locks o solamente aquellos tomados desde el

savepoint en cuestión

Ejemplo:

mysql> CREATE TABLE t (name CHAR(20), UNIQUE (name)) TYPE = INNODB;

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



mysql> BEGIN;

mysql> INSERT INTO t SET name = 'William';

mysql> INSERT INTO t SET name = 'Wallace';

mysql> COMMIT;

mysql> SELECT * FROM t;

+---------+

| name |

+---------+

| Wallace |

| William |

+---------+

mysql> BEGIN;

mysql> INSERT INTO t SET name = 'Gromit';


ERROR 1062: Duplicate entry 'Wallace' for key 1

mysql> ROLLBACK;


+---------+

| name |

+---------+

| Wallace |

| William |

+---------+

mysql> DROP TABLE t;

mysql> CREATE TABLE t (name CHAR(20), UNIQUE (name)) TYPE = INNODB;

mysql> SET AUTOCOMMIT = 0;

mysql> INSERT INTO t SET name = 'William';


mysql> COMMIT;


+---------+

| name |

+---------+

| Wallace |

| William |

+---------+

Otro ejemplo utilizando el savepoint:

BEGIN

INSERT INTO student(student_id, Last_name, zip, registration_date, created_by,

created_date, modified_by, modified_date)VALUES (student_id_seq.nextval, 'Sonam',

10015,'01-JAN-99', 'STUDENTA','01-JAN-99','STUDENTA','01-JAN-99');

SAVEPOINT A;


created_date, modified_by, modified_date) VALUES (student_id_seq.nextval, ‟Tashi', 10015,

'01-JAN-99', 'STUDENTB', '01-JAN-99','STUDENTB', '01-JAN-99');

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



SAVEPOINT B;


created_date, modified_by, modified_date)VALUES (student_id_seq.nextval, 'Norbu',

10015,'01-JAN-99', 'STUDENTB', '01-JAN-99','STUDENTB', '01-JAN-99');

SAVEPOINT C;

ROLLBACK TO B;

SELECT * FROM student WHERE last_name = 'Norbu';

ROLLBACK TO A;

SELECT last_name FROM student WHERE last_na me = 'Tashi';

COMMIT;

END;

Panorámica de InnoDB

InnoDB dota a MySQL de un motor de almacenamiento transaccional (conforme a ACID) con

capacidades de commit (confirmación), rollback (cancelación) y recuperación de fallas. InnoDB

realiza bloqueos a nivel de fila y también proporciona funciones de lectura consistente sin

bloqueo al estilo Oracle en sentencias SELECT. Estas características incrementan el

rendimiento y la capacidad de gestionar múltiples usuarios simultáneos. No se necesita un

bloqueo escalado en InnoDB porque los bloqueos a nivel de fila ocupan muy poco espacio.

InnoDB también soporta restricciones FOREIGN KEY. En consultas SQL, aún dentro de la misma

consulta, pueden incluirse libremente tablas del tipo InnoDB con tablas de otros tipos.

InnoDB se diseñó para obtener el máximo rendimiento al procesar grandes volúmenes de

datos. Probablemente ningún otro motor de bases de datos relacionales en disco iguale su

eficiencia en el uso de CPU.

A pesar de estar totalmente integrado con el servidor MySQL, el motor de almacenamiento

InnoDB mantiene su propio pool de almacenamiento intermedio para tener un cache de datos e

índices en la memoria principal. InnoDB almacena sus tablas e índices en un espacio de tablas,

el cual puede consistir de varios ficheros (o particiones disco). Esto difiere de, por e jemplo, el

motor MyISAM, donde cada tabla se almacena empleando ficheros separados. Las tablas

InnoDB pueden ser de cualquier tamaño, aún en sistemas operativos donde el tamaño de los

ficheros se limita a 2GB.

Ta

ller

de

Ba

se d

e D

ato

s

I S

C



En MySQL 5.0, InnoDB viene incluido por defecto en las distribuciones binarias. El instalador

Windows Essentials configura a InnoDB como el tipo de base de datos MySQL por defecto en

Windows.

InnoDB se utiliza en muchos grandes sitios de bases de datos que necesitan alto rendimiento. El

famoso sitio de noticias de Internet Slashdot.org corre sobre InnoDB. Mytrix, Inc. almacena más

de 1TB de datos en InnoDB, y otros sitios manejan una carga promedio de 800 inserciones y

actualizaciones por segundo en InnoDB.

InnoDB se publica bajo la misma licencia GNU GPL Versión 2 (de Junio de 1991) que MySQL.

Para más información sobre el licenciamiento de MySQL, consulte

http://www.mysql.com/company/legal/licensing/.

Ejemplo:

mysql> create database x;


mysql> use x

Database changed

mysql> create table transac (clave int primary key, nombre varchar (20)) type= i

nnodb;

Query OK, 0 rows affected, 1 warning (0.38 sec)

mysql> select * from transac;

Empty set (0.00 sec)

mysql> insert into transac values(4, "compas"), (2,"lapiz");



mysql> begin;



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

| clave | nombre |

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

| 2 | lapiz |

| 4 | compas |

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


http://www.mysql.com/company/legal/licensing/

Ta

ller

de

Ba

se d

e D

ato

s

I S

C




Aplicar el concepto de transacción. Realizar ejercicios donde utilice los diferentes grados de consistencia y niveles de

aislamiento. Realizar prácticas donde se evalúe como afecta al desempeño el nivel de aislamiento de

la transacción. Realizar prácticas donde se observe la recuperación de las diferentes fallas de una

transacción. Realizar prácticas donde se presenten deadlocks.

EVALUACION

Examen Práctico 60 %

Participación 10 %

Laboratorio 30 % 100 %

taller de base de datos - · pdf fileunidad uno introducción al sistema manejador de...

Documents