1 of 47

Banco de Dados

Introdução a BD

Prof. Marcelo Iury de Sousa Oliveira

marcelo@ci.ufpb.br

http://sites.google.com/site/marceloiury

Banco de Dados

Aula 6 – SQL: Definição e Manipulação de Dados

(Baseado no Material da Profa. Bernadette Lóscio)

2 of 47

INTRODUÇÃO

O Modelo Relacional prevê, desde sua concepção, a existência de uma linguagem baseada em caracteres que suporte a definição do esquema físico (tabelas, restrições, etc.), e sua manipulação (inserção, consulta, atualização e remoção)

Structured Query Language

SQL

Introdução

3 of 47

HISTÓRICO

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

Surgiram diversos dialetos desenvolvidos por outros produtores
A ANSI e a ISO juntaram esforços de pesquisa, originando a versão SQL86, ou SQL1

O SQL foi revisto em 1992, 1999 e 2003.

Histórico

4 of 47

SQL Category

Data Query Language (DQL) - usada para consulta de dados
Data Manipulation Language (DML) – usada para criar, modificar ou remover dados
Data Definition Language (DDL) – usada para criar esquemas físicos
Data Control Language (DCL) – usada para controle de acesso e segurança dos dados

SQL é mais que uma linguagem de consulta!

UAAAUUUHH!!!!

5 of 47

How Can I Learn SQL on My Own?

Free Tutorials Online:

Give Me Your Info Online (But Free):

Como aprender SQL além destes slides?

6 of 47

Attention Please !

1. Palavras-chave do SQL e nomes de tabelas/colunas NÃO diferenciam maiúsculas de minúsculas: ‘select’ e ‘SELECT’ são os mesmos
2. Os valores armazenados em uma tabela podem diferenciar maiúsculas de minúsculas – dependendo da configuração
3. Normalmente, aspas simples ('') ou aspas duplas ("") não importam, mas podem ser configuradas de outra forma
4. O ponto e vírgula ';' é a maneira padrão de separar instruções SQL. Pode ser necessário em alguns SGBDs terminar cada instrução com um ';' mesmo após uma única instrução
5. Os comentários podem ser usados para tornar o SQL mais legível. Geralmente '--' para comentários de linha única e '/' e '/' para comentários de várias linhas. Adicione '--' no início para indicar uma linha de comentário
6. Padrão NÃO É PADRÃO – nenhum padrão SQL é totalmente implementado por todos os fornecedores. Preste atenção nas diferenças que a implementação de cada fornecedor tem do ‘padrão’ SQL

Attention Please!

7 of 47

SQL como DDL

Instruções para definição do esquema da base de dados:
CREATE TABLE

Cria uma nova tabela na base de dados, especificando nome, atributos e restrições

ALTER TABLE

Altera definições de uma tabela

DROP TABLE

Remove uma tabela, quando suas definições não são mais necessárias

SQL como DDL

8 of 47

CREATE DATABASE

Cria um novo banco de dados
Sintaxe pode mudar a depender do SGBD

CREATE DATABASE <nome_do_BD>

CREATE DATABASE EmpresaABC

Criação de Bancos de Dados

9 of 47

Nem todos os usuários são autorizados a criar esquemas ou elementos de esquema, como tabelas e colunas, por exemplo.
Os privilégios para esse tipo de ação devem ser concedidos às contas de usuário pelo administrador do sistema ou o pelo DBA (administrador do banco de dados).

CREATE SCHEMA <nome do esquema>;

CREATE SCHEMA <nome do esquema>

AUTHORIZATION <usuário dono do esquema>;

ou

Criação de Esquemas/Bancos de Dados

10 of 47

CRIAÇÃO DE TABELAS

CREATE TABLE

Colunas são especificadas primeiro, sob a forma:

<nomeCol> <domínio> <restrição>

Depois Chaves, integridade referencial e restrições de integridade

