1 2 Consultas Avanzadas PDF

Preview

Summary

Clausulas de bases de datos en mysql...

Description

BASES DE DATOS PARA APLICACIONES Ingeniería en Tecnologías de la Información

UNIDAD ACADÉMICA DEL SURESTE Rodolfo Martínez Puente [email protected]

Contenido BASES DE DATOS PARA APLICACIONES 1 MANIPULACIÓN AVANZADA DE DATOS CON SQL............................................................. 1 1.2 CLÁUSULAS AVANZADAS PARA MANIPULACIÓN DE DATOS ...................................... 1 1.2.1 La base de datos .......................................................................................................... 1 1.2.2 Consultas con cláusulas de agregado...................................................................... 2 1.2.3 Consultas avanzadas ................................................................................................... 5 2 BIBLIOGRAFÍA ......................................................................................................................... 14

Bases de Datos para Aplicaciones Competencias a la contribuye la asignatura Dirigir proyectos de tecnologías de información (T.I.) para contribuir a la productividad y logro de los objetivos estratégicos de las organizaciones utilizando las metodologías apropiadas. Evaluar sistemas de tecnologías de información (T.I.) para establecer acciones de mejora e innovación en las organizaciones mediante el uso de metodologías para auditoría.

Objetivo El alumno implementará bases de datos avanzadas que contribuyan al manejo adecuado de la información como apoyo en la toma de decisiones en una organización.

Bases de Datos para Aplicaciones El alumno de la materia de Base de datos para aplicaciones, al terminar la materia será competente para implementar soluciones de bases de datos que contribuyan a la productividad y logro de los objetivos estrategicos a través de acciones de mejora e innovaciónen lasorganizaciones, de tal forma que en la elaboración de un portafolio Competencia de evidencias compuesto por 7 actividades obtenga un 8o% de General desempeño mínimo, conforme a los criterios de evaluación de la rúbrica 8.

Introducción Desde sus orígenes, las bases de datos han permitido administrar y gestionar la información con que se cuenta en las empresas, de hecho, hoy en día son de vital importancia a fin de controlar los enormes volúmenes de información que se manejan en una empresa. Las bases de datos son el soporte fundamental de la toma de decisiones por lo cual estan presentes en casi todas las aplicaciones con que cuentan las empresas, su uso va desde una simple colección de clientes, hasta el control de los inventiarios de productos o la nomina de los empleados. Existen difernetes administradores de bases de datos, cada uno con características propias, así como ventajas y desventajas; y con difentes enfoques, lo que los nos proporciona una difícil desición al momento de seleccionar alguno. Durante el presente documento se hará uso de Microsoft SQL Server Express Edition 20081 y MySQL2.

1 2

http://www.microsoft.com/es-mx/download/details.aspx?id=30438 http://dev.mysql.com/downloads/mysql/

1 MANIPULACIÓN AVANZADA DE DATOS CON SQL El alumno elaborará consultas avanzadas mediante el lenguaje SQL utilizando las mejores prácticas para el manejo adecuado de la información. Objetivo

Competencia particular

El alumno de la materia de Base de datos para aplicaciones, al terminar la unidad I será competente para diseñar consultas avanzadas del SQL en la solución de un caso práctico que involucre la solución a una problemática real de la organización, de tal forma que, en la construcción de un registro de diario compuesto por un cuadernillo de scripts, obtendrá un 8o% de desempeño mínimo, conforme a los criterios de evaluación de la rúbrica 1

Introducción Una de las principales tareas de una base de datos consiste en presentar la información de una forma que resulte funcional para la toma de decisiones en la organización. Es por este motivo que es necesario diseñar las consultas adecuadas para generar los reportes o informes adecuados de acuerdo a las necesidades específicas de la organización.

1.2 CLÁUSULAS AVANZADAS PARA MANIPULACIÓN DE DATOS

Competencia específica

