EXERCÍCIOS DE MYSQL

Quase todo o material aqui mostrado é extraído do livro: MySQL 5 interativo Guia básico de orientação e desenvolvimento, autor: José Augusto N. G. Manzano, ed Érica.

LIÇÃO 1 – PREPARAÇÃO DO AMBIENTE

Para não ter muito trabalho para entrar no mySql, grave um arquivo batch, usando o NotePad, com os comandos abaixo, para abrir o MySql dentro do prompt do Windows:

rem AbrirMySql.bat: arquivo para abrir o MySql

cd \

cd Arquivos de Programas

cd EasyPhp

cd MySql

cd bin

mySqlD

mySql –u root

Salve com o nome AbrirMySql.bat na pasta que é aberta inicialmente pelo prompt do Windows, ou no C:\ (diretório raiz). Assim, quando for abrir o programa, entre no prompt do Windows e digite apenas:

AbrirMySql

Isto deverá abrir o prompt do mySql:

mysql>

A partir deste ponto, os comandos digitados são do mySql e não do Windows. Todos os comandos do mySql devem terminar com ; (ponto e vírgula).

Para sair do mySql digite:

quit; ou exit;

Para sair do prompt do Windows, digite:

exit

Saia do prompt do Windows, entre novamente e teste se seu arquivoAbrirMySql está funcionando.

Se a instalação do mySql foi feita criando uma conta com senha (que é a forma correta de se fazer em uma instalação comercial), a linha para executar o programa deve ser:

mySql –u <usuário> –p

A chave –p exige que o usuário digite a senha antes de carregar o programa.

LIÇÃO 2 – PRIMEIROS COMANDOS

1. Primeiros comandos

Observe que cada comando digitado (sempre terminado com ;) é respondido pelo mySql. Para conhecer a versão do programa, data e hora, no prompt do mySql, digite os comandos abaixo e observe o resultado de cada um:

select version();

select current_date;

select current_time;

select current_date, current_time;

select curdate(), curtime();

select now();

Dica: para repetir comandos no prompt do Windows ou do mySql, use as setas para cima e para baixo, que permitem navegar pelos últimos comandos digitados.

O help nos permite checar a sintaxe dos comandos. Digite help; e veja os grupos de comandos. Veja, por ex.: clear, connect, exit, go, prompt, quit, source, status, tee e use, que são aqueles que mais iremos usar.

Digite help contents; e veja que o help se estende para um índice de conteúdo de manual. Em seguida, digite help data definition; e veja que surgem os comandos de definição de dados. Digite help create table; um dos comandos de data definition, que é o comando de criação de tabelas. A resposta deste comando ocupa mais de uma página; para ler, use a barra de rolagem do lado direito da janela.

Os comandos podem ocupar mais de uma linha. Enquanto não aparecer o ; (ponto e vírgula) é o mesmo comando. Por exemplo, digite

select aperte <enter>

user();

Depois, digite: select current_user; Algumas instruções mais longas, portanto, podem ser digitadas em mais de uma linha, o que garantirá uma clareza e facilidade maiores.

Digite os comandos abaixo, para ver que o prompt do mySql pode funcionar como uma calculadora simples.

select 5 + 7.5;

select 5 - 7.5;

select 5 + 7.5;

select 5 - 7.5, 5 + 7.5, 5 * 7.5, 5 / 7.5;

select 1 + 2 * 3;

select 1 + (2 * 3);

select (1 + 2) * 3;

A calculadora tem outras potencialidades: resto, valor absoluto, arredondamente, potência, logaritmo, que serão usadas mais tarde em comandos select.

2. Log de alterações

Em um ambiente em que seja necessário controlar as alterações no BD (por ex.: quando exista uma equipe de programadores autorizados a alterar o BD), deve ser usada a opção tee ao carregar o programa. Por exemplo:

mySql –u root --tee=”logbd.txt”

Isto gravará um arquivo com o log dos comandos que foram digitados no prompt do mySql durante esta seção. A opção notee desativa a gravação do log. Para testar isto, faça o seguinte:

- Saia do prompt do mySql: exit;

- Entre novamente, com a opção ativada: mySql –u root --tee=”logbd.txt”

- Digite dois comandos, por ex.: select user(); e select curtime();

- Desative a gravação: digite notee;

- Digite outro comando: select now();

- Saia, digite: quit;

- Abra o arquivo logbd.txt com o NotePad e verifique os comandos gravados.

LIÇÃO 3 – CRIAÇÃCO DO BD E DAS TABELAS,

INSERÇÃO E CONSULTA DE REGISTROS

1. Manuseio do banco de dados

As seguintes instruções manuseiam o banco de dados:

cria o banco	create database [if not exists] <nome>
mostra os bancos existentes	show databases
coloca um BD em uso	use <nome>
exclui um BD	drop database <nome>

Para testar estas instruções, digite as instruções abaixo, no prompt do MySql. No Windows, podem ser usadas maiúsculas e minúsculas sem problema; no Linux, não. Alguns autores adotam a convenção de escrever todos os comandos SQL em maiúsculas.

show databases;

create database teste;

show databases;

Alguns BDs listados são privativos do MySql: são usados para guardar as informações sobre os BDs criados pelos usuários. Abra o Meu Computador e faça o caminho onde está instalado o MySql e verifique na pasta Data a criação do BD teste.

