SQL - script - clase 1
From Ibbddunq
Contents |
Clase 1
Antes que nada, qué base vamos a usar
La del circo, que vamos a ir introduciendo de a poco. Los esquemas de tabla son
ciudad <nomCiudad, provincia, poblacion>
funcion <diaFn, horaFn, nomCiudad, precioEntrada, publicoEsperado>
acto <nomActo, gradoPel, dmin, dmax> (duraciones mínima y máxima de cada acto)
artista <nomArt, fnac, cachet, nomArt-maestro, gradoMaxPel>
fnac = fecha de nacimiento, gradoMaxPel = grado máximo de peligrosidad que soporta, "aguante")
formaParte <diaFn, horaFn, nomActo, nroOrden, duracion, nomArt>
puedeParticipar <nomActo, nomArt, destreza>
actoPublicitario <diaPubl, nomCiudad, hora, contacto> (la PK son los primeros dos)
Intro
Hoy vamos a ver el lenguaje con el que vamos a hablar con una instancia de BD relacional que maneja un SGBD. Ese lenguaje se llama SQL, y cubre todas las operaciones que uno puede tener ganas de hacer con una base. ¿Qué operaciones? Eso lo vamos a ir viendo.
En particular, una operación muy importante es la consulta, nosotros ya sabemos expresar consultas a una BD relacional, lo hacemos con las operaciones del álgebra relacional. El SQL tiene la misma idea del AR, vamos a ver cómo. A lo que vamos a dedicar más tiempo es a aprender a hacer consultas en SQL.
Meternos en el entorno de interacción con una BD relacional
Ahora vamos a trabajar con una BD relacional, les vamos a dar una instancia a c/u para jugar, y un programa para accederla. Vamos a empezar practicando con un modelo relacional del circo.
Imaginarnos cómo va a ser: yo me conecto a la instancia, que es nuevita para mí.
¿Qué es lo primero que tenemos que hacer? Claro, crear las tablas. Eso lo vamos a hacer con un formulario que nos presenta, ahí indicamos el esquema, el nombre, y nos crea en la instancia donde estemos trabajando una nueva tabla cuyo esquema es el que le dijimos.
¿Qué datos necesitamos decir? (esto interactivo, así van despertando).
- El nombre de la tabla nueva.
- Atributos, para cada uno dominio.
- Cuál/es de los atributos conforman la PK.
- Qué FKs a otras tablas hay.
- Otras restricciones, p.ej. not null.
Tambíén podemos crear tablas con SQL (i.e. expresar en el lenguaje la creación de una tabla, indicando todos los datos que dijimos), en las máquinas vamos a ver cómo.
Entonces, paso 1: crear las tablas.
Ejemplo: creamos las tablas funcion y ciudad (ahi anotamos los esquemas).
Repaso de INSERT / UPDATE / DELETE
OK, tengo las tablas creadas cada una con el esquema que dijimos, pero ¿cómo están? Claro, vacías.
Ya vimos (sí lo vimos en la clase de modelo relacional) la sintaxis de INSERT / UPDATE / DELETE, repasémosla.
(sí, que la lean de su carpeta)
INSERT INTO tabla(atr1,...,atrn) VALUES (val1,...,valn) UPDATE tabla SET atr=val, atr=val, ... WHERE condicion DELETE FROM tabla WHERE condicion
Esto lo vamos a practicar el lunes (hoy sólo contarlo rápido)
Hacemos en pizarrón un par de INSERT sobre ciudad y uno sobre funcion, más tal vez un par de UPDATE, para cerrar la idea.
INSERT INTO ciudad (nomCiudad, provincia, poblacion)
VALUES ('Rosario', 'Santa Fe', 1200000),
('Rafaela', 'Santa Fe', 130000),
('San Francisco', 'Cordoba', 78000);
(una ciudad más)
INSERT INTO ciudad (nomCiudad, provincia, poblacion)
VALUES ('Pehuajo', 'Buenos Aires', 53000);
INSERT INTO funcion (diaFn, horaFn, nomCiudad, precioEntrada, publicoEsperado)
VALUES (20080601, 19, 'San Francisco', 18, 320);
UPDATE ciudad SET poblacion = 150000
WHERE nomCiudad = 'Rafaela'
... aumentar dos pesos el precio de entrada para las funciones cuyo público esperado no llegue a las 300 personas y cuyo precio de entrada no supere 15 pesos (para ver un UPDATE masivo).
Acá vemos varias cosas
- Formato de los String
- Formato de las fechas (hay otros, este tiene de bueno que no se pone ni comilla ni barra ni nada)
- la sentencia SQL termina en punto y coma.
- que se pueden insertar varias filas de una.
Pregunta, ¿qué pasa si hago un INSERT sobre función poniendo una ciudad que no existe, o para la misma fecha-hora que una que ya está?
ponele ...
INSERT INTO funcion (diaFn, horaFn, nomCiudad, precioEntrada, publicoEsperado)
VALUES (20080615, 19, 'Pepelandia', 18, 320);
y 'Pepelandia' no es una ciudad
Claro, salta la restricción de integridad correspondiente. Eso lo podemos probar el lunes.
Se puede hacer que digan un INSERT que viole clave, y un UPDATE que haría saltar una restricción, para practicar la sintaxis.
Introducción al SELECT
Una vez que hicimos unos cuantos INSERT válidos, tenemos una base con tablas que tienen filas, que no están vacías.
Ahora queremos obtener información haciendo búsquedas sobre esta instancia de BD. Ya sabemos una forma de expresar qué queremos: AR.
El concepto análogo al álgebra relacional en SQL es una operación que se llama SELECT, que tiene un montón de opciones que cubren la mayor parte del AR y más cosas. Una cosa importante es que al igual que en el AR, todo SELECT de SQL devuelve una tabla, y vamos a pensar al igual que con AR qué atributos tiene (a nivel de esquema) y qué filas tiene (a nivel de instancia) el resultado de un SELECT.
Vamos a empezar con algo sencillo: proyección y selección sobre una tabla.
La sintaxis básica del SELECT es
SELECT atr1,...,atrn FROM tabla WHERE condicion ORDER BY criterio de ordenamiento
La parte SELECT ... FROM ... WHERE ... la podemos escribir en AR así
P<atr1,...,atrn>(S<cond> tabla)
Con el ORDER BY vemos una característica de los SGBD distinta a la teoría del modelo relacional: las tablas están ordenadas,
una tabla es una secuencia de filas, no un conjunto de filas
Se puede entender que en toda tabla, incluyendo los resultados de las consultas, hay un atributo "oculto", que es el nro de orden de cada fila. Las tablas que forman la instancia de BD se ordenan (en ppio) con un criterio físico que no viene al caso. Lo que sí puedo hacer es ordenar el resultado de una consulta, para eso el ORDER BY.
Ejemplos usando ciudad, funcion y formaParte (anotamos los esquemas de acto y formaParte):
Acá contar que un atributo puede ser una cuenta, y el renombrado. P.ej.
- nombre y margen de tiempo (dmax - min) de cada acto peligroso.
- nombreArtistico, fechaNacimiento, cachet de cada artista, con estos nombres de atributo.
Síiii el renombrado es más fácil.
El tema del "atributo oculto del orden" también explica que aunque dos filas en el resultado de una consulta coincidan en todos los atributos que vemos, no son iguales porque difieren en el valor del atributo oculto; de ahí que veamos que en SQL pueden aparecer "filas repetidas" lo que no valía en AR. P.ej. día y hora de las funciones en las que participó pepe, si hizo dos actos en la misma función, sí va a aparecer repetido. Si quiero que el resultado de un SELECT no tenga duplicado, tengo que poner
SELECT DISTINCT atr1, ..., atrn ... el resto igual ...
- nombre y población de las ciudades de la prov. de Santa Fe, ordenados por ciudad.
- variaciones: ciudades de Santa Fe y Córdoba (en la misma consulta), ciudades de Santa Fe con al menos 100000 habitantes, ordenar por población (¿cuál queda primero? debatir), ordenar por población de mayor a menor.
- funciones ordenadas por día/hora, acá ver
SELECT * - provincias, sirve para ver duplicados y SELECT DISTINCT
- para null: (día, hora, nroOrden, acto) de los actos programados sin artista asignado.
- nombre y duración de los actos que se presentan en la función del 01/06/2008, en el orden en el que van en la función, acá se aprecia el ORDER BY.
- nombre de las ciudades donde va a haber funciones donde se esperan más de 300 personas
- día, hora, ciudad, ingreso estimado (precio * público) de las funciones de junio de 2008. Esto para que se vea un atributo calculado.
- variación: donde se esperan más de 300 personas y el precio de la entrada es más de 15 pesos.
- nombres de las ciudades donde se va a presentar el circo en junio de 2008. Este engancha con el primer ejemplo de la ronda siguiente, así que dejalo solito en el pizarrón ;-).
Joins
Join natural
Volvemos a la sintaxis básica
SELECT atr1,...,atrn FROM tabla WHERE condicion ORDER BY criterio de ordenamiento
ahora quiero joinear dos tablas; ponele, quiero día, hora, ciudad y provincia de cada función de abril, entonces (que lo digan ellos) tengo que joinear ciudad con funcion mediante natural join.
Nosotros que sabemos mucho de AR lo haríamos así
P<...>((S <diaFn >= 01/04/09 ^ diaFn <= 30/04/09> funcion) * ciudad)
pero también se puede meter el join para adentro
P<...>(S <diaFn >= 01/04/09 ^ diaFn <= 30/04/09> (funcion * ciudad))
La forma de traducir a SQL estas consultas se va a parecer a la segunda, como lo vamos a escribir es como si estuvieran todos los joins adentro y toda la selección afuera. El motor de SQL se va a avivar, muchas veces, de hacer las operaciones de una forma razonablemente eficiente. De eso vamos a hablar (un poquito) cerca del fin del curso, x ahora vamos con los joins para adentro.
Entonces, vamos a SQL. ¿En qué lugar de la sentencia SELECT pondrían el join ...? Usen su intuición (los que no saben SQL, claro)
Bien, va dentro del FROM, la tabla del FROM puede ser el resultado de una operación (¿de un SELECT también? pero claro que sí, eso lo vamos a ver en detalle la clase que viene).
Para joinear dos tablas mediante join natural ... tabla 1 NATURAL JOIN tabla2. Por costumbre se escribe así
SELECT atr1,...,atrn
FROM tabla1
NATURAL JOIN tabla2
WHERE condicion
ORDER BY criterio de ordenamiento
Acá otra ronda de ejemplos
- nombre de ciudad y provincia donde se va a presentar el circo en junio de 2008. Este engancha con el último de la ronda anterior.
- actos que se van a dar en junio.
- día, hora, ciudad y público esperado de las funciones donde va a haber payasos.
¿Y si quiero joinear varias tablas? Ponele las provincias donde se van a presentar payasos en junio de 2008. En AR (que lo piensen) tengo que hacer
(S<acto = 'payasos'>formaParte) * (S<fecha en junio 2008>funcion) * ciudad
que es lo mismo que
S<acto = 'payasos'>formaParte AND fecha en junio 2008>(formaParte * funcion * ciudad)
en SQL lo vamos a escribir en principio de la forma de abajo, con todos los select afuera; los motores se avivan de lo que en AR hacíamos a mano de meter las selecciones para adentro.
La sintaxis para joinear varias tablas nos queda
SELECT atr1,...,atrn
FROM tabla1
NATURAL JOIN tabla2
NATURAL JOIN tabla3
WHERE condicion
ORDER BY criterio de ordenamiento
Hacer el ejemplo.
Acá detenerse un poco. Un SELECT de SQL como los que vimos equivale en resultado a hacer esto en "AR extendido" (extendido por el ordenamiento)
O<crit>(P<atrs>(S<condiciones>(join de tablas)))
Eso no quiere decir que el SGBD lo vaya a hacer así, muchas veces se aviva de mandar los select para adentro para que el join no se haga entre tablas tan grandes. Una consulta SQL es una forma de programa ... pero tiene características bien distintas al Gobstones. En particular, nosotros indicamos una consulta que implica varias operaciones, pero nosotros no indicamos el orden de las operaciones, especificamos más el resultado que la secuencia de pasos para obtenerlo. Esto tiene que ver con declarativo vs. imperativo.
Sí debemos recordar que cuando hacemos join natural, mira todos los atributos que coinciden en nombre, entonces a veces hay que tener cuidado con eso.
Más ejemplos
- provincias donde va a haber actos muy peligrosos (grado de peligrosidad > 5)
- nombre, duración minima y duración máxima de los actos que se van a presentar en Santa Fe o Córdoba (en al menos una de las dos provincias) en junio de 2008.
(nota al margen: si pido "que se presenten en Santa Fe y en Córdoba se pone más espeso, lo más directo que se me ocurre es con subselect, tal vez sea un lindo ejemplo para introducir subselect).
- artistas que pueden participar en al menos un acto que se va a presentar en Santa Fe o Córdoba (en al menos una de las dos provincias) en junio de 2008.
- Artistas y actos en los que pueden participar, que son más peligroso que el aguante que declara el artista.
- <día, hora, nro orden, acto, artista posible> para las funciones que se van a hacer en Amstrong, ordenados x día, hora y nro de orden.
Otros joins
También puedo hacer las otras versiones de JOIN
Para join con condición ("moñito")
FROM tabla1
JOIN tabla2 ON condicion
Para producto cartesiano
FROM tabla1
JOIN tabla2
Ejemplos:
- Combinaciones de artistas con actos
- Todos
- Los que le da el aguante al artista
- Aguante y además cachet artista <= 100 * peligrosidad del acto
- Funciones con actos
- todos
- actos de peligrosidad <= precio de la entrada / 5 (o sea, por 15 pesos, sólo presento actos de peligrosidad <= 3, si quieren heavy que paguen :P).
- actos que están programados en funciones y que no cumplen este requisito económico (o sea, que su grado de peligrosidad es > precio de la entrada / 5).
Agrupamiento de joins
Cuando ponemos
FROM tabla1
NATURAL JOIN tabla2
NATURAL JOIN tabla3
o cualquier combinación de sabores de join, el agrupamiento es a izquierda, o sea, lo anterior se lee así
FROM (tabla1
NATURAL JOIN tabla2)
NATURAL JOIN tabla3
en la mayor parte de los casos no va a influir, en algunos sí. Si quiero forzar que agrupe distinto, como (tabla1 JOIN tabla2) es una tabla, lo puedo hacer así
FROM tabla1
NATURAL JOIN (tabla2 NATURAL JOIN tabla3)
Ejemplo:
- para los actos de la función del 01/06/2008 a las 19 sin artista asignado: acto, dmin, dmax, duración real, nro orden, artista que podría hacerlo con criterio aguante, es (formaParte * acto) join-moñito artista, o artista join-moñito (formaParte * acto).
- para las funciones de la prov Sta Fe, los actos que podrían ir con criterio peligrosidad <= precio de la entrada / 5.
Lo que no puede pasar es que cuando hago una consulta no poner el SELECT, si pongo p.ej.
tabla1 NATURAL JOIN tabla2
así solito, y le mando la sentencia al SGBD, me va a tirar un error.
Siempre la sentencia completa tiene que ser
SELECT ... FROM ... [WHERE ...] [ORDER BY ...]
WHERE y ORDER BY pueden faltar, SELECT y FROM no. Si no pongo WHERE, es como no poner selección.
P.ej. si quiero ver toda la tabla acto, ¿cómo hago? ... contar el SELECT * que quiere decir "sin proyección". Sí, ya lo usamos, refrescarlo.
Momento Rexona
Creo que acá viene bien un repaso con un par más de ejemplos antes de seguir
- artistas que van a conocer Venado Tuerto (son los que van a participar de alguna función que se hace en esa ciudad).
- <dia,hora,ciudad,provincia,publicoEsperado,poblacion> para las funciones que tienen al menos un acto con duración incorrecta (i.e. menor a la mínima o mayor a la máxima).
- <dia,hora,ciudad,acto,duración,dmax,artista,destreza en ese acto> para las funciones con precio entrada >= 30
- <nroOrden, acto, duracion, artista, cachet> para la función del 01/06/2008 a las 19
Chiches
Constantes y funciones
Cada atributo de la lista del SELECT ... puede ser una cuenta (p.ej. atr1 + atr2), o también una cte (p.ej. 'A').
Ejemplos
- para las funciones del 2008 (función YEAR): <dia, hora, precio entrada, recaudación total esperada, % de la población de la ciudad que se espera>
- día / hora / ciudad / provincia para las funciones de recaudación total esperada > 10000
- para los actos que se presentan en la función del 01/06/2008 a las 19: <nroOrden, acto, duración, destreza del artista asignado, cuánto se puede estirar -hasta dmax-, cuánto se puede acortar -hasta dmin-, coeficiente de eficiencia>.
El coeficiente de eficiencia de un acto es: cachet del artista / (grado de peligrosidad * destreza). - para los actos que se presentan en la función del 01/06/2008 a las 19: <'A', 3, nroOrden, artista> o "FALTA ASIGNAR" si no tiene artista asignado (IFNULL(nomArt, 'FALTA ASIGNAR'))
Aliases = cambio de nombre + nombres de atributo vienen con tabla adelante
OJO que los atributos se llaman, en realidad, tabla.atributo, y así las puedo tratar en el SELECT. Por lo tanto, si joineamos tablas que coinciden en los nombres de atributo, van a estar los dos. Esto no se cumple con un join natural. No jode porque es muy raro usar SELECT *, entonces pido los atributos que quiero. Y puedo desambiguar en la consulta poniendo el alias.
P.ej. si joineo cada función con cada ciudad cuya población >= 300 veces el público esperado, i.e.
select *
from funcion
join ciudad where poblacion > 300 * publicoEsperado
order by diaFn, horaFn, poblacion;
el resultado tiene dos atributos, funcion.nomCiudad y ciudad.nomCiudad. Sí vale poner
select funcion.nomCiudad, ciudad.nomCiudad
from funcion
join ciudad where poblacion > 300 * publicoEsperado
order by diaFn, horaFn, poblacion;
y también ponerle alias
select funcion.nomCiudad as ciudadFuncion, ciudad.nomCiudad as ciudadMasGrande
from funcion
join ciudad where poblacion > 300 * publicoEsperado
order by diaFn, horaFn, poblacion;
Vemos que es más fácil renombrar atributos en SQL que en AR, porque le puedo poner "as" a los que quiero. P.ej.
select diaFn, horaFn, nomCiudad, precioEntrada, publicoEsperado,
precioEntrada * publicoEsperado as recaudacionEsperada
from funcion
order by diaFn, horaFn;
También mostrar por qué no conviene poner "nombre" como atributo, te engancha en el join natural.
Imaginemos que tanto nomCiudad (en ciudad, funcion y actoPublicitario) como nomActo (en acto, formaParte, puedeParticipar) se llamaran "nombre" los dos. Al hacer
SELECT loQueSea
FROM funcion
NATURAL JOIN formaParte
estaríamos comparando diaFn, horaFn ... y nombre, que estaría asimilando nombre de ciudad (en funcion) y nombre de acto (en formaParte).
Para poder hacer el join que queremos, lo deberíamos hacer con moñito, o sea
SELECT loQueSea
FROM funcion
JOIN formaParte ON funcion.diaFn = formaParte.diaFn AND funcion.horaFn = formaParte.horaFn
Un uso común de los alias es cuando tengo que joinear dos filas de la misma tabla, entonces las distingo poniéndole dos alias distintos a las dos filas que voy a joinear.
Un ejemplo (contarlo despacito, que a esta altura ya van a estar cansados) Quiero una tabla <nombre, cachet, nombre del maestro, cachet del maestro> para los artistas. ¿Cómo hacerlo en AR? ... parar a pensar un poco, tal vez desde el resultado y planteando una tabla de ejemplo
- ¿Qué queremos en cada fila del resultado?
- La info que queremos en una fila del resultado ... está en dos filas distintas de la misma tabla. Entonces tengo que pegar las filas.
- Es un join de artista con artista.
- Uh, para ponerlo en AR hay que renombrar:
artista * (P(nomArt-maestro,cachet-maestro)<nomArt,cachet> artista)(Mara, pensalo un toque y te das cuenta :P). - Acá usamos que el nombre completo de un atributo es tabla.atributo, y que puedo ponerle alias a las tablas. Entonces las pienso como dos tablas (acá elegir los alias, p.ej. disc y maestro), ¿cómo es el join (sin renombrar atributos?
Acá escribiría los atributos dos veces, con sus tablas, para que sea gráfico cómo tiene que ser el join. Nos queda
select disc.nomArt, disc.cachet, maestro.nomArt, maestro.cachet
from artista disc
join artista maestro on disc.nomArt_maestro = maestro.nomArt
order by disc.nomArt;
(sólo si les entra la duda, si no lo decimos el lunes)
OJO que los aliases de atributos no los puedo usar en las condiciones, los de tabla sí. El orden
O<crit>(P<atrs>(S<condiciones>(join de tablas)))
nos explica por qué :D (y más adelante también ayuda a entender por qué haciendo un subquery sí puedo).
Más ejemplos de join de una tabla consigo misma
<duracion payasos, duracion mago> de las funciones que tengan payasos y mago.
select mago.duracion, payasos.duracion
from formaParte mago
join formaParte payasos on mago.diaFn = payasos.diaFn and mago.horaFn = payasos.horaFn
where mago.nomActo = 'mago'
and payasos.nomActo = 'payasos';
actos que vienen después de los payasos, con la diferencia de nro de orden entre ambos
select actoSiguiente.diaFn, actoSiguiente.horaFn, actoSiguiente.nomActo,
actoSiguiente.nroOrden - actoPayasos.nroOrden as difOrden
from formaParte actoSiguiente
join formaParte actoPayasos on actoSiguiente.diaFn = actoPayasos.diaFn
and actoSiguiente.horaFn = actoPayasos.horaFn
and actoSiguiente.nroOrden > actoPayasos.nroOrden
where actoPayasos.nomActo = 'payasos'
order by actoSiguiente.diaFn, actoSiguiente.horaFn, actoSiguiente.nroOrden;
<artista, acto, destreza, maestro, destreza del maestro>
select puedeDisc.nomActo, disc.nomArt, puedeDisc.destreza, maestro.nomArt, puedeMaestro.destreza
from artista disc
join artista maestro on disc.nomArt_maestro = maestro.nomArt
join puedeParticipar puedeDisc on puedeDisc.nomArt = disc.nomArt
join puedeParticipar puedeMaestro on puedeMaestro.nomArt = maestro.nomArt
where puedeDisc.nomActo = puedeMaestro.nomActo;
artistas que actúan inmediatamente después de Pepitito.
Union
Ejemplos
- todo lo que va a pasar en junio de 2008, hay que unir funciones con actos publicitarios. Está bueno para montar la explicación sobre eso.
- artistas que: o bien van a participar de la función del 01/06/2008 a las 19, o bien tienen destreza > 3 en sogas.
- actos que son poco peligrosos, o que tienen alguien que los maneja (destreza > 6)
Contar la sintaxis.
- En particular, la condición de compatibilidad para la unión cómo es. En MySQL sólo se pide igual cantidad de columnas, con los dominios la maneja de alguna forma (pasando todo a String en último caso).
- La conveniencia de poner constantes para saber de dónde vienen, p.ej.
select 'A', diaFn, horaFn from funcion where month(diaFn) = 6 and year(diaFn) = 2008 union select 'FP', diaPubl, hora from actoPublicitario where month(diaPubl) = 6 and year(diaPubl) = 2008;
Ver que lo que estamos haciendo es
[O (P (S ( joins ) ) ) ] U [O (P (S ( joins ) ) ) ]
Entonces, en particular, ¿cómo queda ordenado un UNION? Claro, no lo decimos, porque no tenemos un ORDER BY afuera. Entonces el resultado del UNION tiene que ser la "tabla" de otro SELECT, de esta forma
SELECT *
FROM ( (SELECT ... FROM ... WHERE ...)
UNION
(SELECT ... FROM ... WHERE ...)
)
ORDER BY ...
analizarlo con un ejemplo, p.ej. funciones y acciones publicitarias en abril, ordenadas x fecha.
Lo mismo si quiero hacer S(T1 U T2), p.ej.
select *
from ( select 'A', diaFn as dia, horaFn as hora from funcion
union
select 'FP', diaPubl as dia , hora from actoPublicitario
) eventos
where month(dia) = 6 and year(dia) = 2008
order by dia, hora
