Análisis del lenguaje de consulta para BD relacionales

 

9. Análisis del lenguaje de consulta para BD relacionales




 

Durante las décadas de los sesenta y setenta Edgar Frank Codd trabajó en lo que hoy en día ha desembocado en el modelo relacional. Fue en 1970 cuando publicó uno de sus trabajos más importantes «Un modelo relacional de datos para grandes bancos de datos compartidos» y ese fué el germen de las actuales bases de datos.

 

Junto con el modelo relacional propone un sublenguaje de acceso a dichos datos llamado SEQUEL (Structured English Query Language) el cual fue el predecesor del SQL (Structured Query Language).

 

Actualmente se utiliza la segunda versión llamada SQL2 o SQL92 la cual es un estándar revisado y ampliado del primer SQL (SQL1 o SQL86). La mayoría de los sistemas gestores de bases de datos actuales trabajan con esta versión que permite una gran variedad de operaciones aunque haya sido revisado en fechas posteriores introduciendo nuevas características o pequeñas modificaciones a las existentes.

 

SQL tiene dos subcomponentes muy importantes como son el lenguaje de definición de datos o LDD y el lenguaje interactivo de manipulación de datos o LMD:

 

  • El LDD o Lenguaje de Definición de Datos tiene todo tipo de comandos para crear las estructuras y esquemas de relación, así como el borrado y la modificación de los mismos. Con el LDD entre otras operaciones podemos crear o modificar tablas que son los contenedores básicos donde se almacenan los datos en una base de datos relacional, se pueden crear relaciones entre ellas, establecer atributos de las mismas, etc, así como borrarlas. También con el LDD se podrán definir restricciones de integridad que deberán cumplir los datos alojados en dichas tablas. Otra característica de este lenguaje es que se pueden establecer derechos de acceso tanto a las relaciones como a las vistas estableciendo de ese modo el nivel de seguridad requerido.

  • Con el LMD o Lenguaje de Manejo de Datos se pueden consultar dichas estructuras y recuperar los datos según los criterios establecidos. Dicho lenguaje se basa en álgebra relacional y cálculo relacional. Su potencia y robustez reside sobre todo en la base matemática de estos dos últimos lenguajes.

  • También existe el LCD o Lenguaje de Control de Datos que permite al administrador del sistema o propietario de un objeto otorgar o quitar privilegios sobre el mismo.

 

En este capítulo se verán algunas de las características del lenguaje SQL.

 

9.1 Sentencias de definición de datos.

 

El lenguaje de definición de datos en una base de datos como su nombre indica define o modifica la estructura de una base de datos. Tiene cuatro operaciones básicas (CREATE, ALTER, DROP y TRUNCATE) y sirven para crear, modificar, borrar o definir los objetos de una base de datos.

 

9.1.1 Creación (Create).

 

Con el comando create se pueden crear nuevos objetos en la bases de datos como pueden ser entre otros tablas, vistas, una nueva base de datos o procedimientos almacenados.

 

Un ejemplo básico de creación de una tabla sería el siguiente:

 

CREATE TABLE raquetas

( raqueta_id number(10) not null,

 raqueta_modelo varchar2(50) not null,

 raqueta_marca varchar2(50)

);

 

En la sentencia anterior se puede observar cómo se crea la tabla raquetas mediante la cláusula “create table”. Dentro de los paréntesis se especifican los campos de los que va a constar la tabla. El primero será raqueta_id que es un campo numérico de 10 posiciones y que no puede ser nulo (not null). El segundo campo almacena el nombre de la raqueta que al igual que el anterior no puede ser nulo pero este contiene un varchar2 o cadena de caracteres alfanuméricos. El tercer campo almacena la marca y puede contener datos vacíos (en esta tabla puede haber raquetas sin marca asociada).

 

Como puedes observar, el comando SQL siempre termina con un punto y coma.

 

9.1.2 Modificación (Alter).

 

Como se ha dicho, SQL mediante la cláusula ALTER permite modificar los objetos de la base de datos. Por ejemplo ALTER TABLE modificará la estructura de la tabla.

 

En el siguiente ejemplo se muestra cómo modificar la estructura de la tabla creada anteriormente añadiendo un nuevo campo raqueta_descripcion.

 

 

ALTER TABLE raquetas
ADD raqueta_descripcion varchar2(150);

 

También es posible mediante la cláusula ALTER modificar un campo ya existente. El siguiente comando cambia la longitud del campo raqueta_descripcion y lo configura para que impida introducir datos vacíos.

 

ALTER TABLE raquetas
MODIFY raqueta_descripcion varchar2(200) not null;

 

Además mediante la misma cláusula es posible eliminar columnas de la tabla. En el siguiente ejemplo eliminamos el campo raqueta_descripcion de la tabla.

 

