[ad_1]
SQL (Structured Query Language) é uma linguagem poderosa e expressiva para lidar com dados de bancos de dados relacionais, mas pode parecer assustadora para os não iniciados. Essas ‘receitas’ são exemplos de um banco de dados simples, mas os padrões que você aprende aqui podem ajudá-lo a escrever consultas precisas que farão com que você se sinta como os dados equivalentes a um Masterchef em pouco tempo.
Uma observação sobre a sintaxe: A maioria das consultas abaixo é escrita no estilo usado para Postgresql na linha de comando do psql. Diferentes mecanismos SQL podem ter diferentes estilos / comandos. A maioria das consultas abaixo deve funcionar na maioria dos mecanismos, sem ajustes, embora alguns mecanismos ou ferramentas da GUI possam exigir a omissão de aspas nos nomes de tabelas e colunas, etc.
Prato 1: retornar todos os usuários criados em um período específico
Ingredientes
Método
SELECT *FROM "Users"WHERE "created_at" > "2020-01-01"AND "created_at" < "2020-02-01";Este prato simples é um grampo versátil. Aqui estamos retornando usuários que atendem a duas condições particulares, encadeando o WHERE condições com um AND declaração. Podemos estender isso ainda mais com mais AND afirmações.
Embora o exemplo aqui seja para um período específico, a maioria das consultas exige algum tipo de condição para filtrar os dados de maneira útil.
(Novos) Ingredientes
Método
SELECT "Comments"."comment", "Users"."username"FROM "Comments"JOIN "Users"ON "Comments"."userId" = "Users"."id"WHERE "Comments"."bookId" = 1;Esta consulta assume a seguinte estrutura de tabela:
Uma das coisas que podem começar a confundir iniciantes com SQL é o uso de JOINs para encontrar dados de tabelas associadas.
O ERD (diagrama de relacionamento da entidade) acima mostra três tabelas, usuários, livros e comentários e suas associações.
Cada tabela possui um id qual é negrito no diagrama para mostrar que é a chave primária da tabela. Essa chave primária é sempre um valor exclusivo e é usada para diferenciar os registros das tabelas.
o itálico nomes de colunas userId e bookId na tabela Comentários são chaves estrangeiras, o que significa que elas são a chave primária em outras tabelas e são usadas aqui para fazer referência a essas tabelas.
Os conectores no ERD acima também mostram a natureza dos relacionamentos entre as 3 tabelas. A extremidade do ponto único no conector significa 'um' e a extremidade dividida no conector significa 'muitos'; portanto, a tabela Usuário possui um relacionamento 'um para muitos' com a tabela Comentários. Um usuário pode ter muitos comentários, por exemplo, mas um comentário só pode pertencer a um único usuário. Livros e Comentários têm o mesmo relacionamento no diagrama acima.
A consulta SQL deve fazer sentido com base no que sabemos agora. Estamos retornando apenas as colunas nomeadas, ou seja, a coluna de comentários da tabela Comentários e o nome de usuário da tabela Usuários associada (com base na chave estrangeira referenciada). No exemplo acima, restringimos a pesquisa a um único livro, novamente com base na chave estrangeira na tabela Comentários.
(Novos) Ingredientes
Método
SELECT "Users"."username", COUNT("Comments"."id") AS "CommentCount"FROM "Comments"JOIN "Users"ON "Comments"."userId" = "Users"."id"GROUP BY "Users"."id";Esta pequena consulta faz algumas coisas interessantes. O mais fácil de entender é o AS declaração. Isso nos permite renomear arbitrariamente e temporariamente as colunas nos dados retornados. Aqui renomeamos a coluna derivada, mas também é útil quando você tem vários id colunas, já que você pode renomeá-las para coisas como userId ou commentId e assim por diante.
o COUNT A instrução é uma função SQL que, como seria de esperar, conta as coisas. Aqui contamos o número de comentários associados a um usuário. Como funciona? Bem o GROUP BY é o ingrediente final importante.
Vamos imaginar brevemente uma consulta um pouco diferente:
SELECT "Users"."username", "Comments"."comment"FROM "Comments"JOIN "Users"ON "Comments"."userId" = "Users"."id";Observe, sem contagem ou agrupamento. Nós apenas queremos cada comentário e quem o fez.
A saída pode ser algo como isto:
|----------|-----------------------------|| username | comment ||----------|-----------------------------|| jackson | it's good, I liked it || jackson | this was ok, not the best || quincy | excellent read, recommended || quincy | not worth reading || quincy | I haven't read this yet |------------------------------------------Agora imagine que queríamos contar os comentários de Jackson e Quincy - fáceis de ver aqui, mas mais difíceis com um conjunto de dados maior, como você pode imaginar.
o GROUP BY A instrução essencialmente diz à consulta para tratar todas as jackson registros como um grupo e todas as quincy registros como outro. o COUNT A função conta os registros nesse grupo e retorna esse valor:
|----------|--------------|| username | CommentCount ||----------|--------------|| jackson | 2 || quincy | 3 |---------------------------(Novos) Ingredientes
Método
SELECT "Users"."username"FROM "Users"LEFT JOIN "Comments"ON "Users"."id" = "Comments"."userId"WHERE "Comments"."id" IS NULL;As várias junções podem ficar muito confusas, então não vou descompactá-las aqui. Há uma excelente repartição deles aqui: Representações visuais de junções SQL, que também responde por algumas das diferenças de sintaxe entre vários tipos ou SQL.
Vamos imaginar uma versão alternativa desta consulta rapidamente:
SELECT "Users"."username", "Comments"."id" AS "commentId"FROM "Users"LEFT JOIN "Comments"ON "Users"."id" = "Comments"."userId";Ainda temos o LEFT JOIN mas adicionamos uma coluna e removemos o WHERE cláusula.
Os dados de retorno podem ser algo como isto:
|----------|-----------|| username | commentId ||----------|-----------|| jackson | 1 || jackson | 2 || quincy | NULL || abbey | 3 |------------------------Então, Jackson é responsável pelos comentários 1 e 2, Abbey por 3 e Quincy não comentou.
A diferença entre um LEFT JOIN e um INNER JOIN (o que chamamos apenas de JOIN até agora, o que é válido) é que a junção interna mostra apenas registros onde existem valores para ambas as tabelas, enquanto uma junção esquerda retorna tudo da primeira tabela ou da esquerda (a FROM um) mesmo que não haja nada na tabela correta. Uma junção interna, portanto, mostraria apenas os registros de Jackson e Abbey.
Agora que podemos visualizar o que o LEFT JOIN retorna, é mais fácil argumentar sobre o que o WHERE...IS NULL parte faz. Retornamos apenas os usuários em que commentId é um valor nulo e, na verdade, não precisamos da coluna de valor nulo incluída na saída, portanto, sua omissão original.
(Novos) Ingredientes
- GROUP_CONCAT ou STRING_AGG
Método (MySQL)
SELECT "Users"."username", GROUP_CONCAT("Comments"."comment" SEPARATOR " | ") AS "comments"FROM "Users"JOIN "Comments"ON "Users"."id" = "Comments"."userId"GROUP BY "Users"."id";Método (Postgresql)
SELECT "Users"."username", STRING_AGG("Comments"."comment", " | ") AS "comments"FROM "Users"JOIN "Comments"ON "Users"."id" = "Comments"."userId"GROUP BY "Users"."id";Esta receita final mostra uma diferença de sintaxe para uma função semelhante em dois dos mecanismos SQL mais populares.
Aqui está um exemplo de saída que podemos esperar:
|----------|---------------------------------------------------|| username | comments ||----------|---------------------------------------------------|| jackson | it's good, I liked it | this was ok, not the best || quincy | excellent read, recommended | not worth reading |----------------------------------------------------------------Podemos ver aqui que os comentários foram agrupados e concatenados / agregados, ou seja, reunidos em um único campo de registro.
Bon apetite
Agora que você tem algumas receitas SQL para recorrer, seja criativo e sirva seus próprios pratos de dados! Eu gosto de pensar em WHERE, JOIN, COUNT, GROUP_CONCAT Enquanto o Sal, ácido gordo, calor do SQL - depois que você souber o que está fazendo com esses elementos principais, estará no caminho do domínio.
Se essa foi uma coleção útil, ou se você tem outras receitas favoritas para compartilhar, envie-me um comentário ou siga no Twitter: @JacksonBates.
[ad_2]
Fonte
