ADMINISTRAÇÃO DE BANCO DE DADOS

1

ADMINISTRAÇÃO DE BANCO DE

DADOS

ARTEFATO 02

2

Indice

ESQUEMAS NO BANCO DE DADOS ... 3

CRIANDO SCHEMA ... 3

CRIANDO TABELA EM DETERMINADO ESQUEMA ... 4

NOÇÕES BÁSICAS SOBRE CRIAÇÃO E MODIFICAÇÃO DE TABELA ... 4

PROPRIEDADES DA TABELA ... 4

PROPRIEDADES DE COLUNA ... 5

CRIAR TABELAS, ALTERAR E APAGAR COM CREATE TABLE, ALTER E DROP ... 5

MODIFICANDO TABELAS ... 5

CRIAR TABELA COM CREATE TABLE ... 6

ALTERANDO TABELAS COM ALTER TABLE ... 6

Adicionar um campo ... 7

Alterar um campo ... 7

Remover um campo ... 7

APAGAR TABELA COM DROP TABLE ... 8

TABELAS TEMPORÁRIAS... 9

OPÇÕES DE CRIAÇÃO ... 9

UTILIZANDO A TABELA UTILIZANDO A TABELA UTILIZANDO A TABELA UTILIZANDO A TABELA TEMPORÁRIATEMPORÁRIATEMPORÁRIATEMPORÁRIA ... 9

CRIANDO E MODIFICANDO RESTRIÇÕES PRIMARY KEY ... 9

RESTRIÇÃO DE EXCLUSÃO DE PK ... 10

USANDO VARIÁVEIS E PARÂMETROS ... 10

TRABALHANDO COM TABELAS E SQL ... 11

I - INSERINDO DADOS ... 11

II - ATUALIZANDO DADOS ... 11

III - EXCLUINDO REGISTROS ... 11

IV – RECUPERANDO REGISTROS ... 12

FUNÇÕES AGREGADAS (TRANSACT-SQL)... 12

FUNÇÕES DE AGREGAÇÃO DISPONÍVEIS NO SQL: ... 12

3

Esquemas no Banco de Dados

• Coleção de objetos dentro de um determinado database (banco de dados),

• Servem para agrupar objetos no nível de aplicação como também para simplesmente

fazer divisões departamentais.

• São bastante utilizados em padrões de sistema de banco de dados.

São muito importantes para a performance e segurança.

Criando Schema

CREATE SCHEMA [nome_esquema] AUTHORIZATION dbo

4

Criando tabela em determinado esquema

CREATE TABLE FUNCIONARIO.CONTABILIDADE ( COD INT, NOME VARCHAR (GO) ) GO

Noções básicas sobre criação e

modificação de tabela

Introdução

Depois de criar um banco de dados, podemos criar as tabelas que armazenarão os dados. Geralmente, os dados são armazenados em tabelas permanentes; porém, você também pode criar tabelas temporárias. As tabelas são armazenadas em arquivos de banco de dados até serem excluídas e estão disponíveis a qualquer usuário que tenha as permissões necessárias.

Propriedades da tabela

• Nome: Exibe o nome da tabela. Para editar o nome, digite-o na caixa de texto.

• Nome do banco de dados: Mostra o nome da fonte dos dados para a tabela selecionada.

• Descrição: Mostra uma descrição da tabela selecionada.

• Esquema: Mostra o nome do esquema ao qual essa tabela pertence.

• Server Name: Mostra o nome do servidor para a fonte de dados.

• Categoria Designer de Tabelas Expande para mostrar propriedades para Coluna de identidade, Indexávele

5

porque você não é o proprietário da tabela ou porque a tabela contém colunas com tipos de dados de texto, ntext ou imagem.

• É Replicável Mostra se a tabela é replicada em outro local.

• Categoria de especificação espaço de dados regular: Expande para mostrar propriedades para (Tipo de Espaço de Dados), Grupo de Arquivos ou Nome do Esquema de Partição, e Lista de Colunas de Partição.

• (Tipo de Espaço de Dados): Mostra se essa tabela é armazenada usando um grupo de arquivos ou esquema de partição.

• Nome do Esquema de Partição ou Grupo de Arquivos: Mostra o nome do grupo de arquivos ou esquema de partição.

• Lista de Colunas de Partição: Fornece acesso à caixa de diálogo Lista de Colunas de PartiçãO.

Propriedades de coluna

