I've been handed over management of a database, and I have had very little exposure to MySQL (or databases in general) so far.
I would like to do two things, both of which are (I expect) fairly simple, however it is the combination of the two which is causing me difficulty.
At present, I have one very large table containing all data. It can easily be divided into three smaller tables, as one of the fields effectively has only three distinct values, by which I wish to separate it. I assume writing a query to find entries of each value and copy only the correct one across to a new table would be very simple.
However, my problem is that the fields in this table are very, very poorly named and misspelled. So, when I create my new tables I would like to rename many of the field names. I will also potentially need to add fields.
Is there a way to achieve this so that I may create new tables with correctly named fields and then copy the data across without any intermediary steps?
The two obvious (though very clumsy) ways to do this that I can see would be to -
Copy the current table, rename all the fields to match the newly designed tables, and then copy the data across since the field names match. (This is not ideal as the current table is very large, and the issue of additional fields in the new tables may still cause issues?)
Create the new tables with the same poor field names as the original. Copy the data across. Change the new table's field names.
Both these potential methods seem very clumsy. Is there a way to do this that simply lets me create a new table with correct field names and then transfer the old data easily at any time?
(Please forgive me if this is an obvious or awkward question, I'm very, very new to this. Thank you.)