Banco de Dados Structured Query Language – SQL/DDL Prof. MS Wagner Siqueira Cavalcante Este artigo toma como base os seguintes livros: 01. Silberschatz, Abraham; Korth, Henry & Sudarshan, S. – Sistema de banco de dados, Rio de Janeiro: Campus, 3ª ed., 2005. → Na biblioteca FAC, cite: 005.74 – S576s . 02. ELMASRI, Ramez; NAVATHE, Shamkant – Sistemas de banco de dados. 4.ed. Rio de Janeiro: Addison_Wesley, 2005. → Na biblioteca FAC, cite: 004 – E43s . 03. Costa, Rogério Luis de Carvalho – SQL Guia Prático. 2ª ed. São Paulo: Brasport, 2006. → Na biblioteca FAC, cite: 005.135Sq – C875s – 2.ed . Criação de estrutura de tabelas: CREATE TABLE <nome da tabela> ( <atributo_1> <tipo> [[[NOT] NULL] UNIQUE], ... <atributo_n> <tipo> [[[NOT] NULL] UNIQUE], CONSTRAINT <nome da restrição_1> <RESTRIÇÃO_1>, ... CONSTRAINT <nome da restrição_n> <RESTRIÇÃO_n> ); Alteração de estrutura de tabelas: ALTER TABLE <nome da tabela> ADD <atributo_1> <tipo> [[[NOT] NULL] UNIQUE], ... ADD <atributo_n> <tipo> [[[NOT] NULL] UNIQUE], DROP <atributo_p> <tipo> [[[NOT] NULL] UNIQUE], ... DROP <atributo_z> <tipo> [[[NOT] NULL] UNIQUE], ADD CONSTRAINT <nome da restrição_1> <RESTRIÇÃO_1>, ... ADD CONSTRAINT <nome da restrição_n> <RESTRIÇÃO_n>; Remoção de estrutura de tabelas: DROP TABLE <nome da tabela> [CASCADE CONSTRAINTS] ← com cascade, somente no Oracle! Regras básicas: Quaisquer nomes (bancos de dados, tabelas, restrições, atributos, etc.): 1. Codificados, somente quando exigidos, porém, de preferência, semelhantes ao seu significado. 2. Iniciar por caractere alfabético, continuar com alfabéticos, numéricos e sublinhado (nada de especiais!). 3. Usar nomes de tamanho razoável (de preferência, até uns 10 caracteres). 4. Não usar palavras reservadas nos nomes (como “constraint”, “select”, “where”, etc.) Tipos de Dados Descrição CHARACTER (n) Caractere de tamanho fixo - usualmente conhecido como CHAR CHARACTERVARYING (n) Caractere de tamanho variante - usualmente conhecido como VARCHAR CHARACTER LARGE OBJECT Caractere longo - usualmente conhecido como CLOB BINARY LARGE OBJECT String binário para objetos longos - usualmente conhecido como BLOB NUMERIC (n,d) Numérico exato, com casas decimais [-238+1..238-1 → number (n,d)] DECIMAL (n,d) Numérico exato, com casas decimais [-238+1..238-1 → number (n,d)] SMALLINT Numérico exato inteiro [-215..215+1 → number (5)] INTEGER Numérico exato inteiro [-231..231+1 → number (10)] BIGINT Numérico exato inteiro [-263..263+1 → number (19)] FLOAT Numérico aproximado [-1,797+308..-2,225-308,0,+2,225-308..1,797+308] REAL Numérico aproximado [-3,40+38..-1,18-38,0,+1,18-38..3,40+38] DOUBLE PRECISION Numérico aproximado BOOLEAN Booleano [TRUE / FALSE] DATE Data com informações de dia, mês e ano [dd/mm/aaaa] TIME Hora com informações de hora, minuto e segundo [hh:mm:ss] TIMESTAMP Momento, com informações de ano, mês, dia, hora, minuto e segundo Banco de Dados Structured Query Language – SQL/DDL Prof. MS Wagner Siqueira Cavalcante → Restrições: 1. PRIMARY KEY (<atributo_PK>) somente uma única PK, lógico! Uma PK pode ser composta... 2. FOREIGN KEY (<atributo_FK_nesta_tabela>) REFERENCES <tabela_relacionada> <atributo_PK_na_relacionada> [ON UPDATE [DEFAULT] [NULL] [CASCADE] [NO ACTION]] [ON DELETE [DEFAULT] [NULL] [CASCADE] [NO ACTION]] 3. CHECK (<atributo> <operando> (<conjunto_de_valores>)) Na página 11, a figura 1.3 propõe um modelo (MER) para o desenvolvimento de uma base de dados e consultas sobre ela. Código Matrícula Nome CPF Data de nascimento Endereço nacionalidade Nome Editora Preço Lançamento 0,1 publica 0,N Livro 1,N Autor 1,1 Assunto escrito por 1,N 0,N Código Data de contrato é de um Descrição Sigla Figura 1.3 Criação das estrutura das tabelas: Tabela Coluna Tipo de Dados Editora Código Numérico inteiro com valor máximo inferior a 100 Nome Cadeia de caracteres de tamanho variável, com tamanho máximo igual a 80 caracteres Assunto Sigla Cadeia de caracteres de tamanho fixo igual a 1 caractere Descrição Cadeia de caracteres de tamanho variável, com tamanho máximo igual a 50 caracteres Autor Matrícula Numérico inteiro com valor máximo inferior a 1000 Nome Cadeia de caracteres de tamanho variável, com tamanho máximo igual a 80 caracteres CPF Cadeia de caracteres de tamanho fixo igual a 11 caracteres Endereço Cadeia de caracteres de tamanho variável, com tamanho máximo igual a 120 caracteres Data nascimento Data Nacionalidade Cadeia de caracteres de tamanho variável, com tamanho máximo igual a 30 caracteres Livro Código Numérico inteiro com valor máximo inferior a 1000 Título Cadeia de caracteres de tamanho variável, com tamanho máximo igual a 80 caracteres Preço Número real Lançamento Data Assunto 0 mesmo tipo de dados da coluna SIGLA da tabela ASSUNTO Editora 0 mesmo tipo de dados da coluna CÓDIGO da tabela EDITORA Autor_Livro Código_livro 0 mesmo tipo de dados da coluna CÓDIGO da tabela LIVRO Matrícula 0 mesmo tipo de dados da coluna MATRICULA da tabela AUTOR Req Sim Sim Sim Sim Sim Sim Sim Sim Sim Sim Sim Sim Não Não Sim Não Sim Sim Banco de Dados Structured Query Language – SQL/DDL Prof. MS Wagner Siqueira Cavalcante Tabela Editora: CREATE TABLE Editora ( Codigo SMALLINT Nome VARCHAR (50) CONSTRAINT NOT NULL, NOT NULL, PK_Editora PRIMARY KEY (Codigo)); → OU ← CREATE TABLE Editora ( Codigo SMALLINT CONSTRAINT Nome VARCHAR (50) → OU ← CREATE TABLE Editora ( Codigo SMALLINT Nome VARCHAR (50) ALTER TABLE Editora ADD CONSTRAINT PRIMARY KEY Tabela Assunto: CREATE TABLE Assunto ( Sigla CHAR (01) Descrição VARCHAR (50) CONSTRAINT PK_Assunto Tabela Autor: CREATE TABLE Autor ( Matricula SMALLINT Nome VARCHAR(80) CPF CHAR (11) Endereco VARCHAR (120) Nascim DATE Nacional VARCHAR (30) CONSTRAINT PK_Autor Tabela Livro: CREATE TABLE Livro ( Codigo SMALLINT Titulo VARCHAR(80) Preco NUMERIC(10,2), Lancamento DATE, Assunto CHAR (01) Editora SMALLINT, CONSTRAINT PK_Livro CONSTRAINT FK_Assunto CONSTRAINT FK_Editora Tabela Autor-Livro: CREATE TABLE Autor_Livro ( Codigo_Livro SMALLINT Matricula SMALLINT CONSTRAINT FK_Livro CONSTRAINT FK_Autor ← Execute e Commit NOT NULL PK_Livro PRIMARY KEY, NOT NULL); ← Execute e Commit NOT NULL, NOT NULL); ← Execute e Commit PK_Editora (Codigo); ← Execute e Commit NOT NULL, NOT NULL, PRIMARY KEY (Sigla)); NOT NULL, NOT NULL, NOT NULL UNIQUE, NOT NULL, NOT NULL, NOT NULL, PRIMARY KEY (Matricula)); NOT NULL, NOT NULL, NOT NULL, PRIMARY KEY (Codigo), FOREIGN KEY (Assunto) REFERENCES Assunto(Sigla), FOREIGN KEY (Editora) REFERENCES Editora(Codigo)); NOT NULL, NOT NULL, FOREIGN KEY (Codigo_Livro) REFERENCES Livro (Codigo), FOREIGN KEY (Matricula) REFERENCES Autor); ← Execute e Commit Banco de Dados Structured Query Language – SQL/DDL Prof. MS Wagner Siqueira Cavalcante Alterar e remover estruturas de tabelas (página 35...): 1. Inserir o atributo "Sexo" à tabela Autor, com "M" como padrão: ALTER TABLE Autor ADD Sexo CHAR DEFAULT 'M'; 2. Adicionando a restrição de sexo = 'F' ou 'M': ALTER TABLE Autor ADD CONSTRAINT CK_Autor CHECK (Sexo in ('F', 'M')); → Experimente inserir dados para um Autor, com sexo diferente de “F” ou “M” (mesmo minúsculas). 3. Removendo a restrição de sexo = 'F' ou 'M' ALTER TABLE Autor DROP CONSTRAINT CK_Autor; 4. Excluindo o atributo "Sexo" da tabela autor (página 36) ALTER TABLE Autor DROP Sexo;