« assignment 3 | Main | class05 »
February 14, 2005
class 04
relationship between tables
null values to enter no information which once can add at a later time.
Now that we have a basic understanding of how to create a simple table, we need to develop a better understanding of how to separate our data into a particular configuration of tables. Database design has evolved to the point where there are specific steps you can take to optimize any database configuration. This process is called normalization and it has three distinct forms.
forms on normalization found on class notes
removing further redundancy deals with many to many relationship tables.
Multiple Table Joins
The whole point of having a relational database is to be able to connect two or more different tables such that we can extract various sets of data. The process of pulling data from two or more related tables is called a join. The most common is the inner join which has two different syntaxes. For our purposes, we will be using the older syntax, which is a bit easier to read. This format is:
SELECT [DISTINCT] column1[,column2]
FROM table1,table2[,table3]
[WHERE "conditions"]
[GROUP BY "column-list" [ASC | DESC] ]
[HAVING "conditions]
[ORDER BY "column-list" [ASC | DESC] ]
[LIMIT [offset,] number-rows]
see extended text to see working function
For class note:
SELECT m.firstname,m.lastname,c.name
FROM music101_musician m, music101_country c
WHERE m.birth_country_id = c.id
For puffles and types:
SELECT p.name, m.type
FROM media_types m, puffles p
WHERE m.id = p.type
DATE_FORMAT(date,format)
Formats the date value according to the format string. The
following specifiers may be used in the format string:
| Specifier | Description |
| %M | Month name (January..December) |
| %W | Weekday name (Sunday..Saturday) |
| %D | Day of the month with English suffix (0th, 1st, 2nd, 3rd, etc.) |
| %Y | Year, numeric, 4 digits |
| %y | Year, numeric, 2 digits |
| %X | Year for the week where Sunday is the first day of the week, numeric, 4 digits; used with %V |
| %x | Year for the week, where Monday is the first day of the week, numeric, 4 digits; used with %v |
| %a | Abbreviated weekday name (Sun..Sat) |
| %d | Day of the month, numeric (00..31) |
| %e | Day of the month, numeric (0..31) |
| %m | Month, numeric (00..12) |
| %c | Month, numeric (0..12) |
| %b | Abbreviated month name (Jan..Dec) |
| %j | Day of year (001..366) |
| %H | Hour (00..23) |
| %k | Hour (0..23) |
| %h | Hour (01..12) |
| %I | Hour (01..12) |
| %l | Hour (1..12) |
| %i | Minutes, numeric (00..59) |
| %r | Time, 12-hour (hh:mm:ss followed by AM or PM) |
| %T | Time, 24-hour (hh:mm:ss) |
| %S | Seconds (00..59) |
| %s | Seconds (00..59) |
| %f | Microseconds (000000..999999) |
| %p | AM or PM |
| %w | Day of the week (0=Sunday..6=Saturday) |
| %U | Week (00..53), where Sunday is the first day of the week |
| %u | Week (00..53), where Monday is the first day of the week |
| %V | Week (01..53), where Sunday is the first day of the week; used with %X |
| %v | Week (01..53), where Monday is the first day of the week; used with %x |
| %% | A literal `%'. |
Posted by dimitri at February 14, 2005 03:35 PM