Aula 16 120411 SQL (Parte 1)

Prévia do material em texto

Banco de Dados
Haroldo Amaral (haroldo.amaral@gmail.com)
Licenciatura em Computação – 5º Período
12/04/2011
Aula 16 – SQL (Parte 1)
Introdução
Criação de Esquemas e Tabelas
Alteração de Tabelas
Inserção, Remoção e Atualização de Valores em Tabelas
Privilégios de Acesso
12/04/2011
Banco de Dados
2
Introdução
12/04/2011
Banco de Dados
3
Introdução
12/04/2011
Banco de Dados
4
Primeira versão:
SEQUEL, definida por Chamberlain, em 1974, na IBM
Revisada e ampliada entre 1976 e 1977, tendo seu nome alterado para SQL
Linguagem padrão para SGBDs Relacionais
Em 1986, o American National Standard Institute (ANSI) e a International Standard Organization (ISO) definiram uma versão padrão da SQL (SQL-86 ou SQL1)
Em 1992, o padrão foi revisado e bastante modificado, gerando o SQL-92 (ou SQL2)
Em 1999, desenvolvido o padrão SQL:1999, que começou como SQL3
Posteriormente, SQL :2003, SQL:2006 (recursos XML) e SQL:2008 (recursos de BD de objeto)
Introdução
12/04/2011
Banco de Dados
5
Fundamentada no modelo relacional (álgebra relacional e cálculo relacional de tupla), incluindo comandos para:
Definição de dados
Consulta a dados
Atualização de dados
DDL e DML
Introdução
12/04/2011
Banco de Dados
6
Além disso, ela possui facilidades para:
Definir visões sobre o banco de dados
Especificar segurança e autorização
Definir restrições de integridade
Especificar controles de transação
Ela, também, possui regras para embutir instruções SQL em linguagens de programação (Java, COBOL, C/C++, etc.)
Utilizada tanto de forma interativa como incluída em linguagens hospedeiras
Introdução
12/04/2011
Banco de Dados
7
Introdução
12/04/2011
Banco de Dados
8
Os padrões mais recentes (a partir do SQL:1999) são divididos em duas partes:
Uma especificação núcleo
Extensões especializadas
O núcleo é implementado por todos os fornecedores de SGBDR compatíveis
As extensões são implementadas como módulos opcionais adquiridos para aplicações de BD específicas
Mineração de dados, dados espaciais, dados temporais, data warehousing, processamento analítico on-line (OLAP), dados multimídia, etc.
Introdução
Enfoques da Linguagem
12/04/2011
Banco de Dados
9
Introdução
Enfoques da Linguagem
12/04/2011
Banco de Dados
10
Introdução
Vantagens
Desvantagens
12/04/2011
Banco de Dados
11
Independência de fabricante
Portabilidade entre sistemas
Redução de custos com treinamento
Comandos em inglês
Consulta interativa
Múltiplas visões de dados
Manipulação dinâmica dos dados
A padronização inibe a criatividade
Está longe de ser uma linguagem relacional ideal
Falta de ortogonalidade nas expressões
Discordância com as linguagens hospedeiras
Não dá suporte a alguns aspectos do modelo relacional
Comandos
12/04/2011
Banco de Dados
12
Criação de esquemas
Criação, alteração e destruição de tabelas
Inserção, modificação e remoção de dados
Extração de dados de uma tabela – consultas
Definição de visões
Definição de privilégios de acesso
12/04/2011
Banco de Dados
13
Exemplo Ilustrativo
12/04/2011
Banco de Dados
14
Pnome
Minicial
Unome
Cpf
Datanasc
Endereco
Sexo
Salario
Cpf_superv
Dnr
EMPREGADO
Dnome
Dnumero
Cpf_gerente
Data_inicio_gerente
DEPARTAMENTO
Dnumero
Dlocal
LOCALIZACOES_DEP
Projnome
Projnumero
Projlocal
Dnum
PROJETO
Fcpf
Pnr
Horas
TRABALHA_EM
Fcpf
Nome_dependente
Sexo
Datanasc
Parentesco
DEPENDENTE
CHAVES ESTRANGEIRAS?
12/04/2011
Banco de Dados
15
Criação de Esquemas e Tabelas
O Comando CREATE SCHEMA
12/04/2011
Banco de Dados
16
Um esquema SQL é identificado por um nome de esquema, incluindo um identificador de autorização para indicar o proprietário (usuário ou conta), bem como descrições para cada elemento (tabelas, restrições, views, domínios e outras construções)
A instrução CREATE SCHEMA é usada, de forma geral:
Exemplo:
	CREATE SCHEMA EMPRESA AUTHORIZATION ‘Jose’;
