SQL para desenvolvedores da Web: Um manual abrangente

Aprenda SQL, a linguagem essencial para interagir com bancos de dados relacionais, com este manual SQL abrangente para desenvolvedores web.

SQL está em toda parte hoje em dia. Esteja você aprendendo desenvolvimento de back-end , engenharia de dados, DevOps ou ciência de dados , SQL é uma habilidade que você deseja ter em seu conjunto de ferramentas.

Este é um manual gratuito e aberto baseado em texto. Se você quiser começar, basta rolar para baixo e começar a ler. Dito isto, existem duas outras opções para acompanhar:

1. Experimente a versão interativa deste curso de SQL no Boot.dev , completa com desafios e projetos de codificação
2. Assista ao vídeo passo a passo deste curso no canal do FreeCodeCamp no YouTube (incorporado abaixo):

Índice

• Capítulo 1 Introdução
• Capítulo 2: Tabelas SQL
• Capítulo 3: Restrições
• Capítulo 4: Operações CRUD
• Capítulo 5: Consultas SQL básicas
• Capítulo 6: Como estruturar dados de retorno em SQL
• Capítulo 7: Como realizar agregações em SQL
• Capítulo 8: Subconsultas SQL
• Capítulo 9: Normalização do Banco de Dados
• Capítulo 10: Como unir tabelas em SQL
• Capítulo 11: Desempenho do Banco de Dados

Capítulo 1 Introdução

Structured Query Language, ou SQL , é a principal linguagem de programação usada para gerenciar e interagir com bancos de dados relacionais . SQL pode realizar várias operações, como criar, atualizar, ler e excluir registros em um banco de dados.

O que é uma instrução SQL Select?

Vamos escrever nossa própria instrução SQL do zero. Uma SELECTinstrução é a operação mais comum em SQL – geralmente chamada de “consulta”. SELECTrecupera dados de uma ou mais tabelas. SELECTAs instruções padrão não alteram o estado do banco de dados.

SELECT id from users;

Como selecionar um único campo

Uma SELECTinstrução começa com a palavra-chave SELECTseguida pelos campos que você deseja recuperar.

SELECT id from users;

Como selecionar vários campos

Se quiser selecionar mais de um campo, você pode especificar vários campos separados por vírgulas como este:

SELECT id, name from users;

Como selecionar todos os campos

Se quiser selecionar todos os campos de um registro, você pode usar a *sintaxe abreviada.

SELECT * from users;

Depois de especificar os campos, você precisa indicar de qual tabela deseja extrair os registros usando a frominstrução seguida do nome da tabela.

Falaremos mais sobre tabelas mais tarde, mas por enquanto você pode pensar nelas como estruturas ou objetos. Por exemplo, a userstabela pode ter 3 campos:

• id
• name
• balance

E, finalmente, todas as declarações terminam com ponto e vírgula ;.

Quais bancos de dados usam SQL?

SQL é apenas uma linguagem de consulta. Normalmente você o usa para interagir com uma tecnologia de banco de dados específica. Por exemplo:

• SQLite
• PostgreSQL
• MySQL
• BarataDB
• Oráculo

E outros.

Embora muitos bancos de dados diferentes utilizem a linguagem SQL , a maioria deles terá seu próprio dialeto . É fundamental compreender que nem todos os bancos de dados são criados iguais. Só porque um banco de dados compatível com SQL faz as coisas de uma determinada maneira, não significa que todo banco de dados compatível com SQL seguirá exatamente os mesmos padrões.

Estamos usando SQLite

Neste curso, usaremos SQLite especificamente. SQLite é ótimo para projetos incorporados, navegadores da web e projetos de brinquedo. É leve, mas tem funcionalidade limitada em comparação com PostgreSQL ou MySQL – duas das tecnologias SQL de produção mais comuns.

E farei questão de apontar para você sempre que alguma funcionalidade com a qual estamos trabalhando for exclusiva do SQLite.

NoSQL versus SQL

Ao falar sobre bancos de dados SQL, também temos que mencionar o elefante na sala: NoSQL .

Simplificando, um banco de dados NoSQL é um banco de dados que não usa SQL (Structured Query Language). Cada NoSQL normalmente possui sua própria maneira de escrever e executar consultas. Por exemplo, o MongoDB usa MQL (MongoDB Query Language) e o ElasticSearch simplesmente possui uma API JSON.

Embora a maioria dos bancos de dados relacionais sejam bastante semelhantes, os bancos de dados NoSQL tendem a ser bastante exclusivos e são usados ​​para propósitos mais específicos. Algumas das principais diferenças entre um banco de dados SQL e NoSQL são:

1. Os bancos de dados NoSQL geralmente não são relacionais, os bancos de dados SQL geralmente são relacionais (falaremos mais sobre o que isso significa mais tarde).
2. Os bancos de dados SQL geralmente possuem um esquema definido, os bancos de dados NoSQL geralmente possuem um esquema dinâmico.
3. Os bancos de dados SQL são baseados em tabelas, os bancos de dados NoSQL têm uma variedade de métodos de armazenamento diferentes, como documento, valor-chave, gráfico, coluna larga e muito mais.

Tipos de bancos de dados NoSQL

• Banco de dados de documentos
• Armazenamento de valor-chave
• Coluna larga
• Gráfico

Alguns dos bancos de dados NoSQL mais populares são:

• MongoDB
• Cassandra
• CouchDB
• DynamoDB
• ElasticSearch

Comparando bancos de dados SQL

Vamos nos aprofundar e falar sobre alguns dos bancos de dados SQL populares e o que os torna diferentes uns dos outros. Alguns dos bancos de dados SQL mais populares atualmente são:

• PostgreSQL
• MySQL
• Servidor SQL da Microsoft
• SQLite
• E muitos outros

Fonte: db-engines.com

Embora todos esses bancos de dados usem SQL, cada banco de dados define regras, práticas e estratégias específicas que os separam de seus concorrentes.

SQLite versus PostgreSQL

Pessoalmente, SQLite e PostgreSQL são meus favoritos da lista acima. Postgres é um banco de dados SQL muito poderoso, de código aberto e pronto para produção. SQLite é um banco de dados leve, incorporável e de código aberto. Normalmente escolho uma dessas tecnologias se estiver fazendo trabalho SQL.

SQLite é um sistema de gerenciamento de banco de dados sem servidor (SGBD) que tem a capacidade de ser executado dentro de aplicativos, enquanto o PostgreSQL usa um modelo Cliente-Servidor e requer que um servidor seja instalado e escute em uma rede, semelhante a um servidor HTTP.

Veja uma comparação completa aqui .

Novamente, neste curso trabalharemos com SQLite, um banco de dados leve e simples. Para a maioria dos servidores web backend , o PostgreSQL é uma opção mais pronta para produção, mas o SQLite é ótimo para aprendizado e para sistemas pequenos.

Capítulo 2: Tabelas SQL

A CREATE TABLEinstrução é usada para criar uma nova tabela em um banco de dados.

Como usar a CREATE TABLEdeclaração

Para criar uma tabela, use a CREATE TABLEinstrução seguida do nome da tabela e dos campos que você deseja na tabela.

CREATE TABLE employees (id INTEGER, name TEXT, age INTEGER, is_manager BOOLEAN, salary INTEGER);

Cada nome de campo é seguido por seu tipo de dados. Chegaremos aos tipos de dados em um minuto.

Também é aceitável e comum dividir a CREATE TABLEdeclaração com alguns espaços em branco como este:

CREATE TABLE employees(
    id INTEGER,
    name TEXT,
    age INTEGER,
    is_manager BOOLEAN,
    salary INTEGER
);

Como alterar tabelas

