Mysql commands

Handy Cheat-Sheet of MySQL Commands

From your login shell

Creating a Database

mysqladmin create mydatabase

Dropping (Removing) a Database

mysqladmin drop mydatabase

Populating an Existing Database from a *.sql File

mysql databasename < filename.sql

Dumping Database Structure and Data to a *.sql file

mysqldump databasename > filename.sql
mysqldump databasename table1 > table1.sql

From within the MySQL interface

Starting MySQL from the Command Line

mysql

You will be welcomed with the following message:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is ## to server version: #.##.##

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

The prompt changes to "mysql>", which will be shown in each example below.

Seeing What Databases are Available

show databases;

(be sure to use the semi-colon to terminate the command)
Telling MySQL to Use a Specific Database

use mydatabase;

Seeing What Tables are Available Within a Database

show tables;

Looking at the Data in a Particular Table

select * from [tablename];

Rename an existing Table

RENAME TABLE tbl_name TO new_tbl_name

Adding a Database User with Password
grant all privileges on [databasename].* to [dbusername]@localhost identified by '[dbpassword]';

grant all privileges on mydatabase.* to joeuser@localhost identified by 'supersecretpasswd';
flush privileges;

Removing a Database User

delete from mysql.user where user='techgeek' and host='localhost';
flush privileges;

Add data to a table

insert into cid  (name, number) values 
('My Name', '0731234321'),
('Mr Name', '0412344321');

Delete data

delete from cid where name like '0%';

Performing Search-and-Replace Actions on a Table
update DatabaseName set TableName = REPLACE(TableName,"[String to Search For]","[String Replacement]");

update roesublist set Grades = REPLACE(Grades,"Grades: Any","K,1,2,3,4,5,6,7,8,9,10,11,12");