1 / 55

Bancos de Dados I

Bancos de Dados I. SQL Server – Aula Prática Guilherme Amorim Juliana Xavier Walter Galvão. SQL Server. SGBD Relacional Versões mais recentes: 6.5, 7.0 e 2000 Transact-SQL SQL ANSI-92 + comandos adicionais Compatibilidade 6.5 e 7.0 não compatíveis em vários aspectos

baris
Download Presentation

Bancos de Dados I

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Bancos de Dados I SQL Server – Aula Prática Guilherme Amorim Juliana Xavier Walter Galvão

  2. SQL Server • SGBD Relacional • Versões mais recentes: 6.5, 7.0 e 2000 • Transact-SQL • SQL ANSI-92 + comandos adicionais • Compatibilidade • 6.5 e 7.0 não compatíveis em vários aspectos • 2000 e 7.0 compatíveis SQL Server - Aula Prática

  3. Segurança • Login e senha. • Independência de usuário proprietário • Objetos de nomes iguais podem coexistir no mesmo BD, contanto que sejam proprietários diferentes • O proprietário (usuário criador) gerencia completamente o objeto • Mudança de senha • exec sp_password ‘senha antiga’, ‘senha nova’ SQL Server - Aula Prática

  4. Bancos de Dados • BD’s de Sistema • Master, model, msdb, tempdb, distribution. • BD’s de Usuário • BD’s de exemplo • Northwind • Pubs • BD’s de aplicações • Bd1 SQL Server - Aula Prática

  5. Objetos do BD • Nomenclatura completa de 4 partes • Servidor.banco.proprietário.nome_objeto • Opcionais: servidor e banco • Servidor atual e bd atual • Semi-obrigatório: Proprietário • Obrigatório: Nome_objeto • No CIN-UFPE, sempre usar proprietário.objeto! SQL Server - Aula Prática

  6. Query Analyzer - linha de comando • Query Analyzer • Ferramenta primária para uso em BD1 • Por linha de comando – via isqlw.exe • Digite isqlw.exe /? para ver os parâmetros. • Parâmetros case sensitive - isqlw –U é diferente de isqlw –u. • Útil para enviar um script inteiro para o servidor. • Batch files com chamadas a isqlw.exe SQL Server - Aula Prática

  7. Outras Ferramentas Cliente • Utilitários gráficos • Enterprise Manager • Bastante completo, permite gerência completa de vários bd’s. • Não disponível para a disciplina de BD1 • Transact-SQL Help • Helpsql.hlp • SQL Profiler • “Escuta” do lado do servidor • DTS SQL Server - Aula Prática

  8. SQL Server no CIN-UFPE • Servidor • Máquina CIN03 - SQL Server 2000. • Utilitários cliente • Caminho: P:\MSSQL7\BINN • Menu Iniciar • Logins • bd1e1,bd1e2,...,bd1e25 • Senha: “bd1” • Banco de dados: Bd1 • Caminho para scripts de exemplo: • http://www.cin.ufpe.br/~if559 SQL Server - Aula Prática

  9. Exercício – rodando um script • Executar exemplo1.sql através do isqlw.exe P:\mssql7\binn\isqlw –SCIN03 –Ubd1e1 -Pbd1e1–dNorthwind –iexemplo1.sql –osaida.txt • Preparar batch file para executar exemplo1.sql. • Executar batch file. SQL Server - Aula Prática

  10. Criação de Tabelas • Pessoa.sql, Turma.sql • Ajustar valor de @usuario • Rodar script • Observar • Campos • Tipos de dados • Constraints • Not null, Primary Keys, Unique, Default • Operadores específicos • Identity SQL Server - Aula Prática

  11. Criação de Tabelas • Aluno.sql • Constraints • Foreign Key • Cuidados com FK’s • Remover as FK's que referenciam a tabela a ser removida antes de remover a tabela SQL Server - Aula Prática

  12. Manutenção de Scripts • Exercício • Gerar batch file com script para recriação das 3 tabelas: pessoa, turma e aluno. • Usar isqlw.exe. • Criar script para remover as tabelas ou FK’s na ordem correta. SQL Server - Aula Prática

  13. Bancos de Dados I Inserindo Registros Fábio Ávila <farp@cin.ufpe.br>Ávila Sistemas Ltda.Grupo Recife de TI

  14. Query Analyzer • Conexão • Várias janelas • Editor de Texto • Syntax-Highlighting • Executando comandos • F5, CTRL+E ou botão Play • Execução parcial • Resultados • Formato planilha/texto puro SQL Server - Aula Prática

  15. Verificando objetos do BD • Sp_help • Verifica estrutura de objetos do banco • Útil para testar existência de objetos e consultar sua estrutura. • Mostra FK’s com dependência para esta tabela • Exercício com isqlw • Executar sp_help para as tabelas aluno, pessoa e turma. SQL Server - Aula Prática

  16. Inserindo registros • INSERT tabela(lista_de_campos)VALUES(lista_de_valores) • A lista de campos é opcional, mas é sempre bom incluir. • Para os valores, quando for texto, colocar entre aspas. Quando for data, colocar no formato 'mm/dd/yyyy hh:nn'. Para números decimais, (xy.wz). SQL Server - Aula Prática

  17. Inserindo registros INSERT bd1en.pessoa (CPF,nome,endereco,telefone,email) VALUES (12345678901,'Zé Mané', 'Rua do Pombal, 20' ,0812224422 ,'zemane@nothing.com') SELECT @@IDENTITY SQL Server - Aula Prática

  18. Inserindo Registros • Pode-se inserir registros resultantes de um SELECT INSERT tabela (lista_de_campos) SELECT ... SQL Server - Aula Prática

  19. Exercício • Responda, para cada tabela criada: qual o número mínimo de campos para os quais devem ser informados valores em uma inserção de dados? • Inserir 1 registro em cada uma das tabelas: turma, pessoa e aluno, informando todos os campos exceto os IDENTITY. Usar Query Analyzer. • Inserir 1 registro em cada uma das tabelas informando apenas os campos obrigatórios. Usar Query Analyzer. • IDENTITY e PRIMARY KEY são sinônimos? SQL Server - Aula Prática

  20. Bancos de Dados I Consultas a uma Tabela Fábio Ávila <farp@cin.ufpe.br>Ávila Sistemas Ltda.Grupo Recife de TI

  21. Consultando dados • Mais prático usar isql/w ou Query Analyzer. • Comando SELECT do SQL • Transact-SQL traz melhorias ao SQL ANSI-92 também no SELECT • Pode-se (e deve-se) quebrar em várias linhas • Execução do texto selecionado • Comentários • In-line (--) • Delimitado (/* */) SQL Server - Aula Prática

  22. Cláusulas do SELECT • SELECT <lista_de_campos> • FROM tabela1 [,tabela2,tabela3...] • <tipo> JOIN <tabelax> ON <criterio_associacao> • Informa critérios de junção de tabelas • WHERE <condição_booleana> • Especifica critérios para filtragem de dados • GROUP BY <lista_de_campos> • Agrupamento de dados. • ORDER BY <lista_de_campos [ASC/DESC]> • Altera a ordem dos registros SQL Server - Aula Prática

  23. Especificando Campos SELECT 2 SELECT "CIN" SELECT 2+4 SELECT "CIN" + "-UFPE" SELECT user_name() SELECTconvert(varchar(10),getdate(),103) SQL Server - Aula Prática

  24. Especificando Campos • Apelidos de campo SELECT "CIN" + "-UFPE" as Centro ,Universidade = "UFPE" • Expressões como campos • Podem estar presentes na lista de campos, no ORDER BY ou no GROUP BY SQL Server - Aula Prática

  25. Cláusula WHERE • Operadores • OR, AND, NOT • >, <, >=, <=, =, <> • | (bitwise OR), & (bitwise AND), ^ (bitwise XOR) • LIKE • IN • EXISTS • BETWEEN • Uso de parênteses SQL Server - Aula Prática

  26. Cláusula WHERE • Cuidados com NULL e NOT NULL • Operador IS NULL • NULL = NULL é falso! NULL não é igual a nada. • Configurações SET para comportamento com NULL • Comparando com constantes • Inteiro – pode colocar entre parênteses • String – entre aspas simples ou duplas • Data – entre aspas, no formato "mm/dd/yyyy hh:nn:ss" • Ano pode conter 2 dígitos SQL Server - Aula Prática

  27. WHERE Simples Use Northwind SELECT employeeid, name = firstname + ' ' + lastname, title FROM dbo.employees WHERE employeeid = 5 SQL Server - Aula Prática

  28. Usando Like Use Northwind SELECT companyname FROM dbo.customers WHERE companyname LIKE '%Restaurant%' OR companyname LIKE '_en' OR companyname LIKE '[CK]%' OR companyname LIKE '[S-V]%' OR companyname LIKE 'M[^c]%' SQL Server - Aula Prática

  29. Faixa de valores Use Northwind SELECT productname, unitprice FROM dbo.products WHERE unitprice BETWEEN 10 AND 20 SQL Server - Aula Prática

  30. Lista de valores Use Northwind SELECT companyname, country FROM dbo.suppliers WHERE country IN ('Japan','Italy') • Pode ser outro SELECT retornando 1 campo SQL Server - Aula Prática

  31. Valores desconhecidos • Usar IS NULL depois do campo • NOT IS NULL = IS NOT NULL Use Northwind SELECT companyname, fax FROM dbo.suppliers WHERE fax IS NULL SQL Server - Aula Prática

  32. Ordenando Dados Use Northwind SELECT productid, productname, categoryid, unitprice FROM dbo.products ORDER BY categoryid ASC, unitprice DESC SQL Server - Aula Prática

  33. Eliminando valores duplicados Use Northwind SELECT DISTINCT country FROM dbo.suppliers ORDER BY country SQL Server - Aula Prática

  34. Exemplo de SELECT de 1 Tabela Use Northwind SELECT convert(varchar(10),productid) + " – " + productname as 'Nome Produto', UnitsInStock as 'Em estoque', ReorderLevel as 'Nível Crítico', Diferenca = (ReorderLevel - UnitsInStock) FROM dbo.products WHERE UnitsInStock < Reorderlevel ORDER BY (ReorderLevel - UnitsInStock) DESC SQL Server - Aula Prática

  35. Exercícios • Dica: use sp_help para ver a estrutura de cada tabela • Retorne os nomes dos produtos que começam com as letras a, d, de m a z, ordenados por nome. • Retorne os nomes dos fornecedores que estão sem homepage informada na tabela (nulos). • Mostre a lista de países dos clientes da empresa (tabela Customers), sem repetições. • Mostre os nomes dos clientes que não fizeram pedidos. Dica: use IN. Os pedidos estão na tabela Orders. • Soluções estarão disponíveis em http://www.cin.ufpe.br/~farp/bd1 SQL Server - Aula Prática

  36. Bancos de Dados I Consultas de duas ou mais tabelas Fábio Ávila <fabio@avilasistemas.com.br>Ávila Sistemas Ltda.Grupo Recife de TI

  37. Consultas em 2 ou mais tabelas • No SQL Server: cláusulas JOIN. • Podem ser INNER ou OUTER. • Importante uso de apelidos de tabelas. • Normalmente os JOINs unem FK's com PK's. SQL Server - Aula Prática

  38. Sintaxe Parcial do JOIN • SELECT <lista_de_campos> • FROM tabela1 [,tabela2,tabela3...] • <tipo> JOIN <tabelax> ON <criterio_associacao> • Pode existir mais de um JOIN no comando SQL Server - Aula Prática

  39. Exemplo com duas tabelas Use Northwind SELECT cs.companyname, cs.customerid, os.orderdate FROM dbo.customers cs JOIN dbo.orders os ON os.customerid = cs.customerid Where os.orderdate between '01/01/1997' and '03/1/1997' SQL Server - Aula Prática

  40. Exemplo com mais de duas tabelas Use Northwind SELECT cliente = cs.companyname, empregado = em.firstname + ' ' + em.lastname, 'Data Pedido' = os.orderdate, Via = sh.companyname FROM dbo.customers cs JOIN dbo.orders os ON os.customerid = cs.customerid JOIN dbo.employees em ON em.employeeid = os.employeeid JOIN dbo.shippers sh ON sh.shipperId = os.shipVia Where os.orderdate between '01/01/1997' and '03/1/1997' SQL Server - Aula Prática

  41. OUTER JOIN • Pode ser RIGHT, LEFT ou FULL • RIGHT e LEFT são equivalentes, só muda a direção. • FULL mostra das duas tabelas • Aplicações clássicas • Mostrar lista dos clientes que não têm pedidos no Sistema • Mostrar lista dos empregados com os respectivos pedidos realizados (mostrar TODOS os empregados, mesmo os que não efetuaram pedidos). SQL Server - Aula Prática

  42. Exemplo de OUTER JOIN Use Northwind SELECT cs.companyname, cs.customerid, os.orderdate FROM dbo.customers cs LEFT JOIN dbo.orders os ON os.customerid = cs.customerid Where os.orderdate between '01/01/1997' and '03/1/1997' or orderdate is null Order by os.orderdate • Mostra todos os clientes com respectivas datas de pedido. Dois deles (FISSA e Paris Spécialités) não fizeram pedidos. SQL Server - Aula Prática

  43. CROSS JOIN • Basta não especificar o critério de associação • Produto cartesiano de registros SELECT te.TerritoryDescription, re.RegionDescription FROM territories te CROSS JOIN region re SELECT te.TerritoryDescription, re.RegionDescription FROM territories te,region re SQL Server - Aula Prática

  44. UNION • Usado para juntar resultados de diferentes SELECTs SELECT name = (em.firstname + ' ' + em.lastname), em.city,em.postalcode FROM employees em UNION SELECT cs.companyname,cs.city,cs.postalcode FROM customers cs SQL Server - Aula Prática

  45. Sub-Consultas • Consiste em introduzir em determinados pontos da consulta uma outra consulta entre parênteses • Técnica poderosa e sofisticada • Sub-consulta como um campo • Só pode retornar um campo e um valor • Sub-consulta após o FROM ou JOIN • Importante uso de apelidos • Sub-consulta no WHERE • Só pode retornar um campo e um valor SQL Server - Aula Prática

  46. Exercícios • Listar os nomes de empregados que fizeram pedidos no mês de Janeiro/1997, sem repetições de nome. • Listar os nomes das empresas clientes atendidas por Nancy Davolio, sem repetições. • Mostrar os nomes e telefones de clientes e empregados. • Soluções estarão disponíveis em http://www.cin.ufpe.br/~farp/bd1 SQL Server - Aula Prática

  47. Bancos de Dados I Agrupamento de Dados Fábio Ávila <farp@cin.ufpe.br>Ávila Sistemas Ltda.Grupo Recife de TI

  48. Agrupamento de Dados • Pode conter a cláusula GROUP BY e HAVING • Funções de agrupamento • AVG, COUNT, MAX, MIN, SUM, STDEV, STDEVP, VAR, VARP SQL Server - Aula Prática

  49. Agrupamento de Dados • Sem GROUP BY SELECT COUNT(*) FROM orders SELECT COUNT(orderdate) FROM orders SELECT MAX(orderdate) FROM orders SELECT SUM(freight) FROM orders SQL Server - Aula Prática

  50. Agrupamento de Dados • Regras do GROUP BY • Um item da lista de campos do SELECT pode ser um campo de tabela, uma expressão ou uma aplicação de função de agrupamento. • Todo campo ou expressão contido na lista de campos do SELECT que não for aplicação de uma função de agrupamento deve estar na lista de campos do GROUP BY • A inversa não é verdadeira – os campos do GROUP BY não necessariamente precisam estar contidos na lista de campos • Para filtrar registros baseado no resultado de uma função de agrupamento, usar HAVING. SQL Server - Aula Prática

More Related