Mysql Useful Commands

Login
[Shell] > mysql -h <host_ip_address> -u <username> -p
Enter password:<password>

Create
create database [databasename];

Use / Select a particular database
use [db name];

Show
show databases;
show tables;
show columns from [table name];
show variables;
show grants for user@localhost; ( eg :- show grants for test123@localhost; )
show columns from <database_name>.<table_name>; ( show columns from test_db.table1; )
show privileges;
show character set;

Describe table structure
describe [table name];

Drop
drop database [database name];
drop table [table name];

Select Commands
SELECT * FROM [table name];
SELECT * FROM [table name] WHERE [field name] = “whatever”;
SELECT * FROM [table name] WHERE name = “Bob” AND phone_number = ‘3444444′;
SELECT * FROM [table name] WHERE name != “Bob” AND phone_number = ‘3444444′ order by phone_number;
SELECT * FROM [table name] WHERE name like “Bob%” AND phone_number = ‘3444444′;
SELECT * FROM [table name] WHERE rec RLIKE “^a$”;
SELECT DISTINCT [column name] FROM [table name];
SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;
SELECT COUNT(*) FROM [table name];
SELECT SUM(*) FROM [table name];

Myql Admin
mysqladmin -u root -h <host-ip-address> -p password ‘new-password’
mysqladmin extended-status
mysqladmin status
mysqladmin variables
mysqladmin version
mysqladmin create db_name
mysqladmin drop db_name
mysqladmin flush-privileges
mysqladmin ping
mysqladmin reload
mysqladmin kill ps_id,ps_id…
mysqladmin –user=root shutdown

Alter Table
alter table [table name] drop column [column name];
alter table [table name] add column [new column name] varchar (20);
alter table [table name] change [old column name] [new column name] varchar (50);
alter table [table name] add unique ([column name]);
alter table [table name] modify [column name] VARCHAR(3);
alter table [table name] drop index [colmn name];

Grant Privileges
grant usage on *.* to bob@localhost identified by ‘passwd’;
grant all privileges on databasename.* to username@localhost;

Backup and Restore
mysqll db_name < input.sql
mysqldump db_name my_table > output.sql
mysqldump –no-data db_name my_table > dump_table_name.sql
mysqldump –add-drop-table db_name my_table > dump_table_name.sql
mysqldump –compatible=mysql40 db_name my_table > dump_table_name.sql

Load a CSV file into a table.
LOAD DATA INFILE ‘/tmp/filename.csv’ replace INTO TABLE [table name] FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ (field1,field2,field3);

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s