Here's a collection of useful commands when working with the MySQL Database Server, either on the terminal or directly inside the database console.

Command Line

**mysql -u root @localhost**

.... login to mysql server with username 'root', host = localhost, this
will drop you into a sql console where you can fire off common SQL
queries & commands (e.g. select * from users, create table users...)

mysqladmin -u root password [mysqlpassword]

.... change the 'root' password

mysqladmin -u root create sessions_development

.... using 'root' account, create database 'sessions_development'

mysqladmin -u root drop sessions_development

.... using 'root' account, delete database 'sessions_development'

mysqldump -u root -ppassword ---opt all.sql

.... backup all databases to disk

mysqldump -u root mydb > mydb.sql

.... backup only database 'mydb' to disk

mysql -u username -ppassword mydb < mydb.sql

.... restore database mydb from disk

Console Queries

CREATE TABLE new_tbl SELECT * FROM orig_tbl;

.... create one table from the results of a SELECT query

CREATE TABLE people (
id INT NOT NULL AUTO\_INCREMENT PRIMARY KEY, fullname VARCHAR (255) );

.... creates a new table 'people' with an auto-incrementing
(AUTO_INCREMENT) 'id' field that is the primary key (PRIMARY KEY) and
can't be null (NOT NULL), along with a 'fullname' variable text string
field

INSERT INTO goods (price) VALUES (1.99);

.... insert a new record into goods with the field 'price' of 1.99

UPDATE goods SET price = 2.99 WHERE name = 'shampoo';

.... update 'price' value for record with 'name' of shampoo in 'goods'
table

DROP TABLE IF EXISTS goods;

.... conditionally only delete the table 'goods' if it exists

SHOW databases;

.... list all databases on server

USE mydb;

.... switch to another database

DESC goods;

.... show table definition for 'goods' table

SHOW CREATE TABLE goods;

.... show the sql syntax for creating the 'goods' table

DESCRIBE goods;

.... to see all of table 'goods' field formats

FLUSH PRIVILEGES;

.... update all database permissions & privileges

COMMIT;

.... commit all pending transactions

ROLLBACK;

.... rollback previous transaction