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