¿Por qué SQL? (I)

Ciencia de Datos

Business Intelligence / Inteligencia de Negocios (BI)

Administrador de Bases de Datos

Desarrollo Web / Web development

• Todas estos trabajos requieren almacenar grandes cantidades de información.
• Antes de hacer cualquier análisis se requiere recibir los datos correctos.

En consecuencia se necesita desarrollar habilidades para analizar problemas y aplicar soluciones con un software que permita extraer la información deseada de la BD. Para ello se requiere SQL

¿Por qué SQL? (II)

Sabemos que SQL = Structured Query Language / Lenguaje de consulta estructurado

• Es un lenguaje de programación específicamente diseñado para trabajar con Bases de Datos.
• Permite crear, manipular y compartir datos de Sistemas Manejadores de Bases de Datos Relacionales.
• Las query (consultas) son piezas de código que indican a la computadora que ejecute operaciones para recibir los datos deseados (salida).

Query /

Consulta

Output /

Salida

¿Por qué SQL? (III)

• Intuitivo
• Fácil de aprender
• Es esencial para problemas de negocios que involucran grandes cantidades de datos.
• Es un lenguaje muy poderoso

¿Por qué SQL? (IV)

https://www.codingdojo.com/blog/the-7-most-in-demand-programming-languages-of-2019

¿Por qué MySQL?

Hay otras plataformas que soportan SQL

• Oracle
• MariaDB
• Microsoft SQL Server
• SQLite
• Microsoft Access
• PostgreSQL

Entonces ¿por qué MySQL?

Confiable

Maduro: desde mayo 1995

          Programado (C y C++) 

Open-source (Gratuito)

Usado por:

Facebook

Youtube

Dropbox

Twitter

Pinterest

Booking.com

Linkedin

Ranking de Bases de Datos

DB-Engines Ranking

https://db-engines.com/en/ranking

SQL como lenguaje declarativo (I)

SQL ejecuta comandos que permiten crear y manipular Bases de Datos relacionales.

Tipos de programación

Procedimental (imperativo)

Orientado a Objeto

Declarativo

Funcional

SQL

Investigue la diferencia entre cada tipo

Significa que mientras programamos no importa cómo queremos que se haga el trabajo, sino que nos enfocamos qué o cuáles resultados queremos

SQL como lenguaje declarativo (II)

Procedimental (imperativo)

Declarativo - No Procedimental

Cómo

Qué

Lenguajes como C o Java

SQL

1. Por favor, abre la puerta
2. Ve afuera
3. Toma la pelota que olvidé ahí
4. Tráela de regreso a mí

1. Tráeme la pelota, por favor.

Debe seguirse el procedimiento paso a paso

Los algoritmos ya están construidos y hay un optimizador el cual separa las tareas es pasos más pequeños

En SQL nos concentramos en lo que queremos recibir de la base de datos

Introducción a las BD (I)

Los cuatro valores de datos formar un registro

registro = fila / Entidad horizontal / Instancia de entidad

record = row

Un campo / field /Entidad vertical = una columna en la tabla contiene información específica acerca de todos los registros en una tabla. Todos los registros de VENTAS tendrán cuatro campos.

Entidad: la unidad más pequeña que puede contener un conjunto significativo de datos

Entidad = objeto de base de datos

El álgebra relacional nos permite recibir datos eficientemente.

Las tres tablas están relacionadas formando una base de datos relacional cada una con su propio significado: una contiene ventas, otra clientes y la última items.

registro = fila

record = row

Introducción a las BD (II)

Principales componentes de la sintaxis de SQL

• Lenguaje de definición de datos (DDL)
• Lenguaje de Manipulación de Datos (DML)
• Lenguaje de Control de Datos (DCL)
• Lenguaje de control de transacciones (TCL)

Terminología Básica

Base de datos relacional

Hay dos tipos principales de bases de Datos

Base de datos relacionales

Base de datos no relacionales

considera sistemas más complejos + matemática y programación

https://aws.amazon.com/es/nosql/

Base de datos relacional

Objetivo principal: organizar enormes cantidades de datos que pueden ser recuperados rápidamente.

Eficiente

Compacta

Bien estructurada

Las BD ocupan memoria

Base de datos relacional

Imagine una BD (como un conjunto) con una única tabla que es consultada de forma recurrente y que cuenta con millones de registros, esto afectarían el rendimiento de la computadora y llevaría mucho tiempo dar respuesta a las solicitudes.  

Por cada consulta deben recuperarse todos los registros de la tabla lo cual resultaría frustrante. 

¿Para saber quién compró en un fecha determinada? Debe obtenerse toda la BD

Base de datos relacional

Solución: consultar porciones de esos registros  divididos en varias tablas. Tres tablas

ventas

clientes

items

(id_cliente)

(codigo_item)

(fecha_compra)

(nombre, apellido)

Base de datos relacional

Solución: para dar respuesta a quién compró en una fecha determinada ni siquiera necesitamos de la entidad items, lo que hace más óptima la consulta.

ventas

clientes

(id_cliente)

(fecha_compra)

(nombre, apellido)

Base de datos relacional

Tres tablas = 3 relaciones

ventas

clientes

items

Cuando combinamos la base de datos y sus relaciones existentes obtenemos el término famoso sistema manejador de base de datos relacionales RDBMS (Relational Database Management System).

Situación problemática

Imagina que eres dueño de una tienda y que te has dado cuenta que la facturación diaria se está incrementando de forma beneficiosa y que tienes más de un millón de filas con datos. Entonces lo que necesitas es una base de Datos, pero no sabes nada acerca de las bases de datos. ¿A quién llamas?

1. ¿Un especialista SQL?

2. ¿Un diseñador de BD?

• Decide cómo organizar los datos en tablas.
• Establece las relaciones entre tablas lo cual es crucial. Una BD mal diseñada evitará que el sistema dé respuesta a lo que la empresa necesita.

El Diseñador de Bases de Datos

Traza el sistema de bases de datos completo en un lienzo usando una herramienta de visualización. Hay dos maneras:

Diagrama Entidad Relación

Esquema Relacional

Útil cuando tienes certeza de la estructura y organización de la BD 

clientes

Esquema de base de datos

Clientes

Items

Ventas

Empresas

El esquema de BD, resulta de la combinación de esquemas relacionales

Manejo de base de datos

• Una vez que se ha completado el diseño de la Base de Datos, se procede a crear la base de datos
• En este momento podemos decir que SQL será útil para configurar la base de datos físicamente. (DDL)
• Luego podemos disfrutar de la manipulación de datos (DML). Esto permite usar nuestro conjunto de datos para extraer ideas de negocios que vienen a mejorar el rendimiento y eficiencia del negocio.

Diseño de Bases de Datos + creación + manipulación = database management (manejo de base de datos)

Administrador de Bases de Datos

Es la persona que diariamente cuida y da mantenimiento de la base de datos.

Esquema Relacional: Clave primaria (Primary key)

Primary Key: una columna (o un conjunto de columnas) cuyo valor existe y es único para cada registro en una tabla se llama clave primaria. 

Esquema Relacional: Clave primaria (Primary key)

• Una PK por tabla.
• Puede estar compuesta por dos o más campos.
• La PK es el identificador único de una tabla
• No contiene valores nulos
• No todas las tablas tienen una clave primaria

X

X

X

Error

Error

Error

Esquema Relacional: Clave única y valores nulos

Unique Key: se usa cada vez que desea especificar que no desea ver datos duplicados en un campo determinado

Valores Nulos

Primary Key

Unique Key

No

Sí

Número de keys

0, 1, 2, ...

1

Múltiples columnas

Sí

Sí

Relaciones

Las relaciones le indican qué cantidad de datos de un campo de clave foránea se puede ver en la columna de clave primaria de la tabla con la que se relacionan los datos y viceversa

Primary key

Foreign Key

Relación ono-a-muchos: Un valor de la columna id_cliente en la tabla "Clientes" se puede encontrar muchas veces en la columna id_cliente en la tabla "Ventas".

Relaciones

Clientes

Ventas

1 cliente

1 compra

 > 1 compra

1 compra

1 compra

Número mínimo de instancias de la tabla "Clientes" que pueden asociarse con la entidad "Ventas"

Relaciones

Clientes

Ventas

mínimo = 1 cliente = máximo

1 compra

1 compra

1 compra

Número mínimo de instancias de la tabla "Clientes" que pueden asociarse con la entidad "Ventas"

Relaciones

Clientes

Ventas

Ventas a clientes: muchos-a-uno

Clientes a ventas : uno-a-muchos

Relaciones

Clientes

Ventas

Restricciones de cardinalidad (cardinality constraints) 

Tipos de Relaciones

• Uno-a-uno
• Uno-a-muchos
• Muchos-a-muchos

Para mayor información, leer el material de lectura obligatoria sobre MER, disponible en el espacio virtual de Diseño de Bases de Datos.

Creación de Bases de Datos (I)

Base de Datos = esquema

Clientes

Items

Ventas

Empresas

Ventas

Creación de Bases de Datos (II)

CREATE DATABASE [IF NOT EXISTS] nombre_base_de_datos;

• CREATE DATABASE: crea una base de datos como una unidad abstracta
• IF NOT EXISTS: verifica si existe una base de datos con el mismo nombre, los corchetes indican que esta instrucción es opcional.
• nombre_base_de_datos: dar un nombre que es corto pero al mismo tiempo relacionado con el contenido de los datos.
• El código SQL no es sensible a mayúsculas y minúsculas.
• Las comillas en los nombres de los objetos es opcional, por ejemplo 'Ventas'
• El final de la sentencia se marca con un punto y coma ";"

