-- view hostname
select @@hostname;

-- view version
select version();

-- view engines
show engines;

-- show processes
show full processlist;

-- list databases
show databases;

-- list queries
mysqladmin -i 1 processlist

-- change database
use mysql;

-- show tables in database
show tables;

-- show table informations
show table status;

-- get table create query
SHOW CREATE TABLE tbl_name;

-- show engines
show engines;

-- show bin_log info
show variables like '%log_bin%';

-- list users
select user, host from mysql.user;

-- delete user
DROP USER 'USER_NAME'@'%';

-- set user password
--SET PASSWORD FOR 'USER_NAME'@'%' = PASSWORD('NEW_PASSWORD')
use mysql;
update user set password=PASSWORD("NEW_PASS") where User='USER_NAME';
flush privileges;

-- show users privilieges
select * from information_schema.user_privileges;

-- create DB
create database db1;
grant all on db1.* to user1@localhost identified by 'pass';

-- select 10 random row from table
SELECT column FROM table
order by rand()
limit 10;

-- update table (use join)
UPDATE table1
JOIN table2
ON table1.product = table2.a
SET table1.sku = table2.b
--where table1.product = 2138

-- view all databases
show databases;

-- view tables in database
show tables from mysql;

-- timestamp 7 days ago
date_sub(curdate(), interval 7 day);

-- format date
select to_char(time, 'YYYY-MM-DD HH24:MI') from log;

-- replace first char to upercase
UPDATE customer SET first_name = CONCAT(UPPER(LEFT(first_name, 1)), SUBSTRING(first_name, 2));

-- string concatenation
select concat('Engine: ',  ENGINE)
from information_schema.ENGINES;

-- get database total size
SELECT @@hostname "Host", sum( data_length + index_length ) / 1024 / 1024 "Size in MB"
FROM information_schema.TABLES;

-- get database size
SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Size in MB"
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY 2 desc;

-- table size
SELECT table_name AS "Tables", round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB"
FROM information_schema.TABLES
WHERE table_schema = "MY_DATABASE_NAME"
ORDER BY 2 desc;

-- get foreign keys
select table_name, CONSTRAINT_NAME
from information_schema.key_column_usage
where constraint_schema = "MY_DATABASE_NAME"
and REFERENCED_TABLE_SCHEMA is not null;

--
-- insert or update
--
-- create unique index
ALTER TABLE table1
ADD CONSTRAINT table1_uq
UNIQUE(ts, kw, mac, sn);

-- insert data
INSERT INTO table1(ts, kw, mac, sn)
VALUES ('ts01', '2013-05', '00:11:22:33:44:55', '11223344556677')
ON DUPLICATE KEY UPDATE time = CURRENT_TIMESTAMP;

-- format output
SHOW SLAVE STATUS\G;

-- convert database to UTF-8
ALTER DATABASE db1 CHARACTER SET = 'utf8'  COLLATE = 'utf8_bin';

-- convert table to UTF-8
ALTER TABLE table1 CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- rename all
CMD_SQL="mysql -h ${DB_HOST} -u ${DB_USER} -p"${DB_PASS}" -D ${DB_NAME} -sNe"
for TABLE in $(${CMD_SQL} 'show tables'); do
    ${CMD_SQL} "rename table ${DB_NAME}.${TABLE} to ${DB_NAME}.${TABLE}_org"
done