Modelo Mic- 07 Mic -14 Mic- 26 Mic- 42 Mic- 45 Litros OC ...
SEGUNDA FASE / S2B MIC PERNAMBUCO Banco de Dados Turma: Manhã / FIR Recife-PE .
Transcript of SEGUNDA FASE / S2B MIC PERNAMBUCO Banco de Dados Turma: Manhã / FIR Recife-PE .
SE
GU
ND
A F
AS
E /
S2
B
MIC
PE
RN
AM
BU
CO
Banco de Dados
Turma: Manhã / FIRTurma: Manhã / FIRRecife-PERecife-PE
www.micpernambuco.com.br
Condições de pesquisa NOT atrasam a recuperaçãode dados
Condições de pesquisa LIKE atrasam a recuperaçãode dados
Correspondências exatas ou intervalos acelerama recuperação de dados
A cláusula ORDER BY atrasa a recuperação de dados
Usando aliases para nomes de tabelas Combinando dados de várias tabelas Combinando vários conjuntos de resultados
Exemplo 1 (sem nome de alias)
Exemplo 2 (com nome de alias)
USE joindbSELECT buyer_name, s.buyer_id, qty FROM buyers AS b INNER JOIN sales AS s ON b.buyer_id = s.buyer_idGO
USE joindbSELECT buyer_name, s.buyer_id, qty FROM buyers AS b INNER JOIN sales AS s ON b.buyer_id = s.buyer_idGO
USE joindb SELECT buyer_name, sales.buyer_id, qty FROM buyers INNER JOIN sales ON buyers.buyer_id = sales.buyer_idGO
USE joindb SELECT buyer_name, sales.buyer_id, qty FROM buyers INNER JOIN sales ON buyers.buyer_id = sales.buyer_idGO
Introdução às associações Usando associações internas Usando associações externas Usando associações cruzadas Associando mais de duas tabelas Associando uma tabela a si mesma
Selecionar colunas específicas a partir de várias tabelas A palavra-chave JOIN especifica quais tabelas
serão associadas e como associá-las A palavra-chave ON especifica as colunas que as
tabelas têm em comum
Consultar duas ou mais tabelas para produzir um conjunto de resultados Usar chaves primárias e externas como
condiçõesde associação
Usar colunas comuns às tabelas especificadas para associar tabelas
USE joindbSELECT buyer_name, sales.buyer_id, qty FROM buyers INNER JOIN sales ON buyers.buyer_id = sales.buyer_idGO
USE joindbSELECT buyer_name, sales.buyer_id, qty FROM buyers INNER JOIN sales ON buyers.buyer_id = sales.buyer_idGO
buyer_namebuyer_namebuyer_namebuyer_name
Adam BarrAdam Barr
Sean ChaiSean Chai
Eva CoretsEva Corets
Erin O’MeliaErin O’Melia
buyer_idbuyer_idbuyer_idbuyer_id
11
22
33
44
sales
buyer_idbuyer_idbuyer_idbuyer_id prod_idprod_idprod_idprod_id qtyqtyqtyqty
11
11
44
33
22
33
11
55
1515
55
3737
1111
44 22 10031003
buyers
Resultado
buyer_namebuyer_namebuyer_namebuyer_name
Adam BarrAdam Barr
Adam BarrAdam Barr
Erin O’MeliaErin O’Melia
Eva CoretsEva Corets
buyer_idbuyer_idbuyer_idbuyer_id qtyqtyqtyqty
11
11
44
33
1515
55
3737
1111
Erin O’MeliaErin O’Melia 44 10031003
Exemplo 1Exemplo 1
USE joindbSELECT buyer_name, sales.buyer_id, qty FROM buyers LEFT OUTER JOIN sales ON buyers.buyer_id = sales.buyer_idGO
USE joindbSELECT buyer_name, sales.buyer_id, qty FROM buyers LEFT OUTER JOIN sales ON buyers.buyer_id = sales.buyer_idGO
buyer_idbuyer_idbuyer_idbuyer_id prod_idprod_idprod_idprod_id qtyqtyqtyqty
11
11
44
33
22
33
11
55
1515
55
3737
1111
44 22 10031003
buyer_namebuyer_namebuyer_namebuyer_name
Adam BarrAdam Barr
Sean ChaiSean Chai
Eva CoretsEva Corets
Erin O’MeliaErin O’Melia
buyer_idbuyer_idbuyer_idbuyer_id
11
22
33
44
salesbuyers
Resultado
buyer_namebuyer_namebuyer_namebuyer_name
Adam BarrAdam Barr
Adam BarrAdam Barr
Erin O’MeliaErin O’Melia
Eva CoretsEva Corets
buyer_idbuyer_idbuyer_idbuyer_id qtyqtyqtyqty
11
11
44
33
1515
55
3737
1111
Erin O’MeliaErin O’Melia 44 10031003
Sean ChaiSean Chai NULLNULL NULLNULL
Exemplo 1Exemplo 1
Resultado
USE joindbSELECT buyer_name, qty FROM buyers CROSS JOIN salesGO
USE joindbSELECT buyer_name, qty FROM buyers CROSS JOIN salesGO
buyer_namebuyer_namebuyer_namebuyer_name
Adam BarrAdam Barr
Adam BarrAdam Barr
Adam BarrAdam Barr
Adam BarrAdam Barr
qtyqtyqtyqty
1515
55
3737
1111
Adam BarrAdam Barr 10031003
Sean ChaiSean Chai 1515
Sean ChaiSean Chai 55
Sean ChaiSean Chai 3737
Sean ChaiSean Chai 1111
Sean ChaiSean Chai 10031003
Eva CoretsEva Corets 1515
...... ......
sales
buyer_idbuyer_idbuyer_idbuyer_id prod_idprod_idprod_idprod_id qtyqtyqtyqty
11
11
44
33
22
33
11
55
1515
55
3737
1111
44 22 10031003
buyers
buyer_idbuyer_idbuyer_idbuyer_id
11
22
33
44
buyer_namebuyer_namebuyer_namebuyer_name
Adam BarrAdam Barr
Sean ChaiSean Chai
Eva CoretsEva Corets
Erin O’MeliaErin O’Melia
Exemplo 1Exemplo 1
USE joindbSELECT buyer_name, prod_name, qty FROM buyers INNER JOIN sales ON buyers.buyer_id = sales.buyer_id INNER JOIN produce ON sales.prod_id = produce.prod_idGO
USE joindbSELECT buyer_name, prod_name, qty FROM buyers INNER JOIN sales ON buyers.buyer_id = sales.buyer_id INNER JOIN produce ON sales.prod_id = produce.prod_idGO
producebuyers sales
Resultado
prod_idprod_idprod_idprod_id prod_nameprod_nameprod_nameprod_name
11
22
33
44
ApplesApples
PearsPears
OrangesOranges
BananasBananas
55 PeachesPeaches
buyer_idbuyer_idbuyer_idbuyer_id
11
22
33
44
buyer_namebuyer_namebuyer_namebuyer_name
Adam BarrAdam Barr
Sean ChaiSean Chai
Eva CoretsEva Corets
Erin O’MeliaErin O’Melia
buyer_idbuyer_idbuyer_idbuyer_id
11
11
33
44
prod_idprod_idprod_idprod_id
22
33
11
55
22 22
qtyqtyqtyqty
1515
55
3737
1111
10031003
Exemplo 1Exemplo 1
buyer_namebuyer_namebuyer_namebuyer_name
Erin O’MeliaErin O’Melia
Adam BarrAdam Barr
Erin O’MeliaErin O’Melia
Adam BarrAdam Barr
Eva CoretsEva Corets
prod_nameprod_nameprod_nameprod_name
ApplesApples
PearsPears
PearsPears
OrangesOranges
PeachesPeaches
qtyqtyqtyqty
3737
1515
10031003
55
1111
USE joindbSELECT a.buyer_id AS buyer1, a.prod_id ,b.buyer_id AS buyer2 FROM sales AS a INNER JOIN sales AS b ON a.prod_id = b.prod_idWHERE a.buyer_id > b.buyer_idGO
USE joindbSELECT a.buyer_id AS buyer1, a.prod_id ,b.buyer_id AS buyer2 FROM sales AS a INNER JOIN sales AS b ON a.prod_id = b.prod_idWHERE a.buyer_id > b.buyer_idGO
sales b
buyer_idbuyer_idbuyer_idbuyer_id prod_idprod_idprod_idprod_id qtyqtyqtyqty
11
11
44
33
22
33
11
55
1515
55
3737
1111
44 22 10031003
sales a
buyer_idbuyer_idbuyer_idbuyer_id prod_idprod_idprod_idprod_id qtyqtyqtyqty
11
11
44
33
22
33
11
55
1515
55
3737
1111
44 22 10031003
Resultado
buyer1buyer1buyer1buyer1
44prod_idprod_idprod_idprod_id buyer2buyer2buyer2buyer2
22 11
Exemplo 3Exemplo 3
VIEWSVIEWS
Focalizar os dados para os usuários Focalizar somente em dados importantes ou
apropriados Limitar o acesso a dados confidenciais
Mascarar a complexidade do banco de dados Ocultar estruturas complexas de banco de dados Simplificar consultas complexas, incluindo consultas
distribuídas para dados heterogêneos Simplificar o gerenciamento de permissões de
usuários Melhorar o desempenho Organizar dados para serem exportados para
outros aplicativos
VIEWSVIEWS
EmployeeViewEmployeeViewEmployeeViewEmployeeView
Lastname Lastname Firstname Firstname
Davolio Fuller Leverling
Davolio Fuller Leverling
Nancy Andrew Janet
Nancy Andrew Janet
EmployeesEmployeesEmployeesEmployees
EmployeeIDEmployeeID LastName LastName FirstnameFirstname TitleTitle
123
123
DavolioFullerLeverling
DavolioFullerLeverling
NancyAndrewJanet
NancyAndrewJanet
~~~~~~~~~
~~~~~~~~~
View de usuárioView de usuário
USE NorthwindGOCREATE VIEW dbo.EmployeeViewAS SELECT LastName, FirstnameFROM Employees
USE NorthwindGOCREATE VIEW dbo.EmployeeViewAS SELECT LastName, FirstnameFROM Employees
VIEWSVIEWS
OrderIDOrderIDOrderIDOrderID
1066310827104271045110515
1066310827104271045110515
CustomerIDCustomerIDCustomerIDCustomerID
BONAP BONAP PICCO QUICKQUICK
BONAP BONAP PICCO QUICKQUICK
~~~ ~~~ ~~~ ~~~ ~~~
~~~ ~~~ ~~~ ~~~ ~~~
RequiredDateRequiredDateRequiredDateRequiredDate
1997-09-241998-01-261997-02-241997-03-051997-05-07
1997-09-241998-01-261997-02-241997-03-051997-05-07
ShippedDateShippedDateShippedDateShippedDate
1997-10-031998-02-061997-03-031997-03-121997-05-23
1997-10-031998-02-061997-03-031997-03-121997-05-23
Orders Customers
ShipStatusViewUSE NorthwindGOCREATE VIEW dbo.ShipStatusViewASSELECT OrderID, ShippedDate, ContactNameFROM Customers c INNER JOIN Orders o ON c.CustomerID = O.CustomerIDWHERE RequiredDate < ShippedDate
USE NorthwindGOCREATE VIEW dbo.ShipStatusViewASSELECT OrderID, ShippedDate, ContactNameFROM Customers c INNER JOIN Orders o ON c.CustomerID = O.CustomerIDWHERE RequiredDate < ShippedDate
CustomerIDCustomerIDCustomerIDCustomerID
BONAPPICCOQUICK
BONAPPICCOQUICK
CompanyNameCompanyNameCompanyNameCompanyName
Bon app'Piccolo und mehrQUICK-Stop
Bon app'Piccolo und mehrQUICK-Stop
ContactNameContactNameContactNameContactName
Laurence LebihanGeorg PippsHorst Kloss
Laurence LebihanGeorg PippsHorst Kloss
OrderIDOrderIDOrderIDOrderID
102641027110280
102641027110280
1996-08-211996-08-291996-09-11
1996-08-211996-08-291996-09-11
ShippedDateShippedDateShippedDateShippedDate
1996-08-231996-08-301996-09-12
1996-08-231996-08-301996-09-12
ContactNameContactNameContactNameContactName
Maria LarssonArt BraunschweigerChristina Berglund
Maria LarssonArt BraunschweigerChristina Berglund
Alterando views
Descartando views
USE NorthwindGOALTER VIEW dbo.EmployeeViewAS SELECT LastName, FirstName, ExtensionFROM Employees
USE NorthwindGOALTER VIEW dbo.EmployeeViewAS SELECT LastName, FirstName, ExtensionFROM Employees
DROP VIEW dbo.ShipStatusViewDROP VIEW dbo.ShipStatusView
VIEWSVIEWS
PROCEDIMENTOS PROCEDIMENTOS ARMAZENADOS (STORED ARMAZENADOS (STORED PROCEDURES)PROCEDURES) Compartilham a lógica do aplicativo Protegem os detalhes das tabelas do banco de
dados Fornecem mecanismos de segurança Melhoram o desempenho Reduzem o tráfego de rede
PROCEDIMENTOS ARMAZENADOS (STORED PROCEDURES)USE LocadoraGOCREATE PROC dbo.FilmesNaoDevolvidosAS SELECT * FROM dbo.Pedidos WHERE DataDeEntregaPrevista < GETDATE() AND DataDeEntregaEfetiva IS NullGO
EXEC FilmesNaoDevolvidos
PROCEDIMENTOS ARMAZENADOS (STORED PROCEDURES)
USE NorthwindGOALTER PROC dbo.OverdueOrdersASSELECT CONVERT(char(8), RequiredDate, 1) RequiredDate, CONVERT(char(8), OrderDate, 1) OrderDate, OrderID, CustomerID, EmployeeID FROM OrdersWHERE RequiredDate < GETDATE() AND ShippedDate IS NullORDER BY RequiredDateGO
USE NorthwindGOALTER PROC dbo.OverdueOrdersASSELECT CONVERT(char(8), RequiredDate, 1) RequiredDate, CONVERT(char(8), OrderDate, 1) OrderDate, OrderID, CustomerID, EmployeeID FROM OrdersWHERE RequiredDate < GETDATE() AND ShippedDate IS NullORDER BY RequiredDateGO
PROCEDIMENTOS ARMAZENADOS (STORED PROCEDURES)
CREATE PROCEDURE dbo.[Locacao de Filmes] @DataInicio DateTime, @DataEntrega DateTime ASIF @DataInicio IS NULL OR @DataEntrega IS NULLBEGIN RAISERROR(‘Valores NULL não são permitidos', 14, 1) RETURNENDSELECT PED.DataEntregaPrevista, PRE.Subtotal, FROM PEDIDOS PED INNER JOIN PRECO PRE ON PED.IDPedido = PRE.IDPedidoWHERE PED.DataEntregaPrevista BETWEEN @DataInicioAND @DataEntregaGO
CREATE PROCEDURE dbo.[Locacao de Filmes] @DataInicio DateTime, @DataEntrega DateTime ASIF @DataInicio IS NULL OR @DataEntrega IS NULLBEGIN RAISERROR(‘Valores NULL não são permitidos', 14, 1) RETURNENDSELECT PED.DataEntregaPrevista, PRE.Subtotal, FROM PEDIDOS PED INNER JOIN PRECO PRE ON PED.IDPedido = PRE.IDPedidoWHERE PED.DataEntregaPrevista BETWEEN @DataInicioAND @DataEntregaGO
PROCEDIMENTOS ARMAZENADOS (STORED PROCEDURES) Passando valores por nome de parâmetro
Passando valores por posição
EXEC AdicionarCliente @ClienteID = 154, @NomeCliente = 'Maria Anders', @NomeEmpresa = 'Alfreds Futterkiste', @Cargo = 'Sales Representative', @Endereco = 'Obere Str. 57', @Cidade = 'Berlin', @CodigoPostal = '12209', @Pais = 'Germany', @Telefone = '030-0074321'
EXEC AdicionarCliente @ClienteID = 154, @NomeCliente = 'Maria Anders', @NomeEmpresa = 'Alfreds Futterkiste', @Cargo = 'Sales Representative', @Endereco = 'Obere Str. 57', @Cidade = 'Berlin', @CodigoPostal = '12209', @Pais = 'Germany', @Telefone = '030-0074321'
EXEC AdicionarCliente 154, 'Maria Anders', 'Alfreds Futterkiste', 'Sales Representative', 'Obere Str. 57', 'Berlin', NULL, '12209', 'Germany', '030-0074321'
EXEC AdicionarCliente 154, 'Maria Anders', 'Alfreds Futterkiste', 'Sales Representative', 'Obere Str. 57', 'Berlin', NULL, '12209', 'Germany', '030-0074321'
PROCEDIMENTOS ARMAZENADOS (STORED PROCEDURES)
CREATE PROCEDURE dbo.Multiplicador @n1 smallint, @n2 smallint, @resultado smallint OUTPUTAS SET @resultado = @n1 * @n2GO
DECLARE @resposta smallintEXECUTE Multiplicador 5,6, @resposta OUTPUTSELECT ‘O resultado é:', @resposta
O resultado é: 30
CREATE PROCEDURE dbo.Multiplicador @n1 smallint, @n2 smallint, @resultado smallint OUTPUTAS SET @resultado = @n1 * @n2GO
DECLARE @resposta smallintEXECUTE Multiplicador 5,6, @resposta OUTPUTSELECT ‘O resultado é:', @resposta
O resultado é: 30
Resultados do Resultados do procedimentoprocedimentoarmazenadoarmazenado
Resultados do Resultados do procedimentoprocedimentoarmazenadoarmazenado
Executando o Executando o procedimentoprocedimentoarmazenadoarmazenado
Executando o Executando o procedimentoprocedimentoarmazenadoarmazenado
Criando o Criando o procedimentoprocedimentoarmazenadoarmazenado
Criando o Criando o procedimentoprocedimentoarmazenadoarmazenado