Escola Secundária de Albufeira

Comandos MySQL(Páginas Web Dinâmicas: PHP e MySQL)

Carlos Nunes(csmnunes@gmail.com)

2009/2010

2Carlos Nunes

Gerir bases de dados

■ Criar Base de DadosCREATE DATABASE baseDeDados;

■ Apagar Base de DadosDROP DATABASE baseDeDados;

■ Listar Bases de Dados

SHOW DATABASES;

■ Usar Base de DadosUSE baseDeDados;

3Carlos Nunes

Gestão de tabelas - Criar Tabela

CREATE TABLE nomeTabela ( campo1 tipoDados1 [OPÇÃO1, OPÇÃO2, ...], campo2 tipoDados2, ...), PRIMARY KEY (campo1));

Exemplo:CREATE TABLE cliente ( cod_cliente bigint AUTO_INCREMENT, nome VARCHAR (50) NOT NULL, morada TEXT, estado ENUM ('casado','solteiro') DEFAULT 'casado', telefone CHAR(9), ordenado DECIMAL(10,2) UNSIGNED, PRIMARY KEY (cod_cliente));

4Carlos Nunes

Que tipos de dados podemos definir?

■ Numérico BIT TINYINT BOOL SMALLINT MEDIUMINT INT BIGINT FLOAT DOUBLE DECIMAL

■ Data e Hora DATE TIME DATETIME YEAR TIMESTAMP

■ String CHAR VARCHAR BINARY VARBINARY BLOB TEXT ENUM SET

5Carlos Nunes

Tipos de dados NuméricosTipo Valor

mínimoValor

Máximo

0 255

SMALLINT -32768 32767

0 65535

MEDIUMINT -8388608 8388607

0 16777215

INT -2147483648 2147483647

0 4294967295

BIGINT -9,22E+018 9,22E+018

0 1,84E+019

Tipo Tamanho

TINYINT 1 byte

SMALLINT 2 bytes

MEDIUMINT 3 bytes

INT, INTEGER 4 bytes

BIGINT 8 bytes

FLOAT(p) 4 bytes if 0 <= p <= 24, 8 bytes if 25 <= p <= 53

FLOAT 4 bytes

DOUBLE [PRECISION], REAL

8 bytes

DECIMAL(M,D), NUMERIC(M,D)

Variável

6Carlos Nunes

Tipo de dados Data e HoraTipo Exemplo

DATETIME 0000-00-00 00:00:00'

DATE 0000-00-00'

TIMESTAMP (4.1 and up) 0000-00-00 00:00:00'

TIMESTAMP (before 4.1) 00000000000000'

TIME 00:00:00'

YEAR 0000'

Tipo Tamanho

DATE 3 bytes

TIME 3 bytes

DATETIME 8 bytes

TIMESTAMP 4 bytes

YEAR 1 byte

7Carlos Nunes

Tipo de dados StringTipo Tamanho

CHAR(M) M × w bytes, 0 <= M <= 255, where w is the number of bytes required for the maximum-length character in the character set

BINARY(M) M bytes, 0 <= M <= 255

VARCHAR(M), VARBINARY(M)

L + 1 bytes, 0 <= L <= 255

TINYBLOB, TINYTEXT L + 1 bytes, where L < 28

BLOB, TEXT L + 2 bytes, where L < 216

MEDIUMBLOB, MEDIUMTEXT

L + 3 bytes, where L < 224

LONGBLOB, LONGTEXT

L + 4 bytes, where L < 232

ENUM('value1','value2',...)

1 or 2 bytes, depending on the number of enumeration values (65,535 values maximum)

SET('value1','value2',...) 1, 2, 3, 4, or 8 bytes, depending on the number of set members (64 members maximum)

8Carlos Nunes

Gestão de tabelas – Alterar tabelas

ALTER TABLE tabela …

De seguida deve ser colocada escolhido um dos seguintes comandos:

Exemplos:ALTER TABLE cliente ADD idade INT UNSIGNED;ALTER TABLE cliente RENAME clientes;

9Carlos Nunes

Outros comandos para gestão de tabelas

■ Apagar tabelaDROP TABLE tabela;

■ Listar tabelasSHOW TABLES;

■ Mostrar descrição da tabelaDESCRIBE tabela;

10Carlos Nunes

Gestão de dados – Inserir dados

INSERT INTO tabela (campo1, campo2, ...) VALUES (valor1, valor2, ...);

Exemplos:

INSERT INTO cliente (nome, morada, telefone, estado, ordenado)VALUES ('Zé','Rua das flores','912345678','solteiro',800.50);

INSERT INTO cliente (nome, ordenado)VALUES ('Manel',850),('Xico',700),('Toino',850);

11Carlos Nunes

Alterar registos em tabela

UPDATE tabela SET campo1="novoValor"WHERE condição;

Exemplos:UPDATE cliente SET morada="Rua das camélias"WHERE cod_cliente=3;

UPDATE cliente SET ordenado="700"WHERE estado='casado';

12Carlos Nunes

Apagar registos em tabela

DELETE FROM tabela WHERE condição;

Exemplos:DELETE FROM cliente WHERE cod_cliente > 3;

13Carlos Nunes

Estrutura de uma consulta simples

SELECT campo(s)FROM tabela(s)

14Carlos Nunes

Cláusula WHERE

■ Utilizada para especificar condições para os resultados a apresentar.

Exemplo que mostra os tripulantes do barco 3:SELECT *FROM TripulanteWHERE barco=3;

