<p><img src="https://i.imgur.com/D01Fpky.png" alt="" title="Entre no nosso site e veja nossos cursos"></p>
<h1>
<img class="emoji" alt=":coffee:" src="https://cdn.jsdelivr.net/npm/@hackmd/emojify.js@2.1.0/dist/images/basic/coffee.png"> BD Essencial - Aula 2
</h1>

<p>O objetivo desse curso &eacute; capacitar o aluno a entrar no mercado de trabalho executando as atividades primordiais que s&atilde;o solicitadas no desenvolvimento, manuten&ccedil;&atilde;o, ou atualiza&ccedil;&atilde;o de bancos de dados dos mais diferentes tipos de sistemas.</p>
<p>Focamos na execu&ccedil;&atilde;o, e n&atilde;o em cria&ccedil;&atilde;o de diagramas - atividade essa que &eacute; desenvolvida por profissionais mais experientes.</p>
<p>Nas nossas prieiras aulas, utilizamos o Gerenciador de Bancos de Dados Relacionais <strong>MySQL</strong> nesse nosso curso de Banco de Dados Essencial, porque essa ferramenta &eacute; gratuita e de f&aacute;cil utiliza&ccedil;&atilde;o - entretanto, todo o nosso conte&uacute;do pode ser aplicado em qualquer Gerenciador de Bancos de Dados Relacionais, apenas com algumas m&iacute;nimas diferen&ccedil;as.</p>
<p>Na segunda parte do curso, utilizamos o Gerenciador de Bancos de Dados Relacionais <strong>PostgreSQL</strong>.</p>

<blockquote style="border-left-color: red;">
<p><span class="color" data-color="red"></span><span> </span><small><i class="fa fa-user"></i> Prof Fernando Gomes - [e-mail]</small></p></blockquote>

<h2 id="-Tema-da-aula" data-id="-Tema-da-aula"><a class="anchor hidden-xs" href="#-Tema-da-aula" title="-Tema-da-aula"><span class="octicon octicon-link"></span></a><img class="emoji" alt=":book:" src="https://cdn.jsdelivr.net/npm/@hackmd/emojify.js@2.1.0/dist/images/basic/book.png">
<span> Tema da aula:</span></h2><span>
<p>Nessa segunda aula, vamos falar sobre</p>
<ul>
<li>Enum;</li>
<li>Chave composta;</li>
<li>Relacionamento Many to Many;</li>
<li>VIEW;</li>
<li>STORED PROCEDURE;</li>
<li>TRIGGER;</li>
<li>before, old;</li>
<li>backup de uma tabela;</li>
<li>Count(*);</li>
<li>sum, max e min;</li>
<li>group by;</li>
<li>union all;</li>
<li>date_format;</li>
<li>limit;</li>
<li>time_format</li>
</ul>

<p>Estamos aprendendo <strong>SQL</strong>. A linguagem SQL &eacute; uma s&oacute;, por&eacute;m ela &eacute; dividida em tipos de acordo com a funcionalidade dos comandos.</p>
<p>Os tipos da linguagem <strong>SQL</strong> s&atilde;o:</p>
<p><strong>DDL</strong> - Data Definition Language - Linguagem de Defini&ccedil;&atilde;o de Dados. Possui comandos que interagem com os elementos do banco. S&atilde;o comandos DDL - CREATE, ALTER e DROP</p>
<p><strong>DML</strong> - Data Manipulation Language - Linguagem de Manipula&ccedil;&atilde;o de Dados. Possui comandos que interagem com os dados dentro das tabelas. S&atilde;o comandos DML: INSERT, DELETE e UPDATE</p>
<p><strong>DQL</strong> - Data Query Language - Linguagem de Consulta de dados. Possui o comando de consulta. SELECT &eacute; o comando de consulta. Aqui cabe uma observa&ccedil;&atilde;o: em alguns livros o SELECT fica na DML em outros tem esse grupo pr&oacute;prio.</p>
<p><strong>DTL</strong> - Data Transaction Language - Linguagem de Transa&ccedil;&atilde;o de Dados. S&atilde;o os comandos para controle de transa&ccedil;&atilde;o.<br />S&atilde;o comandos DTL: BEGIN TRANSACTION, COMMIT E ROLLBACK</p>
<p><strong>DCL</strong> - Data Control Language - Linguagem de Controle de Dados. Serve para controlar a parte de seguran&ccedil;a do banco de dados (dar e retirar permiss&otilde;es). S&atilde;o comandos DCL o GRANT, REVOKE E DENY.</p>
<p>&nbsp;</p>

-- Many to Many

drop database if exists  banco2;

create database banco2;

use banco2;

show tables;

create table aluno(
	idAluno int primary key AUTO_INCREMENT,
          nome varchar (35),
          email varchar (50) unique,
          sexo enum ('m','f') 
); 

create table professor(
	idProf int primary key AUTO_INCREMENT,
          nome varchar (35),
          email varchar (50) unique,
          sexo enum ('m','f') 
); 

-- nao pode haver unique em id_prof 
-- porque um mesmo professor pode ter mais de uma turma
create table turma(
	idTurma int primary key AUTO_INCREMENT,
          disciplina varchar (50),
          id_prof int not null,
          foreign key (id_prof) references professor(idProf)
);

insert into aluno(nome, email, sexo) values
('joao', '[e-mail]', 'm'),
('jose', '[e-mail]', 'm'),
('maria', '[e-mail]', 'f'),
('marcia', '[e-mail]', 'f'),
('pedro', '[e-mail]', 'm'),
('patricia', '[e-mail]', 'f');

insert into professor(nome, email, sexo) values
('belem', '[e-mail]', 'm'),
('luciana', '[e-mail]', 'f'),
('fernando', '[e-mail]', 'm');

MariaDB [banco2]> select * from aluno;
+---------+----------+--------------------+------+
| idAluno | nome     | email              | sexo |
+---------+----------+--------------------+------+
|       1 | joao     | [e-mail]     | m    |
|       2 | jose     | [e-mail]     | m    |
|       3 | maria    | [e-mail]    | f    |
|       4 | marcia   | [e-mail]   | f    |
|       5 | pedro    | [e-mail]    | m    |
|       6 | patricia | [e-mail] | f    |
+---------+----------+--------------------+------+
6 rows in set (0.001 sec)

MariaDB [banco2]> select * from professor;
+--------+----------+--------------------+------+
| idProf | nome     | email              | sexo |
+--------+----------+--------------------+------+
|      1 | belem    | [e-mail]    | m    |
|      2 | luciana  | [e-mail]  | f    |
|      3 | fernando | [e-mail] | m    |
+--------+----------+--------------------+------+
3 rows in set (0.000 sec)

insert into turma(disciplina, id_prof) values
('JAVA', 3),
('Angular', 2),
('JAVA FULL STACK', 1);

MariaDB [banco2]> select * from turma;
+---------+-----------------+---------+
| idTurma | disciplina      | id_prof |
+---------+-----------------+---------+
|       1 | JAVA            |       3 |
|       2 | Angular         |       2 |
|       3 | JAVA FULL STACK |       1 |
+---------+-----------------+---------+

-- nesse caso, nao pode haver unique nas chaves estrangeiras
-- chave composta
create table aluno_turma(
          id_turma int not null,
          foreign key (id_turma) references turma(idTurma),
          id_aluno int not null,
          foreign key (id_aluno) references aluno(idAluno),
          primary key (id_turma, id_aluno)
);

insert into aluno_turma(id_turma, id_aluno) values
(1, 1),
(1, 2),
(1, 3),
(2, 4),
(2, 5),
(2, 6),
(3, 2),
(3, 4),
(3, 6);

-- todos os alunos do belem
select a.nome, t.disciplina, p.nome 
    from aluno a
    inner join aluno_turma att
    on a.idAluno = att.id_aluno
    inner join turma t
    on att.id_turma = t.idTurma
    inner join professor p
    on p.idProf = t.id_prof
    where p.nome = 'belem';

MariaDB [banco2]> select a.nome, t.disciplina, p.nome
    ->     from aluno a
    ->     inner join aluno_turma att
    ->     on a.idAluno = att.id_aluno
    ->     inner join turma t
    ->     on att.id_turma = t.idTurma
    ->     inner join professor p
    ->     on p.idProf = t.id_prof
    ->     where p.nome = 'belem';
+----------+-----------------+-------+
| nome     | disciplina      | nome  |
+----------+-----------------+-------+
| jose     | JAVA FULL STACK | belem |
| marcia   | JAVA FULL STACK | belem |
| patricia | JAVA FULL STACK | belem |
+----------+-----------------+-------+

-- mais uma turma para o belem
insert into turma(disciplina, id_prof) values
('ORACLE', 1);

-- mais alunos para essa nova turma
insert into aluno_turma(id_turma, id_aluno) values
(4, 1),
(4, 3),
(4, 5);


MariaDB [banco2]> select a.nome, t.disciplina, p.nome
    ->     from aluno a
    ->     inner join aluno_turma att
    ->     on a.idAluno = att.id_aluno
    ->     inner join turma t
    ->     on att.id_turma = t.idTurma
    ->     inner join professor p
    ->     on p.idProf = t.id_prof
    ->     where p.nome = 'belem';
+----------+-----------------+-------+
| nome     | disciplina      | nome  |
+----------+-----------------+-------+
| jose     | JAVA FULL STACK | belem |
| marcia   | JAVA FULL STACK | belem |
| patricia | JAVA FULL STACK | belem |
| joao     | ORACLE          | belem |
| maria    | ORACLE          | belem |
| pedro    | ORACLE          | belem |
+----------+-----------------+-------+
6 rows in set (0.001 sec)


-- todos os professores de joao
select a.nome, t.disciplina, p.nome 
    from aluno a
    inner join aluno_turma att
    on a.idAluno = att.id_aluno
    inner join turma t
    on att.id_turma = t.idTurma
    inner join professor p
    on p.idProf = t.id_prof
    where a.nome = 'joao';

MariaDB [banco2]> select a.nome, t.disciplina, p.nome
    ->     from aluno a
    ->     inner join aluno_turma att
    ->     on a.idAluno = att.id_aluno
    ->     inner join turma t
    ->     on att.id_turma = t.idTurma
    ->     inner join professor p
    ->     on p.idProf = t.id_prof
    ->     where a.nome = 'joao';
+------+------------+----------+
| nome | disciplina | nome     |
+------+------------+----------+
| joao | ORACLE     | belem    |
| joao | JAVA       | fernando |
+------+------------+----------+

-- VIEW
create or replace view V$AlunoProf as
select a.nome as ALUNO, t.disciplina, 
                p.nome as PROF 
    from aluno a
    inner join aluno_turma att
    on a.idAluno = att.id_aluno
    inner join turma t
    on att.id_turma = t.idTurma
    inner join professor p
    on p.idProf = t.id_prof
    order by p.nome, t.disciplina, a.nome;

select * from V$AlunoProf where ALUNO='joao';
select * from V$AlunoProf where PROF='belem';

-- STORED PROCEDURE
drop procedure if exists mostraLista;
delimiter $$

create procedure mostraLista(nivel varchar(30), nome varchar(30))
begin
 case nivel
    when 'aluno' then
        select * from V$AlunoProf where ALUNO=nome;
    when 'prof' then
        select * from V$AlunoProf where PROF=nome;
    else
    select 'ERRO na passagem de parametros';
end case;
end;
$$
delimiter ;

call mostraLista('prof','belem');
call mostraLista('aluno','joao');


-- TRIGGER
create table alunobackup as select * from aluno  where 1=0;


MariaDB [banco2]> desc alunobackup;
+---------+---------------+------+-----+---------+-------+
| Field   | Type          | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| idAluno | int(11)       | NO   |     | 0       |       |
| nome    | varchar(35)   | YES  |     | NULL    |       |
| email   | varchar(50)   | YES  |     | NULL    |       |
| sexo    | enum('m','f') | YES  |     | NULL    |       |
+---------+---------------+------+-----+---------+-------+
4 rows in set (0.047 sec)