Os elementos do esquema podem ser definidos depois
CREATE SCHEMA <nome do esquema>
	AUTHORIZATION <nome usuario/conta>;
Catálogo de um Esquema
12/04/2011
Banco de Dados
17
Além do conceito de esquema, SQL usa o conceito de catálogo
Coleção nomeada de esquemas
Contém um esquema especial, chamado INFORMATION_SCHEMA, que fornece informações sobre todos os esquemas no catálogo e todos os descritores de seus elementos
As restrições de integridade podem ser definidas entre as relações somente se existirem nos esquemas do mesmo catálogo
Os esquemas dentro do mesmo catálogo também podem compartilhar elementos, como definições de domínio
O Comando CREATE TABLE
12/04/2011
Banco de Dados
18
O comando CREATE TABLE é usado para criação de tabelas, dando-lhe um nome e especificando seus atributos e restrições (de chave, de integridade de entidade, de integridade referencial, etc.)
Forma geral:
Onde a descrição dos atributos  <nome> <tipo>
Exemplo:
	CREATE TABLE FUNCIONARIO ...
CREATE TABLE <nome da tabela>
	(<descrição dos atributos>
	 <descrição das chaves>
	 <descrição das restrições>);
Tipos de Dados de Atributos e Domínios
12/04/2011
Banco de Dados
19
Numéricos
Inteiros de vários tamanhos
INT (ou INTEGER) e SMALLINT
Reais (ponto flutuante) de várias precisões
FLOAT (ou REAL) e DOUBLE PRECISION
O formato dos números pode ser declarado usando DECIMAL(i, j) ou DEC(i, j) ou NUMERIC(i, j)
i é a precisão  número total de dígitos decimais
j é a escala  número de dígitos depois do ponto decimal
O valor padrão para a escala é zero e o valor da precisão é definido pela implementação
Tipos de Dados de Atributos e Domínios
12/04/2011
Banco de Dados
20
Cadeias de caracteres
De tamanho fixo
CHAR(n) ou CHARACTER(n), onde n é o número de caracteres
Para cadeias de tamanho fixo, uma cadeia mais curta é preenchida com caracteres em branco à direita
Espaços preenchidos são ignorados quando cadeias são comparadas
Para fins de comparação, as cadeias são consideradas ordenadas em ordem alfabética. Assim se uma cadeia str1 aparecer antes de outra cadeia str2, então str1 é considerada menor que str2
De tamanho variável
VARCHAR(n) ou CHAR VARYING(n) ou CHARACTER VARYING(n), onde n é o número máximo de caracteres
Literais são delimitados por aspas simples e são case sensitive
Concatenação de cadeias  operador ||
Tipos de Dados de Atributos e Domínios
12/04/2011
Banco de Dados
21
Cadeias de caracteres
Outro tipo de dado de tamanho variável (CHARACTER LARGE OBJECT ou CLOB) está disponível para especificar colunas que possuem grandes valores de texto,como documentos
O tamanho máximo de CLOB pode ser especificado em kilobytes (K), megabytes (M) ou gigabytes (G)
Por exemplo:
CLOB(20M) especifica um tamanho máximo de 20 megabytes
Tipos de Dados de Atributos e Domínios
12/04/2011
Banco de Dados
22
Cadeias de bits (Bit-string)
De tamanho fixo n
BIT(n)
De tamanho variável
BIT VARYING(n), onde n é o número máximo de bits
O valor padrão para n é 1
Literais são delimitados por aspas simples, porém precedidos por B para distingui-los de cadeias de caracteres
B’10101’
Outro tipo de dados de tamanho variável (BINARY LARGE OBJECT ou BLOB) está disponível para especificar colunas que possuem grandes valores binários, como imagens
O tamanho máximo de BLOB pode ser especificado em kilobits (K), megabits (M) ou gigabits (G)
Por exemplo:
BLOB(30G) especifica um tamanho máximo de 30 gigabits
Tipos de Dados de Atributos e Domínios
12/04/2011
Banco de Dados
23
Booleano
Possui os valores TRUE e FALSE
Como SQL permite valores null, um terceiro valor booleano foi definido - UNKNOWN
Tipos de Dados de Atributos e Domínios
12/04/2011
Banco de Dados
24
Datas e Horários
DATE (data) contém dez posições e seus componentes são YEAR (ano), MONTH (mês) e DAY (dia), no formato YYYY-MM-DD
TIME tem, no mínimo, oito posições com os componentes HOUR (hora), MINUTE (minuto) e SECOND (segundo), no formato HH:MM:SS
A comparação < pode ser usada com datas e horários
Literais são representados porcadeias entre aspas simples precedidas das palavras DATE ou TIME 
Por exemplo: 
DATE ‘2008-09-27’, TIME ‘09:12:47’
Tipos de Dados de Atributos e Domínios
12/04/2011
Banco de Dados
25
Datas e Horários
Um tipo TIME(i), onde i é chamado de precisão em segundos fracionários de tempo, especifica i + 1 posições adicionais para TIME
Uma posição para um caractere separador de período adicional (.), e i posições para especificar as frações de um segundo
Por exemplo:
12:00:30.57
Um tipo TIME WITH TIME ZONE inclui seis posições adicionais para especificar o deslocamento com base no fuso horário universal padrão, que está na faixa de +13:00 a -12:59, em unidades de HOURS:MINUTES
Caso WITH TIME ZONE não for incluído, o valor padrão é o fuso horário local para a sessão SQL
Tipos de Dados de Atributos e Domínios
12/04/2011
Banco de Dados
26
Datas e Horários
Um tipo de dado TIMESTAMP inclui os campos DATE e TIME, mais um mínimo de seis posições para frações decimais de segundos e um qualificador opcional WITH TIME ZONE
Valores literais são representados por cadeias entre aspas simples precedidos pela palavra-chave TIMESTAMP, com um espaço em branco entre data e hora
Por exemplo:
TIMESTAMP ‘2011-05-12 10:50:00.123’
Outro tipo relacionado a DATE, TIME e TIMESTAMP é o INTERVAL, que especifica um intervalo (um valor relativo) que pode ser usado para incrementar (ou decrementar) um valor absoluto de uma data, hora ou timestamp
Tipos de Dados de Atributos e Domínios
12/04/2011
Banco de Dados
27
É possível declarar um domínio e o seu nome ser usado na especificação de um atributo
Por exemplo, para criar um domínio TIPO_CPF, usa-se o seguinte comando:
CREATE DOMAIN TIPO_CPF AS CHAR(11);
CREATE DOMAIN <nome dominio> AS <tipo domínio>;
Restrições de Atributos
12/04/2011
Banco de Dados
28
Permitir valores nulos e não nulos
Como SQL permite que atributos possuam valores nulos (NULL), uma restrição NOT NULL pode ser especificada, caso o valor NULL não seja permitido para determinado atributo
Por exemplo:
Nome VARCHAR(15) NOT NULL
Também é possível definir um valor default para um atributo, através da cláusula DEFAULT <valor>
Caso nenhum valor default seja atribuído a atributos que são NOT NULL, seu valor será NULL
Por exemplo:
Cpf_gerente CHAR(11) NOT NULL DEFAULT ‘12345678900’
Restrições de Atributos
12/04/2011
Banco de Dados
29
Através da cláusula CHECK, após uma definição de atributo ou domínio, é possível limitar os seus valores
Por exemplo, para não permitir que o salário seja inferior ao valor mínimo:
Salario INT NOT NULL CHECK (Salario >= 450)
Por exemplo, para permitir que o número de departamento seja definido no intervalo de 1 até 21:
CREATE DOMAIN D_NUM AS INTEGER
	CHECK (D_NUM > 0 AND D_NUM < 21)
Restrições de Chaves Primárias
12/04/2011
Banco de Dados
30
Através da cláusula PRIMARY KEY, especificamos os atributos que compõem a chave primária de uma tabela
Por exemplo:
PRIMARY KEY (Dnumero, Dlocal)para a tabela LOCALIZACAO_DEP
Caso a chave primária possua um único atributo, podemos usar a cláusula PRIMARY KEY diretamente na definição do atributo
Por exemplo:
Para a tabela DEPARTAMENTO, Dnumero INT PRIMARY KEY
A cláusula UNIQUE especifica chaves alternativas, da mesma forma como especificamos chaves primárias
Por exemplo:
Na tabela DEPARTAMENTO, UNIQUE (Dnome), para indicar que esse atributo é uma chave alternativa
PRIMARY KEY <atributos>;
Restrições de Integridade Referencial
12/04/2011
Banco de Dados
31
Através da cláusula FOREIGN KEY, especificamos chaves estrangeiras
Por exemplo:
FOREIGN KEY (Cpf_gerente) REFERENCES FUNCIONARIO(Cpf), para definir a chave estrangeira da tabela DEPARTAMENTO, Cpf_gerente, que referencia a chave primária Cpf da tabela FUNCIONARIO, indicando o gerente de um departamento
FOREIGN KEY <atributos> REFERENCES 
	<tabela referenciada(atributos)>;
Restrições de Integridade Referencial
12/04/2011
Banco de Dados
32
Entretanto, como vimos, uma restrição de integridade referencial pode ser violada quando:
Tuplas são inseridas ou excluídas ou
Um valor de atributo da chave estrangeira ou primária é modificado
A ação default da SQL é rejeitar a operação de atualização
Porém, o projetista do banco de dados pode especificar uma ação alternativa, conectando uma cláusula de ação a qualquer restrição de chave estrangeira através das opções:
SET NULL, CASCADE e SET DEFAULT qualificadas com ON DELETE ou ON UPDATE
Restrições de Integridade Referencial
12/04/2011
Banco de Dados
33
Por exemplo, na tabela FUNCIONARIO:
	FOREIGN KEY (Cpf_supervisor) REFERENCES FUNCIONARIO(Cpf) ON DELETE SET NULL ON UPDATE CASCADE
Significa que, se a tupla para um funcionário supervisor for excluída, o valor de Cpf_supervisor será automaticamente definido como NULL para todas as tuplas de funcionário que estavam referenciando a tupla do funcionário excluído
Por outro lado, se o valor de Cpf, para um funcionário supervisor, for atualizado, o novo valor será propagado em cascata de Cpf_supervisor para todas as tuplas de funcionário que referencia a tupla de funcionário atualizada
12/04/2011
Banco de Dados
34
Exemplo Ilustrativo
(Esquema 1)
12/04/2011
Banco de Dados
35
12/04/2011
Banco de Dados
36
Nomeação de Restrições
12/04/2011
Banco de Dados
37
As restrições vistas até agora podem ser nomeadas, através da palavra-chave CONSTRAINT
Os nomes de todas as restrições, dentro de um esquema, precisam ser únicos (exclusivos)
Com isso, um nome de restrição é usado para identificar uma restrição caso ela deva ser removida, posteriormente, e substituída por outra
Nomear restrições é opcional
12/04/2011
Banco de Dados
38
Exemplo Ilustrativo
(Esquema 2)
12/04/2011
Banco de Dados
39
12/04/2011
Banco de Dados
40
Alteração de Tabelas
Alteração de Tabelas
12/04/2011
Banco de Dados
41
A SQL possui comandos de evolução de esquema que podem ser usados para alterar um esquema, acrescentando ou removendo tabelas, atributos, restrições e outros elementos
DROP e ALTER
O Comando DROP
12/04/2011
Banco de Dados
42
O comando DROP é usado para remover elementos (tabelas, restrições, domínios) do esquema, inclusive o próprio esquema
Possui duas variações:
CASCADE e RESTRICT
O Comando DROP
12/04/2011
Banco de Dados
43
O comando DROP (continuação)
Para remover o esquema:
DROP SCHEMA <nome esquema> CASCADE / RESTRICT
CASCADE  remove tudo (tabelas, domínios e outro elementos)
RESTRICT  remove apenas se o esquema não tiver elementos
Para remover tabelas:
DROP TABLE <nome tabela> CASCADE / RESTRICT
CASCADE  além da própria tabela, remove todas as restrições , views e outros elementos que referenciam a tabela sendo removida
RESTRICT  remove apenas se a tabela não for referenciada em quaisquer restrições
O comando DROP remove também a definição da tabela do catálogo.
Para remover apenas os registros, o comando DELETE deve ser usado (veremos em breve!)
O Comando ALTER
12/04/2011
Banco de Dados
44
O comando ALTER é usado para alterar a definição de tabelas do esquema, permitindo inserção, remoção e modificação de seus elementos
Por exemplo, para incluir atributo que indique a tarefa de um funcionário:
ALTER TABLE FUNCIONARIO ADD COLUMN Tarefa VARCHAR(12);
Podemos, também, incluir uma cláusula DEFAULT para definirmos um valor padrão para o atributo
ALTER TABLE <nome tabela> <ação>;
O Comando ALTER
12/04/2011
Banco de Dados
45
O comando ALTER (continuação)
Assim como no comando DROP, a remoção possui duas variações, CASCADE e RESTRICT
Por exemplo:
ALTER TABLE FUNCIONARIO DROP COLUMN Endereco CASCADE
Todas as restrições e views que referenciam a coluna são removidas automaticamente do esquema, junto com a coluna
O Comando ALTER
12/04/2011
Banco de Dados
46
O comando ALTER (continuação)
Podemos alterar uma definição de coluna, removendo uma cláusula DEFAULT existente ou definindo uma nova
Por exemplo:
ALTER TABLE DEPARTAMENTO ALTER COLUMN Cpf_gerenteDROP DEFAULT;
ALTER TABLE DEPARTAMENTO ALTER COLUMN Cpf_gerente SET DEFAULT ‘33344455566’;
Podemos alterar as restrições especificadas de uma tabela, acrescentando ou removendo uma restrição nomeada
Por exemplo:
ALTER TABLE FUNCIONARIO DROP CONSTRAINT CHESUPERFUNC CASCADE;
12/04/2011
Banco de Dados
47
Inserção, Remoção e Atualização de Valores em Tabelas
O Comando INSERT
12/04/2011
Banco de Dados
48
O comando INSERT é usado para acrescentar os valores de uma nova tupla em uma tabela, na mesma ordem em que os atributos foram definidos com o comando CREATE TABLE
Por exemplo, para inserir uma tupla na tabela FUNCIONARIO:
INSERT INTO FUNCIONARIO VALUES (‘Ricardo’, ‘K’, ‘Marini’, ‘65329865388’, ‘30-12-1960’, Rua Itapira, 44, Santos, SP’, ‘M’, 37.000, ‘65329865388’);
INSERT INTO <nome tabela> VALUES <valores atributos>;
O Comando INSERT
12/04/2011
Banco de Dados
49
O comando INSERT também permite que o usuário especifique apenas os nomes de atributos cujos os novos valores serão fornecidos
Por exemplo, para inserir uma tupla para um novo funcionário do qual se conhece apenas os atributos Pnome, Unome, Dnr e Cpf:
INSERT INTO FUNCIONARIO(Pnome, Unome, Dnr, Cpf) VALUES (‘Ricardo’, ‘Marini’, 4, ‘65329865388’);
Os demais atributos não especificados serão definidos com o seu valor default ou NULL
Um SGBD que implementa totalmente a SQL deve aceitar ou impor todas as restrições de integridade
O Comando DELETE
12/04/2011
Banco de Dados
50
O comando DELETE remove tuplas de uma tabela
Para tal, uma cláusula WHERE é incluída a fim de selecionar as tuplas a serem excluídas
Quando o comando é executado, as tuplas são explicitamente excluídas de apenas uma tabela por vez
Para que a exclusão se propague para tuplas de outras relações, ações de disparo referencial devem ser especificadas
DELETE FROM <nome tabela> WHERE <condição>;
O Comando DELETE
12/04/2011
Banco de Dados
51
Exemplos de remoção de tuplas com o comando DELETE:
DELETE FROM FUNCIONARIO WHERE Unome = ‘Braga’;
DELETE FROM FUNCIONARIO WHERE Cpf = ‘12345678966’;
DELETE FROM FUNCIONARIO WHERE Dnr = 5;
O comando DELETE sem a cláusula WHERE especifica que todas as tuplas deverão ser removidas
Equivalente ao comando DROP, exceto que a tabela permanece no banco de dados
Por exemplo:
DELETE FROM FUNCIONARIO;
O Comando UPDATE
12/04/2011
Banco de Dados
52
O comando UPDATE é usado para modificar valores de atributo de uma ou mais tuplas selecionadas de uma tabela
Assim como no comando DELETE, uma cláusula WHERE seleciona as tuplas a serem modificadas
Uma cláusula SET adicional especifica os atributos a serem modificados, bem como os seus novos valores
As atualizações de valores de atributos podem ser propagadas para outras tuplas de outras tabelas, caso ações de disparo referencial tenham sido especificadas
UPDATE <nome tabela> SET <atributo = valor> WHERE <condição>;
O Comando UPDATE
12/04/2011
Banco de Dados
53
Exemplos:
Para alterar o local e número de departamento que controla o número de projeto 10 para os novos valores ‘Santo André’ e 5:
UPDATE PROJETO SET Projlocal = ‘Santo André’, Dnum = 5 WHERE Projnumero = 10;
Para dar a todos os funcionários no departamento ‘Pesquisa’ um aumento de 10% no salário
UPDATE FUNCIONARIO SET Salario = Salario *1,1 WHERE Dnr = 5;
12/04/2011
Banco de Dados
54
Privilégios de Acesso
O Comando GRANT
12/04/2011
Banco de Dados
55
Para garantir privilégios de acesso, o comando GRANT pode ser usado da seguinte forma:
Onde:
<privilégios>: SELECT, INSERT, DELETE, UPDATE, ALL PRIVILEGES
<usuário>: usuário cadastrado ou PUBLIC
Exemplo:
Conceder a permissão de consulta sobre a tabela FUNCIONARIO ao usuário ‘acs’
	GRANT SELECT ON FUNCIONARIO TO ‘acs’;
GRANT <privilégios> ON <tabela/view>
TO <usuário>;
O Comando REVOKE
12/04/2011
Banco de Dados
56
Para remover privilégios de acesso, o comando REVOKE pode ser usado da seguinte forma:
Onde:
<privilégios>: SELECT, INSERT, DELETE, UPDATE, ALL PRIVILEGES
<usuário>: usuário cadastrado ou PUBLIC
Exemplo:
Remover a permissão de consulta dada aos demais usuários da tabela Projeto
	REVOKE SELECT ON PROJETO TO PUBLIC;
REVOKE <privilégios> ON <tabela/view>
FROM <usuário>;
Referências Usadas
12/04/2011
Banco de Dados
57