alter table - Bruno Moreno

Banco de Dados I
Aula 12 - Prof. Bruno Moreno
04/10/2011
Plano de Aula
• SQL
– Definição
– Histórico
– SQL e sublinguagens
– Definição de dados (DDL)
•
•
•
•
08:20
CREATE
Restrições básicas em SQL
ALTER
DROP
Definição de SQL
• Structured Query Language
• Linguagem padrão de SGBDRs
– Simplicidade
– Facilidade de uso
• Linguagem declarativa
– Indica o que consultar e não como consultar
08:20
Histórico
• 1970: projetada pela IBM Research
– SEQUEL (Structured English QUEry Language)
– System R
– Viabilizar o modelo relacional de E. F. Codd
• 1986: tornou-se padrão de SGBDRs
– ANSI/ISO
– SQL86 ou SQL1 (1º padrão)
08:22
Histórico
• 1992: nova versão
– SQL2 ou SQL92
• 1999: versão mais utilizada
– SQL3 ou SQL99
08:23
SQL em diferentes SGBDs
• Cada SGBD pode incluir pacotes adicionais ao
núcleo de SQL
• Alem disso, SGBDs permitem a definição de
funções e procedimentos em linguagens
específicas
– Oracle: PL/SQL
– PostgreSQL: C
08:24
SQL e sublinguagens
• SQL permite definição de dados, consultas,
atualizações e controle de acesso ao BD
– DDL
– DML
– DQL
– VDL
– DCL
– Permite embutir código SQL em linguagens de
programação genérica
08:25
SQL e sublinguagens
• DDL – Data Definition Language
– Criação, alteração e remoção de objetos do BD
• Esquemas, tabelas e atributos
O que é um esquema?
08:27
SQL e sublinguagens
• DDL – Data Definition Language
– Criação, alteração e remoção de objetos do BD
• Esquemas, tabelas e atributos
Conceito que agrupa tabelas e outros objetos que
pertencem a mesma aplicação de BD
Um esquema contém as tabelas de uma
aplicação de BD
08:29
SQL e sublinguagens
• DDL – Data Definition Language
– Criação, alteração e remoção de objetos do BD
• Esquemas, tabelas e atributos
Conceito que agrupa tabelas e outros objetos que
pertencem a mesma aplicação de BD
Um esquema contém as tabelas de uma
aplicação de BD
CREATE SCHEMA EMPRESA AUTHORIZATION BRUNO;
DCL – Data Control Language
08:36
SQL e sublinguagens
• DDL – Data Definition Language
– Criação, alteração e remoção de objetos do BD
• Esquemas, tabelas e atributos
ALTER SCHEMA EMPRESA RENAME TO EMPRESA_X
ALTER SCHEMA EMPRESA OWNER TO JOAO
08:36
SQL e sublinguagens
• DDL – Data Definition Language
– Criação, alteração e remoção de objetos do BD
• Esquemas, tabelas e atributos
DROP SCHEMA EMPRESA
08:36
SQL e sublinguagens
• DDL – Data Definition Language
– Criação, alteração e remoção de objetos do BD
• Esquemas, tabelas e atributos
CREATE TABLE EMPREGADOS (
NOME
VARCHAR(100) NOT NULL,
CPF
VARCHAR(11) PRIMARY KEY,
DATANASC
DATE,
ENDERECO
VARCHAR(30),
SEXO
CHAR,
SALARIO
DECIMAL(10,2),
SUPERCPF
VARCHAR(11) REFERENCES EMPREGADOS(CPF),
DNO
INT NOT NULL REFERENCES DEPARTAMENTO(DNUM)
);
08:41
SQL e sublinguagens
• DDL – Data Definition Language
– Criação, alteração e remoção de objetos do BD
• Esquemas, tabelas e atributos
ALTER TABLE EMPREGADOS RENAME TO EMPREGADO;
ALTER TABLE EMPREGADO ADD COLUMN FUNCAO TYPE VARCHAR(50);
ALTER TABLE EMPREGADO DROP COLUMN SALARIO;
ALTER TABLE EMPREGADO ALTER COLUMN ENDERECO TYPE VARCHAR(50);
08:41
SQL e sublinguagens
• DDL – Data Definition Language
– Criação, alteração e remoção de objetos do BD
• Esquemas, tabelas e atributos
DROP TABLE EMPREGADO
08:41
SQL e sublinguagens
• DML – Data Manipulation Language
– Comandos para Inserção, remoção e alteração de
linhas da tabela
– INSERT...
– DELETE...
– UPDATE...
08:42
SQL e sublinguagens
• DQL – Data Query Language
– Comandos para seleção
– SELECT FROM...
Existe sempre a dúvida se SELECT é
ou não DML
08:42
SQL e sublinguagens
• VDL – View Data Language
– Comandos para definição de visões
CREATE VIEW EMP_FINANCEIRO AS
SELECT * FROM EMPREGADO
WHERE DNUM = 1;
ID do departamento FINANCEIRO
08:44
SQL e sublinguagens
• DCL – Data Control Language
– Comandos controle de acesso aos dados
GRANT INSERT ON EMPREGADO TO BRUNO;
GRANT ALL PRIVILEGES
ON DEPARTAMENTO TO POSTGRES;
REVOKE DELETE ON EMPREGADO FROM BRUNO;
REVOKE ALL PRIVILEGES ON DEPARTAMENTO FROM
POSTGRES;
08:46
SQL e sublinguagens
Sublinguagem Principais Comandos
08:46
DDL
CREATE TABLE
ALTER TABLE
DROP TABLE
TRUNCATE
DML
INSERT
UPDATE
DELETE
DQL
SELECT
VDL
CREATE VIEW
DCL
GRANT
REVOKE
Banco de Dados I
COMANDOS DDL
08:47
Comandos DDL
• Principal comando: CREATE
– Esquemas: SCHEMA
– Tabelas: TABLE
– Domínios: DOMAIN
– Visões: VIEW
– Procedimentos
– Funções
– Índices
– Gatilhos
08:48
CREATE TABLE
• Cria uma tabela com atributos e restrições
iniciais
• Especificação de restrições
– Dentro do comando, após declaração de cada
atributos
– Dentro do comando, depois que os atributos
forem declarados
– Depois, usando o comando ALTER TABLE
08:48
CREATE TABLE
Dentro do comando, após declaração de
cada atributos
CREATE TABLE EMPREGADOS (
NOME
VARCHAR(100)
NOT NULL,
CPF
VARCHAR(11)
PRIMARY KEY,
DATANASC
DATE,
ENDERECO
VARCHAR(30),
SEXO
CHAR,
SALARIO
DECIMAL(10,2),
SUPERCPF
VARCHAR(11) REFERENCES EMPREGADO(CPF),
DNO
INT NOT NULL REFERENCES DEPARTAMENTO(DNUM)
);
08:49
CREATE TABLE
Dentro do comando, depois que os
atributos forem declarados
CREATE TABLE EMPREGADOS (
NOME
VARCHAR(100)
NOT NULL,
CPF
VARCHAR(11)
NOT NULL,
DATANASC
DATE,
ENDERECO
VARCHAR(30),
SEXO
CHAR,
SALARIO
DECIMAL(10,2),
SUPERCPF
VARCHAR(11),
DNO
INT NOT NULL,
PRIMARY KEY(CPF),
FOREIGN KEY(SUPERCPF) REFERENCES EMPREGADO(CPF),
FOREIGN KEY(DNO) REFERENCES DEPARTAMENTO(DNUM));
08:49
CREATE TABLE
Depois usando o comando ALTER TABLE
CREATE TABLE EMPREGADO (
NOME
VARCHAR(100)
NOT NULL,
CPF
VARCHAR(11)
NOT NULL,
DATANASC
DATE,
ENDERECO
VARCHAR(30),
SEXO
CHAR,
SALARIO
DECIMAL(10,2),
SUPERCPF
VARCHAR(11),
NOME DA RESTRIÇÃO
DNO
INT NOT NULL );
ALTER TABLE EMPREGADO
ADD CONSTRAINT EMPREGADO_PK PRIMARY KEY(CPF),
ADD CONSTRAINT GERENTE_FK
FOREIGN KEY(SUPERCPF) REFERENCES EMPREGADO(CPF),
ADD CONSTRAINT DEP_FK
08:52
FOREIGN KEY(DNO) REFERENCES DEPARTAMENTO(DNUM);
CREATE TABLE
• Tipos de dados de atributos
– Numéricos
• Inteiros de vários tamanhos
• Ponto flutuante de várias precisões
• DECIMAL(i, j) OU NUMERIC(i, j)
– i – precisão
» Número total de dígitos decimais
– j – escala
» Número de dígitos depois do ponto decimal
08:52
CREATE TABLE
• Tipos de dados de atributos
– Cadeia de caracteres
• Tamanho fixo: CHAR(n) ou CHARACTER(n)
• Tamanho variável
– VARCHAR(n)
– CHAR VARYING(n)
– CHARACTER VARYING(n)
• Valores são especificados utilizando aspas simples
• Operador de concatenação: ||
– ‘ABC’ || ‘xyz’ = ABCxyz
08:53
CREATE TABLE
• Tipos de dados de atributos
– Booleano
• TRUE e FALSE
08:54
CREATE TABLE
• Tipos de dados de atributos
– Data
• DATE
– Data
– YYYY-MM-DD
• TIME
– Tempo
– HH:MM:SS
• TIMESTAMP
– Data e Tempo
– YYYY-MM-DD HH:MM:SS
• INTERVAL
– 2 dias, 3 meses, ...
• Operadores > < ≥ ≤ são permitidos
08:55
CREATE DOMAIN
• É possível especificar um tipo de um dado
diretamente
• Exemplo: especificar um tipo somente para
representar CPFs
CREATE DOMAIN TIPO_CPF AS CHAR(11);
CREATE TABLE EMPREGADO (
NOME
VARCHAR(100),
CPF
TIPO_CPF,
[...]
);
08:55
Restrições Básicas em SQL
•
•
•
•
•
Restrições de Chave
Restrições Referencial
Restrições de Domínio
Restrições NULLs
Outras restrições...
08:57
Restrições Básicas em SQL
• Restrições de Atributo
– Se NULL não for permitido
• NOT NULL
• Restrição implícita para chave primária
– Valor default
• ... DEFAULT <VALOR>
• Valor é inserido na coluna de uma relação sempre que
houver uma inserção
• Valor quando DEFAULT não é especificado é NULL
08:58
Restrições Básicas em SQL
• Restrições de Atributo
– Limitar valores de um domínio
• Cláusula CHECK
• Exemplo: número de departamentos é restrito a
números inteiros entre 1 e 20.
CREATE TABLE DEPARTAMENTO(
NOME VARCHAR(50),
DNUMERO INT NOT NULL
CHECK (DNUMERO > 0 AND DNUMERO < 20),
PRIMARY KEY(DNUMERO)
);
08:58
Restrições Básicas em SQL
• Restrições de Domínio
– Cláusula CHECK também pode ser utilizada
CREATE DOMAIN D_NUM AS INTEGER
CHECK (DNUMERO > 0 AND DNUMERO < 20)
);
CREATE TABLE DEPARTAMENTO(
NOME VARCHAR(50),
DNUMERO DNUM NOT NULL,
PRIMARY KEY(DNUMERO)
);
08:59
Restrições Básicas em SQL
• Restrições de Chave
– PRIMARY KEY
• Especifica que um ou mais atributos definem a chave
primária da relação
• Se for uma chave simples pode ser definida logo após o
atributo
• Se composta, só no final do comando CREATE TABLE ou
usando ALTER TABLE
09:00
Restrições Básicas em SQL
• Restrições de Chave
– UNIQUE
• Especifica chaves alternativas
CREATE TABLE ALUNO(
NOME VARCHAR(50),
MATRICULA VARCHAR(8) PRIMARY KEY,
CPF VARCHAR(11) UNIQUE NOT NULL
);
09:01
Restrições Básicas em SQL
• Restrição de integridade referencial
– FOREIGN KEY (chave estrangeira)
Quando uma restrição de
integridade referencial pode ser
violada?
09:01
Restrições Básicas em SQL
• Restrição de integridade referencial
– FOREIGN KEY (chave estrangeira)
– Restrição é violada quando valores de atributos
referentes à FK ou a PK for modificado
• Ação padrão do SGBD: rejeitar (RESTRICT)
Já vimos que o projetista pode definir outras ações a
serem ativadas: AÇÕES REFERENCIAIS ENGATILHADAS
SET NULL, SET DEFAULT OU CASCADE
ON DELETE OU ON UPDATE
09:02
Restrições Básicas em SQL
• Restrição de integridade referencial
– SET NULL ON DELETE
• Na exclusão, marcar nulo
– SET DEFAULT ON DELETE
• Na exclusão, definir valor padrão
– CASCADE ON DELETE
• Na exclusão, propagar
– SET NULL ON UPDATE
• Na atualização, marcar nulo
– SET DEFAULT ON UPDATE
• Na atualização, definir valor padrão
– CASCADE ON UPDATE
• Na atualização, propagar
09:07
Restrições Básicas em SQL
• Restrição de integridade referencial
CREATE TABLE EMPREGADO(
...,
DNO INT NOT NULL DEFAULT 1,
CONSTRAINT EMP_PK PRIMARY KEY (CPF),
CONSTRAINT EMP_SUPER_CPF FOREIGN KEY (SUPERCPF)
REFERENCES EMPREGADO(CPF)
ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT EMP_DEPT_FK FOREIGN KEY (DNO)
REFERENCES DEPARTAMENTO (DNUMERO)
ON DELETE SET DEFAULT ON UPDATE CASCADE
);
09:08
Restrições Básicas em SQL
• Restrição de integridade referencial
CREATE TABLE DEPARTAMENTO(
...,
GER_CPF CHAR(9) NOT NULL DEFAULT ‘284677398’,
...,
CONSTRAINT DEPT_PK PRIMARY KEY (DNUMERO),
CONSTRAINT DEPT_SK UNIQUE (NOME),
CONSTRAINT DEPT_GER_FK FOREIGN KEY (GER_CPF)
REFERENCES EMPREGADO(CPF)
ON DELETE SET DEFAULT ON UPDATE CASCADE
);
09:13
Restrições Básicas em SQL
• Restrição de integridade referencial
CREATE TABLE DEP_LOCALIZACOES(
DNUM
INT
NOT NULL,
DLOCALIZACAO VARCHAR(15)
NOT NULL,
PRIMARY KEY (DNUM, DLOCALIZACAO),
FOREIGN KEY (DNUM)
REFERENCES DEPARTAMENTO(DNUM)
ON DELETE CASCADE ON UPDATE CASCADE
);
09:13
Restrições Básicas em SQL
• A palavra chave CONSTRAINT
– Pode ser seguida pelo nome da restrição
– O nome da restrição deve ser único para o
esquema
– O nome de uma restrição é utilizado para
eliminações e atualizações necessárias
09:15
Restrições Básicas em SQL
• Restrições em todas tuplas de uma relação
– CHECK
– Ao fim de uma relação
– Exemplo: nenhuma tupla da relação
DEPARTAMENTO pode ter GERDATAINICIO <
DEP_DATA_CRIACAO
CHECK(DEP_DATA_CRIACAO < GERDATAINICIO)
09:16
Banco de Dados I
ALTERAÇÃO DE ESQUEMAS SQL
09:17
Alteração de Esquemas SQL
• DROP
– Exclusão
– Só exclui objetos de BD que sejam nomeados
• Esquemas, tabelas, domínios ou restrições
– Há duas opções: CASCADE ou RESTRICT
– Exemplo: para excluir o esquema EMPRESA e todas as
suas tabelas, domínios e outros elementos, utilize
CASCADE
DROP SCHEMA EMPRESA CASCADE;
– Se for utilizado RESTRICT o esquema só é excluído se
não tiver nenhum elemento
09:18
Alteração de Esquemas SQL
• DROP
– Se uma tabela não é mais necessária, pode ser
eliminada
• DROP TABLE
DROP TABLE EMPREGADO CASCADE;
Neste caso, o que ocorre com as tuplas de
DEPENDENTE?
09:18
Alteração de Esquemas SQL
• DROP TABLE EMPREGADO CASCADE
– Tabelas que referenciam EMPREGADO também
são excluídas
• DROP TABLE EMPREGADO RESTRICT
– Exclusão propagada é impedida
09:28
Alteração de Esquemas SQL
• Comando ALTER
– ALTER TABLE permite
• Adicionar ou eliminar uma coluna da tabela
• Alterar o domínio de uma coluna
• Adicionar ou eliminar uma restrição
09:27
Alteração de Esquemas SQL
• Comando ALTER
– ALTER TABLE permite
• Adicionar ou eliminar uma coluna da tabela
• Alterar o domínio de uma coluna
• Adicionar ou eliminar uma restrição
ALTER TABLE EMPRESA.EMPREGADO
ADD FUNCAO VARCHAR(12);
Restrição NOT NULL não é permitida!
09:27
Alteração de Esquemas SQL
• Comando ALTER
– ALTER TABLE permite
• Adicionar ou eliminar uma coluna da tabela
• Alterar o domínio de uma coluna
• Adicionar ou eliminar uma restrição
ALTER TABLE EMPRESA.EMPREGADO
DROP ENDERECO CASCADE;
09:29
Alteração de Esquemas SQL
• Comando ALTER
– ALTER TABLE permite
• Adicionar ou eliminar uma coluna da tabela
• Alterar o domínio de uma coluna
• Adicionar ou eliminar uma restrição
ALTER TABLE EMPRESA.EMPREGADO
ALTER [COLUMN] ENDERECO TYPE VARCHAR(100);
09:31
Alteração de Esquemas SQL
• Comando ALTER
– ALTER TABLE permite
• Adicionar ou eliminar uma coluna da tabela
• Alterar o domínio de uma coluna
• Adicionar ou eliminar uma restrição
ALTER TABLE EMPRESA.EMPREGADO
ALTER GERCPF DROP DEFAULT
09:31
Alteração de Esquemas SQL
• Comando ALTER
– ALTER TABLE permite
• Adicionar ou eliminar uma coluna da tabela
• Alterar o domínio de uma coluna
• Adicionar ou eliminar uma restrição
ALTER TABLE EMPRESA.EMPREGADO
ALTER GERCPF SET DEFAULT ‘372198372’
09:32
Alteração de Esquemas SQL
• Comando ALTER
– ALTER TABLE permite
• Adicionar ou eliminar uma coluna da tabela
• Alterar o domínio de uma coluna
• Adicionar ou eliminar uma restrição
ALTER TABLE EMPRESA.EMPREGADO
DROP CONSTRAINT EMP_SUPER_CPF CASCADE;
09:32
Próxima aula PRÁTICA!
Aula 13 - 07/10/2011
Entrega do modelo conceitual
até hoje!
Freqüência!
Aula 12 - Prof. Bruno Moreno
04/10/2011