INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

UNIDAD I
INTRODUCCIÓN AL SISTEMA
MANEJADOR DE BASES DE
DATOS

ARR, MMCH INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 1

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

1.1 Conceptos.

Bases de Datos. Una base de datos en un conjunto de información interrelacionada con

un objetivo específico.

Sistema Manejador de Bases de Datos. Es un conjunto de datos y los programas de

aplicación que accesan a dichos datos con la finalidad de almacenar, manipular y
consultar dicha información en un momento determinado, de una manera rápida y
eficiente.

El Sistema Manejador de Bases de Datos consta de:

 Lenguaje de definición de datos [DDL: Data Definition Language]. 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.

Ejemplo: Describir y dar nombre a los datos que se requieren para cada
aplicación, junto a las reglas que garantizan su integridad y seguridad.

 Lenguaje de manipulación de datos [DML: Data Manipulation Language]. es

utilizado para escribir programas que crean, actualizan y extraen información de
las bases de datos.

Ejemplo: Consultar, añadir, modificar o borrar datos de la base de datos.

 Lenguaje de Consulta Estructurada. [SQL: Structured Query Language]. 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.

Un Sistema Manejador de Bases de Datos debe permitir definir estructuras de

almacenamiento, acceder a los datos de forma eficiente y segura, definir usuarios, etc.

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 2

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

Ejemplos de Sistema Manejadores de Bases de Datos: Access, Oracle, MySQL, Fox

Pro, SQL Server, PostgreSQL.

Arquitectura de un Sistema Manejador de Bases de Datos

1.2 Característica de un Sistema Manejador de Bases de Datos.

 Un SMBD 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 SMBD 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 SMBD 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 BD.

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 3

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

 Un SMBD 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.

 Un SMBD 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 SMBD 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 SMBD 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 de 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.

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 4

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE 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.

Actividades de la Unidad.

 Presentación por equipos de diferentes manejadores de bases de datos

(Microsft Visual FoxPro, Oracle, Access, MySQL, SQL Server, PostgreSQL,
etc)

 Realizar un análisis comparativo de las características principales de los

Manejadores de Bases de Datos.

 Instalación del Sistema Manejador de Bases de Datos MySQL (Paquete

XAMPP o WAMPP).

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 5

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

UNIDAD II
LENGUAJE DE DEFINICIÓN
DE DATOS (DDL)

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 6

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

Un Lenguaje de Definición de Datos (Data Definition Language, DDL por sus siglas en
inglés) es un lenguaje proporcionado por el sistema de gestión de base de datos que
permite a los usuarios de la misma llevar a cabo las tareas de definición de las estructuras
que almacenarán los datos así como de los procedimientos o funciones que permitan
consultarlos. Un Data Definition Language o Lenguaje de descripción de datos (DDL) es
un lenguaje de programación para definir estructuras de datos.

El DDL término fue introducido por primera vez en relación con el Codasyl modelo de
base de datos, donde el esquema de la base de datos ha sido escrito en un lenguaje de
descripción de datos que describen los registros, los campos, y "conjuntos" que
conforman el usuario modelo de datos.

2.1 Creación de Bases de Datos.

La creación de la base de datos consiste en la creación de las tablas que la componen.

En realidad, antes de poder proceder a la creación de las tablas, normalmente hay que
crear la base de datos, lo que a menudo significa definir un espacio de nombres separado
para cada conjunto de tablas. De esta manera, para una DBMS se pueden gestionar
diferentes bases de datos independientes al mismo tiempo sin que se den conflictos con
los nombres que se usan en cada una de ellas. Cada DBMS prevé un procedimiento
propietario para crear una base de datos. Normalmente, se amplía el lenguaje SQL
introduciendo una instrucción: "CREATE DATABASE".

CREATE DATABASE nombre_de_la_base_de_datos;

Ejemplo: Crear la base de Datos prueba

mysql> CREATE DATABASE prueba;

Para mostrar la base de datos ya creada, se utiliza la sentencia Show Databases;

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 7

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

Ejemplo:

mysql> SHOW DATABASES;

+--------------------+
| Database |
+--------------------+
| mysql |
| prueba |
| test |
+--------------------+
3 rows in set (0.00 sec)

Aquí se muestra las bases de datos ya creadas, y entre ellas la base de datos recién
creada, prueba.

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.

USE nombre_de_la_BD;

Ejemplo: Usar la base de datos prueba

mysql> USE prueba;

Database changed

2.2 Creación de Tablas

Una vez creada la base de datos, se pueden crear las tablas que la componen. La sintaxis
de esta sentencia es muy compleja, ya que existen muchas opciones y tenemos muchas
posibilidades diferentes al momento de crear una tabla. Debemos indicar el nombre de la
tabla y los nombres y tipos de las columnas. La instrucción SQL propuesta para este fin
es CREATE Table.

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 8

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

CREATE TABLE nombre_tabla (

nombre_columna tipo_columna [ cláusula_defecto ] [ vínculos_de_columna ]
[ , nombre_columna tipo_columna [ cláusula_defecto ] [ vínculos_de_columna ] ... ]
[ , [ vínculo_de tabla] ... ] )

nombre_columna: es el nombre de la columna que compone la tabla. Los nombres

tienen que comenzar con un carácter alfabético.

tipo_columna: es la indicación del tipo de dato que la columna podrá contener. Los
principales tipos previstos por el estándar SQL son:

CHARACTER(n) Una cadena de longitud fija con exactamente n caracteres. CHARACTER se

puede abreviar con CHAR.
CHARACTER VARYING(n) Una cadena de longitud variable con un máximo de n caracteres.
CHARACTER VARYING se puede abreviar con VARCHAR o CHAR VARYING.
INTEGER Un número estero con signo. Se puede abreviar con INT. La precisión, es decir el
tamaño del número entero que se puede memorizar en una columna de este tipo, depende de la
implementación de la DBMS en cuestión.
SMALLINT Un número entero con signo y una precisión que no sea superior a INTEGER.
FLOAT(p) Un número con coma móvil y una precisión p. El valor máximo de p depende de la
implementación de la DBMS. Se puede usar FLOAT sin indicar la precisión, empleando, por tanto,
la precisión por defecto, también ésta dependiente de la implementación. REAL y DOUBLE
PRECISION son sinónimo para un FLOAT con precisión concreta. También en este caso, las
precisiones dependen de la implementación, siempre que la precisión del primero no sea superior
a la del segundo.
DECIMAL(p,q) Un número con coma fija de por lo menos p cifras y signo, con q cifras después de
la coma. DEC es la abreviatura de DECIMAL. DECIMAL(p) es una abreviatura de DECIMAL(p,0).
El valor máximo de p depende de la implementación.
INTERVAL Un periodo de tiempo (años, meses, días, horas, minutos, segundos y fracciones de
segundo).
DATE, TIME y TIMESTAMP Un instante temporal preciso. DATE permite indicar el año, el mes y el
día. Con TIME se pueden especificar la hora, los minutos y los segundos. TIMESTAMP es la
combinación de los dos anteriores. Los segundos son un número con coma, lo que permite
especificar también fracciones de segundo.

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 9

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

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 se puede 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.

Por ejemplo, si se quiere 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,

se creará la tabla así:

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 10

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

mysql> CREATE TABLE ciudad3 (nombre CHAR(20) NOT NULL PRIMARY KEY, poblacion INT
NULL DEFAULT 5000);
Query OK, 0 rows affected (0.20 sec)
ó
mysql> CREATE TABLE ciudad4 (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);
Query OK, 0 rows affected (0.11 sec)

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)

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 11

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

Para mostrar la estructura de la tabla .

