Material Estudo Banco de Dados

Prévia do material em texto

MÉTODOS DE CRIAÇÃO SIMPLES 
 
selecionar tabela alunos, 
 
SELECT *from nom_aluno; 
 
selecionar somente as cidades, 
 
SELECT nom_cidade from aluno; 
 
para trazer uma unica vez o nome de cidades repetidas, 
 
SELECT Distinct nom_cidade from aluno; 
 
ordenar os nomes em alfabeticos, 
 
SELECT *from aluno order by aluno.nom_aluno; 
 
se quiser ordenar ao contrario (ultimo para o primeiro), 
 
SELECT *from aluno order by aluno.nom_aluno desc; 
 
caso queira selecionar o tabela do banco de dados pela forma numerica; 
 
SELECT *from curso where carga_horaria = 25; 
SELECT *from curso where carga_horaria > 25; 
SELECT *from curso where carga_horaria < 25; 
SELECT *from curso where carga_horaria >= 25 and carga_horaria <=50; 
SELECT *from curso where carga_horaria <= 25 and carga_horaria <50; 
 
para consultar intervalos temos outro comando alem do "and", 
 
SELECT *from curso where carga_horaria between 20 and 50; 
sempre garantir que o primeiro numero sempre seja menor que o segundo. 
 
caso queira apenas selecionar dois numeros especificos, nao nescessitamos fazer 
carga_horaria = 20 and carga_horaria =50; podemos codificar da seguinte forma utilizando o metodo in( , ), 
 
SELECT *from curso where carga_horaria in(20,50); 
 
caso queira trazer a busca do banco especificando registros que apresentem somente as letras "r" e "e", 
é utilizado a função like '%__%' . 
 
SELECT *from aluno where nom_aluno like '%RE%'; 
 
caso quizesse trazer todos os alunos que começam com uma letra especifica (ex:J), 
 
SELECT *from aluno where nom_aluno like 'J%'; 
 
caso queira trazer o nome pela ultima letra é apenas codificar ao contrario, 
 
SELECT *from aluno where nom_aluno like '%A'; 
 
inserindo um novo nome na tabela aluno, 
 
INSERT into aluno(nom_aluno,nom_cidade) 
 VALUES ('ROGERIO',null); 
 
caso queira buscar o aluno que esta sem o cadastro da cidade; 
 
SELECT *from aluno where nom_cidade is null; 
 
e se eu quiser trazer todos que possuem os registros de cidades, que nao estao nulos, 
 
SELECT *from aluno where nom_cidade is not null; 
 
caso queira selecinar os alunos de determinadas cidades especificas, 
 
SELECT *from aluno where nom_cidade in ('RIBEIRÃO PRETO', 'SERTÃOZINHO'); 
 
porem o uso do "in" gera problemas no uso do banco retornando algo perdido, porem 
essa função abaixo corrige o problema sendo assim a maneira correta de fazer a pesquisa, 
 
SELECT *from aluno where (nom_cidade='RIBEIRÃO PRETO' OR nom_cidade='SERTÃOZINHO'); 
 
caso queira trazer somente um aluno referenciado entre as cidades RIBEIRAO OU SERTAOZINHO, 
 
SELECT *from aluno where (nom_cidade='RIBEIRÃO PRETO' OR nom_cidade='SERTÃOZINHO') 
 AND nom_aluno='ZEQUINHA'; 
 
create table cargo( 
 cod_cargo integer , 
 nome_cargo varchar(50), 
 valor_cargo decimal(10,2), 
 primary key(cod_cargo) 
); 
 
create table funcionario2( 
 matricula integer , 
 nome_funcionario varchar(50), 
 cod_cargo integer, 
 primary key(matricula), 
 constraint fk_cargo foreign key(cod_cargo) references cargo 
 (cod_cargo) 
); 
 
 
 
 
INSERT INTO CARGO(cod_cargo,nome_cargo, valor_cargo) VALUES (1,'GERENTE DE PROJETOS',4500.00); 
 
INSERT INTO CARGO(cod_cargo,nome_cargo, valor_cargo) VALUES (2,'ANALISTA DE SISTEMAS',2500.00); 
 
INSERT INTO CARGO(cod_cargo,nome_cargo, valor_cargo) VALUES (3,'PROGRAMADOR',1500.00); 
 
INSERT INTO CARGO(cod_cargo,nome_cargo, valor_cargo) VALUES (4,'TECNICO TI',1000.00); 
 
 
INSERT INTO FUNCIONARIO2(matricula,nome_funcionario, cod_cargo) VALUES (1,'MARIO',1); 
 
INSERT INTO FUNCIONARIO2(matricula,nome_funcionario, cod_cargo) VALUES (2,'HEITOR',1); 
 
INSERT INTO FUNCIONARIO2(matricula,nome_funcionario, cod_cargo) VALUES (3,'CARLOS',2); 
 
INSERT INTO FUNCIONARIO2(matricula,nome_funcionario, cod_cargo) VALUES (4,'AMAURI',3); 
 
