APOSTILA MySQL. Professor: Msc. Marx Gomes van der Linden Curso Superior em Tecnologia: Redes de Computadores IFPB 2009

(1)

APOSTILA

MySQL

Professor: Msc. Marx Gomes van der Linden

(2)

Introdução a MySQL

Onde armazenar dados?



Praticamente toda aplicação web precisa lidar com grandes volumes de dados.



Dados podem ser armazenados em arquivos (texto ou binários)



Redundância



Manutenção e Escalabilidade



Integridade



Eficiência

Banco de dados



Um banco de dados é um conjunto de dados armazenados de forma automaticamente organizada

e gerenciada.



Um banco de dados é controlado por uma aplicação: (SGBD)



Todo o acesso a dados é feito indiretamente, por meio do SGBD.

Vantagens



Consistência e Integridade



Um banco de dados bem organizado evitar o armazenamente desnecessário de informações

redundantes, evitando a possibilidade de inconsistência.



Eficiência



SGBDs são altamente otimizados para armazenar e buscar informação da maneira mais eficiente

possível.



Manutenção



Um Sistema de Gerenciamento de Banco de Dados cuida automaticamente de todos os detalhes

da implementação física do armazenamento de dados.



Escalabilidade



Sua aplicação pode crescer sem mudanças fundamentais no design.

Banco de dados relacional



Composto por Entidades (ou Tabelas).



Cada tabela tem campos.

(3)

SQL



Structured Query Language



Linguagem-padrão para manipulação de informações em bancos de dados.



Definições de dados



Consultas



Modificações, Inserções, Exclusões



Controle de Acesso

Banco de dados em PHP



PHP tem suporte a praticamente todos os sistemas de Bancos de Dados em uso atualmente:



MS SQL Server



MySQL



Oracle



PostgreSQL



SQLite



etc..

Projeto de banco de dados



Descrição das entidades que compõem o sistema.



Definição das propriedades de cada entidade que se deseja armazenar.



Elaboração dos relacionamentos entre as entidades.

MySQL



MySQL é um dos SGBDs mais amplamente usados no mundo.

o Software livre/open source



Desenvolvido pela MySQL AB, de propriedade da Sun

Microsystems.

(4)



Leve, eficiente e robusto



Excelente integração com PHP

MySQL cliente/servidor

Arquitetura cliente/servidor



O Servidor cuida do controle de concorrência.



Não há conflito se dois clientes tentarem modificar o mesmo dado ao mesmo tempo.



O Servidor tem embutido um mecanismo de controle de acesso.



O Cliente e o Servidor podem estar distribuídos fisicamente de qualquer maneira.

SQL



A unidade básica de SQL é a consulta (query):



Executa uma operação no Banco de Dados.



Pode modificar o BD.



Retorna:



Se a instrução foi ou não um sucesso.



O número de linhas afetadas no banco de dados.



Se aplicável, retorna um conjunto de linhas contendo as informações solicitadas.



Características de SQL:

o Não faz distinção entre maiúsculas e minúsculas.

o Ignora espaços.

o Cada instrução termina com ;

o Comentários podem ser feitos com –

Cliente MySQL



O cliente-padrão MySQL é uma aplicação de linha de comando.



Interativo



Script



Uso:

(5)

SELECT



O comando SELECT busca e retorna uma informação do servidor.



Pode ser usado com qualquer combinação de:



Valores literais.



Funções embutidas.



Campos da tabela.



SELECT com valor literal:

mysql> SELECT 'Hello, World!'; +---+

| Hello, World! | +---+ | Hello, World! | +---+ 1 row in set (0.00 sec)

Criando um banco de dados



A primeira ação a ser tomada em uma nova instalação do MySQL é criar um novo banco de

dados.



Vários bancos de dados podem estar instalados ao mesmo tempo.



Cada banco de dados é identificado por um nome.

CREATE DATABASE



Para criar um novo banco de dados, utiliza-se o comando CREATE DATABASE.



Sintaxe:



