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