Unlimited Plugins, WordPress themes, videos & courses! Unlimited asset downloads! From $16.50/m
Advertisement
  1. Code
  2. Databases
Code

SQL para principiantes parte 2

by
Length:LongLanguages:

Spanish (Español) translation by Ana Paulina Figueroa Vazquez (you can also view the original English article)

Es importante que todos los desarrolladores web estén familiarizados con las interacciones que pueden llevar a cabo con la base de datos. En la segunda parte de la serie, continuaremos explorando el lenguaje SQL y aplicando lo que hemos aprendido en una base de datos MySQL. Aprenderemos sobre índices, tipos de datos y estructuras de consulta más complejas.

Lo que necesitas

Por favor consulta la sección "Lo que necesitas" aquí, en el primer artículo: SQL para principiantes (parte 1).

Si deseas seguir los ejemplos de este artículo en tu propio servidor de desarollo, haz lo siguiente:

  1. Abre la consola de MySQL e inicia sesión.
  2. Si aún no lo has hecho, crea una base de datos llamada "my_first_db" con una consulta CREATE.
  3. Cambia a la base de datos con la instrucción USE.

Índices de bases de datos

Los índices (o claves) se usan principalmente para mejorar la velocidad de las operaciones de recuperación de datos (por ejemplo SELECT) en las tablas.

Son una parte tan importante del buen diseño de una base de datos, que es difícil clasificarlos como una "optimización". En la mayoría de los casos se incluyen en el diseño inicial, pero también pueden ser añadidos posteriormente con una consulta ALTER TABLE.

Las razones más comunes para indexar las columnas de la base de datos son:

  • Casi todas las tablas deben tener un índice PRIMARY KEY, generalmente como una columna "id".
  • Si se espera que una columna contenga valores únicos, ésta debe tener un índice UNIQUE.
  • Si vas a llevar a cabo búsquedas en una columna a menudo (en la cláusula WHERE), debe tener un INDEX regular.
  • Si una columna es usada para establecer una relación con otra tabla, debe ser una FOREIGN KEY de ser posible, o de lo contrario tener un simple índice regular.

PRIMARY KEY

Casi todas las tablas deben tener una PRIMARY KEY, en la mayoría de los casos como un INT con la opción AUTO_INCREMENT.

Si recuerdas el primer artículo, creamos un campo 'user_id' en la tabla users y fue una PRIMARY KEY. De esta manera, en una aplicación web podemos hacer referencia a todos los usuarios por sus números de id.

Los valores almacenados en una columna PRIMARY KEY deben ser únicos. Además de eso, no puede haber más de una PRIMARY KEY en cada tabla.

Veamos una consulta de ejemplo, creando una tabla con una lista de los estados de EE. UU.:

También puede ser escrita de esta forma:

UNIQUE

Ya que esperamos que el nombre del estado sea un valor único, debemos cambiar un poco el ejemplo de la consulta anterior:

De forma predeterminada, el índice recibirá su nombre de acuerdo al nombre de la columna. Si lo deseas, puedes asignarle un nombre diferente:

Ahora el índice se llama 'state_name' en vez de 'name'.

INDEX

Digamos que queremos añadir una columna para representar el año en el que se unió cada estado.

Acabo de añadir la columna 'join_year' y la he indexado. Este tipo de índice no tiene la restricción de tener que ser único.

También puedes llamarlo KEY en vez de INDEX.

Más información sobre desempeño

Añadir un índice reduce el desempeño de las consultas INSERT y UPDATE. Esto se debe a que cada vez que se agregan nuevos datos a la tabla, los datos del índice también se actualizan automáticamente, lo que requiere trabajo adicional. Las mejoras en el desempeño de las consultas SELECT usualmente superan esta desventaja por mucho. Pero aún así, no agregues índices en cada columna de las tablas sin pensar en las consultas que ejecutarás.

Tabla de ejemplo

Antes de que continuemos con más consultas, me gustaría crear una tabla de ejemplo con un poco de información.

Esta será una lista de los estados de EE. UU., con las fechas en las que se unieron (la fecha en la que el estado ratificó la Constitución de los Estados Unidos o cuando fue admitido en la Unión) y sus poblaciones actuales. Puedes copiar y pegar lo siguiente en tu consola de MySQL:

GROUP BY: Agrupando datos

La cláusula GROUP BY reúne las filas de datos resultantes en grupos. Este es un ejemplo:

Entonces ¿qué acaba de pasar? tenemos 50 filas en la tabla, pero esta consulta devolvió 34 resultados. Esto se debe a que los resultados fueron agrupados por la columna 'join_year'. En otras palabras, solamente vemos una fila por cada valor distinto de 'join_year'. Dado que algunos estados tienen el mismo valor en 'join_year', obtuvimos menos de 50 resultados.

Por ejemplo, solamente hubo una fila para el año 1787, pero hay 3 estados en ese grupo:

Así que hay tres estados aquí, pero solamente el nombre de Delaware apareció después de la anterior consulta GROUP BY. De hecho pudo haber sido cualquiera de los tres estados y no podemos depender de este fragmento de información. Entonces ¿cuál es el objetivo de usar la cláusula GROUP BY?.

Sería casi inútil sin el uso de una función de agregado como COUNT(). Veamos qué hacen algunas de estas funciones y cómo pueden proporcionarnos algunos datos útiles.

COUNT(*): Conteo de filas

Esta es tal vez la función más usada junto con las consultas GROUP BY. Devuelve el número de filas de cada grupo.

Por ejemplo, podemos usarla para ver el número de estados por cada 'join_year':

Agrupando todo

Si usas una función de agregado GROUP BY y no especificas una cláusula GROUP BY, todos los resultados serán colocados en un único grupo.

Número total de filas en la tabla:

Número de filas que satisfacen a una cláusula WHERE:

MIN(), MAX() y AVG()

Estas funciones devuelven los valores mínimo, máximo y promedio:

GROUP_CONCAT()

Esta función concatena todos los valores del grupo en una cadena única, con un separador dado.

En la primera consulta GROUP BY de ejemplo, solamente pudimos ver un nombre de estado por año. Puedes usar esta función para ver todos los nombres de cada grupo:

Si la imagen redimensionada es difícil de leer, aquí está la consulta:

SUM()

Puedes usar esta función para sumar los valores numéricos.

IF() y CASE: Control de flujo

De manera similar a otros lenguajes de programación, SQL tiene algo de soporte para el control de flujo.

IF()

Esta es una función que recibe tres argumentos. El primer argumento es la condición, el segundo argumento se usa si la condición es verdadera y el tercer argumento se usa si la condición es falsa.

Este es un ejemplo más práctico en el que lo usamos con la función SUM():

La primera llamada a SUM() cuenta el número de estados grandes (con una población de más de 5 millones) y la segunda cuenta el número de estados pequeños. La llamada IF() dentro de estas llamadas SUM() devuelve ya sea 1 o 0, dependiendo de la condición.

Este es el resultado:

CASE

Esta instrucción funciona de manera similar a las instrucciones switch-case, con las que debes estar familiarizado al programar.

Digamos que queremos categorizar cada estado en una de tres categorías posibles.

Como puedes ver, podemos usar GROUP BY para agrupar por el valor devuelto por la instrucción CASE. Esto es lo que ocurre:

HAVING: Condiciones en campos ocultos

La cláusula HAVING nos permite aplicar condiciones a campos 'ocultos', tales como los resultados devueltos por las funciones de agregado. Por esta razón generalmente se usa junto a GROUP BY.

Por ejemplo, veamos la consulta que usamos para contar el número de estados por año de unión:

El resultado fue de 34 filas.

Sin embargo, digamos que solamente estamos interesados en filas que tienen un conteo de más de 1. No podemos usar la cláusula WHERE para esto:

Aquí es en donde HAVING se vuelve útil:

Ten en mente que esta herramienta quizá no esté disponible en todos los sistemas de bases de datos.

Subconsultas

Es posible obtener los resultados de una consulta y usarlos en otra consulta.

En este ejemplo obtendremos el estado con la mayor población:

La consulta interna devolverá la mayor población de entre todos los estados. Y la consulta externa buscará en la tabla de nuevo usando ese valor.

Es posible que pienses que éste fue un mal ejemplo, y en cierta manera estoy de acuerdo. La misma consulta podría ser escrita de manera más eficiente así:

Los resultados en este caso son los mismos, pero hay una diferencia importante entre estos dos tipos de consultas. Quizá otro ejemplo demuestre esto de manera más clara.

En este ejemplo obtendremos los últimos estados que se hayan integrado a la Unión:

Esta vez hay dos filas en los resultados. Si hubiéramos usado el tipo de consulta ORDER BY ... LIMIT 1 aquí, no habríamos recibido el mismo resultado.

IN()

Algunas veces es posible que quieras usar múltiples resultados devueltos por la consulta interior.

La siguiente consulta encuentra los años en los que múltiples estados se integraron a la Unión, y devuelve la lista de esos estados:

Más información sobre subconsultas

Las subconsultas pueden volverse bastante complejas, por eso no entraré en más detalles en este artículo. Si quieres leer más acerca de ellas consulta el manual de MySQL.

Además vale la pena mencionar que las subconsultas algunas veces pueden tener un mal desempeño, por lo que deben ser usadas con precaución.

UNION: Combinando datos

Con una consulta UNION podemos combinar los resultados de múltiples consultas SELECT.

Este ejemplo combina los estados que comienzan con la letra 'N' y los estados con poblaciones grandes.

Nota que Nueva York es grande y su nombre comienza con la letra 'N' también. Pero aparece solamente una vez, debido a que las filas duplicadas son eliminadas de los resultados automáticamente.

Otro aspecto positivo de UNION es que puedes combinar consultas en tablas diferentes.

Supongamos que tenemos tablas para empleados (employees), gerentes (managers) y clientes (customers). Y que cada tabla tiene un campo de correo electrónico. Si queremos obtener todos los correos con una sola consulta, podemos ejecutar esta instrucción:

Esto obtendría todos los correos de todos los empleados y gerentes, pero solamente los correos de clientes que se hayan suscrito para recibir correos electrónicos.

Continuación de INSERT

Ya hemos hablado de la consulta INSERT en el artículo anterior. Ahora que hemos explorado los índices de las bases de datos, podemos hablar más sobre las características avanzadas de la consulta INSERT.

INSERT ... ON DUPLICATE KEY UPDATE

Esta es casi como una instrucción condicional. La consulta primero intenta llevar a cabo un INSERT determinado, y si falla debido a un valor duplicado de una PRIMARY KEY o UNIQUE KEY, entonces en vez de eso lleva a cabo un UPDATE.

Vamos a crear una tabla de pruebas primero.

Es una tabla que contiene productos. La columna 'stock' es el número de productos que tenemos en existencia.

Ahora intenta insertar un valor duplicado y observa lo que ocurre.

Obtuvimos un error como era de esperarse.

Digamos que recibimos una nueva máquina para hacer pan y queremos actualizar la base de datos, y que no sabemos si ya hay un registro para ella. Podemos revisar si hay registros existentes y luego hacer otra consulta en base a eso. O podemos simplemente hacer todo esto en una única consulta:

REPLACE INTO

Esta instrucción funciona exactamente como INSERT, con una excepción importante. Si encuentra una fila duplicada, ésta la elimina primero y luego lleva a cabo el INSERT, por lo que no recibimos mensajes de error.

Toma en cuenta que, dado que ésta es una fila completamente nueva, el id fue incrementado.

INSERT IGNORE

Esta es una manera de eliminar los errores de duplicidad, usualmente para evitar que la aplicación falle. Algunas veces quizá quieras intentar insertar una nueva fila y simplemente dejar que falle sin quejas en caso de que se encuentre un duplicado.

No se devolvieron errores ni tampoco se actualizó ninguna fila.

Tipos de datos

Cada columna de una tabla necesita tener un tipo de dato. Hasta ahora hemos usado los tipos INT, VARCHAR y DATE, pero no hablamos de ellos a detalle. Además hay muchos otros tipos de datos que debemos explorar.

Primero vamos a comenzar con los tipos de datos numéricos. Me gusta clasificarlos en dos grupos separados: Enteros vs. No enteros.

Tipos de datos enteros

Una columna de tipo entero puede almacenar solamente números naturales (sin decimales). De forma predeterminada pueden ser números positivos o negativos. Pero si la opción UNSIGNED está habilitada solamente puede almacenar números positivos.

MySQL es compatible con 5 tipos de enteros, con diferentes tamaños y rangos:

Tipos de datos numéricos no enteros

Estos tipos de datos pueden almacenar números decimales: FLOAT, DOUBLE y DECIMAL.

FLOAT es de 4 bytes, DOUBLE es de 8 bytes y trabajan de manera similar. Sin embargo DOUBLE tiene una mejor precisión.

DECIMAL(M, N) tiene un tamaño variable basado en el nivel de precisión, que puede ser personalizado. M es el número máximo de dígitos, y N es el número de dígitos a la derecha del punto decimal.

Por ejemplo, DECIMAL(13, 4) tiene un máximo de 9 dígitos enteros y 4 dígitos fraccionarios.

Tipos de datos de cadena

Como el nombre lo sugiere, podemos almacenar cadenas en las columnas que tengan estos tipos de datos.

CHAR(N) puede almacenar hasta N caracteres, y tiene un tamaño fijo. Por ejemplo, CHAR(50) siempre ocupará 50 caracteres de espacio, por fila, sin importar el tamaño de la cadena que contenga. El máximo absoluto es de 255 caracteres.

VARCHAR(N) trabaja igual, pero el tamaño del almacenamiento no es fijo. N solamente se usa para el tamaño máximo. Si una cadena de menos de N caracteres es almacenada, ocupará mucho menos espacio en el disco duro. El tamaño máximo absoluto es de 65535 caracteres.

Las variaciones del tipo de dato TEXT son más adecuadas para cadenas largas. TEXT tiene un límite de 65535 caracteres, MEDIUMTEXT 16.7 millones de caracteres y LONGTEXT 4.3 mil millones de caracteres. MySQL usualmente los almacena en ubicaciones separadas del servidor, de manera que el almacenamiento principal para la tabla siga siendo relativamente pequeño y rápido.

Tipos de fecha

DATE almacena fechas y las muestra en este formato 'AAAA-MM-DD' pero no contiene la información de la hora. Tiene un rango de 1001-01-01 a 9999-12-31.

DATETIME contiene tanto la fecha como la hora, y se muestra en este formato 'AAAA-MM-DD HH:MM:SS'. Tiene un rango de '1000-01-01 00:00:00' a '9999-12-31 23:59:59'. Ocupa 8 bytes de espacio.

TIMESTAMP trabaja como DATETIME con algunas excepciones. Solamente ocupa 4 bytes de espacio y el rago es de '1970-01-01 00:00:01' UTC a '2038-01-19 03:14:07' UTC. Así que, por ejemplo, es posible que no sea bueno para almacenar fechas de nacimiento.

TIME solamente almacena la hora y YEAR solamente almacena el año.

Otros

Existen otros tipos de datos compatibles con MySQL. Puedes ver una lista de ellos aquí. También deberías consultar los tamaño de almacenamiento de cada tipo de dato aquí.

Conclusión

Gracias por leer el artículo. SQL es un lenguaje importante y una herramienta en el arsenal de los desarrolladores web.

Por favor escribe tus comentarios y preguntas ¡y que tengas un gran día!.

  • Síguenos en Twitter, o suscríbete al canal RSS Nettuts+ para obtener los mejores tutoriales sobre desarrollo web en Internet.

¿Estás listo para llevar tus habilidades al siguiente nivel y comenzar a beneficierte de tus componentes y secuencias de comandos? echa un vistazo a nuestro mercado hermano, CodeCanyon.

CodeCanyon

Advertisement
Advertisement
Advertisement
Advertisement
Looking for something to help kick start your next project?
Envato Market has a range of items for sale to help get you started.