Para verificar o erro e a criação de um BD já existente, digite:

create database teste;

show databases;

create database if not exists teste;

show databases;

Coloque o BD teste em uso, digite: use teste; Apague o BD, digite: drop database teste; Depois, verifique novamente os BDs existentes, com show databases; Abra o Meu Computador e faça o caminho onde está instalado o MySql e verifique na pasta Data a exclusão do BD teste.

2. Criação de tabelas

A tabela para o cadastro de funcionários deve se chamar cadfun, utiliza alguns dos principais comandos da linguagem SQL e tem a seguinte estrutura:

Campo	Tipo*	Descrição
CodFun**	integer	código do funcionário (não nulo)
Nome	varchar(40)	nome do funcionário (não nulo)
Depto	char ( 2 )	departamento onde está locado o funcionário
Funcao	char (20)	função do funcionário
Salario	decimal (10, 2)	salário do funcionário

* os tipos de dados possíveis no mySql estão na tabela anexa TIPOS DE DADOS

** chave primária.

Antes de criar a tabela, vamos criar um novo BD virtual, que conterá a maior parte dos dados que usaremos nas lições seguintes. Para isto, digite o seguinte:

create database virtual;

use virtual;

Para criar a tabela, digite o comando abaixo. Compare a sintaxe do comando digitado com a descrição da estrutura da tabela. Para efeito didático, a instrução foi digitada em várias linhas para destacar a endentação; observe a colocação das vírgulas e parênteses e que o ; (ponto e vírgula) encerra a instrução.

create table CadFun (

CODFUN integer not null primary key,

NOME varchar(40) not null,

DEPTO char( 2),

FUNCAO char(20),

SALARIO decimal(10, 2)

);

Para mostrar as tabelas do BD em uso, digite: show tables;

Para mostrar a estrutura da tabela, ou de apenas um campo, digite:

describe CadFun;

describe CadFun CODFUN;

3. Inserção de registros

A inserção de registros pode ser feita pelos comandos INSERT e pelo comando LOAD DATA. O primeiro insere diretamente os registros na tabela, após a execução de cada Insert; já Load data permite inserir de uma vez os comandos gravados em um arquivo. Insert tem o seguinte formato (atenção com os parênteses):

INSERT INTO <tabela>

(campo1, campo2, campo3…, campoN)

VALUES (valor1, valor2, valor3…, valorN);

Insira o primeiro registro. Observe os parênteses, as vírgulas e as aspas para os campos tipo char, bem como o ponto decimal e o ; (ponto e vírgula).

INSERT INTO cadfun

(CODFUN, NOME, DEPTO, FUNCAO, SALARIO)

VALUES (12, 'CARLOS ALBERTO', '3', 'VENDEDOR', 1530.00);

Só por castigo, ou melhor, para treinar um pouco mais, acrescente mais dois registros:

codfun	nome	depto	funcao	salario
15	MARCOS HENRIQUE	2	GERENTE	1985.75
7	APARECIDA SILVA	3	SECRETARIA	1200.50

Quando um campo deva ser inserido com NULL, basta suprimir o nome deste campo da lista de campos. Na nossa tabela, os campos CodFun e Nome não podem ser nulos. Por exemplo, o comando a seguir insere um registro sem informar o campo FUNCAO.

INSERT INTO cadfun

(CODFUN, NOME, DEPTO, SALARIO)

VALUES (44, JACIBA DA SILVA', '3', 1500.00);

Quando estiver inserindo todos os campos do registro e na sua ordem correta, você pode suprimir a primeira parte da instrução. Insira mais dois registros digitando o seguinte:

INSERT INTO cadfun VALUES (

2, 'WILSON DE MACEDO', '3', 'PROGRAMADOR', 1050.00);

INSERT INTO cadfun VALUES (

5, 'AUGUSTO SOUZA', '3', 'PROGRAMADOR', 1050.00);

Vamos agora acrescentar mais alguns registros utilizando o comando load data. Para isto, grave um arquivo texto puro (pode ser com o NotePad) com o nome insere.txt no mesmo diretório ..\bin do mySql com as linhas abaixo. Para digitar este arquivo corretamente observe o seguinte:

- as colunas são separadas, umas das outras, pelo caractere <Tab>,

- no final de cada linha é digitado o <Enter>,

- os campos numéricos são alinhados pela direita, preenchendo os espaços com brancos,

- assim, para digitar a primeira linha, faça o seguinte:

o digite um espaço em branco e depois digite 4,

o aperte <Tab>,

o digite CARLOS BASTOS e aperte <Tab>,

o digite 5 e aperte <Tab>,

o digite VENDEDOR e aperte <Tab>,

o digite 1530.00 e aperte < Enter >.

- caso necessite inserir um campo com NULL, no arquivo, a coluna relativa ao campo deve ser informada com \N (barra invertida N maiúsculo).

4 CARLOS BASTOS 5 VENDEDOR 1530.00

25 PEDRO SILVA 3 SUPERVISOR 1599.51

3 ANA BASTOS 5 VENDEDORA 1530.00

10 PAULO DA SILVA 2 VENDEDOR 1530.00

9 SOLANGE PACHECO 5 SUPERVISORA 1599.51

6 MARCELO SOUZA 3 ANALISTA 2250.11

1 CELIA NASCIMENTO 2 SECRETARIA 1200.50

Volte ao prompt do mySql e digite o comando: load data local infile “insere.txt” into table cadfun; Caso o comando load data não esteja funcionando (deve se tratar de uma versão anterior do mySql), digitar os seguintes comandos:

INSERT INTO cadfun (CODFUN, NOME, DEPTO, FUNCAO, SALARIO)

VALUES (4, ‘CARLOS BASTOS’, ‘5', 'VENDEDOR', 1530.00);

INSERT INTO cadfun (CODFUN, NOME, DEPTO, FUNCAO, SALARIO)

VALUES (25, ‘PEDRO SILVA’, ‘3', ‘SUPERVISOR', 1599.51);

INSERT INTO cadfun (CODFUN, NOME, DEPTO, FUNCAO, SALARIO)

VALUES (3, ‘ANA BASTOS’, ‘5', 'VENDEDORA', 1530.00);

INSERT INTO cadfun (CODFUN, NOME, DEPTO, FUNCAO, SALARIO)

VALUES (10, ‘PAULO DA SILVA’, ‘2', ‘VENDEDOR’, 1530.00);

INSERT INTO cadfun (CODFUN, NOME, DEPTO, FUNCAO, SALARIO)

VALUES (9, ‘SOLANGE PACHECO’, ‘5', ‘SUPERVISORA’, 1599.51);

INSERT INTO cadfun (CODFUN, NOME, DEPTO, FUNCAO, SALARIO)

VALUES (6, ‘MARCELO SOUZA’, '3', ‘ANALISTA’, 2250.00);

INSERT INTO cadfun (CODFUN, NOME, DEPTO, FUNCAO, SALARIO)

VALUES (1, ‘CELIA NASCIMENTO’, ‘2', ‘SECRETARIA’, 1200.50);

4. Consulta de registros

A consulta pode ser feita com a seguinte instrução:

SELECT [tipo] <campos> FROM <tabela> [condição];

- Tipo: pode ser ALL (todos os registros) ou DISTINCT (registros distintos).

- Campos: é uma lista dos campos, separada por vírgula; caso se use * (asterisco), deseja-se ver todos os campos.

- Condição: é dada por outras cláusulas WHERE, ORDER BY..., etc, que serão detalhadas mais à frente.

Para ver todos os funcionários cadastrados, digite: SELECT * FROM cadfun;

Para ver somente o nome e a função, digite: SELECT NOME, FUNCAO FROM cadfun;

Para ver quem trabalha em um departamento, digite:

SELECT NOME, CODFUN FROM cadfun

WHERE DEPTO = ‘3’;

Para mudar a ordem da consulta, ascendente e descendente, digite estes exemplos:

SELECT NOME, SALARIO FROM cadfun

ORDER BY NOME;

SELECT NOME, SALARIO FROM cadfun

ORDER BY NOME DESC;

SELECT NOME, SALARIO FROM cadfun

ORDER BY NOME DESC;

SELECT DEPTO, NOME FROM cadfun

ORDER BY DEPTO, NOME DESC;

SELECT NOME FROM cadfun

WHERE DEPTO = ‘3’

ORDER BY NOME;

5. Alteração de registros

A alteração de registros pode ser feita com a seguinte instrução:

UPDATE <tabela> SET <campo1> = <expressão1>, <campo2> = <expressão2>, ..., <campoN> = <expressãoN>

[condição];

- Campo: nome da coluna da tabela que se deseja atualizar. Expressão: valor que se deseja atribuir ao campo. A mesma instrução UPDATE pode alterar vários campos do registro.

- Condição: é dada pela cláusula WHERE.

Vamos alterar o salário do funcionário 7 e transferir a Ana Bastos do departamento 5 para o 3. Os SELECT mostram o estado antes e depois dos UPDATE.

SELECT * FROM cadfun WHERE CODFUN = 7;

UPDATE cadfun SET SALARIO = 2300.56 WHERE CODFUN = 7;

SELECT * FROM cadfun WHERE CODFUN = 7;

SELECT * FROM cadfun WHERE NOME = 'ANA BASTOS';

UPDATE cadfun SET DEPTO = '3' WHERE NOME = 'ANA BASTOS';

SELECT * FROM cadfun WHERE NOME = 'ANA BASTOS';

Vamos também conceder um reajuste de 10% para todos os empregados. Digite:

SELECT * FROM cadfun;

UPDATE cadfun SET SALARIO = SALARIO * 1.10;

SELECT * FROM cadfun;

6. Exclusão de registros

A exclusão de registros pode ser feita com a seguinte instrução:

DELETE FROM <tabela> [condição];

- Condição: é dada pela cláusula WHERE. Atenção!, pois se esta cláusula for omitida, é feita a exclusão de todos os registros da tabela.

Vamos excluir o departamento 2 inicialmente e depois o empregado Marcelo Souza. Os SELECT mostram o estado antes e depois dos DELETE.

SELECT * FROM cadfun;

SELECT DISTINCT DEPTO FROM cadfun;

DELETE FROM cadfun WHERE DEPTO = '2';

SELECT * FROM cadfun;

SELECT DISTINCT DEPTO FROM cadfun;

DELETE FROM cadfun WHERE NOME = 'MARCELO SOUZA';

SELECT * FROM cadfun;

Embora possamos excluir todos os registros de uma tabela com DELETE, a instrução abaixo faz isto com maior eficiência:

TRUNCATE TABLE <tabela>;

7. Alteração da estrutura da tabela

A alteração da estrutura da tabela pode ser feita com a seguinte instrução:

ALTER TABLE <tabela> <operação>;

- Operação: permite adicionar e excluir colunas de uma tabela:

o ADD <campo> <tipo>: insere uma nova coluna <campo>, informando em seguinda seu <tipo> de dados,

o DROP <campo>: elimina um campo da tabela.

Vamos colocar na tabela o campo ADMISSAO (data de admissão). Em seguida, vamos preencher os dados destes campos. No mySql o formato da data é: ‘aaaa-mm-dd’; em outros SGBD este formato pode ser diferente. Os SELECT mostram o estado da tabela antes e depois.

SELECT * FROM cadfun;

ALTER TABLE cadfun ADD ADMISSAO DATE;

SELECT * FROM cadfun;

UPDATE cadfun SET ADMISSAO = '2006-01-15' WHERE CODFUN = 2;

UPDATE cadfun SET ADMISSAO = '1999-10-21' WHERE CODFUN = 3;

UPDATE cadfun SET ADMISSAO = '2004-10-21' WHERE CODFUN = 4;

UPDATE cadfun SET ADMISSAO = '2006-04-26' WHERE CODFUN = 5;

UPDATE cadfun SET ADMISSAO = '1980-05-10' WHERE CODFUN = 7;

UPDATE cadfun SET ADMISSAO = '1999-12-15' WHERE CODFUN = 9;

UPDATE cadfun SET ADMISSAO = '2000-12-21' WHERE CODFUN = 12;

UPDATE cadfun SET ADMISSAO = '2000-10-21' WHERE CODFUN = 25;

SELECT * FROM cadfun;

Vamos aproveitar e testar o campo de data. O primeiro select usa uma função para extrair o mês; o segundo usa >=. Digite:

SELECT NOME, ADMISSAO FROM cadfun WHERE MONTH(ADMISSAO) = 12;

SELECT NOME, ADMISSAO FROM cadfun WHERE ADMISSAO >= '2000-01-01';

Para preparar a tabela para os próximos exercícios, vamos recriar o departamento 2, readmitir um empregado e admitir outros três. Digite:

INSERT INTO cadfun VALUES (

15, 'MARCOS HENRIQUE', '2', 'GERENTE', 2184.33, '2006-05-25');

INSERT INTO cadfun VALUES (

20, 'AUDREY TOLEDO', '2', 'SUPERVISORA', 1700.00, '2006-07-05');

INSERT INTO cadfun VALUES (

22, 'SANDRA MANZANO', '2','ANALISTA', 2000.00, '2006-07-01');

INSERT INTO cadfun VALUES (

24, 'MARCIO CANUTO', '2', 'PROGRAMADOR', 1200.00, '2006-07-10');

SELECT * FROM cadfun;

A microempresa deve ter ficado com doze empregados.

8. Criação da tabela do arquivo morto

Frequentemente precisamos guardar registros excluídos para atender consultas que abordam dados passados. No nosso caso, vamos criar uma tabela morto com a mesma estrutura da CadFun para guardar os empregados demitidos. Digite:

CREATE TABLE morto (

CODFUN INTEGER PRIMARY KEY,

NOME VARCHAR(40),

DEPTO CHAR( 2),

FUNCAO CHAR(20),

SALARIO DECIMAL(10, 2),

ADMISSAO DATE);

SHOW TABLES;

Para copiar os dados de uma tabela para outra podemos usar um INSERT casado com um SELECT, no formato abaixo:

INSERT INTO <tabeladestino>

SELECT <campos>

FROM <tabelafonte>

WHERE <condição>;

- Tabeladestino: tabela onde será feito o INSERT;

- Campos: lista de campos da <tabelafonte>. É importante que os <campos> estejam na mesma ordem e sejam do mesmo tipo que os da <tabeladestino>. Neste caso, não pode ser usado o * asterisco.

- Tabelafonte: a tabela que fornecerá os registros.

- Condição: a condição do SELECT.

Vamos demitir o funcionário 12. Primeiro o inserimos no morto e depois o deletamos do CadFun. Digite:

INSERT INTO morto

SELECT CODFUN, NOME, DEPTO, FUNCAO, SALARIO, ADMISSAO

FROM cadfun

WHERE CODFUN = 12;

SELECT * FROM morto;

DELETE FROM cadfun WHERE CODFUN = 12;

SELECT * FROM cadfun;

SELECT * FROM morto;

9. Subconsultas

Podemos basear uma query em outra. Isto permite estruturar as consultas de forma que possamos isolar partes da instrução e testá-las separadamente (o que chamamos de depuração ou debug). A uma query assim construída dá-se o nome de subquery (subconsulta). Por exemplo, vamos fazer (1) uma consulta para os nomes dos empregados ativos que tenham salário igual aos dos funcionários demitidos, e (2) uma consulta para saber os empregados cujo departamento é igual ao dos funcionários demitidos. Para isto, digite:

SELECT NOME FROM cadfun;

SELECT SALARIO FROM morto;

SELECT NOME FROM cadfun

WHERE SALARIO = (SELECT SALARIO FROM morto);

SELECT * FROM cadfun

WHERE SALARIO = (SELECT SALARIO FROM morto);

SELECT DEPTO FROM morto;

SELECT * FROM cadfun

WHERE DEPTO = (SELECT DEPTO FROM morto);

10. Exercícios adicionais

- Mostre nomes e funções de todos os funcionários.

- Mostre códigos, nomes e funções de todos os funcionários.

- Mostre códigos, nomes, funções e departamentos de todos os funcionários.

- Mostre os nomes dos funcionários do departamento 5.

- Mostre nomes e departamentos de todos os funcionários que ocupam o cargo de vendedor.

- Mostre quantas funções diferentes existem no departamento 5.

- Mostre os salários dos funcionários do departamento 3 ordenados de forma descendente.

- Mostre os nomes e funções de todos os funcionários, ordenados de forma descendente pelo nome da função.

- Mostre os nomes, funções e salários de todos os funcionários, ordenados de forma ascendente pelo nome da função e de forma descendente pelo salário.

- Mostre os registros dos funcionários que tenham sido admitidos no mês de outubro de qualquer ano.

- Mostre os registros dos funcionários que tenham sido admitidos antes de 2005.

- Mostre os registros dos funcionários que tenham mais de 5 anos na empresa.

- Mostre quantas funções diferentes existem na empresa. Como existem casos em que está escrito “VENDEDORA”, “SUPERVISORA”..., faça algumas sql de update para trocar para “VENDEDOR”, “SUPERVISOR”, etc. Obs.: tem de ser feita uma sql para cada cargo cuja notação se deseja alterar.

LIÇÃO 4 – CONSULTAS COM OPERADORES

1. Operadores aritméticos

Os operadores aritméticos (soma, subtração, multiplicação, divisão e resto) estão descritos na tabela anexa “tabela de operadores e funções matemáticas”. Os operadores podem ser usados dentro de um comando Select: eles criam um campo calculado dentro da consulta, mas não afetam os valores das tabelas. Verifique, após os exemplos abaixo, que a tabela permanece inalterada. Digite:

SELECT NOME, SALARIO FROM cadfun;

SELECT NOME, SALARIO + 100 FROM cadfun;

SELECT NOME, SALARIO FROM cadfun;

SELECT NOME, SALARIO * 1.20 FROM cadfun;

SELECT NOME, SALARIO FROM cadfun;

Digite também os exemplos abaixo para relembrar a calculadora do comando Select:

SELECT 2 + 3 * 5;

SELECT (2 + 3) * 5;

2. Operadores de comparação ou relacionais

Os operadores relacionais que podem ser usados no mySql constam na tabela a seguir.

Operador	Descrição
>	Maior do que
<	Menor do que
=	Igual a
<> ou !=	Diferente de
>=	Maior ou igual a
<=	Menor ou igual a
<=>	Igual para Null

Por exemplo, digite:

SELECT * FROM cadfun WHERE DEPTO = ‘5’;

SELECT * FROM cadfun WHERE FUNCAO = 'VENDEDOR';

SELECT * FROM cadfun WHERE SALARIO <= 1700;

SELECT * FROM cadfun WHERE SALARIO > 1700 + 50;

SELECT * FROM cadfun WHERE SALARIO > (1700 + 50);

3. Operadores lógicos

Os operadores lógicos conhecidos na programação também podem ser usados no mySql e sua notação consta na tabela a seguir.

Operador	Operador	Descrição
AND	&&	E – conjunção
OR	\|\|	Ou – disjunção
NOT	!	Não – negação
XOR		Ou exclusivo

Por exemplo, digite:

SELECT * FROM cadfun

WHERE (DEPTO = '3') AND (FUNCAO = 'PROGRAMADOR');

SELECT * FROM cadfun

WHERE (DEPTO = '3') OR (DEPTO = '5');

SELECT * FROM cadfun

WHERE NOT (FUNCAO = 'VENDEDOR');

SELECT * FROM cadfun

WHERE (DEPTO = '5') XOR (FUNCAO = 'PROGRAMADOR');

4. Operadores adicionais

Alguns operadores adicionais podem ser usados no mySql e sua notação e seu significado consta na tabela a seguir.

Operador	Descrição
IS NULL	Verifica se um campo está vazio.
BETWEEN	Verifica se um valor está dentro de um intervalo.
IN	Verifica se um valor esté em um conjunto de valores.
LIKE	Busca valores semelhantes.

Para testar estes operadores, crie um novo campo (a quantidade de filhos) nas tabelas CadFun e Morto e depois verifique seus valores. Para isto, digite:

ALTER TABLE cadfun ADD FILHOS SMALLINT;

ALTER TABLE morto ADD FILHOS SMALLINT;

SELECT NOME, FILHOS FROM cadfun;

SELECT CODFUN, NOME, FILHOS FROM cadfun

WHERE FILHOS IS NULL;

SELECT CODFUN, NOME, FILHOS FROM cadfun

WHERE NOT FILHOS IS NULL;

SELECT CODFUN, NOME, FILHOS FROM cadfun

WHERE FILHOS IS NOT NULL;

SELECT CODFUN, NOME, FILHOS FROM cadfun

WHERE NOT (FILHOS IS NULL);

Vamos alterar os valores deste campo para melhorar os exemplos. Para isto, digite:

UPDATE cadfun SET FILHOS = 1 WHERE CODFUN = 2;

UPDATE cadfun SET FILHOS = 3 WHERE CODFUN = 3;

UPDATE cadfun SET FILHOS = 2 WHERE CODFUN = 5;

UPDATE cadfun SET FILHOS = 1 WHERE CODFUN = 9;

UPDATE cadfun SET FILHOS = 4 WHERE CODFUN = 20;

UPDATE cadfun SET FILHOS = 3 WHERE CODFUN = 25;

SELECT CODFUN, NOME, FILHOS FROM cadfun;

SELECT CODFUN, NOME, FILHOS FROM cadfun

WHERE FILHOS IS NULL;

SELECT CODFUN, NOME, FILHOS FROM cadfun

WHERE NOT (FILHOS IS NULL);

SELECT * FROM cadfun

WHERE SALARIO BETWEEN 1700 AND 2000;

SELECT * FROM cadfun

WHERE SALARIO NOT BETWEEN 1700 AND 2000;

SELECT * FROM cadfun

WHERE DEPTO IN ('2', '3');

SELECT * FROM cadfun

WHERE DEPTO NOT IN ('2', '3');

O operador LIKE é ainda mais poderoso, pois pode trabalhar com dois caracteres curinga:

% (percentagem) significa qualquer quantidade (0, 1, 2...) de caracteres combinando, (alguns BDs usam * asterisco),

_ (sublinhado) significa apenas um caractere igual.

Estes caracteres podem ser usados no início, meio e fim do campo, permitindo inúmeras situações e combinações. Alguns exemplos:

Exemplo: Where...	Operação
Salario like ‘11%’	Encontra valores que começam com 11.
Salario like ‘%8%’	Encontra valores que tenham 8 em qualquer posição.
Salario like ‘_0%’	Encontra valores que tenham 0 na segunda posição.
Salario like ‘1_%_%’	Encontra valores que começam com 1 e tenham 3 caracteres de comprimento (?? no mínimo??)
Salario like ‘%6’	Encontra valor que termine com 6.
Salario like ‘_1%6’	Encontra valores que tenham 1 na segunda posição e termine com 6.

Digite os comandos a seguir para testar o operador Like:

SELECT * FROM cadfun WHERE NOME LIKE 'A%';

SELECT * FROM cadfun WHERE NOME LIKE '_A%';

SELECT * FROM cadfun WHERE NOME LIKE '%AN%';

SELECT * FROM cadfun WHERE SALARIO LIKE '%6';

SELECT * FROM cadfun WHERE SALARIO LIKE '_5%2';

SELECT * FROM cadfun WHERE NOME NOT LIKE '%AN%';

Agora digite os comandos a seguir completando de acordo com a tabela anterior, para verificar os exemplos apresentados:

SELECT NOME, SALARIO FROM cadfun WHERE SALARIO LIKE …(complete de acordo com a tabela de exemplos).

5. Exercícios adicionais: ver arquivo MySqlExec.doc.

LIÇÃO 5 – FUNÇÕES

Há um resumo dos tipos de função permitidos no anexo “TABELA DE OPERADORES E FUNÇÕES MATEMÁTICAS”.

1. Funções de agregação ou estatísticas

Função	Retorna...
AVG()	Média aritmética
COUNT()	A quantidade de registros
MAX()	O maior valor
MIN()	O menor valor
STD() ou STDDEV()	O desvio padrão
SUM()	Soma dos valores
VARIANCE()	A variância

Para testar estas funções, digite:

SELECT AVG(SALARIO) FROM cadfun;

SELECT AVG(SALARIO) FROM cadfun WHERE DEPTO = '3';

SELECT COUNT(*) FROM cadfun WHERE DEPTO = '3';

SELECT COUNT(*) FROM cadfun WHERE SALARIO > 2000;

SELECT COUNT(DISTINCT DEPTO) FROM cadfun;

SELECT MAX(SALARIO) FROM cadfun;

SELECT MIN(SALARIO) FROM cadfun;

SELECT STD(FILHOS) FROM cadfun;

SELECT STDDEV(FILHOS) FROM cadfun;

SELECT SUM(SALARIO) FROM cadfun;

SELECT SUM(SALARIO) FROM cadfun WHERE DEPTO = '2';

SELECT VARIANCE(FILHOS) FROM cadfun;

Um exercício interessante é verificar como estas funções se comportam com valores nulos. Verifique primeiro se existem ainda campos SALARIO ou FILHOS com valores nulos. Caso não exista, inclua (INSERT) dois novos registros com estes dados nulos ou altere (UPDATE) dois registros existentes para colocar estes dados nulos. Em seguida, digite novamente os comandos abaixo para testar as funções com os valores nulos.

SELECT SALARIO FROM cadfun;

SELECT AVG(SALARIO) FROM cadfun;

SELECT SUM(SALARIO) FROM cadfun;

SELECT COUNT(*) FROM cadfun;

SELECT COUNT(SALARIO) FROM cadfun;

SELECT MIN(SALARIO) FROM cadfun;

SELECT FILHOS FROM cadfun;

SELECT STD(FILHOS) FROM cadfun;

SELECT VARIANCE(FILHOS) FROM cadfun;

2. Funções de data e hora

Função	Retorna...
CURDATE()	Data atual
CURTIME()	Hora atual
DATEDIFF()	Diferença entre duas datas
DAY()	O dia, de uma data
HOUR()	O valor da hora, de um campo de hora
MINUTE()	Os minutos, de um campo de hora
MONTH()	O mês, de uma data
MONTHNAME()	O nome do mês, de uma data
NOW()	Data e hora atual
SECOND()	Os segundos, de um campo de hora
YEAR()	O ano, de uma data

Para testar estas funções, digite as instruções abaixo. As duas primeiras calculam o número de dias ou de anos desde a data de admissão do empregado.

SELECT NOME, DATEDIFF('2006-09-15', ADMISSAO) FROM cadfun;

SELECT NOME, DATEDIFF('2006-09-15', ADMISSAO) / 365 FROM cadfun;

SELECT DAY(ADMISSAO), MONTH(ADMISSAO), YEAR(ADMISSAO)

FROM cadfun;

SELECT NOME, ADMISSAO, MONTHNAME(ADMISSAO) FROM cadfun;

SELECT HOUR(NOW()), MINUTE(NOW()), SECOND(NOW());

3. Funções numéricas

Função	Retorna...
ABS()	Valor absoluto
ACOS(), ASIN(), ATAN()	Arco do cosseno, do seno ou da tangente
COS(), SIN(),TAN()	Cosseno, seno ou tangente
DEGREES()	Converte radianos para graus
EXP()	O exponencial
LN()	O logaritmo natural
MOD()	O resto de uma divisão de inteiros
PI()	O valor de PI
POWER()	A potência de uma base elevada a um expoente
RADIANS()	Converte graus para radianos
ROUND()	Arredonda
SQRT()	Extrai a raiz quadrada

Para testar estas funções, digite as instruções abaixo. Para o caso das funções trigonométricas, observe se os valores retornados consideram os parâmetros em radianos ou em graus. Para as funções de exponenciação e logarítmicas, observe se os valores retornados consideram os parâmetros na base 10 ou na base e (logaritmo natual).

SELECT ABS(-8.238765);

SELECT ABS(-9);

SELECT MOD(5, 2);

SELECT MOD(4, 2);

SELECT ROUND(1.9);

SELECT ROUND(1.3);

SELECT SQRT(9);

SELECT SQRT(2);

SELECT PI();

SELECT PI() + 0.000000000000000000;

SELECT ACOS(-1);

SELECT ACOS(0.5);

SELECT ASIN(-1);

SELECT ASIN(0.5);

SELECT ATAN(-1);

SELECT ATAN(0.5);

SELECT COS(-1);

SELECT COS(0.5);

SELECT SIN(1);

SELECT SIN(0.5);

SELECT TAN(1);

SELECT TAN(0.5);

SELECT DEGREES(ASIN(1));

SELECT DEGREES(ASIN(0.5));

SELECT RADIANS(1);

SELECT RADIANS(0.5);

SELECT EXP(3.4);

SELECT EXP(1);

SELECT LN(3);

SELECT LN(EXP(10));

SELECT POWER(2, 3);

SELECT POWER(2, -3);

4. Funções de tratamento de strings

Função	Retorna...
ASCII()	Código ASCII da string
BIN(), OCT(), HEX()	Converte decimal para binário, octal, hexadecimal
CONCAT()	Concatena strigs
LOWER(),UPPER()	Converte para minúsculas, maiúsculas
LEFT()	Parte do lado esquerdo da string
LENGTH()	Tamanho da string

Para testar estas funções, digite as instruções abaixo.

SELECT ASCII('A');

SELECT ASCII('B');

SELECT ASCII('AB');

SELECT BIN(10);

SELECT BIN(255);

SELECT HEX(10);

SELECT HEX(255);

SELECT OCT(10);

SELECT OCT(255);

SELECT CONCAT('My', 'SQL');

SELECT CONCAT('Augusto', ' ', 'Manzano');

SELECT LEFT('Augusto Manzano', 5);

SELECT LEFT(NOME, 9) FROM cadfun WHERE DEPTO = '2';

SELECT LENGTH('Augusto Manzano');

SELECT NOME, LENGTH(NOME) FROM cadfun WHERE DEPTO = '2';

SELECT LOWER('Augusto Manzano');

SELECT NOME, LOWER(NOME) FROM cadfun WHERE DEPTO = '2';

SELECT UPPER('Augusto Manzano');

6. Exercícios adicionais: ver arquivo MySqlExec.doc.

LIÇÃO 6 – AGRUPAMENTOS E UNIÕES (GROUP BY e UNION)

Até agora vimos seleções com uma única tabela. Esta lição e a próxima apresentam consultas com mais de uma tabela e também novas funções de agregação e agrupamento. O agrupamento, feito pela cláusula GROUP BY, usa um campo básico ou mais, para permitir a agregação das demais colunas da consulta. A união, feita pela cláusula UNION, é obtida combinando duas ou mais consultas em apenas uma; neste caso, os campos das consultas combinadas devem ter o mesmo significado.

Para permitir enriquecer os exemplos, vamos acrescentar novos registros ao BD Virtual. Digite os seguintes comandos:

INSERT INTO cadfun VALUES (

1, 'SILVIO DE MENESES', '2', 'ANALISTA', 2000.00, '2006-08-10', 1);

INSERT INTO cadfun VALUES (

6, 'SILVIA DA SILVA', '5', 'ANALISTA', 2000.00, '2006-08-10', 3);

INSERT INTO cadfun VALUES (

8, 'ANTONIO DA SILVA', '5', 'GERENTE', 2184.33, '2006-09-05', 2);

INSERT INTO cadfun VALUES (

10, 'ANTONIA DE CARVALHO', '5', 'SECRETARIA', 2530.62, '2006-09-07', 4);

INSERT INTO cadfun VALUES (

11, 'SILVANA DOS SANTOS', '4', 'VENDEDOR', 1683.00, '2006-09-07', 0);

INSERT INTO cadfun VALUES (

12, 'ANTONIO DOS SANTOS', '4', 'PROGRAMADOR', 1155.00, '2006-10-10', 1);

INSERT INTO cadfun VALUES (

21, 'EPAMINONDAS DA SILVA', '4', 'PROGRAMADOR', 1155.00, '2006-10-10', 2);

Além disto, os registros cujo campo Filhos estejam com valor NULL devem ser zerados e o campo Função deve ser escrito da mesma forma (alguns registros têm escrito Vendedora e Supervisora, enquanto outros têm escrito Vendedor e Supervisor). Digite os comandos para homogeneizar estes registros e depois verifique se algum ainda apresenta o campo Filhos com valores nulos ou Função escrita de forma diferente.

UPDATE cadfun SET FILHOS = 0 WHERE CODFUN = 4;

UPDATE cadfun SET FILHOS = 0 WHERE CODFUN = 7;

UPDATE cadfun SET FILHOS = 0 WHERE CODFUN = 15;

UPDATE cadfun SET FILHOS = 0 WHERE CODFUN = 22;

UPDATE cadfun SET FILHOS = 0 WHERE CODFUN = 24;

UPDATE cadfun SET FUNCAO = 'SUPERVISOR'

WHERE FUNCAO = 'SUPERVISORA';

UPDATE cadfun SET FUNCAO = 'VENDEDOR'

WHERE FUNCAO = 'VENDEDORA';

SELECT * FROM cadfun;

1. Agrupamentos

Para o agrupamento, usa-se a cláusula GROUP BY dentro da instrução SELECT. Digite os exemplos abaixo, comparandos os resultados individuais com os resultados agregados obtidos. Observe no exemplo final que foi introduzida a cláusula HAVING, que permite selecionar valores em campos agregados; é, assim, diferente da cláusula WHERE, que permite a seleção de valores em colunas não agregadas.

SELECT DEPTO FROM cadfun;

SELECT DISTINCT DEPTO FROM cadfun;

SELECT DEPTO, COUNT(*) FROM cadfun GROUP BY DEPTO;

SELECT DEPTO, SUM(SALARIO) FROM cadfun GROUP BY DEPTO;

SELECT DEPTO, SUM(SALARIO) FROM cadfun GROUP BY DEPTO DESC;

SELECT DEPTO, COUNT(*) FROM cadfun GROUP BY DEPTO

ORDER BY DEPTO DESC;

SELECT FUNCAO FROM cadfun;

SELECT DISTINCT FUNCAO FROM cadfun;

SELECT FUNCAO, COUNT(*) FROM cadfun GROUP BY FUNCAO;

SELECT FUNCAO, COUNT(*), SUM(SALARIO)

FROM cadfun GROUP BY FUNCAO;

SELECT DEPTO, SUM(SALARIO) FROM cadfun

WHERE DEPTO IN ('2', '4')

GROUP BY DEPTO;

SELECT DEPTO, SALARIO FROM cadfun;

SELECT DEPTO, COUNT(*), SUM(SALARIO) FROM cadfun GROUP BY DEPTO;

SELECT DEPTO, AVG(SALARIO) FROM cadfun GROUP BY DEPTO;

SELECT DEPTO, MAX(SALARIO) FROM cadfun GROUP BY DEPTO;

SELECT DEPTO, MIN(SALARIO) FROM cadfun GROUP BY DEPTO;

SELECT DEPTO, SUM(SALARIO) FROM cadfun

GROUP BY DEPTO

HAVING SUM(SALARIO) > 8000;

2. Uniões

Uma união baseia-se em múltiplos comandos Select, de uma forma parecida com uma subconsulta, na forma geral:

SELECT ... UNION [ALL | DISTINCT]

SELECT ... [UNION [ALL | DISTINCT]

SELECT ... ]; onde

ALL | DISTINCT: representam todos os registros, ou apenas os registros distintos (eliminando duplicatas) – neste caso, DISTINCT é o default.

Antes de executar as uniões, vamos atualizar a tabela CadFun, imaginando que a empresa fez oito demissões. Neste caso, temos que inserir os demitidos na tabela Morto antes de deletá-los da CadFun. Observe ainda que as inserções em Morto estão sendo feitas a partir de uma consulta, o que também é um recurso interessante para evitar linhas de código. Lembre-se que estamos utilizando a tabela Morto porque as colunas das consultas utilizadas devem ser do mesmo tipo. Para isto, digite o seguinte:

INSERT INTO morto