4. Consulta

4. Consulta Dataprix 6 Octubre, 2009 - 11:52

Como ya hemos explicado, las consultas sobre la base de datos se ejecutan mediante sentencias SELECT introducidas en el propio programa cliente y los resultados se presentan en forma de tabla.

4.1. La base de datos demo

4.1. La base de datos demo Dataprix 6 Octubre, 2009 - 12:10
Podeís ver la creación de la base de datos demo en el apartado “Proceso por lotes” de esta misma unidad didáctica.

En esta sección utilizaremos la base de datos demo que hemos creado con el comando source demo.sql. Así que, antes de estudiar las consultas en MySQL, revisaremos brevemente la estructura de esta base de datos, que consta de las siguientes tablas:

 

Las cuatro tablas representan, de manera ficticia, la base de datos de un distribuidor de equipos de procesamiento. Están diseñadas para servir de ejemplo a los casos presentados en este capítulo, por lo que no necesariamente serán útiles en la vida real.

En nuestro ejemplo imaginario representamos la siguiente situación.

•    Nuestro vendedor tiene una relación de proveedores que venden sus productos a crédito, en efectivo o ambos. Las compras a crédito pagan intereses, pero son útiles porque no siempre es posible pagar en  efectivo. Se utiliza una columna de tipo conjunto para pago, que puede tomar los valores ’crédito’, ’efectivo’ o ambos:

create table proveedores (
empresa varchar(20) not null,
pago set(’crédito’,’efectivo’),
primary key (empresa)
);

Los productos que se distribuyen son partes de equipo de cómputo. Para la mayoría de los productos en el mercado, los fabricantes sugieren un precio de venta al público que, aunque no es obligatorio, los consumidores no están dispuestos a pagar más. Las claves de los productos son asignadas para control interno con un número consecutivo. Con estas especificaciones, la tabla productos se define de la manera siguiente:

create table productos (
parte varchar(20),
tipo varchar(20) ,
especificación varchar (20) ,
psugerido float(6,2),
clave int(3) zerofill not null auto_increment,
primary key (clave)
);

•    La empresa define una política para las ganancias mínimas que se deben obtener en ventas: el 5% al por mayor y el 12% al por menor. Estos valores se almacenan en la tabla ganancias, donde se decidió incluir una columna de nombre factor, con el número por el que se multiplica el precio de compra para obtener el precio de venta. Los tipos de venta ‘Por mayor’ y ‘Por menor’ se definen con un tipo de datos enum:

create table ganancia(
venta enum(’Por mayor’,’Por menor’),
factor decimal (2,2)
);

•    La lista de precios se define a partir de la empresa proveedor y el producto, asignándole un precio. Por ese motivo, las columnas empresa y clave se definen como foreign key.

create table precios (
empresa varchar(20) not null,
clave int(3) zerofill not null,
precio float(6,2),
foreign key (empresa) references proveedores,
foreign key (clave) references productos
);

4.2 Consultar informacion

4.2 Consultar informacion Dataprix 7 Octubre, 2009 - 10:36

MySQL ofrece un conjunto muy amplio de funciones auxiliares (tanto estándares como propias) que nos pueden ayudar mucho en determinados momentos, dejando parte del trabajo de manipular los resultados al propio gestor. Debido al rápido ritmo en el desarrollo de este SGBD, es muy conveniente consultar siempre la documentación de nuestra versión para conocer sus posibilidades concretas.

En el módulo 3 de este curso ya estudiamos en detalle el Lenguaje SQL, por lo que no vamos a extendernos aquí en su uso y posibilidades. Únicamente mostraremos los aspectos destacables, facilidades o limitaciones que ofrece MySQL respecto a él.

4.2.1. Funciones auxiliares

4.2.1. Funciones auxiliares Dataprix 7 Octubre, 2009 - 11:01

Las funciones auxiliares que podemos utilizar en nuestras consultas (tanto en la proyección de las columnas como en condiciones en su selección) se pueden clasificar según el tipo de datos con el que trabajan.

Ejemplo

<img src="/files/uploads/32image/funcuones_auxiliares1.png" alt="" />

Algunos ejemplos de las funciones más usadas:

Operadores lógicos

Comparación. Aparte de los estándares =, !=, <, >, IS NULL, IS NOT NULL, BETWEN, IN, destacan COALESCE, INTERVAL, LEAST, GREATEST para trabajar con listas de valores.

Control del flujo de ejecución

•    CASE .. WHEN .. THEN .. ELSE .. END: Similar a la estructura que crearíamos mediante cualquier lenguaje de programación:

•    IF(expr1,expr2,expr3): Típica estructura condicional, si la expr1 es cierta, devuelve la expr2, en caso contrario, la expr3:

Funciones para trabajar con cadenas de caracteres (sólo algunos ejemplos)

•    CONCAT, INSTR (encontrar en una cadena), SUBSTRING, LCASE/RCASE, LENGTH, REPLACE, TRIM, entre otras, son funciones similares a las que podemos encontrar en lenguajes de programación para manipular cadenas de caracteres.

•    QUOTE: delimita una cadena de texto correctamente para evitar problemas al usarla en sentencias SQL. La cadena resultante estará delimitada por comillas simples. Las comillas, el valor ASCII NUL y otros potencialmente conflictivos serán devueltos precedidos del carácter '\'.

•    ENCODE/DECODE,  CRYPT,  COMPRESS/UNCOMPRESS,  MD5,  etc.  son funciones que nos pueden ayudar mucho en el almacenamiento de datos sensibles como contraseñas, etc.

Funciones numéricas

•    Los operadores aritméticos clásicos para realizar todo tipo de operaciones, suma, resta, división, producto, división entera, etc.

•    Funciones matemáticas de todo tipo, trigonométricas, logarítmicas, etc.

Funciones para trabajar con fechas y horas

•    Obtención de fechas en cualquier formato: DATE_FORMAT, DATE, NOW, CURRDATE, etc.

•    Manipulación y cálculos con fechas: ADDDATE, ADDTIME, CONVERT_TZ, DATE_DIFF, etc.

4.2.2. La sentencia EXPLAIN

4.2.2. La sentencia EXPLAIN Dataprix 7 Octubre, 2009 - 12:13

MySQL nos ofrece también facilidades a la hora de evaluar las sentencias SQL, gracias a la sentencia EXPLAIN.

Presentamos primero la ejecución de una sentencia SQL más o menos compleja:

<img src="/files/uploads/32image/explain1.png" alt="" />

Ahora utilizamos la sentencia EXPLAIN para que MySQL nos explique cómo ha realizado esta consulta:

mysql> explain select productos.clave, concat(parte,' ',tipo,' ', especificación) as producto, proveedores.empresa , precio , pago from productos natural join precios natural join proveedores;

En cada fila del resultado, nos explica cómo ha utilizado los índices de cada tabla involucrada en la consulta. La columna 'type' nos indica el tipo de “join” que ha podido hacer. En nuestro caso, 'eq_ref', 'ref' o 'ref_or_null' indica que se ha consultado una fila de esta tabla para cada combinación de filas de las otras. Es una buena señal, se están utilizando los índices, tal como indican el resto de columnas (en concreto el atributo 'clave' que es su clave primaria).

Vemos que en las otras dos tablas, el tipo de 'join' es ALL, esto indica que el gestor ha tenido que leer toda la tabla para comprobar las condiciones que le hemos exigido en la consulta. En el caso de la tabla proveedores, habría podido utilizar la clave primaria ('possible_keys'), pero no lo ha hecho.

Vamos a intentar mejorar esta consulta. Vemos que en la tabla precios no se ha definido ningún índice, lo que facilitaría la labor al SGBD:

Veremos más sobre los índices en el subapartado 5.5 “Análisis y optimización” de esta unidad didáctica.

Las cosas han cambiado sustancialmente. El gestor ha pasado de leer 24 filas de datos, a leer 11. También ha cambiado el orden de lectura de las tablas, haciendo primero una lectura total de la tabla proveedores (que es inevitable ya que no hemos puesto ninguna condición en el SELECT) y, después, ha aprovechado los índices definidos en 'precios' y en 'productos'.

4.3. Manipulacion de filas

4.3. Manipulacion de filas Dataprix 8 Octubre, 2009 - 11:26

Para la manipulación de filas disponemos de las sentencias SQL INSERT, UPDATE y DELETE, su uso y sintaxis ya se ha visto en el módulo 3 de este curso. En algunos casos, MySQL nos proporciona extensiones o modificadores que nos pueden ayudar mucho en determinadas situaciones.

•    INSERT [DELAYED]. Cuando la sentencia INSERT puede tardar mucho en devolver el resultado (tablas muy grandes o con muchos índices que deben recalcularse al insertar una nueva fila) puede ser interesante añadir la palabra clave DELAYED para que MySQL nos devuelva el control y realice la inserción en segundo plano.

•    INSERT [[LOW_PRIORITY] | [HIGH_PRIORITY]]. En tablas muy ocupadas, donde muchos clientes realizan consultas constantemente, una inserción lenta puede bloquear al resto de clientes durante un tiempo. Mediante estos modificadores podemos variar este comportamiento.

•    INSERT [IGNORE]. Este modificador convierte los errores de inserción en avisos. Por ejemplo, si intentamos insertar una fila que duplica una clave primaria existente, el SGBD nos devolverá un aviso (y no insertará la nueva fila), pero nuestro programa cliente podrá continuar con su cometido si el resultado de la inserción no era importante para su correcta ejecución.

•    UPDATE [LOW_PRIORITY] [IGNORE]. Se comportan de igual modo que en la sentencia INSERT.

•    DELETE [QUICK]. Borra el/los registros sin actualizar los índices.

•    TRUNCATE. Es una forma muy rápida de borrar todos los registros de una tabla, si no necesitamos saber el número de registros que ha borrado. DELETE FROM <tabla> realiza el mismo cometido, pero devuelve el número de registros borrados.

•    LAST_INSERT_ID().  Devuelve el último  identificador asignado  a una columna de tipo AUTO_INCREMENT después de una sentencia INSERT.