MariaDB [banco2]> desc aluno;
+---------+---------------+------+-----+---------+----------------+
| Field   | Type          | Null | Key | Default | Extra          |
+---------+---------------+------+-----+---------+----------------+
| idAluno | int(11)       | NO   | PRI | NULL    | auto_increment |
| nome    | varchar(35)   | YES  |     | NULL    |                |
| email   | varchar(50)   | YES  | UNI | NULL    |                |
| sexo    | enum('m','f') | YES  |     | NULL    |                |
+---------+---------------+------+-----+---------+----------------+
4 rows in set (0.049 sec)

ALTER TABLE alunobackup MODIFY idAluno int primary key;

MariaDB [banco2]> ALTER TABLE alunobackup MODIFY idAluno int primary key;
Query OK, 0 rows affected (0.096 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [banco2]> desc alunobackup;
+---------+---------------+------+-----+---------+-------+
| Field   | Type          | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| idAluno | int(11)       | NO   | PRI | NULL    |       |
| nome    | varchar(35)   | YES  |     | NULL    |       |
| email   | varchar(50)   | YES  |     | NULL    |       |
| sexo    | enum('m','f') | YES  |     | NULL    |       |
+---------+---------------+------+-----+---------+-------+

delimiter $$
create trigger beforeDel
    before delete on aluno
    for each row
        begin  
        insert into alunobackup values 
        (old.idAluno,  old.nome,  old.email, old.sexo); 
end;
$$
delimiter ;

insert into aluno values 
(1000,'exemplo','[e-mail]','m');
select * from aluno;
select * from alunobackup;

delete from aluno where idAluno = 1000;
select * from alunobackup;

insert into aluno values 
(1001,'exemplo1','[e-mail]','f'),
(1002,'exemplo2','[e-mail]','f'),
(1003,'exemplo3','[e-mail]','f'),
(1004,'exemplo4','[e-mail]','f');

delete from aluno where idAluno>1000;
select * from alunobackup;


drop database if exists  banco3;

create database banco3;

use banco3;

show tables;


create table supervisao(
	id int primary key auto_increment,
	vendedor varchar (50),
	valor    double, 
	dataVenda date,
	supervisao varchar (35),
	sexo enum ('m','f') 
);


insert into supervisao values 
(null,'gabriel',10000,'2015/01/10','luciana','m'); 
insert into supervisao values 
(null,'gabriel',5000,'2015/02/20','luciana','m'); 
insert into supervisao values 
(null,'gabriel',7000,'2015/03/10','luciana','m'); 
insert into supervisao values 
(null,'isabel',6000,'2015/01/10','luciana','f'); 
insert into supervisao values 
(null,'isabel',8000,'2015/02/20','luciana','f'); 
insert into supervisao values 
(null,'isabel',9000,'2015/03/10','luciana','f'); 
insert into supervisao values 
(null,'marcelo',10000,'2015/01/10', 'chaves','m'); 
insert into supervisao values 
(null,'marcelo',12000,'2015/02/20','chaves', 'm'); 
insert into supervisao values 
(null,'marcelo',14000,'2015/03/10','chaves', 'm'); 


select * from supervisao;

--conta o numero de registros da tabela
select  count(*) from supervisao;

+----------+
| count(*) |
+----------+
|        9 |
+----------+

-- max(valor): maior valor da coluna 
-- com o nome entre parenteses
select max(valor) maiorValor from supervisao;

+------------+
| maiorValor |
+------------+
|      14000 |
+------------+

select min(valor) menorValor from supervisao;

+------------+
| menorValor |
+------------+
|       5000 |
+------------+

-- agrupa por vendedor e conta 
-- o numero de registros para cada um
select count(*), vendedor from supervisao 
    group by vendedor;
+----------+----------+
| count(*) | vendedor |
+----------+----------+
|        3 | gabriel  |
|        3 | isabel   |
|        3 | marcelo  |
+----------+----------+

-- agrupamos por vendedor e realizamos uma totalizacao
-- calculamos quanto cada um vendeu somando 
-- os valores do campo 'valor'
select sum(valor) as soma, vendedor 
    from supervisao group by (vendedor);

+-------+----------+
| soma  | vendedor |
+-------+----------+
| 22000 | gabriel  |
| 23000 | isabel   |
| 36000 | marcelo  |
+-------+----------+

-- total de vendas realizadas pelos 
-- vendedores de cada supervisor
select sum(valor) as soma, supervisao 
  from supervisao group by (supervisao);

+-------+------------+
| soma  | supervisao |
+-------+------------+
| 36000 | chaves     |
| 45000 | luciana    |
+-------+------------+

-- para exibir os dois resultados numa consulta
-- precisamos fazer a uniao dos dos conjuntos
-- cada conjunto representado por uma das consultas acima
-- também precisamos apresentar os resultados 
-- em colunas com nomes diferentes

select sum(valor) as soma, vendedor, '' as supervisao 
  from supervisao group by (vendedor)
union all
select sum(valor) as soma, '' as vendedor, supervisao 
  from supervisao group by (supervisao);

+-------+----------+------------+
| soma  | vendedor | supervisao |
+-------+----------+------------+
| 22000 | gabriel  |            |
| 23000 | isabel   |            |
| 36000 | marcelo  |            |
| 36000 |          | chaves     |
| 45000 |          | luciana    |
+-------+----------+------------+

-- formatando datas e colocando em ordem descrescente
-- selecionando apenas as 5 primeiras datas
select date_format(dataVenda,'%d/%m/%y') 
    as data from supervisao
    order by data desc
    limit 5;

+----------+
| data     |
+----------+
| 20/02/15 |
| 20/02/15 |
| 20/02/15 |
| 10/03/15 |
| 10/03/15 |
+----------+

-- union all novamente; dessa vez, 
-- agrupamos e totalizamos 
-- por vendedor e por mes

select sum(valor) as soma, vendedor, '' as mes
  from supervisao group by (vendedor)
union all
select sum(valor) as soma, '' as vendedor,  
    date_format(dataVenda,'%m') as mes 
    from supervisao group by (mes);

+-------+----------+------+
| soma  | vendedor | mes  |
+-------+----------+------+
| 22000 | gabriel  |      |
| 23000 | isabel   |      |
| 36000 | marcelo  |      |
| 26000 |          | 01   |
| 25000 |          | 02   |
| 30000 |          | 03   |
+-------+----------+------+

MariaDB [banco3]> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-03-21 23:09:31 |
+---------------------+

select time_format(now(),'%h:%i:%s %p');

+----------------------------------+
| time_format(now(),'%h:%i:%s %p') |
+----------------------------------+
| 11:16:07 PM                      |
+----------------------------------+

/* 
%f	Microseconds (000000 to 999999)
%H	Hour (00 to 23)
%h	Hour (00 to 12)
%I	Hour (00 to 12)
%i	Minutes (00 to 59)
%p	AM or PM
%r	Time in 12 hour AM or PM format (hh:mm:ss AM/PM)
%S	Seconds (00 to 59)
%s	Seconds (00 to 59)
%T	Time in 24 hour format (hh:mm:ss) 
*/

<!--
<h3>
Tipos de Dados Numéricos do MySQL
</h3>

<p>
Na tabela abaixo vemos os tipos da dados numéricos aceitos no MySQL. Utilizamos, BIT ou TINYINT(1) para armazenar um campo booleano. Além dessa situação, os mais utilizados são TINYINT, INT, e DOUBLE. Por padrão, os campos admitem números com sinal, portanto, o padrão [default] é SIGNED.
</p>

-->

<blockquote style="border-left-color: red;">
<p><span class="color" data-color="red"></span><span> </span><small><i class="fa fa-user"></i> Prof Fernando Gomes - [e-mail]</small></p></blockquote>

<br>
<div class="alert alert-info">
<blockquote style="border-left-color: blue;">
<p><span class="color" data-color="blue"></span><span> </span><small><i class="fa fa-user"></i> E.B. Cursos https://www.cursoseb.com.br/</small><br>
<span class="color" data-color="blue"></span><span> </span><small><i class="fa fa-user"></i> E.B. Cursos EAD https://edsonbelemtreinamento.com.br/ead/</small></p>
</blockquote>
</div>