SQL para principiantes

La mayoría de las aplicaciones web modernas interactúan con bases de datos, generalmente con un lenguaje llamado SQL. Por suerte para nosotros, este idioma es bastante fácil de aprender. En este artículo, comenzaremos con algunas consultas SQL básicas y las usaremos para interactuar con una base de datos MySQL.

Que necesitas

SQL (lenguaje de consulta estructurado) es un lenguaje diseñado para interactuar con sistemas de administración de bases de datos relacionales (RDBMS), como MySQL, Oracle o SQLite. Para realizar las consultas SQL de este artículo, le sugiero que tenga instalado MySQL. También recomiendo phpMyAdmin como interfaz visual para MySQL.

Las siguientes aplicaciones facilitan la instalación de MySQL y phpMyAdmin en su máquina local:

Vamos a utilizar la línea de comandos para las consultas. WAMP ya viene con una consola MySQL.

Crear una base de datos

Aquí viene nuestra primera consulta. Vamos a crear una base de datos para trabajar.

En primer lugar, abra su consola MySQL e inicie sesión. Para WAMP, la contraseña predeterminada está en blanco. Para MAMP, la contraseña debe ser root por defecto.

Después de iniciar sesión, escriba esta consulta y presione enter:

Tenga en cuenta que el punto y coma (;) se agrega al final de la consulta, al igual que al final de las líneas de código en JavaScript.

Además, las palabras especiales CREATE DATABASE no distinguen entre mayúsculas y minúsculas, junto con todas las palabras especiales en SQL. Pero por el bien de la legibilidad y la convención, los escribiremos en mayúsculas.

Listar todas las bases de datos

La siguiente consulta se utiliza para obtener una lista de todas las bases de datos que tiene.

Mostrar bases de datos

Eliminar una base de datos

Puede eliminar una base de datos existente con la siguiente consulta.

Soltar una base de datos
Tenga cuidado con esta consulta, porque no le da ninguna advertencia. Si tiene tablas y datos en la base de datos, todos se eliminarán instantáneamente.

Siempre es una buena práctica hacer una copia de seguridad de su base de datos antes de eliminarla. Más adelante, veremos cómo puede realizar una copia de seguridad completa de su base de datos.

Selección de una base de datos

Puedes usar el USE palabra clave para seleccionar una base de datos con la que desea operar. Es una declaración y no requiere un punto y coma al final.

Seleccione una base de datos

Le dice a MySQL que seleccione una base de datos predeterminada para trabajar, para la sesión actual. Ahora estamos listos para crear tablas y hacer otras cosas en esta base de datos.

¿Qué es una tabla de base de datos?

Puede pensar en una tabla de base de datos como una hoja de cálculo o un archivo CSV que contiene datos estructurados, como se muestra en la siguiente captura de pantalla.

Estructura de la tabla de base de datos

Al igual que en este ejemplo, las tablas tienen nombres de columnas y filas de datos. Con consultas SQL podemos crear estas tablas. También podemos añadir, leer, actualizar y borrar los datos.

Crear una tabla

Con esta consulta podemos crear tablas en la base de datos. Desafortunadamente, el Documentación de mysql no es muy amigable para los nuevos estudiantes. La estructura de este tipo de consultas puede volverse muy compleja, pero comenzaremos con una sencilla.

La siguiente consulta creará una tabla con dos columnas.

Tenga en cuenta que podemos escribir una consulta en varias líneas e incluso usar tabulaciones para la sangría.

La primera línea es fácil. Simplemente creamos una tabla llamada users. A continuación, entre paréntesis, tenemos una lista de columnas de la tabla separadas por comas. Después de cada nombre de columna, tenemos un tipo de datos, como VARCHAR o DATE.

los VARCHAR(20) tipo de datos significa que la columna es un tipo de cadena y puede tener un máximo de veinte caracteres. los DATE es también un tipo de datos que se usa específicamente para almacenar fechas, en este formato: YYYY-MM-DD.

Claves primarias

Idealmente, también deberíamos incluir una columna de user_id, que será una clave principal. Sin entrar demasiado en los detalles, puede pensar en una clave principal como una forma de identificar cada fila en una tabla. Es un número único para identificar cualquier fila y no se puede duplicar para varias filas.

Ahora la consulta debería verse así:

los INT el tipo de datos lo convierte en un tipo entero de 32 bits (es decir, numérico). los AUTO_INCREMENT La palabra clave genera automáticamente un nuevo número de identificación cada vez que agregamos nuevas filas de datos. No es obligatorio, pero lo hace mucho más conveniente.

