Linguagem SQL

1. Linguagem SQL

2. Introdução • SQL (Structured Query Language): desenvolvida pela IBM (70) como parte do sistema System R. A SQL foi inicialmente chamada de SEQUEL • É a linguagem de consulta padrão para os SGBDR's • Já existem padrões propostos: ANSI-SQL(SQL-89), SQL-92 e padrões mais recentes: SQL:1999 e SQL:2003.

3. Introdução • O padrão SQL define precisamente uma interface para a definição de tabelas, para as operações sobre as mesmas (seleção, projeção, junção, e outras) e para a definição de regras de integridade de bancos de dados.

4. Introdução • Categorias • DDL: Linguagem de Definição de Dados. Usada para definir os esquemas, atributos, visões, regras de integridade, índices, etc. • DML: Linguagem de Manipulação de Dados. Usada para se ter acesso aos dados armazenados no BD. • DCL: Linguagem de controle dos dados. Usada para garantir ou negar o acesso aos dados.

5. Banco WorldJanu

6. Modelo Relacional • Banco (codigo, nome, endereco) • Agencia_Bancaria (numagencia, banco_fk, endereco) • Conta (numconta, saldo, tipo, numagencia_fk, banco_fk) • Emprestimo (numempr, valor, tipo, numagencia_fk, banco_fk) • Cliente (codigo, nome, telefone, endereco) • Conta_cliente (cliente_fk, conta_fk) • Emprestimo_cliente (cliente_fk, conta_fk)

7. DDL

8. DDL • Os comandos SQL para definição de dados são: • CREATE: criação de novas estruturas • DROP: remoção de estruturas • ALTER: alteração de estruturas

9. CREATE TABLE - DDL • CREATE TABLE: especifica uma nova tabela (relação), dando o seu nome e especificando as colunas(atributos) (cada uma com seu nome, tipo e restrições) • Sintaxe: • CREATE TABLE nome_tabela • (coluna tipo_dado)

10. CREATE TABLE - DDL • create table banco ( • codigo int, • nome varchar(50), • endereco varchar(100))

11. Interbase - Tipos de dados • Tipos em SQL:1999 • Numéricos exatos: • INTEGER (INT) e SMALLINT para representar inteiros • NUMERIC(p,s): tem uma precisão e uma escala(número de dígitos na parte fracionária). A escala não pode ser maior que a precisão. Muito usado para representar dinheiro • DECIMAL: também tem precisão e escala. A precisão é fornecida pela implementação (SGBD). • Numéricos aproximados: • REAL: ponto flutuante de precisão simples • DOUBLE: ponto flutuante com precisão dupla • FLOAT(p): permite especificar a precisão que se quer. Usado para tranportar (portability) aplicações

12. Interbase - Tipos de dados • Tipos em SQL:1999 • Character • CHARACTER(x) (CHAR): representa um string de tamanho x. Se x for omitido então é equivalente a CHAR(1). Se um string a ser armazenado é menor do que x, então o restante é preenchido com brancos. • VARCHAR(N): representa um string de tamanho x. Armazena exatamente o tamanho do string (tam <= x) sem preencher o resto com brancos. Neste caso x é obrigatório. • OBS.: Existem os National character data types: NCHAR, NVARCHAR, NCLOB que permitem implementar internacionalização

13. Interbase - Tipos de dados • Tipos em SQL:1999 • Binary Strings (BLOB) • BINARY LARGE OBJECT (BLOB): para armazenar grande quantidades de bytes como fotos, vídeo, áudio, gráficos, mapas, etc.

14. Interbase - Tipos de dados • Tipos em SQL:1999 • DATETIMES • DATE: armazena ano (4 digitos), mês (2 digitos) e dia(2 digitos). • TIME: armazena hora(2digitos), minuto(2 digitos) e segundo(2digitos, podendo ter frações 0 a 61.9999) • TIMESTAMP: DATE + TIME

15. Interbase - Tipos de dados • Tipos em SQL:1999 • Intervals • Um intervalo é a diferença entre duas datas e tempos. Intervalos do tipo year-month ou day-time. • Year-month interval: contém apenas um valor de ano, ou de mês ou ambos. YEAR(p), MONTH(p), INTERVAL YEAR TO MONTH(p) • Day-Time interval: contém apenas um dia, uma hora, um minuto e/ou um segundo. INTERVAL DAY(p), INTERVAL DAY TO HOUR, INTERVAL DAY(6) TO MINUTE, INTERVAL SECOND(7), etc. • Booleans: lógica de três valores (TRUE, FALSE e UNKNOWN)

