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");