Skip to content

Instantly share code, notes, and snippets.

@FernandoLins8

FernandoLins8/sql.md

Created October 30, 2020 21:08
Show Gist options
  • Save FernandoLins8/8c8eb90b4335ec3ae4e0607b5657ad31 to your computer and use it in GitHub Desktop.
Save FernandoLins8/8c8eb90b4335ec3ae4e0607b5657ad31 to your computer and use it in GitHub Desktop.
Download ZIP
Notas do curso de MySQL do Curso em Vídeo
Raw
sql.md

Notas feitas com base no curso de Banco de Dados MySQL do canal Curso em Video.

1 - Criando a Primeira Tabela

Login:

mysql -u root -p

Tipos

  • Numerico
    • Inteiro (TinyInt, SmallInt, Int, MediumInt, BigInt)
    • Real (Decimal, Float, Double, Real)
    • Logico (Bit, Boolean)
  • Data/Tempo
    • (Date, DateTime, TimeStamp, Time, Year)
  • Literal
    • Caractere (Char, VarChar)
    • Text (TinyText, Text, MediumText, LongText)
    • Binario (TinyBlob, Blob, MediumBlob, LongBlob)
    • Coleção (Enum, Set)
  • Espacial
    • (Geometry, Point, Polygon, MultiPolygon)

Primeira Tabela

Comandos úteis

Criar uma base de dados:

create database DATABASE_NAME;
create schema DATABASE_NAME;

Remover base de dados:

drop database DATABASE_NAME; 
drop schema DATABASE_NAME;

Mostrar bases de dados:

show databases;
show schemas;

Selecionar base de dados:

use DATABASE_NAME;

Criar tabela:

create table TABLE_NAME(COLUMN1 DATATYPE, COLUMN2 DATATYPE);

Remover tabela:

drop table TABLE_NAME;

Mostra tabelas na base de dados atual ou selecionada:

show tables;
-- ou 
show tables from DATABASE_NAME;

Mostrar estrutura da tabela:

desc TABLE_NAME;

Criando e selecionando a database:

create database teste;
use teste;

Criando a tabela pessoas:

create table pessoas(
id int auto_increment,
nome varchar(30) not null,
nascimento date,
sexo enum('M', 'F'),
peso decimal(5,2),
altura decimal(3,2),
nacionalidade varchar(20) default 'Brasil',
primary key(id));

Observações

  • auto_increment - ao usar default ao inserir linhas na tabela o valor de id será incrementado.
  • not null - por padrão as colunas da tabela aceitam valores "null" (vazios), especificando "not null" fazemos com que essa coluna obrigatoriamente seja preenchida com algum valor.
  • date - o tipo date armazena datas no formato 'aaaa-mm-dd', ex.: '1999-07-26';
  • enum - com enum('M', 'F') especificamos os valores 'M' ou 'F'.
  • decimal - com decimal(x, y) especificamos um número de x dígitos e com y dígitos após a vírgula, ex.: decimal(5, 2) armazena números como "100.50" ou "65.00", enquanto decimal(3, 2) armazena números como "1.75".
  • default - um valor padrão para a coluna a ser usado quando não especificado.

2 - Alterando a Estrutura

Adicionando a coluna profissao na tabela pessoas:

alter table pessoas add profissao varchar(20) after nome;

Opções do alter table:

alter table TABLE_NAME

Para adicionar uma coluna no final da tabela, começo (usando "first" no final do comando) ou após uma coluna especifica (usando no final do comando "after" e o nome da coluna anterior):

add column COLUMN_NAME datatype;

Para remover uma coluna:

drop column COLUMN_NAME;

Para modificar o tipo de uma coluna específica:

modify COLUMN_NAME NEW_TYPE;

Para adicionar uma chave primária:

add primary key(COLUMN_NAME);

Para adicionar uma chave estrangeira:

add foreign key(COLUMN_NAME) references TABLE_NAME(COLUMN_NAME);

Para renomear a tabela:

rename to NEW_TABLE_NAME;

3 - Inserindo Dados

insert into pessoas values
(1, 'Joao', 'Professor', '2000-1-1', 'M', '70', '1.78', 'Brasil'),
(default, 'Maria', 'Programador', null, 'F', '65', '1.66', default);

insert into pessoas (id, nome, sexo) values
(default, 'teste', 'F');

Observações

Para verificar o resultado podemos usar:

select * from pessoas;

Podemos usar truncate para remover todos os registros de uma tabela sem apagar sua estrutura:

truncate NAME_TABLE;

Na primeira forma os valores de todas as colunas da tabela devem ser especificados. Na segunda forma especificamos as colunas com os valores que queremos adicionar, dessa forma, será adicionada uma linha com o id default (auto_inc), o nome "teste" e sexo 'F', para as demais colunas os valores serão null ou os valores default especificados.

4 - Manipulando Linhas

Criando tabela cursos

create table if not exists cursos(
	id int,
	nome varchar(20) not null unique, 
	descrição text, 
	carga int unsigned, 
	totalaulas int unsigned, 
	ano year default 2019,
	primary key(id));

Inserindo valores na tabela cursos

insert into cursos values
('1', 'HTML4', 'Curso de HTML5', '40', '37', '2014'),
('2', 'Algoritmos', 'Lógica de Programação', '20', '15', '2014'),
('3', 'Photoshop', 'Dicas de Photoshop CC', '10', '8', '2014'),
('4', 'PGP', 'Curso de PHP para iniciantes', '40', '20', '2010'),
('5', 'Jarva', 'Introdução à Linguagem Java', '10', '29', '2000'),
('6', 'MySQL', 'Bancos de Dados MySQL', '30', '15', '2016'),    
('7', 'Word', 'Curso completo de Word', '40', '30', '2016'),    
('8', 'Sapateado', 'Danças Rítmicas', '40', '30', '2018'),      
('9', 'Cozinha Árabe', 'Aprenda a fazer Kibe', '40', '30', '2018'), 
('10', 'Youtuber', 'Gerar polêmica e ganhar inscritos', '5', '2', '2018');

Corrigindo valores

update cursos
	set nome = 'HTM5'
	where id = '1';

update cursos
	set nome = 'PHP', ano = '2015'
	where id = '4';

update cursos
	set nome = 'Java', carga = '40', ano = '2015'
	where id = '5'
	limit 1;

Removendo registros

delete from cursos where ano = '2018';

Removendo todos os registros (sem apagar a estrutura da tabela)

truncate TABLE_NAME;

5 - Importando base de dados

  • Checar o arquivo cadastro.sql em um editor de texto.

No shell do mySQL digitar: source PATH/cadastro.sql.

Com isso será selecionada a base de dados cadastro (será criada caso não exista). As tabelas "cursos" e "pessoas" serão removidas caso existam e serão criadas novas tabelas "cursos" e "pessoas" com seus dados inseridos em seguida.

6 - Select (I)

Comando Select

O comando select retorna linhas de uma tabela, permitindo especificar tanto as colunas a serem retornadas como uma condição para filtrar as linhas.

Para retornar todas as colunas:

select * from cursos;

Especificando colunas a serem adicionadas:

select nome, ano from cursos;

Order by

Orderna os valores retornados pelo select por uma coluna específica, além de especificar asc (crescente) ou desc (decrescente).

select * from cursos order by nome asc;
select * from cursos order by nome desc;

Distinct

Usado para retornar apenas valores distintos.

Selecionando os valores de nacionalidade na tabela pessoas (sem repetições):

select distinct nacionalidade from pessoas;

Limit

Limita o número de linhas retornadas.

Retornando apenas 5 linhas:

select * from cursos limit 5;

Where

Usado para filtrar linhas que atendam a uma determinada condição.

Selecionando apenas cursos com ano = "2016":

select nome, carga from cursos where ano = '2016';

Operadores Relacionais

Os operadores relacionais são: <, <=, >, >=, =, !=.

Selecionando cursos com ano diferente de 2016 (outro operador pra diferença é <>):

select * from cursos where ano != 2016;

Operadores Booleanos

Os operadores booleanos são: and, or, not.

Selecionando cursos do ano 2014 ou 2016:

select * from cursos where ano = 2014 or ano = 2016;

