banco de dados 4

Ola gente vamos dar continuidade a banco de dados, nessa nova etapa irei mostrar alguns comandos mais avançados de banco de dados.
Vamos lá
Primeiro vamos modelar os dados com o workbrench
Iremos criar um banco de dados simulando infrações de transito.
Como já mostrei em banco de dados 1 a modelagem, só irei agora colocar imagens referentes ao mesmo, colocando os atributos que iremos trabalhar.
Nome do banco de dados
database : veiculos
Tabelas :
1.categoria
1.1 - id_categoria – chave primaria
1.2 - nome

2.cores
2.1 - id_cores – chave primaria
2.1 - nome

3.marcas
3.1 - id_marcas – chave primaria
3.2 – nome

4.modelos
4.1 - id_modelos – chave primaria
4.2 - nome

5.combustivel
5.1 - id_combustivel – chave primaria
5.2 - nome

6.estados
6.1 - id_estados – chave primaria
6.2 - nome
6.3 - sigla

7.cidade
7.1 - id_cidade – chave primaria
7.2 - nome

8.proprietarios
8.1 - id_proprietarios – chave primaria
8.2 - cpf
8.3 - nome
8.4 - endereco
8.5 - cep
8.6- sexo
8.7- datanascimento

9.veiculos
9.1 - id_veiculos – chave primaria
9.2 - renavam
9.3- chassi
9.4 - placa
9.5 - anofabricacao

10.tiposinfracoes
10.1 - id_tiposinfracoes – chave primaria
10.2 - descricao
10.3- valor

11.agentes
11.1 - id_agentes – chave primaria
11.2 - nome
11.3 - datacontratacao

12.infracoes
12.1 - id_infracoes – chave primaria
12.2 - datahora
12.3- local
12.4- velocidade
12.5- observacao

13.telefones
13.1- id_telefones – chave primaria
13.2- ddd
13.4- numero

Bom a sua tabela tem que está assim :

A diferença dos pontos tracejados para as linhas continuas é chave como primária e estrangeira ao mesmo tempo da relacionada (traço contínuo). Na maioria das vezes utilizamos o tracejado que utiliza a chave como somente estrangeira da entidade relacionada.

Ok se está tudo certo vamos gerar os scripts, dê o nome como veiculos.

Ok 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 `veiculos` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;

USE `veiculos` ;

-- -----------------------------------------------------

-- Table `veiculos`.`categoria`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `veiculos`.`categoria` (

`id_categoria` INT NOT NULL ,

`nome` VARCHAR(45) NULL ,

PRIMARY KEY (`id_categoria`) )

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `veiculos`.`cores`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `veiculos`.`cores` (

`id_cores` INT NOT NULL ,

`nome` VARCHAR(45) NULL ,

PRIMARY KEY (`id_cores`) )

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `veiculos`.`marcas`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `veiculos`.`marcas` (

`id_marcas` INT NOT NULL ,

`nome` VARCHAR(45) NULL ,

PRIMARY KEY (`id_marcas`) )

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `veiculos`.`modelos`

-- ----------------------------------------------------

