BANCO DE DADOS -INTRODUÇÃO AO SQL. Prof. Angelo Augusto Frozza, M.Sc.

B

ANCO DE

D

ADOS

- I

NTRODUÇÃO AO

SQL

Prof. Angelo Augusto Frozza, M.Sc. [email protected]

I

NTRODUÇÃO AO

SQL

|

Ferramentas

y

brModelo – Ferramenta Case

SQL – S

TRUTURED

Q

UERY

L

ANGUAGE

| Em 1986 o American Nacional Standard Institute (ANSI), publicou e padronizou a linguagem SQL;

| SQL estabeleceu-se como linguagem padrão do Modelo

Relacional;

| “Deveria” ser padrão para todos os gerenciadores de Banco de Dados:

y 1º versão 1989

y 2º versão 1992

SQL – S

TRUTURED

Q

UERY

L

ANGUAGE

| Os comandos SQL podem ser agrupados em 3 classes:

y DDL – Data Definition Language

| Comandos para a Definição de Dados y DML – Data Manipulation Language

| Comandos para a Manipulação de Dados y DCL – Data Control Language

SQL – S

TRUTURED

Q

UERY

L

ANGUAGE

| Objetos manipuláveis em um Banco de Dados:

y Database (Base de Dados)

y Table (Tabelas)

y Index (Indíces)

y View (Visões)

y Stored Procedures (Procedimentos)

y Triggers (Gatilhos)

y Domain (Domínios)

y Generator (Variáveis Globais)

y Exception (Excecões)

y User (Usuários)

SQL – S

TRUTURED

Q

UERY

L

ANGUAGE

| Comandos básicos da Linguagem de Definição dos Dados (DDL):

y CREATE

y ALTER

SQL – S

TRUTURED

Q

UERY

L

ANGUAGE

|

PostgreSQL

SQL – S

TRUTURED

Q

UERY

L

ANGUAGE

|

Comandos básicos do PostgreSQL:

\l Lista todos os bancos de dados

disponíveis

\c nome_banco Conecta a um banco de dados existente

\d Lista as tabelas do banco de dados ativo

\d nome_tabela Mostra a descrição da tabela

\q Sai do prompt do SGBD

\? Ajuda de Linha de comando

SQL – S

TRUTURED

Q

UERY

L

ANGUAGE

|

Objeto Database (Base de Dados)

- Definição

y Arquivo que contém a descrição dos dados, metadados, esquema do banco de dados;

y Contém todos os objetos criados;

y Contém os dados;

y Detém a segurança da estrutura completa;

y Sintaxe básica:

SQL – S

TRUTURED

Q

UERY

L

ANGUAGE

|

Criação do banco de dados no PostgreSQL

CREATE DATABASE nome

[ [ WITH ] [ OWNER [=] dono_do_banco_de_dados ] [ TEMPLATE [=] modelo ]

[ ENCODING [=] codificação ]

[ TABLESPACE [=] espaço_de_tabelas ]

[ CONNECTION LIMIT [=] limite_de_conexões ] ] ;

SQL – S

TRUTURED

Q

UERY

L

ANGUAGE

|

Tipos de dados do PostgreSQL

y Tipos numéricos

Tipo Tamanho Faixa

smallint 2 bytes -32.768 até 32.767

integer 4 bytes -2.147.483.648 até +2.147.483.647

bigint 8 bytes -9.223.372.036.854.775.808 to

9.223.372.036.854.775.807

decimal Variável Sem limite

numeric (precisao, escala) Variável Sem limite (até 1.000 dígitos de precisão) real 4 bytes 6 dígitos de precisão decimal (inexata)

double precision 8 bytes 15 dígitos de precisão decimal (inexata)

serial 4 bytes 1 até 2.147.483.647

bigserial 8 bytes 1 to 9.223.372.036.854.775.807

9

9

9

9

SQL – S

TRUTURED

Q

UERY

L

ANGUAGE

|

Tipos de dados do PostgreSQL

y Tipos caracter

Tipo Tamanho Faixa

character varying(n) varchar(n)

??? Tamanho variável com limite

character(n) char(n)

??? Tamanho fixo

text ??? Tamanho variável ilimitado

9

9

9

SQL – S

TRUTURED

Q

UERY

L

ANGUAGE

|

Tipos de dados do PostgreSQL

y Tipos data

y OBS. Datas devem ser informadas entre aspas (‘ ’)

Tipo Tamanho Faixa

timestamp [ (p) ] [

without time zone ]

8 bytes Data e hora

timestamp [ (p) ] with time zone

8 bytes Data e hora, com zona de tempo

interval [ (p) ] 12 bytes Intervalos de tempo

date 4 bytes Data somente

time [ (p) ] [ without

time zone ]

8 bytes Hora do dia apenas (00:00:00 até 24:00:00)

time [ (p) ] with time zone

12 bytes Hora do dia apenas, com zona de tempo

9

9

9

SQL – S

TRUTURED

Q

UERY

L

ANGUAGE

Example Description

January 8, 1999 unambiguous in any datestyle input mode

1999-01-08 ISO 8601; January 8 in any mode (recommended format) 1/8/1999 January 8 in MDY mode; August 1 in DMY mode 1/18/1999 January 18 in MDY mode; rejected in other modes

01/02/03 January 2, 2003 in MDY mode; February 1, 2003 in DMY mode; February 3, 2001 in YMD mode 1999-Jan-08 January 8 in any mode

Jan-08-1999 January 8 in any mode 08-Jan-1999 January 8 in any mode

99-Jan-08 January 8 in YMD mode, else error 08-Jan-99 January 8, except error in YMD mode Jan-08-99 January 8, except error in YMD mode 19990108 ISO 8601; January 8, 1999 in any mode 990108 ISO 8601; January 8, 1999 in any mode 1999.008 year and day of year

J2451187 Julian day

January 8, 99 BC year 99 before the Common Era

SQL – S

TRUTURED

Q

UERY

L

ANGUAGE

|

Tipos de dados do PostgreSQL -

Example Description

04:05:06.789 ISO 8601 04:05:06 ISO 8601 04:05 ISO 8601 040506 ISO 8601

04:05 AM same as 04:05; AM does not affect value 04:05 PM same as 16:05; input hour must be <= 12 04:05:06.789-8 ISO 8601

04:05:06-08:00 ISO 8601 04:05-08:00 ISO 8601 040506-08 ISO 8601

04:05:06 PST time zone specified by abbreviation 2003-04-12 04:05:06 America/New_York time zone specified by full name

SQL – S

TRUTURED

Q

UERY

L

ANGUAGE

|

Tipos de dados do PostgreSQL

y Tipos data

Tipo Tamanho Faixa

boolean 1 byte TRUE FALSE

't' 'f' 'true' 'false' 'y' 'n' 'yes' 'no' '1' '0'

9

SQL – S

TRUTURED

Q

UERY

L

ANGUAGE

|

Objeto Domínio

– Definição

y São tipos de dados criados para evitar redundâncias de tipos e facilitar a manutenção do Banco de Dados;

y Exemplos:

create domain d_nome_pessoa as varchar(40); create domain d_moeda as numeric(15,2);

SQL – S

TRUTURED

Q

UERY

L

ANGUAGE

|

Objeto Domínio

– Definição

CREATE DOMAIN nome [ AS ] tipo_de_dado

[ DEFAULT expressão ] [ restrição [ ... ] ];

SQL – S

TRUTURED

Q

UERY

L

ANGUAGE

|

Criando objetos Domínio

create domain d_logradouro as varchar(40);

create domain d_numero as varchar(6);

create domain d_cep as varchar(8);

create domain d_bairro as varchar(30);

create domain d_complemento as varchar(20);

create domain d_fone as varchar(10);

create domain d_cpf as varchar(11);

create domain d_rg as varchar(15);

SQL – S

TRUTURED

Q

UERY

L

ANGUAGE

| Criando objetos Domínio

y CREATE DOMAIN d_logico VARCHAR(1) CHECK (VALUE IN (‘S’, ‘N’));

y CREATE DOMAIN d_sexo VARCHAR(1) CHECK (VALUE IN (‘M’, ‘F’));

y CREATE DOMAIN d_estado_civil VARCHAR(15) CHECK (VALUE IN (‘CASADO’, ‘SOLTEIRO’, ‘DESQUITADO’, ‘SEPARADO’, ‘OUTROS’));

y CREATE DOMAIN d_data_atual AS DATE DEFAULT 'NOW‘ NOT NULL;

SQL – S

TRUTURED

Q

UERY

L

ANGUAGE

|

Alterando um objeto Domínio

y ALTER DOMAIN d_logico SET DEFAULT ‘x’;

y ALTER DOMAIN nome

{ SET DEFAULT expressão | DROP DEFAULT }

y ALTER DOMAIN nome

{ SET | DROP } NOT NULL

y ALTER DOMAIN nome

ADD restrição_de_domínio

y ALTER DOMAIN nome

DROP CONSTRAINT nome_restrição [ RESTRICT | CASCADE ]

y ALTER DOMAIN nome OWNER TO novo_dono

SQL – S

TRUTURED

Q

UERY

L

ANGUAGE

|

Eliminando um objeto Domínio

( \dD para visualizar todos os domínios existentes )

y DROP DOMAIN d_logico;

SQL – S

TRUTURED

Q

UERY

L

ANGUAGE

Objeto Table

– Definição

y É o local onde armazenamos os dados;

y Regras para definição do objeto TABLE:

| Primeiro criar as tabelas que não possuem chave estrangeira; | A chave primária sempre será um campo não nulo;

| Criar Constraints (dar nomes às chaves primárias e

estrangeiras);

| Constraints são restrições criadas para manter a consistência

da base de dados;

| Não podem existir Contraints com mesmo nome na mesma

base de dados;

| Ex: A definição de uma chave primária é uma restrição; A

SQL – S

TRUTURED

Q

UERY

L

ANGUAGE

|

Criando um Objeto Table

y Tabela Municipio

CREATE TABLE municipio (

id_municipio SERIAL PRIMARY KEY,

nome VARCHAR(35) NOT NULL, uf VARCHAR(2) NOT NULL

);

Ou

CREATE TABLE municipio (

id_municipio SERIAL PRIMARY KEY,

nome d_nome_pessoa NOT NULL,

uf VARCHAR(2) NOT NULL

SQL – S

TRUTURED

Q

UERY

L

ANGUAGE

|

Criando um Objeto Table

CREATE TABLE contador (

id_contador SERIAL PRIMARY KEY, nome VARCHAR(50) NOT NULL, logradouro D_LOGRADOURO, nr D_NUMERO, complemento D_COMPLEMENTO, cep D_CEP, bairro D_BAIRRO, fone D_FONE, fax D_FONE, email D_EMAIL,

id_municipio integer references municipio (id_municipio) On Update Cascade On Delete no Action

SQL – S

TRUTURED

Q

UERY

L

ANGUAGE

|

Integridade referencial

y Integridade referencial é um conjunto de regras e de consistências entre os registros de duas tabelas que se relacionam;

y Como foi visto no modelo relacional:

| quando duas tabelas se relacionam, a chave primária de uma é

copiada para a outra;

| se esses dados forem alterados ou excluídos da tabela original é

necessário verificar o que será feito com os dados e registros duplicados na outra tabela;

SQL – S

TRUTURED

Q

UERY

L

ANGUAGE

|

Integridade referencial

y Quando se define uma integridade referencial, está se definindo o procedimento que será tomado quando esses processos ocorrerem;

y Sejam duas tabelas “A” e “B” que se relacionam através de uma coluna “c” que é a chave primária de “A” e,

portanto, foi repetida em “B” para se fazer o relacionamento;

y Quando se define uma integridade referencial para esse relacionamento, está se definindo que a coluna “c” da

tabela “B” só pode conter valores já cadastrados na coluna “c” da tabela “A”;

SQL – S

TRUTURED

Q

UERY

L

ANGUAGE

|

Integridade referencial

y Existem cinco formas de se manter essa regra quando registros da tabela “A” são excluídos:

Ação Descrição

NO ACTION Produz um erro indicando que a exclusão ou a atualização cria uma

violação da restrição de chave estrangeira. Esta é a ação padrão.

RESTRICT

Produz um erro indicando que a exclusão ou a atualização cria uma

violação da restrição de chave estrangeira. É o mesmo que NO ACTION, exceto que a verificação não é postergável.

CASCADE

Exclui qualquer linha que faça referência à linha excluída, ou atualiza

o valor da coluna que faz referência para o novo valor da coluna referenciada, respectivamente.

SET NULL Atribui o valor nulo às colunas que fazem referência. SET DEFAULT Atribui o valor padrão às colunas que fazem referência.

SQL – S

TRUTURED

Q

UERY

L

ANGUAGE

| Alterando a estrutura de um objeto Table

- Adicionar o campo data do cadastro na tabela contador: Alter Table Contador add Data D_DataAtual;

- Adicionar o campo teste do cadastro na tabela contador: Alter Table Contador add Teste Varchar(10);

- Alterando o tipo de um atributo na entidade contador;

Alter Table Contador alter nome type

varchar(50) not null;

- Eliminando um atributo na entidade contador;

SQL – S

TRUTURED

Q

UERY

L

ANGUAGE

|

Eliminando um objeto Table

drop table municipio;

y Não vai aceitar, pois existe a tabela contador relacionada;

1

_{) drop table contador;}

SQL – S

TRUTURED

Q

UERY

L

ANGUAGE

Resumo:

|

Foi visto a criação dos seguintes objetos no

PostgreSQL:

y Database (Base de Dados)

y Table (Tabelas)

y Index (Índices)