SQLite Consulta: Seleccionar, Dónde, LÍMITE, COMPENSACIÓN, Contar, Agrupar por
Marcus Allen
Para escribir consultas SQL en un SQLite base de datos, 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 SQLite cláusulas.
Lectura de datos con selección
La cláusula SELECT es la declaración principal que utiliza para consultar un SQLite base de datos. 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 de las que seleccionar los datos, como veremos más adelante en los tutoriales.
Tenga en cuenta que, para todos los ejemplos siguientes, debe ejecutar sqlite3.exe y abrir una conexión a la base de datos de muestra como se indica a continuación:
Paso 1) En este paso,
- Abra Mi PC y navegue hasta el siguiente directorio “C:\sqlite"Y
- Luego abre “sqlite3.exe"
Paso 2) Abre la base de datos “TutorialesSampleDB.db" mediante el siguiente comando:
Ahora está listo para ejecutar cualquier tipo de consulta en la base de datos.
En la cláusula SELECT, no solo puede seleccionar un nombre de columna, sino que también tiene muchas otras opciones para especificar qué seleccionar. Como se muestra a continuación:
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:
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:
Un valor literal
Un valor literal es un valor constante que se puede especificar en la sentencia SELECT. Puede utilizar valores literales normalmente de la misma forma que utiliza los nombres de columna 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:
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:
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:
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:
Esto puede resultar muy útil si va a unir más de una tabla; en lugar de repetir el nombre completo de la tabla en la consulta, puede asignarle a cada tabla un alias corto. Por ejemplo, en la siguiente 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":
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 "SQLite Se une al tutorial.
Esto le dará el resultado exacto de la consulta anterior:
¿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 mediante la cláusula WHERE, debe utilizar expresiones y operadores.
Lista de operadores en SQLite y como usarlos
En la siguiente sección explicaremos cómo puedes filtrar usando expresiones y operadores.
La expresión es uno o más valores literales o columnas combinados entre sí mediante un operador.
Tenga en cuenta que puede utilizar expresiones tanto en la cláusula SELECT como en la cláusula WHERE.
En los siguientes ejemplos, probaremos las expresiones y los operadores tanto de la cláusula select como de la cláusula WHERE para mostrarle cómo funcionan.
Hay diferentes tipos de expresiones y operadores que puedes 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 a partir 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 Operador 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 hacerlo 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 siguiente 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á:
El resultado es el siguiente:
- 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.
SQLite Aritmética Operatores:
Tome dos o más valores literales numéricos o columnas numéricas y devuelva un valor numérico. Los operadores aritméticos admitidos en SQLite son:
|
Ejemplo:
En el siguiente 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á:
Observe cómo usamos una declaración SELECT sin una cláusula FROM aquí. Y esto está permitido en SQLite siempre y cuando seleccionemos valores literales.
SQLite Operadores de comparación
Compare dos operandos entre sí y devuelva un verdadero o falso de la siguiente manera:
|
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:
SQLite Operadores de coincidencia de patrones
"COMO” – se utiliza para combinar patrones. Usando "Como“, 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:
- Obtenga nombres de estudiantes que comiencen con la letra 'j':
SELECT StudentName FROM Students WHERE StudentName LIKE 'j%';
Resultado:
- Obtenga que los nombres de los estudiantes terminen con la letra "y":
SELECT StudentName FROM Students WHERE StudentName LIKE '%y';
Resultado:
- Obtenga los nombres de los estudiantes que contienen la letra 'n':
SELECT StudentName FROM Students WHERE StudentName LIKE '%n%';
Resultado:
“GLOB” – es equivalente al operador LIKE, pero GLOB distingue entre mayúsculas y minúsculas, a diferencia del operador LIKE. Por ejemplo, los dos comandos siguientes devolverán resultados diferentes:
SELECT 'Jack' GLOB 'j%'; SELECT 'Jack' LIKE 'j%';
Esto te dará:
- 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
Operador lógico que combina una o más expresiones. Devolverá verdadero solo si todas las expresiones arrojan un valor “verdadero”. Sin embargo, devolverá falso solo si todas las expresiones arrojan un valor “falso”.
Ejemplo:
La siguiente 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%');
Como resultado, en la captura de pantalla anterior, esto le dará solo "Nancy". Nancy es la única estudiante que cumple ambas condiciones.
SQLite OR
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 como resultado falso, devolverá falso.
Ejemplo:
La siguiente 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á:
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. De lo contrario, 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 la siguiente 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:
SQLite IN
Toma un operando y una lista de operandos. Devolverá verdadero si el valor del primer operando es igual al valor de uno de los operandos de la lista. El operador IN devuelve verdadero (1) si la lista de operandos contiene el valor del primer operando dentro de sus valores. De lo contrario, devolverá falso (0).
Como esto: "columna ENTRADA(x, y, z)“. Esto equivale a” (col=x) o (col=y) o (col=z) ".
Ejemplo:
La siguiente consulta seleccionará únicamente estudiantes con identificadores 2, 4, 6 y 8:
SELECT * FROM Students WHERE StudentId IN(2, 4, 6, 8);
Me gusta:
La consulta anterior dará el mismo resultado que la siguiente consulta porque son equivalentes:
SELECT * FROM Students WHERE (StudentId = 2) OR (StudentId = 4) OR (StudentId = 6) OR (StudentId = 8);
Ambas consultas arrojan el mismo resultado. Sin embargo, la diferencia entre ambas consultas es que en la primera utilizamos el operador “IN”. En la segunda, utilizamos varios operadores “OR”.
El operador IN es equivalente a utilizar varios operadores OR. El operador “DONDE StudentId EN(2, 4, 6, 8)" es equivalente a " DONDE (StudentId = 2) O (StudentId = 4) O (StudentId = 6) O (StudentId = 8);"
Me gusta:
SQLite NO EN
El operando “NOT IN” es el opuesto del operador IN, pero con la misma sintaxis: toma 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. De esta manera: “col NO EN(x, y, z)“. Esto equivale a “(col<>x) Y (col<>y) Y (col<>z)".
Ejemplo:
La siguiente consulta seleccionará estudiantes con identificadores distintos a uno de estos identificadores 2, 4, 6, 8:
SELECT * FROM Students WHERE StudentId NOT IN(2, 4, 6, 8);
Como este
La consulta anterior nos da el mismo resultado que la siguiente consulta porque son equivalentes:
SELECT * FROM Students WHERE (StudentId <> 2) AND (StudentId <> 4) AND (StudentId <> 6) AND (StudentId <> 8);
Me gusta:
En la captura de pantalla anterior,
Utilizamos varios operadores desiguales “<>” para obtener una lista de estudiantes que no son iguales a ninguno de los siguientes identificadores 2, 4, 6 ni 8. Esta consulta devolverá todos los demás estudiantes excepto esta lista de identificadores.
SQLite EXISTE
Los operadores EXISTS no toman ningún operando; solo toman una cláusula SELECT después de ellos. El operador EXISTS devolverá verdadero (1) si hay filas devueltas desde la cláusula SELECT, y devolverá falso (0) si no hay filas devueltas desde la cláusula SELECT.
Ejemplo:
En el siguiente ejemplo, seleccionaremos el nombre del departamento, si el ID 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á:
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 NOT
Reverres el resultado del operador precedente que viene después. 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 siguiente ejemplo, utilizaremos el operador NOT con el operador EXISTS para obtener los nombres de los departamentos que no existen en la tabla Students, que es el resultado inverso del operador EXISTS. Por lo tanto, la búsqueda se realizará a través de DepartmentId que no existen en la tabla department.
SELECT DepartmentName
FROM Departments AS d
WHERE NOT EXISTS (SELECT DepartmentId
FROM Students AS s
WHERE d.DepartmentId = s.DepartmentId);
Salida:
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
SQLite Order
SQLite El orden 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 siguiente ejemplo, seleccionaremos 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á:
- SQLite Primero ordenará a todos los estudiantes por el nombre de su 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.
SQLite Límite:
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 siguiente ejemplo, devolveremos solo 3 estudiantes a partir del ID de estudiante 5 utilizando 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:
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:
La siguiente consulta devolverá “valores de nombre de departamento” duplicados: Aquí tenemos valores duplicados con los 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:
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:
Agregados
SQLite Los agregados son funciones integradas definidas en SQLite eso agrupará múltiples valores de múltiples filas en un solo valor.
Aquí están los agregados soportados por SQLite:
SQLite AVG()
Devolvió el promedio de todos los valores de x.
Ejemplo:
En el siguiente ejemplo obtendremos la nota media que obtendrán los alumnos en todos los exámenes:
SELECT AVG(Mark) FROM Marks;
Esto le dará el valor “18.375”:
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 siguiente 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á:
Como sigue:
- 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 de agregación GROUP_CONCAT concatena varios valores en un único valor con una coma para separarlos. Tiene las siguientes 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
La siguiente consulta concatenará todos los valores de los nombres de los departamentos de las tablas de estudiantes y departamentos en una cadena separada por comas. Por lo tanto, en lugar de devolver una lista de valores, devolverá 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á:
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
La siguiente consulta concatenará los valores distintos del nombre del departamento de las tablas 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á:
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
La siguiente consulta concatenará todos los valores de la columna de nombre de 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á:
Observe cómo se utiliza el carácter "&" en lugar del carácter predeterminado "", para separar los valores.
SQLite MÁXIMO MINIMO()
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 la siguiente consulta, utilizaremos las funciones MIN y MAX para obtener la nota más alta y la nota más baja de la “Marcas" mesa:
SELECT MAX(Mark), MIN(Mark) FROM Marks;
Esto te dará:
SQLite SUMA(x), Total(x)
Ambos devolverán la suma de todos los valores x, pero se diferencian en lo siguiente:
- 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 la siguiente consulta usaremos SUM y total para obtener la suma de todas las notas en el “Marcas" mesas:
SELECT SUM(Mark), TOTAL(Mark) FROM Marks;
Esto te dará:
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 la siguiente consulta mostraremos la diferencia entre SUM y TOTAL cuando obtienen la SUMA de valores NULL:
SELECT SUM(Mark), TOTAL(Mark) FROM Marks WHERE TestId = 4;
Esto te dará:
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:
La siguiente 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á:
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 la siguiente consulta seleccionaremos aquellos departamentos que tengan solo dos alumnos:
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á:
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.
SQLite Consulta y subconsulta
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 la siguiente 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á:
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 la siguiente 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 NOT EXISTS.
Esta consulta le dará:
En la consulta anterior, hemos seleccionado el departamento que no tiene ningún estudiante matriculado. Que es el departamento de “Matemáticas” de aquí.
Set Operaciones – UNION,Intersect
SQLite Admite las siguientes 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 siguiente 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á:
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 UNIÓN TODO Ejemplo
En el siguiente 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á:
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:
SQLite INTERSECARSE
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 la siguiente consulta, seleccionaremos los valores de DepartmentId que existen en ambas tablas Students y Departments en la columna DepartmentId:
SELECT DepartmentId FROM Students Intersect SELECT DepartmentId FROM Departments;
Esto te dará:
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 tiene dos listas de filas, list1 y list2, y desea obtener solo las filas de list1 que no existen en list2, puede utilizar la cláusula "EXCEPT". La cláusula EXCEPT compara las dos listas y devuelve las filas que existen en list1 y no existen en list2.
Ejemplo
En la siguiente 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á:
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
El "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, veremos ahora cómo manejar los valores NULL.
Buscar valores NULL
No se puede utilizar el operador de igualdad normal (=) para buscar valores nulos. Por ejemplo, la siguiente consulta busca los estudiantes que tienen un valor DepartmentId nulo:
SELECT * FROM Students WHERE DepartmentId = NULL;
Esta consulta no dará ningún resultado:
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 manera:
SELECT * FROM Students WHERE DepartmentId IS NULL;
Esto te dará:
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á:
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 la calificación como se muestra a continuación:
– “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 las siguientes:
- Puedes utilizar diferentes condiciones:
CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 WHEN condition3 THEN result3 … ELSE resultn END
- 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 siguiente ejemplo, utilizaremos el CASE expresión con NULL valor en la columna Id. del departamento en la tabla Estudiantes para mostrar el texto 'Sin departamento' de la siguiente manera:
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:
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 WITH delante de una instrucción SELECT de la siguiente manera:
WITH CTEname AS ( SELECT statement ) SELECT, UPDATE, INSERT, or update statement here FROM CTE
El "nombreCTE" es cualquier nombre que se le pueda dar a la CTE, y se puede utilizar para hacer referencia a ella más adelante. Tenga en cuenta que puede definir instrucciones SELECT, UPDATE, INSERT o DELETE en las CTE.
Ahora, veamos un ejemplo de cómo usar CTE en la cláusula SELECT.
Ejemplo
En el siguiente ejemplo, definiremos una CTE a partir de una declaración SELECT y luego la usaremos más adelante 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á:
Consultas avanzadas
Las consultas avanzadas son aquellas que contienen uniones complejas, subconsultas y algunos agregados. En la siguiente 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
- Mostrar que el departamento 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 ÚNETE 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:
Resum
Esta fue una introducción a la escritura. SQLite consultas y los conceptos básicos de consultar la base de datos y cómo filtrar los datos devueltos. Ahora puedes escribir el tuyo propio. SQLite consultas