INSERT INTO FUNCIONARIO2(matricula,nome_funcionario, cod_cargo) VALUES (5,'RENATO',3); 
 
INSERT INTO FUNCIONARIO2(matricula,nome_funcionario, cod_cargo) VALUES (6,'SILVANO',3); 
 
INSERT INTO FUNCIONARIO2(matricula,nome_funcionario, cod_cargo) VALUES (7,'WILSON',NULL); 
 
 
 
 
 
 
Exercícios 
 
1) Selecione todos os funcionários e seus respectivos cargos (apenas aqueles que tiverem um cargo), utilizando INNER JOIN 
 
SELECT F.nome_funcionario, 
 
 C.cod_cargo 
FROM cargo AS C 
 
INNER JOIN funcionario2 AS F 
 
 ON (F.cod_cargo = C.cod_cargo); 
 
 
2) Selecione todos os funcionários e seus respectivos cargos (mesmo aqueles não não tiverem cargo), utilizando OUTER LEFT 
JOIN 
 
SELECT F.nome_funcionario, 
 
 C.nome_cargo 
 
FROM funcionario2 AS F 
 
LEFT OUTER JOIN cargo AS C 
 
 ON (F.cod_cargo = C.cod_cargo); 
 
 
3) Selecione todos os cargos e seus respectivos funcionarios (mesmo aqueles cargos que não tenham funcionários), utilizando o 
OUTER RIGHT JOIN 
 
SELECT F.nome_funcionario, 
 C.cod_cargo 
FROM funcionario2 AS F 
RIGHT OUTER JOIN cargo AS C 
 ON (F.cod_cargo = C.cod_cargo); 
 
 
4) Faça uma consulta nas tabelas funcionario e cargo utilizando ALTER FULL JOIN 
 
SELECT F.nome_funcionario, 
 
 C.cod_cargo 
FROM funcionario2 AS F 
 
FULL OUTER JOIN cargo AS C 
 
 ON (F.cod_cargo = C.cod_cargo); 
 
 
5) Faça uma consulta nas tabelas funcionario e cargo utilizando CROSS JOIN 
 
 
 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
 
 
 
 
 
 
 
 
 
Funções SQL 
--------------------------------------------------------------------------------- 
 
 
***Tratar o retorno do dado/tratar a saida do banco mediante uma consulta. 
***Existem situações que determinadas busca de registros devem ser escritas em maisculas em determinado caso, e minusculas 
em outro. 
 
1-Escrevendo dados caixa alta/caixa baixa: 
 
todas as letras em minusculos: select lower(nom_aluno) nome_aluno from aluno; 
todas as letras em maiusculas: select upper(nom_aluno) nome_aluno from aluno; 
 
2-Recuperando registros com a inicial em caixa alta: 
 
select initcap(nom_aluno) nome_aluno from aluno; 
 
3-Inclusão de caracteres á esquerda: 
 
select lpad(nom_aluno,30,'*') nome_aluno from aluno; 
select length(lpad(nom_aluno,30,'*')) nome_aluno from aluno; //retorna o numero de caracteres definidos no registro gravado 
no campo (ou seja tabela aluno) 
 
4-Inclusão de caracteres á direita: 
 
select rpad(nom_aluno,30,'*') nome_aluno from aluno; 
select length(rpad(nom_aluno,30,'*')) nome_aluno from aluno; 
 
5-Recuperando partes de texto: 
 
select substr(nom_cidade,1,4) cidade from aluno; 
select substr(nom_cidade,0,4) cidade from aluno; 
 
6-Removendo caracteres á esquerda tomando como base coluna/valor: 
 
select nom_cidade, ltrim(nom_cidade,'RIB') cidade from aluno; 
select nom_cidade, ltrim(nom_cidade,'R') cidade from aluno; 
 
7-Removendo caracteres á direita tomando como base coluna/valor: 
 
select nom_cidade, rtrim(nom_cidade,'O') cidade from aluno; 
select nom_cidade, rtrim(nom_cidade,'S') cidade from aluno; 
 
CAPTURANDO O TOTAL DE CARACTERES DO DADO 
 
select nom_cidade, length(nom_cidade) totalChars length ('POSTGRES') from aluno; 
 
ARREDONDAMENTO DE VALORES 
 
Decimal <5 
select round(22.24, 1); 
 
Decimal >=5 
select round(22.25, 1); 
 
EXPONENCIAÇÃO (VALOR,POTENCIA) 
select power(10,1); //primeira potencia = 10 
select power(10,2); //primeira potencia = 10*10 
select power(10,3); //primeira potencia = 10*10*10 
 
RAIZ QUADRADA 
select SQRT(4); 
select SQRT(100); 
 
DATAS POSTGRES 
select current_date mydate; 
 
CURRENTS TIME 
select current_time mytime; 
 
CONVERSAO - VALOR / MASCARA DE CONVERSÃO 
 
select to_char(125,'999'); 
select to_char(current_date,'yyyy-mm-dd'); 
 
MEDIA DE VALORES SELECIONADOS 
select avg(carga_horaria) from curso; 
 
MINIMO E MAXIMOselect min(carga_horaria) from curso; 
select max(carga_horaria) from curso; 
 
CONTADOR 
select count(carga_horaria) from curso; //retorna o numero de registros de carga horaria que aparece em curso 
 
SOMATORIA DE VALORES 
select sum(carga_horaria) from curso; //soma todos os valores de carga horaria de curso. 
 
AGRUPANDO DADOS 
select nom_cidade, count(*) from aluno group by nom_cidade; //retorna quantas vezes o nome cidade aparece na tabela 
select nom_cidade, count(*) from aluno group by nom_cidade having count(*) >= 2; //agrupa o resultado pelo agrupamento 
tomando condição pelo que defini. mostra apenas os registros que aparecem pelo menos duas vezes 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
aula_04_09 OPERAÇÕES BASICAS DA COMPUTAÇÃO - BD 
 
================================================================================== 
INSERT - inserir dados na tabela, trabalha com apenas uma tabela por vez 
 
Instrução : 
- temos as colunas que receberão dados e tambem os valores referentes. 
- nº de colunas tem que ser igual ao numero de valores. 
- os critérios de validação dos campos devem ser respeitados, em um campo q recebe valores 
do tipo INT tem que passar valores do tipo INT. 
 
Definição: 
INSERT into aluno (nom_aluno,nom_cidade) 
VALUES ('Joaozinho', 'BATATAIS'); //caso precise inserir mais uma linha, preciso usar o ( ; ) para fazer essa nova inclusão 
INSERT into aluno (nom_aluno,nom_cidade) 
VALUES ('Cleusmildo','FRANCA'); 
 
================================================================================== 
UPDATE - atualização das informações contidas na tabela. 
 
Definição: 
UPDATE aluno 
Set nom_aluno = 'Juquinha' //quais colunas serão alteradas 
WHERE cod_aluno = 4; //nunca utilizo a mesma coluna marcada para mudança para condição da mudança 
 
Se eu quizer mudar mais de uma coluna. 
 
UPDATE aluno 
Set nom_aluno = 'Juquinha', nom_cidade = 'Tambau' 
WHERE cod_aluno = 4; 
 
================================================================================== 
DELETE - excluir informações da tabela. 
 
Definição: 
DELETE from aluno 
WHERE cod_aluno = 4; 
 
caso queira excluir mais codigos 
 
Definição: 
DELETE from aluno 
WHERE cod_aluno IN (4,5,6); //posso fazer isso tanto na exclusão quanto inclusão de dados. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
DDL - Criar estruturas dentro do banco de dados. 
 
Iremos gerar a tabela USUARIO, PESSOA FISICA e PESSOA JURIDICA. 
 
Usuario > principal 
Pessoa Fisica > relacionar com o ususario 
Pessoa Juridica > relacionar com o usuario 
 
CREATE TABLE usuario( 
 
codusuario INTEGER NOT NULL, 
nome VARCHAR(500), 
endereco VARCHAR(500), 
numero VARCHAR(20), 
login VARCHAR(50) NOT NULL, 
senha VARCHAR(30) NOT NULL, 
 
PRIMARY KEY (codusuario) 
 
 
 ) - padrao inicial para criar uma tabela no banco 
 
Definição para criação de colunas ( nome coluna / tipo de dado) 
Sempre ao termino de definição de uma coluna eu coloco ( , ) 
 
Diferença entre o CHAR e o VARCHAR ( char é quando incluimos um dado com apenas 1 caracter, o varchar é de tamanho 
variavel de no maximo 500) 
Nenhuma estrutura computacional aceita definição com gramatica ( ex: ç , acentos, etc.) 
Na ultima instrução nao usamos virgula, terminase como foi codificada. 
 
CREATE TABLE pessoafisica ( 
 
codpessoafisica INTEGER NOT NULL, 
codusuario INTEGER NOT NULL, 
cpf VARCHAR(20), 
rg VARCHAR(30), 
PRIMARY KEY(codpessoafisica), 
CONSTRAINT usuarioFK FOREIGN KEY(codusuario) REFERENCES usuario(codusuario)ON DELETE RESTRICT 
 
 
) 
 
CREATE TABLE pessoajuridica ( 
 
codpessoajuridica INTEGER NOT NULL, 
codusuario INTEGER NOT NULL, 
cnpj VARCHAR(25), 
ie VARCHAR(50), 
PRIMARY KEY(codpessoajuridica), 
CONSTRAINT usuariofk FOREIGN KEY(codusuario) REFERENCES usuario(codusuario)ON DELETE RESTRICT 
 
 
) 
CONSTRAINT metodo para nomear as constantes da tabela, constantes = nao variam. 
 
ON DELETE RESTRICT serve para evitar que aconteça uma exclusão generalizada dos dados. 
 
Caso ocorra um erro e eu queira consertar, corrigir algo. Da mesma forma que consigo criar tambem consigo apagar, destruir. 
DROP TABLE pessoafisica;