MySQL Common Commands List
Here are the MySQL commands that we use often and are very useful.
Log in to MySQL. If you connect to a remote database, you need to specify the hostname with the -h parameter.
mysql -h hostname -u root -p
Create a database
mysql> create database [databasename];
List all databases
mysql> show databases;
Switch to a database.
mysql> use [db name];
Display all tables of a database.
mysql> show tables;
View the field format of the data table.
mysql> describe [table name];
Delete a database.
mysql> drop database [database name];
Delete a data table.
mysql> drop table [table name];
Display all data in one data table.
mysql> SELECT * FROM [table name];
Returns information about each column of the specified data table.
mysql> show columns from [table name];
Filter and display some selected rows that include “ddos” value.
mysql> SELECT * FROM [table name] WHERE [field name] = “ddos”;
Display all records containing the name “ddos” and phone number “988485”.
mysql> SELECT * FROM [table name] WHERE name = “ddos” AND phone_number = ‘988485’;
Display all records that do not contain the name “ddos” and the phone number “988485”, sorted by the phone_number field.
mysql> SELECT * FROM [table name] WHERE name != “ddos” AND phone_number = ‘988485’ order by phone_number;
Display selected records in ascending or descending order.
mysql> SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;
Returns the number of rows.
mysql> SELECT COUNT(*) FROM [table name];
Delete columns.
mysql> alter table [table name] drop column ;
Add columns to db.
mysql> alter table [table name] add column [new column name] varchar (20);
Change the column name.
mysql> alter table [table name] change [old column name] [new column name] varchar (50);
Add unique columns.
mysql> alter table [table name] add unique ();
Import a CSV file into a table.
mysql> LOAD DATA INFILE ‘/tmp/filename.csv’ replace INTO TABLE [table name] FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ (field1,field2,field3);
Export all databases to a sql file.
mysqldump -u root -ppassword –opt >/tmp/alldatabases.sql
Export a database.
mysqldump -u username -ppassword –databases databasename >/tmp/databasename.sql
Export a table from a database
mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql
Restore database (data table) from sql file.
mysql -u username -ppassword databasename < /tmp/databasename.sql
Create a new user.
mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user (Host,User,Password) VALUES(‘%’,’username’,PASSWORD(‘password’));
mysql> flush privileges;
Change the user password from the Unix command line.
mysqladmin -u username -h hostname -p password ‘new-password’
Change user password from mysql command line
# /etc/init.d/mysql stop
# mysqld_safe –skip-grant-tables &
# mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD(“newrootpassword”) where User=’root’;
mysql> flush privileges;
mysql> quit
# /etc/init.d/mysql stop
# /etc/init.d/mysql start
Update database permissions / privileges.
mysql> flush privileges;
