Baixe o app para aproveitar ainda mais
Prévia do material em texto
BANCO DE DADOS E-book 4 Gratuliano Lucena Neste E-Book: INTRODUÇÃO ����������������������������������������������4 COMANDOS DE SQL PARA OPERAÇÕES AVANÇADAS DE BANCOS DE DADOS ���������������������������������� 5 COMANDOS SQL GERAIS ������������������������ 6 OPERADORES ARITMÉTICOS ����������������� 9 OPERADORES RELACIONAIS ����������������10 TABELAS PARA USO DOS EXEMPLOS A SEGUIR ��������������������������������11 CLASSIFICAÇÃO DE DADOS ������������������15 OPERADORES AUXILIARES �������������������18 VERIFICAÇÃO DE CARACTERES �����������21 FUNÇÕES AGREGADAS �������������������������� 23 RELACIONAMENTO DE TABELAS ������� 25 INFORMAÇÕES AGRUPADAS ���������������26 QUALIFICADORES ������������������������������������27 SUBQUERY �������������������������������������������������30 JUNÇÃO DE TABELAS ����������������������������� 32 2 ALIAS PARA TABELAS ����������������������������34 FUNÇÕES STRING ������������������������������������� 35 FUNÇÕES DATA ����������������������������������������36 TRIGGERS NO MYSQL �����������������������������37 “STORED PROCEDURE” ������������������������45 CONSIDERAÇÕES FINAIS ����������������������50 SÍNTESE ��������������������������������������������������������51 3 INTRODUÇÃO Várias abordagens de comandos de SQL são expli- cadas neste módulo: como proceder inclusão, alte- ração, exclusão e consulta de dados, de forma mais avançada. Serão explicadas técnicas para fazer junção de ta- bela – item importante para que tenhamos toda a forma de composição de informações para atender as necessidades de regras de negócio de usuários. Complementando a junção de tabelas, também se- rão abordadas explicações sobre automações de funções – operações lógicas que, ao invés de exe- cutá-las nos aplicativos, são executadas com uma programação própria em banco de dados. Uma dessas automações detalha de forma prá- tica com exemplos testados na ferramenta IDE Workbench MySQL, chamada de “Trigger”. A outra for- ma de automação é chamada de “Stored Procedure”, que é também uma operação lógica, programada e executada com linguagem de Banco de Dados DML. Vamos lá? 4 COMANDOS DE SQL PARA OPERAÇÕES AVANÇADAS DE BANCOS DE DADOS Novamente realizaremos o processo de aprendizado em uma ferramenta IDE Workbench MySQL para fixar os conhecimentos na prática. Conforme estudamos anteriormente, o aprendiza- do de bancos se apoia na prática na manutenção de “Schema”, tabelas, linhas e colunas, construção de índices. Bem como na execução de comandos DDL (Data Definition Language) de modo prático, a manipulação de dados com comandos do SQL, por exemplo, a Inclusão de dados (Insert), a alteração de dados (Update), exclusão de dados (Delete) e con- sulta de Dados (Select). 5 COMANDOS SQL GERAIS Alguns comandos de SQL ajudam e facilitam na ope- ração da ferramenta IDE Workbench MySQL. São instruções que geram consultas interativas para sa- ber sobre o sistema gerenciador de banco de dados (SGBD). Essas instruções são executadas por meio de query (consultas), na ferramenta IDE. Uma dessas instru- ções é verificar o diretório de tabelas, conforme mos- trado na figura 1. Comando: show tables Figura 1: Consultas das tabelas existentes por Schema no SGBD. Fonte: Elaboração própria. Se quisermos saber informações sobre: quais são as colunas de uma tabela, qual é chave primária, qual 6 o tipo de dados de cada coluna, tamanho de cada coluna, qual é a coluna tem geração automática de conteúdo (auto_increment – que não aceita informa- ções vinda do usuário), utilizamos o comando abaixo e o resultado é apresentado conforme figura 2. Comando: show columns from aluno Figura 2: Informações sobre as colunas de uma tabela. Fonte: Elaboração própria. Se quisermos saber informações sobre a versão utili- zada do IDE Workbench MySQL, utilizamos o coman- do abaixo. O resultado é mostrado conforme figura 3: Comando: Select version() Figura 3: Informações sobre versão do IDE Workbench MySQL. Fonte: Elaboração própria. Se quisermos saber informações sobre a data do sistema gerenciador de banco de dados, utilizamos o comando abaixo, conforme figura 4. 7 Select current_date Figura 4: Informações sobre a data do sistema SGBD. Fonte: Elaboração própria. Se quisermos saber informações sobre o usuário do banco de dados, utilizamos o comando abaixo (figura 5). Comando: Select user() Figura 5: Informações sobre o usuário do sistema SGBD. Fonte: Elaboração própria. 8 OPERADORES ARITMÉTICOS Podemos executar: + Adição - Subtração * Multiplicação / Divisão Tabela 1: Operadores Matemáticos utilizados em comandos SQL. 9 OPERADORES RELACIONAIS Os operadores lógicos são aplicados para compor condições de decisões para realizar uma consulta ou em situação de atualizações de dados – são as- sociados à cláusula “Where”. Essas condições envolvem comparações entre o conteúdo de uma coluna com valor fixo ou com uma variável do aplicativo, ou ainda com o conteúdo de outra coluna. Utilizamos os símbolos da tabela 2, para fazer com- parações. Exemplo Select * From Tabela where sa- lario > 1000. > Maior que < Menor que = Igual a <> Diferente de >= Maior ou igual a <= Menor ou igual a Tabela 2: Operadores lógicos para operação em comandos SQL. 10 TABELAS PARA USO DOS EXEMPLOS A SEGUIR Os “scripts” abaixo servem para criar tabelas na ferramenta IDE Workbench MySQL para explicar os exemplos mostrados ao longo deste documento. Servem para explicar o funcionamento e mostrar os resultados dos comandos de SQL, citados em cada situação a seguir. CREATE TABLE teste ( codigo int(11) NOT NULL AUTO_INCREMENT, nome varchar(15) NOT NULL, email varchar(30) DEFAULT NULL, telefone varchar(8) DEFAULT NULL, PRIMARY KEY (codigo)); CREATE TABLE funcionario ( Codigo Int (10) not null primary key, Nome Char(40) not null, Setor Char(2) not null, Cargo Char(20) not null, Salario Decimal(10,2) not null); CREATE TABLE aluno ( 11 ra INT NOT NULL AUTO_INCREMENT PRIMARY KEY, nome CHAR(30) NOT NULL) nascimento DATE NOT NULL); Create table clientes ( codigo int(10) primary key, nome char(40) not null, sobrenome char(40) not null, idade int(10) not null, endereco char(50) not null, cidade char(20) not null, estado char(2) not null, cep int(9) not null); Create table conta ( numero int(10) primary key, valor decimal(10,2) not null, vencimento date not null, codcli int(3) not null); Create table pedido ( nr int primary key, cliente int, valor decimal(15,2)); 12 Create table funcionarios( codigo_funcionario int, nome varchar(50) ); Create table pagamentos( codigo_pagto int, codigo_funcionario int, valor decimal(10,2) ); Create table descontos( codigo_desconto int, codigo_funcionario int, valor decimal(10,2) ); Create table tbl_autores ( id_autor int, codigo_Livro int, descricao_Livro char (40), autor_Livro char (40), editora_livro char (40), ano_livro int, Preco_Livro decimal(10,2) ); CREATE TABLE contato ( codigo INT NOT NULL, 13 nome VARCHAR(45) NULL, idade int null, setor INT NULL, cargo VARCHAR(45) NULL, salario DECIMAL(15,2) NULL, admissao DATE NULL, PRIMARY KEY (codigo)); 14 CLASSIFICAÇÃO DE DADOS É comum surgir a necessidade de fazermos uma con- sulta dos dados de uma tabela ordenada por ordem crescente ou decrescente. Para isso, utilizamos uma cláusula chamada de “ORDER BY”, que associamos com o comando “Select”. Incluir na tabela teste os seguintes dados: INSERT INTO ‘teste2’.‘teste’ (‘codigo’, ‘nome’, ‘email’, ‘telefone’) VALUES (‘1’, ‘Elaine’, ‘a@a.com’, ‘33334444’); INSERT INTO ‘teste2’.‘teste’ (‘codigo’, ‘nome’, ‘email’, ‘telefone’) VALUES (‘2’, ‘Elaine’, ‘b@b.com’, ‘11112222’); INSERT INTO ‘teste2’.‘teste’ (‘codigo’, ‘nome’, ‘email’, ‘telefone’) VALUES (‘3’, ‘Elaine’, ‘b@b.com’, ‘22223333’); Após a inclusão, os dados da tabela ficam conforme demonstrado na figura 6. 15 Figura 6: Conteúdoda Tabela teste após a inclusão. Fonte: Elaboração própria. Figura 7: Consulta da Tabela teste classificada por telefone. Fonte: Elaboração própria. Vamos classificar todas as linhas da tabela aluno, com duas colunas em diferentes ordens: ordem de classificação, nascimento ordem descendente (DESC), e nome em ordem ascendente (ASC). 16 Antes vamos executar uma inclusão de dados para podemos, em seguida, executar o “select” com order by. INSERT INTO aluno (ra, nome, nascimento) VALUES (1, “JOAO”, “2019/09/27”); INSERT INTO aluno (ra, nome, nascimento) VALUES (2, “ANTONIO”, “2019/08/27”); INSERT INTO aluno (ra, nome, nascimento) VALUES (3, “CARLOS”, “2019/08/26”); INSERT INTO aluno (ra, nome, nascimento) VALUES (4, “ALVARO”, “2019/08/26”); Vamos lá. Select * from aluno order by nascimento desc, nome asc; Figura 8: Consultada Tabela Aluno Classificada por nascimento e nome. Fonte: Elaboração própria. 17 OPERADORES AUXILIARES Vamos trabalhar com a cláusula “Between” para fa- zer a seleção de linhas, obedecendo a uma condição que atenda a um valor mínimo e valor máximo. A condição está associada com a cláusula “Where”. A sintaxe tem a seguinte estrutura: “<expressão> [Not] BETWEEN <mínimo> and <máximo>”. Antes, para podermos testar esta condição, faremos inclusão de dados na tabela contatos: INSERT INTO contato (codigo, nome, idade, setor, cargo, salario, admissao) VALUES (20, ‘Aline Brito’, 20 , 2, ‘Supervisora’, 1700, ‘2019-09-05’); INSERT INTO contato (codigo, nome, idade, setor, cargo, salario, admissao) VALUES (22, ‘Silvia Mendes’, 20, 2, ‘Gerente’, 2000, ‘2019-09-01’); INSERT INTO contato (codigo, nome, idade, setor, cargo, salario, admissao) VALUES (24, ‘Moacir Campos’, 30, 2, ‘Programador’, 2000, ‘2019-09-10’); INSERT INTO contato (codigo, nome, idade, setor, cargo, salario, admissao) 18 VALUES (26, ‘Marcos Silva’, 30, 2, ‘Programador’, 2000, ‘2019-09-15’); Após a inclusão dos dados na tabela contatos, os conteúdos dos dados ficam conforme figura 9: Figura 9: Consulta da Tabela Contato após inclusão. Fonte: Elaboração própria. Após a execução do comando “Select * from contato where idade between 10 and 25;”, foram selecionadas somente as linhas em que a idade com conteúdo 20 que atende as condições do between, conforme mostrado na figura 10. Figura 10: Consulta da Tabela Contato após between. Fonte: Elaboração própria. Também temos outras formas de seleção por meio de condição com conteúdos sequenciais. Para isso utilizamos a cláusula IN. Vamos executar o coman- do “Select * from contato where idade in (20,25);”, e podemos verificar que o resultado fica parecido com o apresentando na figura 10. 19 Outra situação que podemos utilizar é a de verificar a existência de colunas que tenham conteúdos de valores nulos ou não. Para testarmos essa situação, vamos executar a instrução “Select * from contato where nome is NOT null;”, o resultado é apresentado conforme a figura 9. Se formos executar o coman- do “Select * from contato where nome is null;”, não teremos nenhum resultado apresentado, pois a co- luna está definida como “NOT NULL”, ou seja, será exigido um conteúdo quando da inclusão dos dados. 20 VERIFICAÇÃO DE CARACTERES Para verificar a sequência de caracteres dentro de uma coluna do tipo string (char ou varchar), podemos utilizar a cláusula “where”, uma condição baseada no uso do operador LIKE, e a sintaxe tem a seguinte configuração de instrução: “<expressão> [NOT] LIKE <valor>”. Primeiro exemplo, vamos verificar quando a coluna nome da tabela contato começa com a letra “M”. Para isso, executamos o comando “Select * From contato Where nome LIKE ‘M%’;” e temos como re- sultado a consulta de dados conforme apresentado na figura 11. Figura 11: Consulta de nome começando pela “M” . Fonte: Elaboração própria. Outro exemplo. Vamos verificar quando a coluna nome da tabela contato tenha a segunda letra igual a “l”. Para isso, executamos o comando “Select * From contato Where nome LIKE ‘_l%’;” e temos como resultado a consulta de dados conforme apresentado na figura 12. 21 Figura 12: Consulta de nome com a segunda letra igual a “l”. Fonte: Elaboração própria. Mais um exemplo. Vamos verificar quando a coluna nome da tabela contato tenha a letra “o” em qualquer posição do nome. Para isso, executamos o comando “Select * From contato Where nome LIKE ‘%o%’;”, e temos como resultado a consulta de dados conforme apresentado na figura 13. Figura 13: Consulta de nome que tenha a letra “O” em qualquer posição. Fonte: Elaboração própria. 22 FUNÇÕES AGREGADAS Existem funções no SQL que contribuem para reali- zar operações matemáticas e estatísticas. A seguir apresentamos, na tabela 3, uma relação de funções e o que cada função pode resultar. Também para cada função é mostrado um exemplo de comando SQL. Função Resultado Exemplo de comando SQL AVG() Média aritmética SELECT AVG(Preco_Livro) FROM tbl_Livro; MAX() Maior valor SELECT MAX(Preco_Livro) FROM tbl_Livro; MIN() Menor valor SELECT MIN(Preco_Livro) FROM tbl_Livro; SUM() Soma dos valores SELECT SUM(Preco_Livro) FROM tbl_Livro; COUNT() Número de valores SELECT COUNT(*) FROM tbl_autores; ALL Contagem dos valores não vazios SELECT COUNT(ALL id_autor) FROM tbl_Livro; Distinct Contagem dos valores não vazios e únicos SELECT COUNT(DISTINCT id_autor) FROM tbl_Livro; Tabela 3: Funções Agregadas. Vamos pegar uma função para explicar melhor. Imagine uma situação em que queremos saber quais as cidades dos nossos clientes. Como pode- 23 mos ter vários clientes da mesma cidade, usamos o DISTINCT para não mostrar várias vezes o mesmo nome de cidade. SELECT DISTINCT nome, sobrenome, idade FROM clientes; Figura 14: Consulta da tabela clientes com a cláusula DISTINCT. Fonte: Elaboração própria. 24 RELACIONAMENTO DE TABELAS Se houver necessidade de relacionar colunas en- tre duas ou mais tabelas, vamos analisar que preci- samos apresentar, ao mesmo tempo, o número da conta da tabela conta e também nome do cliente da tabela cliente. Nesse caso, podemos utilizar o comando SQL: SELECT conta.numero, cliente.nome FROM cliente, conta WHERE cliente.codigo=conta.codcli; Esse comando pode ser entendido como juntar as tabelas Cliente e Conta. O objetivo é selecionar na comparação da cláusula “Where” através do códi- go do cliente ser igual ao codcli da tabela conta. Todas as linhas que atenderem a essas condições do “Where” serão apresentadas às colunas número da tabela conta e nome da tabela cliente. 25 INFORMAÇÕES AGRUPADAS Podem existir necessidades de agrupar as funções agregadas por alguma coluna para obter uma so- matória, média ou contagem, por uma determinada coluna de uma tabela. Para solucionar essa necessidade, devemos utilizar a cláusula WHERE com a cláusula GROUP BY. Podemos resolver, tomando como referência cinco exemplos de comando SQL, conforme mostrado na tabela 4. 1 SELECT codcli, COUNT(*) FROM conta GROUP BY codcli; 2 SELECT cliente.Nome, COUNT(*) FROM cliente, conta WHERE cliente.codigo = cobranca.codcli GROUP BY cliente.Nome; 3 SELECT codcli, avg(valor) FROM conta GROUP BY codcli having avg(valor)>2000; 4 Select codcli, sum (valor) From conta Group by codcli with ROLLUP; O modificador ROLLUP faz com que o mysql retorne também as linhas totalizadas, ou seja, o total por cliente e o total geral. 5 Select cliente.nome, conta.numero, sum (conta.valor)as total From conta, cliente where conta.codcli = cliente.codigo Group by cliente.nome, conta.numero with ROLLUP; Tabela 4: Tabela de exemplos de agrupamentos. 26 QUALIFICADORES Quando utilizamos as funções matemáticas ou estatísticas para uma apresentação fina, surge a necessidade de definirmos outro qualificador para às funções. Quando utilizamos, podemos substituir por um nome que julgamos ser o mais ideal. Para isso aplicamos uma cláusula “as” para definir o novonome. Executar as seguintes inclusões de conta: INSERT INTO ‘teste2’.‘conta’ (‘numero’, ‘valor’, ‘ven- cimento’, ‘codcli’) VALUES (‘1’, ‘10’, ‘2019-08-29’, ‘1’); INSERT INTO ‘teste2’.‘conta’ (‘numero’, ‘valor’, ‘ven- cimento’, ‘codcli’) VALUES (‘2’, ‘50’, ‘2019-08-29’, ‘1’); INSERT INTO ‘teste2’.‘conta’ (‘numero’, ‘valor’, ‘ven- cimento’, ‘codcli’) VALUES (‘3’, ‘40’, ‘2019-08-28’, ‘1’); INSERT INTO ‘teste2’.‘conta’ (‘numero’, ‘valor’, ‘ven- cimento’, ‘codcli’) VALUES (‘4’, ‘30’, ‘2019-08-28’, ‘2’); INSERT INTO ‘teste2’.‘conta’ (‘numero’, ‘valor’, ‘ven- cimento’, ‘codcli’) VALUES (‘5’, ‘20’, ‘2019-08-28’, ‘2’); INSERT INTO ‘teste2’.‘conta’ (‘numero’, ‘valor’, ‘ven- cimento’, ‘codcli’) VALUES (‘6’, ‘40’, ‘2019-08-28’, ‘3’); INSERT INTO ‘teste2’.‘conta’ (‘numero’, ‘valor’, ‘ven- cimento’, ‘codcli’) VALUES (‘7’, ‘95’, ‘2019-08-29’, ‘4’); 27 Figura 15: Consulta da tabela Clientes. Fonte: Elaboração própria. Figura 16: Consulta da tabela Conta. Fonte: Elaboração própria. Considerando os dados da tabela clientes, conforme ilustrado na figura 15, e mais os dados da tabela con- ta, conforme ilustrado na figura 16, vamos executar o comando SQL a seguir, a fim verificarmos como funciona a cláusula “AS”. SELECT clientes.Nome, COUNT(*) AS contas, sum(conta.valor) as valor FROM clientes, conta WHERE clientes.codigo = conta. codcli GROUP BY clientes.Nome; Após a execução do comando acima, verificamos, na figura 17, que o count(*) foi substituído pela palavra “contas” e que sum (conta.valor) foi substituído pela palavra “valor”. 28 Figura 17: Apresentação do resultado com a cláusula “AS” . Fonte: Elaboração própria. 29 SUBQUERY Uma subquery é um comando SELECT que faz uma seleção sobre outra seleção já feita por outro SELECT. A subquery deve ser colocada entre parênteses; deve ser colocada depois de um operador de comparação, e a cláusula ORDER BY não deve ser incluída em uma subquery. Executar as seguintes inclusões: INSERT INTO ‘teste2’.‘clientes’ (‘codigo’, ‘nome’, ‘so- brenome’, ‘idade’, ‘endereco’, ‘cidade’, ‘estado’, ‘cep’) VALUES (‘1’, ‘Jose’, ‘Lacerda’, ‘30’, ‘Rua um numero 5’, ‘São Paulo’, ‘SP’, ‘00000100’); INSERT INTO ‘teste2’.‘clientes’ (‘codigo’, ‘nome’, ‘so- brenome’, ‘idade’, ‘endereco’, ‘cidade’, ‘estado’, ‘cep’) VALUES (‘2’, ‘Maria’, ‘Antonia’, ‘25’, ‘Rua um numero 6’, ‘Araraquara’, ‘SP’, ‘00000200’); INSERT INTO ‘teste2’.‘clientes’ (‘codigo’, ‘nome’, ‘so- brenome’, ‘idade’, ‘endereco’, ‘cidade’, ‘estado’, ‘cep’) VALUES (‘3’, ‘Marco’, ‘Jose’, ‘25’, ‘Rua H numero 30’, ‘Araraquara’, ‘SP’, ‘00000300’); INSERT INTO ‘teste2’.‘clientes’ (‘codigo’, ‘nome’, ‘so- brenome’, ‘idade’, ‘endereco’, ‘cidade’, ‘estado’, ‘cep’) VALUES (‘4’, ‘Aparicio’, ‘Alfredo’, ‘30’, ‘Rua X numero 15’, ‘Araraquara’, ‘SP’, ‘00000400’); A tabela ficará com os conteúdos conforme mostra- do na figura 18. 30 Figura 18: Consulta da tabela clientes. Fonte: Elaboração própria. Vamos executar o seguinte Exemplo: SELECT nome FROM clientes WHERE nome LIKE ‘M%’ AND cidade IN (SELECT cidade FROM clientes where cidade LIKE ‘A%’); O resultado do primeiro SELECT executado fo- ram as linhas com códigos 2, 3 e 4, pois existem 3 ocorrências que começam pela letra “A”, ou seja, “Araraquara”. Dessas três linhas, o segundo SELECT executado seleciona os nomes que começam por “M”. Temos a Maria e o Márcio, que é o resultado final, pois o Aparício não é considerado, ficando con- forme mostrado na figura 19. Figura 19: Consulta do resultado da subquery. Fonte: Elaboração própria. 31 JUNÇÃO DE TABELAS Utilizamos a cláusula WHERE para aplicar a jun- ção de tabelas, conhecido termo em inglês “JOINs” (relacionamentos). A cláusula JOIN é usada para associar dados de duas ou mais tabelas do banco de dados. Temos duas categorias de joins: a) INNER JOIN: Retorna linhas (registros) quando houver pelo menos uma correspondência em ambas as tabelas pela cláusula “where”. b) OUTER JOIN: Retorna linhas (registros) mesmo quando não houver ao menos uma correspondência em uma das tabelas (ou ambas). No caso do OUTER JOINS. Há três tipos de aplicações: a) LEFT JOIN: Retorna todas as linhas da tabela à esquerda, mesmo se não houver nenhuma corres- pondência na tabela à direita. b) RIGHT JOIN: Retorna todas as linhas da tabela à direita, mesmo se não houver nenhuma correspon- dência na tabela à esquerda. c) FULL JOIN: Retorna linhas quando houver uma correspondência em qualquer uma das tabelas. 32 Para criar uma alias para uma tabela, basta acres- centar um identificador à frente do nome da tabela. A partir de então, basta utilizar este alias para se referenciar à tabela, por exemplo, “p” para pedido, “c” para clientes. Select nome,nr,valor from pedido p inner join clientes c on (p.cliente=c.codigo); Vamos executar o comando a seguir para verificar o entendimento do join e do alias. Select f.nome, p.valor as pagamento from funcionarios f INNER JOIN pagamentos p ON f.codigo_ funcionario = p.codigo_ funcionario; select f.nome, p.valor as pagamento from funcionar ios f LEFT JOIN pagamentos p ON f.codigo_ funcionario= p.codigo_ funcionario; Podcast 1 33 https://famonline.instructure.com/files/168938/download?download_frd=1 ALIAS PARA TABELAS Quando usamos Join, o nome da tabela é citado para diferenciar a qual campo se está fazendo referência. Quando a consulta é complexa e envolve várias ta- belas, referenciar o nome da tabela pode aumentar muito o tamanho da consulta. Para criar uma alias para uma tabela, basta acres- centar um identificador à frente do nome da tabela. A partir de então, basta utilizar este alias para se referenciar à tabela. Select nome,nr,valor from pedido p inner join clientes c on (p.cliente=c.codigo); 34 FUNÇÕES STRING As instruções para tratamento de textos são muito utilizadas para resolver situações para separar ou juntar informações strings, conforme demonstrado na tabela 5. Comandos SQL Resultado SELECT CONCAT(‘My’, ‘S’, ‘QL’); MySQL SELECT CONCAT_WS(“,”,”First name”,”Second name”,“Last Name”); ‘First name,Second name,Last Name’ SELECT REPEAT(‘MySQL’, 3); MySQLMySQLMySQL SELECT SUBSTRING(‘Unicamp’,5); amp SELECT CHAR_LENGTH(‘Elaine’); 6 SELECT SUBSTRING_ INDEX(‘www.mysql.com’, ‘.’, 2); www.mysq Vai até o segundo bloco antes do ponto. Primeiro bloco = www Segundo bloco = mysql Terceiro bloco = com SELECT SUBSTRING_ INDEX(‘www.mysql.com’, ‘.’, -2); mysql.com Considera dois blocos antes do delimitador “.” SELECT REVERSE(‘abc’); cba SELECT UCASE(‘Elaine’); ELAINE Conversão para maiúsculo SELECT Upper(‘Elaine’); ELAINE Conversão para maiúsculo SELECT LCASE(‘MYSQL’); Mysql Conversão para minúsculo SELECT Lower(‘MYSQL’); Mysql Conversão para minúsculo Tabela 5: Funções para tratamentos de variáveis 35 http://www.mysq FUNÇÕES DATA As instruções para tratamento de data são muito úteis para resolver situações para descobrir a partir de uma data, qual o dia da semana, qual nome do dia, qual o nome do mês, conforme demonstrado na tabela 6. Função Objetivo Resultado SELECT DAYOFWEEK (‘2019-09-17’); Descobrir qual o dia da semana 1- domingo, 2- segunda, 3- terça, 4-quarta, 5- quinta, 6- sábado 3 SELECT WEEKDAY(‘2019-09-17’); Descobrir qual semana 0- segunda, 1- terça, 2- quarta, 3- quinta, 4- sexta, 5- sábado, 6- domingo 1 SELECT DAYOFMONTH (‘2019-09-28’); Qual o dia do mês 28 SELECT DAYNAME(‘2019-09-28’); Qual o nome do dia da semana Saturday SELECT MONTHNAME (‘2019-09-28’); Qual o nome do mês September Tabela 6: Funções de tratamento de datas. 36 TRIGGERS NO MYSQL Um trigger é um conjunto de instruções SQL para desempenhar funções lógicas em banco de dados, como atualizar uma coluna estoque do produto. Toda vez que houver uma venda de um item de produto, automaticamente pode subtrair o item de venda do estoque, por meio de uma configuração trigger na tabela de itens de venda. Antes, podemos saber sobre triggers armazena- das, utilizando o comando SQLa seguir: SELECT * FROM INFORMATION_SCHEMA. TRIGGERS; Vamos, num primeiro momento, criar duas tabelas: uma de produto e outra de itens de venda. Para isso, executamos os scripts a seguir, por meio de queries. Recomenda-se seguir os seguintes passos na ordem em que são citados os exemplos. 1. CRIAÇÃO TABELA PRODUTOS CREATE TABLE Produtos ( Referencia INT PRIMARY KEY, Descricao VARCHAR(50) UNIQUE, Estoque INT NOT NULL DEFAULT 0); 2. INSERT TABELA PRODUTOS INSERT INTO Produtos VALUES (1, ‘Feijão’, 10); 37 INSERT INTO Produtos VALUES (2, ‘Arroz’, 5); INSERT INTO Produtos VALUES (3, ‘Farinha’, 15); INSERT INTO Produtos VALUES (4, ‘SAL’, 15); INSERT INTO Produtos VALUES (5, ‘ACUCAR’, 9); INSERT INTO Produtos VALUES (6, ‘CAFE’, 12); 3. Apresentação dos dados da tabela PRODUTOS Select * from produtos; Figura 20: Consulta dos dados da tabela Produtos. Fonte: Elaboração própria. 4. CRIAÇÃO TABELA itens de venda CREATE TABLE itensvenda ( Venda INT NOT NULL, Produto INT NOT NULL, Quantidade INT NULL, PRIMARY KEY (Venda, Produto)); 5. CRIAÇÃO TRIGGER itens de venda para INSERT 38 Toda vez que incluir um item de venda, atualiza-se o valor de estoque do produto, subtraindo do estoque do produto a quantidade de vendas. DELIMITER $ CREATE TRIGGER Tgr_ItensVenda_Insert AFTER INSERT ON ItensVenda FOR EACH ROW BEGIN UPDATE Produtos SET Estoque = Estoque - NEW.Quantidade WHERE Referencia = NEW.Produto; END$ O nome da trigger é Tgr_ItensVenda_Insert. Após cada vez que for incluído um item na tabela itens de venda (AFTER INSERT), será subtraída a quantidade (NEW.Quantidade) do estoque (Estoque). Para cercar isso, a atualização correta do registro é comparada à coluna Referência da tabela produto com itens de produto (NEW.Produto). Após a criação do trigger, irá aparecer dentro da tabela de vendas (conforme figura 21). 39 Figura 21: Criação de Trigger para itens de vendas. Fonte: Elaboração própria. 6. INSERT DE ITENS DE VENDAS Vamos incluir os itens de venda para ativar o trigger. INSERT INTO ItensVenda VALUES (1, 1,3); INSERT INTO ItensVenda VALUES (1, 2,1); INSERT INTO ItensVenda VALUES (1, 3,5); Após a inclusão de dados na tabela de ItensVenda, é mostrado o resultado da consulta (conforme tabela 22). Após a inclusão, entra em ação o Tgr_ ItensVenda_Insert, que subtrai a quantidade de itens de vendas do estoque do produto (10 – 3 = 7). Isso é demonstrado na figura 23. 40 Figura 22: Consulta da tabela Itens de venda. Fonte: Elaboração própria. Figura 23: Consulta da tabela Produto após inclusão de itens de vendas. Fonte: Elaboração própria. 7. CRIAÇÃO DE TRIGGGER PARA TABELA ITENS DE VENDAS APÓS DELETE Toda vez que excluir um item de venda, atualiza-se o valor de estoque do produto, somando no estoque do produto a quantidade de vendas. DELIMITER $ CREATE TRIGGER Tgr_ItensVenda_Delete AFTER DELETE ON ItensVenda 41 FOR EACH ROW BEGIN UPDATE Produtos SET Estoque = Estoque + OLD.Quantidade WHERE Referencia = OLD.Produto; END$ O nome da trigger é Tgr_ItensVenda_Delete. Após cada vez que for excluído um item na tabela itens de venda (AFTER DELETE), será somada a quantidade (OLD.Quantidade) do estoque (Estoque). Para cercar isso, a atualização correta do registro é comparada à coluna Referência da tabela produto com itens de produto (OLD.Produto). Após a criação do trigger, irá aparecer dentro da ta- bela de vendas (figura 24). 42 Figura 24: Consulta da tabela itens de vendas com a criação de trigger. Fonte: Elaboração própria. 8. DELETE NA TABELA ITENS DE VENDAS Vamos executar a exclusão do item de venda. Para ativar a trigger “Tgr_ItensVenda_Insert” de exclusão do item de venda. DELETE FROM itensvenda WHERE venda = 1 AND produto = 1; Após a exclusão de dados na tabela de ItensVenda, é mostrado o resultado da consulta, conforme figura 25. 43 Figura 25: Consulta da tabela itens de vendas com a criação de trigger. Fonte: Elaboração própria. 9. CONSULTA DA TABELA PRODUTOS Após a exclusão do item de venda, entra em ação o Tgr_ItensVenda_Delete, que soma a quantidade de itens de vendas do estoque do produto (7 + 3 = 10), conforme demonstrado na figura 25. Figura 26: Consulta da tabela de produtos após exclusão do item de venda. Fonte: Elaboração própria. Podcast 2 44 https://famonline.instructure.com/files/168939/download?download_frd=1 “STORED PROCEDURE” A sintaxe geral para criação de Stored Procedure é a seguinte: CREATE PROCEDURE proc_name([parameters, ...]) [characteristics] [BEGIN] corpo_da_rotina; [END] Detalhes sobre a configuração de parâmetros para sintaxe de “Stored Procedure”: proc_name: seu procedimento armazenado deve ter um nome para, quando for chamado, podermos então usá-lo. tipo_param: existem três tipos de parâmetros em uma Stored Procedure. proc_name: seu procedimento armazenado deve ter um nome para, quando for chamado, podermos então usá-lo. 45 tipo_param: existem três tipos de parâmetros em uma Stored Procedure no MySQL: a) IN – este é um parâmetro de entrada, ou seja, um parâmetro cujo valor será utilizado no interior do procedimento para produzir algum resultado. b) OUT – este parâmetro retorna algo de dentro do procedimento para o lado externo, colocando os valores manipulados disponíveis na memória ou no conjunto de resultados. c) INOUT – faz os dois trabalhos ao mesmo tempo. Para colocar em prática, vamos criar uma tabela “tbl_ correntista”, conforme script abaixo; o objetivo é criar uma Stored Procedure para passar por parâmetros NOME e CPF, em que esses parâmetros são passa- dos por uma chamada por “CALL”, que é executado por query, na ferramenta IDE WorkBench MySQL. A chamada por “CALL” ativa a “Stored Procedure” que, com os parâmetros passados, é atualizada a tabela tbl_correntista. 1. PRIMEIRO PASSO: Criação da tabela Correntista CREATE TABLE tbl_correntista ( 46 correntista_id int auto_increment pri- mary key, correntista_nome varchar(60) not null unique, correntista_cpf varchar(20) not null, dt_cadastro timestamp default current_timestamp); 2. SEGUNDO PASSO: Criação STORED PROCEURE para INSERT Vamos criar a “STORED PROCEDURE” para criar in- cluir dados de parâmetros passados pela chamada do “CALL” para incluir dados na tabela Correntista. DELIMITER $ CREATE PROCEDURE mySp_correntistaInsert (v_correntista_nome VARCHAR(60), v_correntista_cpf VARCHAR(20)) BEGIN IF ((v_correntista_nome!= ‘’) && (v_correntista_cpf!= ‘’)) THEN INSERT INTO tbl_correntista (correntista_nome, correntista_cpf) 47 VALUES (v_correntista_nome, v_correntista_cpf); ELSE SELECT ‘NOME e CPF devem ser fornecidos para o cadastro!’ AS Msg; END IF; END$ O nome da “Stored Procedure” é “mySp_correntistaIn- sert”. Após cada vez que for acionada por uma cha- mada por “CALL”, será ativada a “Stored Procedure” e, então, é efetivada a inclusão na tabela correntista. Os parâmetros são recebidos nome (v_correntis- ta_nome) e CPF (v_correntista_cpf). Depois disso, são verificados se os parâmetros conteúdos não são vazios; então, é efetivada a inclusão da tabe- la correntista por meio da instrução (INSERT INTO tbl_correntista) e são incluídos os dados “VALUES (v_correntista_nome, v_correntista_cpf)”. 3. TERCEIRO PASSO: Chamada da “Stored Procedure” por “CALL” Antes da chamada é apresentada a consulta da tabela, conforme figura 26: Figura 27: Consulta da tabela de correntista. Fonte: Elaboração própria. 48 A chamada de “CALL” da “Stored Procedure” é fei- ta por query na ferramenta IDE WorkBench MySQL, conforme a seguinte instrução: CALL mySp_correntistaInsert (‘Wagner Bianchi’, ‘023.456.789-10’); Após a chamada por “CALL” ativa a “Stored Procedure” e, então, a inclusão de dados é providen- ciada e,depois disso, a tabela de correntista fica com os dados conforme a figura abaixo. Figura 28: Consulta da tabela de correntista após Stored Procedure. Fonte: Elaboração própria. 49 CONSIDERAÇÕES FINAIS Neste módulo, os conteúdos foram explicados por meio de exemplos testados na ferramenta Workbench. Os exemplos práticos servem para que o aprendizado seja simples e objetivo, além de es- timular reflexões de entendimento de forma mais consistente. Há muitas abordagens para sedimentar o conheci- mento. Duas abordagens, dentre todas as explicadas, merecem destaque: uma é a junção de tabelas, es- sencial para compor informações que são necessá- rias como resultado para os usuários. A outra abordagem refere-se aos conhecimentos sobre operações lógicas que podem ser programa- das e executadas pelo próprio banco de dados. São funções automatizadas; com funções de bancos de dados por aplicativos que podem ser executadas dentro do próprio banco de dados. Quanto às funções automatizadas, uma programação é chamada de Trigger e a outra, de Stored Procedure. Essas técnicas, aos serem transferidas a progra- mação e a execução do aplicativo para o banco de dados, tornam o aplicativo mais leve na execução. Esperamos que todo o conhecimento adquirido aqui tenha sido bem aproveitado! 50 • Stored Procedure. • Trigger. • Automações de funções. • Técnicas para fazer a junção de tabelas. • Operadores Auxiliares. • Operadores Relacionais. • Operadores Aritméticos. Estudamos: Várias abordagens de comandos de SQL foram explicadas de como proceder a inclusão, alteração, exclusão e consulta de dados, de modo mais avançado. Banco de Dados DE ATH YST SÍNTESE Referências Bibliográficas & Consultadas ASCENCIO, A. F. G.; ARAÚJO, G. S. Estruturas de dados: algoritmos, análise da complexidade e implementações em JAVA e C/C++. São Paulo: Pearson Prentice Hall, 2010. [Biblioteca Virtual] BARBOZA, F. F. M. et al. Modelagem e desenvol- vimento de banco de dados. Porto Alegre: Sagah, 2018. [Minha Biblioteca] BOOCH, G.; RUMBAUGH, J.; JACOBSON, I. UML: guia do usuário. São Paulo: Campus, 2000. CALSARA, A.; MACHADO, C. A. F.; REINEHR, S. S.; BURNETT, R. C. Aderência do RUP à norma NBR ISO/IEC 12207. Dezembro/2002. Disponível em: https://docplayer.com.br/18795196-Aderencia-do- -rup-a-norma-nbr-iso-iec-12207.html. Acesso em: 03 out. 2019. DATE, C. J. Introdução a sistemas de bancos de dados. 7. ed. Rio Janeiro: Campus, 2000. DBDesignerfork. Software livre para modelagem de Dados. Disponível em: https://db-designer-fork. soft112.com. Acesso em: 05 set. 2019. ELMASRI, R.; NAVATHE, S. Sistemas de banco de dados. 4. ed. São Paulo: Pearson, 2005. [Biblioteca Virtual] GANE, C. Análise estruturada de sistemas. 7. ed. Rio Janeiro: LTC, 1993. HAY, D. C. Princípios de modelagem de dados. São Paulo: Makron, 1999. HEUSER, C. A. Projeto de banco de dados. 6.ed. Porto Alegre: Bookman, 2009. [Minha Biblioteca] KRUCHTEN, P. Introdução ao RUP Rational Unified Process. 2. ed. Rio de Janeiro: Ciência Moderna, 2003. MEDEIROS, L. F. Banco de dados: princípios e práti- ca. Curitiba: InterSaberes, 2013. [Biblioteca Virtual] PRESSMAN, R. S. Engenharia de software. 5. ed. São Paulo: Makron Books, 2002. PUGA, S.; FRANÇA, E.; GOYA, M. Banco de dados: implementação em SQL, PL/SQL e Oracle 11g. São Paulo: Pearson Education do Brasil, 2013. [Biblioteca Virtual] https://db-designer-fork.soft112.com/ https://db-designer-fork.soft112.com/ RAMAKRISHNAN, R.; GEHRKE, J. Sistemas de gerenciamento de banco de dados. 3. ed. Porto Alegre: AMGH, 2011. [Minha Biblioteca] RESENDE, D. A. Engenharia de software e sistemas de informação. 2. ed. Rio Janeiro: Brasport, 2003. SETZER, V. W. Banco de dados. 3. ed. Rio Janeiro: Edgard Blücher, 1989. SOMMERVILLE, I. Engenharia de software. 6. ed. São Paulo: Pearson, 1995. TELES, V. M. Extreme Programming. São Paulo: Novatec, 2004. WOLFGANG, P. A. T.; KOFFMAN, E. B. Objetos, abs- tração, estruturas de dados e projeto usando C++. Rio de Janeiro: LTC, 2008. [Minha Biblioteca] _GoBack Introdução Comandos de SQL para Operações Avançadas de Bancos de Dados Comandos SQL Gerais Operadores Aritméticos Operadores Relacionais Tabelas para uso dos exemplos a Seguir Classificação de dados Operadores auxiliares Verificação de caracteres Funções Agregadas Relacionamento de tabelas Informações Agrupadas Qualificadores Subquery Junção de Tabelas Alias para Tabelas Funções String Funções Data Triggers no MySQL “Stored Procedure” Considerações finais Síntese
Compartilhar