16. DROP TABLE - DDL • DROP TABLE: remove uma tabela-base do BD. Remove tanto os dados quanto a definição da tabela • Sintaxe: DROP TABLE <nomeTabela> • Ex.: DROP TABLE Peca

17. Restrições - DDL

18. Not null - DDL • Algumas restrições • NOT NULL: Uma restrição de não-nulo simplesmente especifica que uma coluna não pode conter o valor nulo. • create table banco ( • codigo int not null, nome varchar(50) not null, endereco varchar(100))

19. Default - DDL - DDL • Algumas restrições • DEFAULT: indica um valor padrão para a coluna • CREATE TABLE "CLIENTE" • ( • "CPF" INTEGER NOT NULL, • "NOME" VARCHAR(50), • "TELEFONE" VARCHAR(13), • "ENDERECO" VARCHAR(50) default 'Januaria' • );

20. ALTER TABLE - DDL • Com o comando Alter Table podemos: • Incluir / Alterar / Excluir coluna • Incluir / Excluir restrições

21. ALTER TABLE - DDL • Incluir uma coluna • ALTER TABLE banco ADD estado varchar(40) • Excluir uma coluna • ALTER TABLE banco DROP estado • Alterar o tipo do campo • ALTER TABLE banco ALTER nome TYPE varchar(60)

22. ALTER TABLE - DDL • Adicionar a restrição default • ALTER TABLE banco • ALTER endereco SET DEFAULT ‘Januaria’ • Excluir a restrição default • ALTER TABLE banco • ALTER endereco DROP DEFAULT

23. UNIQUE - DDL • Algumas restrições • UNIQUE: indica que o atributo tem valor único na tabela. Qualquer tentativa de se introduzir uma linha na tabela contendo um valor igual ao do atributo será rejeitada. Serve para indicar chaves secundárias. Nomear as restrições (CONSTRAINTS) • Deve ser definido primeiramente como NOT NULL • CREATE TABLE BANCO( • CODIGO INT NOT NULL CONSTRAINT U_BANCOCODIGO UNIQUE, • NOME VARCHAR (40), • ENDERECO VARCHAR(40))

24. ALTER TABLE - DDL • Adicionar uma restrição Unique • ALTER TABLE banco ADD CONSTRAINT u_bu UNIQUE(nome) • Remover uma restrição • ALTER TABLE banco DROP CONSTRAINT u_bu

25. Check - DDL • Algumas restrições • CHECK: Permite especificar que os valores de uma determinada coluna devem estar de acordo com uma expressão arbitrária. • create table conta ( • numconta int not null unique, • saldo numeric constraint ch_saldo check(saldo>1000), • tipo int, • num_agencia int, • cod_banco int)

26. ALTER TABLE - DDL • Adicionar restrição Check • ALTER TABLE conta ADD CONSTRAINT c_saldo CHECK (saldo>1000)

27. ALTER TABLE - DDL • Remover constraint • ALTER TABLE BANCO DROP CONSTRAINT U_BU

28. Primary key - DDL • Primary key: Indica que a coluna pode ser utilizado como identificador único para as linhas da tabela. Pode ser composta por um ou dois campos. Deve ser definida como not null. • create table banco ( • codigo int primary key, • nome varchar (40), • endereco varchar (60)) • create table agencia_bancaria ( • numagencia int, • codbanco int, • endereco varchar(50), • primary key(numagencia,codbanco))

29. ALTER TABLE - DDL • Adicionar Chave Primária • alter table agencia_bancaria add primary key (codbanco,numagencia)

30. Foreign key - DDL • Foreign key: Especifica que o valor da coluna deve corresponder a algum valor que existe em uma linha de outra tabela. Mantém a integridade referencial entre as tabelas relacionadas. • create table agencia_bancaria ( • numagencia int not null, • codbanco int references banco, • endereco varchar(40), • primary key(numagencia, codbanco))

31. ALTER TABLE - DDL • Adicionar chave estrangeira • alter table conta add foreign key (cod_banco,num_agencia) references agencia_bancaria

32. Recomendação • É recomendado definir constraints e nomeá-las para que o sistema não crie nomes automáticos que geralmente não são ilegíveis.

33. FOREIGN KEY - DDL • Uma cláusula FOREIGN KEY inclui regras de remoção/atualização: • FOREIGN KEY (coluna) REFERENCES tabela [ON DELETE {RESTRICT|CASCADE|SET NULL| SET DEFAULT}] [ON UPDATE {RESTRICT|CASCADE|SET NULL| SET DEFAULT}] • Supondo que T2 tem uma chave estrangeira para T1, vejamos as cláusulas ON DELETE e ON UPDATE

34. FOREIGN KEY - DDL • ON DELETE: • RESTRICT: (default) significa que uma tentativa de se remover uma linha de T1 falhará se alguma linha em T2 combina com a chave • CASCADE: remoção de uma linha de T1 implica em remoção de todas as linhas de T2 que combina com a chave de T1 • SET NULL: remoção de T1 implica em colocar NULL em todos os atributos da chave estrangeira de cada linha de T2 que combina. • SET DEFAULT: remoção de linha em T1 implica em colocar valores DEFAULT nos atributos da chave estrangeira de cada linha de T2 que combina.

35. FOREIGN KEY - DDL • ON UPDATE: • RESTRICT: (default) update de um atributo de T1 falha se existem linhas em T2 combinando • CASCADE: update de atributo em T1 implica que linhas que combinam em T2 também serão atualizadas • SET NULL: update de T1 implica que valores da chave estrangeira em T2 nas linhas que combinam são postos par NULL. • SET DEFAULT: update de T1 implica que valores da chave estrangeira de T2 nas linhas que combinam terão valores default aplicados.

36. FOREIGN KEY - DDL • As restrições de integridade podem ter um nome e serem especificadas com a cláusula CONSTRAINT. • Isto permite que possamos no futuro eliminar (DROP) ou alterar (ALTER) o constraint.

37. DML

38. DML • Linguagem de manipulação dos dados • INSERT: inserção de registros • DELETE: deleção de registros • UPDATE: atualização de registros • SELECT: seleção de registros

39. INSERT - DML • Ao ser criada a tabela não contém nenhum dado. Os dados são inseridos uma linha por vez. • Os valores dos dados são colocados na mesma ordem que as colunas aparecem na tabela, separados por vírgula. • INSERT INTO banco VALUES (1,’Banco 1’,’End 1‘) • Quando não se sabe a ordem das colunas: • INSER INTO banco (nome, endereco, codigo) VALUES (‘Banco 1’, ‘End 1’, 1)

40. UPDATE - DML • O comando UPDATE • Modifica o valor de atributos de uma ou mais tuplas. • Sintaxe: UPDATE tabela SET lista_atributos com atribuições de valores [WHERE condição] • Obs.: omitir a cláusula WHERE implica que o UPDATE deve ser aplicado a todas as tuplas da relação

41. DELETE - DML • O comando DELETE • Remove tuplas de uma relação • Sintaxe: DELETE FROM tabela [WHERE condição] • Obs.: Se omitirmos a cláusula WHERE, então o DELETE deve ser aplicado a todas as tuplas da relação. Porém, a relação permanece no BD como uma relação vazia.

42. DML

43. DML • Esquemas do BD Empresa: • Empregado(matricula, nome, endereco, salario, supervisor, depto) • Departamento(coddep, nome, gerente, dataini) • Projeto(codproj, nome, local, depart) • Alocacao(matric,codigop, horas)

44. SELECT • O comando Select: • A forma básica do comando Select é: SELECT <lista atributos> FROM <lista tabelas> WHERE <condição>

45. SELECT • Obtenha todos os nomes dos empregados Select nome from Empregado • Obtenha o salário de José Select salario From Empregado Where nome = ‘José’ • Obs.: Podemos renomear o nome da coluna no resultado select salario as SalarioJose from empregado where nome = ‘José’

46. SELECT • Obs2: Podemos usar colunas como expressões select mat as matricula, salario, 0.15 * salario as IR from empregado • Podemos inserir constantes na cláusula select se necessário select nome, ‘marajá’ as Marajá from empregado where salario > 10.000,00

47. SELECT • Selecione todos os atributos de todos os empregados do departamento Pesquisa Select * From Empregado Where depto = ‘Pesquisa’

48. SELECT • Obter os nomes dos empregados e o nome do departamento que ele trabalha • Obter os nomes dos departamentos • Obter os nomes dos projetos e o local de realização

49. SELECT • Obter os nomes dos projetos e o nome dos departamentos que o controlam • Obter o nome do gerente de cada departamento, com o nome do departamento

50. SELECT • Selecione o nome e o endereço de todos os empregados que trabalham no departamento de produção Select e.nome, e.endereco From empregado e join departamento d Where d.nome = ‘Produção’