MySQL Comandos DDL Insert

Prévia do material em texto

1 1 
Banco de Dados II – Prof Tavares 
Banco de Dados II 
MySQL - Estudo de Caso 
Instruções DDL 
Banco de Dados - PROJETO 
2 2 
Banco de Dados II – Prof Tavares 
MySQL - Tipos de Dados 
3 3 
Banco de Dados II – Prof Tavares 
MySQL - Tipos de Dados 
4 4 
Banco de Dados II – Prof Tavares 
MySQL - Tipos de Dados 
5 5 
Banco de Dados II – Prof Tavares 
O comando para criar tabelas: 
create table TABELA( 
CAMPO1 TIPO OPÇÃO1 OPÇÃO2 OPÇÃO3, 
CAMPO2 TIPO OPÇÃO1, ...... 
CAMPOn TIPO OPÇÃO1); 
 
As opções são algumas palavras chaves que determinam algumas 
características do campo: 
NOT NULL - campo não pode ser nulo, ou seja, não pode estar em branco; 
AUTO_INCREMENT - se deixarmos o campo em branco, o MySQL automaticamente 
o preencherá com um inteiro uma unidade maior que o valor máximo já existente. 
Deve ser usado com o tipo de dado inteiro e só pode haver um campo destes em 
cada tabela. Campos marcados com esta opção devem ser indexados; 
PRIMARY KEY - define o campo como chave primária. Os dados inseridos neste 
campo devem ser únicos e eles são automaticamente indexados; 
UNSIGNED - significa que o valor só pode ter valor positivo ou zero. Usado com 
dados do tipo inteiro. 
 
Criando tabelas 
6 6 
Banco de Dados II – Prof Tavares 
Exemplo: 
create table material 
( 
 codigo char(6) not null primary key, 
 conta tinyint unsigned not null, 
 unidade tinyint unsigned not null, 
 descricao char(50) not null, 
 qtde bigint unsigned not null, 
 valor float(5,2) not null 
); 
 
Criando tabelas 
7 7 
Banco de Dados II – Prof Tavares 
Banco de Dados 
PROJETO 
 
8 8 
Banco de Dados II – Prof Tavares 
Modelo Conceitual 
9 9 
Banco de Dados II – Prof Tavares 
Modelo Lógico 
10 10 
Banco de Dados II – Prof Tavares 
11 11 
Banco de Dados II – Prof Tavares 
12 12 
Banco de Dados II – Prof Tavares 
Criação das Tabelas 
SQL 
ESTUDO DE CASO 
CREATE TABLE CLIENTE 
( codigo_cliente smallint not null PRIMARY KEY, 
 nome_cliente varchar(20), 
 endereco varchar(30), 
 cidade varchar(15), 
 CEP varchar(8), 
 UF varchar(2), 
 CGC varchar(20), 
 IE varchar(20) ) 
13 13 
Banco de Dados II – Prof Tavares 
14 14 
Banco de Dados II – Prof Tavares 
Criação das Tabelas 
SQL 
ESTUDO DE CASO 
CREATE TABLE VENDEDOR 
( codigo_vendedor smallint not null, 
 nome_vendedor varchar(20), 
 salario_fixo decimal(12,2), 
 faixa_comissao char(1), 
 PRIMARY KEY (codigo_vendedor) ) 
15 15 
Banco de Dados II – Prof Tavares 
Criação das Tabelas 
SQL 
ESTUDO DE CASO 
CREATE TABLE PEDIDO 
( 
num_pedido int not null PRIMARY KEY, 
prazo_entrega smallint not null, 
codigo_cliente smallint not null, 
codigo_vendedor smallint not null, 
FOREIGN KEY (codigo_cliente) 
 REFERENCES CLIENTE(codigo_cliente), 
FOREIGN KEY (codigo_vendedor) 
 REFERENCES VENDEDOR(codigo_vendedor) 
) 
16 16 
Banco de Dados II – Prof Tavares 
Criação das Tabelas 
SQL 
ESTUDO DE CASO 
CREATE TABLE PRODUTO 
( codigo_produto smallint not null, 
 unidade varchar(3), 
 descricao varchar(30), 
 val_unit decimal(12,2), 
 PRIMARY KEY (codigo_produto) ) 
17 17 
Banco de Dados II – Prof Tavares 
Criação das Tabelas 
SQL 
ESTUDO DE CASO 
CREATE TABLE ITEM_DO_PEDIDO 
( 
num_pedido int not null , 
codigo_produto smallint not null , 
quantidade decimal, 
FOREIGN KEY (num_pedido) 
 REFERENCES PEDIDO(num_pedido), 
FOREIGN KEY (codigo_produto) 
 REFERENCES PRODUTO(codigo_produto), 
PRIMARY KEY (num_pedido , codigo_produto) 
) 
18 18 
Banco de Dados II – Prof Tavares 
Para inserir dados numa tabela use o comando: 
 
