Consulta SQLite: Seleccionar, Dónde, LÍMITE, COMPENSACIÓN, Contar, Agrupar por

Para escribir consultas SQL en una base de datos SQLite, debe saber cómo funcionan las cláusulas SELECT, FROM, WHERE, GROUP BY, ORDER BY y LIMIT y cómo usarlas.

Durante este tutorial, aprenderá cómo utilizar estas cláusulas y cómo escribir cláusulas SQLite.

Lectura de datos con selección

La cláusula SELECT es la declaración principal que utiliza para consultar una base de datos SQLite. En la cláusula SELECT, usted indica qué seleccionar. Pero antes de la cláusula select, veamos desde dónde podemos seleccionar datos usando la cláusula FROM.

La cláusula FROM se utiliza para especificar dónde desea seleccionar los datos. En la cláusula from, puede especificar una o más tablas o subconsultas para seleccionar los datos, como veremos later en los tutoriales.

Tenga en cuenta que, para todos los siguienteswing En los ejemplos, debe ejecutar sqlite3.exe y abrir una conexión a la base de datos de muestra como flo.wing:

Paso 1) En este paso,

  1. Abra Mi PC y navegue hasta el siguientewing directorio “C:\sqlite"Y
  2. Luego abre “sqlite3.exe"

Lectura de datos con selección

Paso 2) Abre la base de datos “TutorialesSampleDB.db” por el siguientewing mando:

Lectura de datos con selección

Ahora está listo para ejecutar cualquier tipo de consulta en la base de datos.

En la cláusula SELECT, puede seleccionar no solo el nombre de una columna, sino que también tiene muchas otras opciones para especificar qué seleccionar. Como siguewing:

SELECT *

Este comando seleccionará todas las columnas de todas las tablas (o subconsultas) a las que se hace referencia en la cláusula FROM. Por ejemplo:

SELECT * 
FROM Students
INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

Esto seleccionará todas las columnas tanto de las tablas de estudiantes como de las tablas de departamentos:

Lectura de datos con selección

SELECCIONE el nombre de la tabla.*

Esto seleccionará todas las columnas solo de la tabla "nombre de tabla". Por ejemplo:

SELECT Students.*
FROM Students
INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

Esto seleccionará todas las columnas de la tabla de estudiantes únicamente:

Lectura de datos con selección

Un valor literal

Un valor literal es un valor constante que se puede especificar en la declaración de selección. Puede utilizar valores literales normalmente de la misma manera que utiliza nombres de columnas en la cláusula SELECT. Estos valores literales se mostrarán para cada fila de las filas devueltas por la consulta SQL.

A continuación se muestran algunos ejemplos de diferentes valores literales que puede seleccionar:

  • Literal numérico: números en cualquier formato como 1, 2.55,… etc.
  • Literales de cadena: cualquier cadena "EE. UU.", "este es un texto de muestra", etc.
  • NULO – valor NULO.
  • Current_TIME: le dará la hora actual.
  • CURRENT_DATE: esto le dará la fecha actual.

Esto puede resultar útil en algunas situaciones en las que debe seleccionar un valor constante para todas las filas devueltas. Por ejemplo, si desea seleccionar todos los estudiantes de la tabla Estudiantes, con una nueva columna llamada país que contiene el valor "EE. UU.", puede hacer esto:

SELECT *, 'USA' AS Country FROM Students;

Esto le dará todas las columnas de los estudiantes, además de una nueva columna "País" como esta:

Lectura de datos con selección

Tenga en cuenta que esta nueva columna País no es en realidad una nueva columna agregada a la tabla. Es una columna virtual, creada en la consulta para mostrar los resultados y no se creará en la tabla.

Nombres y alias

El alias es un nombre nuevo para la columna que le permite seleccionar la columna con un nombre nuevo. Los alias de las columnas se especifican utilizando la palabra clave "AS".

Por ejemplo, si desea seleccionar que la columna StudentName se devuelva con “Student Name” en lugar de “StudentName”, puede asignarle un alias como este:

SELECT StudentName AS 'Student Name' FROM Students;

Esto le dará los nombres de los estudiantes con el nombre "Nombre del estudiante" en lugar de "Nombre del estudiante" como este:

Nombres y alias

Tenga en cuenta que el nombre de la columna sigue siendo "Nombre del estudiante“; la columna StudentName sigue siendo la misma, no cambia según el alias.

El alias no cambiará el nombre de la columna; simplemente cambiará el nombre para mostrar en la cláusula SELECT.

Además, tenga en cuenta que la palabra clave "AS" es opcional, puede poner el nombre del alias sin ella, algo como esto:

SELECT StudentName 'Student Name' FROM Students;

Y le dará exactamente el mismo resultado que la consulta anterior:

Nombres y alias

También puede dar alias a las tablas, no solo a las columnas. Con la misma palabra clave “AS”. Por ejemplo, puedes hacer esto:

SELECT s.* FROM Students AS s;

Esto le dará todas las columnas de la tabla Estudiantes:

Nombres y alias

Esto puede resultar muy útil si te unes a más de una mesa; en lugar de repetir el nombre completo de la tabla en la consulta, puede asignar a cada tabla un nombre de alias corto. Por ejemplo, en el siguientewing consulta:

SELECT Students.StudentName, Departments.DepartmentName
FROM Students
INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

Esta consulta seleccionará el nombre de cada estudiante de la tabla "Estudiantes" con su nombre de departamento de la tabla "Departamentos":

Nombres y alias

Sin embargo, la misma consulta se puede escribir así:

SELECT s.StudentName, d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;
  • Le dimos a la tabla de Estudiantes un alias "s" y a la tabla de departamentos un alias "d".
  • Luego, en lugar de usar el nombre completo de la tabla, usamos sus alias para referirnos a ellas.
  • INNER JOIN une dos o más tablas usando una condición. En nuestro ejemplo, unimos la tabla Estudiantes con la tabla Departamentos con la columna DepartmentId. También hay una explicación detallada para INNER JOIN en el tutorial "SQLite Joins".

