Banco de dados - Linguagem SQL

Banco de dados
Linguagem SQL
Prof. Emerson Ribeiro de Mello
Instituto Federal de Santa Catarina – IFSC
campus São José
[email protected]
http://docente.ifsc.edu.br/mello/bcd
06 de março de 2017
1/16
Abordagem relacional: Restrição de integridade
Princı́pio fundamental de um SGBD é a manutenção da integridade dos
dados
Dados refletem corretamente a realidade representada pelo BD e são
consistentes entre si
Integridade de domı́nio
Valor de um campo deve respeitar a definição de valores permitidos
Integridade de vazio
Indica se os valores de uma coluna podem ou não ser vazios (NULL)
Integridade de chave
Valores das chaves primárias devem ser únicos
Integridade referencial
Valores que aparecem uma chave estrangeira devem aparecer na chave
primária da tabela referenciada
2/16
Structured Query Language – SQL
Desenvolvida dentro da IBM na década de 70, sendo hoje a mais usada
em SGBD relacional
Baseada em álgebra relacional, permite:
definição e controle de acesso aos dados; manipulação de dados
Existem padrões ANSI e ISO, porém um código SQL geralmente não é
portável e pequenos ajustes são necessários para cada SGBD
SQL-86, SQL-89, SQL-92
SQL:1999, SQL:2003, SQL:2008, SQL:2011
3/16
Linguagem SQL é
Data Definition Language – DDL
Especificação do esquema relacional
Cria, altera, exclui tabelas
Data Manipulation Language – DML
Consulta, insere, modifica e exclui tuplas das tabelas
A linguagem não é sensı́vel a caixa (alta ou baixa)
Ex: CREATE, create
4/16
Data Definition Language – DDL
Linguagem para especificação do esquema ou estrutura do banco de
dados
Compilador gera um conjunto de tabelas em um conjunto de arquivos
denominados dicionário de dados
A DDL permite a especificação de:
Tabelas
Domı́nio dos valores permitidos para cada atributo
Restrições de integridade
Índices, controle de acesso, estrutura fı́sica das tabelas no disco
5/16
Data Definition Language – DDL
Linguagem para especificação do esquema ou estrutura do banco de
dados
Compilador gera um conjunto de tabelas em um conjunto de arquivos
denominados dicionário de dados
A DDL permite a especificação de:
Tabelas
Domı́nio dos valores permitidos para cada atributo
Restrições de integridade
Índices, controle de acesso, estrutura fı́sica das tabelas no disco
Os próximos exemplos são válidos para o MySQL 5.7
5/16
Alguns tipos de domı́nio
Tipo
CHAR(M)
VARCHAR(M)
BINARY(M)
SMALLINT(M)
INT(M)
BIGINT(M)
FLOAT(M,D)
DOUBLE(M,D)
BOOLEAN
Descrição
Cadeia de caracteres de tamanho fixo. 0 ≥ M ≤ 255
Cadeia de caracteres de tamanho variável. 0 ≥ M ≤
65.535. UTF-8 requer 3 bytes por caracter, então tamanho máximo de 21.844
Equivalente ao CHAR, porém armazena string de forma
binária ao invés de caracteres
Inteiro de −32.768 a 32.767
Inteiro de −2.147.483.648 a 2.147.483.648
Inteiro
de
−9.223.372.036.854.775.808
a
9.223.372.036.854.775.807
Real sendo M o total de dı́gitos para a parte inteira e D
o total de digital para a parte decimal
Float com o dobro de precisão
Tipo booleano (TRUE ou FALSE)
6/16
Criando tabelas
https://dev.mysql.com/doc/refman/5.7/en/create-table.html
Esquema de uma tabela – nome da tabela, atributos e seus tipos de
domı́nio
1
2
3
4
CREATE TABLE Aluno(
matricula INT,
nome VARCHAR(80),
email VARCHAR(80));
7/16
Criando tabelas
https://dev.mysql.com/doc/refman/5.7/en/create-table.html
Esquema de uma tabela – nome da tabela, atributos e seus tipos de
domı́nio
10
11
12
13
CREATE TABLE Aluno(
matricula INT,
nome VARCHAR(80),
email VARCHAR(80));
Campo matrı́cula como chave primária e seu valor incrementado
automaticamente para cada nova tupla inserida na tabela
14
15
16
17
18
CREATE TABLE Aluno(
matricula INT NOT NULL AUTO_INCREMENT,
nome VARCHAR(80),
email VARCHAR(80),
PRIMARY KEY (matricula));
7/16
Criando tabelas
Nome das colunas não precisam fazer referência ao nome da tabela
Ex: nomeAluno, emailAluno
Para a chave primária é desejado que faça referência ao nome da tabela,
pois esse campo poderá ser chave estrangeira em outra tabela
Ex: matriculaAluno
8/16
Apagando e alterando tabelas
Apagando
19
DROP TABLE Aluno;
Alterando
20
ALTER TABLE Aluno ADD COLUMN tel INT;
21
22
ALTER TABLE Aluno CHANGE COLUMN tel telefone VARCHAR(40);
23
24
ALTER TABLE Aluno MODIFY COLUMN telefone VARCHAR(25);
25
26
ALTER TABLE Aluno DROP COLUMN telefone;
9/16
Data Manipulation Language – DML
Permite acessar ou manipular dados no banco de dados
https://dev.mysql.com/doc/refman/5.7/en/non-typed-operators.html
Operador
AND , &&
OR , ||
NOT , !
!= , <>
>, >=
<, <=
=
:=
LIKE
IS [NOT] NULL
BETWEEN ...AND ...
Descrição
E lógico
OU lógico
Negação
Diferente
Maior, maior ou igual
Menor, menor ou igual
Atribuição para as instruções SET e UPDATE;
igualdade para os demais contextos
Atribuição
Busca por padrão
Se [não] é NULO
Valor dentro de uma faixa
10/16
Data Manipulation Language – DML
Permite acessar ou manipular dados no banco de dados
Inserindo
27
28
INSERT INTO Aluno (nome, email, telefone)
VALUES ('Joao', '[email protected]', '48-1234');
Apagando todas as linhas da tabela
29
DELETE FROM Aluno;
Atualizando valores de todas as linhas da tabela
30
UPDATE Aluno SET curso := 'Telecomunicaç~
oes';
11/16
SELECT – consulta SQL (query)
Resultado de uma consulta SQL é uma tabela
31
32
33
SELECT A1, A2, ..., An
FROM T1, T2, ..., Tn
WHERE P
A – Atributo
T – Tabela
P – predicado da consulta
Recuperando todas as colunas e linhas uma tabela
34
SELECT * FROM Aluno;
Recuperando as colunas nome e email de todas as linhas
35
SELECT nome, email FROM Aluno;
Recuperando todas disciplinas cursadas por um aluno e removendo
duplicatas
36
SELECT DISTINCT disciplina FROM Aluno;
12/16
SELECT
Recuperando todos os dados de todos os alunos do curso de
Telecomunicações e que moram em São José
37
38
SELECT * FROM Aluno
WHERE curso = 'Telecomunicaç~
oes' AND cidade = 'S~
ao José';
Todos funcionários com salário maior que R$ 1.000,00
39
40
SELECT * FROM Funcionarios
WHERE salario > 1000
Usando operadores aritméticos
41
SELECT 5 * 2 AS resultado;
42
43
SELECT horaExtra * valorHora * 2 as valorHE FROM funcionario;
13/16
SELECT
Ordenando o resultado
44
45
SELECT * FROM Aluno
ORDER BY nome, matricula;
Buscando por padrões em cadeias de caracteres
% – qualquer substring
– qualquer caracter
46
SELECT * FROM Aluno WHERE nome LIKE 'Jo~
ao%';
47
48
SELECT * FROM Aluno WHERE nome LIKE '%Silva%';
49
50
SELECT * FROM Aluno WHERE nome LIKE 'Jo_o %';
14/16
SELECT
Funções de agregação
Valor médio (AVG), mı́nimo (MIN), máximo (MAX), soma (SUM), total
(COUNT)
51
SELECT COUNT(*) as totalAlunos FROM Aluno;
52
53
SELECT AVG(salario) from Funcionario;
Com exceção do COUNT, todos os demais ignoram tuplas que tenham o
valor NULL nos atributos agregados
GROUP BY – Obtendo salário médio dos funcionários por departamento
54
55
SELECT departamento, AVG(salario) from Funcionario
GROUP BY departamento;
Atributos na instrução SELECT que estejam fora das funções de agregação
devem obrigatoriamente aparecer na lista do GROUP BY
15/16
SELECT
Obtendo nomes dos departamentos cujo salário médio de seus
funcionários seja maior que 1000
56
57
58
SELECT departamento, AVG(salario) from Funcionario
GROUP BY departamento
HAVING AVG(salario) > 1000;
Retornando no máximo as 100 primeiras linhas
59
60
SELECT nome FROM Aluno LIMIT 100;
SELECT nome FROM Aluno LIMIT 0,100;
Retornando no máximo 20 linhas a partir da linha 100
61
SELECT nome FROM Aluno LIMIT 100,20;
16/16