15Carlos Nunes

Expressões para a cláusula WHERE

Existem várias expressões que são utilizadas para filtrar dados numa cláusula WHERE

■ Comparar expressões (=, >, <, >=, <=, !) Ex: WHERE matricula != 3

■ Valores dentro de intervalo (BETWEEN) Ex:WHERE matricula BETWEEN 2 AND 3

■ Valores dentro de um conjunto (IN) Ex: WHERE matricula IN (2,4,7)

■ Strings com determinadas características (LIKE) Ex (segunda letra=a): WHERE nome LIKE “_a%”

16Carlos Nunes

Expressões para a cláusula WHERE

Dentro da cláusula WHERE, também podemos utilizar os seguintes operadores:

■ NOT (negação) Ex: WHERE nome NOT LIKE “_a%”

■ AND (e) Ex: WHERE matricula > 3 AND nome LIKE “a%”

■ OR (ou) Ex: WHERE matricula > 3 OR nome LIKE “a%”

Nota: Deverá ter-se em consideração a prioridade dos operadores

17Carlos Nunes

Cláusula HAVING

Idêntica à cláusula WHERE, mas com a vantagem de se poder usar funções como condição.

■ A seguinte consulta devolve a quantidade de funcionários em cada barco: SELECT count(cod_funcionario), barco FROM tripulante

■ Para obtermos apenas os barcos com mais de 3 funcionários, acrescentamos: HAVING count(cod_funcionario)>3

18Carlos Nunes

UNIONÉ possível unir resultados de várias consultas usando a expressão UNION.

■ As condições para podermos unir consultas são: Possuírem a mesma quantidade de campos Os campos seleccionados terem o mesmo tipo de

dados

Exemplo: SELECT campos FROM tabela1 UNION ALL SELECT campos FROM tabela2

Nota: A expressão ALL é utilizada quando pretendemos mostrar dados que existam em duplicados nas consultas

19Carlos Nunes

JOIN

JOIN serve para juntar tabelas (lado a lado) através de um campo comum

■ Divide-se em dois tipos: Outer JOIN Inner JOIN

20Carlos Nunes

Inner Join

Para utilizar este tipo de JOIN, basta igualar os campos comuns na cláusula WHERE

Exemplo que mostra os tripulantes e os seus respectivos barcos:

SELECT cod_funcionario, tripulante.nome, barco, matricula, barco.nome

FROM tripulante, barco WHERE barco=matricula

21Carlos Nunes

Inner Join

SELECT barco.nomeFROM tripulante, barcoWHERE cod_funcionario=3 AND barco=matricula;

22Carlos Nunes

Outer JOIN

Para fazer um outer JOIN, temos de o declarar na cláusula FROM■ Existem dois tipos de outer JOIN:

LEFT JOIN – Ignora os campos inexistentes na tabela da direita

RIGHT JOIN - Ignora os campos inexistentes na tabela da esquerda

FROM tripulante LEFT JOIN barco ON barco=matricula

FROM tripulante RIGHT JOIN barco ON barco=matricula

23Carlos Nunes

Cláusulas Existentes

■ SELECT campos■ FROM tabelas■ WHERE condições■ HAVING condições■ GROUP BY campos■ ORDER BY campos■ LIMIT valores

24Carlos Nunes

Cláusulas SELECT e FROM

■ Mostrar os registos de todos os campos da tabela tripulante: SELECT * FROM tripulante

■ Mostrar o nome de todos os tripulantes: SELECT nome FROM tripulante

25Carlos Nunes

Funções, Alias e DISTINCT

■ Funções que podem ser usadas em SELECT são: AVG – média SUM – soma MIN – valor mínimo MAX – valor máximo COUNT – contar

■ DISTINCT é utilizado para não mostrar valores repetidos (EX: SELECT DISTINCT barco)

■ Um alias é utilizado para especificar um nome diferente daquele definido no campo (usando AS)

Exemplo para mostrar quantidade de tripulantes:

SELECT COUNT(nome) AS 'Nº de tripulantes'FROM tripulante

26Carlos Nunes

Cláusula LIMIT

Utilizada para definir a quantidade de registos a mostrar.

■ A expressão pode ser definida com um ou dois parâmetros Dois parâmetros (LIMIT inicio, numeroRegistos)

➔ Ex: LIMIT 3, 4 Um parâmetro (LIMIT numeroRegistos)

➔ Ex: LIMIT 4

27Carlos Nunes

Cláusula ORDER BYUtilizada para definir a ordem em que os resultados são mostrados

■ É possível ordenar resultados de forma ascendente ou descendente (usando vários campos)

■ Ordenação descendente ORDER BY DESC nome

■ Ordenar através de dois campos ORDER BY nome, DESC peso

Ordenar registos pelo nome do barco: SELECT * FROM barco ORDER BY nome

28Carlos Nunes

Cláusula GROUP BY

GROUP BY é utilizada para agrupar informação com o mesmo conteúdo.

A seguinte consulta devolve a quantidade de funcionários:

SELECT count(cod_funcionario) FROM tripulante

Se adicionarmos a cláusula GROUP BY,obtemos quantidade de funcionários em cada barco:

GROUP BY barco

29Carlos Nunes

Cláusula WHERE

■ Utilizada para especificar condições para os resultados a apresentar.

Exemplo que mostra os tripulantes do barco 3:SELECT *FROM TripulanteWHERE barco=3;