MySQL01 > MySQL02 > MySQL03 > MySQL04

MySQL01

Neste módulo vamos ver:

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:

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.

bthome.gifTopo

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/"
  1. 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 de log;
  2. A opção --basedir define onde ficam os binários;
  3. A opção --datadir define onde vão ficar os data files;
  4. O ficheiro de configuração da BD (my.cnf ou my.ini para Windows) deve ficar armazenado na diretoria --basedir ou --datadir;
  5. 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;
  6. A opção --explicit_defaults_for_timestamp é usada em Windows e evita um warning relativo ao timestamp do servidor;
  7. 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%
#! /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

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. 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

bthome.gifTopo

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"

bthome.gifTopo

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';

bthome.gifTopo

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 utf8
  default collate utf8_general_ci;

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

create database winestore;

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;

bthome.gifTopo

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');

Para eliminar o utilizador winestore execute o comando abaixo:

drop user 'teste'@'localhost';

bthome.gifTopo

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:

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;

bthome.gifTopo

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.

O script para instalar as tabelas pode ser descarregado aqui.

bthome.gifTopo

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;

bthome.gifTopo

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.

bthome.gifTopo

Tipos de dados para as colunas

O SGBDR MySQL suporta os seguintes tipos de colunas:

O MySQL suporta ainda os seguintes tipos complexos:

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;

bthome.gifTopo

Modificadores para as colunas

As colunas podem receber os seguintes modificadores:

bthome.gifTopo

UNIQUE KEY, PRIMARY KEY e NN

Uma UNIQUE KEY tem as seguintes características:

Uma PRIMARY KEY tem as seguintes características:

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.

bthome.gifTopo


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