CREATE DATABASE nome_bd;

mysql> CREATE DATABASE teste; Query OK, 1 row affected (0.08 sec)

USE



Depois, é necessário informar ao MySQL que começaremos a usar o banco de dados criado.



Sintaxe:



USE nome_bd;

mysql> USE teste; Database changed

CREATE TABLE



Para criar uma nova tabela no banco de dados, usa-se o comando CREATE TABLE.



Sintaxe:

 CREATE TABLE [IF NOT EXISTS] nome_tabela ( definição ) [ENGINE = nome_engine] [CHARACTER SET charset] [COLLATE collation];



A definição consiste de pares:

nome TIPO,

(6)



É possível especificar um mecanismo de tabela (engine)



Se não for especificada, é usado o padrão.



É possível especificar um conjunto de caracteres e/ou uma collation.



Exemplo:

mysql> CREATE TABLE tabela -> (

-> f FLOAT(10,4),

-> c CHAR(15) NOT NULL DEFAULT 'none', -> i TINYINT UNSIGNED NULL

-> );

Query OK, 0 rows affected (0.04 sec)

Tipos de dados



No MySQL, a cada coluna de cada tabela deve ser associado um tipo de dado.



Existem vários tipos de dados, pertencentes a várias categorias:



Numéricos



Inteiros



Reais



Textos



Dados Binários



Data/Hora

Tipos de dados inteiros



TINYINT

→ Inteiro de 1 byte



SMALLINT

→ Inteiro de 2 bytes



MEDIUMINT

→ Inteiro de 3 bytes



INT → Inteiro de 4 bytes (32 bits)



BIGINT → Inteiro de 8 bytes (64 bits)

Tipos de dados numéricos



Todos os tipos de dados inteiros têm 3 campos opcionais:



(M) → Largura máxima de exibição (número de caracteres)



UNSIGNED

→ Não permite valores negativos (dobra a capacidade para positivos)



ZEROFILL →

Preenche com zeros o espaço restante, na exibição

BIT



BIT[(M)]



Armazena uma seqüência de M bits.

 Padrão: M = 1

(7)

Tipos de dados reais



Em todos os casos: M dígitos totais, com D casas decimais.



FLOAT[(M[,D])] →

Número de ponto flutuante de precisão simples (32 bits)



DOUBLE[(M[,D])] →

Número de ponto flutuante de precisão dupla (64 bits)



DECIMAL[(M[,D])]

→ Número de ponto fixo.



Padrão:

M = 10, D = 0

Tipos de dados para texto



CHAR[(M)]

→ M caracteres



Padrão: M = 1



VARCHAR(M) →

Uma string de, no máximo, M caracteres



TINYTEXT →

Texto de até 255 caracteres.



TEXT[(M)]

→ Texto de até 64kb.

 MEDIUMTEXT →

Texto de até 14mb.

 LONGTEXT →

Texto de até 4gb

.

ENUM e SET

 ENUM('valor1','valor2',...)

Uma string que pode ter apenas um dos valores descritos na enumeração.

 SET('valor1','valor2',...)

Uma string que pode ter zero ou mais valores, desde que todos façam parte do conjunto.



A lista de dados especificada na criação define os únicos valores legais do campo.



Pode haver ou não distinção entre maiúsculas e minúsculas, dependendo do collation.



No ENUM, a ordem especificada é a ordem usada em operações de ordenamento.



Os campos de SET e ENUM são armazenados como números inteiros:



ENUM: 1 ou 2 bytes



SET: 1,2,3,4 ou 8 bytes

Tipos de dados para data e hora



DATE → Data, no formato 'CCYY-MM-DD'



De '1000-01-01' a '9999-12-31'



TIME → Hora, no formato 'hh:mm:ss'



De '-838:59:59' a '838:59:59'



DATETIME → Data e hora, no formato, 'CCYY-MM-DD hh:mm:ss'



De '1000-01-01 00:00:00' a '9999-12-31 23:59:59'



TIMESTAMP → Timestamp 32bits, no formato 'CCYY-MM-DD hh:mm:ss'



De '1970-01-01 00:00:01' a '2038-01-09 03:14:07'



YEAR → Ano, no formato CCYY ou YY



De 1901 to 2155, ou 0000

NULL vs. NOT NULL



Para qualquer campo, é possível especificar se se quer que sejam permitidos valores nulos

(vazios):

(8)



NOT NULL



Para a maioria dos campos, é possível especificar um valor padrão:



DEFAULT valor

PRIMARY KEY



Para especificar uma chave primária, basta acrescentar após o nome do campo a frase

PRIMARY KEY.



Chaves primárias:



Não podem ter valores repetidos.



Não podem ter valores nulos.



Identificam inequivocadamente uma linha na tabela.

Inserção e seleção em tabelas



Para inserir novas entradas (linhas) em um tabela, usa-se INSERT INTO.



Sintaxe:



INSERT INTO

tabela

[(campo1, campo2...)]

VALUES (valor1,

valor2...)

Exemplo

mysql> INSERT INTO usuario VALUES ('sawyer', 'James', 'Ford', 'masc'); Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM usuario;

mysql> INSERT INTO usuario (ultimo_nome, login, sexo) VALUES ('Eko', 'mreko', 'masc'); Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM usuario;

(9)

INSERT INTO



É possível inserir mais de uma linha em um mesmo comando INSERT INTO.



Cada linha da tabela deve estar contida em (), com os agrupamentos separados por vírgulas.

mysql> INSERT INTO usuario VALUES ('hurley', 'Hugo', 'Reyes', 'masc'), ('sun', 'Sun-Hwa', 'Kwon', 'fem');

Query OK, 2 rows affected (0.10 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM usuario;

SELECT



O comando SELECT também serve para extrair informações armazenadas no banco de

dados.



É possivelmente o comando mais complexo e poderoso da linguagem SQL.



Sintaxe (simplificada):

o SELECT <campos> FROM tabela [WHERE condições]

 O símbolo * significa "todos os campos"

mysql> SELECT * FROM sgbd;

+----+---+---+---+---+ | id | nome | maxcols | lanc | livre | +----+---+---+---+---+ | 1 | MySQL | 3398 | 1996 | sim | | 2 | PostgreSQL | 1600 | 1989 | sim | | 3 | Oracle | 1000 | 1979 | nao | | 4 | MS SQL Server | 1024 | 1989 | nao | | 5 | MS Acess | 255 | 1992 | nao | | 6 | SQLite | 2000 | 2000 | sim | +----+---+---+---+---+ 6 rows in set (0.00 sec)

SELECT... WHERE



Após a cláusula WHERE, é possível especificar uma condição envolvendo uma ou mais

colunas.

(10)

Operadores booleanos



É possível usar operadores booleanos em expressões:

 OR, ||

 AND, &&  NOT, !  XOR

mysql> SELECT * FROM sgbd

-> WHERE lanc > 1990 AND livre = 'sim'; +----+---+---+---+---+ | id | nome | maxcols | lanc | livre | +----+---+---+---+---+ | 1 | MySQL | 3398 | 1996 | sim | | 6 | SQLite | 2000 | 2000 | sim | +----+---+---+---+---+ 2 rows in set (0.00 sec)

SELECT... ORDER BY



Para ordenar os resultados retornados por um select, basta anexar o parâmentro ORDER BY.



Sintaxe:

 SELECT (...) ORDER BY campo1 [DESC|ASC] [, campo2 [DESC|ASC] ... ] mysql> SELECT * FROM usuario ORDER BY login;

SELECT DISTINCT



A palavra DISTINCT indica que o SELECT deve retornar todos os valores não repetidos de

uma coluna ou combinação de colunas.



Sintaxe:

(11)

SELECT… LIMIT

mysql> SELECT * FROM exemplo; +---+---+---+ | x | y | z | +---+---+---+ | a | b | c | | a | b | d | | a | b | e | | i | j | d | | i | j | e | | i | j | k | | i | z | k | +---+---+---+ 6 rows in set (0.00 sec)

COUNT, GROUP BY



A função COUNT pode ser usada em um SELECT para contabilizar a quantidade de

resultados encontrados.



Sintaxe:

 SELECT COUNT(colunas...)(...)[GROUP BY coluna]

SELECT COUNT

mysql> SELECT COUNT(*) FROM usuario -> WHERE sexo = 'masc';

+---+ | COUNT(*) | +---+ | 8 | +---+

1 row in set (0.00 sec)

SELECT COUNT.. GROUP BY

(12)

MySQL – Múltiplas Tabelas

Relacionamentos



Três tipos de relacionamentos podem ser definidos entre duas entidades (tabelas) em um

banco de dados:

o 1 para 1

(1-1)

o 1 para vários

(1-*)

o Vários para vários

(-)



O uso correto de relacionamentos

o Evita a redundância no armazenamento de informações.

o Permite a realização de consultas complexas de maneira eficiente.



Relacionamentos 1-1 são incomuns, pois pode ser modelados como atributos.

Relacionamentos 1-*

Pessoa

mysql> SELECT * FROM pessoa; +---+---+ | pessoa_id | nome | +---+---+ | 1 | Maria | | 2 | Jose | | 3 | Ana | | 4 | Carlos | | 5 | Paulo | +---+---+ 5 rows in set (0.01 sec) mysql> SELECT * FROM endereco; +---+---+---+ | end_id | pessoa_id | end | +---+---+---+ | 1 | 1 | AB | | 2 | 1 | CD | | 3 | 2 | EF | | 4 | 2 | GH | | 5 | 2 | IJ | | 6 | 4 | XY | | 7 | 4 | QW | | 8 | 4 | ZZ | | 9 | 5 | PQ | | 10 | 6 | ST | +---+---+---+ 10 rows in set (0.00 sec)

(13)

SELECT... JOIN... ON



Para buscar informações em mais de uma tabela, usa-se JOIN.



Sintaxe:



SELECT campos FROM tabela1 JOIN tabela2

[ON condição]

JOIN sem ON

mysql> SELECT * FROM pessoa JOIN endereco;

+---+---+---+---+---+ | pessoa_id | nome | end_id | pessoa_id | end | +---+---+---+---+---+ | 1 | Maria | 1 | 1 | AB | | 2 | Jose | 1 | 1 | AB | | 3 | Ana | 1 | 1 | AB | | 4 | Carlos | 1 | 1 | AB | | 5 | Paulo | 1 | 1 | AB | | 1 | Maria | 2 | 1 | CD | | 2 | Jose | 2 | 1 | CD | | 3 | Ana | 2 | 1 | CD | | 4 | Carlos | 2 | 1 | CD | | 5 | Paulo | 2 | 1 | CD | | 1 | Maria | 3 | 2 | EF | | 2 | Jose | 3 | 2 | EF | (...)

JOIN... ON

mysql> SELECT * FROM pessoa JOIN endereco ON pessoa.pessoa_id = endereco.pessoa_id;

+---+---+---+---+---+ | pessoa_id | nome | end_id | pessoa_id | end | +---+---+---+---+---+ | 1 | Maria | 1 | 1 | AB | | 1 | Maria | 2 | 1 | CD | | 2 | Jose | 3 | 2 | EF | | 2 | Jose | 4 | 2 | GH | | 2 | Jose | 5 | 2 | IJ | | 4 | Carlos | 6 | 4 | XY | | 4 | Carlos | 7 | 4 | QW | | 4 | Carlos | 8 | 4 | ZZ | | 5 | Paulo | 9 | 5 | PQ | +---+---+---+---+---+ 9 rows in set (0.00 sec)

SELECT... JOIN... ON



É possível simplificar os nomes das tabelas usando AS.



Sintaxe:

(14)

mysql> SELECT p.nome, e.end

-> FROM pessoa AS p JOIN endereco AS e -> ON p.pessoa_id = e.pessoa_id; +---+---+ | nome | end | +---+---+ | Maria | AB | | Maria | CD | | Jose | EF | | Jose | GH | | Jose | IJ | | Carlos | XY | | Carlos | QW | | Carlos | ZZ | | Paulo | PQ | +---+---+

9 rows in set (0.00 sec)

USING



A palavra USING pode ser usada em lugar de ON, para simplificar a expressão.



Sintaxe:



SELECT campos FROM tabela1 [AS t1] [LEFT|RIGHT] JOIN tabela2

[AS t2][ON condição | USING (campo1, campo2...)]

mysql> SELECT * FROM pessoa

-> JOIN endereco USING(pessoa_id); +---+---+---+---+ | pessoa_id | nome | end_id | end | +---+---+---+---+ | 1 | Maria | 1 | AB | | 1 | Maria | 2 | CD | | 2 | Jose | 3 | EF | | 2 | Jose | 4 | GH | | 2 | Jose | 5 | IJ | | 4 | Carlos | 6 | XY | | 4 | Carlos | 7 | QW | | 4 | Carlos | 8 | ZZ | | 5 | Paulo | 9 | PQ | +---+---+---+---+ 9 rows in set (0.00 sec)

(15)

Tabela CD

mysql> SELECT * FROM cd;

+---+---+---+--- | cd_id | titulo | artista | lanca +---+---+---+--- | 1 | Red Album | Weezer | 2008- | 2 | Blue Album | Weezer | 1994- | 3 | Green Album | Weezer | 2001- | 4 | Icky Thump | The White Stripes | 2007- | 5 | Elephant | The White Stripes | 2003- | 6 | Green | R.E.M | 1988- | 7 | Nevermind | Nirvana | 1991- +---+---+---+--- 7 rows in set (0.00 sec)

Tabela música

mysql> SELECT * FROM musica;

+---+---+---+--- | musica_id | cd_id | faixa | titulo +---+---+---+--- | 1 | 1 | 1 | Troublemaker | 2 | 1 | 2 | The Greatest Man T | 3 | 1 | 3 | Pork And Beans | 4 | 1 | 4 | Heart Songs | 5 | 1 | 5 | Everybody Get Dang | 6 | 1 | 6 | Dreamin' | 7 | 1 | 7 | Thought I Knew | 8 | 1 | 8 | Cold Dark World | 9 | 1 | 9 | Automatic | 10 | 1 | 10 | The Angel And The | 11 | 1 | 11 | Miss Sweeney (Bonu (...)

JOIN… USING

(16)

Consultas



Quantas músicas estão associadas a cada CD?



Qual a duração total de cada CD?



Músicas por CD

Renomeando colunas



A partícula AS também pode ser usada após cada campo a ser incluído no SELECT, para

renomear uma coluna de modo a torná-la mais legível.



Músicas por CD

mysql> SELECT artista, cd.titulo, -> COUNT(*) AS musicas



Duração total de cada CD

mysql> SELECT artista, cd.titulo, -> COUNT(*) AS musicas, -> SUM(duracao) AS totaldur

-> FROM cd JOIN musica USING(cd_id) -> GROUP BY cd.titulo ORDER BY artista;

(17)



Duração total de cada CD, vol. 2

mysql> SELECT artista, cd.titulo, -> COUNT(*) AS musicas,

-> SEC_TO_TIME(SUM(duracao)) AS totaldur -> FROM cd JOIN musica USING(cd_id) -> GROUP BY cd.titulo ORDER BY totaldur;

+---+---+---+---+ | artista | titulo | musicas | totaldur | +---+---+---+---+ | Weezer | Green Album | 10 | 00:43:49 | | R.E.M | Green | 11 | 01:04:46 | | Weezer | Blue Album | 10 | 01:05:53 | | Nirvana | Nevermind | 12 | 01:07:12 | | The White Stripes | Icky Thump | 13 | 01:16:09 | | The White Stripes | Elephant | 14 | 01:17:47 | | Weezer | Red Album | 14 | 01:36:29 | +---+---+---+---+ 7 rows in set (0.00 sec)



Consultas

o Quais músicas, de que artistas, têm duração maior que 5min?

o Quantas músicas maiores que 5min tem cada artista?



Músicas maiores que 5 mins

mysql> SELECT artista, m.titulo, duracao -> FROM cd JOIN musica AS m USING(cd_id) -> WHERE duracao > '00:05:00';

mysql> SELECT artista, COUNT(*) AS musicas -> FROM cd JOIN musica AS m USING(cd_id) -> WHERE duracao > '00:05:00' -> GROUP BY artista; +---+---+ | artista | musicas | +---+---+ | Nirvana | 1 | | R.E.M | 1 | | The White Stripes | 2 | | Weezer | 6 | +---+---+ 4 rows in set (0.00 sec)

(18)



Número total e duração média das músicas, organizadas por gravadora do CD.

mysql> SELECT gravadora, COUNT(*) AS musicas, -> SEC_TO_TIME(AVG(duracao)) AS mediadur -> FROM cd JOIN musica USING(cd_id) -> GROUP BY gravadora;

+---+---+---+ | gravadora | musicas | mediadur | +---+---+---+ | Geffen Records | 36 | 00:06:23 | | Interscope Records | 10 | 00:04:23 | | Warner Bros | 38 | 00:05:45 | +---+---+---+ 3 rows in set (0.00 sec)

ALL, ANY



X <= ALL (Z)



Retorna verdadeiro se X for menor ou igual a todos os valores de (Z)



X <= ANY (Z)



X <= SOME (Z)



Retorna verdadeiro se X for menor ou igual a pelo menos um valor de (Z)

mysql> SELECT duracao FROM musica -> WHERE faixa > 10; +---+ | duracao | +---+ | 00:04:02 | | 00:04:02 | | 00:04:43 | | 00:05:11 | | 00:04:18 | | 00:04:16 | | 00:03:00 | | 00:01:48 | | 00:03:40 | | 00:03:17 | | 00:02:42 | | 00:03:09 | | 00:03:16 | | 00:03:52 | +---+

Consulta



Todas as músicas cuja duração seja menor ou igual à duração de qualquer música que

apareça em uma faixa acima de 10.

ALL

(19)

mysql> SELECT titulo, duracao

-> FROM musica WHERE duracao <= ALL ( -> SELECT duracao FROM musica

-> WHERE faixa > 10 -> );

+---+---+ | titulo | duracao | +---+---+ | St. Andrew [This Battle Is In The Air] | 00:01:47 | | Hypnotize | 00:01:48 | +---+---+ 2 rows in set (0.00 sec)

(20)

CD

mysql> SELECT * FROM cd;

+---+---+---+---+ | cd_id | art_id | titulo | lancamento | +---+---+---+---+ | 1 | 1 | Red Album | 2008-06-03 | | 2 | 1 | Blue Album | 1994-05-10 | | 3 | 1 | Green Album | 2001-05-15 | | 4 | 2 | Icky Thump | 2007-06-19 | | 5 | 2 | Elephant | 2003-04-01 | | 6 | 3 | Green | 1988-11-08 | | 7 | 4 | Nevermind | 1991-09-24 | +---+---+---+---+ 7 rows in set (0.00 sec)

Gravadora

GravadoraCD

mysql> SELECT * FROM gravadora_cd; +---+---+ | cd_id | grav_id | +---+---+ | 1 | 1 | | 2 | 1 | | 3 | 2 | | 4 | 3 | | 5 | 3 | | 6 | 3 | | 7 | 1 | +---+---+ 7 rows in set (0.00 sec)

(21)

JOIN

mysql> SELECT gcd.cd_id, g.nome AS gravadora, -> titulo, lancamento FROM gravadora AS g -> JOIN gravadora_cd AS gcd JOIN cd -> ON gcd.cd_id = cd.cd_id

-> AND gcd.grav_id = g.grav_id;