Muitas vezes precisamos alterar o esquema do nosso banco de dados sem excluí-lo e recriá-lo. Imagine se o Twitter excluísse seu banco de dados toda vez que precisasse adicionar um recurso, isso seria um desastre! Sua conta e todos os seus tweets seriam apagados diariamente.

Em vez disso, podemos usar a ALTER TABLEinstrução para fazer alterações sem excluir nenhum dado.

Como usarALTER TABLE

Com SQLite, uma ALTER TABLEinstrução permite:

1. Renomeie uma tabela ou coluna, o que você pode fazer assim:

ALTER TABLE employees
RENAME TO contractors;

ALTER TABLE contractors
RENAME COLUMN salary TO invoice;

1. ADD ou DROP uma coluna, o que você pode fazer assim:

ALTER TABLE contractors
ADD COLUMN job_title TEXT;

ALTER TABLE contractors
DROP COLUMN is_manager;

Introdução às Migrações

Uma migração de banco de dados é um conjunto de alterações em um banco de dados relacional. Na verdade, as ALTER TABLEafirmações que fizemos no último exercício foram exemplos de migrações.

As migrações são úteis na transição de um estado para outro, na correção de erros ou na adaptação de um banco de dados às alterações.

Boas migrações são alterações pequenas, incrementais e idealmente reversíveis em um banco de dados. Como você pode imaginar, ao trabalhar com bancos de dados grandes, fazer alterações pode ser assustador. Temos que ter cuidado ao escrever migrações de banco de dados para não quebrar nenhum sistema que dependa do esquema antigo do banco de dados.

Exemplo de uma migração ruim

Se um servidor back-end executar periodicamente uma consulta como SELECT * FROM peoplee executarmos uma migração de banco de dados que altere o nome da tabela de peoplepara users sem atualizar o código , o aplicativo será interrompido. Ele tentará obter dados de uma tabela que não existe mais.

Uma solução simples para esse problema seria implantar um novo código que usasse uma nova consulta:

SELECT * FROM users;

E implantaríamos esse código na produção imediatamente após a migração.

Tipos de dados SQL

SQL como linguagem pode suportar muitos tipos de dados diferentes. Mas os tipos de dados suportados pelo seu sistema de gerenciamento de banco de dados ( SGBD ) variam dependendo do banco de dados específico que você está usando.

SQLite suporta apenas os tipos mais básicos e estamos usando SQLite neste curso.

Tipos de dados SQLite

Vejamos os tipos de dados suportados pelo SQLite: e como eles são armazenados.

1. NULL- Valor nulo.
2. INTEGER- Um inteiro assinado armazenado em 0,1,2,3,4,6 ou 8 bytes.
3. REAL- Valor de ponto flutuante armazenado como um número de ponto flutuante IEEE de 64 bits .
4. TEXT- String de texto armazenada usando codificação de banco de dados como UTF-8
5. BLOB- Abreviação de objeto binário grande e normalmente usado para imagens, áudio ou outros multimídia.

Por exemplo:

CREATE TABLE employees (
    id INTEGER,
    name TEXT,
    age INTEGER,
    is_manager BOOLEAN,
    salary INTEGER
);

Valores booleanos

É importante observar que o SQLite não possui uma BOOLEANclasse de armazenamento separada. Em vez disso, os valores booleanos são armazenados como números inteiros:

• 0=false
• 1=true

Na verdade, não é tão estranho assim - afinal, os valores booleanos são apenas bits binários!

O SQLite ainda permitirá que você escreva suas consultas usando booleanexpressões e true/ falsepalavras-chave, mas converterá os booleanos em números inteiros.

Capítulo 3: Restrições

A constrainté uma regra que criamos em um banco de dados que impõe algum comportamento específico. Por exemplo, definir uma NOT NULLrestrição em uma coluna garante que a coluna não aceitará NULLvalores.

Se tentarmos inserir um NULLvalor em uma coluna com NOT NULLrestrição, a inserção falhará com uma mensagem de erro. As restrições são extremamente úteis quando precisamos garantir que certos tipos de dados existam em nosso banco de dados.

Restrição NOT NULL

A NOT NULLrestrição pode ser adicionada diretamente à CREATE TABLEinstrução.

CREATE TABLE employees(
    id INTEGER PRIMARY KEY,
    name TEXT UNIQUE,
    title TEXT NOT NULL
);

Limitação do SQLite

Em outros dialetos do SQL você pode fazer isso ADD CONSTRAINTdentro de uma ALTER TABLEinstrução. O SQLite não suporta esse recurso, portanto, quando criamos nossas tabelas, precisamos ter certeza de que especificamos todas as restrições que desejamos.

Aqui está uma lista de recursos SQL que o SQLite não implementa caso você esteja curioso.

Restrições de chave primária

Uma chave define e protege relacionamentos entre tabelas. A primary keyé uma coluna especial que identifica exclusivamente os registros em uma tabela. Cada tabela pode ter uma e apenas uma chave primária.

Sua chave primária quase sempre será a coluna “id”

É muito comum ter uma coluna nomeada idem cada tabela de um banco de dados, e essa idé a chave primária dessa tabela. Não há duas linhas nessa tabela que possam compartilhar um arquivo id.

Uma PRIMARY KEYrestrição pode ser especificada explicitamente em uma coluna para garantir a exclusividade, rejeitando qualquer inserção onde você tente criar um ID duplicado.

Restrições de chave estrangeira

As chaves estrangeiras são o que torna os bancos de dados relacionais relacionais! As chaves estrangeiras definem os relacionamentos entre as tabelas. Simplificando, a FOREIGN KEYé um campo em uma tabela que faz referência a outra tabela PRIMARY KEY.

Criando uma chave estrangeira no SQLite

A criação de um FOREIGN KEYno SQLite acontece na criação da tabela! Depois de definirmos os campos e restrições da tabela adicionamos um adicional CONSTRAINTonde definimos o FOREIGN KEYe seu REFERENCES.

Aqui está um exemplo:

CREATE TABLE departments (
    id INTEGER PRIMARY KEY,
    department_name TEXT NOT NULL
);

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    department_id INTEGER,
    CONSTRAINT fk_departments
    FOREIGN KEY (department_id)
    REFERENCES departments(id)
);

Neste exemplo, an employeetem um department_id. Deve department_idser igual ao idcampo de um registro da departmentstabela.

Esquema

Já usamos a palavra esquema algumas vezes, vamos falar sobre o que essa palavra significa. O esquema de um banco de dados descreve como os dados são organizados nele.

Tipos de dados, nomes de tabelas, nomes de campos, restrições e os relacionamentos entre todas essas entidades fazem parte do esquema de um banco de dados .

Não existe uma maneira perfeita de arquitetar um esquema de banco de dados

Ao projetar um esquema de banco de dados, normalmente não existe uma solução "correta". Fazemos o possível para escolher um conjunto sensato de tabelas, campos, restrições, etc., que atinja os objetivos do nosso projeto. Como muitas coisas na programação, diferentes designs de esquema apresentam diferentes compensações.

Como decidimos sobre uma arquitetura de esquema sensata?

Uma decisão muito importante que precisa ser tomada é decidir qual tabela armazenará o saldo do usuário! Como você pode imaginar, garantir que nossos dados sejam precisos ao lidar com dinheiro é muito importante. Queremos ser capazes de:

• Acompanhe o saldo atual de um usuário
• Veja o equilíbrio histórico em qualquer ponto do passado
• Veja um registro de quais transações alteraram o saldo ao longo do tempo

Existem muitas maneiras de abordar esse problema. Para nossa primeira tentativa, vamos tentar o esquema mais simples que atenda às necessidades do nosso projeto.

Capítulo 4: Operações CRUD em SQL

O que é CRUD?

CRUD é um acrônimo que significa CREATE,,,, e . Essas quatro operações são a base de quase todos os bancos de dados que você criará.READUPDATEDELETE