CREATE TABLE IF NOT EXISTS `veiculos`.`modelos` (

`id_modelos` INT NOT NULL ,

`nome` VARCHAR(45) NULL ,

`marcas_id_marcas` INT NOT NULL ,

PRIMARY KEY (`id_modelos`) ,

INDEX `fk_modelos_marcas1` (`marcas_id_marcas` ASC) ,

CONSTRAINT `fk_modelos_marcas1`

FOREIGN KEY (`marcas_id_marcas` )

REFERENCES `veiculos`.`marcas` (`id_marcas` )

ON DELETE NO ACTION

ON UPDATE NO ACTION)

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `veiculos`.`combustivel`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `veiculos`.`combustivel` (

`id_combustivel` INT NOT NULL ,

`nome` VARCHAR(45) NULL ,

PRIMARY KEY (`id_combustivel`) )

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `veiculos`.`estados`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `veiculos`.`estados` (

`id_estados` INT NOT NULL ,

`nome` VARCHAR(45) NULL ,

`sigla` VARCHAR(2) NULL ,

PRIMARY KEY (`id_estados`) )

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `veiculos`.`cidade`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `veiculos`.`cidade` (

`id_cidade` INT NOT NULL ,

`nome` VARCHAR(45) NULL ,

`estados_id_estados` INT NOT NULL ,

PRIMARY KEY (`id_cidade`) ,

INDEX `fk_cidade_estados1` (`estados_id_estados` ASC) ,

CONSTRAINT `fk_cidade_estados1`

FOREIGN KEY (`estados_id_estados` )

REFERENCES `veiculos`.`estados` (`id_estados` )

ON DELETE NO ACTION

ON UPDATE NO ACTION)

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `veiculos`.`proprietarios`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `veiculos`.`proprietarios` (

`id_proprietarios` INT NOT NULL ,

`cpf` VARCHAR(45) NULL ,

`nome` VARCHAR(45) NULL ,

`endereco` VARCHAR(45) NULL ,

`cep` VARCHAR(45) NULL ,

`sexo` CHAR(1) NULL ,

`datanascimento` DATETIME NULL ,

`cidade_id_cidade` INT NOT NULL ,

PRIMARY KEY (`id_proprietarios`) ,

INDEX `fk_proprietarios_cidade1` (`cidade_id_cidade` ASC) ,

CONSTRAINT `fk_proprietarios_cidade1`

FOREIGN KEY (`cidade_id_cidade` )

REFERENCES `veiculos`.`cidade` (`id_cidade` )

ON DELETE NO ACTION

ON UPDATE NO ACTION)

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `veiculos`.`veiculos`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `veiculos`.`veiculos` (

`id_veiculos` INT NOT NULL ,

`renavam` INT NULL ,

`chassi` VARCHAR(20) NULL ,

`placa` VARCHAR(20) NULL ,

`anofabricacao` INT NULL ,

`categoria_id_categoria` INT NOT NULL ,

`cores_id_cores` INT NOT NULL ,

`modelos_id_modelos` INT NOT NULL ,

`combustivel_id_combustivel` INT NOT NULL ,

`proprietarios_id_proprietarios` INT NOT NULL ,

PRIMARY KEY (`id_veiculos`) ,

INDEX `fk_veiculos_categoria` (`categoria_id_categoria` ASC) ,

INDEX `fk_veiculos_cores1` (`cores_id_cores` ASC) ,

INDEX `fk_veiculos_modelos1` (`modelos_id_modelos` ASC) ,

INDEX `fk_veiculos_combustivel1` (`combustivel_id_combustivel` ASC) ,

INDEX `fk_veiculos_proprietarios1` (`proprietarios_id_proprietarios` ASC) ,

CONSTRAINT `fk_veiculos_categoria`

FOREIGN KEY (`categoria_id_categoria` )

REFERENCES `veiculos`.`categoria` (`id_categoria` )

ON DELETE NO ACTION

ON UPDATE NO ACTION,

CONSTRAINT `fk_veiculos_cores1`

FOREIGN KEY (`cores_id_cores` )

REFERENCES `veiculos`.`cores` (`id_cores` )

ON DELETE NO ACTION

ON UPDATE NO ACTION,

CONSTRAINT `fk_veiculos_modelos1`

FOREIGN KEY (`modelos_id_modelos` )

REFERENCES `veiculos`.`modelos` (`id_modelos` )

ON DELETE NO ACTION

ON UPDATE NO ACTION,

CONSTRAINT `fk_veiculos_combustivel1`

FOREIGN KEY (`combustivel_id_combustivel` )

REFERENCES `veiculos`.`combustivel` (`id_combustivel` )

ON DELETE NO ACTION

ON UPDATE NO ACTION,

CONSTRAINT `fk_veiculos_proprietarios1`

FOREIGN KEY (`proprietarios_id_proprietarios` )

REFERENCES `veiculos`.`proprietarios` (`id_proprietarios` )

ON DELETE NO ACTION

ON UPDATE NO ACTION)

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `veiculos`.`tiposinfracoes`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `veiculos`.`tiposinfracoes` (

`id_tiposinfracoes` INT NOT NULL ,

`descricao` VARCHAR(45) NULL ,

`valor` DECIMAL(15,2) NULL ,

PRIMARY KEY (`id_tiposinfracoes`) )

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `veiculos`.`agentes`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `veiculos`.`agentes` (

`id_agentes` INT NOT NULL ,

`nome` VARCHAR(45) NULL ,

`datacontratacao` DATE NULL ,

PRIMARY KEY (`id_agentes`) )

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `veiculos`.`infracoes`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `veiculos`.`infracoes` (

`id_infracoes` INT NOT NULL ,

`datahora` DATETIME NULL ,

`local` VARCHAR(45) NULL ,

`velocidade` INT NULL ,

`observacao` VARCHAR(255) NULL ,

`veiculos_id_veiculos` INT NOT NULL ,

`tiposinfracoes_id_tiposinfracoes` INT NOT NULL ,

`agentes_id_agentes` INT NOT NULL ,

PRIMARY KEY (`id_infracoes`) ,

INDEX `fk_infracoes_veiculos1` (`veiculos_id_veiculos` ASC) ,

INDEX `fk_infracoes_tiposinfracoes1` (`tiposinfracoes_id_tiposinfracoes` ASC) ,

INDEX `fk_infracoes_agentes1` (`agentes_id_agentes` ASC) ,

CONSTRAINT `fk_infracoes_veiculos1`

FOREIGN KEY (`veiculos_id_veiculos` )

REFERENCES `veiculos`.`veiculos` (`id_veiculos` )

ON DELETE NO ACTION

ON UPDATE NO ACTION,

CONSTRAINT `fk_infracoes_tiposinfracoes1`

FOREIGN KEY (`tiposinfracoes_id_tiposinfracoes` )

REFERENCES `veiculos`.`tiposinfracoes` (`id_tiposinfracoes` )

ON DELETE NO ACTION

ON UPDATE NO ACTION,

CONSTRAINT `fk_infracoes_agentes1`

FOREIGN KEY (`agentes_id_agentes` )

REFERENCES `veiculos`.`agentes` (`id_agentes` )

ON DELETE NO ACTION

ON UPDATE NO ACTION)

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `veiculos`.`telefones`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `veiculos`.`telefones` (

`id_telefones` INT NOT NULL ,

`ddd` VARCHAR(2) NULL ,

`numero` VARCHAR(8) NULL ,

`proprietarios_id_proprietarios` INT NOT NULL ,

PRIMARY KEY (`id_telefones`) ,

INDEX `fk_telefones_proprietarios1` (`proprietarios_id_proprietarios` ASC) ,

CONSTRAINT `fk_telefones_proprietarios1`

FOREIGN KEY (`proprietarios_id_proprietarios` )

REFERENCES `veiculos`.`proprietarios` (`id_proprietarios` )

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;

Ok, agora gerado o script, abra o mysql query browser e vamos começar a brincar,vá em file open query ,navegue ate onde você salvou o script veiculos, abra ele e execute. Depois vá onde tem o database (a sanfona amarela) e com o botão direito do mouse click em refresh schema. Depois disso irá verificar que foi criado o bancco de dados chamado veiculos com todas as tabelas. Agora iremos inserir dados nela, para cada uma coloque os seguintes dados :

-- -----------------------------------------------------

--Insira registros nas tabelas: agentes,

-- -----------------------------------------------------

INSERT INTO agentes VALUES('1','andrea','2000-01-01');

INSERT INTO agentes VALUES('2','adriana','2000-02-01');

INSERT INTO agentes VALUES('3','iza','2000-03-01')

INSERT INTO agentes VALUES('4','sidnei','2000-04-01')

INSERT INTO agentes VALUES('5','lula','2000-05-01')

INSERT INTO agentes VALUES('6','marta','2000-06-01')

INSERT INTO agentes VALUES('7','wel','2000-07-01')

INSERT INTO agentes VALUES('8','beta','2000-08-01')

INSERT INTO agentes VALUES('9','emanoel','2000-09-01')

INSERT INTO agentes VALUES('10','marli','2000-10-01')

-- -----------------------------------------------------

-- Insira registros nas tabelas:categorias,

-- -----------------------------------------------------

INSERT INTO categoria VALUES('1','carro');

INSERT INTO categoria VALUES('2','moto');

INSERT INTO categoria VALUES('3','onibus')

INSERT INTO categoria VALUES('4','caminhao')

-- -----------------------------------------------------

-- Insira registros nas tabelas:cores,

-- -----------------------------------------------------

INSERT INTO cores VALUES('1','prata')

INSERT INTO cores VALUES('2','vermelho')

INSERT INTO cores VALUES('3','preto')

INSERT INTO cores VALUES('4','amarelo')

INSERT INTO cores VALUES('5','branco')

-- -----------------------------------------------------

-- Insira registros nas tabelas:combustiveis,

-- -----------------------------------------------------

INSERT INTO combustivel VALUES('1','gasolina')

INSERT INTO combustivel VALUES('2','etanol')

INSERT INTO combustivel VALUES('3','etanol-gasolina')

-- -----------------------------------------------------

-- Insira registros nas tabelas:estados,

-- -----------------------------------------------------

INSERT INTO estados VALUES('1','Bahia','Ba')

INSERT INTO estados VALUES('2','Pernambuco','Pe')

INSERT INTO estados VALUES('3','Sao Paulo','SP')

INSERT INTO estados VALUES('4','Rio de Janeiro','RJ')

INSERT INTO estados VALUES('5','Sergipe','Se')

INSERT INTO estados VALUES('6','Minas Gerais','MG')

INSERT INTO estados VALUES('7','Ceara','Ce')

-- -----------------------------------------------------

-- Insira registros nas tabelas:infracoes,

-- -----------------------------------------------------

INSERT INTO infracoes VALUES('1','2010-10-12 12:43:00','av.caymi','120','advertencia','1','1','1')

INSERT INTO infracoes VALUES('2','2010-09-13 11:21:00','av.acm','200','advertencia','2','2','2')

INSERT INTO infracoes VALUES('3','2010-08-14 10:11:00','av.paralela','240','advertencia','3','3','3')

INSERT INTO infracoes VALUES('4','2010-07-15 15:13:00','av.suburbana','130','advertencia','4','4','4')

INSERT INTO infracoes VALUES('5','2010-06-16 17:16:00','av.lucaia','150','advertencia','5','5','5')

-- -----------------------------------------------------

-- Insira registros nas tabelas:marcas,

-- -----------------------------------------------------

INSERT INTO marcas VALUES('1','RENAULT')

INSERT INTO marcas VALUES('2','Volkswagen')

INSERT INTO marcas VALUES('3','Peugeout')

INSERT INTO marcas VALUES('4','Citroen')

INSERT INTO marcas VALUES('5','Chevrolet')

INSERT INTO marcas VALUES('6','Honda')

INSERT INTO marcas VALUES('7','Suzuki')

INSERT INTO marcas VALUES('8','Yamaha')

INSERT INTO marcas VALUES('9','Kawasaki')

INSERT INTO marcas VALUES('10','Dukati')

-- -----------------------------------------------------

-- Insira registros nas tabelas:modelos,

-- -----------------------------------------------------

INSERT INTO modelos VALUES('1','Clio','1')

INSERT INTO modelos VALUES('2','Gol','2')

INSERT INTO modelos VALUES('3','307HB','3')

INSERT INTO modelos VALUES('4','C3','4')

INSERT INTO modelos VALUES('5','Agile','5')

INSERT INTO modelos VALUES('6','CB600','6')

INSERT INTO modelos VALUES('7','HAYABUSA','7')

INSERT INTO modelos VALUES('8','FAZER 600','8')

INSERT INTO modelos VALUES('9','Ninja','9')

INSERT INTO modelos VALUES('10','Matrix','10')

-- -----------------------------------------------------

-- Insira registros nas tabelas: proprietarios,

-- -----------------------------------------------------

INSERT INTO proprietarios VALUES('1','123123','Andrea','rua a','400000','F','1977-10-23','1')

INSERT INTO proprietarios VALUES('2','123321','Adriana','rua b','400000','F','1992-02-06','2')

INSERT INTO proprietarios VALUES('3','321123','Sidnei','rua c','400000','M','1974-10-28','3')

INSERT INTO proprietarios VALUES('4','213213','Lula','rua d','400000','M','1950-02-03','4')

INSERT INTO proprietarios VALUES('5','543211','Iza','rua e','400000','F','1954-10-24','5')

INSERT INTO proprietarios VALUES('6','654321','Marly','rua f','400000','F','1972-07-15','7')

-- -----------------------------------------------------

-- Insira registros nas tabelas: tiposinfracoes

-- -----------------------------------------------------

INSERT INTO tiposinfracoes VALUES('1','grave','350.00','7')

INSERT INTO tiposinfracoes VALUES('2','media','250.00','5')

INSERT INTO tiposinfracoes VALUES('3','leve','150.00','3')

INSERT INTO tiposinfracoes VALUES('4','advertencia','50.00','2')

INSERT INTO tiposinfracoes VALUES('5','gravissima','550.00','7')

-- -----------------------------------------------------

-- Insira registros nas tabelas: veiculos

-- -----------------------------------------------------

INSERT INTO veiculos VALUES('1','1212','2222111333','JQH6726','2009','1','1','2','1','1')

INSERT INTO veiculos VALUES('2','3211','1143553322','HQW1233','2008','2','2','1','2','2')

INSERT INTO veiculos VALUES('3','4322','3211445553','FWE3255','2005','3','3','3','3','3')

INSERT INTO veiculos VALUES('4','5443','7665533222','ISE1234','2003','4','4','4','1','4')

INSERT INTO veiculos VALUES('5','6211','9244343222','JDW2111','2002','2','5','5','2','5')

INSERT INTO veiculos VALUES('6','7322','5332332211','AQW1232','2001','3','1','6','3','6')

-- -----------------------------------------------------

-- Insira registros nas tabelas: cidades,

-- -----------------------------------------------------

INSERT INTO cidade VALUES('1','Salvador','1')

INSERT INTO cidade VALUES('2','Pernambuco','2')

INSERT INTO cidade VALUES('3','Sao Paulo','3')

INSERT INTO cidade VALUES('4','Rio de Janeiro','4')

INSERT INTO cidade VALUES('5','Sergipe','5')

INSERT INTO cidade VALUES('6','Minas Gerais','6')

INSERT INTO cidade VALUES('7','Fortaleza','7')

Agora iremos testar alguns comandos do mysql, alguns até parecidos com os primeiros exemplos,uma coisa que aprendi: não tenha pressa, vá com calma, pois o conhecimento é igual a uma construção de um arranha céus, não é feito do dia para noite mais devagar , se não entender, poste duvidas. Espero ter ajudado a alguém...

Alterar a tabela proprietarios para que o atributo nome seja um varchar(60).

ALTER TABLE proprietarios MODIFY nome varchar(60);

Alterar a tabela tipoinfracoes para acrescentar um novo atributo pontos int(1).

ALTER TABLE tipoinfracoes ADD pontos int(11);

Exclua a tabela telefones.

DROP TABLE telefones;

Altere em combustível o registro onde nome ="álcool e gasolina" para nome="flex- álcool e gasolina".

UPDATE combustíveis set nome="flex- álcool e gasolina" where nome ="álcool e gasolina";

Liste o nome dos modelos da marca 3.

SELECT nome from modelos where marcas_id_marcas=3

Liste o renavam, data e tipoinfracao das infrações que ocorreram apartir do dia 01/04/2009.

SELECT * FROM infracoes where `datahora` >= '2009-04-01'

Liste o renavam e data das infrações que ocorreram apartir do dia 01/04/2009 e tipo de infração igual a 5.

SELECT * FROM infracoes where `datahora` >= '2009-04-01' and tiposinfracoes_id_tiposinfracoes = '5'

Liste o renavam e data das infrações que ocorreram entre os dias 01/03/2009 e 31/03/2009 e tipo de infração igual a 5

SELECT * FROM infracoes where `datahora` >= '2009-03-01' and `datahora` <= '2009-03-31' and tiposinfracoes_id_tiposinfracoes = '5'

Exiba placa, modelo e cor dos veículos que a placa começa com JRO.

SELECT placa,modelos_id_modelos,cores_id_cores from veiculos where placa LIKE 'JRO%'

Exiba placa, modelo e cor dos veículos que a placa tem o terceiro dígito Z e termina com 9.

SELECT placa,modelos_id_modelos,cores_id_cores FROM veiculos WHERE placa LIKE '__Z%0'

Selecione marca e modelo dos modelos que têm marcas que sejam igual a 01, 05, 12, 25, 33 ou 42, ordenado por marca e modelo.

SELECT * FROM marcas m LEFT OUTER JOIN modelos mo ON mo.marcas_id_marcas=m.id_marcas

WHERE marcas_id_marcas = '1' or marcas_id_marcas = '5' or marcas_id_marcas = '12' or marcas_id_marcas = '25'

or marcas_id_marcas = '33' or marcas_id_marcas = '42' ORDER BY m.nome

Mostre quantas multas são emitidas por dia no ano de 2009.

SELECT datahora, count(*) from infracoes GROUP BY datahora;

Mostre qual a velocidade média e qual a máxima das infrações.

SELECT avg(velocidade), max(velocidade) from infracoes;

Liste a quantidade de veículos de cada modelo.

SELECT marcas_id_marcas,COUNT(*)from modelos

Mostre a quantidade de veículos por tipo de combustível para a categoria 2.

SELECT id_veiculos, count(*) FROM veiculos where combustivel_id_combustivel = '2'

Mostre a quantidade de veículos por tipo de combustível para combustíveis cuja quantidade seja maior do que 50.

SELECT combustivel, count(*) from veiculos GROUP BY combustivel HAVING count(combustivel) > 50;

20) Mostre a quantidade de infrações de cada tipo de infração ocorrida em 2009 somente para tipos de infrações que a quantidade seja maior do que 10.

SELECT tiposinfracao, count(*) from infracoes where data like “2009%” GROUP BY tiposinfracao HAVING count(tiposinfracao) > 2;

Usamos agora a forma case;

SELECT velocidade,local,

case when velocidade < 100 then velocidade * 3

when(velocidade >= 150)and(velocidade <500)then velocidade *2

end nome_velocidade

from infracoes

Usando a função update atualizando valor de tipos de infrações;

UPDATE tiposinfracoes t SET valor = case

when valor < 100 then valor * 3

when(valor>=200)and(valor < 300)then valor * 2

else valor

end

Length → comando para determinar o tamanho em bytes de uma determinada cadeia de caracteres;

select valor,length(valor)from tiposinfracoes

Left → com esta função se extraem os “n” caracteres à esquerda de uma cadeia de caracteres;

select valor,left(valor,2)from tiposinfracoes

Right → Esta função estrai os “n” caracters à direita de uma cadeia de caracteres;

select valor,RIGHT(valor,3)from tiposinfracoes

Esta função extrai os “n” caracteres a partir de uma determinada posição em uma cadeia de caracteres, contando-se da esquera para a direita;

select nome,SUBSTRING(nome,3,2)from proprietarios

Para garantir que o conteudo de uma cadeia de caracteres seja sempre tratado como maiuscula, usa-se a função UPPER, minuscula LOWER