CREATE TABLE <nome_da_tabela>

( <nome_da_col1> <tipo_da_col1> NOT NULL,

<nome_da_col2> <tipo_da_col2> NOT NULL,

...

PRIMARY KEY <lista_de_nomes_de_col>,

FOREIGN KEY <nomes_de_col>

REFERENCES <nome_tab_ref>(<nome_da_col_ref>)

);

Criação de Tabelas

11 of 47

Exemplo:

CREATE TABLE Fornecedor

( Fcod INTEGER NOT NULL,

Fnome VARCHAR(20) NOT NULL,

Status INTEGER,

Cidade VARCHAR(20)

);

Fornecedor

Criação de Tabelas

12 of 47

CHAR(tamanho)

CHARACTER(tamanho)

INT

INTEGER

SMALLINT

NUMERIC(precisão,escala)

DECIMAL(precisão,escala) �DEC(precisão,escala)

FLOAT(precisão)

REAL

DOUBLE PRECISION

VARCHAR(tamanho)

CHAR VARYING(tamanho)

CHARACTER VARYING(tamanho)

NCHAR(tamanho)

NATIONAL CHAR(tamanho)

NATIONAL CHARACTER(tamanho)

VARYING(tamanho)

BIT(tamanho)

BIT VARYING(tamanho)

DATETIME

TIME(precisão)

TIMESTAMP(precisão)

INTERVAL

https://www.w3resource.com/sql/data-type.php

Tipos de Dados

13 of 47

13

PL/SQL Data Types

14 of 47

Definição de Restrições

As restrições podem ser criadas no momento da criação da tabela, logo após a criação dos atributos.
Tais restrições também podem ser criadas depois, através do comando ALTER TABLE.

Restrições

15 of 47

Restrições:

NOT NULL

Restrição aplicadas a colunas cujos valores não podem ser nulos

Valores Default

Adiciona-se a cláusula DEFAULT <valor> logo após a restrição:

CREATE TABLE Empregado

( ...

Sexo CHAR(1) NOT NULL DEFAULT “F”,

...

);

Restrição

Definição do

valor default

Restrição e Valor Default

16 of 47

Definição de Restrições

Descrição das restrições

Definindo valores permitidos com CHECK

O CHECK Permite verificar se os valores de um atributo estão conforme o esperado.
Exemplo 1: o salário não pode ser inferior ao valor do salário mínimo.

Exemplo 2: a data de criação do departamento não pode ser maior que data de início do gerente no departamento.

CONSTRAINT <nome da restrição> CHECK (salario >= 678)

CONSTRAINT <nome da restrição>

CHECK (dep_data_cricao <= data_inicio_gerente)

Integridade de Domínio

17 of 47

Especificação de chaves:

Primária:

PRIMARY KEY(<nomeColuna>)

PRIMARY KEY(<nomeColuna1>, <nomeColuna2> )

Estrangeira:

FOREIGN KEY(<nomeCol>) REFERENCES <NomeTabRefer>,

Especificação de Chaves

18 of 47

Definição de Restrições

Chaves: Chave Primária

Caso se queira, a PK pode ser definida como sendo auto incrementada automaticamente. Cada fabricante de SGBD tem a forma de declarar o auto incremento.

PostgreSQL: tipo serial
MySQL: auto_increment

CONSTRAINT <nome_restrição> PRIMARY KEY (<nome(s)_coluna(s)>)

Restrição criada explicitamente

Chave Primária

19 of 47

Definição de Restrições

Chaves: Chave Candidata

<descrição da coluna> UNIQUE

CONSTRAINT <nome_da_restrição> UNIQUE (<colunas>)

UNIQUE (<colunas>)

Restrição criada explicitamente

ou

Chave Candidata

20 of 47

Definição de Restrições

Chaves: Chave Estrangeira

CONSTRAINT <nome_da_restrição>

FOREIGN KEY (<colunas FK>)

REFERENCES <tabela onde tá a PK> <colunas PK>)

Restrição criada explicitamente

Chave Estrangeira

21 of 47

CRIAÇÃO DE TABELAS

Exemplo com chave primária:

CREATE TABLE Departamento

( Dcod INTEGER NOT NULL,

Dnome VARCHAR(20) NOT NULL,

Cidade VARCHAR(20),

PRIMARY KEY(Dcod)

);

Departamento

Chave Primária: Exemplo

22 of 47

CRIAÇÃO DE TABELAS

Exemplo com chave primária composta

CREATE TABLE Empregado

( Ecod INTEGER NOT NULL,

Enome VARCHAR(40) NOT NULL,

CPF VARCHAR(15) NOT NULL,

Salario DECIMAL(7,2),

Cod_Dept INTEGER NOT NULL,

PRIMARY KEY(Ecod,ENome)

);

Empregado

Chave Primária: Exemplo

23 of 47

CRIAÇÃO DE TABELAS

Exemplo com chave estrangeira

CREATE TABLE Empregado

( ...,

CONSTRAINT TrabalhaEm

FOREIGN KEY(Cod_Dept) REFERENCES Departamento(Dcod),

...

);

Empregado

Chave Estrangeria: Exemplo

24 of 47

CRIAÇÃO DE TABELAS

Exemplo com chave alternativa

CREATE TABLE Empregado

( ...,

UNIQUE KEY(CPF)

);

Empregado

Chave Alternativa

25 of 47

Integridade referencial

Cuidados

Quando colunas são excluídas ou alteradas
Quando o valor do atributo da chave estrangeira é modificado na tabela referenciada

Ações disparadas quando ocorrem violações:

SET NULL

SET DEFAULT

CASCADE

ON DELETE

ON UPDATE

Integridade Referencial

26 of 47

Integridade referencial

Exemplo, na tabela de Empregado

CREATE TABLE Empregado

( ...

FOREIGN KEY(Cod_Dept) REFERENCES Departamento(Dcod)

ON DELETE SET NULL

ON UPDATE CASCADE,

...

);

Ou seja...

Na remoção da linha que contém o valor da chave estrangeira, colocar nulo à coluna (se a coluna admitir NULL)
Na alteração do valor da chave estrangeira, alterar em cascata as chaves que referenciam este valor

Integridade Referencial

27 of 47

CRIAÇÃO DE TABELAS

CREATE TABLE Empregado

( Ecod INTEGER NOT NULL,

Enome VARCHAR(40) NOT NULL,

CPF VARCHAR(15) NOT NULL,

Salario DECIMAL(7,2),

Cod_Dept INTEGER NOT NULL,

PRIMARY KEY(Ecod,ENome);

CONSTRAINT TrabalhaEm

FOREIGN KEY(Cod_Dept)

REFERENCES Departamento(Dcod),

UNIQUE KEY(CPF)

);

Empregado

Exemplo Completo

28 of 47

REMOÇÃO DE TABELAS

DROP TABLE

Elimina completamente a tabela (vazia ou não)

DROP TABLE <nome_da_tabela>;

Ex.:

DROP TABLE Empregado;

Remoção de Tabelas

Cuidado!

Não há como recuperar

uma tabela removida

29 of 47

ALTERAÇÃO DE TABELAS

ALTER TABLE: Permite alterar ou adicionar atributos de uma determinada tabela. Os novos atributos terão valores nulos em todas as linhas. Utilizado na alteração ou exclusão das restrições da tabela

Obs.: em alguns BDs, [COLUMN] não existe!

Obs.: se a tabela já possui tuplas, é possível incluir uma nova coluna somente “null”!

Alteração de Tabelas

ALTER TABLE <nome_da_tabela>

ADD [COLUMN] <nome_coluna> <tipo_dados> [DEFAULT expr] [, <nome_coluna> <tipo_dados>...);

ALTER TABLE <nome_da_tabela>

MODIFY [COLUMN] (<nome_coluna> <tipo_dados> [DEFAULT expr] [, <nome_coluna> <tipo_dados>] ...);

ALTER TABLE <nome_da_tabela>

DROP [COLUMN] <nome_coluna>;

30 of 47

Modelo Físico (DDL)

Para remover uma coluna de uma tabela:

ALTER TABLE <nome_tabela>

DROP [COLUMN] <nome_coluna>

Para adicionar uma restrição a uma tabela:

ALTER TABLE <nome_tabela>

ADD CONSTRAINT <nome_restrição e seus parâmetros>

Para remover uma restrição de um tabela

ALTER TABLE <nome_tabela>

DROP CONSTRAINT <nome_restrição>

Obs.: em alguns Bancos de Dados não utiliza-se a palavra CONSTRAINT ao excluir.

Alteração de Tabelas

31 of 47

Modelo Físico (DDL)

Utilizaremos o seguinte exemplo para entender o ALTER TABLE

Alteração de Tabelas: Exemplos

32 of 47

Modelo Físico (DDL)

ALTER TABLE Livro ADD Idioma VARCHAR(15)

(Adiciona a coluna Idioma com 15 espaços na tabela Livro)

ALTER TABLE Livro MODIFY Idioma VARCHAR(30)

(Altera a coluna Idioma para 30 espaços)

ALTER TABLE Livro DROP Idioma

(Exclui a coluna Idioma)

Alteração de Tabelas: Exemplos

33 of 47

Modelo Físico (DDL)

ALTER TABLE Livro DROP PRIMARY KEY

(Exclui a chave primária da tabela Livro)

ALTER TABLE Livro ADD CONSTRAINT PRIMARY KEY(Titulo, Cod_Editora)

(Adiciona novamente a chave primária)

Alteração de Tabelas: Exemplos

34 of 47

Exemplo

Exemplo Completo

35 of 47

Exemplo

Tabela Funcionario

CREATE TABLE Empresa.Funcionario

(CPF CHAR(11) PRIMARY KEY ,

Pnome VARCHAR(20) NOT NULL,

Minicial VARCHAR(20),

Unome VARCHAR(20) NOT NULL,

Datanasc DATE NOT NULL,

Endereco VARCHAR(50),

Sexo CHAR(1) NOT NULL,

Salario NUMERIC(10,2),

CPF_supervisor CHAR(11),

Dnr INTEGER,

CONSTRAINT ck_sexo CHECK (sexo IN (‘F’, ‘M’))

);

Exemplo Completo

36 of 47

Exemplo

Tabela Departamento

CREATE TABLE Departamento

( Dnumero INTEGER PRIMARY KEY ,

Dnome VARCHAR(20) NOT NULL UNIQUE,

CPF_gerente CHAR(11) NOT NULL DEFAULT ‘00437887878’,

Data_inicio_gerente DATE,

CONSTRAINT departamento_fk1 FOREIGN KEY (CPF_gerente)

REFERENCES Funcionario (CPF)

ON UPDATE CASCADE

ON DELETE SET DEFAULT

);

Exemplo Completo

37 of 47

Exemplo

Tabela Localizacoes_Dep

CREATE TABLE Localizacoes_Dep

( Dnumero INTEGER,

Dlocal VARCHAR(30) NOT NULL,

PRIMARY KEY (Dnumero, Dlocal),

FOREIGN KEY (Dnumero) REFERENCES

Departamento (Dnumero)

ON UPDATE CASCADE

ON DELETE CASCADE

);

Exemplo Completo

38 of 47

Exemplo

Tabela Projeto

CREATE TABLE Projeto

( Projnumero INTEGER,

Projnome VARCHAR(30) NOT NULL,

Projlocal VARCHAR(40),

Dnum INTEGER NOT NULL,

CONSTRAINT Projeto_pk PRIMARY KEY (Projnumero),

CONSTRAINT Projeto_fk1 FOREIGN KEY (Dnum)

REFERENCES Departamento (Dnumero)

);

Exemplo Completo

39 of 47

Exemplo

Tabela Trabalha_Em

CREATE TABLE Trabalha_Em

( Fcpf CHAR(11),

Pnr INTEGER NOT NULL,

Horas NUMERIC(2,1),

CONSTRAINT Trabalha_Em_pk PRIMARY KEY (Fcpf,Pnr),

CONSTRAINT Trabalha_Em_fk1 FOREIGN KEY (Fcpf)

REFERENCES Funcionario (CPF),

CONSTRAINT Trabalha_Em_fk2 FOREIGN KEY (Pnr)

REFERENCES Projeto(Projnumero)

);

Exemplo Completo

40 of 47

Exemplo

Tabela Dependente

CREATE TABLE Dependente

( Fcpf CHAR(11),

nome_dependente VARCHAR (50) NOT NULL,

datanasc DATE NOT NULL,

sexo CHAR(1) NOT NULL,

parentesco VARCHAR(20) NOT NULL,

CONSTRAINT ck_sexo CHECK (sexo IN (‘F’, ‘M’)),

CONSTRAINT Dependente_pk PRIMARY KEY

(Fcpf,nome_dependente),

CONSTRAINT Dependente_fk1 FOREIGN KEY (Fcpf)

REFERENCES Funcionario (CPF)

ON DELETE CASCADE

ON UPDATE CASCADE

);

Exemplo Completo

41 of 47

SQL como DDL

Instruções para definição criar, alterar e remover dados no BD
INSERT

Insere uma ou mais tuplas em uma tabela

UPDATE

Altera os dados que já existem na tabela. Normalmente, é necessário especificar um condicional para especificar quais tuplas serão atualizadas.

DELETE

Remove uma ou mais tuplas em uma tabela. Normalmente, é necessário especificar um condicional para especificar quais tuplas serão removidas.

SQL como DML

42 of 47

Inserção de Dados

Adicionar uma tupla em uma tabela → INSERT

Se a inserção for realizada em todos os atributos e na ordem em que estão as colunas:

Forma variante, caso queria se especificar explicitamente os atributos:

INSERT INTO <nome_tabela> (<lista de colunas>) VALUES (<lista de valores>);

INSERT INTO <nome da tabela>

VALUES (<lista de valores>);

Insert

43 of 47

Inserção de Dados

Exemplo:

INSERT INTO Departamento (Dnumero, Dnome, CPF_gerente, Data_inicio_gerente)

VALUES (1, ‘Recursos Humanos’, ‘0986754356’, ‘20-04-2011’);

Insert

44 of 47

Atualização de Dados em tabelas

Com base nos critérios especificados, alterar valores de campos de uma tabela → UPDATE

UPDATE <nome tabela>

SET <nome coluna> = <valor>

WHERE <expressão de condição>;

Update

45 of 47

Atualização de Dados em tabelas

Exemplo: Atualizar salário do funcionário 15 para R$2000,00

UPDATE Funcionario SET Salario = 2000.00

WHERE CPF = 15;

Update

Se a condição não for

especificada, todas as

tuplas da tabela serão

atualizadas.

Cuidado!

46 of 47

Remoção de Dados

Com base nos critérios especificados, remover dados de uma tabela → DELETE

DELETE FROM <tabela>

WHERE <expressão de condição>;

Delete

47 of 47

Remoção de Dados

Exemplo: Remover todos os funcionários com salário superior a R$ 4000,00 e que trabalham no departamento 1.

DELETE FROM Funcionario

WHERE Salario > 4000.00 AND Dnr = 1 ;

Delete

Se a condição não for especificada,

todas as tuplas da tabela serão

removidas. Cuidado!