Ps-Graduao em Anlise e Projetos de Sistemas

Disciplina: Banco de DadosProfessor: Clayton Escouper das Chagas

6 aula

6 aula:

Tpicos de Manipulao e Administrao de Banco: SQL (Structured Query Language) - DDL e DML:

Introduo SQL como Linguagem de Definio de Dados (DDL) SQL como Linguagem de Manipulao de Dados (DML)

Introduo

lgebra: usurio preocupa-se com a ordem que as operaes devem ser executadas, preocupao com a performance e otimizao

SQL: usurio apenas especifica qual o resultado desejado atravs de uma consulta declarativa, deixando a otimizao com o SGBD

Histrico: - SEQUEL - Structured English Query Language

- Projetada e implementada no Centro de Pesquisa da IBM como interface de um banco de dados relacional experimental chamado System R

- ANSI (American National Standards Institute) e a ISO (International Standards Organization) juntaram esforos de pesquisa, originando a verso SQL86, ou SQL 1

- SQL 2, aprovada em 1992 (SQL92)

- SQL 3, aprovada em 1999

SQL mais do que uma linguagem de consulta: - Definio de dados: permite a definio da estrutura do banco de dados - Recuperao de dados: possibilita a recuperao dos dados do banco - Manipulao de dados: permite alteraes e excluses de dados j existentes, bem como a adio de novos dados - Controle de acesso: fornece meios para determinar o controle de acesso de usurios aos dados do banco - Compartilhamento de dados: coordena o acesso concorrente de usurios, assegurando que um no interfira no trabalho do outro - Integridade de dados: permite definir restries de integridade aos dados do banco

SQL como Linguagem de Definio de Dados (DDL - Data Definition Language)

Declaraes SQL:- Definio de dados- Consulta a dados- Update de dados

Pode-se embutir declaraes SQL em vrios tipos de linguagens como C, C++, Java, etc

SQL como linguagem de definio da dados - DDL

Definio de dados - CREATE

Update de dados - ALTER

Remoo de dados - DROP

Instrues para definio do esquema da base de dados:

CREATE TABLE: cria uma nova tabela na base de dados, especificando nome, atributos e restries

ALTER TABLE: altera definies de uma tabela

DROP TABLE: remove uma tabela, quando suas definies no so mais necessrias

CREATE TABLE - Colunas so especificadas primeiro, sob a forma: - Depois chaves, integridade referencial e restries de integridade

CREATE TABLE ( NOT NULL, NOT NULL,... PRIMARY KEY , FOREIGN KEY REFERENCES ());

Exemplo:

CREATE TABLE Fornecedor( FcodINTEGERNOT NULL, FnomeVARCHAR(20)NOT NULL, StatusINTEGER, CidadeVARCHAR(20));

Tabela Fornecedor:

Tipos para definio de colunas

Especificao de chaves: - Primria:PRIMARY KEY(),

- Estrangeira:FOREIGN KEY() REFERENCES ,

- Alternativa:UNIQUE KEY(CPF),(SQL2)

Exemplo com chave primria:

CREATE TABLE Departamento( DcodINTEGERNOT NULL, DnomeVARCHAR(20)NOT NULL, CidadeVARCHAR(20), PRIMARY KEY(Dcod));

Tabela Departamento

Exemplo com chave primria composta:

CREATE TABLE Empregado( EcodINTEGERNOT NULL, EnomeVARCHAR(40)NOT NULL, CPFVARCHAR(15)NOT NULL, SalarioDECIMAL(7,2), Cod_DeptINTEGERNOT NULL, PRIMARY KEY(Ecod, Enome));

Tabela Empregado

Exemplo com chave estrangeira (tabela Empregados):

CREATE TABLE Empregado( . . . CONSTRAINT TrabalhaEm FOREIGN KEY(Cod_Dept) REFERENCES Departamento(Dcod), . . . );

Tabela Empregado

OpcionalDefinido no SQL2

Assim, a tabela Empregado fica:

CREATE TABLE Empregado( EcodINTEGERNOT NULL, EnomeVARCHAR(40)NOT NULL, CPFVARCHAR(15)NOT NULL, SalarioDECIMAL(7,2), Cod_DeptINTEGERNOT NULL, PRIMARY KEY(Ecod, Enome), CONSTRAINT TrabalhaEm FOREIGN KEY(Cod_Dept) REFERENCES Departamento(Dcod),);

Restries - Constraint

O SQL2 trata as restries de forma uniforme: chave primria, chave estrangeira, chave alternativa).

CREATE TABLE Produto( PcodINTEGERNOT NULL, PnomeVARCHAR(40)NOT NULL, ModeloINTEGERNOT NULL, Cod_DeptINTEGERNOT NULL, CONSTRAINTChPrimariaProd PRIMARY KEY(Pcod), CONSTRAINTPertenceAo FOREIGN KEY(Cod_Dept) REFERENCES Departamento(Dcod), );

Nomes para as restries

Restries e Valores Default:

Restries:- NOT NULL: restries podem ser aplicadas a colunas cujos valores no podem ser nulos

Valores Default:- Adiciona-se a clusula DEFAULT logo aps a restrio:

CREATE TABLE Empregado( . . .EsexoCHAR(1)NOT NULL DEFAULT F . . . );

RestrioDefinio do valor Default

Integridade Referencial

Cuidados: - Quando colunas so excludas ou alteradas - Quando o valor do atributo da chave estrangeira modificado na tabela referenciada

Aes disparadas quando ocorrem violaes:

- SET NULL- CASCADE

ON DELETEON UPDATE

Exemplo na tabela Empregado

CREATE TABLE Empregado( . . . FOREIGN KEY(Cod_Dept) REFERENCES Departamento(Dcod) ON DELETE SET NULL ON UPDATE CASCADE, . . .);

Na remoo da linha que contm o valor da chave estrangeira, setar nulo coluna (se a coluna admitir NULL)

Na alterao do valor da chave estrangeira, alterar em cascata as chaves que referenciam este valor

Remoo de Tabelas:

DROP TABLE: elimina completamente a tabela, vazia ou no

DROP TABLE

Ex: DROP TABLE Empregado;

Ateno: No h como recuperar uma tabela removida

SQL2 inclui clusulas:

CASCADE: se existirem outros objetos que dependam da tabela excluda sero excludos tambm

RESTRICT: exclui a tabela somente se no existirem objetos do banco de dados que dependam da tabela

DROP TABLE Empregado CASCADE;

Alterao de Tabela:

ALTER TABLE

EXEMPLOS:

ALTER TABLE EmpregadoADD Telefone VARCHAR(30),ADD Sexo CHAR(1) SET DEFAULT F;

ALTER TABLE DepartamentoALTER Cidade VARCHAR(50);

ALTER TABLE EmpregadoDROP PRIMARY KEY;

ALTER TABLE EmpregadoDROP FOREIGN KEY TrabalhaEm;

Obs: A excluso de uma chave estrangeira s poder ser feita se ela recebeu um nome quando foi definida atravs de da diretiva CONSTRAINT

ALTER TABLE EmpregadoADD FOREIGN KEY(CodProf)CONSTRAINT TemProfissoesREFERENCES Profissoes;

ALTER TABLE EmpregadoALTER Sexo DROP DEFAULT;

ALTER TABLE EmpregadoALTER Sexo SET DEFAULT M;

SQL como Linguagem de Modelagem de Dados (DML - Data Modelage Language)

Linguagem de Consulta

Linguagem de Atualizao

Sintaxe bsica de uma instruo de consulta:

SELECT FROM WHERE

O resultado de uma consulta SQL uma relao ou uma tabela (resultset)

Esquema dos exemplos:

Fornecedor (Fcod, Fnome, Status, Cidcod, fone)Cidade (Ccod, Cnome, uf)Peca (Pcod, Pnome, Cor, Peso, Ccod)Projeto (Prcod, Prnome, Ccod)Fornecimento(Fcod, Pcod, Prcod, Quantidade)

Instrues bsicas da linguagem de consulta:

Recuperar cores utilizadas para peas

SELECTCorFROMPeca

Tabela no bancoResultado da consulta

Muita redundncia

Soluo para o problema: clusula DISTINCT

SELECT DISTINCT CorFROM Peca

A clusula DISTINCT executa a operao da lgebra relacional PROJEO, onde no h replicaes dos dados: Cor (Peca)

Uso da expresso WHERE:

Operadores Lgicos: =, , =

BETWEEN valor AND valor

LIKE %banco de%

LIKE 19_ _

- = um caracter

% = seqncia de caracteres

Exemplo:

Recuperar nomes de cidades fluminenses

SELECT CnomeFROM CidadeWHERE uf = RJ;

Tabela com seu contedoResultado

SQL - Junes:

Quando a consulta SQL envolver duas tabelas

No SQL padro no h operao de juno natural, o usurio especifica os atributos da juno

A clusula WHERE especifica os atributos usados para a juno das duas tabelas

SQL - Exemplos de Junes

Selecionar os nomes e status de fornecedores do Rio de Janeiro

SELECT Fnome, StatusFROM Fornecedor, CidadeWHERE Cnome=Porto Alegre AND Ccod=Cidcod;

Quando h mais de uma tabela envolvida, na clusula WHERE, deve aparecer uma condio de equivalncia entre as chaves destas tabelas, isto indica os atributos de juno das tabelas

Exemplo:

Tabelas do BancoResultado

SQL - Exemplos de Junes

