Home » Database

Useful MySQL Commands

14 February 2011 No Comment
VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)

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 your response!

Add your comment below, or trackback from your own site. You can also subscribe to these comments via RSS.

Be nice. Keep it clean. Stay on topic. No spam.

You can use these tags:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

This is a Gravatar-enabled weblog. To get your own globally-recognized-avatar, please register at Gravatar.