MySQL - Comandos DDL - Drop View Alter

Prévia do material em texto

11
Banco de Dados II – Prof Tavares
Banco de Dados II
MySQL - Estudo de Caso
Instruções DDL 02/02
Banco de Dados - PROJETO
22
Banco de Dados II – Prof Tavares
33
Banco de Dados II – Prof Tavares
DROP TABLE [IF EXISTS] tableName, ...
CREATE TABLE [IF NOT EXISTS] tableName (
columnName columnType columnAttribute, ...
PRIMARY KEY(columnName),
FOREIGN KEY (columnNmae)
REFERENCES tableName (columnNmae)
)
DROP TABLE 
44
Banco de Dados II – Prof Tavares
ALTER TABLE tableName
{ADD [COLUMN] columnName columnDefinition}
{ALTER|MODIFY [COLUMN] columnName columnDefinition
{SET DEFAULT columnDefaultValue} | {DROP DEFAULT}}
{DROP [COLUMN] columnName [RESTRICT|CASCADE]}
{ADD tableConstraint}
{DROP tableConstraint [RESTRICT|CASCADE]}
ALTER TABLE 
55
Banco de Dados II – Prof Tavares
ALTER TABLE customers
ADD COLUMN email VARCHAR(100)
ALTER TABLE 
66
Banco de Dados II – Prof Tavares
Criar uma tabela baseada numa consulta
CREATE TABLE temp_produto AS SELECT * FROM products
Modificar o tipo de um campo
ALTER TABLE temp_produto MODIFY productName varchar(80)
Adicionar um novo campo na tabela
ALTER TABLE temp_produto ADD estoque integer
Renomear o nome de um campo na tabela
ALTER TABLE temp_produto CHANGE estoque teste_estoque
varchar (10)
Exemplos de Comandos DDL
77
Banco de Dados II – Prof Tavares
Apagar um campo da tabela
ALTER TABLE temp_produto DROP COLUMN estoque
Renomear uma tabela
RENAME table temp_produto TO copia_produto
Adicionar uma Chave Primária (PK)
ALTER TABLE <tabela>ADD PRIMARY(coluna)
Exemplos de Comandos DDL
88
Banco de Dados II – Prof Tavares
• Uma VIEW é uma tabela virtual gerada a partir de uma
consulta, na qual as informações são obtidas diretamente nas
tabelas usadas nas consultas.
• Atualizando-se os dados das tabelas de origem,
automaticamente atualiza-se a VIEW.
VIEW (Visão)
99
Banco de Dados II – Prof Tavares
Vantagens do uso de VIEW
• Uma das vantagens da utilização de VIEW está na facilidade
de se manipular dados originados de diferentes tabelas.
• Outra vantagem se relaciona a segurança, pois é possível
'ocultar' campos ou dados através da consulta.
• Visões geradas sem campos calculados podem ser usadas,
inclusive, para fazer inserção, alteração e exclusão de dados.
VIEW (Visão)
1010
Banco de Dados II – Prof Tavares
Criação de Visões
CREATE VIEW <nome_da_visao> AS <expressão_consulta>
onde, <expressão_consulta> é qualquer consulta SQL válida.
Exemplo:
CREATE VIEW view_clientes AS
SELECT customerName, addressLine1, phone
FROM customers
VIEW (visão)
1111
Banco de Dados II – Prof Tavares
Criação de Visões
Para acessar uma VIEW, usamos o comando SELECT como se
fosse uma tabela normal (inclusive via linguagem de
programação)
VIEW (visão)
select * from view_clientes
1212
Banco de Dados II – Prof Tavares
Pode-se criar visões que utilizem dados de mais de uma tabela.
Por exemplo, para exibir a relação de empregados com seus
respectivos clientes.
CREATE VIEW empregados_contato_cliente AS
SELECT concat(e.firstName," ",e.lastName) as empregado, 
c.customerName as cliente 
FROM employees e 
JOIN customers c ON e.employeeNumber = 
c.salesRepEmployeeNumber
ORDER BY e.firstName
Acesso: select * from empregados_contato_cliente
VIEW (visão)
1313
Banco de Dados II – Prof Tavares
Consultas com campos calculados também podem ser usados
para criar uma visão que apresentar a quantidade de funcionário
por departamento.
CREATE VIEW pagamentos_clientes AS
SELECT c.customerName as cliente, 
sum(p.amount) as quantia_paga
FROM payments p JOIN customers c 
ON p.customerNumber = c.customerNumber
GROUP BY c.customerName
Acesso: 
select * 
from pagamentos_clientes
where quantia_paga > 100000
VIEW (visão)
1414
Banco de Dados II – Prof Tavares
Exclusão de uma VIEW
DROP VIEW pagamentos_clientes
VIEW (visão)
1515
Banco de Dados II – Prof Tavares
✓ Os índices são utilizados, principalmente, para melhorar o
desempenho do banco de dados (embora a utilização não
apropriada possa resultar em uma degradação de
desempenho).
✓ O índice do banco de dados segue a mesma filosofia de um
índice de um livro: achar a informação que procura mais
rapidamente.
✓ O comando CREATE INDEX constrói o índice
nome_do_índice na tabela especificada.
INDEX
1616
Banco de Dados II – Prof Tavares
✓ Estudos apontam que 80% das atualizações de hardware
por novos equipamentos seriam desnecessários, bastando
apenas o ajuste adequado do software.
✓ ATENÇÃO: O uso excessivo de indexação pode tornar o
acesso lento!!!
✓ Assim, deve-se criar índices apenas para tabelas com
grande número de registros (alguns milhares, pelo menos)
e para campos que sejam frequentemente utilizados em
buscas.
INDEX
1717
Banco de Dados II – Prof Tavares
Instrução:
CREATE [UNIQUE] INDEX <nome_indice>
ON <nome_tabela> (<nome_coluna> [ASC / DESC])
Exemplo:
CREATE UNIQUE INDEX idx_CHECK
ON PAYMENTS(checkNumber);
INDEX
1818
Banco de Dados II – Prof Tavares
CREATE TABLE `cliente_teste`
( `codigo_cliente` smallint(6) NOT NULL,
`nome_cliente` varchar(20) DEFAULT NULL,
`endereco` varchar(30) DEFAULT NULL,
`cidade` varchar(15) DEFAULT NULL,
`CEP` varchar(8) DEFAULT NULL,
`UF` varchar(2) DEFAULT NULL,
`CGC` varchar(20) DEFAULT NULL,
`IE` varchar(20) DEFAULT NULL,
PRIMARY KEY (`codigo_cliente`),
UNIQUE KEY `idx_CGC` (`CGC`)
)
INDEX
1919
Banco de Dados II – Prof Tavares
START TRANSACTION;
SET autocommit = 0; >>> default = 1
CREATE TABLE temp_CLIENTE AS SELECT * FROM cliente;
set sql_safe_updates = 0;
START TRANSACTION;
delete from temp_cliente;
rollback;
select * from temp_cliente;
2020
Banco de Dados II – Prof Tavares
START TRANSACTION;
set sql_safe_updates = 0;
START TRANSACTION;
delete from temp_cliente;
commit;
select * from temp_cliente;
2121
Banco de Dados II – Prof Tavares
Fim