Consultas DML en SQL: manipulación de datos y funciones MySQL

unendo BY afa LA UNIVERSIDAD DE LAS PROFESIONES

[TEMA 5-PARTE 1 Consultas. DML Consulta la información almacenada en una base de datos empleando asistentes, herramientas gráficas y el lenguaje de manipulación de datos

Sumario

1. - Lenguaje de manipulación de datos. 3
2. - La sentencia SELECT 3
3. - Sintaxis de la instrucción SELECT 3
4. - Clausula SELECT 4
5. - Consultas calculadas 5
6. - Funciones de MySQL en la cláusula SELECT 6
7. - Modificadores ALL, DISTINCT 7
8. - Cláusula ORDER BY 8
9. - Cláusula LIMIT 9
10. - Cláusula WHERE 9
11. - Operadores disponibles en MySQL 9
12. - Operador BETWEEN 11
13. - Operador IN 12
14. - Operador LIKE 12
15. - Operador REGEXP y expresiones regulares 13
16. - Operadores IS e IS NOT 13
17. - Funciones disponibles en MySQL ¡Error! Marcador no definido.
18. - Errores comunes ¡Error! Marcador no definido.
19. - Consultas resumen. 14
20. - Funciones de agregación 15
21. - Diferencia entre COUNT(*) y COUNT(columna). 15
22. - Contar valores distintos COUNT(DISTINCT columna) 16
23. - Agrupamiento de filas (GROUP BY) 16
24. - Condición de agrupamiento (HAVING) 17

unendo BY afa® LA UNIVERSIDAD DE LAS PROFESIONES

OBJETIVOS

SQL es un lenguaje estándar en la mayoría de las bases de datos relacionales con el que normalmente se crean accesos a la información que contienen, mediante la realización de diferentes tipos de operaciones, lanzando consultas y obteniendo datos de interés para el usuario, de una forma sencilla.

En esta unidad conocerás los distintos tipos de consultas que puedes realizar tanto a una tabla como a varias, aprovechando las relaciones del modelo creado.

Por tanto los objetivos son:

• Conocer el estándar SQL como lenguaje para manipular las tablas y sus datos.
• Utilizar las sentencias, operadores y funciones que constituyen la base de SQL. 2

unendo BY afa® LA UNIVERSIDAD DE LAS PROFESIONES

1 .- Lenguaje de manipulación de datos

La primera fase del trabajo con cualquier base de datos comienza, como ya hemos visto en la unidad anterior, con sentencias DDL (Lenguaje de Definición de Datos), puesto que antes de poder almacenar y recuperar información debimos definir las estructuras donde agrupar la información: las tablas.

La siguiente fase será manipular los datos, es decir, trabajar con sentencias DML (Lenguaje de Manipulación de Datos).

El DML (Data Manipulation Language) o Lenguaje de Manipulación de Datos es la parte de SQL dedicada a la manipulación de los datos. Las sentencias DML son las siguientes:

• SELECT: se utiliza para realizar consultas y extraer información de la base de datos.
• INSERT: se utiliza para insertar registros en las tablas de la base de datos.
• UPDATE: se utiliza para actualizar los registros de una tabla.
• DELETE: se utiliza para eliminar registros de una tabla.

En esta unidad nos vamos a centrar en el uso de la sentencia SELECT.

2 .- La sentencia SELECT

Para recuperar o seleccionar los datos, de una o varias tablas tenemos que utilizar la sentencia SELECT.

Según el nivel de complejidad indicado, se puede obtener:

• Una unidad de datos . Todos los datos
• Un subconjunto de datos
• Cualquier conjunto de subconjunto de datos

2.1 .- Sintaxis de la instrucción SELECT

Según la documentación oficial de MySQL ésta sería la sintaxis para realizar una consulta con la sentencia SELECT en MySQL

Para empezar con consultas sencillas podemos simplificar la definición anterior y quedarnos con la siguiente:

SELECT [DISTINCT] select_expr [, select_expr . . . ] [FROM table references] _ [WHERE where condition] _ [GROUP BY {col_name | expr | position} [ASC | DESC] , [WITH ROLLUP] ] [HAVING having_condition] 3

unendo BY afa LA UNIVERSIDAD DE LAS PROFESIONES

[ORDER BY {col_name | expr | position} [ASC | DESC] , . . . ] [LIMIT { [offset, ] row COUNT | row COUNT OFFSET offset} ]

Es muy importante conocer en que orden se ejecuta cada una de las cláusulas que forman la sentencia SELECT. El orden de ejecución es el siguiente:

• Cláusula FROM.
• Cláusula WHERE (Es opcional, puede ser que no aparezca).
• Cláusula GROUP BY (Es opcional, puede ser que no aparezca).
• Cláusula HAVING (Es opcional, puede ser que no aparezca).
• Cláusula SELECT.
• Cláusula ORDER BY (Es opcional, puede ser que no aparezca).
• Cláusula LIMIT (Es opcional, puede ser que no aparezca). 5

SELECT [DISTINCT] select_expr [, select_expr ... ] 1 1 [FROM table_references] - 1 2 [WHERE where_condition] 1 3 [GROUP BY {col_name | expr | position} [ASC | DESC] , ... [WITH ROLLUP] ] 1 4 [HAVING having_condition] 1 ¥6 [ORDER BY {col_name | expr | position} [ASC |DESC] , ... ] 7 [LIMIT { [offset, ]row_count | row_count OFFSET offset} ]

2.2 .- Clausula SELECT

Nos permite indicar cuáles serán las columnas que tendrá la tabla de resultados de la consulta que estamos realizando. Las opciones que podemos indicar son las siguientes:

• El nombre de una columna de la tabla sobre la que estamos realizando la consulta. Será una columna de la tabla que aparece en la cláusula FROM.
• Una constante que aparecerá en todas las filas de la tabla resultado.
• Una expresión que nos permite calcular nuevos valores.

Si en lugar de solicitar una columnas queremos ver todas pondremos asterisco *. Esta parte es obligatoria.

Para referenciar una columna se puede indicar de las siguientes formas:

• nombreColumna
• nombreTabla.nombreColumna 4

unendo BY afa LA UNIVERSIDAD DE LAS PROFESIONES

Siempre que la sentencia afecte a más de una tabla es aconsejable especificar la tabla a la que pertenece cada columna.

Ej. BD Intituto

Obtener todos los datos de todos los alumnos matriculados en el curso.

SELECT * FROM TAlumno;

Ejemplo

Obtener el nombre y los apellidos de todos los alumnos.

SELECT cnombre, capellido1, capellido2 FROM TAlumno;

Ejemplo

Obtener el nombre de todos los alumnos.

SELECT CNombre FROM TAlumno;

2.2.1 .- Consultas calculadas

En algunas ocasiones es interesante realizar operaciones con algunos campos para obtener información derivada de éstos. Si tuviéramos un campo Precio, podría interesarnos calcular el precio incluyendo el IVA o si tuviéramos los campos Sueldo y Paga Extra, podríamos necesitar obtener la suma de los dos campos. Estos son dos ejemplos simples pero podemos construir expresiones mucho más complejas. Para ello haremos uso de la creación de columnas calculadas.

Los operadores aritméticos se pueden utilizar para hacer cálculos en las consultas.

Estas columnas calculadas se obtienen a través de la sentencia SELECT poniendo a continuación la expresión que queramos. Esta consulta no modificará los valores originales de las columnas ni de la tabla de la que se está obteniendo dicha consulta, únicamente mostrará una columna nueva con los valores calculados.

5

unendo BY afa LA UNIVERSIDAD DE LAS PROFESIONES

Podemos ponerle un alias a la columna creada añadiéndolo detrás de la expresión junto con la palabra AS. Si el nuevo nombre que estamos creando para el alias contiene espacios en blanco es necesario usar comillas.

Ejemplo: BD Tienda

Queremos calcular una nueva columna con el precio total, es decir, la cantidad comprada x precio por elemento

SELECT pkid, ncantidad_comprada, nprecio_por_elemento, ncantidad_comprada * nprecio_por_elemento FROM Tventas;

Ejemplo: BD Tienda

Realizar la consulta anterior utilizando un alias apropiado para cada columna.

SELECT pkid, ncantidad_comprada, nprecio_por_elemento, ncantidad_comprada * nprecio_por_elemento AS 'precio total' FROM Tventas;

2.2.2 .- Funciones de MySQL en la cláusula SELECT

¿Has pensado en todas las operaciones que puedes realizar con los datos que guardas en una base de datos? Seguro que son muchísimas. Pues bien, en casi todos los Sistemas Gestores de Base de Datos existen funciones ya creadas que facilitan la creación de consultas mas complejas. Dichas funciones varían según el SGBD, veremos aquí las que utiliza MySQL.

Las funciones son realmente operaciones que se realizan sobre los datos y que realizan un determinado cálculo. Para ello necesitan unos datos de entrada llamados parámetros o argumentos y en función de éstos, se realizará el cálculo de la función que se esté utilizando. Normalmente los parámetros se especifican entre paréntesis.

MySQL nos ofrece funciones matemáticas, funciones para trabajar con cadenas y funciones para trabajar con fechas y horas. Algunos ejemplos de las funciones de MySQL que utilizaremos a lo largo del curso son las siguientes.

Funciones con cadenas: contcat, concat_ws, lower, upper, substr Funciones matemáticas: abs, pow, sqrt, pi, round, truncate Funciones de fecha y hora: now, curtime 6

unendo BY afa LA UNIVERSIDAD DE LAS PROFESIONES

Ejemplo: BD Instituto

obtener el nombre y los apellidos de todos los alumnos en una única columna.

SELECT CONCAT (cnombre, capellido1, capellido2) AS "Nombre completo" FROM Talumno;

La función CONCAT de MySQL no añade ningún espacio entre las columnas, por eso los valores de las tres columnas aparecen como una sola cadena sin espacios entre ellas. Para resolver este problema podemos hacer uso de la función CONCAT WS que nos permite definir un carácter separador entre cada columna. En el siguiente ejemplo haremos uso de la función CONCAT_WS y usaremos un espacio en blanco como separador.

Ejemplo: BD Instituto

Obtener el nombre y los apellidos de todos los alumnos en una única columna separados por un espacio

SELECT CONCAT_WS(' ' , cnombre, capellido1, capellido2) AS Nombre FROM Talumno;

Importante: La función CONCAT devolverá NULL cuando alguna de las cadenas que está concatenando es igual NULL, mientras que la función CONCAT_WS omitirá todas las cadenas que sean igual a NULL y realizará la concatenación con el resto de cadenas

Ejemplo: BD Instituto

Obtener los apellidos y nombre de todo el alumnado en mayúscula.

SELECT upper (capellido1), upper(capellido2), upper(cnombre) FROM Talumno;

2.3 .- Modificadores ALL, DISTINCT

Los modificadores ALL y DISTINCT indican si se deben incluir o no filas repetidas en el resultado de la consulta.

• ALL indica que se deben incluir todas las filas, incluidas las repetidas. Es la opción por defecto, por lo tanto no es necesario indicarla.
• DISTINCT elimina las filas repetidas en el resultado de la consulta. 7