Nomes de fornecedores de Camiseta

SELECT FnomeFROM Fornecedor, Peca, FornecimentoWHERE Peca.Pcod=Fornecimento.Pcod AND Fornecedor.Fcod=Fornecimento.Fcod AND Pnome=Camiseta;

Atributos com o mesmo nome devem ser qualificados

SQL - Uso de aliases

Funcionario (Fcod, Fnome, Fsupervisor)

Selecionar nomes dos funcionrios e nomes de seus clientes

SELECT F1.Fnome, F2.FnomeFROM Funcionario F1, Funcionario F2,WHERE F1.Fcod=F2.Fsupervisor

SQL - Uso do operador *

SELECT Fcod, Fnome, Status, CidadeFROM Fornecedor

o mesmo que

SELECT * FROM Fornecedor

Consultas aninhadas: Uso da clusula IN ( ou NOT IN)

Indica se um valor est no resultado de outra consulta:

SELECT PrnomeFROM PrijetoWHERE IcodIN (SELECT Icod FROM Instituicao WHERE I.nome=Empresa X);

Corresponde a:

SELECT PrnomeFROM Projeto P, Instituicao IWHERE P.Icod = I.Icod AND I.Inome=Empresa X;

Campos Vazios Pode-se testar atributos nulos

Uso de IS NULL (ou IS NOT NULL)

Recuperar os nomes de fornecedores cujo status foi informado

SELECT FnomeFROM FornecedorWHERE Status IS NOT NULL

Comparao de Strings

Uso do operador LIKE (ou NOT LIKE)Selecionar o Status dos fornecedores da famlia Silva

SELECT StatusFROM FornecedorWHERE Fnome LIKE %Silva%;% = nr indeterminado de caracteres_ = nr fixo de caracteres

Operadores Aritmticos

Adio+ Subtrao- Multiplicao* Diviso/

Aplicados diretamente a valores numricos ou a atributos com domnio numrico

Operadores Aritmticos - Exemplo:

Mostrar os pesos das peas acrescidas de 10%

SELECT Pnome, 1.1 * PesoFROM Peca

Funes Agregadas:

Mdia - AVG: Calcula a mdia dos valores no nulos de uma coluna. O domnio da coluna deve ser numrico

Mximo e Mnimo - MAX e MIN: Encontra o valor mais alto e mais baixo (respectivamente) de uma coluna. O domnio da coluna deve ser numrico

Total - SUM: Soma os valores no nulos de uma coluna. O domnio da coluna deve ser numrico

Funes Agregadas:

Contagem - COUNT: Conta o nmero de linhas no nulas de uma coluna. COUNT(*) conta o nmero de linhas no nulas de uma tabela.

Exemplo: Obter o nmero de peas no BDSELECT COUNT(*)FROM Peca

As funes podem ser aplicadas sobre toda a tabela ou sobre grupos de linhas com a diretiva GROUP BY

SQL - Modelo estendido de consultas:

SELECT FROM [WHERE ][GROUP BY ][HAVING ][ORDER BY ]

Agrupamento:

GROUP BY Divide o resultado de uma consulta em grupos de tuplas cujos valores das colunas especificadas so iguais

Obter o nmero de peas fornecidas para cada fornecedor

SELECT Fcod, COUNT(Pcod)FROM FornecimentoGROUP BY Fcod

Condio:

HAVING Especifica uma condio que cada grupo deve satisfazer

Obter o nmero de peas fornecidas para cada fornecedor, desde que a quantidade fornecida seja maior que 600

SELECT Fcod, COUNT(Pcod)FROM FornecimentoGROUP BY FcodHAVING Quantidade > 600;

Ordenao:

ORDER BY Ordena as linhas da tabela resultante, por defaullt, ordem crescente, ou uso de DESC

SELECT Pnome, Peso, CorFROM PecaWHERE Cor = vermelhaORDER BY Pnome DESC

SQL - Comandos de Atualizao:

Instrues de atualizao do contedo

Inserir linhas na tabela: INSERT

Alterar dados na tabela: UPDATE

Excluir linhas da tabela: DELETE

Inserir linhas na tabela:

Comando INSERTINSERT INTO [ ( ) ]VALUES ( )

Exemplo:INSERT INTO PecaVALUES (P9, meia, azul, 0.3, Queimados);

INSERT INTO Peca (Pcod, Pnome, Cor, Peso, CidadeVALUES (P10, cala, preta, 1.2, Petrpolis);

Alterar linhas da tabela:

Comando UPDATE

UPDATE SET WHERE

UPDATE PecaSET Peso = 0.6WHERE Pnome = Camiseta;

Excluir linhas das tabela:

Comando DELETE

DELETE FROM [ WHERE ]

DELETE FROM Peca

DELETE FROM PecaWHERE CIDADE = So Paulo;