mysql

PHP + MySQL + jQuery,Tutoriais

Trabalhando com o banco de dados MySQL (Avançado)

20 mar , 2014  

Vamos dar continuidade aos tutoriais de MySQL, agora falando um pouco de relacionamentos entre tabelas, entidades, atributos, chaves primárias, estrangeiras.

Segue os links dos tutoriais relacionados ao MySQL:

1 – MySQL – Básico (Criação e utilização no PHP)
2 – MySQL – Intermediário (INNER JOIN, LEFT JOIN, RIGHT JOIN, GROUP BY, HAVING, UNION e Aliases)
3 – MySQL – Avançado (Relacionamentos de tabelas)
4 – MySQL – Backup (Realizar backup do banco de dados)
5 – MySQL – Segurança (Métodos de manter a segurança de informação)

O modelo relacional é a relação entre tabelas, uma relação é constituída de um ou mais atributos (campos) que tenha um relacionamento, como ‘departamento’ na tabela ‘funcionário’ tem relação com a ‘id’ na tabela ‘departamento’.

Uma entidade (tabela), representa um conjunto de atributos que armazenam informações no banco de dados, composto de campos e registros.

Um atributo serve também para associar informações entre entidades, representando de forma textual.

As chaves em uma entidade, garante que cada registro possa ser identificado. São usadas para impor integridade no desenvolvimento de um banco de dados e também para desenvolver relacionamentos entre entidades.

A chave primária deve ser um único registro da entidade no banco de dados, como uma ‘ID’. Impõem a integridade à tabela e ajuda a estabelecer o relacionamento entre as entidades.

A chave estrangeira estabelece o relacionamento entre duas entidades. Como o exemplo acima, entre departamento e funcionário.

A cardinalidade, refere-se as ocorrências de registro de uma entidade relacionada a outra. Vamos a alguns exemplos:

Aqui temos um relacionamento entre funcionário e departamento, onde um funcionário tem relacionado apenas a um departamento.

7

Já nesse exemplo, temos um cliente relacionado a vários pedidos.

8

Bom, vamos agora para a parte prática do nosso tutorial, onde vamos aplicar esse conhecimento em um banco de dados MySQL relacional.

Vamos criar duas tabelas ‘Cliente’ e ‘Cidade’, o relacionamento entre elas será de um cliente para uma cidade.

CREATE TABLE `cliente` (
  `id_cliente` int(10) NOT NULL,
  `nome_cliente` varchar(255) default NULL,
  `cidade_cliente` int(10) default NULL,
  PRIMARY KEY  (`id_cliente`),
  KEY `cidade_cliente` (`cidade_cliente`),
  CONSTRAINT `fk_cidade_cliente` FOREIGN KEY (`cidade_cliente`) REFERENCES `cidade` (`id_cidade`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `cidade` (
  `id_cidade` int(10) NOT NULL,
  `nome_cidade` varchar(255) default NULL
  PRIMARY KEY  (`id_cidade`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Não tem segredo nenhum em criar o relacionamento entre as tabelas, basicamente a criação é a mesma, a unica parte que acrescentamos é uma chave estrangeira na tabela ‘Cliente’:

KEY `cidade_cliente` (`cidade_cliente`),

E referenciamos a chave estrangeira a chave primária da tabela ‘Cidade’:

CONSTRAINT `fk_cidade_cliente` FOREIGN KEY (`cidade_cliente`) REFERENCES `cidade` (`id_cidade`) ON DELETE NO ACTION ON UPDATE NO ACTION

E ainda acrescentamos a operação ‘ON DELETE NO ACTION’ e ‘ON UPDATE NO ACTION’.

A ‘ON DELETE NO ACTION’, exclui somente o registro pai sem afetar os registros filhos ou relacionados em outras entidades. Há também como usar ‘ON DELETE CASCADE’, chamado cascateamento, que exclui o registro pai e também os relacionados em outras tabelas.

Ainda há o ‘ON DELETE RESTRICT’ que é muito parecido com o ‘ON DELETE NO ACTION’, mas há uma diferença entre os dois, ‘NO ACTION’ a verificação de integridade referencial é feita depois de tentar executar a instrução UPDATE e DELETE, já a ‘RESTRICT’ verifica antes de executar as instruções. No nosso caso vamos utilizar o ‘NO ACTION’, pois é padrão no MySQL.

Como um assunto adicional, vamos falar um pouco sobre Triggers, Views, Stored Procedures e DELIMITER.

Triggers

É um objeto de banco de dados que esta relacionado a uma tabela, definido para ser executado quando um evento (INSERT, REPLACE, DELETE ou UPDATE) ocorre na tabela.

CREATE TRIGGER
  trigger_nome trigger_tempo trigger_evento
  ON tabela_nome
  FOR EACH ROW
BEGIN
  trigger_comandos
END;

Onde:

trigger_nome é o nome da trigger;
trigger_tempo é a o tempo de ação da trigger, podemos usar BEFORE (antes) ou AFTER (depois) para indicar que o trigger será ativado antes ou depois.
trigger_comandos serão todos os querys SQL executados pelo trigger

Inserindo uma nova linha em uma tabela, temos os valores das colunas através do operador NEW.nome_coluna.

Excluíndo uma linha, temos os valores excluídos através do operador OLD.nome_coluna.

Os dois operadores (NEW e OLD) estão disponíveis no UPDATE e no REPLACE, pois as duas declarações temos um DELETE seguido por um INSERT.

Podemos acessar os valores que serão enviados a uma tabela antes (BEFORE) ou depois (AFTER) de um UPDATE.

Com os operadores, podemos examinar os valores inseridos, atualizados ou excluídos da tabela.

Vamos criar uma trigger onde limpe o total de salários de um determinado departamento se algum funcionário daquele departamento sair da empresa.

CREATE TRIGGER total_salario
AFTER DELETE ON empregado
FOR EACH ROW
UPDATE departamento
SET total_salario=total_salario-old.salario
WHERE dpto=old.dpto;

Essa trigger será executada quanto a função DELETE for chamada:

DELETE FROM empregado WHERE cpf='01234567890';

Além desse exemplo, você pode usar para inúmeras possibilidades, como amostra de uma mensagem caso ocorra um erro.

Views

Views nada mais é criar tabelas virtuais, ou seja, que não ocupa um espaço físico no seu servidor, seria o chamado CLOUD, como todo mundo anda falando.

É um caminho alternativo para consultar, editar ou excluir dados de tabelas no seu banco de dados.

Você pode usar INNER JOINS e demais recursos do MySQL, mas no nosso caso, vamos utilizar bem simples, com fins didáticos.

Segue sintaxe:

CREATE VIEW 
(nome_das_colunas) AS
SELECT <nome_das_colunas>
FROM <nome_da_tabela>
WHERE <condição>

Pense que você precisa liberar acesso a certos campos de suas tabelas a algum usuário, então você cria uma view, assim não libera o acesso ao banco de dados inteiro, restringindo o seu acesso.

Vamos a um exemplo prático, você precisa criar um view que mostre o código, descrição e unidade de medida de algum produto, cuja unidade de medida seja em metro.

CREATE VIEW produto_metro
(id_produto, descricao_produto, unidade_produto) AS
SELECT id_produto, descricao_produto, unidade_produto
FROM produto
WHERE unidade_produto = 'm';

Para você listar, utilizando views:

SELECT id_produto, descricao_produto, undiade_produto
FROM produto_metro
WHERE unidade_produto = 'm';

Inserindo dados, utilizando views:

INSERT INTO produto_metro
VALUES (2, 'teste','m');

Alterando dados, utilizando views:

UPDATE produto_metro
SET descricao_produto='teste'
WHERE id_produto = 2;

Apagando dados, utilizando views:

DELETE FROM produto_metro
WHERE id_produto = 2;

E para apagar a sua view criada:

DROP VIEW produto_metro

Stored Procedures

É um conjunto de comando SQL que podem ser armazenados no servidor. Assim o cliente não reenvia o comando novamente, mas faz referência aos procedimentos armazenados.

Melhorando assim o desempenho do servidor, pois o cliente não envia novamente uma instrução para o servidor. No entanto, aumenta a carga no servidor de banco de dados, já que a maior parte é feita no servidor e não na aplicação.

É utilizado quando você utiliza várias aplicações com linguagens diferentes, mas precisa realizar as mesmas operações no banco de dados.

Fornece um ambiente consistente e seguro, onde cada operação é registrada de forma apropriada. Nessa configuração, a aplicação e o usuário não tem acesso diretamente as tabelas.

Vamos a sintaxe de criação:

CREATE PROCEDURE <nome_do_procedimento>
(<parâmetros>)
<declaração_locais>
<corpo_do_procedimento>;

E para chamar o procedimento:

CALL <nome_do_procedimento>(<lista_de_argumentos>);

Vamos a um exemplo prático, usando um DELIMITER para iniciar e encerrar um procedimento:

DELIMITER $$
CREATE PROCEDURE inserir_cliente(id_cliente int, nome_cliente char, salario_cliente decimal)
BEGIN
REPLACE INTO cliente VALUES (id_cliente, nome_cliente, salario_cliente);
END $$

Para chamar, enviando os dados:

CALL inserir_cliente(2, 'Rodrigo', '500.00');

Nesse caso o BEGIN inicia o procedimento, o REPLACE, insere ou altera um registro no lugar do UPDATE e INSERT.

Vamos ao procedimento de listagem:

DELIMITER $$
CREATE PROCEDURE listar_cliente(IN id int)
BEGIN
   if(id is null)then
        SELECT id_cliente, nome_cliente FROM cliente;
   else
        SELECT id_cliente, nome_cliente FROM cliente WHERE id_cliente = id;
   end if;
END $$
DELIMITER ;

CALL listar_cliente(null);
CALL listar_cliente(2);

Para excluir item pelo procedimento:

DELIMITER $$
CREATE PROCEDURE excluir_cliente(IN id int)
BEGIN
   DELETE FROM cliente WHERE id_cliente = id;
END $$

CALL excluir_cliente(2);

Buscar dados pelo procedimento:

DELIMITER $$
CREATE PROCEDURE buscar_cliente(IN busca char(50))
BEGIN
   PREPARE teste FROM "SELECT nome_cliente, salario_cliente FROM cliente WHERE nome_cliente like ?";
   SET @strBusca = busca;
   EXECUTE teste USING @strBusca;
END $$

CALL buscar_cliente("%rodrigo%");

Você pode retornar características do procedure, como nome, tipo, quem criou, datas de modificação e criação.

SHOW PROCEDURE STATUS

Ou de alguma procedure especifica:

SHOW PROCEDURE STATUS like 'teste%'

Para alterar um procedure:

DELIMITER $$
ALTER PROCEDURE listar_cliente(IN nome char(50))
BEGIN
   if(nome is null)then
        SELECT id_cliente, nome_cliente FROM cliente;
   else
        SELECT id_cliente, nome_cliente FROM cliente WHERE nome_cliente = nome;
   end if;
END $$

Para excluir um procedure:

DROP PROCEDURE buscar_cliente;

Delimiter

Ao criar um novo objeto de banco de dados, o MySQL tem como delimitador o ; (ponto e virgula), ou seja, o MySQL compreende que ali encerra o objeto. Mas sem o END no final, compreende que ainda não finalizou, ocorrendo falha no seu objeto.

Segue aqui um exemplo de objeto trigger, com o delimitador ponto e virgula:

CREATE TRIGGER
  trigger_nome trigger_tempo trigger_evento
  ON tabela_nome
  FOR EACH ROW
BEGIN
  trigger_comandos
END;

Podemos utilizar o DELIMITER, onde você indica o inicio e fim do seu objeto, assim executando corretamente. Junto ao DELIMITER deve-se acompanhar com um símbolo, vamos utilizar dois cifrões:

DELIMITER $$
CREATE TRIGGER
  trigger_nome trigger_tempo trigger_evento
  ON tabela_nome
  FOR EACH ROW
BEGIN
  trigger_comandos
END;
$$

Espero que esse tutorial tenha sido importante para o seu aprendizado. Continue postando seus comentários.

Abraço.

, , , , , , , , ,


Comments are closed.