SQLite Consulta: Selecionar, Onde, LIMIT, OFFSET, Contar, Agrupar por

Para escrever consultas SQL em um SQLite banco de dados, você precisa saber como funcionam as cláusulas SELECT, FROM, WHERE, GROUP BY, ORDER BY e LIMIT e como usá-las.

Durante este tutorial, você aprenderá como usar essas cláusulas e como escrever SQLite cláusulas.

Lendo dados com Select

A cláusula SELECT é a instrução principal usada para consultar um SQLite base de dados. Na cláusula SELECT, você indica o que selecionar. Mas antes da cláusula select, vamos ver de onde podemos selecionar os 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 adiante nos tutoriais.

Observe que, para todos os exemplos a seguir, você deve executar sqlite3.exe e abrir uma conexão com o banco de dados de amostra conforme flui:

Passo 1) Nesta etapa,

1. Abra Meu Computador e navegue até o seguinte diretório “C:\sqlite"E
2. Em seguida, abra “sqlite3.exe":

Passo 2) Abra o banco de dados “TutoriaisSampleDB.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:

SELECT *

Este comando selecionará todas as colunas de todas as tabelas referenciadas (ou subconsultas) na cláusula FROM. Por exemplo:

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

Isso selecionará todas as colunas das tabelas de alunos e de departamentos:

SELECIONE o nome da tabela.*

Isso selecionará todas as colunas apenas da tabela “tablename”. Por exemplo:

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

Isso selecionará todas as colunas apenas da tabela de alunos:

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 forma que usa nomes de colunas 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 'EUA', 'este é um texto de amostra',… etc.
• NULO – valor NULO.
• Current_TIME – Fornece a hora atual.
• CURRENT_DATE – isso lhe dará 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 o seguinte:

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 esta 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 apelidos

O alias é um novo nome para a coluna que permite selecionar a coluna com um novo nome. Os aliases das colunas são especificados usando a palavra-chave “AS”.

Por exemplo, se você deseja selecionar a coluna StudentName para ser retornada com “Student Name” em vez de “StudentName”, você pode fornecer um alias como este:

SELECT StudentName AS 'Student Name' FROM Students;

Isso lhe dará os nomes dos alunos com o nome “Nome do Aluno” em vez de “Nome do Aluno” assim:

Observe que o nome da coluna ainda “Nome do aluno“; a coluna StudentName continua a mesma, não muda pelo alias.

O alias não alterará o nome da coluna; apenas alterará o nome de exibição na cláusula SELECT.

Além disso, observe que a palavra-chave “AS” é opcional, você pode colocar o nome alternativo sem ela, algo assim:

SELECT StudentName 'Student Name' FROM Students;

E lhe dará exatamente o mesmo resultado da consulta anterior:

Você também pode fornecer aliases de 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:

Isto pode ser muito útil se você estiver participando de mais de uma mesa; em vez de repetir o nome completo da tabela na consulta, você pode atribuir a cada tabela um nome alternativo curto. Por exemplo, na seguinte consulta:

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

Esta consulta selecionará o nome de cada 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.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

• Demos à tabela Alunos um alias “s” e à tabela departamentos um alias “d”.
• Então, em vez de usar o nome completo da tabela, usamos seus apelidos para nos referirmos a elas.
• INNER JOIN une duas ou mais tabelas usando uma condição. Em nosso exemplo, unimos a tabela Alunos à tabela Departamentos com a coluna DepartmentId. Há também uma explicação detalhada para INNER JOIN no “SQLite Tutorial de junções”.

Isso lhe dará 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á todas as linhas das tabelas. Porém, se quiser filtrar os dados retornados, você deve adicionar uma cláusula “WHERE”.

A cláusula WHERE é usada para filtrar o conjunto de resultados retornado pelo Consulta SQL. É assim que funciona a cláusula WHERE:

• Na cláusula WHERE, você pode especificar uma “expressão”.
• Essa expressão será avaliada para cada linha retornada das tabelas especificadas na cláusula FROM.
• A expressão será avaliada como uma expressão booleana, com resultado verdadeiro, falso ou nulo.
• Então, somente 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 o conjunto de resultados 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ões e operadores.

Expressão é um ou mais valores literais ou colunas combinadas entre si por meio de um operador.

Observe que você pode usar expressões tanto na cláusula SELECT quanto na cláusula WHERE.

Nos exemplos a seguir, tentaremos as expressões e operadores tanto na cláusula select quanto 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 sequência de resultados de todos os valores literais ou colunas concatenados. Por exemplo:

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

Isso irá concatenar em um novo alias “StudentIdWithName":

• O valor literal da string “ID com nome: "
• com o valor de “Identidade estudantil”coluna e
• com o valor de “Nome do aluno” coluna

SQLite Operador CAST:

O operador CAST é usado para converter um valor de um tipo de dados para outro tipo de dados.

Por exemplo, se você tiver um valor numérico armazenado como um valor de string como este ” '12.5' ”E você deseja convertê-lo em um valor numérico, você pode usar o operador CAST para fazer isso assim“CAST('12.5' COMO 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 este “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 string, será convertido para um valor REAL.
• CAST(12.5 AS INTEGER) – o valor 12.5 é um valor decimal, será convertido para um valor inteiro. A parte decimal será truncada e se tornará 12.

SQLite Aritmética Operatores:

Pegue dois ou mais valores literais numéricos ou colunas numéricas e retorne um valor numérico. Os operadores aritméticos suportados em SQLite como:

Adição "+”- fornece a soma dos dois operandos. Subtração “-”- subtrai os dois operandos e resulta na diferença. Multiplicação “*”- o produto dos dois operandos. Lembrete (módulo) “%”- fornece o resto resultante da divisão de um operando pelo segundo operando. Divisão “/”- retorna os resultados do quociente da divisão do operando esquerdo pelo operando direito.

Exemplo:

No exemplo a seguir, tentaremos os cinco operadores aritméticos com valores numéricos literais no mesmo

cláusula de seleção:

SELECT 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 em SQLite contanto que selecionemos valores literais.

SQLite Operadores de comparação

Compare dois operandos entre si e retorne verdadeiro ou falso da seguinte forma:

"<”- retorna verdadeiro se o operando esquerdo for menor que o operando direito. "<=”- retorna verdadeiro se o operando esquerdo for menor ou igual ao operando direito. ">”- retorna verdadeiro se o operando esquerdo for maior que o operando direito. ">=”- retorna verdadeiro se o operando esquerdo for maior ou igual ao operando direito. "=e==”- retorna verdadeiro se os dois operandos forem iguais. Observe que ambos os operadores são iguais e não há diferença entre eles. "!=e<>”- retorna verdadeiro se os dois operandos não forem iguais. Observe que ambos os operadores são iguais e não há diferença entre eles.

Observe que, SQLite expressa o valor verdadeiro com 1 e o valor falso com 0.

Exemplo:

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 '<>';

Isso dará algo assim:

SQLite Operadores de correspondência de padrões

"COMO”- é usado para correspondência de padrões. Usando "Como“, você pode procurar 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, NomeDoAluno LIKE '%a%' – buscará os nomes dos alunos que contenham a letra “a” em qualquer posição da coluna StudentName.
• Começa com o padrão. Por exemplo, "NomeDoAluno LIKE 'a%'” – pesquise os nomes dos alunos que começam com a letra “a”.
• Termina com o padrão. Por exemplo, "NomeAluno LIKE '%a'” – Pesquise os nomes dos alunos que terminam com a letra “a”.
• Corresponder qualquer caractere único em uma string usando a letra sublinhada “_”. Por exemplo, "Nome do Aluno COMO 'J___'” – Pesquise nomes de alunos com 4 caracteres. Deve começar com a letra “J” e pode ter mais três caracteres após a letra “J”.

Exemplos de correspondência de padrões:

1. Obtenha nomes de alunos que começam com a letra 'j':

   SELECT StudentName FROM Students WHERE StudentName LIKE 'j%';

   Resultado:

   

2. Faça com que os nomes dos alunos terminem com a letra 'y':

   SELECT StudentName FROM Students WHERE StudentName LIKE '%y';

   Resultado:

   

3. Obtenha os nomes dos alunos que contenham a letra 'n':

   SELECT StudentName FROM Students WHERE StudentName LIKE '%n%';

   Resultado:

   

“GLOBO” – é 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:

SELECT 'Jack' GLOB 'j%';
SELECT 'Jack' LIKE '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 diferencia maiúsculas de minúsculas, então 'j' é igual a 'J'.

Outros operadores:

SQLite E

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, retornará falso somente se todas as expressões produzirem um valor “falso”.

Exemplo:

A consulta a seguir irá procurar por alunos que tenham StudentId > 5 e StudentName comece com a letra N, os alunos retornados deverão atender às duas condições:

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

Como saída, na imagem acima, isso lhe dará apenas “Nancy”. Nancy é a única aluna que atende a ambas as condições.

SQLite OR

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 produzirem falso, retornará falso.

Exemplo:

A consulta a seguir irá procurar por alunos que tenham StudentId > 5 ou StudentName comece com a letra N, os alunos retornados deverão atender pelo menos uma das condições:

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

Isso lhe dará:

Como saída, na captura de tela acima, isso lhe dará o nome de um aluno com a letra “n” em seu 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 E 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 E Z" é equivalente a "X >= Y E X <= Z“, X deve ser maior ou igual a Y e X é menor ou igual a Z.

Exemplo:

No exemplo de consulta a seguir, escreveremos uma consulta para obter alunos com valor de Id entre 5 e 8:

SELECT *
FROM Students
WHERE StudentId BETWEEN 5 AND 8;

Isso dará apenas aos alunos com IDs 5, 6, 7 e 8:

SQLite IN

Leva um operando e uma lista de operandos. Ele retornará verdadeiro se o valor do primeiro operando for igual ao valor de um dos operandos da lista. O operador IN retorna verdadeiro (1) se a lista de operandos contiver o primeiro valor do operando dentro de seus valores. Caso contrário, retornará falso (0).

Assim: "coluna IN(x, y, z)“. Isso é equivalente a ” (col=x) ou (col=y) ou (col=z) ".

Exemplo:

A consulta a seguir selecionará apenas alunos com IDs 2, 4, 6, 8:

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

Gosto disto:

A consulta anterior fornecerá o resultado exato da consulta a seguir porque são equivalentes:

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

Ambas as consultas fornecem a saída exata. Porém, a diferença entre as duas consultas é que na primeira consulta usamos o operador “IN”. Na segunda consulta, usamos vários operadores “OR”.

O operador IN é equivalente ao uso de vários operadores OR. O "ONDE StudentId IN(2, 4, 6, 8)" é equivalente a " ONDE (StudentId = 2) OU (StudentId = 4) OU (StudentId = 6) OU (StudentId = 8);"

Gosto disto:

SQLite NÃO EM

O operando “NOT IN” é o oposto do operador IN. Mas com a mesma sintaxe; é necessário um operando e uma lista de operandos. Ele retornará verdadeiro se o valor do primeiro operando não for igual ao valor de um dos operandos da lista. ou seja, retornará verdadeiro (0) se a lista de operandos não contiver o primeiro operando. Assim: "coluna NÃO EM (x, y, z)“. Isto é equivalente a “(col<>x) AND (col<>y) AND (col<>z)".

Exemplo:

A consulta a seguir selecionará alunos com IDs diferentes de um destes IDs 2, 4, 6, 8:

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

Assim

Na consulta anterior, fornecemos o resultado exato da consulta a seguir porque são equivalentes:

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

Gosto disto:

Na captura de tela 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 além desta lista de Id's.

SQLite EXISTE

Os operadores EXISTS não aceitam nenhum operando; leva apenas uma cláusula SELECT depois dela. 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, caso o id do departamento exista na tabela alunos:

SELECT DepartmentName
FROM Departments AS d
WHERE 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á retornado. E o nome do departamento “Matemática” não será retornado porque não há aluno naquele departamento, portanto o Id do departamento não existe na tabela de alunos. É por isso que o operador EXISTS ignorou o “Matemática”departamento.

SQLite NÃO

Revsubstitui o resultado do operador precedente 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.
• NOT GLOB – Retornará verdadeiro se GLOB retornar falso e vice-versa.
• NOT EXISTS – Retornará verdadeiro se EXISTS 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 de DepartmentId que não existem na tabela departamento.

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

saída:

Somente o departamento “Matemática " será retornado. Porque o "Matemática” departamento é o único departamento que não existe na tabela de alunos.

Limitação e Ordenação

SQLite Pedido

SQLite Ordem é 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 forma:

• 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 para 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. 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 em ordem 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, e depois pelo nome do departamento, em ordem crescente:

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;

Isso lhe dará:

• SQLite primeiro ordenará todos os alunos pelo nome do departamento em ordem crescente
• Então, para cada nome de departamento, todos os alunos desse nome de departamento serão exibidos em ordem decrescente por seus nomes.

SQLite Limite:

Você pode limitar o número de linhas retornadas pela 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 começando em uma posição específica usando a cláusula OFFSET. Por exemplo, "LIMITE 4 DESVIO 4”Ignorará as primeiras 4 linhas e retornará 4 linhas começando na 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 “LIMITE 4, 4”E lhe dará os resultados exatos.

Exemplo:

No exemplo a seguir, retornaremos apenas 3 alunos a partir do ID de aluno 5 usando a consulta:

SELECT * FROM Students LIMIT 4,3;

Isso lhe dará apenas três alunos começando na linha 5. Portanto, você terá as linhas com StudentId 5, 6 e 7:

Removendo duplicatas

Se sua consulta SQL retornar valores duplicados, você pode usar o “DISTINCT”palavra-chave para remover essas duplicatas e retornar valores distintos. Você pode especificar mais de uma coluna após a chave DISTINCT funcionar.

Exemplo:

A consulta a seguir retornará “valores de nome de departamento” duplicados: Aqui temos valores duplicados com nomes TI, Física e Artes.

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

Isso fornecerá 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 exclusivos. Assim:

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

Isso fornecerá apenas três valores exclusivos para a coluna do nome do departamento:

Agregado

SQLite Agregados são funções integradas definidas em SQLite que agrupará vários valores de várias linhas em um valor.

Aqui estão os agregados suportados por SQLite:

SQLite AVG()

Devolveu 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:

SELECT AVG(Mark) FROM Marks;

Isso lhe dará o valor “18.375”:

Esses resultados vêm da soma de todos os valores das notas divididos por sua contagem.

CONTAGEM() – CONTAGEM(X) ou CONTAGEM(*)

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 valores de x, onde x é o nome de uma coluna. Ele irá ignorar 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 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 agregada GROUP_CONCAT concatena múltiplos valores em um valor com uma vírgula para separá-los. Possui as seguintes opções:

• GROUP_CONCAT(X): Isso 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 irá 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.

Exemplo de GROUP_CONCAT(NomeDepartamento)

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írgula. Então, 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írgula:

SELECT GROUP_CONCAT(d.DepartmentName)
FROM Students AS s
INNER 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írgula.

Exemplo de GROUP_CONCAT(DISTINCT NomeDepartamento)

A consulta a seguir concatenará os valores distintos do nome do departamento da tabela de alunos e departamentos em uma string separada por vírgula:

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

Isso lhe dará:

Observe como o resultado é diferente do resultado anterior; apenas três valores foram retornados, que são os nomes dos departamentos distintos, e os valores duplicados foram removidos.

GROUP_CONCAT(NomeDepartamento ,'&') 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, '&')
FROM Students AS s
INNER 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 MÁX() e MÍN()

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 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 de “marcas" mesa:

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

Isso lhe dará:

SQLite SOMA(x), Total(x)

Ambos retornarão a soma de todos os valores de 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 de x forem inteiros. No entanto, se os valores não forem inteiros, retornará um valor de ponto flutuante.

Exemplo

Na consulta a seguir usaremos SUM e total para obter a soma de todas as marcas no “marcas”tabelas:

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

Isso lhe dará:

Como você pode ver, TOTAL sempre retorna um ponto flutuante. Mas SUM retorna um valor inteiro porque os valores na coluna “Marca” podem estar em números inteiros.

Exemplo de diferença entre SUM e TOTAL:

Na consulta a seguir mostraremos a diferença entre SUM e TOTAL quando eles obtêm a SOMA dos valores NULL:

SELECT SUM(Mark), TOTAL(Mark) FROM Marks WHERE 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.

Agrupar 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 grupo por colunas, você pode usar uma função agregada 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 StudentsCount
FROM Students AS s 
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId
GROUP BY d. DepartmentName;

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ão os alunos que nele integram.

cláusula HAVING

Se quiser filtrar os grupos retornados pela cláusula GROUP BY, você pode 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 possuem apenas dois alunos:

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;

Isso lhe dará:

A cláusula HAVING COUNT(S.StudentId) = 2 filtrará os grupos retornados e retornará apenas os grupos que contêm exatamente dois alunos. No nosso caso, o departamento de Artes tem 2 alunos, por isso é exibido na saída.

SQLite Consulta e subconsulta

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 da cláusula FROM

Na consulta a seguir incluiremos uma subconsulta dentro da 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;

A pergunta:

   SELECT StudentId, Mark
   FROM Tests AS t
   INNER JOIN Marks AS 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 por ele na consulta.

Esta consulta lhe dará:

Então, no nosso caso,

• s.StudentName é selecionado na 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 da cláusula WHERE

Na consulta a seguir incluiremos uma subconsulta na cláusula WHERE:

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

A pergunta:

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

A consulta acima é chamada de subconsulta aqui porque está aninhada na cláusula WHERE. A subconsulta retornará os valores DepartmentId que serão utilizados pelo operador NOT EXISTS.

Esta consulta lhe dará:

Na consulta acima, selecionamos o departamento que não possui nenhum aluno matriculado. Que é o departamento de “Matemática” aqui.

Conjunto Operações – UNION,Intersect

SQLite suporta as seguintes operações SET:

UNIÃO E UNIÃO TODOS

Ele combina um ou mais conjuntos de resultados (um grupo de linhas) retornados de diversas 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 de UNIÃO

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:

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

Isso lhe dará:

A consulta retorna apenas 5 linhas que são valores de ID de departamento distintos. Observe o primeiro valor que é o valor nulo.

SQLite UNIÃO TODOS Exemplo

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:

SELECT DepartmentId AS DepartmentIdUnioned FROM Students
UNION ALL
SELECT 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 há 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 INTERSEÇÃO

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 DepartmentId que existem nas tabelas Alunos e Departamentos na coluna DepartmentId:

SELECT DepartmentId FROM Students
Intersect
SELECT DepartmentId FROM Departments;

Isso lhe dará:

A consulta retorna apenas três valores 1, 2 e 3. Quais são os valores que existem em ambas as tabelas.

Porém, os valores nulos 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 departamentos e não na tabela alunos.

É por isso que os valores NULL e 4 foram ignorados e não incluídos nos valores retornados.

EXCETO

Suponha que se você tiver duas listas de linhas, lista1 e lista2, e quiser apenas as linhas da lista1 que não existem 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 DepartmentId que existem na tabela de departamentos e não existem na tabela de alunos:

SELECT DepartmentId FROM Departments
EXCEPT
SELECT DepartmentId FROM Students;

Isso lhe dará:

A consulta retorna apenas o valor 4. Que é o único valor que existe na tabela departamentos e não existe na tabela alunos.

Tratamento NULO

O "NULL” valor é um valor especial em SQLite. É usado para representar um valor desconhecido ou ausente. Observe que o valor nulo é totalmente diferente de “0”ou valor “” em branco. Entretanto, como 0 e o valor em branco são valores conhecidos, o valor nulo é desconhecido.

Valores NULL requerem um tratamento especial em SQLite, veremos agora como lidar com os valores NULL.

Pesquisar valores NULL

Você não pode usar o operador de igualdade normal (=) para pesquisar valores nulos. Por exemplo, a consulta a seguir procura os alunos que possuem um valor DepartmentId nulo:

SELECT * FROM Students WHERE DepartmentId = NULL;

