Bienevenidos sean a mi post. Veremos todas las opciones que permite el comando SELECT, tambien como hacer consultas complejas, como formatearlas, estructurarlas y delimitarlas. Como vimos anteriormente al comando SELECT lo utilizamos para obtener la informacion de la tablas que tenemos cargadas en la bases, por ejemplo la instruccion mas basica que podemos utilizar con las bases que creamos en el post anterior es select * from alumnos, esta nos va a traer toda la informacion de todos los alumnos pero tambien se le puede dar un formato por ejemplo para obtener un determinado registro  de la tabla como por ejemplo: nombre, apellido y edad de una tabla de una sola persona, la linea seria la siguiente: select nombre,apellido,edad from alumnos where apellido=’perez’. Pasemos a ver la estructura, con sus clausulas mas clasicas:

SELECT campos a obtener
FROM tablas a chequear
WHERE condicional para la busqueda

Mas adelante en este post veremos algunas clausulas mas que se pueden utilizar en el comando SELECT, este comando no solamente visualiza los datos de una tabla, la cual es definida a traves de la clausula FROM sino tambien sirve para visualizar otras operaciones, por ejemplo nosotros podemos hacer la siguiente sentencia:

select 2+2, ‘Hello, World’, version();

Y la salida de esa linea es esta:

+——-+———————+—————————+
| 2+2 | Hello, World | version()              |
+——-+———————+————————–+
|   4   | Hello, World | 5.5.49-0+deb8u1 |
+——-+———————+————————–+

Pero, volvamos a lo que mas nos interesa (o por lo menos lo mas utilizado), la consulta de informacion de las bases de datos. Cuando uno ejecuta una consulta y utiliza el comodin asterisco (*) este nos devolvera toda la informacion y las columnas de las tablas en el mismo orden que fue creada, una suposicion posible seria necesitar modificar el formato de las columnas o simplemente no necesitar toda la informacion, en esos casos definimos las columnas a visualizar o bien las columnas en el nuevo orden que necesitamos, las vamos definiendo una por una en la secuencia deseada, veamos un ejemplo con la base alumnos que ya tenemos creada:

Primero veamos la estructura de la tabla alumnos, a traves del comando describe:

mysql01

Ahora supongamos que ejecutamos el comando SELECT para ver toda la informacion de la tabla:

mysql02

Ahora pasemos al primer caso que necesitamos que se formatee en otro orden las columnas:

mysql03

Ahora suponemos que no necesitamos toda la informacion de la tabla sino solamente algunos datos de nuestros alumnos:

mysql04

Perdon si tuve que mostrarles con fotos pero queda mejor visualizado de esta forma en lugar de copiar y pegar de la ventana de comandos, o por lo menos para estos ejemplos, pero como pueden ver primeros ejecutamos un describe de la tabla para ver como esta formateada la misma, despues ejecutamos un select para ver toda la informacion contenida en la misma luego hicimos una consulta modificando el orden de las columnas de la tabla para el query y por ultimo hicimos una consulta modificando el orden y no mostrando algunos campos que no necesitemos. Ahora vamos a ver como podemos estructuar la informacion a mostrar a traves de algun criterio de busqueda declarada mediante la clausula WHERE. Estos son algunos ejemplos:

select nombre,apellido from alumnos where pcia = ‘ba’;

Si nosotros ejecutaramos esta linea, estamos diciendo que de la tabla alumnos nos traiga el nombre y apellido de los alumnos que sean de la provincia (pcia) de ba (Buenos Aires), por lo menos es para el caso de la informacion que cargue en mis tablas, la salida seria asi:

+————–+——————–+
| nombre |    apellido     |
+————–+——————–+
|    Juan    | Perez             |
|   Marta  | Gargaglione |
|   Martin | Miranda       |
+————–+——————–+

Ahora veamos el siguiente ejemplo:

select nombre,apellido from alumnos where edad<30;

En este caso nos va a traer unicamente los alumnos que sean menores de 30 años, la salida del comando seria asi:

+————–+—————+
| nombre | apellido  |
+————–+—————+
|    Juan    | Perez       |
|  Martin  | Miranda |
+————–+—————+

Vamos a suponer la siguiente situacion, nosotros necesitamos obtener un listado de alumnos menores de 30 que sean de la ciudad de Castelar, para eso hariamos lo siguiente:

select nombre,apellido from alumnos where edad<30 and ciudad = ‘castelar’;

Si ejecutaramos esto, con la data que ingrese, me devolveria esta salida:

+————–+—————+
| nombre | apellido  |
+————–+—————+
| Martin   | Miranda |
+————–+—————+

Porque es el unico que cumple la condicion de que es menor de 30 años y a su vez es de la ciudad de Castelar. Como ven es bastante simple, solamente es usar un poco de logica a la hora de dar formato al condicional, un dato a tener cuenta es al utilizar las condiciones en algunas ocasiones puede que no devuelva ningun dato ya que la salida no cumple ninguna de las condiciones que pusimos. El comando select acepta los siguientes operadores:

Operadores matematicos:

  • + (Adicion)
  • –  (Sustraccion)
  • * (Multiplicacion)
  • /  (Division)

Operadores de comparacion:

  • <  (Menor que)
  • >  (Mayor que)
  • =  (Igual que)
  • != (Distinto a ) – Tambien se puede usar el <> pero es mas recomendable el otro.
  • <= (Menor o igual que)
  • >= (Mayor o igual que)

Operadores logicos:

  • AND (y logico)
  • OR    (o logico)
  • NOT (Negacion logica)

Como ven los operadores matematicos son para ejecutar las operaciones de toda la vida, sea sumar, restar, multiplicar o dividir, con los otros podremos decir cuando una columna de una tabla sea igual, menor o mayor a un valor X o a otra columna de la tabla o la columna de otra tabla y por ultimo tenemos los operadores logicos, cuando utilicemos el operador AND realizando una comparacion de valores con otra comparacion de valores y en el caso de ser verdad ambas se ejecute el query o utilizar el operador OR para realizar una comparacion con otra comparacion, y si alguna de ellas (no las dos) sea verdad proceda con la ejecucion del query. Ya hasta aca vimos algunos ejemplos y como estructurar la salida de nuestro Query. La clausula que veremos ahora es ORDER BY, esta nos permite ordenar la informacion en base a alguna columna de la tabla, por ejemplo volvamos al ejemplo donde dijimos que necesitamos algunos datos como eran el nombre, apellido, id y edad. Ahora vamos a modificar la linea para que nos traiga esa informacion pero ordenado por la edad

select nombre,apellido,id,edad from alumnos order by edad asc

Como se ve, ahora cuando lo ejecutemos la salida del formato va a estar condicionada por la clausula order by, la cual va a ordenar la salida en base a la edad y el asc le indica que tiene que hacerlo de forma ascendente, es decir de menor a mayor, en el caso de que fuera texto lo ordena alfabeticamente (de la A a la Z), la otra opcion para utilizar en lugar de asc es desc, en este caso le informamos al order by que lo ordene de forma descendente, seria de mayor a menor en el caso de valores numericos y en el caso de texto de forma inversa al alfabeto (de la Z a la A). Esta opcion en el order by no es obligatoria por lo tanto si la omitimos esta de forma predeterminada tomara la opcion de forma ascendente. Con esto pudimos hacer un orden de los datos que nos devuelve la consulta, otra clausula mas que tiene select es poder limitar la cantidad de lineas que nos tiene que devolver, esta opcion se puede utilizar para una consulta muy grande pero que nosotros sabemos que vamos a  necesitar solamente algunas lineas, haciendo que no se desperdicien recursos en el servidor trayendo un monton de informacion innecesaria, la clausula es LIMIT y esta en gral va a la final de la linea, veamos el ejemlo anterior pero con esta nueva clausula:

select nombre,apellido,id,edad from alumnos order by edad asc limt 3

