Baixe Microsoft SQL Server 7.0 - Apostilas - Informática Part2 e outras Notas de estudo em PDF para Informática, somente na Docsity!
Uma coluna identificadora pode ser acrescentada ou removida usando a propriedade IDENTIY ou ROWGUIDCOL. A propriedade ROWGUIDCOL também pode ser adicionada ou removida de uma coluna existente, embora apenas uma coluna em uma tabela possa ter a propriedade ROWGUIDCOL de cada vez. O nome ou o dono de uma tabela também podem ser modificados. Quando você faz isso, também deve-se mudar o nome da tabela em quaisquer gatilhos, procedimentos armazenados, scripts SQL, ou outro código de programação que utilize o nome ou proprietário antigo da tabela. Nota: É importante considerar que a mudança de tipo de dados em uma coluna pode causar truncamento dos dados, ou mesmo ser impossível de ser feita (por exemplo, se você quiser converter um tipo char para um tipo inteiro e já houver valores não- numéricos armazenados nessa coluna).
Alterando a tabela com o Enterprise Manager
Essas modificações podem ser feitas no Enterprise Manager. Por exemplo, clique na tabela "Cliente" (dentro de "Exemplo\Tables", as tabelas do banco de dados Exemplo aparecerão do lado direito do Enterprise Manager) com o botão direito e clique em Design Table. No final da lista de colunas, acrescente uma nova coluna, com o nome "Idade", do tipo "tinyint" (idades não serão maiores que 255). Note que quando você adiciona uma nova coluna, a opção "Allow Nulls" deve ficar marcada. Altere o comprimento de Cidade para 30. Também clique na coluna "UF" e altere o
nome para "Estado". Após fazer isso, clique no botão "Save" para atualizar a tabela.
Alterando a tabela com comandos SQL
Também é possível alterar uma tabela com comandos SQL. Para isso, use o comando ALTER TABLE. Abaixo será mostrada uma sintaxe simples deste procedimento: Sintaxe: ALTER TABLE [banco_dados.[owner.]]nome_tabela
{ [ALTER COLUMN nome_coluna
{novo_tipo_de_dados [(precisão[, escala])] | ADD {nome_coluna dados_coluna | [WITH CHECK | WITH NOCHECK]} Onde: banco_dados é o nome do banco de dados a que a tabela pertence. Essa opção é opcional, ela será usada somente quando se estiver posicionado num determinado banco de dados se e deseja excluir a tabela de outro banco de dados. nome_tabela é o nome da tabela que deseja alterar. nome_coluna é a coluna que se quer alterar. novo_tipo_de_dados é o tipo de dados que a coluna aceitará a partir de agora. WITH CHECK | WITH NOCHECK Especificam se os dados na tabela devem ou não ser validados contra uma nova ou reabilitada restrição FOREIGN KEY ou CHECK. Se não especificada, assume-se WITH CHECK para novas restrições e WITH NOCHECK para restrições reabilitadas. Veja mais sobre restrições. Exemplo: ALTER TABLE Cliente ADD ender varchar(50) NULL GO ALTER TABLE Cliente
ALTER COLUMN CIDADE VARCHAR (25) Para renomear uma coluna, usa-se o procedimento sp_rename : sp_rename 'Cliente.ender', Endereco Nota: Perceba que ao executar o comando acima, você recebe um aviso que diz: "Cuidado: Mudar qualquer parte do nome de um objeto pode invalidar scripts e procedimentos armazenados.
Criando um
"Script" das tabelas
Algumas tarefas são mais fáceis de fazer com os comandos CREATE TABLE. Por exemplo, para recriar um banco de dados em outro servidor, você pode salvar um
banco de dados. Quando se entrar com o comando CREATE TABLE, a não ser que o criador indique explicitamente NOT NULL, a regra se aplicará também à criação da tabela.
ANSI Nulls
Quando em TRUE, as comparações de NULL com qualquer valor vão retornar um NULL. Quando em FALSE, apenas comparação de valores não-Unicode retornarão TRUE se e somente se ambos valores forem nulos. O padrão para essa opção é FALSE.
ANSI Warnings
Quando em TRUE, avisos de erro são exibidos, quando ocorrerem condições tais como divisão por zero ou valores nulos aparecerem em funções de agregação. Por padrão, é FALSE.
*autoclose
Quando em TRUE, o banco de dados é fechado automaticamente quando o último usuário encerra a conexão. Isto é muito útil para ambientes pequenos, mas deve ser evitado nos casos em que conexões são constantemente feitas e encerradas. A quantidade de carga adicional gerada pela abertura e fechamento de um banco de dados poe ter efeitos negativos em um ambiente de produção.
autoshrink
Quando em TRUE, o SQL Server periodicamente reduzirá os arquivos do banco de dados se necessário.
*dbo use only
Quando em TRUE, apenas o dbo (proprietário do banco de dados) tem acesso ao banco de dados. Use esta opção quando estiver executando reparos nem bancos de dados.
published
Utilizado para relicação, quando published estiver em TRUE, indica que a publicação está habilitada. Colocar essa opção em FALSE desabilita a publicação.
*read only
Se TRUE indica que o banco de dados é somente para leitura. FALSE permite acesso para leitura/escrita.
*recursive triggers
Quando TRUE, é permitido o disparo de gatilhos recursivos [recursive triggers]. Quando FALSE (o padrão), gatilhos não podem disparar recursivamente. Um gatilho recursivo é aquele que dispara na tabela que o originou, causando uma atualização em outra tabela, a qual causa uma atualização na tabela que originou o gatilho.
*selec into / bulk copy
Permite que o banco de dados aceite ações não registradas em log, tais como SELECT INTO e o utilitário BCP fazem.
*single user
Permite que apenas um usuário acesse o banco de dados.
subscribed
Quando em TRUE, o banco de dados pode ser assinado para publicação.
*torn page detection
Se TRUE, o SQL Server detectará leituras incompletas em disco, e fará com que sejam marcadas. Quedas de energia ou outros defeitos podem causar essas leituras incompletas.
Truncate log on Checkpoint (trunc. Log on chkpt.)*
Quando estiver em TRUE, o SQL Server trunca o log de transações toda vez que encontrar um checkpoint. Esta opção é usada frequentemente para desenvolvimento, fazendo com que o log de transações não fique cheio com tanta frequência. Você não deve utilizar esta opção em um sistema "real".
Definindo opções do banco de dados com sp_dboption
Para mudar as opções de um banco de dados com o procedimento armazenado sp_dboption, faça o seguinte: Sintaxe: sp_dboption ['banco_de_dados'] [,'opção'] [,'valor'] Por exemplo: sp_dboption 'pubs', 'read only', 'true' Para ver o estado atual das opções do banco de dados pubs , entre com o seguinte comando: sp_dboption 'pubs' Todas as opções que estiverem ativadas são listadas.
Definindo opções do banco de dados pelo Enterprise Manager
Quando se utiliza o Enterprise Manager para configurar as opções do banco de dados, você só tem acesso a um subconjunto (cerca de metade) das opções realmente disponíveis. Para mudar opções do banco de dados com o Enterprise Manager, faça assim:
- Expanda o grupo do servidor.
- Expanda o servidor.
- Expanda os bancos de dados.
- Clique com o botão direito no banco de dados que você quer mudar, e então clique em Propriedades [Porperties ].
- Selecione as opções a mudar.
- Clique em OK quando tiver acabado.
Publicando qualquer de suas tabelas (parte da replicação SQL). Você também não pode excluir os seguintes bancos de dados: Master Model Tempdb Embora lhe seja permitido excluir o banco de dados de sistema msdb, você não deve excluí-lo se usa ou pretende usar: Replicação SQL Server Agent Assistente de criação de páginas Web Histórico de backups Serviços de transformação de dados Quando excluir um banco de dados, considere os seguintes fatos: Com o método SQL DROP DATABASE, você pode excluir vários bancos de dados de uma vez. O Enterprise Manager só lhe permite excluir um banco de dados de cada vez. Depois que você excluir um banco de dados, qualquer ID de login que usava o banco de dados excluído como seu banco de dados padrão, usará agora o banco de dados master. Você deve SEMPRE fazer backup do banco de dados master , sempre que qualquer novo banco de dados for adicionado ou excluído.
Documentação dos passos de criação de bancos de dados no
SQL Server
Documentar os passos de criação de bancos de dados SQL Server pode ser útil por diversas razões, mas é claro que a principal e mais motivadora é o fato de ter um back up do trabalho que você fez. Isso não necessariamente vai lhe prevenir de perdas de dados, mas vai salvar seu modelo, e um modelo de banco de dados é uma coisa terrível a se perder. O SQL Server tem um gerador de script que torna fácil para você documentar, e se necessário reconstruir, seu banco de dados. O gerador de scripts pode construir o banco de dados e os objetos criados no banco de dados. Você tem a opção de selecionar desde todos até um único objeto. Você pode pegar um script de um banco de dados e rodá-lo em outro para criar cópias exatas de procedimentos armazenados, regras, gatilhos, etc. Você pode gerar scripts para os seguintes objetos: Tabelas [Tables] Procedimentos armazenados [Stored procedures] Gatilhos [Triggers] Índices [Indexes] Visões [Views]
Usuários e Grupos [Users and Groups] Tipos de dados definidos pelo usuário [User-defined data types] Logins Regras [Rules] Default Tabelas-chave / DRI O esquema pode ser salvo em um arquivo único ou você pode querer dividí-lo baseado em objetos. Independentemente do seu método, você não tgem mais uma boa desculpa para um banco de dados não documentado.
Gerando um script a partir do Enterprise Manager
- Expanda o grupo de servidores.
- Expanda o servidor.
- Expanda os bancos de dados.
- Clique com o botão direito no banco de dados escolhido, e selecione All Tasks.
- Selecione Gerar Scripts SQL [Generate SQL Scripts], como indicado abaixo.
A Sintaxe do SELECT
O comando SELECT recupera dados de uma ou mais tabelas. A sua sintaxe mais simples pode ser resumida da forma:
Sintaxe: SELECT lista_de_colunas FROM lista_de_tabelas WHERE condições Onde A lista_de_colunas especifica quais colunas serão retornadas como resultado, separadas por vírgulas ou um asterisco (*) que indica todas as colunas da tabela. A cláusula FROM, com uma lista_de_tabelas , especifica quais tabelas serão consultadas. A cláusula WHERE especifica condições que devem ser satisfeitas pelas linhas das tabelas. O comando Select pode ser utilizado para mostrar o conteúdo de variáveis, valores literais, etc... Como exemplo execute o seguinte comando no Query Analyzer: Select @@VERSION O resultado será:
@@Version é uma variável global do SQL Server que contém a versão do SQL Server utilizado. Para mostrar um valor literal digite o comando: Select 'Teste' O resultado será a palavra Teste.
Exemplo: Consultando Todas as Colunas
Para nossos exemplos, vamos usar o banco de dados pubs , um banco de exemplo que é instalado pelo SQL Server. Esse banco de dados armazena informações sobre livros, na tabela titles e sobre autores de livros, na tabela authors (entre outras coisas).
Execute o Query Analyzer e se conecte ao servidor como "sa". Na lista "DB", selecione "pubs". Digite e execute o seguinte comando: select * from authors
O resultado irá mostrar todas as colunas e todas as linhas da tabela 'authors' (ou seja,
todo o seu conteúdo), como abaixo (algumas linhas e colunas foram omitidas):
Note a mensagem "23 row(s) affected" [23 linhas afetadas]. Isso indica quantas linhas foram retornadas pelo SELECT. Você poderia escolher ver os resultados em uma grade, ao invés de em modo texto.
Para isso basta selecionar, clicando na seta à direita no ícone, a opção "Results in Grid" (Ctrl+D).
Caso você não queira selecionar na lista 'DB' o banco de dados a que a tabela que deseja procurar pertence, será necessário indicar no comando a qual banco de dados a tabela pertence, e o comando seria o seguinte: select * from pubs..authors Nessa tabela de exemplo, a coluna 'au_fname' é o primeiro nome do autor, 'au_lname' é o sobrenome [last name] e 'au_id' é o número de identificação. Agora suponhamos que você quer consultar apenas essas três primeiras colunas, omitindo a informação de telefone (phone) e endereço (address). O '*' no comando acima especifica que todas as colunas da tabela são retornadas, mas você pode listar só as que são desejadas. Clique na página Query, e altere o comando anterior para o seguinte: select au_id, au_fname, au_lname from authors
Usando Condições
Os comandos que já usamos não têm a cláusula WHERE. Nesse caso, todas as linhas da tabela são retornadas. Se o WHERE estiver presente, ele especifica uma condição que seleciona as linhas, e apenas as que satisfazem essa condição serão mostradas. Por exemplo, se quisermos os autores que moram na Califórnia, podemos consultar as linhas cuja coluna 'state' (estado) tem o valor 'CA': select au_fname, au_lname, city, state from authors where state='CA' O resultado será:
Note que o resultado mostra apenas 15 linhas (15 rows affected) e não 23, que é o total da tabela. As linhas que aparecem são apenas as que satisfazem a consulta. Existem vários tipos de condições de pesquisa, como veremos.
Manipulando expressões
Um comando SELECT pode retornar nas colunas de resultado uma coluna da tabela, ou um valor calculado. Por exemplo, a tabela titles contém os títulos de livro (title) e os preços de cada um (price). Se quisermos ver como fica o preço de cada um após um aumento de 10%, pode ser feito o seguinte: select price Preço , (price * 1.1) "Preço com 10% de aumento", title from titles
Note que "Preço com 10% de aumento" é o nome do cabeçalho da expressão (price * 1.1), como o nome colocado possui espaços, foi necessário coloca-lo entre aspas.
Ou seja, a segunda coluna , cujo nome é "Preço com 10% de aumento " mostra o resultado de price * 1.1 para cada linha. Você pode também usar vários operadores em
expressões com colunas numéricas: adição (+), subtração (-), multiplicação (*), divisão (/) e módulo (%). O módulo só pode ser usado com tipos inteiros e calcula o resto da divisão de dois números inteiros (Ex.: 13 % 4 = 1).
Funções matemáticas
Além de operadores, você pode usar funções matemáticas do SQL Server, por exemplo: ABS( valor ) retorna o valor absoluto (sem sinal) de um item. POWER( valor , p ) retorna o valor elevado à potência p. ROUND( valor,n ) arredonda o valor para n casas decimais. SQRT (valor) retorna a raiz quadrada do valor especificado. PI valor constante 3. Para outras funções, consulte o help do Transact-SQL em 'Math functions'. Por exemplo, para arredondar o valor do preço de cada livro para duas casas decimais, pode ser feito o seguinte: Select price Preço , ROUND(price, 1) "Preço com 1 casa decimal", title Título from titles O resultado será:
Funções de caracteres
Você pode usar funções para manipular dados do tipo caracter ( char ou varchar ), por exemplo, para pegar uma sub-string de uma seqüência de caracteres. E você pode usar o operador + para concatenar dois valores de tipo caracter. Por exemplo, digite o seguinte comando: select au_fname + ' ' + au_lname 'Nome completo', city + ', ' + state 'Cidade' from authors
Por exemplo digite o seguinte comando: Select substring(title, 1, 30) Título , str(price, 5, 1) Preço from titles O que fizemos foi mostrar até 30 caracteres da coluna title e mostramos a coluna price com no máximo 5 números antes da vírgula e 1 casa decimal depois da vírgula.
Então o resultado será:
Digite o seguinte comando : Select Replicate('a', 10) Com este comando a letra a foi mostrada 10 vezes.
Funções de Data/hora
O tipo datetime , como vimos, armazena datas e horas. Algumas funções trabalham com esse tipo de dados: DATEADD( parte,número ,data )
adiciona um certo número de dias (ou meses, anos etc.) à data DATEDIFF( parte,data1,d ata2 )
subtrai as duas datas ( data2 - data1 ), retornando um resultado em dias, meses etc. dependendo de datepart DATEPART( parte,data ) retorna a parte especificada da data DATENAME( parte,data ) retorna o nome por extenso da parte especificada GETDATE() retorna a data e hora atuais Nas funções acima, o argumento parte, especifica qual parte da data usar. Ele pode ser um dos seguintes valores: yy o ano qq o trimestre mm o mês dy o dia do ano (1-365) dd o dia do mês wk o número da semana (0-51) dw o dia da semana (domingo=1, segunda=2,...) hh a hora (0-23)
mi os minutos ss os segundos ms os milisegundos Por exemplo, digite o seguinte comando: Select pubdate, datepart(yy, pubdate) Ano from titles O resultado será:
O que fizemos foi mostrar o campo pubdate(Data de publicação do livro) e o de publicação do livro. Para mostrar o ano utilizamos a função de data datepart.
Conversão de dados
A função CONVERT permite converter de um tipo de dado para outro. A sua forma geral de uso é: CONVERT( tipo_de_dados , valor ) Por exemplo: select convert(char(10),au_fname) + ' '+ convert(char(10),au_lname) from authors
Ao executar este comando o resultado será:
Com valores datetime , convert pode ter um parâmetro a mais, que especifica o formato de data a ser usado. Os formatos mais usados são 3 (padrão brasileiro dd/mm/aa), 103 (dd/mm/aaaa) e os padrões americanos 1 (mm/dd/yy) e 101 (mm/dd/yyyy). O default é 0, que mostra datas como:
where pubdate between '1/1/91' and '12/31/91' Onde BETWEEN...AND... seleciona os valores de 'pubdate' que estão dentro de uma determinada faixa. Para fazer o contrário, bastaria usar NOT BETWEEN (mas nesse caso não é tão eficiente a consulta).
Usando listas
Você pode selecionar valores de acordo com uma lista. Se o valor pertence à lista, a linha será incluída no resultado. Por exemplo: select au_lname, city, state from authors where state in ('UT','CA') O resultado contém as linhas onde 'state' tem um dos valores 'UT' ou 'CA'. Equivale ao mesmo que usar uma condição composta: where state = 'UT' OR state = 'CA' Para retornar os valores que não estão na lista, pode-se usar NOT IN.
Casamento de padrões
O operador LIKE [como] faz casamento de padrões. Um padrão é uma string contendo caracteres que podem ser combinados com parte de outra string. Por exemplo, o caractere % em um padrão representa qualquer quantidade de caracteres. Por exemplo, para obter todos os autores cujo (primeiro) nome começa com A, use: select au_fname, au_lname from authors where au_fname like 'A%' Para obter todos os nomes que contém as letras 'en' no meio (ou no início ou no fim), use: select au_fname, au_lname from authors where au_fname like '%en%' Outro caractere para usar em padrões é o sublinhado (_). Ele combina com um único caractere. Por exemplo, se nos seus dados existem pessoas com nome 'Sousa' ou 'Souza', você pode usar: LIKE '%sou_a%'. Finalmente, é possível usar os colchetes para combinar com uma determinada faixa de caracteres. Por exemplo, LIKE '[CK]%' encontra os nomes que iniciam com C ou K e LIKE '[A-E]%' os que começam com as letras de A até E. Já LIKE '[^V]%' encontra os nomes que não começam com V (o caractere ^ indica não). Note que as comparações feitas com LIKE dependem da ordem de classificação [ sort order ] escolhida durante a instalação do SQL Server. Se foi usada a ordem "accent- insensitive", como foi recomendado, ele consegue procurar ignorando acentos. Por exemplo, LIKE 'camara' vai encontrar também 'Câmara'.
Procurando valores nulos
O valor NULL no SQL Server indica "não informado" ou "desconhecido". Ele é inserido numa coluna quando aquele valor não é conhecido ou não se aplica. Praticamente qualquer coluna pode ter valores NULL, exceto se tiver sido declarada como NOT NULL na criação da tabela.
NULL não é tratado como outros valores. Especialmente, qualquer operação com NULL tem como resultado NULL e qualquer comparação com NULL tem resultado FALSO. Por exemplo, veja as duas consultas a seguir: select price, title from titles where price = 19.
select price, title from titles where price <> 19. Algumas linhas da tabela 'title' têm 'price'=NULL. Essas linhas não vão aparecer nem na primeira nem na segunda consulta, porque NULL=19.99 é falso e NULL<>19. também é falso. Para ver essas linhas com valores nulos, use: select price, title from titles where price is NULL Para ver as linhas onde o valor está preenchido (não NULL), use: select price, title from titles where price is NOT NULL Usar 'price = NULL' funciona no SQL Server, mas não é compatível com o padrão ANSI.
Juntando várias condições
Você pode fazer condições compostas com AND, OR ou NOT. O operador AND (E) liga duas condições e retorna verdadeiro apenas se ambas são verdadeiras e falso se pelo menos uma delas é falsa. Já OR (OU) retorna verdadeiro se pelo menos uma delas for verdadeira e falso se ambas forem falsas. O operador NOT (NÃO) inverte uma condição. Por exemplo: select title, pub_id, price from titles where (title like 'T%' OR pub_id = '0877') AND (price > $16.00) Isso retorna os livros onde: Ambas as condições 1 e 2 são verdadeiras: 1- Uma das seguintes é verdadeira o título ('title') começa com T, OU o código da editora ('pub_id') é '0877' E
2- o preço ('price') é maior que 16. Os parênteses indicam a precedência das condições. Na falta de parênteses, o operador NOT, se presente, é aplicado primeiro, depois as condições com AND são agrupadas, depois as condições com OR são agrupadas. Você pode usar parênteses, mesmo se não necessários, para tornar a expressão mais legível.
