A complete list of commonly used MySQL commands

The following are MySQL commands that we often use and are very useful. Below you see # means executing the command under the Unix command line, and seeing mysql> means that you are currently logged in to the MySQL server and the mysql command is executed on the mysql client.
Log in to MySQL. If you want to connect to a remote database, you need to specify the hostname with -h.

# [mysql dir]/bin/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 of a data table.

mysql> SELECT * FROM [table name];

Returns the column information of the specified data table.

mysql> show columns from [table name];

Use the value "whatever" to filter to display selected certain rows.

mysql> SELECT * FROM [table name] WHERE [field name] = "whatever";

Displays all records containing the name "Bob" and the phone number "3444444".

mysql> SELECT * FROM [table name] WHERE name = "Bob" AND phone_number = '3444444';

Displays all records that do not contain name "Bob" and phone number "3444444", and sort by the phone_number field.

mysql> SELECT * FROM [table name] WHERE name != "Bob" AND phone_number = '3444444' order by phone_number;

Displays all records whose name starts with the letters "bob" and whose phone number is "3444444".

mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444';

Displays the 1st to 5th records whose name starts with the letters "bob" and whose phone number is "3444444".

mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444' limit 1,5;

Find records using regular expressions. Use "regex binary" to force case sensitivity. This command finds any record starting with a.

mysql> SELECT * FROM [table name] WHERE rec RLIKE "^a";

Return unique records that are different.

mysql> SELECT DISTINCT [column name] FROM [table name];

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

Counts the sum of the specified column values.

mysql> SELECT SUM(*) FROM [table name];

Join table.

mysql> select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;

Create a new user. Log in as root. Switch to the mysql database, create a user, and refresh permissions.

# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user (Host,User,Password) VALUES('%','username',PASSWORD('password'));
mysql> flush privileges;

Change user password from unix command line.

# [mysql dir]/bin/mysqladmin -u username -h hostname.blah.org -p password 'new-password'

Change user password from mysql command line. Log in as root, set password, and update permissions.

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

Set the root password when the root password is empty.

# mysqladmin -u root password newpassword

Update root password.

# mysqladmin -u root -p oldpassword newpassword

Allow user "bob" to connect to the server from localhost with password "passwd". Log in as root and switch the mysql database. Set permissions, update permissions.

# mysql -u root -p
mysql> use mysql;
mysql> grant usage on *.* to bob@localhost identified by 'passwd';
mysql> flush privileges;

Set permissions for database db. Log in as root, switch to the mysql database, grant permissions, and update permissions.

# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('%','databasename','username','Y','Y','Y','Y','Y','N');
mysql> flush privileges;

or

mysql> grant all privileges on databasename.* to username@localhost;
mysql> flush privileges;

Update data in an existing table.

mysql> UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where [field name] = 'user';

Delete rows in the table where [field name] = ‘whatever’.

mysql> DELETE from [table name] where [field name] = 'whatever';

Update permissions/privileges on database.

mysql> flush privileges;

Delete columns.

mysql> alter table [table name] drop column [column name];

Add column to db.

mysql> alter table [table name] add column [new column name] varchar (20);

Change column names.

mysql> alter table [table name] change [old column name] [new column name] varchar (50);

Add unique column.

mysql> alter table [table name] add unique ([column name]);

Set the column value larger.

mysql> alter table [table name] modify [column name] VARCHAR(3);

Remove unique columns.

mysql> alter table [table name] drop index [colmn name];

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 sql files.

# [mysql dir]/bin/mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql

Export a database.

# [mysql dir]/bin/mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql

Export a table from a database.

# [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

Restore database (data table) from sql file.

# [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql

Create data table example 1.

mysql> CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));

Create data table example 2.

mysql> create table [table name] (personid int(50) not null auto_increment primary key,firstname varchar(35),middlename varchar(50),lastnamevarchar(50) default 'bato');