Basic MySQL Shell commands

#Mysql Shell notes

#restart service if needed
sudo /etc/init.d/mysql restart

#login with password prompt
mysql -u root -p

#list databases
show databases;

#create database
CREATE DATABASE MyDatabase;

#choose database to use
USE MyDatabase;

#list tables
show tables;

#Create a table with auto increments and some fields
CREATE TABLE MyTable ( id INT NOT NULL AUTO_INCREMENT , name TEXT NOT NULL , address TEXT NOT NULL , phone TEXT NOT NULL , PRIMARY KEY (id)) ENGINE = InnoDB;

#enter data into table
INSERT INTO MyTable (id, name, address, phone) VALUES (NULL, 'John Smith', '123 Elm Street', '(555)555-5555');

#show entire table
select * from MyTable;

#delete row
DELETE FROM MyTable WHERE id = '2';

#query (Not case sensitive)
SELECT * FROM MyTable WHERE name = 'betty friend';
SELECT * FROM MyTable WHERE name LIKE 'betty%';

#modify row
UPDATE MyTable SET address="124 Elm Street", phone="(555)555-3333" WHERE id=4;

#Backup and restore Database
mysqldump -u root -p --all-databases > dump.sql
mysql -u root -p <dump.sql

#or by database
mysqldump -u root -p MyDatabase > dumpfilename.sql
mysql -u root -p MyDatabase < dumpfilename.sql