Resumo Módulo 3

Página 1

*CREATE *DROP *ALTER

CREATE= CRIAR

CREATE DATABESE nome_da_base_de_dados;

USE nome_da_base_de_dados;

CREATE TABLE nome_da_tabela (

nome_da_coluna1 TIPO DE DADO,

nome_da_coluna2 TIPO DE DADO,

PRIMARY KEY (nome_da_coluna),

FOREIGN KEY (nome_da_coluna) REFERENCES (nome_da_tabela)

);

DROP=EXCLUIR

DROP TABLE IF EXIST nome_da_tabela;

ALTER= ALTERAR COMANDOS

ADD= adicionar

MODIFY= modificar

DROP= excluir

ALTER TABLE nome_da_tabela

ADD nome_da_coluna;

ALTER TABLE nome_da_tabela

MODIFY nome_da_coluna;

ALTER TABLE nome_da_tabela

DROP nome_da_coluna;

Página 2

*INSERT *UPDATE *DELETE

INSERT= INSERIR

INSERT INTO nome_da_tabela (coluna1, coluna2, coluna3)

VALUES (valor1, valor2, valor3)

* Precisa seguir a ordem das colunas X valores

UPDATE= MODIFICAR

UPDATE nome_da_tabela

SET nome_da_coluna = valor1, valor2, valor3

WHERE condição (id);

DELETE= DELETAR

DELETE FROM nome_da_tabela WHERE condição (id);

Página 3

*SELECT *WHERE *ORDER BY

SELECT= SELECIONAR

SELECT nome_da_coluna, nome_da_coluna

FROM nome_da_tabela;

WHERE= CONDICIONAR

SELECT coluna1, coluna2, coluna3

FROM tabela

WHERE condição;

ORDER BY= ORDENAR

SELECT coluna1, coluna2

FROM tabela

WHERE condição

ORDER BY nome_da_coluna ASC ou DESC;

Página 4

*ALIAS *LIMIT *OFFSET

ALIAS= DAR NOME TEMPORARIO

SELECT coluna1 ALIAS nome_temporario

FROM tabela;

* Da para fazer com a coluna e com a tabela tb.

LIMIT= LIMITAR O NUMERO DE LINHAS

SELECT coluna1, coluna2

FROM tabela

LIMIT número;

OFFSET= ESCOLHER A PARTIR DE QUAL LINHA É

SELECT coluna1, coluna2

FROM tabela

LIMIT numero

OFFSET numero;

Página 5

FUNÇÕES DE AGREGAÇÃO *MAX *MIN *AVG *COUNT *SUM

COUNT= CONTAR O N° DE LINHAS

SELECT COUNT(*) FROM tabela;

EX:

SELECT COUNT(id) AS total FROM movies WHERE genre.id=3

AVG=MEDIA

SELECT AVG(coluna) FROM tabela;

SUM=SOMA

SELECT SUM(coluna) FROM tabela;

MIN=VALOR MINIMO

SELECT MIN(coluna) FROM tabela;

MAX=VALOR MAXIMO

SELECT MAX(coluna) FROM tabela;

Página 6

*GROUP BY *HAVING

GROUP BY=AGRUPAR

SELECT coluna1

FROM tabela

WHERE condição

GROUP BY coluna;

HAVING=FAZ A MESMA COISA QUE O WHERE NAS FUNÇÕES DE AGREGAÇÃO

SELECT coluna

FROM tabela

WHERE condicao

GROUP BY coluna

HAVING condicao_grupo

ORDER BY coluna;

EX:

SELECT pais, COUNT (clienteId)

FROM clientes

GROUP BY pais

HAVING COUNT (clienteId)>3;

Página 7

*JOINS

INNER JOIN=CRUZAMENTO

SELECT tabela.coluna, abela. coluna

EX:

SELECT clientes.id AS id, clientes.nome, vendas.data

FROM clientes

INNER JOIN vendas

ON clientes.id = vendas.clientes_id;

Página 8

*FORMATAÇÃO *DISTINCT

DATE_FORMAT é usado para formatar datas em diferentes configurações.

SELECT DATE_FORMAT('data_cadastro', '%d %M %Y') FROM livros;

DISTINCT= quando tem duas linhas idênticas e você precisa fazer distinção.

SELECT DISNTICT coluna_1, coluna_2

FROM nome_tabela;

Tipo quando tem várias linhas com o mesmo país, dai vai exibir todas, quando vc usa o distinct vai aparecer somente 1 linha para cada país.

Página 9

SUBQUERIES