HTTP e CRUD

As operações CRUD se correlacionam perfeitamente com os métodos HTTP que você já deve ter aprendido:

• HTTP POST-CREATE
• HTTP GET-READ
• HTTP PUT-UPDATE
• HTTP DELETE-DELETE

Instrução de inserção SQL

As tabelas são bastante inúteis sem dados nelas. Em SQL podemos adicionar registros a uma tabela usando uma INSERT INTOinstrução. Ao usar uma INSERTinstrução, devemos primeiro especificar onde tableestamos inserindo o registro, seguido pela fieldstabela à qual queremos adicionar VALUES.

Aqui está um exemplo de INSERT INTOdeclaração:

INSERT INTO employees(id, name, title)
VALUES (1, 'Allan', 'Engineer');

Ciclo de vida do banco de dados HTTP CRUD

É importante compreender como os dados fluem através de uma aplicação web típica.

1. O front-end processa alguns dados da entrada do usuário - talvez um formulário seja enviado.
2. O front-end envia esses dados ao servidor por meio de uma solicitação HTTP - talvez um arquivo POST.
3. O servidor faz uma consulta SQL ao banco de dados para criar um registro associado - provavelmente usando uma INSERTinstrução.
4. Depois que o servidor processa que a consulta ao banco de dados foi bem-sucedida, ele responde ao front-end com um código de status! Esperançosamente, um código de nível 200 (sucesso)!

Entrada manual

Inserir manualmente INSERTcada registro em um banco de dados seria uma tarefa extremamente demorada! Trabalhar com SQL bruto como fazemos agora não é muito comum ao projetar sistemas backend .

Ao trabalhar com SQL em um sistema de software, como um aplicativo web de back-end, você normalmente terá acesso a uma linguagem de programação como Go ou Python .

Por exemplo, um servidor back-end escrito em Go pode usar concatenação de strings para criar instruções SQL dinamicamente, e geralmente é assim que é feito.

sqlQuery := fmt.Sprintf(`
INSERT INTO users(name, age, country_code)
VALUES ('%s', %v, %s);
`, user.Name, user.Age, user.CountryCode)

Injeção SQL

O exemplo acima é uma simplificação do que realmente acontece quando você acessa um banco de dados usando código Go. Em essência, está correto. A interpolação de strings é como os sistemas de produção acessam os bancos de dados. Dito isto, deve ser feito com cuidado para não ser uma vulnerabilidade de segurança . Falaremos mais sobre isso mais tarde!

Contar

Podemos usar uma SELECTinstrução para obter uma contagem dos registros em uma tabela. Isso pode ser muito útil quando precisamos saber quantos registros existem, mas não nos importamos particularmente com o que há neles.

Aqui está um exemplo em SQLite:

SELECT count(*) from employees;

Neste *caso, refere-se a um nome de coluna. Não nos importamos com a contagem de uma coluna específica - queremos saber o número total de registros para podermos usar o curinga (*).

Ciclo de vida do banco de dados HTTP CRUD

Falamos sobre como uma operação de “criação” flui através de uma aplicação web. Vamos falar sobre uma “leitura”.

Vamos falar através de um exemplo. Nosso gerente de produto deseja mostrar os dados do perfil na página de configurações de um usuário. Veja como poderíamos projetar essa solicitação de recurso:

1. Primeiro, a página front-end é carregada.
2. O front-end envia uma GETsolicitação HTTP para um /usersendpoint no servidor back-end.
3. O servidor recebe a solicitação.
4. O servidor usa uma SELECTinstrução para recuperar o registro do usuário da userstabela do banco de dados.
5. O servidor converte a linha de dados SQL em um JSONobjeto e os envia de volta ao front-end.

Cláusula WHERE

Para continuar aprendendo sobre as operações CRUD em SQL, precisamos aprender como tornar mais específicas as instruções que enviamos ao banco de dados. SQL aceita uma WHEREinstrução dentro de uma consulta que nos permite ser muito específicos em nossas instruções.

Se não pudéssemos especificar o registro específico que queríamos READ, UPDATEou DELETEfazer consultas a um banco de dados seria muito frustrante e ineficiente.

Usando uma cláusula WHERE

Digamos que tivéssemos mais de 9.000 registros em nossa userstabela. Freqüentemente, queremos examinar dados específicos do usuário nessa tabela sem recuperar todos os outros registros da tabela. Podemos usar uma SELECTinstrução seguida por uma WHEREcláusula para especificar quais registros recuperar. A SELECTinstrução permanece a mesma, apenas adicionamos a WHEREcláusula ao final do SELECT.

Aqui está um exemplo:

SELECT name FROM users WHERE power_level >= 9000;

Isso selecionará apenas o namecampo de qualquer usuário na userstabela WHEREcujo power_levelcampo seja maior ou igual a 9000.

Encontrando valores NULL

Você pode usar uma WHEREcláusula para filtrar valores por serem ou não NULL.

É NULO

SELECT name FROM users WHERE first_name IS NULL;

NÃO É NULO

SELECT name FROM users WHERE first_name IS NOT NULL;

EXCLUIR

Quando um usuário exclui sua conta no Twitter ou exclui um comentário em um vídeo do YouTube, esses dados precisam ser removidos de seu respectivo banco de dados.

Instrução DELETE

Uma DELETEinstrução remove um registro de uma tabela que corresponde à WHEREcláusula. Como um exemplo:

DELETE from employees
    WHERE id = 251;

Esta DELETEinstrução remove todos os registros da employeestabela que possuem um id 251!

O perigo de excluir dados

Excluir dados pode ser uma operação perigosa. Depois de removidos, os dados podem ser muito difíceis, senão impossíveis, de restaurar! Vamos falar sobre algumas maneiras comuns pelas quais os engenheiros de back-end se protegem contra a perda de dados valiosos do cliente.

Estratégia 1 – Backups

Se você estiver usando um serviço de nuvem como o Cloud SQL do GCP ou o RDS da AWS , você deve sempre ativar backups automatizados. Eles tiram um instantâneo automático de todo o seu banco de dados em algum intervalo e o mantêm por algum tempo.

Por exemplo, o banco de dados Boot.dev tem um instantâneo de backup feito diariamente e retemos esses backups por 30 dias. Se eu acidentalmente executar uma consulta que exclua dados valiosos, poderei restaurá-los a partir do backup.

Você deve ter uma estratégia de backup para bancos de dados de produção.

Estratégia 2 – Exclusões suaves

Uma "exclusão reversível" ocorre quando você não exclui dados do seu banco de dados, mas apenas "marca" os dados como excluídos.

Por exemplo, você pode definir uma deleted_atdata na linha que deseja excluir. Então, em suas consultas você ignora tudo que tem deleted_atdata definida. A ideia é que isso permita que seu aplicativo se comporte como se estivesse excluindo dados, mas você sempre pode voltar e restaurar quaisquer dados que tenham sido removidos.

Você provavelmente só deve excluir de forma reversível se tiver um motivo específico para fazer isso. Os backups automatizados devem ser “bons o suficiente” para a maioria dos aplicativos que estão interessados ​​apenas em proteção contra erros do desenvolvedor.

Atualizar consulta em SQL

Sempre que você atualiza sua foto de perfil ou altera sua senha online, você está alterando os dados em um campo de uma tabela de um banco de dados. Imagine se toda vez que você acidentalmente estragasse um Tweet no Twitter você tivesse que deletar o tweet inteiro e postar um novo em vez de apenas editá-lo...

...Bem, isso é um mau exemplo.

Declaração de atualização

A UPDATEinstrução em SQL nos permite atualizar os campos de um registro. Podemos até atualizar muitos registros dependendo de como escrevemos a declaração.