select nome,UPPER(nome)from proprietarios

select nome,LOWER(nome)from proprietarios

Trim,Ltrim,Rtrim

A função LTRIM retira apenas os espaços à esquerda (L é de left) da string

RTRIM → retira apenas os espaços a direita

TRANSLATE → é possível substituir caracteres de uma cadeia tomando como base uma outra cadeia de caracteres informada.

Função de manipulação de data

Select CURRENT_DATE()

Select CURRENT_TIME()

Select CURRENT_TIMESTAMP()

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)

Banco de dados

atributos de jogadores

Column Name DataType

numj int

nome varchar

pnome varchar

pais char

ano_n Decimal

ano_p Decimal

cid_n varchar

cid_res varchar

tit_s Decimal

tit_d Decimal

Bom esses são os atributos, agora irei explicar o PK significa que vai receber a chave primária,

NN - O campo não pode ser nulo

UQ - O campo não pode ser repetido.

A estrutura do workbench deve ficar assim :

Banco de dados

Olá gente, no inicio parece difícil quando não sabemos, mais depois que entendemos torna-se fácil. É só se dedicar.
Irei colocar algumas coisas que aprendi na faculdade e nos livros sobre banco de dados, espero que ajude.
Irei começar com modelagem de dados, irei usar o mysql workbench, e depois o mysql.
Vamos lá.....
Abra o worbench, click em create new ERR model
Bom olha a tela do mysql workbench:

Bom vamos criar um banco de dados com o nome de torneios, (Mais como fazer ?) bom dê dois clicks em cima de mydb, e onde está escrito Name : mydb , apague o mydb e coloque o nome torneios.

Depois disso dê dois clicks em Add Diagram

Irá apresentar essa tela :

Iremos criar 3 tabelas, click em Place New Table e depois click na area vazia, você poderá observar que  foi desenhado uma tabela, cujo nome padrão é Table1.
Segue imagem :

Bom apague o nome table1 e escreva jogadores.
Você terá uma tabela com nome jogadores
Depois disso click em Columns
Você irá adicionar atributos na sua tabela,
A sua tela estará assim no momento:

Em columns name apague a palavra idjogadores , pois por padrão o worbench ja atribui uma id para a tabela, iremos colocar os seguintes atributos:

Supply Chain Management

Cadeia de fornecimento é o grupo de fornecedores que supre as necessidades de uma empresa na criação e no desenvolvimento dos seus produtos. Pode ser entendido também como uma forma de colaboração entre fornecedores, varejistas e consumidores para a criação de valor. Cadeia de fornecimento pode ser definida como o ciclo da vida dos processos que compreendem os fluxos físicos, informativos, financeiros e de conhecimento, cujo objectivo é satisfazer os requisitos do consumidor final com produtos e serviços de vários fornecedores ligados. A cadeia de fornecimento, no entanto, não está limitada ao fluxo de produtos ou informações no sentido Fornecedor→ Cliente. Existe também um fluxo de informação, de reclamações e de produtos, entre outros, no sentido Cliente→ Fornecedor (Ayers, 2001, p. 4-5).

O Supply Chain Management (SCM) é a gestão da cadeia de fornecimento. Segundo alguns estudiosos, a competição no mercado global não ocorre entre empresas, mas entre cadeias de fornecimento. A gestão da logística e do fluxo de informações em toda a cadeia permite aos executivos avaliar pontos fortes e pontos fracos na sua cadeia de fornecimento, auxiliando a tomada de decisões que resultam na redução de custos, aumento da qualidade, entre outros, aumentando a competitividade do produto e/ou criando valor agregado e diferenciais em relação a concorrência (Shapiro, 2001, p. 4).

Os resultados que se esperam da utilização de sistemas que automatizem o SCM são:

• Reduzir custos;
• Aumentar a eficiência;
• Ampliar os lucros;
• Melhorar os tempos de ciclos da cadeia de fornecimento;
• Melhorar o desempenho nos relacionamentos com clientes e fornecedores;
• Desenvolver serviços de valor agregado que dão a uma empresa uma vantagem competitiva;
• Obter o produto certo, no lugar certo, na quantidade certa e com o menor custo;
• Manter o menor estoque possível.

Esses resultados são obtidos à medida que a gestão da cadeia de fornecimento simplificar e acelerar as operações que estão relacionadas com a forma como os pedidos do cliente são processados pelo sistema, até serem atendidos, e também, com a forma das matérias-primas serem adquiridas, e entregues, pelos processos de fabricação e distribuição.

