MySQL Quick Reference

This is just a simple quick reference for normal mysql routines that I forget from time to time and have to lookup.

MySQL

Indexes (from SitePoint)

Adding a “normal” index via CREATE INDEX:
mysql> CREATE INDEX [index_name] ON tablename (index_columns);
Example mysql> CREATE INDEX fname_lname_age ON people (firstname,lastname,age);

Adding a unique index via CREATE INDEX:
mysql> CREATE UNIQUE INDEX [index_name] ON tablename (index_columns);
Example: mysql> CREATE UNIQUE INDEX fname_lname_age ON people (firstname,lastname,age);

Adding a “normal” index via ALTER TABLE:
mysql> ALTER TABLE tablename ADD INDEX [index_name] (index_columns);
Example: mysql> ALTER TABLE people ADD INDEX fname_lname_age (firstname,lastname,age);

Adding a unique index via ALTER TABLE:
mysql> ALTER TABLE tablename ADD UNIQUE [index_name] (index_columns);
Example:mysql> ALTER TABLE people ADD UNIQUE fname_lname_age (firstname,lastname,age);

Adding a primary key via ALTER TABLE:
mysql> ALTER TABLE tablename ADD PRIMARY KEY (index_columns);
Example: mysql> ALTER TABLE people ADD PRIMARY KEY (peopleid);

Adding a “normal” index via CREATE TABLE:
mysql> CREATE TABLE tablename (
rest of columns,
INDEX [index_name] (index_columns)
[other indexes]
);

Example:
mysql> CREATE TABLE people (
peopleid SMALLINT UNSIGNED NOT NULL,
firstname CHAR(50) NOT NULL,
lastname CHAR(50) NOT NULL,
age SMALLINT NOT NULL,
townid SMALLINT NOT NULL,
INDEX fname_lname_age (firstname,lastname,age)
);

Adding a unique index via CREATE TABLE:
mysql> CREATE TABLE tablename (
rest of columns,
UNIQUE [index_name] (index_columns)
[other indexes]
);
Example:
mysql> CREATE TABLE people (
peopleid SMALLINT UNSIGNED NOT NULL,
firstname CHAR(50) NOT NULL,
lastname CHAR(50) NOT NULL,
age SMALLINT NOT NULL,
townid SMALLINT NOT NULL,
UNIQUE fname_lname_age (firstname,lastname,age)
);

Adding a primary key via CREATE TABLE:
mysql> CREATE TABLE tablename (
rest of columns,
INDEX [index_name] (index_columns)
[other indexes]
);
Example:
mysql> CREATE TABLE people (
peopleid SMALLINT NOT NULL AUTO_INCREMENT,
firstname CHAR(50) NOT NULL,
lastname CHAR(50) NOT NULL,
age SMALLINT NOT NULL,
townid SMALLINT NOT NULL,
PRIMARY KEY (peopleid)
);

Dropping (removing) a “normal” or unique index via ALTER TABLE:
mysql> ALTER TABLE tablename DROP INDEX index_name;
Example: mysql> ALTER TABLE people DROP INDEX fname_lname_age;

Dropping (removing) a primary key via ALTER TABLE:
mysql> ALTER TABLE tablename DROP PRIMARY KEY;
Example: mysql> ALTER TABLE people DROP PRIMARY KEY;

-----------------------------------------------------------------------

Add Column
mysql> ALTER TABLE tablename ADD COLUMN [Col_Name] [Col_Definitions]

Add TinyINT Column
mysql> ALTER TABLE customers ADD COLUMN age tinyint unsigned NOT NULL default '0';

Add Varchar Column
mysql> ALTER TABLE customers ADD COLUMN first_name varchar(40) NULL;


Drop Column
mysql> ALTER TABLE tablename DROP COLUMN [Col_Name]
Example
mysql> ALTER TABLE tablename DROP COLUMN age;

-----------------------------------------------------------------------

Import/Export DB and Tables

DB Import Example
mysql -u [username] -p [password] [database] < [filename]
~$ mysql -u my_user -p < customers_backup.sql

Full DB Export Example
mysqldump [options...] -u [username] -p [password] [database] > [filename]
~$ mysqldump -u my_user -p customers > customers_backup.sql

Full DB Export with Drop Table
mysqldump [options...] -u [username] -p [password] [database] > [filename]
~$ mysqldump --add-drop-table -u my_user -p customers > customers_backup.sql

DB Export with Specific Tables
mysqldump -u [username] -p [password] [databasename] [table1 table2 ...] > [filename]
~$ mysqldump --add-drop-table -u my_user -p customers account_info blog_entries > customers_backup.sql

-----------------------------------------------------------------------

Table Stats

SHOW TABLE STATUS
SHOW TABLE STATUS [ {FROM | IN} DB_NAME ] [LIKE '%' | WHERE
The following example uses a database name of "stats" and a table name of "Server"
mysql> SHOW TABLE STATUS FROM `stats` WHERE `Name` = 'Server'

-----------------------------------------------------------------------

Misc

Create User
CREATE USER user [IDENTIFIED BY [PASSWORD] 'password'];
mysql> Create USER my_username IDENTIFIED BY 'my_password';

Create Database
$ mysqladmin -u -p create
~$ mysqladmin -u my_username -p create my_database

Drop/Delete Database
$ mysqladmin -u -p drop
~$ mysqladmin -u my_username -p drop my_database