Thursday, November 06, 2008

More database commands

I mentioned a few articles ago that I am getting some real quality MySQL database experience at my job and I wasn't kidding.  I am loving it and having fun. 

After writing a Perl function to audit our databases to ensure they were defined correctly, per our pre-defined schemas, I had to start making changes to the databases in question.  The first thing that I took care of was the deletions of whole tables and databases.  That was easy through the use of the 'drop' command, but now I am into the modifications. 

There are fields in tables that have either the wrong name or the wrong defined type.  So, I have to change them.  The wrong defined type is easy and modified using the following command:

          alter table <table_name> modify column <column_name> type;

<table_name>  => replace with the name of the table with fields you are fixing.
<column_name> => replace with the column / field that you are fixing.
type  =>  replace with the type that you want to change to (ie: int, bigint, float, varchar(??), etc)

The other type of change is where the name of a field/column is incorrect.  In that case, you would use the following command sequence:

           alter table <table_name> change column <old_column_name> <new_column_name> type;

<table_name>  =>  the table that has fields needing fixing.
<old_column_name>  =>  the present name of the column you want to fix.
<new_column_name> =>  the new name for the column you want to fix.
type  =>  while you are not changing it, you still have to specify the type for the column/field.  Just look at what it is presently defined as using "describe <table_name>" to see all field definitions.

Now, with the last one, I am running into a bunch of issues where two fields need their names switched.  The data is in the correct place, but the names aren't.  In that case, you would move one of them to a temporary name (like the new name with "_temp" added on to it) and then moved to its final name after the other field has been moved to its new name.

As I said, its all very interesting and fun to learn, while being quite involving.  I have become very acquainted with the MySQL online documentation, that is for sure. 

No comments:

Creative Commons License
This work is licensed under a Creative Commons Attribution 3.0 License.