Banco de Dados
Maria Augusta (Magu)
•1
Create Database
CREATE DATABASE PaisProducaoMineral
USE PaisProducaoMineral
•2
Criando tabelas (1)
Informações importantes:
1. Nome da tabela. (*)
2. Nome e tipo dos dados. (*)
3. Chave primária.
4. Chave(s) estrangeira(s).
5. Outras restrições sobre os campos.
(*) Informações essenciais.
•3
Tipos de Dados no SQL Server (1)
Tipos Numéricos
Para armazenar inteiros
Para armazenar números reais
TINYINT [0 a 256]
NUMERIC(18,0)
SMALLINT [-32.768 a 32.767]
DECIMAL(18,0)
INT [-2.147.483.648 a 2.147.483.647]
FLOAT [-1.79E + 308 a 1.79E + 308]
BIGINT [-9.223.372.036.854.775.808
a -9.223.372.036.854.775.807]
REAL [-3.40E + 38 a 3.40E + 38]
BIT: Armazena os valores lógicos 0 ou 1.
Valores monetários
SMALLMONEY:Valores numéricos decimais variando de -214 748.3648 a
•4
214 748.3647.
MONEY: Decimais variando de -922 337 203 685 477.5808 a
+922 337 203 685 477.5807
Tipos de Dados no SQL Server (2)
Tipos para datas
SMALLDATETIME: Armazena data e hora, com precisão de minutos.
DATETIME: Armazena data e hora, com precisão de centésimos de
segundos.
TIME: Armazena somente hora. Pode armazenar segundos até a fração
de 9999999.
DATE: Armazena somente data.
DATETIME2: É uma combinação dos tipos de dados DATE e TIME. A
diferença para o tipo DATETIME é a precisão ao armazenar as horas.
DATETIMEOFFSET: Armazena valores data e hora com a combinação
da hora do dia com o fuso horário. O intervalo de deslocamento do fuso
horário é de -14:00 a +14:00.
•5
Tipos de Dados no SQL Server (3)
Tipos para texto
CHAR(N): Armazena N caracteres fixos (até 8.000) no formato não Unicode. Independente da
quantidade de caracteres utilizados, irá sempre armazenar o tamanho de caracteres do campo,
sendo preenchido o restante com espaços em branco
VARCHAR(N): Armazena N caracteres (até 8.000) no formato não Unicode.
VARCHAR(MAX): Armazena caracteres no formato não Unicode. MAX indica que o máximo a
ser armazenado pode chegar a 2^31-1 bytes.
TEXT: Armazena caracteres no formato não Unicode. Esse tipo de dado suporte até
2.147.483.647 caracteres e existem funções específicas para trabalhar com esse tipo de dado.
NCHAR(N): Armazena N caracteres fixos (até 4.000) no formato Unicode. Independente da
quantidade de caracteres utilizados, irá sempre armazenar o tamanho de caracteres do campo, sendo
preenchido o restante com espaços em branco.
NVARCHAR(N): Armazena N caracteres (até 4.000) no formato Unicode.
NVARCHAR(MAX): Armazena caracteres no formato Unicode. MAX indica que o máximo a
ser armazenado pode chegar a 2^31-1 bytes.
NTEXT: Armazena caracteres no formato Unicode. Esse tipo de dado suporte até 1.073.741.823
•6
caracteres e existem funções específicas para trabalhar com esse tipo de dado.
Create Table (1) – Apenas os Campos
CREATE TABLE Pais1(
Nome varchar(50),
Pib money
)
•7
Create Table (2) –
Definição de Chave Primária – Sintaxe 1
CREATE TABLE Pais2(
Nome varchar(50) PRIMARY KEY,
Pib money
)
•8
Create Table (3) –
Definição de Chave Primária – Sintaxe 2
CREATE TABLE Pais3(
Nome varchar(50),
Pib money,
PRIMARY KEY(Nome)
)
•9
Create Table (4) –
Definição de Chave Primária – Sintaxe 3
CREATE TABLE Pais4(
Nome varchar(50),
Pib money,
CONSTRAINT PK_Pais4
PRIMARY KEY(Nome)
)
•10
Create Table (5) –
Definição de Chave Primária – Sintaxe 3
CREATE TABLE Mineral(
Nome varchar(30),
Valor money,
CONSTRAINT PK_Mineral
PRIMARY KEY(Nome)
)
•11
Create Table (6) –
Definição de Chave Estrangeira –
Sintaxe 1
CREATE TABLE PaisMineral1(
NomePais varchar(50)
REFERENCES Pais4(Nome),
NomeMineral varchar(30)
REFERENCES Mineral(Nome),
ReservaEstimada real,
ProducaoAnual real,
CONSTRAINT PK_PaisMineral1
PRIMARY KEY(NomePais, NomeMineral))
•12
Create Table (7) –
Definição de Chave Estrangeira –
Sintaxe 2
CREATE TABLE PaisMineral2(
NomePais varchar(50),
NomeMineral varchar(30),
ReservaEstimada real,
ProducaoAnual real,
CONSTRAINT PK_PaisMineral2
PRIMARY KEY(NomePais, NomeMineral),
FOREIGN KEY (NomePais)
REFERENCES Pais4(Nome),
FOREIGN KEY (NomeMineral)
REFERENCES Mineral(Nome)
•13)
Create Table (8) –
Definição de Chave Estrangeira –
Sintaxe 3
CREATE TABLE PaisMineral3(
NomePais varchar(50),
NomeMineral varchar(30),
ReservaEstimada real,
ProducaoAnual real,
CONSTRAINT PK_PaisMineral3
PRIMARY KEY(NomePais, NomeMineral),
CONSTRAINT FK1_PaisMineral3
FOREIGN KEY (NomePais) REFERENCES Pais4(Nome),
CONSTRAINT FK2_PaisMineral3
FOREIGN KEY (NomeMineral) REFERENCES
Mineral(Nome))
•14
Create Table (9) – Outras Restrições
 Outras restrições que podem ser impostas sobre um
