Comandos Alias, Joins,
Funções e Índice
Professor Esp. Diego André Sant’Ana
E-mail: [email protected]
Disciplina: Banco de Dados II
professordiegosantana.wordpress.com
Alias(Apelido)
Alias
 Você pode dar uma tabela ou uma coluna outro nome
usando um alias.
Isto pode ser uma boa coisa a se fazer se o nome da tabela
ou coluna que forem muito complexo
 Alias de Colunas


SELECT id_estado AS idest,* FROM estado

Alias de Tabelas
SELECT e.id_estado FROM estado AS e
 WHERE e.id_estado=1

JOIN(JUNÇÃO)
A palavra-chave JOIN é usada em uma instrução SQL
para consultar os dados de duas ou mais tabelas, com
base em uma relação entre deter
 Tabelas em um banco de dados são, muitas vezes,
relacionadas umas às outras com as teclas.
 Uma chave primária é uma coluna (ou uma
combinação de colunas), com um valor único para
cada linha. Cada chave primária valor deve ser
exclusivo dentro da tabela.
 O objetivo é vincular os dados em conjunto, em
tabelas, sem repetição de todos os dados em cada
tabela.

INNER JOIN(JUNÇÃO)
select * from
 clube.pessoa
pes
inner
clube.pessoa_clube pesclu
 on pes.id_pessoa=pesclu.id_pessoa
 inner join clube.clube clu
 on pesclu.id_clube=clu.id_clube

join
JOIN(JUNÇÃO)

INNER JOIN: Regresso filas quando há,
pelo menos, um jogo em ambas as tabelas
SELECT * FROM estado est
 INNER JOIN cidade cid
 ON est.id_estado = cid.id_estado


LEFT JOIN
LEFT JOIN: Retornar todas as linhas da
tabela à esquerda, mesmo quando não há
jogos no quadro do direito
 SELECT * FROM estado est
 LEFT JOIN cidade cid
 ON est.id_estado = cid.id_estado


LEFT JOIN
select * from
 clube.pessoa pes left join
clube.pessoa_clube pesclu
 on pes.id_pessoa=pesclu.id_pessoa
 left join clube.clube clu
 on pesclu.id_clube=clu.id_clube

RIGHT JOIN
RIGHT JOIN: Retornar todas as linhas
da tabela à direita, mesmo se não houver
jogos no quadro da esquerda
 SELECT * FROM estado est
 RIGHT JOIN cidade cid
 ON est.id_estado = cid.id_estado


RIGHT JOIN
select * from
 clube.pessoa pes right join
clube.pessoa_clube pesclu
 on pes.id_pessoa=pesclu.id_pessoa
 right join clube.clube clu
 on pesclu.id_clube=clu.id_clube

FULL JOIN

FULL JOIN: Regresso filas quando há
um jogo em um dos quadros
SELECT * FROM estado est
 FULL JOIN cidade cid
 ON est.id_estado = cid.id_estado

UNION
O operador UNION é usado para combinar o resultado-conjunto de dois
ou
mais
SELECT.
Observe que cada SELECT declaração no âmbito da União devem ter o
mesmo número de colunas.
 As colunas devem ter também os tipos de dados semelhantes. Além disso,
as colunas em cada SELECT declaração deve ser na mesma ordem.


SELECT SIGLA FROM estado est

UNION ALL

SELECT SIGLA FROM estado est
select nome_cliente from cliente
 union all
 select nome_pessoa from clube.pessoa

CROSS JOIN

CROSS JOIN – Todos os dados da tabela à
esquerda de JOIN são cruzados com os dados da
tabela à direita de JOIN por meio do CROSS
JOIN, também conhecido como produto
cartesiano. É possível cruzarmos informações de
duas ou mais tabelas.
select
NOME_CARRO,substring(nome_carro,0,4)||
lpad(generate_series::integer||'',4,'0') from
auto.carro cross join
 generate_series(1,1000,2)

CROSS JOIN

CROSS JOIN – Todos os dados da tabela à
esquerda de JOIN são cruzados com os dados da
tabela à direita de JOIN por meio do CROSS
JOIN, também conhecido como produto
cartesiano. É possível cruzarmos informações de
duas ou mais tabelas.
select
NOME_CARRO,substring(nome_carro,0,4)||
lpad(generate_series::integer||'',4,'0') from
auto.carro cross join
 generate_series(1,1000,2)

