MySQL01 > MySQL02 > MySQL03 > MySQL04

MySQL04

Neste módulo vamos ver:

INSERT

O comando INSERT na sua forma mais simples tem 3 sintaxes alternativas:

Sintaxe #1

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
);

bthome.gifTopo

Sintaxe #2

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'
);

bthome.gifTopo

Sintaxe #3

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.

bthome.gifTopo

Incremento automático da Primary Key

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;

bthome.gifTopo

DELETE

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.

bthome.gifTopo

UPDATE

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';

bthome.gifTopo

REPLACE

O comando REPLACE tem a mesma sintaxe que o comando INSERT e funciona do modo seguinte:

bthome.gifTopo

Alterar tabelas

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;

note04.gif

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);

note04.gif

O comando abaixo muda o nome de uma tabela:

alter table customer rename clients;
alter table clients rename customer;

bthome.gifTopo

Índices

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);

bthome.gifTopo

FK - Foreign keys

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;

bthome.gifTopo

Trabalhar com grandes volumes de dados

Neste ponto vamos ver um conjunto de comandos que permitem trabalhar com grandes volumes de dados.

bthome.gifTopo

Criar tabela com SELECT

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;

note04.gif

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;

note04.gif

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;

bthome.gifTopo

Inserir dados com SELECT

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;

bthome.gifTopo

Exportar dados de tabela e importar em tabela

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 '';

bthome.gifTopo

Stored procedure

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;

bthome.gifTopo

Ordenação de caracteres

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é' 

bthome.gifTopo

Transacções em MySQL

Este documento descreve como funcionam as transacções em MySQL.

bthome.gifTopo


Realizado por Turtle Learning ®. Última alteração em 2011-02-26