MySql Operações com SQL básico continuação

MySQL - Operações com SQL básico
Para testar se o MySQL esta instalado corretamente , execute a seguinte linha no
prompt do DOS:
c:\mysql\bin\mysql
Se tudo estiver nos seus devidos lugares você vai receber a seguinte tela:
Vamos então a partir daqui mostrar alguns conceitos básicos relacionados com
comandos SQL para o MySQL no Windows usando o prompt de comandos. Embora
existam programas com interfaces gráficas que fazem este serviço acho bom você
conhecer esta opção.
Os comandos serão digitados na linha de comando ; após o termino de cada linha
tecle Enter e para executar o comando tecle ; e Enter.
1- Para exibir os banco de dados disponíveis use o comando : Show DataBases
O resultado do comando esta
exibindo os três banco de dados
que eu tenho disponível no meu
MySQL
Nota: O comando Select
Database(); exibe o database
atual.
2- Agora eu vou criar um novo banco de dados chamado Teste usando o comando
: Create Database e a seguir irei exibir os banco de dados existentes , conforme
abaixo:
3- Para trabalhar com um banco de dados usamos o comando : Use
<nome_do_banco_de_dados> . Vamos usar o banco de dados Teste e exibir as
tabelas existentes usando o comando Show Tables:
O comando Show tables não encontrou nenhuma tabela
no banco de dados Teste
Nota: Para excluir um banco de dados use comando : DROP DATABASE
<nome_do_banco_de_dados>
4- Vamos criar uma tabela no banco de dados Teste. Para isto usamos o comando
Create Table com a seguinte sintaxe:
CREATE TABLE table_name (column_name1 type [modifiers]
[, column_name2 type [modifiers]])
Vamos criar uma tabela chamada Clientes com seguinte estrutura:
•
•
•
•
Codigo - chave primária
Nome - VarChar(30)
Endereco - VarChar(30)
Estado - char(2)
nota: Para definir a coluna como do tipo autoincremental basta acrescentar
:AUTO_INCREMENT na definição da coluna. Para o exemplo acima faríamos:
CODIGO BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT
5- Para inserir dados na tabela usamos o comando Insert Into :
INSERT INTO table_name (column1, column2, ..., columnN)
VALUES (value1, value2, ..., valueN)
Vamos inserir um registro na tabela usando a instrução SQL abaixo:
INSERT INTO CLIENTES (Codigo,Nome, endereco, estado)
VALUES(1,'Macoratti', 'Av. Mirassol 100', 'SP');
6- Se quisermos obter informações detalhadas da estrutura da tabela usamos o
comando : DESCRIBE <NOME_DA_TABELA>
Nota: Temos abaixo os principais tipos de dados
Tipo de
Dados
Descrição
INT
Representa um valor inteiro. Pode ser com sinal ou sem sinal
REAL
Representa um valor com ponto flutuante. Oferece uma grande
precisão e uma extensa faixa de valores
CHAR(n)
Representa um valor caractere com tamanho fixo.
TEXT
Representa um valor para caractere com tamanho variável
DATE
Representa um valor de data padrão. Formato : YYYY-MM-DD (200101-01)
TIME
Representa um valor de tempo padrão. Armazena a hora de um dia
independente de uma data particular. Formato : hh:mm:ss (06:00:00)
7- Para excluir uma tabela basta usar o comando DROP <nome_da_tabela>
8- Para atualizar uma coluna podemos usar o comando : UPDATE . Abaixo uma
das sintaxes possíveis onde atualizamos a coluna Endereco.
A sintaxe para o
comando UPDATE é :
UPDATE
table_name
SET
column1=value1,
column2=value2,
...,
columnN=valueN
[WHERE clause]
9 - Para excluir uma linha da tabela usamos o comando DELETE . Abaixo estou
excluindo a linha inserida acima . Note que é aconselhável especificar a condição na
cláusula WHERE.
A sintaxe é :
DELETE FROM table_name
[WHERE clause]
10 - Vamos realizar uma junção de duas tabelas. Para isto vamos criar uma tabela
chamada veículos que estará relacionada coma tabela teste criada acima e conterá
os dados dos veículos de cada cliente:
Exibindo as tabelas existentes e a estrutura da tabela criada teremos:
Vamos agora incluir alguns dados na tabela teste e na tabela veículos usando
INSERT INTO.
Obs: Podemos usar a notação Insert Into values() : Ex: insert into veiculos
values( 1, 'MERCEDES', 'BRANCA', 1, 3000)
Abaixo temos o resultado final das incluções:
Vamos fazer a nossa junção. Usamos para isto a cláusula SELECT e definimos a
tabela e o nome do campo que desejamos exibir separados por um ponto ; a seguir
relacionamos as tabelas de dados na cláusula FROM e definimos a condição na
cláusual WHERE .
O resultado é exibido abaixo:
O que foi mostrado aqui é apenas a ponto do iceberg , mas com os conceitos
expostos você pode se virar usando comandos SQL básicos na linha de comando do
prompt do MySQL.
ALTER TABLE nome_da_tabela especificações_para_alteração;
As especificações para anteração devem ser listadas e separadas por vírgula, a natureza
delas pode variar: excluir colunas, adicionar colunas, modificar uma coluna,
adicionar/renomear um índice/chave, etc. Algumas das especificações mais comumente
usadas são:
•
•
•
•
ADD COLUMN nome_da_nova_coluna
DROP COLUMN nome_da_coluna
MODIFY COLUMN nome_da_coluna definições_da_coluna
ADD INDEX/KEY nome_do_indice (lista_de_colunas)
•
•
•
DROP INDEX/KEY nome_do_indice
ADD PRIMARY KEY (lista_de_colunas)
DROP PRIMARY KEY
Veja alguns exemplos:
ALTER TABLE tabela MODIFY COLUMN coluna_6 INT NOT NULL;
Isso modificaria a coluna chamada coluna_6 para INT e NOT NULL.
ALTER TABLE tabela ADD INDEX indice(coluna_1, coluna_2);
O comando acima adiciona à tabela de nome tabela um índice de nome indice
referente às colunas coluna_1 e coluna_2.
ALTER TABLE tabela DROP PRIMARY KEY, ADD PRIMARY KEY
(coluna_1, coluna_3, coluna_4);
Este comando apara a atual chave primária da tabala chamada tabela e adiciona,
em seguida, uma nova com as colunas coluna_1, coluna_3 e coluna_4.
ALTER TABLE tabela DROP COLUMN coluna_0;
Este último comando elimina a coluna coluna_0 da tabela chamada tabela.
Seleção de tabelas I
Como realizar seleções eficientemente. Exemplos práticos.
A seleção total ou parcial de uma tabela se realiza mediante a instrução Select. Em tal seleção
há que especificar:
-Os campos que queremos selecionar
-A tabela na qual fazemos a seleção
Em nossa tabela modelo de clientes poderíamos fazer, por exemplo, uma seleção do nome e
endereço dos clientes com uma instrução deste tipo:
Select nome, endereço From clientes
Se quiséssemos selecionar todos os campos, ou seja, toda a tabela, poderíamos utilizar o
asterisco * da seguinte forma:
Select * From clientes
Também é muito útil filtrar os registros mediante condições que vêem expressas depois da
cláusula Where. Se quiséssemos mostrar os clientes de uma determinada cidade usaríamos
uma expressão como esta:
Select * From clientes Where cidade Like 'Rio de janeiro'
Ademais, poderíamos ordenar os resultados em função de um ou vários de seus campos. Para
este último exemplo poderíamos ordená-los por nome assim:
Select * From clientes Where cidade Like 'Rio de janeiro' Order By nome
Tendo em conta que pode haver mais de um cliente com o esmo nome, poderíamos dar um
segundo critério que poderia ser o sobrenome:
Select * From clientes Where cidade Like 'Rio de Janeiro' Order By nome, sobrenome
Se invertêssemos a ordem " nome,sobrenome " por " sobrenome, nome ", o resultado seria
diferente. Teríamos os clientes ordenados por sobrenome e aqueles que tivessem sobrenomes
idênticos se sub-classificariam pelo nome.
É possível também classificar por ordem inversa. Se por exemplo quiséssemos ver nossos
clientes por ordem de pedidos realizados tendo aos maiores em primeiro lugar escreveríamos
algo assim:
Select * From clientes Order By pedidos Decres
Uma opção interessante é a de efetuar seleções sem coincidência. Se por exemplo,
buscássemos saber em que cidades se encontram nossos clientes sem a necessidade de que
para isso apareça várias vezes a mesma cidade, usaríamos uma sentença desta classe:
Select Distinct cidade From clientes Order By cidade
Assim evitaríamos ver repetido Rio de Janeiro tantas vezes quantos clientes tivermos nessa
cidade.
Seleção de tabelas II
Lista de operadores e exemplos práticos para realizar seleções.
Quisemos compilar na forma de tabela certos operadores que podem ser úteis em
determinados casos. Estes operadores serão utilizados depois da cláusula Where e podem ser
combinados habilmente mediante parênteses para otimizar nossa seleção a níveis bastante
altos.
Operadores matemáticos:
>
Maior que
<
Menor que
>= Maior ou igual que
<= Menor ou igual que
<> Diferente
=
Igual
Operadores lógicos
And
Or
Not
Outros operadores
Like
Seleciona os registros cujo valor de campo se assemelhe, não tendo em
conta maiúsculas e minúsculas.
In e Not In
Dá um conjunto de valores para um campo para os quais a condição de
seleção é (ou não) válida
Is Null e Is Not
Null
Seleciona aqueles registros onde o campo especificado está (ou não) vazio.
Between...And
Seleciona os registros compreendidos em um intervalo
Distinct
Seleciona os registros não coincidentes
Desc
Classifica os registros por ordem inversa
Curingas
* Substitui a todos os campos
% Substitui a qualquer coisa ou nada dentro de uma cadeia
_ Substitui só um caractere dentro de uma cadeia
Vejamos a seguir aplicações práticas destes operadores.
Nesta sentença selecionamos todos os clientes de Salvador cujo nome não seja Jose. Como se
pode ver, empregamos Like ao invés de = simplesmente para evitar inconvenientes devido ao
emprego ou não de maiúsculas.
Select * From clientes Where cidade Like 'salvador' And Not nome Like 'Jose'
Se quiséssemos recolher em uma seleção aos clientes de nossa tabela cujo sobrenome começa
por A e cujo número de pedidos esteja compreendido entre 20 e 40:
Select * From clientes Where sobrenomes like 'A%' And pedidos Between 20 And 40
O operador In, será visto mais adiante, é muito prático para consultas em várias tabelas. Para
casos em uma única tabela é empregada da seguinte forma:
Select * From clientes Where cidade In ('Salvador','Fortaleza','Florianopolis')
Desta forma selecionamos aqueles clientes que vivem nessas três cidades.
Seleção de tabelas III
Como realizar seleções sobre várias tabelas. Exemplos práticos baseados em uma
aplicação de e-comercio.
Um banco de dados pode ser considerado como um conjunto de tabelas. Estas tabelas, em
muitos casos, estão relacionadas entre elas e se complementam unas com outras.
Fazendo referência ao nosso clássico exemplo de um banco de dados para uma aplicação de e-
comercio, a tabela clientes que estivemos falando pode estar perfeitamente coordenada com
uma tabela onde armazenamos os pedidos realizados por cada cliente. Esta tabela de pedidos
pode por sua vez, estar conectada com uma tabela onde armazenamos os dados
correspondentes a cada artigo do inventário.
Deste modo poderíamos facilmente obter informações contidas nessas três tabelas como pode
ser a designação do artigo mais popular em uma determinada região onde a designação do
artigo seria obtida na tabela de artigos, a popularidade (quantidade de vezes que esse artigo
foi vendido) viria da tabela de pedidos e a região estaria compreendida obviamente na tabela
clientes.
Este tipo de organização baseada em múltiplas tabelas conectadas nos permite trabalhar com
tabelas muito mais manejáveis e ao mesmo tempo, nos evita copiar o mesmo campo em
vários lugares já que podemos acessa-lo a partir de uma simples chamada à tabela que o
contém.
Neste capítulo veremos como, usando o que foi aprendido até agora, podemos realizar
facilmente seleções sobre várias tabelas. Definiremos antes de nada as diferentes tabelas e
campos que vamos utilizar em nossos exemplos:
Tabela de clientes
Nome campo
Tipo campo
id_cliente
Numérico inteiro
nome
Texto
sobrenomes
Texto
endereço
Texto
cidade
Texto
cep
Texto
telefone
Numérico inteiro
email
Texto
Tabela de pedidos
Nome campo
Tipo campo
id_pedido
Numérico inteiro
id_cliente
Numérico inteiro
id_artigo
Numérico inteiro
data
Data
quantidade Numérico inteiro
Tabela de artigos
Nome campo
Tipo campo
id_artigo
Numérico inteiro
titulo
Alfanumérico
autor
Alfanumérico
editorial
Alfanumérico
preço
Numérico real
Estas tabelas podem ser utilizadas simultaneamente para extrair informações de todo tipo.
Suponhamos que queremos enviar um mailing a todos aqueles que tiverem realizado um
pedido nesse mesmo dia. Poderíamos escrever algo assim:
Select clientes.sobrenomes, clientes.email From clientes,pedidos Where pedidos.data like
'25/02/00' And pedidos.id_cliente= clientes.id_cliente
Como pode ser visto desta vez, depois da cláusula From, introduzimos o nome das duas
tabelas de onde tiramos as informações. Ademais, o nome de cada campo vai precedido da
tabela de proveniência separado ambos por um ponto. Nos campos que possuem um nome
que só aparece em uma das tabelas, não é necessário especificar sua origem embora na hora
de ler sua sentença possa ser mais claro tendo esta informação mais precisa. Neste caso, o
campo data poderia ter sido designado como "data" ao invés de "pedidos.data".
Vejamos outro exemplo mais para consolidar estes novos conceitos. Desta vez queremos ver o
título do livro correspondente a cada um dos pedidos realizados:
Select pedidos.id_pedido, artigos.titulo From pedidos, artigos Where
pedidos.id_artigo=artigos.id_artigo
Na verdade a filosofia continua sendo a mesma que para a consulta de uma única tabela.
Seleção de tabelas IV
O emprego de funções para a exploração dos campos numéricos e outras
utilidades. Exemplos práticos.
Além dos critérios até agora explicados para realizar as consultas em tabelas, SQL permite
também aplicar um conjunto de funções pré-definidas. Estas funções, embora sejam básicas,
podem nos ajudar em alguns momentos a expressar nossa seleção de uma maneira mais
simples sem ter que recorrer a operações adicionais por parte do script que estivermos
executando.
Algumas destas funções são representadas na seguinte tabela:
Função
Descrição
Soma(campo) Calcula a soma dos registros do campo especificado
Avg(Campo) Calcula a média dos registros do campo especificado
Count(*)
Proporciona o valor do número de registros que foram selecionados
Max(Campo) Indica qual é o valor máximo do campo
Min(Campo) Indica qual é o valor mínimo do campo
Dado que o campo da função não existe no banco de dados, pois o estamos gerando
virtualmente, isto pode criar inconvenientes quando estivermos trabalhando com nossos
scripts na hora de tratar seu valor e seu nome de campo. É por isso que o valor da função tem
que ser recuperada a partir de um apelido que nós especificaremos na sentença SQL a partir
da instrução AS. A coisa poderia ficar assim:
Select Soma(total) As soma_pedidos From pedidos
A partir desta sentença calculamos a soma dos valores de todos os pedidos realizados e
armazenamos esse valor em um campo virtual chamado soma_pedidos que poderá ser
utilizado como qualquer outro campo por nossas páginas dinâmicas.
Obviamente, tudo que foi visto até agora pode ser aplicado neste tipo de funções de modo
que, por exemplo, podemos estabelecer condições com a cláusula Where construindo
sentenças como esta:
Select Soma(quantidade) as soma_artigos From pedidos Where id_artigo=6
Isto nos proporcionaria a quantidade de exemplares de um determinado livro que foram
vendidos.
Outra propriedade interessante destas funções é que permitem realizar operações com vários
campos dentro de um mesmo parênteses:
Select Avg(total/quantidade) From pedidos
Esta sentença dá como resultado o preço médio que estão sendo vendidos os livros. Este
resultado não tem porquê coincidir com o do preço médio dos livros presentes no inventário,
já que, pode ser que as pessoas tenham tendência a comprar os livros caros ou os baratos:
Select Avg(precio) as preco_venda From artigos
Uma cláusula interessante no uso das funções é Group By. Esta cláusula nos permite agrupar
registros aos quais vamos aplicar a função. Podemos por exemplo calcular o dinheiro gastado
por cada cliente:
Select id_cliente, Soma(total) as soma_pedidos From pedidos Group By id_cliente
Ou saber o número de pedidos que foram realizados:
Select id_cliente, Count(*) as numero_pedidos From pedidos Group By id_cliente
As possibilidades como vemos são numerosas e pode ser práticas. Agora tudo fica à disposição
de nossas ocorrências e imaginação.
Vejamos o Diagrama Entidade-Relacionamento abaixo:
Neste diagrama existem duas entidades, uma chamada cliente outra tipo cliente e um
relacionamento que interliga essas entidades. Em um banco de dados é necessário
definir fisicamente esse relacionamento, e ele é definido pela chave estrangeira
(Foreing Key).
Implementando em um banco as duas tabelas teríamos:
mysql>create table cliente(
codigo int not null primary key,
nome varchar(50),
tipo int,
endereco varchar(100),
cidade varchar(30),
uf varchar(2),
datanascimento date,
datacadastro date);
mysql> create table tipocliente(
codigotipo int not null primary key,
descricao varchar(500));
Assim ficaram as tabelas:
mysql> describe cliente;
+----------------+-----------------+------+-----+---------+-------+
| Field
| Type
| Null | Key | Default | Extra |
+----------------+-----------------+------+-----+---------+-------+
| codigo
| int(11)
| NO
| PRI | 0
|
|
| nome
| varchar(100)
| YES |
| NULL
|
|
| tipo
| int
| YES |
| NULL
|
|
| endereco
| varchar(100)
| YES |
| NULL
|
|
| cidade
| varchar(30)
| YES |
| NULL
|
|
| uf
| varchar(2)
| YES |
| NULL
|
|
| datanascimento | date
| YES |
| NULL
|
|
| datacadastro
| date
| YES |
| NULL
|
|
+----------------+-----------------+------+-----+---------+-------+
8 rows in set (0.03 sec)
mysql> describe tipocliente;
+----------------+-----------------+------+-----+---------+-------+
| Field
| Type
| Null | Key | Default | Extra |
+----------------+-----------------+------+-----+---------+-------+
| codigotipo
| int(11)
| NO
| PRI | 0
|
|
| descricao
| varchar(500)
| YES |
| NULL
|
|
+----------------+-----------------+------+-----+---------+-------+
2 rows in set (0.03 sec)
Mas ainda não existe a implementação da ligação dessas duas tabelas,