campo (além de chave primária e chave estrangeira):
1. NOT NULL: O campo não pode assumir valores nulos.
2. UNIQUE: O campo não pode assumir valores
repetidos.
3. CHECK(condição): O valor do campo deve satisfazer a
uma condição expressa na cláusula CHECK.
4. DEFAULT valor: Se nenhum valor for especificado
para o campo quando for feita a inserção de um
registro no mesmo, então o campo assume o valor
“default”.
•15
Insert (1) – Comando da DML
 Comando utilizado para a inserção de registros nas
tabelas.
Sintaxe 1
INSERT INTO nome_tabela VALUES (valor1, ... , valorN)
 Ao se usar esta sintaxe, os valores especificados após a
cláusula VALUES têm que estar, necessariamente, na
mesma ordem em que foram criados.
Exemplo:
INSERT INTO Pais VALUES (‘Brasil’, 12000) - Certo
INSERT INTO Pais VALUES (12000, ‘Brasil’) - Errado
•16
Insert (2) – Comando da DML
Sintaxe 2
INSERT INTO nome_tabela (campo1, ..., campoN)
VALUES (valor1, ... , valorN)
 Ao se usar esta sintaxe, os valores especificados após a
cláusula VALUES têm que estar, necessariamente, na
mesma ordem em que o nome dos campos aparecem
após o nome da tabela.
Exemplo:
INSERT INTO Pais (Pib, Nome) VALUES (12000, ‘Brasil’)
INSERT INTO Pais (Nome) VALUES ( ‘Argentina’)
•17
Exercício– Criação de Tabelas
Criar as tabelas a seguir:
Genero (CodGenero, NomeGenero)
DVD (CodDVD, TituloFilme, ValorLocacao, CodGenero)
CE(Genero)
Cliente (CodCliente, NomeCliente, Endereco, Telefone)
Locacao (CodCliente, CodDVD, DataLocacao, NumeroDias)
CE(Cliente)
•18
CE(DVD)
Voltando às Restrições (1)
 Se executarmos o comando INSERT abaixo:
INSERT INTO Genero(CodGenero) VALUES (1)
 Verificando o conteúdo da tabela Genero, veremos que
consta um NULL para o campo NomeGenero associado
ao código de Genero 1.
 Para impedir a inserção de valores nulos, impomos a
restrição NOT NULL quando da declaração do campo.
Exemplo:
CREATE TABLE Genero1(
CodGenero int,
NomeGenero varchar(30) NOT NULL,
CONSTRAINT PK_Genero1 PRIMARY KEY(CodGenero)
)
•19
Voltando às Restrições (2)
 Se executarmos o comando INSERT abaixo:
INSERT INTO Genero1(CodGenero) VALUES (1)
veremos que a inserção não ocorre, aparecendo a seguinte
mensagem de erro:
Não é possível inserir o valor NULL na coluna 'NomeGenero', tabela
'Genero1'; a coluna não permite nulos. Falha em INSERT.
•20
Voltando às Restrições (3)
 Se executarmos os comandos INSERT abaixo:
INSERT INTO Genero VALUES (2, ‘Romance’)
INSERT INTO Genero VALUES (3, ‘Romance’)
 Verificando o conteúdo da tabela Genero, veremos que
o gênero Romance aparece duas vezes, associado ao
código 2 e ao código 3.
 Para impedir a inserção de valores duplicados de
NomeGenero, impomos a restrição UNIQUE quando da
declaração do campo. Exemplo:
CREATE TABLE Genero2(
CodGenero int,
NomeGenero varchar(30) UNIQUE,
CONSTRAINT PK_Genero2 PRIMARY KEY(CodGenero)
•21
)
Voltando às Restrições (3)
CREATE TABLE Genero2(
CodGenero int,
NomeGenero varchar(30),
CONSTRAINT PK_Genero2 PRIMARY KEY(CodGenero),
CONSTRAINT UQ_Genero2 UNIQUE(NomeGenero)
)
•22
Voltando às Restrições (4)
 Se executarmos os comandos INSERT abaixo:
INSERT INTO Genero2 VALUES (2, ‘Romance’)
INSERT INTO Genero2 VALUES (3, ‘Romance’)
verificamos que a primeira inserção ocorre, porém a
segunda não e o SGBD emite a seguinte mensagem de
erro:
Violação da restrição UNIQUE KEY
'UQ__Genero2__081698E50425A276'. Não é possível inserir a chave
duplicada no objeto 'dbo.Genero2'.
A instrução foi finalizada.
•23
Voltando às Restrições (5)
 Se executarmos o comando INSERT abaixo:
INSERT INTO DVD
VALUES (1, ‘A Volta dos que não Foram’, -1.5, 1)
estaremos inserindo na base um DVD com valor de locação
negativo.
•24
Voltando às Restrições (6)
 Para impedir a inserção de valores de locação negativos,
impomos uma restrição através da cláusula CHECK
quando da declaração do campo. Exemplo:
CREATE TABLE DVD1 (
CodDVD int,
TituloFilme varchar(50),
ValorLocacao money CHECK(ValorLocacao>0),
CodGenero int,
CONSTRAINT PK_DVD1 PRIMARY KEY(CodDVD),
CONSTRAINT FK1_DVD1
FOREIGN KEY(CodGenero)
REFERENCES Genero(CodGenero)
)
•25
Voltando às Restrições (6)
•
Uma outra sintaxe possível para se fazer a mesma declaração é:
CREATE TABLE DVD2 (
CodDVD int,
TituloFilme varchar(50),
ValorLocacao money ,
CodGenero int,
CONSTRAINT PK_DVD2 PRIMARY KEY(CodDVD),
CONSTRAINT FK_DVD2
FOREIGN KEY(CodGenero)
REFERENCES Genero(CodGenero),
CONSTRAINT CK_DVD2 CHECK(ValorLocacao > 0)
)
•26
Voltando às Restrições (7)
 Se executarmos o comando INSERT abaixo:
INSERT INTO DVD1
VALUES (1, ‘A Volta dos que não Foram’, -1.5, 1)
veremos que não é possível fazer a inserção e o SGBD
emite a seguinte mensagem de erro:
A instrução INSERT conflitou com a restrição do CHECK
"CK__DVD1__ValorLocac__0F975522". O conflito ocorreu no bando de
dados "PaisProducaoMinerais", tabela "dbo.DVD1", column
'ValorLocacao'.
A instrução foi finalizada.
•27
Voltando às Restrições (8)
 Se executarmos o comando INSERT abaixo:
INSERT INTO DVD1
(CodDvd, TituloFilme, ValorLocacao)
VALUES (1, ‘A Origem dos Guardiões’, 3.5)
estaremos inserindo na base um DVD sem informar qual é
o gênero de filme ao qual ele corresponde.
 Neste caso, ficará constando na coluna CodGenero o
valor NULL para este filme.
 Podemos estabelecer que todo filme que seja inserido
sem que se informe o código do gênero, seja
considerado como sendo do gênero Infantil (código 1).
•28
Voltando às Restrições (9)
 Fazemos isso por meio da cláusula DEFAULT.
Exemplo:
CREATE TABLE DVD2 (
CodDVD int,
TituloFilme varchar(50),
ValorLocacao money,
CodGenero int DEFAULT 1,
CONSTRAINT PK_DVD2 PRIMARY KEY(CodDVD),
CONSTRAINT FK1_DVD2
FOREIGN KEY(CodGenero)
REFERENCES Genero(CodGenero)
)
•29
Delete (1) – Comando da DML
 Comando utilizado para a exclusão de registros das
tabelas.
Sintaxe Completa
DELETE FROM nome_tabela WHERE condição
 Ao se executar um comando de deleção são excluídos
da tabela todos os registros que satisfazem à condição
especificada.
Obs: A cláusula WHERE pode ser omitida. Neste caso,
são excluídos todos os registros da tabela.
•30
Integridade Referencial e a
Criação de Tabelas (1)
 Quando se tentar excluir um registro de uma tabela e
este registro referencia um registro de outra tabela, o
comportamento padrão do SGBD é não permitir a
exclusão.
 A tabela que referencia é comumente chamada de
tabela filha e a tabela referenciada de tabela pai ou
tabela mãe.
•31
Integridade Referencial e a
Criação de Tabelas (2)
 Suponhamos, que os tabelas Genero e DVD tenham os
seguintes conteúdos:
CodGenero
NomeGenero
1
Romance
2
Ficção
3
Suspense
4
Terror
5
Infantil
6
Comédia
CodDVD
TituloFilme
ValorLocacao CodGenero
1
Procurando Nemo
3,50
5
2
O Exorcista
3,00
4
3
Titanic
2,50
1
4
Blade Runner
4,00
2
5
A Órfã
3,00
3
 Vamos considerar que todas as versões da tabela DVD,
que se chamarão DVD1, DVD2, DVD3, etc, terão os
mesmos registros representados acima.
•32
Integridade Referencial e a
Criação de Tabelas (3)
• Se tentarmos excluir um registro da tabela Genero, que
seja referenciado por algum registro da tabela DVD,
como por exemplo:
DELETE FROM Genero
WHERE CodGenero = 1
o SGBD não permite que se faça a exclusão, uma vez que
ele é responsável por manter a integridade referencial
na base de dados.
• Para excluírmos o gênero Romance (CodGenero = 1) da
tabela Genero, temos que, antes, excluir o registro com
o filme “Titanic”, que é deste gênero, da tabela DVD.
•33
Integridade Referencial e a
Criação de Tabelas (4)
• Há, entretanto, outros comportamentos que podem ser
definidos de tal forma que o SGBD permita a exclusão (ou
atualização) de registros referenciados, tomando ações
compensatórias, a fim de garantir a integridade
referencial. São eles:
1. ON UPDATE/ ON DELETE CASCADE: Faz atualização/exclusão
em cascata.
2. ON UPDATE/ ON DELETE SET NULL: Ao se atualizar ou
deletar um registro na tabela mãe, coloca um NULL nos
campos dos registros que o referenciam, na tabela filha.
3. ON UPDATE/ ON DELETE SET DEFAULT: Ao se atualizar ou
deletar um registro na tabela mãe, coloca o valor
declarado como DEFAULT nos campos dos registros que o
referenciam na tabela filha.
•34
Integridade Referencial e a
Criação de Tabelas (5)
• Assim, se a tabela DVD3 é criada com a cláusula ON
)
DELETE CASCADE, como abaixo:
CREATE TABLE DVD3 (
CodDVD int, TituloFilme varchar(50), ValorLocacao money,
CodGenero int DEFAULT 5,
CONSTRAINT PK_DVD3 PRIMARY KEY(CodDVD),
CONSTRAINT FK1_DVD3 FOREIGN KEY(CodGenero)
REFERENCES Genero(CodGenero)
ON UPDATE CASCADE ON DELETE CASCADE
ao emitirmos o comando:
DELETE FROM Genero WHERE CodGenero = 1
o registro relativo ao filme Titanic também será excluído da
tabela DVD3.
•35
Integridade Referencial e a
Criação de Tabelas (6)
• Assim, se a tabela DVD4 é criada com a cláusula ON
)
DELETE SET NULL, como abaixo:
CREATE TABLE DVD4 (
CodDVD int, TituloFilme varchar(50), ValorLocacao money,
CodGenero int DEFAULT 5,
CONSTRAINT PK_DVD4 PRIMARY KEY(CodDVD),
CONSTRAINT FK1_DVD4 FOREIGN KEY(CodGenero)
REFERENCES Genero(CodGenero)
ON UPDATE SET NULL ON DELETE SET NULL
ao emitirmos o comando:
DELETE FROM Genero WHERE CodGenero = 1
o registro relativo ao filme Titanic terá seu campo
CodGenero “setado” para NULL na tabela DVD4.
•36
Integridade Referencial e a
Criação de Tabelas (7)
• Assim, se a tabela DVD5 é criada com a cláusula ON
)
DELETE SET NULL, como abaixo:
CREATE TABLE DVD5 (
CodDVD int, TituloFilme varchar(50), ValorLocacao money,
CodGenero int DEFAULT 5,
CONSTRAINT PK_DVD5 PRIMARY KEY(CodDVD),
CONSTRAINT FK1_DVD5 FOREIGN KEY(CodGenero)
REFERENCES Genero(CodGenero)
ON UPDATE SET DEFAULT ON DELETE SET DEFAULT
ao emitirmos o comando:
DELETE FROM Genero WHERE CodGenero = 1
o registro relativo ao filme Titanic terá seu campo
CodGenero “setado” para 5 na tabela DVD5.
•37
Alterando a Estrutura de Tabelas (1)
• Quando uma tabela precisa sofrer alterações estruturais,
uma alternativa possível é excluí-la da base de dados e
criar outra com a estrutura desejada.
• Porém, esta não é a melhor solução quando a tabela em
questão já está populada com dados.
• Neste caso, é mais conveniente fazer uso do comando
ALTER TABLE, que faz parte do grupo de comandos da
DDL.
•38
Alterando a Estrutura de Tabelas (2)
• Com o comando ALTER TABLE, é possível fazer os
seguintes tipos de alterações estruturais:
1.
2.
3.
4.
5.
•39
Incluir um novo campo.
Excluir um campo.
Alterar o tipo de um campo.
Excluir uma restrição.
Incluir uma restrição.
Alterando a Estrutura de Tabelas (3)
• Para ilustrar as várias possibilidades de uso do comando
ALTER TABLE, suponhamos que tenhamos criado a tabela
Cliente, com o seguinte comando:
CREATE TABLE Cliente (
CodCliente int,
NomeCliente varchar(50),
Endereco varchar(100),
Telefone varchar(11)
CONSTRAINT PK_Cliente
PRIMARY KEY (CodCliente))
•40
Alterando a Estrutura de Tabelas (4)
• Para incluir o campo NumFilhos do tipo int, fazemos:
ALTER TABLE Cliente
ADD NumFilhos int
Exercício:
• Inclua agora, nesta mesma tabela, um campo Cpf do tipo
varchar(12).
•41
Alterando a Estrutura de Tabelas (5)
• Para excluir o campo NumFilhos, fazemos:
ALTER TABLE Cliente
DROP COLUM NumFilhos
Exercício:
• Exclua agora, desta mesma tabela, o campo Cpf que você
incluiu anteriormente.
•42
Alterando a Estrutura de Tabelas (6)
• Vamos incluir o campo NumFilhos, do tipo int novamente e,
em seguida, suponhamos que tenhamos a necessidade de
alterar seu tipo para tinyint:
ALTER TABLE Cliente
ALTER COLUM NumFilhos tinyint
Exercício:
• Torne a incluir nesta tabela o campo Cpf como varchar(120
e, sem seguida, altere o seu tipo para varchar(11).
•43
Alterando a Estrutura de Tabelas (6)
• Para excluir da tabela Cliente a restrição PRIMARY KEY
fazemos:
ALTER TABLE Cliente
DROP CONSTRAINT PK_Cliente
•44
Alterando a Estrutura de Tabelas (6)
• Para incluir na tabela Cliente a restrição PRIMARY KEY de
tal forma que agora a chave primária passe a ser o Cpf,
fazemos:
ALTER TABLE Cliente
ADD CONSTRAINT PK_Cliente
PRIMARY KEY (Cpf)
•45