• Nome: Exibe o nome da coluna selecionada.

• Permitir Nulos: Indica se essa coluna permite valores nulos. • Tipo de dados: Exibe o tipo de dados para a coluna selecionada.

• Valor ou Associação Padrão: Exibe o padrão para a coluna sempre que nenhum valor for especificado para a mesma.

• Comprimento: Mostra o número de caracteres permitidos para tipos de dados com base em caractere. Esta propriedade só está disponível para tipos de dados com base em caractere

• Escala: Exibe o número de máximo de dígitos que podem aparecer à direita do ponto decimal para valores dessa coluna. Essa propriedade mostra 0 para tipos de dados não numéricos.

• Precisão: Exibe o número máximo de dígitos para valores nessa coluna. Essa propriedade mostra 0 para tipos de dados não numéricos.

Criar tabelas, alterar e apagar com create

table, alter e drop

Em linguagem SQL as instruções DDL (Data Definition Language) são usadas para definir a

estrutura dos dados, isto é, tudo que diz respeito à criação de tabelas e demais objetos de

banco de dados, alteração e exclusão destas estruturas.

Modificando tabelas

O que podemos fazer:

• As colunas podem ser adicionadas, modificadas ou excluídas. Por exemplo, o nome, o comprimento, o tipo de dados, a precisão, a escala e a nulidade da coluna podem ser alterados.

• As restrições PRIMARY KEY e FOREIGN KEY podem ser adicionadas ou excluídas. • As restrições UNIQUE e CHECK, as definições DEFAULT e os objetos podem ser

adicionados ou excluídos.

• Uma coluna de identificador pode ser adicionada ou excluída usando a propriedade IDENTITY.

6

• Uma tabela e colunas selecionadas dentro da tabela podem ser registradas para indexação de texto completo.

Criar tabela com create table

CREATE TABLE empregados (

codigo_empregado int identity primary key,

nome varchar(60),

data_nascimento datetime,

salario decimal(10,2),

cidade varchar(60),

estado varchar(30)

)

• CREATE TABLE - comando usado para criar uma tabela no banco de dados

• empregados - nome da tabela

• codigo_empregado,nome,data_nascimento,salario,cidade e estado - nome dos campos ou colunas da tabela empregados

• int, varchar, decimal e datetime - define o tipo de dados que cada campo irá receber

• identity - (apenas SQL Server) indica que o campo terá o seu valor preenchido automaticamente com um número sequencial e não reaproveitável.

• primary key - indica que o campo será uma chave do tipo primária

• varchar(xx) - como visto o varchar indica o tipo de dado que o campo vai receber e o xx é a quantidade máxima de caracteres.

• decimal(10,2) - decimal é o tipo, 10 indica que o campo poderá receber até 10 posições, sendo 8 inteiro e 2 para as casas decimais.

Existem diversos tipos de campos que podemos usar no SQL Server, veja a lista dos principais:

• Int - Este campo armazenará um valor numérico inteiro

• Decimal - Aceita valores numéricos com casas decimais, neste caso deve ser informado a

quantidade de casas decimais desejadas. Exemplo: decimal(10,2), indica que iremos armazenar oito posições numéricas e duas casas decimais.

• Varchar - Aceita valores do tipo texto. É necessário especificar o tamanho desejado.

• Text - Aceita valores do tipo texto

• Datetime - Aceita valores do tipo data e hora. O formato de gravação será mm/dd/aaaa hh:mm:ss

• Bit - Valor inteiro 0 ou 1

7

Uma vez criada, uma tabela poderá sofrer alterações em sua estrutura, para realizar essas alterações usamos o comando ALTER TABLE.

As alterações na estrutura da tabela podem ser para:

• Adicionar um campo

• Alterar um campo

• Remover um campo

Adicionar um campo

Você pode adicionar colunas a tabelas existentes desde que a coluna permita valores nulos ou que uma restrição DEFAULT seja criada na coluna. Quando você adiciona uma nova coluna à tabela, o Mecanismo de Banco de Dados insere um valor nessa coluna para cada linha de dados existente na tabela. Por isso, é útil adicionar uma definição DEFAULT à coluna ao adicioná-la à tabela. Se a nova coluna não tiver uma definição DEFAULT, você deve especificar que a nova coluna permite valores nulos. O Mecanismo de Banco de Dados inserirá valores nulos na coluna ou retornará um erro se a coluna não permitir valores nulos.

