1 Banco de Dados – Comandos DLL do MySQL – 17.09.2013

BANCO DE DADOS MySQL

1. COMANDOS DDL (Data Definition Language)

1.1. CREATE

Comando usado para criar base de dados, tabelas e views (visões). Veremos VISÕES posteriormente.

Criar uma Base de Dados Create database <nomeBD>; Após criar a base de dados, devemos selecioná-la. Use database <nomeBD>;

Criar uma Tabela create table filmes ( id int(10) unsigned not null auto_increment, titulo varchar(80) not null, ano int(4) unsigned not null, diretor varchar(80) not null, primary key (id) );

TABELA COM CHAVE ESTRANGEIRA A criação de uma chave estrangeira é bem mais complexa do que uma chave primária. Primeiramente é necessário nomear a restrição através de uma CONSTRAINT e só então especifica quem é o campo que será chave estrangeira e a qual tabela e campo ela está REFERENCIANDO. Para questão de padronização você pode nomear a CONSTRAINT (nomear a restrição) colocando o seguinte nome: fk_tabelaChaveEstrangeira_tabelaChavePrimária. Abaixo 2 exemplos:

Criar uma Tabela com 1 Chave Estrangeira create table notafiscal (

numnota int(4) not null auto_increment, dataEmissao date, idCliente int(4) not null, primary key (numnota), constraint fk_notafiscal_cliente foreign key (idCliente) REFERENCES cliente(idCliente)

);

Criar uma Tabela com 2 Chaves Estrangeiras create table itemnota (

numItem int(4) not null, codItem int(4) not null, numNota int(4) not null, qtd int(4), valorItem float, primary key (numItem,codItem,numNota), constraint fk_itemnota_produto foreign key (codItem) REFERENCES produto(codItem), constraint fk_itemnota_notafiscal foreign key (numNota) REFERENCES notafiscal(numNota)

);

1.2. DROP Comando usado para excluir. Podemos excluir base de dados, tabelas, views. Excluir uma base de dados drop database nomeDaBase;

Curso Técnico em Informática – 3º Semestre Disciplina: Banco de Dados Professor: Arley Rodrigues Semestre: 2013.1 Turno: Tarde Data: 17/09/2013

2 Banco de Dados – Comandos DLL do MySQL – 17.09.2013

Excluir uma tabela drop table nomeTabela; Excluir uma view drop view nomeView;

1.3. ALTER Comando usado para alterar tabelas, colunas, views, índices, chaves (primária, estrangeira e candidata), etc. As alterações podem ser em nomes, exclusões, inclusões, etc. Adicionar um índice ALTER TABLE nomeTabela ADD INDEX nomeIndice; Remover um índice ALTER TABLE <nomeTabela> DROP INDEX <nomeIndice>; Altera o nome de uma tabela ALTER TABLE <nomeTabela> RENAME TO <novoNome>; Altera o nome dos campos de uma tabela ALTER TABLE <nomeTabela> CHANGE <nomeCampo> <novoNomeCampo> <tipo_campo>; Remove um novo campo de uma tabela ALTER TABLE <nomeTabela> DROP <nomeCampo>; Adiciona um novo campo à uma tabela ALTER TABLE <nomeTabela> ADD <nomeCampo> <tipoCampo>; Adiciona um novo campo à uma tabela após um campo especificado ALTER TABLE <nomeTabela> ADD <nomeCampo> <tipoCampo> AFTER <nomeCampoAnterior>; Adiciona um novo campo à uma tabela na primeira posição ALTER TABLE <nomeTabela> ADD <nomeCampo> <tipoCampo> FIRST; Modifica tipo de um campo da tabela indicada ALTER TABLE <nomeTabela> MODIFY <nomeCampo> <tipoCampo>; Adicionar um campo como chave primária ALTER TABLE nomeTabela ADD PRIMARY KEY (<nomeCampo>); Adicionar vários campos como chave primária ALTER TABLE nomeTabela ADD PRIMARY KEY (<nomeCampo1>, <nomeCampo2>, etc); Excluir uma chave primária ALTER TABLE nomeTabela DROP PRIMARY KEY; Para inserir uma chave estrangeira é necessário nomeá-la através de uma CONSTRAINT (Restrição) ALTER TABLE nomeTabela ADD CONSTRAINT <nomeConstraint> FOREIGN KEY <nomeCampoFK> REFERENCES <nomeBD>.<nomeTabela> (<nomeCampo>); Para apagar uma chave estrangeira é necessário excluir através do nome da sua CONSTRAINT ALTER TABLE nomeTabela DROP FOREIGN KEY nomeConstraint;