mysql> show full columns from nombre_tabla;

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 Cambia el tipo de dato o nombre de
apaterno apaterno varchar(50); la columna ‘apaterno’ a varchar(50).
Alter table clientes rename Cambia el nombre de la tabla
tabla_clie; clientes por tabla_clie.

Alter tabla tabla_clie drop Elimina la columna ‘domicilio’ de la

domicilio; tabla ‘tabla_clie’.

Alter table tabla_clie add nombre Añade la columna ‘nombre’ de tipo

varchar(30); varchar(30) a la tabla ‘tabla_clie’.

Alter table tabla_clie add index Pone como columna indexada

(apaterno); ‘apaterno’ de la tabla ‘tabla_clie’.
Alter table tabla_clie add primary Hace de la columna ‘id_clientes’ de
key (id_clientes); la tabla ‘tabla_clie’, la llave primaria.

DROP
Drop table gente; Elimina la tabla ‘gente’.
Drop database NombreBd; Elimina toda la base de datos.
Drop index apaterno on tabla_clie Le quita la indexación a la columna
‘apaterno’ de la tabla ‘tabla_clie’.
Alter table table_clie drop Primary Borra una clave primaria (en una
Key tabla solo existe una llave primaria
por eso no se pone el nombre de la
columna).

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 12

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

2.2.1 Integridad.

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.

2.2.2 Integridad Referencial Declarativa.

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.

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 13

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

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.

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.

CREATE TABLE cliente(id_cliente INT NOT NULL, nombre VARCHAR(20), apaterno

VARCHAR(20), amaterno VARCHAR(20), rfc VARCHAR(13),
PRIMARY KEY (id_cliente)) TYPE = INNODB;

CREATE TABLE vendedor(id_vendedor INT NOT NULL, nombre VARCHAR(35), depto VARCHAR
(10), fecha DATE, PRIMARY KEY(id_vendedor)) TYPE = INNODB;

Se crea la tabla venta y se crea el campo id_cliente como llave foránea, haciendo
referencia o relacionándolo con el campo id_cliente de la tabla cliente.

CREATE TABLE venta(id_factura INT NOT NULL, id_cliente INT NOT NULL, cantidad INT, vendedor
int NOT null, comentarios text, PRIMARY KEY(id_factura), FOREIGN KEY (id_cliente)
REFERENCES cliente(id_cliente)) TYPE = INNODB;

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 14

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

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 otra manera con el uso de una sentencia ALTER
TABLE. Vamos a agregar otra llave foránea en la tabla ‘venta’ a través del campo
‘vendedor’, haciendo referencia o relacionándolo con el campo ‘id_vendedor’ de la tabla
‘vendedor’.

ALTER TABLE venta ADD FOREIGN KEY(vendedor) REFERENCES vendedor(id_vendedor) TYPE

= INNODB;

Esto es lo que acabamos de crear.

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 15

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

NOTA: Verificar la versión del MySQL, ya que dependiendo de la versión se crean

automáticamente las tablas de tipo InnoDB, y no es necesario agregar el
‘TYPE=INNODB’,

La integridad referencial se puede comprometer básicamente en tres situaciones:

1.- Cuando se está insertando un nuevo registro,

2.- Cuando se está eliminando un registro, y
3.- 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','Hernández','Martínez','HEMJ781024TTT');

Query OK, 1 row affected (0.05 sec)
mysql>INSERT INTO cliente VALUES (2,'José','Pèrez','Morales','PEMJ761104RTY');
Query OK, 1 row affected (0.05 sec)
mysql> INSERT INTO vendedor VALUES (10,'Ana De la Rosa Manríquez','Abarrotes', '2003-09-20');
Query OK, 1 row affected (0.05 sec)
mysql> INSERT INTO vendedor VALUES (20,'Marcelo Enríquez Gómez','Deportes', '2000-10-23');
Query OK, 1 row affected (0.03 sec)
mysql> INSERT INTO `venta` (`id_factura`, `id_cliente`, `cantidad`, `vendedor`, `comentarios`)
VALUES ('100', '2', '3456', '20', 'primer venta del día');
Query OK, 1 row affected (0.03 sec)

En este momento no hay ningún problema, sin embargo, vamos a ver qué sucede cuando
intentamos insertar un registro en la tabla venta que se refiera a un cliente no existente
cuyo id_cliente es 67:

mysql> INSERT INTO venta VALUES ('200', '67', '3456', '20', 'primer venta del día');
ERROR 1216: Cannot add or update a child row: a foreign key constraint fails

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 16

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

El hecho es que MySQL no nos permite insertar este registro, ya que el cliente cuyo
id_cliente es 67 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,'Martín', ‘De la rosa’,’Cabrera’, ‘ROCM811127’);

Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO venta VALUES(2,3,39,10,’venta de contado’);

Query OK, 1 row affected (0.05 sec)

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 mantiene la integridad de nuestros datos. Sin embargo existe una
forma en la que 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.

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 17

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

mysql> show create table venta;

-------------------------+
| Table | Create Table

-------------------------+
| venta | CREATE TABLE `venta` (
`id_factura` int(11) NOT NULL,
`id_cliente` int(11) NOT NULL,
`cantidad` int(11) DEFAULT NULL,
`vendedor` int(11) NOT NULL,
`comentarios` text,
PRIMARY KEY (`id_factura`),
KEY `id_cliente` (`id_cliente`),
KEY `vendedor` (`vendedor`),
CONSTRAINT `venta_ibfk_2` FOREIGN KEY (`vendedor`) REFERENCES `vendedor` (`id_
vendedor`),
CONSTRAINT `venta_ibfk_1` FOREIGN KEY (`id_cliente`) REFERENCES `cliente` (`id
_cliente`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
-------------------------+
1 row in set (0.00 sec)

En nuestro ejemplo, la restricción tiene el ID `venta_ibfk_1` y `venta_ibfk_1`(es muy

probable que este valor sea diferente en cada caso); el primero que relaciona a venta con
cliente y el segundo que relaciona a venta con vendedor. Borremos la llave foránea que
relaciona a venta con cliente con el ID `venta_ibfk_1`.

mysql> ALTER TABLE venta DROP FOREIGN KEY venta_ibfk_1;

Query OK, 3 rows affected (0.23 sec)
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.

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 18

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

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 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.

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 19

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

Vamos a mostrar un ejemplo de eliminación en cascada, agregando nuevamente la llave

foránea que relaciona venta con cliente, pero esta vez con borrado en cascada:

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

DELETE CASCADE;
Query OK, 3 rows affected (0.23 sec)

Vamos a ver cómo están nuestros registros antes de ejecutar la sentencia DELETE:

Tabla cliente

Tabla vendedor

Tabla venta

Ahora eliminaremos a José Pérez de la base de datos:

mysql> DELETE FROM cliente WHERE id_cliente=2;

Query OK, 1 row affected (0.05 sec)

Al revisar la tabla venta, ahora estará vacía puesto que la venta del cliente 2 ha sido
eliminada.

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 20

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

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

relacionado con José Pérez.

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. Éstas 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; Query OK,
2 rows affected (0.22 sec) Records: 2 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE venta ADD FOREIGN KEY(id_cliente)REFERENCES cliente(id_cliente) ON
DELETE RESTRICT ON UPDATE CASCADE;
Query OK, 2 rows affected (0.22 sec) Records: 2 Duplicates: 0 Warnings: 0

NOTA: Se debe especificar ON DELETE antes de ON UPDATE, ya que de otra manera

se recibirá un error al definir la restricción.

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 21

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

Ahora está lista la clave foránea para una actualización en cascada. Este es el ejemplo:

mysql> INSERT INTO `venta` (`id_factura`, `id_cliente`, `cantidad`, `vendedor`, `comentarios`)

VALUES ('200', '1', '5000', '20', 'primer venta del día');
mysql> SHOW TABLE `venta`

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

Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM venta;

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.

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.

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 22

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

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.

Ventajas de los índices:

 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.

1. 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,...)

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 23

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

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));

2. 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));

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.

3. 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));

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 24

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

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));

Actividades de la Unidad.

 Creación de una base de datos con tres tablas.

 Añadir relaciones a la base de datos.
 Agregar llaves foráneas a las tablas.
 Aplicar la integridad referencial a la base de datos.
 Indexar una tabla por un campo específico.

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 25

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

UNIDAD III
LENGUAJES Y CONSULTAS
DE MANIPULACIÓN DE
DATOS (DML)

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 26

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

Lenguaje de Manipulación de Datos. DML (Data Manipulation Language). Es el que

se usa para modificar y obtener datos desde las bases de datos.

3.1 Instrucciones INSERT, UPDATE Y DELETE.

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.

Para estos ejemplos, se utilizará la BD denominada “Prueba”, realizada en la unidad

anterior;

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

Query OK, 1 row affected (0.05 sec)

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

Query OK, 1 row affected (0.04 sec)

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

Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0

Si no se necesita asignar un valor concreto para alguna columna, se indica el valor por
defecto indicado para esa columna cuando se creó la tabla, usando la palabra DEFAULT:

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

Query OK, 1 row affected (0.03 sec)

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

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 27

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

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 para poder insertar datos:

mysql> INSERT INTO ciudad5 (poblacion,nombre) VALUES (7000000, 'Madrid'), (9000000, 'París'),
(3500000, 'Berlín');
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0

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

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

Query OK, 1 row affected (0.05 sec)

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);

Query OK, 1 rows affected (0.02 sec)

mysql> INSERT INTO ciudad3 (nombre, poblacion) VALUES ('París', 9000000), ('Madrid', 7200000)
ON DUPLICATE KEY UPDATE poblacion=VALUES(poblacion);
Query OK, 3 rows affected (0.06 sec)
Records: 2 Duplicates: 1 Warnings: 0

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:

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 28

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

mysql> INSERT INTO ciudad3 (nombre, poblacion) VALUES -> ('París', 9100000) -> ON DUPLICATE KEY
UPDATE poblacion=poblacion; Query OK, 2 rows affected (0.02 sec)

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;

Query OK, 5 rows affected (0.15 sec)
Rows matched: 5 Changed: 5 Warnings: 0

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;

Query OK, 4 rows affected (0.05 sec) Rows matched: 4 Changed: 4 Warnings: 0

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.

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 29

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

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
esta 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';

Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0

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.

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;

Query OK, 2 rows affected (0.05 sec) Rows matched: 2 Changed: 2 Warnings: 0

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)
Rows matched: 1 Changed: 1 Warnings: 1

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

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 30

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

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

Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Cuando exista una clave primaria o única, se usará ese orden por defecto, si no se
especifica una cláusula ORDER BY.

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;

Query OK, 5 rows affected (0.05 sec)

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;

Query OK, 1 row affected (0.05 sec)

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;

Query OK, 2 rows affected (0.03 sec)

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 31

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

3.2 Consultas básicas SELECT, WHERE y Funciones a nivel

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> SELECT * FROM gente;

+---------+------------+
| nombre | fecha |
+---------+------------+
| Fulano | 1985-04-12 |
| Mengano | 1978-06-15 |
| Tulano | 2001-12-02 |
| Pegano | 1993-02-10 |
+---------+------------+
4 rows in set (0.00 sec)

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.

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 32

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

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

mysql> SELECT nombre FROM gente;

mysql> SELECT clave,poblacion FROM ciudad5;

Empty set (0.00 sec)

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 |
+---------+------------+------------------------------------+
4 rows in set (0.00 sec)

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 |
+---------+------------+-------+
4 rows in set (0.00 sec)

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 33

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

Podemos hacer:

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

+-----+
| 2+3 |
+-----+
|5|

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

-> ('Frutano', '1985-04-12');
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0

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:

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 34

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

mysql> SELECT DISTINCT fecha FROM gente;

+------------+
| fecha |
+------------+
| 1985-04-12 |
| 1978-06-15 |
| 2001-12-02 |
| 1993-02-10 |
+------------+
4 rows in set (0.00 sec)

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:

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

+---------+------------+
| nombre | fecha |
+---------+------------+
| Mengano | 1978-06-15 |
+---------+------------+
1 row in set (0.03 sec)

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

+--------+------------+
| nombre | fecha |
+--------+------------+
| Tulano | 2001-12-02 |
| Pegano | 1993-02-10 |
+--------+------------+
2 rows in set (0.00 sec)

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 35

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

mysql> SELECT * FROM gente WHERE fecha>="1986-01-01" AND fecha < "2000-01-01";
+--------+------------+
| nombre | fecha |
+--------+------------+
| Pegano | 1993-02-10 |
+--------+------------+
1 row in set (0.00 sec)

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.

La UNION de tablas se utiliza cuando tenemos dos tablas con las mismas columnas y
queremos obtener una nueva tabla con las filas de la primera y las filas de la segunda. En
este caso la tabla resultante tiene las mismas columnas que la primera tabla (que son las
mismas que las de la segunda tabla).

Por ejemplo tenemos una tabla de libros nuevos y una tabla de libros antiguos y
queremos una lista con todos los libros que tenemos. En este caso las dos tablas tienen
las mismas columnas, lo único que varía son las filas, además queremos obtener una lista
de libros (las columnas de una de las tablas) con las filas que están tanto en libros nuevos
como las que están en libros antiguos, en este caso utilizaremos este tipo de operación.
Cuando hablamos de tablas pueden ser tablas reales almacenadas en la base de datos o
tablas lógicas (resultados de una consulta), esto nos permite utilizar la operación con
más frecuencia ya que pocas veces tenemos en una base de datos tablas idénticas en
cuanto a columnas. El resultado es siempre una tabla lógica.

Por ejemplo queremos en un sólo listado los productos cuyas existencias sean iguales a
cero y también los productos que aparecen en pedidos del año 90.

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 36

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

En este caso tenemos unos productos en la tabla de productos y los otros en la tabla de
pedidos, las tablas no tienen las mismas columnas no se puede hacer una union de ellas
pero lo que interesa realmente es el identificador del producto (idfab,idproducto), luego
por una parte sacamos los códigos de los productos con existencias cero (con una
consulta), por otra parte los códigos de los productos que aparecen en pedidos del año 90
(con otra consulta), y luego unimos estas dos tablas lógicas.

El operador que permite realizar esta operación es el operador UNION

La COMPOSICIÓN DE TABLAS consiste en concatenar filas de una tabla con filas de

otra. En este caso obtenemos una tabla con las columnas de la primera tabla unidas a las
columnas de la segunda tabla, y las filas de la tabla resultante son concatenaciones de
filas de la primera tabla con filas de la segunda tabla.

El ejemplo anterior quedaría de la siguiente forma con la composición:

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 37

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

A diferencia de la unión la composición permite obtener una fila con datos de las dos
tablas, esto es muy útil cuando queremos visualizar filas cuyos datos se encuentran en
dos tablas.

Por ejemplo queremos listar los pedidos con el nombre del representante que ha hecho el
pedido, pues los datos del pedido los tenemos en la tabla de pedidos pero el nombre del
representante está en la tabla de empleados y además queremos que aparezcan en la
misma línea; en este caso necesitamos componer las dos tablas (Nota: en el ejemplo
expuesto a continuación, hemos seleccionado las filas que nos interesan).

Existen distintos tipos de composición, aprenderemos a utilizarlos todos y a elegir el tipo

más apropiado a cada caso.
Los tipos de composición de tablas son:

 El producto cartesiano
 El INNER JOIN
 El LEFT / RIGHT JOIN

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 más simples de una subconsulta, esta aparece dentro de una
clausula WHERE o HAVING de otra instrucción SQL.

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 38

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

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.

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 .

Ejemplo: Listar todos los clientes a los que sirve ‘Antonio Viguer’

La subconsulta utiliza dos tablas: clientes y empleado, las cuales están

relacionadas a través de campo repclie (clientes) y del campo numemp (empleado).

TABLA CLIENTES TABLA EMPLEADOS

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 39

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

SELECT nombre FROM clientes WHERE RepClie = (SELECT numemp FROM empleado where
nombre ='Antonio Viguer')

Como se puede observar, después de la cláusula WHERE se condiciona el campo

RepClie (representante del cliente), el cual no se conoce, sólo contamos con el nombre
‘Antonio Viguer’, el cual se encuentra dentro de la tabla clientes, por lo tanto se crea una
subconsulta en la cual el campo que se desplegará será el campo numemp, que nos
servirá para utilizarlo en la consulta inicial.

Ya en la subconsulta (dentro del paréntesis), se puede tener acceso al campo nombre, el

cual forma parte de la tabla empleados.

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 único 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

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 40

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

Tabla empleado Tabla oficina

SELECT nombre FROM empleado WHERE oficina IN (SELECT oficina FROM oficina WHERE
ventas > objetivo)

Cabe hacer notar que en la sentencia IN, la subconsulta del paréntesis puede
arrojar un rango de valores y no sólo un valor con en las subconsultas anteriores.

3.3.1 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.

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 41

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

Se denomina composiciones 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. 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.

Ejemplo con el JOIN

Tabla empleado Tabla oficina

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 42

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

SELECT * FROM empleado JOIN oficina ON [Link] = [Link]

Con esta sentencia los empleados que no tienen una oficina asignada (un valor nulo en el
campo oficina de la tabla empleados) no aparecen en el resultado ya que la condición
[Link] = [Link] será siempre nula para esos empleados.

En los casos en que no se quiera que aparezcan las filas que no tienen una fila
coincidente en la otra tabla, utilizaremos el LEFT o RIGHT JOIN.

Haremos la misma consulta con LEFT y RIGHT JOIN para notar la diferencia entre ambas
sentencias.

SELECT * FROM empleado LEFT JOIN oficina ON [Link] = [Link]

SELECT * FROM empleado RIGHT JOIN oficina ON [Link] = [Link]

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 43

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

Como nos podemos dar cuenta dependiendo del orden en que coloquemos las
tablas en la consulta, aparecerán todos los registros de la tabla que nos interese
aparezcan (ya sea izquierda o derecha, es decir, la primera o segunda tabla en la
consulta, en ese orden), y si el campo coincidente con la otra tabla no tiene un valor, se
deja indicado como NULL.

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.

Si listamos los contratos, es decir la fecha de contratación, nos lista todas las fechas aún
repetidas:

SELECT contrato FROM empleado;

En cambio si sólo se requieren las fechas de contratación sin repetir, la sentencia GROUP
BY nos sirve, ya que agrupa las fechas sin repetirlas.

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 44

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

SELECT contrato FROM empleado GROUP BY contrato;

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:

SELECT contrato, COUNT(*) AS cuenta FROM empleado GROUP BY contrato

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.
Ejemplo:

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 45

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

Se requiere desplegar los nombres y los límites de créditos de los clientes y agruparlos
por el campo nombre, aun cuando haya nombres repetidos sólo desplegará aquél que
tenga el crédito mayor de los dos, aunque los dos tengan el límite de crédito mayor a lo
especificado (60,000). Si únicamente se utilizara la condición que el límite de crédito sea
mayor de 60,000, desplegaría lo siguiente:

Pero sólo nos interesan aquéllos que aunque cumplan la condición los agrupe por nombre
y despliegue únicamente al que tenga el crédito mayor. Por lo tanto se tendrá que utilizar
la sentencia HAVING que es más compleja y la consulta quedaría de la siguiente manera:

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 46

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

SELECT nombre, MAX(limitecredito) FROM clientes GROUP BY nombre HAVING MAX(limitecredito)

> 60000

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

MIN.

Las funciones de agregación previstas por el estándar SQL son COUNT, SUM, AVG,
MAX y MIN, las cuales calculan respectivamente el conteo de campos, la suma, la media
aritmética, el máximo y el mínimo de los valores escalares presentes en la columna a la
que se aplican.

Count

Se requiere contar el número de registros de la tabla oficinas:

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 47

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

SELECT count(*) AS Número_de_Oficinas from oficina

SUM

Cuál es total del inventario en pesos.

Tabla productos

SELECT SUM(Precio*existencias) AS TOTAL_INVENTARIO FROM Productos

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 48

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

AVG

Sacar el promedio de ventas de los empleados de la oficina 12.

SELECT AVG(Ventas) from empleado WHERE Oficina=12

Como se puede observar son 3 los empleados que pertenecen a la oficina 12, de los
cuales saca el promedio de las ventas de ellos únicamente. Todas éstas sentencias se
pueden combinar con algunas otras, incluso con subconsultas de 2 o más tablas.

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 49

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

MAX y MIN

Cuál de las productos tiene el mayor precio y cuál el menor.

SELECT MAX(Precio) AS Prod_precio_máximo, MIN(Precio) AS Prod_precio_mínimo FROM

productos

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 50

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

Actividades de la Unidad.

 Inserción de valores en tablas.

 Actualización y borrado de registros.

 Planteamiento y resolución de consultas con una o más tablas.

 Resolución de consultas complejas.

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 51

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

UNIDAD IV
CONTROL DE
TRANSACCIONES

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 52

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

Transacción. Definición de Transacción. Se le llama Transacción a una colección de

operaciones que forman una unidad lógica de trabajo. También puede definirse como una
Secuencia de operaciones que se ejecutan completamente o bien no se realiza, no puede
quedarse en un estado intermedio.

Ejemplo:

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.

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

4.1 Propiedades de la Transacción

 Atomicidad: Se realizan o todas las instrucciones o ninguna.

 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:

 Aislamiento: Los efectos de una transacción no se ven en el exterior hasta que

esta finaliza.

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

 Seriabilidad: La ejecución concurrente de varias transacciones debe generar el

mismo resultado que la ejecución en serie de las mismas.

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 53

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

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.

Ejemplo:

Crearemos la tabla ‘ejemplo’ con un solo campo, llamada ‘campo’.

mysql> CREATE TABLE ejemplo (campo INT NOT NULL PRIMARY KEY) TYPE = InnoDB;
Query OK, 0 rows affected (0.10 sec)

mysql> INSERT INTO ejemplo VALUES(1);

Query OK, 1 row affected (0.08 sec)

mysql> INSERT INTO ejemplo VALUES(2);

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO ejemplo VALUES(3);

Query OK, 1 row affected (0.04 sec)

mysql> SELECT * FROM ejemplo;

+-------+
| campo |
+-------+
|1|
|2|
|3|
+-------+
3 rows in set (0.00 sec)

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 54

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

De acuerdo, nada espectacular. Ahora se verá cómo usar transacciones.

mysql> BEGIN;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO ejemplo VALUES(4);

Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM ejemplo;

+-------+
| campo |
+-------+
|1|
|2|
|3|
|4|
+-------+
4 rows in set (0.00 sec)

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;
Query OK, 0 rows affected (0.06 sec)

mysql> SELECT * FROM ejemplo;

+-------+
| campo |
+-------+
|1|
|2|
|3|
+-------+
3 rows in set (0.00 sec)

Para asegurar una transacción iniciada con un BEGIN, se utiliza el COMMIT y así
se asegura la transacción.

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 55

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

4.2 Grados de consistencia.

Consistencia es un término más amplio que el de integridad. Podría definirse como la

coherencia entre todos los datos de la base de datos. Cuando se pierde la integridad
también se pierde la consistencia. Pero la consistencia también puede perderse por
razones de funcionamiento.

Una transacción finalizada (confirmada parcialmente) puede no confirmarse

definitivamente (consistencia). Si se confirma definitivamente el sistema asegura la
persistencia de los cambios que ha efectuado en la base de datos. Si se anula los
cambios que ha efectuado son deshechos.

La ejecución de una transacción debe conducir a un estado de la base de datos

consistente (que cumple todas las restricciones de integridad definidas). Si se confirma
definitivamente el sistema asegura la persistencia de los cambios que ha efectuado en la
base de datos. Si se anula los cambios que ha efectuado son deshechos.

Una transacción que termina con éxito se dice que está comprometida (commited), una
transacción que haya sido comprometida llevará a la base de datos a un nuevo estado
consistente que debe permanecer incluso si hay un fallo en el sistema. En cualquier
momento una transacción sólo puede estar en uno de los siguientes estados.

 Activa (Active): el estado inicial; la transacción permanece en este estado durante

su ejecución.
 Parcialmente comprometida (Uncommited): Después de ejecutarse la última
transacción.
 Fallida (Failed): tras descubrir que no se puede continuar la ejecución normal.
 Abortada (Rolled Back): después de haber retrocedido la transacción y
restablecido la base de datos a su estado anterior al comienzo de la transacción.
 Comprometida (Commited): tras completarse con éxito.

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 56

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

Aspectos relacionados al procesamiento de transacciones.

Los siguientes son los aspectos más importantes relacionados con el procesamiento de
transacciones:

 Modelo de estructura de transacciones. Es importante considerar si las

transacciones son planas o pueden estar anidadas.
 Consistencia de la base de datos interna. Los algoritmos de control de datos
semántico tienen que satisfacer siempre las restricciones de integridad cuando
una transacción pretende hacer un commit.
 Protocolos de confiabilidad. En transacciones distribuidas es necesario introducir
medios de comunicación entre los diferentes nodos de una red para garantizar la
atomicidad y durabilidad de las transacciones. Así también, se requieren
protocolos para la recuperación local y para efectuar los compromisos (commit)
globales.
 Algoritmos de control de concurrencia. Los algoritmos de control de concurrencia
deben sincronizar la ejecución de transacciones concurrentes bajo el criterio de
correctitud. La consistencia entre transacciones se garantiza mediante el
aislamiento de las mismas.
 Protocolos de control de réplicas. El control de réplicas se refiere a cómo
garantizar la consistencia mutua de datos replicados. Por ejemplo se puede seguir
la estrategia read-one-write-all (ROWA).

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 después 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

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 57

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

 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 SUCIA NO REPETIBLE FANTASMA

AISLACIÓN
READ SI SI SI
UNCOMMITED NO SI SI
READ
COMMITED NO NO SI
REPEATABLE
REPEATABLE NO NO NO
READABLE
SERIALIZABLE

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.

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 58

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

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:

Ya creada la base de datos y la tabla, haremos un ejemplo sencillo de una tabla con dos
campos. Ingresaremos ahora los datos:

Se definen puntos con SAVEPOINT después de empezada la transacción con la

sentencia BEGIN, con el fin de poder deshacer un bloque de sentencias.

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 59

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

Se pueden definir más puntos, dependiendo de las necesidades de ir asegurando las

transacciones.

Con el ROLLBACK se puede deshacer el camino recorrido y teniendo puntos salvados, se

puede deshacer por partes.

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 60

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

El COMMIT asegura la transacción completa cuando ya se está seguro que los datos y
las transacciones sean correctas.

Actividades de la Unidad.

 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.

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 61

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

UNIDAD V
VISTAS

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 62

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

5.1 Definición y objetivo de las vistas.

Definición. Las vistas se pueden definir como tablas virtuales basadas en una o más
tablas o vistas y cuyos contenidos vienen definidos por una consulta sobre las mismas.
Esta tabla virtual o consulta se le asigna un nombre y se almacena permanentemente en
la BD, generando al igual que en las tablas una entrada en el diccionario de datos. Las
vistas permiten que diferentes usuarios vean la BD desde diferentes perspectivas, así
como restringir el acceso a los datos de modo que diferentes usuarios accedan sólo
aciertas filas o columnas de una tabla. Desde el punto de vista del usuario, la vista es
como una tabla real con filas y columnas, pero a diferencia de ésta, sus datos no se
almacenan físicamente en la BD. Las filas y las columnas de datos visibles a través de la
vista son los resultados producidos por la consulta que define la vista.

Objetivos.

 Permiten que los diferentes usuarios vean los datos de la forma conveniente de
acuerdo a su nivel de conocimientos y experiencia.

 A los usuarios especializados, las vistas simplifican sus esquemas facilitando sus
consultas.

5.2 Instrucciones para la Administración de Vistas.

Creación de vistas

La cláusula CREATE VIEW permite la creación de vistas. La cláusula asigna un nombre

a la vista y permite especificar la consulta que la define. Su sintaxis es

CREATE VIEW id_vista [(columna,…)] AS especificación_consulta;

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 63

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

Opcionalmente se puede asignar un nombre a cada columna de la vista. Si se especifica,

la lista de nombres de las columnas debe de tener el mismo número de elementos que el
número de columnas producidas por la consulta.

Si se omiten, cada columna de la vista adopta el nombre de la columna correspondiente

en la consulta. Existen dos casos en los que es obligatoria la especificación de la lista de
columnas:

 Cuando la consulta incluye columnas calculadas.

 Cuando la consulta produce nombres idénticos.

Modificación de Vistas

Si queremos modificar la definición de nuestra vista podemos utilizar la sentencia ALTER

VIEW, de forma muy parecida a como lo hacíamos con las tablas. Cabe mencionar que
solo se puede modificar la definición (campos o condiciones de la Vista).

ALTER VIEW Nombre_Vista AS (Parámetros a modificar)

Acceso a Vistas

El usuario accede a los datos de una vista exactamente igual que si estuviera accediendo
a una tabla. De hecho, en general, el usuario no sabrá que está consultando una vista.

Eliminación de Vistas

Se hace con DROP VIEW id_vista;

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 64

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

Ventajas e inconvenientes de las vistas.

VENTAJAS:

 Las consultas con selecciones complejas se simplifican.

 Permiten personalizar la BD para los distintos usuarios, de forma que presenten

los datos con una estructura lógica para los mismos.

 Control de acceso a la BD, haciendo que los usuarios vean y manejen solo
determinada información.

DESVENTAJAS:

 Las restricciones referidas a las actualizaciones.

 La caída del rendimiento cuando se construyen vistas con selecciones complejas.

Por ejemplo, si tenemos unas tablas que representan empleados y oficinas, y queremos
hacer un listado plano de empleados y sus empleados, podemos ejecutar un query que
haga una junta (join) entre estas dos tablas. Pero si posteriormente queremos pedir solo
unas líneas de este resultado a partir de otro filtro, vamos a tener que re-ejecutar el query
completo, agregando nuestro filtro.

Obviamente es posible, pero también implica repetir operaciones anteriores. En el caso de

tener pedidos complejos, esto puede resultar en una pérdida de eficiencia grande, y
mucho trabajo adicional para el desarrollador.

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 65

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

Ejemplo de Creación de Vistas

En la pestaña SQL del phpMyAdmin se teclea la sentencia de la vista como una consulta
normal.

La vista se realizó correctamente y de lado izquierdo aparece ya la vista agregada

como una tabla más en la Base de Datos, aunque es solamente una tabla lógica a la cual
se puede acceder en cualquier momento.

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 66

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

Aparece la consulta de la vista como un acceso a los datos de una consulta normal,
accediendo a la vista como una tabla lógica, en este caso “resumenventas”

Para borrar la vista, únicamente se escribe la sentencia

DROP VIEW `resumenventas`

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 67

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

Otra manera de crear Vistas en phpMyAdmin es desde la parte inferior en la opción

CREATE VIEW.

Posteriormente se teclea la consulta sin la sentencia CREATE VIEW, y se colocan los

nombres de los campos como se desean, deben ser exactamente el mismo número de
campos que se colocan en la etiqueta nombres de las columnas, con respecto a los
campos que se piden desplegar en la consulta.

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 68

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

Actividades de la Unidad.

 Crear vistas con código de consultas complejas.

 Realizar ejercicios donde utilice vistas guardadas como tablas lógicas.

 Borrar y actualizar vistas.

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 69

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

UNIDAD VI
SEGURIDAD

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 70

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

6.1 Esquemas de Autorización

Existen tres preocupaciones respecto a las posibles violaciones de la seguridad en las

BD´s

 Lectura
 Modificación
 Destrucción de los datos

Para evitar la violación de la seguridad se deben establecer medidas en todos los

niveles del Sistema de Base de Datos

 DBMS
 Sistema Operativo
 Red
 Acceso físico al sitio
 Recursos Humanos

1.- DBMS.

No se debe permitir a todos los usuarios el mismo tipo de acceso a los datos; por
ejemplo, algunos no podrán modificar, solo consultar y, no todos podrán consultar todos
los datos. El DBMS deberá implementar esos medios de seguridad.

2.- Sistema Operativo.

Debe impedirse el acceso a los archivos y carpetas donde físicamente se almacenan los
datos, excepto los técnicos o ingenieros responsables

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 71

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

3.- Red.

En la actualidad la gran mayoría de las bases de datos permiten acceso desde

ubicaciones de red. Asimismo, las estaciones de trabajo están conectadas a internet.
Por lo que las redes deben contar con software y/o hardware de seguridad para evitar
accesos no autorizados.

4.- Acceso Físico al Sitio.

Si se tienen todos los medios de seguridad en Sw y/o HW pero el acceso físico al lugar
donde se encuentra el equipo no se restringe, hay riesgo de que intrusos puedan robar
el equipo y tratar de violar con toda calma los medios de seguridad establecidos.

5.- Recursos Humanos.

Debe elegirse cuidadosamente el personal que tendrá acceso a datos restringidos para
evitar que puedan ser sujetos de sobornos para revelar a intrusos las contraseñas o
información relativa a la seguridad.

En esta unidad solo se enfocará a la seguridad del DBMS, sin embargo es importante
considerar que si un organismo solo contempla esta parte y se olvida de las demás, el
riesgo de violaciones de seguridad es muy alto.

A los usuarios se les puede otorgar permisos de:

 Lectura
 Inserción
 Actualización

6.2 Instrucciones GRANT y REVOKE

Los comandos GRANT y REVOKE permiten a los administradores de sistemas crear

cuentas de usuario MySQL y darles permisos y quitarlos de las cuentas.

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 72

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

Los permisos pueden darse en varios niveles:

 Nivel global

Los permisos globales se aplican a todas las bases de datos de un servidor dado.
Estos permisos se almacenan en la tabla [Link]. GRANT ALL ON *.* y
REVOKE ALL ON *.* otorgan y quitan sólo permisos globales.

 Nivel de base de datos

Los permisos de base de datos se aplican a todos los objetos en una base de
datos dada. Estos permisos se almacenan en las tablas [Link] y [Link] .
GRANT ALL ON db_name.* y REVOKE ALL ON db_name.* otorgan y quitan sólo
permisos de bases de datos.

 Nivel de tabla

Los permisos de tabla se aplican a todas las columnas en una tabla dada. Estos
permisos se almacenan en la tabla mysql.tables_priv . GRANT ALL ON
db_name.tbl_name y REVOKE ALL ON db_name.tbl_name otorgan y quitan
permisos sólo de tabla.

 Nivel de columna

Los permisos de columna se aplican a columnas en una tabla dada. Estos

permisos se almacenan en la tabla mysql.columns_priv . Usando REVOKE,
debe especificar las mismas columnas que se otorgaron los permisos.

 Nivel de rutina

Los permisos CREATE ROUTINE, ALTER ROUTINE, EXECUTE, y GRANT se

aplican a rutinas almacenadas. Pueden darse a niveles global y de base de datos.
Además, excepto para CREATE ROUTINE, estos permisos pueden darse en nivel
de rutinas para rutinas individuales y se almacenan en la tabla mysql.procs_priv .

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 73

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

Para los comandos GRANT y REVOKE , priv_type pueden especificarse como

cualquiera de los siguientes:

Permiso Significado
ALL [PRIVILEGES] Da todos los permisos simples excepto GRANT OPTION
ALTER Permite el uso de ALTER TABLE
ALTER ROUTINE Modifica o borra rutinas almacenadas
CREATE Permite el uso de CREATE TABLE
CREATE ROUTINE Crea rutinas almacenadas
CREATE Permite el uso de CREATE TEMPORARY TABLE
TEMPORARY
TABLES
CREATE USER Permite el uso de CREATE USER, DROP USER, RENAME USER, y REVOKE ALL
PRIVILEGES.
CREATE VIEW Permite el uso de CREATE VIEW
DELETE Permite el uso de DELETE
DROP Permite el uso de DROP TABLE
EXECUTE Permite al usuario ejecutar rutinas almacenadas
FILE Permite el uso de SELECT ... INTO OUTFILE y LOAD DATA INFILE
INDEX Permite el uso de CREATE INDEX y DROP INDEX
INSERT Permite el uso de INSERT
LOCK TABLES Permite el uso de LOCK TABLES en tablas para las que tenga el permiso SELECT
PROCESS Permite el uso de SHOW FULL PROCESSLIST
REFERENCES No implementado
RELOAD Permite el uso de FLUSH
REPLICATION Permite al usuario preguntar dónde están los servidores maestro o esclavo
CLIENT
REPLICATION Necesario para los esclavos de replicación (para leer eventos del log binario desde el
SLAVE maestro)
SELECT Permite el uso de SELECT
SHOW DATABASES SHOW DATABASES muestra todas las bases de datos
SHOW VIEW Permite el uso de SHOW CREATE VIEW
SHUTDOWN Permite el uso de mysqladmin shutdown
SUPER Permite el uso de comandos CHANGE MASTER, KILL, PURGE MASTER LOGS,
and SET GLOBAL , el comando mysqladmin debug le permite conectar (una vez)
incluso si se llega a max_connections
UPDATE Permite el uso de UPDATE
USAGE Sinónimo de “no privileges”
GRANT OPTION Permite dar permisos

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 74

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

Sintaxis:

GRANT

GRANT privilegios (columnas)

ON elemento
TO nombre_usuario IDENTIFIED BY 'contraseña'
(whith grant option);

REVOKE

REVOKE privilegios [(columnas)]

ON elemento
FROM nopmbre_de_usuario

Para configurar un administrador, podemos escribir:

mysql > grant all on * to TBD identified by 'Qe4w' with grant option;

mysql > revoke all on * from TBD;

Para Configurar un usuario normal:

mysql > grant usage on empresa .* to TBD identified by 'Qe4w';

