MySQL01

Neste módulo vamos ver:

O servidor MySQL
Trabalhando com schemas (databases) e tabelas

Terminologia

Database = Base de dados - um repositório de dados.

Table = Tabela - estrutura bidimensional que armazena dados relativos com um objecto ou actividade. Uma tabela tem colunas e linhas.

Attributes = Atributos = colunas da tabela. Todas as linhas têm os mesmos atributos.

Row = Linha = Registo - cada linha contem um dado para cada coluna (atributo).

Relational Model = Modelo relacional - modelo formal que usa tabelas, colunas e relacionamentos para para armazenar dados e criar e manter as relações entre eles.

RDBMS - SGBDR - Relational Database Management System - Sistema Gestor de Base de Dados Relacional - software que faz a gestão de dados baseado no modelo relacional.

SQL - Structured Query Language - Uma linguagem normalizada (ANSI e ISO) que permite interrogar os dados armazenados num SGBDR

Constraint = Restrição - limitações impostas sobre as tabelas, colunas e dados:

NN - Not Null;
PK - Primary Key
FK - Foreign Key
UN - Unique

Index = Índice - estrutura de dados usada para acelerar o acesso aos dados de uma tabela. Algumas restrições usam índices para a sua implementação interna.

Entity relationship Model = Modelo entidade relacionamento

D-ER = Diagrama Entidade Relacionamento

Normalized Database = Base de dados normalizada - As tabelas que constituem a base de dados (schema) seguem as regras de normalização: 1FN, 2FN e 3FN.

Topo

Instalação, arranque e paragem do MySQL

No site do MySQL encontra versões para todas as plataformas e diferentes formas de instalação. Recomendamos a instalação da versão genérica, que será a versão zip no Windows ou a tar.gz para Linux. Siga as instruções de instalação descritas no site.

Se tiver instalado a versão genérica (zip ou tar.gz) a base de dados não está inicializada, ou seja, não tem as tabelas de sistema criadas. Para a inicializar execute o comando abaixo:

C:\Util\mysql5.7.21\bin\mysqld.exe --initialize --basedir="C:/Util/mysql5.7.21" --datadir="C:/Workspace/MySQL/MySQLDataFiles" --log_syslog=0 --explicit_defaults_for_timestamp=1 --secure-file-priv="C:/Workspace/MySQL/"

A opção --initialize força a inicialização do servidor. A partir da versão 5.7 o utilizador root recebe uma password, sendo o seu valor escrito no ficheiro *.log ou *.err;
A opção --basedir define onde ficam os binários;
A opção --datadir define onde vão ficar os data files;
O ficheiro de configuração da BD (my.cnf ou my.ini para Windows) deve ficar armazenado na diretoria --basedir ou --datadir;
A opção --log_syslog é usada apenas em Windows e força o MySQL a não escrever no Registry, o que permite a sua execução sem privilégios de administração. Nas vresão 8.0.20 já não é necessário;
A opção --explicit_defaults_for_timestamp é usada em Windows e evita um warning relativo ao timestamp do servidor;
A opção --secure-file-priv define onde podem ser feitos os dumps do conteudo do servidor. Se for deixada em branco não é possivel fazer dump dos conteúdos do SGBD;

Para inicializar o serviço que disponibiliza o MySQL executamos o comando abaixo:

rem Em Windows
C:\Util\mysql5.7.21\bin\mysqld.exe    --basedir="C:/Util/mysql5.7.21" --datadir="C:/Workspace/MySQL/MySQLDataFiles" --log_syslog=0 --explicit_defaults_for_timestamp=1 --secure-file-priv="C:/Workspace/MySQL/" --user=%username%

Na versão 8.0.20 a opção não pode ser usada:

rem Em Windows
C:\Util\mysql8.0.20\bin\mysqld.exe    --basedir="C:/Util/mysql5.7.21" --datadir="C:/Workspace/MySQL/MySQLDataFiles" --explicit_defaults_for_timestamp=1 --secure-file-priv="C:/Workspace/MySQL/" --user=%username%

#! /bin/bash
#Em Linux
sudo /usr/local/mysql/bin/mysqld --user=_mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --log-error=/usr/local/mysql/data/mysqld.local.err --pid-file=/usr/local/mysql/data/mysqld.local.pid --keyring-file-data=/usr/local/mysql/keyring/keyring --early-plugin-load=keyring_file=keyring_file.so & 

ps -ef | grep mysql
sudo lsof -n -i:3306

A versão 8 requer que a password de root seja alterada antes que se possa fazer shutdown:

set MYSQLBASE=C:\Users\jcl\Desktop\UwAmp\bin\database\mysql-8.0.20
%MYSQLBASE%\bin\mysql --user=root  --password=0dlHoQ3?Y;fM --host=localhost
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'Xpto_1234';    -- version 8
mysql> set password for 'root'@'localhost' = PASSWORD('Xpto_1234'); -- previous versions
mysql> SELECT User, Host FROM mysql.user;

Para parar o serviço (ou processo) executamos o comando abaixo, que requer a password de root. Esta foi definida na inicialização e aparece no ficheiro de *.log ou *.err. Pode ser alterada à posteriori usando um comando apresentado mais à frente:

rem Em Windows
C:\Util\mysql5.7.21\bin\mysqladmin --user=root  --password=Xpto_1234  --host=localhost  shutdown

#! /bin/bash
#Em Linux
/usr/local/mysql/bin/mysqladmin --user=root  --password=Xpto_1234  --host=localhost shutdown

Para ver as ligações que estão feitas ao MySQL:

rem Em Windows
@echo off
set /a "x = 0"
:while1
    if %x% leq 50 (
        rem echo %x%
        C:\Util\mysql5.7.21\bin\mysql  --user=root  --password=Xpto_1234  --host=localhost --execute="show processlist;" --database=tldev
        set /a "x = x + 1"
        sleep 3
        goto :while1
    )
endlocal

Abaixo apresentamos o script equivalente em Linux. Este tem que ser guardados com fim de linha em formato Unix, pois caso contrário o interpretador de comandos dá erro. Se executar o comando $file nome.sh consegue verificar em que formato foi gravado.

#! /bin/bash
for i in {1..50} 
do 
	/usr/local/mysql/bin/mysql  --user=root  --password=Xpto_1234  --host=localhost --execute="show processlist;" --database=tldev
	sleep 2s
done

Topo

Interpretador de comandos MySQL

O interpretador de comandos é a componente do SGBD que recebe os pedidos do utilizador, os interpreta e manda executar. Os comandos são introduzidos num cliente SQL, por exemplo o MySQL Workbench, depois de abrir uma ligação ao SGBD usando um utilizador nele definido, por exemplo o root.

Temos dois tipos de comandos: SQL e MySQL. Os primeiros estão descritos pela norma SQL e são aceites em todos os SGBDs com algumas variações, enquanto os segundos são apenas válidos no ambiente MySQL e destinam-se a ordens especificas sobre este tipo de SGBD.

O interpretador de comandos standard funciona na linha de comandos e para abrir uma ligação usamos o comando abaixo numa consola:

rem Em Windows
C:\Util\mysql5.7.21\bin\mysql --user=root  --password=Xpto_1234 --host=localhost
# Para entrar com o utilizador winestore e ligar à sua base de dados
C:\Util\mysql5.7.21\bin\mysql --user=winestore  --password=Xpto_1234  --host=localhost --database=winestore

#Em Linux ou MAC
# Para entrar como root
/usr/local/mysql/bin/mysql  --user=root  --password=Xpto_1234  --host=localhost --database=tldev
# Para entrar com o utilizador winestore e ligar à sua base de dados
/usr/local/mysql/bin/mysql  --user=winestore  --password=Xpto_1234  --host=localhost --database=winestore

Oa comandoa abaixo são exemplos de instruções SQL que serão explicadas mais à frente neste manual:

mysql> SELECT NOW();
mysql> SELECT User, Host, Password FROM mysql.user;

O resultado do comando pode ser apresentado na vertical (uma linha para cada coluna) sendo neste caso necessário terminar com \G em vez de ;

mysql> SELECT User, Host, Password FROM mysql.user\G

Os comandos podem ser agrupados e executados todos de uma vez ("batch job" ). O processo para fazer isto depende do cliente SQL que está a usar. No cliente standard criamos um ficheiro de comandos e mandamos executar:

rem Em Windows
C:\Util\mysql5.7.21\bin\mysql --user=root  --password=Xpto_1234 --host=localhost --database=winestore --execute="nomeficheirocomandos"

Topo

SHOW

Seguem alguns exemplos de utilização do comando SHOW:

show databases;
show tables;
show table status;
show create table customer;
show open tables;
show columns from customer;
show index from customer;
show privileges;
show status;
show processlist;
show variables;
show errors;
show grants for 'admin'@'localhost';

Topo

Criar schema ou database

Um schema ou database é um espaço onde se armazenam tabelas e outros objetos. Para criar um schema é preciso abrir uma ligação usando um cliente MySQL com o utilizador root. Este utilizador tem plenos poderes sobre o SGBD.

Para criar o schema (database) winestore execute o comando abaixo, que define o charater set que será adotado pelas tabelas, assim como a sequência de ordenação. Estas duas últimas opções não são obrigatórias.

create schema winestore
  default character set utf8mb4
  default collate utf8mb4_unicode_ci;

Como alternativa pode substituir a palavra reservada schema por database. Neste comando não colocámos as cláusulas opcionais:

create database winestore2
  default character set utf8mb4
  default collate utf8mb4_unicode_ci;

O nome de um schema é constituído no máximo por 64 caracteres, não podendo conter: '\','/' e '.' Os nomes são sensíveis a letras maiúsculas e minúsculas.

Os comandos abaixo permitem remover o schema (database) winestore.

drop database winestore;

drop database if exists winestore;

Após entrar num cliente MySQL tem que escolher a base de dados (schema) com que quer trabalhar. Isto é feito seleccionando a base de dados na interface gráfica ou usando o comando USE como mostra o exemplo abaixo:

use winestore;

Topo

Criar utilizador

Abrir um cliente MySQL com o utilizador root. Este utilizador tem plenos poderes sobre o SGBD.

Para criar o utilizador teste execute o comando abaixo:

create user 'teste'@'localhost' identified by 'Xpto_1234';

Para alterar a password do utilizador winestore execute o comando abaixo:

set password for 'teste'@'localhost' = PASSWORD('Xpto_1234');
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Xpto_1234';    -- version 8

Para eliminar o utilizador winestore execute o comando abaixo:

drop user 'teste'@'localhost';

Topo

Dar privilégios a um utilizador sobre um schema (database) ou tabela

Os comandos deste subtema requerem privilégios especiais sobre o SGBD, pelo que têm que ser executados após ligação como root.

Os comandos abaixo permitem-lhe identificar os hosts (servidores) e utilizadores que estão definidos no SGBD:

select * from mysql.host;  -- versões antigas
select * from mysql.servers; -- versões mais recentes

select * from mysql.user where user in ('teste');

select * from mysql.db where user in ('teste');

select * from mysql.db where db in ('winestore');

Os comandos abaixo permitem-lhe identificar os privilégios que foram atribuidos aos utilizadores seguindo dois critérios:

Os privilégios atribuídos sobre o schema (database) e que portanto se aplicam a todas as tabelas nele contidos;
Os privilégios atribuídos específicamente sobre cada tabela;

select * from mysql.db where user in ('teste');

select * from mysql.db where db in ('winestore');

select * from mysql.tables_priv where user in ('teste');

Os comandos abaixo permitem-lhe dar e retirar privilégios de consulta sobre uma tabela específica do schema winestore:

grant select on winestore.countries to 'teste'@'localhost';

select * from mysql.tables_priv where user in ('teste');

delete from mysql.tables_priv where host='localhost' and db='winestore' and user='teste' and table_name='countries'; 

select * from mysql.tables_priv where user in ('teste');

Os comandos abaixo permitem-lhe dar e retirar privilégios de consulta sobre todas as tabelas do schema winestore:

grant select on winestore.* to 'teste'@'localhost';

select * from mysql.db where db in ('winestore') order by db;

revoke select on winestore.* from 'teste'@'localhost';

select * from mysql.db where db in ('winestore') order by db;

Os comandos abaixo permitem-lhe dar e retirar todos os privilégios possíveis sobre todas as tabelas do schema winestore:

grant all on winestore.* to 'teste'@'localhost';

select * from mysql.db where db in ('winestore') order by db;

revoke all on winestore.* from 'teste'@'localhost';

select * from mysql.db where db in ('winestore') order by db;

Topo

Grupo de tabelas Winestore

Os exemplos deste manual estão baseados no grupo de tabelas WINESTORE, que foi retirado do livro "Web Database Applications with PHP and MySQL, 2nd Edition" escrito por Hugh E. Williams, David Lane, Jakub Korab e Derryn Grabowski. Este diagrama descreve essas tabelas e respectivos relacionamentos.

Um cliente faz encomendas de vinhos (Customer, Orders, Items, Wine);
Um cliente possui um ou mais utilizadores que acedem ao site via WEB;
Um cliente possui um título (Mr., Dr., etc) e vive num país;
Uma encomenda possuí um ou mais Items;
Um item consiste numa determinada quantidade de um vinho com um determinado preço;
Um vinho tem sempre um tipo, como por exemplo "Red", "White", "Sparkling";
Um vinho é produzido por num Winery (produtor ou adega);
Um Winery pertence a uma Region (região);
Um vinho é feito por um ou mais grape_variety (tipos de uva). A ordem pela qual aparecem os tipos de uva na tabela wine_variety é importante. Por exemplo (Cabernet, Sauvignon) é diferente de (Sauvigon, Cabernet);
Um Inventory representa uma entrada de x garrafas de vinho numa determinada data, com determinado preço de compra;
Um vinho pode ter uma ou mais entradas em Inventory;

O script para instalar as tabelas pode ser descarregado aqui.

Topo

Trabalhando com schemas ("databases") e tabelas

Use Database

Após abrir sessão no MySQL tem que escolher a base de dados (schema) com que quer trabalhar. Isto é feito seleccionando a base de dados na lista do lado direito ou esquerdo (depende do cliente que está a usar) ou executando o comando USE como mostra o exemplo abaixo:

use winestore;

Topo

Tabelas

O comando abaixo permite ver o SQL que deu origem à tabela CUSTOMER:

show create table customer;

CREATE TABLE MyCustomer (
    cust_id int(5) NOT NULL,
    surname varchar(50) default NULL,
    firstname varchar(50) default NULL,
    initial char(1) default NULL,
    title_id enum ('Mr','Miss') default 'Mr',
    address varchar(50) default NULL,
    city varchar(50) default NULL,
    state varchar(20) default NULL,
    zipcode varchar(10) default NULL,
    country_id int(4) default NULL,
    phone varchar(15) default NULL,
    birth_date char(10) default NULL,
    PRIMARY KEY  (cust_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

O comando para apagar uma tabela é DROP:

drop table MyCustomer;
drop table if exists MyCustomer;

Cada coluna tem um nome, tipo (domínio de dados), dimesão, valor por omissão (default) e restrição NN.

Topo

Tipos de dados para as colunas

O SGBDR MySQL suporta os seguintes tipos de colunas:

int(n); ==> representação exacta
Atributos tiny, small e medium. Ex: tinyInt, samllInt, mediumint.
decimal(n,d) ==> virgula fixa - com erro de arredondamento;
float, double precision ==> virgula flutuante - com erro de arredondamento;
datetime;
time;
date;
timestamp ==> regista a data de criação da linha ou a data de actualização;
varchar(n) ==> cadeia de carateres com tamanho variável;
char(n) ==> cadeia de carateres com tamanho fixo;
lob ==> Large Object do tipo binário (BLOB) ou texto (TEXT);

O MySQL suporta ainda os seguintes tipos complexos:

ENUM - Enumerate - A coluna só pode receber os dados que constam da lista;
SET - conjunto

CREATE TABLE MyCustomer (
    cust_id int(5) NOT NULL,
    surname varchar(50) default NULL,
    firstname varchar(50) default NULL,
    initial char(1) default NULL,
    title_id enum ('Mr','Miss') default 'Mr',
    address varchar(50) default NULL,
    city varchar(50) default NULL,
    state varchar(20) default NULL,
    zipcode varchar(10) default NULL,
    country_id int(4) default NULL,
    phone varchar(15) default NULL,
    birth_date char(10) default NULL,
    PRIMARY KEY  (cust_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Topo

Modificadores para as colunas

As colunas podem receber os seguintes modificadores:

Not Null (NN);
Default - valor por omissão - valor que a coluna recebe quando se insere null;
Zerofill - usado nos valores numéricos - preencher com zeros à esquerda;
Unsigned - usado nos valores numéricos - assume apenas valores positivos, o que duplica o maior valor possível;
Auto_increment - usado para valores numéricos;

Topo

UNIQUE KEY, PRIMARY KEY e NN

Uma UNIQUE KEY tem as seguintes características:

Identifica univocamente todas as linhas da tabela;
É constituída por uma ou mais colunas;
Designações alternativas: Unique, Alternate key, Chave Alternativa;
Uma tabela pode ter várias keys (16 no máximo);
Pode receber NULL por linha;

Uma PRIMARY KEY tem as seguintes características:

Identifica univocamente todas as linhas da tabela;
É constituída por uma ou mais colunas;
Designações alternativas: Chave primária;
Uma tabela só pode ter uma PK;
Não pode receber NULL por linha: PK = KEY + NN;
Não é obrigatório que a tabela tenha PK mas é aconselhável;
Escolhida entre todas as KEYs disponíveis;

As UNIQUE KEYS e PRIMARY KEYS são suportadas por índices, pois estes aceleram o acesso aos dados (pesquisa). No entanto os índices prejudicam os INSERTS, UPDATES e DELETES, porque têm que ser actualizados.

O próximo exemplo mostra como criar uma PRIMARY KEY e uma KEY:

drop table MyCustomer;
CREATE TABLE MyCustomer (
    cust_id int(5) NOT NULL,
    surname varchar(50) default NULL,
    firstname varchar(50) default NULL,
    initial char(1) default NULL,
    title_id enum ('Mr','Miss') default 'Mr',
    address varchar(50) default NULL,
    city varchar(50) default NULL,
    state varchar(20) default NULL,
    zipcode varchar(10) default NULL,
    country_id int(4) default NULL,
    phone varchar(15) default NULL,
    birth_date char(10) default NULL,
    PRIMARY KEY  (cust_id),
    UNIQUE fullname (firstname, surname)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Além de índices UNIQUE é possível criar índices "NOT UNIQUE". Ver subcapítulo sobre índices.

Topo

Realizado por Turtle Learning ®. Última alteração em 2018-02-24