Aplicativos em PHP/Administração dos SGBDs/MySQL

Instalação

Para testes locais uma instalação prática é com o pacote Xampp

http://xampp.sf.net

Para uso em servidores onde se exige segurança idealmente instalar compilando os fontes para um controle maior das características instaladas.

Executando

Uma boa opção de administração do MySQL é o phpmyadmin, que também acompanha o Xampp.

Para administração pela linha de comando use:

mysql -h host -u user -p (o super usuário default é root)

mysql -u root (quando estiver sem senha)

Trocando a senha do usuário root

Acessar o servidor do MySQL:
mysql -u root mysql (Usuário root acessar banco mysql)

Alterar senha atual do root para novasenha:
UPDATE user SET Password=PASSWORD("novasenha") WHERE user="root";

Atualizar os procedimentos:
FLUSH PRIVILEGES;

Criando Usuários e Concedendo Privilégios

mysql --user=root mysql

GRANT ALL PRIVILEGES ON *.* TO super@localhost IDENTIFIED BY 'senha' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON *.* TO super@"%" IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

super - é um total super usuário que pode se conectar no localhost e de qualquer lugar ("%"), mas precisa usar senha

GRANT RELOAD,PROCESS ON *.* TO admin@localhost;

admin - usuário que pode se conectar no localhost sem senha.

Pode executar os comandos mysqladmin reload, mysqladmin refresh, and mysqladmin flush-* e mysqladmin processlist . Não tem nenhum privilégio relacionado aos bancos.

GRANT USAGE ON *.* TO fraco@localhost;

fraco - pode conectar somente via localhost sem senha mas sem privilégios, somente para uso.

Exemplo:

GRANT ALL PRIVILEGES ON *.* TO ribafs@localhost IDENTIFIED BY 'ribafs' WITH GRANT OPTION;

mysql -u ribafs // Dá erro de senha

mysql -u ribafs -p //Funciona após entrar a senha ribafs

Criando Usuários de Olho na Segurança

Este usuário somente conecta o MySQL via localhost.

- Abrir phpMyAdmin como super usuário
- Clicar no link Privilégios
- Clicar em Adicionar novo usuário
- Entre com o nome do usuário
- Em servidor selecione Local
- Em senha selecione Sem senha
- Abaixo em Privilégios globais selecione todas as checkbox Dados
- Caso queira marcar mais algum dos privilégios, faça-o e clique em Executar.

Pronto, este usuário somente poderá realizar conexões locais e não terá nenhum privilégio a não ser os de cadastrar os dados (nada de excluir, criar ou alterar a estrutura do banco).

Removendo Usuários

DROP USER nomeusuario;

Privilégios

REVOKE GRANT ALL ON nomebancooutabelaou*ou*.* FROM nomeusuario

- todas as tabelas

.* todos os bancos e todas as tabelas

banco.* - todas as tabelas do banco

GRANT SELECT,INSERT,UPDATE ON nomebanco.* TO nomeuser;

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON nomebanco.* TO usuario@localhost IDENTIFIED BY 'senha';

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON nomebanco.* TO usuario@dominio.com.br IDENTIFIED BY 'senha';

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON nomebanco.* TO usuario@'%' IDENTIFIED BY 'senha';

Instalando como Serviço

Instalar MySQL como serviço no Windows para trabalhar com Java (J2EE):

mysqld-nt --install --ansi --sql-mode=ANSI_QUOTES

Instalar como serviço:

bin\mysqld-nt --install mysql

Remover o serviço:

bin\mysqld --remove mysql

Remover serviço ansi:

bin\mysqld --remove --ansi

Removendo Serviços no Windows XP/NT

mysql\bin\mysqld -- remove (remove o serviço mysql) -- remove --ansi (remover o serviço ansi)

Criando Bancos e Tabelas

CREATE DATABASE db_name
[[DEFAULT] CHARACTER SET charset_name]
[[DEFAULT] COLLATE collation_name]
ALTER DATABASE db_name
[[DEFAULT] CHARACTER SET charset_name]
[[DEFAULT] COLLATE collation_name]

CREATE TABLE tbl_name (column_list)
[[DEFAULT] CHARACTER SET charset_name] [COLLATE collation_name]]
ALTER TABLE tbl_name
[[DEFAULT] CHARACTER SET charset_name] [COLLATE collation_name]
Example:
CREATE TABLE t1 ( ... ) CHARACTER SET latin1 COLLATE latin1_danish_ci;

col_name {CHAR | VARCHAR | TEXT} (col_length)
[CHARACTER SET charset_name] [COLLATE collation_name]

Exemplo:
CREATE TABLE Table1
(
column1 VARCHAR(5) CHARACTER SET latin1 COLLATE latin1_german1_ci
);

Criar Tabelas com Relacionamentos

create table produto(
	codigo int not null primary key, 
	nome varchar(50) not null unique, 
	descricao varchar(200), 
	valor real(6, 2)
) ENGINE=INNODB;

create table cliente(
	codigo int not null primary key, 
	nome varchar(50) not null, 
	email varchar(100) not null unique, 
	cpf varchar(11) not null
) ENGINE=INNODB;

create table pedido(
	numero int not null primary key auto_increment, 
	codigocliente int not null references cliente(codigo), 
	valortotal real(7,2) DEFAULT '0.00' NOT NULL
) ENGINE=INNODB;

create table item(
	numeropedido int not null references pedido(numero), 
	codigoproduto int not null references produto(codigo), 
	quantidade int not null, 
	primary key(numeropedido, codigoproduto)
) ENGINE=INNODB;

CREATE TABLE product (
	category INT NOT NULL, 
	id INT NOT NULL,
	price DECIMAL,
	PRIMARY KEY(category, id)
) ENGINE=INNODB;

CREATE TABLE product_order (
	no INT NOT NULL AUTO_INCREMENT,
	product_category INT NOT NULL,
	product_id INT NOT NULL,
	customer_id INT NOT NULL,
	PRIMARY KEY(no),
	INDEX (product_category, product_id),
	FOREIGN KEY (product_category, product_id) REFERENCES product(category, id) 
           ON UPDATE CASCADE ON DELETE RESTRICT, 
        INDEX (customer_id),
	FOREIGN KEY (customer_id) REFERENCES customer(id)
) ENGINE=INNODB;

O tipo InnoDb dá suporte à constraint Foreign Key (references).

Usando o MySQL

1) mysql -u root -p ou mysql -u root

mysql -h host -u user -p banco

Obs: Caso receba a mensagem: Can't connect to MySQL server on 'localhost'

Falta startar o MySQL

2) create database nomebanco;

3) use nomebanco;

4) create table nometabela(campos tipos...);

5) select * from nometabela;

6) show databases;

7) show tables;

8) describe nometabela;

Importação e Exportação de Dados e Estruturas

Exportando:

bin\mysqldump -u user -p passwd banco > banco.sql

Importando:

bin\mysql -u user -p password banco < banco.sql

IMPORTAR Todos os Bancos de um Script

Temos um script contendo diversos bancos então:

mysql -u root < varios_bancos.sql

Mudar Conjunto de Caracters para LATIN1

musql -u root

\C latin1

Importação e Exportação com o phpMyAdmin

Exportar todo um banco

- Abrir o phpMyAdmin e selecionar o banco

- Clicar no botão Exportar

- Basta rolar a tela e clicar em Executar abaixo

- O conteúdo será exibido na tela. Devemos então selecionar e criar um arquivo texto com o mesmo. Por convenção criamos arquivos com a extensão .sql.

- Também podemos exportar para um arquivo compactado. Basta clicar na opção "Compactado com zip ou gzip.

- Existem muitas outras opções para a exportação.

Caso queira exportar somente uma tabela o processo é o mesmo, mudando apenas que devemos selecionar apenas a tabela desejada.

Populando Tabelas após a criação

O comando LOAD DATA pode ser utilizado para popular tabelas, trazendo de arquivos:

LOAD DATA LOCAL INFILE '/path/arquivo.txt' INTO TABLE nometabela;

SELECT DATABASE();

SHOW CHARACTER SET;

Importar CSV no MySQL

$handle = fopen ('./file.csv', 'r');
		while (($data = fgetcsv($handle, 1000, ',', '"')) !== FALSE)
		{
			$query = "INSERT INTO services VALUES ('". implode("','", $data)."')";
 			$query = @mysql_query($query);
		}

LOAD DATA INFILE "./ImportData.csv"
     INTO TABLE table1
     FIELDS TERMINATED BY ","
     OPTIONALLY ENCLOSED BY """"
     LINES TERMINATED BY "\r\n";

OPTIONALLY ENCLOSED is optional.

Funções com Datas

DATE_SUB
SELECT something FROM tbl_name WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_col;
SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');

DATE_ADD
SELECT DATE_ADD('2006-05-00',INTERVAL 1 DAY);

SELECT CURDATE();

SELECT CURTIME();

DATE_FORMAT
SELECT date_format( '2006-04-30', '%d/%m/%Y' ); -- 30/04/2006
SELECT DATE_FORMAT('2003-10-03',GET_FORMAT(DATE,'EUR')); -- 03.10.2003
SELECT DATE_FORMAT('2006-06-00', '%d/%m/%Y');

SELECT NOW();

SELECT TO_DAYS('1997-10-07'); -- RETORNA DIAS

SELECT YEAR('2000-01-01');

Ativando o suporte a INNODB no MySQL do XAMPP

A versão atual do phpMyAdmin que vem no Xampp 1.62 ainda vem sem o suporte a InnoDB.

Acontece que se você baixar a versão do phpMyAdmin atual (2.10.2) esta já vem com o suporte ativado e este tutorial não se faz necessário, apenas para versões anteriores. Acredito que versões posteriores do Xampp também já venham com esse suporte ativado.

Detalhe: Apenas faça o download do phpMyAdmin (http://www.phpmyadmin.net), descompacte no diretório web e sem nenhuma configuração abra no navegador.

O tipo de tabelas padrão do MySQL (MyISAM) não oferece suporte a relacionamentos (chave estrangeira). Para isso precisamos ativar o suporte a um tipo de tabelas de terceitos (InnoDB).

Editar o arquivo my.cnf:

D:\_xampplite\mysql\bin\my.cnf

Caso apareça um arquivo "my" sem extensão clique com o botão direito, enviar para e abra num editor de texto e faça as seguintes alterações:

Comentar a linha:

#skip-innodb

Descomentar as linhas:

innodb_data_home_dir = D:/_xampplite/mysql/data/
innodb_data_file_path = ibdata1:10M:autoextend 
innodb_log_group_home_dir = D:/_xampplite/mysql/data/
innodb_log_arch_dir = D:/_xampplite/mysql/data/

set-variable = innodb_buffer_pool_size=16M 
set-variable = innodb_additional_mem_pool_size=2M 

set-variable = innodb_log_file_size=5M 
set-variable = innodb_log_buffer_size=8M 
innodb_flush_log_at_trx_commit=1 
set-variable = innodb_lock_wait_timeout=50

Pronto. Reinicie o MySQL e agora você pode criar tabelas com suporte a INNODB (consequentemente foreign key e relacionamentos).

Conversão de Funções do MySQL para o PostgreSQL no PHP

Na conversão de scripts em PHP com MySQL para PHP com PostgreSQL temos que atendar para vários detalhes: nomes das funções (ex.: mysql_connect para pg_connect), sintaxe das funções, que varia em muitas delas (veremos adiante), estrutura do banco: tipos de tabelas, tipos de dados, auto-incremento, etc. Óbvio que para converter entre dois SGBDs devemos conhecer as características de ambos. Exemplo: como vou usar um tipo de dados do MySQL no PostgreSQL, se este não tem o referido tipo? Deverei encontrar no PostgreSQL, um tipo que satisfaça os requisitos daquele do MySQL (para isso precisarei conhecer as características dos tipos de ambos, suas faixas de valores, tipos de dados, etc).

Alguns Exemplos de Conversão de Funções. Não vou me preocupar com tratamento de erros nem com outros detalhes, mostrarei apenas as funções para comparar:

Conexão ao Banco de Dados

No MySQL:

Abrir a conexão:

$con_my = mysql_connect("localhost:porta", "usuario", "senha");

Quando for usar, selecionar o banco:

mysql_select_db('nomebanco', $con_my);

No PostgreSQL:

Etapa única:

$con_pg = pg_connect("host=127.0.0.1 port=5432 dbname=banco user=usuario password=senha");

Obs.: na conexão já se seleciona o banco a ser usado.

Consultas

MySQL:

$q = mysql_query(“SELECT * FROM tabela”, $con_my);

PostgreSQL:

$q = pg_query($con_pg, “SELECT * FROM tabela”);

Obs.: Veja que a ordem dos parâmetros é invertida.

LIMIT

Aqui temos uma boa diferença entre ambos.

Quando for a forma resumida não há diferença entre ambos. Por exemplo:

SELECT * FROM tabela ORDER BY campo LIMIT 5;

Obs.: Altamente recomendado usar ORDER BY antes de LIMIT, para um retorno coerente e o campo do ORDER BY deve ser o campo chave primparia.

Quando o LIMIT trouxer os dois parâmetros então teremos diferença.

Sintaxe no PostgreSQL:

SELECT lista_de_campos FROM expressão [LIMIT { número | ALL }] [OFFSET inicio]

LIMIT ALL – mesmo que omitir LIMIT.

OFFSET inicio – orienta para que a consulta retorne somente a partir de inicio.

OFFSET 0 – mesmo que omitir OFFSET.

LIMIT 50 OFFSET 11 – Deverá trazer 50 registros do 12 até o 50, caso existam.

Exemplos:

SELECT * FROM cliente ORDER BY codigo LIMIT 2 OFFSET 0;

Irá retornar os registros do 1 e 2.

SELECT * FROM cliente ORDER BY codigo LIMIT 2 OFFSET 1;

Irá retornar os registros do 2 e 3.

SELECT * FROM cliente ORDER BY codigo LIMIT 2 OFFSET 2;

Irá retornar os registros do 3 e 4 (Se existirem).

Ou seja, o primeiro parâmetro do é a quantidade e o segundo o inicial (começando do 0).

Sintaxe no MySQL:

LIMIT [início,] linhas

Retorna o número de linhas especificado. Se o valor início for fornecido, aquelas linhas são puladas antes do dado ser retornado. A primeira linha é 0.

Exemplo:

SELECT * FROM cliente ORDER BY codigo LIMIT 3,2;

O comando acima pede apenas os registros de código 4 e 5, os dois após o 3.

SELECT * FROM cliente ORDER BY codigo LIMIT 3,1;

Aqui retornará 1 registro, de código 4, que é o próximo após o 3.

SELECT * FROM cliente ORDER BY codigo LIMIT 2 , 4;

    Aqui retornará os registros de código 3,4,5 e 6.

No MySQL o início é o primeiro parâmetro e a quantidade é o segundo.

Experimente os exemplos acima num gerenciador dos SGBDs para consolidar o conhecimento (phpmyadmin e phppgadmin).

Replicação no SGBD MySQL

Migrando .DBF para MySQL

No ótimo site Vivaolinux - http://www.vivaolinux.com.br/dicas/verDica.php?codigo=8792

phpMyAdmin

Software para administração web do MySQL, que conta com muitos recursos e tem interface simples de usar.

Site oficial - http://www.phpmyadmin.net/home_page/index.php

Download - http://www.phpmyadmin.net/home_page/downloads.php

Documentação principal - http://www.phpmyadmin.net/documentation/

FAQs - http://www.phpmyadmin.net/documentation/#faq

Doing More With phpMyAdmin: part 1 - http://www.devshed.com/c/a/PHP/Doing-More-With-phpMyAdmin-Part-1/

Doing More With phpMyAdmin: part 2 - http://www.devshed.com/c/a/PHP/Doing-More-With-phpMyAdmin-Part-2/

Conversão de Tipos

Convertendo varchar em date no MySQL

Autor: Fábio Berbert de Paula <fberbert@gmail.com> Data: 23/07/2007

Convertendo varchar em date no MySQL

Peguei uma manutenção de um sistema PHP/MySQL pra fazer e surgiu a necessidade de ordenar determinada tabela pelo campo data, porém pra minha surpresa esse campo era do tipo VARCHAR() ao invés de DATE() ou DATETIME().

A data estava armazenada na tabela no formato "dd/mm/yyyy", exemplo:

SELECT data FROM contas;

+------------+
| data       |
+------------+
| 26/11/2003 | 
| 04/12/2003 | 
| 28/11/2003 | 
| 05/12/2003 | 
| 29/12/2003 | 
+------------+

O problema é que ao mandar ordenar a tabela por data, por ser VARCHAR o resultado não funciona como o esperado:

SELECT data FROM contas ORDER BY data;

+------------+
| data       |
+------------+
| 04/12/2003 | 
| 05/12/2003 | 
| 26/11/2003 | 
| 28/11/2003 | 
| 29/12/2003 | 
+------------+

Isso acontece porque se o campo é texto, ele começa a ordenar da esquerda pra direita em ordem alfanumérica, onde 04/12 é menor que 26/11, o que está errado, visto que em data a gente precisa levar em conta, em ordem de prioridade, ano-mês-dia.

Pra resolver esse problema sem ter de mexer na tabela (o que não tinha permissão pra fazer), use a função str_to_date() do MySQL:

SELECT str_to_date(data, '%d/%m/%Y') AS data FROM contas ORDER BY data;

+------------+
| data       |
+------------+
| 2003-11-26 | 
| 2003-11-28 | 
| 2003-12-04 | 
| 2003-12-05 | 
| 2003-12-29 | 
+------------+

Hmmm, agora sim! A sintaxe da função é:

str_to_date(CAMPO, 'formato armazenado da string')

Maiores informações:

   * http://dev.mysql.com/...#function_str-to-date

Fonte: http://www.vivaolinux.com.br/dicas/verDica.php?codigo=9000

Referências

- Manual Online do MySQL 4.1 em Português - com opção de busca pelo conteúdo.

http://dev.mysql.com/doc/refman/4.1/pt/index.html

- Manual do MySQL em vários idiomas e em vários formatos para download - http://mysql.org/doc/

- Removendo Duplicidades em MySQL - http://www.dicas-l.com.br/print/20060930.html

Como o MySQL pode facilitar a sua vida

Artigo do Diego Hellas no PHPBrasil

http://phpbrasil.com/articles/article.php/id/1361

Usando o DBDesigner 4

http://www.revistaphp.com.br/print.php?id=160

Store Procedures com Transaction no MySQL

http://www.vivaolinux.com.br/artigos/impressora.php?codigo=7177

Instalação

Executando

Trocando a senha do usuário root

Criando Usuários e Concedendo Privilégios

Removendo Usuários

Instalando como Serviço

Criando Bancos e Tabelas

Usando o MySQL

Importação e Exportação de Dados e Estruturas

Mudar Conjunto de Caracters para LATIN1

Importar CSV no MySQL

Funções com Datas

Ativando o suporte a INNODB no MySQL do XAMPP

Conversão de Funções do MySQL para o PostgreSQL no PHP

Replicação no SGBD MySQL

Recriar replicação no MySQL de maneira fácil

Replicação no MySQL

Replicação com o MySQL

Migrando .DBF para MySQL

phpMyAdmin

Conversão de Tipos

Convertendo varchar em date no MySQL

Referências

Como o MySQL pode facilitar a sua vida

Usando o DBDesigner 4

Store Procedures com Transaction no MySQL