Aula 002

Banco de Dados

PRONATECPrograma Nacional de Acesso ao

Ensino Técnico e Emprego

PRONATECPrograma Nacional de Acesso ao

Ensino Técnico e Emprego

1. A SQL – Structured Query Language• Linguagem de Definição de Dados (DDL)

juntamente com a Linguagem de Manipulação dos dados (DML)

• Comandos em inglês mais próximos da inteligência humana do que as linguagens de máquina.

• Principais comandos DDL: “create database”, “use database”, “create table”

• Principais comandos DML: “insert”, “update”, “delete” e “select”

2. Principais comandos DDL• CREATE DATABASE – cria um banco de dados para

conter todas as estruturas necessárias ao mesmo: espaço para o dicionário de dados, espaço para os dados e espaço para as “stored procedures” e “triggers”

• Ex: create database clinica;• Existem parâmetros para se usar esse comando

mas basicamente essa é a sintaxe. Um parâmetro muito usado é o conjunto de caracteres

• Exemplo: create database `exames`character set 'utf8’ collate 'utf8_general_ci';

3. Principais comandos DDL• USE DATABASE `nome´; - abre o banco de dados para

ser usado no programa SQL• CREATE TABLE – cria uma tabela dentro do banco

com as definições especificadas na sintaxe do comando. Exemplo:

CREATE TABLE `cidade` ( `id` bigint(20) NOT NULL auto_increment, `nome` varchar(100) default NULL, `uf` char(2) default NULL, PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

4. Principais comandos DML• INSERT – usado para incluir dados em tabelas

do Banco de Dados. Exemplo:INSERT INTO `cidade` (`id`, `nome`, `uf`) VALUES

(1,'Patos de Minas','MG');Sintaxe: insert into `tabela´ (`col1´,...,`colN´)

values (`val1´,...,`valN´);Onde cada “val” combina com o tipo

corresponde ao seu “col”

5. Principais comandos DML• UPDATE – usado para alterar dados em tabelas

do Banco de Dados. Exemplo:ALTER TABLE `cidade´ SET `nome´ = `PO´ WHERE

`id´ = 1;Sintaxe: alter table `tabela´ set `coluna´ =

`novo_valor´ [where `condição_lógica´];OuAlter table `tabela´ set `col1´=`val1´, `col2´=`val2

´,...,`colN´=`valN´ where `condição´;

6. Principais comandos DML• DELETE – usado para excluir linhas em tabelas

do Banco de Dados. Exemplo:DELETE FROM `cidade´ WHERE `id´ = 1;Sintaxe: delete from `tabela´ [where `condição_lógica´];Se a condição não for definida apaga todas as linhas da tabela sem confirmação.

7. Principais comandos DML• SELECT – é o comando mais usado da DML com ele podemos

consultar os dados do banco. As consultas podem ser: seleção, projeção, junção ou União.

• Seleção: consulta simples a uma tabela selecionando algumas linhas da mesma de acordo com uma ou mais condições lógicas

• Projeção: consulta simples a uma tabela onde escolhemos além das linhas a serem mostradas, também as colunas que queremos

• Junção: é a fusão de duas ou mais tabelas ligadas por suas chaves estrangeiras e primárias de forma a constituir uma ampla gama de dados resultando em um produto cartesiano entre as mesmas, o qual ainda poderá sofrer uma seleção ou projeção

• União: é a consulta a duas ou mais tabelas com a mesma estrutura de forma a “somar” suas linhas e não “multiplicar” como no caso da junção.

8. O Comando Select – Parte1• A partir desse ponto vamos considerar um banco de dados `empresa´ com

as tabelas `cliente´, `cidade´,`vendedor´ seguindo o DER:VENDEDOR CLIENTE CIDADEV-C C-C1 N N 1

• Vendedor (PK=ID) Cidade (PK=ID)ID Nome Comissão

1 José da Silva 1,50%

2 Maria Aparecida 3,00%

3 Antônio Costa 1,50%

ID Nome Estado

1 Patos de Minas MG

2 Uberlândia MG

3 São Paulo SP

• Cliente (PK=ID, FK1=cidade, FK2=vendedor)ID Nome Cidade Vendedor Ultima Venda Vendas

1 Só Argamassas 2 1 José $500,00

2 A Constrular 1 3 Maria $300,00

3 Barato Acabamentos 1 2 André $0,00

4 Supermercado Frondoso 3 2 Ana $1200,00

9. Seleção com “select”• Uma consulta simples em uma tabela escolhendo algumas linhas da mesma. • Exemplo: selecionar todas as cidade de MG• Select * from `cidade´ where `estado´=`MG´;• Resultado da consulta: ID Nome Estado

1 Patos de Minas MG

2 Uberlândia MG

• Podemos ainda ordenar o resultado por alguma coluna usando a cláusula “order by” com opções: asc (ascendente) ou desc (descendente)

• Exemplo, ordenando por Nome de forma decrescente• Select * from `cidade´ where `estado´=`MG´ order by `nome´ desc;

ID Nome Estado

2 Uberlândia MG

1 Patos de Minas MG

10. Projeção com “select”• Uma projeção é uma seleção com a escolha das colunas a serem mostradas ao

invés de serem todas elas• Select `nome´,`estado´ from `cidade´;• Resultado desta projeção:

• O “*” após o “select” indica todas as colunas, se desejar escolher as colunas deve substituir o “*” pelos nomes das colunas separados por “,” isso que é fazer a projeção de uma consulta.

• Podemos ter ter uma “projeção” juntamente com uma “seleção” e “ordenação”• Select `estado´,`nome´ from `cidade´ where `estado´=`MG´ order by `nome´ desc;

Estado Nome

MG Uberlândia

MG Patos de Minas

Nome Estado

Patos de Minas MG

Uberlândia MG

São Paulo SP

11. Junção: Produto Cartesiano• Uma junção é um select feito em duas ou mais tabelas de forma a resultar em um

produto cartesiano entre elas. Assim se for feita em duas tabelas A e B, todas as linhas de A são combinadas com todas as linhas de B

• Exemplo: select * from `cliente´,`cidade´;• Resultado desta projeção terá 12 linhas = 4 de cliente X 3 de cidade

ID Nome Cidade Vendedor Ultima Venda Vendas ID Nome Estado

1 Só Argamassas 2 1 José $500,00 1 Patos de Minas MG

2 A Constrular 1 3 Maria $300,00 1 Patos de Minas MG

3 Barato Acabamentos 1 2 André $0,00 1 Patos de Minas MG

4 Supermercado Frondoso 3 2 Ana $1200,00 1 Patos de Minas MG

1 Só Argamassas 2 1 José $500,00 2 Uberlândia MG

2 A Constrular 1 3 Maria $300,00 2 Uberlândia MG

3 Barato Acabamentos 1 2 André $0,00 2 Uberlândia MG

4 Supermercado Frondoso 3 2 Ana $1200,00 2 Uberlândia MG

1 Só Argamassas 2 1 José $500,00 3 São Paulo SP

2 A Constrular 1 3 Maria $300,00 3 São Paulo SP

3 Barato Acabamentos 1 2 André $0,00 3 São Paulo SP

4 Supermercado Frondoso 3 2 Ana $1200,00 3 São Paulo SP

12. Junção com Seleção• A junção sempre terá o número de linhas igual ao produto das linhas de cada tabela da select. Assim,

se tivermos 3 tabelas: A,B,C o total de linhas da junção AxBxC será: linhas A X linhas B X linhas C• As colunas resultantes serão a soma das colunas de todas as tabelas. Ou seja: colunas A + colunas B +

colunas C. Se as colunas tem mesmo nome então usamos o nome da tabela que ela pertence seguida de “.” e o seu nome: A.id, B.id, C.id para diferenciar

• Os resultados de junção podem ser enormes, assim normalmente deve-se fazer uma “seleção” e “projeção” no resultado para se ter um resultado com algum sentido. Em nosso exemplo seria ligar a tabela “cliente” com a “cidade” levando em consideração a chave estrangeira em “cliente” que indica a qual cidade ele se localiza

• Exemplo: select * from `cliente´,`cidade´ where cliente.cidade = cidade.id;• Veja que agora o resultado da junção faz mais sentido: cada cliente com sua respectiva cidade onde

estão localizados

Colunas de `cliente´ Colunas de `cidade´ID Nome Cidade Vendedor Ultima Venda Vendas ID Nome Estado

1 Só Argamassas 2 1 José $500,00 2 Uberlândia MG2 A Constrular 1 3 Maria $300,00 1 Patos de Minas MG3 Barato Acabamentos 1 2 André $0,00 1 Patos de Minas MG4 Supermercado Frondoso 3 2 Ana $1200,00 3 São Paulo SP

13. Junção com Projeção• Na junção podemos “apelidar” as tabelas para tornar mais fácil seu

manuseio, assim “cliente” pode ser “a” e “cidade” será “b”• Exemplo: select * from `cliente´ a, ` cidade´b where a.cidade = b.id;• Podemos também projetar as colunas que desejamos no resultado

e apelida-las com nomes mais próximo ao real, assim:• Select a.nome as Cliente, b.nome as Cidade, b.estado from cliente

a, cidade b where a.cidade = b.id• O resultado deste último comando “select” seria:Cliente Cidade Estado

Só Argamassas Uberlândia MGA Constrular Patos de Minas MGBarato Acabamentos Patos de Minas MGSupermercado Frondoso São Paulo SP

14. A Cláusula “where”• A cláusula “where” é muito importante para o

comando “select”, pois por ela podemos limitar o conjunto de linhas selecionadas da consulta e nos atermos somente à informação necessária

• A condição resultante do “where” sempre será um valor lógico (True ou False) e será executado para cada linha da consulta resultante podendo usar qualquer combinação das colunas selecionadas e ser aplicado alguns operadores:

• () – parênteses vem primeiro, mais internos• As Funções e operadores lógicos vem a seguir• Operador NOT seguido de AND, depois OR

15. Operadores da Cláusula “where”Operadores:• = igualdade• > maior que• < menor que• >= maior ou igual a• <= menor ou igual a• IS NULL retorna verdade se o valor é indefinido NULL• IN (valor1, valor2,..., valorN) operador “está em”• BETWEEN...AND operador “entre valores”• LIKE “%...%” operador string “semelhante a”

Funções de teste e outros propósitos de consulta:• LOWER(“Texto”) – texto todo em minúsculo• UPPER(“Texto”) – texto todo em maiúsculo• SUBSTR(“Texto” FROM posi FOR qde) devolve parte do “Texto” que vai de

“posi” até “qtde” caracteres• EXTRACT DAY FROM data, EXTRACT MONTH FROM data, EXTRACT YEAR FROM

data: pega respectivamente o DIA, MÊS ou ANO de “data”

16. A Cláusula “order by”• A cláusula “order by” no final do comando select indica

para qual ou quais colunas o resultado deverá ser ordenado• Se for escolhida uma coluna de texto, será ordenado em

ordem alfabética ascendente, se for uma coluna numérica, será em ordem crescente de valor. Se for escolhida uma coluna de data, o resultado sairá em ordem ascendente de data. E assim sucessivamente.

• Para mudarmos de “ascendente” para “descendente” usamos o qualificador “desc” após o nome da coluna a ser ordenada

• Se desejarmos uma ordem por mais de uma coluna então a primeira coluna da cláusula será a ordem principal, a segunda a ordem secundária dentro da ordem primária e assim sucessivamente

17. Exemplos de Order By• Todas as cidades em ordem alfabética crescente:Select * from cidade order by nome;• Todos os vendedores em ordem alfabética inversa (z..a) sem

considerar diferença entre letras maiúsculas e minúsculas:Select * from vendedor order by lower(nome) desc;• Todos os nomes dos clientes com os nomes das suas cidades em

ordem alfabética de cidade e dentro dessa ordem ordenar pelo nome do cliente sem considerar diferença de caso (maiúsculas e minúsculas)

Select a.nome, b.nome from cliente a, cidade b where a.cidade = b.id order by lower(b.nome), lower(a.nome)• Todos os nomes dos clientes com nomes dos seus vendedores em

ordem alfabética de vendedor e dentro dessa ordem ordenar pelo nome do cliente sem considerar diferença de caso

Select a.nome, b.nome from cliente a, vendedor b where a.vendedor = b.id order by lower(b.nome), lower(a.nome)