Unidade 2 Introdução ç a SQL
Transcrição
Unidade 2 Introdução ç a SQL
18/08/2008 Faculdade INED Curso Superior de Tecnologia em Banco de Dados Disciplina: Projeto de Banco de Dados Relacional 1 Prof.: Fernando Hadad Zaidan 1 Unidade 2 Introdução ç a SQL 2 2 Introdução à SQL • 2.1 Conceitos iniciais • 2.2 Sintaxe e comandos básicos • 2.3 Operações da álgebra relacional em SQL 3 1 18/08/2008 Objetos básicos do SQL • A linguagem do SQL Server tem os mesmos recursos básicos das outras linguagens de programação comuns; • Comentários Æ /* */ ou -- ; • Identificadores: – Constantes, variáveis e nome de programas; • Palavras-chave reservadas; 4 Tipos de dados • Tipos de dados numéricos; • Tipos de dados de string; • Tipos de dados para data e/ou hora; • Tipos de dados derivados. 5 Tipos de dados Numéricos INT Representa valores inteiros 4 bytes SMALLINT Valores inteiros entre –32768 e 32767 2 bytes DECIMAL(p,[s]) 2 a 17 bytes Descreve valores em ponto fixo. O argumento p (precisão) especifica o número total de algarismos com os dígitos s (escala) de ponto decimal pressuposto à partir da direita NUMERIC(p,[s]) Sinônimo de DECIMAL 6 2 18/08/2008 Tipos de dados Numéricos FLOAT([p]) Representa valores em ponto flutuante, como real. P define a precisão, com p < 25 como precisão simples (4 bytes) e p >= 25 como precisão dupla (8bytes) - 4 e 8 bytes REAL e DOUBLE PRECISION Usado para valores de ponto flutuante. A faixa da valores positivos é de 2,23E-308 a 1,79E+308. Valores negativos é de -2,23E-308 a -1,79E+308 7 Tipos de dados String CHAR[(n)] 1 a 8000 bytes Representa uma string, onde n é o número de caracteres fixo dentro da string. O valor máximo de n é 8000. Se n for omitido, o comprimento suposto será 1. VARCHAR[(n)] Descreve uma string de tamanho variável 1 a 8000 bytes. 8 BLOB - Firebird • Tipo que possui tamanho variável; • Não é conhecido o tamanho correto momento de criação; • Poderá ser usado para armazenar qualquer dado em que não se sabe seu tamanho exato, como fotos, textos (memos), gráficos, etc. • Não pode ser indexado. • No momento de criação de um campo BLOB, deve se observar seu sub-tipo: SUB_TYPE 0 Æusado para armazenar dados binários - fotos; SUB_TYPE 1 Æ usado para armazenar textos - memos; SUB_TYPE -1 Æ inteiro negativo. 9 3 18/08/2008 Date / Time • DATE dd/mm/aaaa ou dd.mm.aaaa • TIME hh:mm:ss • TIMESTAMP dd/mm/aaaa hh:mm:ss 10 Esquema de uma relação • Relação: normalmente chamada de tabela nos SGBDs relacionais do mercado. É composta por 2 partes: – Cabeçalho: conjunto fixo de atributos, que são as colunas ou campos da relação. O número de atributos define o grau da relação. – Corpo: conjunto variável de tuplas, que são as linhas d relação. da l ã O número ú d de ttuplas l d define fi a cardinalidade da relação. Código 10 20 30 40 Nome Ana Maria Paulo Beatriz Leonardo Função Idade Analista Programador Analista Gerente Salário 30 22 40 45 1500 900 2200 3500 11 Esquema de uma relação • Nas relações podem ser observadas as seguintes propriedades: – – – – Não existem tuplas duplicadas; A ordem das tuplas é insignificante; A ordem dos atributos é insignificante; Os valores dos atributos são atômicos atômicos, sem grupos repetitivos; – Os atributos podem ter valores nulos, sendo que nulo significa inexistência de valor, diferente, portanto, de zero e branco. 12 4 18/08/2008 Domínios • Conjunto de valores a partir do qual um ou mais atributos extraem seus valores. • Os atributos são comparáveis entre si, se e somente se pertencerem a um mesmo domínio. • No exemplo da relação de funcionários, poderíamos dizer que o domínio do atributo idade é um número inteiro entre 13 e 70 e o domínio do salário seria um número real entre 100,00 e 10.000,00. 13 Domínios • Domínio é um conceito semântico. Por exemplo, peso e peça são numéricos, mas têm significado diferente. O mesmo domínio pode ser utilizado múltiplas vezes em uma relação. • Ex: relação Peça (Cod-Peça, Nome, Peso) – 3 atributos diferentes com 2 domínios apenas Nos SGDBs podem ser criados os domínios específicos. 14 Chaves Chave Candidata: conjunto de atributos que definem unicamente e minimamente cada tupla de uma relação. Ex: Funcionario (Código, Nome, CPF). Código e CPF são chaves candidatas. Chave Primária (Primary Key): é uma chave escolhida entre as chaves candidatas para identificar a tupla da relação relação. Ex: Funcionario (Código, Nome, CPF). Código é a chave primária. Chave Alternativa: são as chaves candidatas não escolhidas para chave primária. Ex: Funcionario (Código, Nome, CPF). CPF é a chave alternativa. 15 5 18/08/2008 • Chave Estrangeira (Foreign Key): conjunto de atributos de uma relação R1 que constituem chave primária de uma outra relação R2. - As chaves estrangeiras funcionam como elo de ligação entre relações. - Ex: Funcionario (Cod-Emp, Nome, Cod-Depto(FK)) Departamento (Cod-Depto, Desc_Depto) 16 PK/FK X CARDINALIDADE Um-para-Muitos (1:N) • A PK da entidade cuja cardinalidade é 1, é FK na entidade cuja cardinalidade é N. Departamento 1 N Tem Funcionário @MATRÍCULA NOMFUNC ENDFUNC SALFUNC CODDEP (FK) @CODDEP NOMEDEP PK FK 17 Um-para-Um (1:1) A PK da entidade que tem o maior “peso” no relacionamento é FK na outra entidade. Agência @ CODAGE NOMEAGE ENDAGE PK 1 Ch fi Chefia 1 Gerente @ MATRÍCULA NOMGERENTE CODAGE(FK) FK 18 6 18/08/2008 Muitos-para-Muitos (N:N) As PKs das entidades são FKs no relacionamento que será implementado fisicamente como uma nova entidade. Normalmente, a concatenação das FKs formarão a PK desta nova entidade. Funcionário N Projeto @ CODPRJ (FK) @ MATFUC (FK) @ MATFUC NOMFUC ENDFUC VALSALFUC PK N Equipe FK FK @ CODPRJ NOMPRJ DTAINI DTAFIM PK 19 Tipos de integridade de dados 20 Restrições • Restrição de domínio – Especifica que o valor de cada atributo A de uma relação deve ser um valor atômico. • Ex: Atributo Cargo na entidade funcionário possui um único valor; • Restrições de chave – Tuplas de uma mesma relação não podem ter valores iguais para todos os seus atributos --> Chave Primária <> • Restrições de Integridade de Entidade – Nenhuma chave primária pode ser nulo 21 7 18/08/2008 • Restrição de Integridade Referencial – Se uma entidade X possui uma chave estrangeira (FK) que corresponde à chave primária (PK) de outra entidade Y, então cada valor da FK da entidade X deve ser igual ao valor da PK da entidade Y e se não existir, ser totalmente NULA, ou seja, não possuir valor (Regra de Inclusão). • T1[Fk]=T2[Pk], T1[Fk] T2[Pk] onde d T2 é alguma l ttupla l d de R2 R2; ou • T1[Fk] é nulo • Na prática, implica que não são permitidos valores de chave estrangeira que não tenham sido cadastrados previamente como chave primária. 22 • Para garantir a integridade, a exclusão de um valor de PK da entidade Y é dependente da existência de ocorrências de FK na entidade X (Regra de Exclusão). • Cascade(C): A exclusão de um valor de PK implica na exclusão dos valores de FKs correspondentes. • Restrict (R): ( ) A exclusão de um valor de PK só ó pode ser efetuada quando não houver valores de FKs correspondentes. • Set Null (N): A exclusão de um valor de PK implica na atualização dos valores de FKs com NULO, para isso a natureza do preenchimento da FK deve ser opcional. 23 • Constraint Æ restrições que a tabela possui, incluindo chave primária, unicidade de campos, default de campos e verificação de consistências. • Toda restrição constraint deve possuir um nome para controle interno. 24 8 18/08/2008 PRIMARY KEY constraint: garante a integridade de entidade. • Todas as colunas participantes de uma chave primária devem ser NOT NULL; • Apenas uma restrição PRIMARY KEY por tabela. • Cria C i um ííndice di exclusivo l i nas colunas l especificadas. ifi d UNIQUE constraint: como uma tabela possui somente uma chave primária, as chaves alternativas ou candidatas que sejam únicas são implementadas através desta restrição. • Permitem um valor nulo; • Permitem várias restrições UNIQUE em uma tabela. 25 FOREIGN KEY constraint: a tabela referenciada deve possuir uma restrição de PRIMARY KEY ou UNIQUE. • Esta restrição de chave estrangeira não cria índices automaticamente; • F Fornecem uma integridade i id d referencial f i ld de uma ou várias ái colunas; 26 DEFAULT constraint: especifica o valor default que será gravado em uma coluna quando o valor do campo não for informado no momento do INSERT. • Aplicam-se apenas a instruções INSERT; • Apenas uma restrição DEFAULT por coluna; CHECK constraint: especifica a validação do domínio do campo. • São usadas com as instruções INSERT e UPDATE; • Podem fazer referência a outras colunas na mesma tabela; • Não podem conter subconsultas. 27 9 18/08/2008 Definição da Base de Dados em SQL CREATE TABLE CREATE TABLE table_name ( Atibuto1Dominio1, …, AtibutonDominion <regras de integridade1> …, <regras de integridaden> ) 28 CREATE TABLE CREATE TABLE Funcionario ( Matric INT NOT NULL, Nome CHAR(30) NOT NULL, Salario DECIMAL NOT NULL, C Cargo C CHAR(15) ( ) DEFAULT 'Analista', Estado CHAR(2) NOT NULL, Idade SMALLINT NOT NULL, Cod_Depto SMALLINT NOT NULL, CONSTRAINT chavefunc PRIMARY KEY (Matric), CONSTRAINT uniconome UNIQUE (Nome), CONSTRAINT checkestado CHECK (Estado IN ('MG', 'RJ', 'SP'))) 29 ALTER TABLE • O comando ALTER TABLE pode ser usado para, por exemplo, acrescentar colunas numa tabela. A coluna é adicionada no final da tabela. ALTER TABLE Funcionario ADD Aniversario DATE ALTER TABLE Funcionario ADD CONSTRAINT CHAVEFUNC PRIMARY KEY (MATRIC); 30 10 18/08/2008 DROP TABLE • O comando DROP TABLE elimina a estrutura da tabela e os registros da mesma. DROP TABLE Funcionario; Em alguns SGBDs, ao se eliminar uma tabela, todas as estruturas relacionadas a mesma (visões, índices) são também excluídos automaticamente. 31 CREATE TABLE Departamento ( Cod_Depto SMALLINT NOT NULL, Desc_Depto VARCHAR(30)); ALTER TABLE departamento ADD CONSTRAINT CHAVECOD PRIMARY KEY (COD_DEPTO); Departamento Funcionario Funcionario ALTER TABLE Departamento ----------------- ADD CONSTRAINT Depto_Func ---------------FOREIGN KEY (Cod_Depto) REFERENCES Funcionario Departamento (Cod_Depto); 32 ÍNDICES CREATE INDEX • Os índices são estruturas físicas de banco de dados criadas para otimizar a performance no acesso. • O Os comandos d SELECT que envolvem l ORDER BY fificam mais rápidos após a criação de índices pelos campos de ordenação. • Para escolher bem os índices, analisa-se quais campos da tabela participam das expressões WHERE de comandos de SELECT, UPDATE e DELETE. 33 11 18/08/2008 • No entanto, o uso excessivo de índices pode ser prejudicial ao desempenho, pois todo comando que atualiza a tabela origem pode gerar uma alteração no índice. • Os índices não são visíveis para o usuário, pois o algoritmo g do otimizador do SGBD é q que decide se deve ou não utilizá-los. • Os índices geralmente são definidos em conjunto pelo DBA e pelo analista do sistema, que conhecem quais são as consultas mais críticas que evolvem um maior volume de acesso a dados. 34 • Os índices podem ou não ser únicos (UNIQUE). • O índice é único quando não se permitem repetições. Índice pela chave primária é sempre UNIQUE. • Em alguns SGBDs este índice pela chave primária já é gerado d automaticamente. t ti t • Portanto, os índices são criados usualmente para as chaves primárias e para as chaves estrangeiras, visando agilizar os comandos que envolvem junção de tabelas. 35 • O índice deve ser o mais seletivo possível. Ex.: não se deve criar um índice para o campo Sexo Æ só 2 valores. CREATE [UNIQUE] INDEX Nome-do-índice ON tabela (coluna ou lista-de-colunas) • Exemplos: CREATE INDEX IX_Cod_Depto ON Funcionario (Cod_Depto); CREATE UNIQUE INDEX xfuncionarios ON Funcionario (Matricula); 36 12 18/08/2008 DCL - Segurança e Controle de Dados GRANT e REVOKE • Os bancos de dados envolvem armazenamento de informações estratégicas, e às vezes sigilosas, da organização. • O aspecto segurança de dados relaciona qual usuário ou grupo de usuários tem privilégio de INSERT, DELETE, SELECT ou UPDATE em uma tabela ou visão. • O privilégio de SELECT é o mais usual, sendo os outros mais restritos. 37 • Quando se deseja conceder todos os privilégios, a palavra chave ALL é utilizada. • Quando se deseja conceder privilégios para todos os usuários, a palavra chave PUBLIC é utilizada. • O comando REVOKE tem o efeito oposto do GRANT, pois revoga privilégios já concedidos. • Cabe ao DBA em conjunto com o analista de sistema e o usuário gestor do sistema estabelecer a política de privilégios. 38 GRANT GRANT <lista de operações> ON <tabela ou visão> TO <usuário ou PUBLIC> Exemplos: GRANT SELECT, INSERT ON FUNCIONARIO TO MARIA GRANT ALL ON FUNCIONARIO TO CARLOS, ANTONIO GRANT SELECT ON DEPARTAMENTO TO PUBLIC GRANT ALL ON FUNCIONARIO TO PUBLIC Æ Concede privilégios para todos nesta tabela. 39 13 18/08/2008 REVOKE REVOKE <lista de operações> ON <tabela ou visão> FROM <usuário ou PUBLIC> • Exemplos: REVOKE SELECT, INSERT ON FUNCIONARIO FROM MARIA, CARLOS REVOKE ALL ON FUNCIONARIO FROM PUBLIC Æ Retira privilégios de todos nesta tabela. 40 DML SELECT • Sintaxe: SELECT <lista de atributos> FROM <nome das tabelas> WHERE <condição de pesquisa / filtro> create table Peca ( Peca Cod_Peca Nome_Peca Preco Qte 56 Peca X 23,90 10 99 Peca Y 56,99 5 200 Peca Z 80,00 0 Cod_Peca smallint not null, Nome_Peca varchar(30), Preco decimal(12,2), Qte int); alter table peca add constraint pkcodpec primary key (cod_peca); 41 • Selecionar o código e o nome das peças com código menor do que 100: SELECT Cod_Peca, Nome_Peca FROM Peca WHERE Cod_Peca < 100 • Selecionar todas as informações de todas as peças: SELECT * FROM Peca SELECT ALL Nome_Peca FROM Peca ALL – palavra-chave que especifica explicitamente que as duplicidades não serão elimindas. 42 14 18/08/2008 INSERT - Inserção Sintaxe - Inserção Unitária: INSERT INTO <tabela> ( <lista-de-colunas>) VALUES ( <li <lista-de-valores>) t d l >) – Inserir Cod_Peca, Nome_Peca, Preco INSERT INTO Peca (Cod_Peca, Nome_Peca,Preco) VALUES (380,’Peca W’,77.00) 43 – Inserir uma Peca com todos os atributos INSERT INTO Peca VALUES (423,’Peca K’,100.00,15) Sintaxe - Inserção ç em Massa: INSERT INTO <tabela1> (<lista-de-colunas>) SELECT ... 44 UPDATE - Alteração UPDATE <tabela> SET <coluna1> = <expressão1>, <expressão1> <coluna2> =<expressão2>, ... WHERE <condição-de-alteração> 45 15 18/08/2008 Exemplos: Alterar o Preco da peça 200 de 80,00 para 90,00 UPDATE Peca SET Peso = 90.00 WHERE Cod_Peca = 200 Alterar o preco das peças cuja qte for menor que 10, para 50,00 UPDATE Peca SET Preco = 50.00 WHERE Qte < 10 or Qte is null 46 DELETE (Exclusão) DELETE FROM <tabela> WHERE <condição-de-exclusão> Excluir a p peça ç 200: DELETE FROM Peca WHERE Cod_Peca = 200 Excluir as peças que tem mais de 1000 na Qte: DELETE FROM Peca WHERE Qte > 1000 47 Funcionalidades do SQL Operadores: – – – – – – Comparação: =, <>, >, <, >=, <= Lógicos: AND, OR, NOT BETWEEN <expressão1> AND <expressão2>: testa intervalo IN ( <lista de valores>): testa presença na lista IS NULL: testa nulo LIKE: testa conteúdo de string de caracteres 48 16 18/08/2008 Comparação de Padrões de Strings LIKE – O operador LIKE é usado quando se deseja obter colunas de um registro que sigam um determinado padrão p p pré-especificado. p Quando se q quer saber os nomes de todos os funcionários cujo nome começa com JOAO ou termina com SILVA, usa-se o LIKE. – O caracter % dentro da expressão LIKE tem a mesma função do caracter * no MS-DOS, assim como o _ (underscore) tem a semelhança com o ? do MS-DOS. 49 Like - resumo Resultado LIKE ‘A%’ Quaisquer strings que iniciem com a letra A. LIKE ‘%A’ Quaisquer strings que finalizem com a letra A. LIKE ‘%A%’ Quaisquer strings que possuem a letra A em qualquer posição. LIKE ‘A_’ Strings de dois caracteres que tenham a primeira letra A e qualquer outro caractere na segunda posição. LIKE ‘_A A’ Strings de dois caracteres com qualquer caractere na primeira posição e a última letra seja a letra A. LIKE ‘_A_’ Strings de três caractere – a segunda letra seja A - independentemente do primeiro ou do último caractere. LIKE ‘%A_’ Quaisquer strings que tenham a letra A na penúltima posição - a última posição seja qualquer outro caractere. LIKE ‘_A%’ Quaisquer strings que tenham a letra A na segunda posição - o primeiro caractere seja qualquer outro caractere. LIKE ‘____’ Quaisquer strings com exatamente quatro caracteres. LIKE ‘____%’ Quaisquer strings com pelo menos quatro caracteres. 50 Like - Exemplos Æ Código e Nome das Peças cujo nome inicia com Peca SELECT Cod_Peca, Nome_peca FROM Peca WHERE Nome_Peca LIKE 'Peca%' Æ Código g e Descrição ç do Deparmento p cuja j 3ª letra da descrição é p Select Departamento.cod_depto, Departamento.desc_depto From Departamento Where Departamento.desc_depto Like '__p%' Æ E se tiver p escrito em maiúsculo e minúsculo? Where upper(Departamento.desc_depto) Like '__P%' 51 17 18/08/2008 Parâmentos • Se desejar entrar com parâmetros em sentenças, utiliza-se o : (dois pontos) antes e o nome de uma variável; • Uma janela para entrada de dados vai abrir. Exemplos: … Where Nome Like :PNome Pode-se entrar com valores para PNome 52 Like – Outras funções Concatenar || (pipe) Æ Todos os nomes de departamentos que iniciem e terminem com qualquer coisa. Possibilitar a digitação de caracteres que farão parte do nome. Where Departamento.desc_depto Like '%' || :PNome || '%' 53 Datas • Utiliza-se a função extract com as palavras-chave day, month e year. Exemplo: where extract(month from campoData) = 8 Æ Todos os meses de Agosto do campoData 54 18 18/08/2008 Montagem de Filtros – Na expressão WHERE, especificam-se as condições para seleção das linhas da tabela. Qualquer expressão lógica envolvendo os campos das tabelas é válida. Os campos que fazem parte da expressão WHERE não precisam necessariamente ter sido selecionados na expressão SELECT. Æ Selecionar Código e Nome das Pecas que estão com Qte em estoque menor do que 5 unidades neste ano. SELECT Cod_Peca, Nome_peca FROM Peca WHERE Qte < 5 and extract(year from DataUltMov) = 2008 55 ORDER BY - Ordenação – Exibir os registros em uma determinada ordem. Crescente (ASC – default) ou decrescente (Desc). Caso a expressão não esteja presente, os registros serão exibidos na ordem em que foram inseridos na tabela. – Os campos que constam da expressão ORDER BY devem obrigatoriamente estar presentes na expressão SELECT. SELECT Não é necessário possuir um índice fisicamente criado e composto pelos campos da ordenação para usar o ORDER BY. No entanto, caso o índice exista o comando será executado mais rápido. rápido Pode-se utilizar números indicando que a ordenação será feita por determinado campo de acordo com a ordem do SELECT. 56 Exemplos – Order by SELECT Cod_Func, Nome FROM Funcionario ORDER BY Nome SELECT Salario, Salario Nome FROM Funcionario ORDER BY 1 DESC Æ Nomes dos funcionários em ordem decrescente de salário. 57 19 18/08/2008 DISTINCT - Distinção – Sintaxe: DISTINCT nome-do-campo – O operador DISTINCT elimina ocorrências repetidas de um mesmo campo. Não faz sentido utilizar DISTINCT para um campo que já seja chave primária. SELECT DISTINCT Cod_Depto FROM Funcionario 58 Exemplos - Distinct SELECT DISTINCT Cidade FROM Clientes Æ Nomes das cidades onde se têm clientes, sem repetições p ç de cidades. SELECT COUNT (DISTINCT Cod_Depto) FROM Funcionario Æ Quantidade de departamentos dos Funcionarios 59 IN - Pertinência – O operador IN verifica se um elemento pertence a um conjunto, isto é, se um campo assume o valor de algum membro de uma lista de valores. – É utilizado para substituir grandes expressões de OR para o mesmo campo. SELECT Nome, Salario FROM Funcionario WHERE Cargo IN (‘ANALISTA’, ‘PROGRAMADOR’, ‘GERENTE’ ) É melhor ou pior? WHERE Cargo = ‘ANALISTA’ OR Cargo = ‘PROGRAMADOR’ OR Cargo = ‘GERENTE’ 60 20 18/08/2008 BETWEEN – Intervalos – O operador BETWEEN permite testar se um determinado campo assume o valor dentro de um intervalo especificado. – É utilizado por ser mais prático do que o teste de >= e <=. SELECT Nome FROM Funcionario WHERE Salario BETWEEN 1800 AND 2000 É melhor ou pior? SELECT Nome FROM Funcionario WHERE Salario >= 1800 AND Salario <= 2000 61 NULL - Tratamento de Nulos – A palavra chave NULL é usada para representar valores nulos, o que tem significado diferente de branco ou zero. O nulo significa não se aplica, ou seja, o campo não assume nenhum valor. No momento da criação da tabela é que se define se o campo pode ou não aceitar nulos nulos. SELECT Nome FROM Funcionario WHERE Comissao IS NOT NULL Æ Nome dos funcionários que recebem alguma comissão. 62 Questões para discussão 1 - É possível utilizar na linguagem SQL a sintaxe: UPDATE ... SET ... FROM...? 2 - Por que o ‘*’ deve ser evitado em consultas de recuperação de dados (SELECT)? 3 - Alguns SGBD’s permitem a construção de consultas de inserção de dados (INSERT) sem que sejam especificadas as colunas nas quais os dados devem ser inseridos. Quais as desvantagens dessa “omissão”? 63 21