Facebooktwitterredditpinterestlinkedintumblr

Useful MySQL CommandsIf you support or use MySQL as your database backend, you will most likely be doing certain tasks over and over again. For example, once you have a table, you may want to modify the columns. Here are some commands that come in handy and can be used as a reference guide.

Add a column to an existing table.

The statement below will add a column called email (with type varchar) after the subject column to the contacts table:

ALTER TABLE contacts ADD email VARCHAR(60) AFTER subject;

The statement below will add a column called deleted with type boolean and default value 0 to the movies table:

ALTER TABLE movies ADD deleted TINYINT(1) DEFAULT 0;

Delete a column from an existing table.

The statement below will delete a column called email from the contacts table:

ALTER TABLE contacts DROP email;

Rename a column to an existing table.

The statement below will rename a column called email to address to the contacts table:

ALTER TABLE contacts CHANGE email address VARCHAR(60);

Create an index for a column.

The statement below will create an index called index_contacts for a column called email on the contacts table:

CREATE INDEX index_contacts ON contacts(email);

Show all the indexes on a table.

The statement below will show all the indexes for a table called contacts:

SHOW INDEXES FROM contacts;

Import delimited data into MySQL.

The statement below will show you how to import a comma-delimited text file called testfile.csv into the test_table with table columns called column1, column2 and column3:

LOAD DATA LOCAL INFILE ”testfile.csv”
INTO TABLE test_table
FIELDS TERMINATED BY ”,”
LINES TERMINATED BY ”\\n”
(column1, column2, column3);

Leave a Comment