Banco de Dados Postgres SQL - DDL e DML

Esta é uma pré-visualização de arquivo. Entre para ver o arquivo original

SQL – DDL e DML
Com o PostgreSQL
Prof. Luis Marcelo
SQL
SQL  Structured Query Language (Linguagem de Consulta Estruturada)
Linguagem de pesquisa declarativa para BD relacionais
Muitas de suas características vêm da álgebra relacional
Desenvolvido no início da década de 70 pela IBM
Nome original: SEQUEL (Structured English Query Language)
Tornou-se padrão nos BDs Relacionais
SQL
Devido à criação de “dialetos” por desenvolvedores, foi criado um padrão para a linguagem pela ANSI (1986) e ISO (1987)
Com o tempo, a linguagem foi revistas e novas funcionalidades foram agregadas
Ano
Nome
Apelido
1986
SQL-86
SQL-87
1989
SQL-89
FIPS 127-1
1992
SQL-92
SQL2, FIPS 127-2
1999
SQL:1999
SQL3
2003
SQL:2003
SQL 2003
2006
SQL:2006
SQL 2006
2008
SQL:2008
SQL2008
2011
SQL:2011
SQL 2011
SQL
Os comandos do SQL estão agrupados em conjuntos, de acordo com sua funcionalidade:
DDL – Data Definition Language (Linguagem de Definição de Dados)  comandos para criação, modificação e exclusão de objetos (BDs, tabelas, índices, etc) dentro de um BD
CREATE DATABASE, CREATE TABLE, DROP TABLE, ALTER TABLE, ...
DML – Data Manipulation Language (Linguagem de Manipulação de Dados)  comandos para inserção, atualização, exclusão e recuperação de dados em tabelas de um BD 
INSERT, UPDATE, DELETE e SELECT
DCL – Data Control Language (Linguagem de Controle de Dados)  comandos para controle de acesso aos dados e objetos de um BD
GRANT e REVOKE
SQL
Simbologia para os comandos:
[] – parâmetros opcionais
{} – lista de parâmetros obrigatórios separados por uma barra (|) (deve ser escolhido pelo menos um)
O comandos não são sensíveis à caixa
CREATE = create = CrEaTe
Para que a caixa do texto seja considerada, deve-se coloca a informação entre aspas duplas (“”)
“tabela” ≠ “TABELA” ≠ “TaBeLa”
O nome de campos e tabelas não pode possuir espaços
Não é recomendado, mas para usar espaços, coloque o nome do campo / tabela entre aspas duplas
“Nome do Usuário”, “Itens da Compra”
SQL
DDL
Comandos
CREATE DATABASE
DROP DATABASE
CREATE TABLE
ALTER TABLE
DROP TABLE
DDL
CREATE DATABASE
Cria um Banco de Dados
Onde:
Nome: nome do BD que se deseja criar
Owner: usuário proprietário do banco de dados
Template: modelo para criação do BD
Encoding: Codificação dos dados (UTF-8, WIN-1252, ...)
Tablespace: tablespace onde o BD será criado
Caso não seja especificado algum item, serão utilizados os valores padrões
CREATE DATABASE nome
[ [ WITH ] [ OWNER [=] dono_do_banco_de_dados ]
[ TEMPLATE [=] modelo ]
[ ENCODING [=] codificação ]
[ TABLESPACE [=] espaço_de_tabelas ] 
]
DDL
CREATE DATABASE
Exemplos
CREATE DATABASE loja;
CREATE DATABASE site OWNER luis;
CREATE DATABASE biblioteca OWNER carlos ENCODING UTF-8;
DDL
DROP DATABASE
Exclui definitivamente um Banco de Dados
Não existe no padrão SQL
Para excluir um BD no PgAdmin, devemos fechar todas as janelas relacionadas ao banco
DROP DATABASE nome
DML
CREATE TABLE
Cria uma tabela em um BD
Onde:
nome_tabela: nome da tabela
nome_coluna: nome da coluna
tipo_dado: tipo de dados da tabela
expressão_padrão: valor padrão da coluna, caso não seja especificado um
restrição_coluna: restrições da coluna
restrição_tabela: restrições da tabela
CREATE TABLE nome_tabela (
{ nome_coluna tipo_dado [ DEFAULT expressão_padrão ] 
 [ restrição_coluna [ ... ] ]
 | restrição_tabela
}
)
DDL
TIPOS DE DADOS DO POSTGRESQL
Numéricos
Tipo
Apelidos
Descrição
Tamanho (bytes)
Intervalo
SMALLINT
INT2
Valores inteiros
2
-32768 a +32767
INTEGER
INT / INT4
Valoresinteiros
4
-2 bilhões a +2 bilhões
BIGINT
INT8
Valores inteiros
8
-9.1018a +9.1018
REAL
FLOAT4
Valoresreais com até 6 casas decimais
4
DOUBLE PRECISION
FLOAT8
Valores reais com até 15 casasdecimais
8
SERIAL
SERIAL4
Autoincremento
4
1 a 2 bilhões
BIGSERIAL
SERIAL8
Auto incremento(intervalo maior)
8
1 a9.1018
NUMERIC(p,s)
DECIMAL(p,s)
Valoresreais com até 1000 dígitos:
p: total de dígitos
s: dígitos decimais
8 + (2 *nro. grupos dede4 dígitos)
DDL
TIPOS DE DADOS DO POSTGRESQL
Monetários
Strings
Tipo
Apelidos
Descrição
Tamanho (bytes)
Intervalo
MONEY
Obsoleto, substituído pelo REAL
4
-21474836,48 a +21474836,47
Tipo
Apelidos
Descrição
Tamanho (bytes)
Intervalo
CHAR(n)
CHARACTER(n)
Stringsde tamanhofixo (preenche com espaços os caracteres não utilizados)
4 + n
Até 1GB de informação
VARCHAR(n)
CHARACTER VARYING(n)
Stringsde tamanho variável (só armazenaos caracteres de fato utilizados)
4 + n
Até 1GB de informação
TEXT
Textos semlimite de tamanho
4 + n
Até 1GB de informação
DDL
TIPOS DE DADOS DO POSTGRESQL
Data e Hora
Tipo
Apelidos
Descrição
Tamanho (bytes)
Intervalo
DATE
Datado sistema (dia, mês e ano)
4
4713 AC a 5874987 DC
TIME
TIMEwithouttime zone
Horário local semo fuso horário
8
00:00:00 a 24:00:00
TIMEZT(n)
TIME(n)withtime zone
Horário com o fuso horário (seo fuso não for especifica, coloca o fuso local)
12
TIMESTAMP
Mescla dos tipos DATE e TIME, armazenando uma data e horário
12
TIMESTAMPZT (n)
TIMESTAMP(n)withtime zone
Semelhanteao TIMESTAMP, mas armazena as informações do fuso horário
8
DDL
TIPOS DE DADOS DO POSTGRESQL
Binários / Booleanos
Tipo
Apelidos
Descrição
Tamanho (bytes)
Exemplo
BYTEA
Armazenaobjetos binário (BLOB)
4 + n
‘\\\\alfa\\\\beta\n235’
BIT(n)
Armazenastringsbinárias. O dados deve possuir exatamente o tamanho defino
n
B’010001101010’
VARBIT(n)
BIT VARYING (n)
Armazenastringsbinárias de tamanhovaríavel(menor ou igual ao definido)
n
B’1101100011101’
BOOLEAN
BOOL
Armazena valores booleanos (TRUE ou FALSE):
Verdadeiro:TRUE,true, ‘YES’, ‘yes’, ‘T’, ‘t’, ‘Y’, ‘y’, ‘1’
Falso: FALSE, false, ‘NO’, ‘no’, ‘F’, ‘f’, ‘N’, ‘n’, ‘0’
1
DDL
TIPOS DE DADOS DO POSTGRESQL
Redes
Tipo
Descrição
Tamanho (bytes)
Exemplo
CIDR
Armazena endereços de rede
12 ou 24
’10.1.100.128/25’
’10.1/25’
INET
Armazenaendereços de rede, mas não aceita endereços incompletos
12 ou 24
‘2001:4f8:3:ba::/64’
MACADDR
Armazena o endereçoMAC das placas de rede
6
’11:00:1e:02:12:01’
‘11001e:021201’
’11-22-1e-02-12-01’
DDL
TIPOS DE DADOS DO POSTGRESQL
Geométricos
Tipo
Descrição
Tamanho (bytes)
Exemplo
PATH
Armazena as coordenadas de um desenho formado pelos pontos especificados
16+16n
‘((1,5), (1,6))’
‘1, 5, 1,6, 2, 8, 4, 5, 2, 3’
LSEG
Armazenapares de pontos que define um segmento de reta
32
‘((1, 5), (1, 6))’
‘(1, 5), (1, 6)’
BOX
Representa um quadrado a partirdos pontos de suas quinas opostas
32
‘((1, 5), (3, 2))’
‘1, 5, 3, 2’
POINT
Representaum ponto
16
‘(1,5)’
POLYGON
Representaum polígono de formato regular/irregular defino pelos pontos informados
40+16n
‘(1, 5), (1, 6), (2, 7)’
CIRCLE
Representa um círculoformado pelo seu ponto central e por seu raio
24
‘((1,5), 6)’
‘<(1, 5), 6>’
‘1, 5, 6’
DDL
RESTRIÇÕES DE COLUNA
[ CONSTRAINT nome_da_restrição ]
{
 NOT NULL |
 NULL |
 UNIQUE |
 PRIMARY KEY |
 CHECK (expressão) |
 REFERENCES tab_referenciada [(col_referenciada)]
 [ ON DELETE ação ] [ ON UPDATE ação ] 
}
DDL
RESTRIÇÕES DE COLUNA
Onde:
CONSTRAINT nome_da_restrição: define um nome para a restrição. Se não for especificado, o SGBD gera um 
NOT NULL: a coluna não pode receber valores nulos
NULL: a coluna não receber valores nulos (Padrão)
Defasado. Só está disponível para manter a compatibilidade com BDs fora de padrão
UNIQUE: a coluna não pode possuir valores repetidos
PRIMARY KEY: a coluna é chave primária da tabela 
CHECK (expressão): especifica uma condição para inserção de dados em uma tabela. Os dados somente serão inseridos se atenderem à condição
DDL
RESTRIÇÕES DE COLUNA
Onde:
REFERENCES tab_referenciada (col_referenciada): cria uma chave estrangeira, ligando a coluna a uma chave primária em outra tabela. Caso as colunas possuam o mesmo nome, col_referenciada pode ser omitido
ON DELETE/ ON UPDATE ação: ação a ser tomada caso a chave primária ligada ao campo seja excluída/alterada:
NO ACTION (padrão) / RESTRICT: gera um erro de violação da chave estrangeira e impede qualquer ação
CASCADE: exclui todos as linhas dependentes, ou atualiza seus valores
SET NULL: atribui valor nulo às colunas que fazem referência
SET DEFAULT: atribui o valor padrão às colunas que fazem referência
DDL
RESTRIÇÕES DE TABELA
[ CONSTRAINT nome_da_restrição ]
{
 UNIQUE ( nome_da_coluna [, ... ] ) |
 PRIMARY KEY ( nome_da_coluna [, ... ] ) |
 CHECK ( expressão ) |
 FOREIGN KEY ( nome_da_coluna [, ... ] )
 REFERENCES tab_referenciada [( col_referenciada [, ...] )]
 [ ON DELETE ação ] [ ON UPDATE ação ]
}
DDL
RESTRIÇÕES DE TABELA
Onde:
CONSTRAINT nome_da_restrição: define um nome para a restrição. Se não for especificado, o SGBD gera um 
UNIQUE ( nome_da_coluna [, ... ] ): especifica um conjunto de colunas que não podem assumir valores repetidos
PRIMARY KEY ( nome_da_coluna [, ... ] ): especifica um conjunto de campos que compõem a chave primária da tabela
CHECK (expressão): especifica uma condição para inserção de dados em uma tabela. Os dados somente serão inseridos se atenderem à condição
DDL
RESTRIÇÕES DE TABELA
Onde:
FOREIGN KEY (coluna) REFERENCES tab_referenciada (col_referenciada): cria uma chave estrangeira, ligando coluna a uma chave primária em outra tabela. Caso as colunas possuam o mesmo nome, col_referenciada pode ser omitido 
ON DELETE/ ON UPDATE ação: ação a ser tomada caso a chave primária ligada ao campo seja excluída/alterada:
NO ACTION (padrão) / RESTRICT: gera um erro de violação da chave estrangeira e impede qualquer ação
CASCADE: exclui todos as linhas dependentes, ou atualiza seus valores
SET NULLL: atribui valor nulo às colunas que fazem referência
SET DEFAULT: atribui o valor padrão às colunas que fazem referência
DDL
CREATE TABLE
Exemplo
CREATE TABLE Clientes (
 CodCliente INT PRIMARY KEY,
 Nome VARCHAR(100) NOT NULL,
 CPF BIGINT UNIQUE,
 DataNascimento DATE
)
CREATE TABLE CuponsPromo(
 CodCupom INT PRIMARY KEY,
 DescricaoCupom VARCHAR(200) NOT NULL,
 Cliente INT,
 FOREIGN KEY (Cliente) references Cliente (CodCliente)
 ON DELETE CASCADE ON UPDATE CASCADE
)
DDL
ALTER TABLE
Modifica a estrutura de uma tabela
ALTER TABLE nome 
 RENAME [ COLUMN ] coluna TO novo_nome_da_coluna
ALTER TABLE nome RENAME TO novo_nome
ALTER TABLE nome ação [, ... ]
DDL
ALTER TABLE
Onde ação pode ser:
Adicionar uma coluna à tabela
ADD [ COLUMN ] coluna tipo [ restrição_de_coluna [ ... ] ]
Excluir uma coluna
DROP [ COLUMN ] coluna [ RESTRICT | CASCADE ]
Alterar o tipo de dados de uma coluna
ALTER [ COLUMN ] coluna TYPE tipo [ USING expressão ]
Definir o valor padrão de uma coluna
ALTER [ COLUMN ] coluna SET DEFAULT expressão
DDL
ALTER TABLE
Onde ação pode ser:
Excluir o valor padrão
ALTER [ COLUMN ] coluna DROP DEFAULT
Definir/Excluir definição de NOT NULL em um campo
ALTER [ COLUMN ] coluna { SET | DROP } NOT NULL
Adiciona uma restrição de tabela
ADD restrição_de_tabela
Exclui uma restrição
DROP CONSTRAINT nome_da_restrição [ RESTRICT | CASCADE ]
DDL
ALTER TABLE
Exemplo
ALTER TABLE CuponsPromo RENAME TO Cupons
ALTER TABLE Clientes RENAME COLUMN Nome TO NomeCliente
ALTER TABLE Cliente ADD CONSTRAINT fk_tipo
 FOREIGN KEY (Tipo) REFERENCES TipoCliente (CodTipo)
 ON DELETE SET NULL ON UPDATE CASCADE