Esta columna no tiene que ser un número entero, pero es el tipo más utilizado. Tampoco es necesario tener una columna PRIMARY KEY, pero se recomienda encarecidamente para un buen diseño y rendimiento de la base de datos.

Ejecutemos la consulta:

Cómo ejecutar una consulta

Listar todas las tablas

La siguiente consulta le permite obtener una lista de las tablas que se encuentran actualmente en la base de datos.

Listar todas las tablas

Mostrar estructura de tabla

Para ver la estructura de una tabla existente, puede utilizar la siguiente consulta.

Mostrar estructura de tabla

Los campos (también conocidos como columnas) se enumeran en los resultados, con sus propiedades.

Eliminar una tabla

Al igual que el DROP DATABASES comando, la siguiente consulta elimina una tabla y su contenido, sin una advertencia.

Suelta una mesa

Una vez más, tenga mucho cuidado con cualquier DROP ¡operación!

Modificar una tabla

Puede modificar una tabla con el ALTER consulta. Esta consulta también puede tener bastante Estructura compleja por la multitud de cambios que puede realizar sobre una mesa. Veamos algunos ejemplos simples.

¡Asegúrese de volver a crear la tabla que acabamos de eliminar o las siguientes consultas obviamente no funcionarán!

Agregar una columna

Gracias a la legibilidad de SQL, no creo que esto necesite una explicación. Puedes simplemente el ADD palabra clave para agregar una nueva columna en el ALTER consulta.

Agregar una columna

Eliminar una columna

De la misma manera, puede simplemente el DROP palabra clave para eliminar una columna existente en el ALTER consulta.

Eliminar una columna

También es muy simple. Pero utilícelo con precaución, ya que elimina datos de forma permanente sin previo aviso.

Vuelva a agregar el email columna porque la vamos a usar más adelante:

Modificar una columna

En ocasiones, es posible que desee cambiar las propiedades de una columna para no tener que eliminarla y volver a crearla.

Echemos un vistazo a la siguiente consulta.

Modificar una columna

Cambiaría el nombre de username columna a user_name y cambiar el tipo de VARCHAR(20) a VARCHAR(30). Un cambio como este no debería alterar ninguno de los datos existentes en la tabla.

Agregar datos a una tabla

Veamos cómo agregar algunos datos a la tabla usando la siguiente consulta.

Agregar datos a la tabla

Como puedes ver, VALUES() contiene la lista de valores de campo, separados por comas. Los valores de cadena están encerrados entre comillas simples. Y los valores deben estar en el orden de las columnas que se definieron cuando creamos la tabla.

Tenga en cuenta que el primer valor es NULL para el campo de clave principal que creamos, que es user_id. Hacemos esto para que se genere una identificación automáticamente, porque la columna se establece en AUTO_INCREMENT. Al ingresar una fila de datos por primera vez, la identificación será 1. La siguiente fila insertada será 2 y así sucesivamente.

De esta manera, puede agregar más datos en el users mesa.

Sintaxis alternativa—SET

Aquí hay otra sintaxis para insertar filas.

Agregar datos a la tabla

En la consulta anterior, estamos usando la palabra clave SET en vez de VALUES, y no va seguido de paréntesis. Esto es más seguro que VALUES, porque hay menos peligro de olvidar o confundir el orden de las columnas. Hay algunas cosas a tener en cuenta aquí:

  • Se puede omitir una columna. Por ejemplo, no asignamos un valor a user_idque por defecto será el AUTO_INCREMENT funcionalidad. Si omites un VARCHAR columna, por defecto sería una cadena vacía (a menos que se haya especificado un valor predeterminado diferente durante la creación de la tabla).
  • Cada columna tiene que ser referenciada por su nombre. Por eso, pueden estar en cualquier orden, a diferencia de la sintaxis anterior.

Sintaxis alternativa: valores con nombre

Aquí hay otra sintaxis. En este caso le decimos al INSERT declaración para qué columnas estamos proporcionando valores, y luego use el VALUES palabra clave para pasar esos valores.

Agregar datos a la tabla

De nuevo, dado que se hace referencia a cada columna por su nombre, pueden estar en cualquier orden.

los LAST_INSERT_ID() Función

Puede utilizar la siguiente consulta para obtener el AUTO_INCREMENT id para la última fila insertada, en la sesión actual.

Obtener ID de última inserción

los NOW() Función

Creo que es un buen momento para demostrar cómo puede usar una función MySQL dentro de sus consultas.

los NOW() La función devuelve la fecha actual. Así que puedes usarlo para establecer automáticamente un DATE columna al día actual al insertar una nueva fila.

Obtener hora actual

Tenga en cuenta que recibimos una advertencia de MySQL, pero no es gran cosa. La razón es que NOW() en realidad también devuelve información de tiempo.

Obtener hora actual

Pero el create_date columna que creamos solo contiene el DATE, y no la hora, por lo que se truncaron los datos devueltos. Podríamos usar el CURDATE() en su lugar, que devuelve solo la fecha, pero los datos almacenados al final serían los mismos de cualquier manera.

Leer datos de una tabla

Obviamente, los datos que agregamos serían inútiles a menos que podamos leerlos. Aquí es donde el SELECT entra la consulta.

Aquí está lo más simple posible. SELECT consulta para leer de una tabla:

Leer datos de la tabla

En este caso, el asterisco (*) significa que solicitamos obtener todas las columnas de la tabla. Si desea solo columnas específicas, la consulta se vería así:

Leer datos de la tabla

los WHERE Cláusula

La mayoría de las veces, solo estamos interesados ​​​​en algunas de las filas, y no en todas. Por ejemplo, digamos que queremos la dirección de correo electrónico para el tutsplus usuario.

Para recuperarlo, puede utilizar la siguiente consulta.

Dónde cláusula

Piense en ello como un IF declaración. los WHERE La cláusula le permite poner condiciones en la consulta para los resultados que está buscando.

Tenga en cuenta que para la condición de igualdad, solo se usa un solo signo igual (=), en lugar de doble igual (==) al que podría estar acostumbrado desde la programación.

También puede usar otras condiciones de comparación:

Dónde cláusula

los AND y OR Los operadores se pueden utilizar para combinar condiciones:

y y o condiciones

Tenga en cuenta que los valores numéricos no tienen que estar entre comillas.

los IN Operador

los IN El operador es útil para hacer coincidir múltiples valores.

Cláusula IN

los LIKE Operador

los LIKE operador le permite realizar búsquedas con comodines.

LIKE Operador

El signo de porcentaje (%) se utiliza como comodín.

los ORDER BY Cláusula

Si desea que los resultados se devuelvan en un orden específico, puede utilizar el ORDER BY cláusula.

ORDEN POR Cláusula

El orden predeterminado es ASC (es decir, ascendente). Puedes añadir DESC ordenarlo por orden descendente.

los LIMITOFFSET Cláusula

Con la ayuda del LIMIT y OFFSET cláusula, puede limitar el número de resultados devueltos.

Uso de LÍMITE y COMPENSACIÓN

los LIMIT 2 la cláusula solo obtiene los primeros 2 resultados. los LIMIT 1 OFFSET 2 obtiene 1 resultado, después de los primeros 2 resultados. los LIMIT 2, 1 significa lo mismo, pero tenga en cuenta que el primer número es el desplazamiento y el segundo número es el límite.

los GROUP BY Cláusula

los GROUP BY La cláusula es realmente útil cuando desea agregar los datos en función de una columna en particular. Supongamos que en nuestra tabla de usuarios, tiene country_id columna, que almacena el país del usuario. Ahora, si desea obtener el recuento de todos los usuarios según el país, puede usar el GROUP BY cláusula para lograrlo.

La consulta sería algo como esto.

Agrupar por cláusula

los JOIN Cláusula

los JOIN cláusula le ayuda a seleccionar datos comunes de dos tablas diferentes. Idealmente, ambas tablas deberían contener una columna que sea común entre esas dos tablas.

En nuestro ejemplo, hemos users tabla que contiene la user_id columna. Ahora, supongamos que tenemos otra tabla llamada user_detailsque contiene user_id, user_company_name y user_ssn_no columnas los user_details La tabla se utiliza para almacenar información adicional sobre los usuarios. En este caso, tenemos la user_id columna en ambas tablas y es común, por lo que puede usar el JOIN cláusula para recuperar toda la información del usuario mediante el uso de una sola consulta.

Echemos un vistazo a la siguiente consulta.

Le daría todos los detalles de todos los usuarios en una sola consulta. Hay diferentes tipos de uniones como LEFT JOIN, RIGHT JOIN y INNER JOIN. los INNER JOIN es uno de los join más utilizados entre todos.

los HAVING Cláusula

los HAVING La cláusula se usa generalmente junto con el GROUP BY cláusula. Puedes pensar en ello como un WHERE cláusula para las columnas agregadas como COUNT, SUM etc. Entonces, básicamente, cuando desea filtrar los resultados en función de las columnas agregadas, debe usar el HAVING cláusula.

En la sección anterior, discutimos cómo puede encontrar el grupo total de usuarios por el country_id columna. Podemos extender el mismo ejemplo y filtrar los países que tienen más de dos usuarios.

Echemos un vistazo al siguiente ejemplo.

Tener cláusula

Actualizar datos en una tabla

La siguiente consulta se utiliza para actualizar los datos de una tabla.

Actualizar datos en una tabla

La mayoría de las veces, se utiliza con un WHERE cláusula, porque desearía que solo se actualizaran filas específicas. si un WHERE no se proporciona la cláusula, ¡todas las filas se actualizarán con los mismos cambios! Por lo tanto, debe tener cuidado al usar UPDATE consulta.

Un UPDATE consulta sin WHERE La cláusula escribirá los mismos datos en cada registro de la tabla, borrando permanentemente los datos anteriores. ¡Ten mucho cuidado con esta consulta!

También puedes usar un LIMIT cláusula para limitar el número de filas que se actualizarán.

Actualización con cláusula LIMIT

Eliminar datos de una tabla

Al igual que UPDATEla siguiente consulta también se suele utilizar con un WHERE cláusula.

Eliminar datos de la tabla
Un DELETE consulta sin WHERE la cláusula borrará todos los registros de la tabla de forma permanente. ¡Ten mucho cuidado con esta consulta!

Truncar una tabla

Para eliminar todo el contenido de una tabla, puede hacer esto:

Pero generalmente es más eficiente en rendimiento usar TRUNCATE en cambio.

Tabla TRUNCADA

los TRUNCATE cláusula también restablece el AUTO_INCREMENT números para que una nueva fila vuelva a empezar desde el principio. Pero esto no sucede con un DELETE consulta, y el contador sigue subiendo.

Escapar valores de cadena y palabras especiales

Valores de cadena

Ciertos caracteres deben escaparse, de lo contrario, puede tener problemas.

Echemos un vistazo a la siguiente consulta.

Valores de escape

La barra invertida () se utiliza para escapar. Eso asegura que el personaje (por ejemplo, el ' carácter en este ejemplo) se trata como parte de la cadena, ignorando su significado especial en SQL.

Esto también es muy importante por razones de seguridad. Cualquier entrada del usuario que ingrese a la base de datos debe escaparse correctamente. En PHP, usas el mysql_real_escape_string() funcionan o usan declaraciones preparadas ya que se escapan automáticamente.

Palabras especiales

Dado que MySQL tiene muchas palabras especiales como SELECT o UPDATE, puede evitar colisiones poniendo comillas alrededor de los nombres de sus tablas y columnas. Pero estas no son las cotizaciones regulares; necesitas usar el back-tick (`) personaje.

Digamos que desea agregar una columna llamada delete por alguna razón:

Palabras especiales

Como puede ver, cuando intentamos ejecutar la consulta sin el carácter de retroceso, MySQL nos dio la advertencia. Y cuando usamos el carácter de tilde invertida, nos permitió ejecutar la consulta con éxito.

Cómo hacer una copia de seguridad de una base de datos

En esta sección, veremos rápidamente cómo puede realizar una copia de seguridad de su base de datos MySQL con la ayuda de la consola de línea de comandos.

Echemos un vistazo al siguiente comando.

Debe reemplazar los marcadores de posición según sea necesario. los {HOSTNAME} debe ser reemplazado con el nombre de su servidor, sería localhost si está trabajando localmente. los {USERNAME} debe ser reemplazado con su nombre de usuario de MySQL. los {DATABASENAME} debe reemplazarse con el nombre de la base de datos MySQL de la que desea realizar una copia de seguridad. Cuando presiona enter para ejecutar el comando anterior, le pedirá la contraseña, ya que hemos proporcionado el -p parámetro. Solo necesita ingresar su contraseña de MySQL y hará una copia de seguridad de toda la base de datos en el copia de seguridad.sql expediente.

Cómo restaurar la copia de seguridad

¡Una copia de seguridad no sirve de mucho sin una forma de restaurarla!

Echa un vistazo al siguiente comando.

Esto restaurará la base de datos.

Conclusión

Con suerte, en este artículo pude convencerlo de que SQL es muy poderoso, pero fácil de aprender.

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *