Slide 1 - Blog da Prof Andrea Garcia

Leandro Lago da Silva
Banco de Dados I
Técnico Integrado em Informática
MySQL e Esquemas
Usando
MySQL Query Browser
MySQL Console
2010
• PARTE I
2
• TIPOS DE DADOS
3
Principais tipos
•
•
•
INT (M) UNSIGNED ZEROFILL
FLOAT (M,D) UNSIGNED ZEROFILL
DOUBLE (M,D) UNSIGNED ZEROFILL
•
•
•
•
•
DECIMAL (M ,D) UNSIGNED ZEROFILL
BLOB (M)
CHAR (M)
VARCHAR (M)
TEXT (M)
•
•
•
•
DATE
DATETIME
TIME
YEAR (2|4)
•
•
ENUM
SET
4
Principais tipos Numéricos
•
INT (M) UNSIGNED ZEROFILL = INTEGER
– Signed: -2.147.483.648 a 2.147.483.647
– Unsigned: 0 a 4.294.967.295.
•
FLOAT (M,D) UNSIGNED ZEROFILL
– Single-precision: 7 casas decimais
• -3.402.823.466E+38
• 3.402.823.466E+38
•
DOUBLE (M,D) UNSIGNED ZEROFILL
– Double-precision: 15 casas decimais
• -1.7976931348623157E+308
• 1.7976931348623157E+308
•
DECIMAL (M ,D) UNSIGNED ZEROFILL
– Double armazenado em caracteres.
– Geralmente usado para valores monetários.
5
Principais tipos Textuais
•
BLOB (M)
– String usado para armazenamento de dados binários.
– 65535 caracteres.
– Case-sensitive: “texto” != “Texto”
•
CHAR (M)
– 255 caracteres
– Preenche espaços vazios com caracter espaço.
– Ex: CHAR(10)  Inserir “John”  “John “
•
VARCHAR (M)
– 255 caracteres
– Não preenche espaços vazios. (+ novo)
•
TEXT (M)
– String usado para armazenamento de dados textuais.
– Non Case-Sensitive: “texto” == “Texto”
6
Principais tipos de Data/Hora
•
DATE
– AAAA-MM-DD
 Exemplo: ‘2009-08-01’
– 1000-01-01 até 9999-12-31
•
DATETIME
– AAAA-MM-DD HH:MM:SS
– Exemplo: ‘2009-08-01 08:25:56’
– 1000-01-01 00:00:00 até 9999-12-31 23:59:59
•
TIME
– HH:MM:SS
 Exemplo: ’08:25:56’
– -838:59:59 até 838:59:59
•
YEAR (2|4)
– AAAA ou AA
– 1901 até 2155
7
Principais Tipos: Listas
•
ENUM
– Define lista de valores.
– Na inserção, campo deve assumir um dos valores.
• TABLE Pessoa (
nome VARCHAR(45) PRIMARY KEY,
sexo ENUM (‘M’, ‘F’)
);
• INSERT INTO Pessoa VALUES (‘Jane Dark’, ‘F’);
•
SET
– Define lista de valores.
– Na inserção, campo pode assumir zero ou mais valores.
• TABLE Filme (
nome VARCHAR(45) PRIMARY KEY,
genero SET (‘ação’, ‘terror’, ‘comédia’, ‘drama’, ‘romance’)
);
• INSERT INTO Pessoa VALUES (‘Resident Evil’, ‘ação, terror’);
8
Características dos tipos
• Algumas convenções no uso dos tipos
–M
• Indica o tamanho do campo
– Número de dígitos/letras
• Exemplo: INT (M), VARCHAR (M)
–D
• Aplicada aos tipos de ponto flutuante
• Indica o número de digitos após o ponto decimal.
Exemplo: FLOAT (M, D)
9
Características dos tipos
• Algumas convenções no uso dos tipos
– ZEROFILL
• Preenche o campo com zeros.
• Exemplo
– INT(6) ZEROFILL
– Inserir 8  000008
– UNSIGNED
• Só aceita valores positivos
– unsigned = sem sinal
• ZEROFILL assume automaticamente UNSIGNED.
10
• MYSQL CONSOLE
11
Para testar os códigos SQL
• Instalar MySQL
• Abrir console do Windows: “cmd.exe”
• Entrar no console do mysql:
– mysql --user=usuario --password=senha
– mysql -u usuario -p senha(abreviado)
– mysql -u usurario
(senha é pedida depois)
• Utilizar os SQL para
–
–
–
–
Criar e visualizar a estrutura de bases de dados
Criar e visualizar a estrutura de bases de tabelas
Inserir, remover e alterar dados nas tabelas.
Realizar consultas nas tabelas (relatórios).
12
Comandos console mysql
• show databases;
mostra bases de dados existentes
• use <nome da base>;
seleciona a base de dados a ser usada
• show tables;
mostra as tabelas contidas na base de dados selecionada
• describe <nome da tabela>;
mostra a estrutura da tabela dada
• source <caminho do arquivo de scripts>;
executa os scripts contidos no arquivo (pode ser “txt”)
13
• MySQL Query Browser
14
MS Query Browser
• Instalado no pacote “Gui Tools”
• Executa:
1 comando SQL (Resultset)
N comandos SQL (Script)
15
DDL (Data Definition Language)
Definindo esquemas (estrutura)
16
CREATE/DROP DATABASE
#cria base de dados
CREATE DATABASE hospital;
CREATE DATABASE `hospital`;
CREATE DATABASE IF NOT EXISTS hospital;
#seleciona base de dados (chamado após CREATE DATABASE)
USE hospital;
#remove base de dados
DROP DATABASE hospital;
DROP DATABASE `hospital`;
DROP DATABASE IF EXISTS hospital;
• No MySQL, cada Base de Dados é uma pasta:
C:\Arquivos de Programas\MySQL\MySQL
Server\data\hospital\
17
CREATE TABLE
CREATE TABLE
pac_rg
nome
idade
cidade
doenca
PRIMARY KEY
);
paciente (
int(10),
#inteiro de 10 dígitos
varchar(45), #string de 45 caracteres
int(10),
varchar(45),
varchar(45),
(pac_rg)
#chave primária (PK)
• No MySQL, cada Tabela é um arquivo:
C:\Arquivos de Programas\MySQL\MySQL
Server\data\hospital\...
18
CREATE/DROP TABLE
#renomeia tabela
RENAME TABLE paciente TO paciente2006;
#cria tabela com cautela
CREATE TABLE IF NOT EXISTS hospital;
#cria tabela com cautela
DROP TABLE IF EXISTS hospital;
OBS: as tabelas sempre devem ser destruídas na
ordem inversa que foram criadas por causa das
dependências de chaves estrangeiras.
19
PRIMARY KEY (PK)
CREATE TABLE telefone (
ddd
varchar(2),
numero
varchar(8),
tipo
int,
PRIMARY KEY (ddd, numero) # PK com N campos
);
CREATE TABLE
id
ddd
numero
tipo
);
paciente (
int PRIMARY KEY # PK com 1 campo
varchar(2),
varchar(8),
int
20
CONSTRAINTS (Restrições)
CREATE TABLE paciente (
pac_rg int(10) unsigned NOT NULL auto_increment,
nome
varchar(45) NOT NULL default ‘n/d’,
idade
int(3) unsigned default 18 zerofill,
cidade varchar(45) NOT NULL default ‘n/d’,
doenca varchar(45) NOT NULL default ‘n/d',
PRIMARY KEY
(pac_rg,nome)
);
•
•
•
•
Unsigned: não possui sinal (números positivos).
NULL: aceita valor null (padrão para todo campo caso deixe vazio).
NOT NULL: não pode ficar (NULL) na inserção do registro
Default: valor padrão, caso não seja especificado valor.
– Usada quando não queremos que null seja o valor padrão do campo.
•
•
•
AUTO_INCREMENT: incrementa em 1 o valor a cada novo registro.
PRIMARY KEY: indica os campos que são chave primária.
ZEROFILL: preenche com zeros à esquerda
21
FOREIGN KEY (FK)
CREATE TABLE transacao ( # tabela 1:1:1
cliente_cod
VARCHAR (20),
# PK FK
conta_cod
VARCHAR (10),
# PK FK
filial_cod
VARCHAR (5),
# FK
data
DATE,
tipo
VARCHAR(2),
valor
DECIMAL(12,2),
primary key (cliente_cod, conta_cod, data),
# FKs de tabelas diferente  declaração separada
FOREIGN KEY (cliente_cod)
REFERENCES cliente (cliente_cod),
FOREIGN KEY (conta_cod)
REFERENCES conta (conta_cod),
FOREIGN KEY (filial_cod)
REFERENCES filial (filial_cod)
);
22
FOREIGN KEY (FK)
CREATE TABLE cliente_tem_telefone (# tabela N:N
cliente_cod
int unsigned not null,
tel_ddd
int(3) zerofill,
tel_num
int(9) zerofill,
primary key (cod),
# FKs da mesma tabela  declaração única
FOREIGN KEY (tel_ddd, tel_num)
REFERENCES telefone (ddd, num)
);
23
TIPOS DE TABELAS
• Principais tipos de tabelas do MySQL
– MyISAM
• Ignora restrições de chaves estrangeiras
• Melhor desempenho
• Sem transação (operação atômica)
– InnoDB
• Verifica restrições de chaves estrangeiras
• Pior desempenho
• Com transação
24
FOREIGN KEY (FK)
CREATE TABLE cliente_tem_telefone (# tabela N:N
cliente_cod
int unsigned not null,
tel_ddd
int(3) zerofill,
tel_num
int(9) zerofill,
primary key (cod),
# FKs da mesma tabela  declaração única
FOREIGN KEY (tel_ddd, tel_num)
REFERENCES telefone (ddd, num)
)
Type=InnoDB;
ou...
Engine=InnoDB;
25
ALTER TABLE
ALTER TABLE tabela
ADD [ COLUMN ] coluna tipo [ restrição_de_coluna [ ... ] ]
ALTER TABLE cliente
ADD cliente_cod int unsigned not null,
ADD tel_ddd
int(3) zerofill,
ADD tel_num
int(9) zerofill
ALTER TABLE tabela
ALTER [ COLUMN ] coluna { SET DEFAULT valor | DROP DEFAULT
}
ALTER TABLE cliente ALTER obs SET default ‘Observações‘
ALTER TABLE cliente ALTER obs DROP default
ALTER TABLE cliente
RENAME obs TO observacao
26
Exercício
Crie e destrua todas as tabelas
• Ambulatorio(numero, andar, capacidade)
• Medico(cod, crm, nome, idade, data_nasc, especialidade, amb_numero)
• Paciente(cod, nome, idade, cidade)
• Consulta(med_cod pac_cod, data, hora, diagnostico)
• Funcionario(cod, nome, idade, cidade, salário, amb_numero)
OBS: a ordem de criação e destruição é importante.
27
DML (Data Manipulation Language)
Inserindo dados (povoamento)
28
INSERT INTO
CREATE TABLE pacientes (
pac_rg
int(10) unsigned NOT NULL auto_increment,
nome
varchar(45) NOT NULL default ‘’,
idade
int(10) unsigned default ‘0’,
cidade
varchar(45) default ‘’,
doenca
varchar(45) NOT NULL default ‘’,
sexo
enum(‘M’,’F’),
PRIMARY KEY (pac_rg,nome)
);
INSERT INTO pacientes
VALUES (12345,‘John Doe’,25,‘Sarandi’,‘Gripe’);
INSERT INTO pacientes (pac_rg, nome, doenca)
VALUES (12345, ‘John Doe’, ‘Gripe);
29
INSERT INTO
• Inserindo várias linhas de uma só vez.
INSERT INTO paciente VALUES
('1', 'Mac', '25', NULL , 'gripe', 'm'),
('2', 'Ana', '24', 'Peromba', 'osmose', ‘f'),
('3', 'Jane', '25', 'Pitomba', 'gripe', ‘f'),
('4', 'Marlos', '20', 'Amebópolis', 'fedor', 'm'),
('5', 'Clark', '27', 'Pequenópolis', 'criptonose', 'm'),
('6', 'Barlos', '34', NULL, 'resfriado', 'm'),
('7', 'Ada', '24', 'Racoon City', 'T-Virus', ‘f'),
('8', 'Clara', '20', 'Campo da Lagoa',‘cotovelose',‘f');
30
SELECT
CREATE TABLE pacientes (
pac_rg
int(10) unsigned NOT NULL auto_increment,
nome
varchar(45) NOT NULL default ‘’,
idade
int(10) unsigned default ‘0’,
cidade
varchar(45) default ‘’,
doenca
varchar(45) NOT NULL default ‘’,
sexo
enum(‘M’,’F’),
PRIMARY KEY (pac_rg,nome)
);
SELECT * FROM pacientes;
SELECT pac_rg, nome, idade FROM pacientes;
31
OBS: Relacionamentos
• Participação total
– FK precisa ser NOT NULL
• Se participação parcial
– FK pode ser NULL
• Os tipos importam no relacinamento
– FK deve ter mesmo tipo e restrições (exceto auto_increment) do
campo correspondente na tabela que cedeu o “impréstimo”.
• A ordem de criação importa:
– Só é possível referenciar tabelas anteriores.
• A ordem de destruição importa:
– Primeiro destruímos as tabelas com dependência.
– Dica: use a ordem inversa da criação das tabelas.
32
OBS: Erros do MySQL
• As mensagens de erro não são claras como em Pascal.
• Erros comuns:
– Erro de sintaxe “SINTAX ERROR...”
• Algo errado na SQL.
– Erro de criação de tabela “CAN’T CREATE...”
• Diferença de tipo entre PK e FK na outra tabela.
– Campo não existe “FIELD DOESN’T EXIST...”
• Campo não definido na SQL da tabela.
• Query Browser:
– Clique 2 vezes sobre o erro para ir à tabela errada.
33
Exercício
Crie e destrua todas as tabelas
• Ambulatorio(numero, andar, capacidade)
• Medico(cod, crm, nome, idade, data_nasc, especialidade, amb_numero)
• Paciente(cod, nome, idade, cidade)
• Consulta(med_cod pac_cod, data, hora, diagnostico)
• Funcionario(cod, nome, idade, cidade, salário, amb_numero)
OBS: a ordem de criação e destruição é importante.
34
• PARTE II
35
TABLE
CREATE DATABASE [IF NOT EXISTS] nome;
DROP DATABASE [IF EXISTS] nome;
CREATE TABLE [IF NOT EXISTS] tabela (
coluna características,
coluna características…);
DROP TABLE [IF EXISTS] tabela;
RENAME TABLE nome TO novo_nome;
36
PK e FK
• PRIMARY KEY(coluna1, coluna2);
• FOREIGN KEY (coluna1, coluna2)
REFERENCES <tabela>(coluna1,coluna2);
ON UPDATE ação
ON DELETE ação,
• Ação
– SET NULL: coloca NULL na FK
– SET DEFAULT: coloca o valor DEFAULT na FK
– CASCADE: passa a frente a operação
– NO ACTION: não altera valor da FK
– RESTRICT: não permite exclusão da PK
37
ALTER TABLE
ALTER TABLE tabela
ADD coluna restrições [FIRST | AFTER coluna]
MODIFY coluna restrições [FIRST | AFTER coluna]
CHANGE coluna novo_nome restrições
[FIRST | AFTER coluna]
RENAME TO novo_nome_tabela
// modifica nome da tabela
DROP coluna
38
ALTER TABLE
ALTER TABLE hospital.paciente
ADD COLUMN telefone VARCHAR(10)
AFTER doenca;
• Adicionando campo telefone
• O campo vai depois do campo doenca.
• Caso não tenhamos usado o comando “use hospital;”, será
preciso indicar a qual base de dados a tabela paciente
pertence, como demonstrado neste exemplo.
39
ALTER TABLE
ALTER TABLE paciente
DROP PRIMARY KEY,
ADD PRIMARY KEY(pac_rg, nome);
• Redefine a chave para os campos pac_rg e nome.
• A SQL primeiro remove (DROP PRIMARY KEY) as chaves e
depois define as novas (ADD PRIMARY KEY).
40
ALTER TABLE
ALTER TABLE paciente
ADD COLUMN cpf VARCHAR(11)AFTER telefone,
DROP PRIMARY KEY,
ADD PRIMARY KEY(pac_rg, nome, cpf);
• Adicionando campo CPF que passa a ser parte da chave primária
da tabela.
• A chave primária é redefinida neste exemplo de SQL.
41
ALTER TABLE
ALTER TABLE hospital.paciente
MODIFY COLUMN idade INTEGER UNSIGNED,
MODIFY COLUMN cidade VARCHAR(45);
• Modificando atributos dos campos ‘idade’ e
‘cidade’.
42
ALTER TABLE
ALTER TABLE cliente
DROP PRIMARY KEY,
DROP FOREIGN KEY (cep, rua, num),
ADD PRIMARY KEY (cpf),
ADD FOREIGN KEY (end_id)
REFERENCES end(id);
ALTER TABLE compra_produto # muda nome da tabela
RENAME TO item_compra;
ALTER TABLE compra_produto # muda nome da coluna
CHANGE codigo cod int unsigned;
43
• PARTE III
44
INSERT INTO
• Inserindo dados de outras tabelas com SELECT
INSERT INTO paciente(nome, idade, cidade, doenca)
SELECT nome, idade, cidade, 'resfriado'
FROM funcionario
WHERE idade > 25;
•
Inserindo na tabela paciente todos os funcionários
com mais de 25 anos.
45
DELETE FROM
• Removendo todos os pacientes.
DELETE FROM pacientes;
• Removendo todos os pacientes com resfriado.
DELETE FROM pacientes
WHERE doenca = ‘resfriado’;
• Removendo todos as consultas após 2005.
DELETE FROM pacientes
WHERE data >= ‘2005-01-01’;
DELETE FROM pacientes
WHERE YEAR(data) >= 2005;
46
DELETE FROM
• Removendo todos as consultas realizadas no intervalo do horário
comercial.
DELETE FROM pacientes
WHERE(hora>=’08:00’ AND hora=<’12:00’) OR
(hora>=’13:00’ AND hora=<’18:00’);
• Removendo todos as consultas realizadas fora do intervalo do
horário comercial.
DELETE FROM pacientes
WHERE NOT ((hora>=’08:00’ AND hora=<’12:00’) OR
(hora>=’13:00’ AND hora=<’18:00’));
47
DELETE FROM
• Removendo todos os pacientes com resfriado de Campo Mourão.
DELETE FROM pacientes
WHERE doenca = ‘resfriado’ AND
cidade = ‘campo mourao’;
• Nas buscas do MySQL não há distinção entre maiúsculas e minúsculas,
assim como ele também desconsidera os acentos.
• Em outros bancos:
DELETE FROM pacientes
WHERE UPPER(doenca) = ‘RESFRIADO’ AND
UPPER(cidade) = ‘CAMPO MOURÃO’;
• Pode-se usar os operadores OR, AND e NOT.
48
UPDATE
UPDATE tabela
SET coluna = valor/expressão
WHERE condição;
UPDATE tabela
SET coluna = CASE
WHEN condição THEN valor/expressão
WHEN condição THEN valor/expressão
WHEN condição THEN valor/expressão
ELSE valor/expressão
END;
49
UPDATE
UPDATE paciente
SET idade = idade * 12;
• Idade passa a ser armazenada em meses.
UPDATE funcionario
SET salario = salario * 1,5
WHERE funcao = ‘telefonista’;
• Aumentando em 50% o salário das telefonistas.
50
UPDATE
• UPDATE paciente
SET plano = ‘infantil’
WHERE year(nasc)>= 1998;
#(2008-10)=1998
• Define plano ‘infantil’ para pacientes de até 10 anos
• UPDATE paciente
SET plano = ‘infantil’
WHERE year(now()) – year(nasc) <= 10;
51
Funções de data/hora 1/2
• YEAR(data)
• DAY(data)
Retorna o ano
Retorna o mês (1-12)
Retorna o dia (1-31)
• HOUR(hora)
• MINUTE(hora)
• SECOND(hora)
Retorna hora (0-23)
Retorna minuto (0-59)
Retorna segundos(0-59)
• MONTH(data)
• OBS:Para testar estas funções use:
SELECT YEAR(‘2008-05-06);
52
Funções de data/hora 2/2
• DAYOFMONTH(data)
Mesmo que DAY(data)
• DAYOFWEEK(data)
Retorna: 1-7 (1=Domingo)
Retorna: 1-366
• DAYOFYEARY(data)
• NOW()
Retorna data + hora atual:
Ex: '2007-12-15 23:50:26'
• OBS:Para testar estas funções use:
SELECT YEAR(‘2008-05-06);
53
UPDATE
UPDATE locacao
SET atraso = TRUE,
debito = (diasLocacao–2) * 3 #R$3,00
WHERE diasLocacao > 2;
• Calculando valor (aproximado) para os clientes que não
devolveram um filme no prazo.
54
UPDATE
• Em duas partes:
UPDATE locacao
SET devendo = TRUE
WHERE diasLocacao > 2;
UPDATE locacao
SET debito = (diasLocacao–2) * 3
WHERE diasLocacao > 2;
55
UPDATE CASE
• Com condicional:
UPDATE funcionarios
SET salario = CASE
WHEN salario <= 1000
THEN salario * 1.20
ELSE salario * 1.10
END;
56
Exemplo
• Vários casos:
UPDATE funcionarios
SET salario = CASE
WHEN salario = 1000 THEN salario * 1.20
WHEN salario = 1500 THEN salario * 1.15
ELSE salario * 1.05
END;
57
58
MySQL
 Instalação
• Instalando MySQL
 Execução
• Auto-Executando como Windows Service
• Executando pelo console
 Utilização
• Usando MySQL shell
• Usando Query Browser
59
 Instalação
• Download
– Site: www.mysql.com
– MySQL Server 5
• Contêm MySQL Console
– MySQL GUI Tools (opcional)
• Documentação (BR e EN)
60
 Instalação
• Modo de instalação
– Instalar como Windows Service
• Carrega automático quando Windows abre
– Deixar para chamar manualmente
• Chamar por console ou Windows Explorer
61
 Execução (console)
• C:\Arquivots de Programas\
MySQL\MySQL Server 5.0\bin\mysqld-nt.exe
62
 Utilização
• Usando MySQL Shell
– Abrir outro console
mysql db_name ou
mysql --user=user_name --password=your_password db_name
63
 Utilização
• Usando Query Browser
64
 Utilização
• Usamos o Query Browser para
– Criar Base
– Criar Tabelas
•
•
•
•
Atributos
Chaves primárias (Primary Keys - PK)
Restrições (constraints)
Chaves estrangeiras (Foreign Keys - FK)
• Povoar banco
• Realizar consultas
65
 MySQL Query Browser
66