La linea de nuevo nos trae las cuatro columnas solicitadas pero nos va a mostrar solamente los 3 primeros registros en vez de los seis que tiene la tabla. una opcion de limit es poder decirle desde donde podemos empezar a limitar nuestra consulta. En el caso anterior como pusimos un solo numero limit considera que debe empezar desde cero (el primer registro devuelto) y desde este limitarlo a los tres valores que deseamos, ahora suponiendo que nosotros ponemos el valor 10 y luego separada por una coma 5, el primer valor le va a decir a limit que empiece desde el registro decimo y desde ahi lo limite en cinco, asi seria la linea de ejecucion:

select * from notas order by nota asc limit 10,5;

Entonces el query buscara hasta la linea decima y desde ahi devolvera el valor hasta que cuente cinco o se termine la consulta. Otra opcion interesante es RAND(), esta en conjunto con la clausula order by permitira generar un orden al azar del campo que esta definido como PRIMARY KEY y si no hay ninguno definido elegira a su conveniencia, esto junto con limit podriamos hacer una busqueda de un valor a la azar dentro de una tabla X, por ejemplo:

select nombre,apellido from alumnos order by rand() limit 1;

Va a elegir cualquier valor de la tabla y lo va a devolver y cada vez que lo ejecuten siempre va a devolver uno distinto. Ahora pasemos a como calcular, concatenar y nombrar los valores de columna de salida, ejecutemos la siguiente linea:

select 17,format(sqrt(3*3+4*4),0);

La salida seria de la siguiente forma:

mysql05

Ahora, esto tambien se puede hacer con los textos, por ejemplo nosotros podriamos concatenar texto de varias columnas, esto se hace atraves del comando concat que nos permitira juntar texto de la forma que le digamos, y mostrarles como una sola en la salida, pasemos a una linea de ejecucion para verlo en accion:

select concat(nombre,” “,apellido), concat(ciudad,”, “,pcia) from alumnos;

Esto nos dara una salida como esta:

mysql06

Podran observar que efectivamente realizo la concatenacion solicitada permitiniendo tener una mejor salida de la informacion porque ahora si bien en la tabla de alumnos el nombre y el apellido siguen estando separados pero  para la salida de la consulta pudimos unirlos y mostrando un solo campo, lo mismo para la ciudad y la provincia pero como se ve el encabezado de la columna pone la formula que nosotros utilizamos para ejecutar la accion sobre los campos. Lo bueno de mysql, es la gran cantidad de opciones que tiene para estas eventualidades permitiendonos modificar el encabezado de la salida por otro nombre, esto se logra con una simple opcion llamada as, esta nos permite generar un alias para las formulas que utilicemos, se la ubica detras de cada una de las concatenaciones que realizamos. La nueva linea seria:

select
concat(nombre,” “,apellido) as “Nombre Completo”,
concat(ciudad,”, “,pcia) as “Lugar de Nacimiento”
from alumnos;

Observen que despues del concat utilizo el as y al nombre lo encierro entre comillas para que respete el espacio en blanco entre las palabras y lo tome como un nombre completo, si uds. utilizaran un nombre todo junto (p.e. NombreCompleto) o solamente una palabra, como nombre, no es necesario el uso de las comillas. Les muestro como queda la salida:

mysql07.png

Como ven ahora quedo un poco mas “presentable” nuestra consulta. Otra opcion interesante para trabajar con select son las fechas, como comentamos en el post anterior el formato por convencion de SQL (y creo que las bases de dato en gral.) es AAAA-MM-DD, entiendase por año-mes-dia, esto nos da la pauta que a nuestra consulta para la fecha deberiamos hacerla de esta forma:

select * from examenes where fecha = ‘2017-05-22’;

Esta linea nos traera solamente los registros de la fecha que informamos, por ejemplo veamos un caso donde queremos saber los alumnos aprobados con una nota mayor a 7 entre dos fechas:

select
concat(alumnos.nombre,” “,alumnos.apellido) as Alumno, examenes.tipo as “Tipo de Examen”,
examenes.fecha as “Fecha del examen”,
notas.nota as Nota
from alumnos,examenes,notas
where notas.nota >= 7
and examenes.fecha > ‘2017-04-20’ and examenes.fecha < ‘2017-09-29’
and alumnos.id=notas.alumno_id and examenes.id=notas.examen_id
order by nota desc;

Observen los detalles que pudimos utilizar todo lo aprendido hasta ahora y a su vez ahora adicionamos que tome una brecha de fechas y nos devuelva de forma ordenada de la mejor a la peor nota. La salida es algo asi:

mysql08

Hasta aqui logramos una mejor impresión sobre nuestras consultas y a su vez se pueden definir muchas mejores funciones que nos permiten traer datos cada vez mas precisos de nuestros registros. Otras opciones para utilizar con las fechas son las siguientes:

  • YEAR()
  • MONTH()
  • MONTHNAME()
  • DAYOFMONTH()

Estas opciones se utilizan para busca el año, mes, nombre del mes y el dia de una columna con el formato DATE respectivamente. en este caso haremos un ejemplo con el dia 22 de la tabla examenes:

select * from examenes where dayofmonth(fecha) = 22;

y la salida es:

+—-+—————–+——+
| id |     fecha     |tipo|
+—-+—————–+——+
| 2 | 2017-04-22 | P   |
| 4 | 2017-05-22 | P   |
+—-+—————–+——+
2 rows in set (0.00 sec)

El comando dayofmonth que busca dentro de la columna fecha solamente el valor para el dia 22, lo que afecta a la salida del mismo trayendo el resultado visto en el ejemplo. Tambien se puede hacer con el nombre del mes, como citamos en el listado, y un ejemplo seria asi: select * from examenes where monthname(fecha) = ‘april’; tengan en cuenta que si lo van a utilizar de esta forma siempre utilicen los nombres de los meses en ingles porque en español no funciona. Las fechas tambien se puede restar entre si, por ejemplo para saber una diferencia en particular entre dos fechas, para ver este caso tuve que hacer una tabla nueva llamada president, si desean crearla para ver este ejemplo vayan a este post para poder crearla, mientras tanto veamos este:

select
concat(first_name,” “,last_name) as “Nombre Completo”,
birth as Nacimiento,
death as Fallecimiento,
floor((to_days(death) – to_days(birth))/365) as Edad
from president
where death is not null
order by Edad desc;

Vean que utilizamos dos funciones nuevas, una es la FLOOR, esta nos devolvera un valor entero de la diferencia entre los valores de las columnas death y birth que a su vez se divide por 365, y el otro es TO_DAYS que su unica funcion es convertir los valores de fecha (DATE) informados en los parentesis en dias, esto nos permitira calcular la diferencia de dias entre ambas. La salida es asi:

mysql09

Ahora pasaremos a ver dos opciones nuevas, estas son DATE_ADD() y DATE_SUB(). La primera hace una adicion a una fecha definida y el otro resta a la fecha. En ambos casos se utiliza la declaracion INTERVAL esta va a tener la cantidad a adicionar o sustraer y el tipo (year, month o day), un ejemplo podria ser asi:

select date_add(‘1976-10-22’, interval 10 year);

Su salida es:

+————————————————————-+
| date_add(‘1976-10-22’, interval 10 year) |
+————————————————————-+
| 1986-10-22                                                     |
+————————————————————-+

Ahora veamoslo contra una tabla para ver los resultados obtenidos:

select last_name,first_name,death from president
where death < ‘1906-12-01’
and death >= date_add(‘1875-1-1’, interval 10 year);

mysql10

Ahora veamos otra opcion que es muy importante para algunos tipos de busquedas, hasta aqui siempre supimos el valor a buscar en las tablas pero hay veces que vamos a necesitar recuperar informacion por un dato en particular, ya sea por una letra de inicio, una porcion de texto, un valor numerico, etc. Para estos casos, utilizaremos una opcion que reemplaza al igual (=) y un comodin (%). Por ejemplo si nosotros necesitaremos saber que alumnos tienen nombres que empiezan con M y nos muestren el nombre completo, el Query es asi:

select
concat(nombre,” “,apellido) as Nombre
from alumnos
where nombre like ‘M%’;

Noten que reemplazamos el igual por el like, esto lo dice al mysql que lo buscado no exacto sino algo cuyo Primera letra es M (puede ser en minuscula) y el porcentaje (%) pasa a ser como el comodin asterisco (*) para hacerle saber que todo valor que empiece con M debe mostrarlo, sin importar lo que dice detras. La salida es asi:

+——————————-+
| Nombre                      |
+——————————-+
| Marta Gargaglione  |
| Martin Miranda       |
+——————————-+

El comodin se puede utilizar adelante, atras o en ambas puntas, cada posicion tiene un significado, la primera como vimos es decirle que busque  todo lo que este adelante del porcentaje y coincida, en el caso de ambas puntas seria decirle que el valor contenga este dato y el ultimo dice que el valor termine con este dato, parece muy complicado pero no lo es y con esto nos permite crear, por ahora, pequeños motores de busqueda en las bases de datos. Veamos este ejemplo para que no sea tan confuso:

select
concat(nombre,” “,apellido) as Nombre
from alumnos
where nombre like ‘%rt%’;

+——————————-+
| Nombre                     |
+——————————-+
| Marta Gargaglione |
| Martin Miranda      |
| Roberto Lopez         |
+——————————-+

Busco y trajo todos los datos cuyo nombre contienen las letras “rt”, una clausula muy interesante es distinct esta nos permite poder generar sumarios de una tabla, por ejemplo si uds. quisieran un resumen de las provincias de origen de cada uno de los alumnos, la linea es asi:

select distinct pcia from alumnos;

Observen que trae los valores que se contienen en la columna pcia (provincia) pero no trae los que se repiten. Ya que si vemos la tabla sin filtrar tenemos tres alumnos en BA (Buenos Aires)  y como ven en el resultado solo trajo uno
+——+
| pcia |
+——+
| BA |
| SF |
| CB |
| SL |.
+——+

Otra clausula para poder utilizar es count() esta nos devuelve la cantidad de registros que hay en la tabla, la sintaxis es asi:

select count(*) from alumnos;

Si lo ejecutamos nos va a devolver un valor de seis, dado que son la cantidad de alumnos que tenemos registrados. Ahora veamos un ejemplo como podriamos obtener las provincias y cuantos presidentes han nacido en esa provincia:

select state, count(*) as contar
from president
group by state order by contar desc;

Aca vemos como primero buscamos la provincia (state), ejecutamos un contador que lo llamamos contar, luego le decimos que agrupe todos los estados (group by) para evitar que se repitan innecesariamente y esto se ordena por el contador de forma descendente, vean la salida:
+———+————+
| state | contar |
+———+————+
|   TC   |       2     |
|   BA  |       1     |
|    SJ   |       1     |
+———+————+

Ahora vamos a armar una lista de la que vamos a extraer el mes de nacimiento, el nombre del mes y la cantidad de presidentes que nacieron en esos meses, pongamos manos a la obra:

select month(birth) as mes,
monthname(birth) as nombre,
count(*) as contar
from president
group by nombre
order by mes desc;

Aca esta la salida del query:

+——–+—————-+———+
| mes |  nombre   |contar|
+——–+—————-+———+
|  10   | October    |     2    |
|   7    | July           |     1     |
|   6    | June          |     4     |
|   5    | May          |     1     |
|   3    | March      |     1     |
|   2    | February |     1     |
+——-+—————–+——–+

Observen que agrupamos los meses y los ordenamos por el mes de forma descendente y a su vez tenemos la cantidad de presidentes que nacieron ese mes. El count hasta ahora lo vimos siempre con el comodin (*) pero tambien se puede utilizar para contar alguna columna en particular, por ejemplo en la tabla president podriamos utilizar la siguiente linea:

select count(state),count(death) from president;

Aca esta la salida:

+——————–+———————+
| count(state) | count(death) |
+——————–+———————+
|        11           |           10          |
+——————–+———————+

Observen que hicimos contar la cantidad de provincias (state) en la tabla y la cantidad de fechas de fallecimiento, notaran que dieron distintos valores, esto es porque cargue un presidente que todavia no fallecio (por lo menos hasta la creacion de este post), entonces ese valor figura como NULL haciendo que el contador omita ese valor y siga contando. A parte de count, existen otras funciones complementarias para ayudar a obtener valores en un query, estas son MIN(), MAX(), SUM() y AVG(). La primera obtiene el valor minimo de esa columna, la segunda lo contrario es decir el valor maximo, la tercera hace una sumatoria de todos los valores almacenados en la columna, y la ultima el promedio de la columna. Pasemos a verlo con un ejemplo:

select
alumno_id as “Id del alumno”,
min(nota) as Minimo,
max(nota) as Maximo,
sum(nota) as Total,
avg(nota) as Promedio,
count(nota) as Contar
from notas
group by alumno_id;

Observen que primero seleccionamos el id del alumno, luego vamos a obtener el valor minimo de sus notas, maximo de sus notas, el total de todas las notas, el promedio de las notas y por ultimo cuenta la cantidad de notas ingresadas, y para dejarlo de una forma ordenada hacemos que se agrupen a traves del id del alumno. La salida es asi:

+———————–+————–+————–+———-+—————-+———–+
| Id del alumno | Minimo | Maximo | Total | Promedio | Contar|
+———————–+————–+————–+———-+—————-+———–+
|            1             |        5       |        9      |    39    |   6.5000     |      6     |
|            2             |        5       |        8      |    41    |   6.8333     |      6     |
|            3             |        4       |       10     |    38    |   6.3333     |      6     |
|            4             |        1       |       10     |    31    |   5.1667     |      6     |
|            5             |        2       |        9      |    32    |   5.3333     |      6     |
|            6             |        7       |       10     |    46    |   7.6667     |      6     |
+———————–+————–+————–+———-+—————-+———–+

Ahora vamos a hablar del valor NULL, en realidad es la posibilidad de dejar un campo en blanco sin ningun valor asignado y nos permite poder trabajar en algunos campos con mayor comodidad, por ejemplo en la tabla president, tenemos un campo llamado death, en este se almacena la fecha de fallecimiento del presidente, y obviamente todavia hay gente que no ha muerto. para este caso los dejamos en NULL, entonces nos da la posibilidad de poder evitar usarlos en el condicional, por ejemplo:

select * from president where death is null;

o sino pueden hacerlo de esta forma:

select * from president where death is not null;

Esta es la mejor forma de poder usar el NULL. con el resto de los operadores puede traernos algunos inconvenientes, para finalizar vamos a crear una supet mega consulta para conocer todos los datos de nuestros alumnos, para ver quien aprobo o no, pongamos manos a las obras:

select
concat(alumnos.nombre,” “,alumnos.apellido) as Nombre,
concat(alumnos.ciudad,”, “,alumnos.pcia) as Ciudad,
min(notas.nota) as “Nota mas baja”,
max(notas.nota) as “Nota mas alta”,
avg(notas.nota) as Promedio,
count(*) as Total
from notas,examenes,alumnos
where alumnos.id=notas.alumno_id
and examenes.id=notas.examen_id
group by alumnos.nombre
order by Promedio desc;

La salida es asi:

mysql11

En este caso tenemos el nombre y apellido de los estudiantes, de que ciudad y provincia son, cual es su nota mas baja, su nota mas alta, el promedio final y la cantidad total de examenes que hicieron. Esta es una consulta con todos lo que aprendimos de SELECT, que a su vez es de varias tablas y nos permite identificar mejor las notas. Hasta aca esta lo mas importante y casi todo explicado lo que respecta al select, espero que nos veamos en el proximo post.

Anuncios