Useful MySQL Commands
If 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:
The statement below will add a column called deleted with type boolean and default value 0 to the movies table:
Delete a column from an existing table.
The statement below will delete a column called email from the contacts table:
Rename a column to an existing table.
The statement below will rename a column called email to address to the contacts table:
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:
Show all the indexes on a table.
The statement below will show all the indexes for a table called 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:
INTO TABLE test_table
FIELDS TERMINATED BY ”,”
LINES TERMINATED BY ”\\n”
(column1, column2, column3);