Esto le dará el resultado exacto de la consulta anterior:

Nombres y alias

¿Dónde?

Escribir consultas SQL usando la cláusula SELECT solo con la cláusula FROM, como vimos en la sección anterior, le dará todas las filas de las tablas. Sin embargo, si desea filtrar los datos devueltos, debe agregar una cláusula "DÓNDE".

La cláusula WHERE se utiliza para filtrar el conjunto de resultados devuelto por el Consulta SQL. Así funciona la cláusula WHERE:

  • En la cláusula WHERE, puede especificar una "expresión".
  • Esa expresión se evaluará para cada fila devuelta por las tablas especificadas en la cláusula FROM.
  • La expresión se evaluará como una expresión booleana, con un resultado verdadero, falso o nulo.
  • Luego, solo se devolverán las filas para las cuales la expresión se evaluó con un valor verdadero, y aquellas con resultados falsos o nulos se ignorarán y no se incluirán en el conjunto de resultados.
  • Para filtrar el conjunto de resultados usando la cláusula WHERE, debe usar expresiones y operadores.

Lista de operadores en SQLite y cómo usarlos

en el siguientewing sección, explicaremos cómo puede filtrar usando expresiones y operadores.

La expresión es uno o más valores literales o columnas combinados entre sí con un operador.

Tenga en cuenta que puede utilizar expresiones tanto en la cláusula SELECT como en la cláusula WHERE.

en el siguientewing En los ejemplos, probaremos las expresiones y operadores tanto en la cláusula select como en la cláusula WHERE. Para mostrarte cómo se desempeñan.

Existen diferentes tipos de expresiones y operadores que puede especificar de la siguiente manera:

SQLite el operador de concatenación “||”

Este operador se utiliza para concatenar uno o más valores literales o columnas entre sí. Producirá una cadena de resultados de todos los valores literales o columnas concatenados. Por ejemplo:

SELECT 'Id with Name: '|| StudentId || StudentName AS StudentIdWithName
FROM Students;

Esto se concatenará en un nuevo alias "ID de estudiante con nombre"

  • El valor de cadena literal "Identificación con nombre: "
  • con el valor de “Identificación del Estudiante"columna y
  • con el valor de “Nombre del estudiante"Columna

SQLite el operador de concatenación '||'

Operador SQLite CAST:

El operador CAST se utiliza para convertir un valor de un tipo de datos a otro tipo de datos.

Por ejemplo, si tiene un valor numérico almacenado como un valor de cadena como este " '12.5' "y desea convertirlo en un valor numérico, puede usar el operador CAST para hacer esto de esta manera"REPARTO ('12.5' ​​COMO REAL)“. O si tiene un valor decimal como 12.5 y necesita obtener solo la parte entera, puede convertirlo a un número entero como este “CAST(12.5 AS INTEGER)”.

Ejemplo

en el siguientewing comando intentaremos convertir diferentes valores en otros tipos de datos:

SELECT CAST('12.5' AS REAL) ToReal, CAST(12.5 AS INTEGER) AS ToInteger;

Esto te dará:

Operador SQLite CAST

El resultado es el siguientewing:

  • CAST('12.5' ​​AS REAL): el valor '12.5' ​​es un valor de cadena, se convertirá en un valor REAL.
  • CAST(12.5 AS INTEGER): el valor 12.5 es un valor decimal, se convertirá en un valor entero. La parte decimal se truncará y pasará a ser 12.

Operadores aritméticos de SQLite:

Tome dos o más valores literales numéricos o columnas numéricas y devuelva un valor numérico. Los operadores aritméticos soportados en SQLite son:

  • Suma "+”- dar la suma de los dos operandos.
  • Resta “” – resta los dos operandos y da como resultado la diferencia.
  • Multiplicación “*” – el producto de los dos operandos.
  • Recordatorio (módulo) “%” – da el resto que resulta de dividir un operando por el segundo operando.
  • División “/” – devuelve el cociente resultante de dividir el operando izquierdo por el operando derecho.

Ejemplo:

en el siguientewing Por ejemplo, probaremos los cinco operadores aritméticos con valores numéricos literales en el mismo

cláusula selecta:

SELECT 25+6, 25-6, 25*6, 25%6, 25/6;

Esto te dará:

Operadores aritméticos SQLite

Observe cómo usamos una declaración SELECT sin una cláusula FROM aquí. Y esto está permitido en SQLite siempre que seleccionemos valores literales.

Operadores de comparación SQLite

Compare dos operandos entre sí y devuelva verdadero o falso de la siguiente manera:

  • "<” – devuelve verdadero si el operando izquierdo es menor que el operando derecho.
  • "<=” – devuelve verdadero si el operando izquierdo es menor o igual que el operando derecho.
  • ">” – devuelve verdadero si el operando izquierdo es mayor que el operando derecho.
  • ">=” – devuelve verdadero si el operando izquierdo es mayor o igual que el operando derecho.
  • "= y ==” – devuelve verdadero si los dos operandos son iguales. Tenga en cuenta que ambos operadores son iguales y no hay diferencia entre ellos.
  • "!= y <>” – devuelve verdadero si los dos operandos no son iguales. Tenga en cuenta que ambos operadores son iguales y no hay diferencia entre ellos.

Tenga en cuenta que SQLite expresa el valor verdadero con 1 y el valor falso con 0.

Ejemplo:

SELECT 
  10<6 AS '<', 10<=6 AS '<=',
  10>6 AS '>', 10>=6 AS '>=',
  10=6 AS '=', 10==6 AS '==',
  10!=6 AS '!=', 10<>6 AS '<>';

Esto dará algo como esto:

Operadores de comparación SQLite

