SQL - Structured Query Language

Linguagem SQL

SQL (Structured Query Language) é uma linguagem de consulta estruturada, que é usada para manipular e recuperar dados de um banco de dados relacional. SQL é uma linguagem padrão para acessar e manipular bancos de dados.

Tem como base a álgebra relacional e o cálculo relacional.

O SQL é dividido em três partes principais:

DDL (Data Definition Language) - Linguagem de Definição de Dados
Define esquemas e tabelas, chaves primárias, chaves estrangeiras, exclusão de esquemas, tabelas e colunas, alteração de tabelas.
Diz respeito à estrutura das tabelas e esquemas no DB.

DML (Data Manipulation Language) - Linguagem de Manipulação de Dados
Consulta, inserção de dados no DB, exclusão de dados, alteração de dados. Diz respeito aos dados das tabelas do BD - CRUD ¹

DCL (Data Control Language) - Linguagem de Controle de Dados
Define permissões.

Principais comandos

DDL - Definição de dados: CREATE, DROP, ALTER.
DML - Manipulação de dados: SELECT, INSERT, UPDATE, DELETE.
DCL - Controle de dados: GRANT, REVOKE.

Conceitos

DDL - Data Definition Language

DDL - Data Definition Language (Linguagem de Definição de Dados) é usada para definir a estrutura que armazenará os dados. Define a estrutura das tabelas, índices, chaves primárias, chaves estrangeiras, etc.

Propriedades

A DDL permite não só a especificação de um conjunto de relações (tabelas), como também informações acerca de cada uma das relações, incluindo:

• O esquema de cada relação (estrutura)
• O domínio dos valores associados a cada atributo (int, float, varchar, etc)
• As regras de integridade de cada uma das relações
  • O conjunto de índice para manutenção de cada uma das relações
  • Informações sobre segurança e autoridade sobre cada relação
  • A estrutura de armazenamento físico de cada relação no disco.

Criar banco de dados/esquema

Antes de qualquer tabela, é necessário criar um database (banco de dados) ou um schema (esquema). Em SQL uma base de dados (ou esquema) é identificada atravez de um nome. Os elementos do esquema incluem tabelas, restrições, etc.

Sintaxe:

CREATE DATABASE nome_do_banco;

ou

CREATE SCHEMA nome_do_esquema;

Exemplos:

CREATE SCHEMA Empresa;
CREATE SCHEMA Universidade;
CREATE DATABASE Hospital;

Tipos de domínios

Numéricos

INTEGER: É um inteiro, originado da palavra integer (em inglês).

NUMERIC(p,d): É um número de ponto fixo cuja precisão é definida pelo usuário. O número consiste de $p$ dígitos (mais o sinal), sendo que $d$ dos $p$ dígitos estão à direita do ponto decimal.
Ex. NUMERIC(4,2): 42,00

SERIAL: Números inteiros auto incrementados.

Caracteres (Strings)

CHAR(n): É uma cadeia de caracteres de tamanho fixo, com o tamanho $n$ definido pelo DBA². Abreviação de character (em inglês).
Ex. CHAR(12): Jack Sparrow

VARCHAR(n): É uma cadeia de caracteres de tamanho variável, com o tamanho $n$ definido pelo DBA. Abreviação de character varying (em inglês).

Booleano

BOOLEAN: É um tipo de dado que pode ter um dos dois valores possíveis: TRUE ou FALSE (verdadeiro ou falso).

Data/Tempo

DATE: É um tipo de dado que contém um ano (com 4 dígitos) mês e dia do mês sendo o formato "aaaa/mm/dd" o padrão do MySQL Workbench.

• Year (date) - retorna o ano de uma data
• Month (date) - retorna o mês de uma data
• Day (date) - retorna o dia de uma data

TIME: Representa um horário, com o formato "hh:mm:ss" (00:00:00).

Observações

• Uma chave estrangeira deve possuir o mesmo típo de domínio da chave primária correspondente.
• O valor nulo NULL é um membro de todos os tipos de domínio, isto é, qualquer atributo pode receber o valor NULL exceto aqueles que são chaves primárias (restrição de integridade de entidade).
• O SQL permite que a declaração de domínio de qualquer atributo inclua a especificação de NOT NULL (não nulo), proibindo assim, a inserção de um valor nulo para esse tipo de atributo (obrigatório na PK).

Criar tabela

CREATE TABLE define a estrutura de uma tabela, suas restrições de integridade e cria uma tabela vazia.

Sintaxe:

CREATE TABLE nome_tabela (...);

Exemplos:

CREATE TABLE empregado (atributo1 tipo, atributo2 tipo, ...);

CREATE DATABASE EMPRESA;
 
CREATE TABLE DEPARTAMENTO (
  CodDep SERIAL NOT NULL,
  NomeDepVARCHAR(30),
  PRIMARY KEY (CodDep)
);
 
CREATE TABLE FUNCIONARIO (
  Matricula INTEGER NOT NULL,
  Nome VARCHAR(30) NOT NULL,
  Salario NUMERIC(8,2),
  Cargo VARCHAR(15) DEFAULT 'Analista',
  Estado CHAR(2),
  Idade INTEGER, CodDepto INT,
  PRIMARY KEY (Matricula),
  FOREIGN KEY (CodDepto) references DEPARTAMENTO (CodDep) ON DELETE
  NO ACTION ON UPDATE NO ACTION
);

Criação de um código que gere códigos automáticos não e padrão SQL, mas caso seja necessario, pode-se utilizar o SERIAL na criação do campo. Muito utilizado em relações que possuiem IDs.

Exemplo:

CREATE TABLE cidade (
  id_cidade SERIAL NOT NULL,
  nome_cidade VARCHAR(100) NOT NULL,
 
  PRIMARY KEY (id_cidade)
);

Remover tabela

DROP TABLE remove todos os dados e a própria tabela, estando vazia ou não.

Sintaxe:

DROP TABLE nome_tabela;

Exemplo:

DROP TABLE empregado;

Alterar tabela

ALTER TABLE usado para alterar o esquema da tabela, permite modificar a estrutura de uma tabela existente.

Sintaxe:

ALTER TABLE nome_da_tabela;

1. Sintaxe básica para inclusão de uma coluna:

ALTER TABLE nome_da_tabel ADD COLUMN nome_coluna tipo_atributo;

Ex.:

ALTER TABLE funcionario ADD COLUMN identidade VARCHAR(10);

2. Sintaxe básica para exclusão de uma coluna:

ALTER TABLE nome_da_tabela DROP nome_coluna;

Ex.:

ALTER TABLE funcionario DROP identidade;

3. Sintaxe básica para alteração do nome de uma coluna:

ALTER TABLE nome_da_tabela RENAME COLUMN nome_da_coluna_atual TO novo_nome_da_coluna;

Ex.:

ALTER TABLE empregado RENAME COLUMN sexo TO genero;

Atributos

Chave Primária PRIMARY KEY: É um atributo ou conjunto de atributos que identifica unicamente uma tupla em uma relação. A PK é um atributo ou conjunto de atributos que não pode ter valores repetidos.

Chave Estrangeira FOREIGN KEY: É um atributo ou conjunto de atributos que faz referência a uma chave primária ou única em outra tabela. A FK é um atributo ou conjunto de atributos que pode ter valores repetidos.
A FK pode ser declarada com algumas opções de ação para deleção (ON DELETE) e atualização (ON UPDATE) de registros:

• CASCADE
• SET NULL
• SET DEFAULT
• RESTRICT
• NO ACTION

Restrição de Atributos (PostgreSQL):

• NOT NULL - NN - O valor não pode ser nulo.
• DEFAULT <valor> - O valor padrão para o atributo caso não seja passado.

Cláusulas da FK

ON DELETE

Cascata - CASCADE

Quando um registro é deletado da tabela referenciada, todos os registros que possuem a chave estrangeira referenciando o registro deletado também são deletados.
Sintaxe:

FOREIGN KEY cpf_cliente REFERENCES Cliente(cpf) ON DELETE CASCADE

Onde cpf_cliente é o atributo da tabela atual e Cliente(cpf) é a tabela e atributo referenciado.

Restrito - RESTRICT

Quando um registro é deletado da tabela referenciada, a operação é restringida (da erro), ou seja, não é permitido deletar o registro pai se houver outros registros filhos á referenciando.
Sintaxe:

FOREIGN KEY cpf_cliente REFERENCES Cliente(cpf) ON DELETE RESTRICT

Não faz nada - NO ACTION padrão - default