Esta consulta não dará nenhum resultado:

Como o valor NULL não é igual a nenhum outro valor que inclua um valor nulo, é por isso que não retornou nenhum resultado.

• No entanto, para fazer a consulta funcionar, você deve usar o "É NULO" operador para procurar valores nulos da seguinte forma:

SELECT * FROM Students WHERE DepartmentId IS NULL;

Isso lhe dará:

A consulta retornará os alunos que possuem um valor DepartmentId nulo.

• Se você deseja obter esses valores que não são nulos, você deve usar o “NÃO É NULO”operador como este:

SELECT * FROM Students WHERE DepartmentId IS NOT NULL;

Isso lhe dará:

A consulta retornará os alunos que não possuem um valor NULL DepartmentId.

Resultados condicionais

Se você tiver uma lista de valores e quiser selecionar qualquer um deles com base em algumas condições. Para isso, a condição desse valor específico deve ser verdadeira para ser selecionado.

A expressão CASE avaliará esta lista de condições para todos os valores. Se a condição for verdadeira, ele retornará esse valor.

Por exemplo, se você tiver uma coluna “Nota” e quiser selecionar um valor de texto com base no valor da nota da seguinte forma:

– “Excelente” se a nota for superior a 85 valores.

– “Muito Bom” se a nota estiver entre 70 e 85.

– “Bom” se a nota estiver entre 60 e 70.

Então 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 determinados resultados dependendo de certas condições, como a instrução if, por exemplo.

O operador CASE pode ser definido com diferentes sintaxes como segue:

1. Você pode usar diferentes condições:

CASE 
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  WHEN condition3 THEN result3
  …
  ELSE resultn
END

2. Ou você pode usar apenas uma expressão e colocar diferentes valores possíveis para escolher:

CASE expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  WHEN value3 THEN result3
  …
  ELSE restuln 
END

Observe que a cláusula ELSE é opcional.

Exemplo

No exemplo a seguir usaremos o CASO expressão com NULL valor na coluna ID do departamento na tabela Alunos para exibir o texto 'Nenhum departamento' da seguinte forma:

SELECT 
  StudentName,
  CASE 
    WHEN DepartmentId IS NULL THEN 'No Department'
    ELSE DepartmentId 
  END AS DepartmentId
FROM Students;

• O operador CASE verificará o valor do DepartmentId se é 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, selecionará o valor da coluna DepartmentId.

Isso lhe dará a saída como 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 compreender.

Uma expressão de tabela comum pode ser definida colocando a cláusula WITH na frente de instruções SELECT da seguinte forma:

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

O "CTEnome” é qualquer nome que você possa dar ao CTE, você pode usá-lo para consultá-lo posteriormente. 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 um CTE a partir de uma instrução SELECT e o usaremos posteriormente em outra 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;

Nesta consulta, definimos um CTE e demos a ele o nome “Todos os departamentos“. Este CTE foi definido a partir de uma consulta SELECT:

SELECT DepartmentId, DepartmentName
  FROM Departments

Depois de definirmos o CTE, usamos-o 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. Somente a instrução SELECT afeta a saída da consulta.

Esta consulta lhe dará:

Consultas avançadas

Consultas avançadas são aquelas que contêm junções complexas, subconsultas e algumas agregações. Na seção a seguir veremos um exemplo de consulta avançada:

Onde conseguimos,

• Nomes dos departamentos com todos os alunos de cada departamento
• Nome dos alunos separados por vírgula e
• Mostrar o departamento com pelo menos três alunos

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;

Nós adicionamos um Cadastre-se cláusula 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 de cada grupo de departamento.
• GROUP_CONCAT para concatenar alunos para cada grupo com vírgula separada em uma string.
• Após GROUP BY, utilizamos a cláusula HAVING para filtrar os departamentos e selecionar apenas aqueles departamentos que possuem pelo menos 3 alunos.

O resultado será o seguinte:

Resumo

Esta foi uma introdução à escrita SQLite consultas e os fundamentos da consulta ao banco de dados e como você pode filtrar os dados retornados. Agora você pode escrever o seu próprio SQLite consultas.