Uma UPDATEinstrução especifica a tabela que precisa ser atualizada, seguida pelos campos e seus novos valores usando a SETpalavra-chave. Por último, uma WHEREcláusula indica o(s) registro(s) a ser(em) atualizado(s).

UPDATE employees
SET job_title = 'Backend Engineer', salary = 150000
WHERE id = 251;

Mapeamento Objeto-Relacional (ORMs)

Um Mapeamento Objeto-Relacional ou ORM , para abreviar, é uma ferramenta que permite realizar operações CRUD em um banco de dados usando uma linguagem de programação tradicional. Geralmente, eles vêm na forma de uma biblioteca ou estrutura que você usaria em seu código de back-end.

O principal benefício que um ORM oferece é que ele mapeia os registros do seu banco de dados para objetos na memória. Por exemplo, em Go podemos ter uma struct que usamos em nosso código:

type User struct {
    ID int
    Name string
    IsAdmin bool
}

Esta definição de estrutura representa convenientemente uma tabela de banco de dados chamada userse uma instância da estrutura representa uma linha na tabela.

Exemplo: usando um ORM

Usando um ORM, poderemos escrever um código simples como este:

user := User{
    ID: 10,
    Name: "Lane",
    IsAdmin: false,
}

// generates a SQL statement and runs it,
// creating a new record in the users table
db.Create(user)

Exemplo: usando SQL direto

Usando SQL direto, talvez tenhamos que fazer algo um pouco mais manual:

user := User{
    ID: 10,
    Name: "Lane",
    IsAdmin: false,
}

db.Exec("INSERT INTO users (id, name, is_admin) VALUES (?, ?, ?);",
    user.ID, user.Name, user.IsAdmin)

Você deve usar um ORM?

Isso depende – um ORM normalmente troca simplicidade por controle.

Usando SQL direto, você pode aproveitar ao máximo o poder da linguagem SQL. Usando um ORM, você fica limitado por qualquer funcionalidade que o ORM possua.

Se você tiver problemas com uma consulta específica, pode ser mais difícil depurar com um ORM porque você terá que pesquisar o código e a documentação da estrutura para descobrir como as consultas subjacentes estão sendo geradas.

Eu recomendo fazer projetos nos dois sentidos para que você possa aprender sobre as vantagens e desvantagens. No final das contas, quando você trabalha em uma equipe de desenvolvedores, será uma decisão de equipe.

Capítulo 5: Consultas SQL básicas

Como usar a AScláusula em SQL

Às vezes precisamos estruturar os dados que retornamos de nossas consultas de uma maneira específica. Uma AScláusula nos permite dar um "alias" a um dado em nossa consulta. O alias existe apenas durante a consulta.

ASpalavra-chave

As consultas a seguir retornam os mesmos dados:

SELECT employee_id AS id, employee_name AS name
FROM employees;

e:

SELECT employee_id, employee_name
FROM employees;

A diferença é que os resultados da consulta com alias teriam nomes de colunas ide nameem vez de employee_ide employee_name.

Funções SQL

No final das contas, SQL é uma linguagem de programação que oferece suporte a funções. Podemos usar funções e aliases para calcular novas colunas em uma consulta. Isso é semelhante a como você pode usar fórmulas no Excel.

Função IFI

No SQLite, a IIFfunção funciona como um ternário . Por exemplo:

IIF(carA > carB, "Car a is bigger", "Car b is bigger")

Se afor maior que b, esta instrução será avaliada como string "Car a is bigger". Caso contrário, ele avalia como "Car b is bigger".

Veja como podemos usar IIF()um directivealias para adicionar uma nova coluna calculada ao nosso conjunto de resultados:

SELECT quantity,
    IIF(quantity < 10, "Order more", "In Stock") AS directive
    from products

Como usar BETWEENcomWHERE

Podemos verificar se determinados valores são betweendois números usando a WHEREcláusula de forma intuitiva. A WHEREcláusula nem sempre precisa ser usada para especificar IDs ou valores específicos. Também podemos usá-lo para ajudar a restringir nosso conjunto de resultados. Aqui está um exemplo:

SELECT employee_name, salary
FROM employees
WHERE salary BETWEEN 30000 and 60000;

Esta consulta retorna todos os funcionários namee salarycampos de quaisquer linhas onde salaryfor BETWEEN30.000 e 60.000. Também podemos consultar resultados que sejam NOT BETWEENdois valores especificados.

SELECT product_name, quantity
FROM products
WHERE quantity NOT BETWEEN 20 and 100;

Esta consulta retorna todos os nomes de produtos cuja quantidade não estava entre 20e 100. Podemos usar condicionais para tornar os resultados de nossa consulta tão específicos quanto precisarmos.

Como retornar valores distintos

Às vezes, queremos recuperar registros de uma tabela sem recuperar nenhuma duplicata.

Por exemplo, podemos querer conhecer todas as diferentes empresas nas quais nossos funcionários trabalharam anteriormente, mas não queremos ver a mesma empresa várias vezes no relatório.

SELECT DISTINCT

SQL nos oferece a DISTINCTpalavra-chave que remove registros duplicados da consulta resultante.

SELECT DISTINCT previous_company
    FROM employees;

Isso retorna apenas uma linha para cada previous_companyvalor exclusivo.

Operadores lógicos

Freqüentemente, precisamos usar várias condições para recuperar as informações exatas que desejamos. Podemos começar a estruturar consultas muito mais complexas usando múltiplas condições juntas para restringir os resultados da pesquisa da nossa consulta.

O operador lógico ANDpode ser usado para restringir ainda mais nossos conjuntos de resultados.

ANDoperador

SELECT product_name, quantity, shipment_status
    FROM products
    WHERE shipment_status = 'pending'
    AND quantity BETWEEN 0 and 10;

Isso recupera apenas registros onde ambos shipment_statusestão "pendentes" E quantityestão entre 0e 10.

Operadores de igualdade

Todos os operadores a seguir têm suporte em SQL. Este =é o principal a ter em atenção, não é ==como em muitas outras línguas.

• =
• <
• >
• <=
• >=

Por exemplo, em Python você pode comparar dois valores como este:

if name == "age"

Enquanto no SQL você faria:

WHERE name = "age"

ORoperador

Como você provavelmente já deve ter adivinhado, se o ANDoperador lógico for suportado, ORprovavelmente o operador também será suportado.

SELECT product_name, quantity, shipment_status
    FROM products
    WHERE shipment_status = 'out of stock'
    OR quantity BETWEEN 10 and 100;

Esta consulta recupera registros onde o ship_status conditionOU a quantitycondição são atendidos.

A ordem das operações é importante ao usar esses operadores.

Você pode agrupar operações lógicas com parênteses para especificar a ordem das operações .

(this AND that) OR the_other

O INoperador

Outra variação da WHEREcláusula que podemos utilizar é o INoperador. INretorna trueou falsese o primeiro operando corresponde a qualquer um dos valores do segundo operando. O INoperador é uma abreviação para múltiplas ORcondições.

Essas duas consultas são equivalentes:

SELECT product_name, shipment_status
    FROM products
    WHERE shipment_status IN ('shipped', 'preparing', 'out of stock');

SELECT product_name, shipment_status
    FROM products
    WHERE shipment_status = 'shipped'
        OR shipment_status = 'preparing'
        OR shipment_status = 'out of stock';

Esperamos que você esteja começando a ver como a consulta de dados específicos usando cláusulas SQL ajustadas ajuda a revelar insights importantes. Quanto maior uma tabela se torna, mais difícil é analisá-la sem as consultas adequadas.

A LIKEpalavra-chave

Às vezes não temos o luxo de saber exatamente o que precisamos consultar. Você já quis procurar uma música ou vídeo, mas só se lembra de parte do nome? SQL nos fornece uma opção para quando estivermos em situações LIKEassim.