El alumno de la materia de Base de datos para aplicaciones, durante el tema 1.2 será competente para elaborar consultas avanzadas, basadas en las mejores prácticas, de tal forma que, en la elaboración de un registro de diario compuesto por un cuadernillo de scripts, obtendrá un 8o% de desempeño mínimo, conforme a los criterios de evaluación de la rúbrica 1.

1.2.1 La base de datos Antes de iniciar con la creación de consultas avanzadas, es importante conocer la estructura que tendrá la base de datos, a fin de identificar las diferentes tablas, con sus diversos campos, permitiendo con esto, tener un apoyo visual. La base de datos en cuestión recibe por nombre northwind y presenta la siguiente estructura:

1

FIG. 1. ESTRUCTURA DE LA BASE DE DATOS NORTHWIND

1.2.2 Consultas con cláusulas de agregado A continuación, se describirán enunciados que involucren consultas que contengan funciones de agregado. 1. Obtener el número de clientes registrados (incluidos aquellos con valor null). Para esto se emplearía count(*) SELECT COUNT(*) FROM customers

Dando como resultado: 2

FIG. 2. EMPLEANDO COUNT(*)

2. Conocer la cantidad de regiones, sin importar si están repetidas, de los clientes. En este caso, si existiesen valores nulos (null), se omitirían: SELECT COUNT(Region) FROM customers

Dando como resultado:

FIG. 3. USO DE COUNT SOBRE UNA COLUMNA ESPECÍFICA.

Como se puede observar, la cantidad de registro varia, de 96 a 95, o cual implica que un cliente tiene asignado un valor null en la región. 3. Conocer la cantidad de regiones, sin que se contabilicen los duplicados, de los clientes, en este caso se empleará la cláusula distinct, la cual permite omitir los campos repetitivos. SELECT COUNT(DISTINCT(Region)) FROM customers

Dando como resultado:

FIG. 4. USO DE COUNT Y DISTINCT PARA OBTENER RESULTADO NO REPETITIVOS.

Entonces, al emplear distinct, las regiones que se encuentren repetidas, se contabilizarán solo una vez. 4. Obtener el precio más alto de nuestros productos. En este caso emplearemos max. SELECT MAX(UnitPrice) FROM products

Dando como resultado:

FIG. 5. USO DE MAX.

3

5. Ahora, en caso contrario, se puede obtener el precio más bajo de nuestra lista de productos. Para esto emplearemos min. SELECT MIN(UnitPrice) FROM products

Dando como resultado:

FIG. 6. USO DE MIN.

6. Conocer la cantidad completa de productos en existencia en el inventario. Como se tienen que sumar todos los valores, se puede emplear SUM. SELECT SUM(UnitsInStock) FROM products

Dando como resultado:

FIG. 7. USO DE SUM.

7. Determinar el promedio de la cantidad de productos que tenemos en existencia. En este caso se emplearía AVG. SELECT AVG(UnitsInStock) FROM products

Dando como resultado:

FIG. 8. USO DE AVG.

8. Calcular la desviación estándar correspondiente a los precios de los productos. Para esto se podría emplear STD, STDDEV o STDDEV_POP, ya que producen el mismo resultado: SELECT STD(UnitPrice), STDDEV(UnitPrice), STDDEV_POP(UnitPrice) FROM products

Dando como resultado:

FIG. 9. USO DE STD, STDDEV Y STDDEV _POP.

4

Esta es una pequeña aproximación al uso de las funciones de agregado, en los siguientes temas se emplearán nuevamente estas funciones, pero ahora dentro de consultas más completas.

1.2.3 Consultas avanzadas En este tema nos centraremos en emplear varias tablas en una sola consulta, a fin de poner en práctica el uso de los join. Antes de iniciar con la unión de varias tablas, veamos unos ejemplos de consultas sencillas de una tabla. 1.2.3.1 Operadores booleanos Los operadores booleanos (lógicos) permiten realizar comparaciones del tipo mayor que, menor que, etc. A continuación se muestra una tabla y un conjunto de consultas donde se emplean dichos operadores: TABLA 1. OPERADORES LÓGICOS (NO ESTÁN ORDENADOS POR NIVEL DE PRIORIDAD )