ALTER TABLE raquetas
DROP COLUMN raqueta_descripcion;

9.1.3 Eliminación (Drop).

 

El comando DROP nos va a servir para borrar tanto índices, tablas o bases de datos. Veamos un ejemplo del mismo:

DROP TABLE raquetas;

 

Imaginemos que queremos eliminar la base de datos tienda. Bastaría con ejecutar el siguiente comando:

 

DROP DATABASE database_name

 

Para eliminar un índice el comando puede variar de un gestor de base de datos a otro. Veamos algunos ejemplos:

 

Los siguientes comandos borrarán el índice indraquetas de la tabla raquetas.

 

Sintaxis de DROP index en MS Access:

DROP INDEX indraquetas ON raquetas

 

Sintaxis de DROP index en MS SQL Server:

DROP INDEX raquetas.indraquetas

 

Sintaxis de DROP index en DB2/Oracle:

DROP INDEX indraquetas

 

Sintaxis de DROP index en MySQL:

ALTER TABLE raquetas DROP INDEX indraquetas

 

9.1.4 Borrado o Truncado (Truncate).

 

Imaginemos que solamente queremos borrar los datos de la tabla raquetas. Bastaría con truncar la tabla para conseguirlo:

 

TRUNCATE TABLE raquetas;

 

9.2 Sentencias de manipulación de datos.

 

Las sentencias de manipulación de datos forman parte del lenguaje de manipulación de datos (LMD). El lenguaje de manipulación de datos permite realizar consultas, actualizar la información, insertarla y borrarla. Las sentencias utilizadas son SELECT, INSERT, UPDATE y DELETE. Veamos en detenimiento cada una de ellas:

 

Optimizar las consultas.

Una consulta dependiendo de cómo se escriba le puede llevar al gestor desde menos de un segundo hasta más de 15 minutos (o incluso más).

 

Muchas veces los administradores de datos evalúan las consultas y las optimizan para que se ejecuten de una forma más ágil en el gestor y no congestionen el sistema. Dependiendo del gestor de bases de datos la optimización puede ser diferente.

 

En ocasiones los administradores tienen que crear índices para poder agilizar las consultas.

 

La creación de índices no se puede hacer a lo loco puesto que mejoran las consultas pero penalizan las inserciones y actualizaciones, por lo tanto hay que hacerlo con criterio.

 

9.2.1 Selección de datos (SELECT).

 

La sentencia SELECT básica en SQL contiene las cláusulas SELECT y FROM (selecciona …. de). Veamos un ejemplo de la misma:

 

SELECT nombre, apellidos FROM alumno;

 

Esta sentencia selecciona el nombre y los apellidos de todos los alumnos existentes.

Podemos filtrar los alumnos cuyo nombre sea «EMMA». Para ello necesitaremos añadir la cláusula WHERE.

 

SELECT nombre, apellidos FROM alumno WHERE nombre = ‘EMMA’;

 

La cláusula WHERE como vemos nos sirve para filtrar el resultado de una sentencia SELECT. Muchas veces la tenemos que utilizar debido a que no siempre queremos recuperar todos los datos de una tabla.

 

9.2.1.1 Cláusula DISTINCT.

 

Imaginemos que queremos extraer los diferentes apellidos de la tabla alumno. Necesitamos ejecutar la siguiente sentencia:

SELECT DISTINCT apellidos FROM alumno;

 

DISTINCT va a eliminar las filas duplicadas que vaya encontrando en el resultado.

 

9.2.1.2 Cláusula ORDER BY.

 

Generalmente nos gustará tener ordenar el resultado de las consultas, para ello utilizaremos la sentencia ORDER BY. Un ejemplo de ORDER BY sería el siguiente:

 

SELECT apellidos,nombre FROM alumno order by apellidos ASC, nombre;

 

o bien:

 

SELECT apellidos,nombre FROM alumno order by 1, 2;

 

Las cláusulas ASC y DESC nos servirán para ordenar ascendentemente (ASC) o descendentemente (DESC) el resultado.

9.2.2 Inserción de datos (INSERT).

 

La cláusula INSERT permite insertar datos en una tabla. Generalmente se suele utilizar la sentencia normal o la acortada. La sentencia normal tendría el siguiente formato:

 

INSERT INTO ‘nombreTabla’ (‘columna1’,[‘columna2,… ‘]);

VALUES (‘valor1’, [‘valor2,…’])

 

Un ejemplo de INSERT sería el siguiente:

 

INSERT INTO alumnos (nombre, apellido)

VALUES (‘Robert’, ‘Sinnock’);

 

La sentencia acortada es similar pero se omiten los nombre de las columnas porque se presupone que se conocen:

 

