====== MySQL ======

USE POSTGRES!


===== UTF8 =====

**i18n**

  character-set-server=utf8 
  default-collation=utf8_unicode_ci 


===== Users =====

  mysqladmin -u root password 'novasenha'

<code sql>
GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%' IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';
GRANT USAGE ON *.* TO 'dummy'@'localhost';


create database somedb;
grant all privileges on somedb.* to someusr@"localhost" identified by 'passwrd';
</code>

===== Backup =====

Backups sao legais.

==== All ====

  * -A -> tudo
  * -d -> só o esquema, sem dados

  $ mysqldump -u user --password=**** -A > alldump.sql

  $ mysqldump -u user --password=**** --databases Banco1 Banco2 > somedump.sql

  $ mysqldump -h mysql.com -u user -p > somedump.sql

  $ mysql --verbose --user=XXXXXXXX --password=XXXXXXXX DB_NAME < /PATH/TO/DUMPFILE.SQL

==== Duplicar tabelas ====

  CREATE DATABASE db2;
  mysqldump -u root --password=pass db1 | mysql -u root --password=pass db2


==== Duplicate issues ====

<code sql>
CREATE TEMPORARY TABLE 
bad_temp(id INT,name VARCHAR(20))
TYPE=HEAP;

INSERT INTO bad_temp(id,name) SELECT DISTINCT id,name FROM bad_table;
</code>

em uma linha:

<code sql>
CREATE TEMPORARY TABLE bad_temp AS SELECT DISTINCT * FROM bad_table
</code>

ids distintas

<code sql>
CREATE TEMPORARY TABLE 
bad_temp2(id VARCHAR(10), name VARCHAR(20))
TYPE=HEAP;

INSERT INTO bad_temp2(name) SELECT DISTINCT name FROM bad_table2;

DELETE FROM bad_table2;

INSERT INTO bad_table2(id,name) SELECT id,name FROM bad_temp2;
</code>




===== query mais bonita do mundo =====

<code sql>
SELECT inscritos.Id, nome, rg, cpf, endereco, numero, salario, rendafam, percapta,
COUNT(family.nome_fam) AS conta FROM `pmmsone`.`inscritos`
LEFT JOIN family ON family.id_inscri_fam = inscritos.Id GROUP by inscritos.Id ;
</code>

<code sql>
 SELECT inscritos.Id, nome, rg, cpf, endereco, numero, salario, rendafam, percapta, COUNT( family.nome_fam ) AS conta
FROM `pmmsone`.`inscritos`
LEFT JOIN family ON family.id_inscri_fam = inscritos.Id
WHERE idade_fam <17
AND parentesco_fam LIKE "%FIL%"
GROUP BY inscritos.Id 
</code>

====== source(s) ======

[[http://www.databasejournal.com/features/mysql/article.php/10897_2201621_3]]

[[http://www.dicas-l.com.br/dicas-l/20040414.php]]

[[http://www.criarweb.com/artigos/161.php]]

[[http://www.vivaolinux.com.br/dicas/verDica.php?codigo=56]]


USE POSTGRES!