Between

Seleciona valores em um determinado intervalo.

Selecionando cursos com o ano entre 2014 e 2016 (notar que inclui tanto 2014 como 2016).

select * from cursos where ano between 2014 and 2016;

In

Usado para especificar multiplos valores.

Selecionando cursos onde o ano é 2016 ou 2018:

select * from cursos where ano in (2016, 2018);

Like

Usado no where para selecionar padrão especifico de texto.

  • %: usado para especificar zero ou mais caracteres.
  • _: usado para especificar exatamente um caractere.

Selecionando cursos com o nome que comece com "a":

select ano, nome from cursos where nome like 'a%';

Selecionando as pessoas com "silva" no nome:

select * from pessoas where nome like '%silva%';

Selecionando pessoas com um "a" como segunda letra no nome:

select * from pessoas where nome like '_a%';

Selecionando pessoas com um "a" como segunda letra no nome e com o nome contendo duas letras apenas (empty set):

select * from pessoas where nome like '_a';

Com datas:

Selecionando pessoas nascidas no ano '1999' (1999-mm-dd):

select * from pessoas where nascimento like '1999%';

Selecionando pessoas nascidas nos anos '199_':

select * from pessoas where nascimento like '199_%';

Is null

Verificamos se um valor é nulo ou não usando "is null" ou "is not null".

Adicionando um valor com apenas idcurso e nome para o exemplo:

insert into cursos (idcurso, nome) values
(0, 'Em breve');

Selecionando cursos sem descricao:

select * from cursos where descricao is null;

Removendo o curso adicionado:

delete from cursos where idcurso = 0;

7 - Select (II)

Funções de agregação

Funções de agregação: count(), max(), min(), sum(), avg().

Count

Retorna o número de linhas.

Retornando o número de pessoas com o valor de nacionalidade "Brasil":

select count(*) from pessoas where nacionalidade = 'Brasil';

Retornando o número de cursos feitos em 2017:

select count(*) from cursos where  ano = 2017;

Max

Retorna o valor máximo em uma determinada coluna.

Retornando o número de aulas do curso com mais aulas:

select max(totaulas) from cursos;

Retornando a altura da pessoa mais alta:

select max(altura) from pessoas;

Min

Retorna o valor mínimo em uma determinada coluna.

Retornando a carga do curso de menor carga horária:

select min(carga) from cursos;

Retornando o peso da pessoa com menor peso:

select min(peso) from pessoas

Sum

Retorna a soma dos valores de uma determinada coluna.

Retornando o total de aulas dos cursos de 2010:

select sum(totaulas) from cursos where ano = '2010';

Avg

Retorna a média dos valores de uma determinada coluna.

Retornando a média de peso:

select avg(peso) from pessoas;

Funções de agregação em subconsultas.

Retornando as pessoas com peso maior que a media:

select * from pessoas where peso > (select avg(peso) from pessoas);

Renomeando colunas retornadas pelo select:

select avg(peso) 'Peso Medio' from pessoas;

Group By

Com a cláusula group by, podemos agrupar linhas com os mesmo valores em resumos, como por exemplo agrupar pessoas por país e mostrar a quantidade para cada. Group by é comumente usado com funções de agregação.

Agrupando os anos e mostrando a quantidade de cursos em cada ano:

select ano, count(*) from cursos group by ano order by count(*);

Agrupando profissões e mostrando a quantidade de pessoas em cada uma:

select profissao, count(*) from pessoas group by profissao;

Having

Podemos usar having de forma semelhante ao where.

  • Where: a condição é executada antes do agrupamento.
  • Having: a condição descrita no having é executada após o agrupamento.

Retornando a quantidade de pessoas por profissão, selecionando apenas as profissões com mais de 5 pessoas:

select profissao, count(*) from pessoas group by profissao having count(*) > 5;

Retornando as nacionalidades com sua media de altura e número de pessoas:

select nacionalidade, avg(altura), count(*) from pessoas group by nacionalidade;

O mesmo que o de cima, filtrando antes de agrupar as pessoas menores que a media:

select nacionalidade, avg(altura), count(*) from pessoas where altura > (select avg(altura) from pessoas) group by nacionalidade;

Com o having, após agrupar os países que possuem a media menor do que a media geral são filtrados:

select nacionalidade, avg(altura), count(*) from pessoas group by nacionalidade having avg(altura) > (select avg(altura) from pessoas);

8 - Junções

Uma junção é usada para combinar linhas de duas ou mais tabelas.

  • Para os exemplos de junção, será adicionada a coluna "curso_atual" na tabela pessoas.
  • Supor que uma pessoa faz um curso por vez.
  • "curso_atual" será uma chave estrangeira que faz referencia a idcurso da tabela cursos.
alter table pessoas add column curso_atual int;
alter table pessoas add foreign key(curso_atual) references cursos(idcurso);

update pessoas
	set curso_atual = '6'
	where id = '1';

update pessoas 
	set curso_atual = '2' 
	where id between 2 and 4;

update pessoas 
	set curso_atual = 8 
	where id in (5, 6, 7);

Junção interna (inner join)

Compara cada linha de uma tabela A com cada linha de uma tabela B para encontrar todos os pares de linhas que satisfaçam o predicado de junção.

select p.id, p.nome, p.profissao, p.nacionalidade, c.nome from pessoas p join cursos c on p.curso_atual = c.idcurso;
  • Notar que serão retornadas colunas de ambas as tabelas (id, nome, profissao e nacionalidade de pessoas e nome de curso);
  • As linhas retornadas serão apenas as em que a condição de junção é verdadeira (on p.curso_atual = c.idcurso);
  • É especificado um apelido tanto para a tabela pessoas (p) como para cursos (c);
  • O apelido serve para encurtar o nome da tabela ao escolher as colunas a serem projetadas;
  • Em joins é comum usar table.column para evitar ambiguidade pois poderiam existir colunas com o mesmo nome nas duas tabelas;

Junção externa esquerda (left outer join)

Retorna todas as linhas da tabela da esquerda além dos pares que satisfaçam a condição de junção.

select p.id, p.nome, p.profissao, p.nacionalidade, c.nome from pessoas p left join cursos c on p.curso_atual = c.idcurso;

Além das linhas retornadas no inner join, também foram retornadas as outras linhas da tabela pessoas.

Junção externa direita (right outer join)

Semelhante à junção externa esquerda.

select p.id, p.nome, p.profissao, p.nacionalidade, c.nome from cursos c right join pessoas p on p.curso_atual = c.idcurso;

Mesmo exemplo da junção externa esquerda feito com uma junção externa direita.

Junções com mais de duas tabelas

  • Alunos agora poderão estar relacionados a mais de um curso;
  • Será removida a coluna curso_atual;
  • Será criada uma tabela inscricao tendo como chave primária os ids do aluno e do curso;
alter table pessoas drop constraint pessoas_ibfk_1; - removendo a chave estrangeira
alter table pessoas drop curso_atual; - removendo a coluna curso_atual
create table inscricao(
id_pessoa int,
id_curso int,
data_inicio date,
status enum('concluido', 'cursando'),
primary key(id_pessoa, id_curso),
foreign key(id_pessoa) references pessoas(id),
foreign key(id_curso) references cursos(idcurso));

insert into inscricao values
(1, 6, CURDATE(), 'cursando'),
(1, 8, '2020-01-01', 'concluido');
  • O comando acima adiciona os cursos da pessoa de id = 1;
  • O curso de MySQL tem data de inicio hoje (CURDATE()) e o status como "cursando";
  • O curso de Python tem inicio em '2020-01-01' e status "concluido".
select p.id, p.nome, p.nascimento, p.nacionalidade, c.nome, i.status 
from pessoas p join inscricao i 
on p.id = i.id_pessoa 
join cursos c 
on i.id_curso = c.idcurso;

Temos dois joins sendo feitos:

  • pessoas com inscrição (on p.id = i.id_pessoa);
  • o resultado deste primeiro join com a tabela cursos (on i.id_curso = c.idcurso);

Cross Join

Combina todas as linhas da primeira tabela com cada linha da segunda tabela.

select * from pessoas, cursos;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment