Para escrever consultas SQL em um banco de dados SQLite, você precisa saber como as cláusulas SELECT, FROM, WHERE, GROUP BY, ORDER BY e LIMIT funcionam e como usá-las.
Durante este tutorial, você aprenderá como usar essas cláusulas e como escrever cláusulas SQLite.
Neste tutorial, você aprenderá-
- Leitura de dados com Select
- Nomes e Alias
- ONDE
- Limitando e ordenando
- Removendo duplicatas
- Agregar
- Grupo POR
- Consulta e Subconsulta
- Definir operações -UNION, cruzar
- Manipulação de NULL
- Resultados condicionais
- Expressão de tabela comum
- Consultas avançadas
Leitura de dados com Select
A cláusula SELECT é a instrução principal que você usa para consultar um banco de dados SQLite. Na cláusula SELECT, você declara o que selecionar. Mas antes da cláusula select, vamos ver de onde podemos selecionar dados usando a cláusula FROM.
A cláusula FROM é usada para especificar onde você deseja selecionar os dados. Na cláusula from, você pode especificar uma ou mais tabelas ou subconsultas para selecionar os dados, como veremos mais tarde nos tutoriais.
Observe que, para todos os exemplos a seguir, você deve executar o sqlite3.exe e abrir uma conexão com o banco de dados de amostra como fluxo:
Etapa 1) Nesta etapa,
- Abra Meu Computador e navegue até o seguinte diretório " C: \ sqlite " e
- Em seguida, abra " sqlite3.exe ":
Etapa 2) Abra o banco de dados " TutorialsSampleDB.db " pelo seguinte comando:
Agora você está pronto para executar qualquer tipo de consulta no banco de dados.
Na cláusula SELECT, você pode selecionar não apenas um nome de coluna, mas também tem muitas outras opções para especificar o que selecionar. Como segue:
SELECIONE *
Este comando selecionará todas as colunas de todas as tabelas referenciadas (ou subconsultas) na cláusula FROM. Por exemplo:
SELECIONE *DE AlunosINNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;
Isso selecionará todas as colunas das tabelas alunos e departamentos:
SELECT tablename. *
Isso selecionará todas as colunas apenas da tabela "tablename". Por exemplo:
SELECIONE Alunos. *DE AlunosINNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;
Isso selecionará todas as colunas da tabela de alunos apenas:
Um valor literal
Um valor literal é um valor constante que pode ser especificado na instrução select. Você pode usar valores literais normalmente da mesma maneira que usa nomes de coluna na cláusula SELECT. Esses valores literais serão exibidos para cada linha das linhas retornadas pela consulta SQL.
Aqui estão alguns exemplos de diferentes valores literais que você pode selecionar:
- Literal numérico - números em qualquer formato, como 1, 2,55, ... etc.
- Literais de string - qualquer string 'USA', 'este é um texto de amostra',… etc.
- NULL - valor NULL.
- Current_TIME - Ele lhe dará a hora atual.
- CURRENT_DATE - fornecerá a data atual.
Isso pode ser útil em algumas situações em que você precisa selecionar um valor constante para todas as linhas retornadas. Por exemplo, se você deseja selecionar todos os alunos da tabela Alunos, com uma nova coluna chamada país que contém o valor "EUA", você pode fazer isso:
SELECT *, 'USA' AS Country FROM Students;
Isso lhe dará todas as colunas dos alunos, além de uma nova coluna "País" como esta:
Observe que essa nova coluna País não é, na verdade, uma nova coluna adicionada à tabela. É uma coluna virtual, criada na consulta para exibição dos resultados e não será criada na tabela.
Nomes e Alias
O alias é um novo nome para a coluna que permite selecionar a coluna com um novo nome. Os apelidos de coluna são especificados usando a palavra-chave "AS".
Por exemplo, se você deseja selecionar a coluna StudentName a ser retornada com "Student Name" em vez de "StudentName", você pode atribuir a ela um alias como este:
SELECIONE StudentName AS 'Student Name' FROM Students;
Isso fornecerá os nomes dos alunos com o nome "Nome do aluno" em vez de "Nome do aluno" como este:
Observe que o nome da coluna ainda é " StudentName "; a coluna StudentName ainda é a mesma, não muda pelo alias.
O alias não mudará o nome da coluna; ele apenas mudará o nome de exibição na cláusula SELECT.
Além disso, observe que, a palavra-chave "AS" é opcional, você pode colocar o nome do alias sem ela, algo assim:
SELECT StudentName 'Student Name' FROM Students;
E ele dará a você exatamente a mesma saída da consulta anterior:
Você também pode dar nomes alternativos às tabelas, não apenas colunas. Com a mesma palavra-chave "AS". Por exemplo, você pode fazer isso:
SELECT s. * FROM Students AS s;
Isso lhe dará todas as colunas da tabela Alunos:
Isso pode ser muito útil se você estiver ingressando em mais de uma mesa; em vez de repetir o nome completo da tabela na consulta, você pode dar a cada tabela um nome alternativo curto. Por exemplo, na seguinte consulta:
SELECT Students.StudentName, Departments.DepartmentNameDE AlunosINNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;
Esta consulta selecionará cada nome de aluno da tabela "Alunos" com o nome do departamento da tabela "Departamentos":
No entanto, a mesma consulta pode ser escrita assim:
SELECT s.StudentName, d.DepartmentNameDOS Alunos AS sINNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;
- Atribuímos à tabela Alunos um apelido "s" e à tabela departamentos um apelido "d".
- Então, em vez de usar o nome completo da tabela, usamos seus apelidos para nos referir a eles.
- INNER JOIN une duas ou mais tabelas usando uma condição. Em nosso exemplo, juntamos a tabela Students à tabela Departments com a coluna DepartmentId. Há também uma explicação detalhada para INNER JOIN no tutorial "SQLite Joins".
Isso fornecerá a saída exata da consulta anterior:
ONDE
Escrever consultas SQL usando a cláusula SELECT sozinha com a cláusula FROM, como vimos na seção anterior, fornecerá a você todas as linhas das tabelas. No entanto, se você deseja filtrar os dados retornados, é necessário adicionar uma cláusula "WHERE".
A cláusula WHERE é usada para filtrar o conjunto de resultados retornado pela consulta SQL. É assim que a cláusula WHERE funciona:
- Na cláusula WHERE, você pode especificar uma "expressão".
- Essa expressão será avaliada para cada linha retornada da (s) tabela (s) especificada (s) na cláusula FROM.
- A expressão será avaliada como uma expressão booleana, com um resultado verdadeiro, falso ou nulo.
- Então, apenas as linhas para as quais a expressão foi avaliada com um valor verdadeiro serão retornadas, e aquelas com resultados falsos ou nulos serão ignoradas e não incluídas no conjunto de resultados.
- Para filtrar os resultados definidos usando a cláusula WHERE, você deve usar expressões e operadores.
Lista de operadores em SQLite e como usá-los
Na seção a seguir, explicaremos como você pode filtrar usando expressão e operadores.
Expressão é um ou mais valores literais ou colunas combinadas entre si com um operador.
Observe que você pode usar expressões na cláusula SELECT e na cláusula WHERE.
Nos exemplos a seguir, tentaremos as expressões e os operadores na cláusula select e na cláusula WHERE. Para mostrar como eles atuam.
Existem diferentes tipos de expressões e operadores que você pode especificar da seguinte maneira:
SQLite, o operador de concatenação "||"
Este operador é usado para concatenar um ou mais valores literais ou colunas entre si. Ele produzirá uma string de resultados de todos os valores literais concatenados ou colunas. Por exemplo:
SELECIONE 'Id com nome:' || StudentId || StudentName AS StudentIdWithNameFROM Students;
Isso se concatenará em um novo alias " StudentIdWithName ":
- O valor literal da string " Id com nome: "
- com o valor da coluna " StudentId " e
- com o valor da coluna " StudentName "
Operador SQLite CAST:
O operador CAST é usado para converter um valor de um tipo de dados em outro tipo de dados.
Por exemplo, se você tiver um valor numérico armazenado como um valor de string como este " '12 .5 ' " e quiser convertê-lo em um valor numérico, você pode usar o operador CAST para fazer isso assim " CAST ('12 .5' AS REAL) ". Ou se você tiver um valor decimal como 12,5 e precisar obter apenas a parte inteira, poderá convertê-lo em um número inteiro como "CAST (12,5 AS INTEGER)".
Exemplo
No comando a seguir, tentaremos converter valores diferentes em outros tipos de dados:
SELECT CAST ('12 .5 'AS REAL) ToReal, CAST (12.5 AS INTEGER) AS ToInteger;
Isso lhe dará:
O resultado é o seguinte:
- CAST ('12 .5 'AS REAL) - o valor '12 .5' é um valor de string, ele será convertido para um valor REAL.
- CAST (12,5 AS INTEGER) - o valor 12,5 é um valor decimal, ele será convertido para um valor inteiro. A parte decimal será truncada e se tornará 12.
Operadores aritméticos SQLite:
Pegue dois ou mais valores literais numéricos ou colunas numéricas e retorne um valor numérico. Os operadores aritméticos suportados no SQLite são:
|
Exemplo:
No exemplo a seguir, tentaremos os cinco operadores aritméticos com valores numéricos literais no mesmo
cláusula select:
SELECIONE 25 + 6, 25-6, 25 * 6, 25% 6, 25/6;
Isso lhe dará:
Observe como usamos uma instrução SELECT sem uma cláusula FROM aqui. E isso é permitido no SQLite, desde que selecionemos valores literais.
Operadores de comparação SQLite
Compare dois operandos entre si e retorne verdadeiro ou falso da seguinte maneira:
|
Observe que o SQLite expressa o valor verdadeiro com 1 e o valor falso com 0.
Exemplo:
SELECIONE10 <6 AS '<', 10 <= 6 AS '<=',10> 6 AS '>', 10> = 6 AS '> =',10 = 6 AS '=', 10 == 6 AS '==',10! = 6 AS '! =', 10 <> 6 AS '<>';
Isso vai dar algo assim:
Operadores de correspondência de padrões SQLite
" LIKE " - é usado para correspondência de padrões. Usando " Like ", você pode pesquisar valores que correspondam a um padrão especificado usando um curinga.
O operando à esquerda pode ser um valor literal de string ou uma coluna de string. O padrão pode ser especificado da seguinte forma:
- Contém padrão. Por exemplo, StudentName LIKE '% a%' - pesquisará os nomes dos alunos que contêm a letra "a" em qualquer posição na coluna StudentName.
- Começa com o padrão. Por exemplo, " StudentName LIKE 'a%' " - pesquise os nomes dos alunos que começam com a letra "a".
- Termina com o padrão. Por exemplo, " StudentName LIKE '% a' " - Pesquise os nomes dos alunos que terminam com a letra "a".
- Correspondência de qualquer caractere único em uma string usando a letra de sublinhado "_". Por exemplo, " StudentName LIKE 'J___' " - Pesquise os nomes dos alunos com 4 caracteres de comprimento. Deve começar com a letra "J" e pode ter quaisquer outros três caracteres a mais após a letra "J".
Exemplos de correspondência de padrões:
- Obtenha os nomes dos Alunos que começam com a letra 'j':
SELECIONE StudentName FROM Students WHERE StudentName LIKE 'j%';
Resultado:
- Faça com que os nomes dos Alunos terminem com a letra 'y':
SELECIONE StudentName FROM Students WHERE StudentName LIKE '% y';
Resultado:
- Obtenha os nomes dos Alunos que contenham a letra 'n':
SELECT StudentName FROM Students WHERE StudentName LIKE '% n%';
Resultado:
"GLOB" - é equivalente ao operador LIKE, mas GLOB diferencia maiúsculas de minúsculas, ao contrário do operador LIKE. Por exemplo, os dois comandos a seguir retornarão resultados diferentes:
SELECIONE 'Jack' GLOB 'j%';SELECIONE 'Jack' como 'j%';
Isso lhe dará:
- A primeira instrução retorna 0 (falso) porque o operador GLOB diferencia maiúsculas de minúsculas, então 'j' não é igual a 'J'. No entanto, a segunda instrução retornará 1 (verdadeiro) porque o operador LIKE não faz distinção entre maiúsculas e minúsculas, então 'j' é igual a 'J'.
Outros operadores:
SQLite AND
Um operador lógico que combina uma ou mais expressões. Ele retornará verdadeiro, apenas se todas as expressões produzirem um valor "verdadeiro". No entanto, ele retornará falso apenas se todas as expressões produzirem um valor "falso".
Exemplo:
A consulta a seguir irá procurar alunos que tenham StudentId> 5 e StudentName comece com a letra N, os alunos retornados devem atender às duas condições:
SELECIONE *DE AlunosONDE (StudentId> 5) AND (StudentName LIKE 'N%');
Como saída, na imagem acima, isso mostrará apenas "Nancy". Nancy é a única aluna que atende às duas condições.
SQLite OU
Um operador lógico que combina uma ou mais expressões, de modo que, se um dos operadores combinados resultar em verdadeiro, ele retornará verdadeiro. No entanto, se todas as expressões resultarem em falso, ele retornará falso.
Exemplo:
A consulta a seguir irá procurar alunos que tenham StudentId> 5 ou StudentName que comece com a letra N, os alunos devolvidos devem atender a pelo menos uma das condições:
SELECIONE *DE AlunosONDE (StudentId> 5) OU (StudentName LIKE 'N%');
Isso lhe dará:
Como saída, na captura de tela acima, você verá o nome de um aluno com a letra "n" no nome mais o id do aluno com valor> 5.
Como você pode ver, o resultado é diferente da consulta com o operador AND.
SQLite ENTRE
BETWEEN é usado para selecionar os valores que estão dentro de um intervalo de dois valores. Por exemplo, " X ENTRE Y AND Z " retornará verdadeiro (1) se o valor X estiver entre os dois valores Y e Z. Caso contrário, retornará falso (0). " X ENTRE Y AND Z " é equivalente a " X> = Y AND X <= Z ", X deve ser maior ou igual a Y e X é menor ou igual a Z.
Exemplo:
Na consulta de exemplo a seguir, escreveremos uma consulta para obter alunos com valor de Id entre 5 e 8:
SELECIONE *DE AlunosONDE StudentId ENTRE 5 E 8;
Isso dará apenas aos alunos com ids 5, 6, 7 e 8:
SQLite IN
Obtém um operando e uma lista de operandos. Ele retornará verdadeiro se o valor do primeiro operando for igual a um dos valores dos operandos da lista. O operador IN retorna verdadeiro (1) se a lista de operandos contiver o valor do primeiro operando dentro de seus valores. Caso contrário, ele retornará falso (0).
Assim: " col IN (x, y, z) ". Isso é equivalente a " (col = x) ou (col = y) ou (col = z) ".
Exemplo:
A consulta a seguir selecionará alunos com ids 2, 4, 6, 8 apenas:
SELECIONE *DE AlunosONDE StudentId IN (2, 4, 6, 8);
Como isso:
A consulta anterior fornecerá o resultado exato como a consulta a seguir porque eles são equivalentes:
SELECIONE *DE AlunosONDE (StudentId = 2) OR (StudentId = 4) OR (StudentId = 6) OR (StudentId = 8);
Ambas as consultas fornecem a saída exata. No entanto, a diferença entre as duas consultas é que na primeira consulta usamos o operador "IN". Na segunda consulta, usamos vários operadores "OU".
O operador IN é equivalente a usar vários operadores OR. O " WHERE StudentId IN (2, 4, 6, 8) " é equivalente a " WHERE (StudentId = 2) OR (StudentId = 4) OR (StudentId = 6) OR (StudentId = 8); "
Como isso:
SQLite NÃO IN
O operando "NOT IN" é o oposto do operador IN. Mas com a mesma sintaxe; leva um operando e uma lista de operandos. Ele retornará verdadeiro se o valor do primeiro operando não for igual a um dos valores dos operandos da lista. ou seja, ele retornará verdadeiro (0) se a lista de operandos não contiver o primeiro operando. Assim: " col NOT IN (x, y, z) ". Isso é equivalente a " (col <> x) AND (col <> y) AND (col <> z) ".
Exemplo:
A consulta a seguir selecionará alunos com ids diferentes de um desses Ids 2, 4, 6, 8:
SELECIONE *DE AlunosONDE O StudentId NÃO ESTÁ EM (2, 4, 6, 8);
Como isso
A consulta anterior, damos o resultado exato como a consulta a seguir porque eles são equivalentes:
SELECIONE *DE AlunosONDE (StudentId <> 2) AND (StudentId <> 4) AND (StudentId <> 6) AND (StudentId <> 8);
Como isso:
Na imagem acima,
Usamos vários operadores diferentes "<>" para obter uma lista de alunos, que não são iguais a nenhum dos seguintes Id's 2, 4, 6 ou 8. Esta consulta retornará todos os outros alunos que não sejam essas listas de Ids.
SQLite EXISTS
Os operadores EXISTS não aceitam nenhum operando; leva apenas uma cláusula SELECT depois disso. O operador EXISTS retornará verdadeiro (1) se houver alguma linha retornada da cláusula SELECT e retornará falso (0) se não houver nenhuma linha retornada da cláusula SELECT.
Exemplo:
No exemplo a seguir, selecionaremos o nome do departamento, se o id do departamento existir na tabela de alunos:
SELECT DepartmentNameDE Departamentos AS dWHERE EXISTS (SELECT DepartmentId FROM Students AS s WHERE d.DepartmentId = s.DepartmentId);
Isso lhe dará:
Apenas os três departamentos " TI, Física e Artes " serão devolvidos. E o nome do departamento " Matemática " não será retornado porque não há aluno naquele departamento, então o ID do departamento não existe na tabela de alunos. É por isso que o operador EXISTS ignorou o departamento " Matemática ".
SQLite NOT
Reverte o resultado do operador anterior que vem depois dele. Por exemplo:
- NOT BETWEEN - Retornará verdadeiro se BETWEEN retornar falso e vice-versa.
- NOT LIKE - Retornará verdadeiro se LIKE retornar falso e vice-versa.
- NÃO GLOB - Retornará verdadeiro se GLOB retornar falso e vice-versa.
- NÃO EXISTE - Retornará verdadeiro se EXISTE retornar falso e vice-versa.
Exemplo:
No exemplo a seguir, usaremos o operador NOT com o operador EXISTS para obter os nomes dos departamentos que não existem na tabela Alunos, que é o resultado inverso do operador EXISTS. Assim, a busca será feita através do DepartmentId que não existe na tabela de departamentos.
SELECT DepartmentNameDE Departamentos AS dONDE NÃO EXISTE (SELECT DepartmentIdDOS Alunos AS sONDE d.DepartmentId = s.DepartmentId);
Produto :
Apenas o departamento " Matemática " será devolvido. Porque o departamento " Matemática " é o único departamento que não existe na tabela de alunos.
Limitando e ordenando
Ordem SQLite
Ordem SQLite é para classificar seu resultado por uma ou mais expressões. Para ordenar o conjunto de resultados, você deve usar a cláusula ORDER BY da seguinte maneira:
- Primeiro, você deve especificar a cláusula ORDER BY.
- A cláusula ORDER BY deve ser especificada no final da consulta; somente a cláusula LIMIT pode ser especificada depois dela.
- Especifique a expressão com a qual ordenar os dados, esta expressão pode ser um nome de coluna ou uma expressão.
- Após a expressão, você pode especificar uma direção de classificação opcional. Tanto DESC, para ordenar os dados em ordem decrescente, ou ASC, para ordenar os dados em ordem crescente. Se você não especificou nenhum deles, os dados seriam classificados de forma crescente.
- Você pode especificar mais expressões usando "," entre si.
Exemplo
No exemplo a seguir, selecionaremos todos os alunos ordenados por seus nomes, mas em ordem decrescente, a seguir pelo nome do departamento em ordem crescente:
SELECT s.StudentName, d.DepartmentNameDOS Alunos AS sINNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentIdORDER BY d.DepartmentName ASC, s.StudentName DESC;
Isso lhe dará:
- SQLite irá primeiro ordenar todos os alunos por seus nomes de departamento em ordem crescente
- Em seguida, para cada nome de departamento, todos os alunos sob esse nome de departamento serão exibidos em ordem decrescente por seus nomes
Limite SQLite:
Você pode limitar o número de linhas retornadas por sua consulta SQL, usando a cláusula LIMIT. Por exemplo, LIMIT 10 fornecerá apenas 10 linhas e ignorará todas as outras linhas.
Na cláusula LIMIT, você pode selecionar um número específico de linhas a partir de uma posição específica usando a cláusula OFFSET. Por exemplo, " LIMIT 4 OFFSET 4 " irá ignorar as primeiras 4 linhas e retornará 4 linhas a partir da quinta linha, então você obterá as linhas 5,6,7 e 8.
Observe que a cláusula OFFSET é opcional, você pode escrevê-la como " LIMIT 4, 4 " e ela fornecerá os resultados exatos.
Exemplo :
No exemplo a seguir, retornaremos apenas 3 alunos começando com a id de aluno 5 usando a consulta:
SELECIONE * DE ALUNOS LIMITE 4,3;
Isso dará a você apenas três alunos começando da linha 5. Então, ele fornecerá as linhas com StudentId 5, 6 e 7:
Removendo duplicatas
Se sua consulta SQL retornar valores duplicados, você pode usar a palavra-chave " DISTINCT " para remover essas duplicatas e retornar valores distintos. Você pode especificar mais de uma coluna após o trabalho da tecla DISTINCT.
Exemplo:
A consulta a seguir retornará "valores de nome de departamento" duplicados: Aqui temos valores duplicados com os nomes TI, Física e Artes.
SELECT d.DepartmentNameDOS Alunos AS sINNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;
Isso lhe dará valores duplicados para o nome do departamento:
Observe como existem valores duplicados para o nome do departamento. Agora, usaremos a palavra-chave DISTINCT com a mesma consulta para remover essas duplicatas e obter apenas valores únicos. Como isso:
SELECT DISTINCT d.DepartmentNameDOS Alunos AS sINNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;
Isso fornecerá apenas três valores exclusivos para a coluna de nome do departamento:
Agregar
Os agregados SQLite são funções integradas definidas no SQLite que agruparão vários valores de várias linhas em um único valor.
Aqui estão os agregados suportados pelo SQLite:
SQLite AVG ()
Retornou a média de todos os valores de x.
Exemplo:
No exemplo a seguir, obteremos a nota média que os alunos obtiveram em todos os exames:
SELECIONE AVG (marca) DAS marcas;
Isso dará a você o valor "18.375":
Esses resultados vêm da soma de todos os valores das marcas divididos por sua contagem.
COUNT () - COUNT (X) ou COUNT (*)
Retorna a contagem total do número de vezes que o valor x apareceu. E aqui estão algumas opções que você pode usar com COUNT:
- COUNT (x): conta apenas os valores x, onde x é o nome de uma coluna. Ele irá ignorar os valores NULL.
- COUNT (*): conta todas as linhas de todas as colunas.
- COUNT (DISTINCT x): Você pode especificar uma palavra-chave DISTINCT antes de x que obterá a contagem dos valores distintos de x.
Exemplo
No exemplo a seguir, obteremos a contagem total de departamentos com COUNT (DepartmentId), COUNT (*) e COUNT (DISTINCT DepartmentId) e como eles são diferentes:
SELECT COUNT (DepartmentId), COUNT (DISTINCT DepartmentId), COUNT (*) FROM Students;
Isso lhe dará:
Como segue:
- COUNT (DepartmentId) fornecerá a você a contagem de todos os ids do departamento e ignorará os valores nulos.
- COUNT (DISTINCT DepartmentId) fornece valores distintos de DepartmentId, que são apenas 3. Quais são os três valores diferentes do nome do departamento. Observe que existem 8 valores de nome de departamento no nome do aluno. Mas apenas os três valores diferentes que são Matemática, TI e Física.
- COUNT (*) conta o número de linhas na tabela de alunos que são 10 linhas para 10 alunos.
GROUP_CONCAT () - GROUP_CONCAT (X) ou GROUP_CONCAT (X, Y)
A função de agregação GROUP_CONCAT concatena valores múltiplos em um valor com uma vírgula para separá-los. Possui as seguintes opções:
- GROUP_CONCAT (X): Isto irá concatenar todos os valores de x em uma string, com a vírgula "," usada como separador entre os valores. Valores NULL serão ignorados.
- GROUP_CONCAT (X, Y): Isso concatenará os valores de x em uma string, com o valor de y usado como separador entre cada valor em vez do separador padrão ','. Valores NULL também serão ignorados.
- GROUP_CONCAT (DISTINCT X): Isso concatenará todos os valores distintos de x em uma string, com a vírgula "," usada como separador entre os valores. Valores NULL serão ignorados.
GROUP_CONCAT (DepartmentName) Exemplo
A consulta a seguir concatenará todos os valores do nome do departamento dos alunos e da tabela de departamentos em uma string separada por vírgulas. Portanto, em vez de retornar uma lista de valores, um valor em cada linha. Ele retornará apenas um valor em uma linha, com todos os valores separados por vírgulas:
SELECT GROUP_CONCAT (d.DepartmentName)DOS Alunos AS sINNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;
Isso lhe dará:
Isso lhe dará a lista de valores de nomes de 8 departamentos concatenados em uma string separada por vírgulas.
GROUP_CONCAT (DISTINCT DepartmentName) Exemplo
A consulta a seguir concatenará os valores distintos do nome do departamento da tabela de alunos e departamentos em uma string separada por vírgulas:
SELECT GROUP_CONCAT (DISTINCT d.DepartmentName)DOS Alunos AS sINNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;
Isso lhe dará:
Observe como o resultado é diferente do resultado anterior; apenas três valores retornados, que são nomes de departamentos distintos, e os valores duplicados foram removidos.
GROUP_CONCAT (DepartmentName, '&') Exemplo
A consulta a seguir concatenará todos os valores da coluna de nome do departamento da tabela de alunos e departamentos em uma string, mas com o caractere '&' em vez de uma vírgula como separador:
SELECT GROUP_CONCAT (d.DepartmentName, '&')DOS Alunos AS sINNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;
Isso lhe dará:
Observe como o caractere "&" é usado em vez do caractere padrão "," para separar os valores.
SQLite MAX () e MIN ()
MAX (X) retorna o valor mais alto dos valores X. MAX retornará um valor NULL se todos os valores de x forem nulos. Enquanto MIN (X) retorna o menor valor dos valores de X. MIN retornará um valor NULL se todos os valores de X forem nulos.
Exemplo
Na consulta a seguir, usaremos as funções MIN e MAX para obter a nota mais alta e a nota mais baixa da tabela " Marcas ":
SELECT MAX (marca), MIN (marca) DAS marcas;
Isso lhe dará:
SQLite SUM (x), Total (x)
Ambos retornarão a soma de todos os valores x. Mas eles são diferentes no seguinte:
- SUM retornará nulo se todos os valores forem nulos, mas Total retornará 0.
- TOTAL sempre retorna valores de ponto flutuante. SUM retorna um valor inteiro se todos os valores x forem inteiros. No entanto, se os valores não forem inteiros, ele retornará um valor de ponto flutuante.
Exemplo
Na consulta a seguir, usaremos SUM e total para obter a soma de todas as marcas nas tabelas " Marcas ":
SELECIONE SOMA (Marca), TOTAL (Marca) DE Marcas;
Isso lhe dará:
Como você pode ver, TOTAL sempre retorna um ponto flutuante. Mas SUM retorna um valor inteiro porque os valores na coluna "Mark" podem ser inteiros.
Exemplo de diferença entre SUM e TOTAL:
Na consulta a seguir, mostraremos a diferença entre SUM e TOTAL quando eles obtiverem os valores SUM de NULL:
SELECIONE SOMA (Marca), TOTAL (Marca) DE Marcas ONDE TestId = 4;
Isso lhe dará:
Observe que não há marcas para TestId = 4, portanto, há valores nulos para esse teste. SUM retorna um valor nulo em branco, enquanto TOTAL retorna 0.
Grupo POR
A cláusula GROUP BY é usada para especificar uma ou mais colunas que serão usadas para agrupar as linhas em grupos. As linhas com os mesmos valores serão reunidas (organizadas) em grupos.
Para qualquer outra coluna que não esteja incluída no agrupamento por colunas, você pode usar uma função de agregação para ela.
Exemplo:
A consulta a seguir fornecerá o número total de alunos presentes em cada departamento.
SELECT d.DepartmentName, COUNT (s.StudentId) AS StudentsCountDOS Alunos AS sINNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentIdGRUPO POR d. Nome do departamento;
Isso lhe dará:
A cláusula GROUPBY DepartmentName agrupará todos os alunos em grupos, um para cada nome de departamento. Para cada grupo de "departamento", contará os alunos dele.
Cláusula HAVING
Se você quiser filtrar os grupos retornados pela cláusula GROUP BY, poderá especificar uma cláusula "HAVING" com expressão após GROUP BY. A expressão será usada para filtrar esses grupos.
Exemplo
Na consulta a seguir, selecionaremos os departamentos que têm apenas dois alunos:
SELECT d.DepartmentName, COUNT (s.StudentId) AS StudentsCountDOS Alunos AS sINNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentIdGRUPO POR d. Nome do departamentoTENDO CONTAGEM (s.StudentId) = 2;
Isso lhe dará:
A cláusula HAVING COUNT (S.StudentId) = 2 filtrará os grupos retornados e retornará apenas aqueles grupos que contêm exatamente dois alunos. No nosso caso, o departamento de Artes tem 2 alunos, por isso é exibido na saída.
Consulta e subconsulta SQLite
Dentro de qualquer consulta, você pode usar outra consulta em SELECT, INSERT, DELETE, UPDATE ou dentro de outra subconsulta.
Essa consulta aninhada é chamada de subconsulta. Veremos agora alguns exemplos de uso de subconsultas na cláusula SELECT. No entanto, no tutorial Modificando Dados, veremos como podemos usar subconsultas com as instruções INSERT, DELETE e UPDATE.
Usando subconsulta no exemplo de cláusula FROM
Na consulta a seguir, incluiremos uma subconsulta dentro da cláusula FROM:
SELECIONEs.StudentName, t.MarkDOS Alunos AS sJUNÇÃO INTERNA(SELECIONE StudentId, MarkDE testes AS tINNER JOIN marca como m ON t.TestId = m.TestId) ON s.StudentId = t.StudentId;
A pergunta:
SELECIONE StudentId, MarkDE testes AS tINNER JOIN marca como m ON t.TestId = m.TestId
A consulta acima é chamada de subconsulta aqui porque está aninhada dentro da cláusula FROM. Observe que demos a ele um nome alternativo "t" para que possamos nos referir às colunas retornadas dele na consulta.
Esta consulta fornecerá a você:
Então, em nosso caso,
- s.StudentName é selecionado a partir da consulta principal que fornece o nome dos alunos e
- t.Mark é selecionado na subconsulta; que dá notas obtidas por cada um desses alunos
Usando subconsulta no exemplo de cláusula WHERE
Na consulta a seguir, incluiremos uma subconsulta na cláusula WHERE:
SELECT DepartmentNameDE Departamentos AS dONDE NÃO EXISTE (SELECT DepartmentIdDOS Alunos AS sONDE d.DepartmentId = s.DepartmentId);
A pergunta:
SELECT DepartmentIdDOS Alunos AS sONDE d.DepartmentId = s.DepartmentId
A consulta acima é chamada de subconsulta aqui porque está aninhada na cláusula WHERE. A subconsulta retornará os valores de DepartmentId que serão usados pelo operador NOT EXISTS.
Esta consulta fornecerá a você:
Na consulta acima, selecionamos o departamento que não possui nenhum aluno matriculado. Qual é o departamento de "Matemática" aqui.
Set Operations - UNION, Intersect
SQLite suporta as seguintes operações SET:
UNION & UNION ALL
Ele combina um ou mais conjuntos de resultados (um grupo de linhas) retornados de várias instruções SELECT em um conjunto de resultados.
UNION retornará valores distintos. No entanto, UNION ALL não incluirá e incluirá duplicatas.
Observe que o nome da coluna será o nome da coluna especificado na primeira instrução SELECT.
Exemplo UNION
No exemplo a seguir, obteremos a lista de DepartmentId da tabela de alunos e a lista de DepartmentId da tabela de departamentos na mesma coluna:
SELECIONE ID de Departamento como Departamento Indicado de AlunosUNIÃOSELECT DepartmentId FROM Departments;
Isso lhe dará:
A consulta retorna apenas 5 linhas, que são os valores de id de departamento distintos. Observe o primeiro valor, que é o valor nulo.
Exemplo SQLite UNION ALL
No exemplo a seguir, obteremos a lista de DepartmentId da tabela de alunos e a lista de DepartmentId da tabela de departamentos na mesma coluna:
SELECIONE ID de Departamento como Departamento Indicado de AlunosUNION ALLSELECT DepartmentId FROM Departments;
Isso lhe dará:
A consulta retornará 14 linhas, 10 linhas da tabela de alunos e 4 da tabela de departamentos. Observe que existem duplicatas nos valores retornados. Além disso, observe que o nome da coluna foi aquele especificado na primeira instrução SELECT.
Agora, vamos ver como UNION all dará resultados diferentes se substituirmos UNION ALL por UNION:
SQLite INTERSECT
Retorna os valores existentes em ambos os conjuntos de resultados combinados. Os valores que existem em um dos conjuntos de resultados combinados serão ignorados.
Exemplo
Na consulta a seguir, selecionaremos os valores de DepartmentId que existem nas tabelas Students e Departments na coluna DepartmentId:
SELECIONE ID do departamento DE AlunosCruzarSELECT DepartmentId FROM Departments;
Isso lhe dará:
A consulta retorna apenas três valores 1, 2 e 3. Que são os valores que existem em ambas as tabelas.
No entanto, os valores nulo e 4 não foram incluídos porque o valor nulo existe apenas na tabela de alunos e não na tabela de departamentos. E o valor 4 existe na tabela de departamentos e não na tabela de alunos.
É por isso que os valores NULL e 4 foram ignorados e não incluídos nos valores retornados.
EXCETO
Suponha que se você tem duas listas de linhas, lista1 e lista2, e deseja apenas as linhas da lista1 que não existe na lista2, você pode usar a cláusula "EXCEPT". A cláusula EXCEPT compara as duas listas e retorna as linhas que existem na lista1 e não existem na lista2.
Exemplo
Na consulta a seguir, selecionaremos os valores de DepartmentId que existem na tabela de departamentos e não existem na tabela de alunos:
SELECT DepartmentId FROM DepartmentsEXCETOSELECT DepartmentId FROM Students;
Isso lhe dará:
A consulta retorna apenas o valor 4. Que é o único valor que existe na tabela de departamentos, e não existe na tabela de alunos.
Manipulação de NULL
O valor " NULL " é um valor especial no SQLite. É usado para representar um valor desconhecido ou ausente. Observe que o valor nulo é totalmente diferente de " 0 " ou o valor em branco "". Como 0 e o valor em branco são um valor conhecido, no entanto, o valor nulo é desconhecido.
Os valores NULL requerem um tratamento especial no SQLite, veremos agora como tratar os valores NULL.
Pesquisa por valores NULL
Você não pode usar o operador de igualdade normal (=) para pesquisar os valores nulos. Por exemplo, a consulta a seguir procura os alunos que têm um valor nulo de DepartmentId:
SELECT * FROM Students WHERE DepartmentId = NULL;
Esta consulta não dará nenhum resultado:
Como o valor NULL não é igual a nenhum outro valor incluído um valor nulo em si, é por isso que ele não retornou nenhum resultado.
- No entanto, para fazer a consulta funcionar, você deve usar o operador "IS NULL" para pesquisar os valores nulos da seguinte forma:
SELECT * FROM Students WHERE DepartmentId IS NULL;
Isso lhe dará:
A consulta retornará os alunos que possuem um valor nulo de DepartmentId.
- Se você deseja obter os valores que não são nulos, deve usar o operador " IS NOT NULL " como este:
SELECT * FROM Students WHERE DepartmentId IS NOT NULL;
Isso lhe dará:
A consulta retornará aqueles alunos que não têm um valor NULL DepartmentId.
Resultados condicionais
Se você tem uma lista de valores e deseja selecionar qualquer um deles com base em algumas condições. Para isso, a condição para aquele determinado valor deve ser verdadeira para ser selecionado.
A expressão CASE avaliará essa lista de condições para todos os valores. Se a condição for verdadeira, ele retornará esse valor.
Por exemplo, se você tem uma coluna "Nota" e deseja selecionar um valor de texto com base no valor da nota da seguinte forma:
- "Excelente" se a nota for superior a 85.
- "Muito bom" se a nota estiver entre 70 e 85.
- "Bom" se a nota estiver entre 60 e 70.
Em seguida, você pode usar a expressão CASE para fazer isso.
Isso pode ser usado para definir alguma lógica na cláusula SELECT para que você possa selecionar certos resultados dependendo de certas condições como a instrução if, por exemplo.
O operador CASE pode ser definido com diferentes sintaxes, conforme a seguir:
- Você pode usar diferentes condições:
CASOWHEN condição1 THEN resultado1WHEN condição2 ENTÃO resultado2WHEN condição3 THEN result3… ELSE resultnFIM
- Ou você pode usar apenas uma expressão e colocar diferentes valores possíveis para escolher:
Expressão CASEWHEN valor1 THEN resultado1WHEN valor2 ENTÃO resultado2WHEN valor3 THEN result3… ELSE restulnFIM
Observe que a cláusula ELSE é opcional.
Exemplo
No exemplo a seguir, usaremos a expressão CASE com valor NULL na coluna Id do departamento na tabela Alunos para exibir o texto 'Sem Departamento' como segue:
SELECIONENome do aluno,CASOQUANDO O ID do Departamento É NULO, 'Nenhum Departamento'ELSE DepartmentIdEND AS DepartmentIdFROM Students;
- O operador CASE verificará o valor de DepartmentId, seja nulo ou não.
- Se for um valor NULL, ele selecionará o valor literal 'No Department' em vez do valor DepartmentId.
- Se não for um valor nulo, ele selecionará o valor da coluna DepartmentId.
Isso fornecerá a saída conforme mostrado abaixo:
Expressão de tabela comum
Expressões de tabela comuns (CTEs) são subconsultas definidas dentro da instrução SQL com um determinado nome.
Ele tem uma vantagem sobre as subconsultas porque é definido a partir das instruções SQL e tornará as consultas mais fáceis de ler, manter e entender.
Uma expressão de tabela comum pode ser definida colocando a cláusula WITH na frente de uma instrução SELECT da seguinte maneira:
COM nome CTECOMO(Instrução SELECT)SELECT, UPDATE, INSERT ou update instrução aqui FROM CTE
O "nome CTE " é qualquer nome que você pode dar para o CTE, você pode usá-lo para se referir a ele mais tarde. Observe que você pode definir a instrução SELECT, UPDATE, INSERT ou DELETE em CTEs
Agora, vamos ver um exemplo de como usar CTE na cláusula SELECT.
Exemplo
No exemplo a seguir, definiremos uma CTE a partir de uma instrução SELECT e, em seguida, a usaremos mais tarde em outra consulta:
COM AllDepartmentsCOMO(SELECT DepartmentId, DepartmentNameDE Departamentos)SELECIONEs.StudentId,s.StudentName,a.DepartmentNameDOS Alunos AS sINNER JOIN AllDepartments AS a ON s.DepartmentId = a.DepartmentId;
Nesta consulta, definimos um CTE e demos a ele o nome de " AllDepartments ". Este CTE foi definido a partir de uma consulta SELECT:
SELECT DepartmentId, DepartmentNameDE Departamentos
Então, depois de definir o CTE, nós o usamos na consulta SELECT que vem depois dele.
Observe que as expressões de tabela comuns não afetam a saída da consulta. É uma forma de definir uma visão lógica ou subconsulta para reutilizá-las na mesma consulta. Expressões de tabela comuns são como uma variável que você declara e a reutiliza como uma subconsulta. Apenas a instrução SELECT afeta a saída da consulta.
Esta consulta fornecerá a você:
Consultas avançadas
Consultas avançadas são aquelas que contêm junções complexas, subconsultas e alguns agregados. Na seção a seguir, veremos um exemplo de consulta avançada:
Onde obtemos o,
- Nomes do departamento com todos os alunos de cada departamento
- Nome dos alunos separado por vírgula e
- Mostrar que o departamento tem pelo menos três alunos nele
SELECIONEd.DepartmentName,COUNT (s.StudentId) StudentsCount,GROUP_CONCAT (StudentName) AS StudentsDE Departamentos AS dINNER JOIN Students AS s ON s.DepartmentId = d.DepartmentIdGROUP BY d.DepartmentNameTENDO CONTAGEM (s.StudentId)> = 3;
Adicionamos uma cláusula JOIN para obter o DepartmentName da tabela Departments. Depois disso, adicionamos uma cláusula GROUP BY com duas funções agregadas:
- "COUNT" para contar os alunos para cada grupo de departamento.
- GROUP_CONCAT para concatenar alunos para cada grupo com vírgulas separadas em uma string.
- Depois do GROUP BY, usamos a cláusula HAVING para filtrar os departamentos e selecionar apenas os departamentos que têm pelo menos 3 alunos.
O resultado será o seguinte:
Resumo:
Esta foi uma introdução à escrita de consultas SQLite e os fundamentos de consultar o banco de dados e como você pode filtrar os dados retornados. Agora você pode escrever suas próprias consultas SQLite.