#SQL na unha: manipulando dados

Se é a primeira que está aqui, saiba que essa é a terceira parte de uma série de artigos abordando banco de dados. E para te ajudar, já deixo aqui os links para que leio tanto o primeiro quanto o segundo artigo. Agora vamos para o que interessa.

Inserindo dados

Podemos fazer uma inserção informando os campos que devem ser preenchidos, lembre-se que a ordem importa:

insert into user (id, name, email, updated_at, created_at) value (1, 'erandir', '[e-mail]', now(), now());

Podemos fazer uma inserção apenas seguindo a ordem das colunas definidas na tabela:

insert into user value (2, 'maria', '[e-mail]', now(), now());

Podemos fazer uma inserção ignorando todos os campos com valores padrão, eles serão preenchidos pelo própio SGBD:

insert into user (name, email) value ('erandir junior', '[e-mail]');

Podemos fazer várias inserções de uma única vez:

insert into user (name, email) values ('joao', '[e-mail]'), ('jessica', '[e-mail]'), ('ricardo', '[e-mail]'), ('fernanda', '[e-mail]'), ('jessica', '[e-mail]');

insert into product (name, value, description, user_id) values ('TV 50', 1000.00, 'Tv de 50 polegadas', 4), ('Smartphone', 2000.00, NULL, 5), ('Notebook', 1000.00, NULL, 6), ('Geladeira', 1000.00, 'Geladeira daquelas caras, que sai água da porta', 7);

Listando dados

Podemos listar todos os registros de uma tabela específica:

select * from user;

Não recomendo o uso do * em um select. Ele traz todas as colunas e muito provavelmente você não vai precisar de todas elas. Sempre liste somente o que você vai precisar.

Podemos exibir somente os registros das colunas informadas:

select name, email from user;

select email, name from user;

Podemos dar um apelido para as colunas especificadas:

select name, email, created_at as creation from user;

A cláusula WHERE, nos permite filtrar nossas listagens, observe bem e execute os comandos abaixo:

select id, name from user where id = 3; -- retorna apenas os dados com id igual a 3

select id, name from user where name = 'erandir'; -- retorna apenas os dados com name igual a erandir

select id, name from user where id > 4; -- retorna apenas os registros com o id sendo maior 4

select id, name from user where id < 3; -- retorna apenas os registros com id menor que 3

select id, name from user where id <> 1; -- retorna apenas os registros com id diferente de 1

select id, name from product where description IS NULL; -- retorna apenas os registros que tem o campo descrição como nulo

select id, name from product where description IS NOT NULL; -- retorna apenas os registros que tem o campo descrição preenchida

select id, name from user where id = 1 AND id = 2; -- retorna todos os registros que atendem às duas condições

select id, name from user where id = 1 OR id = 2; -- retorna todos os registros que atendem a uma das duas condições

O comando LIKE, permite verificar se um determinado valor está contido nos registros de uma coluna especificada:

select id, name, email from user where email like 'erandir%'; -- retorna todos os registros onde os valores da coluna informada começa com o dado passado, independente do que vier depois

select id, name, email from user where email like '%email%'; -- retorna todos os registros onde os valores da coluna informada contém o valor passado, independentemente se começa ou termina com  o valor informado

select id, name, email from user where email like '%email'; -- retorna todos os registros onde os valores da coluna informada termina com o dado passado, independente do que vier antes, neste caso não retorna nenhum registro pois não existe nenhum que termine com email

O comando ORDER BY, permite ordenar os registros retornados:

select id, name from user order by id desc; -- ordena de forma decrescente se baseando nos valores da coluna informada

select id, name from user order by id asc; -- ordena de forma crescente se baseando nos valores da coluna informada

O comando GROUP BY, ele nos permite agrupar registros idênticos das colunas informadas:

select name from user GROUP BY name; -- em nossa base, temos dados onde o nome 'jessica' é o mesmo, esse comando vai agrupar os registros duplicados em apenas 1

O comando INNER JOIN, permite juntar tabelas em uma listagem. Com esse comando, somente os registros das tabelas que atendem a condição informada serão listados:

SELECT
    *
FROM
    user
    INNER JOIN product ON product.user_id = user.id;
-- retorna todos os dados das tabelas user e product, onde o valor do campo user_id da tabela product for igual ao campo id da tabela user  

Se olharmos o resultado da consulta acima, percebemos que a coluna name está contida nas duas tabelas, podemos ter algum erro ao tentar manipular essa coluna de alguma das duas tabelas especificamente. Para contornar isso, podemos adicionar apelidos as colunas:

SELECT
    U.name as user,
    U.email as email,
    P.name as product,
    P.value as value
FROM
    user U
    INNER JOIN product P ON P.user_id = U.id;

Um comando bem semelhante ao anterior é o LEFT JOIN, com ele, os registros retornados são todos os que estão na tabela anterior ao comando, e os que atendem a condição:

SELECT
    U.name as user,
    U.email as email,
    P.name as product,
    P.value as value
FROM
    user U
    LEFT JOIN product P ON P.user_id = U.id;
-- traz todos os resultados da tabela user e somente os dados da tabela product que atendem a condição

O comando RIGHT JOIN, faz o inverso do comando anterior. Oberserve bem a query abaixo e os resultados obtidos:

SELECT
    U.name as user,
    U.email as email,
    P.name as product,
    P.value as value
FROM
    product P
    RIGHT JOIN user U ON U.id = P.user_id;
-- traz apenas os dados da tabela product que atendem a condição, e todos os dados da tabela user

Precisei inverter a ordem das tabelas para trazer um resultado mais fácil de ser entendido.

---

A imagem abaixo mostra um resumo dos comandos JOIN, tanto os apresentados aqui, como outros que não mostrei:

---

Podemos fazer consultas utilizando subqueries:

select * from product where user_id = (select id from user where id = 6);

Continuando com subqueries, podemos fazê-las no início de um select:

select *, (select name from user where id = product.user_id) as user from product; --

Nos exemplos de subqueries exibidos, apenas um campo pode ser retornado da subquery.

Atualizando dados

Para atualizar um registro é muito simples, basta informar tabela, os campos e os novos valores:

update user set name = 'marcos', email = '[e-mail]' where id = 1; -- vai atualizar os campos name e email da tabela user onde o id for igual a 1

Lembre-se sempre de iniciar um comando UPDATE pela cláusula WHERE, caso vocês esqueçam do WHERE, todos os registros da tabela serão atualizados. Já tive muitos problemas por causa disso.

Deletando dados

Excluir um registro do nosso banco tão simples quando uma listagem, basta informar a tabela e uma recomendação minha: utilizar em conjunto com a cláusula WHERE.

delete from user where id = 1;

Novamente, lembre-se de iniciar um comando DELETE com a cláusula WHERE. Evite problemas.

Outro detalhe importante é que em nosso caso, não conseguiremos excluir um registro da tabela user, caso exista uma referência na tabela product, é necessário primeiro remover as referências:

delete from user where id = 4;
-- ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`dev_article`.`product`, CONSTRAINT `product_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`));

delete from product where id = 1; -- removemos primeiro o produto

delete from user where id = 4; -- e só então conseguimos remover no registro de user

Se pegarmos uma tabela com a mesma estrutura da tabela user, inserirmos 10 registros, depois apagarmos esses mesmo registros, e inserimos um novo registro novamente, veremos que a chave primária desse registro será 11 e não 1, como pode-se pensar. Isso ocorre por causa do índice da tabela, que sempre vai apontar para o próximo. Para resolver o problema acima, podemos usar o comando abaixo que irá limpar toda nossa tabela, e limpar o índice da mesma:

truncate table product;

Tome muito cuidado ao executar este comando.

Outros comandos...

Galera, gostaria de falar que esses não são os únicos comandos que existem, ok? Os que abordei aqui são os que normalmente mais utilizo. Recomendo um estudo por mais comandos como por exemplo: HAVING, LIMIT, UNION, etc, vasculhem a documentação.

Está sendo bem cansativo fazer estes artigos, é verdade, porém, está sendo muito gratificante, espero que estejam gostando.