Olá, para iniciarmos com o mysql eu preparei um guia do que um dev back-end costuma lidar no seu dia dia, pricipalmente em aplicações legadas, você vai precisar.

O Banco de dados MYSQL tem por característica salvar dados em arquivos dentro do host atual ou remoto e criar tratativas para esses dados. Para trabalhar com os dados utilizamos estrutura em tabelas que ficam dentro de banco de dados nomeados.

Databases == schemas

Em banco de dados MYSQL temos as Databases e Schemas, na verdade são sinônimos e podemos pensar neles como agrupadores de tabelas. Por exemplo: a aplicação do comércio do João tem uma base de dados específica que nada mais é do que tabelas relacionadas entre si utilizando um corpo de trabalho, lugar ou schemas.

Qual a finalidade do sql? Facilitar a manipulação dos dados podendo interagir de forma produtiva. Existem querys sql, funções, triggers e entre outros que vão tratar dados para facilitar a vida de quem lida com esses dados de forma a ser mais fácil inserir, excluir ou atualizar com base nas regras do negócio.

Como criar uma base de dados?

CREATE DATABASE padaria_joao;

Para criar uma base de dados podemos passar o comando CREATE DATABASE, e definimos o nome de nossa base de dados, nesse caso criamos uma base de dados padaraia_joao

Composição de uma tabela

No exemplo da imagem acima temos uma tabela produtos com as características:

columns(colunas),

indexes(índices),

Foreign Keys(Chaves Estrangeiras),

Triggers (Gatilhos).

As colunas são os campos a serem preenchidos com seus respectivos dados, como exemplos podemos pensar em: id, nome e preco , os índices geralmente se tratam de nossa chave primária que em muitas vezes são incrementais ou seja, ao ser inserido um dado o proprio MYSQL faz gerêncimento, os indices também podem ser índices não clusterizados onde podemos trabalhar para melhorar as consultas em uma tabela muito acessada.

Chaves estrangeiras são índices relacionados ao mundo exterior ou seja, as outras tabelas dentro de sua base de dados.

Os gatilhos geralmente são comportamentos que queremos que nossa tabela faça se determinada ocorrência acontecer antes ou depois de uma execução. Por exemplo, temos uma tabela cliente e temos uma tabela mensalidades, sempre que um cliente é inserido podemos criar um gatilho para ser disparado e iniciar a mensalidade com zero dentro da tabela mensalidade, sem uma linha de código no backend da aplicação.

Corpo de uma tabela

Você pode imaginar uma tabela como uma planilha do excel ou libre office, onde temos as colunas e em suas linhas tendo os valores, veja o exemplo abaixo.

-------------------------------------------
id| nome                   | idade   |
-------------------------------------------
1 | André Martins          | 150     |
2 | Bia Martins            | 11      |

as colunas são id, nome e idade. os valores são os dados embaixo de cada titulo de coluna.

Sessão de comandos

Criação de um tabela

create table estudo_mysql.teste(
    id int not null auto_increment,
    nome varchar(90) not null,
    primary key (`id`));

No exemplo acima criamos uma tabela com o nome teste que está dentro da base de dados estudo_mysql e tem os campos id e nome, sendo que o id é auto incrementável, não podendo ser nulo e o nome é do tipo varchar suportando até 90 caracteres, também definimos a chave primária sendo o id.

Como criar um relacionamento entre duas tabelas

Acredito que poucas pessoas façam esse tipo de operação sem a ajuda de uma interface gráfica, mas de qualquer forma irei deixar o registro de como fazer.

CREATE TABLE `testes`.`produtos` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `nome` VARCHAR(45) NULL,
  `id_categoria` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_categoria_idx` (`id_categoria` ASC) VISIBLE,
  CONSTRAINT `fk_categoria`
    FOREIGN KEY (`id_categoria`)
    REFERENCES `testes`.`categorias` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION);

No exemplo acima iremos destacar que criamos um INDEX com o nome fk_categoria_idx que tem uma referência para a coluna id_categoria, declaramos que ela é visivel, também declaramos a CONSTRAINT (limitação em tradução literal) que é chamada de fk_categoria, por fim nossa FOREING_KEY que de fato é onde iremos posteriormente atribuir os resultados. Sempre que fizermos um delete de uma categoria por termos colocado ON DELETE CASCASE na tabela de produtos o MYSQL irá excluir todos os produtos relacionados a uma categoria, assim não existirão dados mortos em nosso banco, já no update não colocamos nenhuma ação para ser feita.

Adicionando colunas a uma tabela Para adicionar uma coluna em MYSQL precisamos alterar uma tabela existente, vamos utilizar a tabela de produtos adicionando a coluna preco.

ALTER TABLE produtos ADD COLUMN preco decimal(10,2) default 0.0;

Utilizamos no exemplo o ALTER TABLE passando o nome da tabela e adicionando uma coluna preco, note que aplicamos o default com o valor 0.0, nesse caso todos os registros que já existam terão o valor padrão 0.0 e como não definimos essa coluna como NOT NULL quando não inserido nada o valor padrão será 0.0

Modificando tipo de dado de uma coluna existente

Para modificar um tipo de uma coluna existente vamos aplicar na tabela produtos, alterando o valor preco para uma string que suporta 45 caracteres.

ALTER TABLE produtos modify COLUMN preco varchar(45) default 'novo_valor';

O que já existe na tabela não será alterado, porém toda inserção nova que não tiver valores irá atribuir o novo valor padrão 'novo_valor'

Adicionando em uma tabela

inserção única

insert into produtos (nome, preco, descricao) values ('Testes de primeira inserção', 100.00, 'descricao do produto');

inserção em massa

INSERT INTO tabela(campo1, campo2) values ("valor 1", 1), ("valor 2", 2), ("valor 3", 3);

Pra inserir em uma tabela precisamos passar o comando insert into o nome da tabela, seus campos e após isso os values(valores) que vem precedido da palavra reservada values, note que é preciso respeitar cada tipo de valor que será inserido em sua ordem, no caso da tabela de produtos temos nome sendo varchar, preco sendo decimal (10,2) e temos a descrição sendo text.

caso queira estudar mais sobre tipos de dados para mysql segue um link da documentação oficial

dev.mysql.com/doc/refman/8.0/en/data-types...

Seleção de dados de uma tabela

Para trabalhar com recuperação de dados temos muitas formas a partir do select, veja alguns exemplos abaixo:

SELECT * FROM pessoa;

Esse comando seleciona todos os campos da tabela pessoa.

SELECT * FROM pessoa WHERE id=1;

Esse comando seleciona todos os campos de uma pessoa selecionada a partir de uma id passado, perceba que a palavra reservada where é onde passamos esse valor.

select * from produtos where preco <= 100;

Podemos selecionar todos os produtos desde que (where) o preco for menor ou igual a 100

select * from produtos order by preco [desc || asc]

Agora imagine que você gostaria de trazer todos os produtos com preços por ordem ascendente ou descendente, podemos fazer isso com o order by passando a coluna que queremos.

Apelidos em colunas de tabelas

Utilizamos apelidos em colunas para apresentar de uma forma diferente

SELECT nome as `Nome do Produto`, preco as `Preço do produto` from produtos;

dessa forma alteramos a apresentação das colunas de uma tabela.

Seleção IN

Podemos ter a necessidade de trazer vários produtos com ids passados mas ao mesmo tempo deixar de fora outros, vejamos um exemplo:

SELECT * FROM produtos WHERE id IN (1, 2, 3);

Muitas vezes queremos trazer dados de tabelas relacionadas e montamos geralmente a partir de joins, mas podemos utilizar a cláusula IN para performar melhor em consultas que frequentemente acontecem.

Exemplo: tabela produto e tabela categoria sendo que um produto tem um relacionamento de n-1 com categoria (n produtos podem estar na mesma categoria), ou seja uma categoria pode ter vários produtos.

tabela de produto campos: id, nome, preco, id_categoria(sendo fk)

tabela de categoria campos: id, nome, descricao

select * from produtos where id_categoria in(
    select id from categorias where nome like 'frutas'
);

Dessa forma percebemos que podemos trazer todos os produtos que estão em uma determinada categoria que selecionamos a partir de um nome.

select * from produtos where id_categoria in(
    select id from categorias where id=2
);

Agora trazemos todos os produtos que estão na categoria de id 2

select * from produtos where id_categoria in(
    select id from categorias where id in(1, 2)
);

Agora trazemos todos os produtos que estão na categoria 1 e 2

select * from produtos where valor > 10 and id_categoria in(
    select id from categorias where id in(1, 3, 5)
);

Agora trazemos todos os produtos com valor maior que 10 e que estão na categoria de id 1, 3 ou 5 perceba o nivel de granularidade que podemos ter em nossas consultas, isso é PODEROSO.

select nome, preco from produtos p where id_categoria IN(
   select id from categorias where (nome='frutas' OR nome='verduras') and p.preco >= 15
);

Nesse exemplo estamos selecionando nome e preco de produtos desde que o id esteja contido no select mais abaixo, onde definimos que a seleção será a partir de nome de uma categoria igual a frutas ou nome igual a verduras, perceba que passamos um apelido para nossa tabela produtos onde utilizamos o mesmo na consulta interna e adicionamos mais uma condição, essa últimas condição poderia ser no primeiro select sem problemas também.

Seleção AND

Podemos utilizar juntamente com o where o and ou or para operar logicamente, veja o exemplo.

# AND
select * from produtos where preco > 100 and preco < 112;
# OR
select * from produtos where (valor > 20 ou valor < 30);

No exemplo acima selecionamos na primeira linha de execução todos os produtos que o preço é maior que 100 e menor que 112 e na segunda linha de execução selecionamos todos os produtos que o valor é maior que 10 ou menor que 30

Seleção Like

SELECT nome FROM produtos WHERE nome LIKE'm%';

No exemplo acima selecionamos um nome de uma tabela chamada produtos desde que nome comece com m e tenha qualquer sequencia de caractéres

SELECT nome FROM produtos WHERE nome NOT LIKE'm%';

Agora é o inverso, onde não temos a letra m sendo a primeira traremos os outros resultados.

SELECT NOME from produtos WHERE NOME like '_a%';

Selecionamos todos os produtos desde que contenham a segunda letra sendo a seguido de qualquer outro caractere

SELECT nome FROM produtos WHERE nome NOT LIKE '_a%';

Selecionamos todos os produtos desde que não contenham a segunda letra sendo a seguido de qualquer outro caractere

Selecionando tipos distintos em uma coluna Caso queira verificar quais são os tipos diferentes em determinada coluna você poderá utilizar o distinct para trazer apeanas os valores diferentes.

por exemplo: em uma determinada tabela chamada produto temos a coluna nome, e nessa coluna temos diversos valores diferentes mas queremos trazer apenas o valores distintos uns dos outros, então podemos fazer o comando:

    SELECT distinct(nome) from produtos;

Como resultado será retornado todos os nomes de produtos que se tem valores diferentes.

Seleção com Regexp Podemos utilizar expressões regulares para selecionar dados em uma tabela, alguns exemplos abaixo.

SELECT nome from testes.produtos where nome regexp '^[cp]';

No exemplo acima, utilizamos o caractere especial ^ fora dos colchetes para dizer que qualquer nome que comece com a letra c ou p será retornado nessa consulta.

select nome from testes.produtos where nome regexp '^[^cp]';

Nesse outro exemplo fizemos o NOT, quando colocamos ^ dentro do colchete estamos trazendo uma negação para a consulta, nesse caso seria descrito: selecione nome de produtos desde que não comece com c ou p

select nome from produtos where nome regexp '[a]$';

Com a utilização do caractere $ no final de uma expressão estamos dizendo que queremos selecionar todos os nomes que terminem com a, caso estivesse com o ^a seria um NOT.

Seleção com group by A clausula group by tem por finalidade como o nome já diz retornar dados agrupados por, um exemplo seria: digamos que temos uma tabela de endereço e nessa tabela temos usuarios de varios estados e temos também vários usuários que moram no mesmo Estado, com essa cláusula fica facil saber quantos usuários tem em cada estado.

Na tabela endereco temos as colunas, estado, nome_usuario e endereco

select estado, count(estado) from endereco group by estado;

Descrevendo o comando: selecione estado contando todos os estados e agrupando por estado, dessa forma como SP tem varias pessoas ele vai agrupar nessa linha o resultado para sp, se RJ tiver varios usuarios ele também vai agrupar esses usuarios em uma linha especifica.

Having o where do group by Quando queremos filtros em cima de dados agrupados podemos utilizar a cláusula having.

select estado count(estado) from estado group by estado having count(estado)=1;

dessa forma podemos construir filtros em cima do que foi agrupado em nossa consulta. ainda poderíamos ser mais específicos trazendo um where logo após o from estado por exemplo

select estado count(estado) from estado where nome='sp' or nome='rj' group by estado having count(estado) > 10;

Inner join

Um dos assuntos que as pessoas ficam mais confusas é a utilização de inner join, Para illustrar imagine que você tenha uma tabela chamada alunos e outra tabela chamada cursos, para saber quais cursos tem alunos e quais alunos estão presentes em cursos podemos utilizar o artifício do inner join.

Na imagem acima podemos ver que a seleção a partir de inner join trará todas as intersecções entre alunos e cursos que está destacado com a seta vermelha, onde se vê as setas verdes são o outer join que em tradução literal é exterior, onde podemos perceber que ficando intuitivo, inner join para dados esperados outer join para dados exteriores.

Exemplo: digamos que nós temos uma tabela produtos e uma tabela categorias, onde cada produto pode estar com um relacionamento para uma categoria.

select p.nome, p.valor, c.nome, c.descricao
from produtos p
inner join categorias c
on p.categoria_id=c.id

No exemplo acima fizemos uma seleção de nome e valor da tabela produtos e nome e descrição de categorias, se tratando do inner join ele vai trazer todos os produtos que tem uma categoria inserida.

Observações, c e p são alias para suas respectivas tabelas.

Ainda falando do exemplo acima podemos fazer filtros nos resultados obtidos, por exemplo:

select p.nome, p.valor, c.nome, c.descricao
from produtos p
inner join categorias c
on p.categoria_id=c.id
where c.nome='comida'

com filtros tão específicos fica fácil fazer seleções em tabelas relacionadas.

left join

Na imagem acima podemos ver o exemplo de um left join, no caso sendo selecionado o left join para alunos e cursos do lado right, o que será retornado nesse tipo de seleção é tudo do lado left e tudo do inner(meio ou intercecção), nada do right será retornado, imagine que na tabela estamos trazendo duas colunas nome_aluno e nome_curso, nesse caso ele vai trazer todos os alunos mesmo que não tenham nenhuma associação com algum curso alem de trazer todos os alunos que tenham.

exemplo de left join na tabela de produtos e categorias.

select p.nome, p.valor, c.nome, c.descricao
from produtos
left join categorias c
on p.id_categoria=c.id

com o left join podemos trazer todos os produtos que tenham e que não tenham uma categoria relacionada essa é a unica diferença entre o left e o inner

Right join

Na imagem acima podemos ver o exemplo de um right join, no caso sendo selecionado o right join para cursos e alunos do lado left, o que será retornado nesse tipo de seleção é tudo do lado right e tudo do inner(meio ou intercecção), nada do left será retornado, imagine que na tabela estamos trazendo duas colunas nome_aluno e nome_curso, nesse caso ele vai trazer todos os nome_curso mesmo que não tenham nenhuma associação com algum aluno especifico e apenas nome_aluno que tenha associação, alem de trazer todos os cursos que tenham.

exemplo de left join na tabela de produtos e categorias.

select p.nome, p.valor, c.nome, c.descricao
from produtos
right join categorias c
on p.id_categoria=c.id

Em determinada situação podemos querer saber apenas categorias que não tem nenhum produto setado, podemos fazer como abaixo

select c.nome, c.descricao, p.nome, p.valor
from categorias c
left join produtos p
on c.id = p.id_categoria
where p.nome is null;

Selecione nome da categoria, descrição da categoria, nome do produto e valor do produto da tabela categoria, fazendo um left join com produtos, ou seja irá trazer categorias que tem produtos e categorias que não tem produtos, e por fim fazemos uma condição desde que o nome do produto seja null, nesse caso irá trazer apenas as categorais que não tem produtos cadastrados.

com o right join podemos trazer todos as categorias que tenham e que não tenham um produto relacionado essa é a unica diferença entre o right e o inner

Remoção

DROP TABLE tabela;

Comando para deletar uma tabela do banco de dados, um comando muito perigoso.

As vezes temos a necessidade de deletar apenas uma linha de nossa tabela então podemos executar o comando:

DELETE from table_teste where id=5;

Atenção, sempre utilize o where esse é um comando muito perigoso, verifique se você tem um dump de backup de sua base de dados, e sempre se arrisque em ambiente de desenvolvimento nunca em produção.

Comparações entre valores

Se você tiver a necessidade de selecionar algum dado de uma tabela que esteja entre dois valores poderá utilizar a clausula between, essa cláusula não é apenas para comparativos entre datas, pode se utilzar outros valores para fazer os comparativos.

Select * from produtos where(data_inicio BETWEEN '2020-12-16' and '2020-12-20' and status = '1');

No exemplo acima filtramos um produto que está entre as datas 16 do 12 e 20 do 12 e que seu status esteja definido igual a 1

select nome as Nome, preco as Preço from produtos as p where(p.preco between 10.00 and 15.00);

Podemos trazer dois preços em algum intervalo de uma tabela, ou seja irá trazer preços que estejam entre 10 e 15 da tabela de produtos

Atualização

UPDATE users set name='novo', idade='23' where(id=1);

UPDATE produtos set nome='maçã é muito boa' where id=1

Para atualizar uma tabela passamos a cláusula update seguida do nome da tabela e apos isso passamos o set para cada coluna que será atualizada, caso não passe alguma coluna ela permanecerá com o valor que já existe, e não custa lembrar para sempre passar a cláusula where para atualizar apenas a tabela selecionada.

Adicionar campos a uma tabela

Caso tenhamos a necessidade de adicionar campos em alguma tabela podemos fazer isso atravéz do alter table, veja o exemplo. ex1

ALTER TABLE produtos ADD preco_de decimal(10,2);

No exemplo acima passamos a cláusula alter table para alterar a tabela de produtos e passamos o add para adicionar um campo que nomeamos de preco_de passando seu tipo como decimal(10,2)

Removendo campo de uma tabela ex1

alter table produtos drop column preco;

No exemplo acima nos alteramos a tabela produtos removendo a coluna preco.

Explanação de recursos no mysql

É possível verificar quanto de recurso uma consulta irá gastar, podemos fazer isso atravéz do explain

explain select * from produtos where nome='maçã é boa';

com base nesse tipo de consulta podemos verificar quanto de recurso será utilizado

Funções de agregação

Podemos utilizar funções de agregação para fazer operações aritiméticas em uma determinada tabela, vamos ver alguns exemplos.

select count(*) from produtos;

Conta todas as linhas de uma tabela

select count(distinct id_categoria) from produtos;

Conta todas as linhas de uma tabela mas sem id_categoria repetidos, ou seja se tiver dois produtos que tem o mesmo id irá contar apenas o primeiro.

select max(preco) from produtos;

Seleciona o maior preço da tabela produto

select min(preco) from produtos;

Seleciona o menor preço da tabela de produtos.

select min(preco) from produtos where (id <= 10 and id >= 5);

Seleciona o menor preço da tabela de produtos desde que o id for menor ou igual a 10 e maior ou igual a 5

select avg(preco) from produtos;

Podemos retornar a media de preços de todos os produtos ao qual passamos a função avg.

select sum(preco) from produtos;
select sum(preco) from produtos where (id>1 and id <5);

Somamos a coluna preço e trazemos a somatória de todos os preços, e no outro select somamos desde que o id for maior que 1 e menor que 5.

Renomear uma tabela

rename table table_teste to test_new_name;

Com esse comando é possivel renomear uma tabela.

Backup/restore de uma base de dados Para se fazer o backup de uma base de dados mysql você precisa do mysqldump que é um utilitário do mysql.

A partir do terminal você poderá seguir com o comando para executar os seu backup.

mysqldump -u root -p meu_banco > /home/SEU_USER/dumps/meu_banco.sql

Para fazer a restauração de um banco de dados você pode utilizar o comando mysql

mysql -u root -p meu_novo_banco < /home/andremartds/dumps/meu_banco.sql

Caso de uma restauração é necessário sempre observar que o novo banco de preferência esteja vazio, caso o banco não exista você pode utilizar comando abaixo.

create database meu_novo_banco;

Funcões Podemos utilizar funcões personalizadas no mysql, vamos colocar alguns exemplos:

CREATE FUNCTION fn_teste(n DECIMAL(10,2), m int)
returns INT deterministic
return n * m;

a função acima é muito simples, ela apenas multiplica um decimal com um inteiro e retorna um inteiro, quando executamos essa função ela será armazenada em nosso banco, podemos utilizar nossa função da seguinte forma.

select fn_teste (10.5,20) as soma;

Funcões com delimitadores

DELIMITER $$
CREATE FUNCTION adiciona_valor(preco DECIMAL(10,2), aumento decimal(10,2)) returns decimal(10,2) deterministic
BEGIN
    RETURN preco + preco * aumento / 100;
END$$
DELIMITER ;

select concat('O total a pagar é = ', adiciona_valor(100, 10), ' reais') as valor;

A funcao acima adiciona uma porcentagem a um valor passado, mas o interessante desse exemplo são os delimitadores e o begin e end, dentro do bloco begin e and podemos ter nossas execuções e dessa forma podemos tratar valores para respostas, o problema é que por padrão o ponto e virgula sempre mata a execução e para resolver essa questão definimos outro delimitador, que pode ser qualquer caractére não reservado do myql.

após executamos a função ela será armazenada em nossa base de dados, e dai pomos executar, nesse caso concatenamos para uma melhor saída para o usuário.

Procedimentos armazenados

No myslq podemos criar procedimentos armazenados, Logo abaixo podemos ver um procedimento que tem por nome verPreco e como parâmetro passamos o id de um produto sendo do tipo int, o selecionamos e concatenamos com uma mensagem para trazer uma mensagem, id e valor com um alias, logo depois definimos a tabela que vamos selecionar e também a partir de que condição(where), nesse caso estamos trazendo pelo id do produto que foi o que passamos por parâmetros.

# criação
create procedure verPreco(id_produto int)
select concat('o preço é do produto identificado pelo id ',p.id, p.valor) as 'Descrição'
from produtos p
where p.id = id_produto;

# vamos chamar nosso procedimento
call verPreco(1);

Obrigado por chegar até aqui!! um abraço!! ;)

by: André Martins