Concluindo, é fundamental que as empresas se preocupem com a integração desses conjuntos de soluções de gestão, automatizadas através da tecnologia de informação (Simchi-Levi, 2003), pois só assim será possível obter maior vantagem estratégica e competitiva.

Um bom gerenciamento da cadeia de suprimentos, começa na avaliação dos gastos, no modelo atual de compras, na avaliação dos índices financeiros aplicados na renovação doscontratos por fornecedores e etc (Simchi-Levi, 2003), não basta colocar um software de administração da cadeia, se não alterar o modelo de gerenciamento. A idéia do Supply Chain, é reduzir as atividades táticas, ampliando a ação estratégica.

A área de suprimentos hoje, diferentemente de ontem, é responsável pelos resultados da empresa, a sinergia - ação dos órgãos simultaneamente, desenvolvida entre os departamentos fortalece a área que hoje, não só acompanha a aplicação dos contratos, mas como é responsável por todo o período de negociação (Chaib-Draa, 2006). Os fornecedores nesse momento, passam a ser parceiros no desenvolvimento de produtos, a quantidade de fornecedores é reduzida e o controle dos KPI´s acordados passam a ser melhor administrados, vis a vis, a possibilidade de relatórios emitidos pela ferramenta utilizada, com equalizações e demonstrativos de resultados.

