INTRODUCCIÓN A BASE DE DATOS Y SQL
RANGEL ALVARADO
1
INTRODUCCIÓN
En todo
momento
tenemos que
interactuar con
base de datos.
¿Cúando sabemos o qué es exactamente una base
de datos?
2
BASE DE DATOS
Colección de
datos
almacenados de
una manera
elegante.
• Por extensión, se llama oráculo al propio lugar
en que se hace la consulta y se recibe la
respuesta (el oráculo) - Wikipedia
Ejm:
• DBMS:
DataBase Management System,
software de administración de base de datos.
• Base de datos: Contenedor (archivos o lista de
estos) de manera ordenada.
• No se debe confundir la base de datos con el
software de administración, por lo general se
utiliza este software (DBMS) para acceder a la
base de datos correspondiente por ud.
3
TABLAS
Cuando se almacena
información no se
tira en el cajón, sino
que se crea un
archivo relacionado
con
archivos
específicos.
• Archivos de base de datos = tablas
• Archivo estructurado que puede alojar datos
de una manera en específica. Una lista de
datos estructurada de una manera en
específica.
• En la misma base de datos cada nombre de
tabla es único.
Ejm:
• Schema o Esquema: Información de
cómo se relacionan la base de datos con
las capas de tablas y sus propiedades
4
COLUMNAS Y TIPO DE DATOS
Las tablas estan
hechas u organizadas
en columnas.
Las
columnas
contienen
información
particular de las
tablas
Ejm:
• Columnas: Un campo sencillo de
una tabla
• Todas las tablas están constituidas
de una o más columnas
• Es importante en una base de datos
desmenuzar la información para que
después sea posible ordenar o filtrar
• Cada columna tiene un tipo de dato
asociado permitido.
• Cada columna de tabla tiene un tipo
de dato que restringe (o acepta)
datos específicos a esta columna
• Nos permite realizar ordenamiento
de la base de datos y optimización
de espacio en disco
• La compatibilidad de tipo de datos
es el principal fuente de error a la
hora de realizar aplicaciones de
usuario.
5
FILAS
Los datos en las
tablas de la base de
datos se almacenan
en filas y cada
registro guardado se
almacena en esta fila
• Fila: Un registro en una tabla
• ¿Registros o filas?. Ambos son significados
válidos, pero el propio a referir es filas.
Ejm:
6
LLAVES PRIMARIAS
Las llaves primarias
son identificadores
únicos.
Para una tabla puede
haber más de una
llave primaria
• Llave primaria: una columna o
grupo de estas cuyo valor es único e
identifica cada fila en la tabla
• Son necesarias para realizar más
fácilmente
el
trabajo
de
actualización de filas o borrado en
las tablas y base de datos
• Las llaves primarias no son
requeridas, pero se DEBE definir
una llave primaria para que la
información sea administrable.
• Condiciones de llave primaria:
• Dos filas no pueden tener el mismo valor si es llave primaria.
• Cada fila debe poseer una llave primaria con valor (no nulo).
• Valores de las llaves primarias no pueden ser alterados.
• La llave primaria no debe ser reutilizada. No puede ser utilizada
una llave primaria en el futuro para otro registro.
7
SQL
• SQL o Structured Query
Language es un lenguaje
diseñado específicamente para
comunicación a base de datos.
• A diferencia de otros lenguajes
(hablado o de programación)
SQL se basa en pocas palabras.
• Ventajas:
• SQL es no propietario. Aprender SQL te permite interactuar con
casi todas las bases de datos asi utilicemos un DBMS.
• Fácil de aprender. Se deriva del inglés y son instrucciones
básicas
• A pesar de ser sencillo, permite construir instrucciones complejas
para conocer información y operación de base de datos
específica.
• Extensiones de SQL: muchas son propietarias desarrolladas por
vendedores de DBMS, pero procuraremos estilar ANSI SQL, que es un
estándar.
8
SENTENCIA “SELECT”
Keyword o palabras
claves son palabras
reservadas que no se
pueden utilizar, en
este
caso,
para
nombrar bases de
datos,
columnas,
tipos.
• SINTAXIS PARA COLUMNA SIMPLE:
SELECT <columna> FROM <tabla>;
• La información se muestra en
el orden agregado
• La información no es filtrada
• La sentencia SQL se puede
romper en varias líneas para un
mejor entendimiento
• Muchos softwares de DBMSs
no importa si se terminan con
“;”
• Las palabras claves son
insensibles a su uso, es decir,
aceptan
mayúsculas
o
minúsculas
9
SENTENCIA “SELECT”
La única diferencia es
que
la
sentencia
SELECT
para
múltiples casos es que
las columnas son
separadas por comas.
• SINTAXIS PARA MULTIPLES COLUMNAS:
SELECT <columna1>,<columna2>, …, <columnaN> FROM <tabla>;
• Las sentecias SQL retornan datos sin formatear (ver columna “phone”)
• La aplicación de usuario es la que se encarga de obtener la información
desplegada.
• La última columna no debe llevar coma!
10
SENTENCIA “SELECT”
El
asterisco
(*)
comunmente se le
conoce
como
“wildcard”.
• SINTAXIS PARA TODAS LAS COLUMNAS:
SELECT * FROM <tabla>;
•
•
•
•
Cuando se especifíca el wildcard TODAS las columnas son listadas
No necesariamente están en el orden que se ve en el esquema (schema)
Traer todas las columnas dismuniye el tiempo de la aplicación!!!
La gran ventaja de utilizar wildcards es que se puede listar el nombre de
columnas desconocidas
11
ORDENAR DATOS DE QUERIES
Como la información
que se despliega no
tiene
un
orden
particular,
las
cláusulas de SQL nos
pueden ayudar a
filtrar de una manera
organizada
la
información
• SINTAXIS :
SELECT <columna> FROM <tabla> ORDER BY <columna>;
• La cláusula ORDER BY, es una cláusula opcional de la sentencia
SELECT que debe de ir al final.
• Adicionalmente se puede ordenar por columnas no listadas, esta es una
práctica muy común
12
ORDENAR DATOS DE QUERIES
• SINTAXIS :
SELECT <columna1>,<columna2>, …, <columnaN> FROM <tabla>;
ORDER BY <columna1>, …, <columnaN>;
• En esta sección, primero se ordena por telefono y luego por nombre de
cliente
13
ORDENAR DATOS DE QUERIES
Esta técnica NO se
puede utilizar para
columnas que no
aparecen listadas en
la sentencia de SQL
• SINTAXIS :
SELECT
<columna>
FROM
<num_col1>,<num_col2> ;
<tabla>
ORDER
BY
• Es el mismo resultado de la filmina anterior
• La ventaja de esta técnica es el no tener que repetir el nombre de las
columnas
• ORDER BY 3, 2 significa ordenar por telefono y nombre de cliente
14
ORDENAR DATOS DE QUERIES
El ordenamiento de
información
por
defecto en SQL es
estilo A - Z
• SINTAXIS :
SELECT <columna> FROM <tabla> ORDER BY <columna> [ASC|DESC]
• Ordenamiento ascendente por defecto
• Para ordenar múltiples columnas en forma descendente o ascendente, en
cada columna debe de tener la palabra reservada DESC o ASC
• Así como en un archivador ‘a’ se ordena con ‘A’, esto es más
dependiente del ajuste en la base de datos y para las bases de datos más
complejas esto se ajusta con el administrador de base de datos.
15
FILTRANDO INFORMACIÓN
Las bases de datos
contienen
gran
cúmulo
de
información
y
generalmente
se
requiere información
específica
para
generar reportes.
• SINTAXIS :
SELECT <columna(s)> FROM <tabla> WHERE <columna operador valor>;
• En este caso se filtra por cantidades mayor a B/. 70,000.00
• El filtro también puede ser aplicado en la capa de aplicación (API)
• No recomendable debido a que las bases de datos fueron creadas
para enviar información concisa
• Se envía tráfico innecesario por la red
• Traducido en pérdida de ancho de banda
• Si se utiliza ORDER BY debe ir después de WHERE
16
FILTRANDO INFORMACIÓN
Las bases de datos
contienen
gran
cúmulo
de
información
y
generalmente
se
requiere información
específica
para
generar reportes.
• OPERADORES:
Operador
Descripción
=
Igualdad
<>
Desigualdad
!=
Desigualdad
<
Menor que
<=
Menor o igual que
!<
No menor que
>
Mayor que
>=
Mayor o igual que
!>
No mayor que
BETWEEN
Entre dos valores específicos
IS NULL
Es un valor nulo
17
FILTRADO AVANZADO
Para ejecutra filtros
avanzados
nos
ayudamos de otras
palabras reservadas
como AND y OR
• SINTAXIS :
SELECT <columna(s)> FROM <tabla> WHERE <columna operador
valor> [AND|OR] <columna operador valor>;
• Se debe tener especial cuidado al realizar filtrados!!!
18
FILTRADO AVANZADO
• Se desea de la tabla de productos las líneas de productos de
motocicletas o modelos clásicos de carros y que estén en escala de 1:10
• SQL como muchos lenguajes de programación procesan AND antes que
OR
• ¿Qué entendió?: Escala de producto de 1:10 para Motocicletas ó
cualquier producto que sea modelo clásico de automóviles.
• ¿Cómo resolver?: Utilizando paréntesis
• Los paréntesis tienen prioridad más alta que la sentencias AND y OR
• Es recomendable en todo caso utilizar paréntesis para eliminar
ambiguedades.
19
FILTRADO AVANZADO
El operador IN se
utiliza
para
especificar un rango
de condiciones de la
misma columna
• SINTAXIS :
SELECT <columna(s)> FROM <tabla> WHERE <columna> IN (<valor 1>,
…, <valor N>);
• Es equivalente a: SELECT * FROM employees WHERE
firstname=‘Barry’ OR firstname=‘Larry’ OR firstname=‘Leslie’ OR
firstname=‘Peter’;
• Ventajas de uso del operador IN
• Cuando se posee una lista de opciones válidas es prólijo al leer.
• El orden de evaluación es fácil de administrar
• IN ejecuta más rápido el query que los operadores OR
• Lo más notable de utilizar IN es que dentro de esta puede haber
otra sentencia de SQL realizando operaciones dinámicas de
filtrado
20
FILTRADO AVANZADO
El operador NOT
solamente niega la
condición a la cual le
sigue
• SINTAXIS :
SELECT <columna(s)> FROM <tabla>
operador valor>;
WHERE NOT <columna
• Este query es equivalente a: SELECT * FROM orderdetails WHERE
orderLineNumber <> 1 OR … OR orderLineNumber <> 9
• A diferencia de otros operadores, NOT puede ser utilizado antes de la
columna a filtrar
• En la base de datos MySQL, NOT se utiliza (como en este caso) para
negar la existencia de datos.
21
USO DE COMODINES
Los filtros anteriores
eran
de
valores
conocidos, en esta
sección se estudia
cuando no se conoce
exactamente
este
valor.
• SINTAXIS :
SELECT <columna(s)> FROM <tabla> WHERE <columna> LIKE <valor>;
• Wildcards (comodines): carácter especial para comparar partes de un
valor.
• Patrón de búsqueda: condición de búsqueda construida de texto,
comodines o combinación de ambos
• Predicados: LIKE es un predicado, no un operador
• El símbolo ‘%’ simboliza el comodín de “cualquier numero de
ocurrencias de cualquier carácter”.
• En microsoft access, el wildcard equivalente a % es *
22
USO DE COMODINES
• SINTAXIS :
SELECT <columna(s)> FROM <tabla> WHERE <columna> LIKE <valor>;
• Se puede utilizar más de una vez el wildcard
• El query simboliza: resultados de cualquier nombre de empleado que
contengan internamente las iniciales ‘ar’
• Igualmente se pueden utilizar entre oraciones, pero es muy raro verlo.
• Cuidado con los espacios luego de las cadenas!!!
• En algunas DBMSs, por ejemplo, en la columna lastName, puede
que haya espacios luego del último carácter, para llenar la fila, así,
es recomendable filtrar al final por %
• El comodín [] no es soportado en MySQL, y se utiliza para listar un
grupo de caracters en común, por ejemplo Larry, Barry serían [LB]
23
USO DE COMODINES
El comodín _ se
utiliza
para
especificar
SOLAMENTE
un
caractér
• SINTAXIS :
SELECT <columna(s)> FROM <tabla> WHERE <columna> LIKE <valor>;
•
•
•
•
Solamente lista un carácter en vez de todos los caracteres anteriores
Cuidado con los espacios luego de las cadenas!!!
SIEMPRE el comodín _ encuentra solo y solamente un caractér
Existe otro comodín [], por ejemplo, en una sentencia como SELECT *
FROM employees WHERE lastName LIKE ‘[PB]%’ listaría los
apellidos que empiecen con Patterson y Bolt
• NO TODAS las DBMSs soportan []
• Para negar un carácter se utiliza ^, por ejemplo SELECT * FROM
employees WHERE lastName LIKE ‘[^PB]%’ solamente listaría Bolt
24
USO DE COMODINES
• Los comodines son sentencias que utilizan
mucho tiempo de procesamiento
• No se deben sobreutilizar los comodines si
existe otra opción
• Cuando se utilicen los comodines, NO se
deben utilizar antes del patrón de búsqueda por
lo general, consumen más tiempo de ejecución
• Si se especifican mal, pueden retornar más
datos de los que ud. necesita.
25
CREAR CAMPOS CALCULADOS
Se procede a la
creación de campos
calculados pues la
información
proveniente de la DB
no necesariamente es
la
salida
que
buscamos
• Desplegar campos de manera conjunta que
existen en tablas y columnas separadas
• La información en las columnas está en
formato mixto de mayúsculas y minúsculas,
pero se necesita en mayúsculas
• Se tienen precios y cantidades, pero no totales
por cada item y mucho menos el gran total
• Se necesita el promedio de un grupo de datos
• Los campos calculados no existen en las tablas
de la base de datos!!!
• Cualquiér cálculo debe ser realizado en el nivel
de base de datos, son eficientes
26
CREAR CAMPOS CALCULADOS
Para algunas DB, el
concatenar campos se
utiliza el caractér ‘+’
o ‘||’. Sin embargo, en
MySQL se utiliza la
función CONCAT()
• SINTAXIS :
SELECT CONCAT(<columna1> ,<columna2>, …, <columnaN>) [AS <alias>]
FROM <tabla>;
• Concatenar:
unir
dos
valores
(adjuntar) para formar uno mas largo.
• MySQL no soporta || o +, en su caso,
utiliza CONCAT()
• En MySQL || es equivalente a OR y
&& es equivalente a AND
• Recordar que muchas DB retornan
caracteres a la derecha que deben ser
eliminados con la función RTRIM()
• El uso del alias es un
nombre el cual se quira
dar.
• Se usan alias cuando
los nombres de las
• Para usar un alias con espacios debe ir entre ‘ ‘ columnas son difíciles
de interpretar.
• A los Alias se les conoce como:
• Alias = Columnas derivadas
27
CREAR CAMPOS CALCULADOS
También
podemos
generar
columnas
calculadas,
p.e.,
cantidades totales de
un producto u Orden
de Compra
• Del siguiente ejemplo podríamos
calcular las cantidades totales por
producto
• Lo haremos en una columna de
‘totalPerQty’
• Otros
operadores
soportados son:
• +
• • *
• /
• Sin embargo no son las
unicas
operandos
soportados
28
FUNCIONES
En programación, las
funciones tienen al
menos un valor de
retorno, puede o no
tener un argumento
de entrada.
• El uso de funciones se da cuando se quiere
manipular datos para facilitar su comprensión
• No todo código en SQL sirve en la misma
aplicación desarrollada, depende de la DB
• Problemas de portabilidad
– Dependiendo de las DB, las funciones pueden
llamarse de manera diferente
– Ejms. MID() en Access, SUBSTR() en PostgreSQL
• ¿Se debe de usar funciones?
– Depende del programador
– Si se utilizan FAVOR comentar ( -- ) para que otro
programador sepa que se hizo.
29
FUNCIONES
También
podemos
generar
columnas
calculadas,
p.e.,
cantidades totales de
un producto u Orden
de Compra
• La mayoría de las implementaciones en SQL soportan las funciones:
• De texto o cadenas de texto
• Numéricas para operaciones matemáticas
• Fecha y hora, p.e. adjuntar una estampa de tiempo o diferencia
• Funciones de sistema (saber si se ejecuto un query)
• Las funciones de tiempo son una de las mas utilizadas, sin embargo son
las menos portables
30
RESUMIENDO INFORMACIÓN
No
necesariamente
podemos listar la
información,
sino
llevar un resumen.
Funciones Agregadas:
Funciones que operan
con un grupo de filas
para
calcular
o
retornar un valor.
Operador
Descripción
AVG()
Retorna el promedio de las
columas
COUNT()
Retorna el número máximo de
filas en una columna
MAX()
Retorna el valor más alto
MIN()
Retorna el valor más bajo
SUM()
Retorna la suma de los valores
Promedio de toda la data
Promedio de un grupo específico
• Se usan estas funciones especiales
(funciones
agregadas)
para
resumir
y
analizar
datos
provenientes de la DB, algunas de
estas son:
• Determinar el numero de
filas (o aquellas que se
somentan a una condición
especial)
• Obtener el total de datos
• Tomar los valores máximo,
mínimo y promedio de una
muestra
• Funciones de sistema (saber
si se ejecuto un query)
31
RESUMIENDO INFORMACIÓN
Contar todos los clientes
Todos los clientes con
dirección alternativa
• Modos de uso de COUNT()
• COUNT(*): Para determinar TODOS los valores,
incluso los nulos
• COUNT(columna): Para determinar el número de
valores en filas ignorando los NULL (nulos)
• Usar MIN() con datos no numéricos, p.e., fechas, retorna el valor de
menor.
• Cuando es utilizado con datos de texto, MIN() debería retornar el
primer campo insertado (el más viejo).
• Todo lo anterior depende de la DBMS que se esté utilizando.
32
RESUMIENDO INFORMACIÓN
Orden 10104
Suma individual de la
orden 10104
Precio Total de la
orden 10104
• Importante!!! Los valores nulos no son considerados con
SUM()!!!!
33
RESUMIENDO INFORMACIÓN
• Las funciones agregadas pueden utilizarse de dos formas:
• Para administrar calculos en todas las filas especificando o no el
argumento ALL (porque ALL es el argumento por defecto)
• Para incluir calculos de campos únicos con DISTINCT
Orden 10122
Total de cantidades únicas
• MS Access NO soporta DISTINCT
• Solo usar DISTINCT con COUNT() si
se especifica la columna
• No tiene uso usar DISTINCT con
MIN() o MAX() pues es un solo valor
• Otras DBMSs soportan funciones
agredadas como TOP PERCENT para
el cálculo de porcentajes
34
RESUMIENDO INFORMACIÓN
Es
una
recomendación
al
utilizar
funciones,
renombrar por un
ALIAS a la columna
que posee la función,
es más claro al
visualizar
• Las funciones agregadas pueden ser utilizadas de forma combinada
Orden 10122
Resumen de Funciones
35
GRUPOS DE DATOS
Las
funciones
agregadas nos sirven
para
realizar
un
resumen de datos.
Los grupos de datos
para presentar un
resumen de los datos
individuales.
• Se aprendió a sumarizar datos para
• Contar filas
• Buscar máximos, mínimos y promedios
• Se utiliza de por medio la clausula WHERE
• ¿Qué sucedería si necesitaramos los numeros de totales de
productos por cada orden de compra?
• Se debe usar grupos!!!!
• Los grupos te permiten dividir datos en diferentes grupos
individuales pero reuniendo todos los datos en un solo resultado.
36
GRUPOS DE DATOS
En
el
siguiente
ejemplo realizamos
los resumenes por
orden de cantidades
totales por orden y el
numero de items.
• GROUP BY se puede
filtrar
por
columna
relativa (GROUP BY 2,3)
• Algunas
implementaciones de SQL
soportan ALL en GROUP
BY
• No se debe evaluar cada producto para ser calculado, la DBMs lo realiza
de manera individual y eficiente
• Comentarios acerca de la cláusula GROUP BY
• GROUP BY contiene cuantas columnas ud. requiera y se puede
anidar
• GROUP BY agrupa todos los datos o registros, no las funciones.
• La columna utilizada en GROUP BY debe estar presente en
SELECT
• La mayoría de sentencias SQL GROUP BY no funcionan bien con
tipos de datos variables en largo (texto)
• Si uno o más registros contienen NULL, NULL se retornará.
• GROUP BY deber de estar seguido de WHERE y antes de ORDER
BY
37
GRUPOS DE DATOS
Podemos
realizar
filtros
de
cuales
incluir y excluir, p.e.,
incluir
todas
las
ordenes que tuvieron
mas de “n” cantidad
de items.
• Anteriormente se utilizó
WHERE para filtrar filas
• HAVING filtra por grupos
• WHERE no tiene idea de
qué es un grupo
• HAVING soporta los
operadores de WHERE
• WHERE filtra columnas antes de ser agrupados los datos. Al usar
HAVING, no se utilizarán todas las columnas y se afectará el cálculo.
• Ejemplo: Filtramos del anterior, grupos de ordenes con items > $120
• Usar HAVING y WHERE
HAVING
en
algunas
DBMSs es lo mismo usando
WHERE si GROUP BY no
se especifica
• Usar HAVING solo en
conjunto
con
GROUP
BY!!!!
38
GRUPOS DE DATOS
Como práctica común
se
debe
utilizar
ORDER BY, no
confiar en GROUP
BY para ordenar los
datos.
Agrupar y Ordenar
ORDER
BY
Cláusula SELECT
CLÁUSULA
DESCRIPCIÓN
Ordena la
salida
generada
Agrupa las
columnas, no
necesariament
e en orden,
pero puede
darse el caso.
Se puede
utilizar
cualquier
columna,
hasta las no
visibles
Solo las
columnas
seleccionadas
pueden usarse
Opcional
Requerida si
se utilizan
columnas con
expresiones
REQUERIDO?
SELECT
Retorna columnas
Sí
FROM
Tabla a buscar
datos
Solo si se
selecciona datos de
la tabla
WHERE
Filtrado por filas
No
GROUP BY
Selección grupal
Solo si se calculan
valores agregados
HAVING
Filtrado por grupo
No
ORDER BY
Salida ordenada
No
GROUP BY
39
SUBQUERIES
Query: Una sentencia
SQL, generalmente
las
personas
la
asocian a SELECT
Subqueries:
Peticiones embebidas
o indexadas dentro de
otras peticiones
MySQL 4.1 o mayor
soporta subqueries
• Hasta ahora se han visto queries que son de una sola tabla pidiendo
datos de tablas individuales… SELECT comments FROM Orders;
• Supongamos que quisiéramos el nombre y apellido de los clientes
que ordenaron un producto en específico, el ‘S18_1749’, lo
correcto sería
• Buscar los número de orden de los productos S18_1749
• Buscar los números de cliente basado en el número de orden del
producto encontrado anteriormente
• Buscar el nombre y apellido de los clientes basados en el número
de cliente del query anterior basados en el número de cliente
• Finalmente, cada query por separado puede ser unido
• Es recomendable anidar SubQueries para mejor comprensión
• Los queries internos solo deben retornar una columna
• No es la forma más eficiente (mas adelante se usara “join”)
40
SUBQUERIES
1. Buscar los número de
orden de los productos
S18_1749
2. Buscar los números de cliente
basado en el número de orden del
producto encontrado anteriormente
(existen más datos)
3. Buscar el nombre y apellido de
los clientes basados en el número
de cliente del query anterior
basados en el número de cliente
41
SUBQUERIES
También se puede
utilizar subqueries en
campos calculados.
El
ejemplo
presentado
es
funcional, pero puede
que no sea la solución
óptima.
•
Queremos desplegar el número total de ordenes por cada
cliente de la tabla de clientes
1. Como para cada orden existen “N” clientes, listamos los totales en
una columna. Veamos el ejemplo individual:
2. Vinculamos el query anterior por numero de cliente en el filtro para
cada tabla, pero de la tabla clientes listamos los campos buscados.
42
JUNTURAS (JOINS)
JOINS: Se utilizan
para unir diferentes
tablas al vuelo
Es la operación más
poderosa
para
SELECT y su uso
debe
entenderse
específicamente con
bases
de
datos
relacionales.
• Bases de Datos Relacionales - Tablas
Base de datos No Base de datos
Relacional
Relacional
•
Data inconsistente es difícil
para realizar un reporte
CLIENTES INCIDENCIA
CLIENTES INCIDENCIA Con bases de datos relacionales…
NIC
Incidente
NIC
Incidente
Evitamos
múltiples
Nombre Descripcion
Nombre Descripcion •
Telefono
Estado
Telefono
Estado
ocurrencias de la misma
Ubicación
Nombre
Ubicación
NIC
información (fácil actualizar)
Circuito
Telefono
Circuito
• La información está dividida
Ubicación
en
múltiples
tablas
y
• ¿Porqué usar JOIN entonces?
relacionadas por un valor
• Ventaja:
información
común (llave primaria)
almacenada en múltiples
•
•
•
tablas
Desventaja: no se puede unir
en una sola SENTENCIA.
SOLUCIÓN: JOINS!!!
Nota: Los JOINS no crean tablas físicas (no existe como archivo),
solo se crea y persiste durante la ejecución de la sentencia
43
JUNTURAS (JOINS)
• SINTAXIS PARA JOIN:
SELECT <columna1>, …, <columnaN> FROM <tabla1>, <tabla2>
WHERE <tabla1>.<llaveprimaria1>=<tabla2>.<llaveprimaria2>;
• La llave primaria listada como columna
debe especificar la tabla de la cual se
extrae, sino abrá un error en el query!!!!
• Es común usar WHERE para juntar
las tablas, sin la condición de
WHERE listará todos los datos sin
filtrar e incurrira en errores de datos
44
JUNTURAS (JOINS)
Equijoin:
Uniones
basadas en igualdades
entre tablas.
• SINTAXIS PARA JOIN:
SELECT <columna1>, …, <columnaN> FROM <tabla1> INNER JOIN
<tabla2> ON <tabla1>.<llaveprim1>=<tabla2>.<llaveprim2>;
Se
puede
usar
igualmente
la
sentencia
INNER
JOIN
• Exactamente el mismo resultado
anterior
• Existen RIGHT y LEFT JOIN
respectivamente, pero INNER JOIN es
la sentencia ANSI recomendada
45
JUNTURAS (JOINS)
• También podemos unir más de dos tablas (multiples junturas de tablas)
• El siguiente ejemplo une valores únicos de diferentes tablas
• Consideraciones de desempeño: las junturas son realizadas en tiempo
real y el proceso puede consumir muchos recursos.
• A mayor cantitad de tablas que se unifique mayor será la degradación
de desempeño!
• No existe tamaño máximo para límite de tablas a juntar, sin embargo,
esto depende del software de DBMS que se use
46
JUNTURAS (JOINS)
• De la sección de subqueries, se observó que probablemente habría una
forma más eficiente de hacer el query
• Ejemplo en subqueries: Nombre y apellido de los clientes que
ordenaron un producto en específico, el ‘S18_1749’
• Subqueries
requieren
unir multiples queries
• Joins utiliza un query
para realizar el trabajo
• Existe siempre más de
una solución posible
47
CREANDO JUNTURAS (AVANZADAS)
Anteriormente
utilizamos Alias para
columnas
• ¿Porqué usamos los Alias?
• Acortar sentencias SQL
• Habilitar a múltiples usos con una sola sentencia SELECT
Sin embargo SQL
permite igualmente
Alias para tablas
• Oracle no soporta la cláusula AS
• MySQL = Orders AS O
• Oracle DB = Orders O
• Alias de tablas solo son por ejecución, no se retorna al cliente
48
CREANDO JUNTURAS (AVANZADAS)
Cada INNER JOIN
creado anteriormente
es un NATURAL
JOIN
y
probablemente
NUNCA JAMÁS se
necesite alguno que
no sea NATURAL
JOIN.
• Junturas propias (Self Joins)
• Usar alias promueve la reusabilidad en la sentencia SQL
• Utilizar siempre Self Joins en vez de subqueries debido a que es más
eficiente cuando se necesite traer datos de la misma tabla
• Junturas naturales (Natural Joins)
• El Join comun arroja todas las columnas aún si existen repetidas
• Las junturas naturales eliminan esta información repetida, es
decir, selecciona la información que es única
49
CREANDO JUNTURAS (AVANZADAS)
Los JOIN relacionan
columnas de una tabla
con otra, pero a veces
necesitamos incluir filas
aunque
no
estén
relacionadas
Nota: FULL OUTER
JOIN relaciona todos
los datos de ambas
tablas especificadas, sin
embargo,
no
es
soportado por MySQL
• Outer Join (Junturas Externas)
• Queremos contar cuantas ordenes cada cliente, incluyendo aquellos
que no han puesto una orden
• A diferencia del JOIN común que relaciona las filas de las tablas,
OUTER JOIN incluye incluso las filas no relacionadas
• RIGHT OUTER JOIN lista en
este caso toma todas las filas
• LEFT OUTER JOIN lista en
que guarden o no relación de la
este caso toma todas las filas
tabla Orders
que guarden o no relación de la
tabla Customers
50
CREANDO JUNTURAS (AVANZADAS)
Los
JOIN
también
pueden ser utilizados
con funciones.
• Usando Junturas con funciones
• Queremos contar cuantas ordenes cada cliente, incluyendo aquellos
que no han puesto una orden, su número total!!!
• También las podemos utilizar con LEFT / RIGHT OUTER JOIN
51
CREANDO JUNTURAS (AVANZADAS)
• Resumen del uso de junturas (JOINS)
– Poner especial cuidado en el uso de Joins,
generalmente se utilizará un INNER JOIN, pero
habrá casos que se requieran OUTER JOIN
– Siempre informarse de las capacidades de la DBMS
para procesar Queries
– Asegurarse de que el JOIN que ud. utilice es el
correcto, de lo contrario obtendrá data errónea
– Si no se especifica una condición de JOIN se
obtendrá un producto cartesiano (ejm.: Tabla 1 = 3
registros, Tabla 2 = 9 registros… Tabla final = 27)
– A pesar de que se pueda unificar varias tablas con
JOIN, tratarlas por separado y luego unificarlas, hara
más simples las verificaciones
52
UNIONES (COMBINAR QUERIES)
Hemos visto queries
SELECT
que
retornan datos de una
o varias tablas, pero
se pueden ejecutar
varios y después
unirlos…
queries
compuestos.
• Existen dos escenarios donde se utilizan peticiones compuestas
• Retornar datos de misma estructura pero de diferentes tablas
• Realizar peticiones múltiples de una tabla sencilla y retornar data
como una sola petición.
• Al combinar queries o realizar múltiples cláusulas de WHERE es
prácticamente lo mismo.
• Para combinar queries se utiliza la cláusula UNION
53
UNIONES (COMBINAR QUERIES)
• El query anterior es igual a:
Podemos igualmente
utilizar ORDER BY
para el orden de las
UNION, sin embargo,
solo debe ser puesto
en el último query
• No hay límites en cuantas uniones uno pueda realizar
• En teoria se ejecutan optimizadamente tanto WHERE como UNION, sin
embargo esto debe ser comprobado en la práctica.
• Reglas de uso de uniones:
• Separadas por SELECT pero unidas por la palabra UNION
• Debe contener las mismas columnas y funciones por query
• El tipo de datos de columnas debe ser compatible por query
• Eliminar o incluir datos duplicados
• En los queries anteriores hay un dato que se repite
• UNION (por defecto) automáticamente remueve el dato duplicado
• UNION ALL incluye todos los datos (duplicados o no)
54
INSERTANDO DATOS
SELECT es comun
para hacer peticiones;
INSERT
para
ingresar
nueva
información a la base
de datos
• INSERT puede ser utilizado para:
• Ingresar completamente una fila (registro)
• Ingresar parcialmente una fila
• Ingresar el resultado de una petición
• SE DEBE tener los privilegios necesarios (GRANT) para utilizarla
• No hay límites en cuantas uniones uno pueda realizar
• Vamos a ingresar datos a la tabla office!!
• Notar que la data almacenada, corresponde a cada columna de la tabla
Office
• Si no existe valor para la columna se rellena con NULL
• En algunas implementaciones INTO es opcional
• Esta no es la manera más apropiada de insertar un dato, de hecho es la
más insegura (puede que nos equivoquemos de columna)
55
INSERTANDO DATOS
• El modo correcto de ingresar información, pero el más engorroso es:
• Este es el método más seguro porque cada columna debe concordar con
cada tipo de dato
• Como regla de oro, debe usarse un INSERT con su nombre de column
56
INSERTANDO DATOS
• Para ingresar datos parciales:
• Notemos que las columnas addressLine1 y phone no fueron listadas
• Se recomienda omitir columnas si y solo si:
• La columna acepta NULL
• Un valor por defecto puede ser especificado en esta columna aún
cuando no se especifique
• Si se omite lo anterior, puede que no se inserte el registro arrojando un
error la DBMS.
57
INSERTANDO DATOS
• Para insertar datos masivamente (provenientes de otra tabla):
• Ejemplo: Ingresar datos de nuevos productos
•
•
•
•
Se insertan datos masivamente
Se copian todos los datos
Podemos utilizar WHERE para ingresar solo los datos que queramos
Los nombres de las columnas no necesariamente tienen que concordar,
mas sí los tipos de datos
• INSERT solamente inserta un registro, para insertar varios se utiliza
INSERT SELECT como acabamos de ver
58
INSERTANDO DATOS
• Otra manera de copiar datos masivo
• Ejemplo: Ingresar datos de nuevos productos
• En vez de utilizar INSERT, crearemos la tabla al vuelo
• Consideraciones al utilizar este tipo de sentencia:
• Se pueden utilizar WHERE y GROUP BY para insertar datos
• JOINS para insertar datos de múltiples tablas
• La información solo se inserta como una sola tabla a pesar de que
provengan de diferentes tablas
• Es recomendable hacer copias antes de trabajar con la data en bruto
59
ACTUALIZAR Y BORRAR
Para modificar datos
se utiliza la sentencia
UPDATE
• UPDATE puede utilizarse para los siguientes objetivos:
• Modificar una sola fila en específico
• Modificar varias o todas las filas de la tabla
• Especial atención y mucho CUIDADO!!!
• Para utilizar UPDATE (al igual que INSERT) se debe tener
privilegios por el administrador de base de datos
• No utilizar UPDATE sin WHERE (modificará toda la tabla)
• Ejemplo: Modificar la línea de productos, su descripción
• Ejemplo: Modificar la línea de productos, nombre y descripción
• Se pueden utilizar igualmente SUBQUERIES para insertar datos de una
sentencia SELECT
• Algunas DBMS soportan FROM des
• Para borrar el valor de una columna, se debe llenar con el campo NULL
60
ACTUALIZAR Y BORRAR
Para elimiar un dato
se utiliza la sentencia
DELETE
• DELETE puede utilizarse para los siguientes objetivos:
• Eliminar una sola fila en específico
• Eliminar varias o todas las filas de la tabla
• Especial atención y mucho CUIDADO!!!
• Para utilizar DELETE (al igual que UPDATE, INSERT) se debe
tener privilegios por el administrador de base de datos
• No utilizar DELETE sin WHERE (borrará toda la tabla)
• Ejemplo: Eliminar la línea de productos insertada y modificada
• DELETE solo borra contenidos, no la tabla misma
• Una manera de borrar toda la tabla es utilizando TRUNCATE (lo hace
más rápido)
• Lineamientos a la hora de borrar:
• No usar si WHERE
• Asegurarse que cada tabla tenga llave primaria
• Ejecutar un SELECT antes verificando la información a eliminar
• Habilitar si la DBMS la posee UPDATE y DELETE con WHERE
• Habilitar la DBMS para que no borre tablas llaves primaria asociad
61
CREAR, MANIPULAR TABLAS/DB
Para
ambas
situaciones se utiliza
el comando CREATE
• SINTAXIS PARA CREAR BASE DE DATOS:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_specification [, create_specification] ...] create_specification:
[DEFAULT] CHARACTER SET charset_name | [DEFAULT]
COLLATE collation_name
• Nos cambiamos de esquema/base de datos con la sentencia USE
• Antes de iniciar a crear tablas se aclara que existen varias maneras de
crearlas (así como las DB)
• Con la herramienta de administración
• Sentencias SQL
• Para empezar a crear tablas se debe suministrar la siguiente
información:
• Nombre de la tabla después de CREATE DB
• Nombre y definición de las columnas
• Algunas DBMS necesitan especificar la localización de la tabla
62
CREAR, MANIPULAR TABLAS/DB
• SINTAXIS PARA CREAR TABLAS:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)] [table_options] [select_statement]
• El formato de la sentencia se puede romper en varias filas, pero se
recomienda este orden
• El campo NOT NULL previene que al insertar datos, no se acepte la fila
sin valor, habrá un error de inserción
• Algunas DB toman en cuenta no especificar NULL como valor nulo.
• Las llaves primarias se especifican sin valor NULL
• No confundir NULL con cadenas vacías (‘’) que son valores válidos
63
CREAR, MANIPULAR TABLAS/DB
Para
reasignar
columnas se utiliza
ALTER
• SQL permite especificar valores por defecto (DEFAULT)
• Si no se especifica un valor, el valor por defecto (1) toma lugar.
• Un uso práctico de esto es utilizar DEFAULT CURRENT_DATE() para
insertar la estampa de tiempo de un evento.
• Manipular tablas!. Idealmente, se diseñan las DB para que esto no
suceda, sin embargo:
• Todas las DBMS permiten añadir o borrar columnas
• Permiten igualmente modificar columnas de lugar
• Cambiar de nombre las columnas
• Restricciones de columnas (no insertar si …)
• En el siguiente ejemplo añadimos una columna mas a una tabla de la DB
• Igualmente podemos borrar la tabla recién creada o cualquier otra
64
CREAR, MANIPULAR TABLAS/DB
• Cuando se van a realizar actualizaciones de columnas se debe:
• Crear una tabla nueva con el formato de columnas de la vieja tabla
• Copiar los datos de la tabla con, p.e., INSERT SELECT
• Verificar que la tabla contiene los datos
• Renombrar la tabla vieja (o borrarla si eres valiente)
• Renombrar la nueva tabla con el nombre de la vieja
• Recrear ciertas condiciones especiales de la tabla (p.e., triggers)
• Usar ALTER cuidadosamente, asegurarse de tener copias antes de
proceder, pues borrar una columna necesaria incurre en la pérdida de
información.
• ELIMINAR tabla completamente
• RENOMBRAR tablas
65
MANIPULACIÓN DE VISTAS
Vistas
=
Virtuales
Tablas
Tablas
contienen
datos,
vistas
contienen queries que
recargan
datos
dinamicamente
cuando se usan
• Recordando el siguiente query de joins: Nombre y apellido de los
clientes que ordenaron un producto en específico, el ‘S18_1749’
• Entender el query y estructura de
datos es complejo
• Si queremos información de otro
producto, solo se modifica el num.
• Las
vistas
comprimen
las
sentencias
• Con vistas, el query es más simple
• ¿Porqué usar vistas?
• Reusabilidad de sentencias SQL
• Simplificar queries, sin necesidad de conocer su interior
• Expone partes de tablas y no tablas enteras
• Seguridad de la información. Acceso a ciertas tablas
• Cambiar la presentación visual
66
MANIPULACIÓN DE VISTAS
Para crear vistas
utilizamos CREATE
VIEW
• Crear una vista!, del siguiente query.
• Las vistas simplifican las búsquedas!!!
• Se deben crear las vistas sin estar atadas a la información.
• Crear una vista de datos reformateados!
• Crear una vista datos calculados!
67
CREACIÓN DE PROCEDIMIENTOS
Se han visto queries
de una o mas tablas
que unen otras tablas.
Generalmente
se
realizan otro tipo de
operaciones!!!
Procedimientos:
Sentencias
SQL
almacenadas
para
futuro uso
• Ejemplo de procesar una orden:
• El cliente antes de poner una orden, el sistema verifica si hay
stock
• Si existe stock, deben ser reservados los items para no ser
vendidos
• La cantidad de stock se reduce
• Items no en stock necesitan interaccion con el vendedor para
recuperar el stock
• El cliente necesita ser notificado de cuales estan en stock y en
back order
• ¿Cómo se realiza esto?
• Sentencias SQL escritas por separado
• Cada sentencia se ejecuta condicionalmente
68
CREACIÓN DE PROCEDIMIENTOS
• ¿Porqué utilizar procedimientos almacenados?. Simplicidad, seguridad
y desempeño.
• Simplificar operaciones complejas
• Consistencia de los datos y no se recreen una y otra vez los pasos
• Simplificar cambios, reduce corrupción de datos
• Se almacena de forma compilada e incrementa el desempeño
• Flexibilidad!. Simples queries se utilizan para escribir un pedazo
de código.
• Desventajas
• Varian entre DBMS, pero se puede lograr a hacer lo más portable
posible
• Más difíciles de escribir que sentencias SQL
69
CREACIÓN DE PROCEDIMIENTOS
• Creando un procedimiento sencillo (Buscar el cliente por nombre)
• Llamar el procedimiento
• Importante!!!.
• Como los procedimientos son código y depende de la lógica
individual, se debe documentar el código para su comprensión
• -- Comentarios en SQL
70
ADMINISTRACIÓN DE TRANSACCIONES
Objetivo: Mantener
la integridad de la
base de datos para las
transacciones
ejecutadas o no
• Ejemplo: Creando una transacción (Flujo normal)
• Verificar el cliente en la DB, si no existe, crear
• Recuperar el ID de cliente
• Añadir la fila a la tabla de ordenes asociadas al ID de cliente
• Recuperar el ID de la orden
• Añadir los detalles de la orden de la tabla de productos en la tabla
de detalles de orden cuadrando los datos de la orden
• Imaginemos varios fallos! (causados por espacio, seguridad, restricción)
• Fallo luego de agregar cliente!
• Normal. Es común tener clientes sin ordenes. El cliente
intentará de nuevo ingresar al sistema
• Fallo luego de agregar una orden, pero antes de agregar un item!
• Existe una orden vacía en la DB
• Fallo agregando items a la orden!
• Orden parcial en la base de datos
• ¿Cómo se soluciona? – Procesos Transaccionales
71
ADMINISTRACIÓN DE TRANSACCIONES
•
•
•
•
Transacciones: Un bloque de sentencias SQL
Retroceso (Rollback): El proceso de deshacer las transacciones hechas
Confirmar (Commit): Escribir las transacciones SQL a la DB
Punto de Retorno (Savepoint): Un punto temporal de la transacción
donde se realiza el retroceso
• ¿Qué sentencias se les puede hacer Rollback?
• INSERT
• UPDATE
• DELETE
• SELECT?. No se puede (no es necesario)
• Inicio de Transacciones
• Retroceso de transacciones
• Confirmar Transacciones
• Punto de Retorno
72
ADMINISTRACIÓN DE TRANSACCIONES
• Administrando las transacciones nos aseguramos que las sentencias en
SQL se ejecuten en bloques y no den operaciones parciales.
• El resultado del ejemplo sería:
• Verificar el cliente en la DB, si no existe, crear
• Confirmar la operación del cliente
• Recuperar el ID de cliente
• Añadir la fila a la tabla de ordenes asociadas al ID de cliente
• Hacer un retorceso si se produce un error del enunciado anterior
• Recuperar el ID de la orden
• Añadir los detalles de la orden de la tabla de productos en la tabla
de detalles de orden cuadrando los datos de la orden
• Si se produce un error realizar un retroceso de todos los items y
toda la orden
73
ENTENDIMIENTO DE CURSORES
SQL trabaja dando un
resultado de filas de
un resultado.
Algunas veces es
necesario ir hacia
delante o atrás una o
más veces al mismo
tiempo.
• Los cursores nos permiten navegar por la data en el sentido que sea
necesario.
• Con cursores podemos:
• Separar la data para solo lectura
• Control de flujo de operación
• Separar la columna como editable o no
• Los cursores NO sirven en aplicaciones basadas en Web, en base al
modelo cliente/servidor
• Proceso de cursores:
• Antes de ser usado, debe ser definido (usando SELECT)
• Una vez declarado, debe ser abierto
• Luego de que se pueble de datos, filas individuales pueden
buscarse
• Finalmente, debe de ser desalojado para liberar memoria
74
ENTENDIMIENTO DE CURSORES
• Ejemplo de cursor en MySQL
75
CARACTERISTICAS AVANZADAS DE SQL
MySQL
con
el
tiempo ha adquirido
características
sofisticadas de DB.
A estas características
que limitan el ingreso
de la informacióin en
las bases de datos, se
les llama limitantes
(Constraints)
• Para relacionar la información se utilizan llaves
• En bases de datos relacionales es necesario corroborar que la
información se ha insertado sin ser corrompida.
• Aunque se pueden verificar antes de insertar, siempre ejecutar un
SELECT para saber si la data existe:
• Si las reglas de integridad de base de datos se aplican a nivel de
cliente, cada cliente tiene la obligación de hacer cumplir esas
normas, y es inevitable que algunos clientes no lo hará.
• Se debe hacer cumplir reglas de UPDATE e INSERT
• Trate de que la DBMS haga las verificaciones, el cliente no es tan
eficiente.
76
CARACTERISTICAS AVANZADAS DE SQL
Llaves
Primarias,
como se mencionó
anteriormente,
son
columnas de valor
insertado único en la
tabla de la base de
datos.
• Sin llaves primarias es difícil hacer UPDATE o DELETE sin saber
verázmente que esta es la única información a modificar
• Las llaves primarias tienen las siguientes condiciones:
• Dos filas no puede el mismo nombre único
• Cada fila tiene una llave primaria
• La llave primaria no puede ser modificada o actualizada
• La llave primaria no puede ser reusada
77
CARACTERISTICAS AVANZADAS DE SQL
Los índices se crean
para
mejorar
la
velocidad
de
búsqueda de datos
• Supongamos que queremos buscar la palabra “SELECT” en la
presentación! (iremos página por página!)
• Los índices llevan hacia la información que queremos buscar
• La llave primaria es un índice, pues siempre está ordenada
• Buscar valores en columnas no es eficiente si no se busca por llave
primaria
• La DB debe leer cada registro
• Por ejemplo: Buscar en la tabla de clientes por ciudad las personas
que están en un estado específico
• Antes de usar índices considere:
• Estos mejoran la búsqueda, pero degradan el desempeño de
inserción
• Ocupan gran espacio de almacenamiento
• Se utilizan para filtrar y ordenar datos, si la información siempre se
pide de esta manera, se recomienda utilizar índices
• Múltiples columnas pueden ser índices
78
CARACTERISTICAS AVANZADAS DE SQL
Disparadores
(Triggers)
son
acciones que ejecuta
la base de datos
cuando
detecta
actividad.
• A diferencia de los procedimientos, los disparadores solamente se
relacionan a una tabla
• Se pueden ejecutar después de operaciones como:
•
•
•
Ingreso de nuevos datos (INSERT)
Actualizacion de datos nuevos o viejos (UPDATE)
Borrado de datos (DELETE)
• Los triggers se usan comunmente para:
• Consistencia de datos
• Validación de datos y retroceso (ROLLBACK)
• Calcular datos de valores de columnas o actualizar estampas de
tiempo
• Creamos la tabla y el disparador
• Insertamos valores y cuestionamos la información
79
CARACTERISTICAS AVANZADAS DE SQL
La seguridad de la
base de datos se da
con las cláusulas
GRANT y REVOKE
• Para la seguridad se usan comandos que habilitan o deshabilitan
permisos de acceso a:
• Administración de la base de datos
• Vista a tablas y columnas
• Tipo de acceso (solo lectura, escritura, borrado)
• Procedimientos
• Habilidad de administración de cuentas
• Creamos el usuario de prueba
• Damos ciertos privilegios como SELECT, DROP, GRANT
• Quitamos todos los privilegios
• Eliminamos el usuario
80
FIN DE LA JORNADA!!!
• Ejemplo:
– Interacción de Web Browser / MySQL / PHP
81
Descargar

Skyscrapers