Vamos adicionar o campo sexo na tabela empregados criada no post anterior.

ALTER TABLE empregados ADD sexo varchar(10)

Observe que usamos a condição ADD que indica que estamos adicionando.

É possível ainda adicionar mais de um campo de uma mesma vez, para isto basta usar uma vírgula para separar a os itens da lista de campos a serem inseridas. Veja o próximo exemplo onde iremos adicionar os campos CPF e RG na tabela empregados.

ALTER TABLE empregados ADD cpf varchar(20),rg varchar(15)

Alterar um campo

Para alterar o campo sexo já existente, usamos a condição MODIFY COLUMN. Exemplo:

ALTER TABLE empregados ALTER COLUMN sexo varchar(30)

Podemos alterar o tipo e o tamanho, mas não o nome do campo. Veja ainda outro exemplo onde estaremos alterando o tipo de dados do campo cpf.

ALTER TABLE empregados ALTER COLUMN cpf int

Remover um campo

Reciprocamente, podemos excluir colunas de tabelas existentes, com exceção das colunas com as seguintes características:

• Usada em um índice.

• Usada em uma restrição CHECK, FOREIGN KEY, UNIQUE ou PRIMARY KEY. • Associada a uma definição DEFAULT ou um objeto padrão.

8

• Registrada para suporte de texto completo.

• Usada como uma chave de texto completo para uma tabela.

Para excluir um campo usamos a condição DROP COLUMN. Exemplo:

ALTER TABLE empregados DROP COLUMN sexo

ou ainda

ALTER TABLE empregados DROP COLUMN cpf,rg

Apagar tabela com drop table

Para excluir uma tabela do nosso banco de dados, usamos o comando DROP TABLE como no exemplo a seguir.

DROP TABLE empregados

Ao excluir uma tabela todos os dados serão excluídos, naturalmente.

9

Tabelas temporárias

Tabelas temporárias são similares a tabelas permanentes, exceto por serem armazenadas em tempdb e excluídas automaticamente quando não são mais usadas.

Há dois tipos de tabelas temporárias:

