Neste módulo vamos ver:
O comando INSERT na sua forma mais simples tem 3 sintaxes alternativas:
insert into customer values ( 651, 'Willians', 'Lucy', 'E', 3, '272 Station Street', 'Carlton North', 'VIC', '3054', 12, '(613)83008460', '2002-07-02' );
O primeiro valor é inserido na primeira coluna, o segundo valor na segunda coluna, etc. A ordem de INSERT é a mesma pela qual foram criadas as colunas na tabela. Para ver qual a ordem em que foram criadas as colunas executar:
show columns from customer;
Para validar o INSERT podemos executar a consulta abaixo:
select * from customer where surname like 'Willians%' and firstname like 'Lucy%';
O inconveniente desta sintaxe é que quando se adicionam colunas na tabela, mesmo que suportem valores NULL, a instrução deixa de funcionar. Se esta instrução for usada no código de uma aplicação esta deixa de funcionar, pelo que temos que fazer alterações ao código para que funcione novamente.
Para não inserir dados numa coluna é preciso colocar NULL na sua posição. A base de dados aceita desde que essa coluna suporte NULL ou tenha um valor DEFAULT. Se tiver DEFAULT esse será o valor inserido na coluna. Ver exemplo abaixo:
insert into customer values ( 652, 'Willians', 'Lucy', NULL, 3, NULL, NULL, NULL, NULL, 12, NULL, NULL );
A instrução INSERT em MySQL permite inserir mais que uma linha por comando:
insert into customer values ( 653, 'Willians', 'Lucy', 'E', 3, '272 Station Street', 'Carlton North', 'VIC', '3054', 12, '(613)83008460', '2002-07-02' ), ( 654, 'Willians', 'Selina', 'J', 4, '12 Hotham Street', 'Collingwood', 'VIC', '3066', 12, '(613)99255432', '1980-06-03' );
A sintaxe abaixo dá mais trabalho a escrever mas suporta alterações na tabela quando se trata da adição de colunas, desde que estas permitam NULL ou tenham a cláusula DEFAULT.
insert into customer ( cust_id, surname, firstname, initial, title_id, address, city, state, zipcode, country_id, phone, birth_date ) values ( 655, 'Willians', 'Lucy', 'E', 3, '272 Station Street', 'Carlton North', 'VIC', '3054', 12, '(613)83008460', '2002-07-02' );
A mesma instrução pode também ser escrita desta forma:
insert into customer set cust_id = 656, surname = 'Willians', firstname = 'Lucy', initial = 'E', title_id = 3, address = '272 Station Street', city = 'Carlton North', state = 'VIC', zipcode = '3054', country_id = 12, phone = '(613)83008460', birth_date = '2002-07-02';
Com esta sintaxe podemos não introduzir valores nas colunas que suportam NULL:
insert into customer ( cust_id, surname, firstname, city ) values ( 657, 'Willians', 'Lucy', 'Carlton North' );
As colunas não referenciadas ficam com o valor DEFAULT e se este não estiver definido ficam com NULL.
O MySQL possui o modificador de coluna AUTO_INCREMENT que garante a inserção de um número sempre diferente na coluna onde é utilizado. O modificador guarda o último valor inserido o que permite que no próximo INSERT esse valor seja incrementado automaticamente de uma unidade e o novo valor seja atribuído na nova linha. Apenas uma coluna da tabela pode ter este modificador.
Esta funcionalidade não está definida na norma ISO/ANSI do SQL mas todas as bases de dados a disponibilizam, se não da mesma forma, através de um mecanismo equivalente. Quando vários utilizadores fazem INSERT em simultâneo este mecanismo evita a contenção que resultaria de bloquear a tabela procurando o maior valor e em seguinda incrementar uma unidade.
Para exemplificar a utilização de auto_increment vamos começar por criar uma tabela:
create table nomes ( id smallint(4) not null auto_increment, nome varchar(100), primary key (id) );
Podemos inserir uma linha indicando apenas o nome:
insert into nomes (nome) values ("Pedro"); insert into nomes (nome) values ("João");
Para verificar que as instruções INSERT foram executadas:
select * from nomes;
O comando DELETE é utilizado para remover linhas de uma tabela. Se não for utilizada a cláusula WHERE são removidas todas as linhas, como no exemplo abaixo:
delete from mycustomer;
No próximo exemplo são removidas todas as linhas referentes ao cliente Lucy Willians:
delete from customer where surname like 'Willians%' and firstname like 'Lucy%';
No fim do comando anterior a base de dados informa-nos de quantas linhas foram removidas.
Uma instrução UPDATE permite alterar:
Para obter o que foi descrito acima usamos:
Este exemplo converte para maiúsculas a coluna STATE em todas as linhas da tabela:
update customer set state = upper(state);
Este exemplo converte para maiúsculas as colunas STATE e CITY em todas as linhas da tabela:
update customer set state = upper(state), city = upper(city);
Este exemplo altera a coluna SURNAME apenas numa linha:
update customer set surname = 'Smith' where cust_id = '7';
Este exemplo altera a coluna ZIPCODE para todas as linhas que têm o valor 'Melbourne' na coluna CITY:
update customer set zipcode = '3001' where city = 'Melbourne';
O comando REPLACE tem a mesma sintaxe que o comando INSERT e funciona do modo seguinte:
As tabelas podem ser alteradas usando ferramentas gráficas como o MySQL Administrator ou o DBDesigner, que têm uma utilização mais intuítiva que a linha de comando. Todas as instruções dadas usando esse interface são traduzidas em comandos SQL. Por esse motivo vamos apresentar os comandos mais importantes para alteração de tabelas.
O comando abaixo adiciona um índice sobre a coluna city da tabela CUSTOMER. O nome do índice será city_idx.
alter table customer add index city_idx (city);
Para apagar o índice temos dois comandos alternativos:
alter table customer drop index city_idx; drop index city_idx on customer;
O comando abaixo permite adicionar uma coluna na tabela CUSTOMER:
alter table customer add fax varchar(15);
O comando anterior coloca a nova coluna no fim da lista de colunas. O MySQL permite que a coluna seja adicionada numa posição intermédia:
alter table customer add fax varchar(15) after telefone; alter table customer add fax varchar(15) first;
O comando abaixo apaga a coluna criada no comando anterior:
alter table customer drop fax;
Os comandos abaixo modificam o tipo de dados suportado por uma coluna:
alter table customer modify cust_id smallint; alter table customer modify cust_id int(5); alter table customer modify surname char(50); alter table customer modify surname varchar(50) after firstname;
O comando abaixo modifica o nome da coluna:
alter table customer change cust_id id smallint; alter table customer change id cust_id int(5);
O comando abaixo muda o nome de uma tabela:
alter table customer rename clients; alter table clients rename customer;
Os índices são estruturas de dados que aceleram as pesquisas. São constituídos sobre uma ou mais colunas e dão suporte às UNIQUE KEYS, PRIMARY KEYS e FOREIGN KEYS. O MySQL suporta os seguintes tipos de índices:
As estruturas de dados que implementam estes índices podem ser dos tipos abaixo indicados:
Os índices podem ser criados com a tabela ou adicionados com um ALTER TABLE. O exemplo abaixo cria os índices com a tabela:
drop table MyCustomer; create table MyCustomer ( cust_id int(5) NOT NULL, surname varchar(50) default NULL, firstname varchar(50) default NULL, initial char(1) default NULL, title_id int(2) default NULL, address varchar(50) default NULL, city varchar(50) default NULL, state varchar(20) default NULL, zipcode varchar(10) default NULL, country_id int(4) default NULL, phone varchar(15) default NULL, birth_date char(10) default NULL, PRIMARY KEY (cust_id), UNIQUE fullname (firstname, surname) )ENGINE=InnoDB DEFAULT CHARSET=latin1;
Os exemplos abaixo alteram a tabela para criar o índice:
ALTER TABLE mycustomer ADD PRIMARY KEY teste(cust_id); ALTER TABLE mycustomer ADD INDEX firstname(firstname); ALTER TABLE mycustomer ADD UNIQUE fullname(firstname,lastname); ALTER TABLE mycustomer ADD FULLTEXT idx_pesquisa_texto(firstname);
Quando as tabelas são criadas usando o motor InnoDB a base de dados MySQL permite a criação e a manutenção de "Foreign Keys". Uma Foreign Key define um relacionamento entre duas tabelas. No nosso exemplo um cliente tem sempre um pais associado e portanto a tabela CUSTOMER recebe o identificador da tabela COUNTRIES. Esse identificador é a Foreig Key (chave estrangeira). A base de dados permite a definição desta regra passando a garantir que:
A activação destas regras aporta as seguintes vantagens:
A FK pode ser definida quando se cria a tabela ou pode ser adicionada à posteriori com um comando ALTER TABLE. O exemplo abaixo cria a tabela MYCUSTOMER com as duas FKs:
drop table MyCustomer; create table MyCustomer ( cust_id int(5) NOT NULL, surname varchar(50) default NULL, firstname varchar(50) default NULL, initial char(1) default NULL, title_id int(2) default NULL, address varchar(50) default NULL, city varchar(50) default NULL, state varchar(20) default NULL, zipcode varchar(10) default NULL, country_id int(4) default NULL, phone varchar(15) default NULL, birth_date char(10) default NULL, constraint pk_01 primary key (cust_id), key fk_mycustomer_1 (country_id), CONSTRAINT FK_mycustomer_1 FOREIGN KEY (country_id) REFERENCES countries(country_id) ON DELETE RESTRICT ON UPDATE CASCADE, KEY FK_mycustomer_2 (title_id), CONSTRAINT FK_mycustomer_2 FOREIGN KEY (title_id) REFERENCES titles(title_id) ON DELETE RESTRICT ON UPDATE CASCADE )engine=innodb default charset=latin1;
O exemplo abaixo cria a tabela MyCustomer sem FKs e depois altera-a adicionando as FK's:
drop table if exists MyCustomer; create table MyCustomer ( cust_id int(5) NOT NULL, surname varchar(50) default NULL, firstname varchar(50) default NULL, initial char(1) default NULL, title_id int(2), address varchar(50) default NULL, city varchar(50) default NULL, state varchar(20) default NULL, zipcode varchar(10) default NULL, country_id int(4) default NULL, phone varchar(15) default NULL, birth_date char(10) default NULL, constraint pk_01 primary key (cust_id) )engine=innodb default charset=latin1; ALTER TABLE mycustomer ADD KEY fk_mycustomer_1 (country_id); ALTER TABLE mycustomer ADD CONSTRAINT FK_mycustomer_1 FOREIGN KEY (country_id) REFERENCES countries(country_id) ON DELETE RESTRICT ON UPDATE CASCADE; ALTER TABLE mycustomer ADD KEY FK_mycustomer_2 (title_id); ALTER TABLE mycustomer ADD CONSTRAINT FK_mycustomer_2 FOREIGN KEY FK_mycustomer_2(title_id) REFERENCES titles(title_id) ON DELETE RESTRICT ON UPDATE CASCADE;
Neste ponto vamos ver um conjunto de comandos que permitem trabalhar com grandes volumes de dados.
O comando CREATE TABLE ... SELECT permite criar uma tabela à semelhança de outra e inserir dados:
drop table if exists sales_until_now ; create table sales_until_now select c.cust_id, c.surname, c.firstname, sum(price) as totalsales from customer c inner join orders o on (c.cust_id=o.cust_id) inner join items i on (o.cust_id=i.cust_id and o.order_id=i.order_id) group by i.cust_id;
Uma variante do comando anterior permite-nos definir os nossos próprios nomes e tipos para as colunas:
drop table if exists sales_until_now; CREATE TABLE sales_until_now ( cust_id2 int(6) NOT NULL default 0, surname2 varchar(60) default NULL, firstname2 varchar(60) default NULL, totalsales2 decimal(27,2) default NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 select c.cust_id, c.surname, c.firstname, sum(price) from customer c inner join orders o on (c.cust_id=o.cust_id) inner join items i on (o.cust_id=i.cust_id and o.order_id=i.order_id) group by i.cust_id;
Uma variante do comando CREATE TABLE permite-nos criar uma nova tabela como cópia integral da tabela inicial, mas sem linhas:
create table sales_until_yesterday like sales_until_now;
O comando INSERT ... SELECT permite inserir linhas resultantes de um comando SELECT:
drop table if exists sales_until_now; CREATE TABLE sales_until_now ( cust_id int(5) NOT NULL default 0, surname varchar(50) default NULL, firstname varchar(50) default NULL, totalsales decimal(27,2) default NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; insert into sales_until_now (cust_id, surname, firstname, totalsales) select c.cust_id, c.surname, c.firstname, sum(price) from customer c inner join orders o on (c.cust_id=o.cust_id) inner join items i on (o.cust_id=i.cust_id and o.order_id=i.order_id) group by i.cust_id;
Para exportar dados de uma tabela podemos usar as opções abaixo do comando SELECT. O ficheiro fica guardado no servidor onde corre a BD.
SELECT * INTO OUTFILE '/tmp/products.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' FROM products;
O comando LOAD permite fazer carregamentos de dados a partir de um ficheiro de texto. Os dados estão dentro do ficheiro em formato CSV (Comma Separeted Value) ou equivalente.
LOAD DATA INFILE 'D:/Documents and Settings/aser/My Documents/mysql/evento.txt' INTO TABLE dpm2.evento FIELDS TERMINATED BY '||' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' STARTING BY '';
O mesmo comando pode ser invocado no sistema operativo:
mysql -udpm2 -pxpto$1 -e "LOAD DATA INFILE 'D:/Documents and Settings/aser/My Documents/mysql/evento.txt' INTO TABLE dpm2.evento FIELDS TERMINATED BY '||' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' STARTING BY ''"
Se a ordem das colunas no ficheiro txt não corresponder à ordem das colunas na tabela podemos adicionar a clausula (coluna1, coluna2, coluna3).
Se a tabela já estiver preenchida podemos utilizar as clausulas REPLACE ou IGNORE para substituir os valores já existentes ou ignorá-los.
LOAD DATA INFILE 'D:/Documents and Settings/aser/My Documents/mysql/evento.txt' REPLACE INTO TABLE dpm2.evento FIELDS TERMINATED BY '||' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' STARTING BY '';
Um SGBD permite a definição de código dentro da base de dados e ao lado dos dados. Este código foi especialmente concebido para lidar com os dados, permitindo o processamento de forma muito mais rápida que fora da base de dados, pelas seguintes razões:
O próximo exemplo mostra como se define uma stored procedure:
DELIMITER $$ CREATE DEFINER=`teste`@`localhost` PROCEDURE myProc (IN var1 VARCHAR(100), OUT var2 VARCHAR(100)) BEGIN SET var2 = concat('Concatenar este texto com ', var1) ; END $$ DELIMITER ;
O código abaixo mostra como se chama a stored procedure:
set @varTextoResposta = ""; CALL myProc("texto colocado no parâmetro", @varTextoResposta); select @varTextoResposta;
A sequência de comandos apresentada abaixo mostra como a ordenação de caracteres é influênciada pelo conjunto de caracteres usado na tabela (ou na coluna) e pela sequência de "collate":
#apagar a tabela se existir drop table if exists teste; #criar a tabela teste create table teste (nome varchar(50) charset latin1 collate latin1_bin); #inserir dados na tabela teste insert into teste (nome) values ('José'),('João'),('joão'),('joaquim'),('josé'),('Joaquim'); #ver as linhas sem ordenação (em principio pela ordem de inserção) select * from teste; #ver as linhas com ordenação select * from teste order by nome; #O critério de ordenação é binário, pelo que José fica antes de João e este antes de joão. #Alterar o collate para ordenação latin_swedish_case_sensitive ALTER TABLE teste convert to CHARACTER SET latin1 COLLATE latin1_general_cs; #ver as linhas com ordenação select * from teste order by nome; #Agora João aparece antes de José, mas este fica antes de joão (case sensitive) #Nota: Joaquim aparece antes de João !!! Não devia !!!!!!!! #Alterar o collate para ordenação latin_swedish_case_insensitive ALTER TABLE teste convert to CHARACTER SET latin1 COLLATE latin1_swedish_ci; #ver as linhas com ordenação select * from teste order by nome; #Agora João e joão aparecem antes de José (case insensitive) #Os dois 'Joaquim' aparecem depois dos dois João, o que está correcto. # O collate mais correcto é LATIN_SWEDISH_CI # O comando seguinte permite fazer uma pesquisa usando COLLATE LATIN1_BIN quando a # definição na tabela é LATIN_SWEDISH_CI. update teste set nome='José___' where nome collate latin1_bin ='José'
Este documento descreve como funcionam as transacções em MySQL.
Realizado por Turtle Learning ®. Última alteração em 2011-02-26