A LIKEpalavra-chave permite o uso dos operadores curinga %e _. Vamos nos concentrar %primeiro.

%Operador

O %operador corresponderá a zero ou mais caracteres. Podemos usar este operador em nossa string de consulta para encontrar mais do que apenas correspondências exatas, dependendo de onde o colocamos.

Aqui estão alguns exemplos que mostram como isso funciona:

O produto começa com "banana":

SELECT * FROM products
WHERE product_name LIKE 'banana%';

O produto termina com “banana”:

SELECT * from products
WHERE product_name LIKE '%banana';

O produto contém "banana":

SELECT * from products
WHERE product_name LIKE '%banana%';

Operador de sublinhado

Conforme discutido, o %operador curinga corresponde a zero ou mais caracteres. Enquanto isso, o _operador curinga corresponde apenas a um único caractere.

SELECT * FROM products
    WHERE product_name LIKE '_oot';

A consulta acima corresponde a produtos como:

• bota
• raiz
• pé

SELECT * FROM products
    WHERE product_name LIKE '__oot';

A consulta acima corresponde a produtos como:

• atirar
• grande

Capítulo 6: Como estruturar dados de retorno em SQL

A LIMITpalavra-chave

Às vezes não queremos recuperar todos os registros de uma tabela. Por exemplo, é comum que uma tabela de banco de dados de produção tenha milhões de linhas e SELECTinserir todas elas pode travar o sistema. É aqui que a LIMITpalavra-chave entra no chat.

A LIMITpalavra-chave pode ser usada no final de uma instrução select para reduzir o número de registros retornados.

SELECT * FROM products
    WHERE product_name LIKE '%berry%'
    LIMIT 50;

A consulta acima recupera todos os registros da productstabela onde o nome contém a palavra berry. Se executarmos esta consulta no banco de dados do Facebook, é quase certo que ela retornará muitos registros.

A LIMITinstrução permite apenas que o banco de dados retorne até 50 registros correspondentes à consulta. Isso significa que se não houver muitos registros correspondentes à consulta, a LIMITinstrução não terá efeito.

A palavra-chave SQLORDER BY

SQL também nos oferece a capacidade de classificar os resultados de uma consulta usando ORDER BY. Por padrão, a ORDER BYpalavra-chave classifica os registros pelo campo fornecido em ordem crescente ou ASCabreviada. No entanto, ORDER BYtambém suporta ordem decrescente com a palavra-chave DESC.

Exemplos

Esta consulta retorna os campos name, pricee quantityda productstabela classificados priceem ordem crescente:

SELECT name, price, quantity FROM products
    ORDER BY price;

Esta consulta retorna o name, pricee quantitydos produtos encomendados pela quantidade em ordem decrescente:

SELECT name, price, quantity FROM products
    ORDER BY quantity desc;

Ordenar por e Limitar

Ao usar ORDER BYe LIMIT, a ORDER BYcláusula deve vir primeiro.

Capítulo 7: Como realizar agregações em SQL

Uma "agregação" é um valor único derivado da combinação de vários outros valores. Realizamos uma agregação anteriormente quando usamos a countinstrução para contar o número de registros em uma tabela.

Por que usar agregações?

Os dados armazenados em um banco de dados geralmente devem ser armazenados brutos . Quando precisarmos calcular alguns dados adicionais a partir dos dados brutos, podemos usar uma agregação.

countTomemos como exemplo a seguinte agregação:

SELECT COUNT(*)
FROM products
WHERE quantity = 0;

Esta consulta retorna o número de produtos que possuem um quantityvalor de 0. Poderíamos armazenar uma contagem dos produtos em uma tabela de banco de dados separada e incrementá-la/decrementá-la sempre que fizermos alterações na productstabela - mas isso seria redundante.

É muito mais simples armazenar os produtos em um único local (chamamos isso de fonte única de verdade ) e executar uma agregação quando precisamos derivar informações adicionais dos dados brutos.

A SUMfunção

A sumfunção de agregação retorna a soma de um conjunto de valores.

Por exemplo, a consulta abaixo retorna um único registro contendo um único campo. O valor retornado é igual ao salário total arrecadado por todos da employeestabela employees.

SELECT sum(salary)
FROM employees;

O que retorna:

A MAXfunção

Como seria de esperar, a maxfunção recupera o maior valor de um conjunto de valores. Por exemplo:

SELECT max(price)
FROM products

Esta consulta analisa todos os preços da productstabela e retorna o preço com o maior valor de preço. Lembre-se de que ele retorna apenas o price, não o restante do registro. Você sempre precisa especificar cada campo que deseja que uma consulta retorne.

Uma nota sobre o esquema

• O sender_idestará presente para quaisquer transações em que o usuário em questão ( user_id) esteja recebendo dinheiro (do remetente).
• O recipient_idestará presente para quaisquer transações em que o usuário em questão ( user_id) esteja enviando dinheiro (para o destinatário).

Em outras palavras, uma transação só pode ter um sender_idou um recipient_id- não ambos. A presença de um ou de outro indica se o dinheiro está entrando ou saindo da conta do usuário.

Este esquema que projetamos é apenas uma maneira de projetar um banco de dados de transações – existem outras maneiras válidas de fazer isso user_id. É o que estamos usando e mais tarde falaremos mais sobre as vantagens e desvantagens nas diferentes opções de design de banco de dados.recipient_idsender_id

A MINfunção

A minfunção funciona da mesma forma que a maxfunção, mas encontra o valor mais baixo em vez do valor mais alto.

SELECT product_name, min(price)
from products;

Esta consulta retorna os campos product_namee pricedo registro com o menor price.

A GROUP BYcláusula

Há momentos em que precisamos agrupar dados com base em valores específicos.

SQL oferece a GROUP BYcláusula que pode agrupar linhas com valores semelhantes em linhas de "resumo". Ele retorna uma linha para cada grupo. A parte interessante é que cada grupo pode ter uma função agregada aplicada a ele que opera apenas nos dados agrupados.

Exemplo deGROUP BY

Imagine que temos um banco de dados com músicas e álbuns e queremos ver quantas músicas existem em cada álbum. Podemos usar uma consulta como esta:

SELECT album_id, count(song_id)
FROM songs
GROUP BY album_id;

Esta consulta recupera uma contagem de todas as músicas de cada álbum. Um registro é retornado por álbum e cada um deles tem seu próprio arquivo count.

A AVG()função

Assim como podemos querer encontrar os valores mínimos ou máximos dentro de um conjunto de dados, às vezes precisamos saber a média !

SQL nos oferece a AVG()função. Semelhante a MAX(), AVG()calcula a média de todos os valores não NULOS.

select song_name, avg(song_length)
from songs

Esta consulta retorna a média song_lengthda songstabela.

A HAVINGcláusula

Quando precisarmos filtrar GROUP BYainda mais os resultados de uma consulta, podemos usar a HAVINGcláusula. A HAVINGcláusula especifica uma condição de pesquisa para um grupo.

A HAVINGcláusula é semelhante à WHEREcláusula, mas opera em grupos depois de terem sido agrupados, em vez de linhas antes de terem sido agrupados.

SELECT album_id, count(id) as count
FROM songs
GROUP BY album_id
HAVING count > 5;

Esta consulta retorna a album_idcontagem de suas músicas, mas apenas para álbuns com mais de 5músicas.

HAVINGversus WHEREem SQL

É bastante comum que os desenvolvedores fiquem confusos sobre a diferença entre as cláusulas HAVINGe as WHERE- afinal, elas são bastante semelhantes.

A diferença é bastante simples na realidade:

