>_Banco de dados SQL – Delete – Update – As – Count – Order By – Sum – Avg – 1.2

Comando DELETE

O comando DELETE é usado para apagar registros de uma tabela em um banco de dados.
Sintaxe
1:DELETE FROM nome_tabela
2:WHERE nome_coluna = valor_coluna

Nota: Observe a cláusula WHERE na sintaxe do DELETE. A cláusula WHERE especifica que registro ou registros devem ser apagados. Se você omitir a cláusula WHERE, todos os registros serão excluídos!

Deletando da tabela COMPRAS tudo que for menor que 01-01-2009 no campo DATA

mysql> DELETE FROM COMPRAS WHERE DATA < '2009-01-01';
Query OK, 7 rows affected (0.06 sec)

Comando UPDATE

A sintaxe básica do comando UPDATE é a seguinte:

UPDATE nome_tabela SET nome_coluna1=expr1 [, nome_coluna2=expr2 …] [WHERE definição_where]

UPDATE é aplicado a uma tabela e a cláusula SET atribui a um campo o valor de uma expressão que pode ou não conter o valor de um campo da própria tabela. A cláusula WHERE restringe as atualizações apenas aos registro que satisfação suas condições.

Atualizando a tabela COMPRAS no campo OBSERVACOES inserindo o texto ‘preparando o natal’ com a condição no campo DATA com o intervalo de 20-12-2010 a 25-12-2010

mysql> UPDATE COMPRAS SET OBSERVACOES = 'preparando o natal' WHERE DATA IN ('2010-12-20','2010-12-25');

Verificando a alteração

mysql> SELECT * FROM COMPRAS WHERE DATA IN ('2010-12-20','2010-12-25');
+----+--------+------------+--------------------+----------+
| ID | VALOR  | DATA       | OBSERVACOES        | RECEBIDO |
+----+--------+------------+--------------------+----------+
| 36 |  253.7 | 2010-12-20 | preparando o natal |        0 |
| 37 | 370.15 | 2010-12-25 | preparando o natal |        0 |
+----+--------+------------+--------------------+----------+
2 rows in set (0.00 sec)

Deletando na tabela COMPRAS com a condição no campo OBSERVACOES procurando pelo texto ‘COMPRS COMUM’

mysql> DELETE FROM COMPRAS WHERE OBSERVACOES = 'COMPRAS COMUM';

Alterando o campo VALOR de todas as compras feitas antes de 01/06/2009, somando R$10,00 ao valor atual

mysql> UPDATE COMPRAS SET VALOR =  VALOR + '10.00' WHERE DATA  <= '2009-06-01';

Atualizando todas as compras feitas entre 01/07/2009 e 01/07/2010 para que elas tenham a observação ‘entregue antes de 2011’ e a coluna recebido com o valor TRUE.

mysql> UPDATE COMPRAS SET OBSERVACOES = 'entregue antes de 2011',RECEBIDO = '1' WHERE DATA BETWEEN '2009-07-09' AND '2010-07-01';

Excluindo as compras realizadas entre 05 e 20 março de 2009

mysql> SELECT * FROM COMPRAS WHERE DATA BETWEEN '2009-03-05' AND '2009-03-20';

Selecionando a tabela COMPRAS com a condição no campo DATA não exibindo a data 03-11-2011

mysql> SELECT * FROM COMPRAS WHERE DATA NOT IN ('2011-11-03');

Comando ALTER TABLE

O comando básico para se alterar colunas é o ALTER TABLE, seguido do nome da tabela

Alterando a tabela COMPRAS modificando o campo OBSERVACOES para TEXT e NOT NULL não exibindo o NULL

mysql> ALTER TABLE COMPRAS MODIFY COLUMN OBSERVACOES TEXT NOT NULL;
mysql> SELECT * FROM COMPRAS;
+----+----------+---------------------+------------------------------+----------+
| id | valor    | data                | OBSERVACOES                  | recebido |
+----+----------+---------------------+------------------------------+----------+
|  1 |   200.00 | 2008-02-19 00:00:00 | MATERIAL ESCOLAR             |        1 |
|  2 |  3500.00 | 2008-05-21 00:00:00 | TELEVISAO                    |        0 |
|  3 |  1576.40 | 2008-04-30 00:00:00 | MATERIAL DE CONSTRUCAO       |        1 |
|  4 |   163.45 | 2008-12-15 00:00:00 | PIZZA PRA FAMILIA            |        1 |
|  5 |  4780.00 | 2009-01-23 00:00:00 | SALA DE ESTAR                |        1 |
|  6 |   392.15 | 2009-03-03 00:00:00 | QUARTOS                      |        1 |
|  7 |  1203.00 | 2009-03-18 00:00:00 | QUARTOS                      |        1 |
|  8 |   402.90 | 2009-03-21 00:00:00 | COPA                         |        1 |
|  9 |    54.98 | 2009-04-12 00:00:00 | LANCHONETE                   |        0 |
| 10 |    12.34 | 2009-05-23 00:00:00 | LANCHONETE                   |        0 |
| 11 |    78.65 | 2009-12-04 00:00:00 | LANCHONETE                   |        0 |
| 12 |    12.39 | 2009-01-06 00:00:00 | SORVETE NO PARQUE            |        0 |
| 13 |    98.12 | 2009-07-09 00:00:00 | HOPI HARI                    |        1 |
| 14 |  2498.00 | 2009-01-12 00:00:00 | COMPRAS DE JANEIRO           |        1 |
| 15 |  3212.40 | 2009-11-13 00:00:00 | COMPRAS DO MES               |        1 |
| 16 |   223.09 | 2009-12-17 00:00:00 | COMPRAS DE NATAL             |        1 |
| 17 |   768.90 | 2009-01-16 00:00:00 | FESTA                        |        1 |
| 18 |   827.50 | 2010-01-09 00:00:00 | FESTA                        |        1 |
| 19 |    12.00 | 2010-02-19 00:00:00 | SALGADO NO AEROPORTO         |        1 |
| 20 |   678.43 | 2010-05-21 00:00:00 | PASSAGEM PRA BAHIA           |        1 |
| 21 | 10937.12 | 2010-04-30 00:00:00 | CARNAVAL EM CANCUN           |        1 |
| 22 |  1501.00 | 2010-06-22 00:00:00 | PRESENTE DA SOGRA            |        0 |
| 23 |  1709.00 | 2010-08-25 00:00:00 | PARCELA DA CASA              |        0 |
| 24 |   567.09 | 2010-09-25 00:00:00 | PARCELA DO CARRO             |        0 |
| 25 |   631.53 | 2010-10-12 00:00:00 | IPTU                         |        1 |
| 26 |   909.11 | 2010-02-11 00:00:00 | IPVA                         |        1 |
| 27 |   768.18 | 2010-04-10 00:00:00 | GASOLINA VIAGEM PORTO ALEGRE |        1 |
| 28 |   434.00 | 2010-04-01 00:00:00 | RODEIO INTERIOR DE SAO PAULO |        0 |
| 29 |   115.90 | 2010-06-12 00:00:00 | DIA DOS NAMORADOS            |        0 |
| 30 |    98.00 | 2010-10-12 00:00:00 | DIA DAS CRIANÇAS             |        0 |
| 31 |   253.70 | 2010-12-20 00:00:00 | NATAL                        |        0 |
| 32 |   370.15 | 2010-12-25 00:00:00 | NATAL                        |        0 |
| 33 |   434.00 | 2010-12-25 00:00:00 | NATAL                        |        0 |
| 34 |    32.09 | 2011-07-02 00:00:00 | LANCHONETE                   |        1 |
| 35 |   954.12 | 2011-11-03 00:00:00 | SHOW DA IVETE SANGALO        |        1 |
| 36 |    98.70 | 2011-02-07 00:00:00 | LANCHONETE                   |        1 |
| 37 |   213.50 | 2011-09-25 00:00:00 | ROUPAS                       |        0 |
| 38 |  1245.20 | 2011-10-17 00:00:00 | ROUPAS                       |        0 |
| 39 |    23.78 | 2011-12-18 00:00:00 | LANCHONETE DO ZÉ             |        1 |
| 40 |   576.12 | 2011-09-13 00:00:00 | SAPATOS                      |        1 |
| 41 |    12.34 | 2011-07-19 00:00:00 | CANETAS                      |        0 |
| 42 |    87.43 | 2011-05-10 00:00:00 | GRAVATA                      |        0 |
| 43 |   887.66 | 2011-02-02 00:00:00 | PRESENTE PARA O FILHAO       |        1 |
+----+----------+---------------------+------------------------------+----------+
43 rows in set (0.00 sec)

Alterando a tabela COMPRAS modificando o campo RECEBIDO setando com DEFAULT o valor 0

mysql> ALTER TABLE COMPRAS MODIFY COLUMN RECEBIDO TINYINT(1) DEFAULT '0';
mysql> INSERT INTO COMPRAS (VALOR, DATA, OBSERVACOES) VALUES ('10.50','2014-06-24','COMPRA CELULAR');
mysql> SELECT * FROM COMPRAS WHERE OBSERVACOES = 'COMPRA CELULAR';
+----+-------+---------------------+----------------+----------+
| id | valor | data                | OBSERVACOES    | RECEBIDO |
+----+-------+---------------------+----------------+----------+
| 44 | 10.50 | 2014-06-24 00:00:00 | COMPRA CELULAR |        0 |
+----+-------+---------------------+----------------+----------+
1 row in set (0.00 sec)

Alterando a tabela COMPRAS adicionando o campo FORMA_PAGT, aceitando apenas três opções

mysql> ALTER TABLE COMPRAS ADD COLUMN FORMA_PAGT ENUM('CARTAO','BOLETO','DINHEIRO');
mysql> INSERT INTO COMPRAS (VALOR, DATA, OBSERVACOES, FORMA_PAGT) VALUES ('10.50','2014-06-24','COMPRA CELULAR','CARTAO');
mysql> SELECT * FROM COMPRAS WHERE FORMA_PAGT = 'CARTAO';
+----+-------+---------------------+----------------+----------+------------+
| id | valor | data                | OBSERVACOES    | RECEBIDO | FORMA_PAGT |
+----+-------+---------------------+----------------+----------+------------+
| 45 | 10.50 | 2014-06-24 00:00:00 | COMPRA CELULAR |        0 | CARTAO     |
+----+-------+---------------------+----------------+----------+------------+
1 row in set (0.00 sec)

Alterando a tabela COMPRAS utilizando o comando CHANGE corrigindo o nome do campo FORMA_PAGT para FORMA_PAGT0

mysql> ALTER TABLE COMPRAS CHANGE FORMA_PAGT FORMA_PAGTO 
ENUM('CARTAO','BOLETO','DINHEIRO');
mysql> SELECT * FROM COMPRAS WHERE FORMA_PAGT = 'CARTAO';
ERROR 1054 (42S22): Unknown column 'FORMA_PAGT' in 'where clause'
mysql> SELECT * FROM COMPRAS WHERE FORMA_PAGTO = 'CARTAO';
+----+-------+---------------------+----------------+----------+-------------+
| id | valor | data                | OBSERVACOES    | RECEBIDO | FORMA_PAGTO |
+----+-------+---------------------+----------------+----------+-------------+
| 45 | 10.50 | 2014-06-24 00:00:00 | COMPRA CELULAR |        0 | CARTAO      |
+----+-------+---------------------+----------------+----------+-------------+
1 row in set (0.00 sec)

Atualizando a tabela COMPRAS no campo FORMA_PAGTO definindo tudo como ‘CARTAO’

mysql> UPDATE COMPRAS SET FORMA_PAGTO = 'CARTAO';

Função SUM

A função agregada SUM, soma todas os campos selecionados
Selecionando a tabela COMPRAS somando todos os dados do campo VALOR

mysql> SELECT SUM(VALOR) FROM COMPRAS;
+------------+
| SUM(VALOR) |
+------------+
|   43585.92 |
+------------+
1 row in set (0.05 sec)

Função AVG

A função agregada AVG, tira a média de todos os campos selecionados
Selecionando a tabela COMPRAS tirando a média dos dados do campo VALOR

mysql> SELECT AVG(VALOR) FROM COMPRAS;
+------------+
| AVG(VALOR) |
+------------+
| 947.520000 |
+------------+
1 row in set (0.02 sec)
mysql> SELECT AVG(VALOR), SUM(VALOR) FROM COMPRAS;
+------------+------------+
| AVG(VALOR) | SUM(VALOR) |
+------------+------------+
| 947.520000 |   43585.92 |
+------------+------------+
1 row in set (0.00 sec)