O comprador definitivamente, abandona a fase de baixa ou nenhuma rastreabilidade e auditabilidade de seu processo de compras, passando a ter total visualização de todo o processo.

Processos

Abaixo um exemplo de cadeia de suprimentos, quanto aos gastos e despesas da empresa:

Linha de processos envolvidos Marketing Planejamento/Controle/Produção (PCP) Fornecedores Almoxarifado/Armazenagem Produção Estocagem Administração do Pedido/Despacho Marketing (retorno)

Todos o processos envolvem clientes, distribuição, produção e fornecedores.

Sendo:

• Marketing é essencialmente a arte de enviar uma mensagem aos clientes potenciais, e também aos que já fazem parte de sua carteira de clientes, para convencê-los a comprar de você. Envolvendo: faixa etária, poder aquisitivo, classe social, localização, concorrente; alem da função de propaganda e sistemas promocionais. As empresas reconhecem a importância do marketing e direcionam uma boa parte de seus recursos humanos e financeiros a essa atividade.
• PCP (Planejamento/ Controle/ Produção): tradução, expectativa para a realização da produção. As peças necessárias, equipamentos, etc.
• Fornecedores: fornecedores da matéria-prima, devem ser tratadas como parceiros, devendo até serem convidados a verem a produção; participar da produção, do dia a dia da empresa; já que ambos os conhecimentos podem atuar juntos, surgindo assim uma estrutura de competência altíssima.
• Almoxarifado/Armazenagem: sua atuação é importante no controle dos materiais que entram na empresa, vendo inclusive se os mesmos não estão em excesso.

Para melhor explorarmos a cadeia de fornecimento ou de suprimentos, entramos na logística empresarial que é o estudo da cadeia de suprimentos. Temos então, as Atividades Primárias e as Secundárias.

• Atividades Primárias
  • Transportes
  • Manutenção de estoques
  • Processamento de pedido

• Atividades Secundárias
  • Armazenagem
  • Manuseio de materiais
  • Embalagem de materiais
  • Obtenção (seleção de fontes, quantidades de compra)
  • Programação do produto (distribuição - fluxo de saída - oriente programação PCP)
  • Manutenção de informação (base de dados gerada pela cadeia - fonte de dados para futuros planejamentos.