Database Commands and Functions | SQL Joins Guide

Comandos Alias, Joins, Funções e Índice Professor Esp. Diego André Sant’Ana E-mail: diego.santana@ifms.edu.br 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_estadoAS 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.pessoapes inner join clube.pessoa_clubepesclu • on pes.id_pessoa=pesclu.id_pessoa • inner join clube.clubeclu • on pesclu.id_clube=clu.id_clube

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.pessoapesleftjoinclube.pessoa_clubepesclu • onpes.id_pessoa=pesclu.id_pessoa • leftjoinclube.clubeclu • onpesclu.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.pessoapesrightjoinclube.pessoa_clubepesclu • onpes.id_pessoa=pesclu.id_pessoa • rightjoinclube.clubeclu • onpesclu.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 • selectnome_clientefrom cliente • unionall • selectnome_pessoafromclube.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. • selectNOME_CARRO,substring(nome_carro,0,4)|| lpad(generate_series::integer||'',4,'0') fromauto.carrocrossjoin • generate_series(1,1000,2)

Função LPAD • Completa a esquerda com o caracter que desejar ate a quantidade informada lpad(valor, qtdcaracter, 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, qtdcaracter, caracter a ser colocado) • rpad(valor||’’,4,'0')

CRIAR A TABELA CARRO • CREATE TABLE auto.carro • ( • id_carrobigserial NOT NULL, • nome_carrocharactervarying(100), • placa charactervarying(7) NOT NULL, • CONSTRAINT carro_pkey PRIMARY KEY (id_carro) • )]

INSERT DA TABELA CARRO • INSERT INTO auto.carro(nome_carro, placa) VALUES ('FUSCA', 'HTT'||lpad(random()||'',4,'0')); • INSERT INTO auto.carro(nome_carro, placa) VALUES ('UNO', 'HTT'||lpad(random()||'',4,'0')); • INSERT INTO auto.carro(nome_carro, placa) VALUES ('SENTRA', 'HTT'||lpad(random()||'',4,'0')); • INSERT INTO auto.carro(nome_carro, placa) VALUES ('CIVIC', 'HTT'||lpad(random()||'',4,'0')); • INSERT INTO auto.carro(nome_carro, placa) VALUES ('PALIO', 'HTT'||lpad(random()||'',4,'0')); • INSERT INTO auto.carro(nome_carro, placa) VALUES ('FUSCA', 'HTT'||lpad(random()||'',4,'0')); • INSERT INTO auto.carro(nome_carro, placa) VALUES ('CITY', 'HTT'||lpad(random()||'',4,'0')); • INSERT INTO auto.carro(nome_carro, placa) VALUES ('S10', 'HTT'||lpad(random()||'',4,'0')); • INSERT INTO auto.carro(nome_carro, placa) VALUES ('RANGER', 'HTT'||lpad(random()||'',4,'0')); • INSERT INTO auto.carro(nome_carro, placa) VALUES ('SILVERADO', 'HTT'||lpad(random()||'',4,'0')); • INSERT INTO auto.carro(nome_carro, placa) VALUES ('SANDERO', 'HTT'||lpad(random()||'',4,'0')); • INSERT INTO auto.carro(nome_carro, placa) VALUES ('CLIO', 'HTT'||lpad(random()||'',4,'0')); • INSERT INTO auto.carro(nome_carro, placa) VALUES ('MERIVA', 'HTT'||lpad(random()||'',4,'0'));

INSERT DA TABELA CARRO • --COMO FAZER UPDATE UTILIZANDO O RAMDOM E LPAD • updateauto.carro set placa='HTT'||lpad(random()::integer||'',4,'0') • --SELECT BASICO • SELECT * FROM AUTO.CARRO • --SELECT COM CROSS JOIN • selectNOME_CARRO,substring(nome_carro,0,4)|| lpad(generate_series::integer||'',4,'0') fromauto.carrocrossjoin • 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') fromauto.carrocrossjoin • 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_IDXON 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_IDXON 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 B-tree. 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 * fromgenerate_series(1,4,1) • SELECT • nome_aluno, mae, pai, • data_nascimento+generate_series, descricao • fromifms.alunocrossjoin • 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 • fromifms.alunocrossjoin • 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/functions-matching.html http://www.codigofonte.net/dicas/bancodedados/561_utilizando-o-postgres-dicas-de-comandos-sql-e-essenciais-para-a-manipulacao-de-dados

Database Commands and Functions | SQL Joins Guide