Introducción a los Tipos de Datos

• Siempre debemos especificar el tipo de dato que se insertará en cada columna de la tabla
• Los diferentes tipos de datos representan diferentes tipos de información que pueden estar contenidos en una columna específica

string

apellido

'Meneses'

length / longitud

size / tamaño

7 símbolos

7 bytes

Introducción a los Tipos de Datos

string

apellido

'Meneses'

length / longitud

size / tamaño

7 símbolos

7 bytes

• Longitud: Una medida utilizada para indicar cuántos símbolos tiene una cadena determinada
• Size: indica el espacio de memoria usado por el tipo de dato
• storage / almacenamiento: el espacio físico en la memoria de la unidad de la computadora, donde se guardan o almacenan los datos

Tipo de Dato String

• String: El formato para los textos en MySQL
• 'Meneses' - una variable del tipo de dato string o alfanumérico

Tipo de dato string

Caracter

CHAR

Almac. / Storage

Ejemplo

Fijo

CHAR(7)

CHAR(7): 7 representa el número máximo de símbolos que es
permitido usar para escribir un valor en este formato

length

Símbolos

size

bytes

'Meneses'

7

7

'Li'

2

7

Tipo de Dato String

Tipo de dato string

Caracter

CHAR

Almac. / Storage

Ejemplo

Fijo

CHAR(7)

length

Símbolos

size

bytes

'Meneses'

7

7

'Li'

2

7

VARCHAR

Caracter variable

Variable

VARCHAR(7)

'Meneses'

7

7

'Li'

2

3

Tipo de Dato String

Tipo de dato string

Caracter

CHAR

Tamaño máximo

255

VARCHAR

Caracter variable

65,535

Se adapta al valor insertado

50% más rápido

Tipo de Dato String

Tipo de dato string

Caracter

CHAR

Ejemplo

VARCHAR

Caracter variable

ENUM

"Enumerado"

ENUM

CHAR(7)

VARCHAR(7)

ENUM('M', 'F')

MySQL mostrará un error si intentamos insertar cualquier valor diferente de 'M' o 'F'

Integer (Enteros)

Tipos de datos numéricos

integer

fix-point

floating-point

números enteros sin punto decimal. Ejemplo: 5; 15; -200; 1,000

INTEGER

INT

Integer (Enteros)

Tipo de dato numérico

size

(bytes)

Valor mínimo

(Con signo / sin signo)

Valor máximo

(Con signo / sin signo)

Integer (Enteros)

Con signo

Sín signo

Si el rango abarcado incluye
valores positivos y negativos

Si se permite que los enteros sean solo positivos

Nota: los tipos de datos enteros de forma predeterminada son "con signo". Si se desea utilizar un rango que contenga solo valores positivos, "sin signo", hay que especificar esto en la consulta (lo veremos más adelante)

Integer (Enteros)

¿Por qué no usar BIGINT todo el tiempo?

La respuesta viene con la idea de mejorar la eficiencia en términos de velocidad de procesamiento de la computadora

Por ejemplo: Si está seguro de que, en una determinada columna, no necesitará un número entero menor que 0 o mayor que 100, TINYINT haría el trabajo a la perfección y no necesitaría más espacio de almacenamiento.

Un tipo entero más pequeño puede aumentar la velocidad de procesamiento

Tipo de Dato Fijo y Punto Flotante

Precisión: se refiere al número de dígitos en un número.

Escala: se refiere al número de dígitos a la derecha del punto decimal en un número

número

10.523

Precisión

3

Escala

5

35.875

3

5

Ejemplo: DECIMAL(5, 3)

Tipo de Dato Fijo y Punto Flotante

Dato con punto fijo: representa el valores exactos.

10.523

10.5 

DECIMAL(5, 3)

10.500

10.5236789

10.524

Tipo de Dato Fijo y Punto Flotante

Dato con punto fijo: representa el valores exactos.

1234567

DECIMAL(7)

Cuando únicamente se especifica un dígito entre paréntesis, se tratará como la precisión del tipo de dato

DECIMAL(7, 0)

Es lo mismo que 

Tipo de Dato Fijo y Punto Flotante

DECIMAL = NUMERIC

Ejemplo: salario

NUMERIC (p, s)

precisión: p = 7

escala: s = 2

Ejemplo NUMERIC (p, s) $75,000.50

Tipo de Dato Fijo y Punto Flotante

FLOAT (5, 3)

Tipo de dato de punto-flotante

- utilizado sólo para valores aproximados
- tiene como objetivo equilibrar el rango y la precisión (=> "flotante")

10.523

10.5236789

10.524      No hay warning

10.524  Es un valor aproximado

Tipo de Dato Fijo y Punto Flotante

FLOAT (5, 3)

La principal diferencia entre el tipo de punto fijo y el tipo de punto flotante está en la forma en que el valor se representa en la memoria de la computadora

10.524

10.5236789

10.524      

DECIMAL (5, 3)

10.5236789

Tipos de datos numéricos

integer

fix-point

floating-point

INTEGER

DECIMAL

NUMERIC

FLOAT

DOUBLE

Tipo de Dato Fijo y Punto Flotante

Tipo de Dato Fijo y Punto Flotante

La diferencia entre el float y el double es técnica

Un programador presta atención acerca cuál debe ser el tipo de dato exacto

Otros tipos de datos

DATE

Usado para representar una fecha en el formato AAAA-MM-DD. Rango: 1 de enero de 1000 al 31 de diciembre de 9999

Ejemplo: '2018-07-25'

DATETIME

Fecha y hora, AAAA-MM-DD HH: MM: SS [.fracción]

Rango: 0 - 23: 59: 59.999999

Ejemplo: '2018-07-25 9:30:00'

Otros tipos de datos

DATETIME

representa la fecha que se muestra en el calendario y la hora que se muestra en el reloj

VS

TIMESTAMP

48 años

Otros tipos de datos

TIMESTAMP

Inicio

Fin

Otros tipos de datos

TIMESTAMP

DATETIME

TIMESTAMP

DATE

VARCHAR

CHAR

INTEGER

DECIMAL

INTEGER

NUMERIC

FLOAT

DOUBLE

Los datos deber ser escritos entre comillas

Los datos sin comillas

Tipos de Datos Númericos

Tipos cadena, fecha y hora

Encontramos estos tipos de datos o similares en otros SGBD

Otros tipos de datos

BLOB

Objeto binario grande
- Se refiere a un archivo de datos binarios - datos con 1 y 0
- Implica guardar archivos en un registro

Creación de tablas

CREATE TABLE nombre_tabla (

   nombre_columna_1 tipo_dato restricción,

   nombre_columna_1 tipo_dato restricción,

   ...

   nombre_columna_n tipo_dato restricción

);

El nombre de la tabla puede coincidir con el nombre de la Base de Datos y debe contener mínimo un campo.

Creación de tablas

AUTO_INCREMENT: le libera de tener que insertar todos los números de compra manualmente a través del comando INSERT en una etapa posterior

• Asigna 1 al primer registro de la tabla e incrementa automáticamente en 1 por cada fila posterior

Creación de tablas

Ejercicio

Cree la tabla "clientes" en la base de datos "ventas". Deje que contenga las siguientes 5 columnas: id_cliente, nombre, apellido, email y numero_quejas. Deje que los tipos de datos de id_cliente y numero_quejas sean enteros, mientras que los tipos de datos de todas las demás columnas serán VARCHAR de 255.

Usar base de datos

Siempre que desee hacer referencia a un objeto SQL en sus consultas, debe especificar la base de datos a la que se aplica

También puede especificar la base de datos

Eliminar tablas

Instrucción DROP

Usada para eliminar objetos existentes

DROP tipo_objeto nombre_objeto;

Restricciones / constraints

Son reglas específicas, o límites, que definimos en nuestras tablas

• Ejemplo: NOT NULL

Foreign Key / constraints

Apunta a una columna de otra tabla y, por lo tanto, vincula las dos tablas

Clientes

Ventas

Tabla padre = tabla referenciada

Tabla hija = referencia tabla 

Recuerde, este no es un requisito obligatorio: estas dos claves pueden tener dos nombres completamente diferentes. ¡Lo importante es que los tipos de datos y la información coincidan! Es una práctica común usar, si no el mismo, nombres similares para ambas claves.

Foreign Key / constraints

La clave externa (foránea) mantiene la integridad referencial dentro de la base de datos

ON DELETE CASCADE

Si se ha eliminado un valor específico de la clave primaria de la tabla primaria, también se eliminarán todos los registros de la tabla secundaria que se refieran a este valor.

Modificación de tablas

Instrucción ALTER

Usada para modificar objetos existentes

• ADD
• REMOVE
• RENAME

ventas

Keywords - Palabras claves

Keywords

• CREATE
• ALTER
• ADD
• etc.

Objetos o bases de datos no pueden tener nombres que coincidan con palabras claves (reservadas) de SQL.

CREATE TABLE alter (numero_compra int);

Error

Buenas prácticas

Técnicas de codificación (I)

Casi nunca se programa solo. Es más común la programación en equipo.

Clean code (código limpio)

Código que es entendible, lo que significa que puede ser legible, lógico y modificable.

Buen código no es el que una computadora entiende, es el que los humanos pueden entender.

Técnicas de codificación (II)

Cuando se asigna nombre a variables u objetos SQL:

• Siempre elige nombres cortos y significativos.
• Los nombres constituyen más del 80% del código.
• Las palabras claves o reservadas de SQL se escriben en mayúsculas.
• Los nombres de campos (atributos) se escriben en minúsculas y si el nombre incluye varias palabras significativas, estas se separan con guión bajo.

Legible

• Organización horizontal y vertical del código
• Color

Maneras de mantener un buen código

1. Usar software apropiado que reorganice el código y color de las palabras de forma consistente.

• El tiempo es factor
• Unificar el estilo de programación es de alta prioridad

Es poco profesional unir código escrito en el mismo lenguaje, pero con diferentes estilos

​2. Usar la herramienta para reformater script de Workbench (Ctrl + B)

3. Intervenir manualmente y hacer los ajustes según tus gustos

Maneras de mantener un buen código

Maneras de mantener un buen código

Comentarios: líneas de texto que Workbench no ejecutará como código. Es un mensaje para cualquiera que lea nuestro código.

/* ... */        Comentario multilínea

# o --           Comentario de línea

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Objetivos de la unidad

• Conocer el Procesamiento lógico de consultas
• Identificar fases del procesamiento lógico de consultas
• Usar Identificadores de límites
• Listar Tipos de Datos
• Aplicar Comandos y Cláusulas en las consultas a BD

I UNIDAD: Fundamentos de Consultas de Bases de Datos

1. Orden de procesamiento de consultas I

I UNIDAD: Fundamentos de Consultas de Bases de Datos

VT significa "tabla virtual" y muestra cómo se producen diversos datos a medida que se procesa la consulta.

1. FROM: se realiza un producto cartesiano (combinación cruzada) entre las dos primeras tablas de la cláusula FROM y, como resultado, se genera la tabla virtual VT1.

2. ON: El filtro ON se aplica a VT1. Solo las filas para las cuales es VERDADERO se insertan en VT2.

1. Orden de procesamiento de consultas II

I UNIDAD: Fundamentos de Consultas de Bases de Datos

3. OUTER (join): si se especifica OUTER JOIN (a diferencia de CROSS JOIN o INNER JOIN), las filas de la tabla o tablas conservadas para las que no se encontró una coincidencia se agregan a las filas de VT2 como filas externas, generando VT3. Si aparecen más de dos tablas en la cláusula FROM, los pasos 1 a 3 se aplican repetidamente entre el resultado de la última combinación y la siguiente tabla en la cláusula FROM hasta que se procesen todas las tablas.

Logical Query Processing Phase

1. Orden de procesamiento de consultas III

I UNIDAD: Fundamentos de Consultas de Bases de Datos

4. WHERE: El filtro WHERE se aplica a VT3. Solo las filas para las cuales es VERDADERO se insertan en VT4.

5. GROUP BY: Las filas de VT4 se organizan en grupos según la lista de columnas especificada en la cláusula GROUP BY. Se genera VT5.

6. CUBO | ROLLUP: los supergrupos (grupos de grupos) se agregan a las filas de VT5, generando VT6.

7. HAVING: El filtro HAVING se aplica a VT6. Sólo los grupos para los cuales es TRUE se insertan en VT7.

1. Orden de procesamiento de consultas IV

I UNIDAD: Fundamentos de Consultas de Bases de Datos

8. SELECCIONAR: se procesa la lista SELECCIONAR, generando VT8.

9. DISTINTO: las filas duplicadas se eliminan de VT8. Se genera VT9.

10. ORDER BY: Las filas de VT9 se ordenan según la lista de columnas especificada en la cláusula ORDER BY. Se genera un cursor (VC10).

1. Orden de procesamiento de consultas V

I UNIDAD: Fundamentos de Consultas de Bases de Datos

11. TOP: El número especificado o el porcentaje de filas se selecciona desde el principio de VC10. La tabla VT11 se genera y se devuelve a la persona que llama. LIMIT tiene la misma funcionalidad que TOP en algunos dialectos de SQL como Postgres y Netezza.

Base de Datos Employees (I)

I UNIDAD: Fundamentos de Consultas de Bases de Datos

¡Es hora de pasar al siguiente nivel! ¡Comenzaremos a manipular datos en MySQL!

Para este propósito, utilizará un gran conjunto de datos que se puede encontrar en GitHub. Los programadores lo utilizan con frecuencia para mejorar y profundizar en SQL. Esto significa que este conjunto de datos es la mejor opción posible para sentar las bases del trabajo con MySQL y bases de datos relacionales.

Aprovecharemos su complejidad resolviendo varias tareas desafiantes. Estas tareas serán un ejercicio interesante que lo convertirá en un usuario competente de SQL.

Nota: la mayor complejidad hasta aquí es que la BD está en inglés, pero con un conocimiento básico bastará.

Base de Datos Employees (II)

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Hemos reorganizado el script de la base de datos en un archivo SQL que puede descargar y ejecutar. Por favor, acceda al enlace disponible aquí:

https://www.dropbox.com/s/asqkku6qwflkw6u/employees.sql?dl=0

Una vez que el proceso de descarga esté listo, abra el archivo de script desde Workbench como abriría otros archivos SQL y ejecute el código.

Base de Datos Employees (III)

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Consultas de selección

I UNIDAD: Fundamentos de Consultas de Bases de Datos

En esta sección:

• Estudiaremos consultas de manipulación de datos

La sentencia SELECT (una de las más importantes de SQL y MySQL):

• Permite extraer una fracción del conjunto de datos completo
  • Se utiliza para recuperar datos de objetos de bases de datos, como tablas
  • Al extraer información, SELECT va con FROM
  • El carácter comodín (*), significa "todos" y "todo"

SELECT columna_1, columna_2,... columna_n
FROM nombre_tabla;

Ejercicios

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Ejercicio 1. Seleccione la información de la columna "dept_no" de la tabla "departments".

Ejercicio 2. Seleccione todos los datos de la tabla "departments".

La cláusula WHERE

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Nos permitirá establecer una condición sobre la cual especificaremos qué parte de los datos queremos recuperar de la base de datos

SELECT columna_1, columna_2,... columna_n

FROM nombre_tabla
WHERE condición;

Ejemplo: Si volvemos a la tabla 'employees' y queremos información solamente de los empleados con nombre 'Denis', sería:

Ejercicio

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Ejercicio 3. Seleccione todas las personas de la tabla de "employees" cuyo primer nombre (first_name) es "Elvis".

WHERE

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Operador igual (=)

En SQL, hay muchas otras palabras clave y símbolos de enlace, llamados operadores, que puede usar con la cláusula WHERE

• AND
• OR
• IN          NOT IN
• LIKE      NOT LIKE
• BETWEEN... AND...

• EXISTS       NOT EXISTS
• IS NULL     IS NOT NULL
• Operadores de Comparación
• Etc.

Vamos a estudiar los más comunes

AND

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Permite combinar lógicamente dos instrucciones en el bloque de condición. Realmente estrecha más los resultados.

Ejemplo: queremos obtener los empleados con el nombre 'Denis' y género masculino 'male'

SELECT columna_1, columna_2,... columna_n

FROM nombre_tabla
WHERE condición_1 AND condición_2;

'Denis'

'M'

AND

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Ejemplo: queremos obtener los empleados con el nombre 'Denis', género masculino 'male' y contratados el '1990-05-21'

Ejercicio

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Ejercicio 4. Obtener una lista de empleadas cuyo nombre es Kellie

OR

I UNIDAD: Fundamentos de Consultas de Bases de Datos

AND

La condición se establece en columnas diferentes. Enlaza ambos criterios que deben cumplirse.

OR

La condición se establece en la misma columna y combina el resultado de ambos criterios

Ejemplo: queremos obtener los empleados con el nombre 'Denis' o 'Elvis'

Precedencia de los operadores

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Debemos conocer el orden lógico que debe cumplirse cuando se usan ambos operadores en el mismo bloque WHERE

• SQL que establece que en la ejecución de la consulta, el operador AND se aplica primero, mientras que el operador OR se aplica segundo

AND > OR

• Independientemente del orden en que use estos operadores, SQL siempre comenzará leyendo las condiciones alrededor del operador AND

Precedencia de los operadores

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Analicemos la diferencia entre las dos instrucciones siguientes:

Ejercicio

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Ejercicio 5. Recupere una lista con todas las empleadas cuyo primer nombre sea Kellie o Aruna.

OR

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Analice el resultado de la siguiente consulta

Ejercicio

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Ejercicio 5. Obtener una lista de empleadas cuyo nombre es Kellie o Aruna

IN

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Ejemplo: extraer los empleados con nombre Cathie, Mark o Nathan

Ejemplo: extraer los empleados con nombre Cathie, Mark o Nathan

IN permite a SQL retornar lo escrito entre paréntesis, si existen en nuestra tabla.

Usando IN (en menor tiempo que el anterior)

NOT IN

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Ejemplo: extraer los empleados cuyo nombre no sea Cathie, Mark o Nathan

Ejercicio

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Ejercicio 6: Use el operador IN para seleccionar a todas las personas de la tabla de "employees", cuyo primer nombre es "Denis" o "Elvis".

Ejercicio 7. Extraiga todos los registros de la tabla de "employees", aparte de aquellos con empleados llamados John, Mark o Jacob.

LIKE y NOT LIKE

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Sirve para buscar patrones

Ejemplo: deseamos una lista de personas cuyo nombre inicia con 'Mar' que podría ser parte de nombres como Mark, Martin, Margarret, etc.

Si removemos al 'M' se mostrarían los que inician con 'ar'

% sustituye una secuencia de caracteres

LIKE y NOT LIKE

I UNIDAD: Fundamentos de Consultas de Bases de Datos

¿Qué sucede en estos casos?

LIKE y NOT LIKE

I UNIDAD: Fundamentos de Consultas de Bases de Datos

¿Qué sucede en este caso?

_ sustituye un caracter

Los empleados cuyo nombre no incluye la palabra 'Mar'

Recuerde que MySQL no es sensible a mayúsculas y minúsculas.

Ejercicios

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Ejercicio 8. Al trabajar con la tabla de "employees", use el operador LIKE para seleccionar los datos de todas las personas, cuyo primer nombre comienza con "Mark"; especifique que el nombre puede ser sucedido por cualquier secuencia de caracteres.

Ejercicio 9. Recupere una lista con todos los empleados que fueron contratados en el año 2000.

Ejercicio 10. Recupere una lista con todos los empleados cuyo número de empleado esté escrito con 5 caracteres y comience con "1000".

Caracteres comodines

I UNIDAD: Fundamentos de Consultas de Bases de Datos

% sustituye una secuencia de caracteres

LIKE ('Mar%')

Mark, Martin, Margare

_ sustituye un caracter

LIKE ('Mar_') 

Mark, Marv, Marl

* entregará una lista de todas las columnas en una tabla

SELECT * FROM employees;

Más adelante veremos que se puede usar para contar todas las filas de una tabla

Ejercicios

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Ejercicio 11. Extraiga a todos los individuos de la tabla de "employees" cuyo primer nombre contiene "Jack".

Ejercicio 12. Una vez que haya hecho eso, extraiga otra lista que contenga los nombres de los empleados que no contengan "Jack".

BETWEEN ... AND

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Nos ayuda a designar el intervalo al que pertenece un valor dado

'1990-01-01' Y '2000-01-01' se incluirán en la lista recuperada de registros

Ejemplo: seleccionar las personas contratadas entre el primero de enero de 1990 y el primero de enero de 2000

NOT BETWEEN ... AND

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Se refiere a un intervalo compuesto de dos partes:
     - un intervalo por debajo del primer valor indicado
     - un segundo intervalo por encima del segundo valor

- la fecha de contratación es anterior a '1990-01-01' o
- la fecha de contratación es posterior a ‘2000-01-01'

BETWEEN ... AND

I UNIDAD: Fundamentos de Consultas de Bases de Datos

- No se usa solo para valores de fecha
- También podría aplicarse a cadenas y números

Ejercicios

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Ejercicio 13. Seleccione toda la información de la tabla de "salaries" con respecto a contratos de 66,000 a 70,000 dólares por año.

Ejercicio 14. Recupere una lista con todas las personas cuyo número de empleado no esté entre "10004" y "10012".

Ejercicio 15. Seleccione los nombres de todos los departamentos con números entre "d003" y "d006".

IS NOT NULL / IS NULL

I UNIDAD: Fundamentos de Consultas de Bases de Datos

IS NOT NULL: se usa para extraer valores que no son nulos

SELECT columna_1, columna_2,... columna_n

FROM nombre_tabla
WHERE nombre_columna IS NOT NULL;

Ejemplo: Extraer información de los empleados cuyo nombre no es nulo.

IS NOT NULL / IS NULL

I UNIDAD: Fundamentos de Consultas de Bases de Datos

IS NULL: se usa para extraer valores que son nulos

SELECT columna_1, columna_2,... columna_n

FROM nombre_tabla
WHERE nombre_columna IS NULL;

Ejemplo: Extraer información de los empleados cuyo nombre es nulo.

Ejercicio

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Ejercicio 16: Seleccione los nombres de todos los departamentos cuyo valor de número de departamento no sea nulo.

Otros operadores de comparación

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Otros operadores de comparación

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Empleados con nombre igual a 'Mark'

Empleados con nombre diferente de 'Mark'

Empleados contratados después del 1 de enero de 2000

Empleados contratados del 1 de enero de 2000 en adelante

Ejercicios

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Ejercicio 17: Recupere una lista con datos sobre todas las empleadas que fueron contratadas en el año 2000 o después.

Sugerencia: si resuelve la tarea correctamente, SQL debería devolver 7 filas.

Ejercicio 19: Extraiga una lista con los salarios de todos los empleados superiores a $ 150,000 por año.

SELECT DISTINCT

I UNIDAD: Fundamentos de Consultas de Bases de Datos

SELECT: puede recuperar filas de una columna designada, dados algunos criterios

SELECT DISTINCT columna_1, columna_2,... columna_n
FROM nombre_tabla;

SELECT DISTINCT: selecciona todos los valores de datos distintos

Ejercicios

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Ejercicio 20. Obtenga una lista con todas las diferentes "fechas de contratación" de la tabla "empleados".

Introducción a las funciones agregadas

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Funciones agregadas: se aplican en varias filas de una sola columna de una tabla y devuelven una salida de un solo valor

COUNT()

SUM()

MIN()

MAX()

AVG()

cuenta el número de registros no nulos en un campo

suma todos los valores no nulos en una columna

devuelve el valor mínimo de toda la lista

devuelve el valor máximo de toda la lista

calcula el promedio de todos los valores no nulos que pertenecen a una determinada columna de una tabla

Introducción a las funciones agregadas

I UNIDAD: Fundamentos de Consultas de Bases de Datos

COUNT()

cuenta el número de registros no nulos en un campo

Se usa frecuentemente en combinación con la palabra reservada "DISTINCT"

SELECT COUNT(nombre_columna)
FROM nombre_tabla;

Los paréntesis después de COUNT() deben comenzar justo después de la palabra clave, no después de un espacio en blanco

Ejemplo: cuantos empleados están registrados en nuestra BD

Lo mismo ¿por qué?

Introducción a las funciones agregadas

I UNIDAD: Fundamentos de Consultas de Bases de Datos

COUNT(DISTINCT)

SELECT COUNT(DISTINCT nombre_columna)
FROM nombre_tabla;

Ejemplo: ¿cuántos nombres distintos podemos encontrar en la tabla 'employees'?

Observe que la palabra reservada DISTINCT se coloca entre los paréntesis y a la izquierda del nombre del campo

Ejercicios

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Ejercicio 21. ¿Cuántos contratos anuales con un valor superior o igual a $ 100,000 se han registrado en la tabla de salarios?

Ejercicio 22. ¿Cuántos gerentes tenemos en la base de datos de "empleados"? Use el símbolo de estrella (*) en su código para resolver este ejercicio.

ORDER BY

I UNIDAD: Fundamentos de Consultas de Bases de Datos

ASC, abreviatura de "ASCENDING", se agrega de forma implícita

DESC, abreviatura de "DESCENDING"

No solo se usa para cadenas sino con otros tipos como números. Además se pueden incluir múltiples columnas.

Ejercicio

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Ejercicio 23. Seleccione todos los datos de la tabla "employees", ordenándolos por "hire_date" en orden descendente.

GROUP BY

I UNIDAD: Fundamentos de Consultas de Bases de Datos

• Cuando se trabaja en SQL, los resultados se pueden agrupar según un campo o campos específicos.
• GROUP BY debe colocarse inmediatamente después de las condiciones WHERE y justo antes de la cláusula ORDER BY
• GROUP BY es una de las herramientas más potentes y útiles en SQL

SELECT nombre_columna(s)

FROM nombre_tabla
WHERE condiciones
GROUP BY nombre_columna(s)

ORDER BY nombre_columna(s);

GROUP BY

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Al agregar GROUP BY se seleccionan solo valores distintos para el nombre

1275 filas, equivalente a: (vea el tiempo de respuesta)

300024 filas

GROUP BY

I UNIDAD: Fundamentos de Consultas de Bases de Datos

En esta instrucción la función de agregación se agrupa por nombres, así obtenemos el conteo de veces que cada nombre es encontrado en la tabla employees.

GROUP BY

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Con la cláusula de ordenamiento

GROUP BY

I UNIDAD: Fundamentos de Consultas de Bases de Datos

• En la mayoría de los casos, cuando necesita una función agregada, también debe agregar una cláusula GROUP BY en su consulta
• ¡Incluya siempre el campo por el que ha agrupado sus resultados en la instrucción SELECT!

Uso de alias (as)

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Veamos la salida de la última instrucción

Un alias es usado para renombrar una selección de la consulta

selección 1

selección 2

HAVING

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Refina la salida de registros que no satisfacen una determinada condición

• Implementado frecuentemente con GROUP BY

SELECT nombre_columna(s)

FROM nombre_tabla
WHERE condiciones
GROUP BY nombre_columna(s)

HAVING condiciones
ORDER BY nombre_columna(s);

HAVING es como WHERE pero aplicado al bloque GROUP BY

HAVING

I UNIDAD: Fundamentos de Consultas de Bases de Datos

o

Ejemplo:

HAVING

I UNIDAD: Fundamentos de Consultas de Bases de Datos

WHERE vs HAVING

Después de HAVING, puede tener una condición con una función agregada, mientras que WHERE no puede usar funciones agregadas dentro de sus condiciones

HAVING

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Ejemplo: queremos extraer una lista con todos lo nombres (first_name) que aparecen más de 250 veces en la tabla employees

Esta es la forma correcta, se usa having porque vamos a filtrar usando función de agregación

Ejercicios

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Ejercicio 24: seleccionar todos los empleados para quienes el salario promedio sea mayor que 120,000 por año. (101) registros

Ejercicio 25: compare la salida obtenida de ambas consultas.

sql_mode=only_full_group_by

Having vs Where

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Where

Permite establecer un conjunto de condiciones que se refieren a un subconjunto de  individual de filas.

Se aplica antes de reorganizar la salida en grupos

Having vs Where

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Reorganiza la salida en grupos

(GROUP_BY)

Having vs Where

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Ejemplo: extraer una lista de los nombre de empleados que aparecen menos que 200 veces. Permita que los datos se refieran a personas contratadas después del primero de enero de 1999.

Having vs Where

I UNIDAD: Fundamentos de Consultas de Bases de Datos

HAVING

No puede tener una condición de agregación y no agregación en la cláusula HAVING

SELECT nombre_columna(s)

FROM nombre_tabla
WHERE condiciones
GROUP BY nombre_columna(s)

HAVING conditions
ORDER BY nombre_columna(s);

¿Cuál es el problema aquí?

Having vs Where

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Para funciones de agregación

Usar GROUP BY - HAVING

Para condiciones generales

Usar WHERE

Ejercicio

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Ejercicio 26:

Seleccione los números de empleados de todas las personas que hayan firmado más de 1 contrato después del 1 de enero de 2000.

Sugerencia: Para resolver este ejercicio, use la tabla "dept_emp".

LIMIT

I UNIDAD: Fundamentos de Consultas de Bases de Datos

SELECT nombre_columna(s)

FROM nombre_tabla
WHERE condiciones
GROUP BY nombre_columna(s)

HAVING condiciones

ORDER BY nombre_columna(s)

LIMIT número ;

LIMIT

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Ejemplo:

Seleccione los números de empleados de los 10 (empleados) mejores pagados

Ejercicio

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Ejercicio 27:

Seleccione las primeras 100 filas de la tabla "dept_emp".

Lenguaje de control de transacciones (TCL)

Lenguaje de control de transacciones (TCL)

SUBCONSULTAS

INSERT

Antes de entrar al tema de subconsultas vamos a repasar la sentencia INSERT y UNION que son esenciales para temas avanzados de esta clase

Sentencia Insert

INSERT INTO nombre_tabla (columna_1, columna_2, ..., columna_n)
VALUES (valor_1, valor_2, ..., valor_n);

Ejemplo

Lo números se detallan sin comillas como convención. MySQL convierte de forma automática de String a number, pero consume tiempo.

Ejemplos

• No importa el orden de los campos.
• Debemos poner los valores en el mismo orden en que se listan los nombres de los campos.
• Deben especificarse tantos valores como nombres de campos.
• También podemos insertar registros y tomar como referencia el orden original de los campos en la tabla, ejemplo:

Ejercicio

Ejercicio 28: Seleccione diez registros de la tabla de "titles" para tener una mejor idea sobre su contenido.

• Luego, en la misma tabla, inserte información sobre el empleado número 999903. Indique que él / ella es un "Senior Engineer", que comenzó a trabajar en este puesto el 1 de octubre de 1997.
• Al final, clasifique los registros de la tabla de "títulos" en orden descendente para verificar si ha insertado correctamente el nuevo registro.

Sugerencia: ¡Para resolver este ejercicio, deberá insertar datos en solo 3 columnas!

Ejercicio - solución

Ejercicio 28: Seleccione diez registros de la tabla de "titles" para tener una mejor idea sobre su contenido.

• Luego, en la misma tabla, inserte información sobre el empleado número 999903. Indique que él / ella es un "Senior Engineer", que comenzó a trabajar en este puesto el 1 de octubre de 1997.
• Al final, clasifique los registros de la tabla de "títulos" en orden descendente para verificar si ha insertado correctamente el nuevo registro.

Ejercicio

Ejercicio 29: Inserte información sobre el individuo con el número de empleado 999903 en la tabla "dept_emp". Él / Ella está trabajando para el departamento número 'd005', y comenzó a trabajar el 1 de octubre de 1997; su contrato es por tiempo indefinido.

Sugerencia: use la fecha "9999-01-01" para designar que el contrato es por un período indefinido.

Ejercicio - solución

Ejercicio 29: Inserte información sobre el individuo con el número de empleado 999903 en la tabla "dept_emp". Él / Ella está trabajando para el departamento número 'd005', y comenzó a trabajar el 1 de octubre de 1997; su contrato es por tiempo indefinido.

Sugerencia: use la fecha "9999-01-01" para designar que el contrato es por un período indefinido.

Insertar dato en una nueva tabla

INSERT INTO tabla_2 (columna_1, columna_2, ..., columna_n)

SELECT columna_1, columna_2, ..., columna_n
FROM tabla_1
WHERE condición;

Ejemplo

UNION vs UNION ALL

UNION vs UNION ALL

Para explicar esta sección vamos a crear la tabla employees_dup que es un duplicado de la tabla employees y vamos a insertar en employees_dup los registros disponibles en employees

UNION vs UNION ALL

Vamos a duplicar el primer registro de employees_dup y verificamos.

UNION vs UNION ALL

UNION ALL suele combinar algunas declaraciones SELECT en una sola salida.

- Puedes considerarlo como una herramienta que te permite unificar tablas

SELECT
    N columnas
FROM tabla_1 

         UNION ALL SELECT

         N columnas

FROM
    tabla_2;

• Tenemos que seleccionar el mismo número de columnas de cada tabla.
• Estas columnas deben tener el mismo nombre,
• Deben estar en el mismo orden y debe contener tipos de datos relacionados.

UNION vs UNION ALL

Ejemplo: a los registros de la tabla dept_manager (dept_no, from_date) queremos adjuntarle los registros de la tabla employees_dup (emp_no, first_name, last_name) para el empleado 10001. 

En el esquema relacional vemos que las tablas  difieren en el número de columnas y tienen nombres distintos en su mayoría.

UNION vs UNION ALL

Ejemplo: a los registros de la tabla dept_manager queremos adjuntarle los registros de la tabla employees_dup. 

Para cumplir la regla de UNION ALL de que las columnas deben tener el mismo nombre, estar en el mismo orden y deben contener tipos de datos relacionados, simplemente rellenamos los campos faltantes en cada tabla

26 filas

UNION vs UNION ALL

Al unir dos tablas organizadas idénticamente

- UNION muestra solo valores distintos en la salida
- UNION usa más recursos MySQL (potencia computacional y espacio de almacenamiento)

- UNION ALL recupera los duplicados también

UNION vs UNION ALL

Con UNION se omiten los duplicados

25 filas

UNION vs UNION ALL

Buscando mejores resultados? - use UNION

¿Buscas optimizar el rendimiento? - opta por UNION ALL

Subconsultas

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Subconsultas

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Subconsultas: son consultas incrustadas en una consulta. Es decir, son consultas internas (INNER QUERY), consultas anidadas (NESTED QUERY) o selección interna (INNER SELECT)

SUBCONSULTA = CONSULTAS INTERNAS = CONSULTAS ANIDADAS = SELECCIÓN INTERNA

La consulta externa es OUTER QUERY

Subconsultas

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Ejemplo: seleccione el nombre (first_name) y apellido (last_name) de los empleados (employees) cuyos números de empleados (emp_no) aparecen en la tabla gerentes de departamentos (dept_manager)

Subconsultas

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Podemos visualizar los emp_no (números de empleados) que aparecen en la tabla dept_manager (24), pero para obtener el nombre y apellido también debemos consultar la tabla employees. Una forma de solución sería con subconsultas.

Subconsultas

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Ejemplo: seleccione el nombre (first_name) y apellido (last_name) de los empleados (employees) cuyos números de empleados (emp_no) aparecen en la tabla gerentes de departamentos (dept_manager)

A esto llamamos: Subconsultas anidadas con IN dentro de un WHERE

Subconsultas anidadas con IN dentro de un WHERE

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Una subconsulta siempre debe colocarse entre paréntesis

1) El motor SQL comienza ejecutando la consulta interna
2) Luego usa su salida devuelta, que es intermedia, para ejecutar la consulta externa

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Una subconsulta puede devolver un solo valor (un escalar), una sola fila, una sola columna o una tabla completa

- Puede tener más de una subconsulta en su consulta externa
- Es posible anidar consultas internas dentro de otras consultas internas. En ese caso, el motor SQL ejecutaría primero la consulta más interna y luego cada consulta posterior, hasta que ejecute la última consulta más externa

Subconsultas anidadas con IN dentro de un WHERE

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Ejercicio: Extraiga la información sobre todos los gerentes de departamento que fueron contratados entre el 1 de enero de 1990 y el 1 de enero de 1995.

Subconsultas anidadas con IN dentro de un WHERE

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Ejercicio: Extraiga la información sobre todos los gerentes de departamento que fueron contratados entre el 1 de enero de 1990 y el 1 de enero de 1995.

Subconsultas anidadas con IN dentro de un WHERE

Solución 1:

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Ejercicio: Extraiga la información sobre todos los gerentes de departamento que fueron contratados entre el 1 de enero de 1990 y el 1 de enero de 1995.

Subconsultas anidadas con IN dentro de un WHERE

Solución 2:

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Subconsultas anidadas con EXISTS - NOT EXISTS en el WHERE

EXISTS comprueba si ciertos valores de fila (consulta externa) se encuentran dentro de una subconsulta

- Esta comprobación se realiza fila por fila

- Devuelve un valor booleano

Si el valor de la fila de la subconsulta  existe

Si el valor de la fila de la subconsulta  no existe

True

False

se extrae el registro correspondiente de la consulta externa

no se extrae ningún valor de fila de la consulta externa

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Subconsultas anidadas con EXISTS - NOT EXISTS en el WHERE

Ejemplo: seleccione el nombre (first_name) y apellido (last_name) de los empleados (employees) cuyos números de empleados (emp_no) aparecen en la tabla gerentes de departamentos (dept_manager)

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Subconsultas anidadas con EXISTS - NOT EXISTS en el WHERE

Prueba la existencia de valores de fila

Más rápido en recuperar grandes cantidades de datos

Búsquedas entre valores
 

Más rápido con conjuntos de datos pequeños

EXISTS

IN

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Subconsultas anidadas con EXISTS - NOT EXISTS en el WHERE

