Magoo's Wise Words
MySQL Quickstart Guide

P
A
G
E

F
O
U
R

O
F

F
O
U
R

MySQL Command Quick-reference

Here is a list of commonly used commands in MyQL for easy reference.

Admistration

Login:

Mysql –u username –p

or

Mysql –u username –ppassword (this method of logging in is insecure)

Note: The root password is blank by default

Logout:

QUIT

Create user or add user to a database:

GRANT ALL PRIVILEGES ON databasename.* TO ‘username’@’hostname’;

Delete a user or remove a user from a database:

REVOKE ALL PRIVILEGES ON databasename.* FROM username;

or

DROP USER username;

Change passwords:

SET PASSWORD = PASSWORD(' some password') -to change the password of the user currently logged in

SET PASSWORD FOR user = PASSWORD(' some password') -to change the password of a different user (current user must have administrative privileges)

Databases

Create a database:

CREATE DATABASE databasename; -current user must have create privilege on databasename

Delete a database:

DROP DATABASE databasename;

List all the databases:

SHOW DATABASES;

Change which database you are working with:

USE databasename;

Tables

See what tables are in the database you are working with:

SHOW TABLES;

Create a new table:

CREATE TABLE tablename(column1name columntype, column2name columntype);

Add a new column to a table after another column:

ALTER TABLE tablename ADD columnname columntype after columnname1;

Delete a column from a table:

ALTER TABLE tablename DROP columnname;

Change a column type

ALTER TABLE tablename MODIFY columnname newcolumntype;

Add a row to a table:

INSERT INTO tablename (colmn1, colmn2) VALUES (value1, value2);

Delete a row or rows matching a case:

DELETE FROM tablename WHERE columnname=case

Show all the columns in a particular table:

DESCRIBE tablename;

Display an entire table

SELECT * FROM tablename;

Display particular rows of a table in a particular order:

SELECT * FROM tablename WHERE columnname=value ORDER BY order;

Display data from only particlar columns of a table:

SELECT columnname FROM tablename;

Change the information stored in a row or rows that match a case:

UPDATE tablename SET colmnname=value WHERE columnname2=case;

More advanced commands can be found in the MySQL documentation on the official web site.

Next =>[Magoo's MySQL Tutorial - Page 1, MySQL Tutorial Introduction]