3 Banco de Dados – Comandos DLL do MySQL – 17.09.2013

1.4. Informações Adicionais ao Declarar Chave Estrangeira Como sabemos as chaves estrangeiras garante a integridade referencial em relação aos valores contidos em uma outra tabela. Podemos controlar o que pode acontecer com os valores contidos nas chaves estrangeiras quando excluímos ou alteramos valores no campo da chave primária a qual ela faz referência. Quando criamos uma chave estrangeira ela tem 2 ações: ON DELETE e ON UPDATE. A ação ON DELETE está condicionada a deleção de um valor ou do campo que faz parte da chave primária, enquanto o ON UPDATE está com a atualização do mesmo.

As ações ON DELETE e ON UPDATE podem ser configuradas com as opções RESTRICT, CASCADE, SET NULL, NO ACTION, que significam:

CASCADE: Se houver um DELETE da tabela pai, as tabelas filhas também serão deletadas, evitando inconsistência de dados. Se houver um UPDATE na chave primária da tabela pai, as chaves estrangeiras das tabelas filhas também serão atualizadas.

SET NULL: Caso uma a tabela pai seja deletada ou sua chave primária seja alterada, as tabelas filhas terão as suas chaves estrangeiras setadas para NULL (exceto quando forem criadas com o parâmetro NOT NULL).

NO ACTION: A tentativa de deletar ou atualizar uma tabela pai será ignorada caso ela tenha tabelas filhas.

RESTRICT: O mesmo que NO ACTION. Por padrão as chaves estrangeiras são criadas com as seguintes configurações: ON DELETE NO ACTION ON UPDATE NO ACTION. Para você ver como está criada sua tabela basta executar o comando: SHOW CREATE TABLE <nomeTabela>; Exemplo de uma tabela criada com outras opções: CREATE TABLE `cursa` (

`idAluno` int(11) NOT NULL, `idDisciplina` int(11) NOT NULL, `semestre` varchar(6) NOT NULL, PRIMARY KEY (`idAluno`,`idDisciplina`), CONSTRAINT `fk_cursa_alunos` FOREIGN KEY (`idAluno`) REFERENCES `alunos` (`idAlunos`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_cursa_disciplina` FOREIGN KEY (`idDisciplina`) REFERENCES `disciplina` (`idDisciplina`) ON DELETE RESTRICT ON UPDATE NO ACTION

); Para alterar configurações já existentes: ALTER TABLE `aluga` DROP FOREIGN KEY `fk_aluga_cliente` , ADD CONSTRAINT fk_aluga_cliente FOREIGN KEY (`cod_cliente`) REFERENCES `locadora`.`cliente` (`codcliente`) ON DELETE NO ACTION ON UPDATE CASCADE ; ALTER TABLE `aluga` DROP FOREIGN KEY `fk_aluga_dvd` , ADD CONSTRAINT fk_aluga_dvd FOREIGN KEY ( `cod_dvd` ) REFERENCES `locadora`.`dvd` (`CodD`) ON DELETE NO ACTION ON UPDATE CASCADE ; PRATICAR!!! Crie tabelas, faça modificações em sua estrutura adicionando, excluindo, alterando suas colunas. Inclua, exclua chaves primária e estrangeira. Faça testes de restrições referenciais (chave estrangeira), tentando adicionar valores que ainda não existem na chave primária.