ALTER TABLE Cliente ADD COLUMN Tipo INT DEFAULT 1
DDL
DROP TABLE
Exclui uma Tabela do Banco de Dados
Onde:
nome: nome da tabela a ser excluída
CASCADE: remove as tabelas e todos os objetos dependentes delas
RESTRICT (Padrão): impede a remoção das tabelas caso existam itens dependentes delas
DROP TABLE nome [, ...] [ CASCADE | RESTRICT ]
DDL
DROP TABLE
Exemplo
DROP TABLE Clientes CASCADE
DROP TABLE Cupons
SQL
DML
Comandos
INSERT
UPDATE
DELETE 
SELECT
DML
INSERT
Insere dados em uma tabela
Onde:
tabela: nome da tabela onde os dados serão inseridos
coluna: lista de colunas da tabela (caso não seja especificado, os valores das colunas devem ser fornecidos na mesma ordem dos campos da tabela)
DEFAULT VALUES / DEFAULT: insere um registro contendo os valores padrões das coluna
VALUES (expressão | DEFAULT): lista de valores para as colunas
consulta: consulta SELECT que retorna valores para as colunas
INSERT INTO tabela [ ( coluna [, ...] ) ]
{ DEFAULT VALUES | 
 VALUES ( { expressão | DEFAULT } [, ...] ) | 
 consulta 
}
DML
INSERT
Exemplos:
INSERT INTO Clientes (CodCliente, Nome, CPF, DataNascimento)
 VALUES (1, ‘João da Silva’, 12345678977, ’10/10/1977’)
