Share
Es importante que cada desarrollador web esté familiarizado con las interacciones de la base de datos. En la segunda parte de la serie, continuaremos explorando el lenguaje SQL y aplicaremos lo que hemos aprendido en una base de datos MySQL. Aprenderemos sobre índices, tipos de datos y estructuras de consulta más complejas..
Consulte la sección "Lo que necesita" en el primer artículo aquí: SQL para principiantes (parte 1).
Si desea seguir los ejemplos de este artículo en su propio servidor de desarrollo, haga lo siguiente:
Los índices (o claves) se utilizan principalmente para mejorar la velocidad de las operaciones de recuperación de datos (por ejemplo, SELECT) en las tablas.
Son una parte tan importante de un buen diseño de base de datos, es difícil clasificarlos como "optimización". En la mayoría de los casos, se incluyen en el diseño inicial, pero también se pueden agregar más adelante con una consulta ALTER TABLE.
Las razones más comunes para indexar columnas de bases de datos son:
Casi todas las tablas deben tener una CLAVE PRIMARIA, en la mayoría de los casos como una INT con la opción AUTO_INCREMET.
Si recuerdas el primer artículo, creamos un campo 'user_id' en la tabla de usuarios y fue una CLAVE PRIMARIA. De esta manera, en una aplicación web podemos referirnos a todos los usuarios por sus números de identificación.
Los valores almacenados en una columna de CLAVE PRIMARIA deben ser únicos. Además, no puede haber más de una CLAVE PRIMARIA en cada tabla.
Veamos una consulta de muestra, creando una tabla para la lista de estados de Estados Unidos:
Estados de CREATE TABLE (ID INT AUTO_INCREMENT PRIMARY KEY, nombre VARCHAR (20));
También se puede escribir así:
Estados de CREATE TABLE (id INT AUTO_INCREMENT, nombre VARCHAR (20), PRIMARY KEY (id));
Como esperamos que el nombre del estado sea un valor único, deberíamos cambiar un poco el ejemplo de la consulta anterior:
Estados de CREATE TABLE (id INT AUTO_INCREMENT, nombre VARCHAR (20), CLAVE PRIMARIA (id), UNIQUE (nombre));
Por defecto, el índice será nombrado después del nombre de la columna. Si lo desea, puede asignarle un nombre diferente:
Estados de CREATE TABLE (id INT AUTO_INCREMENT, nombre VARCHAR (20), CLAVE PRIMARIA (id), UNIQUE state_name (nombre));
Ahora el índice se llama 'nombre_estado' en lugar de 'nombre'.
Digamos que queremos agregar una columna para representar el año en que se unió cada estado.
Estados de CREATE TABLE (id INT AUTO_INCREMENT, nombre VARCHAR (20), join_year INT, PRIMARY KEY (id), UNIQUE (name), INDEX (join_year));
Acabo de agregar la columna join_year y la indexé. Este tipo de índice no tiene la restricción de unicidad..
También puedes llamarlo KEY en lugar de INDEX..
Estados de CREATE TABLE (id INT AUTO_INCREMENT, nombre VARCHAR (20), join_year INT, PRIMARY KEY (id), UNIQUE (name), KEY (join_year));
Agregar un índice reduce el rendimiento de las consultas INSERTAR y ACTUALIZAR. Debido 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 un trabajo adicional. Las mejoras de rendimiento en las consultas de SELECT generalmente superan esto con mucho. Pero aún así, no solo agregue índices en cada columna de la tabla sin pensar en las consultas que ejecutará.
Antes de continuar con más consultas, me gustaría crear una tabla de muestra con algunos datos.
Esta será una lista de los estados de EE. UU., Con sus fechas de ingreso (la fecha en que el estado ratificó la Constitución de los Estados Unidos o fue admitida en la Unión) y sus poblaciones actuales. Puedes copiar y pegar lo siguiente en tu consola MySQL:
Estados de CREATE TABLE (id INT AUTO_INCREMENT, nombre VARCHAR (20), join_year INT, población INT, PRIMARY KEY (id), UNIQUE (nombre), KEY (join_year)); INSERTE EN LOS VALORES DE LOS ESTADOS (1, 'Alabama', 1819, 4661900), (2, 'Alaska', 1959, 686293), (3, 'Arizona', 1912, 6500180), (4, 'Arkansas', 1836, 2855390 ), (5, 'California', 1850, 36756666), (6, 'Colorado', 1876, 4939456), (7, 'Connecticut', 1788, 3501252), (8, 'Delaware', 1787, 873092), (9, 'Florida', 1845, 18328340), (10, 'Georgia', 1788, 9685744), (11, 'Hawaii', 1959, 1288198), (12, 'Idaho', 1890, 1523816), (13 , 'Illinois', 1818, 12901563), (14, 'Indiana', 1816, 6376792), (15, 'Iowa', 1846, 3002555), (16, 'Kansas', 1861, 2802134), (17, ' Kentucky ', 1792, 4269245), (18,' Louisiana ', 1812, 4410796), (19,' Maine ', 1820, 1316456), (20,' Maryland ', 1788, 5633597), (21,' Massachusetts ' , 1788, 6497967), (22, 'Michigan', 1837, 10003422), (23, 'Minnesota', 1858, 5220393), (24, 'Mississippi', 1817, 2938618), (25, 'Missouri', 1821 , 5911605), (26, 'Montana', 1889, 967440), (27, 'Nebraska', 1867, 1783432), (28, 'Nevada', 1864, 2600167), (29, 'New Hampshire', 1788, 1315809), (30, 'New Jersey', 1787, 8682 661), (31, 'New Mexico', 1912, 1984356), (32, 'New York', 1788, 19490297), (33, 'North Carolina', 1789, 9222414), (34, 'North Dakota', 1889, 641481), (35, 'Ohio', 1803, 11485910), (36, 'Oklahoma', 1907, 3642361), (37, 'Oregon', 1859, 3790060), (38, 'Pennsylvania', 1787, 12448279), (39, 'Rhode Island', 1790, 1050788), (40, 'South Carolina', 1788, 4479800), (41, 'South Dakota', 1889, 804194), (42, 'Tennessee', 1796 , 6214888), (43, 'Texas', 1845, 24326974), (44, 'Utah', 1896, 2736424), (45, 'Vermont', 1791, 621270), (46, 'Virginia', 1788, 7769089 ), (47, 'Washington', 1889, 6549224), (48, 'West Virginia', 1863, 1814468), (49, 'Wisconsin', 1848, 5627967), (50, 'Wyoming', 1890, 532668) ;
La cláusula GROUP BY agrupa las filas de datos resultantes en grupos. Aquí hay 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 se agruparon por la columna 'join_year'. En otras palabras, solo vemos una fila para cada valor distinto de join_year. Dado que algunos estados tienen el mismo join_year, obtuvimos menos de 50 resultados.
Por ejemplo, solo hubo una fila para el año 1787, pero hay 3 estados en ese grupo:
Así que hay tres estados aquí, pero solo el nombre de Delaware apareció después de la consulta GROUP BY anterior. En realidad, podría haber sido cualquiera de los tres estados y no podemos confiar en este dato. Entonces, ¿qué sentido tiene utilizar la cláusula GROUP BY??
Sería en su mayoría inútil sin utilizar una función agregada como COUNT (). Veamos qué hacen algunas de estas funciones y cómo pueden obtener algunos datos útiles..
Esta es quizás la función más utilizada junto con las consultas de GROUP BY. Devuelve el número de filas en cada grupo..
Por ejemplo, podemos usarlo para ver el número de estados de cada join_year:
Si utiliza una función agregada GROUP BY y no especifica una cláusula GROUP BY, los resultados completos se colocarán en un solo grupo.
Número de todas las filas en la tabla:
Número de filas que satisfacen una cláusula WHERE:
Estas funciones devuelven los valores mínimo, máximo y promedio:
Esta función concatena todos los valores dentro del grupo en una sola cadena, con un separador dado.
En el primer ejemplo de consulta GROUP BY, solo pudimos ver un nombre de estado por año. Puede usar esta función para ver todos los nombres en cada grupo:
Si la imagen redimensionada es difícil de leer, esta es la consulta:
SELECT GROUP_CONCAT (nombre SEPARATOR ','), join_year FROM states GROUP BY join_year;
Puedes usar esto para sumar los valores numéricos..
Similar a otros lenguajes de programación, SQL tiene algún soporte para el flujo de control.
Esta es una función que toma 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.
Aquí hay un ejemplo más práctico donde lo usamos con la función SUM ():
SELECCIONE SUMA (IF (población> 5000000, 1, 0)) AS big_states, SUM (IF (población <= 5000000, 1, 0) ) AS small_states FROM states;
La primera llamada SUM () cuenta el número de estados grandes (más de 5 millones de habitantes) y la segunda cuenta el número de estados pequeños. La llamada IF () dentro de estas llamadas SUM () devuelve 1 o 0 según la condición.
Aquí está el resultado:
Esto funciona de manera similar a las declaraciones de casos de conmutación con las que podría estar familiarizado en la programación.
Digamos que queremos categorizar cada estado en una de tres categorías posibles.
SELECCIONE CUENTA (*), CASO CUANDO población> 5000000 ENTONCES 'grande' CUANDO población> 1000000 ENTONCES 'mediano' ELSE 'pequeño' FINALIZA COMO estado_ tamaño DE ESTADOS GRUPO POR estado_size;
Como puede ver, en realidad podemos agrupar por el valor devuelto por la declaración CASE. Esto es lo que pasa:
La cláusula HAVING nos permite aplicar condiciones a los campos 'ocultos', como los resultados devueltos de las funciones agregadas. Por eso se suele utilizar junto con GROUP BY..
Por ejemplo, veamos la consulta que usamos para contar el número de estados por año de unión:
SELECT COUNT (*), join_year FROM states GROUP BY join_year;
El resultado fue de 34 filas..
Sin embargo, digamos que solo nos interesan las filas que tienen un conteo mayor que 1. No podemos usar la cláusula WHERE para esto:
Aquí es donde HAVING se vuelve útil:
Tenga en cuenta que esta función puede no estar disponible en todos los sistemas de bases de datos..
Es posible obtener los resultados de una consulta y usarla para otra consulta..
En este ejemplo, obtendremos el estado con la población más alta:
SELECCIONAR * DE los estados DONDE la población = (SELECCIONAR MAX (población) DE los estados);
La consulta interna devolverá la mayor población de todos los estados. Y la consulta externa buscará la tabla nuevamente usando ese valor.
Podría estar pensando que este fue un mal ejemplo, y de alguna manera estoy de acuerdo. La misma consulta podría escribirse más eficientemente como esto:
SELECCIONAR * DE LOS ESTADOS ORDENADOS POR LA población DESC LIMIT 1
Los resultados en este caso son los mismos, sin embargo, existe una diferencia importante entre estos dos tipos de consultas. Quizás otro ejemplo lo demuestre mejor..
En este ejemplo, obtendremos los últimos estados que se unieron a la Unión:
SELECT * FROM states WHERE join_year = (SELECT MAX (join_year) FROM states);
Hay dos filas en los resultados esta vez. Si hubiésemos utilizado el tipo de consulta ORDER BY ... LIMIT 1 aquí, no habríamos recibido el mismo resultado.
A veces es posible que desee utilizar varios resultados devueltos por la consulta interna.
La siguiente consulta encuentra los años, cuando varios estados se unieron a la Unión y devuelve la lista de esos estados:
SELECT * FROM states WHERE join_year IN (SELECT join_year FROM states GROUP BY join_year HAY CUENTA (*)> 1) ORDEN POR join_year;
Las subconsultas pueden llegar a ser bastante complejas, por lo que no profundizaré mucho en ellas en este artículo. Si desea leer más sobre ellos, consulte el manual de MySQL.
También vale la pena señalar que las subconsultas a veces pueden tener un mal rendimiento, por lo que deben usarse con precaución.
Con una consulta UNION, podemos combinar los resultados de múltiples consultas SELECT.
Este ejemplo combina estados que comienzan con la letra 'N' y estados con grandes poblaciones:
(SELECCIONAR * DE LOS ESTADOS DONDE NOMBRARSE COMO "n%") UNIÓN (SELECCIONAR * DE ESTADOS DONDE la población> 10000000);
Tenga en cuenta que Nueva York es grande y su nombre comienza con la letra 'N'. Pero aparece solo una vez porque las filas duplicadas se eliminan de los resultados automáticamente.
Otra cosa buena de UNION es que puedes combinar consultas en diferentes tablas.
Supongamos que tenemos mesas para empleados, gerentes y clientes. Y cada tabla tiene un campo de correo electrónico. Si queremos recuperar todos los correos electrónicos con una sola consulta, podemos ejecutar esto:
(SELECCIONE el correo electrónico DE los empleados) UNION (SELECCIONE el correo electrónico DE los gerentes) UNION (SELECCIONE el correo electrónico DE los clientes DONDE suscritos = 1);
Buscaría todos los correos electrónicos de todos los empleados y gerentes, pero solo los correos electrónicos de los clientes que se hayan suscrito para recibir correos electrónicos..
Ya hemos hablado sobre la consulta INSERT en el último artículo. Ahora que exploramos los índices de las bases de datos hoy, podemos hablar sobre características más avanzadas de la consulta INSERT.
Esto es casi como una declaración condicional. La consulta primero intenta realizar un INSERT dado, y si falla debido a un valor duplicado para una CLAVE PRIMARIA o CLAVE ÚNICA, realiza una ACTUALIZACIÓN en su lugar.
Vamos a crear una tabla de prueba primero.
Es una mesa para guardar productos. La columna 'stock' es el número de productos que tenemos en stock.
Ahora intenta insertar un valor duplicado y ver qué pasa.
Recibimos un error como se esperaba.
Digamos que recibimos un nuevo fabricante de pan y queremos actualizar la base de datos, y no sabemos si ya existe un registro para ello. Podríamos revisar los registros existentes y luego hacer otra consulta basada en eso. O simplemente podríamos hacerlo todo en una simple consulta:
Esto funciona exactamente igual que INSERTAR con una excepción importante. Si se encuentra una fila duplicada, primero la elimina y luego ejecuta INSERT, por lo que no aparece ningún mensaje de error..
Tenga en cuenta que dado que esta es en realidad una fila completamente nueva, el ID se incrementó.
Esta es una forma de suprimir los errores duplicados, generalmente para evitar que la aplicación se rompa. A veces es posible que desee intentar insertar una nueva fila y dejar que falle sin quejas en caso de que se encuentre un duplicado.
No se devolvieron errores y no se actualizaron filas.
Cada columna de la tabla debe tener un tipo de datos. Hasta ahora hemos utilizado los tipos INT, VARCHAR y DATE, pero no hemos hablado de ellos en detalle. También hay varios otros tipos de datos que debemos explorar.
Primero, comencemos con los tipos de datos numéricos. Me gusta ponerlos en dos grupos separados: enteros y no enteros.
Una columna entera puede contener solo números naturales (sin decimales). Por defecto pueden ser números negativos o positivos. Pero si la opción UNSIGNED está establecida, solo puede contener números positivos.
MySQL admite 5 tipos de enteros, con varios tamaños y rangos:
Estos tipos de datos pueden contener números decimales: FLOAT, DOUBLE y DECIMAL.
FLOTADOR es de 4 bytes, DOBLE es de 8 bytes y funcionan de manera similar. Sin embargo DOBLE tiene mejor precisión..
DECIMAL (M, N) tiene un tamaño variable según el nivel de precisión, que se puede personalizar. 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.
Como su nombre lo indica, podemos almacenar cadenas en estas columnas de tipo de datos.
CHAR (N) puede contener hasta N caracteres y tiene un tamaño fijo. Por ejemplo, CHAR (50) siempre tomará 50 caracteres de espacio, por fila, independientemente del tamaño de la cadena. El máximo absoluto es de 255 caracteres.
VARCHAR (N) funciona igual, pero el tamaño de almacenamiento no es fijo. N solo se utiliza para el tamaño máximo. Si se almacena una cadena más corta que N caracteres, ocupará mucho menos espacio en el disco duro. El tamaño máximo absoluto es de 65535 caracteres..
Las variaciones del tipo de datos TEXT son más adecuadas para cadenas largas. TEXTO tiene un límite de 65535 caracteres, MEDIUMTEXT 16.7 millones de caracteres y LONGTEXT 4.3 mil millones de caracteres. Por lo general, MySQL los almacena en ubicaciones separadas en el servidor, de modo que el almacenamiento principal de la tabla sigue siendo relativamente pequeño y rápido.
DATE almacena las fechas y las muestra en este formato 'YYYY-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 'YYYY-MM-DD HH: MM: SS'. Tiene un rango de '1000-01-01 00:00:00' a '9999-12-31 23:59:59'. Lleva 8 bytes de espacio..
TIMESTAMP funciona como DATETIME con algunas excepciones. Toma solo 4 bytes de espacio y el rango es '1970-01-01 00:00:01' UTC a '2038-01-19 03:14:07' UTC. Entonces, por ejemplo, puede no ser bueno para almacenar fechas de nacimiento..
TIEMPO solo almacena el tiempo y AÑO solo almacena el año.
Hay algunos otros tipos de datos soportados por MySQL. Puedes ver una lista de ellos aquí. También debe consultar los tamaños de almacenamiento de cada tipo de datos aquí.
Gracias por leer el artículo. SQL es un lenguaje importante y una herramienta en el arsenal de desarrolladores web..
Por favor, deje sus comentarios y preguntas, y tenga un gran día!
¿Listo para llevar sus habilidades al siguiente nivel y comenzar a beneficiarse de sus scripts y componentes? Echa un vistazo a nuestro mercado hermano, CodeCanyon.
Accentsconagua