• Uma WHEREcondição é aplicada a todos os dados em uma consulta antes de serem agrupados por uma GROUP BYcláusula.
• Uma HAVINGcondição só é aplicada às linhas agrupadas retornadas após GROUP BYa aplicação de a.

Isso significa que se você deseja filtrar o resultado de uma agregação, você precisa usar HAVING. Se quiser filtrar um valor presente nos dados brutos, você deverá usar uma WHEREcláusula simples.

A ROUNDfunção

Às vezes precisamos arredondar alguns números, principalmente quando trabalhamos com os resultados de uma agregação. Podemos usar a ROUND()função para realizar o trabalho.

A round()função SQL permite que você especifique o valor que deseja arredondar e a precisão com que deseja arredondá-lo:

round(value, precision)

Se nenhuma precisão for fornecida, o SQL arredondará o valor para o valor inteiro mais próximo:

select song_name, round(avg(song_length), 1)
from songs

Esta consulta retorna a média song_lengthda songstabela, arredondada para uma única casa decimal.

Capítulo 8: Subconsultas SQL

Subconsultas

Às vezes, uma única consulta não é suficiente para recuperar os registros específicos de que precisamos.

É possível executar uma consulta no conjunto de resultados de outra consulta - uma consulta dentro de uma consulta! Isso é chamado de "cepção de consulta"... erm... quero dizer uma "subconsulta".

As subconsultas podem ser muito úteis em diversas situações ao tentar recuperar dados específicos que não seriam acessíveis simplesmente consultando uma única tabela.

Como recuperar dados de múltiplas tabelas

Aqui está um exemplo de subconsulta:

SELECT id, song_name, artist_id
FROM songs
WHERE artist_id IN (
    SELECT id
    FROM artists
    WHERE artist_name LIKE 'Rick%'
);

Neste banco de dados hipotético, a consulta acima seleciona todos os song_ids, song_names e artist_ids da songstabela escritos por artistas cujo nome começa com "Rick". Observe que a subconsulta nos permite utilizar informações de uma tabela diferente – neste caso, a artiststabela.

Sintaxe de subconsulta

A única sintaxe exclusiva de uma subconsulta são os parênteses que cercam a consulta aninhada. O INoperador poderia ser diferente, por exemplo, poderíamos usar o =operador se esperarmos que um único valor seja retornado.

Aqui está um exemplo:

SELECT id, song_name, artist_id
FROM songs
WHERE artist_id IN (
    SELECT id
    FROM artists
    WHERE artist_name LIKE 'Rick%'
);

Não são necessárias tabelas

Ao trabalhar em um aplicativo back-end, isso não acontece com frequência, mas é importante lembrar que SQL é uma linguagem de programação completa . Geralmente o usamos para interagir com dados armazenados em tabelas, mas é bastante flexível e poderoso.

Por exemplo, você pode obter SELECTinformações que são calculadas de forma simples, sem a necessidade de tabelas.

SELECT 5 + 10 as sum;

Capítulo 9: Normalização do Banco de Dados

Relacionamentos de Tabela

Os bancos de dados relacionais são poderosos devido aos relacionamentos entre as tabelas. Esses relacionamentos nos ajudam a manter nossos bancos de dados limpos e eficientes.

Um relacionamento entre tabelas pressupõe que uma dessas tabelas tenha um valor foreign keyque faça referência a primary keyoutra tabela.

@youtube _

Tipos de relacionamentos

Existem 3 tipos principais de relacionamentos em um banco de dados relacional:

1. Um a um
2. Um para muitos
3. Muitos para muitos

Um a um

Um one-to-onerelacionamento geralmente se manifesta como um campo ou conjunto de campos em uma linha de uma tabela. Por exemplo, a userterá exatamente um password.

Os campos de configurações podem ser outro exemplo de relacionamento um-para-um. Um usuário terá exatamente um email_preferencee exatamente um arquivo birthday.

Um para muitos

Ao falar sobre relacionamentos entre tabelas, um relacionamento um-para-muitos é provavelmente o relacionamento mais comumente usado.

Um relacionamento um-para-muitos ocorre quando um único registro em uma tabela está relacionado a potencialmente muitos registros em outra tabela.

Observe que a relação um->muitos só vai em uma direção, um registro na segunda tabela não pode ser relacionado a vários registros na primeira tabela!

Exemplos de relacionamentos um para muitos

• Uma customersmesa e uma ordersmesa. Cada cliente tem 0, 1ou muitos pedidos que fez.
• Uma usersmesa e uma transactionsmesa. Cada um usertem 0, 1ou muitas transações que participaram.

Muitos para muitos

Um relacionamento muitos para muitos ocorre quando vários registros em uma tabela podem estar relacionados a vários registros em outra tabela.

Exemplos de relacionamentos muitos para muitos

• Uma productsmesa e uma suppliersmesa - Os produtos podem ter 0muitos fornecedores e os fornecedores podem fornecer 0muitos produtos.
• Uma classesmesa e uma studentsmesa - Os alunos podem frequentar potencialmente muitas aulas e as aulas podem ter muitos alunos matriculados.

Unindo tabelas

A união de tabelas ajuda a definir relacionamentos muitos-para-muitos entre dados em um banco de dados. Por exemplo, ao definir o relacionamento acima entre produtos e fornecedores, definiríamos uma tabela de junção chamada products_suppliersque contém as chaves primárias das tabelas a serem unidas.

Então, quando quisermos ver se um fornecedor fornece um produto específico, podemos olhar na tabela de junção para ver se os ids compartilham uma linha.

Restrições exclusivas em 2 campos

Ao impor restrições de esquema específicas, talvez seja necessário impor a UNIQUErestrição em dois campos diferentes.

CREATE TABLE product_suppliers (
  product_id INTEGER,
  supplier_id INTEGER,
  UNIQUE(product_id, supplier_id)
);

Isso garante que possamos ter várias linhas com o mesmo product_idou supplier_id, mas não podemos ter duas linhas em que product_ide supplier_idsejam iguais.

Normalização de banco de dados

A normalização do banco de dados é um método para estruturar o esquema do seu banco de dados de uma forma que ajuda:

• Melhore a integridade dos dados
• Reduza a redundância de dados

O que é integridade de dados?

"Integridade dos dados" refere-se à precisão e consistência dos dados. Por exemplo, se a idade de um usuário for armazenada em um banco de dados, em vez de sua data de nascimento, esses dados se tornarão incorretos automaticamente com o passar do tempo.

Seria melhor armazenar uma data de aniversário e calcular a idade conforme necessário.

O que é redundância de dados?

A "redundância de dados" ocorre quando o mesmo dado é armazenado em vários locais. Por exemplo: salvar o mesmo arquivo várias vezes em discos rígidos diferentes.

A redundância de dados pode ser problemática, especialmente quando os dados em um local são alterados de tal forma que não são mais consistentes em todas as cópias desses dados.

Formulários normais

O criador da "normalização de banco de dados", Edgar F. Codd , descreveu diferentes "formas normais" às quais um banco de dados pode aderir. Falaremos sobre os mais comuns.

• Primeira forma normal (1NF)
• Segunda forma normal (2NF)
• Terceira forma normal (3NF)
• Forma normal de Boyce-Codd (BCNF)

Resumindo, a 1ª forma normal é a forma menos "normalizada" e Boyce-Codd é a forma mais "normalizada".

Quanto mais normalizado for um banco de dados, melhor será a integridade dos dados e menos dados duplicados você terá.

No contexto dos formulários normais, “chave primária” significa algo um pouco diferente

No contexto da normalização do banco de dados, usaremos o termo “chave primária” de maneira um pouco diferente. Quando falamos sobre SQLite, uma “chave primária” é uma única coluna que identifica exclusivamente uma linha.

