SQL (Lenguaje de consulta estructurado) ha estado con nosotros durante más de medio siglo y no desaparecerá pronto. Popular tanto en bases de datos relacionales tradicionales como en las nuevas tecnologías de bases de datos NoSQL, SQL se usa ampliamente para análisis de datos, procesamiento de Big Data, lenguajes de codificación y más.

Soy fan de SQL. Es un lenguaje poderoso pero sencillo. En este post, compartiré:

• El poder de SQL y por qué lo usamos
• Errores comunes y mejores prácticas

¿Qué es SQL y por qué debería usarlo?

SQL fue desarrollado inicialmente por IBM a principios de la década de 1970, adoptado por Oracle a finales de esa década, y se ha utilizado en bases de datos relacionales desde entonces. En 1986, SQL fue adoptado como estándar por el American National Standards Institute (ANSI).

A medida que el volumen de datos ha crecido con el tiempo, han surgido nuevas bases de datos y tecnologías. Uno de ellos es el Big Data, para conjuntos de datos demasiado complejos para las aplicaciones tradicionales de procesamiento de datos. Común en Big Data, las bases de datos NoSQL no se basan en el modelo relacional. A veces se llaman No Solo SQL, para enfatizar que pueden admitir un lenguaje de consulta similar a SQL. Algunos ejemplos de bases de datos y bibliotecas no relacionales más recientes que admiten SQL incluyen:

• Motor de análisis Apache Spark
• Almacén de datos de análisis gestionado por Google Big Query
• Facebook Presto y AWS Athena
• Elastic search
• Biblioteca de análisis de datos Python (pandasql)

SQL es ampliamente utilizado tanto en OLTP (Procesamiento de transacciones) como en OLAP (Procesamiento Analítico), a pesar de que los dos son casos de uso muy diferentes:

Fragmento 1: pandasql

Antes de sumergirse en SQL, aquí hay un ejemplo de uso de SQL fuera de las bases de datos: la Biblioteca de Análisis de datos de Python:

import pandas as pdimport pandasql as psdata_frame = pd.DataFrame(, , , ], columns=)sql = """SELECT name, COUNT(DISTINCT id) FROM data_frame GROUP BY NAME """print(ps.sqldf(sql, locals()))

 Name COUNT(DISTINCT ID)0 James 11 John 22 Ted 1

Como muestran estos ejemplos, SQL está vivo y bien en las tecnologías más nuevas y populares.

Echemos un vistazo más de cerca a su poder.

Ejemplos de funciones SQL

La parte más fuerte de SQL son sus funciones. Estos son los tres tipos de funciones compatibles con SQL:

recomendamos ir a través de las funciones de su motor de consulta para entender sus capacidades. Aquí, por ejemplo, puede encontrar la documentación de Presto sobre funciones agregadas y funciones analíticas.

Convenciones de Codificación SQL recomendadas (Nombres y Más)

Para obtener el poder de SQL, recomendamos encarecidamente el uso de convenciones de codificación. Esto mejorará la escritura y la lectura, y mejorará el trabajo en equipo. Aquí hay una breve lista de los más importantes:

Ejemplos de consultas SQL: Bueno & Malo

Para mostrarle la importancia de las convenciones de codificación, mire estos ejemplos buenos y malos.

Fragmento 4: Mal ejemplo

select emp.department, sum(exp.amount) expensesfrom employees emp, expenses expwhere emp.department in (select departmentfrom employeeswhere department like '%development%' or department like '%management%'group by departmenthaving count(distinct id) > 50) and emp.id = exp.employee_idgroup by emp.department

Fragmento 5: Buen ejemplo

-- filter: Development and Management departments with more than 50 employeesWITH dept AS (SELECT departmentFROM employeesWHERE department LIKE '%Development%'OR department LIKE '%Management%'GROUP BY departmentHAVING Count(DISTINCT id) > 50)-- find expenses for each of the filtered departmentsSELECT emp.department,SUM(exp.amount) AS expensesFROM employees AS empINNER JOIN deptON emp.department = dept.departmentINNER JOIN expenses AS expON emp.id = exp.employee_idGROUP BY emp.department

Aquí hay una buena herramienta en línea para formatear SQL que puede usar: https://sqlformat.org/

Solución de problemas SQL: Errores & Errores

Al igual que cualquier otro lenguaje, SQL tiene sus propios errores que debe tener en cuenta para ahorrarse tiempo y evitar errores en su aplicación.

La primera causa de errores SQL & errores: Valores nulos

Fragmento 6: Resultado de consulta NULO

SELECT NULL = NULL --> NULLSELECT NULL != NULL --> NULLSELECT NULL IS NULL --> trueSELECT 1 + NULL --> NULL SELECT SUM(num_column) WHERE FALSE --> NULL

Para superar este problema, debe usar coalesce siempre que haya un peligro:

Fragmento 7: Superar uniones nulas

FROM t1INNER JOIN t2 ON COALESCE(t1.name, '') = COALESCE(t2.name, '')

Los índices y particiones SQL son No se utiliza

Pasamos mucho tiempo construyendo nuestro esquema de base de datos agregando índices y declarando particiones. Pero no obtendremos el rendimiento que queremos si nos detenemos allí, tenemos que asegurarnos de que nuestro motor de base de datos los use.

Por ejemplo, supongamos que tenemos un índice en la columna first_name:

SELECT first_nameFROM employeeWHERE UPPER(first_name) LIKE 'A%'

SELECT first_nameFROM employeeWHERE first_name LIKE 'A%' OR first_name LIKE 'a%'

Puede haber cambios de una implementación a otra, pero el concepto es válido en cualquier implementación SQL.

¿Cómo se asegura de que se utilicen sus índices y particiones? Analice su plan de ejecución de consultas. En algunas implementaciones de SQL, puede usar la palabra clave EXPLAIN. En Oracle, por ejemplo, se llama EXPLICAR PLAN.

Errores de funciones analíticas en el Orden de evaluación SQL

Este es importante si está utilizando una función analítica, ya que malentender el orden de evaluación puede provocar errores. Por ejemplo:

Fragmento 8: Función analítica con filtro

SELECT first_name, last_name, department, COUNT(1) OVER (PARTITION BY department) AS size_of_departmentFROM employeesWHERE last_name LIKE 'A%'

Obtendremos el número de empleados en el departamento con un apellido que comienza por A, en lugar de todos los empleados en el departamento. Recuerde que las funciones analíticas se ejecutan en último lugar.

Estandarización SQL

La sintaxis de fecha y hora, los valores nulos, la concatenación de cadenas, la sensibilidad de mayúsculas y minúsculas de comparación y otros aspectos de SQL varían de un proveedor a otro, lo que dificulta el traslado de una aplicación entre proveedores.

Intente usar solo SQL estándar. Por ejemplo, use COALESCE en lugar de ISNULL (SQL Server) o NVL(Oracle).

Prevenir ataques de inyección SQL (SQLi)

Un problema de seguridad bien conocido en las aplicaciones web es el ataque de inyección SQL – una técnica de inyección de código que ataca a las aplicaciones insertando instrucciones en un campo para su ejecución. Estos ataques se utilizan generalmente para robar datos, cambiar datos o cambiar permisos en la base de datos atacada.

La prevención de ataques de inyección SQL se inicia durante el desarrollo, y debe ir seguida del uso de escáneres y cortafuegos de aplicaciones web para proteger su aplicación. Consulte la entrada de blog de Inyección SQL para obtener ejemplos y formas de prevenir dichos ataques.

Terminar

SQL es genial. Personalmente, prefiero el lenguaje basado en SQL a cualquier otro lenguaje de consulta o API. Sin embargo, debe usarlo correctamente para facilitar su trabajo. Comience por construir su esquema correctamente y luego use convenciones de codificación y otras opciones, como las funciones de palabra clave y ventana CON.

No olvide tener en cuenta las trampas conocidas. Y, si está escribiendo una aplicación, protéjala de la inyección SQL.

El post Introducción a SQL: Ejemplos, Mejores prácticas y Trampas appeared first on Blog.

* * * Este es un blog sindicado de la Red de Bloggers de seguridad de Blog escrito por Ori Nakar. Lea la publicación original en: https://www.imperva.com/blog/introduction-to-sql-examples-best-practices-and-pitfalls/