ORDER BY (consultas anidadas)

Es más profesional aplicar ORDER BY en la consulta externa.

- Es lógicamente más aceptable ordenar la versión final de su conjunto de datos

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Subconsultas anidadas con EXISTS - NOT EXISTS en el WHERE

Algunas, aunque no todas, las consultas anidadas pueden reescribirse usando JOIN, que son más eficientes en general

Esto es cierto especialmente para consultas internas que utilizan la cláusula WHERE

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Subconsultas anidadas con EXISTS - NOT EXISTS en el WHERE

Subconsultas

- Permite una mejor estructuración de la consulta externa. Por lo tanto, cada consulta interna se puede considerar de forma aislada, de ahí el nombre de SQL - ¡Lenguaje de consulta estructurado!

- En algunas situaciones, el uso de subconsultas es mucho más intuitivo en comparación con el uso de JOIN complejas

- Muchos usuarios prefieren las subconsultas simplemente porque ofrecen una legibilidad de código mejorada

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Ejercicio: Seleccione la información completa para todos los empleados cuyo cargo es Asistente de ingeniero (Assistant Engineer).

Sugerencia: Para resolver este ejercicio, use la tabla 'employees'.

Subconsultas anidadas con EXISTS - NOT EXISTS en el WHERE

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Ejercicio: Seleccione la información completa para todos los empleados cuyo cargo es Asistente de ingeniero (Assistant Engineer).

Solución

Subconsultas anidadas con EXISTS - NOT EXISTS en el WHERE

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Ejercicio: Asigne el número de empleado 110022 como gerente de todos los empleados desde el 10001 hasta el 10020 y el empleado número 110039 como gerente de todos los empleados desde el 10021 al 10040.

Subconsultas anidadas en SELECT y FROM

 (Challenge - Reto)

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Solución:

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Ejercicio-parte 1: Comenzando su código con "DROP TABLE", cree una tabla llamada “emp_manager” (emp_no – integer, not null; dept_no – CHAR de 4, null; manager_no – integer, not null). 

Subconsultas anidadas en SELECT y FROM

 (Challenge - Reto)

Resuelva

I UNIDAD: Fundamentos de Consultas de Bases de Datos

Ejercicio-parte 2: 

Llene emp_manager con datos sobre los empleados, el número del departamento en el que están trabajando y sus gerentes.

Su esqueleto de consulta debe ser:

Insertar en emp_manager SELECT

U. *

FROM

                  (A)

UNION (B) UNION (C) UNION (D) AS U;

A y B deben ser los mismos subconjuntos utilizados en la última clase (subconsultas SQL anidadas en SELECT y FROM). En otras palabras, asigne el número de empleado 110022 como gerente a todos los empleados de 10001 a 10020 (esto debe ser el subconjunto A), y el número de empleado 110039 como gerente a todos los empleados de 10021 a 10040 (esto debe ser el subconjunto B).

Use la estructura del subconjunto A para crear el subconjunto C, donde debe asignar el número de empleado 110039 como gerente al empleado 110022.

Siguiendo la misma lógica, cree el subconjunto D. Aquí debe hacer lo contrario: asignar el empleado 110022 como gerente al empleado 110039.

Su salida debe contener 42 filas.

¡Buena suerte!

Unidad II:

Scripting SQL, uso de variables, SQL dinámico

Índices en MySQL

Unidad II: Scripting SQL, uso de variables, SQL dinámico

• El índice de una tabla funciona como el índice de un libro, sin él nos llevaría horas hacer búsquedas en el mismo.
  • Los datos se toman de una columna de la tabla y se almacenan en un determinado orden en un lugar distinto, llamado índice

• Sus conjuntos de datos (dataset o tabla) generalmente contendrán más de 100,000 o incluso más de 1,000,000 de registros.
  • Cuanto más grande es una base de datos, más lento es el proceso de búsqueda del registro o registros que necesita

Índices en MySQL

Unidad II: Scripting SQL, uso de variables, SQL dinámico

Podemos usar un índice para aumentar la velocidad de las búsquedas relacionadas con una tabla

CREATE INDEX nombre_índice
ON nombre_tabla (columna_1, columna_2, ...);

• Los paréntesis nos sirven para indicar los nombres de las columnas en las que se basará nuestra búsqueda

• Estos deben ser campos de su tabla por los cuales

  buscará con frecuencia (esto se llama optimización)

Índices en MySQL

Unidad II: Scripting SQL, uso de variables, SQL dinámico

Ejemplo:

¿Cuántas personas han sido contratadas después del primero de enero de 2000?

Vamos a crear índice sobre el WHERE

El tiempo de respuesta es mucho menor

Índices en MySQL

Unidad II: Scripting SQL, uso de variables, SQL dinámico

Índices compuestos

CREATE INDEX nombre_índice
ON nombre_tabla (columna_1, columna_2, ...);

• Índice aplicado a múltiples columnas, no solo a una sola

¡Elija cuidadosamente las columnas que optimizarían su búsqueda!

Índices en MySQL

Unidad II: Scripting SQL, uso de variables, SQL dinámico

Ejemplo

Seleccionar todos los empleados que llevan el nombre "Georgi Facello"

El tiempo de respuesta es mucho menor

Índices en MySQL

Unidad II: Scripting SQL, uso de variables, SQL dinámico

Las claves primarias (PRIMARY KEY) y UNIQUE son índices en MySQL

• Representan columnas en las que una persona normalmente basaría su búsqueda

Índices en MySQL

Unidad II: Scripting SQL, uso de variables, SQL dinámico

Índices en MySQL

Unidad II: Scripting SQL, uso de variables, SQL dinámico

Los especialistas en SQL siempre buscan un buen equilibrio entre la mejora de la velocidad de búsqueda y los recursos utilizados para su ejecución.

Conjunto de datos (dataset o tablas) pequeñas

Conjunto de datos (dataset o tablas) grandes

Los costos de tener un índice podrían ser más alto que los beneficios

Un índice bien optimizado puede tener un impacto positivo en el proceso de búsqueda

Índices en MySQL

Unidad II: Scripting SQL, uso de variables, SQL dinámico

Ejercicio: Elimine el índice "i_hire_date".

Índices en MySQL

Unidad II: Scripting SQL, uso de variables, SQL dinámico

Ejercicio:

Seleccione todos los registros de la tabla de "salaries" de personas cuyo salario sea superior a $ 89,000 por año.

Luego, cree un índice en la columna "salary" de esa tabla y verifique si ha acelerado la búsqueda de la misma instrucción SELECT.

Índices en MySQL

Unidad II: Scripting SQL, uso de variables, SQL dinámico

Ejercicio:

Seleccione todos los registros de la tabla de "salaries" de personas cuyo salario sea superior a $ 89,000 por año.

Luego, cree un índice en la columna "salary" de esa tabla y verifique si ha acelerado la búsqueda de la misma instrucción SELECT.

Solución

Unidad II:

Scripting SQL, uso de variables, SQL dinámico

Declaración UPDATE

Antes de continuar estudiemos

Lenguaje de Control de Transacciones (TCL)

TCL COMMIT y ROLLBACK

La declaración COMMIT (Compromiso)
- Guarda la transacción en la base de datos
- Los cambios no se pueden deshacer

La cláusula ROLLBACK
- Te permite dar un paso atrás
- Los últimos cambios realizados no contarán
- Vuelve al último estado no comprometido (non-committed)

Utilizado para guardar el estado de los datos en la base de datos en el momento de su ejecución

Se referirá al estado correspondiente a la última vez que ejecutó el COMMIT

TCL COMMIT y ROLLBACK

- ROLLBACK tendrá un efecto en la última ejecución que se haya realizado.
- No puede restaurar los datos a un estado correspondiente a un COMMIT anterior.

En este caso todos los ROLLBACK restauran los datos del último COMMIT, por lo que los COMMIT anteriores al décimo COMMIT ya no pueden manipularse

TCL COMMIT y ROLLBACK

Hay que expandir las funcionalidades de Workbench, para ello vaya al menú edit/preferences. Seleccione la sección SQL Editor y desactive la opción Safe Updates

TCL COMMIT y ROLLBACK

Lo que hicimos anteriormente fue quitar la opción que impide la actualización y eliminación sin restricciones (condiciones) esta opción impide que borremos datos de forma inesperada en alguna instrucción de eliminación o actualización, por ejemplo imagine un DELETE sin WHERE borraría todo, por lo que esta opción impide ese tipo de errores, pero también quita la posibilidad de controlar el estado de los datos en nuestra Base de Datos, lo cual es precisamente el objetivo de esta clase, por eso la hemos desactivado.

La declaración UPDATE

Se utiliza para actualizar los valores de registros existentes en una tabla

UPDATE nombre_tabla
SET columna_1 = valor_1, columna_2 = valor_2 ... WHERE condiciones;

- No tenemos que actualizar cada valor del registro en cuestión, basta con fijar las columnas que deseamos modificar
- Al ejecutarse un UPDATE podemos decir que hemos actualizado de forma permanente el/los registros específicos

- Si no proporciona una condición WHERE, todas las filas de la tabla se actualizarán

La declaración UPDATE

Ejemplo 1: actualizar la información del empleado número: 999901

La declaración UPDATE

Ejemplo 2: actualizar la tabla departments_dup y luego hacer un rollback

Unidad II:

Scripting SQL, uso de variables, SQL dinámico

Vistas

Vistas

Es una una tabla virtual cuyo contenido se obtiene de una tabla o tablas existentes, llamadas tablas base

La recuperación ocurre a través de una declaración SQL, incorporada en la vista

Vistas

Obtener el código de los empleados que han sido registradas más de una vez en la tabla dept_emp