INSERT INTO Cupons VALUES 
 (1, ‘Vale CD’, 1),
 (2, ’10% de desconto’, 1)
DML
UPDATE
Atualiza dados em uma tabela
Onde:
tabela: nome da tabela onde os dados serão atualizados
coluna: coluna da tabela
expressão: novo valor para a coluna
condição: condição para seleção do registro desejado
UPDATE tabela SET coluna = { expressão | DEFAULT } [, ...]
 [ WHERE condição ]
DML
UPDATE
Exemplos:
UPDATE Clientes 
 SET Nome = ‘Marcos’, CPF = 13298764588, 
 DataNascimento = ’15/08/1984’
 WHERE CodCliente = 1
UPDATE Cupons SET CodCliente = 4
 WHERE CodCupom = 3
DML
DELETE
Exclui dados de uma tabela
Onde:
tabela: nome da tabela de onde os dados serão removidos
condição: condição para seleção dos registros desejados. Caso não seja especificado uma condição, todos os registros serão apagados, esvaziando a tabela
DELETE FROM tabela [ WHERE condição ]
DML
DELETE
Exemplos:
DELETE FROM Clientes WHERE CodCliente = 2
DELETE FROM Cupons WHERE CodCliente = 4
DML
SELECT
Consulta e retorna os dados armazenados no BD
Comando mais utilizado
Onde:
DISTINCT: elimina registros duplicados do conjunto resposta
expressão: coluna ou expressão
tabela: tabela que se deseja consultar
apelido_tabela: nome alternativo para referenciar a tabela
condição: condição para seleção dos registros desejados
SELECT [DISTINCT] * | [tabela. | apelido_tabela.] expressão 
 [ AS nome_de_saída ] [, ...]
[ FROM tabela [ apelido_tabela ] [, ...] ]
[ WHERE condição ]
DML
SELECT
Condição: conjunto de testes com um resultado booleano (V ou F)
Operadores de Comparação
Operador
Operação
Exemplo
>
Maior que
x > 3
>=
Maior ou igualque
x>= 4
<
Menor que
x < 9
<=
Menor ou igualque
x <= 12
<>ou !=
Diferente de
estado <> ‘SP’
BETWEEN... AND ...
Valor no intervalo
x BETWEEN25 AND 60
NOT BETWEEN... AND ...
Valor fora do intervalor
xNOT BETWEEN 25 AND 60
IS NULL
Campo é nulo
CidadeIS NULL
IS NOT NULL
Campo é não nulo
Nome IS NOT NULL
DML
SELECT
Operadores de Comparação
Máscaras:
% – sequência de caracteres de qualquer tamanho
‘%ana%’  Banana, Cabana, iguana, ...
_ – um caractere
‘_ana%’  Banana, Iana, 
Operador
Operação
Exemplo
LIKE
BuscadeStringspor sequência (Considera a caixa do texto)
Campo LIKEseq
ILIKE
BuscadeStringspor sequência (Não considera a caixa do texto)
Campo ILIKEseq
DML
SELECT
Operadores Lógicos
Operadores Matemáticos
Mais em: http://pgdocptbr.sourceforge.net/pg80/functions-math.html
Operador
Operação
Exemplo
AND
E Lógico
(x > 3) AND (x <=10)
OR
OU Lógico
(est= ‘SP’) OR (est = ‘MG’)
NOT
Não Lógico
NOT est = ‘ES’
Operador
Operação
Exemplo
+, -, *, /, %
3+ 5,Preco- 2, 5 * 5, Total /Qtde
^
Exponenciação
2^2 4, 5^3  125
|/
RaizQuadrada
|/25 5
DML
SELECT
Exemplos
SELECT * FROM Clientes;
SELECT * FROM Clientes WHERE Estado = ‘SP’;
SELECT Nome, Cidade FROM Clientes 
 WHERE Cidade = ‘SP’;
SELECT prod.Descrição from FROM Produtos prod
 WHERE prod.PrecoVenda BETWEEN 10 AND 20;
SELECT * from FROM Produtos 
 WHERE PrecoVenda BETWEEN 10 AND 20
 AND Descricao ILIKE ‘%maçã%’;