List of useful mysql commands

Lets start with mysql database administration commands.
1) /bin/mysql -h hostname -u root -p #To login
2) SHOW DATABASES; # List all databases
3) DROP TABLE [table name]; #To delete a table
4) To create a new user - Login as root, switch to mysql db, make the user, update privileges
mysql) USE mysql;
mysql) INSERT INTO USER (Host, User, Password) VALUES ('%', 'username', PASSWORD('Password'));
mysql) flush privileges;
5)To change a password
/bin/mysqladmin -u username -h hostname -p password 'new-password'
or
mysql> SET PASSWORD FOR 'User'@'hostname'=PASSWORD('Passwd');
mysql> flush privileges;
6) To list available users - Login as root;
Use mysql;
SELECT USER FROM user;
SELECT USER,PASSWORD FROM user; #To know whether password is set fot users

Here are some more use full commands

1) CREATE DATABASE 134a;
2) DROP DATABASE 134a;
3) USE 134a;
4) CREATE TABLE president (
last_name VARCHAR(15) NOT NULL,
first_name VARCHAR(15) NOT NULL,
state VARCHAR(2) NOT NULL,
city VARCHAR(20) NOT NULL,
birth DATE NOT NULL DEFAULT '0000-00-00',
death DATE NULL
);
5) SHOW TABLES; #list tables
6) DESCRIBE president; #to view structure of table
7) INSERT INTO president VALUES ('Washington','Bush','George', 'VA', 'New York', '19320212', '19991214');
8) SELECT * FROM president;
9) SELECT * FROM president WHERE state="VA"; #selecting rows by using WHERE clause
10) SELECT state,first_name,last_name FROM president; #selecting specific columns
11) DELETE FROM president WHERE first_name="George"; # Deleting selected row
12) UPDATE president SET state="CA" WHERE first_name="George"; #Modify entries
13) LOAD DATA LOCAL INFILE 'president_db' INTO TABLE president; #Loading your data from a file into a table. Also try "mysql -u USERNAME -p < my_mysql_file
14) SELECT * FROM president WHERE death=NULL; # or WHERE death IS NULL #list president who are alive
15) SELECT last_name, birth FROM president WHERE birth < '1800-09-01';
16) SELECT last_name, birth FROM president ORDER BY birth ASC LIMIT 1; #select president who was born first
17) SELECT state, count(*) AS times FROM president GROUP BY state ORDER BY times DESC LIMIT 5; # Names of first 5 states in which the greatest number of presidents have been born
18) SELECT * FROM president WHERE (YEAR (now())-YEAR(birth)) < 60; #President who have been born in last 60 years
19) SELECT last_name, birth, death, FLOOR ((TO_DAYS(death)-TO_DAYS(birth))/365) AS age FROM president WHERE death IS NOT NULL ORDER BY age DESC LIMIT 10; #President who have died by their age in descending order
20) SELECT last_name, address, test_date, score FROM test, student WHERE test.ssn = student.ssn;

You can refer mysql.com/documentation/index.html and
mysql.com/documentation/bychapter/manual_Introduction.html for more details