Operadores de coincidencia de patrones SQLite

"COMO” – se utiliza para combinar patrones. Usando "Me gusta“, puede buscar valores que coincidan con un patrón especificado mediante un comodín.

El operando de la izquierda puede ser un valor literal de cadena o una columna de cadena. El patrón se puede especificar de la siguiente manera:

  • Contiene patrón. Por ejemplo, Nombre del estudiante COMO '%a%' – esto buscará los nombres de los estudiantes que contengan la letra “a” en cualquier posición en la columna StudentName.
  • Comienza con el patrón. Por ejemplo, "Nombre del estudiante COMO 'a%'”- busque los nombres de los estudiantes que comiencen con la letra “a”.
  • Termina con el patrón. Por ejemplo, "Nombre del estudiante COMO '%a'” – Busque los nombres de los estudiantes que terminen con la letra “a”.
  • Hacer coincidir cualquier carácter en una cadena usando la letra de guión bajo "_". Por ejemplo, "Nombre del estudiante COMO 'J___'” – Busque nombres de estudiantes que tengan 4 caracteres de longitud. Debe comenzar con la letra "J" y puede tener otros tres caracteres más después de la letra "J".

Ejemplos de coincidencia de patrones:

  1. Obtenga nombres de estudiantes que comiencen con la letra 'j':
    SELECT StudentName FROM Students WHERE StudentName LIKE 'j%';

    Resultado:

    Operadores de coincidencia de patrones SQLite

  2. Obtenga que los nombres de los estudiantes terminen con la letra "y":
    SELECT StudentName FROM Students WHERE StudentName LIKE '%y';

    Resultado:

    Operadores de coincidencia de patrones SQLite

  3. Obtenga los nombres de los estudiantes que contienen la letra 'n':
    SELECT StudentName FROM Students WHERE StudentName LIKE '%n%';

    Resultado:

    Operadores de coincidencia de patrones SQLite

“GLOB” – es equivalente al operador LIKE, pero GLOB distingue entre mayúsculas y minúsculas, a diferencia del operador LIKE. Por ejemplo, el siguientewing Dos comandos devolverán resultados diferentes:

SELECT 'Jack' GLOB 'j%';
SELECT 'Jack' LIKE 'j%';

Esto te dará:

Operadores de coincidencia de patrones SQLite

  • La primera declaración devuelve 0 (falso) porque el operador GLOB distingue entre mayúsculas y minúsculas, por lo que 'j' no es igual a 'J'. Sin embargo, la segunda declaración devolverá 1 (verdadero) porque el operador LIKE no distingue entre mayúsculas y minúsculas, por lo que "j" es igual a "J".

Otros operadores:

SQLite Y

Un operador lógico que combina una o más expresiones. Devolverá verdadero, sólo si todas las expresiones arrojan un valor "verdadero". Sin embargo, devolverá falso sólo si todas las expresiones arrojan un valor "falso".

Ejemplo:

El following La consulta buscará estudiantes que tengan StudentId > 5 y StudentName comience con la letra N, los estudiantes devueltos deben cumplir las dos condiciones:

SELECT * 
FROM Students 
WHERE (StudentId > 5) AND (StudentName LIKE 'N%');

Operador SQLite Y

Como resultado, en la captura de pantalla anterior, esto le dará solo "Nancy". Nancy es la única estudiante que cumple ambas condiciones.

SQLite O

Un operador lógico que combina una o más expresiones, de modo que si uno de los operadores combinados da como resultado verdadero, devolverá verdadero. Sin embargo, si todas las expresiones dan resultado falso, devolverá falso.

Ejemplo:

El following La consulta buscará estudiantes que tengan StudentId > 5 o StudentName comience con la letra N, los estudiantes devueltos deben cumplir al menos una de las condiciones:

SELECT * 
FROM Students 
WHERE (StudentId > 5) OR (StudentName LIKE 'N%');

Esto te dará:

Operador SQLite O

Como resultado, en la captura de pantalla anterior, esto le dará el nombre de un estudiante que tiene la letra "n" en su nombre más la identificación del estudiante que tiene un valor>5.

Como puede ver, el resultado es diferente a la consulta con el operador AND.

SQLite ENTRE

ENTRE se utiliza para seleccionar aquellos valores que están dentro de un rango de dos valores. Por ejemplo, "X ENTRE Y Y Z” devolverá verdadero (1) si el valor X está entre los dos valores Y y Z. Otroswise, devolverá falso (0). “X ENTRE Y Y Z" es equivalente a "X >= Y Y X <= Z“, X debe ser mayor o igual a Y y X es menor o igual a Z.

Ejemplo:

en el siguientewing Consulta de ejemplo, escribiremos una consulta para obtener estudiantes con un valor de Id entre 5 y 8:

SELECT *
FROM Students
WHERE StudentId BETWEEN 5 AND 8;

Esto les dará solo a los estudiantes con identificadores 5, 6, 7 y 8:

Operador SQLite ENTRE

SQLite EN

Toma un operando y una lista de operandos. Devolverá verdadero si el valor del primer operando es igual a uno de los valores de los operandos de la lista. El operador IN devuelve verdadero (1) si la lista de operandos contiene el primer valor del operando dentro de sus valores. Otrowise, devolverá falso (0).

Como esto: "columna ENTRADA(x, y, z)“. Esto equivale a” (col=x) o (col=y) o (col=z) ".

Ejemplo:

El following La consulta seleccionará estudiantes con identificadores 2, 4, 6, 8 únicamente:

SELECT * 
FROM Students
WHERE StudentId IN(2, 4, 6, 8);

Me gusta:

Operador SQLite IN

La consulta anterior dará el resultado exacto como siguewing consulta porque son equivalentes:

SELECT * 
FROM Students
WHERE (StudentId = 2) OR (StudentId =  4) OR (StudentId =  6) OR (StudentId = 8);

Ambas consultas dan el resultado exacto. Sin embargo, la diferencia entre las dos consultas es que en la primera consulta utilizamos el operador "IN". En la segunda consulta, utilizamos múltiples operadores "O".

El operador IN equivale a utilizar varios operadores OR. El "DONDE StudentId EN(2, 4, 6, 8)" es equivalente a " DONDE (StudentId = 2) O (StudentId = 4) O (StudentId = 6) O (StudentId = 8);"

Me gusta:

Operador SQLite IN

SQLite NO EN

El operando “NOT IN” es lo opuesto al operador IN. Pero con la misma sintaxis; se necesita un operando y una lista de operandos. Devolverá verdadero si el valor del primer operando no es igual al valor de uno de los operandos de la lista. es decir, devolverá verdadero (0) si la lista de operandos no contiene el primer operando. Como esto: "col NO EN(x, y, z)“. Esto equivale a “(col<>x) Y (col<>y) Y (col<>z)".

Ejemplo:

El following La consulta seleccionará estudiantes con identificadores distintos de uno de estos identificadores 2, 4, 6, 8:

SELECT * 
FROM Students
WHERE StudentId NOT IN(2, 4, 6, 8);

Como este

SQLite NO EN Operador

La consulta anterior le damos el resultado exacto como siguewing consulta porque son equivalentes:

SELECT * 
FROM Students
WHERE (StudentId <> 2) AND (StudentId <> 4) AND (StudentId <> 6) AND (StudentId <> 8);

Me gusta:

SQLite NO EN Operador

En la captura de pantalla anterior,

Usamos múltiples operadores no iguales “<>” para obtener una lista de estudiantes que no son iguales a ninguno de los siguienteswing Id. 2, 4, 6 ni 8. Esta consulta devolverá todos los demás estudiantes además de esta lista de Id.

SQLite EXISTE

Los operadores EXISTS no aceptan ningún operando; sólo se necesita una cláusula SELECT después. El operador EXISTS devolverá verdadero (1) si la cláusula SELECT devuelve alguna fila, y devolverá falso (0) si no se devuelve ninguna fila de la cláusula SELECT.

Ejemplo:

en el siguientewing Por ejemplo, seleccionaremos el nombre del departamento, si la identificación del departamento existe en la tabla de estudiantes:

SELECT DepartmentName
FROM Departments AS d
WHERE EXISTS (SELECT DepartmentId FROM Students AS s WHERE d.DepartmentId = s.DepartmentId);

Esto te dará:

Operador SQLite EXISTE

Sólo los tres departamentos”TI, física y artes" Será devuelto. Y el nombre del departamento”Matemáticas”no se devolverá porque no hay ningún estudiante en ese departamento, por lo que el ID del departamento no existe en la tabla de estudiantes. Es por eso que el operador EXISTS ignoró el "Matemáticas" departamento.

SQLite NO

Invierte el resultado del operador anterior que le sigue. Por ejemplo:

  • NO ENTRE: devolverá verdadero si ENTRE devuelve falso y viceversa.
  • NO ME GUSTA: devolverá verdadero si LIKE devuelve falso y viceversa.
  • NOT GLOB: devolverá verdadero si GLOB devuelve falso y viceversa.
  • NO EXISTE: devolverá verdadero si EXISTE devuelve falso y viceversa.

Ejemplo:

en el siguientewing Por ejemplo, usaremos el operador NOT con el operador EXISTS para obtener los nombres de los departamentos que no existen en la tabla Estudiantes, que es el resultado inverso del operador EXISTS. Entonces, la búsqueda se realizará a través de DepartmentId que no existe en la tabla de departamentos.

SELECT DepartmentName
FROM Departments AS d
WHERE NOT EXISTS (SELECT DepartmentId 
                  FROM Students AS s 
                  WHERE d.DepartmentId = s.DepartmentId);

Salida:

Operador SQLite NO

Sólo el departamento”Matemáticas " Será devuelto. Porque el "Matemáticas"El departamento es el único departamento que no existe en la tabla de estudiantes.

Limitar y ordenar

Orden SQLite

SQLite Order consiste en ordenar el resultado por una o más expresiones. Para ordenar el conjunto de resultados, debe utilizar la cláusula ORDER BY de la siguiente manera:

  • Primero, debe especificar la cláusula ORDER BY.
  • La cláusula ORDER BY debe especificarse al final de la consulta; sólo se puede especificar la cláusula LIMIT después de ella.
  • Especifique la expresión con la que ordenar los datos; esta expresión puede ser un nombre de columna o una expresión.
  • Después de la expresión, puede especificar una dirección de clasificación opcional. Ya sea DESC, para ordenar los datos de forma descendente o ASC para ordenar los datos de forma ascendente. Si no especificaste ninguno de ellos, los datos se ordenarían de forma ascendente.
  • Puede especificar más expresiones utilizando "", entre sí.

Ejemplo

en el siguientewing Por ejemplo, seleccionaremos a todos los estudiantes ordenados por sus nombres pero en orden descendente, luego por el nombre del departamento en orden ascendente:

SELECT s.StudentName, d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId
ORDER BY d.DepartmentName ASC , s.StudentName DESC;

Esto te dará:

Limitar y ordenar

  • SQLite primero ordenará a todos los estudiantes por nombre de departamento en orden ascendente
  • Luego, para cada nombre de departamento, todos los estudiantes bajo ese nombre de departamento se mostrarán en orden descendente por sus nombres.

Límite de SQLite:

Puede limitar el número de filas devueltas por su consulta SQL utilizando la cláusula LIMIT. Por ejemplo, LIMIT 10 le dará solo 10 filas e ignorará todas las demás filas.

En la cláusula LIMIT, puede seleccionar un número específico de filas a partir de una posición específica utilizando la cláusula OFFSET. Por ejemplo, "LÍMITE 4 DESPLAZAMIENTO 4”ignorará las primeras 4 filas y devolverá 4 filas a partir de la quinta fila, por lo que obtendrá las filas 5,6,7 y 8.

Tenga en cuenta que la cláusula OFFSET es opcional, puede escribirla como "LÍMITE 4, 4”Y le dará los resultados exactos.

Ejemplo:

en el siguientewing Por ejemplo, devolveremos solo 3 estudiantes a partir del ID de estudiante 5 usando la consulta:

SELECT * FROM Students LIMIT 4,3;

Esto le dará solo tres estudiantes a partir de la fila 5. Por lo tanto, le dará las filas con StudentId 5, 6 y 7:

Limitar y ordenar

Eliminar duplicados

Si su consulta SQL devuelve valores duplicados, puede utilizar la opción "DISTINCT"Palabra clave para eliminar esos duplicados y devolver valores distintos. Puede especificar más de una columna después de que funcione la tecla DISTINCT.

Ejemplo:

El following La consulta devolverá “valores de nombre de departamento” duplicados: aquí tenemos valores duplicados con nombres TI, Física y Artes.

SELECT d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Esto le dará valores duplicados para el nombre del departamento:

Eliminar duplicados

Observe cómo hay valores duplicados para el nombre del departamento. Ahora usaremos la palabra clave DISTINCT con la misma consulta para eliminar esos duplicados y obtener solo valores únicos. Como esto:

SELECT DISTINCT d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Esto le dará sólo tres valores únicos para la columna de nombre del departamento:

Eliminar duplicados

Agregados

Los agregados SQLite son funciones integradas definidas en SQLite que agruparán múltiples valores de múltiples filas en un solo valor.

Estos son los agregados compatibles con SQLite:

SQLite AVG()

Devolvió el promedio de todos los valores de x.

Ejemplo:

en el siguientewing Por ejemplo, obtendremos la nota media que los estudiantes obtendrán en todos los exámenes:

SELECT AVG(Mark) FROM Marks;

Esto le dará el valor “18.375”:

Agregado: SQLite AVG()

Estos resultados provienen de la suma de todos los valores de las marcas divididos por su recuento.

CONTAR() – CONTAR(X) o CONTAR(*)

Devuelve el recuento total del número de veces que apareció el valor x. Y aquí hay algunas opciones que puedes usar con COUNT:

  • COUNT(x): cuenta solo los valores de x, donde x es el nombre de una columna. Ignorará los valores NULL.
  • CONTAR(*): Cuenta todas las filas de todas las columnas.
  • COUNT (DISTINCT x): Puede especificar una palabra clave DISTINCT antes de x que obtendrá el recuento de los valores distintos de x.

Ejemplo

en el siguientewing Por ejemplo, obtendremos el recuento total de departamentos con COUNT(DepartmentId), COUNT(*) y COUNT(DISTINCT DepartmentId) y en qué se diferencian:

SELECT COUNT(DepartmentId), COUNT(DISTINCT DepartmentId), COUNT(*) FROM Students;

Esto te dará:

Agregado:CONTAR() – CONTAR(X) o CONTAR(*)

Como siguewing:

  • COUNT(DepartmentId) le dará el recuento de todos los ID de departamento e ignorará los valores nulos.
  • COUNT(DISTINCT DepartmentId) le brinda valores distintos de DepartmentId, que son solo 3. Que son los tres valores diferentes del nombre del departamento. Observe que hay 8 valores de nombre de departamento en el nombre del estudiante. Pero solo los tres valores diferentes que son Matemáticas, Informática y Física.
  • COUNT(*) cuenta el número de filas en la tabla de estudiantes, que son 10 filas para 10 estudiantes.

GROUP_CONCAT() – GROUP_CONCAT(X) o GROUP_CONCAT(X,Y)

La función agregada GROUP_CONCAT concatena múltiples valores en un valor con una coma para separarlos. tiene el siguientewing opciones:

  • GROUP_CONCAT(X): Esto concatenará todo el valor de x en una cadena, con la coma “,” utilizada como separador entre los valores. Los valores NULL serán ignorados.
  • GROUP_CONCAT(X, Y): Esto concatenará los valores de x en una cadena, con el valor de y usado como separador entre cada valor en lugar del separador predeterminado ','. Los valores NULL también se ignorarán.
  • GROUP_CONCAT(DISTINCT X): Esto concatenará todos los valores distintos de x en una cadena, con la coma “,” utilizada como separador entre los valores. Los valores NULL serán ignorados.

GROUP_CONCAT(NombreDepartamento) Ejemplo

El following La consulta concatenará todos los valores del nombre del departamento de los estudiantes y la tabla de departamentos en una cadena separada por comas. Entonces, en lugar de devolver una lista de valores, un valor en cada fila. Devolverá solo un valor en una fila, con todos los valores separados por comas:

SELECT GROUP_CONCAT(d.DepartmentName)
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Esto te dará:

Agregado:GROUP_CONCAT() – GROUP_CONCAT(X) o GROUP_CONCAT(X,Y)

Esto le dará la lista de los valores de los nombres de 8 departamentos concatenados en una cadena separada por comas.

GROUP_CONCAT(DISTINCT NombreDepartamento) Ejemplo

El following La consulta concatenará los distintos valores del nombre del departamento de la tabla de estudiantes y departamentos en una cadena separada por comas:

SELECT GROUP_CONCAT(DISTINCT d.DepartmentName)
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Esto te dará:

Agregado:GROUP_CONCAT(DISTINCT NombreDepartamento) Ejemplo

Observe cómo el resultado es diferente al resultado anterior; solo se devolvieron tres valores, que son los nombres de los distintos departamentos, y se eliminaron los valores duplicados.

GROUP_CONCAT(NombreDepartamento,'&') Ejemplo

El following La consulta concatenará todos los valores de la columna del nombre del departamento de la tabla de estudiantes y departamentos en una cadena, pero con el carácter '&' en lugar de una coma como separador:

SELECT GROUP_CONCAT(d.DepartmentName, '&')
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Esto te dará:

Agregado:GROUP_CONCAT(NombreDepartamento,’&’) Ejemplo

Observe cómo se utiliza el carácter "&" en lugar del carácter predeterminado "", para separar los valores.

SQLite MAX() y MIN()

MAX(X) le devuelve el valor más alto de los valores X. MAX devolverá un valor NULL si todos los valores de x son nulos. Mientras que MIN(X) le devuelve el valor más pequeño de los valores X. MIN devolverá un valor NULL si todos los valores de X son nulos.

Ejemplo

en el siguientewing consulta, usaremos las funciones MIN y MAX para obtener la calificación más alta y la calificación más baja del "Marcas" mesa:

SELECT MAX(Mark), MIN(Mark) FROM Marks;

Esto te dará:

Agregado: SQLite MAX() y MIN()

SUMA SQLite(x), Total(x)

Ambos devolverán la suma de todos los valores de x. Pero son diferentes en lo siguiente.wing:

  • SUM devolverá nulo si todos los valores son nulos, pero Total devolverá 0.
  • TOTAL siempre devuelve valores de punto flotante. SUM devuelve un valor entero si todos los valores de x son un número entero. Sin embargo, si los valores no son un número entero, devolverá un valor de punto flotante.

Ejemplo

en el siguientewing consulta usaremos SUM y total para obtener la suma de todas las marcas en el "Marcas" mesas:

SELECT SUM(Mark), TOTAL(Mark) FROM Marks;

Esto te dará:

Agregado: SQLite SUM(x), Total(x)

Como puedes ver, TOTAL siempre devuelve un punto flotante. Pero SUMA devuelve un valor entero porque los valores de la columna "Marca" pueden estar en números enteros.

Ejemplo de diferencia entre SUMA y TOTAL:

en el siguientewing consulta mostraremos la diferencia entre SUMA y TOTAL cuando obtengan la SUMA de valores NULL:

SELECT SUM(Mark), TOTAL(Mark) FROM Marks WHERE TestId = 4;

Esto te dará:

Agregado: diferencia entre SUMA y TOTAL Ejemplo

Tenga en cuenta que no hay marcas para TestId = 4, por lo que hay valores nulos para esa prueba. SUM devuelve un valor nulo en blanco, mientras que TOTAL devuelve 0.

Agrupar por

La cláusula GROUP BY se usa para especificar una o más columnas que se usarán para agrupar las filas en grupos. Las filas con los mismos valores se reunirán (organizarán) en grupos.

Para cualquier otra columna que no esté incluida en el grupo por columnas, puede utilizar una función agregada.

Ejemplo:

El following La consulta le dará el número total de estudiantes presentes en cada departamento.

SELECT d.DepartmentName, COUNT(s.StudentId) AS StudentsCount
FROM Students AS s 
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId
GROUP BY d. DepartmentName;

Esto te dará:

Grupo BY: Cláusula HAVING

La cláusula GROUPBY DepartmentName agrupará a todos los estudiantes en grupos, uno para cada nombre de departamento. Para cada grupo de “departamento”, se contarán los estudiantes que lo integran.

TENIENDO cláusula

Si desea filtrar los grupos devueltos por la cláusula GROUP BY, puede especificar una cláusula "HAVING" con una expresión después de GROUP BY. La expresión se utilizará para filtrar estos grupos.

Ejemplo

en el siguientewing consulta, seleccionaremos aquellos departamentos que tengan solo dos estudiantes:

SELECT d.DepartmentName, COUNT(s.StudentId) AS StudentsCount
FROM Students AS s 
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId
GROUP BY d. DepartmentName
HAVING COUNT(s.StudentId) = 2;

Esto te dará:

Agrupar por

La cláusula HAVING COUNT(S.StudentId) = 2 filtrará los grupos devueltos y devolverá solo aquellos grupos que contengan exactamente dos estudiantes. En nuestro caso, el departamento de Artes tiene 2 estudiantes, por lo que se muestra en el resultado.

Consulta y subconsulta SQLite

Dentro de cualquier consulta, puede utilizar otra consulta ya sea en SELECCIONAR, INSERTAR, ELIMINAR, ACTUALIZAR o dentro de otra subconsulta.

Esta consulta anidada se llama subconsulta. Veremos ahora algunos ejemplos del uso de subconsultas en la cláusula SELECT. Sin embargo, en el tutorial Modificación de datos, veremos cómo podemos usar subconsultas con instrucciones INSERT, DELETE y UPDATE.

Uso de subconsulta en el ejemplo de la cláusula FROM

en el siguientewing consulta incluiremos una subconsulta dentro de la cláusula FROM:

SELECT
  s.StudentName, t.Mark
FROM Students AS s 
INNER JOIN
(
   SELECT StudentId, Mark
   FROM Tests AS t
   INNER JOIN Marks AS m ON t.TestId = m.TestId
)  ON s.StudentId = t.StudentId;

La consulta:

   SELECT StudentId, Mark
   FROM Tests AS t
   INNER JOIN Marks AS m ON t.TestId = m.TestId

La consulta anterior se denomina aquí subconsulta porque está anidada dentro de la cláusula FROM. Observe que le asignamos un nombre de alias "t" para que podamos hacer referencia a las columnas devueltas en la consulta.

Esta consulta le dará:

Consulta y subconsulta SQLite: uso de subconsulta en la cláusula FROM

Entonces en nuestro caso,

  • s.StudentName se selecciona de la consulta principal que proporciona el nombre de los estudiantes y
  • t.Mark se selecciona de la subconsulta; que da las calificaciones obtenidas por cada uno de estos estudiantes

Usando subconsulta en el ejemplo de la cláusula WHERE

en el siguientewing consulta incluiremos una subconsulta en la cláusula WHERE:

SELECT DepartmentName
FROM Departments AS d
WHERE NOT EXISTS (SELECT DepartmentId 
                  FROM Students AS s 
                  WHERE d.DepartmentId = s.DepartmentId);

La consulta:

SELECT DepartmentId 
FROM Students AS s 
WHERE d.DepartmentId = s.DepartmentId

La consulta anterior se denomina aquí subconsulta porque está anidada en la cláusula WHERE. La subconsulta devolverá los valores de DepartmentId que utilizará el operador NO EXISTE.

Esta consulta le dará:

Consulta y subconsulta SQLite: uso de subconsulta en la cláusula WHERE

En la consulta anterior, hemos seleccionado el departamento que no tiene ningún estudiante matriculado. Que es el departamento de “Matemáticas” de aquí.

Establecer operaciones: UNION, Intersección

SQLite admite lo siguientewing Operaciones SET:

UNIÓN Y UNIÓN TODOS

Combina uno o más conjuntos de resultados (un grupo de filas) devueltos por múltiples declaraciones SELECT en un conjunto de resultados.

UNION devolverá valores distintos. Sin embargo, UNION ALL no incluirá duplicados.

Tenga en cuenta que el nombre de la columna será el nombre de la columna especificado en la primera instrucción SELECT.

Ejemplo de UNIÓN

en el siguientewing Por ejemplo, obtendremos la lista de DepartmentId de la tabla de estudiantes y la lista de DepartmentId de la tabla de departamentos en la misma columna:

SELECT DepartmentId AS DepartmentIdUnioned FROM Students
UNION
SELECT DepartmentId FROM Departments;

Esto te dará:

Operaciones de conjunto: ejemplo UNION

La consulta devuelve solo 5 filas que son los distintos valores de identificación del departamento. Observe el primer valor que es el valor nulo.

SQLite UNION TODO Ejemplo

en el siguientewing Por ejemplo, obtendremos la lista de DepartmentId de la tabla de estudiantes y la lista de DepartmentId de la tabla de departamentos en la misma columna:

SELECT DepartmentId AS DepartmentIdUnioned FROM Students
UNION ALL
SELECT DepartmentId FROM Departments;

Esto te dará:

Operaciones de conjunto: ejemplo UNION

La consulta devolverá 14 filas, 10 filas de la tabla de estudiantes y 4 de la tabla de departamentos. Tenga en cuenta que hay duplicados en los valores devueltos. Además, tenga en cuenta que el nombre de la columna fue el especificado en la primera instrucción SELECT.

Ahora, veamos cómo UNION all dará resultados diferentes si reemplazamos UNION ALL con UNION:

INTERSECCIÓN SQLite

Devuelve los valores que existen en ambos conjuntos de resultados combinados. Se ignorarán los valores que existan en uno de los conjuntos de resultados combinados.

Ejemplo

en el siguientewing consulta, seleccionaremos los valores de DepartmentId que existen en las tablas Estudiantes y Departamentos en la columna DepartmentId:

SELECT DepartmentId FROM Students
Intersect
SELECT DepartmentId FROM Departments;

Esto te dará:

Establecer operaciones - INTERSECTAR

La consulta devuelve solo tres valores 1, 2 y 3. Que son los valores que existen en ambas tablas.

Sin embargo, los valores nulo y 4 no se incluyeron porque el valor nulo existe únicamente en la tabla de estudiantes y no en la tabla de departamentos. Y el valor 4 existe en la tabla de departamentos y no en la tabla de estudiantes.

Es por eso que tanto los valores NULL como 4 fueron ignorados y no incluidos en los valores devueltos.

EXCEPTO

Supongamos que si tiene dos listas de filas, lista1 y lista2, y desea las filas solo de la lista1 que no existen en la lista2, puede usar la cláusula "EXCEPTO". La cláusula EXCEPT compares las dos listas y devuelve aquellas filas que existen en lista1 y no existen en lista2.

Ejemplo

en el siguientewing consulta, seleccionaremos los valores de DepartmentId que existen en la tabla de departamentos y no existen en la tabla de estudiantes:

SELECT DepartmentId FROM Departments
EXCEPT
SELECT DepartmentId FROM Students;

Esto te dará:

Establecer operaciones - EXCEPTO

La consulta devuelve solo el valor 4. Que es el único valor que existe en la tabla de departamentos y no existe en la tabla de estudiantes.

Manejo NULL

Los "NULL"El valor es un valor especial en SQLite. Se utiliza para representar un valor desconocido o faltante. Tenga en cuenta que el valor nulo es totalmente diferente a "0” o valor “” en blanco. Sin embargo, debido a que 0 y el valor en blanco son valores conocidos, el valor nulo es desconocido.

Los valores NULL requieren un manejo especial en SQLite, ahora veremos cómo manejar los valores NULL.

Buscar valores NULL

No puede utilizar el operador de igualdad normal (=) para buscar valores nulos. Por ejemplo, el siguientewing La consulta busca los estudiantes que tienen un valor de DepartmentId nulo:

SELECT * FROM Students WHERE DepartmentId = NULL;

Esta consulta no dará ningún resultado:

Manejo NULO

Debido a que el valor NULL no es igual a ningún otro valor incluido un valor nulo en sí mismo, es por eso que no arrojó ningún resultado.

  • Sin embargo, para que la consulta funcione, debe utilizar el "ES NULO" operador para buscar valores nulos de la siguiente manerawing:
SELECT * FROM Students WHERE DepartmentId IS NULL;

Esto te dará:

Manejo NULO

La consulta devolverá aquellos estudiantes que tengan un valor de DepartmentId nulo.

  • Si desea obtener esos valores que no son nulos, debe utilizar el comando "NO ES NULO”operador así:
SELECT * FROM Students WHERE DepartmentId IS NOT NULL;

Esto te dará:

Manejo NULO

La consulta devolverá aquellos estudiantes que no tienen un valor NULL DepartmentId.

Resultados condicionales

Si tiene una lista de valores y desea seleccionar cualquiera de ellos según algunas condiciones. Para eso, la condición para ese valor particular debe ser verdadera para poder ser seleccionado.

La expresión CASE evaluará esta lista de condiciones para todos los valores. Si la condición es verdadera, devolverá ese valor.

Por ejemplo, si tiene una columna "Calificación" y desea seleccionar un valor de texto basado en el valor de calificación como siguewing:

– “Excelente” si la nota es superior a 85.

– “Muy Bueno” si la nota está entre 70 y 85.

– “Bueno” si la nota está entre 60 y 70.

Entonces puedes usar la expresión CASE para hacer eso.

Esto se puede usar para definir cierta lógica en la cláusula SELECT para que pueda seleccionar ciertos resultados dependiendo de ciertas condiciones como la declaración if, por ejemplo.

El operador CASE se puede definir con diferentes sintaxis como siguewing:

  1. Puedes utilizar diferentes condiciones:
CASE 
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  WHEN condition3 THEN result3
  …
  ELSE resultn
END
  1. O puedes usar solo una expresión y poner diferentes valores posibles para elegir:
CASE expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  WHEN value3 THEN result3
  …
  ELSE restuln 
END

Tenga en cuenta que la cláusula ELSE es opcional.

Ejemplo

en el siguientewing ejemplo, usaremos el CASE expresión con NULL valor en la columna Id. de departamento en la tabla Estudiantes para mostrar el texto "Sin departamento" como siguewing:

SELECT 
  StudentName,
  CASE 
    WHEN DepartmentId IS NULL THEN 'No Department'
    ELSE DepartmentId 
  END AS DepartmentId
FROM Students;
  • El operador CASE verificará el valor de DepartmentId si es nulo o no.
  • Si es un valor NULL, seleccionará el valor literal "Sin departamento" en lugar del valor DepartmentId.
  • Si no es un valor nulo, seleccionará el valor de la columna DepartmentId.

Esto le dará el resultado como se muestra a continuación:

Resultados condicionales

Expresión de tabla común

Las expresiones de tabla comunes (CTE) son subconsultas que se definen dentro de la declaración SQL con un nombre determinado.

Tiene una ventaja sobre las subconsultas porque se define a partir de declaraciones SQL y hará que las consultas sean más fáciles de leer, mantener y comprender.

Se puede definir una expresión de tabla común colocando la cláusula CON delante de las declaraciones SELECT como siguewing:

WITH CTEname
AS
(
   SELECT statement
)
SELECT, UPDATE, INSERT, or update statement here FROM CTE

Los "nombreCTE” es cualquier nombre que le puedas dar al CTE, puedes usarlo para referirte a él later. Tenga en cuenta que puede definir la instrucción SELECT, UPDATE, INSERT o DELETE en CTE

Ahora, veamos un ejemplo de cómo usar CTE en la cláusula SELECT.

Ejemplo

en el siguientewing Por ejemplo, definiremos un CTE a partir de una instrucción SELECT y luego lo usaremos later en otra consulta:

WITH AllDepartments
AS
(
  SELECT DepartmentId, DepartmentName
  FROM Departments
)
SELECT 
  s.StudentId,
  s.StudentName,
  a.DepartmentName
FROM Students AS s
INNER JOIN AllDepartments AS a ON s.DepartmentId = a.DepartmentId;

En esta consulta, definimos un CTE y le dimos el nombre “Todos los departamentos“. Este CTE se definió a partir de una consulta SELECT:

SELECT DepartmentId, DepartmentName
  FROM Departments

Luego, después de definir el CTE, lo usamos en la consulta SELECT que viene después.

Tenga en cuenta que las expresiones de tabla comunes no afectan el resultado de la consulta. Es una forma de definir una vista lógica o subconsulta para poder reutilizarlas en la misma consulta. Las expresiones de tabla comunes son como una variable que usted declara y la reutiliza como una subconsulta. Sólo la instrucción SELECT afecta el resultado de la consulta.

Esta consulta le dará:

Expresión de tabla común

Consultas avanzadas

Las consultas avanzadas son aquellas consultas que contienen complex uniones, subconsultas y algunos agregados. en el siguientewing sección veremos un ejemplo de una consulta avanzada:

¿De dónde obtenemos el,

  • Nombres de los departamentos con todos los estudiantes de cada departamento.
  • Nombre de los estudiantes separados por coma y
  • Showing el departamento que tiene al menos tres estudiantes en él
SELECT 
  d.DepartmentName,
  COUNT(s.StudentId) StudentsCount,
  GROUP_CONCAT(StudentName) AS Students
FROM Departments AS d 
INNER JOIN Students AS s ON s.DepartmentId = d.DepartmentId
GROUP BY d.DepartmentName
HAVING COUNT(s.StudentId) >= 3;

Agregamos un SUSCRÍBETE cláusula para obtener el nombre del departamento de la tabla Departamentos. Después de eso agregamos una cláusula GROUP BY con dos funciones agregadas:

  • “CONTAR” para contar los estudiantes de cada grupo de departamento.
  • GROUP_CONCAT para concatenar estudiantes para cada grupo con comas separadas en una cadena.
  • Después de GROUP BY, usamos la cláusula HAVING para filtrar los departamentos y seleccionar solo aquellos departamentos que tengan al menos 3 estudiantes.

El resultado será el siguiente.wing:

Consultas Avanzadas

Resumen

Esta fue una introducción a la escritura de consultas SQLite y los conceptos básicos de consultar la base de datos y cómo filtrar los datos devueltos. Ahora puede escribir sus propias consultas SQLite.