leandro lago da silva banco de dados i técnico integrado em informática 2010 mysql e esquemas...

Leandro Lago da SilvaBanco de Dados I

Técnico Integrado em Informática

2010

MySQL e Esquemas

Usando MySQL Query

BrowserMySQL Console

2

• PARTE I

3

• TIPOS DE DADOS

4

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

5

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.

6

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”

7

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

8

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’);

9

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)

10

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.

11

• MYSQL CONSOLE

12

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).

13

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”)

14

• MySQL Query Browser

15

MS Query Browser• Instalado no pacote “Gui Tools”• Executa: 1 comando SQL (Resultset)

N comandos SQL (Script)

16

DDL (Data Definition Language)

Definindo esquemas (estrutura)

17

CREATE/DROP DATABASE#cria base de dadosCREATE 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 dadosDROP 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\

18

CREATE TABLECREATE TABLE paciente ( pac_rg int(10), #inteiro de 10 dígitos nome varchar(45), #string de 45 caracteres idade int(10), cidade varchar(45), doenca varchar(45), PRIMARY KEY (pac_rg) #chave primária (PK));

• No MySQL, cada Tabela é um arquivo:C:\Arquivos de Programas\MySQL\MySQL Server\data\hospital\...

19

CREATE/DROP TABLE#renomeia tabelaRENAME TABLE paciente TO paciente2006;

#cria tabela com cautelaCREATE TABLE IF NOT EXISTS hospital;

#cria tabela com cautelaDROP 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.

20

PRIMARY KEY (PK)CREATE TABLE telefone (

ddd varchar(2), numero varchar(8), tipo int,

PRIMARY KEY (ddd, numero) # PK com N campos);

CREATE TABLE paciente (id int PRIMARY KEY # PK com 1 campoddd varchar(2),

numero varchar(8), tipo int);

21

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

22

FOREIGN KEY (FK)CREATE TABLE transacao ( # tabela 1:1:1

cliente_cod VARCHAR (20), # PK FKconta_cod VARCHAR (10), # PK FKfilial_cod VARCHAR (5), # FKdata 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));

23

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 únicaFOREIGN KEY (tel_ddd, tel_num)

REFERENCES telefone (ddd, num));

24

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

25

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 únicaFOREIGN KEY (tel_ddd, tel_num)

REFERENCES telefone (ddd, num))Type=InnoDB;

ou...

Engine=InnoDB;

26

ALTER TABLEALTER TABLE tabela ADD [ COLUMN ] coluna tipo [ restrição_de_coluna [ ... ] ]

ALTER TABLE clienteADD cliente_cod int unsigned not null, ADD tel_ddd int(3) zerofill,ADD tel_num int(9) zerofill

ALTER TABLE tabelaALTER [ 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 clienteRENAME obs TO observacao

27

ExercícioCrie 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.

28

DML (Data Manipulation Language)

Inserindo dados (povoamento)

29

INSERT INTOCREATE 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);

30

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');

31

SELECTCREATE 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;

32

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.

33

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.

34

ExercícioCrie 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.

35

• PARTE II

36

TABLECREATE 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;

37

PK e FK• PRIMARY KEY(coluna1, coluna2);

• FOREIGN KEY (coluna1, coluna2) REFERENCES <tabela>(coluna1,coluna2);ON UPDATE açãoON 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

38

ALTER TABLEALTER 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

39

ALTER TABLEALTER 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.

40

ALTER TABLEALTER TABLE pacienteDROP 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).

41

ALTER TABLEALTER 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.

42

ALTER TABLEALTER TABLE hospital.pacienteMODIFY COLUMN idade INTEGER UNSIGNED,MODIFY COLUMN cidade VARCHAR(45);

• Modificando atributos dos campos ‘idade’ e ‘cidade’.

43

ALTER TABLEALTER 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 tabelaRENAME TO item_compra;

ALTER TABLE compra_produto # muda nome da colunaCHANGE codigo cod int unsigned;

44

• PARTE III

45

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.

46

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;

47

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’));

48

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.

49

UPDATEUPDATE tabela SET coluna = valor/expressãoWHERE 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;

50

UPDATEUPDATE 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.

51

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;

52

Funções de data/hora 1/2

• YEAR(data) Retorna o ano• MONTH(data) Retorna o mês (1-12)• DAY(data) Retorna o dia (1-31)

• HOUR(hora) Retorna hora (0-23)• MINUTE(hora) Retorna minuto (0-59)• SECOND(hora) Retorna segundos(0-59)

• OBS:Para testar estas funções use:SELECT YEAR(‘2008-05-06);

53

Funções de data/hora 2/2

• DAYOFMONTH(data) Mesmo que DAY(data)• DAYOFWEEK(data) Retorna: 1-7 (1=Domingo)• DAYOFYEARY(data) Retorna: 1-366

• NOW() Retorna data + hora atual: Ex: '2007-12-15 23:50:26'

• OBS:Para testar estas funções use:SELECT YEAR(‘2008-05-06);

54

UPDATEUPDATE 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.

55

UPDATE• Em duas partes:

UPDATE locacao SET devendo = TRUE WHERE diasLocacao > 2;

UPDATE locacao SET debito = (diasLocacao–2) * 3 WHERE diasLocacao > 2;

56

UPDATE CASE• Com condicional:

UPDATE funcionariosSET salario = CASEWHEN salario <= 1000

THEN salario * 1.20ELSE salario * 1.10

END;

57

Exemplo• Vários casos:

UPDATE funcionariosSET salario = CASE

WHEN salario = 1000 THEN salario * 1.20 WHEN salario = 1500 THEN salario * 1.15 ELSE salario * 1.05END;

58

59

MySQL Instalação• Instalando MySQL

Execução• Auto-Executando como Windows Service• Executando pelo console

Utilização• Usando MySQL shell• Usando Query Browser

60

Instalação• Download– Site: www.mysql.com– MySQL Server 5• Contêm MySQL Console

– MySQL GUI Tools (opcional)

• Documentação (BR e EN)

61

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

62

Execução (console)• C:\Arquivots de Programas\

MySQL\MySQL Server 5.0\bin\mysqld-nt.exe

63

Utilização• Usando MySQL Shell– Abrir outro consolemysql db_name oumysql --user=user_name --password=your_password db_name

64

Utilização

• Usando Query Browser

65

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

66

MySQL Query Browser