Baixe o app para aproveitar ainda mais
Prévia do material em texto
SQL – Structured Query Language Prof. Me. Sirlon Thiago Introdução • SQL - Structured Query Language - Linguagem de Consulta Estruturada • Início dos anos 70 nos laboratórios da IBM • Viabilidade da implementação do modelo relacional proposto por Edgar Codd • Originalmente SEQUEL Introdução • Não procedural, ou seja, forma do resultado ao invés de como chegar ao resultado • Padronizada pela ANSI e ISO no fim da década de 80 e revisada algumas vezes • Portável, mas não 100% Introdução • DDL - Data DefiniWon Language (Linguagem de Definição de Dados) • Principais comandos: CREATE, ALTER e DROP • DML - Data ManipulaWon Language (Linguagem de Manipulação de Dados) • Principais comandos: SELECT, INSERT, UPDATE, DELETE, TRUNCATE e outros. • DCL - Data Control Language (Linguagem de Controle de Dados) • Principais comandos: GRANT, REVOKE e SET. Introdução • SGBD uWlizado -> PostgreSQL • Melhor livro é o próprio manual • Superusuário padrão: postgres • Senha (se não alterada): postgres DDL – Data De5inition Language DDL • Usado para a definição das estruturas de dados, fornecendo as instruções que permitem a criação, modificação e remoção de objetos de banco de dados (base de dados, esquemas, tabelas, índices etc.) DDL • Comandos básicos: • CREATE: cria um objeto (tabelas, índices, usuários etc.) dentro da base de dados. • DROP: apaga um objeto do banco de dados. • ALTER: permite ao usuário alterar um objeto, por exemplo, adicionando uma coluna a uma tabela existente. Usuários Criar Usuários • Criação de uma conta de usuário que pode manipular o BD • Recurso exclusivo a superusuários Criar Usuários - Comando • CREATE USER nome [ [ WITH ] opção [ ... ] ] • onde opção pode ser: • | CREATEDB | NOCREATEDB • | CREATEUSER | NOCREATEUSER • | IN GROUP nome_do_grupo [, ...] • | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'senha' | VALID UNTIL 'data_e_hora' Criar Usuários - Comando • nome • O nome do usuário. • CREATEDB - NOCREATEDB • Estas cláusulas definem a permissão para o usuário criar banco de dados. Se for especificado CREATEDB, o usuário sendo definido terá́ permissão para criar seus próprios bancos de dados. Se for especificado NOCREATEDB, nega-se ao usuário a permissão para criar banco de dados. Se nenhuma destas cláusulas for especificada, o padrão é NOCREATEDB. Criar Usuários - Comando • CREATEUSER - NOCREATEUSER • Estas cláusulas determinam se o usuário pode ou não criar novos usuários. CREATEUSER também torna o usuário um superusuário, o qual pode passar por cima de todas as restrições de acesso. Se nenhuma destas cláusulas for especificada, o padrão é NOCREATEUSER. Criar Usuários - Comando • nome_do_grupo • O nome de um grupo existente onde o usuário será́ incluído como um novo membro. Podem ser especificados nomes de vários grupos. • senha • Define a senha do usuário. Esta opção pode ser omiWda, mas o usuário não poderá́ mais se conectar se for decidido mudar para autenWcação por senha. A senha poderá́ ser definida ou mudada posteriormente através do comando ALTER USER. Criar Usuários - Comando • ENCRYPTED - UNENCRYPTED • Estas cláusulas controlam se a senha será́ armazenada criptografada, ou não, nos catálogos do sistema; Se não for especificada nenhuma destas cláusulas, o comportamento padrão será́ determinado pelo parâmetro de configuração password_encrypWon. • data_e_hora • A cláusula VALID UNTIL define uma data e hora após a qual a senha do usuário não é mais válida. Se esta cláusula for omiWda, a conta será́ válida para sempre. Criar Usuários – Exemplo • Criar um usuário sem senha: • CREATE USER tads; • Criar um usuário com senha no grupo desenvolvedores: • CREATE USER tads PASSWORD ’p455w0rd' IN GROUP desenvolvedores; • Criar um usuário com uma senha válida até o fim de 2016. Após o primeiro segundo de 2017, a senha não será́ mais válida. • CREATE USER tads PASSWORD ’p455w0rd' VALID UNTIL '2017-01-01’; Criar Usuários – Exemplo • Criar uma conta onde o usuário pode criar bancos de dados: • CREATE USER tads PASSWORD ’p455w0rd' CREATEDB; • Criar um superusuário: • CREATE USER “tads” CREATEUSER CREATEDB PASSWORD ’p455w0rd'; Alterar Usuários – Comando • Alteração dos dados de um usuário já existente • Superusuário pode alterar qualquer uma das configurações para qualquer usuário • Usuários comuns podem apenas alterar suas próprias senhas Alterar Usuários – Comando • ALTER USER nome [ [ WITH ] opção [ ... ] ] • ALTER USER nome R ENAME TO novo_nome • ALTER USER nome SET parâmetro { TO | = } { valor | DEFAULT } • ALTER USER nome RESET parâmetro Alterar Usuários – Exemplo • Mudar a senha do usuário: • ALTER USER tads PASSWORD ’s3nh4'; • Mudar a data de expiração da senha do usuário: • ALTER USER tads VALID UNTIL 'Jan 31 2030'; • Mudar a data de expiração da senha, especificando que a senha expira ao meio dia de 4 de maio de 2010: • ALTER USER tads VALID UNTIL 'May 4 2016 12:00:00’; Alterar Usuários – Exemplo • Tornar o usuário válido para sempre: • ALTER USER tads VALID UNTIL 'infinity'; • Dar ao usuário permissão para criar outros usuários e novos bancos de dados: • ALTER USER tads CREATEUSER CREATEDB; Excluir Usuários – Comando • Exclui um usuário do BD • Não remove as tabelas, visões ou outros objetos pertencentes ao usuário • Se o usuário possuir algum banco de dados, uma mensagem de erro é gerada • Não aconselhado excluir usuários que possuam objetos ou permissões a objetos Excluir Usuários – Comando • DROP USER nome • nome • O nome do usuário a ser removido Excluir Usuários – Exemplo • DROP USER tads; Grupos Criar Grupos – Comando • Cria um grupo de usuários no BD • Recurso de superusuário • Conhecido como ROLE no padrão SQL e outros SGBDs Criar Grupos – Comando • CREATE GROUP nome [ [ WITH ] opção [ ... ] ] • onde opção pode ser: • | USER nome_do_usuario [, ...] Criar Grupos – Comando • nome • O nome do grupo. • nome_do_usuario • A lista dos usuários a serem incluídos no grupo. Os usuários devem exisWr. Criar Grupos – Exemplo • Criar um grupo vazio: • CREATE GROUP desenvolvedores; • Criar um grupo com membros: • CREATE GROUP desenvolvedores WITH USER tads, fulano; Alterar Grupos – Comando • Altera atributos de um grupo existente • Recurso de superusuário • Para conhecer o conjunto de grupos existentes, deve ser consultada a tabela do sistema “pg_group” com o seguinte comando: • SELECT groname FROM pg_group; Alterar Grupos – Comando • ALTER GROUP nome_do_grupo ADD USER nome_do_usuario [, ... ] • ALTER GROUP nome_do_grupo DROP USER nome_do_usuario [, ... ] • ALTER GROUP nome_do_grupo RENAME TO novo_nome Alterar Grupos – Comando • nome_do_grupo • O nome do grupo a ser modificado. • nome_do_usuário • Os usuários a serem adicionados ou removidos do grupo. Os usuários devem exisWr; o comando ALTER GROUP não cria nem remove usuários. • novo_nome • O novo nome do grupo. Alterar Grupos – Exemplo • Adicionar usuários a um grupo: • ALTER GROUP desenvolvedores ADD USER ciclano, beltrano; • Remover um usuário de um grupo: • ALTER GROUP desenvolvedoresDROP USER fulano; Excluir Grupos – Comando • Exclui um grupo • Não exclui os usuários • Recurso de superusuário • Não aconselhável excluir grupos que tenham permissão a objetos Excluir Grupos – Comando • DROP GROUP nome • nome_do_grupo • O nome do grupo a ser removido. Excluir Grupos – Exemplo • DROP GROUP desenvolvedores; Bases de Dados Criar Database – Comando • Cria uma nova database • CREATE DATABASE nome • [ [ W I T H ] [ O W N E R [ = ] dono_do_banco_de_dados ] • [ TEMPLATE [=] modelo ] • [ ENCODING [=] codificacao ] • [ TABLESPACE [=] espaco_de_tabelas ] ] Criar Database – Comando • nome • O nome do banco de dados a ser criado. • dono_do_banco_de_dados • O nome do usuário que será́ o proprietário do novo banco de dados. • modelo • Nome do modelo a parWr do qual o novo banco de dados será́ criado. Criar Database – Comando • codificação • Método de codificação do conjunto de caracteres a ser uWlizado no novo banco de dados. (por exemplo, 'UTF8') • espaço_de_tabelas • O nome do espaço de tabelas (tablespace) associado ao novo banco de dados. Criar Database – Exemplo • Para criar um banco de dados: • CREATE DATABASE aula; • Para criar o banco de dados “aula” pertencendo ao usuário “tads” com o tablespace padrão “alutads”: • CREATE DATABASE aula OWNER tads TABLESPACE alutads; Criar Database – Exemplo • Para criar o banco de dados musica com suporte ao conjunto de caracteres ISO-8859-1: • CREATE DATABASE aula ENCODING 'LATIN1'; • Para criar um banco de dados com suporte ao conjunto de caracteres Unicode: • CREATE DATABASE aula WITH OWNER = tads ENCODING = 'UTF8'; Alterar Database – Comando • Alterar as propriedades de uma database • ALTER DATABASE nome SET parametro {TO|=} { |DEFAULT} • ALTER DATABASE nome RESET parâmetro • ALTER DATABASE nome RENAME TO novo_nome • ALTER DATABASE nome OWNER TO novo_dono Alterar Database – Comando • nome • O nome do banco de dados cujos atributos estão sendo alterados. • parâmetro / valor • Define o padrão de sessão deste banco de dados, para o parâmetro de configuração especificado, como o valor fornecido. Se valor for DEFAULT ou, de forma equivalente, se RESET for uWlizado, a definição específica para o banco de dados é removida, e a definição padrão global do sistema passa a ser herdada nas novas sessões. Alterar Database – Comando • novo_nome • O novo nome do banco de dados. • novo_dono • O novo dono do banco de dado. Alterar Database – Exemplo • renomeando um banco de dados • ALTER DATABASE aula RENAME TO aula_tads; • Alterando o padrão de datas para suportar o formato dd/mm/aaaa. • ALTER DATABASE aula_tads SET DateStyle = sql, dmy; • para listar o padrão de data atual use SHOW DATESTYLE; Alterar Database – Exemplo • Alterando o método de codificação de um banco de dados • ALTER DATABASE a u l a _ t ad s S E T client_encoding = laWn1; • para listar a codificação atual use SHOW CLIENT_ENCODING; Excluir Database – Comando • Remove entradas para o banco de dados e o diretório contendo os dados • Executado apenas pelo dono do banco de dados • Não pode ser executado enquanto houver alguém conectado à database • DROP DATABASE nome; Excluir Database – Exemplo • DROP DATABASE aula_tads; Tipos de Dados Tipos de dados • Suportados pelo PostgreSQL: • Numéricos • Monetários (obsoleto) • Strings (caracteres e textos) • Datas e horas • Binários/Booleanos • Rede • Geométricos Tipos de dados - Principais • SmallInt ou INT2: • Números inteiros • Intervalo de -32768 a +32767 • Integer/INT/INT4: • Números inteiros • Intervalo de -2147483648 a +2147483647 • BIGINT: • Valores inteiros • Interva lo de -9223372036854775807 a 9223372036854775807 Tipos de dados - Principais • Real/Float: • Números de ponto flutuante • 6 casas decimais • Serial • Notação para colunas únicas e auto incrementáveis • Intervalo é o posiWvo do Integer • BigSerial • Mesmo que Serial • Intervalo é o posiWvo do BigInt Tipos de dados - Principais • Char(n): • String com um número fixo de caracteres (n) • Preenche a parte não uWlizada com espaços • Varchar: • String com um número fixo de caracteres (n) • Não preenche a parte não uWlizada com espaços • Text: • String com número infinito de caracteres • Até 1Gb de informação Tipos de dados - Principais • Date: • Datas em diversos formatos • Padrão ISO (YYYY-MM-DD) • Intervalo de 4713 AC a 5874897 DC • TIME without Wme zone: • Hora do dia sem fuso horário • Intervalo de 00:00:00 a 24:00:00 • TIME with Wme zone: • Intervalo de 00:00:00+1459 a 24:00:00-1459 Tipos de dados - Principais • Timestamp with/without Wme zone: • Data e hora com/sem fuso horário • Intervalo de 4713 AC a 5874897 DC • BYTEA: • Mesmo que BLOB • Armazena cadeias binárias • UWlizado para armazenar arquivos • Cuidado com desempenho Tipos de dados - Principais • Boolean: • Booleanos Verdadeiro ou Falso • Intervalos: • TRUE, true, ‘YES’,‘yes’, ‘T’,’t’,’Y’,’y’,’1’ • FALSE, false, ‘NO’, ‘no’, ‘F’, ‘f’, ‘N’, ‘n’, ‘0’ Tabelas Introdução • Todos os dados são, conceitualmente, armazenados em tabelas • Colunas / campos / atributos • Linhas / registros / tuplas Introdução • Convenções: • Nomes de tabelas singular ou plural • Minúsculo separado por underline ( _ ) • Nomes de campos seguem padrão de nomes das tabelas Criação - Comando • CREATE [ TEMPORARY | TEMP ] TABLE nome_da_tabela • ( colunas ) • [ opções avançadas ] Criação - Comando • Coluna: • nome_da_coluna Npo_de_dado [DEFAULT valor] [NULL | NOT NULL] • [PRIMARY KEY | UNIQUE] Criação - Exemplo • CREATE TABLE tads ( • id SERIAL NOT NULL, • nome VARCHAR(65) NOT NULL, • cpf VARCHAR(12) NOT NULL, • data_nascimento DATE, • salario FLOAT NOT NULL DEFAULT '1000', • comentario TEXT NULL, • PRIMARY KEY (id), • UNIQUE (cpf) • ); Alteração - Comando • Envolve um conjunto de comandos para realizar diversas ações possíveis como: • Renomear tabelas; • Renomear campos (colunas); • Inserir novos campos; • Remover campos existentes; • Alterar a estrutura de uma coluna (Wpo de dado e/ou seus parâmetros) • As ações que envolvem a alteração de tabelas são realizadas por meio do comando ALTER TABLE. Alteração - Comando • Renomear tabela: • ALTER TABLE nome_da_tabela RENAME TO novo_nome; • Exemplo: • ALTER TABLE tab_tads RENAME TO tabela_tads; Alteração - Comando • Renomear campo: • ALTER TABLE nome_da_tabela RENAME COLUMN nome_da_coluna TO novo_nome; • Exemplo: • ALTER TABLE tabela_tads RENAME COLUMN nome TO nome_cliente; Alteração - Comando • Adicionar campo: • ALTER TABLE nome_da_tabela ADD COLUMN nome_da_coluna Npo_de_dado [parâmetros]; • Exemplo: • ALTER TABLE tabela_tads ADD COLUMN endereco varchar(30) NOT NULL; Alteração - Comando • Remover campo: • ALTER TABLE nome_da_tabela DROP COLUMN nome_da_coluna [RESTRICT | CASCADE]; • Exemplo: • ALTER TABLE tabela_tads DROP COLUMN endereco CASCADE; Alteração - Comando • Alterar campo: • ALTER TABLE nome_da_tabela RENAME|ALTER {TYPE} Npo expressão_SQL; • Exemplo: • ALTER TABLE distribuidores ALTER COLUMN enderecoTYPE varchar(80), ALTER COLUMN nome TYPE varchar(100); • ALTER TABLE distribuidores RENAME COLUMN logradouro TO endereco; • ALTER TABLE distribuidores ALTER COLUMN logradouro SET NOT NULL; • ALTER TABLE distribuidores ALTER COLUMN logradouro DROP NOT NULL; Exclusão - Comando • DROP TABLE nome_da_tabela [CASCADE]; • Se houver registro de ligação dessa tabela a outra (chave estrangeira), a exclusão só será possível uWlizando o parâmetro CASCADE • Caso o CASCADE seja uWlizado, a tabela é e x c l u í d a , b em c omo t o d o s o s relacionamentos dela, ou seja, as chaves estrangeiras nas outras tabelas Exercícios • Executar os comandos de criação de bancos e tabelas referentes aos exercícios da úlWma aula (Biblioteca e Holliwud) • Seguir a folha de exercícios 1
Compartilhar