SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Addict
    Join Date
    Jan 2012
    Posts
    266
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Manipulating Database Tables

    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:

    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. #2
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,264
    Mentioned
    196 Post(s)
    Tagged
    2 Thread(s)
    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. #3
    SitePoint Addict
    Join Date
    Jan 2012
    Posts
    266
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks; that looks like a pretty impressive software program.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Chavista View Post
    Can anyone tell me if some of the functions I've listed below can be done with 1) phpMyAdmin or 2) other database software?
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •