Working with multiple tables 27

What  we  really  need  to  do  is  create  a  new  table,  much  like  we  did  above.  Since  we'll  be

creating it from almost scratch this time, we won't have the same problems we had above.

The first thing to do is make a new table with all the genres in it. We know how to make a

table from data in an existing table. First, we find the select statement we're going to need.

select distinct genre from songs

That shows us the 29 genres we're going to have in our new genre table.

create table genres select distinct genre from songs

That creates a very simple one column table with our genre names in it. The next step is to

add  the  rest  of  the  columns  we  need:  an  auto  increment  ID  column,  and  the  two  text

columns for description and history, respectively.

You  already  know  how  to  create  the  ID  column.  But  our  current  text  column  type,  the

variable length character column, is really designed for small pieces of text. If your text is

going to contain more than 255 characters, varchar will not suffice. For longer text, we have

the  text  column  type.  Text  columns  can  contain  up  to  65,535  characters.  (If  that  isn't

enough for you, there is also mediumtext which can contain up to 16,777,215 characters!)

Make the description and history columns be text types.

Now,  go  ahead  and  set  up  some  content  for  the  description  and  history  of  a  few  of  the

genres. For example:

update genres set description="Rock and roll is a form of popular music,

usually featuring vocals (often with vocal harmony), electric guitars and

a strong back beat; other instruments, such as the saxophone, are common

in some styles. Rock s social impact on the world is unparalleled by any

other kind of music. It has been credited with ending wars and spreading

peace and tolerance, as well as corrupting the innocent and spreading

moral rot." where genre="Rock"

update genres set history="Rock and roll emerged as a defined musical style

in America in the 1950s, though elements of rock and roll can be heard in

rhythm and blues records as far back as the 1920s. Early rock and roll

combined elements of blues, boogie woogie, jazz and rhythm and blues, and

is also influenced by traditional Appalachian folk music, gospel and

country and western. Going back even further, rock and roll can trace a

foundational lineage to the old Five Points district of mid 19th century

New York City, the scene of the first fusion of heavily rhythmic African

shuffles and sand dances with melody driven European genres, particularly

the Irish jig.\nRocking was a term first used by gospel singers in the

American South to mean something akin to spiritual rapture. By the 1940s,

however, the term was used as a double entendre, ostensibly referring to

dancing, but with the hidden subtextual meaning of sex; an example of this

is Roy Brown s \"Good Rocking Tonight\". This type of song was usually