INSERT into TABELA (CAMPO1, CAMPO2, ... , CAMPOn) 
 VALUES (VALOR1, VALOR2, ... , VALORn); 
 
INSERT into TABELA VALUES (VALOR1, VALOR2, ... ,VALORn); 
 
Inserindo dados (CRUD) 
19 19 
Banco de Dados II – Prof Tavares 
SQL 
ESTUDO DE CASO 
Tabela Cliente 
INSERT INTO CLIENTE VALUES (1, "Carlos Alberto", "Rua da Paz, 12", "Rio", 
"20000", "RJ", "0101010", "02020"); 
INSERT INTO CLIENTE VALUES (2, "Luis Eduardo", "Rua do Sol, 102", "Rio", 
"20000", "RJ", "454545", "02021"); 
INSERT INTO CLIENTE VALUES (3, "Alan Silva", "Rua da Alegria, 12", "São 
Paulo", "11000", "SP", "686879", "02020"); 
INSERT INTO CLIENTE VALUES (4, "Carlos Neves", "Rua do Sol, 212", "São 
Paulo", "11000", "SP", "686878",null); 
20 20 
Banco de Dados II – Prof Tavares 
Inserção de Dados nas Tabelas (CRUD) 
SQL 
ESTUDO DE CASO 
Tabela Vendedor 
INSERT INTO VENDEDOR VALUES (10, "João Carlos", 200.00, "A"); 
INSERT INTO VENDEDOR VALUES (20, "Luis Pedro", 300.00, "B"); 
INSERT INTO VENDEDOR VALUES (30, "Luis Lemos", 350.00, "A"); 
21 21 
Banco de Dados II – Prof Tavares 
Inserção de Dados nas Tabelas (CRUD) 
SQL 
ESTUDO DE CASO 
Tabela Produto 
INSERT INTO PRODUTO VALUES (100, "un", "Leite em pó", 3.00); 
INSERT INTO PRODUTO VALUES (200, "kg", "Queijo", 12.00); 
INSERT INTO PRODUTO VALUES (300, "un", "Macarrão", 4.00); 
INSERT INTO PRODUTO VALUES (400, "lt", "Suco laranja", 2.00); 
INSERT INTO PRODUTO VALUES (500, "un", "Chocolate", 5.00); 
22 22 
Banco de Dados II – Prof Tavares 
Inserção de Dados nas Tabelas (CRUD) 
SQL 
ESTUDO DE CASO 
Tabela Pedido 
INSERT INTO PEDIDO VALUES (1000, 10, 1, 10); 
INSERT INTO PEDIDO VALUES (2000, 20, 1, 20); 
INSERT INTO PEDIDO VALUES (3000, 15, 2, 10); 
 
23 23 
Banco de Dados II – Prof Tavares 
Inserção de Dados nas Tabelas (CRUD) 
SQL 
ESTUDO DE CASO 
Tabela Item_do_Pedido 
INSERT INTO ITEM_DO_PEDIDO VALUES (1000, 100, 10); 
INSERT INTO ITEM_DO_PEDIDO VALUES (1000, 200, 20); 
INSERT INTO ITEM_DO_PEDIDO VALUES (1000, 300, 15); 
INSERT INTO ITEM_DO_PEDIDO VALUES (2000, 300, 40); 
INSERT INTO ITEM_DO_PEDIDO VALUES (2000, 400, 10); 
INSERT INTO ITEM_DO_PEDIDO VALUES (2000, 500, 5); 
INSERT INTO ITEM_DO_PEDIDO VALUES (3000, 500, 5); 
24 24 
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 
25 25 
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 
26 26 
Banco de Dados II – Prof Tavares 
ALTER TABLE cliente 
ADD COLUMN email VARCHAR(100) 
ALTER TABLE 
27 27 
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 
28 28 
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 umaChave Primária (PK) 
ALTER TABLE <tabela>ADD PRIMARY(coluna) 
Exemplos de Comandos DDL 
29 29 
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. 
• Tipos de Visão: 
1. Visão Idêntica 
2. Visão por Seleção de Linhas 
3. Visão por Seleção de Colunas 
4. Visão por Seleção de Linhas e Colunas 
5. Visão por Junção de Tabelas 
VIEW (visão) 
30 30 
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) 
31 31 
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) 
32 32 
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 
33 33 
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) 
34 34 
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) 
35 35 
Banco de Dados II – Prof Tavares 
Exclusão de uma VIEW 
 
 
DROP VIEW pagamentos_clientes 
VIEW (visão) 
36 36 
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 
37 37 
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 
38 38 
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_CLIENTES_CODIGO 
ON CLIENTE(CGC); 
describe cliente; 
INDEX 
39 39 
Banco de Dados II – Prof Tavares 
Fim