Banco de Dados -3

Agora iremos criar tabela torneios

Column Name  -- DataType

numt                 int
ano                   numeric(4)
numj                numeric(4)
premio             numeric(4)

Agora iremos criar a tabela lista_torneios

Column Name  -- DataType

numt                   int
nomet                 varchar
pais                     char(3)
cat                      char
quadra                char
nparts                 char

Agora iremos fazer as conexoes entre as tabelas jogadores,torneios,lista_torneios

Click em 1:N um para muitos e faça a conexão o torneio pode ter varios jogadores, e lista_torneios pode der varios torneios.
O workbrench deve ficar assim :

Agora iremos gerar o script do mysql
Faça o seguinte vá em File - Export - Forward Enginner SQL create script

Em output sql script file, dê um nome para o seu script
Depois é só dar next ate o final
O seu script estará assim :


SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

CREATE SCHEMA IF NOT EXISTS `torneio` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `torneio` ;

-- -----------------------------------------------------
-- Table `torneio`.`jogadores`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `torneio`.`jogadores` (
  `numj` DECIMAL(10,0)  NOT NULL ,
  `nome` VARCHAR(45) NOT NULL ,
  `pnome` VARCHAR(45) NOT NULL ,
  `pais` CHAR NOT NULL ,
  `ano_n` DECIMAL(10,0)  NOT NULL ,
  `ano_p` DECIMAL(10,0)  NOT NULL ,
  `cid_n` VARCHAR(45) NOT NULL ,
  `cid_res` VARCHAR(45) NOT NULL COMMENT ' ,
  `tit_s` DECIMAL(10,0)  NULL ,
  `tit_d` DECIMAL(10,0)  NULL ,
  `vits` DECIMAL(10,0)  NULL ,
  `derrs` DECIMAL(10,0)  NULL ,
  `natp` DECIMAL(10,0)  NULL ,
  UNIQUE INDEX `nome_UNIQUE` (`nome` ASC) ,
  PRIMARY KEY (`numj`) )
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `torneio`.`lista_torneios`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `torneio`.`lista_torneios` (
  `numt` DECIMAL(10,0)  NOT NULL ,
  `nomet` VARCHAR(45) NOT NULL ,
  `pais` CHAR NOT NULL ,
  `cat` CHAR NOT NULL ,
  `quadra` CHAR NOT NULL ,
  `nparts` CHAR NOT NULL ,
  PRIMARY KEY (`numt`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `torneio`.`torneios`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `torneio`.`torneios` (
  `numt` DECIMAL(10,0)  NOT NULL ,
  `ano` DECIMAL(10,0)  NOT NULL ,
  `numj` DECIMAL(10,0)  NOT NULL ,
  `premio` DECIMAL(10,0)  NOT NULL ,
  `jogadores_numj` DECIMAL(10,0)  NOT NULL ,
  `Lista_Torneios_numt` DECIMAL(10,0)  NOT NULL ,
  PRIMARY KEY (`numt`) ,
  INDEX `fk_Torneios_jogadores` (`jogadores_numj` ASC) ,
  INDEX `fk_Torneios_Lista_Torneios1` (`Lista_Torneios_numt` ASC) ,
  CONSTRAINT `fk_Torneios_jogadores`
    FOREIGN KEY (`jogadores_numj` )
    REFERENCES `torneio`.`jogadores` (`numj` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Torneios_Lista_Torneios1`
    FOREIGN KEY (`Lista_Torneios_numt` )
    REFERENCES `torneio`.`lista_torneios` (`numt` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Nessa parte terminamos agora seguiremos para o mysql :

Abra o mysql query browser, depois vá em file , open script e abra o script que você salvou

FIcará assim :

apague as linas iniciais :

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

E as linhas finais :

SET SQL_MODE=@OLD_SQL_MODE;

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Depois disso dê um Execute

Depois de executar onde tem o banco de dados (database) essas sanfonas amarelas ao lado direito, click com o botão direito do mouse e dê um refresh schema.

Verifique que foi criado um novo database por nome torneio

Dê um duplo click em cima de torneio e abra o banco de dados, verifique que existe a tabela jogadores,torneios,lista_torneios.

Dê um duplo click em cima de jogadores e verá que o banco de dados torneio se encontra vazio.

Agora iremos comecar inserir dados dentro das tabelas....

Faça o seguinte

Click em jogadores e arraste para dentro do retangulo branco do mysql, observe que quando voçĉe o coloca entre a linha , aparece varios comandos do mysql, como segue a imagem :

Bom observe que foi criado o comando INSERT INTO torneio.jogadores VALUES()

Bom se você observou os atributos dos jogadores são: numj ,nome ,pnome , pais, ano_n, ano_p, cid_n, cid_res , tit_s , tit_d , vits , derrs , natp

Vamos inserir valores :

INSERT INTO jogadores VALUES ('16','Bruguera','Sergi','ESP','71','88','Barcelona','Barcelona','14','3','447','271','171'),

 ('19','Edberg','Stefan','SWE','66','83','Vastervik','Londres','42','18','806','270',NULL),

 ('29','Agassi','Andre','EUA','70','86','Las Vegas','Las Vegas','53','1','731','227','470'),

 ('56','Sa','Andre','BRA','77','96','Belo Horizonte','Belo Horizonte','0','1','42','65','597'),

 ('102','Muster','Thomas','AUT','67','85','Leibnitz','Monte Carlo','44','1','622','271',NULL),

 ('123','Korda','Petr','CHE','68','87','Praga','Monte Carlo','10','10','410','248',NULL),

 ('126','Krajicek','Richard','HOL','71','89','Rotterdham','Monte Carlo','17','3','444','209','1271'),

 ('127','Safin','Marat','RUS','80','97','Moscou','Monte Carlo','10','1','214','123','180'),

 ('324','Johansson','Thomas','SWE','75','94','Linkoping','Monte Carlo','7','0','224','179','375'),

 ('412','Meligeni','Fernando','BRA','71','90','Buenos Aires','Sao Paulo','3','7','194','206','471'),

 ('625','Costa','Albert','ESP','75','93','Lerida','Barcelona','12','0','325','204','675'),

 ('812','Sampras','Pete','EUA','71','88','Washington','Los Angeles','64','2','762','222','871');

 ('817','Courier','Jim','EUA','70','78','Sanford','Orlando','23','6','506','237',NULL),

 ('827','Moya','Carlos','ESP','76','95','Palma Maiorca','Genebra','11','0','309','176','876'),

 ('910','Kuerten','Gustavo','BRA','76','95','Florianopolis','Florianopolis','17','8','281','136','971'),

 ('913','Ivanisevic','Goran','CRO','71','88','Split','Monte Carlo','22','9','595','320','1971'),

 ('1122','Becker','Boris','GER','67','84','Leimen','Monte Carlo','49','15','713','214',NULL),

 ('1228','Rafter','Patrick','AUS','72','91','Mount Isa','Bermuda','11','10','358','191','1272');

click em executar

Agora iremos inserir valores torneios : 

INSERT INTO `torneio`.`torneios` VALUES  ('1','1992','817','278','16','3','0'),

 ('2','1992','817','490','19','5','0'),

 ('3','1992','29','501','29','7','0'),

 ('4','1992','19','500','56','13','0');

click em executar

Agora iremos inserir valores em lista_torneios :

INSERT INTO `torneio`.`lista_torneios` VALUES  ('1','Australian Open','A','G','D','1'),

 ('2','Roland Garros','F','G','S','1'),

 ('3','Wimbledon','I','G','S','1'),

 ('4','Us Open','E','G','D','1'),

 ('5','Indian Wells','E','M','D','6'),

 ('6','Miami','E','M','D','9'),

 ('7','Monte Carlo Open','M','M','S','6'),

 ('8','Roma','I','M','S','6'),

 ('9','Hamburgo','G','M','S','6'),

 ('10','Toronto','C','M','D','6'),

 ('11','Cincinati','E','M','D','6'),

 ('12','Madri','E','M','T','6'),

 ('13','Paris Open','F','M','T','6'),

 ('14','Tennis Master Cup 2002','C','M','D','8');

click em executar

Bom agora dê um click duplo na tabela jogadores e será mostrado igual a tela a seguir :

Teste todos...

Viu o quanto é fácil fazer a modelagem dos dados no workbench e depois chamar no mysql

Agora iremos brincar com os comandos do mysql ---- Começaremos com o Select

Caracteristicas basicas :

Iremos agora fazer uma consulta.. é para isso que serve o comando Select, bom eu quero a informação do nome,ano e cidade de todos os jogadores, só do Brasil e ordene a lista por nome...

E agora como faço isso?

Vamos lá

SELECT pnome,nome,ano_n,cid_res from jogadores where pais = 'BRA'order by pnome

é isso.

Bom vamos dizer que agora eu queira eliminar os anos repetidos da tabela jogadores.

Faremos : 

SELECT distinct ano_n from jogadores

Bom agora eu quero o nome dos jogadores que se tornaram profissionais com menos de 22 anos :

SELECT nome from jogadores where ano_p - ano_n < 22

Bom agora eu quero o nome e ano de nascimento dos jogadores nascidos entre 1975 e 1981

SELECT nome,ano_n from jogadores where ano_n between 75 and 81

Agora eu quero todos jogadores que começam com a letra B

SELECT nome from jogadores where nome like 'B%' 

Que termine com a letra B

SELECT nome from jogadores where nome like '%B'

Agora eu quero para cada torneio o nome, ano e o numero do seu vencedor. Observe as tabelas torneios e lista de torneios.

E agora como fazer a junção das tabelas para obter os dados....

Simples :

SELECT nomet,ano,numj from torneios, lista_torneios where torneios.numt=lista_torneios.numt

Obtenha o nome de cada jogador que venceu pelo menos um torneio

SELECT nome from jogadores where numj in(select numj from torneios)

Para cada jogador que venceu torneios, obtenha o seu nome o numero do torneio e o ano

SELECT nome,numt,ano from jogadores, torneios where jogadores.numj=torneios.numj

Obtenha o sobrenome e o nome do jogador que ganhou o torneio 2 maior que 1997

SELECT pnome,nome from jogadores where numj=(select numj from torneios where numt=2 and ano <1997)

Obtenha o nome de cada jogador que venceu algum torneio

SELECT nome from jogadores where exists(select * from torneios where torneios.numj=jogadores.numj)

Agora quero saber quantos torneios existem : O comando count é um contador

SELECT count(*)from lista_torneios

Qual foi o valor do premio maximo

SELECT max(premio) from torneios

Quantas cidades de residência distintas existem 

SELECT count(distinct cid_res)from jogadores

Quantos jogadores vivem em Monte Carlo

SELECT count(*)from jogadores where cid_res='Monte Carlo'

De o numero de cada jogador que venceu pelo menos dois torneios distintos

SELECT numj from torneios group by numj having count(distinct numt)>=2

Apresente cada jogador que ganhou a maior soma de prêmios e qual esse valor 

SELECT numj, sum(premio)from torneios group by numj having sum(premio)>=all (select sum(premio)from torneios group by numj)