Handy Cheat-Sheet of MySQL Commands
From your login shell...
Creating a Database
# mysqladmin create [databasename]
Example: # mysqladmin create mydatabase [Enter]
Dropping (Removing) a Database
# mysqladmin drop [databasename]
Example: # mysqladmin drop mydatabase [Enter]
Populating an Existing Database from a *.sql File
# mysql [databasename] < [databasedumpfile.sql]
Example: # mysql mydatabase < mydatabase.sql [Enter]
Dumping Database Structure and Data to a *.sql file
mysqldump --opt [database] > [databasefilename.sql]
Example: mysqldump --opt techmanual > techmanual.sql;
From within the MySQL interface...
Starting MySQL from the Command Line
# mysql
Example: mysql [Enter]
You will be welcomed with the following message:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is ## to server version: #.##.##
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
The prompt changes to "mysql>", which will be shown in each
example below.
Seeing What Databases are Available
mysql> show databases;
Example: >mysql show databases; [Enter]
(be sure to use the semi-colon to terminate the command)
Telling MySQL to Use a Specific Database
mysql> use [databasename];
Example: mysql> use mydatabase; [Enter]
Seeing What Tables are Available Within a Database
mysql> show tables;
Example: mysql> show tables; [Enter]
Looking at the Data in a Particular Table
mysql> select * from [tablename];
Example: mysql> select * from lastname; [Enter]
Adding a Database User with Password
mysql> grant all privileges on [databasename].* to [dbusername]@localhost
identified by '[dbpassword]';
Example: grant all privileges on mydatabase.* to joeuser@localhost
identified by 'supersecretpasswd';
Removing a Database User
mysql> DELETE FROM mysql.user WHERE user='username' and host='localhost';
FLUSH PRIVILEGES;
Example: DELETE FROM mysql.user WHERE user='techgeek' and host='localhost';
FLUSH PRIVILEGES;
Performing Search-and-Replace Actions on a Table
mysql> update DatabaseName set TableName = REPLACE(TableName,"[String to Search For]","[String Replacement]");
Example: mysql> update roesublist set Grades = REPLACE(Grades,"Grades: Any","K,1,2,3,4,5,6,7,8,9,10,11,12");