MySQL Queries

-- 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