Useful MySQL queries

By Hilmar Meijer
On february 10, 2014
About commandline,sql, database
mysql
Most of the time I am using phpmyadmin for mysql database administration. But sometimes phpmyadmin is not available. So I find myself searching for the right queries to do certain operations. Here is a list of useful queries that might come in handy when phpmyadmin is not available and you have to use the normal mysql queries.

I am assuming your database is called `mydb` and your table is called `mytable`.

Creating a database with a UTF-8 character set
CREATE DATABASE `mydb` CHARACTER SET utf8 COLLATE utf8_general_ci;
Show all databases
SHOW DATABASES;
Dropping a database
DROP DATABASE `mydb`;
Granting localhost privileges for a user on a database
GRANT ALL ON `mydb`.* TO `username`@localhost IDENTIFIED BY 'password'
FLUSH PRIVILEGES;
Select a database for querying
USE `mydb`;
Show the tables of a selected database
SHOW TABLES;
Show column names of a table
SHOW COLUMNS FROM `mytable`;
Drop a table
DROP TABLE `mytable`;
Table sizes of a database
SELECT `mytable` AS "Tables", round(((data_length + index_length)/1024/1024),2)"Size in MB"
FROM information_schema.TABLES
WHERE table_schema = "mydb"
ORDERBY(data_length + index_length)DESC;
Export a query into a csv file. Replace username, password, mydb and your query with your own data.
mysql -u USERNAME -pPASSWORD mydb -e  "YOUR QUERY" | sed 's/\t/,/g' > /path/filename.csv
Back to blog