Structured Query Language

SQL
Structured Query Language
Construções básicas
Junção de Tabelas Join
O uso da operação JOIN numa cláusula FROM
especifica como se deseja que as tabelas sejam
vinculadas.
Use INNER JOIN para associar somente os
tuplas coincidentes de ambas as tabelas.
O OUTER JOIN liga as linhas de tabelas, mas não
necessariamente precisam casar entre si. Desta forma,
mesmo as linhas que não se encontrou referência no
cruzamento das tabelas aparecerá no Resultado de
Dados. Existem tipos de OUTER JOIN:
Construções básicas
LEFT JOIN usado para associar todas as
tuplas da primeira tabela com apenas as
coincidentes na segunda tabela. O SQL retorna
com valor nulo para as tuplas que não
coincidem na segunda tabela.
RIGHT JOIN usado para associar todas as
tuplas da segunda tabela com apenas as
coincidentes na primeira tabela
Construções básicas
SELECT Nome_Tabela.nome_campo
FROM Tabela1 LEFT OUTER JOIN Tabela2
ON Tabela1.chave_primaria =
Tabela2.chave_estrangeira
SELECT Nome_Tabela.nome_campo
FROM Tabela1 RIGHT OUTER JOIN Tabela2
ON Tabela1.chave_primaria =
Tabela2.chave_estrangeira
Construções básicas
SELECT nome_da_tabela1.nome_do_campo,
nome_da_tabela2.nome_do_campo
FROM nome_da_tabela1 inner join
nome_da_tabela2 on
nome_da_tabela1.nome_da_chaveprimaria1=
nome_da_tabela2.nome_da_chave_estrangeira
Conversão de dados
A função CONVERT permite converter
um tipo de dado para outro. A sua
forma geral de uso é:
CONVERT(tipo_de_dados, valor)
SELECT convert(char(10), pri_nome) +
' '+ convert(char(10), sobrenome)
FROM clientes
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.
O caractere % em um padrão representa qualquer
caracter. Por exemplo, para obter todos os autores
cujo (primeiro) nome começa com A, use:
SELECT prinome, sobrenome
FROM clientes
WHERE prinome LIKE 'A%'
Casamento de padrões
(cont.)
Para obter todos os nomes que contém
as letras 'en' no meio (ou no início ou
no fim), use:
SELECT prinome, sobrenome
FROM clientes
WHERE sobrenome LIKE '%en%'
Like '%en%'
Casamento de padrões
(cont.)
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%'.
Também é 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 '[AE]%' 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).
LIKE '%sou_a%'
Like ck
LIKE ‘[A-E]’%
LIKE '[^V]%'
Casamento de padrões
(cont.)
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 ‘guimaraes' vai
encontrar também ' Guimarães '.
LIKE ‘guimaraes'
Alias
Para simplificar a qualificação de colunas,
pode-se usar um apelido [alias] de tabela,
um nome colocado imediatamente após o
nome da tabela.
SELECT c.cadger_nome AS NomePessoa,
p.NumProntuario AS [Nº Prontuario]
FROM Cadastro_Pessoa c
INNER JOIN CadPessoaProntUnico p ON
c.cadger_codender = p.CodPessoa
WHERE (c.cadger_nome LIKE N'%GUIMARAES%')
ORDER BY c.cadger_nome
Sem Alias
SELECT Cadastro_Pessoa.cadger_nome,
CadPessoaProntUnico.NumProntuario
FROM Cadastro_Pessoa
INNER JOIN CadPessoaProntUnico ON
Cadastro_Pessoa.cadger_codender =
CadPessoaProntUnico.CodPessoa
WHERE (Cadastro_Pessoa.cadger_nome
LIKE N'%GUIMARAES%')
ORDER BY Cadastro_Pessoa.cadger_nome
Alias
(cont.)
Note que os aliases podem ser usados
na lista do SELECT ou nas condições de
junção (ou em outros lugares, como
numa cláusula WHERE).
Integridade Declarativa de
Dados
Integridade de dados é a maneira de
dizer que os dados que estão na sua
base são confiáveis.
Existem dois tipos de implementar
integridade de dados no SQL Server:
declarativa e procedural.
Declarativa diz respeito ao uso das
constraints e das rules. Procedural diz
respeito ao uso de Stored Procedures e
Triggers.
Constraint
Uma restrição [constraint] é uma
propriedade de uma coluna usada para
reforçar a integridade de dados.
Constraint
DEFAULT
Serve para indicar um valor padrão para um
campo, quando o mesmo não for
especificado. Exemplo: se a tabela TESTE
possui três campos, COD, NOME e UF, e
para o campo UF existe uma constraint
DEFAULT de valor SP criada, ao inserirmos
dados nesta tabela podemos somente
fornecer o conteúdo do campo COD e
NOME, pois o conteúdo do campo UF será
automaticamente preenchido com o valor
definido na constraint DEFAULT.
Constraint DEFAULT
Não podemos criar constraints em
campos com o tipo de dados
rowversion e campos que possuem
uma propriedade IDENTITY definida.
Constraints
(cont.)
CHECK
CHECK constraints reforça a integridade do
domínio através da limitação de valores aceitos
por essa coluna. São similares às chaves
estrangeiras (FOREIGN KEY), que controlam os
valores aceitos para uma coluna. A diferença está
na forma como esses valores são validados.
FOREIGN KEY constraints obtém a lista de valores
possíveis em outra tabela enquanto o CHECK
constraints determina os valores válidos por uma
expressão lógica que não está baseada em
nenhuma outra coluna.
Constraint CHECK
Por exemplo: é possível limitar a faixa salarial da
coluna salario através da criação de uma CHECK
constraint, estipulando intervalos de 500 a
10000. Isso evita que salários sejam entrados
fora dessa faixa salarial.
Podemos criar uma CHECK constraint com
qualquer expressão lógica (Boolean) que retorna
TRUE ou FALSE baseada em operadores lógicos
(=; >=;<=). O CHECK para o exemplo anterior
é a expressão lógica
salario >= 500 AND salario <= 10000.
Constraints
(cont.)
PRIMARY KEY
Esta constraint serve para definirmos um ou
mais campos como chaves primárias. Uma
chave primária é o atributo de um ou mais
campos que identifica unicamente um
registro em uma tabela. Podemos criar
somente uma constraint PRIMARY KEY por
tabela e não podemos colocar o valor NULL
nos campos que compõem a chave
primária.
Constraint PRIMARY KEY
Quando esta constraint é criada na
tabela, um índice também é
automaticamente criado sobre as
colunas que fazem parte da chave
primária.
Constraints
(cont.)
UNIQUE
Esta constraint faz a validação de valores
únicos em uma ou mais colunas de uma
tabela. Se um campo estiver definido com a
constraint UNIQUE nenhum valor repetido
poderá ser fornecido para esta campo.
Podemos colocar várias constraints UNIQUE
por tabela mas para cada campo que tem
uma constraint UNIQUE podemos somente
inserir o valor NULL uma vez.
Constraint UNIQUE
Quando esta constraint é criada
na tabela, um índice também é
automaticamente criado sobre a
coluna sobre a qual a constraint
está definida.
Constraints
(cont.)
FOREIGN KEY
Esta constraint é utilizada para
implementar o conceito de chave
estrangeira e serve para indicar que o
conteúdo de um campo deve se
referenciar a um outro campo que se
encontra em outra tabela que seja
chave primária ou uma UNIQUE.
Constraint FOREIGN KEY
Quando o campo que está sendo
referenciado residir na mesma tabela,
não precisamos utilizar a palavra-chave
FOREIGN KEY, podendo somente utilizar
REFERENCES. Esta constraint também
pode ser desabilitada, para o caso de
uma grande inserção de dados na tabela
Podemos programar eventos em cascata
utilizando as cláusulas ON DELETE e ON
UPDADE.
Ferramentas de
Administração
Ferramentas
O SQL Server vem com várias ferramentas de
administração:
Enterprise Manager: gerencia vários servidores,
permitindo executar qualquer tarefa relacionada ao
SQL Server, ele roda dentro MMC (Microsoft
Management Console).
Service Manager(SQLMANGR.EXE): permite iniciar,
pausar, continuar e parar ("finalizar") os serviços do
SQL Server.
Ferramentas
Query Analyzer(ISQLW.EXE): permite
administrar diretamente o SQL Server
usando comandos Transact-SQL. Os
comandos SQL podem ser executados
interativamente, ou podem ser executados
de procedimentos armazenados ou scripts.
Profiler (SQLTRACE.EXE): permite
monitorar toda a atividade do servidor e
registrar essa atividade em arquivos de
log, incluindo comandos SQL executados
pelo servidor.
Ferramentas
ClientNetwork Utility (CLICONFG.EXE): configura
o software de acesso cliente numa estação.
Performance Monitor (SQLCTRS.PMC: integra o
Performance Monitor ("Desempenho do Sistema")
do Windows NT com o SQL Server, para
monitorar o desempenho do sistema.
Ferramentas
Server Network Utility (SRVNETCN.EXE):
permite adicionar, remover ou configurar
as Net-libraries, que são os protocolos
aceitos para comunicação do cliente com o
servidor.
SQL Server Books Online: toda a
documentação do SQL Server, para
consultar online. Permite fazer pesquisas
de texto na documentação.
Diagramas
São os Diagramas Entidades Relacionamentos.
Refletem o Modelo Entidade Relacionamento,
só que agora já estruturado sobre o SGBD
escolhido.
É a ferramenta gráfica do SQL Server que
permite que sejam feitos os relacionamentos
entre tabelas do mesmo Banco de Dados.
Permitem que sejam quebrados por assunto
para facilitar o entendimento e visualização
DIAGRAMAS
Sistemas Gerenciadores
de Banco de Dados
Um sistema gerenciador de banco de
dados (SGBD) como o SQL Server é
responsável por armazenar dados de
forma confiável e permitir fácil
recuperação e atualização desses
dados. Um SGBD relacional armazena
dados de forma relacional, isto é na
forma de linhas e colunas.
Conceitos Relacionais
Um registro [record] ou linha [row] é um
grupo de variáveis com tipos de dados
diferentes, que armazenam dados
relacionados.
Um campo [field] ou coluna [column] é um
dos itens de informação dentro de uma linha
da tabela, como a descrição da informação.
Uma tabela [table] é um conjunto de linhas
(registros) com a mesma estrutura (coluna),
armazenados de forma permanente em
disco.
Um banco de dados [database] é um
conjunto de tabelas que contêm dados
relacionados.
Conceitos Relacionais
Um índice [index, plural 'indexes' ou
'indices'] é um mecanismo que permite
pesquisar rapidamente por linhas em uma
tabela, dado o valor de uma determinada
coluna (ou algumas colunas) da tabela.
Um índice primário ou chave primária
define um valor único, que não pode ser
repetido em outras linhas da tabela.
Uma consulta [query] é um pedido de
pesquisa no banco de dados, que permite
obter todo um subconjunto da tabela ou de
várias tabelas, especificando as condições
de seleção.
SQL Server
Os itens de menu importantes são Action, View
e Tools:
Action te permite fazer coisas tais como registrar
um novo servidor ou um novo grupo (conforme
visto na seção de instalação).
Views te fornece uma lista dos diferentes tipos de
visões disponíveis. Você pode selecionar as visões
grande, pequeno, detalhe ou lista dos ícones e suas
propriedades associadas.
O menu Tools lista todas as ferramentas e
assistentes do SQL Server. Você pode fazer backup
de um banco de dados; parar, iniciar e configurar a
replicação; e iniciar ferramentas como o Query
Analyzer (Analisador de consultas), entre outras.
Visões [Views]
Visões
Uma visão [view] é uma forma alternativa de
olhar os dados contidos em uma ou mais
tabelas. Para definir uma visão, usa-se um
comando SELECT que faz uma consulta sobre
as tabelas. A visão aparece depois como se
fosse uma tabela. Visões têm as seguintes
vantagens:
Uma visão pode restringir quais as colunas
da tabela que podem ser acessadas (para
leitura ou para modificação), o que é útil no
caso de controle de acesso.
Visões
Uma consulta SELECT que é usada
muito freqüentemente pode ser criada
como visão. Com isso, a cada vez que
ela é necessária, basta selecionar dados
da visão.
Visões podem conter valores calculados
ou valores de resumo, o que simplifica
a operação.
Uma visão pode ser usada para
exportar dados para outras aplicações.
Stored Procedures
Stored Procedures
Um procedimento armazenado é um conjunto
de comandos SQL que são compilados e
armazenados no servidor. Ele pode ser
chamado a partir de um comando SQL
qualquer.
Um procedimento armazenado é compilado
em tempo de execução como qualquer outro
comando Transact-SQL. O SQL Server
mantém planos de execução para todos os
comandos SQL na cache de procedimentos.
Stored Procedures
A vantagem de usar procedimentos
armazenados é que eles podem
encapsular rotinas de uso freqüente no
próprio servidor, e estarão disponíveis
para todas as aplicações.
Parte da lógica do sistema pode ser
armazenada no próprio banco de dados,
em vez de ser codificada várias vezes
em cada aplicação.
Stored Procedures
Os Procedimentos Armazenados não seriam úteis
se não pudessem aceitar argumentos.
Procedimentos Armazenados são implementados
como funções, aceitando um ou mais argumentos
e retornando um ou mais valores. Também
retornam um ou mais cursores. Um cursor é o
equivalente a um conjunto de registros. Os
argumentos devem ser declarados imediatamente
após a instrução CREATE PROCEDURE. Aparecem
como uma lista separada por vírgulas após o
nome do procedimento e antes da palavra AS.
Stored Procedures
Sintaxe:
CREATE PROCEDURE procedure_name
@argumento1 tipo1, @argumento2 tipo2, ....
[OUTPUT]
AS
SELECT.....
O argumento que será retornado para o
procedimento é marcado com a palavra-chave
OUTPUT significa que é a variável usada para saída
(retorno da execução do procedimento). Para
retornar cursor, não especifique saída.
Stored Procedures
A palavra INPUT é o padrão, por isso você não
precisa especificá-lo explicitamente.
Ex.
CREATE PROCEDURE CountOrdersbyDate
@startDate datetime, @enddate datetime,
@CountOrders int OUTPUT
AS
Select @CountOrders = COUNT(OrderID) from
Orders
WHERE OrderDate BETWEEN @startdate and
@enddate
Stored Procedures
Declare @date1 datetime
declare @date2 datetime
SET @date1 = '1/1/2006'
SET @date2 = ‘1/31/2006'
declare @ordercount int
EXECUTE CountOrdersbyDate @date1,
@date2, @orderCount OUTPUT
PRINT 'THERE WERE ' +
CONVERT(VARCHAR(5), @ORDERCOUNT) +
'ORDERS PLACED IN THE CHOSEN INTERVAL
TRIGGERS
Triggers
São tipos especiais de procedimentos armazenados
muito usados para tarefas administrativas.
Desempenham um importante papel na programação
do SQL porque é um Procedimento Armazenado que
o SQL chama automaticamente quando
determinadas ações ocorrem. Ex. um Procedimento
Armazenado que é executado automaticamente
quando uma linha da tabela é excluída.
Você pode pensar nos gatilhos como manipuladores de
eventos do VB para os eventos onUpdate, onDelete,
onInsert.
Triggers
Os gatilhos são comumente usados para acompanhar
as alterações ocorridas no banco de dados.
Sintaxe:
CREATE TRIGGER nome_gatilho
ON tabela
[WITH ENCRYPTION]
FOR [DELETE] [,] [INSERT] [,] [UPDATE]
[NOT FOR REPLICATION]
AS
Bloco de instruções T-SQL
Triggers
Cada gatilho possui um nome e é definido para uma
tabela específica e para uma ou mais ações
específicas. A palavra [WITH ENCRYPTION] é para
armazenar o gatilho de forma criptografada a fim de
que usuários não possam acessá-lo. A palavra [NOT
FOR REPLICATION] indica que o gatilho não deve ser
executado quando um processo de replicação
modifica a tabela envolvida no gatilho. O mesmo
gatilho pode ser aplicado para várias ações. Para
distinguir entre elas, sintaxe:
Triggers
Os Triggers são usados para realizar
tarefas relacionadas com validações,
restrições de acesso, rotinas de segurança
e consistência de dados; desta forma estes
controles deixam de ser executados pela
aplicação e passam a ser executados pelos
Triggers em determinadas situações:
Mecanismos de validação envolvendo múltiplas
tabelas
Criação de conteúdo de uma coluna derivada de
outras colunas da tabela
Realizar análise e e atualizações em outras
tabelas com base em alterações e/ou inclusões
da tabela atual
Triggers
Um Trigger é bloco de comandos TransactSQL que é automaticamente executado
quando um comando INSERT , DELETE ou
UPDATE for executado em uma tabela do
banco de dados.
A criação de um Trigger envolve duas
etapas :
Um comando SQL que vai disparar o
Trigger
( INSERT , DELETE , UPDATE)
A ação que o Trigger vai executar
( Geralmente um bloco de códigos SQL )
Triggers
TRIGGER UPDATE EM CASCATA
EX. Create Trigger CascadeImovel_TerritorialUpdate
On dbo.Lote
For Update
As
declare @LoteNumLoteNovo float
declare @LoteNumLoteOld float
Select @LoteNumLoteNovo=LoteNumLote from inserted
Select @LoteNumLoteOld=LoteNumLote from deleted
UPDATE Imovel_Territorial SET CadTerNumLote = @LoteNumLoteNovo
WHERE CadTerNumLote =@LoteNumLoteOld
TRIGGER DELETE EM CASCATA
Ex. Create Trigger CascadeManualItensDelete
On dbo.MatManual
For Delete
As declare @CodID int
Select @CodID=CodManual from deleted
DELETE FROM MatManualItens where CodManual=@CodId
Triggers
Como não poderia deixar de ser ,
existem certas limitações na utilização
de um Trigger:
Não é possível criar um Trigger para uma
visão
O resultado da execução de um Trigger é
retornado para a aplicação que o chamou.
O comando WRITETEXT não ativa um
Trigger
O comando TRUNCATE TABLE não pode
ser reconhecido por um Trigger
Projeto
Para verificação do aprendizado, fazer a
análise, o documento de requisitos, o
MER e a criação do banco de dados em
SQL.
O tema do Projeto é: Controle de
Animais (Zoonoses) Municipal.
Projeto
O Controle de Animais municipal se dá
pelo cadastro dos animais do município,
seu endereço, suas apreensões e suas
retiradas, quando essas ocorrem.