• Local: têm um único sinal numérico (#) como primeiro caractere no nome; elas são visíveis somente na conexão atual para o usuário e são excluídas quando o usuário se desconecta da instância do SQL Server.

• Global: têm dois sinais numéricos (##) como primeiros caracteres no nome; elas são visíveis a qualquer usuário após serem criadas e são excluídas quando todos os usuários que consultam a tabela se desconectam da instância do SQL Server

Opções de criação

1. Criar a tabela temporária durante o próprio comando SELECT/INSERT: SELECT

LOC_IN_CODIGO, LOC_DT_LOCACAO, LOC_RE_VALOR INTO #LOC_LOCACAOTEMP

FROM LOC_LOCACAO

2. A outra opção seria criar a tabela na "unha": CREATE TABLE #LOC_LOCACAOTEMP ( LOC_IN_CODIGO INT NOT NULL, LOC_DT_LOCACAO SMALLDATETIME, LOC_RE_VALOR NUMERIC(12,2) );

Utilizando a tabela temporária

Para inserir dados

INSERT INTO #LOC_LOCACAOTEMP VALUES (7,’2008-06-30′,3.50);

Para visualizar os registros da tabela temporária:

SELECT * FROM #LOC_LOCACAOTEMP

ATENÇÃO! Observe que essa tabela existe apenas para o usuário da atual conexão.

Criando e modificando restrições

PRIMARY KEY

• Podemos criar uma única restrição FOREIGN KEY como parte da definição de tabela quando uma tabela é criada.

• Se já houver uma tabela, podemos adicionar a restrição PRIMARY KEY desde que ainda não exista outra restrição PRIMARY KEY.

10

• Uma tabela pode ter apenas uma restrição PRIMARY KEY.

• Se já houver uma restrição PRIMARY KEY, podemos modificá-la ou excluí-la.

Observação

Para modificar uma restrição PRIMARY KEY, primeiro exclua a PRIMARY KEY já existente e, em seguida, recrie essa restrição com

Quando uma restrição PRIMARY KEY é adicionada a uma ou mais colunas existentes na tabela, o Mecanismo de Banco de Dados examina os metadados e os dados das colunas existentes para se certificar de que o seguinte seja estabelecido para chaves primárias:

• As colunas não podem permitir valores nulos.

• Colunas de restrições PRIMARY KEY especificadas na criação de uma tabela são convertidas implicitamente em NOT NULL.

• Não pode haver valores duplicados.

• Se uma restrição PRIMARY KEY for adicionada a uma coluna que tem valores duplicados ou permite valores nulos, o Mecanismo de Banco de Dados retornará um erro e não adicionará a restrição.

Não é possível adicionar uma restrição PRIMARY KEY que viole essas regras.

O Mecanismo de Banco de Dados cria automaticamente um índice exclusivo para impor o requisito de exclusividade da restrição PRIMARY KEY. Se ainda não houver um índice clusterizado na tabela ou se um índice não clusterizado não for especificado explicitamente, será criado um índice clusterizado exclusivo para impor a restrição PRIMARY KEY.

Restrição de exclusão de PK

Uma restrição PRIMARY KEY não poderá ser excluída nas seguintes condições:

• Se ela for referenciada por uma restrição FOREIGN KEY de outra tabela; a restrição FOREIGN KEY deverá ser excluída primeiro.

• A tabela tem um índice PRIMARY XML aplicado a ela.

Usando variáveis e parâmetros

O Transact-SQL tem vários modos para passar dados entre instruções Transact-SQL.

Eles incluem o seguinte:

• Variáveis locais Transact-SQL.

Uma variável Transact-SQL é um objeto em lotes e scripts Transact-SQL que pode conter um valor de dados. Depois que a variável for declarada, ou definida, uma

instrução em um lote pode definir a variável para um valor e uma instrução posterior no lote pode obter o valor da variável. Por exemplo:

USE AdventureWorks2008R2; GO

DECLARE @EmpIDVar int; SET @EmpIDVar = 1234; SELECT *

FROM HumanRresources.Employee

11

O número máximo de variáveis locais que podem ser declaradas em um lote é 10.000.

Trabalhando com tabelas e SQL

I - Inserindo dados

INSERT INTO table_name [(column_id1,column_id2...)]

VALUES (value1,value2,...)

Exemplos

INSERT INTO autos (1996,'ford')

Inserindo registros múltiplos

INSERT INTO table (c17,c4,c8,c3) SELECT a,b,c,d FROM ...

II - Atualizando dados

UPDATE tbl SET col = value|col|expr

UPDATE table_name SET column_id = expr WHERE condition

Exemplos

update users set password = 'newpass' WHERE user = 'quest'

update users set password = 'newpass' WHERE (UserID > 1) AND (UserID <

113)

III - Excluindo registros

DELETE FROM table_name

[WHERE search_condition] example:

Exemplos

DELETE FROM mytable WHERE userid < 50

12

IV – Recuperando registros

SELECT DISTINCT column_name FROM mytable

SELECT column_name, COUNT(column_name) as mycount FROM

table_name

GROUP BY column_name ORDER BY mycount [ASC | DESC]

SELECT tablea.A1, tableb.B1 FROM tablea, tableb

WHERE tablea.mykey = tableb.mykey

UPDATE table1 SET mycolumn = '2' WHERE userid IN

( SELECT userid FROM table2 WHERE table2.dissat <> 'somevalue')

Funções agregadas (Transact-SQL)

• Executam um cálculo em um conjunto de valores e retornam um único valor. • Com exceção de COUNT, as funções agregadas ignoram valores nulos. • Normalmente são usadas com a cláusula GROUP BY da instrução SELECT.

Funções de agregação disponíveis no SQL:

• AVG: calcula a média dos valores de um campo determinado

• COUNT: retorna a quantidade de registros existentes

• SUM: calcula a soma dos valores de um determinado campo

• MAX: retorna o maior valor existente para um campo especificado

13

Exemplo de utilização

SELECT SUM (Valor) FROM TABELA

--- 1189,00

SELECT SUM (Valor) FROM TABELA

WHERE Cliente = 'C1'

--- 292,00

SELECT Cliente, SUM (Valor) AS Total, COUNT(*) AS QtdeReg

FROM TABELA

GROUP BY Cliente

Cliente Total QtdeReg --- --- ---

C1 292,00 4

C2 325,00 2

C3 572,00 2

SELECT Cliente, SUM (Valor) AS Total, COUNT(*) AS QtdeReg

FROM TABELA

GROUP BY Cliente

HAVING (SUM (Valor) > 300) AND (COUNT(*) >= 1)

Cliente Total QtdeReg

--- --- ---

C2 325,00 2