A partir de ahora le concedemos los privilegios en función de los que nos ha pedido hacer,
poniendo los privilegios adecuados

mysql > grant select, insert, update, delete, index, alter, create, drop on empresa .* to TBD;

mysql > revoke alter, create, drop on empresa.* from TBD;

mysql > revoke all on empresa.* from TBD;

Actividades de la Unidad.

 Aplicar el concepto de autorizaciones y privilegios.

 Crear grupos de usuarios y asignar sus privilegios.

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 75

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

UNIDAD VII
INTRODUCCIÓN AL SQL
PROCEDURAL

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 76

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

7.1 Procedimientos almacenados.

Pues es un programa que se almacena físicamente en una tabla dentro del sistema de
bases de datos. Este programa está hecho con un lenguaje propio de cada Gestor de BD
y esta compilado, por lo que la velocidad de ejecución será muy rápida.

Principales Ventajas

 Seguridad. Cuando llamamos a un procedimiento almacenado, este deberá

realizar todas las comprobaciones pertinentes de seguridad y seleccionará la
información lo más precisamente posible, para enviar de vuelta la información
justa y necesaria y que por la red corra el mínimo de información, consiguiendo así
un aumento del rendimiento de la red considerable.

 Rendimiento. El SGBD, en este caso MySQL, es capaz de trabajar más rápido

con los datos que cualquier lenguaje del lado del servidor, y llevará a cabo las
tareas con más eficiencia. Solo realizamos una conexión al servidor y este ya es
capaz de realizar todas las comprobaciones sin tener que volver a establecer una
conexión. Esto es muy importante, una vez leí que cada conexión con la BD puede
tardar hasta medios segundo, imagínate en un ambiente de producción con
muchas visitas como puede perjudicar esto a nuestra aplicación… Otra ventaja es
la posibilidad de separar la carga del servidor, ya que si disponemos de un
servidor de base de datos externo estaremos descargando al servidor web de la
carga de procesamiento de los datos.

 Reutilización: el procedimiento almacenado podrá ser invocado desde cualquier

parte del programa, y no tendremos que volver a armar la consulta a la BD cada
que vez que queramos obtener unos datos.

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 77

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

Desventajas:

 El programa se guarda en la BD, por lo tanto si se corrompe y perdemos la

información también perderemos nuestros procedimientos. Esto es fácilmente
subsanable llevando a cabo una buena política de respaldos de la BD.
 Tener que aprender un nuevo lenguaje… esto es siempre un engorro, sobre
todo si no tienes tiempo.

Por lo tanto es recomendable usar procedimientos almacenados siempre que se vaya

a hacer una aplicación grande, ya que nos facilitará la tarea bastante y nuestra aplicación
será más rápida.

Vamos a ver un ejemplo.

Abrimos una consola de MySQL seleccionamos una base de datos y empezamos a

escribir:

Vamos a crear dos tablas en una almacenaremos las personas mayores de 18 años y en
otra las personas menores.

CREATE TABLE ninos(edad int, nombre varchar(50));

CREATE TABLE adultos(edad int, nombre varchar(50));

Imagínate que ahora queremos introducir personas en las tablas pero dependiendo de la
edad queremos que se introduzcan en una tabla u otra, si estamos usando PHP
podríamos comprobar mediante código si la persona es mayor de edad. Lo haríamos así:

$nombre = $_POST[‘nombre’];
$edad = $_POST[‘edad’];
if($edad &lt; 18){
mysql_query(‘insert into ninos values(’ . $edad . ‘ ,
“’.$nombre.’”)’);
}else{
mysql_query(‘insert into adultos values(’ . $edad . ‘ ,
“’.$nombre.’”)’);
}

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 78

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

Si la consulta es corta como en este caso, esta forma es incluso más rápida que tener que
crear un procedimiento almacenado, pero si tienes que hacer esto muchas veces a lo
largo de tu aplicación es mejor hacer lo siguiente:

Creamos el procedimiento almacenado:

delimiter //

CREATE procedure introducePersona(IN edad int,IN nombre

varchar(50))
begin
IF edad &lt; 18 then
INSERT INTO ninos VALUES(edad,nombre);
else
INSERT INTO adultos VALUES(edad,nombre);
end IF;
end;

//

Ya tenemos nuestro procedimiento, vamos a por una visión más detallada.

La primera línea es para decirle a MySQL que a partir de ahora hasta que no
introduzcamos // no se acaba la sentencia, esto lo hacemos así porque en nuestro
procedimiento almacenado tendremos que introducir el caracter “;” para las sentencias, y
si pulamos enter MySQL pensará que ya hemos acabado la consulta y dará error.

Con CREATE PROCEDURE empezamos la definición de procedimiento con nombre

introducePersona. En un procedimiento almacenado existen parámetros de entrada y de
salida, los de entrada (precedidos de “IN”) son los que le pasamos para usar dentro del
procedimiento y los de salida (precedidos de “OUT”) son variables que se establecerán a
lo largo del procedimiento y una vez esta haya finalizado podremos usar ya que se
quedaran en la sesión de MySQL.

En este procedimiento simple solo vamos usar de entrada, más adelante veremos cómo
usar parámetros de salida.

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 79

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

Para hacer una llamada a nuestro procedimiento almacenado usaremos la sentencia

CALL:

call introducePersona(25,”JoseManuel”);

Una vez tenemos ya nuestro procedimiento simplemente lo ejecutaremos desde PHP

mediante una llamada como esta:

$nombre = $_POST['nombre'];
$edad = $_POST['edad'];

mysql_query(‘call introducePersona(’ . $edad . ‘ ,“ ’.$nombre.’

”);’);

De ahora en adelante, usaremos siempre el procedimiento para introducir personas en

nuestra BD de manera que si tenemos 20 scripts PHP que lo usan y un buen día
decidimos que la forma de introducir personas no es la correcta, solo tendremos que
modificar el procedimiento introducePersona y no los 20 scripts.

7.2 Disparadores (Triggers)

Un disparador es un objeto de base de datos con nombre que se asocia a una tabla, y se
activa cuando ocurre un evento en particular para la tabla. Algunos usos para los
disparadores es verificar valores a ser insertados o llevar a cabo cálculos sobre valores
involucrados en una actualización.

Un disparador se asocia con una tabla y se define para que se active al ocurrir una
sentencia INSERT, DELETE, o UPDATE sobre dicha tabla. Puede también establecerse que
se active antes o despues de la sentencia en cuestión. Por ejemplo, se puede tener un
disparador que se active antes de que un registro sea borrado, o después de que sea
actualizado.

Para crear o eliminar un disparador, se emplean las sentencias CREATE TRIGGER y DROP
TRIGGER.

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 80

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

Este es un ejemplo sencillo que asocia un disparador con una tabla para cuando reciba
sentencias INSERT. Actúa como un acumulador que suma los valores insertados en una
de las columnas de la tabla.

La siguiente sentencia crea la tabla y un disparador asociado a ella:

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));

mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
-> FOR EACH ROW SET @sum = @sum + [Link];

La sentencia CREATE TRIGGER crea un disparador llamado ins_sum que se asocia con la
tabla account. También se incluyen cláusulas que especifican el momento de activación,
el evento activador, y qué hacer luego de la activación:

 La palabra clave BEFORE indica el momento de acción del disparador. En este

caso, el disparador debería activarse antes de que cada registro se inserte en la
tabla. La otra palabra clave posible aquí es AFTER.

 La palabra clave INSERT indica el evento que activará al disparador. En el ejemplo,

la sentencia INSERT causará la activación. También pueden crearse disparadores
para sentencias DELETE y UPDATE.

 La sentencia siguiente, FOR EACH ROW, define lo que se ejecutará cada vez que el
