Banco de Dados: MySQL Como criar o banco de dados Tipos de

Disciplina: Tópicos Especiais em TI – PHP
Este material foi produzido com base nos livros e documentos citados abaixo, que
possuem direitos autorais sobre o conteúdo. Favor adquiri-los para dar continuidade ao
curso.
Livro: Desenvolvendo Websites com PHP
Apostila da Divisão de Serviços à Comunidade – Centro de Computação – Unicamp
Banco de Dados: MySQL
Este módulo da disciplina irá abordar a utilização de um banco de dados em nossos
scripts PHP. Existem diversos banco de dados no mercado, abordaremos o uso do
MySQL.
Como criar o banco de dados
Para criar um banco de dados no MySQL utilizamos o utilitário mysql. Assim, se você
não tiver permissão para a criação de um banco de dados, deve-se solicitar ao
administrador para que ele crie seu banco de dados inicial.
Para a criação basta digitar:
mysql
> create database bdteste;
> use bdteste;
Para acessar a sua base de dados você digitará o comando:
mysql –u username –p
O primeiro parâmetro deve ser o caminho onde o mysql está localizado. A opção –u
indica que será digitado o nome de usuário e a opção –p indica que será digitado a senha
(password) para conexão.
Tipos de dados aceitos pelo MySQL
Tipo
Tinyint[(M)]
Bit, bool, boolean
Smallint [(M)]
Mediumint [(M)]
Int [(M)]
Bigint [(M)]
Float(precisão)
Float [(M,D)]
Double [(M,D)]
Real [(M,D)]
Descrição
Inteiro pequeno. Varia de –128 até +127
Sinônimo de tinyint.
Inteiro variando de –32768 até +32767.
Inteiro variando de –8388608 até
+8388607
Inteiro normal, variando de –2147483648
até +2147483647
Inteiro
grande,
variando
de
–
9223372036854775808
até
+9223372036854775807
Ponto flutuante com precisão definida
Ponto flutuante de precisão simples
Ponto flutuante normal
É um sinônimo para double
1
Decimal [(M,D)]
Date
Datetime
Timestamp [(M)]
Time
Year [(2|4)]
Char (M)
Varchar (M)
Tinytext
text
Esse tipo pode ser usado como uma string.
Cada número corresponde a um caractere.
Armazena uma data no formato yyy-mmdd. Pode variar de 1000-01-01 até 999912-31
Armazena data e hora. O intervalo
suportado é de 1000-01-01 00:00:00 até
9999-12-31 23:59:59
Armazena a data em formato=padrão
UNIX
Armazena um tempo.
Armazena um ano com 2 ou 4 dígitos
String de tamanho fixo
String de tamanho variável. Possui no
máximo 255 caracteres
Texto contendo até 255 caracteres
Texto com até 65535 caracteres
Observações:
- M representa o tamanho máximo para exibição
- D representa o número de dígitos após o ponto decimal
- Os colchetes indicam as partes opcionais
Como criar tabela em um banco de dados
Após a criação do banco de dados podemos acessá-lo e começar a criar as tabelas que
armazenarão os dados. Isso é feito por meio do comando CREATE TABLE, que possui
a seguinte sintaxe:
CREATE TABLE <nome_tabela> ( <nome_campo> tipo_de_dado [NULL | NOT
NULL] [DEFAULT valor_padrão], ... );
Significado:
nome_tabela: representa o nome da tabela que será criada. Não pode haver nomes de
tabelas repetidos.
nome_campo: representa o nome pelo qual o campo será referenciado.
tipo_de_dado: deve ser substituído por um dos tipos aceitos pelo banco de dados.
NULL|NOT NULL: define se o campo pode aceitar valores nulos ou não.
DEFAULT: define um valor padrão para inserções na tabela. Esse valor será utilizado
se nenhum valor para este campo for informado.
Exemplo: Criação de tabelas para um loja virtual. As seguintes tabelas serão criadas –
produtos, categorias e subcategorias.
CREATE TABLE produtos(
codigo_produto smallint NOT NULL,
nome_produto varchar(80) NOT NULL,
descricao_produto text,
preco float NOT NULL,
peso float,
cod_categoria smallint NOT NULL,
2
);
cod_subcategoria smallint NOT NULL,
adicionais text
CREATE TABLE categorias(
codigo_categoria smallint NOT NULL,
nome_categoria varchar(60) NOT NULL,
);
CREATE TABLE subcategorias(
codigo_subcategoria smallint NOT NULL,
nome_categoria varchar(60) NOT NULL,
codigo_categoria smallint NOT NULL,
);
Os campos seguidos da cláusula NOT NULL não aceitarão valores nulos no momento
da inserção no banco de dados.
Visualizando com o mysql
Para mostrar o nome das tabelas criadas digitamos no gerenciador do MySQL:
show tables;
E para visualizar a estrutura de determinada tabela:
describe <nome_tabela>;
ou
desc <nome_tabela>;
Você verá uma tela com os nomes dos campos, o tipo, se aceitam valores NULL, os
valores-padrão e outras características da tabela consultada.
Inserindo informações em um banco de dados
Cada linha de uma tabela de banco de dados é chamada de registro. Para inserir
registros na tabela, utilizamos o comando SQL INSERT.
Sintaxe:
INSERT INTO <nome_tabela> VALUES (valor1, valor2, ..., valorn);
INSERT INTO <nome_tabela> (nome_campo1, nome_campo 2, ..., nome_campox)
VALUES (valor1, valor2, ..., valorx);
No primeiro caso, os valores digitados no lugar de valor1, valor2, ..., valorn serão
incluídos na mesma ordem em que foram definidos os campos no momento da criação
da tabela. Os valores numéricos não devem ser delimitados por aspas. Já os dados do
tipo char, varchar, date e outros devem ser delimitados por aspas simples.
No segundo caso, os valores valor1, valor2, ..., valorx serão inseridos nos campos
campo1, campo2, ..., campox da tabela. Os campos não listados receberão o valor
3
NULL ou um valor padrão (caso exista). Se os campos não listados foram criados com
NOT NULL então ocorrerá um erro na execução do comando.
Exemplo:
INSERT INTO produtos VALUES(
1, ‘Camisa do gremio’, ‘Camisa com listras azuis’, 89.95, 1.5, 5, 2, ‘Disponivel com as
numerações 3,5 e 9’);
INSERT INTO produtos
(codigo_produto, nome_produto, preco, cod_categoria, cod_subcategoria)
VALUES (2, ‘Camisa do flamengo’, 49.95, 5, 2);
A mensagem exibida após a execução deve ser:
Query OK, 1 row affected (0.05 sec)
Alterando um banco de dados
Podemos alterar a estrutura de uma tabela ou os valores dos registros da tabela. Essas
alterações podem ser realizadas pelos comandos UPDATE e ALTER TABLE.
Comando UPDATE:
Realiza alterações nos valores dos registros da tabela. Sintaxe:
UPDATE <nome_tabela>
SET campo1=valor1 [, campo2=valor2, … , campon=valorn]
[WHERE <condicoes>];
Caso a cláusula WHERE não seja utilizada, a alteração será efetuada em todos os
registros da tabela.
Exemplos:
Alteração do código de uma categoria:
UPDATE categorias SET codigo_categoria=6 WHERE codigo_categoria=5;
Alteração do código de todos os produtos que pertenciam a essa categoria:
UPDATE produtos SET cod_categoria=6 WHERE cod_categoria=5;
O gerenciador do banco de dados sempre informará o número de registros afetados após
a execução do comando UPDATE.
Comando ALTER TABLE:
Realiza alterações na estrutura da tabela (inclui campos, altera nome de um campo ou
de uma tabela). Sintaxe:
ALTER TABLE <nome_tabela> ADD <nome_campo> tipo_de_dado;
ALTER TABLE <nome_tabela> CHANGE
tipo_de_dado not null;
<nome_campo> <novo_nome_campo>
4
ALTER TABLE <nome_tabela> RENAME TO <novo_nome_tabela>;
Exemplo:
ALTER TABLE produtos ADD fabricante varchar(50);
ALTER TABLE produtos CHANGE codigo_produto cod_prod (varchar(50)) not null;
O campo fabricante será incluído como o último campo da tabela produtos.
OBS: Não é possível excluir campos de uma tabela. Se precisarmos excluir um campo
devemos apagar a tabela e criá-la novamente.
Excluindo informação de um banco de dados
Podemos realizar dois tipos de exclusões: excluir uma tabela ou excluir registros de uma
tabela. Isto pode ser feito através dos comandos DELETE e DROP TABLE.
Comando DELETE:
Esse comando exclui um ou mais registros de uma tabela.Sintaxe:
DELETE FROM <nome_tabela> [WHERE <condições>];
Caso a cláusula WHERE não seja utilizada todos os registros da tabela serão excluídos.
Exemplos:
Exclusão da categoria Artigos Esportivos da tabela categorias:
DELETE FROM categorias WHERE nome_categoria=’Artigos Exportivos’;
Exclusão de todos os produtos da categoria áudio e vídeo (3):
DELETE FROM produtos WHERE cod_categoria=3;
Exclusão de todos os produtos existentes na tabela:
DELETE FROM produtos;
O gerenciador do banco de dados sempre informará o número de registros afetados após
a execução do comando DELETE.
Comando DROP TABLE:
Serve para excluir uma tabela de um banco de dados. Sintaxe:
DROP TABLE <nome_tabela1> [ , <nome_tabela2, ... ];
Exemplo:
DROP TABLE produtos, categorias, subcategorias;
Fazendo consultas em um banco de dados
O comando SELECT é responsável pelos diversos tipos de consultas que podem ser
feitas.
5
Comando SELECT:
Com este comando podemos selecionar todas as linhas de uma ou mais tabelas, ou
apenas uma parte delas. Sintaxe:
SELECT <lista_campos> FROM <lista_tabelas> [WHERE <condições>];
Se <lista_campos> for substituída por um asterisco (*), serão retornados todos os
campos existentes na(s) tabela(s) em uso. Se a cláusula WHERE for omitida, serão
mostrados todos os registros das tabelas determinadas em <lista_tabelas>.
Exemplos:
Listar todas as categorias existentes na tabela categorias:
SELECT * FROM categorias;
Listar o código, o nome e o preço de todos os produtos (da tabela produtos):
SELECT codigo_produto, nome_produto, preco FROM produtos;
-
Utilizando a cláusula WHERE para determinar quais registros devem ser
retornados:
Obtendo o nome e a descrição dos produtos que custam mais de R$50,00:
SELECT nome_produto FROM produtos WHERE preco>50;
Listar todas as subcategorias pertencentes a uma categoria da loja:
SELECT nome_subcategoria FROM subcategorias WHERE codigo_categoria=5;
O comando WHERE deve ser seguido por uma ou mais condições. Essas condições
podem conter os operadores de comparação >, <, >=, <=, =, e <>. Se houver mais de
uma condição a ser analisada, utilizamos os operadores lógicos AND e OR entre elas.
Quando for necessário podemos utilizar o parênteses para determinar a ordem de
avaliação das expressões.
Exemplo:
O comando abaixo irá retornar o nome de todos os produtos que pertencem à categoria
Artigos Esportivos e à subcategoria Camisetas de Futebol ou retorna o nome dos
produtos que possuem o código da subcategoria igual a 3, independentemente da
categoria.
SELECT nome_produto FROM produtos
cod_subcategoria=2 OR cod_subcategoria=3;
WHERE
cod_categoria=5
AND
Utilizando o parênteses para informar a ordem de avaliação das expressões:
SELECT nome_produto FROM produtos
(cod_subcategoria=2 OR cod_subcategoria=3);
WHERE
cod_categoria=5
AND
Comando LIKE:
A linguagem SQL oferece o operador LIKE que é utilizado para fazer consultas mais
complicadas. Com ele, pode-se descobrir por exemplo todos os produtos que começam
6
com a letra “E”, ou todos que terminar com a letra “A”, ou todos que contenham a letra
“O”. Podemos também buscar por palavras dentro de um campo dos registros de uma
tabela.
Exemplo:
Pesquisa em uma tabela de veículos todos os carros que possuem o adicinal arcondicionado:
SELECT * FROM veiculos WHERE adicionais LIKE ‘%ar-condicionado%’;
Selecionar todas as categorias cujo nome terminam com a letra “o”:
SELECT * FROM categorias WHERE nome_categoria LIKE ‘%o’;
O comando LIKE é utilizado com o símbolo %, que representa uma sequencia de
caracteres, ou com o símbolo _, que representa um caracter qualquer.
Formas de uso:
Operador
LIKE ‘_E%’
LIKE ‘%O’
LIKE ‘A%E%O’
NOT LIKE ‘%@%’
Descrição
A letra E está na segunda posição.
Termina com a letra O.
Começa com a letra A, termina com a letra
O e possui a letra E no meio.
Retorna aqueles que NÃO contêm o
caractere @. Podemos utilizar, por
exemplo, para verificar se existem e-mails
digitados incorretamente em um banco de
dados, já que o caractere @ deve existir
em todos os e-mails.
OBS: É importante lembrar que existe a distinção entre as letras maiúsculas e
minúsculas. Por isso é interessante que as informações sejam armazenadas no banco de
dados sempre em letras maiúsculas ou minúsculas para facilitar a pesquisa.
Comando DISTINCT:
Esta opção é utilizada sempre que queremos excluir valores repetidos do resultado de
um consulta.
Exemplo: Visualizar o nome de todas as cidades existentes no banco de dados.
SELECT DISTINCT (cidade) FROM usuarios;
Comando COUNT:
Pode ser usado para contar o número de registros existentes na tabela.
Exemplo:
SELECT COUNT(*) FROM produtos;
SELECT COUNT(*) FROM produtos WHERE cod_categoria=1;
7
Comando SUM: (usada apenas para dados do tipo numérico)
Soma os valores de determinado campo de uma tabela.
Exemplo:
SELECT SUM (preco) FROM produtos;
Comando MAX/MIN:
Retorna o registro que possui determinado campo de maior/menor valor.
Exemplo:
SELECT MAX (preco) FROM produtos;
Comando AVG: (usada apenas para dados do tipo numérico)
Calcula a média dos valores de determinado campo.
Exemplo:
SELECT AVG (preco) FROM produtos;
Comando GROUP BY:
Permite agrupar os resultados de uma consulta.
Exemplo:
Para saber quantos produtos existem em cada uma das categorias, em vez de digitar um
comando com COUNT para cada uma, podemos fazer da seguinte forma:
SELECT cod_categoria,COUNT(*) FROM produtos GROUP BY cod_categoria;
Também pode ser utilizado com a opção HAVING que seleciona alguns registros
retornados pelo GROUP BY.
Exemplo:
SELECT cod_categoria,COUNT(*) FROM produtos GROUP BY cod_categoria
HAVING COUNT(*)=1;
Consultas utilizando mais de uma tabela:
Ecemplo: Mostrar o nome da categoria com o número de ocorrências de produtos de
cada código de categoria:
SELECT
cod_categoria,COUNT(*)
FROM
produtos,categorias
codigo_categoria-cod_categoria GROUP BY nome_categoria;
WHERE
Ordenando resultados de uma consulta
Para ordenar registros retornados por uma consulta a um banco de dados, utilizamos a
opção ORDER BY.
8
Exemplo:
Ordenar em ordem alfabética o nome de todos os produtos da loja.
SELECT codigo_produto,nome_produto FROM produtos ORDER BY nome_produto;
SELECT codigo_categoria,nome_subcategoria FROM subcategorias ORDER BY
codigo_categoria,nome_subcategoria;
Opção DESC:
Ordenação decrescente dos resultados.
Ex.: SELECT codigo_produto,nome_produto
nome_produto DESC;
FROM
produtos
ORDER
BY
Determinando o número de linhas retornadas
Utilizando a opção LIMIT podemos determinar o número máximo de registros que uma
consulta pode retornar.
Exemplo:
SELECT * FROM produtos WHERE nome_produto LIKE ‘CD%’ LIMIT 10;
Desta forma será exibida a lista de CDs de 10 em 10 por página. Para mostrar o
resultado sempre na mesma ordem ao usuário é necessário usar a opção ORDER BY.
SELECT * FROM produtos WHERE nome_produto LIKE ‘CD%’ ORDER BY
nome_produto LIMIT 10;
Opção OFFSET:
Utilizado em conjunto com o LIMIT para determinar a partir de qual registro a consulta
deve retornar.
SELECT * FROM produtos WHERE nome_produto LIKE ‘CD%’ ORDER BY
nome_produto LIMIT 10 OFFSET 10;
Utilizando INSERT e SELECT para inserir registros
Podemos utilizar a combinação dos comandos INSERT e SELECT para inserir os
registros de uma tabela em outra.
Exemplo:
INSERT INTO tabela_precos SELECT nome_produto, preco FROM produtos WHERE
codigo_produto>=6 AND codigo_produto<=55;
Criando e utilizando sequências
Sequência é um recurso que utilizamos para fazer uma numeração automática de
determinado campo de uma tabela. Utilizando-se MySQL pode-se criar um campo com
a propriedade de auto-incremento da seguinte forma:
9
CREATE TABLE produto(
codigo int NOT NULL AUTO_INCREMENT,
nome varchar(70) NOT NULL,
primary key(codigo)
);
O campo com numeração automática deve ser declarado como chave primária da tabela
para evitar a gravação de chaves repetidas. E para inserir registros na tabela:
INSERT INTO produto (nome) VALUES (‘Taco de Baseball’);
10