¿Por qué crear una vista de la consulta anterior?

Vista SQL:

- La vista en sí no contienen ningún dato real; los datos se almacenan físicamente en la tabla base

- La vista simplemente muestran los datos contenidos en la tabla base

CREATE VIEW nombre_vista AS
SELECT

    columna_1, columna_2,... columna_n
FROM

nombre_tabla;

Vistas

Imagine que desea visualizar solo el período que abarca el último contrato de cada empleado

Crear vistas puede parecer redundante, porque solo bastaría con ejecutar directamente la consulta SQL y obtener los datos

Usando vistas SQL

Usando vistas SQL

Una vista actúa como un acceso directo para escribir la misma instrucción SELECT cada vez que se realiza una nueva solicitud.

Las vistas SQL

- Ahorran mucho tiempo de codificación

- No ocupan memoria adicional

- Actúan como una tabla dinámica porque reflejan instantáneamente datos y cambios estructurales en la tabla base

Por ejemplo si actualiza en la tabla base el campo to_date del emp_no 10001dicho cambio se refleja al consulta la vista

Las vistas SQL

- No olvide que no son conjuntos de datos físicos reales, lo que significa que no podemos insertar o actualizar la información que ya se ha extraído.

- Deberían verse como tablas de datos virtuales temporales que recuperan información de las tablas base

Las vistas SQL

Ejercicio: Cree una vista que extraiga el salario promedio de todos los gerentes registrados en la base de datos. Redondea este valor al centavo más cercano.

Las vistas SQL

Ejercicio: Cree una vista que extraiga el salario promedio de todos los gerentes registrados en la base de datos. Redondea este valor al centavo más cercano.

Solución:

Rutinas almacenadas

(store routines)

Introducción a rutinas almacenadas

rutina (en un contexto que no sea informática): es una acción habitual o fija, o una serie de acciones, que se repite periódicamente

Introducción a rutinas almacenadas

rutina almacenada
Es una declaración SQL, o un conjunto de declaraciones SQL, que se pueden almacenar en el servidor de bases de datos

- Cada vez que un usuario necesita ejecutar la consulta en cuestión, puede llamar (call), hacer referencia (reference) o invocar la rutina (invoke)

Introducción a rutinas almacenadas

Introducción a rutinas almacenadas

stored rutines

stored procedures

functions

= procedimientos almacenados

= son funciones definidas por el usuario

No es AVG, SUM, MAX, MIN, etc. (Agregación)

Procedimientos almacenados 

(stored procedure)

Sintaxis SQL para los procedimientos almacenados

Punto y coma (;)

- Se usa para terminar una declaración
- Técnicamente, también pueden llamarse delimitador

Escribiendo DELIMITER $$, podrá usar los símbolos de dólar como delimitador y así no nos confundimos con el punto y coma que hemos venido usando. 

Sintaxis SQL para los procedimientos almacenados

Si el delimitador es el punto y coma, se ejecutan las Query #1 y Query #2, pero al llamar al procedimiento almacenado solo se ejecuta p_Query #1 porque encuentra el punto y coma e inmediatamente sale del procedimiento y p_Query #2 nunca se ejecutará.

Sintaxis SQL para los procedimientos almacenados

Para solucionar el problema anterior se cambia el delimitador en la definición del procedimiento almacenado con $$ o //

Procedimientos almacenados

Ejemplo sencillo de procedimiento almacenado sin tablas

Procedimientos almacenados

Modificar un procedimiento almacenado

Realmente lo que debemos hacer es escribir el código PA (Procedimiento Almacenado) con los ajustes, eliminar el PA anterior y ejecutar la creación del nuevo.

Sintaxis SQL para los procedimientos almacenados

Veamos un ejemplo muy general

Cuerpo (Body) del PA

Consulta (Query)

Puede o no tener parámetros

Desde este momento $$ ya no actuará como delimitador

Sintaxis SQL para los procedimientos almacenados

Ejemplo: retornar las primeras 1000 filas de la tabla employees

Sintaxis SQL para los procedimientos almacenados

Para ejecutar el procedimiento almacenado que acabamos de crear

Sintaxis SQL para los procedimientos almacenados

Ejercicio: retornar las primeras 1000 filas de la tabla salaries

Sintaxis SQL para los procedimientos almacenados

Ejercicio:

Cree un procedimiento que proporcione el salario promedio de todos los empleados.

Luego, llame al procedimiento.

Sintaxis SQL para los procedimientos almacenados

Borrar un procedimiento almacenado

Procedimientos almacenados con parámetros de entrada (input)

• Una rutina almacenada puede realizar un cálculo que transforma un valor de entrada en un valor de salida.

• Los procedimientos almacenados pueden tomar un valor de entrada (parámetro IN) y luego usarlo en la consulta o consultas escritas en el cuerpo del procedimiento

Procedimientos almacenados con parámetros de entrada (input)

Ejemplo: imagine que desea obtener las columnas: first_name, last_name, salary, from_date y to_date de un empleado en particular

Procedimientos almacenados con parámetros de entrada (input)

Ejemplo: imagine que desea obtener las columnas: first_name, last_name, y el salario promedio de un empleado en particular

Procedimientos almacenados con parámetros de salida (output)

Representará la variable que contiene el valor de salida de la operación ejecutada por la consulta del procedimiento almacenado

SELECT INTO variables

Procedimientos almacenados con parámetros de salida (output)

Ejercicio: obtener el salario promedio de un empleado en particular y almacenar el resultado en un parámetro de salida

Procedimientos almacenados con parámetros de salida (output)

Ejercicio: Obtener el nombre y apellido de un empleado cualquiera

Procedimientos almacenados con parámetros de salida (output)

Cada vez que crea un procedimiento que contiene parámetros tanto IN como OUT, recuerde que debe usar la estructura SELECT-INTO en la consulta
del cuerpo de este objeto!

Procedimientos almacenados con parámetros de salida (output)

Ejercicio: Cree un procedimiento llamado "emp_info" que utilice como parámetros el nombre y el apellido de un individuo y devuelva su número de empleado.

Condicional IF

Para más información: https://www.mysqltutorial.org/mysql-if-statement/

Variables

Variables

Dos tipos:

• Variables locales
• Variables de usuarios

Variables

Estructura de base de datos inicial para esta clase

Variables Locales (I)

Declarar variables en procedimientos almacenados

Variables Locales (II)

Implementar withdraw

Variables de usuarios

Errores y Avisos

Manejar Errores

Ver documentación oficial sobre como manejar diversos avisos y errores: https://dev.mysql.com/doc/refman/8.0/en/declare-handler.html

Ciclos - While

Hemos visto en los conceptos anteriores las instrucciones de MySQL que nos permiten definir estructuras condicionales dentro de los procedimientos almacenados.

Ahora veremos qué comandos disponemos para repetir bloques de instrucciones.

La primer estructura repetitiva que disponemos y es común a la mayoría de los lenguajes de programación es el 'while'.

Ciclos - While

WHILE condición DO
    lista_de_sentencias
END WHILE;

Ciclos - While

Ciclos LOOP

[etiqueta:] LOOP
    lista_de_sentencias
END LOOP [etiqueta]

Ciclos LOOP

Crear un procedimiento que permita agregar 1000 registros aleatorios a la tabla users de la base de datos test como se muestra a continuación:

Los cursores nos permiten almacenar una conjunto de filas de una tabla en una estructura de datos que podemos ir recorriendo de forma secuencial.

1. DECLARE

El primer paso que tenemos que hacer para trabajar con cursores es declararlo. La sintaxis para declarar un cursor es:

DECLARE nombre_cursor CURSOR FOR sentencia_select

2. OPEN

Una vez que hemos declarado un cursor tenemos que abrirlo con OPEN.

OPEN nombre_cursor 

3. FETCH

Una vez que el cursor está abierto podemos ir obteniendo cada una de las filas con FETCH. La sintaxis es la siguiente:

FETCH [[NEXT] FROM] nombre_cursor INTO nombre_variable [, nombre_variable] ...

4. CLOSE

Cuando hemos terminado de trabajar con un cursor tenemos que cerrarlo.

CLOSE nombre_cursor 

Crear un procedimiento que obtenga los usuarios (users) activos registrados hace un año y que luego agregue el email en una tabla que deberá crear manualmente con el nombre leads y dos campos: la clave (id) y el email

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list]
    ...
    [ELSE statement_list]
END CASE

Cree la tabla products con tres campos: id, product y category. Los registros se muestran en la imagen. Luego cree tres tablas fruits, bakey y vegetables con un campo id y product por cada una. Se requiere un procedimiento que recorra cada fila de products y según la categoría (category) inserte el producto en la tabla correspondiente. Para el caso de los productos sin asignar categoría deberá retornarlos separados por coma.

Crear un procedimiento que retorne una lista de emails de la tabla users y una lista de frutas de la tabla fruits. Es decir, se trabajará con dos cursores.

Sugerencias:

- Para ordenar resultados de forma aleatoria use: ORDER BY RAND()

- Puede usar dos cursores en el mismo ciclo

1. Cree manualmente una tabla (llamada Palabras) que contenga dos columnas y una clave primaria: una columna para la lista de sustantivos y una columna una lista de adjetivos, ejemplo:

1 perrito caliente             2 plátanos rápidos             3 nube naranja             4 cortina encantadora
.....

Escriba un procedimiento que devuelva datos en dos variables "out" (Puede llamarlo crear_listas): una lista contendrá todos los sustantivos unidos con comas, la otra contendrá todos los adjetivos unidos por comas.

Opcional: modifique el procedimiento para que no haya una coma extra adicional al inicio o final de las listas.

2. Escriba un procedimiento almacenado que cree una tabla llamada "estrellas" que contenga una columna de tipo TEXT (nombre) y otra columna para la clave (id). Haga que el procedimiento llene la tabla seleccionando un adjetivo y un sustantivo al azar y que los una con un espacio entre ellos, ejemplos:

Nube caliente                   Perro encantador

.....

Opcional: mayúsculas las primeras letras de las dos palabras.

3. Agregue a la tabla "estrellas" dos columnas una fecha de nacimiento y una fecha de fallecimiento (ambas de tipo date). Cree otro procedimiento (crear_estrellas_con_fechas) que complete las fechas de nacimiento y muerte de la siguiente manera.

   La fecha de nacimiento debe ser aleatoria entre 20 y 80 años atrás.
   La fecha de fallecimiento debe ser aleatoria entre 19 años después de la fecha de nacimiento y hoy.

La fecha de fallecimiento solo se debe completar en el 40% de las veces, al azar. Es decir, todas las filas tienen un 40% de posibilidad de tener fecha de fallecimiento.

TRIGGERS

TRIGGERS

Un desencadenador (o Trigger) es una clase especial de procedimiento almacenado que se ejecuta automáticamente cuando se produce un evento en el servidor de bases de datos.

TRIGGERS

Un trigger es un objeto de la base de datos que está asociado con una tabla y que se activa cuando ocurre un evento sobre la tabla.

Los eventos que pueden ocurrir sobre la tabla son:

• INSERT: El trigger se activa cuando se inserta una nueva fila sobre la tabla asociada.
• UPDATE: El trigger se activa cuando se actualiza una fila sobre la tabla asociada.
• DELETE: El trigger se activa cuando se elimina una fila sobre la tabla asociada.

Entonces, un trigger se asocia a un evento (insert, update o delete) sobre una tabla.

TRIGGERS - SINTAXIS

CREATE
    TRIGGER nombre_del_trigger
    momento_del_trigger evento_trigger
    ON nombre_tabla FOR EACH ROW
    cuerpo_del_trigger

momento_del_trigger: { BEFORE | AFTER }

evento_trigger: { INSERT | UPDATE | DELETE }

TRIGGERS

Ejemplo: se tiene la tabla ventas (sales) con los campos: id, product y value. Deseamos llevar un registro de todos los cambios de valor (value) de la venta.  Imagine que la venta 1 que actualmente es de 15.00 sufre uno o varios cambios en el valor, sería ideal almacenar esas modificaciones, esto como mecanismo de control interno de la empresa, la tabla se debe llamar sales_update con los campos: id, product_id, change_at, before_value, after_value

TRIGGERS - Notas

• Las palabras clave OLD y NEW permiten acceder a las columnas de las filas afectadas por un trigger.

• OLD y NEW son extensiones MySQL para trigger, no son sensibles a mayúsculas y minúsculas.

• En un INSERT no hay fila OLD.

• Una columna nombrada con OLD es de solo lectura. No se puede modificar.

Validaciones con TRIGGERS

Ejemplo: se tiene la tabla products con los campos: id y value. Necesitamos validar que en el campo value el valor máximo sea 100.0, es decir que si el usuario ingresa 130 para el valor de un producto, automáticamente se fije el valor en 100.0 desde el servidor.

Validaciones con TRIGGERS

Ejemplo: se tiene la tabla products con los campos: id y value. Necesitamos validar que en el campo value el valor máximo sea 100.0, es decir que si el usuario ingresa 130.0 para el valor de un producto, automáticamente se fije el valor en 100.0 desde el servidor.

Solución:

Ejercicio

Cree una tabla de animales (animals) que incluya una columna de tipo texto (varchar o text) llamada "animal_name".

Cree un procedimiento almacenado que prevenga la inserción de cualquier nombre de animal que contenga la palabra "perro". Por ejemplo: "Perro Pastor Alemán", "Perro Bulldog", etc.

Si alguien intenta insertar una palabra que incluya "perro", el trigger automáticamente insertará en su lugar "$$$" y debería registrar el intento incluyendo el nombre del animal en una tabla llamada "violations" con la fecha y hora en que ocurrió.

Funciones

Definidas por el usuario

Funciones vs Procedimientos Almacenados

• No retornan valores (no hay return)
• Pueden tener múltiples parámetros de salida
• Los procedimientos se llaman con call procedure;
• Podemos trabajar con INSERT, DELETE, UPDATE

Procedimientos almacenados

Funciones

• Las funciones retornan un único valor
• SELECT function

• No es recomendable INSERT, DELETE, UPDATE

Funciones

DELIMITER $$
CREATE FUNCTION nombre_función(parametro tipo_dato) RETURNS tipo_dato
BEGIN

    SELECT ...
RETURN nombre_variable
END$$
DELIMITER ;

• No hay parámetros de salida
• Todos los parámetros son de entrada y no es necesario que lo indique explícitamente con la palabra "IN"

Aunque no hay
parámetros de salida, hay un
"Valor de retorno" y se obtiene después de ejecutar la consulta contenida en el cuerpo de la función

Puede ser de cualquier tipo

Funciones

Bloqueos y transacciones

LOCK TABLE

Permite que las sesiones del cliente adquieran bloqueos de tabla explícitamente con el fin de cooperar con otras sesiones para acceder a la tabla o para evitar que otras sesiones modifiquen las tablas durante los períodos en que una sesión requiere acceso exclusivo a ellas.

Transacciones

Una transacción SQL es un conjunto de sentencias SQL que se ejecutan formando una unidad lógica de trabajo (LUW del inglés Logic Unit of Work), es decir, en forma indivisible o atómica.

Una transacción SQL finaliza con un COMMIT, para aceptar todos los cambios que la transacción ha realizado en la base de datos, o un ROLLBACK para deshacerlos.

Autocommit

• Algunos Sistemas Gestores de Bases de Datos, como MySQL (si trabajamos con el motor InnoDB) tienen activada por defecto la variable AUTOCOMMIT. Esto quiere decir que automáticamente se aceptan todos los cambios realizados después de la ejecución de una sentencia SQL y no es posible deshacerlos.

Transacciones

MySQL nos permite realizar transacciones en las tablas si hacemos uso del motor de almacenamiento InnoDB (MyISAM no permite el uso de transacciones).

El uso de transacciones nos permite realizar operaciones de forma segura y recuperar datos si se produce algún fallo en el servidor durante la transacción, pero por otro lado las transacciones pueden aumentar el tiempo de ejecución de las instrucciones.

Las transacciones deben cumplir las cuatro propiedades ACID.

Transacciones

Las propiedades ACID garantizan que las transacciones se puedan realizar en una base de datos de forma segura. Decimos que un Sistema Gestor de Bases de Datos es ACID compliant cuando permite realizar transacciones.

ACID es un acrónimo de Atomicity, Consistency, Isolation y Durability

Transacciones

• Atomicidad: Esta propiedad quiere decir que una transacción es indivisible, o se ejecutan todas la sentencias o no se ejecuta ninguna.

• Consistencia: Esta propiedad asegura que después de una transacción la base de datos estará en un estado válido y consistente.

Transacciones

• Aislamiento: Esta propiedad garantiza que cada transacción está aislada del resto de transacciones y que el acceso a los datos se hará de forma exclusiva. Por ejemplo, si una transacción que quiere acceder de forma concurrente a los datos que están siendo utilizados por otra transacción, no podrá hacerlo hasta que la primera haya terminado.

• Durabilidad: Esta propiedad quiere decir que los cambios que realiza una transacción sobre la base de datos son permanentes.

SAVEPOINT, ROLLBACK TO SAVEPOINT y RELEASE SAVEPOINT

• SAVEPOINT: Nos permite establecer un punto de recuperación dentro de la transacción, utilizando un identificacor. Si en una transacción existen dos SAVEPOINT con el mismo nombre sólo se tendrá en cuenta el último que se ha definido.
• ROLLBACK TO SAVEPOINT: Nos permite hacer un ROLLBACK deshaciendo sólo las instrucciones que se hayan ejecutado hasta el SAVEPOINT que se indique.
• RELEASE SAVEPOINT: Elimina un SAVEPOINT.

Acceso concurrente

Cuando dos transacciones distintas intentan acceder concurrentemente a los mismos datos pueden ocurrir los siguientes problemas:

• Dirty Read (Lectura sucia). Sucede cuando una segunda transacción lee datos que están siendo modificados por una transacción antes de que haga COMMIT.

• Nonrepeateable Read (Lectura No Repetible). Se produce cuando una transacción vuelve a leer datos que leyó previamente y encuentra que han sido modificados por otra transacción.

• Phantom Read (Lectura fantasma). Este error ocurre cuando una transacción lee unos datos que no existían cuando se inició la transacción.

Niveles de aislamiento

Para evitar que sucedan los problemas de acceso concurrente que hemos comentado en el punto anterior podemos establecer diferentes niveles de aislamiento.

• Read Uncommited. En este nivel no se realiza ningún bloqueo, por lo tanto, permite que sucedan los tres problemas

• Read Commited. En este caso los datos leídos por una transacción pueden ser modificados por otras transacciones, por lo tanto, se pueden dar los problemas Phantom Read y Non Repeteable Read.

Niveles de aislamiento

• Repeateable Read. En este nivel ningún registro leído con un SELECT puede ser modificado en otra transacción, por lo tanto, sólo puede suceder el problema del Phantom Read.

• Serializable. En este caso las transacciones se ejecutan unas detrás de otras, sin que exista la posibilidad de concurencia.

El nivel de aislamientno que utiliza InnoDB por defecto es Repeateable Read.