disparador se active, lo cual ocurre una vez por cada fila afectada por la sentencia
activadora. En el ejemplo, la sentencia activada es un sencillo SET que acumula
los valores insertados en la columna amount. La sentencia se refiere a la columna
como [Link], lo que significa “el valor de la columna amount que será
insertado en el nuevo registro.”

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 81

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

Para utilizar el disparador, se debe establecer el valor de la variable acumulador a cero,

ejecutar una sentencia INSERT, y ver qué valor presenta luego la variable.

mysql> SET @sum = 0;

mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-
100.00);
mysql> SELECT @sum AS 'Total amount inserted';
+-----------------------+
| Total amount inserted |
+-----------------------+
| 1852.48 |
+-----------------------+

En este caso, el valor de @sum luego de haber ejecutado la sentencia INSERT es 14.98 +
1937.50 - 100, o 1852.48.

Para eliminar el disparador, se emplea una sentencia DROP TRIGGER. El nombre del
disparador debe incluir el nombre de la tabla:

mysql> DROP TRIGGER account.ins_sum;

Debido a que un disparador está asociado con una tabla en particular, no se pueden tener
múltiples disparadores con el mismo nombre dentro de una tabla. También se debería
tener en cuenta que el espacio de nombres de los disparadores puede cambiar en el
futuro de un nivel de tabla a un nivel de base de datos, es decir, los nombres de
disparadores ya no sólo deberían ser únicos para cada tabla sino para toda la base de
datos. Para una mejor compatibilidad con desarrollos futuros, se debe intentar emplear
nombres de disparadores que no se repitan dentro de la base de datos. Adicionalmente al
requisito de nombres únicos de disparador en cada tabla, hay otras limitaciones en los
tipos de disparadores que pueden crearse. En particular, no se pueden tener dos
disparadores para una misma tabla que sean activados en el mismo momento y por el
mismo evento. Por ejemplo, no se pueden definir dos BEFORE INSERT o dos AFTER
UPDATE en una misma tabla. Es improbable que esta sea una gran limitación, porque es
posible definir un disparador que ejecute múltiples sentencias empleando el constructor
de sentencias compuestas BEGIN ... END luego de FOR EACH ROW

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 82

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

También hay limitaciones sobre lo que puede aparecer dentro de la sentencia que el
disparador ejecutará al activarse:

 El disparador no puede referirse a tablas directamente por su nombre, incluyendo

la misma tabla a la que está asociado. Sin embargo, se pueden emplear las
palabras clave OLD y NEW. OLD se refiere a un registro existente que va a borrarse o
que va a actualizarse antes de que esto ocurra. NEW se refiere a un registro nuevo
que se insertará o a un registro modificado luego de que ocurre la modificación.
 El disparador no puede invocar procedimientos almacenados utilizando la
sentencia CALL. (Esto significa, por ejemplo, que no se puede utilizar un
procedimiento almacenado para eludir la prohibición de referirse a tablas por su
nombre).
 El disparador no puede utilizar sentencias que inicien o finalicen una transacción,
tal como START TRANSACTION, COMMIT, o ROLLBACK.

Las palabras clave OLD y NEW permiten acceder a columnas en los registros afectados por
un disparador. (OLD y NEW no son sensibles a mayúsculas). En un disparador para
INSERT, solamente puede utilizarse NEW.nom_col; ya que no hay una versión anterior del
registro. En un disparador para DELETE sólo puede emplearse OLD.nom_col, porque no
hay un nuevo registro. En un disparador para UPDATE se puede emplear OLD.nom_col
para referirse a las columnas de un registro antes de que sea actualizado, y NEW.nom_col
para referirse a las columnas del registro luego de actualizarlo.

Una columna precedida por OLD es de sólo lectura. Es posible hacer referencia a ella pero
no modificarla. Una columna precedida por NEW puede ser referenciada si se tiene el
privilegio SELECT sobre ella. En un disparador BEFORE, también es posible cambiar su
valor con SET NEW.nombre_col = valor si se tiene el privilegio de UPDATE sobre ella.
Esto significa que un disparador puede usarse para modificar los valores antes que se
inserten en un nuevo registro o se empleen para actualizar uno existente.

En un disparador BEFORE, el valor de NEW para una columna AUTO_INCREMENT es 0, no

el número secuencial que se generará en forma automática cuando el registro sea
realmente insertado.

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 83

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

OLD y NEW son extensiones de MySQL para los disparadores.

Empleando el constructor BEGIN ... END, se puede definir un disparador que ejecute
sentencias múltiples. Dentro del bloque BEGIN, también pueden utilizarse otras sintaxis
permitidas en rutinas almacenadas, tales como condicionales y bucles. Como sucede con
las rutinas almacenadas, cuando se crea un disparador que ejecuta sentencias múltiples,
se hace necesario redefinir el delimitador de sentencias si se ingresará el disparador a
través del programa mysql, de forma que se pueda utilizar el caracter ';' dentro de la
definición del disparador. El siguiente ejemplo ilustra estos aspectos. En él se crea un
disparador para UPDATE, que verifica los valores utilizados para actualizar cada columna,
y modifica el valor para que se encuentre en un rango de 0 a 100. Esto debe hacerse en
un disparador BEFORE porque los valores deben verificarse antes de emplearse para
actualizar el registro:

mysql> delimiter //
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
-> FOR EACH ROW
-> BEGIN
-> IF [Link] < 0 THEN
-> SET [Link] = 0;
-> ELSEIF [Link] > 100 THEN
-> SET [Link] = 100;
-> END IF;
-> END;//
mysql> delimiter ;

Podría parecer más fácil definir una rutina almacenada e invocarla desde el disparador
utilizando una simple sentencia CALL. Esto sería ventajoso también si se deseara invocar
la misma rutina desde distintos disparadores. Sin embargo, una limitación de los
disparadores es que no pueden utilizar CALL. Se debe escribir la sentencia compuesta en
cada CREATE TRIGGER donde se la desee emplear.

MySQL gestiona los errores ocurridos durante la ejecución de disparadores de esta

manera:

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 84

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

 Si lo que falla es un disparador BEFORE, no se ejecuta la operación en el

correspondiente registro.
 Un disparador AFTER se ejecuta solamente si el disparador BEFORE (de existir) y
la operación se ejecutaron exitosamente.
 Un error durante la ejecución de un disparador BEFORE o AFTER deriva en la
falla de toda la sentencia que provocó la invocación del disparador.
 En tablas transaccionales, la falla de un disparador (y por lo tanto de toda la
sentencia) debería causar la cancelación (rollback) de todos los cambios
realizados por esa sentencia. En tablas no transaccionales, cualquier cambio
realizado antes del error no se ve afectado.

Actividades de la Unidad.

 Programar procedimientos almacenados para realizar algunas tareas en el

Sistema Manejador de Bases de Datos.

 Implementar algunas restricciones de integridad, programando disparadores.

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 85

INSTITUTO TECNOLÓGICO DE TUXTEPEC TALLER DE BASES DE DATOS

FUENTES DE INFORMACIÓN.

1. Silberschatz, Abraham. Fundamentos de Base de Datos. Mc Graw Hill.

2. Sayless Jonathan. How to use Oracle, SQL PLus. Ed. QED.

3. Koch & Muller. Oracle9i: The Complete Reference. Mc Graw Hill.

4. Tim Martín & Tim Hartley. DB2/SQL Mc Graw Hill.

5. [Link]

6. [Link]

7. [Link]

8. [Link]

9. [Link]

10. [Link]

ARR, MMHC INGENIERÍA EN SISTEMAS COMPUTACIONALES Página 86