I am using MySQL 5.1. As a rookie, here are some basic command I feel useful to remember.
Some basic mysql commands
Use mysql client to access the database.
% myslq -u database-user -p
Show databases
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mytest | | mysql | | phpmyadmin | +--------------------+ 4 rows in set (0.00 sec)
Change to a database
mysql> use mytest;
Show tables
mysql> show tables;
Show a table structure
mysql> describe table-name;
List procedures
mysql> select ROUTINE_NAME from information_schema.ROUTINES;
Show a view or procedure's definition
mysql> SHOW CREATE VIEW view-name;
mysql> SHOW CREATE PROCEDURE procedure-name;
or
mysql> select ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME='procedure-name';
Show errors or warnings of just executed command
mysql> SHOW WARNINGS;
mysql> SHOW ERRORS;
Kill a sql process
To kill a heavy sql process: find the id of that process and kill it
mysql> SHOW FULL PROCESSLIST;
mysql> KILL theprocessid;
Some useful SQL commands
Find Duplicates of a field set
mysql> select *, count(*) as count from table-name group by field1, field2, ... having count(*) >1;