Quando falamos de forma mais geral sobre normalização de dados, o termo “chave primária” significa a coleção de colunas que identificam exclusivamente uma linha. Pode ser uma única coluna, mas na verdade pode ser qualquer número de colunas. Uma chave primária é o número mínimo de colunas necessárias para identificar exclusivamente uma linha em uma tabela.

Se você pensar na tabela de união muitos para muitos product_suppliers, a "chave primária" dessa tabela era na verdade uma combinação dos 2 ids product_ide supplier_id:

CREATE TABLE product_suppliers (
    product_id INTEGER,
    supplier_id INTEGER,
    UNIQUE(product_id, supplier_id)
);

1ª Forma Normal (1NF)

Para estar em conformidade com a primeira forma normal , uma tabela de banco de dados precisa simplesmente seguir 2 regras:

• Deve ter uma chave primária exclusiva.
• Uma célula não pode ter uma tabela aninhada como valor (dependendo do banco de dados que você está usando, isso pode nem ser possível)

Exemplo de NOT 1ª forma normal

Esta tabela não está de acordo com 1NF. Possui duas linhas idênticas, portanto não existe uma chave primária exclusiva para cada linha.

Exemplo de 1ª forma normal

A maneira mais simples (mas não a única) de entrar na primeira forma normal é adicionar uma idcoluna exclusiva.

É importante notar que se você criar uma "chave primária", garantindo que duas colunas sejam sempre "únicas juntas", isso também funcionará.

Você quase nunca deve projetar uma tabela que não esteja de acordo com 1NF

A primeira forma normal é simplesmente uma boa ideia. Nunca construí um esquema de banco de dados em que cada tabela não estivesse pelo menos na primeira forma normal.

2ª Forma Normal (2NF)

Uma tabela na segunda forma normal segue todas as regras da 1ª forma normal e uma regra adicional:

• Todas as colunas que não fazem parte da chave primária dependem de toda a chave primária, e não apenas de uma das colunas da chave primária.

Exemplo de 1ª NF, mas não de 2ª NF

Nesta tabela, a chave primária é uma combinação de first_name+ last_name.

Esta tabela não segue a 2NF. A first_initialcoluna é inteiramente dependente da first_namecoluna, tornando-a redundante.

Exemplo de 2ª forma normal

Uma maneira de converter a tabela acima para 2NF é adicionar uma nova tabela que mapeie a first_namediretamente para seu arquivo first_initial. Isso remove quaisquer duplicatas:

2NF geralmente é uma boa ideia

Você provavelmente deveria manter suas tabelas na segunda forma normal. Dito isto, existem boas razões para desviar-se dela, especialmente por razões de desempenho. A razão é que quando você consulta uma segunda tabela para obter dados adicionais, pode demorar um pouco mais.

Minha regra prática é:

Otimize primeiro a integridade e a desduplicação de dados. Se você tiver problemas de velocidade, desnormalize adequadamente.

3ª Forma Normal (3NF)

Uma tabela na 3ª forma normal segue todas as regras da 2ª forma normal e uma regra adicional:

• Todas as colunas que não fazem parte da primária dependem exclusivamente da chave primária.

Observe que isso é apenas ligeiramente diferente da segunda forma normal. Na segunda forma normal não podemos ter uma coluna completamente dependente de uma parte da chave primária, e na terceira forma normal não podemos ter uma coluna que seja inteiramente dependente de algo que não seja toda a chave primária.

Exemplo de 2ª NF, mas não de 3ª NF

Nesta tabela, a chave primária é simplesmente a idcoluna.

Esta tabela está na 2ª forma normal porque first_initialnão depende de parte da chave primária. Porém, por ser dependente da namecoluna não adere à 3ª forma normal.

Exemplo de 3ª forma normal

A maneira de converter a tabela acima para 3NF é adicionar uma nova tabela que mapeie a namediretamente para seu arquivo first_initial. Observe como esta solução é semelhante a 2NF.

3NF geralmente é uma boa ideia

A mesma regra prática se aplica à segunda e terceira formas normais.

Otimize primeiro a integridade e a desduplicação de dados aderindo ao 3NF. Se você tiver problemas de velocidade, desnormalize adequadamente.

Lembre-se da função IIF e da AScláusula.

Forma normal de Boyce-Codd (BCNF)

Uma tabela na forma normal de Boyce-Codd (criada por Raymond F Boyce e Edgar F Codd ) segue todas as regras da 3ª forma normal, mais uma regra adicional:

• Uma coluna que faz parte de uma chave primária não pode depender inteiramente de uma coluna que não faz parte dessa chave primária.

Isso só entra em ação quando há várias combinações possíveis de teclas primárias que se sobrepõem. Outro nome para isso é "chaves candidatas sobrepostas".

Somente em casos raros uma tabela na terceira forma normal não atende aos requisitos da forma normal de Boyce-Codd.

Exemplo de 3ª NF, mas não de Boyce-Codd NF

O interessante aqui é que existem 3 chaves primárias possíveis:

• release_year+sales
• release_date+sales
• name

Isso significa que, por definição, esta tabela está na 2ª e 3ª forma normal porque essas formas apenas restringem o quão dependente pode ser uma coluna que não faz parte de uma chave primária.

Esta tabela não está na forma normal de Boyce-Codd porque release_yearé inteiramente dependente de release_date.

Exemplo da forma normal de Boyce-Codd

A maneira mais fácil de corrigir a tabela em nosso exemplo é simplesmente remover os dados duplicados do arquivo release_date. Vamos fazer essa coluna release_day_and_month.

BCNF geralmente é uma boa ideia

A mesma regra prática se aplica às formas normais 2ª, 3ª e Boyce-Codd. Dito isto, é improvável que você veja problemas específicos do BCNF na prática.

Otimize primeiro a integridade e a desduplicação de dados, aderindo ao formato normal de Boyce-Codd. Se você tiver problemas de velocidade, desnormalize adequadamente.

Revisão de normalização

Na minha opinião, as definições exatas das formas normais 1ª, 2ª, 3ª e Boyce-Codd simplesmente não são tão importantes em seu trabalho como desenvolvedor back-end.

No entanto, o que é importante é compreender os princípios básicos de integridade e redundância de dados que os formulários normais nos ensinam.

Vamos repassar algumas regras básicas que você deve guardar na memória - elas serão úteis quando você projetar bancos de dados e até mesmo apenas em entrevistas de codificação.

Regras básicas para design de banco de dados

1. Cada tabela deve sempre ter um identificador único (chave primária)
2. 90% das vezes, esse identificador exclusivo será uma única coluna chamadaid
3. Evite dados duplicados
4. Evite armazenar dados que sejam totalmente dependentes de outros dados. Em vez disso, calcule-o instantaneamente quando precisar.
5. Mantenha seu esquema o mais simples possível. Otimize primeiro para um banco de dados normalizado. Desnormalize apenas por uma questão de velocidade quando começar a ter problemas de desempenho.

Falaremos mais sobre otimização de velocidade em um capítulo posterior.

Capítulo 10: Como unir tabelas em SQL

As junções são um dos recursos mais importantes que o SQL oferece. As junções nos permitem fazer uso dos relacionamentos que estabelecemos entre nossas tabelas. Resumindo, as junções nos permitem consultar várias tabelas ao mesmo tempo.

INNER JOIN

O tipo mais simples e comum de junção em SQL é o INNER JOIN. Por padrão, um JOINcomando é um arquivo INNER JOIN.

An INNER JOINretorna todos os registros table_aque possuem registros correspondentes em table_b, conforme demonstrado pelo diagrama de Venn a seguir.

A ONcláusula

Para realizar uma junção, precisamos informar ao banco de dados quais campos devem ser “correspondidos”. A ONcláusula é usada para especificar essas colunas a serem unidas.

SELECT *
FROM employees
INNER JOIN departments 
ON employees.department_id = departments.id;

A consulta acima retorna todos os campos de ambas as tabelas. A INNERpalavra-chave não tem nada a ver com o número de colunas retornadas - ela afeta apenas o número de linhas retornadas.

Namespace em tabelas

Ao trabalhar com várias tabelas, você pode especificar em qual tabela existe um campo usando um arquivo .. Por exemplo:

table_name.column_name

SELECT students.name, classes.name
FROM students
INNER JOIN classes on classes.class_id = students.class_id;

A consulta acima retorna o namecampo da studentstabela e o namecampo da classestabela.

LEFT JOIN

A LEFT JOINretornará todos os registros, table_aindependentemente de algum desses registros ter ou não uma correspondência em table_b. Uma junção à esquerda também retornará quaisquer registros correspondentes de table_b.

Aqui está um diagrama de Venn para ajudar a visualizar o efeito de um LEFT JOIN.

Um pequeno truque que você pode fazer para facilitar a escrita da consulta SQL é definir um alias para cada tabela. Aqui está um exemplo:

SELECT e.name, d.name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.id;

Observe as declarações simples de alias ee dfor employeese departmentsrespectivamente.

Alguns desenvolvedores fazem isso para tornar suas consultas menos detalhadas. Dito isto, eu pessoalmente odeio isso porque variáveis ​​de uma única letra são mais difíceis de entender o significado.

RIGHT JOIN

A RIGHT JOINé, como você pode esperar, o oposto de a LEFT JOIN. Ele retorna todos os registros, table_bindependentemente das correspondências, e todos os registros correspondentes entre as duas tabelas.

Restrição SQLite

SQLite não suporta junções corretas, mas muitos dialetos de SQL sim. Se você pensar bem, a RIGHT JOINé apenas um LEFT JOINcom a ordem das tabelas trocada, então não é grande coisa que o SQLite não suporte a sintaxe.

FULL JOIN

A FULL JOINcombina o conjunto de resultados dos comandos LEFT JOINe RIGHT JOIN. Ele retorna todos os registros de table_ae table_bindependentemente de terem ou não correspondências.

SQLite

Assim como RIGHT JOINs, o SQLite não suporta FULL JOINs, mas ainda é importante conhecê-los.

Capítulo 11: Desempenho do Banco de Dados

Índices SQL

Um índice é uma estrutura na memória que garante que as consultas que executamos em um banco de dados tenham bom desempenho, ou seja, sejam executadas rapidamente.

Se você aprendeu sobre estruturas de dados, a maioria dos índices de banco de dados são apenas árvores binárias . A árvore binária pode ser armazenada na memória RAM e no disco e facilita a pesquisa da localização de uma linha inteira.

PRIMARY KEYas colunas são indexadas por padrão, garantindo que você possa procurar uma linha idmuito rapidamente. Mas se você tiver outras colunas nas quais deseja fazer pesquisas rápidas, precisará indexá-las.

CREATE INDEX

CREATE INDEX index_name on table_name (column_name);

É bastante comum nomear um índice após a coluna em que ele foi criado com o sufixo _idx.

Revisão do índice

Conforme discutimos, um índice é uma estrutura de dados que pode realizar pesquisas rápidas. Ao indexar uma coluna, criamos uma nova estrutura na memória, geralmente uma árvore binária, onde os valores na coluna indexada são classificados na árvore para manter as pesquisas rápidas.

Em termos de complexidade Big-O, um índice de árvore binária garante que as pesquisas sejam O(log(n)) .

Não deveríamos indexar tudo? Podemos tornar o banco de dados ultrarrápido!

Embora os índices tornem tipos específicos de pesquisas muito mais rápidos, eles também adicionam sobrecarga de desempenho – eles podem tornar um banco de dados mais lento de outras maneiras.

Pense nisso: se você indexar todas as colunas, poderá ter centenas de árvores binárias na memória. Isso aumenta desnecessariamente o uso de memória do seu banco de dados. Isso também significa que cada vez que você insere um registro, esse registro precisa ser adicionado a muitas árvores - diminuindo a velocidade de inserção.

A regra é simples:

Adicione um índice às colunas nas quais você sabe que fará pesquisas frequentes. Deixe todo o resto sem indexação. Você sempre pode adicionar índices posteriormente.

Índices de múltiplas colunas

Índices de múltiplas colunas são úteis exatamente pelo motivo que você imagina: eles aceleram pesquisas que dependem de múltiplas colunas.

CREATE INDEX

CREATE INDEX first_name_last_name_age_idx
ON users (first_name, last_name, age);

Um índice de múltiplas colunas é classificado primeiro pela primeira coluna, pela segunda coluna a seguir e assim por diante. Uma pesquisa apenas na primeira coluna de um índice de múltiplas colunas obtém quase todas as melhorias de desempenho que obteria de seu próprio índice de coluna única. Mas as pesquisas apenas na segunda ou terceira coluna terão um desempenho muito degradado.

Regra prática

A menos que você tenha motivos específicos para fazer algo especial, adicione índices de múltiplas colunas apenas se estiver fazendo pesquisas frequentes em uma combinação específica de colunas.

Desnormalizando para velocidade

Deixei vocês com um susto no capítulo "normalização". Acontece que a integridade e a desduplicação dos dados têm um custo, e esse custo geralmente é a velocidade.

Unir tabelas, usar subconsultas, realizar agregações e executar cálculos post-hoc leva tempo. Em escalas muito grandes, essas técnicas avançadas podem, na verdade, causar um enorme impacto no desempenho de um aplicativo - às vezes paralisando o servidor de banco de dados.

Armazenar informações duplicadas pode acelerar drasticamente um aplicativo que precisa procurá-las de diferentes maneiras. Por exemplo, se você armazenar as informações do país de um usuário diretamente em seu registro de usuário, não será necessária nenhuma adesão dispendiosa para carregar sua página de perfil.

Dito isto, desnormalize por sua própria conta e risco. A desnormalização de um banco de dados acarreta um grande risco de dados imprecisos e com erros.

Na minha opinião, deveria ser usado como uma espécie de “último recurso” em nome da velocidade.

Injeção SQL

SQL é uma forma muito comum de os hackers tentarem causar danos ou violar um banco de dados. Um dos meus quadrinhos XKCD favoritos de todos os tempos demonstra o problema:

A piada aqui é que se alguém estivesse usando esta consulta:

INSERT INTO students(name) VALUES (?);

E o "nome" de um aluno era 'Robert'); DROP TABLE students;--então a consulta SQL resultante ficaria assim:

INSERT INTO students(name) VALUES ('Robert'); DROP TABLE students;--)

Como você pode ver, na verdade são 2 consultas! O primeiro insere "Robert" no banco de dados e o segundo exclui a tabela de alunos!

Como nos protegemos contra injeção de SQL?

Você precisa estar ciente dos ataques de injeção de SQL, mas, para ser honesto, a solução hoje em dia é simplesmente usar uma biblioteca SQL moderna que higienize as entradas SQL. Muitas vezes não precisamos mais higienizar insumos manualmente no nível da aplicação.

Por exemplo, os pacotes SQL da biblioteca padrão Go protegem automaticamente suas entradas contra ataques SQL se você usá-los corretamente . Resumindo, não interpole você mesmo a entrada do usuário em strings brutas - certifique-se de que sua biblioteca de banco de dados tenha uma maneira de limpar as entradas e passar esses valores brutos.

Parabéns por chegar até o fim!

Se estiver interessado em fazer as tarefas e testes de codificação interativos deste curso, você pode conferir o curso Aprenda SQL em Boot.dev

Este curso faz parte de minha carreira completa de desenvolvedor back-end, composta por outros cursos e projetos, caso você esteja interessado em conferir.

Fonte: https://www.freecodecamp.org

#sql