Quando um registro é deletado da tabela referenciada, um erro é exibido, e a operação de DELETE é revertida.
Sintaxe:

FOREIGN KEY cpf_cliente REFERENCES Cliente(cpf) ON DELETE NO ACTION

Define como nulo - SET NULL

Quando um registro é deletado da tabela referenciada, a chave estrangeira é definida como NULL.
Sintaxe:

FOREIGN KEY cpf_cliente REFERENCES Cliente(cpf) ON DELETE SET NULL

Valor Padrão - SET DEFAULT

Quando um registro é deletado da tabela referenciada, a chave estrangeira é definida como o valor padrão DEFAULT.
Sintaxe:

FOREIGN KEY cpf_cliente REFERENCES Cliente(cpf) ON DELETE SET DEFAULT

ON UPDATE

Restrito - RESTRICT

Quando um registro é atualizado na tabela referenciada, a operação é restringida (da erro), ou seja, não é permitido atualizar o registro pai se houver outros registros filhos á referenciando.
Sinatxe:

FOREIGN KEY cpf_cliente REFERENCES Cliente(cpf) ON UPDATE RESTRICT

Define como nulo - SET NULL

Quando um registro é atualizado na tabela referenciada de modo que não exista mais a chave primária da tabela alterada, a chave estrangeira é definida como NULL.
Sintaxe:

FOREIGN KEY cpf_cliente REFERENCES Cliente(cpf) ON UPDATE SET NULL

Define como padrão - SET DEFAULT

Quando um registro é atualizado na tabela referenciada de modo que não exista mais a chave primária da tabela alterada, a chave estrangeira é definida como o valor padrão DEFAULT.
Sintaxe:

FOREIGN KEY cpf_cliente REFERENCES Cliente(cpf) ON UPDATE SET DEFAULT

Não faz nada - NO ACTION padrão - default

Quando um registro é atualizado na tabela referenciada, de modo que a chave primária referenciada não exista mais, um erro é exibido, e a operação de UPDATE é revertida.
Sintaxe:

FOREIGN KEY cpf_cliente REFERENCES Cliente(cpf) ON UPDATE NO ACTION

Remover base de dados (DB)

DROP remove toda a base de dados, incluindo todas as tabelas, dados, índices, etc.

Sintaxe:

DROP DATABASE nome_do_banco;

Exemplo:

DROP DATABASE Empresa;
DROP DATABASE Hospital;
DROP DATABASE Universidade;

DML - Data Manipulation Language

DML - Data Manipulation Language (Linguagem de Manipulação de Dados) é usada para gerenciar os dados armazenados em um banco de dados. Manipula os dados de uma tabela, como inserir, atualizar, excluir e selecionar.

Propriedades

A linguagem DML é composta por 4 operações de manipulação de dados:

• Inserção de dados - INSERT
• Exclusão de dados - DELETE
• Atualização de dados - UPDATE
• Seleção de dados (consulta) - SELECT

Inserir Dados

INSERT INTO é usado para inserir novos registros em uma tabela.

Sintaxe:

INSERT INTO nome_tabela (coluna1, coluna2, ...) VALUES (valor1, valor2, ...);

Exemplo:

INSERT INTO empregado (nome, salario, cargo) VALUES ('João', 2000.00, 'Analista');

CREATE TABLE empregado (
  nome VARCHAR(30),
  salario NUMERIC(8,2),
  cargo VARCHAR(15)
);

INSERT INTO empregado VALUES ('João', 2000.00, 'Analista');

"Frodo Bolseiro"

'Frodo Bolseiro'

Excluir Dados

DELETE FROM é usado para excluir registros (tupla/linha) de uma tabela (relação).

Sintaxe:

DELETE FROM nome_tabela WHERE condicao;

Exemplo:

DELETE FROM peca WHERE cod_peca = 200;

Atualizar dados

UPDATE/SET é usado para atualizar registros existentes em uma tabela. Quando há mudança de endereço, nome, etc...

Sintaxe:

UPDATE nome_tabela SET coluna1 = valor1, coluna2 = valor2 WHERE condicao;

Exemplo:

UPDATE peca SET preco = 90.00 WHERE cod_peca = 200;

Selecionar Dados

SELECT é usado para selecionar dados de um banco de dados. A instrução SELECT é usada para recuperar registros de uma ou mais tabelas.

