Manipulating Database Tables

Databases
1

I’m getting in deeper with MySQL databases and would like to have more tools for manipulating them. Can anyone tell me if some of the functions I’ve listed below can be done with 1) phpMyAdmin or 2) other database software?

1. Combine two tables - For example, imagine a table with rows with articles about mammals and another table with articles about birds. Is there a way to automatically combine them into one table, rather than manually copying and pasting data from one to the other?

2. Split a table - If you want to split a huge table into two small tables, is there a quick, easy way to delete all rows with a certain value? For example, if there are 8,000 rows where a value in field Class = Bird, could you instantly delete all those rows?

3. Replace with regular expressions - I’ve learned how to replace data with this code:


UPDATE my_table SET Title = replace(Title,'United States','USA')

But can I use regular expressions to perform more sophisticated search-and-replace operations?

4. Import a matching numerical key - For example, image a table with the following rows:

1 | antelope
2 | bear
3 | zebra

If you have a second table with just two rows…

antelope
zebra

…could you change it so that it displays this?:

1 | antelope
3 | zebra

I should point out that most of my database tables are derived from Excel spreadsheets, so I can make a lot of changes in my spreadsheets, then reimport the data into my database tables. However, database tables with articles pose a special problem, since I can’t store articles in my spreadsheets.

Also, I just downloaded a program called sqlbuddy, though it doesn’t seem much different than phpMyAdmin at first glance.

2

If you’re working with MySQL, maybe you should look into MySQL Workbench?
It’s from the same as MySQL so even though I’ve only used it for relatively “basic” stuff, I can’t imagine there being anything better suited for working with the database. That is, if anything can do it, it’s probably this.

3

Thanks; that looks like a pretty impressive software program.

4

all but one can be done with any software that lets you run sql queries

  1. INSERT INTO mammals SELECT * FROM birds

  2. DELETE FROM daTable WHERE Class = ‘Bird’

  3. sorry, nope, mysql’s REGEXP cannot replace

  4. joined update