====== MySql Cheat Sheet ======
Created Monday 03 April 2017

show processlist;
kill 2;

create database hashes;
use hashes;
create table hashpass (hash varchar(100), pass varchar(100), hashtype varchar(20), index hash (hash));
load data local infile 'z:/powershell/hashcat.pot' into table hashpass fields terminated by ':' lines terminated by '\n';
load data local infile 'f:/temp/hashpass1.txt' ignore into table hashpass fields terminated by ':' lines terminated by '\n';

create index hash on hashpass (hash);
alter table hashpass add HashType varchar(20);
update hashpass set HashType = 'sha1' where length(hash) = 40;
update hashpass set HashType = 'md5' where length(hash) = 32;
select * from hashpass where length(hash) = 40;
ALTER TABLE hashpass MODIFY pass VARCHAR(50);
ALTER TABLE hashpass MODIFY hash VARCHAR(100);
load data local infile 'z:/powershell/hashcat.pot' into table hashpass fields terminated by ':' lines terminated by '\n';
update hashpass set HashType = 'sha1' where length(hash) = 40;
update hashpass set HashType = 'md5' where length(hash) = 32;
use myspace;
create table hashonly (hash varchar(50), index hash (hash));
load data local infile 'f:/temp/hashonly.txt' into table hashonly fields terminated by ':' lines terminated by '\r\n';
select hashpass.hash,hashpass.pass INTO OUTFILE 'f:/temp/hashpass.txt'
  FIELDS TERMINATED BY ':'
  LINES TERMINATED BY '\r\n' from hashpass join (myspace.hashonly) on (hashpass.hash = myspace.hashonly.hash);
select count(distinct hash) from hashpass;
INSERT IGNORE INTO `table2` SELECT * FROM `table1`;
create table emailhash (id varchar(20), email varchar(100), username varchar(50), hash varchar(100), hash1 varchar(100));
load data local infile 'z:/powershell/Myspace.com.txt' into table emailhash fields terminated by ':' enclosed by "'" lines terminated by '\r\n';
load data local infile 'e:/test.txt' into table emailhash (@dummy,email,@dummy,hash,@dummy) fields terminated by ':' enclosed by "'" lines terminated by '\r\n' (email,hash);
load data local infile 'e:/myspace.com.txt' into table emailhash fields terminated by ':' enclosed by "'" lines terminated by '\r\n' (@dummy,email,@dummy,hash,@dummy);
create index hash on emailhash (hash);
SET FOREIGN_KEY_CHECKS = 1;
SET SESSION tx_isolation='REPEATABLE-READ';
select aminno_member_email.email, member_login.password 
from aminno_member_email join member_login on aminno_member_email.pnum = member_login.pnum
INTO OUTFILE 'f:/temp/emailhash.txt'
FIELDS TERMINATED BY ':'
LINES TERMINATED BY '\r\n'; 
select table_name, table_rows from information_schema.tables where table_name = 'idhash';
GRANT ALL ON scan.* to 'nmap'@'localhost' IDENTIFIED BY 'password';
show warnings instead of errors on import
set @@sql_mode='no_engine_substitution';