Sintaxe:

SELECT coluna1, coluna2, ... FROM nome_tabela WHERE condicao;

Exemplo:

SELECT nome_peca, quantidade FROM peca WHERE preco > 50;

SELECT * FROM peca;

Cláusula WHERE (condição)

A cláusula WHERE é usada para filtrar registros. A cláusula WHERE é usada para extrair apenas os registros que atendem a uma condição específica.

Usa conectores lógicos:

• AND - E
• OR - OU
• NOT - NÃO

Usa operadores de comparação:

• > - Maior
• < - Menor
• = - Igual
• <= - Menor ou igual
• >= - Maior ou igual
• BETWEEN - Entre um intervalo (incluindo os extremos). Facilita a especificação de condições númericas que envolvam um intervalo, ao invés de usar os operadores <= e >=.

Exemplos

Iremos fazer algumas operações de busca SELECT usando cláusulas de condição/filtro WHERE na tabela abaixo:

EXEMPLO 1 - Selecionar o código e o nome das peças com o preço menor que $70.00$

SELECT cod_peca, nome_peca FROM peca WHERE preco < 70.00;

Resultado:

EXEMPLO 2 - Selecionar o nome e o preço das peças com preço maior que $50.00$ e menor do que $70.00$

SELECT nome_peca, preco WHERE preco BETWEEN 50.00 AND 70.00

Resultado:

EXEMPLO 3 - Selecionar todas as informações das peças cuja quantidade em estoque seja maior ou igual a $10$.

SELECT * FROM peca WHERE qtd >= 10;

Resultado:

EXEMPLO 4 - Selecionar o código, nome, preço e quantidade de peças no estoque cujo código é $200$.

SELECT cod_peca, nome_peca, preco, qtd FROM peca WHERE cod_peca = 200;

Resultado:

Cláusula ORDER BY (ordenação)

A cláusula ORDER BY é usada para ordenar o resultado de uma consulta em ordem crescente ou decrescente. Ela é aplicada somente à operações de consulta SELECT, após a cláusula WHERE.

Para especificar a forma de ordenação, devemos indicar

• ASC - Crescente padrão - default
• DESC - Decrescente

Sintaxe:

SELECT coluna1, coluna2, ... FROM nome_tabela WHERE condicao ORDER BY coluna ASC|DESC;

Exemplo:

SELECT nome_peca, quantidade FROM peca ORDER BY nome_peca DESC;

Resultado:

Funções de agregação

As funções de agregação são usadas para calcular algo a partir de um conjunto de valores. As funções de agregação são usadas com a cláusula SELECT.

As principais são:

• COUNT - Conta o número de linhas (tuplas)
• SUM - Soma os valores da coluna - apenas em dados numéricos
• AVG - Calcula a média dos valores da coluna (average³) - apenas em dados numéricos
• MIN - Retorna o menor valor da coluna
• MAX - Retorna o maior valor da coluna

EXEMPLO 1 - Encontrar a soma dos preços de todas as peças, o maior preço, o menor preço e a média dos preços.

SELECT SUM(preco), MAX(preco), MIN(preco), AVG(preco) FROM peca;

Resultado:

EXEMPLO 2 - Contar o número de peças que há no estoque.

SELECT COUNT(*) FROM peca;

ou

SELECT COUNT(cod_peca) FROM peca;

Resultado:

Valores NULL (nulo)

Suponhamos que temos a tabela Peça criada anteriormente, estruturada e preenchida da seguinte forma:

CREATE TABLE peca (
  cod_peca INTEGER NOT NULL,
  nome_peca VARCHAR(30) NOT NULL,
  preco NUMERIC(6,2),
  qtd INTEGER DEFAULT 0
);

INSERT INTO peca VALUES
  (1, 'Peça A', 15.00, 10),
  (2, 'Peça B', 8.00, 20),
  (3, 'Peça C', 8.00, 30),
  (4, 'Peça D', 8.00, 10);

Inserindo valores nulos

Quando realizamos um INSERT e não passamos o campo, o banco de dados vai automaticamente inserir NULL no valor da célula.

INSERT INTO peca (cod_peca, nome_peca, qtd) VALUES (5, 'Peça E', 15);

Resultado:

Lembre-se que colunas que tem o valor DEFAULT definido, não serão preenchidas com NULL, mas sim com o valor DEFAULT especificado no momento da criação da tabela.

INSERT INTO peca (cod_peca, nome_peca, preco) VALUES (6, 'Peça F', 20.00);

Resultado:

Existe um outra forma de definir um valor como NULL. Deixando explícito no comando INSERT que a coluna deve receber o valor NULL.

INSERT INTO peca (cod_peca, nome_peca, preco, qtd) VALUES (7, 'Peça G', 17.00, NULL);

Resultado:

Repare que mesmo o campo qtd possuindo um valor DEFAULT, foi definido de forma explícita no INSERT que essa coluna deveria possuir um valor NULL.

INSERT INTO peca (cod_peca, nome_peca, preco, qtd) VALUES (7, NULL, 17.00, 12);

Resultado:

ERROR:  null value in column "nome_peca" of relation "peca" violates not-null constraint
DETAIL:  Failing row contains (7, null, 17.00, 12).
SQL state: 23502

Selecionando valores nulos

Caso você queira selecionar somente as linhas com valores nulos em uma determinada célula, a forma correta é utilizar o IS NULL, e não ... = NULL.

SELECT * FROM peca WHERE preco IS NULL;

SELECT * FROM peca WHERE preco = NULL;

Selecionando valores não nulos

Caso você queira selecionar apenas as linhas que não possuem valores nulos em uma determinada coluna, é só utilizar o c omando IS NOT NULL.

SELECT * FROM peca WHERE preco IS NOT NULL;

Resultado:

Repare que a peça de código $5$ não foi incluída no resultado, por possuir o valor null na coluna preco.

Ordenando colunas com NULL

Por default, caso você ordene um SELECT por uma coluna que possui células com valor NULL, essas células serão as últimas a serem retornadas.

SELECT * FROM peca ORDER BY preco;

Resultado:

Caso você deseje que as células com valores NULL sejam as primeiras a serem retornadas no SELECT, utilizamos o ORDER BY ... NULLS FIRST.

SELECT * FROM peca ORDER BY preco NULLS FIRST;

Resultado:

Selecionar Dados II

Cláusula DISTINCT (linhas únicas)

Linhas duplicadas podem aparecer nas relações. No caso de desejarmos a eliminação de duplicidade, devemos inserir a palavra DISTINCT na cláusula SELECT.

Tabela neste momento:

SELECT * FROM peca;

Sinatxe:

SELECT DISTINCT coluna1, coluna2, ... FROM nome_tabela;

Exemplo:

Selecionar o nome de todas as peças, sem o DISTINCT:

SELECT nome_peca FROM peca;

Selecionar o nome de todas as peças, com o DISTINCT:

SELECT DISTINCT nome_peca FROM peca;

Cláusula GROUP BY (agrupar)

A cláusula GROUP BY é usada para agrupar linhas que possuem o mesmo valor em uma ou mais colunas. É normalmente usada em conjunto com funções de agregação para agrupar os resultados de acordo com um ou mais campos. Desta forma, as funções de agregação será aplicada a cada grupo, e não a todas as tuplas.

Tabela neste momento:

SELECT * FROM peca ORDER BY nome_peca;

Sintaxe:

SELECT coluna1, coluna2, ... FROM nome_tabela GROUP BY coluna1, coluna2, ...;

EXEMPLO 1 - Selecionar o nome de todas as peças e agrupar por veículo (contar por grupo):

SELECT veiculo, COUNT(1) FROM peca GROUP BY veiculo;

Resultado:

EXEMPLO 2 - Obter a soma da quantidade de peças por tipo de veículo

SELECT veiculo, SUM(qtd) FROM peca GROUP BY veiculo;

Resultado:

Cláusula HAVING (filtro)

A cláusula HAVING é usada para filtrar grupos de registros que resultam de uma operação de GROUP BY. A cláusula HAVING é usada em conjunto com a cláusula GROUP BY.

Sintaxe:

SELECT coluna1, coluna2, ... FROM nome_tabela GROUP BY coluna1, coluna2, ... HAVING condicao;

EXEMPLO 2 anterior (alterado) - Obter a soma da quantidade de peças por tipo de veículo que sejam maiores que 20

SELECT veiculo, SUM(qtd) FROM peca GROUP BY veiculo HAVING SUM(qtd) > 20;

Resultado: