SQL. Hélder Antero Amaral Nunes

SQL

Introdução



Desenvolvida pelo departamento de pesquisa da IBM

na década de 1970 (System R);



Linguagem padrão de BD Relacionais;



Apresenta várias padrões evolutivos: SQL86,

SQL89(SQL1), SQL92 (SQL2), SQL99(SQL3);



Diferentes fornecedores de SGBDS apresentam versões

de SQL com algumas particularidades;



A última versão definida pela ANSI/ISO traz

características novas como: store procedures, triggers,

suporte à programação OO, entre muitas outras.

Características Comuns



Permite otimizações;



Utilizadas por várias classes de usuários



Sintaxe simples e bem definida



Presente em todos os SGBDs Relacionais



É incorporada comumente a uma outra linguagem;



Não é uma linguagem completa como C, Java ou

Delphi;



Portável entre sistema operacionais;

Algumas Funcionalidades



Uma série de comandos DDL para definição de dados;



Uma série de comandos DML para manipulação de

dados;



Comandos DQL para consulta de dados;



Instruções para controle de autorização de acesso (DCL

– Linguagem de Controle de Dados);



Instruções para controle de transações e concorrência

(DTL – Linguagem de Transação de Dados);

O uso do SQL



O usuário final não tem acesso a SQL;



A SQL fica embutida na linguagem de programação;



A conexão com o BD é necessário passar alguns

parâmetros, como:

 Local do servidor;

 Nome do banco de dados;  Senha; e

 Usuário do banco de dados.



O usuário pode acessar o banco de dados de acordo

com o seus privilégios;

O uso do SQL (2)

Tipos de dados

Tipo Uso

Char Armazena uma quantidade fixa de

caracteres

Varchar Armazena textos longos

Text Armazena textos de até 65.535

caracteres

Date Armazena data

Time Armazena hora, minuto e segundos

Int Armazena números inteiros

Float Armazena números com casa

decimais

Boolean Armazena true ou false

Atributos de dados do MySQL

Atributo Característica

Primary Key Indica a chave primária

Foreign Key Indica chave estrangeira

Not null Não permite que o valor do campo

seja nulo (campo obrigatório)

Null Permite que o valor do campo seja

nulo (campo não obrigatório)

Auto_Increment Faz uma sequência numérica única

Default Estabelece um valor padrão para uma

coluna

Unsigned Para proibir o uso de valores negativos

Unique Garante a unicidade de todos os seus

valores

Zerofill Coloca zeros a esquerda dos números

Index Cria um índice em uma tabela

Introdução a DDL



Cada Objeto ou estrutura em um BD é criado com

comandos pertencentes à DDL;



Seus comandos podem criar, alterar e eliminar:

 Visões (views);  Índices (index); e  Gatilhos (triggers)

DDL - Comando Create



Serve basicamente para criar os elementos que

constituem o BD (Tabelas, base de dados);



No momento da criação é possível verificar se já existe

a estrutura (comandos IF EXISTS e IF NOT EXISTS)



Exemplos:

 CREATE DATABASE estoque;

 CREATE TABLE IF NOT EXISTS `Autor` ( `Autor` varchar(50) NOT NULL, PRIMARY KEY (`Autor`));

DDL - Views, Triggers e Procedures



Views, trigges e procedures também são DDL, mas

devido ao uso em conjunto com a DQL e DML, será

visto após essas linguagens.

DDL - Comando Alter



Alter é o comando responsável por efetuar

alterações nas estruturas do banco de dados.



Em tabelas:



Adicionar novo campo em uma tabela:

ALTER TABLE usuario ADD COLUMN nome varchar (120) NOT NULL;



Remover um campo:

ALTER TABLE usuario DROP COLUMN dataNascimento;

DDL - Comando Alter (2)



Alterar um campo já existente:

ALTER TABLE usuario MODIFY COLUMN nome TEXT

ALTER TABLE produto CHANGE COLUMN observacao obs TEXT



Adicionar e remover um atributo Foreign Key:

ALTER TABLE produto ADD CONSTRAINT fk_id_fabricante FOREIGN KEY (fabricante) REFERENCES fabricante(id)

ALTER TABLE produto DROP FOREIGN KEY fk_id_fabricante

DDL - Comando Alter (3)



Adicionar e remover um index

ALTER TABLE fabricante ADD INDEX fabricante (cnpj); ALTER TABLE fabricante DROP INDEX fabricante;

ALTER TABLE produtos ADD UNIQUE nome_unico (nome); ALTER TABLE produtos DROP INDEX nome_unique;

DDL - Comando Drop



Drop é o comando que remove praticamente todas as

estruturas no banco de dados;

 Apagando base de dados

DROP DATABASE biblioteca;

DROP DATABASE IF EXISTS supermercado;

 Apagando tabelas

DROP TABLE produto;

DROP TABLE IF EXISTS usuario;

Exercício

A sigla SQL significa Linguagem de Consulta  Seqüencial.

 Segmentada.

 Relacional.  Escalar.

 Estruturada.

Exercício

 A linguagem SQL (Structured Query Language - Linguagem de Consulta Estruturada ) é dividida em grupos. Assinale a alternativa que apresenta apenas comandos do grupo de DDL (Data Definition Language - Linguagem de definição de dados).

 Create, Insert e Update.  Select, Create e Grant.

 Select, Insert, Update e Delete.  Commit e Rollback.

 Create, Alter e Drop.

Introdução a DML



Tem o poder de manipular os dados, ou seja, pode

inserir, atualizar e remover registros de uma tabela.



Seus principais comandos são INSERT, UPDATE, DELETE.

Insert



O comando INSERT é utilizado para inserir registros em

uma tabela;

 INSERT INTO usuario (nome, login, dataNascimento, senha) VALUES (‘Hélder Nunes’, ‘helder’, ‘1990-12-21’, ‘senha123’);

Insert (2)



Cada SGBD trata de uma maneira diferente de receber

os dados. Utilizando o MySQL como exemplo.



Para inserir dados do tipo INT e BIGINT:

 100  9765



Para inserir dados do tipo DECIMAL, DOUBLE:

 7567.987

Insert (3)



Para inserir dados do tipo texto (VARCHAR, TEXT e

outros):

 ‘Hélder Nunes’  ‘Pernambuco’



Para inserir dados do tipo data:

 ‘2014-12-21 10:35:30’

Insert (3)

 Insert INTO sala

(Nome_Da_Sala) VALUES (‘C93’),(’C94’);

 INSERT INTO sala VALUES (null, ‘C95’);

 Insert INTO sala

(Nome_Da_Sala) VALUES (‘C96’);

Update



O comando responsável por todo tipo de alteração

entre os registros de uma tabela.



Existem comandos que interagem entre si para

conseguir o resultado esperado. São eles:

 SET – indica qual campo da tabela sofrerá a alteração.

 WHERE – especificar quais linhas sofrerão alterações através de condições.

Update – cláusula WHERE



É possível utilizar os operadores:

 >  >=  <  <=  =  !=

 Like (e seus Coringas _ e %)

Update (2)



UPDATE sala SET nome = ‘C99’ WHERE id=1;



UPDATE sala SET nome = ‘Sem Nome’;



UPDATE sala SET nome = ‘Sem Nome’ WHERE nome =

‘B01’ or nome = ‘B02’;



UPDATE sala SET nome = ‘Sem Nome’ WHERE nome like

‘%B%’;

Exercício

A instrução UPDATE em SQL

dados das tabelas.

 modifica o tipo atribuído aos dados das tabelas.

 modifica a estrutura das tabelas.  atualiza o conteúdo dos dados

das tabelas.

Exercício

 Qual dos comandos SQL a seguir efetua uma alteração desabilitando o

recebimento de cesta básica dos funcionários que ganham mais que R$2.000,00?

 UPDATE funcionario SET

funcionario_ cestabasica=false;  UPDATE funcionario SET

funcionario_ cestabasica=false WHERE funcionario_ salario>=2000;  UPDATE funcionario SET

funcionario_ cestabasica=false WHERE funcionario_salario>2001;  UPDATE funcionario SET

funcionario_ cestabasica=false WHERE funcionario_salario>2000;  UPDATE funcionario SET

funcionario_ cestabasica=false WHERE funcionario_salario<2000;

Delete



A função do delete serve para remover registros das

tabelas.



O DELETE também utiliza a cláusula WHERE

Delete (2)



DELETE FROM sala WHERE id=1;



DELETE FROM sala WHERE nome = ‘Sem Nome’;



DELETE FROM sala WHERE nome = ‘B01’ or nome = ‘B02’;



DELETE FROM sala WHERE nome like ‘%B%’;

Introdução a DQL



É uma linguagem de consulta;



Grande parte das outros recursos de SQL utiliza o

comando da DQL.



Possui apenas um comando;

Select



A sintaxe básica do SELECT segue o formato abaixo:

 SELECT [modificadores] FROM tabela WHERE condições;



Exemplos de Selects:

 SELECT * FROM usuários;

 SELECT login, senha FROM usuários;

Select (2)



Modificadores de atributos:

 ALL – Seleciona todos os registros mesmo que haja repetição;  DISTINCT – Seleciona todos os registros sem repeti-los;



Exemplos:



SELECT ALL nome FROM usuários;



SELECT DISTINCT nome FROM usuários;

Select (3)



Modificadores de validação

 WHERE – expressão que precede uma validação

 HAVING - expressão que precede uma validação, podendo ser aplicadas na cláusula GROUP BY

 BETWEEN – validar valores entre

SELECT nome, login FROM usuario HAVING id BETWEEN 1 AND 10;

 LIMIT – limita a quantidade dos resultados exibidos

 ORDER BY (ASC | DESC) – ordenar a lista por um atributo

SELECT * FROM usuario ORDER BY id ASC LIMIT 10;

Select (4)



GROUP BY – agrupa os resultados de acordo com uma

necessidade;

 SELECT count(nome), departamento FROM funcionario GROUP BY departamento;



LIKE – Operador que aceita treco de palavras para

consultas e utilizar os seus coringas (_ e %)

 _ um coringa para 1 caractere  % coringa para vários caracteres

SELECT nome FROM usuario WHERE nome LIKE ‘ana%’;

Select (5) - Funções

 As consultas podem ser aprimoradas com o uso das expressões e modificadores disponíveis para a DQL

 Existem uma série de funções que permitem obter melhores resultados nas consultas

Select (6) - Funções

 MAX (coluna) – retorna o valor máximo da coluna

 MIN (coluna) – retorna o valor mínimo da coluna

 RAND() – gera um número

aleatório com o valor maior que ou igual a 0 e menor que 1 (0<= número gerado < 1)

 SUM (coluna) - soma os valores da coluna

 AVG (coluna) – faz a média dos valores da coluna

 COUNT (coluna) – diz a quantidade de linhas

 NOW() – retorna a data no formato ‘AAAA-MM-DD

HH:MM:SS’ (pode ser usada em combinação de outras funções)  DATE(coluna) – Retorna a data

no formato ‘AAAA-MM-DD’

 YEAR (coluna) – retorna o ano da data

 MONTH (coluna) – retorna o mes da data

 DAY (coluna) – retorna o dia da data

Select (7)

 O modelo relacional necessita que tabelas trabalhem juntos interagindo com seus diferentes registros;

 Existem vários operadores especiais que unem consultas de várias tabelas;

Select (8) - Union

 UNION faz a junção de várias consultas.

 Possibilita a junção de vários

SELECTs de diferentes tabelas em um único resultado;

 Modelo:

 SELECT campos FROM tabela1 UNION SELECT campos FROM tabela2

 Requisitos de funcionamento:

 Deve haver o mesmo número de campos entre cada SELECT

 As colunas chamadas pelo

SELECT devem ser do mesmo tipo, pois os registros serão mesclados na coluna.

 UNION – Não retorna registros duplicados

 UNION ALL – Retorna registros duplicados

Select (9) - ALIAS

 Os nomes das colunas podem ser modificados para nomes mais amigáveis  Exemplo:

 SELECT cod_endereço AS end FROM usuario;

Select (10) - JOIN

 O JOIN é ma maneira de criar consultas mescladas entre várias tabelas no banco de dados de forma que os registros sejam relacionados entre si

(chaves estrangeiras);

 A união é feita com os campos de cada tabela;  Modelo:

 SELECT campos FROM tabela1 JOIN tabela2 ON tabela1.campo = tabela2.campo;

ID_FUNC NOME SAL DEPARTAMENTO 1 Maria 1000 Estoque 2 Danilo 2000 RH 3 Marcos 1500 RH 4 Ana 3000 Estoque 5 Julia 1000 Caixa 6 Barbara 2500 Caixa 7 Silvio 3000 Estoque 41

ID_VEIC ID_FUNC VEICULO PLACA ANO

1 1 Gol Kkk-0001 2013

2 2 Uno Kkk-0234 2014

3 3 Siena Kkk-0053 2010

4 0 Civic Kkk-0003 2014

Select (11) – INNER JOIN

 Usando este comando todos os campos da tabela irão aparecer mesclados na consulta, mesmo se for repetido

 Exemplo:

 SELECT * FROM funcionarios JOIN veículos ON funcionários.ID_FUNC = veículos.ID_FUNC;

ou com “EQUI JOIN”

SELECT * FROM funcionarios JOIN veículos USING (ID_FUNC);

 O INNER JOIN só trará os registros onde a relação satisfizer em ambas as tabelas, ou seja, se todos os registros da primeira tabela se relacionar com os da outro;

42

ID_FUNC NOME SAL DEPARTA

MENTO ID_VEIC ID_FUNC VEICULO PLACA ANO

1 Maria 1000 Estoque 1 1 Gol Kkk-0001 2013

2 Danilo 2000 RH 2 2 Uno Kkk-0234 2014

Select (12) – LEFT JOIN

 Mas se fosse necessário mostrar também as pessoas que não possuem carros?

 Para que todos os funcionários apareçam no resultado deve-se usar LEFT JOIN;

 Exemplo:

 SELECT * FROM funcionario LEFT JOIN veiculo USING (ID_FUNC);

43

1 Maria 1000 Estoque 1 Gol Kkk-0001 2013

2 Danilo 2000 RH 2 Uno Kkk-0234 2014

3 Marcos 1500 RH 3 Siena Kkk-0053 2010

4 Ana 3000 Estoque NULL NULL NULL NULL

5 Julia 1000 Caixa NULL NULL NULL NULL

6 Barbara 2500 Caixa NULL NULL NULL NULL

Select (13) – RIGHT JOIN

 A única diferença do LEFT é que não seriam todos os funcionários iriam aparecer e sim todos os veículos mesmo que sem possuir donos.

 Exemplo:

 SELECT * FROM funcionario RIGHT JOIN veiculo USING (ID_FUNC);

44

ID_FUNC ID_VEIC VEICULO PLACA ANO NOME SAL DEPARTAMENTO

1 1 Gol Kkk-0001 2013 Maria 1000 Estoque

2 2 Uno Kkk-0234 2014 Danilo 2000 RH

3 3 Siena Kkk-0053 2010 Marcos 1500 RH

0 4 Civic Kkk-0003 2014 NULL NULL NULL

Select (14) – FULL JOIN

 Representa a união entre o LEFT JOIN e o RIGHT JOIN;

 Alguns SGBDs implementa o FULL JOIN diretamente (ex. MySQL);  Exemplo:

 SELECT f.nome, f.sal, f.departamento, v.ID_FUNC, v.veiculo,v.placa, v.ano FROM funcionario as f LEFT JOIN veiculo as v USING (ID_FUNC);

45

Nome sal departamento ID_FUNC veiculo placa ano

Maria 1000 Estoque 1 Gol Kkk-0001 2013 Danilo 2000 RH 2 Uno Kkk-0234 2014 Marcos 1500 RH 3 Siena Kkk-0053 2010 Ana 3000 Estoque NULL NULL NULL NULL

Julia 1000 Caixa NULL NULL NULL NULL

Barbara 2500 Caixa NULL NULL NULL NULL

Select (15) – Select in

 Pode ser feito uma seleção através de uma lista, por exemplo:

 Select Codigo, Nome from Funcionario where Nome in (‘João’,‘Tadeu’);

 Select código, nome from Funcionario where códigoDepartamento in (Select código from Departamento where nome like ‘Vendas’ );

Exercício

 Quanto à aplicação em bancos de

dados relacionais, Create, Delete, Insert e Drop são, respectivamente, comandos

 DDL, DDL, DML e DML.  DDL, DML, DML e DDL.  DML, DML, DDL e DDL.  DML, DDL, DDL e DML.  DML, DDL, DML e DDL. 48

Exercício

Assinale a opção que

apresenta apenas

operações de

manipulação do SQL.

 SELECT, PUT, GET e DELETE .  TAKE, PUT, GET e CUT

 CREATE, WRITE, READ e REMOVE  CREATE, INSERT, UPDATE e REMOVE  SELECT, INSERT, UPDATE e DELETE

Exercício

Observe as seguintes expressões SQL: I. DELETE II. DROP TABLE III. CREATE INDEX IV. UPDATE

V. INSERT INTO VI. ALTER TABLE

Classificando-se as expressões acima em Linguagem de Definição de Dados - DDL e Linguagem de Manipulação de Dados - DML, está correto o que consta em:

Linguagem de Definição de Dados - DDL / Linguagem de Manipulação de Dados - DML

 I, II e V - III, IV e VI  II e IV - I, III, V e VI  II, III e VI - I, IV e V  II e VI - I, III, IV e V  III e V - I, II, IV e VI 50

Exercício

Observe o código abaixo:

SELECT NOME, DESCONTO FROM CLIENTES WHERE DESCONTO BETWEEN .10 AND .20 A opção que descreve corretamente a seleção obtida na query é:

 apenas o nome de todos os clientes aos quais foram fornecidos descontos entre 10 e 20 por cento;

 o nome e o percentual do desconto dos clientes que tiveram desconto entre 10 e 20 por cento;

 o nome e o percentual do desconto dos clientes que tiveram desconto maior que 10 e menor que 19 por cento;  o percentual do desconto dos clientes

que tiveram desconto entre 10 e 20 por cento;

 o nome e o percentual de desconto dos clientes entre 10 e 19 por cento.

Exercício

Suponha as tabelas Empregado e Departamento com seus esquemas:

Empregado (Matricula, Nome, Sexo, Cargo, Dep) e Departamento (Codigo, Nome,

Telefone ), onde as chaves primárias são, respectivamente, Matricula e Codigo, e Dep é uma chave estrangeira que referencia a tabela Departamento.

O comando SQL :

SELECT Nome, Telefone FROM Empregado, Departamento WHERE Dep=Codigo;

resultaria em erro. Por quê?

 Não há Telefone em Empregado.  Falta a condição de junção.

 Há excesso de vírgulas no comando.

 O campo Nome é ambíguo.

 Não é permitido consultar de duas tabelas.

DCL – Linguagem de Controle

 Para se ocorrer uma conexão com o banco de dados é necessário informar o usuário e senha;

 Estes usuários estão salvos no banco de dados;  Faz parte da segurança do banco de dados;

 Em alguns SGBDs suas informações ficam salvas no próprio banco;

DCL (1)

DCL (2) – Create User

 Cria um usuário no banco de dados  Modelo:

 CREATE USER nome_do_usuario@nome_do_host IDENTIFIED BY senha;

 Sendo que:

 Nome_do_usuario = nome do usuario do banco de dados;

 Nome_do_host = nome do local (computador) na rede onde será acessado. Este nome pode ser por exemplo o IP, um nome de um computador, um host dentro de um domínio ou o % (qualquer local)

 Senha = a senha de autentificação

 Exemplo

 CREATE USER helder@localhost IDENTIFIED BY ‘123456’;

DCL (3) – Rename User

 Renomeia um usuário no banco de dados  Modelo:

 RENAME USER nome_antigo TO nome_novo;

DCL (4) – Drop User

 Exclui completamente o usuário, mesmo que tenha totais privilégios de operar em qualquer base de dados.

 Modelo:

 DROP USER nome_usuario@nome_do_local;

DCL (5) – GRANT e REVOKE

 Pode ser permitido (GRANT) ou bloqueado (REVOKE) qualquer privilegio para um determinado usuário (que quando recém criado não possui nenhuma permissão);

 Usuários só podem dar, a outros usuários, as permissões que ele possui;

DCL (6) – GRANT

 Serve pra conceder privilégios a um usuário de banco de dados  Os privilégios podem ser:

 Create  Alter  Drop  Insert  Update  Delete  Selete  E outros... 59

DCL (7) – GRANT

 Exemplos:

 GRANT SELECT ON *.* TO helder@localhost

 GRANT UPDATE, SELECT, INSERT ON banco_farmácia.* TO helder@localhost  GRANT SELECT ON banco_famarcia.tabela_produto TO helder@localhost  GRANT ALL PRIVILEGES ON *.* TO helder@localhost

DCL (7) – REVOKE

 Revoga os privilégios concedidos a um usuário do banco de dados.  Funciona similar ao GRANT.

 Exemplos:

 REVOKE DROP,ALTER ON *.* TO maria@localhost

DTL

 Uma Transação é um conjunto de operações SQL previamente criado para acontecer em uma chamada na aplicação.

 A integridade é uma das características mais importantes do BD, devido isso foi criado uma linguagem para garantir que a transação ocorra com sucesso.

 Esta linguagem foi adaptada em quatro princípios:

 Atomicidade;  Consistência;  Isolamento;  Atomicidade;

Exercício

Analisando o trecho de

código SQL a seguir

CREATE VIEW

Carro_Especial (Fabricante,

Tipo, Preco, Cor) AS SELECT

Fabricante, Tipo, Preco, Cor

FROM Carros WHERE Cor =

'Azul'

é correto afirmar que

 usando-se o código UPDATE Carro_Especial SET Preco = 15000 WHERE Tipo = 'Corsa' todo carro do tipo Corsa terá seu preço alterado para 15000 diretamente na VIEW Carro_ Especial.

 Os valores do campo Cor na VIEW Carro_ Especial serão diferentes de Azul caso a tabela Carros

tenha para o campo Cor valores diferentes de Azul.

 uma VIEW com o nome Carro_Especial será criada contendo os campos Fabricante, Tipo, Preco e Cor, onde todos os valores do campo Cor nesta VIEW serão iguais a Azul.

 o código INSERT INTO Carro_Especial VALUE ('GM', 'Corsa', 10000, 'Azul') irá inserir um novo dado com os valores especificados por VALUE diretamente na VIEW Carro_Especial.

 se na tabela Carros existir um campo com o nome Ano_de_Fabricacao, este campo será criado automaticamente na VIEW Carro_ Especial.

Exercício

Considerando o SQL, o formato geral do comando de criação de gatilhos é:

CREATE TRIGGER <nome do trigger> <tempo de ação do trigger> <evento para acionar o trigger> ON <nome da tabela>

<ação>

O parâmetro <tempo de ação do trigger> possui as seguintes opções válidas:

 BEFORE e AFTER.  BEGIN e END.  FIRST e LAST.  SAME e DIFFERENT.  START e FINISH. 64