Função LPAD
Completa a esquerda com o caracter que
desejar ate a quantidade informada
lpad(valor, qtd caracter, caracter a
ser colocado)
 lpad(valor||’’,4,'0')

Função RPAD
Completa a direita com o caráter que
desejar ate a quantidade informada
rpad(valor, qtd caracter, caracter a
ser colocado)
 rpad(valor||’’,4,'0')

CRIAR A TABELA CARRO







CREATE TABLE auto.carro
(
id_carro bigserial NOT NULL,
nome_carro character varying(100),
placa character varying(7) NOT NULL,
CONSTRAINT carro_pkey PRIMARY KEY
(id_carro)
)]
INSERT DA TABELA CARRO













INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
auto.carro(nome_carro, placa) VALUES
auto.carro(nome_carro, placa) VALUES
auto.carro(nome_carro, placa) VALUES
auto.carro(nome_carro, placa) VALUES
auto.carro(nome_carro, placa) VALUES
auto.carro(nome_carro, placa) VALUES
auto.carro(nome_carro, placa) VALUES
auto.carro(nome_carro, placa) VALUES
auto.carro(nome_carro, placa) VALUES
auto.carro(nome_carro, placa) VALUES
auto.carro(nome_carro, placa) VALUES
auto.carro(nome_carro, placa) VALUES
auto.carro(nome_carro, placa) VALUES
('FUSCA', 'HTT'||lpad(random()||'',4,'0'));
('UNO', 'HTT'||lpad(random()||'',4,'0'));
('SENTRA', 'HTT'||lpad(random()||'',4,'0'));
('CIVIC', 'HTT'||lpad(random()||'',4,'0'));
('PALIO', 'HTT'||lpad(random()||'',4,'0'));
('FUSCA', 'HTT'||lpad(random()||'',4,'0'));
('CITY', 'HTT'||lpad(random()||'',4,'0'));
('S10', 'HTT'||lpad(random()||'',4,'0'));
('RANGER', 'HTT'||lpad(random()||'',4,'0'));
('SILVERADO', 'HTT'||lpad(random()||'',4,'0'));
('SANDERO', 'HTT'||lpad(random()||'',4,'0'));
('CLIO', 'HTT'||lpad(random()||'',4,'0'));
('MERIVA', 'HTT'||lpad(random()||'',4,'0'));
INSERT DA TABELA CARRO

--COMO FAZER UPDATE UTILIZANDO O RAMDOM E LPAD

update auto.carro set placa='HTT'||lpad(random()::integer||'',4,'0')

--SELECT BASICO

SELECT * FROM AUTO.CARRO

--SELECT COM CROSS JOIN
select NOME_CARRO,substring(nome_carro,0,4)||
lpad(generate_series::integer||'',4,'0') from auto.carro cross join
generate_series(1,1000,2)


INSERT CRUZADO UTILIZANDO
A TABELA CARRO
INSERT INTO CARRO
(NOME_CARRO,PLACA)
 ( select NOME_CARRO,
substring(nome_carro,0,4)||
lpad(generate_series::integer||'',4,'0') from
auto.carro cross join
 generate_series(1,1000,2) )

INDEXAR UM CAMPO

CREATE [ UNIQUE ] INDEX [
CONCURRENTLY ] [ name ] ON table [
USING method ] ( { column | (
expression ) } [ opclass ] [ ASC | DESC ] [
NULLS { FIRST | LAST } ] [, ...] ) [ WITH (
storage_parameter = value [, ... ] ) ] [
TABLESPACE tablespace ] [ WHERE
predicate ]
INDEXAR UM CAMPO

CREATE INDEX ON AUTO.CARRO
((lower(NOME_CARRO)));

CREATE UNIQUE INDEX PLACA_IDX
ON AUTO.CARRO (PLACA);

DROP INDEX PLACA_IDX
INDEXAR UM CAMPO

CREATE INDEX idx_carro ON
AUTO.CARRO (NOME_CARRO , PLACA);

CREATE INDEX ON AUTO.CARRO
((lower(NOME_CARRO)));

CREATE UNIQUE INDEX PLACA_IDX
ON AUTO.CARRO (PLACA);

DROP INDEX PLACA_IDX
Tipos de Índices





O PostgreSQL suporta atualmente quatro tipos de índices: B-tree
(árvore B), R-tree (árvore R), Hash e GiST.
B-tree -> é o tipo padrão (assume quando não indicamos). São
índices que podem tratar consultas de igualdade e de faixa, em
dados que podem ser classificados.
Indicado para consultas com os operadores: <, <=, =, >=, >. Também
pode ser utilizado com LIKE, ILIKE, ~ e ~*.
R-tree -> tipo mais adequado a dados espaciais. Adequado para
consultas com os operadores: <<, &<, &>, >>, @, ~=, &&.
Hash ->indicados para consultas com comparações de igualdade
simples. É desencorajado seu uso. Em seu lugar recomenda-se o Btree. GiST ->
CRIAR UM ESQUEMA IFMS

CREATE SCHEMA ifms;
CRIAR UMA TABELA









CREATE TABLE ifms.aluno(
ID_ALUNO SERIAL,
NOME_ALUNO CHARACTER VARYING(250),
MAE CHARACTER VARYING(250),
PAI CHARACTER VARYING(250),
DATA_NASCIMENTO DATE,
DESCRICAO TEXT,
FOTO BYTEA
)
CARREGAR UMA TABELA

CARREGUE A TABELA COM 15
DADOS, DEPOIS CRIE UM SCRIPT
PARA GERAR DADOS ATE MAIS OU
MENOS 150000 MIL REGISTROS.
INSERTS















INSERT INTO ifms.aluno(nome_aluno, mae, pai, data_nascimento, descricao, foto)
VALUES
('DIEGO','ELIANA' ,'JOSE', '1987-11-30'::DATE,'TRABALHA NO IFMS',NULL)
,('JOAO','ANA' ,'JOAQUIM', '1967-11-30'::DATE,'TRABALHA NO IFMS',NULL)
,('PEDRO','JESSICA' ,'MOISES', '1958-11-10'::DATE,'TRABALHA NO IFMS',NULL)
,('MARIA','JOAQUINA' ,'JOAO', '1951-11-10'::DATE,'TRABALHA NO IFMS',NULL)
,('MARILIA','JOSEFINA' ,'PEDRO', '1957-11-10'::DATE,'TRABALHA NO IFMS',NULL)
,('GABRIEL','RENATA' ,'PAULO', '1910-11-10'::DATE,'TRABALHA NO IFMS',NULL)
,('CLAUDEMIR','ANA' ,'LUIZ', '1910-11-10'::DATE,'TRABALHA NO IFMS',NULL)
,('RODRIGO','JOSEFINA' ,'CLAUDEMIR', '1950-11-10'::DATE,'TRABALHA NO IFMS',NULL)
,('ANGELICA','MARCIA' ,'MARIA', '1957-01-10'::DATE,'TRABALHA NO IFMS',NULL)
,('LUCAS','JOSEFINA' ,'JESUS', '1957-11-10'::DATE,'TRABALHA NO IFMS',NULL)
,('NATHALIA','JOSEFINA' ,'DELMIR', '1995-08-10'::DATE,'TRABALHA NO IFMS',NULL)
,('IVANALDO','CASSIMA' ,'RONIVAN', '1991-11-10'::DATE,'TRABALHA NO IFMS',NULL)
,('CRISTIANA','BEATRIZ' ,'VITOR', '1981-01-10'::DATE,'TRABALHA NO IFMS',NULL);
GENERATE SERIES

select * from generate_series(1,4,1)

SELECT
 nome_aluno, mae, pai,
 data_nascimento+generate_series,
descricao
 from ifms.aluno cross join
 generate_series(1,2000,1)

INSERT
INSERT INTO ifms.aluno (nome_aluno, mae,
pai,
 data_nascimento, descricao )
(
 SELECT
 nome_aluno, mae, pai,
 data_nascimento+generate_series, descricao
 from ifms.aluno cross join
 generate_series(1,2000,1)
)

FAÇA TESTES COM AS PESQUISAS

CARREGUE A TABELA COM 15
DADOS, DEPOIS CRIE UM SCRIPT
PARA GERAR DADOS ATE MAIS OU
MENOS 150000 MIL REGISTROS.
REFERÊNCIAS
http://www.postgresql.org/docs/9.0/static/fu
nctions-matching.html
http://www.codigofonte.net/dicas/bancoded
ados/561_utilizando-o-postgres-dicas-decomandos-sql-e-essenciais-para-amanipulacao-de-dados