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 usar estas cláusulas y cómo escribir cláusulas SQLite.
En este tutorial, aprenderá:
- Leer datos con Seleccionar
- Nombres y alias
- DÓNDE
- Limitar y ordenar
- Eliminar duplicados
- Agregar
- Agrupar por
- Consulta y subconsulta
- Establecer operaciones -UNION, Intersect
- Manejo NULO
- Resultados condicionales
- Expresión de tabla común
- Consultas avanzadas
Leer datos con Seleccionar
La cláusula SELECT es la declaración principal que usa para consultar una base de datos SQLite. En la cláusula SELECT, 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 más adelante en los tutoriales.
Tenga en cuenta que, para todos los siguientes ejemplos, debe ejecutar sqlite3.exe y abrir una conexión a la base de datos de muestra mientras fluye:
Paso 1) En este paso,
- Abra Mi PC y navegue hasta el siguiente directorio " C: \ sqlite " y
- Luego abra " sqlite3.exe ":
Paso 2) Abra la base de datos " TutorialsSampleDB.db " con el siguiente comando:
Ahora está listo para ejecutar cualquier tipo de consulta en la base de datos.
En la cláusula SELECT, puede seleccionar no solo un nombre de columna, sino que tiene muchas otras opciones para especificar qué seleccionar. Como sigue:
SELECCIONE *
Este comando seleccionará todas las columnas de todas las tablas (o subconsultas) referenciadas en la cláusula FROM. Por ejemplo:
SELECCIONE *DE EstudiantesINNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;
Esto seleccionará todas las columnas de las tablas de estudiantes y de departamentos:
SELECCIONAR nombre de tabla. *
Esto seleccionará todas las columnas solo de la tabla "tablename". Por ejemplo:
SELECCIONAR Estudiantes. *DE EstudiantesINNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;
Esto seleccionará todas las columnas de la tabla de estudiantes solamente:
Un valor literal
Un valor literal es un valor constante que se puede especificar en la instrucción de selección. Puede usar valores literales normalmente de la misma manera que usa 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.
- NULL: valor NULL.
- 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:
SELECCIONE *, 'EE. UU.' COMO País DE Estudiantes;
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 mediante la palabra clave "AS".
Por ejemplo, si desea seleccionar la columna StudentName para que se devuelva con "Student Name" en lugar de "StudentName", puede asignarle un alias como este:
SELECCIONE StudentName COMO 'Nombre del estudiante' DE Estudiantes;
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 " StudentName "; la columna StudentName sigue siendo la misma, no cambia por 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 de alias sin ella, algo como esto:
SELECCIONE StudentName 'Student Name' FROM Students;
Y le dará exactamente el mismo resultado que la consulta anterior:
También puede asignar alias a las tablas, no solo columnas. Con la misma palabra clave "AS". Por ejemplo, puede hacer esto:
SELECCIONE s. * DE Estudiantes AS;
Esto le dará todas las columnas de la tabla Estudiantes:
Esto puede resultar muy útil si se une 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 la siguiente consulta:
SELECCIONE Estudiantes.Nombre del estudiante, Departamentos.Nombre del departamentoDE EstudiantesINNER 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í:
SELECCIONE s.tudentName, d.DepartmentNameDE Estudiantes AS sINNER JOIN Departamentos 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 ellos.
- INNER JOIN une dos o más tablas mediante 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 de INNER JOIN en el tutorial "SQLite Joins".
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 la consulta SQL. Así es como funciona la cláusula WHERE:
- En la cláusula WHERE, puede especificar una "expresión".
- Esa expresión se evaluará para cada fila devuelta de 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.
- Entonces, solo se devolverán las filas para las que 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 la siguiente sección, explicaremos cómo puede filtrar usando expresiones y operadores.
La expresión es uno o más valores literales o columnas combinadas 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 los siguientes 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 todas las columnas o valores literales concatenados. Por ejemplo:
SELECCIONE 'Id con nombre:' || StudentId || StudentName COMO StudentIdWithNameDE Estudiantes;
Esto se concatenará en un nuevo alias " StudentIdWithName ":
- El valor de cadena literal " Id con nombre: "
- con el valor de la columna " StudentId " y
- con el valor de la columna " StudentName "
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 hacerlo así " CAST ('12 .5' AS REAL) ". O si tiene un valor decimal como 12.5, y necesita obtener solo la parte entera, puede convertirla en un entero como este "CAST (12.5 AS INTEGER)".
Ejemplo
En el siguiente comando intentaremos convertir diferentes valores en otros tipos de datos:
SELECCIONE CAST ('12 .5 'COMO REAL) ToReal, CAST (12.5 COMO INTEGER) COMO 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 se convertirá en 12.
Operadores aritméticos SQLite:
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
seleccionar cláusula:
SELECCIONAR 25 + 6, 25-6, 25 * 6, 25% 6, 25/6;
Esto te dará:
Observe cómo usamos una instrucció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:
|
Tenga en cuenta que SQLite expresa el valor verdadero con 1 y el valor falso con 0.
Ejemplo:
SELECCIONE10 <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 coincidencia de patrones SQLite
" LIKE ": se utiliza para hacer coincidir patrones. Con " 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, StudentName LIKE '% a%' : esto buscará los nombres de los estudiantes que contengan la letra "a" en cualquier posición de la columna StudentName.
- Comienza con el patrón. Por ejemplo, " StudentName LIKE 'a%' ": busque los nombres de los estudiantes que comiencen con la letra "a".
- Termina con el patrón. Por ejemplo, " StudentName LIKE '% a' ": busque los nombres de los estudiantes que terminan con la letra "a".
- Coincidencia de cualquier carácter individual en una cadena usando la letra de subrayado "_". Por ejemplo, " StudentName LIKE '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 los nombres de los estudiantes que comienzan con la letra 'j':
SELECCIONE StudentName DE Estudiantes DONDE StudentName COMO 'j%';
Resultado:
- Obtener los nombres de los estudiantes terminan con la letra 'y':
SELECCIONE StudentName DE Estudiantes DONDE StudentName COMO '% y';
Resultado:
- Obtenga los nombres de los estudiantes que contienen la letra 'n':
SELECCIONE StudentName DE Estudiantes DONDE StudentName COMO '% 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 siguientes dos comandos devolverán resultados diferentes:
SELECCIONE 'Jack' GLOB 'j%';SELECCIONE 'Jack' COMO '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
Un 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 que regresaron deben cumplir las dos condiciones:
SELECCIONE *DE EstudiantesDONDE (StudentId> 5) Y (StudentName LIKE 'N%');
Como resultado, en la captura de pantalla anterior, esto le dará solo "Nancy". Nancy es la única estudiante que cumple con ambas condiciones.
SQLite OR
Un operador lógico que combina una o más expresiones, de modo que si uno de los operadores combinados arroja verdadero, devolverá verdadero. Sin embargo, si todas las expresiones dan falso, devolverá falso.
Ejemplo:
La siguiente consulta buscará estudiantes que tengan StudentId> 5 o StudentName comience con la letra N, los estudiantes retornados deben cumplir al menos una de las condiciones:
SELECCIONE *DE EstudiantesDONDE (StudentId> 5) O (StudentName LIKE 'N%');
Esto te dará:
Como resultado, en la captura de pantalla anterior, esto le dará el nombre de un estudiante con la letra "n" en su nombre más la identificación del estudiante con un valor> 5.
Como puede ver, el resultado es diferente al de la consulta con el operador AND.
SQLite ENTRE
BETWEEN se utiliza para seleccionar aquellos valores que están dentro de un rango de dos valores. Por ejemplo, " X ENTRE YY 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 que Y y X es menor o igual que Z.
Ejemplo:
En la siguiente consulta de ejemplo, escribiremos una consulta para obtener estudiantes con un valor de Id entre 5 y 8:
SELECCIONE *DE EstudiantesDONDE StudentId ENTRE 5 Y 8;
Esto le dará solo a los estudiantes con ID 5, 6, 7 y 8:
SQLite IN
Toma un operando y una lista de operandos. Devolverá verdadero si el primer valor del 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 primer valor de operando dentro de sus valores. De lo contrario, devolverá falso (0).
Así: " col IN (x, y, z) ". Esto es equivalente a " (col = x) o (col = y) o (col = z) ".
Ejemplo:
La siguiente consulta seleccionará estudiantes con ID 2, 4, 6, 8 únicamente:
SELECCIONE *DE EstudiantesDONDE StudentId IN (2, 4, 6, 8);
Como esto:
La consulta anterior dará el resultado exacto como la siguiente consulta porque son equivalentes:
SELECCIONE *DE EstudiantesDONDE (StudentId = 2) O (StudentId = 4) O (StudentId = 6) O (StudentId = 8);
Ambas consultas dan el resultado exacto. Sin embargo, la diferencia entre las dos consultas es que en la primera consulta usamos el operador "IN". En la segunda consulta, usamos varios operadores "O".
El operador IN es equivalente a utilizar varios operadores OR. El " WHERE StudentId IN (2, 4, 6, 8) " es equivalente a " WHERE (StudentId = 2) OR (StudentId = 4) OR (StudentId = 6) OR (StudentId = 8); "
Como esto:
SQLite NO ESTÁ EN
El operando "NOT IN" es lo opuesto al 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. Así: " col NOT IN (x, y, z) ". Esto es equivalente a " (col <> x) AND (col <> y) AND (col <> z) ".
Ejemplo:
La siguiente consulta seleccionará estudiantes con ID no iguales a uno de estos ID 2, 4, 6, 8:
SELECCIONE *DE EstudiantesDONDE StudentId NO EN (2, 4, 6, 8);
Como esto
La consulta anterior damos el resultado exacto como la siguiente consulta porque son equivalentes:
SELECCIONE *DE EstudiantesDONDE (StudentId <> 2) Y (StudentId <> 4) Y (StudentId <> 6) Y (StudentId <> 8);
Como esto:
En la captura de pantalla anterior,
Usamos varios operadores no iguales "<>" para obtener una lista de estudiantes, que no son iguales a ninguno de los siguientes Id. 2, 4, 6 ni 8. Esta consulta devolverá todos los demás estudiantes además de esta lista de Id.
EXISTE SQLite
Los operadores EXISTS no aceptan operandos; solo lleva una cláusula SELECT después. El operador EXISTS devolverá verdadero (1) si hay filas devueltas de la cláusula SELECT, y devolverá falso (0) si no hay filas devueltas de la cláusula SELECT.
Ejemplo:
En el siguiente ejemplo, seleccionaremos el nombre del departamento, si la identificación del departamento existe en la tabla de estudiantes:
SELECCIONAR NombreDepartamentoDE Departamentos AS dDONDE EXISTE (SELECCIONE DepartmentId DE Estudiantes COMO s WHERE d.DepartmentId = s.DepartmentId);
Esto te dará:
Solo se devolverán los tres departamentos " Informática, Física y Artes ". Y el nombre del departamento " Matemáticas " no se devolverá porque no hay ningún estudiante en ese departamento, por lo que la identificación del departamento no existe en la tabla de estudiantes. Es por eso que el operador EXISTS ignoró el departamento de " Matemáticas ".
SQLite NO
Invierte el resultado del operador anterior que le sigue. Por ejemplo:
- NOT BETWEEN: devolverá verdadero si BETWEEN devuelve falso y viceversa.
- NOT LIKE: devolverá verdadero si LIKE devuelve falso y viceversa.
- NO GLOB: devolverá verdadero si GLOB devuelve falso y viceversa.
- NO EXISTE: devolverá verdadero si EXISTE devuelve falso y viceversa.
Ejemplo:
En el siguiente 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.
SELECCIONAR NombreDepartamentoDE Departamentos AS dDONDE NO EXISTE (SELECCIONE DepartmentIdDE Estudiantes AS sDONDE d.DepartmentId = s.DepartmentId);
Salida :
Solo se devolverá el departamento " Matemáticas ". Debido a que el departamento de " Matemáticas " es el único departamento, eso no existe en la tabla de estudiantes.
Limitar y ordenar
Orden SQLite
SQLite Order es ordenar su resultado por una o más expresiones. Para ordenar el conjunto de resultados, debe usar 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; solo 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. O DESC, para ordenar los datos descendentes o ASC para ordenar los datos ascendentes. Si no especifica ninguno de ellos, los datos se ordenarán de forma ascendente.
- Puede especificar más expresiones utilizando "," entre sí.
Ejemplo
En el siguiente ejemplo, seleccionaremos a todos los estudiantes ordenados por sus nombres pero en orden descendente, luego por el nombre del departamento en orden ascendente:
SELECCIONE s.tudentName, d.DepartmentNameDE Estudiantes AS sINNER JOIN Departamentos AS d ON s.DepartmentId = d.DepartmentIdORDER BY d.DepartmentName ASC, s.StudentName DESC;
Esto te dará:
- SQLite ordenará primero 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.
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 comenzando desde una posición específica usando la cláusula OFFSET. Por ejemplo, " LIMIT 4 OFFSET 4 " ignorará las primeras 4 filas y devolverá 4 filas a partir de las quintas filas, por lo que obtendrá las filas 5, 6, 7 y 8.
Tenga en cuenta que la cláusula OFFSET es opcional, puede escribirla como " LIMIT 4, 4 " y le dará los resultados exactos.
Ejemplo :
En el siguiente ejemplo, devolveremos solo 3 estudiantes a partir de la identificación de estudiante 5 usando la consulta:
SELECCIONAR * DE Estudiantes LÍMITE 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 palabra clave " DISTINCT " para eliminar esos duplicados y devolver valores distintos. Puede especificar más de una columna después del trabajo de la tecla DISTINCT.
Ejemplo:
La siguiente consulta devolverá "valores de nombre de departamento" duplicados: Aquí tenemos valores duplicados con nombres de TI, Física y Artes.
SELECCIONAR d.DepartmentNameDE Estudiantes AS sINNER JOIN Departamentos 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.DepartmentNameDE Estudiantes AS sINNER JOIN Departamentos AS d ON s.DepartmentId = d.DepartmentId;
Esto le dará solo tres valores únicos para la columna del nombre del departamento:
Agregar
Los agregados de 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 siguiente ejemplo obtendremos la nota media que los alumnos obtienen de todos los exámenes:
SELECCIONE AVG (Mark) FROM Marks;
Esto le dará el valor "18.375":
Estos resultados provienen de la suma de todos los valores de calificación divididos por su recuento.
COUNT () - COUNT (X) o COUNT (*)
Devuelve el recuento total del número de veces que apareció el valor x. Y aquí hay algunas opciones que puede usar con COUNT:
- COUNT (x): cuenta solo los valores de x, donde x es un nombre de columna. Ignorará los valores NULL.
- COUNT (*): cuenta todas las filas de todas las columnas.
- COUNT (DISTINCT x): Puede especificar una palabra clave DISTINCT antes de la x que obtendrá el recuento de los distintos valores 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 proporciona 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, TI 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 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. Se ignorarán los valores NULL.
- GROUP_CONCAT (X, Y): esto concatenará los valores de x en una cadena, con el valor de y utilizado 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. Se ignorarán los valores NULL.
Ejemplo de GROUP_CONCAT (DepartmentName)
La siguiente 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)DE Estudiantes AS sINNER JOIN Departamentos AS d ON s.DepartmentId = d.DepartmentId;
Esto te dará:
Esto le dará la lista de valores de nombres de 8 departamentos concatenados en una cadena separados por comas.
Ejemplo de GROUP_CONCAT (DISTINCT DepartmentName)
La siguiente consulta concatenará los valores distintos del nombre del departamento de la tabla de estudiantes y departamentos en una cadena separada por comas:
SELECT GROUP_CONCAT (DISTINCT d.DepartmentName)DE Estudiantes AS sINNER JOIN Departamentos 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 (DepartmentName, '&') 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, '&')DE Estudiantes AS sINNER JOIN Departamentos AS d ON s.DepartmentId = d.DepartmentId;
Esto te dará:
Observe cómo se usa 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 de X. MIN devolverá un valor NULO si todos los valores de X son nulos.
Ejemplo
En la siguiente consulta, usaremos las funciones MIN y MAX para obtener la calificación más alta y la calificación más baja de la tabla " Marcas ":
SELECCIONE MAX (Marca), MIN (Marca) DE Marcas;
Esto te dará:
SUMA de SQLite (x), total (x)
Ambos devolverán la suma de todos los valores de x. Pero son diferentes en lo siguiente:
- SUM devolverá nulo si todos los valores son nulos, pero Total devolverá 0.
- TOTAL siempre devuelve valores de coma 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 marcas en las tablas de " Marcas ":
SELECCIONE SUMA (Marca), TOTAL (Marca) DE Marcas;
Esto te dará:
Como puede ver, TOTAL siempre devuelve un punto flotante. Pero SUM devuelve un valor entero porque los valores de la columna "Marca" pueden estar en números enteros.
Diferencia entre el ejemplo SUM y TOTAL:
En la siguiente consulta mostraremos la diferencia entre SUM y TOTAL cuando obtienen la SUMA de valores NULL:
SELECCIONE SUM (Mark), TOTAL (Mark) FROM Marks DONDE 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 recopilarán (organizarán) juntas en grupos.
Para cualquier otra columna que no esté incluida en el grupo por columnas, puede usar una función agregada para ella.
Ejemplo:
La siguiente consulta le dará el número total de estudiantes presentes en cada departamento.
SELECCIONE d.DepartmentName, COUNT (s.StudentId) AS StudentsCountDE Estudiantes AS sINNER JOIN Departamentos AS d ON s.DepartmentId = d.DepartmentIdGRUPO POR d. Nombre de Departamento;
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", contará 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 solo tienen dos estudiantes en ella:
SELECCIONE d.DepartmentName, COUNT (s.StudentId) AS StudentsCountDE Estudiantes AS sINNER JOIN Departamentos AS d ON s.DepartmentId = d.DepartmentIdGRUPO POR d. Nombre de DepartamentoTENIENDO CONTEO (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 alumnos, por lo que se muestra en la salida.
Consulta y subconsulta SQLite
Dentro de cualquier consulta, puede usar otra consulta en SELECT, INSERT, DELETE, UPDATE o dentro de otra subconsulta.
Esta consulta anidada se denomina subconsulta. Veremos ahora algunos ejemplos de uso de subconsultas en la cláusula SELECT. Sin embargo, en el tutorial Modificar datos, veremos cómo podemos usar subconsultas con la instrucción INSERT, DELETE y UPDATE.
Usando subconsulta en el ejemplo de la cláusula FROM
En la siguiente consulta incluiremos una subconsulta dentro de la cláusula FROM:
SELECCIONEs.StudentName, t.MarkDE Estudiantes AS sUNIR INTERNAMENTE(SELECCIONAR StudentId, MarkDESDE Pruebas AS tINNER JOIN Marcas AS m ON t.TestId = m.TestId) EN s.StudentId = t.StudentId;
La consulta:
SELECCIONAR StudentId, MarkDESDE Pruebas AS tINNER JOIN Marcas AS m ON t.TestId = m.TestId
La consulta anterior se denomina subconsulta aquí porque está anidada dentro de la cláusula FROM. Observe que le dimos un nombre de alias "t" para que podamos hacer referencia a las columnas devueltas de él en la consulta.
Esta consulta te dará:
Entonces, en nuestro caso,
- s.StudentName se selecciona de la consulta principal que da el nombre de los estudiantes y
- t.Mark se selecciona de la subconsulta; que da las notas obtenidas por cada uno de estos alumnos
Usando subconsulta en el ejemplo de la cláusula WHERE
En la siguiente consulta incluiremos una subconsulta en la cláusula WHERE:
SELECCIONAR NombreDepartamentoDE Departamentos AS dDONDE NO EXISTE (SELECCIONE DepartmentIdDE Estudiantes AS sDONDE d.DepartmentId = s.DepartmentId);
La consulta:
SELECCIONAR ID de departamentoDE Estudiantes AS sDONDE d.DepartmentId = s.DepartmentId
La consulta anterior se denomina subconsulta aquí porque está anidada en la cláusula WHERE. La subconsulta devolverá los valores de DepartmentId que usará el operador NOT EXISTS.
Esta consulta te dará:
En la consulta anterior, hemos seleccionado el departamento que no tiene ningún alumno inscrito. Que es el departamento de "Matemáticas" de aquí.
Establecer 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 varias declaraciones SELECT en un conjunto de resultados.
UNION devolverá valores distintos. Sin embargo, UNION ALL no incluirá e 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 UNION
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:
SELECCIONE DepartmentId COMO DepartmentIdUnited DE EstudiantesUNIÓNSELECCIONE DepartmentId DE Departamentos;
Esto te dará:
La consulta devuelve solo 5 filas que son los distintos valores de identificación de departamento. Observe el primer valor que es el valor nulo.
Ejemplo de SQLite UNION ALL
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:
SELECCIONE DepartmentId COMO DepartmentIdUnited DE EstudiantesUNIÓN TODOSSELECCIONE DepartmentId DE Departamentos;
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:
INTERSECCIÓN SQLite
Devuelve los valores existentes 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 las tablas Estudiantes y Departamentos en la columna DepartmentId:
SELECCIONAR DepartmentId DE EstudiantesIntersecarseSELECCIONE DepartmentId DE Departamentos;
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 solo 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 los valores NULL y 4 se ignoraron y no se incluyeron en los valores devueltos.
EXCEPTO
Suponga que si tiene dos listas de filas, list1 y list2, y desea las filas solo de list1 que no existen en list2, puede usar 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:
SELECCIONAR ID de departamento DE DepartamentosEXCEPTOSELECCIONE DepartmentId DE Estudiantes;
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 NULO
El valor " NULL " 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 al valor " 0 " o en blanco "". Sin embargo, debido a que 0 y el valor en blanco es un valor conocido, se desconoce el valor nulo.
Los valores NULL requieren un manejo especial en SQLite, ahora veremos cómo manejar los valores NULL.
Buscar valores NULL
No puede usar el operador de igualdad normal (=) para buscar los valores nulos. Por ejemplo, la siguiente consulta busca los estudiantes que tienen un valor DepartmentId nulo:
SELECCIONE * DE Estudiantes DONDE 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 devolvió ningún resultado.
- Sin embargo, para que la consulta funcione, debe usar el operador "IS NULL" para buscar valores nulos de la siguiente manera:
SELECCIONE * DE Estudiantes DONDE DepartmentId ES NULO;
Esto te dará:
La consulta devolverá aquellos estudiantes que tengan un valor DepartmentId nulo.
- Si desea obtener esos valores que no son nulos, entonces debe usar el operador " NO ES NULO " como este:
SELECCIONE * DE Estudiantes DONDE DepartmentId NO ES NULO;
Esto te dará:
La consulta devolverá aquellos estudiantes que no tengan un valor NULL DepartmentId.
Resultados condicionales
Si tiene una lista de valores y desea seleccionar cualquiera de ellos en función de algunas condiciones. Para eso, la condición para ese valor en particular debe ser verdadera para 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 de la siguiente manera:
- "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 alguna 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 sigue:
- Puede utilizar diferentes condiciones:
CASOCUANDO condición1 ENTONCES resultado1CUANDO condición2 ENTONCES resultado2CUANDO condición3 ENTONCES resultado3… ELSE resultadonFIN
- O puede usar solo una expresión y poner diferentes valores posibles para elegir:
Expresión CASECUANDO valor1 ENTONCES resultado1CUANDO valor2 ENTONCES resultado2CUANDO valor3 ENTONCES resultado3… ELSE restulnFIN
Tenga en cuenta que la cláusula ELSE es opcional.
Ejemplo
En el siguiente ejemplo, usaremos la expresión CASE con valor NULL en la columna Id del departamento en la tabla Estudiantes para mostrar el texto 'No Departamento' como sigue:
SELECCIONENombre del estudiante,CASOCUANDO DepartmentId ES NULL ENTONCES 'No Department'ELSE DepartmentIdEND AS DepartmentIdDE Estudiantes;
- El operador CASE comprobará el valor de DepartmentId si es nulo o no.
- Si es un valor NULL, seleccionará el valor literal 'No Department' en lugar del valor DepartmentId.
- Si no es un valor nulo, seleccionará el valor de la columna DepartmentId.
Esto le dará la salida 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 dado.
Tiene una ventaja sobre las subconsultas porque se define a partir de las sentencias SQL y facilitará la lectura, el mantenimiento y la comprensión de las consultas.
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:
CON CTEnameCOMO(Instrucción SELECT)SELECCIONAR, ACTUALIZAR, INSERTAR o actualizar la declaración aquí DESDE CTE
El " CTEname " es cualquier nombre que pueda dar al CTE, puede usarlo para referirse a él más adelante. 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 siguiente ejemplo, definiremos un CTE a partir de una instrucción SELECT, y luego lo usaremos más adelante en otra consulta:
CON AllDepartmentsCOMO(SELECCIONAR DepartmentId, DepartmentNameDE Departamentos)SELECCIONEs.StudentId,s.StudentName,a.DepartmentNameDE Estudiantes AS sINNER JOIN AllDepartments COMO 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:
SELECCIONAR DepartmentId, DepartmentNameDE Departamentos
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 declaras y la reutilizas como una subconsulta. Solo la instrucción SELECT afecta el resultado de la consulta.
Esta consulta te dará:
Consultas avanzadas
Las consultas avanzadas son aquellas consultas que contienen combinaciones complejas, subconsultas y algunos agregados. En la siguiente sección veremos un ejemplo de una consulta avanzada:
De donde obtenemos el
- Los nombres de los departamentos con todos los estudiantes de cada departamento.
- Nombre de los estudiantes separados por coma y
- Mostrando el departamento que tiene al menos tres estudiantes en él
SELECCIONEd.DepartmentName,COUNT (s.StudentId) StudentsCount,GROUP_CONCAT (StudentName) COMO EstudiantesDE Departamentos AS dINNER JOIN Estudiantes AS s ON s.DepartmentId = d.DepartmentIdGRUPO POR d.DepartmentNameTENIENDO CONTAR (s.StudentId)> = 3;
Agregamos una cláusula JOIN para obtener el DepartmentName de la tabla Departments. Después de eso, agregamos una cláusula GROUP BY con dos funciones agregadas:
- "COUNT" para contar los estudiantes de cada grupo de departamento.
- GROUP_CONCAT para concatenar estudiantes para cada grupo con comas separados en una cadena.
- Después de GROUP BY, usamos la cláusula HAVING para filtrar los departamentos y seleccionar solo aquellos departamentos que tienen al menos 3 estudiantes.
El resultado será el siguiente:
Resumen:
Esta fue una introducción a la escritura de consultas SQLite y los conceptos básicos para consultar la base de datos y cómo se pueden filtrar los datos devueltos. Ahora puede escribir sus propias consultas SQLite.