Common MySQL Commands

Following are some MySQL commands for beginners...

1. Log in into mysql client-

mysql -u username -pPASSWORD dbName
or
mysql -u username -p


2. Show the MySql version-

SELECT VERSION();

3. Show the current user-

SELECT user();

4. Give all privileges to user (brij) on the host (localhost) -

GRANT ALL ON *.* TO 'brij'@'localhost';

5. Show running processes-

SHOW PROCESSLIST ;
or
SHOW FULL PROCESSLIST ;

6. Importing data from TXT or CSV files -

LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;

7. Find difference between two dates -

SELECT TIMESTAMPDIFF(YEAR,'1985-07-18',CURDATE());
Will return 29 (year difference), CURDATE() gives the current date.

here you can replace YEAR with MONTH and DAY also.

8. Find the next month-

SELECT MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));
It will give 3 if current month is 2 (Feb).

9. Using Regular  Expression in query-

SELECT distributorship_name FROM Distributor_Info WHERE distributorship_name REGEXP '^b';

Will return all distributorship_name starts with character 'b';

10. Check table for errors-

CHECK TABLE Distributor_Info FAST QUICK;
or
CHECK TABLE Distributor_Info;

11. Find minimum value from the list -

SELECT LEAST(4,3,8,-1,5);

Will return -1.

12. Find maximum value from the list -

SELECT GREATEST(4,3,8,-1,5);

Will return 8.

13. How to take backup or dump of a database

$ mysqldump -u username -p database_name > /home/Brij/db_backup_file_name.sql

14. How to copy one database to another

For example we want to copy mydb1 contents to mydb2.

Step 1. mysql > create database mydb2

Step 2. copy database mydb1 content into mydb2 using pipe

$ mysqldump -u username -p mydb1 | mysql -u username -p mydb2

15. Que. I want to search all table names which have column name 'votes'.

Ans.  

mysql> USE information_schema;
 
mysql> SELECT TABLE_SCHEMA , TABLE_NAME FROM COLUMNS WHERE COLUMN_NAME = 'votes';