SQL

(1)

JTS SQL básico 1

Sistemas de Banco de Dados

LINGUAGEM

Conceitos básicos

JTS SQL básico 2

• SQL “

structured query language

”

– linguagem comercial e completa de definição e manipulação de banco de dados

– SQL1: aprovado pelo grupo ANSI, em 1986 – SQL2: aprovado em 1992

– SQL3: está em andamento

• SQL – DDL:

data definition language

– Linguagem de definição de dados

– Instruções de definição do esquema da base de dados

• SQL – DML:

data manipulation language

– Linguagem de manipulação de dados

– Instruções de Programação de consultas e transações de manipulação de banco de dados

• Outras instruções

– SQL embutida em linguagens de programação

– Definição de visões, autorização de acesso, controle de transações e concorrência, restrições de integridade, ...

SQL básico 3

SQL: DDL

• Criação de banco de dados

– O padrão SQL não instruções para criação do BD. Alguns produtos têm:

• CREATE DATABASE: cria uma base de dados vazia • DROP DATABASE: elimina uma base de dados

• Definição do esquema da base de dados

– CREATE TABLE

• Cria uma tabela vazia definindo a estrutura de uma tabela e suas restrições

– DROP TABLE • Elimina uma tabela – ALTER TABLE

• Altera a definição de uma tabela – CREATE INDEX

• Permite a criação de índices (em tabelas) na base de dados

SQL básico 4

SQL – DDL: Criação de Tabelas

• O comando CREATE TABLE: cria a tabela solicitada

CREATE TABLE <nome da tabela>

( <descrição_das_colunas> )

( <descrição_das_restrições> )

• <tabela>: nome da nova tabela a ser criada

• <descrição_das_colunas> : lista de colunas (campos) e seus respectivos tipos de dados ( char, integer, decimal, date, ...) • <descrição_das_restrições>: lista de colunas (campos) que são

(2)

JTS SQL básico 5

• Conjunto de domínios de valores de atributos é fixo

• Desejável:

– domínio definível pelo usuário (ex.: sigla de estados, meses do ano) • SGBDs comerciais oferecem domínios adicionais aos do padrão

(CHAR, VARCHAR, INTEGER, REAL, ...) como: – DATE

– BLOB (até 2 gigabytes) para conter imagens, sons, vídeos, ...

• Cláusula NOT NULL é requerida para colunas que

façam parte da chave primária

• colunas de uma tabela são ordenadas pela sua ordem de

definição (linhas não tem ordenação)

SQL – DDL: Criação de Tabelas

JTS SQL básico 6

CREATE TABLE

PACIENTE

( CodPac

CHAR(5)

NOT NULL,

Nome

CHAR(40) NOT NULL,

Cidade

CHAR(25)

PRIMARY KEY (Codigo) )

SQL – DDL: Criação de Tabelas

Nome da tabela

Chave primária

CREATE TABLE CONSULTA

( CodMed

CHAR(3)

NOT NULL,

CodPac

CHAR(3)

NOT NULL,

Dia

CHAR(30) NOT NULL,

Hora

INTEGER,

PRIMARY KEY (CodMed, CodPac, Dia)

FOREIGN KEY (CodMed) references MEDICO(CodMed),

FOREIGN KEY (CodPac) references PACIENTE(CodPac)

)

SQL – DDL: Criação de Tabelas

Chave estrangeira

SQL – DDL: Criação de Tabelas

Restrições de Integridade

• FOREIGN KEY <coluna> references <tabela>

[

<cláusula>

]

• Cláusula

DELETE

– ON DELETE RESTRICT (default)

– ON DELETE CASCADE

• propagação da exclusão linha para as linhas que a referenciam

– ON DELETE SET NULL (p/chave estrangeira que

admitem NULL)

• Cláusula

UPDATE: semelhante a DELETE

(3)

JTS SQL básico 9

SQL – DDL: Remoção de Tabelas

DROP TABLE <tabela> [<cláusula>]

• Cláusula RESTRICT

– Exclui a tabela somente se não existirem objetos

definidos com base na tabela

• Cláusula DELETE

– Não permite

DROP TABLE PACIENTE

– remove completamente uma tabela da base de dados

(vazia ou não)

JTS SQL básico 10

SQL – DDL: Alteração de Tabelas

ALTER TABLE <tabela> <cláusula>

• Nem todas as alterações são permitidas

• Cláusula de colunas:

– ADD – MODIFY

–

“DROP COLUMN (não existe)”

• Cláusula de restrições de chave:

– DROP PRIMARY KEY

– DROP FOREIGN KEY

• Para excluir uma chaves estrangeira é necessário ter dado um nome a ela (tratar como uma CONSTRAINT)

SQL básico 11

SQL – DDL: Alteração de Tabelas

• Exemplo ADD

ALTER TABLE PACIENTE

ADD (ENDERECO VARCHAR(30))

– adiciona nova coluna com valor vazio para todas as linhas – não pode ter especificação NOT NULL

• Exemplo MODIFY

MODIFY (ENDERECO VARCHAR(50))

• Exemplo DROP PRYMARY KEY

ALTER TABLE PACIENTE DROP PRYMARY KEY

SQL básico 12

SQL – DDL: Alteração de Tabelas

• Exemplo DROP FOREIGN KEY

DROP FOREIGN KEY nome_chave_estrangeira

• Como dar nome a uma chave estrangeira?

... FOREIGN KEY fk_ConsultaMedico(CodMed) references

MEDICO(CodMed)

ex.: ALTER TABLE PACIENTE

DROP FOREIGN KEY fk_ConsultaMedico

... CONSTRAINT fk_ConsultaMedicoFOREIGN KEY

(CodMed) references MEDICO(CodMed) ex.: ALTER TABLE PACIENTE

(4)

JTS SQL básico 13

• Criação de índices:

CREATE [UNIQUE]

INDEX <nome do índice>

ON <nome da tabela>

(< coluna(s) >)

• Exemplos:

CREATE UNIQUE INDEX

ipNome

ON PACIENTE (Nome)

CREATE INDEX

icPaciente

ON CONSULTA (CodPac)

SQL – DDL: Índices

JTS SQL básico 14

REMOÇÃO DE ÍNDICES

DROP INDEX <nome_do_índice>

Exemplos:

DROP INDEX

icPACIENTE

DROP INDEX

ipNOME

SQL – DDL: Índices

SQL – DML: atualizações

• SQL apresenta três comandos para atualizaçõesdas tabelas definidas no banco de dados: INSERT, DELETE eUPDATE

• INSERT

– insere uma ou mais tuplas (linhas, registros) em uma tabela

• DELETE

– remove uma ou mais tuplas em uma tabela

• UPDATE

– modifica os dados de uma ou mais tuplas de uma tabela

• Comando INSERT

INSERT INTO <nome da tabela>

<nome da(s) coluna(s)>

VALUES <valores>

• Forma 1

INSERT INTO MEDICO

VALUES (‘M1’, ‘João’, ‘Pediatra’)

• os valores devem ser informados exatamente na mesma ordem em que foram definidos os atributos (create table)

(5)

JTS SQL básico 17

• Forma 2

INSERT INTO

MEDICO (codmed, nome)

VALUES (‘M2’, ‘Maria’)

• ordem dos atributos na definição da tabela não precisa ser seguida.

• Atributos com valores nulos podem ser omitidos.

SQL – DML: INSERT

JTS SQL básico 18

• Forma 3

INSERT INTO

MEDICO (codmed, nome, Especialidade)

VALUES (‘M2’, ‘Maria’, ‘Cardiologia’ )

• ordem dos atributos na definição da tabela não precisa ser seguida.

• Atributos com valores nulos podem ser omitidos.

SQL – DML: INSERT

SQL básico 19

Inserir várias tuplas

– usado normalmente para criar uma tabela com o

resultado de uma consulta

CREATE TABLE CONSULTAS_06_MAIO

{ NomeMed CHAR (20),

NumConsultas INTEGER};

INSERT INTO CONSULTAS_06_MAIO SELECT MEDICO.nome, COUNT(*)

FROM MEDICO, CONSULTA

WHERE Dia = ‘06/05/98’ AND

MEDICO.Cod_Med=CONSULTA.cod_med GROUP BY MEDICO.nome

SQL – DML: INSERT

SQL básico 20

DELETE FROM <nome da tabela>

WHERE <condição>

• Excluir o paciente José

DELETE FROM PACIENTE

WHERE

nome = ‘José’

Exclusão só será efetivada se não violar restrições de integridade (chave estrangeira)

• Excluir todas as consultas

DELETE FROM CONSULTA

• remove todas as tuplas de uma tabela (tabela fica vazia)

• para remover a tabela completamente, deve ser usado o comando DROP TABLE

(6)

JTS SQL básico 21

UPDATE <nome da tabela>

SET <nome da(s) coluna(s)> = “novo valor”

WHERE <condição>

• Modificar o dia e a hora da consulta do médico M2 com

o paciente P3, de 21/9/2000 às 11h para 25/9/2000 às 14h

UPDATE

CONSULTA

SET

Dia=‘21/5/2000’

AND

Hora = 14

WHERE

Codmed=‘M2’

AND

Codpac = ‘P3’

AND

Dia=’25/9/2000’

AND

Hora=11

SQL - DML: UPDATE

JTS SQL básico 22

• Alterar a cidade do paciente ‘P1’ de ‘Soledade’ para

‘Passo Fundo’

• Alterar a especialidade do médico ‘M1’ para

‘Cardiologia’.

• Alterar o dia das consultas do médico M5 com todos os

pacientes, de 24/09/2000 para 25/09/2000

SQL - DML: UPDATE

SQL – DML: Consultas

Estrutura básica de uma consulta

SELECT

lista de colunas

FROM

lista de tabelas

[WHERE

condição]

Exemplos:

– Obter código e nome de todos os PACIENTES SELECT Codigo, Nome

FROM PACIENTE

– Obter todos os dados de PACIENTES

SELECT *

FROM PACIENTE

SQL – DML: Consultas

• Obter código e nome das PACIENTES que residem em “SOLEDADE”

SELECT Codigo, Nome

FROM PACIENTE

WHERE Cidade = ‘SOLEDADE’

• Obter todas as Cidades de todos os PACIENTES

SELECT Cidade

FROM PACIENTE

com eliminação de duplicatas

SELECT DISTINCT Cidade

(7)

JTS SQL básico 25

SQL – DML: Consultas/Junção

MEDICO (codmed, nome, especialidade)

PACIENTE (codpac, nome)

CONSULTA (codmed, codpac, data, hora)

• Obter os nomes dos médicos e dias de suas consultas SELECT MEDICO.Nome, Dia

FROM MEDICO, CONSULTA

WHERE MEDICO.codmed = CONSULTA.codmed

• Obter os nomes dos médicos e de seus pacientes SELECT MEDICO.Nome, PACIENTE.nome FROM MEDICO, PACIENTE, CONSULTA WHERE MEDICO.codmed = CONSULTA.codmed

AND

CONSULTA.codpac = PACIENTE.codpac

JTS SQL básico 26

SQL – DML: modelo completo

Uma consulta SQL pode apresentar até seis cláusulas distintas, sendo apenas as duas primeiras obrigatórias

Ordem das cláusulas:

SELECT

lista de colunas

FROM

lista de tabelas

[WHERE

condição sobre linhas]

[GROUP BY lista de colunas p/agrupamento

[HAVING condição sobre grupo] ]

[ORDER BY lista de colunas]

SQL básico 27

SQL – DML: Funções de Agregação

• SQL permite computar funções sobre grupos de tuplas, usando

a cláusula GROUP BY.

• Funções de agregação permitem computar valores: – COUNT : contagem de linhas

– SUM : soma

– MAX : máximo

– MIN : mínimo

– AVG : média

funções de agregação podem ser aplicadas sobre toda a tabela ou sobre um grupo de linhas

• Obter o número de médicos

SELECT COUNT(*)

FROM MEDICO

• Obter o número de consultas por médico SELECT codmed, COUNT(*) FROM CONSULTA GROUP BY codmed

SQL básico 28

SQL – DDL: Visões

• Uma visão em SQL é uma tabela

VIRTUAL

, que

não está armazenada fisicamente na base de dados

• Visões são utilizadas

– como mecanismos de segurança, quando combinadas com mecanismos de controle de acesso;

– para aumentar a independência de dados; – para diminuir a complexidade das consultas

– para ter uma visão particular de uma tabela (não precisa utilizar toda a tabela)

(8)

JTS SQL básico 29

SQL – DDL: Visões

• Criar uma visão (view)

– CREATE VIEW <NOME DA VISÃO>

[LISTA DE ATRIBUTOS] AS

CONSULTA

onde:

CONSULTA =

SELECT

<NOME DA(S) COLUNA(S)>

FROM

<NOME DA(S) TABELA(S)

WHERE <CONDIÇÃO>

• Apagar uma visão (view)

– DROP VIEW <NOME DA VISÃO>

JTS SQL básico 30

SQL – DDL: Visões

• Visão em que aparecem somente os médicos pediatras

CREATE VIEW MedicosPediatras (CodMed, Nome) AS SELECT CodMed, Nome

FROM MEDICO

WHERE Especialidade = ‘PEDIATRIA’

• Visão em que aparecem os médicos, seus pacientes e

dados das suas consultas

CREATE VIEW Med_Cons_Pac (Nome_Med, Nome_Pac, Dia, Hora)

AS SELECT m.Nome, p.Nome, Dia, Hora

FROM MÉDICO m, PACIENTE p, CONSULTA c WHERE M.codmed = C.codmed AND

P.codpac = C.codpac

SQL – DDL: Visões

• Uma visão é atualizável se:

– é definida a partir de uma única tabela da base

de dados

– cada linha da visão corresponde a um única

linha da tabela base

– cada coluna da visão corresponde a uma única

coluna da tabela base

• Uma visão para ser atualizável não pode:

– ser definida sobre múltiplas tabelas

– utilizar projeção com eliminação de duplicatas

– utilizar GROUP BY ou funções de agregação

Bibliografia

• DATE, C.J. Introdução a Sistemas de Bancos de Dados: tradução (4ª edição americana). Rio de Janeiro: Campus, 1994

• GOLENDZINER, Lia Goldstein. Conceitos de Bancos de Dados. Porto Alegre: CPGCC/UFRGS, 1996 (notas de aula)

• HEUSER, Carlos A. Fundamentos de Banco de Dados. Porto Alegre: CPGCC/UFRGS, 2000 (notas de aula)

• KROENKE, DAVID M. Banco de Dados: Fundamentos, projeto e implementação. Sexta edição (tradução). LTC – Livros Técnicos e Científicos, 1999