MySQL1 Merge & Joins (5&6)

Card Set Information

MySQL1 Merge & Joins (5&6)
2012-07-26 12:02:42

From my class...
Show Answers:

  1. keyword can be used in front of any SELECT statement, to analyze the optimization of that search.
  2. What makes a column indexed and how to your do it?
    • Adding a key
    • alter table bob add key(field_name);
  3. Keyword to add at the end of a statement to make it count in a certain column
    alter table table_of_contents modify chapter int unsigned NOT NULL auto_increment;
  4. Make a primary key
    alter table table_of_contents add primary key(chapter);
  5. Key word to make an alias
  6. if you want to join two tables what do you use?
    What do you do to reference a field from a certain table if there is more than one table?
    • , as in select * from bob, mary
    • . as in bob.fieldname, mary.fieldname
  7. Difference between an inner join and outer join
    inner join contains items that are only included in both tables outer join contains items only in one table.
  8. In an outer join, what key words to you use to combine everything from one table to another?
    • left join (left outer join) as in
    • select * from table_of_contents left outer join chapter_topics ON table_of_contents.chapter = chapter_topics.chapter;
  9. Keyword like where, but is not required
  10. key word that allows you to merge results by one field, listing only the first entry in the field for repeating enties
    • group by
    • mysql> select * from table_of_contents as tc, chapter_topics as ct where
    • -> tc.chapter = ct.chapter GROUP BY tc.chapter;
  11. Word used at the start of a sql search to prevent duplicates
    • distinct
    • select DISTINCT tc.chapter, tc.chapter_name from table_of_contents as tc, chapter_topics as ct where
    • -> tc.chapter = ct.chapter;
  12. function that lists the number of different field entries for a field
    • count()
    • select tc.chapter,tc.chapter_name,count(ct.topic) from table_of_contents
  13. If you are using group by what do you use instead of where? Does it go before or after group by?
    Having...which goes AFTER group by
  14. change the order of the results in reverse order?
    order by fieldname descending
  15. function to have only a subset of results
    limit 0,1

    • 0 is starting row
    • 1 is number of rows to display