INSERT INTO nombreTabla VALUES (‘valor1’, [‘valor2,…’]);

 

En el siguiente ejemplo se presupone que ‘nombre’ y ‘apellidos’ son las únicas columnas de la tabla ‘alumnos’:

 

INSERT INTO alumnos

VALUES (‘Juan Carlos’, ‘Moreno’);

 

En ese caso y para evitar errores hay que poner todas las columnas de la tabla y en el mismo orden de creación de las mismas.

9.2.3 Actualización de datos (UPDATE).

 

Con la cláusula UPDATE podemos modificar los valores de una serie de registros de una tabla.

 

Un ejemplo básico de estructura de este comando sería el siguiente.

 

UPDATE nombreTabla SET campo1 = ‘valor1’ WHERE campo2 = ‘valor2’;

 

Un ejemplo de utilización sería el siguiente:

 

UPDATE alumnos SET nombre = ‘Sancho’ WHERE apellidos = ‘Torres Maestre’;

 

La sentencia anterior actualizará en todos los registros el campo nombre (lo cambiará a ‘Sancho’)  cuyo campo apellidos sea igual a ‘Torres Maestre’.

9.2.4 Borrado de datos (DELETE).

 

Con la cláusula DELETE podemos borrar todos o varios de los registros de una tabla.

 

La estructura básica del comando DELETE sería el siguiente:

 

DELETE FROM nombreTabla;

o

DELETE FROM nombreTabla WHERE columna1 = ‘valor1’;

 

Ejemplos de utilización

 

DELETE FROM alumnos;

 

En este comando anterior se borrarán todos los datos de la tabla. Si queremos filtrar qué datos serán borrados podemos utilizar el siguiente comando:

 

DELETE FROM alumnos WHERE apellidos = ‘Moreno’;

 

En este caso solamente se borrarán aquellos alumnos cuyo apellido sea ‘Moreno’.

 

9.3 Sentencias de concesión y revocación de privilegios.

 

Los comandos para otorgar y denegar permisos en SQL son GRANT y REVOKE. Ambos pertenecen al LCD o Lenguaje de Control de Datos de SQL. Ambos comandos le sirven al administrador del sistema o al propietario de la base de datos otorgar o quitar los privilegios de un objeto de la misma.

 

La sintaxis de este comando (GRANT) es la siguiente:

 

GRANT privilegio

ON objeto

TO usuario | PUBLIC | rol

[WITH GRANT OPTION];

 

Veamos una a una qué significan cada una de estas cláusulas:

 

  • privilegio. Es el privilegio de la base de datos que se quiere otorgar, por ejemplo SELECT.

  • objeto. Es el objeto del que se le va a otorgar el privilegio. Por ejemplo la tabla alumnos

  • usuario, PUBLIC o rol. Son los receptores del privilegio, puede ser un usuario concreto, PUBLIC sería para todo el mundo o bien le podemos asignar el privilegio a un rol.

  • WITH GRANT OPTION. Es un argumento opcional y quiere decir que el usuario podrá conceder privilegios sobre ese objeto a otros usuarios.

GRANT SELECT ON alumnos TO emma WITH GRANT OPTION;

 

Veamos la sintaxis del comando REVOKE:

 

REVOKE privilegio

ON objeto

FROM usuario | PUBLIC | rol;

 

Un ejemplo de este comando sería el siguiente:

 

REVOKE SELECT ON alumnos FROM emma;

 

Como se puede ver, en el comando anterior se le quita el permiso de SELECT al usuario emma sobre la tabla alumnos.

 

9.4 Funciones y procedimientos almacenados.

 

Los procedimientos almacenados y las funciones se utilizan mucho en grandes bases de datos dado que permiten automatizar alguna serie de procesos en la base de datos y por lo tanto mejorar el rendimiento puesto que los clientes que se conectan a la base de datos no tienen por qué enviar tanta información sino que es la base de datos la que hace el trabajo.

 

Mucho del trabajo ahora se realiza en el servidor y no en el cliente y por lo tanto aumenta algo la carga del servidor. No obstante, generalmente utilizando funciones y procedimientos almacenados el rendimiento del sistema mejora.

 

Generalmente cuando hay muchos procedimientos almacenados lo más sensato es crear bibliotecas agrupándolos por características.

 

Algunos sistemas gestores de bases de datos utilizan el lenguaje Java como lenguaje de programación.

 

¿Cuando es mejor utilizar funciones y procedimientos almacenados?

  • Cuando varias aplicaciones cliente acceden a una misma base de datos realizando las mismas operaciones.

  • Cuando queremos simplificar la programación de las aplicaciones cliente.

  • Cuando queremos aumentar la seguridad. Los procedimientos permiten asegurarnos que las operaciones se realizan apropiadamente y no se hacen accesos ni operaciones indebidas.

 

Veamos un ejemplo de función en MySQL:

 

mysql> delimiter //

mysql> CREATE FUNCTION hola (s CHAR(25)) RETURNS CHAR(50)
-> RETURN CONCAT(‘Hola ‘,s);
-> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> SELECT hola(‘Emma’) as saludo;
+—————–+
|     saludo       |
+—————–+
| Hola Emma   |
+—————–+
1 row in set (0.00 sec)

 

Comentemos línea a línea cada uno de los comandos:

  • delimiter //. En este comando le decimos a MySQL que utilice // como delimitador en vez del punto y coma puesto que el punto y coma lo vamos a utilizar dentro de nuestra función y no queremos que lo tome como final de función.

  • CREATE FUNCTION hola (s CHAR(25)) RETURNS CHAR(50) RETURN CONCAT(‘Hola ‘,s);. En estas líneas creamos la función. Como se puede observar la función hola admite un parámetro s que es de tipo CHAR de longitud 25 y devuelve un CHAR de longitud 50. La función utiliza CONCAT para concatenar la palabra Hola al parámetro con que es llamada y devuelve todo esto utilizando la sentencia RETURN.

  • delimiter ;. Reestablecemos el delimitador a punto y coma de nuevo.

  • SELECT hola(‘Emma’) as saludo;. Por último hacemos una llamada a la función con la sentencia SELECT pasándole el parámetro ‘Emma’.

 

Veamos ahora un ejemplo de procedimiento almacenado en MySQL:

 

mysql> delimiter //

mysql> CREATE PROCEDURE elemental (OUT par INT)
-> BEGIN
->   SELECT COUNT(*) INTO par FROM alumnos;
-> END
-> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> CALL elemental(@cuantos);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @cuantos;
+—————+
| @cuantos  |
+—————+
|      1000      |
+—————+
1 row in set (0.00 sec)

 

Como se puede ver el procedimiento llamado elemental empieza con las cláusulas CREATE PROCEDURE y termina con la sentencia END. El procedimiento devuelve un parámetro (especificado con OUT) al que llamamos par que es de tipo numérico. En el cuerpo del procedimiento se hace un recuento (COUNT(*)) de las filas que tiene la tabla alumnos y se almacena el resultado en par.

 

Por último para ejecutar el procedimiento llamado elemental utilizamos la sentencia CALL y posteriormente mostramos con SELECT el valor devuelto por el mismo.

 

9.5 Disparadores.

 

Un trigger o disparador es un procedimiento que se ejecuta cuando se produce cierta condición en las operaciones sobre una base de datos. Generalmente los trigger se configuran para que salten cuando se produce una inserción (INSERT), borrado (DELETE) o actualización (UPDATE) pero algunas bases de datos pueden ejecutar triggers al crear, borrar o modificar usuarios, tablas, etc.

 

La sintaxis en MySQL de un trigger o disparador es la siguiente:

 

CREATE
[DEFINER = { usuario | CURRENT_USER }]
TRIGGER trigger_nombre
trigger_momento trigger_evento
ON tabla_nombre FOR EACH ROW
trigger_cuerpo

Donde la cláusula trigger_momento puede tener los siguientes valores:

  • BEFORE

  • AFTER

Donde la cláusula trigger_evento puede tener los siguientes valores:

  • INSERT

  • UPDATE

  • DELETE

 

Veamos un ejemplo sencillo de disparadores realizado sobre MySQL:

 

Vamos a crear la tabla deudas. La tabla tendrá dos campos, el nombre del cliente y la deuda.

CREATE TABLE deudas (cliente INT, debe DECIMAL(10,2));

 

A continuación vamos a crear el trigger llamado suma_deuda el cual va acumulando en la variable suma el total de las deudas:

CREATE TRIGGER suma_deuda BEFORE INSERT ON deudas

FOR EACH ROW SET @suma = @suma + NEW.debe;

 

A continuación inicializamos la variable suma a cero, e insertamos las deudas de tres clientes (cliente 1, 2 y 3).

mysql> SET @sum = 0;

mysql> INSERT INTO deudas VALUES(1,20.90);

mysql> INSERT INTO deudas VALUES(2,200.50);

mysql> INSERT INTO deudas VALUES(3,50.00);

mysql> SELECT @sum AS ‘Deuda total’;

+—————-+

| Deuda total  |

+—————-+

| 271.40         |

+—————-+

Por último realizamos una consulta de la variable suma (SELECT @sum AS ‘Deuda total’;) a la cual le damos el alias de “Deuda total”.

Deja una respuesta

Tu dirección de correo electrónico no será publicada.

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.