Uma querie dentro de outra.

EX:

SELECT 'livro_id', 'livro_titulo FROM 'livros' WHERE 'livro_preço' > (SELECT AVG('livro_preço') FROM 'livros');

Página 10

FUNÇÕES *CONCAT *COALESCE *DATEDIFF *EXTRACT

CONCAT=CONCATENAR

SELECT CONCAT ('Olá', 'Mundo.');

> Olá Mundo.

SELECT CONCAT ('Nome: 'first_name', ' ', 'lat_name') FROM actors;

> Nome: Emilia Clarke

COALESCE= primeira expressão que não é nula

SELECT coluna_completa, coluna_completa COALESCE(coluna_incompleta, coluna_incompleta) AS nome_para_unificar FROM tabela;

DATEDIFF=diferença entre duas datas.

SELECT DATEDIFF (hour, '2017/08/25 07:00', '2017/08/25 12:45');

> 5

EXTRACT= extrair dados de uma tabela.

SELECT EXTRACT(SECOND FROM '2014-02-13 08:44:21');

>21

Página 11

*REPLACE *DATE FORMAT *CASE

REPLACE= substituir caractere por outro

SELECT REPLACE ('abc abc', 'a', 'B');

>Bbc Bbc

DATEFORMAT= avaliar a sentença e retornar a primeira válida

SELECT DATE_FORMAT('2017-06-15', '%Y');

>2017

CASE= formatação condicional

SELECT id, title, rating

CASE

WHEN rating <4 THEN 'Ruim'

WHEN rating <6 THEN 'Regular'

WHEN rating <8 THEN 'Bom'

WHEN rating <9.5 THEN 'Muito Bom'

ELSE 'Excelente'

END AS rating_categories

FROM movies

ORDER BY rating

Página 12

TIPOS DE JOINS

INNER= apenas os registros que atendem as condições que colocamos em ON

LEFT=retorna todos os registros da primeira tabela, mesmo que não atenda aos requisitos

1 TABELA= CLIENTE 2 TABELA=FATURA

SELECT fatura.id AS nr_fatura, sobrenome, nome, data

FROM clientes

LEFT JOIN fatura

ON cliente.id = fatura.cliente_id;

Ele completa a linha nula de fatura com algum cliente

LEFT EXCLUDING= retorna os dados da primeira tabela excluindo os registros da clausula ON

SELECT fatura.id AS nr_fatura, sobrenome, nome, data

FROM clientes

LEFT JOIN fatura

ON cliente.id = fatura.cliente_id

WHERE ISNULL(fatura.id);

Retorna os clientes sem fatura.

RIGHT= a mesma coisa de LEFT só que faz na segunda tabela.

Página 13

VIEW

É tipo uma consulta que você deixa salva.

CREATE VIEW nome_da_view AS

colocar os dados do select

EX: CREATE VIEW musicas_de_rock AS

SELECT musicas.id, musicas.nome, generos.nome AS genero

FROM musicas

INNER JOIN generos

ON musicas.id_genero = genero.id

WHERE generos.nome IN ('Rock', 'Rock and Roll');

Para chamar a VIEW

SELECT * FROM musicas_de_rock

Página 14

STORE PROCEDURES

É um termo em inglês para Procedimentos Armazenados. São conjuntos de ações recorrentes em MySQL, ou seja, são ações que acontecem com frequência para executar funções.

Vantagens:

Velocidade porque é processada pelo servidor.

Segurança porque você pode criptografar.

Confiabilidade porque não dá para gravar quando tem erro de sintaxe.

Compatibilidade: funciona em qualquer linguagem.

Desvantagens:

Modificação difícil.

Aumento do uso da memória.

Restrita para lógica de negócios complexos.

ESTRUTURA:

DELIMITER $$

CREATE PROCEDURE minhaProcedure (parametro1, parametro2)

BEGIN

Sentenças SQL;

END

$$

DELIMITER;

PARA CHAMAR:

CALL minha Procedure(parametro1, parametro2)

PARAMETROS

IN- Entrada-Recebe dados

OUT- Saída-Retorna dados

INOUT- Entrada-Saída- Recebe e retorna dados

EX:

DELIMITER $$

CREATE PROCEDURE sp_produtos (IN filtro_categoria VARCHAR(15))

BEGIN

SELECT ProdutoNome, PrecoUnitario FROM produtos p

JOIN categorias C ON p.CategoriaID

WHERE CategoriaNome = filtro_categoria

END

$$

CALL sp_produtos ('Frutos do Mar');

Página 15