SQL Criação de estrutura de base de dados em SQL
SQL como Linguagem de Manipulação de Dados A SQL possui um conjunto de instruções que se podem classificar em dois grupos: •Interrogação da Base de Dados (SELECT) •Actualização da Base de Dados (INSERT, DELETE e UPDATE)
SQL como Linguagem de Manipulação de Dados A Inserção de dados nas tabelas faz-se através do comando INSERT, cuja forma mais simples é a seguinte:
INSERT INTO <tabela> [(<colunas>)] VALUES (<valores>) Exemplos: INSERT INTO Clientes VALUES (‘1234’,’J. Silva’,’Estudante’,’Braga’) INSERT INTO Clientes (cod_cliente, cliente, localidade) VALUES (‘1235’, ‘A. Costa’,’Guimarães’) Nota: O último formato utiliza-se quando um ou mais valores da linha a introduzir na tabela não existem ou não são conhecidos. No caso não era conhecida a profissão do cliente e o respectivo valor na tabela será nulo.
SQL como Linguagem de Manipulação de Dados Ex: Uma versão mais sofisticada do comando INSERT permite inserir, não uma, mas várias linhas numa tabela: INSERT INTO Contas_Prazo (num_conta,saldo) SELECT (num_conta, saldo) FROM Contas WHERE saldo < 15000
SQL como Linguagem de Manipulação de Dados A alteração de dados na base de dados faz-se através do comando UPDATE, cujo formato é o seguinte:
UPDATE <tabela> SET <coluna> = <expressão>, … =… [WHERE <condição>]
SQL como Linguagem de Manipulação de Dados Exemplos: UPDATE Contas SET saldo = saldo + 1000 WHERE num_conta = ‘1234567890’ Nota: A operação consiste numa actualização muito simples, em que apenas um valor da linha da tabela Contas é alterado
SQL como Linguagem de Manipulação de Dados Uma utilização mais sofisticada do comando UPDATE é o seguinte: UPDATE Contas SET saldo = (SELECT MAX (saldo) FROM Contas WHERE cod_cliente = ‘1234’) WHERE cod_cliente = ‘1234’
Nota: Nesta operação, o cliente nº 1234 foi “premiado” com a elevação do saldo de todas as suas contas ao valor do seu maior saldo.
SQL como Linguagem de Manipulação de Dados A remoção de linhas das tabelas da base de dados fazse através do comando DELETE, cuja forma é a seguinte:
DELETE FROM <tabela> [WHERE <condição>]
SQL como Linguagem de Manipulação de Dados Exemplos: O comando seguinte remove da tabela Contas uma única linha. DELETE FROM Contas WHERE num_conta = ‘1234567890’
SQL como Linguagem de Manipulação de Dados
O exemplo seguinte remove várias linhas da tabela Clientes (tantas quantos os clientes com contas na agência nº 123):
DELETE FROM Clientes WHERE cod_cliente IN (SELECT cod_cliente FROM Contas WHERE cod_agencia = ‘123’)
SQL como Linguagem de Manipulação de Dados
É importante reparar que uma mesma questão ou operação sobre a base de dados pode ser resolvida em SQL de múltiplas formas. Esta é uma das críticas vulgarmente apontadas à linguagem SQL.
SQL como Linguagem de Manipulação de Dados Definição de esquema em SQL • Create table r (A1D1, A2D2, ..., AnDn, <Regras de integridade1>, <Regras de integridadek>) • Regras de integridade (Constraints) englobam: • Interidade de chave • Primary key (A1, A2, ... , An) • Restrição de integridade • Check (P) • Integridade referencial • Foreign Keys
SQL como Linguagem de Manipulação de Dados • As definições das colunas têm o seguinte formato: • coluna tipo[NOT NULL [UNIQUE]][DEFAULT valor] Onde:
• coluna: nome do atributo que está sendo definido • tipo: domínio do atributo • NOT NULL: expressa que o atributo não pode receber valores nulos • UNIQUE: indica que o atributo tem valor único na tabela. Qualquer tentativa de se introduzir uma linha na tabela contendo um valor igual ao do atributo será rejeitada. Serve para indicar chaves secundárias • DEFAULT: indica um valor default para a coluna
SQL como Linguagem de Manipulação de Dados • Uma cláusula FOREIGN KEY inclui regras de remoção/atualização: • FOREIGN KEY (coluna) REFERENCES tabela [ON DELETE {RESTRICT|CASCADE|SET NULL| SET DEFAULT}] [ON UPDATE RESTRICT| CASCADE| SET NULL| SET DEFAULT}]
• Supondo que T2 tem uma chave estrangeira para T1, vejamos as cláusulas ON DELETE e ON UPDATE
SQL como Linguagem de Manipulação de Dados
ON DELETE: • RESTRICT: (default) significa que uma tentativa de se remover uma linha de T1 falhará se alguma linha em T2 combina com a chave • CASCADE: remoção de uma linha de T1 implica em remoção de todas as linhas de T2 que combina com a chave de T1 • SET NULL: remoção de T1 implica em colocar NULL em todos os atributos da chave estrangeira de cada linha de T2 que combina. • SET DEFAULT: remoção de linha em T1 implica em colocar valores DEFAULT nos atributos da chave estrangeira de cada linha de T2 que combina.
SQL como Linguagem de Manipulação de Dados ON UPDATE : • RESTRICT: (default) update de um atributo de T1 falha se existem linhas em T2 combinando • CASCADE: update de atributo em T1 implica que linhas que combinam em T2 também serão atualizadas • SET NULL: update de T1 implica que valores da chave estrangeira em T2 nas linhas que combinam são postos par NULL. • SET CASCADE: update de T1 implica que valores da chave estrangeira de T2 nas linhas que combinam terão valores default aplicados.
Nota: Usar a palavra CONSTRAINT antes das restrições de integridade para facilitar a manutenção das regras através de Drop (para eliminar) e Alter (para alterar)
SQL como Linguagem de Definição de Dados Da mesma forma que se criam tabelas, também se alteram as tabelas existentes através da adição de novas colunas ou restrições de integridade, da modificação das características de uma coluna, ou da eliminação de colunas ou restrições de integridade existentes. O comando utilizado é o ALTER TABLE. Ex: ALTER TABLE Clientes ADD COLUMN nacionalidade VARCHAR(15) DEFAULT ‘portuguesa’ ALTER TABLE Clientes DROP COLUMN nacionalidade
SQL como Linguagem de Manipulação de Dados ALTER TABLE: Permite que se altere os atributos de uma determinada tabela ou que se adicione novos atributos (evolução de esquemas). Os novos atributos terão valores nulos em todas as linhas. Pode-se também alterar as restrições da tabela. Nota: Ao incluirmos uma coluna devemos especificar o seu tipo de dado, não podendo esta coluna ser NOT NULL.
SQL como Linguagem de Manipulação de Dados ALTER TABLE: • Para adicionar uma nova coluna a uma tabela • ALTER TABLE tabela_base ADD [COLUMN] atributo tipo_dado
• Para modificar uma coluna de uma tabela • ALTER TABLE tabela_base ALTER [COLUMN] atributo SET valor-default ou DROP DEFAULT
SQL como Linguagem de Manipulação de Dados ALTER TABLE: • Para remover uma coluna de uma tabela: ALTER TABLE tabela_base DROP [COLUMN] atributo
• Para adicionar uma restrição a uma tabela ALTER TABLE tabela_base ADD restrição
• Para remover uma restrição de um tabela ALTER TABLE tabela_base DROP CONSTRAINT nome-constraint
SQL como Linguagem de Manipulação de Dados DROP TABLE: Remove uma tabela-base da BD. Remove os dados e definição da tabela. Sintaxe: DROP TABLE <nomeTabela>
Ex.: DROP TABLE Peca
SQL como Linguagem de Manipulação de Dados Especificação de índices em SQL SQL possui comandos para criar e remover índices em atributos de relações base (faz parte da SQL DDL) • Um índice é uma estrutura de acesso físico que é especificado em um ou mais atributos de um arquivo, permitindo um acesso mais eficiente aos dados. • Se os atributos usados nas condições de seleção e junção de uma query são indexados, o tempo de execução da query é melhorado. • Vários bancos de dados (Oracle, Postgresql) criam automaticamente índices em chaves primárias e colunas com UNIQUE
SQL como Linguagem de Manipulação de Dados Ex.: Criar um índice no atributo nome da relação Empregado. CREATE INDEX nome-índice ON Empregado(nome) • O default é ordem ascendente, se quisermos uma ordem descendente adicionamos a palavra chave DESC depois do nome do atributo • Para especificar a restrição de chave usamos a palavra UNIQUE CREATE UNIQUE INDEX matrIndex ON Empregado(matricula) • Para elimiarmos um índice usamos o comando DROP DROP INDEX nome-índice
SQL como Linguagem de Manipulação de Dados Os Comandos BEGIN, COMMIT e ROLLBACK BEGIN inicia um bloco de comandos SQL que fazem parte de uma transação. A transação é finalizada pelo comando COMMIT, que efectiva a transacção na base de dados e torna visível para os utilizadores os resultados da execução dos seus comandos. O comando ROLLBACK aborta a transacção que está em execução, impedindo que as alterações nos dados, nela realizadas, sejam gravados na base de dados.
SQL como Linguagem de Manipulação de Dados Exemplos: Sintaxe mais simples com BEGIN e COMMIT
BEGIN; insert into tstdel values (1,'teste', current_timestamp); insert into tstdel values (2,'teste2', current_timestamp); SELECT * FROM tstdel; COMMIT;
SQL como Linguagem de Manipulação de Dados Exemplos:
A transacção abaixo é abortada com o ROLLBACK. A inserção realizada não é gravada na base de dados. BEGIN; insert into tstdel values (1,'teste', current_timestamp); ROLLBACK;
SQL como Linguagem de Manipulação de Dados
GRANT e REVOKE: Os comandos GRANT e REVOKE permitem aos administradores do sistema criar utilizadores, conceder-lhes e retirar-lhes direitos em quatro níveis de privilégios: Nível Global Nível das Bases de Dados Nível das Tabelas Nível das Colunas
SQL como Linguagem de Manipulação de Dados
GRANT : Exemplos Conceder, para todos os utilizadores, o privilégio de inserir na tabela filmes: GRANT INSERT ON filmes TO PUBLIC; Conceder todos os privilégios ao utilizador Manuel na visão tipos: GRANT ALL PRIVILEGES ON tipos TO manuel;
SQL como Linguagem de Manipulação de Dados
REVOKE : Exemplos Revogar o privilégio de inserir na tabela filmes, concedido a todos os utilizadores: REVOKE INSERT ON filmes FROM PUBLIC; Revogar todos os privilégios concedidos ao utilizador Manuel sobre a visão vis_tipos: REVOKE ALL PRIVILEGES ON vis_tipos FROM manuel;
SQL como Linguagem de Manipulação de Dados TblAlunos
TblCursos
TblNotas
TblProfesso r
Codaluno Nome Endereco Telefone Nascimento Nomepai Nomemae Periodo Serie Numero Sexo Activo observacao
Codcurso Nomecurso Codprofessor creditos
Codaluno Codcurso Nota Ano Bimestre observacao
Codprofessor Nome Endereco Telefone Nascimento Sexo Observação Activo
Codaluno , Codcurso, CodProfessor são chaves primárias das respectivas tabelas
SQL como Linguagem de Manipulação de Dados
Como exemplo iremos definir as relações, entre as tabelas, da seguinte forma:
SQL como Linguagem de Manipulação de Dados A tabela tblprofessor pode ser definida da seguinte forma (Access SQL) :
CREATE TABLE tblprofessor (codprofessor INTEGER CONSTRAINT primarykey PRIMARY KEY, nome TEXT (50), endereco TEXT (50) telefone TEXT (15), nascimento DATE, sexo TEXT (1), activo BIT, observacao TEXT (100));
SQL como Linguagem de Manipulação de Dados
A instrução CREATE TABLE irá criar a tabela tblprofessor, com as definições da lista de campos entre parênteses, separados um dos outros por vírgulas. Cada descrição de campo possui duas partes: o nome do campo e o tipo de dados os quais são separados por um espaço entre si. A cláusula CONSTRAINT é utilizada para definir a chave primária codprofessor, ela define as características das colunas ou índices numa tabela. Pode ser usada para definir uma chave primária ou para estabelecer um relacionamento entre duas tabelas.
SQL como Linguagem de Manipulação de Dados A tabela TblCursos será criada pelas instruções: CREATE TABLE tblcursos (codcurso INTEGER CONSTRAINT primarykey PRIMARY KEY, nomecurso TEXT (15), codprofessor INTEGER CONSTRAINT tblprofessorFK REFERENCES tblprofessor);
A cláusula CONSTRAINT é utilizada para definir uma chave primária e uma chave externa. Note que existe uma relação de um para muitos entre a tabela TblProfessor e a tabela TblCursos, sendo que a coluna codprofessor da tabela TblCursos, é uma chave estrangeira (Foreign Key - FK )
SQL como Linguagem de Manipulação de Dados
Como criar as outras tabelas: A tabela TblNotas
CREATE TABLE tblNotas (codaluno INTEGER CONSTRAINT tblalunosFK REFERENCES tblalunos, Codcurso INTEGER CONSTRAINT tblcursosFK REFERENCES tblcursos, Nota INTEGER, Ano TEXT (4), Bimestre INTEGER);
SQL como Linguagem de Manipulação de Dados A tabela Tblalunos CREATE TABLE tblalunos (codaluno INTEGER CONSTRAINT primarykey PRIMARY KEY, nome TEXT (50), endereco TEXT (50) telefone TEXT (15), nascimento DATE, nomepai TEXT (50), nomemae TEXT (50), periodo TEXT (1), serie TEXT (10), numero TEXT (5), observacao TEXT (100), sexo TEXT (1), activo BIT); Nota:O código usado acima refere-se ao SQL-Access, havendo diferenças quando escrito para o ORACLE , SQL SERVER , SYBASE, etc.
SQL como Linguagem de Manipulação de Dados
Exercícios
SQL como Linguagem de Definição de Dados Considere-se o seguinte universo, utilizado já anteriormente, representado pelas seguintes relações:
Clientes (cod_cliente, cliente, profissao, localidade) Agencias (cod_agencia, agencia, localidade) Contas (num_conta, tipo_conta, cod_cliente, cod_agencia, saldo) Emprestimos (num_emprestimo, cod_cliente, cod_agencia, valor) Considere-se, ainda, que este universo possui as seguintes características particulares: Além de só existirem dois tipos de contas (ordem e prazo), o saldo de qualquer conta terá de ser sempre superior a 5000 Euros. Em relação aos empréstimos, este banco só aceita empréstimos superiores 50000 mas inferiores a 500000 Euros.
SQL como Linguagem de Definição de Dados A base de dados relacional correspondente pode ser definida em SQL da seguinte forma: Tabela Contas: CREATE TABLE Contas ( num_conta CHAR(10), tipo_conta CHAR(5), cod_agencia CHAR(3), cod_cliente CHAR(4) NOT NULL, saldo DECIMAL (10,2) NOT NULL, CONSTRAINT tipos_de_contas CHECK (tipo_conta IN (‘ordem’, ‘prazo’)), Nota: CHECK – Permite verificar se o conteúdo de uma coluna satisfaz determinada condição CONSTRAINT – Pode ser usada para definir uma chave primária ou para estabelecer um relacionamento entre duas tabelas.
SQL como Linguagem de Definição de Dados CONSTRAINT valor_saldo CHECK (saldo >= 5000), CONSTRAINT ch_prim_Contas PRIMARY KEY (num_conta), CONSTRAINT ch_estr_Agencias_Contas FOREIGN KEY (cod_agencia) REFERENCES Agencias (cod_agencia) ON UPDATE CASCADE ON DELETE SET NULL,
)
CONSTRAINT ch_estr_Clientes_Contas FOREIGN KEY (cod_cliente) REFERENCES Clientes (cod_cliente) ON UPDATE CASCADE ON DELETE CASCADE
SQL como Linguagem de Definição de Dados Tabela Empréstimos: CREATE TABLE Emprestimos ( num_emprestimo CHAR(5), cod_agencia CHAR(3), cod_cliente CHAR(4) NOT NULL, valor INTEGER NOT NULL, CONSTRAINT valor_emprestimo CHECK (valor BETWEEN 50000 AND 500000), CONSTRAINT ch_prim_Emprestimos PRIMARY KEY (num_emprestimo),
SQL como Linguagem de Definição de Dados CONSTRAINT ch_estr_Agencias_Emprestimos FOREIGN KEY (cod_agencia) REFERENCES Agencias (cod_agencia) ON UPDATE CASCADE ON DELETE SET NULL,
)
CONSTRAINT ch_estr_Clientes_Emprestimos FOREIGN KEY (cod_cliente) REFERENCES Clientes (cod_cliente) ON UPDATE CASCADE ON DELETE CASCADE
SQL como Linguagem de Definição de Dados Tabela Agências:
CREATE TABLE Agencias ( cod_agencia CHAR(3), agencia VARCHAR(20) NOT NULL, localidade VARCHAR(10) NOT NULL, CONSTRAINT ch_candidata_Agencias UNIQUE (agencia), CONSTRAINT ch_prim_Agencias PRIMARY KEY (cod_agencia), )
SQL como Linguagem de Definição de Dados Tabela Clientes:
CREATE TABLE Clientes ( cod_cliente CHAR(4), cliente VARCHAR(30) NOT NULL, profissao VARCHAR(10), localidade VARCHAR(10) NOT NULL, CONSTRAINT ch_prim_Clientes PRIMARY KEY (cod_cliente), )