Função AS

A Função AS tem como objetivo criar alias de um campo já selecionado
Selecionando a tabela COMPRAS tirando a média AVG e também a soma SUM do campo VALOR, criando o alias MEDIA e VALOR

mysql> SELECT AVG(VALOR) AS MEDIA, SUM(VALOR) AS SOMA FROM COMPRAS;
+------------+----------+
| MEDIA      | SOMA     |
+------------+----------+
| 947.520000 | 43585.92 |
+------------+----------+
1 row in set (0.00 sec)
mysql> SELECT AVG(VALOR) AS MEDIA, SUM(VALOR) AS SOMA FROM COMPRAS WHERE DATA < '2010-01-01';
+-------------+----------+
| MEDIA       | SOMA     |
+-------------+----------+
| 1128.045294 | 19176.77 |
+-------------+----------+
1 row in set (0.00 sec)

Função COUNT

A função de agregação COUNT, tem como objetivo retorna o número total de linhas
Selecionando o total de linhas da tabela COMPRAS no campo VALOR

mysql> SELECT COUNT(VALOR) FROM COMPRAS;
+--------------+
| COUNT(VALOR) |
+--------------+
|           46 |
+--------------+
1 row in set (0.03 sec)
mysql> SELECT COUNT(VALOR) AS QUANTIDADE FROM COMPRAS;
+------------+
| QUANTIDADE |
+------------+
|         46 |
+------------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(VALOR) AS QUANTIDADE FROM COMPRAS WHERE DATA > '2010-01-31'; 
+------------+
| QUANTIDADE |
+------------+
|         28 |
+------------+
1 row in set (0.00 sec)

Função DISTINCT COUNT

A função DISTINCT COUNT tem como objetivo retornar o número de linhas distintas, ou seja, as linhas com valores diferentes

mysql> SELECT DATA FROM COMPRAS WHERE DATA >= '2010-01-02' AND RECEBIDO = false;
+---------------------+
| DATA                |
+---------------------+
| 2010-06-22 00:00:00 |
| 2010-08-25 00:00:00 |
| 2010-09-25 00:00:00 |
| 2010-04-01 00:00:00 |
| 2010-06-12 00:00:00 |
| 2010-10-12 00:00:00 |
| 2010-12-20 00:00:00 |
| 2010-12-25 00:00:00 |
| 2010-12-25 00:00:00 |
| 2011-09-25 00:00:00 |
| 2011-10-17 00:00:00 |
| 2011-07-19 00:00:00 |
| 2011-05-10 00:00:00 |
| 2014-06-24 00:00:00 |
| 2014-06-24 00:00:00 |
| 2014-06-24 00:00:00 |
+---------------------+
16 rows in set (0.00 sec)

mysql> SELECT COUNT(DATA) FROM COMPRAS WHERE DATA >= '2010-01-02' AND RECEBIDO = false;
+-------------+
| COUNT(DATA) |
+-------------+
|          16 |
+-------------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(DISTINCT DATA) FROM COMPRAS WHERE DATA >= '2010-01-02' AND RECEBIDO = false;
+----------------------+
| COUNT(DISTINCT DATA) |
+----------------------+
|                   13 |
+----------------------+
1 row in set (0.00 sec)

Selecionando a tabela COMPRAS somando todos os dados do campo VALOR com a condição que no campo RECEBIDO esteja com o valor True (1)

mysql> SELECT SUM(VALOR) AS SOMA FROM COMPRAS WHERE RECEBIDO = 1;
+----------+
| SOMA     |
+----------+
| 32854.75 |
+----------+
1 row in set (0.00 sec)

Selecionando a tabela COMPRAS somando todos os dados do campo VALOR com a condição que no campo RECEBIDO esteja com o valor False (0)

mysql> SELECT SUM(VALOR) AS SOMA FROM COMPRAS WHERE RECEBIDO = 0;
+----------+
| SOMA     |
+----------+
| 10731.17 |
+----------+
1 row in set (0.00 sec)

Função GROUP BY

O GROUP BY é utilizado em conjunto com as funções de agregação para agrupar seu ResultSet por uma ou mais colunas
Selecionando a tabela COMPRAS, somando o campo VALOR criando o alias SOMA ordenando pelo campo RECEBIDO

mysql> SELECT SUM(VALOR) AS SOMA FROM COMPRAS GROUP BY RECEBIDO;
+----------+
| SOMA     |
+----------+
| 10731.17 |
| 32854.75 |
+----------+
2 rows in set (0.03 sec)
mysql> SELECT RECEBIDO, SUM(VALOR) AS SOMA FROM COMPRAS GROUP BY RECEBIDO;
+----------+----------+
| RECEBIDO | SOMA     |
+----------+----------+
|        0 | 10731.17 |
|        1 | 32854.75 |
+----------+----------+
2 rows in set (0.00 sec)
mysql> SELECT RECEBIDO, SUM(VALOR) AS SOMA FROM COMPRAS WHERE DATA < '2010-01-31' GROUP BY RECEBIDO;
+----------+----------+
| RECEBIDO | SOMA     |
+----------+----------+
|        0 |  3658.36 |
|        1 | 16345.91 |
+----------+----------+
2 rows in set (0.00 sec)

O MySQL sempre apresenta os resultados em ordem ascendente, ou seja, se tivermos tratando de dados de texto, na ordem de ‘A’ a ‘Z’. Se desejarmos visualizar em ordem inversa, adicionamos o termo DESC na cláusula ORDER BY

mysql> SELECT RECEBIDO, SUM(VALOR) AS SOMA FROM COMPRAS WHERE DATA < '2010-01-31' GROUP BY RECEBIDO ORDER BY SOMA DESC;
+----------+----------+
| RECEBIDO | SOMA     |
+----------+----------+
|        1 | 16345.91 |
|        0 |  3658.36 |
+----------+----------+
2 rows in set (0.00 sec)

Em orderm ascendente ASC

mysql> SELECT RECEBIDO, SUM(VALOR) AS SOMA FROM COMPRAS WHERE DATA < '2010-01-31' GROUP BY RECEBIDO ORDER BY SOMA ASC;
+----------+----------+
| RECEBIDO | SOMA     |
+----------+----------+
|        0 |  3658.36 |
|        1 | 16345.91 |
+----------+----------+
2 rows in set (0.00 sec)
mysql> SELECT RECEBIDO, AVG(VALOR) AS MEDIA FROM COMPRAS WHERE DATA < '2009-05-12'; 
+----------+-------------+
| RECEBIDO | MEDIA       |
+----------+-------------+
|        1 | 1296.014167 |
+----------+-------------+
1 row in set (0.00 sec)
mysql> SELECT FORMA_PAGTO, SUM(VALOR) FROM COMPRAS WHERE DATA < '2010-10-10' GROUP BY FORMA_PAGTO;
+-------------+------------+
| FORMA_PAGTO | SUM(VALOR) |
+-------------+------------+
| CARTAO      |   37636.10 |
+-------------+------------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(RECEBIDO) AS 'TOTAL RECEBIDO' FROM COMPRAS WHERE DATA < '2009-05-12' AND RECEBIDO = 1;
+----------------+
| TOTAL RECEBIDO |
+----------------+
|              9 |
+----------------+
1 row in set (0.00 sec)
SELECT COUNT(ID) FROM COMPRAS WHERE DATA < '2009-05-12' AND RECEBIDO = 1

Repare que usamos COUNT(ID). Para a função COUNT não importa qual coluna você vai usar para contar. Por esse motivo, muitos programadores passam a constante 1 para a função COUNT, obtendo o mesmo resultado:

SELECT COUNT(1) FROM COMPRAS WHERE DATA < '2009-05-12' AND RECEBIDO = 1
mysql> SELECT RECEBIDO, FORMA_PAGTO, SUM(VALOR) FROM COMPRAS GROUP BY FORMA_PAGTO,RECEBIDO;
+----------+-------------+------------+
| RECEBIDO | FORMA_PAGTO | SUM(VALOR) |
+----------+-------------+------------+
|        0 | NULL        |      10.50 |
|        0 | CARTAO      |   10720.67 |
|        1 | CARTAO      |   32854.75 |
+----------+-------------+------------+
3 rows in set (0.01 sec)
>_Banco de dados SQL – Delete – Update – As – Count – Order By – Sum – Avg – 1.2
Tagged on:

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

%d blogueiros gostam disto: