get invalid email

SELECT * FROM contact WHERE email NOT REGEXP '^[a-zA-Z0-9][a-zA-Z0-9._-]*[a-zA-Z0-9]@[a-zA-Z0-9][a-zA-Z0-9._-]*[a-zA-Z0-9]\.[a-zA-Z]{2,4}$'

solve: Got a packet bigger than 'max_allowed_packet' bytes

solve import problem: Got a packet bigger than 'max_allowed_packet' bytes
set global max_allowed_packet=1000000000; 
set global net_buffer_length=1000000; 

get database's size

select table_schema "Database Name", round(sum(data_length + index_length) / 1024 / 1024, 1) "Database Size (MB)" 
from information_schema.TABLES group by table_schema;

dumping mysql store procedures, functions and triggers

mysqldump -u user -p -h localhost your_database --routines --no-create-info --no-data --no-create-db --skip-opt > routines.sql

sql sintax samples

select count (FunNome)as Funcionario ,CarCod
from Funcionario,Cargo
where FunCod = FunCod and CarCod = FunCarCod and CarNome = 'Analista de Sistemas'
group by FunCarCod,CarCod

select count (FunNome) as Funcionario,CarCod, CarNome
from Funcionario,Cargo
where FunCod = FunCod and CarCod = FunCarCod
group by FunCarCod,CarCod, CarNome

select * from Eventos
where EveNome is null

select Nome from eventos
select EveNome "Nome", avg (EfuValor) 'media'
from Eventos_Funcionario, Eventos where  EfuEveCod = EveCod
group by EveNome mediafunc where media >1000

select min (EfuValor) 'Menor' , max (EfuValor)'Maior'
from Eventos_Funcionario

select FunNome, CarNome, DepNome
from Funcionario,Cargo, Departamento
where FunCarCod = CarCod and FdeFunCod = FunCod
and DepCod = FdeDepCod

select FunNome, sum (EfuValor)
from Eventos_Funcionario, Funcionario
where FunCod = EfuFunCod
group by FunNome

insert into Departamento
(DepCod,DepNome)
values (1,'Aline_Mara')

delete from Cargo
where CarCod not in
(select FunCarCod from Funcionario

update Eventos_Funcionario 
set EfuValor = (EfuValor+EfuValor/100*5)
where FeuFunCod in (select FunCod from Funcionario where FunCod = 2) 

----------------------------------------------------------------------------------
-- 1 --
select count(FunCarCod) as quantidade 
from funcionario, cargo 
where FunCarCod = CarCod and CarNome = 'Analista de Sistemas'

-- 2 --
select CarNome, count(FunCarCod) 
from funcionario, cargo
where FunCarCod = CarCod 
group by CarNome

-- 3 --
select * 
from eventos 
where EveNome is null

-- 4 --
select EveNome, avg(EfuValor) 
from eventos, eventos_funcionario 
where EveCod = EfuEveCod 
group by EveNome 
having avg(EfuValor)>1200 

-- 5 --
select min(EfuValor), max(EfuValor) 
from eventos_funcionario

-- 6 --
select DepNome, FunNome, CarNome 
from departamento, funcionario, cargo, funcionario_departamento 
where FunCarCod = CarCod and FdeDepCod = DepCod and FdeFunCod = FunCod

-- 7 --
select sum(EfuValor) 
from eventos_funcionario 
group by EfuFunCod

-- 8 --
insert into departamento (DepNome) 
values ('teste')
	
-- 9 --
delete from cargo 
where CarCod not in 
(select FunCarCod from funcionario)

-- 10 --
update eventos_funcionario 
set EfuValor = EfuValor * 1.05 
where EfuFunCod = 2

----------------------------------------------------------------------------------

-- ALTERAR O VALOR PARA ACRESCENTAR 20%
select debvalor * 1.2 from debito


-- VISUALIZA A COLUNA DEBVALOR + 20% E A COLUNA DEBVALOR
select debvalor * 1.2,debvalor from debito

-- COLOCA NOME NA TABELA DEBVALOR COMO REAJUSTE, “RENAME”
select debvalor as reajuste from debito


-- NOME COMPOSTO USA UNDERLINE
select debvalor as debito_do_aluno from debito

-- LISTA SOMENTE ALUNOS QUE COMEÇAM COM A LETRA M -
%R - terminam com a letra R
R% - iniciam com a letra R
%R% - contêm a letra R
select alunome from aluno where alunome like 'M%'


-- LISTA ALUNOS QUE COMEÇAM E QUE TERMINAM COM A LETRA A
select alunome from aluno where alunome like '%a' and alunome like 'a%'

-- LISTA ALUNOS QUE COMEÇAM E QUE TERMINAM COM A LETRA A OU QUE COMEÇAM COM A LETRA R
select alunome from aluno where alunome like '%a' and alunome like 'a%' or alunome like 'r%'


-- LISTA ALUNO EM ORDEM ALFABÉTICA
select alunome from aluno order by alunome


-- LISTA ALUNO EM ORDEM ALFABÉTICA
select alunome from aluno order by alunome asc


-- LISTA ALUNO EM ORDEM DECRESCENTE,”ASCENDENTE” E “DESCENDENTE”
select alunome from aluno order by alunome desc


-- LISTA O CÓDIGO DOS ALUNOS EM ORDEM
select alunome from aluno order by alucod


-- LISTA ALUNOS QUE SOMENTE FAZEM SISTEMAS EM ORDEM ALFABÉTICA
select alunome from aluno,curso,curso_aluno where alucod = calalucod and curcod = calcurcod and curnome = 'sistemas' order by alunome


-- LISTA ALUNOS E SEUS RESPECTIVOS CURSOS, ALUNOS EM ORDEM ALFABÉTICA
select alunome, curnome from aluno,curso,curso_aluno where alucod = calalucod and curcod = calcurcod order by alunome

-- LISTA TUDO, SEM ALL LISTA APENAS O QUE É COMUM
select alucod from aluno
union all
select calalucod from curso_aluno

select alucod from aluno
intersect select calalucod from curso_aluno

select alucod from aluno
except
select calalucod from curso_aluno

select debvalor from debito where debvalor between 100 and 150
ou select debvalor from devito where debvalor >= 100 and debvalor <= 150


-- AVG (IGUAL MÉDIA DO EXCEL)
select avg (debvalor) as media from debito


-- MIN (MENOR VALOR) 
select min (debvalor) as mínimo from debito


--MAX (MAIOR VALOR) 
select Max (debvalor) as máximo from debito


-- SUM (SOMA)
select sum (debvalor) as soma from debito


-- COUNT (QUANTOS NOMES NA TABELA) 
select count (alunome) as quantidade from aluno

-- MOSTRAR QUANTIDADE DE ALUNOS QUE FAZEM SISTEMAS
select count (alunome)
from aluno,curso,curso_aluno
where alucod = calalucod and calcurcod = curcod and curnome = 'sistemas'


-- MOSTRAR QUANTIDADE DE ALUNOS QUE FAZEM SISTEMAS E AGRUPADOS TAMBÉM PELO CURSO -
RESULTADO: 3 / SISTEMAS
select count (alunome), curnome 
from aluno,curso,curso_aluno
where alucod = calalucod and calcurcod = curcod and curnome = 'sistemas'
group by curnome


-- MOSTRAR QUANTIDADE DE ALUNOS QUE FAZEM SISTEMAS, AGRUPADOS TB PELO CURSO E CÓDIGO DO CURSO -
RESULTADO: 3 / SISTEMAS / 1
select count (alunome), curnome, curcod
from aluno,curso,curso_aluno
where alucod = calalucod and calcurcod = curcod and curnome = 'sistemas'
group by curnome, curcod

-- LISTA CURSOS EM ORDEM ALFABÉTICA, DEPOIS ALUNOS EM ORDEM ALFABÉTICA
select alunome, curnome from aluno, curso, curso_aluno
where alucod = calalucod and curcod = calcurcod
order by curnome, alunome


-- HAVING usa somente se tiver função agregada (USA SEMPRE COM GROUP BY)


-- LISTAR OS NOMES DE TODOS OS CURSOS QUE TEM MAIS DE DOIS ALUNOS MATRICULADOS
select curnome from curso, aluno, curso_aluno
where alucod = calalucod and curcod = calcurcod
group by curnome
having count (alunome) > 2

-- NULL
- (EXIBIR CÓDIGOS DE ALUNOS QUE SÃO NULOS)
select  alucod from aluno
where alunome is null


-- IS NOT NULL
-
(EXIBIR CÓDIGOS DE ALUNOS QUE NÃO SÃO NULOS)
select alucod from aluno
where alunome is not null

-- IN / NOT IN (SUBSTITUI INTERSECT / EXCEPT)

select alunome from aluno
where alucod in (select calalucod from curso_aluno) -- {RELACIONAMENTO}
(ALUNOS QUE ESTÃO ASSOCIADOS EM ALGUM CURSO)

select alunome from aluno
where alucod in not (select calalucod from curso_aluno) -- {RELACIONAMENTO}
(NÃO ESTÁ RELACIONADOS EM NENHUM CURSO)

select debcalalucod,debvalor from debito where debvalor > some (select debvalor from debito where debcalalucod = 1)

select debcalalucod,debvalor from debito where debvalor > all (select debvalor from debito where debcalalucod = 3)

select debcalalucod,debvalor from debito where debvalor > all (select debvalor from debito where debcalalucod = 1)

-- O ALUNO QUE MAIS PAGOU

select debcalalucod from debito group by debcalalucod having avg (debvalor) > all (select avg (debvalor) from debito)

-- LISTAR ALUNOS QUE TEM SOMENTE UM DÉBITO

select alunome from debito, aluno, curso, curso_aluno where alucod = calalucod and curcod = calcurcod and calalucod = debcalalucod and calcurcod = debcalcurcod group by alunome having count (alunome) = 1

-- RELAÇÕES DERIVADAS
select media from (select avg (debvalor) from debito) as result (media) where media > 150

select media, matricula from (select avg (debvalor), debcalalucod from debito group by debcalalucod) as result (media, matricula) where media > 150

backup all mysql database

mysqldump -u username -ppassword –all-databases > dump.sql

count domain users using sql

from: http://www.mendable.com/sql-trick-where-are-your-users-from/
SELECT COUNT(*) AS Total, SUBSTRING_INDEX(email, '@', -1) AS Domain FROM users 
GROUP BY SUBSTRING_INDEX(email, '@', -1) ORDER BY COUNT(*) DESC LIMIT 10;

autosimulado queries

-- mostra o número de usuários novos criados dia a dia
select date_format(created_at,'%d/%m/%y') as data, count(id) as total from leonardo_supertestes.users group by date_format(created_at,'%m-%d') order by date_format(created_at,'%m-%d');

-- mostra o número de testes feitos dia a dia
select date_format(created_at,'%d/%m/%y') as data, count(rate) as total from leonardo_supertestes.results group by date_format(created_at,'%m-%d') order by date_format(created_at,'%m-%d');

-- conta o número total de usuários
select count(id) from leonardo_supertestes.users;

-- conta o número total de testes feitos
select sum(hits) from leonardo_supertestes.exams;

mysql dump

import/export a database via terminal
Export
mysqldump -u username -p -h mysqlhostname databasename > databasedump.sql

Import
mysql -u username -p -h mysqlhostname databasename < databasedump.sql

stop/start mysql via terminal

sudo launchctl unload -w /Library/LaunchDaemons/com.mysql.mysqld.plist
sudo launchctl load -w /Library/LaunchDaemons/com.mysql.mysqld.plis

php script for mysql backup

<?php

function backup_tables($host,$user,$pass,$name,$tables = '*'){
	
	$link = mysql_connect($host,$user,$pass);
	mysql_select_db($name,$link);
	
	//get all of the tables
	if($tables == '*')
	{
		$tables = array();
		$result = mysql_query('SHOW TABLES');
		while($row = mysql_fetch_row($result))
		{
			$tables[] = $row[0];
		}
	}
	else
	{
		$tables = is_array($tables) ? $tables : explode(',',$tables);
	}
	
	//cycle through
	foreach($tables as $table)
	{
		$result = mysql_query('SELECT * FROM '.$table);
		$num_fields = mysql_num_fields($result);
		for ($i = 0; $i < $num_fields; $i++) 
		{
			$return.= 'DROP TABLE '.$table.';';
			
			$row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
			$return.= "\n\n".$row2[1].";\n\n";
			
			while($row = mysql_fetch_row($result))
			{
				$return.= 'INSERT INTO '.$table.' VALUES(';
				for($j=0; $j<$num_fields; $j++) 
				{
					$row[$j] = addslashes($row[$j]);
					$row[$j] = ereg_replace("\n","\\n",$row[$j]);
					if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
					if ($j<($num_fields-1)) { $return.= ','; }
				}
				$return.= ");\n";
			}
		}
		$return.="\n\n\n";
	}
	
	//save file
	$handle = fopen('db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');
	fwrite($handle,$return);
	fclose($handle);
}

backup_tables('localhost','username','password','blog');

?>

Drop all tables in a MySQL Database

run in shell:
echo DROP TABLE `echo 'SHOW TABLES;' | mysql -u USER -p DATABASE | tail -n +2 | sed -e 's/$/,/'` | sed -e 's/,$/;/'

mysql one line all database backup

mysqldump --al-databases -u -p > backup.sql

resets autoincrement (mysql)

ALTER TABLE tablename AUTO_INCREMENT = 1
Displaying all 14 Code