Operador

Significado

Operación

Resultado

>

Mayor que

3>3

F

<

Menor que

3=

Mayor o igual a

3 >= 3

T

3) OR (3 < 5)

F OR T = T

TABLA 2. * TABLA DE VERDAD DEL OPERADOR AND.

A

B

A AND B

T

T

T

T

F

F

F

T

F

F

F

F

TABLA 3. ** TABLA DE VERDAD DEL OPERADOR OR.

A

B

A OR B

T

T

T

T

F

T

F

T

T 5

F

F

F

Estos operadores solo pueden dar los resultados de Falso (F) o Verdadero (T), empleándose después de la cláusula WHERE. Veamos en acción estos operadores: 1. Mostrar solo los productos con un precio mayor que 25 SELECT * FROM products WHERE UnitPrice > 25

2. Mostrar todos los productos donde su existencia en inventario sea menor a 10 SELECT * FROM products WHERE UnitsInStock < 10

3. Mostrar todos los clientes cuyo país de residencia sea Alemania (‘Germany’). Cuando se realiza una comparación contra una cadena de caracteres, se deben emplear las comillas simples (‘ ‘). SELECT * FROM customers WHERE Country = 'Germany'

4. Mostrar todos los clientes donde la ciudad sea diferente a ‘Berlin’. Cabe señalar que esta consulta daría como resultado todas las ciudades, sin importar si son de Alemania o no. SELECT * FROM customers WHERE City 'Berlin'

5. Mostrar solo los productos con un precio mayor o igual a 25 SELECT * FROM products WHERE UnitPrice >= 25

6. Mostrar todos los productos donde su existencia en inventario sea menor o igual a 10. SELECT * FROM products WHERE UnitsInStock < 10

7. Mostrar todos los clientes cuyo país de residencia sea Alemania, pero que no vivan en la ciudad de Berlin. De esta forma limitamos más la consulta a diferencia de la consulta 4. SELECT * FROM customers WHERE Country = 'Germany' AND City 'Berlin'

8. Mostrar todos los clientes cuyo país de residencia sea Alemania, pero que vivan en las ciudades de Berlin, Mannheim o Stuttgart 6

SELECT * FROM customers WHERE Country = 'Germany' AND (City ='Berlin' OR City = 'Mannheim' OR City = 'Stuttgart')

Esta consulta se puede reducir, si se emplea la cláusula IN, para unir los OR que tenemos, solo es necesario indicar entre paréntesis y separado por comas los lineamientos de búsqueda, quedando de la siguiente forma: SELECT * FROM customers WHERE Country = 'Germany' AND (City IN ('Berlin', 'Mannheim', 'Stuttgart'))

1.2.3.2 Like MySQL tiene la capacidad estándar para emplear patrones de búsqueda dentro de las consultas, estos patrones están basados en el formato de las expresiones regulares extendidas similares a los empleados en utilidades del sistema operativo UNIX, tal como vi, grep y sed. Estos patrones permiten realizar búsquedas donde se puede desconocer algún carácter o palabra completa, por ejemplo, el carácter de guion bajo ‘_’ representa una coincidencia con un carácter individual, mientras el carácter de porcentaje ‘%’ puede representar n cantidad de caracteres que se encuentren antes, después o antes y después del elemento de búsqueda. En MySQL, por defecto, los patrones SQL no son sensibles al uso de mayúsculas o minúsculas (no es case-sensitive). Tanto al guion bajo como el signo de porcentaje se les puede denominar “comodín”. Es importante recalcar que no se pueden emplear los operadores = o o !=, en lugar de estos se deben emplear los operadores de comparación LIKE o NOT LIKE. Por ejemplo, una consulta que permita mostrar todos los clientes cuya residencia este en Mexico, podría quedar de cualquiera de las siguientes dos formas 1. Con operador: SELECT * FROM customers WHERE Country = 'Mexico'

2. Con LIKE SELECT * FROM customers WHERE Country LIKE 'Mexico'

Entonces, se podría pensar que para que es conveniente empelar LIKE en lugar de los operadores, pues muy sencillo, por ejemplo, para el caso de nuestra base de datos, se tiene el campo Country (país) y México aparece con y sin acento (México, Mexico), entonces, se podría emplear el comodín “_” para suplir ese carácter, o bien emplear el OR, veamos estas consultas 7

1. Con OR SELECT * FROM customers WHERE Country = 'Mexico' OR Country = 'México'

2. Con IN SELECT * FROM customers WHERE Country IN('Mexico', 'México')

3. Con LIKE SELECT * FROM customers WHERE Country LIKE 'M_xico'

Además de la situación anterior, es posible que existan otras consultas donde el uso de LIKE sea la única solución, por ejemplo: se requiere listar todos los clientes cuyo nombre inicie con la letra ‘A’, esto dará como resultado la siguiente consulta: SELECT * FROM customers WHERE ContactName LIKE 'A%'

Al emplear el comodín %, no importa la cantidad de caracteres que existan después de la letra A. De igual forma, el comodín puede ir antes de la letra ‘A’ y buscará los clientes cuyo nombre termine con dicha letra, incluso se pueden colocar comodines a ambos lados de la letra a fin de buscar los nombres que incluyan dicha letra en cualquier posición. 1. Consultar los nombres de los clientes que terminen con la letra A SELECT * FROM customers WHERE ContactName LIKE '%A'

2. Consultar los nombres de los clientes que contengan la letra A SELECT * FROM customers WHERE ContactName LIKE '%A%'

Pero que sucedería si se desea mostrar los clientes cuyo nombre inicie con alguna de las siguientes letras A, B, C, D, E, F, G, H, I, J, K, L y M. En este caso podríamos estar tentados a emplear la siguiente consulta: SELECT * OR OR OR OR OR OR

FROM customers WHERE ContactName LIKE 'A%' ContactName LIKE 'B%' OR ContactName LIKE 'C%' ContactName LIKE 'D%' OR ContactName LIKE 'E%' ContactName LIKE 'F%' OR ContactName LIKE 'G%' ContactName LIKE 'H%' OR ContactName LIKE 'I%' ContactName LIKE 'J%' OR ContactName LIKE 'K%' ContactName LIKE 'L%' OR ContactName LIKE 'M%'

8

Para estos casos donde se requiera el empleo de rangos, se puede emplear RLIKE: SELECT * FROM customers WHERE ContactName RLIKE '^[A-M]'

Pero que sucede si, la consulta es a la inversa y no se desea mostrar los clientes que en cuyo nombre tengan las letras de la A a la M. En estos casos se puede emplear NOT LIKE o NOT RLIKE. 1.2.3.3 Ordenando y limitando los resultados. En muchas ocasiones es muy recomendable que los datos que genera la consulta debiesen estar ordenados, para esto se emplea la cláusula ORDER BY, la cual permite ordenar de la siguiente forma: •

ASC: de forma ascendente, de menor a mayor. Esta opción es la que se encuentra marcada por defecto y no es necesario escribirla.

•

DESC: de forma descendente, de mayor a menor.

A continuación, tomaremos algunas de las consultas anteriores y las ordenaremos por alguno de sus campos. 1. Mostrar solo los productos con un precio mayor que 25,

pero ordenados

de menor a mayor. SELECT * FROM products WHERE UnitPrice > 25 ORDER BY UnitPrice ASC

de

2. Mostrar solo los productos con un precio mayor que 25, mayor a menor.

pero ordenados

SELECT * FROM products WHERE UnitPrice > 25 ORDER BY UnitPrice DESC

3. Mostrar alfabéticamente los clientes cuyo nombre inicie con alguna de las siguientes letras A, B, C, D, E, F, G, H, I, J, K, L y M SELECT * FROM customers WHERE ContactName RLIKE '^[A-M]' ORDER BY ContactName ASC

Otra cláusula que es muy importante es LIMIT, la cual permite definir la cantidad de registros que se desean obtener, por ejemplo: 1. Mostrar los tres productos más costosos que tenemos en inventario. 9

SELECT * FROM products ORDER BY UnitPrice DESC LIMIT 3

Como se puede observar, primero se ordenan los productos en base al precio unitario de mayor a menor y después, por medio de LIMIT, se indica la cantidad de registros que se desean mostrar. 1.2.3.4 Group by Cuando se realizaron las consultas con funciones de agregado, solo se obtenia un solo resultado, el total de algo, el mayor de, etc, sin embargo, empleando la cláusula GROUP BY se pueden obtener subtotales. Una consulta con una cláusula GROUP BY se denomina consulta agrupada ya que agrupa los datos de la tabla origen y produce una única fila resumen por cada grupo formado. Las columnas indicadas en el GROUP BY se llaman columnas de agrupación. Por ejemplo, si se desea saber con cuantos clientes contamos, podríamos emplear la siguiente consulta: SELECT COUNT(*) FROM customers

Pero si deseamos saber cuántos clientes tenemos por país, tendríamos que emplear esto: SELECT Country, COUNT(*) FROM customers GROUP BY Country

De tal forma que al agruparlos por país, se obtiene el resultado de COUNT(*), pero por país. 1.2.3.5 Join Generalmente debemos empelar más de una tabla para realizar las consultas, es aquí donde se pueden emplear diferentes formas de unir dichas tablas, para lograr esto, es recomendable que tengan campos en común a fin de realizar el vínculo: •

Join Natural, este permite el vínculo comparando las llaves primaria y foránea por medio del signo igual (=).

•

Join On, realiza la misma función que el join natural, pero este emplea la palabra Join para determinar el vínculo y con On indica los campos a unir.

La instrucción JOIN permite combinar varias tablas haciendo coincidir los valores de las columnas que nos interesen. Es decir, si se tienen dos tablas A y B que contienen una (o varias) columnas con el mismo nombre, se pueden relacionar ambas tablas por la columna del mismo nombre, siempre y cuando almacenen datos del mismo tipo.

10

Por cada registro de la columna en la tabla A que también esté en la columna de la tabla B, obtendremos una relación. Lo que quiere decir que se produce un producto cartesiano de cada valor de la columna de la tabla A, por todos los valores coincidentes de la columna en la tabla B. Por ejemplo, la tabla empleados nos facilita bastante información de los mismos. La más significativa es el número de empleado, el nombre y la ciudad donde vive. Sin embargo, no nos dice nada de las ventas efectuadas por cada empleado. Si miramos la tabla de ventas veremos que además del número de ventas, tenemos información del empleado que realizo estas ventas. Por lo tanto, por medio de la columna EmployeeID presente en las dos tablas podemos relacionar los empleados con el número de venta. a) Empleando Join Natural: select OrderID, LastName, FirstName, City from Orders O,Employees E WHERE O.EmployeeID = E.EmployeeID order by OrderID

b) Empleando Join On select OrderID, LastName, FirstName, City from Orders O join Employees E on O.EmployeeID = E.EmployeeID order by OrderID

Ambas consultas devolverán los mismos registros, pero que sucede si requerimos información de más de dos tablas. Si nos fijamos en las tablas de empleados y de clientes nos damos cuenta que no hay forma de relacionar que empleado atendió (o vendió) a que cliente. Podemos hacerlo a través de la tabla de ventas que tiene los números de empleados y de clientes. a) Empelando Join natural. SELECT E.EmployeeID, LastName, FirstName, OrderID, C.CustomerID, CompanyName, ContactName FROM Orders O, Employees E, Customers C WHERE O.EmployeeID = E.EmployeeID AND O.CustomerID = C.CustomerID ORDER BY EmployeeID