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]

    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 
    	grant all privileges on [databasename].* to [dbusername]@localhost
	identified by '[dbpassword]';

	Example:  grant all privileges on mydatabase.* to joeuser@localhost
	identified by 'supersecretpasswd';
  • Dumping Database Structure and Data to a *.sql file 
    	mysqldump --opt [database] > [databasefilename.sql]

	Example:  mysqldump --opt techmanual > techmanual.sql;